diff options
| -rwxr-xr-x | bin/update-schema | 8 | ||||
| -rw-r--r-- | db/schema.sql | 12 | ||||
| -rw-r--r-- | db/schema_0032-moderation.sql | 4 | ||||
| -rw-r--r-- | db/schema_0036-add-underscore-to-cobrand.sql | 17 |
4 files changed, 35 insertions, 6 deletions
diff --git a/bin/update-schema b/bin/update-schema index dff2255d5..d40df1689 100755 --- a/bin/update-schema +++ b/bin/update-schema @@ -195,6 +195,7 @@ else { # By querying the database schema, we can see where we're currently at # (assuming schema change files are never half-applied, which should be the case) sub get_db_version { + return '0036' if constraint_contains('problem_cobrand_check', 'a-z0-9_'); return '0035' if column_exists('problem', 'bodies_missing'); return '0034' if ! function_exists('ms_current_timestamp'); return '0033' if ! function_exists('angle_between'); @@ -257,6 +258,13 @@ sub constraint_exists { return dbh()->selectrow_array('select count(*) from pg_constraint where conname = ?', {}, $constraint); } +# Returns true if a check constraint contains a certain string +sub constraint_contains { + my ( $constraint, $check ) = @_; + my ($consrc) = dbh()->selectrow_array('select consrc from pg_constraint where conname = ?', {}, $constraint); + return $consrc =~ /$check/; +} + # Returns true if a function exists sub function_exists { my $fn = shift; diff --git a/db/schema.sql b/db/schema.sql index ea2698b0e..2db89bddb 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -173,8 +173,8 @@ create table problem ( ), lang text not null default 'en-gb', service text not null default '', - cobrand text not null default '' check (cobrand ~* '^[a-z0-9]*$'), - cobrand_data text not null default '' check (cobrand_data ~* '^[a-z0-9]*$'), -- Extra data used in cobranded versions of the site + cobrand text not null default '' check (cobrand ~* '^[a-z0-9_]*$'), + cobrand_data text not null default '' check (cobrand_data ~* '^[a-z0-9_]*$'), -- Extra data used in cobranded versions of the site lastupdate timestamp not null default current_timestamp, whensent timestamp, send_questionnaire boolean not null default 't', @@ -297,9 +297,9 @@ create table comment ( or state = 'confirmed' or state = 'hidden' ), - cobrand text not null default '' check (cobrand ~* '^[a-z0-9]*$'), + cobrand text not null default '' check (cobrand ~* '^[a-z0-9_]*$'), lang text not null default 'en-gb', - cobrand_data text not null default '' check (cobrand_data ~* '^[a-z0-9]*$'), -- Extra data used in cobranded versions of the site + cobrand_data text not null default '' check (cobrand_data ~* '^[a-z0-9_]*$'), -- Extra data used in cobranded versions of the site mark_fixed boolean not null, mark_open boolean not null default 'f', problem_state text check ( @@ -366,8 +366,8 @@ create table alert ( user_id int references users(id) not null, confirmed integer not null default 0, lang text not null default 'en-gb', - cobrand text not null default '' check (cobrand ~* '^[a-z0-9]*$'), - cobrand_data text not null default '' check (cobrand_data ~* '^[a-z0-9]*$'), -- Extra data used in cobranded versions of the site + cobrand text not null default '' check (cobrand ~* '^[a-z0-9_]*$'), + cobrand_data text not null default '' check (cobrand_data ~* '^[a-z0-9_]*$'), -- Extra data used in cobranded versions of the site whensubscribed timestamp not null default current_timestamp, whendisabled timestamp default null ); diff --git a/db/schema_0032-moderation.sql b/db/schema_0032-moderation.sql index b3caded1e..79be3a4c9 100644 --- a/db/schema_0032-moderation.sql +++ b/db/schema_0032-moderation.sql @@ -1,6 +1,8 @@ -- was created in previous versions of this branch DROP TABLE IF EXISTS moderation_log; +BEGIN; + alter table admin_log add column user_id int references users(id) null; @@ -34,3 +36,5 @@ create table user_body_permissions ( ), unique(user_id, body_id, permission_type) ); + +COMMIT; diff --git a/db/schema_0036-add-underscore-to-cobrand.sql b/db/schema_0036-add-underscore-to-cobrand.sql new file mode 100644 index 000000000..7bc381e8e --- /dev/null +++ b/db/schema_0036-add-underscore-to-cobrand.sql @@ -0,0 +1,17 @@ +BEGIN; + +ALTER TABLE problem DROP CONSTRAINT problem_cobrand_check; +ALTER TABLE problem DROP CONSTRAINT problem_cobrand_data_check; +ALTER TABLE comment DROP CONSTRAINT comment_cobrand_check; +ALTER TABLE comment DROP CONSTRAINT comment_cobrand_data_check; +ALTER TABLE alert DROP CONSTRAINT alert_cobrand_check; +ALTER TABLE alert DROP CONSTRAINT alert_cobrand_data_check; + +ALTER TABLE problem ADD CONSTRAINT problem_cobrand_check CHECK (cobrand ~* '^[a-z0-9_]*$'); +ALTER TABLE problem ADD CONSTRAINT problem_cobrand_data_check CHECK (cobrand_data ~* '^[a-z0-9_]*$'); +ALTER TABLE comment ADD CONSTRAINT comment_cobrand_check CHECK (cobrand ~* '^[a-z0-9_]*$'); +ALTER TABLE comment ADD CONSTRAINT comment_cobrand_data_check CHECK (cobrand_data ~* '^[a-z0-9_]*$'); +ALTER TABLE alert ADD CONSTRAINT alert_cobrand_check CHECK (cobrand ~* '^[a-z0-9_]*$'); +ALTER TABLE alert ADD CONSTRAINT alert_cobrand_data_check CHECK (cobrand_data ~* '^[a-z0-9_]*$'); + +COMMIT; |