aboutsummaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rw-r--r--sql/lite.sql34
-rw-r--r--sql/mssql.sql36
-rw-r--r--sql/mysql.sql32
-rw-r--r--sql/pg.sql34
4 files changed, 68 insertions, 68 deletions
diff --git a/sql/lite.sql b/sql/lite.sql
index 2a52c0124..3e9231768 100644
--- a/sql/lite.sql
+++ b/sql/lite.sql
@@ -41,7 +41,7 @@ CREATE TABLE rosterusers (
ask character(1) NOT NULL,
askmessage text NOT NULL,
server character(1) NOT NULL,
- subscribe text,
+ subscribe text NOT NULL,
type text,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
@@ -206,10 +206,10 @@ CREATE TABLE roster_version (
);
CREATE TABLE pubsub_node (
- host text,
- node text,
- parent text,
- type text,
+ host text NOT NULL,
+ node text NOT NULL,
+ parent text NOT NULL DEFAULT '',
+ type text NOT NULL,
nodeid INTEGER PRIMARY KEY AUTOINCREMENT
);
CREATE INDEX i_pubsub_node_parent ON pubsub_node (parent);
@@ -217,22 +217,22 @@ CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node (host, node);
CREATE TABLE pubsub_node_option (
nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
- name text,
- val text
+ name text NOT NULL,
+ val text NOT NULL
);
CREATE INDEX i_pubsub_node_option_nodeid ON pubsub_node_option (nodeid);
CREATE TABLE pubsub_node_owner (
nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
- owner text
+ owner text NOT NULL
);
CREATE INDEX i_pubsub_node_owner_nodeid ON pubsub_node_owner (nodeid);
CREATE TABLE pubsub_state (
nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
- jid text,
+ jid text NOT NULL,
affiliation character(1),
- subscriptions text,
+ subscriptions text NOT NULL DEFAULT '',
stateid INTEGER PRIMARY KEY AUTOINCREMENT
);
CREATE INDEX i_pubsub_state_jid ON pubsub_state (jid);
@@ -240,19 +240,19 @@ CREATE UNIQUE INDEX i_pubsub_state_tuple ON pubsub_state (nodeid, jid);
CREATE TABLE pubsub_item (
nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
- itemid text,
- publisher text,
- creation text,
- modification text,
- payload text
+ itemid text NOT NULL,
+ publisher text NOT NULL,
+ creation text NOT NULL,
+ modification text NOT NULL,
+ payload text NOT NULL DEFAULT ''
);
CREATE INDEX i_pubsub_item_itemid ON pubsub_item (itemid);
CREATE UNIQUE INDEX i_pubsub_item_tuple ON pubsub_item (nodeid, itemid);
CREATE TABLE pubsub_subscription_opt (
- subid text,
+ subid text NOT NULL,
opt_name varchar(32),
- opt_value text
+ opt_value text NOT NULL
);
CREATE UNIQUE INDEX i_pubsub_subscription_opt ON pubsub_subscription_opt (subid, opt_name);
diff --git a/sql/mssql.sql b/sql/mssql.sql
index 7925fd92d..a3b814e02 100644
--- a/sql/mssql.sql
+++ b/sql/mssql.sql
@@ -182,11 +182,11 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW
CREATE TABLE [dbo].[pubsub_item] (
[nodeid] [bigint] NULL,
- [itemid] [varchar] (255) NULL,
- [publisher] [text] NULL,
- [creation] [text] NULL,
- [modification] [varchar] (255) NULL,
- [payload] [text] NULL
+ [itemid] [varchar] (255) NOT NULL,
+ [publisher] [text] NOT NULL,
+ [creation] [text] NOT NULL,
+ [modification] [varchar] (255) NOT NULL,
+ [payload] [text] NOT NULL DEFAULT ''
) TEXTIMAGE_ON [PRIMARY];
CREATE INDEX [pubsub_item_itemid] ON [pubsub_item] (itemid)
@@ -197,8 +197,8 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW
CREATE TABLE [dbo].[pubsub_node_option] (
[nodeid] [bigint] NULL,
- [name] [text] NULL,
- [val] [text] NULL
+ [name] [text] NOT NULL,
+ [val] [text] NOT NULL
) TEXTIMAGE_ON [PRIMARY];
CREATE CLUSTERED INDEX [pubsub_node_option_nodeid] ON [pubsub_node_option] (nodeid)
@@ -206,7 +206,7 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW
CREATE TABLE [dbo].[pubsub_node_owner] (
[nodeid] [bigint] NULL,
- [owner] [text] NULL
+ [owner] [text] NOT NULL
) TEXTIMAGE_ON [PRIMARY];
CREATE CLUSTERED INDEX [pubsub_node_owner_nodeid] ON [pubsub_node_owner] (nodeid)
@@ -214,9 +214,9 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW
CREATE TABLE [dbo].[pubsub_state] (
[nodeid] [bigint] NULL,
- [jid] [varchar] (255) NULL,
- [affiliation] [char] (1) NULL,
- [subscriptions] [text] NULL,
+ [jid] [varchar] (255) NOT NULL,
+ [affiliation] [char] (1) NOT NULL,
+ [subscriptions] [text] NOT NULL DEFAULT '',
[stateid] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [pubsub_state_PRIMARY] PRIMARY KEY CLUSTERED
(
@@ -231,19 +231,19 @@ CREATE UNIQUE INDEX [pubsub_state_nodeid_jid] ON [pubsub_state] (nodeid, jid)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
CREATE TABLE [dbo].[pubsub_subscription_opt] (
- [subid] [varchar] (255) NULL,
- [opt_name] [varchar] (32) NULL,
- [opt_value] [text] NULL
+ [subid] [varchar] (255) NOT NULL,
+ [opt_name] [varchar] (32) NOT NULL,
+ [opt_value] [text] NOT NULL
) TEXTIMAGE_ON [PRIMARY];
CREATE UNIQUE CLUSTERED INDEX [pubsub_subscription_opt_subid_opt_name] ON [pubsub_subscription_opt] (subid, opt_name)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
CREATE TABLE [dbo].[pubsub_node] (
- [host] [varchar] (255) NULL,
- [node] [varchar] (255) NULL,
- [parent] [varchar] (255) NULL,
- [type] [text] NULL,
+ [host] [varchar] (255) NOT NULL,
+ [node] [varchar] (255) NOT NULL,
+ [parent] [varchar] (255) NOT NULL DEFAULT '',
+ [type] [text] NOT NULL,
[nodeid] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [pubsub_node_PRIMARY] PRIMARY KEY CLUSTERED
(
diff --git a/sql/mysql.sql b/sql/mysql.sql
index 84901ffd5..5339614c9 100644
--- a/sql/mysql.sql
+++ b/sql/mysql.sql
@@ -218,10 +218,10 @@ CREATE TABLE roster_version (
-- ALTER TABLE rosterusers ALTER COLUMN askmessage SET NOT NULL;
CREATE TABLE pubsub_node (
- host text,
- node text,
- parent text,
- type text,
+ host text NOT NULL,
+ node text NOT NULL,
+ parent text NOT NULL DEFAULT '',
+ type text NOT NULL,
nodeid bigint auto_increment primary key
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE INDEX i_pubsub_node_parent ON pubsub_node(parent(120));
@@ -229,24 +229,24 @@ CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node(host(20), node(120));
CREATE TABLE pubsub_node_option (
nodeid bigint,
- name text,
- val text
+ name text NOT NULL,
+ val text NOT NULL
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE INDEX i_pubsub_node_option_nodeid ON pubsub_node_option(nodeid);
ALTER TABLE `pubsub_node_option` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE;
CREATE TABLE pubsub_node_owner (
nodeid bigint,
- owner text
+ owner text NOT NULL
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE INDEX i_pubsub_node_owner_nodeid ON pubsub_node_owner(nodeid);
ALTER TABLE `pubsub_node_owner` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE;
CREATE TABLE pubsub_state (
nodeid bigint,
- jid text,
+ jid text NOT NULL,
affiliation character(1),
- subscriptions text,
+ subscriptions text NOT NULL DEFAULT '',
stateid bigint auto_increment primary key
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE INDEX i_pubsub_state_jid ON pubsub_state(jid(60));
@@ -255,20 +255,20 @@ ALTER TABLE `pubsub_state` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (
CREATE TABLE pubsub_item (
nodeid bigint,
- itemid text,
- publisher text,
- creation text,
- modification text,
- payload text
+ itemid text NOT NULL,
+ publisher text NOT NULL,
+ creation text NOT NULL,
+ modification text NOT NULL,
+ payload text NOT NULL DEFAULT ''
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE INDEX i_pubsub_item_itemid ON pubsub_item(itemid(36));
CREATE UNIQUE INDEX i_pubsub_item_tuple ON pubsub_item(nodeid, itemid(36));
ALTER TABLE `pubsub_item` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE;
CREATE TABLE pubsub_subscription_opt (
- subid text,
+ subid text NOT NULL,
opt_name varchar(32),
- opt_value text
+ opt_value text NOT NULL
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE UNIQUE INDEX i_pubsub_subscription_opt ON pubsub_subscription_opt(subid(32), opt_name(32));
diff --git a/sql/pg.sql b/sql/pg.sql
index 9fba94b30..2ce9d1379 100644
--- a/sql/pg.sql
+++ b/sql/pg.sql
@@ -45,7 +45,7 @@ CREATE TABLE rosterusers (
ask character(1) NOT NULL,
askmessage text NOT NULL,
server character(1) NOT NULL,
- subscribe text,
+ subscribe text NOT NULL,
"type" text,
created_at TIMESTAMP NOT NULL DEFAULT now()
);
@@ -224,10 +224,10 @@ CREATE TABLE roster_version (
-- ALTER TABLE rosterusers ALTER COLUMN askmessage SET NOT NULL;
CREATE TABLE pubsub_node (
- host text,
- node text,
- parent text,
- "type" text,
+ host text NOT NULL,
+ node text NOT NULL,
+ parent text NOT NULL DEFAULT '',
+ "type" text NOT NULL,
nodeid SERIAL UNIQUE
);
CREATE INDEX i_pubsub_node_parent ON pubsub_node USING btree (parent);
@@ -235,22 +235,22 @@ CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node USING btree (host, node);
CREATE TABLE pubsub_node_option (
nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
- name text,
- val text
+ name text NOT NULL,
+ val text NOT NULL
);
CREATE INDEX i_pubsub_node_option_nodeid ON pubsub_node_option USING btree (nodeid);
CREATE TABLE pubsub_node_owner (
nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
- owner text
+ owner text NOT NULL
);
CREATE INDEX i_pubsub_node_owner_nodeid ON pubsub_node_owner USING btree (nodeid);
CREATE TABLE pubsub_state (
nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
- jid text,
+ jid text NOT NULL,
affiliation character(1),
- subscriptions text,
+ subscriptions text NOT NULL DEFAULT '',
stateid SERIAL UNIQUE
);
CREATE INDEX i_pubsub_state_jid ON pubsub_state USING btree (jid);
@@ -258,19 +258,19 @@ CREATE UNIQUE INDEX i_pubsub_state_tuple ON pubsub_state USING btree (nodeid, ji
CREATE TABLE pubsub_item (
nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
- itemid text,
- publisher text,
- creation text,
- modification text,
- payload text
+ itemid text NOT NULL,
+ publisher text NOT NULL,
+ creation text NOT NULL,
+ modification text NOT NULL,
+ payload text NOT NULL DEFAULT ''
);
CREATE INDEX i_pubsub_item_itemid ON pubsub_item USING btree (itemid);
CREATE UNIQUE INDEX i_pubsub_item_tuple ON pubsub_item USING btree (nodeid, itemid);
CREATE TABLE pubsub_subscription_opt (
- subid text,
+ subid text NOT NULL,
opt_name varchar(32),
- opt_value text
+ opt_value text NOT NULL
);
CREATE UNIQUE INDEX i_pubsub_subscription_opt ON pubsub_subscription_opt USING btree (subid, opt_name);
generated by cgit v1.2.3 (git 2.25.1) at 2024-12-05 02:41:53 +0000