Add missing NOT NULL restrictions

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);