# HG changeset patch # User Julien Cristau # Date 1421336375 -3600 # Node ID 218c28bff6956096fff9e1ca4ab40f194dbe0bd6 # Parent 534efa7bfaebde4f037117c11f4cd6ed7bc7e8cc Steal schema2sql module from yams It has no user inside yams, and we need it here if we want to be able to reference the (cubicweb-only) entities table from other tables. Related to #4846892 diff -r 534efa7bfaeb -r 218c28bff695 server/schema2sql.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/server/schema2sql.py Thu Jan 15 16:39:35 2015 +0100 @@ -0,0 +1,246 @@ +# copyright 2004-2013 LOGILAB S.A. (Paris, FRANCE), all rights reserved. +# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr +# +# This file is part of yams. +# +# yams is free software: you can redistribute it and/or modify it under the +# terms of the GNU Lesser General Public License as published by the Free +# Software Foundation, either version 2.1 of the License, or (at your option) +# any later version. +# +# yams is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more +# details. +# +# You should have received a copy of the GNU Lesser General Public License along +# with yams. If not, see . +"""write a schema as sql""" + +__docformat__ = "restructuredtext en" + +from hashlib import md5 + +from six.moves import range + +from yams.constraints import SizeConstraint, UniqueConstraint + +# default are usually not handled at the sql level. If you want them, set +# SET_DEFAULT to True +SET_DEFAULT = False + + +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 + """ + 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 rtype in sorted(schema.relations()): + rschema = schema.rschema(rtype) + if rschema.final or rschema.inlined or rschema.rule: + continue + w(rschema2sql(rschema)) + return '\n'.join(output) + + +def dropschema2sql(dbhelper, schema, skip_entities=(), skip_relations=(), prefix=''): + """write to the output stream a SQL schema to store the objects + corresponding to the given schema + """ + output = [] + w = output.append + for etype in sorted(schema.entities()): + eschema = schema.eschema(etype) + if eschema.final or eschema.type in skip_entities: + continue + stmts = dropeschema2sql(dbhelper, eschema, skip_relations, prefix=prefix) + for stmt in stmts: + w(stmt) + for rtype in sorted(schema.relations()): + rschema = schema.rschema(rtype) + if rschema.final or rschema.inlined: + continue + w(droprschema2sql(rschema)) + return '\n'.join(output) + + +def eschema_attrs(eschema, skip_relations): + attrs = [attrdef for attrdef in eschema.attribute_definitions() + if not attrdef[0].type in skip_relations] + attrs += [(rschema, None) + for rschema in eschema.subject_relations() + if not rschema.final and rschema.inlined] + return attrs + +def unique_index_name(eschema, columns): + return u'unique_%s' % md5((eschema.type + + ',' + + ','.join(sorted(columns))).encode('ascii')).hexdigest() + +def iter_unique_index_names(eschema): + for columns in eschema._unique_together or (): + yield columns, unique_index_name(eschema, columns) + +def dropeschema2sql(dbhelper, eschema, skip_relations=(), prefix=''): + """return sql to drop an entity type's table""" + # not necessary to drop indexes, that's implictly done when + # dropping the table, but we need to drop SQLServer views used to + # create multicol unique indices + statements = [] + tablename = prefix + eschema.type + if eschema._unique_together is not None: + for columns, index_name in iter_unique_index_names(eschema): + cols = ['%s%s' % (prefix, col) for col in columns] + sqls = dbhelper.sqls_drop_multicol_unique_index(tablename, cols, index_name) + statements += sqls + statements += ['DROP TABLE %s;' % (tablename)] + return statements + + +def eschema2sql(dbhelper, eschema, skip_relations=(), prefix=''): + """write an entity schema as SQL statements to stdout""" + output = [] + w = output.append + table = prefix + eschema.type + w('CREATE TABLE %s(' % (table)) + attrs = eschema_attrs(eschema, skip_relations) + # XXX handle objectinline physical mode + for i in range(len(attrs)): + rschema, attrschema = attrs[i] + if attrschema is not None: + sqltype = aschema2sql(dbhelper, eschema, rschema, attrschema, + indent=' ') + else: # inline relation + # XXX integer is ginco specific + sqltype = 'integer' + if i == len(attrs) - 1: + w(' %s%s %s' % (prefix, rschema.type, sqltype)) + else: + w(' %s%s %s,' % (prefix, rschema.type, sqltype)) + w(');') + # 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)) + for columns, index_name in iter_unique_index_names(eschema): + cols = ['%s%s' % (prefix, col) for col in columns] + sqls = dbhelper.sqls_create_multicol_unique_index(table, cols, index_name) + for sql in sqls: + w(sql) + w('') + return '\n'.join(output) + + +def aschema2sql(dbhelper, eschema, rschema, aschema, creating=True, indent=''): + """write an attribute schema as SQL statements to stdout""" + attr = rschema.type + rdef = rschema.rdef(eschema.type, aschema.type) + sqltype = type_from_constraints(dbhelper, aschema.type, rdef.constraints, + creating) + if SET_DEFAULT: + default = eschema.default(attr) + if default is not None: + if aschema.type == 'Boolean': + sqltype += ' DEFAULT %s' % dbhelper.boolean_value(default) + elif aschema.type == 'String': + sqltype += ' DEFAULT %r' % str(default) + elif aschema.type in ('Int', 'BigInt', 'Float'): + sqltype += ' DEFAULT %s' % default + # XXX ignore default for other type + # this is expected for NOW / TODAY + if creating: + if rdef.uid: + sqltype += ' PRIMARY KEY' + elif rdef.cardinality[0] == '1': + # don't set NOT NULL if backend isn't able to change it later + if dbhelper.alter_column_support: + sqltype += ' NOT NULL' + # else we're getting sql type to alter a column, we don't want key / indexes + # / null modifiers + return sqltype + + +def type_from_constraints(dbhelper, etype, constraints, creating=True): + """return a sql type string corresponding to the constraints""" + constraints = list(constraints) + unique, sqltype = False, None + size_constrained_string = dbhelper.TYPE_MAPPING.get('SizeConstrainedString', 'varchar(%s)') + if etype == 'String': + for constraint in constraints: + if isinstance(constraint, SizeConstraint): + if constraint.max is not None: + sqltype = size_constrained_string % constraint.max + elif isinstance(constraint, UniqueConstraint): + unique = True + if sqltype is None: + sqltype = dbhelper.TYPE_MAPPING[etype] + if creating and unique: + sqltype += ' UNIQUE' + return sqltype + + +_SQL_SCHEMA = """ +CREATE TABLE %(table)s ( + eid_from INTEGER NOT NULL, + eid_to INTEGER NOT NULL, + CONSTRAINT %(table)s_p_key PRIMARY KEY(eid_from, eid_to) +); + +CREATE INDEX %(table)s_from_idx ON %(table)s(eid_from); +CREATE INDEX %(table)s_to_idx ON %(table)s(eid_to);""" + + +def rschema2sql(rschema): + assert not rschema.rule + return _SQL_SCHEMA % {'table': '%s_relation' % rschema.type} + + +def droprschema2sql(rschema): + """return sql to drop a relation type's table""" + # not necessary to drop indexes, that's implictly done when dropping + # the table + return 'DROP TABLE %s_relation;' % rschema.type + + +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 + """ + 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 rtype in sorted(schema.relations()): + rschema = schema.rschema(rtype) + if rschema.final or rschema.inlined: + continue + w(grant_rschema(rschema, user, set_owner)) + return '\n'.join(output) + + +def grant_eschema(eschema, user, set_owner=True, prefix=''): + output = [] + w = output.append + 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) + + +def grant_rschema(rschema, user, set_owner=True): + output = [] + 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) diff -r 534efa7bfaeb -r 218c28bff695 server/test/data-schema2sql/__init__.py diff -r 534efa7bfaeb -r 218c28bff695 server/test/data-schema2sql/schema/Company.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/server/test/data-schema2sql/schema/Company.py Thu Jan 15 16:39:35 2015 +0100 @@ -0,0 +1,67 @@ +# copyright 2004-2010 LOGILAB S.A. (Paris, FRANCE), all rights reserved. +# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr +# +# This file is part of yams. +# +# yams is free software: you can redistribute it and/or modify it under the +# terms of the GNU Lesser General Public License as published by the Free +# Software Foundation, either version 2.1 of the License, or (at your option) +# any later version. +# +# yams is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more +# details. +# +# You should have received a copy of the GNU Lesser General Public License along +# with yams. If not, see . +from yams.buildobjs import EntityType, RelationType, RelationDefinition, \ + SubjectRelation, String + +class Company(EntityType): + name = String() + +class Subcompany(Company): + __specializes_schema__ = True + subcompany_of = SubjectRelation('Company') + +class Division(Company): + __specializes_schema__ = True + division_of = SubjectRelation('Company') + +class Subdivision(Division): + __specializes_schema__ = True + subdivision_of = SubjectRelation('Company') + +class Employee(EntityType): + works_for = SubjectRelation('Company') + +class require_permission(RelationType): + """link a permission to the entity. This permission should be used in the + security definition of the entity's type to be useful. + """ + fulltext_container = 'subject' + __permissions__ = { + 'read': ('managers', 'users', 'guests'), + 'add': ('managers',), + 'delete': ('managers',), + } + + +class missing_require_permission(RelationDefinition): + name = 'require_permission' + subject = 'Company' + object = 'EPermission' + +class EPermission(EntityType): + """entity type that may be used to construct some advanced security configuration + """ + __permissions__ = { + 'read': ('managers', 'users', 'guests',), + 'add': ('managers',), + 'delete': ('managers',), + 'update': ('managers', 'owners',), + } + name = String(required=True, indexed=True, internationalizable=True, + fulltextindexed=True, maxsize=100, + description=_('name or identifier of the permission')) diff -r 534efa7bfaeb -r 218c28bff695 server/test/data-schema2sql/schema/Dates.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/server/test/data-schema2sql/schema/Dates.py Thu Jan 15 16:39:35 2015 +0100 @@ -0,0 +1,28 @@ +# copyright 2004-2014 LOGILAB S.A. (Paris, FRANCE), all rights reserved. +# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr +# +# This file is part of yams. +# +# yams is free software: you can redistribute it and/or modify it under the +# terms of the GNU Lesser General Public License as published by the Free +# Software Foundation, either version 2.1 of the License, or (at your option) +# any later version. +# +# yams is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more +# details. +# +# You should have received a copy of the GNU Lesser General Public License along +# with yams. If not, see . +from datetime import time, date +from yams.buildobjs import EntityType, Datetime, Date, Time + +class Datetest(EntityType): + dt1 = Datetime(default=u'now') + dt2 = Datetime(default=u'today') + d1 = Date(default=u'today') + d2 = Date(default=date(2007, 12, 11)) + t1 = Time(default=time(8, 40)) + t2 = Time(default=time(9, 45)) + diff -r 534efa7bfaeb -r 218c28bff695 server/test/data-schema2sql/schema/State.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/server/test/data-schema2sql/schema/State.py Thu Jan 15 16:39:35 2015 +0100 @@ -0,0 +1,81 @@ +# copyright 2004-2013 LOGILAB S.A. (Paris, FRANCE), all rights reserved. +# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr +# +# This file is part of yams. +# +# yams is free software: you can redistribute it and/or modify it under the +# terms of the GNU Lesser General Public License as published by the Free +# Software Foundation, either version 2.1 of the License, or (at your option) +# any later version. +# +# yams is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more +# details. +# +# You should have received a copy of the GNU Lesser General Public License along +# with yams. If not, see . +from yams.buildobjs import (EntityType, RelationType, RelationDefinition, + SubjectRelation, Int, String, Boolean) +from yams.constraints import SizeConstraint, UniqueConstraint + +from __init__ import RESTRICTED_RTYPE_PERMS + +class State(EntityType): + """used to associate simple states to an entity + type and/or to define workflows + """ + __permissions__ = { + 'read': ('managers', 'users', 'guests',), + 'add': ('managers', 'users',), + 'delete': ('managers', 'owners',), + 'update': ('managers', 'owners',), + } + + # attributes + eid = Int(required=True, uid=True) + name = String(required=True, + indexed=True, internationalizable=True, + constraints=[SizeConstraint(256)]) + description = String(fulltextindexed=True) + # relations + state_of = SubjectRelation('Eetype', cardinality='+*') + next_state = SubjectRelation('State', cardinality='**') + + +class state_of(RelationType): + """link a state to one or more entity type""" + __permissions__ = RESTRICTED_RTYPE_PERMS + +class next_state(RelationType): + """define a workflow by associating a state to possible following states + """ + __permissions__ = RESTRICTED_RTYPE_PERMS + +class initial_state(RelationType): + """indicate which state should be used by default when an entity using states + is created + """ + __permissions__ = { + 'read': ('managers', 'users', 'guests',), + 'add': ('managers', 'users',), + 'delete': ('managers', 'users',), + } + subject = 'Eetype' + object = 'State' + cardinality = '?*' + inlined = True + +class Eetype(EntityType): + """define an entity type, used to build the application schema""" + __permissions__ = { + 'read': ('managers', 'users', 'guests',), + 'add': ('managers',), + 'delete': ('managers',), + 'update': ('managers', 'owners',), + } + name = String(required=True, indexed=True, internationalizable=True, + constraints=[UniqueConstraint(), SizeConstraint(64)]) + description = String(fulltextindexed=True) + meta = Boolean() + final = Boolean() diff -r 534efa7bfaeb -r 218c28bff695 server/test/data-schema2sql/schema/__init__.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/server/test/data-schema2sql/schema/__init__.py Thu Jan 15 16:39:35 2015 +0100 @@ -0,0 +1,23 @@ +# copyright 2004-2010 LOGILAB S.A. (Paris, FRANCE), all rights reserved. +# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr +# +# This file is part of yams. +# +# yams is free software: you can redistribute it and/or modify it under the +# terms of the GNU Lesser General Public License as published by the Free +# Software Foundation, either version 2.1 of the License, or (at your option) +# any later version. +# +# yams is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more +# details. +# +# You should have received a copy of the GNU Lesser General Public License along +# with yams. If not, see . +"""test schema""" +RESTRICTED_RTYPE_PERMS = { + 'read': ('managers', 'users', 'guests',), + 'add': ('managers',), + 'delete': ('managers',), + } diff -r 534efa7bfaeb -r 218c28bff695 server/test/data-schema2sql/schema/schema.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/server/test/data-schema2sql/schema/schema.py Thu Jan 15 16:39:35 2015 +0100 @@ -0,0 +1,112 @@ +# copyright 2004-2014 LOGILAB S.A. (Paris, FRANCE), all rights reserved. +# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr +# +# This file is part of yams. +# +# yams is free software: you can redistribute it and/or modify it under the +# terms of the GNU Lesser General Public License as published by the Free +# Software Foundation, either version 2.1 of the License, or (at your option) +# any later version. +# +# yams is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more +# details. +# +# You should have received a copy of the GNU Lesser General Public License along +# with yams. If not, see . +from yams.buildobjs import (EntityType, RelationDefinition, RelationType, + SubjectRelation, String, Int, Float, Date, Boolean) + +class Affaire(EntityType): + sujet = String(maxsize=128) + ref = String(maxsize=12) + + concerne = SubjectRelation('Societe') + obj_wildcard = SubjectRelation('*') + sym_rel = SubjectRelation('Person', symmetric=True) + inline_rel = SubjectRelation('Person', inlined=True, cardinality='?*') + +class subj_wildcard(RelationDefinition): + subject = '*' + object = 'Affaire' + + +class Person(EntityType): + __unique_together__ = [('nom', 'prenom')] + 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')) + titre = String(maxsize=128, fulltextindexed=True) + adel = String(maxsize=128) + ass = String(maxsize=128) + web = String(maxsize=128) + tel = Int(__permissions__={'read': (), + 'add': ('managers',), + 'update': ('managers',)}) + fax = Int() + datenaiss = Date() + test = Boolean() + salary = Float() + travaille = SubjectRelation('Societe', + __permissions__={'read': (), + 'add': (), + 'delete': ('managers',), + }) + + evaluee = SubjectRelation('Note') + +class Salaried(Person): + __specializes_schema__ = True + +class Societe(EntityType): + nom = String(maxsize=64, fulltextindexed=True) + web = String(maxsize=128) + tel = Int() + fax = Int() + rncs = String(maxsize=32) + ad1 = String(maxsize=128) + ad2 = String(maxsize=128) + ad3 = String(maxsize=128) + cp = String(maxsize=12) + ville = String(maxsize=32) + + evaluee = SubjectRelation('Note') + + +class Note(EntityType): + date = String(maxsize=10) + type = String(maxsize=1) + para = String(maxsize=512) + + +class pkginfo(EntityType): + modname = String(maxsize=30, required=True) + version = String(maxsize=10, required=True, default='0.1') + copyright = String(required=True) + license = String(vocabulary=('GPL', 'ZPL')) + short_desc = String(maxsize=80, required=True) + long_desc = String(required=True, fulltextindexed=True) + author = String(maxsize=100, required=True) + author_email = String(maxsize=100, required=True) + mailinglist = String(maxsize=100) + debian_handler = String(vocabulary=('machin', 'bidule')) + + +class evaluee(RelationType): + __permissions__ = { + 'read': ('managers',), + 'add': ('managers',), + 'delete': ('managers',), + } + +class concerne(RelationDefinition): + subject = 'Person' + object = 'Affaire' + __permissions__ = { + 'read': ('managers',), + 'add': ('managers',), + 'delete': ('managers',), + } + diff -r 534efa7bfaeb -r 218c28bff695 server/test/data-schema2sql/schema/toignore --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/server/test/data-schema2sql/schema/toignore Thu Jan 15 16:39:35 2015 +0100 @@ -0,0 +1,1 @@ +coucou diff -r 534efa7bfaeb -r 218c28bff695 server/test/unittest_schema2sql.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/server/test/unittest_schema2sql.py Thu Jan 15 16:39:35 2015 +0100 @@ -0,0 +1,288 @@ +# copyright 2004-2014 LOGILAB S.A. (Paris, FRANCE), all rights reserved. +# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr +# +# This file is part of yams. +# +# yams is free software: you can redistribute it and/or modify it under the +# terms of the GNU Lesser General Public License as published by the Free +# Software Foundation, either version 2.1 of the License, or (at your option) +# any later version. +# +# yams is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more +# details. +# +# You should have received a copy of the GNU Lesser General Public License along +# with yams. If not, see . +"""unit tests for module yams.schema2sql +""" + +import os.path as osp + +from logilab.common.testlib import TestCase, unittest_main +from logilab.database import get_db_helper + +from yams.reader import SchemaLoader +from cubicweb.server import schema2sql + +schema2sql.SET_DEFAULT = True + +DATADIR = osp.abspath(osp.join(osp.dirname(__file__), 'data-schema2sql')) + +schema = SchemaLoader().load([DATADIR]) + + +EXPECTED_DATA_NO_DROP = """ +CREATE TABLE Affaire( + sujet varchar(128), + ref varchar(12), + inline_rel integer +); +CREATE INDEX affaire_inline_rel_idx ON Affaire(inline_rel); + +CREATE TABLE Company( + name text +); + +CREATE TABLE Datetest( + dt1 timestamp, + dt2 timestamp, + d1 date, + d2 date, + t1 time, + t2 time +); + +CREATE TABLE Division( + name text +); + +CREATE TABLE EPermission( + name varchar(100) NOT NULL +); +CREATE INDEX epermission_name_idx ON EPermission(name); + +CREATE TABLE Eetype( + name varchar(64) UNIQUE NOT NULL, + description text, + meta boolean, + final boolean, + initial_state integer +); +CREATE INDEX eetype_name_idx ON Eetype(name); +CREATE INDEX eetype_initial_state_idx ON Eetype(initial_state); + +CREATE TABLE Employee( +); + +CREATE TABLE Note( + date varchar(10), + type varchar(1), + para varchar(512) +); + +CREATE TABLE Person( + nom varchar(64) NOT NULL, + prenom varchar(64), + sexe varchar(1) DEFAULT 'M', + promo varchar(6), + titre varchar(128), + adel varchar(128), + ass varchar(128), + web varchar(128), + tel integer, + fax integer, + datenaiss date, + test boolean, + salary float +); +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), + titre varchar(128), + adel varchar(128), + ass varchar(128), + web varchar(128), + tel integer, + fax integer, + datenaiss date, + test boolean, + salary float +); +CREATE UNIQUE INDEX unique_98da0f9de8588baa8966f0b1a6f850a3 ON Salaried(nom,prenom); + +CREATE TABLE Societe( + nom varchar(64), + web varchar(128), + tel integer, + fax integer, + rncs varchar(32), + ad1 varchar(128), + ad2 varchar(128), + ad3 varchar(128), + cp varchar(12), + ville varchar(32) +); + +CREATE TABLE State( + eid integer PRIMARY KEY, + name varchar(256) NOT NULL, + description text +); +CREATE INDEX state_name_idx ON State(name); + +CREATE TABLE Subcompany( + name text +); + +CREATE TABLE Subdivision( + name text +); + +CREATE TABLE pkginfo( + modname varchar(30) NOT NULL, + version varchar(10) DEFAULT '0.1' NOT NULL, + copyright text NOT NULL, + license varchar(3), + short_desc varchar(80) NOT NULL, + long_desc text NOT NULL, + author varchar(100) NOT NULL, + author_email varchar(100) NOT NULL, + mailinglist varchar(100), + debian_handler varchar(6) +); + + +CREATE TABLE concerne_relation ( + eid_from INTEGER NOT NULL, + eid_to INTEGER NOT NULL, + CONSTRAINT concerne_relation_p_key PRIMARY KEY(eid_from, eid_to) +); + +CREATE INDEX concerne_relation_from_idx ON concerne_relation(eid_from); +CREATE INDEX concerne_relation_to_idx ON concerne_relation(eid_to); + +CREATE TABLE division_of_relation ( + eid_from INTEGER NOT NULL, + eid_to INTEGER NOT NULL, + CONSTRAINT division_of_relation_p_key PRIMARY KEY(eid_from, eid_to) +); + +CREATE INDEX division_of_relation_from_idx ON division_of_relation(eid_from); +CREATE INDEX division_of_relation_to_idx ON division_of_relation(eid_to); + +CREATE TABLE evaluee_relation ( + eid_from INTEGER NOT NULL, + eid_to INTEGER NOT NULL, + CONSTRAINT evaluee_relation_p_key PRIMARY KEY(eid_from, eid_to) +); + +CREATE INDEX evaluee_relation_from_idx ON evaluee_relation(eid_from); +CREATE INDEX evaluee_relation_to_idx ON evaluee_relation(eid_to); + +CREATE TABLE next_state_relation ( + eid_from INTEGER NOT NULL, + eid_to INTEGER NOT NULL, + CONSTRAINT next_state_relation_p_key PRIMARY KEY(eid_from, eid_to) +); + +CREATE INDEX next_state_relation_from_idx ON next_state_relation(eid_from); +CREATE INDEX next_state_relation_to_idx ON next_state_relation(eid_to); + +CREATE TABLE obj_wildcard_relation ( + eid_from INTEGER NOT NULL, + eid_to INTEGER NOT NULL, + CONSTRAINT obj_wildcard_relation_p_key PRIMARY KEY(eid_from, eid_to) +); + +CREATE INDEX obj_wildcard_relation_from_idx ON obj_wildcard_relation(eid_from); +CREATE INDEX obj_wildcard_relation_to_idx ON obj_wildcard_relation(eid_to); + +CREATE TABLE require_permission_relation ( + eid_from INTEGER NOT NULL, + eid_to INTEGER NOT NULL, + CONSTRAINT require_permission_relation_p_key PRIMARY KEY(eid_from, eid_to) +); + +CREATE INDEX require_permission_relation_from_idx ON require_permission_relation(eid_from); +CREATE INDEX require_permission_relation_to_idx ON require_permission_relation(eid_to); + +CREATE TABLE state_of_relation ( + eid_from INTEGER NOT NULL, + eid_to INTEGER NOT NULL, + CONSTRAINT state_of_relation_p_key PRIMARY KEY(eid_from, eid_to) +); + +CREATE INDEX state_of_relation_from_idx ON state_of_relation(eid_from); +CREATE INDEX state_of_relation_to_idx ON state_of_relation(eid_to); + +CREATE TABLE subcompany_of_relation ( + eid_from INTEGER NOT NULL, + eid_to INTEGER NOT NULL, + CONSTRAINT subcompany_of_relation_p_key PRIMARY KEY(eid_from, eid_to) +); + +CREATE INDEX subcompany_of_relation_from_idx ON subcompany_of_relation(eid_from); +CREATE INDEX subcompany_of_relation_to_idx ON subcompany_of_relation(eid_to); + +CREATE TABLE subdivision_of_relation ( + eid_from INTEGER NOT NULL, + eid_to INTEGER NOT NULL, + CONSTRAINT subdivision_of_relation_p_key PRIMARY KEY(eid_from, eid_to) +); + +CREATE INDEX subdivision_of_relation_from_idx ON subdivision_of_relation(eid_from); +CREATE INDEX subdivision_of_relation_to_idx ON subdivision_of_relation(eid_to); + +CREATE TABLE subj_wildcard_relation ( + eid_from INTEGER NOT NULL, + eid_to INTEGER NOT NULL, + CONSTRAINT subj_wildcard_relation_p_key PRIMARY KEY(eid_from, eid_to) +); + +CREATE INDEX subj_wildcard_relation_from_idx ON subj_wildcard_relation(eid_from); +CREATE INDEX subj_wildcard_relation_to_idx ON subj_wildcard_relation(eid_to); + +CREATE TABLE sym_rel_relation ( + eid_from INTEGER NOT NULL, + eid_to INTEGER NOT NULL, + CONSTRAINT sym_rel_relation_p_key PRIMARY KEY(eid_from, eid_to) +); + +CREATE INDEX sym_rel_relation_from_idx ON sym_rel_relation(eid_from); +CREATE INDEX sym_rel_relation_to_idx ON sym_rel_relation(eid_to); + +CREATE TABLE travaille_relation ( + eid_from INTEGER NOT NULL, + eid_to INTEGER NOT NULL, + CONSTRAINT travaille_relation_p_key PRIMARY KEY(eid_from, eid_to) +); + +CREATE INDEX travaille_relation_from_idx ON travaille_relation(eid_from); +CREATE INDEX travaille_relation_to_idx ON travaille_relation(eid_to); + +CREATE TABLE works_for_relation ( + eid_from INTEGER NOT NULL, + eid_to INTEGER NOT NULL, + CONSTRAINT works_for_relation_p_key PRIMARY KEY(eid_from, eid_to) +); + +CREATE INDEX works_for_relation_from_idx ON works_for_relation(eid_from); +CREATE INDEX works_for_relation_to_idx ON works_for_relation(eid_to); +""" + +class SQLSchemaTC(TestCase): + + def test_known_values(self): + dbhelper = get_db_helper('postgres') + output = schema2sql.schema2sql(dbhelper, schema) + self.assertMultiLineEqual(EXPECTED_DATA_NO_DROP.strip(), output.strip()) + + +if __name__ == '__main__': + unittest_main()