def with_schema(request, db, with_clean_db):
with db.begin():
# Create the piwheels structures from the create_*.sql script
for stmt in parse_statements(get_script()):
stmt = stmt.format(username=PIWHEELS_USER, dbname=PIWHEELS_TESTDB)
db.execute(text(stmt))
return 'schema'
def with_schema(request, db):
with db.begin():
# Wipe the public schema and re-create it with standard defaults
db.execute("DROP SCHEMA public CASCADE")
db.execute("CREATE SCHEMA public AUTHORIZATION postgres")
db.execute("GRANT CREATE ON SCHEMA public TO PUBLIC")
db.execute("GRANT USAGE ON SCHEMA public TO PUBLIC")
for stmt in parse_statements(get_script()):
stmt = stmt.format(username=PIWHEELS_USER)
db.execute(stmt)
return 'schema'
def test_parse_statements():
assert list(parse_statements('-- This is a comment\nDROP TABLE foo;')) == ['DROP TABLE foo;']
assert list(parse_statements("VALUES (-1, '- not a comment -')")) == ["VALUES (-1, '- not a comment -')"]
assert list(parse_statements('DROP TABLE bar;\nDROP TABLE foo\n')) == ['DROP TABLE bar;', 'DROP TABLE foo']
assert list(parse_statements("VALUES (';');")) == ["VALUES (';');"]
assert list(parse_statements('DROP TABLE "little;bobby;tables";')) == ['DROP TABLE "little;bobby;tables";']
fn = """
CREATE FUNCTION foo(i integer) RETURNS text
LANGUAGE SQL
AS $sql$
VALUES ('foo');
$sql$;"""
assert list(parse_statements(fn)) == [fn.strip()]
def test_full_upgrade(db, with_clean_db, db_super_url, caplog,
create_script_04):
# The following is the creation script from the ancient 0.4 version; this
# is deliberately picked so we run through all subsequent update scripts
# testing they all apply cleanly ... albeit with an empty database which
# won't test the harder bits
with db.begin():
for statement in parse_statements(create_script_04):
db.execute(statement)
assert main(['--dsn', db_super_url, '--user', PIWHEELS_USER, '--yes']) == 0
with db.begin():
for row in db.execute("SELECT version FROM configuration"):
assert row[0] == __version__
break
else:
assert False, "Didn't find version row in configuration"
assert find_message(caplog.records,
message='Upgrading database to '
'version %s' % __version__)
def test_full_upgrade(db, with_clean_db, db_super_url, caplog):
# The following is the creation script from the ancient 0.4 version; this
# is deliberately picked so we run through all subsequent update scripts
# testing they all apply cleanly
create_04 = """
CREATE TABLE packages (
package VARCHAR(200) NOT NULL,
skip BOOLEAN DEFAULT false NOT NULL,
CONSTRAINT packages_pk PRIMARY KEY (package)
);
GRANT SELECT,INSERT,UPDATE,DELETE ON packages TO piwheels;
CREATE INDEX packages_skip ON packages(skip);
CREATE TABLE versions (
package VARCHAR(200) NOT NULL,
version VARCHAR(200) NOT NULL,
skip BOOLEAN DEFAULT false NOT NULL,
CONSTRAINT versions_pk PRIMARY KEY (package, version),
CONSTRAINT versions_package_fk FOREIGN KEY (package)
REFERENCES packages ON DELETE RESTRICT
);
GRANT SELECT,INSERT,UPDATE,DELETE ON versions TO piwheels;
CREATE INDEX versions_skip ON versions(skip);
CREATE TABLE builds (
build_id SERIAL NOT NULL,
package VARCHAR(200) NOT NULL,
version VARCHAR(200) NOT NULL,
built_by INTEGER NOT NULL,
built_at TIMESTAMP NOT NULL DEFAULT (NOW() AT TIME ZONE 'UTC'),
duration INTERVAL NOT NULL,
status BOOLEAN DEFAULT true NOT NULL,
output TEXT NOT NULL,
CONSTRAINT builds_pk PRIMARY KEY (build_id),
CONSTRAINT builds_unique UNIQUE (package, version, built_at, built_by),
CONSTRAINT builds_versions_fk FOREIGN KEY (package, version)
REFERENCES versions ON DELETE CASCADE,
CONSTRAINT builds_built_by_ck CHECK (built_by >= 1)
);
GRANT SELECT,INSERT,UPDATE,DELETE ON builds TO piwheels;
CREATE INDEX builds_timestamp ON builds(built_at DESC NULLS LAST);
CREATE INDEX builds_pkgver ON builds(package, version);
CREATE TABLE files (
filename VARCHAR(255) NOT NULL,
build_id INTEGER NOT NULL,
filesize INTEGER NOT NULL,
filehash CHAR(64) NOT NULL,
package_version_tag VARCHAR(100) NOT NULL,
py_version_tag VARCHAR(100) NOT NULL,
abi_tag VARCHAR(100) NOT NULL,
platform_tag VARCHAR(100) NOT NULL,
CONSTRAINT files_pk PRIMARY KEY (filename),
CONSTRAINT files_builds_fk FOREIGN KEY (build_id)
REFERENCES builds (build_id) ON DELETE CASCADE
);
GRANT SELECT,INSERT,UPDATE,DELETE ON files TO piwheels;
CREATE UNIQUE INDEX files_pkgver ON files(build_id);
CREATE INDEX files_size ON files(filesize);
"""
with db.begin():
for statement in parse_statements(create_04):
db.execute(statement)
assert main(['--dsn', db_super_url, '--user', PIWHEELS_USER, '--yes']) == 0
with db.begin():
for row in db.execute("SELECT version FROM configuration"):
assert row[0] == __version__
break
else:
assert False, "Didn't find version row in configuration"
assert find_message(caplog.records,
'Upgrading database to version %s' % __version__)
def test_upgraded_structure(db, with_schema, db_super_url, create_script_04):
# The following checks that the resulting database structure from a full
# upgrade (see above) matches that of a clean creation
def db_structure():
with db.begin():
return {
'relations':
list(
db.execute("""\
SELECT
cls.relname,
cls.relkind,
CASE WHEN cls.relkind IN ('v', 'm')
THEN pg_get_viewdef(cls.oid)
ELSE ''
END AS reldef,
obj_description(cls.oid, 'pg_class') AS relcomment
FROM
pg_catalog.pg_class cls
JOIN pg_catalog.pg_namespace nsp
ON cls.relnamespace = nsp.oid
WHERE
nsp.nspname = 'public'
ORDER BY
relname;
""")),
'columns':
list(
db.execute("""\
SELECT
attrelid::regclass::name AS attrelname,
ROW_NUMBER() OVER (PARTITION BY attrelid ORDER BY attnum) AS attnum,
attname,
atttypid::regtype::name AS atttype,
atttypmod,
attnotnull,
pg_get_expr(ad.adbin, cls.oid) AS attdefault,
col_description(cls.oid, att.attnum) AS attcomment
FROM
pg_catalog.pg_attribute att
JOIN pg_catalog.pg_class cls
ON att.attrelid = cls.oid
JOIN pg_catalog.pg_namespace nsp
ON cls.relnamespace = nsp.oid
LEFT JOIN pg_catalog.pg_attrdef ad
ON att.attrelid = ad.adrelid AND att.attnum = ad.adnum
WHERE
nsp.nspname = 'public'
AND att.attnum > 0
AND NOT att.attisdropped
ORDER BY
attrelname, attnum
""")),
'constraints':
list(
db.execute("""\
SELECT
con.conname,
pg_get_constraintdef(con.oid) AS condef,
obj_description(con.oid, 'pg_constraint') AS concomment
FROM
pg_catalog.pg_constraint con
JOIN pg_catalog.pg_namespace nsp
ON con.connamespace = nsp.oid
WHERE
nsp.nspname = 'public'
ORDER BY
conname;
""")),
'functions':
list(
db.execute("""\
SELECT
pro.proname,
pg_get_function_arguments(pro.oid) AS proargs,
pg_get_functiondef(pro.oid) AS prodef,
obj_description(pro.oid, 'pg_proc') AS procomment
FROM
pg_catalog.pg_proc pro
JOIN pg_catalog.pg_namespace nsp
ON pro.pronamespace = nsp.oid
WHERE
nsp.nspname = 'public'
ORDER BY
proname, proargs;
""")),
'triggers':
list(
db.execute("""\
SELECT
tg.tgname,
tg.tgrelid::regclass::name AS tgrelname,
tg.tgtype,
tg.tgfoid::regproc::name AS tgprocname,
obj_description(tg.oid, 'pg_trigger') AS tgcomment
FROM
pg_catalog.pg_trigger tg
JOIN pg_catalog.pg_class cls
ON tg.tgrelid = cls.oid
JOIN pg_catalog.pg_namespace nsp
ON cls.relnamespace = nsp.oid
WHERE
nsp.nspname = 'public'
AND NOT tg.tgisinternal
ORDER BY
tgrelname, tgname;
""")),
'privileges':
list(
db.execute("""\
WITH users(name) AS (
VALUES ('{user}'), ('{superuser}'), ('public')
),
schemas(name) AS (
VALUES ('public')
),
tables AS (
SELECT t.table_schema || '.' || t.table_name AS name
FROM information_schema.tables t
JOIN schemas s ON s.name = t.table_schema
WHERE table_type IN ('BASE TABLE', 'VIEW')
),
db_privs(name) AS (
VALUES ('CREATE'), ('CONNECT'), ('TEMPORARY')
),
schema_privs(name) AS (
VALUES ('CREATE'), ('USAGE')
),
table_privs(name) AS (
VALUES ('SELECT'), ('INSERT'), ('UPDATE'), ('DELETE'),
('TRUNCATE'), ('REFERENCES'), ('TRIGGER')
)
SELECT
'database' AS obj_type,
current_database() AS obj_id,
dp.name AS privilege,
u.name AS username
FROM users u, db_privs dp
WHERE has_database_privilege(u.name, current_database(), dp.name)
UNION ALL
SELECT
'schema' AS obj_type,
sc.name AS obj_id,
sp.name AS privilege,
u.name AS username
FROM users u, schemas sc, schema_privs sp
WHERE has_schema_privilege(u.name, sc.name, sp.name)
UNION ALL
SELECT
'table' AS obj_type,
tb.name AS obj_id,
tp.name AS privilege,
u.name AS username
FROM users u, tables tb, table_privs tp
WHERE has_table_privilege(u.name, tb.name, tp.name)
ORDER BY obj_type, obj_id, privilege, username
""".format(user=PIWHEELS_USER,
superuser=PIWHEELS_SUPERUSER))),
}
permissions = []
return relation_defs, column_defs, constraint_defs, function_defs, trigger_defs
create_structure = db_structure()
with db.begin():
# Wipe the public schema and re-create it with standard defaults
db.execute("DROP SCHEMA public CASCADE")
db.execute("CREATE SCHEMA public AUTHORIZATION postgres")
db.execute("GRANT CREATE ON SCHEMA public TO PUBLIC")
db.execute("GRANT USAGE ON SCHEMA public TO PUBLIC")
with db.begin():
for statement in parse_statements(create_script_04):
db.execute(statement)
assert main(['--dsn', db_super_url, '--user', PIWHEELS_USER, '--yes']) == 0
upgrade_structure = db_structure()
assert create_structure == upgrade_structure
