From 56df6e7ba83ee615296691696f638dfc89dd4da8 Mon Sep 17 00:00:00 2001 From: Christophe Romain Date: Wed, 22 Feb 2017 11:09:15 +0100 Subject: Add missing NOT NULL restrictions --- sql/lite.sql | 34 +++++++++++++++++----------------- sql/mssql.sql | 36 ++++++++++++++++++------------------ sql/mysql.sql | 32 ++++++++++++++++---------------- sql/pg.sql | 34 +++++++++++++++++----------------- 4 files changed, 68 insertions(+), 68 deletions(-) (limited to 'sql') 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); -- cgit v1.2.3