sql: Remove PostgreSQL text limits, fixes hangs

Modify all PostgreSQL text column limits to the unlimited TEXT type
instead of VARCHAR.  Modern PostgreSQL versions (year 2000 onward) do
not store text any differently depending on whether or not it's TEXT
or VARCHAR.
https://www.postgresql.org/message-id/20667.971213689%40sss.pgh.pa.us

This avoids having the core fail to save values or hang entirely, and
also addresses migration issues from SQLite where text limits aren't
applied.

See https://bugs.quassel-irc.org/issues/1526

This shouldn't introduce any noticeable performance impact:
https://stackoverflow.com/questions/1067061/does-a-varchar-fields-declared-size-have-any-impact-in-postgresql

Fix up a spurious length-limited field for SQLite in case length
limits are applied in newer SQLite versions.

The proper long-term fix is to implement length checks throughout the
Quassel core (sanitizing data) and client (to warn of length issues),
rather than relying on the database to enforce limits.

Sponsored by Postgres Phone Plans.  UNLIMITED TEXT, TALK, and DATA for
$9.99 a month!  Some restrictions apply.  This is not serious.

[Commit squashed from "jcolson", thanks!]
See https://github.com/quassel/quassel/pull/478

Fixes GH-478
Fixes #1526

diff --git a/src/core/SQL/PostgreSQL/setup_000_quasseluser.sql b/src/core/SQL/PostgreSQL/setup_000_quasseluser.sql
index 57abcea..0d1ee72 100644 (file)
--- a/src/core/SQL/PostgreSQL/setup_000_quasseluser.sql
+++ b/src/core/SQL/PostgreSQL/setup_000_quasseluser.sql
@@ -1,7 +1,7 @@
 CREATE TABLE quasseluser (
        userid serial NOT NULL PRIMARY KEY,
-       username varchar(64) UNIQUE NOT NULL,
+       username TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        hashversion integer NOT NULL DEFAULT 0,
-       authenticator varchar(64) NOT NULL DEFAULT 'Database'
+       authenticator TEXT NOT NULL DEFAULT 'Database'
 )

diff --git a/src/core/SQL/PostgreSQL/setup_010_sender.sql b/src/core/SQL/PostgreSQL/setup_010_sender.sql
index 87c8366..5f6b4b6 100644 (file)
--- a/src/core/SQL/PostgreSQL/setup_010_sender.sql
+++ b/src/core/SQL/PostgreSQL/setup_010_sender.sql
@@ -1,6 +1,6 @@
 CREATE TABLE sender ( -- THE SENDER OF IRC MESSAGES
        senderid bigserial NOT NULL PRIMARY KEY,
-       sender varchar(128) NOT NULL,
+       sender TEXT NOT NULL,
        realname TEXT,
        avatarurl TEXT
 );

diff --git a/src/core/SQL/PostgreSQL/setup_020_identity.sql b/src/core/SQL/PostgreSQL/setup_020_identity.sql
index 35ffe72..d60eb1a 100644 (file)
--- a/src/core/SQL/PostgreSQL/setup_020_identity.sql
+++ b/src/core/SQL/PostgreSQL/setup_020_identity.sql
@@ -1,23 +1,23 @@
 CREATE TABLE identity (
        identityid serial PRIMARY KEY,
        userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
-       identityname varchar(64) NOT NULL,
-       realname varchar(128) NOT NULL,
-       awaynick varchar(64),
+       identityname TEXT NOT NULL,
+       realname TEXT NOT NULL,
+       awaynick TEXT,
        awaynickenabled boolean NOT NULL DEFAULT FALSE,
-       awayreason varchar(256),
+       awayreason TEXT,
        awayreasonenabled boolean NOT NULL DEFAULT FALSE,
        autoawayenabled boolean NOT NULL DEFAULT FALSE,
        autoawaytime integer NOT NULL,
-       autoawayreason varchar(256),
+       autoawayreason TEXT,
        autoawayreasonenabled boolean NOT NULL DEFAULT FALSE,
        detachawayenabled boolean NOT NULL DEFAULT FALSE,
-       detachawayreason varchar(256),
+       detachawayreason TEXT,
        detachawayreasonenabled boolean NOT NULL DEFAULT FALSE,
-       ident varchar(64),
-       kickreason varchar(256),
-       partreason varchar(256),
-       quitreason varchar(256),
+       ident TEXT,
+       kickreason TEXT,
+       partreason TEXT,
+       quitreason TEXT,
        sslcert bytea,
        sslkey bytea,
        UNIQUE (userid, identityname)

diff --git a/src/core/SQL/PostgreSQL/setup_030_identity_nick.sql b/src/core/SQL/PostgreSQL/setup_030_identity_nick.sql
index 65fb5ec..ce2bb6a 100644 (file)
--- a/src/core/SQL/PostgreSQL/setup_030_identity_nick.sql
+++ b/src/core/SQL/PostgreSQL/setup_030_identity_nick.sql
@@ -1,6 +1,6 @@
 CREATE TABLE identity_nick (
        nickid serial PRIMARY KEY,
        identityid integer NOT NULL REFERENCES identity (identityid) ON DELETE CASCADE,
-       nick varchar(64) NOT NULL,
+       nick TEXT NOT NULL,
        UNIQUE (identityid, nick)
 )

diff --git a/src/core/SQL/PostgreSQL/setup_040_network.sql b/src/core/SQL/PostgreSQL/setup_040_network.sql
index bc6815b..f1f3dbf 100644 (file)
--- a/src/core/SQL/PostgreSQL/setup_040_network.sql
+++ b/src/core/SQL/PostgreSQL/setup_040_network.sql
@@ -1,27 +1,27 @@
 CREATE TABLE network (
        networkid serial NOT NULL PRIMARY KEY,
        userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
-       networkname varchar(32) NOT NULL,
+       networkname TEXT NOT NULL,
        identityid integer REFERENCES identity (identityid) ON DELETE SET NULL,
-       encodingcodec varchar(32) NOT NULL DEFAULT 'ISO-8859-15',
-       decodingcodec varchar(32) NOT NULL DEFAULT 'ISO-8859-15',
-       servercodec varchar(32),
+       encodingcodec TEXT NOT NULL DEFAULT 'ISO-8859-15',
+       decodingcodec TEXT NOT NULL DEFAULT 'ISO-8859-15',
+       servercodec TEXT,
        userandomserver boolean NOT NULL DEFAULT FALSE,
        perform TEXT,
        useautoidentify boolean NOT NULL DEFAULT FALSE,
-       autoidentifyservice varchar(128),
-       autoidentifypassword varchar(128),
+       autoidentifyservice TEXT,
+       autoidentifypassword TEXT,
        usesasl boolean NOT NULL DEFAULT FALSE,
-       saslaccount varchar(128),
-       saslpassword varchar(128),
+       saslaccount TEXT,
+       saslpassword TEXT,
        useautoreconnect boolean NOT NULL DEFAULT TRUE,
        autoreconnectinterval integer NOT NULL DEFAULT 0,
        autoreconnectretries integer NOT NULL DEFAULT 0,
        unlimitedconnectretries boolean NOT NULL DEFAULT FALSE,
        rejoinchannels boolean NOT NULL DEFAULT FALSE,
        connected boolean NOT NULL DEFAULT FALSE,
-       usermode varchar(32), -- user mode to restore
-       awaymessage varchar(256), -- away message to restore (empty if not away)
+       usermode TEXT, -- user mode to restore
+       awaymessage TEXT, -- away message to restore (empty if not away)
        attachperform text, -- perform list for on attach
        detachperform text, -- perform list for on detach
        usecustomessagerate boolean NOT NULL DEFAULT FALSE,  -- Custom rate limiting

diff --git a/src/core/SQL/PostgreSQL/setup_050_buffer.sql b/src/core/SQL/PostgreSQL/setup_050_buffer.sql
index 0487dd3..ac1d0b2 100644 (file)
--- a/src/core/SQL/PostgreSQL/setup_050_buffer.sql
+++ b/src/core/SQL/PostgreSQL/setup_050_buffer.sql
@@ -3,15 +3,15 @@ create TABLE buffer (
        userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
        groupid integer,
        networkid integer NOT NULL REFERENCES network (networkid) ON DELETE CASCADE,
-       buffername varchar(128) NOT NULL,
-       buffercname varchar(128) NOT NULL, -- CANONICAL BUFFER NAME (lowercase version)
+       buffername TEXT NOT NULL,
+       buffercname TEXT NOT NULL, -- CANONICAL BUFFER NAME (lowercase version)
        buffertype integer NOT NULL DEFAULT 0,
        lastmsgid bigint NOT NULL DEFAULT 0,
        lastseenmsgid bigint NOT NULL DEFAULT 0,
        markerlinemsgid bigint NOT NULL DEFAULT 0,
        bufferactivity integer NOT NULL DEFAULT 0,
        highlightcount integer NOT NULL DEFAULT 0,
-       key varchar(128),
+       key TEXT,
        joined boolean NOT NULL DEFAULT FALSE, -- BOOL
        cipher TEXT,
        UNIQUE(userid, networkid, buffercname),

diff --git a/src/core/SQL/PostgreSQL/setup_080_ircservers.sql b/src/core/SQL/PostgreSQL/setup_080_ircservers.sql
index 98609d5..5d038f1 100644 (file)
--- a/src/core/SQL/PostgreSQL/setup_080_ircservers.sql
+++ b/src/core/SQL/PostgreSQL/setup_080_ircservers.sql
@@ -2,16 +2,16 @@ CREATE TABLE ircserver (
     serverid serial PRIMARY KEY,
     userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
     networkid integer NOT NULL REFERENCES network (networkid) ON DELETE CASCADE,
-    hostname varchar(128) NOT NULL,
+    hostname TEXT NOT NULL,
     port integer NOT NULL DEFAULT 6667,
-    password varchar(64),
+    password TEXT,
     ssl boolean NOT NULL DEFAULT FALSE, -- bool
     sslversion integer NOT NULL DEFAULT 0,
     useproxy boolean NOT NULL DEFAULT FALSE, -- bool
     proxytype integer NOT NULL DEFAULT 0,
-    proxyhost varchar(128) NOT NULL DEFAULT 'localhost',
+    proxyhost TEXT NOT NULL DEFAULT 'localhost',
     proxyport integer NOT NULL DEFAULT 8080,
-    proxyuser varchar(64),
-    proxypass varchar(64),
+    proxyuser TEXT,
+    proxypass TEXT,
     sslverify boolean NOT NULL DEFAULT FALSE -- bool, validate SSL cert
 )

diff --git a/src/core/SQL/PostgreSQL/version/30/upgrade_000_alter_quasseluser.sql b/src/core/SQL/PostgreSQL/version/30/upgrade_000_alter_quasseluser.sql
new file mode 100644 (file)
index 0000000..7c38bb7
--- /dev/null
+++ b/src/core/SQL/PostgreSQL/version/30/upgrade_000_alter_quasseluser.sql
@@ -0,0 +1,3 @@
+ALTER TABLE quasseluser
+ALTER COLUMN username TYPE TEXT,
+ALTER COLUMN authenticator TYPE TEXT

diff --git a/src/core/SQL/PostgreSQL/version/30/upgrade_010_alter_sender.sql b/src/core/SQL/PostgreSQL/version/30/upgrade_010_alter_sender.sql
new file mode 100644 (file)
index 0000000..be0be4c
--- /dev/null
+++ b/src/core/SQL/PostgreSQL/version/30/upgrade_010_alter_sender.sql
@@ -0,0 +1,2 @@
+ALTER TABLE sender
+ALTER COLUMN sender TYPE TEXT

diff --git a/src/core/SQL/PostgreSQL/version/30/upgrade_020_alter_identity.sql b/src/core/SQL/PostgreSQL/version/30/upgrade_020_alter_identity.sql
new file mode 100644 (file)
index 0000000..a8708b0
--- /dev/null
+++ b/src/core/SQL/PostgreSQL/version/30/upgrade_020_alter_identity.sql
@@ -0,0 +1,11 @@
+ALTER TABLE identity
+ALTER COLUMN identityname TYPE TEXT,
+ALTER COLUMN realname TYPE TEXT,
+ALTER COLUMN awaynick TYPE TEXT,
+ALTER COLUMN awayreason TYPE TEXT,
+ALTER COLUMN autoawayreason TYPE TEXT,
+ALTER COLUMN detachawayreason TYPE TEXT,
+ALTER COLUMN ident TYPE TEXT,
+ALTER COLUMN kickreason TYPE TEXT,
+ALTER COLUMN partreason TYPE TEXT,
+ALTER COLUMN quitreason TYPE TEXT

diff --git a/src/core/SQL/PostgreSQL/version/30/upgrade_030_alter_identity_nick.sql b/src/core/SQL/PostgreSQL/version/30/upgrade_030_alter_identity_nick.sql
new file mode 100644 (file)
index 0000000..fa4aa91
--- /dev/null
+++ b/src/core/SQL/PostgreSQL/version/30/upgrade_030_alter_identity_nick.sql
@@ -0,0 +1,2 @@
+ALTER TABLE identity_nick
+ALTER COLUMN nick TYPE TEXT

diff --git a/src/core/SQL/PostgreSQL/version/30/upgrade_040_alter_network.sql b/src/core/SQL/PostgreSQL/version/30/upgrade_040_alter_network.sql
new file mode 100644 (file)
index 0000000..1ce05d5
--- /dev/null
+++ b/src/core/SQL/PostgreSQL/version/30/upgrade_040_alter_network.sql
@@ -0,0 +1,11 @@
+ALTER TABLE network
+ALTER COLUMN networkname TYPE TEXT,
+ALTER COLUMN encodingcodec TYPE TEXT,
+ALTER COLUMN decodingcodec TYPE TEXT,
+ALTER COLUMN servercodec TYPE TEXT,
+ALTER COLUMN autoidentifyservice TYPE TEXT,
+ALTER COLUMN autoidentifypassword TYPE TEXT,
+ALTER COLUMN saslaccount TYPE TEXT,
+ALTER COLUMN saslpassword TYPE TEXT,
+ALTER COLUMN usermode TYPE TEXT,
+ALTER COLUMN awaymessage TYPE TEXT

diff --git a/src/core/SQL/PostgreSQL/version/30/upgrade_050_alter_buffer.sql b/src/core/SQL/PostgreSQL/version/30/upgrade_050_alter_buffer.sql
new file mode 100644 (file)
index 0000000..bf21908
--- /dev/null
+++ b/src/core/SQL/PostgreSQL/version/30/upgrade_050_alter_buffer.sql
@@ -0,0 +1,4 @@
+ALTER TABLE buffer
+ALTER COLUMN buffername TYPE TEXT,
+ALTER COLUMN buffercname TYPE TEXT,
+ALTER COLUMN key TYPE TEXT

diff --git a/src/core/SQL/PostgreSQL/version/30/upgrade_080_alter_ircservers.sql b/src/core/SQL/PostgreSQL/version/30/upgrade_080_alter_ircservers.sql
new file mode 100644 (file)
index 0000000..cac058c
--- /dev/null
+++ b/src/core/SQL/PostgreSQL/version/30/upgrade_080_alter_ircservers.sql
@@ -0,0 +1,6 @@
+ALTER TABLE ircserver
+ALTER COLUMN password TYPE TEXT,
+ALTER COLUMN hostname TYPE TEXT,
+ALTER COLUMN proxyhost TYPE TEXT,
+ALTER COLUMN proxyuser TYPE TEXT,
+ALTER COLUMN proxypass TYPE TEXT

diff --git a/src/core/SQL/SQLite/setup_000_quasseluser.sql b/src/core/SQL/SQLite/setup_000_quasseluser.sql
index 0a325e2..441f8df 100644 (file)
--- a/src/core/SQL/SQLite/setup_000_quasseluser.sql
+++ b/src/core/SQL/SQLite/setup_000_quasseluser.sql
@@ -3,5 +3,5 @@ CREATE TABLE quasseluser (
        username TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        hashversion INTEGER NOT NULL DEFAULT 0,
-       authenticator varchar(64) NOT NULL DEFAULT "Database"
+       authenticator TEXT NOT NULL DEFAULT "Database"
 )