def test_help(capsys): with pytest.raises(SystemExit): main(['--help']) out, err = capsys.readouterr() assert out.startswith('usage:') assert '--dsn' in out assert '--user' in out
def test_init_debug(db, with_clean_db, db_super_url, caplog): assert main( ['--dsn', db_super_url, '--user', PIWHEELS_USER, '--yes', '--debug']) == 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='Initializing database at version %s' % __version__) assert find_message( caplog.records, message='REVOKE ALL PRIVILEGES ON SCHEMA public FROM PUBLIC;') assert find_message( caplog.records, message='REVOKE ALL PRIVILEGES ON SCHEMA public FROM PUBLIC;') assert find_message( caplog.records, message= 'CREATE INDEX builds_timestamp ON builds(built_at DESC NULLS LAST);') assert find_message( caplog.records, message='CREATE INDEX files_builds ON files(build_id);')
def test_init(db, with_clean_db, db_super_url, caplog): 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, 'Initializing database at version %s' % __version__)
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_no_path(db, with_schema, db_super_url, caplog): with mock.patch('piwheels.initdb.__version__', 'foo'): with pytest.raises(RuntimeError) as exc: main(['--dsn', db_super_url, '--user', PIWHEELS_USER, '--yes']) assert 'Unable to find upgrade path' in str(exc)
def test_current_version(db, with_schema, db_super_url, caplog): with mock.patch('piwheels.terminal.yes_no_prompt') as prompt_mock: prompt_mock.return_value = False assert main(['--dsn', db_super_url, '--user', PIWHEELS_USER]) == 0 assert find_message(caplog.records, 'Database is the current version')
def test_new_abort(db, with_clean_db, db_super_url, caplog): with mock.patch('piwheels.terminal.yes_no_prompt') as prompt_mock: prompt_mock.return_value = False assert main(['--dsn', db_super_url, '--user', PIWHEELS_USER]) == 0 assert find_message(caplog.records, 'Database appears to be uninitialized')
def test_user_is_superuser(db, with_clean_db, db_super_url): with pytest.raises(RuntimeError) as exc: # hopefully you don't have a user named this... main(['--dsn', db_super_url, '--user', PIWHEELS_SUPERUSER]) assert "is a cluster superuser; this is not recommended" in str(exc)
def test_bad_user(db, with_clean_db, db_super_url): with pytest.raises(RuntimeError) as exc: # hopefully you don't have a user named this... main(['--dsn', db_super_url, '--user', 'fdjskalfjdsklfdsjk']) assert "doesn't exist as a cluster user" in str(exc)
def test_not_a_superuser(db, with_clean_db, db_url): with pytest.raises(RuntimeError) as exc: main(['--dsn', db_url]) assert 'not a cluster superuser' in str(exc)
def test_bad_db(): with pytest.raises(RuntimeError) as exc: # hopefully you don't have a database named this... main(['--dsn', 'postgres:///djskalfjsqklfjdsklfjklsd']) assert 'does not exist' in str(exc)
def test_version(capsys): with pytest.raises(SystemExit): main(['--version']) out, err = capsys.readouterr() assert out.strip() == __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