# HG changeset patch # User Sylvain Thénault # Date 1467286936 -7200 # Node ID c172fa18565e4a7782caa92547781333cd964fb4 # Parent ac166217bd8cf118ebfb0fed92db4e80c626655c [schema2sql] Avoid "parsing" SQL statements for database initialization A big SQL string was generated, then splitted. This caused bug if some value in the schema (eg vocabulary, default) contained the separator (';'). To properly fix this, yield each individual statement instead of generating a string. Closes #14050899 diff -r ac166217bd8c -r c172fa18565e cubicweb/hooks/syncschema.py --- a/cubicweb/hooks/syncschema.py Thu Jun 30 11:18:01 2016 +0200 +++ b/cubicweb/hooks/syncschema.py Thu Jun 30 13:42:16 2016 +0200 @@ -251,11 +251,9 @@ description=entity.description) eschema = schema.add_entity_type(etype) # create the necessary table - tablesql = y2sql.eschema2sql(cnx.repo.system_source.dbhelper, - eschema, prefix=SQL_PREFIX) - for sql in tablesql.split(';'): - if sql.strip(): - cnx.system_sql(sql) + for sql in y2sql.eschema2sql(cnx.repo.system_source.dbhelper, + eschema, prefix=SQL_PREFIX): + cnx.system_sql(sql) # add meta relations gmap = group_mapping(cnx) cmap = ss.cstrtype_mapping(cnx) @@ -380,11 +378,9 @@ # need to create the relation if it has not been already done by # another event of the same transaction if not rschema.type in cnx.transaction_data.get('createdtables', ()): - tablesql = y2sql.rschema2sql(rschema) # create the necessary table - for sql in tablesql.split(';'): - if sql.strip(): - sqlexec(sql) + for sql in y2sql.rschema2sql(rschema): + sqlexec(sql) cnx.transaction_data.setdefault('createdtables', []).append( rschema.type) # copy existant data @@ -602,9 +598,8 @@ rtype in cnx.transaction_data.get('createdtables', ())): rschema = schema.rschema(rtype) # create the necessary table - for sql in y2sql.rschema2sql(rschema).split(';'): - if sql.strip(): - cnx.system_sql(sql) + for sql in y2sql.rschema2sql(rschema): + cnx.system_sql(sql) cnx.transaction_data.setdefault('createdtables', []).append( rtype) diff -r ac166217bd8c -r c172fa18565e cubicweb/server/__init__.py --- a/cubicweb/server/__init__.py Thu Jun 30 11:18:01 2016 +0200 +++ b/cubicweb/server/__init__.py Thu Jun 30 13:42:16 2016 +0200 @@ -1,4 +1,4 @@ -# copyright 2003-2014 LOGILAB S.A. (Paris, FRANCE), all rights reserved. +# copyright 2003-2016 LOGILAB S.A. (Paris, FRANCE), all rights reserved. # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr # # This file is part of CubicWeb. @@ -261,9 +261,7 @@ # they are used sometimes by generated sql. Keeping them empty is much # simpler than fixing this... schemasql = sqlschema(schema, driver) - #skip_entities=[str(e) for e in schema.entities() - # if not repo.system_source.support_entity(str(e))]) - failed = sqlexec(schemasql, execute, pbtitle=_title, delimiter=';;') + failed = sqlexec(schemasql, execute, pbtitle=_title) if failed: print('The following SQL statements failed. You should check your schema.') print(failed) diff -r ac166217bd8c -r c172fa18565e cubicweb/server/migractions.py --- a/cubicweb/server/migractions.py Thu Jun 30 11:18:01 2016 +0200 +++ b/cubicweb/server/migractions.py Thu Jun 30 13:42:16 2016 +0200 @@ -1531,11 +1531,9 @@ and a sql database """ dbhelper = self.repo.system_source.dbhelper - tablesql = eschema2sql(dbhelper, self.repo.schema.eschema(etype), - prefix=SQL_PREFIX) - for sql in tablesql.split(';'): - if sql.strip(): - self.sqlexec(sql) + for sql in eschema2sql(dbhelper, self.repo.schema.eschema(etype), + prefix=SQL_PREFIX): + self.sqlexec(sql) if commit: self.commit() @@ -1544,10 +1542,8 @@ This may be useful on accidental desync between the repository schema and a sql database """ - tablesql = rschema2sql(self.repo.schema.rschema(rtype)) - for sql in tablesql.split(';'): - if sql.strip(): - self.sqlexec(sql) + for sql in rschema2sql(self.repo.schema.rschema(rtype)): + self.sqlexec(sql) if commit: self.commit() diff -r ac166217bd8c -r c172fa18565e cubicweb/server/schema2sql.py --- a/cubicweb/server/schema2sql.py Thu Jun 30 11:18:01 2016 +0200 +++ b/cubicweb/server/schema2sql.py Thu Jun 30 13:42:16 2016 +0200 @@ -39,9 +39,9 @@ def sql_create_index(self, table, column, unique=False): idx = self._index_name(table, column, unique) if unique: - return 'ALTER TABLE %s ADD CONSTRAINT %s UNIQUE(%s);' % (table, idx, column) + return 'ALTER TABLE %s ADD CONSTRAINT %s UNIQUE(%s)' % (table, idx, column) else: - return 'CREATE INDEX %s ON %s(%s);' % (idx, table, column) + return 'CREATE INDEX %s ON %s(%s)' % (idx, table, column) @monkeypatch(database._GenericAdvFuncHelper) @@ -67,21 +67,21 @@ def schema2sql(dbhelper, schema, skip_entities=(), skip_relations=(), prefix=''): - """write to the output stream a SQL schema to store the objects - corresponding to the given schema + """Yield SQL statements to create a database schema for the given Yams schema. + + `prefix` may be a string that will be prepended to all table / column names (usually, 'cw_'). """ - output = [] - w = output.append for etype in sorted(schema.entities()): eschema = schema.eschema(etype) if eschema.final or eschema.type in skip_entities: continue - w(eschema2sql(dbhelper, eschema, skip_relations, prefix=prefix)) + for sql in eschema2sql(dbhelper, eschema, skip_relations, prefix): + yield sql for rtype in sorted(schema.relations()): rschema = schema.rschema(rtype) if rschema_has_table(rschema, skip_relations): - w(rschema2sql(rschema)) - return '\n'.join(output) + for sql in rschema2sql(rschema): + yield sql def unique_index_name(eschema, attrs): @@ -103,10 +103,10 @@ def eschema2sql(dbhelper, eschema, skip_relations=(), prefix=''): - """write an entity schema as SQL statements to stdout""" + """Yield SQL statements to initialize database from an entity schema.""" + table = prefix + eschema.type output = [] w = output.append - table = prefix + eschema.type w('CREATE TABLE %s(' % (table)) attrs = [attrdef for attrdef in eschema.attribute_definitions() if not attrdef[0].type in skip_relations] @@ -132,22 +132,21 @@ cstrname, check = check_constraint(rdef, constraint, dbhelper, prefix=prefix) if cstrname is not None: w(', CONSTRAINT %s CHECK(%s)' % (cstrname, check)) - w(');') + w(')') + yield '\n'.join(output) # create indexes for i in range(len(attrs)): rschema, attrschema = attrs[i] if attrschema is None or eschema.rdef(rschema).indexed: - w(dbhelper.sql_create_index(table, prefix + rschema.type)) + yield dbhelper.sql_create_index(table, prefix + rschema.type) if attrschema and any(isinstance(cstr, UniqueConstraint) for cstr in eschema.rdef(rschema).constraints): - w(dbhelper.sql_create_index(table, prefix + rschema.type, unique=True)) + yield dbhelper.sql_create_index(table, prefix + rschema.type, unique=True) for attrs, index_name in iter_unique_index_names(eschema): columns = ['%s%s' % (prefix, attr) for attr in attrs] sqls = dbhelper.sqls_create_multicol_unique_index(table, columns, index_name) for sql in sqls: - w(sql) - w('') - return '\n'.join(output) + yield sql.rstrip(';') # remove trailing ';' for consistency def constraint_value_as_sql(value, dbhelper, prefix): @@ -258,49 +257,54 @@ ); CREATE INDEX %(from_idx)s ON %(table)s(eid_from); -CREATE INDEX %(to_idx)s ON %(table)s(eid_to);""" +CREATE INDEX %(to_idx)s ON %(table)s(eid_to)""" def rschema2sql(rschema): + """Yield SQL statements to create database table and indexes for a Yams relation schema.""" assert not rschema.rule table = '%s_relation' % rschema.type - return _SQL_SCHEMA % {'table': table, + sqls = _SQL_SCHEMA % {'table': table, 'pkey_idx': build_index_name(table, ['eid_from', 'eid_to'], 'key_'), 'from_idx': build_index_name(table, ['eid_from'], 'idx_'), 'to_idx': build_index_name(table, ['eid_to'], 'idx_')} + for sql in sqls.split(';'): + yield sql.strip() def grant_schema(schema, user, set_owner=True, skip_entities=(), prefix=''): - """write to the output stream a SQL schema to store the objects - corresponding to the given schema + """Yield SQL statements to give all access (and ownership if `set_owner` is True) on the + database tables for the given Yams schema to `user`. + + `prefix` may be a string that will be prepended to all table / column names (usually, 'cw_'). """ - output = [] - w = output.append for etype in sorted(schema.entities()): eschema = schema.eschema(etype) if eschema.final or etype in skip_entities: continue - w(grant_eschema(eschema, user, set_owner, prefix=prefix)) + for sql in grant_eschema(eschema, user, set_owner, prefix=prefix): + yield sql for rtype in sorted(schema.relations()): rschema = schema.rschema(rtype) if rschema_has_table(rschema, skip_relations=()): # XXX skip_relations should be specified - w(grant_rschema(rschema, user, set_owner)) - return '\n'.join(output) + for sql in grant_rschema(rschema, user, set_owner): + yield sql def grant_eschema(eschema, user, set_owner=True, prefix=''): - output = [] - w = output.append + """Yield SQL statements to give all access (and ownership if `set_owner` is True) on the + database tables for the given Yams entity schema to `user`. + """ etype = eschema.type if set_owner: - w('ALTER TABLE %s%s OWNER TO %s;' % (prefix, etype, user)) - w('GRANT ALL ON %s%s TO %s;' % (prefix, etype, user)) - return '\n'.join(output) + yield 'ALTER TABLE %s%s OWNER TO %s' % (prefix, etype, user) + yield 'GRANT ALL ON %s%s TO %s' % (prefix, etype, user) def grant_rschema(rschema, user, set_owner=True): - output = [] + """Yield SQL statements to give all access (and ownership if `set_owner` is True) on the + database tables for the given Yams relation schema to `user`. + """ if set_owner: - output.append('ALTER TABLE %s_relation OWNER TO %s;' % (rschema.type, user)) - output.append('GRANT ALL ON %s_relation TO %s;' % (rschema.type, user)) - return '\n'.join(output) + yield 'ALTER TABLE %s_relation OWNER TO %s' % (rschema.type, user) + yield 'GRANT ALL ON %s_relation TO %s' % (rschema.type, user) diff -r ac166217bd8c -r c172fa18565e cubicweb/server/sources/native.py --- a/cubicweb/server/sources/native.py Thu Jun 30 11:18:01 2016 +0200 +++ b/cubicweb/server/sources/native.py Thu Jun 30 13:42:16 2016 +0200 @@ -1,4 +1,4 @@ -# copyright 2003-2014 LOGILAB S.A. (Paris, FRANCE), all rights reserved. +# copyright 2003-2016 LOGILAB S.A. (Paris, FRANCE), all rights reserved. # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr # # This file is part of CubicWeb. @@ -1392,13 +1392,13 @@ source.fti_index_entities(cnx, to_reindex) def sql_schema(driver): + """Yield SQL statements to create system tables in the database.""" helper = get_db_helper(driver) typemap = helper.TYPE_MAPPING - schema = """ -/* Create the repository's system database */ - -%s - + # XXX should return a list of sql statements rather than ';' joined statements + for sql in helper.sql_create_numrange('entities_id_seq').split(';'): + yield sql + for sql in (""" CREATE TABLE entities ( eid INTEGER PRIMARY KEY NOT NULL, type VARCHAR(64) NOT NULL, @@ -1447,35 +1447,36 @@ CREATE INDEX tx_relation_actions_txa_public_idx ON tx_relation_actions(txa_public);; CREATE INDEX tx_relation_actions_eid_from_idx ON tx_relation_actions(eid_from);; CREATE INDEX tx_relation_actions_eid_to_idx ON tx_relation_actions(eid_to);; -CREATE INDEX tx_relation_actions_tx_uuid_idx ON tx_relation_actions(tx_uuid);; -""" % (helper.sql_create_numrange('entities_id_seq').replace(';', ';;'), - typemap['Datetime'], - typemap['Boolean'], typemap['Bytes'], typemap['Boolean']) +CREATE INDEX tx_relation_actions_tx_uuid_idx ON tx_relation_actions(tx_uuid) +""" % (typemap['Datetime'], + typemap['Boolean'], typemap['Bytes'], typemap['Boolean'])).split(';'): + yield sql if helper.backend_name == 'sqlite': # sqlite support the ON DELETE CASCADE syntax but do nothing - schema += ''' + yield ''' CREATE TRIGGER fkd_transactions BEFORE DELETE ON transactions FOR EACH ROW BEGIN DELETE FROM tx_entity_actions WHERE tx_uuid=OLD.tx_uuid; DELETE FROM tx_relation_actions WHERE tx_uuid=OLD.tx_uuid; -END;; +END; ''' # define a multi-columns index on a single index to please sqlserver, which doesn't like several # null entries in a UNIQUE column - schema += ';;'.join(helper.sqls_create_multicol_unique_index('entities', ['extid'], 'entities_extid_idx')) - schema += ';;\n' - return schema + for sql in helper.sqls_create_multicol_unique_index('entities', ['extid'], + 'entities_extid_idx'): + yield sql def grant_schema(user, set_owner=True): - result = '' + """Yield SQL statements to give all access (and ownership if `set_owner` is True) on the + database system tables to `user`. + """ for table in ('entities', 'entities_id_seq', 'transactions', 'tx_entity_actions', 'tx_relation_actions'): if set_owner: - result = 'ALTER TABLE %s OWNER TO %s;\n' % (table, user) - result += 'GRANT ALL ON %s TO %s;\n' % (table, user) - return result + yield 'ALTER TABLE %s OWNER TO %s;' % (table, user) + yield 'GRANT ALL ON %s TO %s;' % (table, user) class BaseAuthentifier(object): diff -r ac166217bd8c -r c172fa18565e cubicweb/server/sqlutils.py --- a/cubicweb/server/sqlutils.py Thu Jun 30 11:18:01 2016 +0200 +++ b/cubicweb/server/sqlutils.py Thu Jun 30 13:42:16 2016 +0200 @@ -115,47 +115,40 @@ def sqlgrants(schema, driver, user, text_index=True, set_owner=True, skip_relations=(), skip_entities=()): - """return sql to give all access privileges to the given user on the system - schema + """Return a list of SQL statements to give all access privileges to the given user on the + database. """ from cubicweb.server.schema2sql import grant_schema from cubicweb.server.sources import native - output = [] - w = output.append - w(native.grant_schema(user, set_owner)) - w('') + stmts = list(native.grant_schema(user, set_owner)) if text_index: dbhelper = db.get_db_helper(driver) - w(dbhelper.sql_grant_user_on_fti(user)) - w('') - w(grant_schema(schema, user, set_owner, skip_entities=skip_entities, prefix=SQL_PREFIX)) - return '\n'.join(output) + # XXX should return a list of sql statements rather than ';' joined statements + stmts += dbhelper.sql_grant_user_on_fti(user).split(';') + stmts += grant_schema(schema, user, set_owner, skip_entities=skip_entities, prefix=SQL_PREFIX) + return stmts def sqlschema(schema, driver, text_index=True, user=None, set_owner=False, skip_relations=PURE_VIRTUAL_RTYPES, skip_entities=()): - """return the system sql schema, according to the given parameters""" + """Return the database SQL schema as a list of SQL statements, according to the given parameters. + """ from cubicweb.server.schema2sql import schema2sql from cubicweb.server.sources import native if set_owner: assert user, 'user is argument required when set_owner is true' - output = [] - w = output.append - w(native.sql_schema(driver)) - w('') + stmts = list(native.sql_schema(driver)) dbhelper = db.get_db_helper(driver) if text_index: - w(dbhelper.sql_init_fti().replace(';', ';;')) - w('') - w(schema2sql(dbhelper, schema, prefix=SQL_PREFIX, - skip_entities=skip_entities, - skip_relations=skip_relations).replace(';', ';;')) + stmts += dbhelper.sql_init_fti().split(';') # XXX + stmts += schema2sql(dbhelper, schema, prefix=SQL_PREFIX, + skip_entities=skip_entities, + skip_relations=skip_relations) if dbhelper.users_support and user: - w('') - w(sqlgrants(schema, driver, user, text_index, set_owner, - skip_relations, skip_entities).replace(';', ';;')) - return '\n'.join(output) + stmts += sqlgrants(schema, driver, user, text_index, set_owner, + skip_relations, skip_entities) + return stmts _SQL_DROP_ALL_USER_TABLES_FILTER_FUNCTION = re.compile('^(?!(sql|pg)_)').match diff -r ac166217bd8c -r c172fa18565e cubicweb/server/test/data-schema2sql/schema/schema.py --- a/cubicweb/server/test/data-schema2sql/schema/schema.py Thu Jun 30 11:18:01 2016 +0200 +++ b/cubicweb/server/test/data-schema2sql/schema/schema.py Thu Jun 30 13:42:16 2016 +0200 @@ -1,4 +1,4 @@ -# copyright 2004-2014 LOGILAB S.A. (Paris, FRANCE), all rights reserved. +# copyright 2004-2016 LOGILAB S.A. (Paris, FRANCE), all rights reserved. # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr # # This file is part of yams. @@ -38,7 +38,7 @@ nom = String(maxsize=64, fulltextindexed=True, required=True) prenom = String(maxsize=64, fulltextindexed=True) sexe = String(maxsize=1, default='M') - promo = String(vocabulary=('bon','pasbon')) + promo = String(vocabulary=('bon','pasbon','pas;bon;;du;;;tout;;;;')) titre = String(maxsize=128, fulltextindexed=True) adel = String(maxsize=128) ass = String(maxsize=128) diff -r ac166217bd8c -r c172fa18565e cubicweb/server/test/unittest_schema2sql.py --- a/cubicweb/server/test/unittest_schema2sql.py Thu Jun 30 11:18:01 2016 +0200 +++ b/cubicweb/server/test/unittest_schema2sql.py Thu Jun 30 13:42:16 2016 +0200 @@ -1,4 +1,4 @@ -# copyright 2004-2014 LOGILAB S.A. (Paris, FRANCE), all rights reserved. +# copyright 2004-2016 LOGILAB S.A. (Paris, FRANCE), all rights reserved. # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr # # This file is part of CubicWeb. @@ -33,19 +33,17 @@ schema = SchemaLoader().load([DATADIR]) -EXPECTED_DATA_NO_DROP = """ -CREATE TABLE Affaire( +EXPECTED_DATA_NO_DROP = [ + """CREATE TABLE Affaire( sujet varchar(128), ref varchar(12), inline_rel integer REFERENCES entities (eid) -); -CREATE INDEX idx_444e29ba3bd1f6c7ea89008613345d7b ON Affaire(inline_rel); - -CREATE TABLE Company( +)""", + "CREATE INDEX idx_444e29ba3bd1f6c7ea89008613345d7b ON Affaire(inline_rel)", + """CREATE TABLE Company( name text -); - -CREATE TABLE Datetest( +)""", + """CREATE TABLE Datetest( dt1 timestamp, dt2 timestamp, d1 date, @@ -53,42 +51,36 @@ t1 time, t2 time , CONSTRAINT cstrf6a3dad792ba13c2cddcf61a2b737c00 CHECK(d1 <= CAST(clock_timestamp() AS DATE)) -); - -CREATE TABLE Division( +)""", + """CREATE TABLE Division( name text -); - -CREATE TABLE EPermission( +)""", + """CREATE TABLE EPermission( name varchar(100) NOT NULL -); -CREATE INDEX idx_86fb596553c6f1ebc159422169f76c32 ON EPermission(name); - -CREATE TABLE Eetype( +)""", + "CREATE INDEX idx_86fb596553c6f1ebc159422169f76c32 ON EPermission(name)", + """CREATE TABLE Eetype( name varchar(64) NOT NULL, description text, meta boolean, final boolean, initial_state integer REFERENCES entities (eid) -); -CREATE INDEX idx_f1f29b77c85f57921df19d2c29044d2d ON Eetype(name); -ALTER TABLE Eetype ADD CONSTRAINT key_f1f29b77c85f57921df19d2c29044d2d UNIQUE(name); -CREATE INDEX idx_27be7c0b18181bbdc76f3a54296dd81f ON Eetype(initial_state); - -CREATE TABLE Employee( -); - -CREATE TABLE Note( +)""", + "CREATE INDEX idx_f1f29b77c85f57921df19d2c29044d2d ON Eetype(name)", + "ALTER TABLE Eetype ADD CONSTRAINT key_f1f29b77c85f57921df19d2c29044d2d UNIQUE(name)", + "CREATE INDEX idx_27be7c0b18181bbdc76f3a54296dd81f ON Eetype(initial_state)", + """CREATE TABLE Employee( +)""", + """CREATE TABLE Note( date varchar(10), type varchar(1), para varchar(512) -); - -CREATE TABLE Person( +)""", + """CREATE TABLE Person( nom varchar(64) NOT NULL, prenom varchar(64), sexe varchar(1) DEFAULT 'M', - promo varchar(6), + promo varchar(22), titre varchar(128), adel varchar(128), ass varchar(128), @@ -98,15 +90,14 @@ datenaiss date, test boolean, salary float -, CONSTRAINT cstr151c2116c0c09de13fded0619d5b4aac CHECK(promo IN ('bon', 'pasbon')) -); -CREATE UNIQUE INDEX unique_e6c2d219772dbf1715597f7d9a6b3892 ON Person(nom,prenom); - -CREATE TABLE Salaried( +, CONSTRAINT cstrf5ac746b90a5fdd00fbe037ec9cf18eb CHECK(promo IN ('bon', 'pasbon', 'pas;bon;;du;;;tout;;;;')) +)""", + "CREATE UNIQUE INDEX unique_e6c2d219772dbf1715597f7d9a6b3892 ON Person(nom,prenom)", + """CREATE TABLE Salaried( nom varchar(64) NOT NULL, prenom varchar(64), sexe varchar(1) DEFAULT 'M', - promo varchar(6), + promo varchar(22), titre varchar(128), adel varchar(128), ass varchar(128), @@ -116,11 +107,10 @@ datenaiss date, test boolean, salary float -, CONSTRAINT cstr069569cf1791dba1a2726197c53aeb44 CHECK(promo IN ('bon', 'pasbon')) -); -CREATE UNIQUE INDEX unique_98da0f9de8588baa8966f0b1a6f850a3 ON Salaried(nom,prenom); - -CREATE TABLE Societe( +, CONSTRAINT cstrb73206eeba9fe96a05105a9db62a1509 CHECK(promo IN ('bon', 'pasbon', 'pas;bon;;du;;;tout;;;;')) +)""", + "CREATE UNIQUE INDEX unique_98da0f9de8588baa8966f0b1a6f850a3 ON Salaried(nom,prenom)", + """CREATE TABLE Societe( nom varchar(64), web varchar(128), tel integer, @@ -132,25 +122,21 @@ cp varchar(12), ville varchar(32) , CONSTRAINT cstra0a1deaa997dcd5f9b83a77654d7c287 CHECK(fax <= tel) -); -ALTER TABLE Societe ADD CONSTRAINT key_abace82c402eba4a37ac54a7872607af UNIQUE(tel); - -CREATE TABLE State( +)""", + "ALTER TABLE Societe ADD CONSTRAINT key_abace82c402eba4a37ac54a7872607af UNIQUE(tel)", + """CREATE TABLE State( eid integer PRIMARY KEY REFERENCES entities (eid), name varchar(256) NOT NULL, description text -); -CREATE INDEX idx_fba3802ef9056558bb9c06b5c6ba9aab ON State(name); - -CREATE TABLE Subcompany( +)""", + "CREATE INDEX idx_fba3802ef9056558bb9c06b5c6ba9aab ON State(name)", + """CREATE TABLE Subcompany( name text -); - -CREATE TABLE Subdivision( +)""", + """CREATE TABLE Subdivision( name text -); - -CREATE TABLE pkginfo( +)""", + """CREATE TABLE pkginfo( modname varchar(30) NOT NULL, version varchar(10) DEFAULT '0.1' NOT NULL, copyright text NOT NULL, @@ -163,125 +149,100 @@ debian_handler varchar(6) , CONSTRAINT cstrbffed5ce7306d65a0db51182febd4a7b CHECK(license IN ('GPL', 'ZPL')) , CONSTRAINT cstr2238b33d09bf7c441e0888be354c2444 CHECK(debian_handler IN ('machin', 'bidule')) -); - - -CREATE TABLE concerne_relation ( +)""", + """CREATE TABLE concerne_relation ( eid_from INTEGER NOT NULL REFERENCES entities (eid), eid_to INTEGER NOT NULL REFERENCES entities (eid), CONSTRAINT key_19e70eabae35becb48c247bc4a688170 PRIMARY KEY(eid_from, eid_to) -); - -CREATE INDEX idx_5ee7db9477832d6e0e847d9d9cd39f5f ON concerne_relation(eid_from); -CREATE INDEX idx_07f609872b384bb1e598cc355686a53c ON concerne_relation(eid_to); - -CREATE TABLE division_of_relation ( +)""", + "CREATE INDEX idx_5ee7db9477832d6e0e847d9d9cd39f5f ON concerne_relation(eid_from)", + "CREATE INDEX idx_07f609872b384bb1e598cc355686a53c ON concerne_relation(eid_to)", + """CREATE TABLE division_of_relation ( eid_from INTEGER NOT NULL REFERENCES entities (eid), eid_to INTEGER NOT NULL REFERENCES entities (eid), CONSTRAINT key_ca129a4cfa4c185c7783654e9e97da5a PRIMARY KEY(eid_from, eid_to) -); - -CREATE INDEX idx_78da9d594180fecb68ef1eba0c17a975 ON division_of_relation(eid_from); -CREATE INDEX idx_0e6bd09d8d25129781928848e2f6d8d5 ON division_of_relation(eid_to); - -CREATE TABLE evaluee_relation ( +)""", + "CREATE INDEX idx_78da9d594180fecb68ef1eba0c17a975 ON division_of_relation(eid_from)", + "CREATE INDEX idx_0e6bd09d8d25129781928848e2f6d8d5 ON division_of_relation(eid_to)", + """CREATE TABLE evaluee_relation ( eid_from INTEGER NOT NULL REFERENCES entities (eid), eid_to INTEGER NOT NULL REFERENCES entities (eid), CONSTRAINT key_61aa7ea90ed7e43818c9865a3a7eb046 PRIMARY KEY(eid_from, eid_to) -); - -CREATE INDEX idx_69358dbe47990b4f8cf22af55b064dc5 ON evaluee_relation(eid_from); -CREATE INDEX idx_634663371244297334ff655a26d6cce3 ON evaluee_relation(eid_to); - -CREATE TABLE next_state_relation ( +)""", + "CREATE INDEX idx_69358dbe47990b4f8cf22af55b064dc5 ON evaluee_relation(eid_from)", + "CREATE INDEX idx_634663371244297334ff655a26d6cce3 ON evaluee_relation(eid_to)", + """CREATE TABLE next_state_relation ( eid_from INTEGER NOT NULL REFERENCES entities (eid), eid_to INTEGER NOT NULL REFERENCES entities (eid), CONSTRAINT key_24a1275472da1ccc1031f6c463cdaa95 PRIMARY KEY(eid_from, eid_to) -); - -CREATE INDEX idx_e5c1a2ddc41a057eaaf6bdf9f5c6b587 ON next_state_relation(eid_from); -CREATE INDEX idx_a3cf3cb065213186cf825e13037df826 ON next_state_relation(eid_to); - -CREATE TABLE obj_wildcard_relation ( +)""", + "CREATE INDEX idx_e5c1a2ddc41a057eaaf6bdf9f5c6b587 ON next_state_relation(eid_from)", + "CREATE INDEX idx_a3cf3cb065213186cf825e13037df826 ON next_state_relation(eid_to)", + """CREATE TABLE obj_wildcard_relation ( eid_from INTEGER NOT NULL REFERENCES entities (eid), eid_to INTEGER NOT NULL REFERENCES entities (eid), CONSTRAINT key_d252c56177735139c85aee463cd65703 PRIMARY KEY(eid_from, eid_to) -); - -CREATE INDEX idx_efbd9bd98c44bdfe2add479ab6704017 ON obj_wildcard_relation(eid_from); -CREATE INDEX idx_e8c168c66f9d6057ce14e644b8436808 ON obj_wildcard_relation(eid_to); - -CREATE TABLE require_permission_relation ( +)""", + "CREATE INDEX idx_efbd9bd98c44bdfe2add479ab6704017 ON obj_wildcard_relation(eid_from)", + "CREATE INDEX idx_e8c168c66f9d6057ce14e644b8436808 ON obj_wildcard_relation(eid_to)", + """CREATE TABLE require_permission_relation ( eid_from INTEGER NOT NULL REFERENCES entities (eid), eid_to INTEGER NOT NULL REFERENCES entities (eid), CONSTRAINT key_24f38c4edaf84fdcc0f0d093fec3d5c7 PRIMARY KEY(eid_from, eid_to) -); - -CREATE INDEX idx_193987ddfd7c66bf43ded029ea363605 ON require_permission_relation(eid_from); -CREATE INDEX idx_f6dd784ff5161c4461a753591fe1de94 ON require_permission_relation(eid_to); - -CREATE TABLE state_of_relation ( +)""", + "CREATE INDEX idx_193987ddfd7c66bf43ded029ea363605 ON require_permission_relation(eid_from)", + "CREATE INDEX idx_f6dd784ff5161c4461a753591fe1de94 ON require_permission_relation(eid_to)", + """CREATE TABLE state_of_relation ( eid_from INTEGER NOT NULL REFERENCES entities (eid), eid_to INTEGER NOT NULL REFERENCES entities (eid), CONSTRAINT key_be6983bc3072230d2e22f7631a0c9e25 PRIMARY KEY(eid_from, eid_to) -); - -CREATE INDEX idx_5f17c14443de03bd1ef79750c89c2390 ON state_of_relation(eid_from); -CREATE INDEX idx_0ee453927e090f6eec01c412278dea9b ON state_of_relation(eid_to); - -CREATE TABLE subcompany_of_relation ( +)""", + "CREATE INDEX idx_5f17c14443de03bd1ef79750c89c2390 ON state_of_relation(eid_from)", + "CREATE INDEX idx_0ee453927e090f6eec01c412278dea9b ON state_of_relation(eid_to)", + """CREATE TABLE subcompany_of_relation ( eid_from INTEGER NOT NULL REFERENCES entities (eid), eid_to INTEGER NOT NULL REFERENCES entities (eid), CONSTRAINT key_25bee50df3b495a40a02aa39f832377f PRIMARY KEY(eid_from, eid_to) -); - -CREATE INDEX idx_1e6ee813030fec8d4439fc186ce752b0 ON subcompany_of_relation(eid_from); -CREATE INDEX idx_259f9ba242f4cb80b9b2f2f9a754fca7 ON subcompany_of_relation(eid_to); - -CREATE TABLE subdivision_of_relation ( +)""", + "CREATE INDEX idx_1e6ee813030fec8d4439fc186ce752b0 ON subcompany_of_relation(eid_from)", + "CREATE INDEX idx_259f9ba242f4cb80b9b2f2f9a754fca7 ON subcompany_of_relation(eid_to)", + """CREATE TABLE subdivision_of_relation ( eid_from INTEGER NOT NULL REFERENCES entities (eid), eid_to INTEGER NOT NULL REFERENCES entities (eid), CONSTRAINT key_4d6f7368345676ebb66758ab71f60aef PRIMARY KEY(eid_from, eid_to) -); - -CREATE INDEX idx_a90a958166c767b50a7294e93858c1a8 ON subdivision_of_relation(eid_from); -CREATE INDEX idx_0360028629649b26da96044a12735ad4 ON subdivision_of_relation(eid_to); - -CREATE TABLE subj_wildcard_relation ( +)""", + "CREATE INDEX idx_a90a958166c767b50a7294e93858c1a8 ON subdivision_of_relation(eid_from)", + "CREATE INDEX idx_0360028629649b26da96044a12735ad4 ON subdivision_of_relation(eid_to)", + """CREATE TABLE subj_wildcard_relation ( eid_from INTEGER NOT NULL REFERENCES entities (eid), eid_to INTEGER NOT NULL REFERENCES entities (eid), CONSTRAINT key_712ea3ec0bc1976bddc93ceba0acff06 PRIMARY KEY(eid_from, eid_to) -); - -CREATE INDEX idx_4dbfa4a0d44aaa0f0816560fa8b81c22 ON subj_wildcard_relation(eid_from); -CREATE INDEX idx_09aa23f8a8b63189d05a63f8d49c7bc0 ON subj_wildcard_relation(eid_to); - -CREATE TABLE sym_rel_relation ( +)""", + "CREATE INDEX idx_4dbfa4a0d44aaa0f0816560fa8b81c22 ON subj_wildcard_relation(eid_from)", + "CREATE INDEX idx_09aa23f8a8b63189d05a63f8d49c7bc0 ON subj_wildcard_relation(eid_to)", + """CREATE TABLE sym_rel_relation ( eid_from INTEGER NOT NULL REFERENCES entities (eid), eid_to INTEGER NOT NULL REFERENCES entities (eid), CONSTRAINT key_c787b80522205c42402530580b0d307b PRIMARY KEY(eid_from, eid_to) -); - -CREATE INDEX idx_a46ed54f98cc4d91f0df5375d3ef73cb ON sym_rel_relation(eid_from); -CREATE INDEX idx_0faa43abe25fc83e9400a3b96daed2b2 ON sym_rel_relation(eid_to); - -CREATE TABLE travaille_relation ( +)""", + "CREATE INDEX idx_a46ed54f98cc4d91f0df5375d3ef73cb ON sym_rel_relation(eid_from)", + "CREATE INDEX idx_0faa43abe25fc83e9400a3b96daed2b2 ON sym_rel_relation(eid_to)", + """CREATE TABLE travaille_relation ( eid_from INTEGER NOT NULL REFERENCES entities (eid), eid_to INTEGER NOT NULL REFERENCES entities (eid), CONSTRAINT key_d7b209a1f84d9cae74a98626ef0aba0b PRIMARY KEY(eid_from, eid_to) -); - -CREATE INDEX idx_b00e86c772e6577ad7a7901dd0b257b2 ON travaille_relation(eid_from); -CREATE INDEX idx_970c052363294a9871a4824c9588e220 ON travaille_relation(eid_to); -""" +)""", + "CREATE INDEX idx_b00e86c772e6577ad7a7901dd0b257b2 ON travaille_relation(eid_from)", + "CREATE INDEX idx_970c052363294a9871a4824c9588e220 ON travaille_relation(eid_to)", +] class SQLSchemaTC(TestCase): def test_known_values(self): dbhelper = get_db_helper('postgres') - output = schema2sql.schema2sql(dbhelper, schema, skip_relations=('works_for',)) - self.assertMultiLineEqual(EXPECTED_DATA_NO_DROP.strip(), output.strip()) + output = list(schema2sql.schema2sql(dbhelper, schema, skip_relations=('works_for',))) + self.assertEqual(output, EXPECTED_DATA_NO_DROP) if __name__ == '__main__':