--- /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 <http://www.gnu.org/licenses/>.
+"""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)

--- /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 <http://www.gnu.org/licenses/>.
+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'))

--- /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 <http://www.gnu.org/licenses/>.
+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))
+

--- /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 <http://www.gnu.org/licenses/>.
+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()

--- /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 <http://www.gnu.org/licenses/>.
+"""test schema"""
+RESTRICTED_RTYPE_PERMS = {
+    'read':   ('managers', 'users', 'guests',),
+    'add':    ('managers',),
+    'delete': ('managers',),
+    }

--- /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 <http://www.gnu.org/licenses/>.
+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',),
+        }
+

--- /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

--- /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 <http://www.gnu.org/licenses/>.
+"""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()