1 # copyright 2004-2014 LOGILAB S.A. (Paris, FRANCE), all rights reserved.

     2 # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr

     3 #

     4 # This file is part of yams.

     5 #

     6 # yams is free software: you can redistribute it and/or modify it under the

     7 # terms of the GNU Lesser General Public License as published by the Free

     8 # Software Foundation, either version 2.1 of the License, or (at your option)

     9 # any later version.

    10 #

    11 # yams is distributed in the hope that it will be useful, but WITHOUT ANY

    12 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR

    13 # A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for more

    14 # details.

    15 #

    16 # You should have received a copy of the GNU Lesser General Public License along

    17 # with yams. If not, see <http://www.gnu.org/licenses/>.

    18 """unit tests for module yams.schema2sql

    19 """

    20 

    21 import os.path as osp

    22 

    23 from logilab.common.testlib import TestCase, unittest_main

    24 from logilab.database import get_db_helper

    25 

    26 from yams.reader import SchemaLoader

    27 from cubicweb.server import schema2sql

    28 

    29 schema2sql.SET_DEFAULT = True

    30 

    31 DATADIR = osp.abspath(osp.join(osp.dirname(__file__), 'data-schema2sql'))

    32 

    33 schema = SchemaLoader().load([DATADIR])

    34 

    35 

    36 EXPECTED_DATA_NO_DROP = """

    37 CREATE TABLE Affaire(

    38  sujet varchar(128),

    39  ref varchar(12),

    40  inline_rel integer

    41 );

    42 CREATE INDEX affaire_inline_rel_idx ON Affaire(inline_rel);

    43 

    44 CREATE TABLE Company(

    45  name text

    46 );

    47 

    48 CREATE TABLE Datetest(

    49  dt1 timestamp,

    50  dt2 timestamp,

    51  d1 date,

    52  d2 date,

    53  t1 time,

    54  t2 time

    55 );

    56 

    57 CREATE TABLE Division(

    58  name text

    59 );

    60 

    61 CREATE TABLE EPermission(

    62  name varchar(100) NOT NULL

    63 );

    64 CREATE INDEX epermission_name_idx ON EPermission(name);

    65 

    66 CREATE TABLE Eetype(

    67  name varchar(64) UNIQUE NOT NULL,

    68  description text,

    69  meta boolean,

    70  final boolean,

    71  initial_state integer

    72 );

    73 CREATE INDEX eetype_name_idx ON Eetype(name);

    74 CREATE INDEX eetype_initial_state_idx ON Eetype(initial_state);

    75 

    76 CREATE TABLE Employee(

    77 );

    78 

    79 CREATE TABLE Note(

    80  date varchar(10),

    81  type varchar(1),

    82  para varchar(512)

    83 );

    84 

    85 CREATE TABLE Person(

    86  nom varchar(64) NOT NULL,

    87  prenom varchar(64),

    88  sexe varchar(1) DEFAULT 'M',

    89  promo varchar(6),

    90  titre varchar(128),

    91  adel varchar(128),

    92  ass varchar(128),

    93  web varchar(128),

    94  tel integer,

    95  fax integer,

    96  datenaiss date,

    97  test boolean,

    98  salary float

    99 );

   100 CREATE UNIQUE INDEX unique_e6c2d219772dbf1715597f7d9a6b3892 ON Person(nom,prenom);

   101 

   102 CREATE TABLE Salaried(

   103  nom varchar(64) NOT NULL,

   104  prenom varchar(64),

   105  sexe varchar(1) DEFAULT 'M',

   106  promo varchar(6),

   107  titre varchar(128),

   108  adel varchar(128),

   109  ass varchar(128),

   110  web varchar(128),

   111  tel integer,

   112  fax integer,

   113  datenaiss date,

   114  test boolean,

   115  salary float

   116 );

   117 CREATE UNIQUE INDEX unique_98da0f9de8588baa8966f0b1a6f850a3 ON Salaried(nom,prenom);

   118 

   119 CREATE TABLE Societe(

   120  nom varchar(64),

   121  web varchar(128),

   122  tel integer,

   123  fax integer,

   124  rncs varchar(32),

   125  ad1 varchar(128),

   126  ad2 varchar(128),

   127  ad3 varchar(128),

   128  cp varchar(12),

   129  ville varchar(32)

   130 );

   131 

   132 CREATE TABLE State(

   133  eid integer PRIMARY KEY,

   134  name varchar(256) NOT NULL,

   135  description text

   136 );

   137 CREATE INDEX state_name_idx ON State(name);

   138 

   139 CREATE TABLE Subcompany(

   140  name text

   141 );

   142 

   143 CREATE TABLE Subdivision(

   144  name text

   145 );

   146 

   147 CREATE TABLE pkginfo(

   148  modname varchar(30) NOT NULL,

   149  version varchar(10) DEFAULT '0.1' NOT NULL,

   150  copyright text NOT NULL,

   151  license varchar(3),

   152  short_desc varchar(80) NOT NULL,

   153  long_desc text NOT NULL,

   154  author varchar(100) NOT NULL,

   155  author_email varchar(100) NOT NULL,

   156  mailinglist varchar(100),

   157  debian_handler varchar(6)

   158 );

   159 

   160 

   161 CREATE TABLE concerne_relation (

   162   eid_from INTEGER NOT NULL,

   163   eid_to INTEGER NOT NULL,

   164   CONSTRAINT concerne_relation_p_key PRIMARY KEY(eid_from, eid_to)

   165 );

   166 

   167 CREATE INDEX concerne_relation_from_idx ON concerne_relation(eid_from);

   168 CREATE INDEX concerne_relation_to_idx ON concerne_relation(eid_to);

   169 

   170 CREATE TABLE division_of_relation (

   171   eid_from INTEGER NOT NULL,

   172   eid_to INTEGER NOT NULL,

   173   CONSTRAINT division_of_relation_p_key PRIMARY KEY(eid_from, eid_to)

   174 );

   175 

   176 CREATE INDEX division_of_relation_from_idx ON division_of_relation(eid_from);

   177 CREATE INDEX division_of_relation_to_idx ON division_of_relation(eid_to);

   178 

   179 CREATE TABLE evaluee_relation (

   180   eid_from INTEGER NOT NULL,

   181   eid_to INTEGER NOT NULL,

   182   CONSTRAINT evaluee_relation_p_key PRIMARY KEY(eid_from, eid_to)

   183 );

   184 

   185 CREATE INDEX evaluee_relation_from_idx ON evaluee_relation(eid_from);

   186 CREATE INDEX evaluee_relation_to_idx ON evaluee_relation(eid_to);

   187 

   188 CREATE TABLE next_state_relation (

   189   eid_from INTEGER NOT NULL,

   190   eid_to INTEGER NOT NULL,

   191   CONSTRAINT next_state_relation_p_key PRIMARY KEY(eid_from, eid_to)

   192 );

   193 

   194 CREATE INDEX next_state_relation_from_idx ON next_state_relation(eid_from);

   195 CREATE INDEX next_state_relation_to_idx ON next_state_relation(eid_to);

   196 

   197 CREATE TABLE obj_wildcard_relation (

   198   eid_from INTEGER NOT NULL,

   199   eid_to INTEGER NOT NULL,

   200   CONSTRAINT obj_wildcard_relation_p_key PRIMARY KEY(eid_from, eid_to)

   201 );

   202 

   203 CREATE INDEX obj_wildcard_relation_from_idx ON obj_wildcard_relation(eid_from);

   204 CREATE INDEX obj_wildcard_relation_to_idx ON obj_wildcard_relation(eid_to);

   205 

   206 CREATE TABLE require_permission_relation (

   207   eid_from INTEGER NOT NULL,

   208   eid_to INTEGER NOT NULL,

   209   CONSTRAINT require_permission_relation_p_key PRIMARY KEY(eid_from, eid_to)

   210 );

   211 

   212 CREATE INDEX require_permission_relation_from_idx ON require_permission_relation(eid_from);

   213 CREATE INDEX require_permission_relation_to_idx ON require_permission_relation(eid_to);

   214 

   215 CREATE TABLE state_of_relation (

   216   eid_from INTEGER NOT NULL,

   217   eid_to INTEGER NOT NULL,

   218   CONSTRAINT state_of_relation_p_key PRIMARY KEY(eid_from, eid_to)

   219 );

   220 

   221 CREATE INDEX state_of_relation_from_idx ON state_of_relation(eid_from);

   222 CREATE INDEX state_of_relation_to_idx ON state_of_relation(eid_to);

   223 

   224 CREATE TABLE subcompany_of_relation (

   225   eid_from INTEGER NOT NULL,

   226   eid_to INTEGER NOT NULL,

   227   CONSTRAINT subcompany_of_relation_p_key PRIMARY KEY(eid_from, eid_to)

   228 );

   229 

   230 CREATE INDEX subcompany_of_relation_from_idx ON subcompany_of_relation(eid_from);

   231 CREATE INDEX subcompany_of_relation_to_idx ON subcompany_of_relation(eid_to);

   232 

   233 CREATE TABLE subdivision_of_relation (

   234   eid_from INTEGER NOT NULL,

   235   eid_to INTEGER NOT NULL,

   236   CONSTRAINT subdivision_of_relation_p_key PRIMARY KEY(eid_from, eid_to)

   237 );

   238 

   239 CREATE INDEX subdivision_of_relation_from_idx ON subdivision_of_relation(eid_from);

   240 CREATE INDEX subdivision_of_relation_to_idx ON subdivision_of_relation(eid_to);

   241 

   242 CREATE TABLE subj_wildcard_relation (

   243   eid_from INTEGER NOT NULL,

   244   eid_to INTEGER NOT NULL,

   245   CONSTRAINT subj_wildcard_relation_p_key PRIMARY KEY(eid_from, eid_to)

   246 );

   247 

   248 CREATE INDEX subj_wildcard_relation_from_idx ON subj_wildcard_relation(eid_from);

   249 CREATE INDEX subj_wildcard_relation_to_idx ON subj_wildcard_relation(eid_to);

   250 

   251 CREATE TABLE sym_rel_relation (

   252   eid_from INTEGER NOT NULL,

   253   eid_to INTEGER NOT NULL,

   254   CONSTRAINT sym_rel_relation_p_key PRIMARY KEY(eid_from, eid_to)

   255 );

   256 

   257 CREATE INDEX sym_rel_relation_from_idx ON sym_rel_relation(eid_from);

   258 CREATE INDEX sym_rel_relation_to_idx ON sym_rel_relation(eid_to);

   259 

   260 CREATE TABLE travaille_relation (

   261   eid_from INTEGER NOT NULL,

   262   eid_to INTEGER NOT NULL,

   263   CONSTRAINT travaille_relation_p_key PRIMARY KEY(eid_from, eid_to)

   264 );

   265 

   266 CREATE INDEX travaille_relation_from_idx ON travaille_relation(eid_from);

   267 CREATE INDEX travaille_relation_to_idx ON travaille_relation(eid_to);

   268 

   269 CREATE TABLE works_for_relation (

   270   eid_from INTEGER NOT NULL,

   271   eid_to INTEGER NOT NULL,

   272   CONSTRAINT works_for_relation_p_key PRIMARY KEY(eid_from, eid_to)

   273 );

   274 

   275 CREATE INDEX works_for_relation_from_idx ON works_for_relation(eid_from);

   276 CREATE INDEX works_for_relation_to_idx ON works_for_relation(eid_to);

   277 """

   278 

   279 class SQLSchemaTC(TestCase):

   280 

   281     def test_known_values(self):

   282         dbhelper = get_db_helper('postgres')

   283         output = schema2sql.schema2sql(dbhelper, schema)

   284         self.assertMultiLineEqual(EXPECTED_DATA_NO_DROP.strip(), output.strip())

   285 

   286 

   287 if __name__ == '__main__':

   288     unittest_main()