Skip to content

Commit 9756819

Browse files
alexeyshchalexeyshch
committed
Update SQL schema files
1 parent 8d2d3a6 commit 9756819

File tree

8 files changed

+48
-0
lines changed

8 files changed

+48
-0
lines changed

‎sql/lite.new.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -106,13 +106,19 @@ CREATE TABLE archive (
106106
id INTEGER PRIMARY KEY AUTOINCREMENT,
107107
kind text,
108108
nick text,
109+
origin_id text,
109110
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
110111
);
111112

112113
CREATE INDEX i_archive_sh_username_timestamp ON archive (server_host, username, timestamp);
113114
CREATE INDEX i_archive_sh_username_peer ON archive (server_host, username, peer);
114115
CREATE INDEX i_archive_sh_username_bare_peer ON archive (server_host, username, bare_peer);
115116
CREATE INDEX i_archive_sh_timestamp ON archive (server_host, timestamp);
117+
CREATE INDEX i_archive_sh_username_origin_id ON archive (server_host, username, origin_id);
118+
119+
-- To update 'archive' from ejabberd <= 23.10:
120+
-- ALTER TABLE archive ADD COLUMN origin_id text NOT NULL DEFAULT '';
121+
-- CREATE INDEX i_archive_sh_username_origin_id ON archive (server_host, username, origin_id);
116122

117123
CREATE TABLE archive_prefs (
118124
username text NOT NULL,

‎sql/lite.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -94,13 +94,19 @@ CREATE TABLE archive (
9494
id INTEGER PRIMARY KEY AUTOINCREMENT,
9595
kind text,
9696
nick text,
97+
origin_id text,
9798
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
9899
);
99100

100101
CREATE INDEX i_username_timestamp ON archive(username, timestamp);
101102
CREATE INDEX i_archive_username_peer ON archive (username, peer);
102103
CREATE INDEX i_archive_username_bare_peer ON archive (username, bare_peer);
103104
CREATE INDEX i_timestamp ON archive(timestamp);
105+
CREATE INDEX i_archive_username_origin_id ON archive (username, origin_id);
106+
107+
-- To update 'archive' from ejabberd <= 23.10:
108+
-- ALTER TABLE archive ADD COLUMN origin_id text NOT NULL DEFAULT '';
109+
-- CREATE INDEX i_archive_username_origin_id ON archive (username, origin_id);
104110

105111
CREATE TABLE archive_prefs (
106112
username text NOT NULL PRIMARY KEY,

‎sql/mssql.new.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@ CREATE TABLE [dbo].[archive] (
3232
[id] [bigint] IDENTITY(1,1) NOT NULL,
3333
[kind] [varchar] (10) NULL,
3434
[nick] [varchar] (250) NULL,
35+
[origin_id] [varchar] (250) NOT NULL,
3536
[created_at] [datetime] NOT NULL DEFAULT GETDATE(),
3637
CONSTRAINT [archive_PK] PRIMARY KEY CLUSTERED
3738
(
@@ -51,6 +52,9 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW
5152
CREATE INDEX [archive_sh_timestamp] ON [archive] (server_host, timestamp)
5253
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
5354

55+
CREATE INDEX [archive_sh_username_origin_id] ON [archive] (server_host, username, origin_id)
56+
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
57+
5458
CREATE TABLE [dbo].[archive_prefs] (
5559
[username] [varchar] (250) NOT NULL,
5660
[server_host] [varchar] (250) NOT NULL,

‎sql/mssql.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,7 @@ CREATE TABLE [dbo].[archive] (
3131
[id] [bigint] IDENTITY(1,1) NOT NULL,
3232
[kind] [varchar] (10) NULL,
3333
[nick] [varchar] (250) NULL,
34+
[origin_id] [varchar] (250) NOT NULL,
3435
[created_at] [datetime] NOT NULL DEFAULT GETDATE(),
3536
CONSTRAINT [archive_PK] PRIMARY KEY CLUSTERED
3637
(
@@ -50,6 +51,9 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW
5051
CREATE INDEX [archive_timestamp] ON [archive] (timestamp)
5152
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
5253

54+
CREATE INDEX [archive_username_origin_id] ON [archive] (username, origin_id)
55+
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
56+
5357
CREATE TABLE [dbo].[archive_prefs] (
5458
[username] [varchar] (250) NOT NULL,
5559
[def] [text] NOT NULL,

‎sql/mysql.new.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -110,6 +110,7 @@ CREATE TABLE archive (
110110
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
111111
kind varchar(10),
112112
nick varchar(191),
113+
origin_id varchar(191),
113114
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
114115
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
115116

@@ -118,6 +119,12 @@ CREATE INDEX i_archive_sh_username_timestamp USING BTREE ON archive(server_host(
118119
CREATE INDEX i_archive_sh_username_peer USING BTREE ON archive(server_host(191), username(191), peer(191));
119120
CREATE INDEX i_archive_sh_username_bare_peer USING BTREE ON archive(server_host(191), username(191), bare_peer(191));
120121
CREATE INDEX i_archive_sh_timestamp USING BTREE ON archive(server_host(191), timestamp);
122+
CREATE INDEX i_archive_sh_username_origin_id USING BTREE ON archive(server_host(191), username(191), origin_id(191));
123+
124+
-- To update 'archive' from ejabberd <= 23.10:
125+
-- ALTER TABLE archive ADD COLUMN origin_id text NOT NULL DEFAULT '';
126+
-- ALTER TABLE archive ALTER COLUMN origin_id DROP DEFAULT;
127+
-- CREATE INDEX i_archive_sh_username_origin_id USING BTREE ON archive(server_host(191), username(191), origin_id(191));
121128

122129
CREATE TABLE archive_prefs (
123130
username varchar(191) NOT NULL,

‎sql/mysql.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -98,6 +98,7 @@ CREATE TABLE archive (
9898
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
9999
kind varchar(10),
100100
nick varchar(191),
101+
origin_id varchar(191),
101102
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
102103
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
103104

@@ -106,6 +107,12 @@ CREATE INDEX i_username_timestamp USING BTREE ON archive(username(191), timestam
106107
CREATE INDEX i_username_peer USING BTREE ON archive(username(191), peer(191));
107108
CREATE INDEX i_username_bare_peer USING BTREE ON archive(username(191), bare_peer(191));
108109
CREATE INDEX i_timestamp USING BTREE ON archive(timestamp);
110+
CREATE INDEX i_archive_username_origin_id USING BTREE ON archive(username(191), origin_id(191));
111+
112+
-- To update 'archive' from ejabberd <= 23.10:
113+
-- ALTER TABLE archive ADD COLUMN origin_id text NOT NULL DEFAULT '';
114+
-- ALTER TABLE archive ALTER COLUMN origin_id DROP DEFAULT;
115+
-- CREATE INDEX i_archive_username_origin_id USING BTREE ON archive(username(191), origin_id(191));
109116

110117
CREATE TABLE archive_prefs (
111118
username varchar(191) NOT NULL PRIMARY KEY,

‎sql/pg.new.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -261,13 +261,20 @@ CREATE TABLE archive (
261261
id BIGSERIAL,
262262
kind text,
263263
nick text,
264+
origin_id text,
264265
created_at TIMESTAMP NOT NULL DEFAULT now()
265266
);
266267

267268
CREATE INDEX i_archive_sh_username_timestamp ON archive USING btree (server_host, username, timestamp);
268269
CREATE INDEX i_archive_sh_username_peer ON archive USING btree (server_host, username, peer);
269270
CREATE INDEX i_archive_sh_username_bare_peer ON archive USING btree (server_host, username, bare_peer);
270271
CREATE INDEX i_archive_sh_timestamp ON archive USING btree (server_host, timestamp);
272+
CREATE INDEX i_archive_sh_username_origin_id ON archive USING btree (server_host, username, origin_id);
273+
274+
-- To update 'archive' from ejabberd <= 23.10:
275+
-- ALTER TABLE archive ADD COLUMN origin_id text NOT NULL DEFAULT '';
276+
-- ALTER TABLE archive ALTER COLUMN origin_id DROP DEFAULT;
277+
-- CREATE INDEX i_archive_sh_username_origin_id ON archive USING btree (server_host, username, origin_id);
271278

272279
CREATE TABLE archive_prefs (
273280
username text NOT NULL,

‎sql/pg.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -98,13 +98,20 @@ CREATE TABLE archive (
9898
id BIGSERIAL,
9999
kind text,
100100
nick text,
101+
origin_id text,
101102
created_at TIMESTAMP NOT NULL DEFAULT now()
102103
);
103104

104105
CREATE INDEX i_username_timestamp ON archive USING btree (username, timestamp);
105106
CREATE INDEX i_username_peer ON archive USING btree (username, peer);
106107
CREATE INDEX i_username_bare_peer ON archive USING btree (username, bare_peer);
107108
CREATE INDEX i_timestamp ON archive USING btree (timestamp);
109+
CREATE INDEX i_archive_username_origin_id ON archive USING btree (username, origin_id);
110+
111+
-- To update 'archive' from ejabberd <= 23.10:
112+
-- ALTER TABLE archive ADD COLUMN origin_id text NOT NULL DEFAULT '';
113+
-- ALTER TABLE archive ALTER COLUMN origin_id DROP DEFAULT;
114+
-- CREATE INDEX i_archive_username_origin_id ON archive USING btree (username, origin_id);
108115

109116
CREATE TABLE archive_prefs (
110117
username text NOT NULL PRIMARY KEY,

0 commit comments

Comments
 (0)