SQL related fixes and updates

nosnilmot commented 1 year ago

Various (mostly) SQL related fixes and updates

These changes may be more easily reviewed by commit, as each is self-contained. If preferred I can submit separately, although there is some ordering required.

Correct README for creating test docker MS SQL DB
MS SQL schema fixes Add missing tables, fix text vs varchar issues, fix definition of mqtt_pub table (refs #3097)
Fix MS SQL error caused by ORDER BY in subquery
New SQL schema migrate fix server_host column on route table already exists in old schema and does not need adding for new schema migration.
Remove unnecessary indexes For columns already included in a compound index there is no benefit to having a separate index with a subset of the same columns in the same order, it just wastes space. (refs #3860)
Minor MS SQL config improvements Add ability to indicate SSL is required using sql_ssl option, and allow custom ODBC connection string specification in sql_server (refs #3978)
Add 'new' schema for MS SQL (resolves #3702)
Enable MySQL support for new schema migration (resolves #3439) Most of the code was there, it just needed finishing up
Add MS SQL support for new schema migration
Fix minor SQL schema inconsistencies
Use python3 to run extauth.py for tests
Change PostgreSQL SERIAL columns to BIGSERIAL (refs #3860)

The notes below can be used to apply these changes to existing databases.

Database Updates

PostgreSQL

Regular or New schema:

To convert columns to allow up to 2 billion rows in these tables. This conversion will require full table rebuilds, and will take a long time if tables already have lots of rows. Optional: this is not necessary if the tables are never likely to grow large.

ALTER TABLE archive ALTER COLUMN id TYPE BIGINT;
ALTER TABLE privacy_list ALTER COLUMN id TYPE BIGINT;
ALTER TABLE pubsub_node ALTER COLUMN nodeid TYPE BIGINT;
ALTER TABLE pubsub_state ALTER COLUMN stateid TYPE BIGINT;
ALTER TABLE spool ALTER COLUMN seq TYPE BIGINT;

PostgreSQL or SQLite

Regular schema:

DROP INDEX i_rosteru_username;
DROP INDEX i_sr_user_jid;
DROP INDEX i_privacy_list_username;
DROP INDEX i_private_storage_username;
DROP INDEX i_muc_online_users_us;
DROP INDEX i_route_domain;
DROP INDEX i_mix_participant_chan_serv;
DROP INDEX i_mix_subscription_chan_serv_ud;
DROP INDEX i_mix_subscription_chan_serv;
DROP INDEX i_mix_pam_us;

New schema:

DROP INDEX i_rosteru_sh_username;
DROP INDEX i_sr_user_sh_jid;
DROP INDEX i_privacy_list_sh_username;
DROP INDEX i_private_storage_sh_username;
DROP INDEX i_muc_online_users_us;
DROP INDEX i_route_domain;
DROP INDEX i_mix_participant_chan_serv;
DROP INDEX i_mix_subscription_chan_serv_ud;
DROP INDEX i_mix_subscription_chan_serv;
DROP INDEX i_mix_pam_us;

Add index that might be missing

PostgreSQL:

CREATE INDEX i_push_session_sh_username_timestamp ON push_session USING btree (server_host, username, timestamp);

SQLite:

CREATE INDEX i_push_session_sh_username_timestamp ON push_session (server_host, username, timestamp);

MySQL

Regular schema:

ALTER TABLE rosterusers DROP INDEX i_rosteru_username;
ALTER TABLE sr_user DROP INDEX i_sr_user_jid;
ALTER TABLE privacy_list DROP INDEX i_privacy_list_username;
ALTER TABLE private_storage DROP INDEX i_private_storage_username;
ALTER TABLE muc_online_users DROP INDEX i_muc_online_users_us;
ALTER TABLE route DROP INDEX i_route_domain;
ALTER TABLE mix_participant DROP INDEX i_mix_participant_chan_serv;
ALTER TABLE mix_participant DROP INDEX i_mix_subscription_chan_serv_ud;
ALTER TABLE mix_participant DROP INDEX i_mix_subscription_chan_serv;
ALTER TABLE mix_pam DROP INDEX i_mix_pam_u;

New schema:

ALTER TABLE rosterusers DROP INDEX i_rosteru_sh_username;
ALTER TABLE sr_user DROP INDEX i_sr_user_sh_jid;
ALTER TABLE privacy_list DROP INDEX i_privacy_list_sh_username;
ALTER TABLE private_storage DROP INDEX i_private_storage_sh_username;
ALTER TABLE muc_online_users DROP INDEX i_muc_online_users_us;
ALTER TABLE route DROP INDEX i_route_domain;
ALTER TABLE mix_participant DROP INDEX i_mix_participant_chan_serv;
ALTER TABLE mix_participant DROP INDEX i_mix_subscription_chan_serv_ud;
ALTER TABLE mix_participant DROP INDEX i_mix_subscription_chan_serv;
ALTER TABLE mix_pam DROP INDEX i_mix_pam_us;

Add index that might be missing:

CREATE INDEX i_push_session_sh_username_timestamp ON push_session (server_host, username(191), timestamp);

MS SQL

DROP INDEX [rosterusers_username] ON [rosterusers];
DROP INDEX [sr_user_jid] ON [sr_user];
DROP INDEX [privacy_list_username] ON [privacy_list];
DROP INDEX [private_storage_username] ON [private_storage];
DROP INDEX [muc_online_users_us] ON [muc_online_users];
DROP INDEX [route_domain] ON [route];
go

MS SQL schema was missing some tables added in earlier versions of ejabberd:

CREATE TABLE [dbo].[mix_channel] (
    [channel] [varchar] (250) NOT NULL,
    [service] [varchar] (250) NOT NULL,
    [username] [varchar] (250) NOT NULL,
    [domain] [varchar] (250) NOT NULL,
    [jid] [varchar] (250) NOT NULL,
    [hidden] [smallint] NOT NULL,
    [hmac_key] [text] NOT NULL,
    [created_at] [datetime] NOT NULL DEFAULT GETDATE()
) TEXTIMAGE_ON [PRIMARY];

CREATE UNIQUE CLUSTERED INDEX [mix_channel] ON [mix_channel] (channel, service)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE INDEX [mix_channel_serv] ON [mix_channel] (service)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE TABLE [dbo].[mix_participant] (
    [channel] [varchar] (250) NOT NULL,
    [service] [varchar] (250) NOT NULL,
    [username] [varchar] (250) NOT NULL,
    [domain] [varchar] (250) NOT NULL,
    [jid] [varchar] (250) NOT NULL,
    [id] [text] NOT NULL,
    [nick] [text] NOT NULL,
    [created_at] [datetime] NOT NULL DEFAULT GETDATE()
) TEXTIMAGE_ON [PRIMARY];

CREATE UNIQUE INDEX [mix_participant] ON [mix_participant] (channel, service, username, domain)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE INDEX [mix_participant_chan_serv] ON [mix_participant] (channel, service)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE TABLE [dbo].[mix_subscription] (
    [channel] [varchar] (250) NOT NULL,
    [service] [varchar] (250) NOT NULL,
    [username] [varchar] (250) NOT NULL,
    [domain] [varchar] (250) NOT NULL,
    [node] [varchar] (250) NOT NULL,
    [jid] [varchar] (250) NOT NULL
);

CREATE UNIQUE INDEX [mix_subscription] ON [mix_subscription] (channel, service, username, domain, node)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE INDEX [mix_subscription_chan_serv_ud] ON [mix_subscription] (channel, service, username, domain)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE INDEX [mix_subscription_chan_serv_node] ON [mix_subscription] (channel, service, node)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE INDEX [mix_subscription_chan_serv] ON [mix_subscription] (channel, service)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

CREATE TABLE [dbo].[mix_pam] (
    [username] [varchar] (250) NOT NULL,
    [channel] [varchar] (250) NOT NULL,
    [service] [varchar] (250) NOT NULL,
    [id] [text] NOT NULL,
    [created_at] [datetime] NOT NULL DEFAULT GETDATE()
) TEXTIMAGE_ON [PRIMARY];

CREATE UNIQUE CLUSTERED INDEX [mix_pam] ON [mix_pam] (username, channel, service)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

go

MS SQL also had some incompatible column types:

ALTER TABLE [dbo].[muc_online_room] ALTER COLUMN [node] VARCHAR (250);
ALTER TABLE [dbo].[muc_online_room] ALTER COLUMN [pid] VARCHAR (100);
ALTER TABLE [dbo].[muc_online_users] ALTER COLUMN [node] VARCHAR (250);
ALTER TABLE [dbo].[pubsub_node_option] ALTER COLUMN [name] VARCHAR (250);
ALTER TABLE [dbo].[pubsub_node_option] ALTER COLUMN [val] VARCHAR (250);
ALTER TABLE [dbo].[pubsub_node] ALTER COLUMN [plugin] VARCHAR (32);
go

... and mqtt_pub table was incorrectly defined in old schema:

ALTER TABLE [dbo].[mqtt_pub] DROP CONSTRAINT [i_mqtt_topic_server];
ALTER TABLE [dbo].[mqtt_pub] DROP COLUMN [server_host];
ALTER TABLE [dbo].[mqtt_pub] ALTER COLUMN [resource] VARCHAR (250);
ALTER TABLE [dbo].[mqtt_pub] ALTER COLUMN [topic] VARCHAR (250);
ALTER TABLE [dbo].[mqtt_pub] ALTER COLUMN [username] VARCHAR (250);
CREATE UNIQUE CLUSTERED INDEX [dbo].[mqtt_topic] ON [mqtt_pub] (topic)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
go

... and sr_group index/PK was inconsistent with other DBs:

ALTER TABLE [dbo].[sr_group] DROP CONSTRAINT [sr_group_PRIMARY];
CREATE UNIQUE CLUSTERED INDEX [sr_group_name] ON [sr_group] ([name])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
go

coveralls commented 1 year ago

Coverage: 33.116% (-0.05%) from 33.171% when pulling 4f0e426a12aadc6a7249e6a8d6fe62cdb0579cd7 on nosnilmot:sql-maintenance into 758c87f56489c479fe44413389802ca1da1ed278 on processone:master.

prefiks commented 1 year ago

Great work, all those patches looks ok to me.

licaon-kter commented 1 year ago

@nosnilmot can you draft an upgrade docs PR with what this entails for existing installs?

You need this for the next release anyway, 23.0x, but I'd like to keep up with ejabberd HEAD and I'm not brave enough to update past this PR as I don't know if "it wil magically work out" or break my schema.

Albeit your next (unmerged) PR mentions "autoupgrades" which sound nice but might not yet be active/usable.

nosnilmot commented 1 year ago

@nosnilmot can you draft an upgrade docs PR with what this entails for existing installs?

You need this for the next release anyway, 23.0x, but I'd like to keep up with ejabberd HEAD and I'm not brave enough to update past this PR as I don't know if "it wil magically work out" or break my schema.

Can I suggest you read the bit in the description of this PR after the words "The notes below can be used to apply these changes to existing databases." :smile:

For the most part you don't strictly need to do anything, everything will just keep working (or, in the case of MS SQL, not working) in exactly the same way as before. If you want the benefits from this PR for an existing installation you need to make the DB schema changes documented.

Albeit your next (unmerged) PR mentions "autoupgrades" which sound nice but might not yet be active/usable.

Nope, no "autoupgrades" there, automated testing of schema upgrades (ie. GitHub workflow).

licaon-kter commented 1 year ago

Ah silly me, that flew above my head, indeed, will do. Thanks

Neustradamus commented 1 year ago

@nosnilmot: Good job!

processone / ejabberd