server/schema2sql.py
author David Douard <david.douard@logilab.fr>
Fri, 03 Jul 2015 15:23:14 +0200
changeset 10498 36e48f7ac61a
parent 10481 6ac4b1726e9f
child 10671 e08102f45237
permissions -rw-r--r--
[predicates] remove deprecated sentences in docstrings
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
10481
6ac4b1726e9f [schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10444
diff changeset
     1
# copyright 2004-2015 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
10199
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
     2
# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
     3
#
10481
6ac4b1726e9f [schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10444
diff changeset
     4
# This file is part of cubicweb.
10199
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
     5
#
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
     6
# yams is free software: you can redistribute it and/or modify it under the
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
     7
# terms of the GNU Lesser General Public License as published by the Free
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
     8
# Software Foundation, either version 2.1 of the License, or (at your option)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
     9
# any later version.
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    10
#
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    11
# yams is distributed in the hope that it will be useful, but WITHOUT ANY
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    12
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    13
# A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for more
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    14
# details.
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    15
#
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    16
# You should have received a copy of the GNU Lesser General Public License along
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    17
# with yams. If not, see <http://www.gnu.org/licenses/>.
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    18
"""write a schema as sql"""
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    19
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    20
__docformat__ = "restructuredtext en"
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    21
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    22
from hashlib import md5
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    23
10443
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
    24
from six import string_types
10199
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    25
from six.moves import range
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    26
10444
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
    27
from yams.constraints import (SizeConstraint, UniqueConstraint, Attribute,
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
    28
                              NOW, TODAY)
10199
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    29
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    30
# default are usually not handled at the sql level. If you want them, set
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    31
# SET_DEFAULT to True
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    32
SET_DEFAULT = False
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    33
10481
6ac4b1726e9f [schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10444
diff changeset
    34
def rschema_has_table(rschema, skip_relations):
6ac4b1726e9f [schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10444
diff changeset
    35
    """Return True if the given schema should have a table in the database"""
6ac4b1726e9f [schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10444
diff changeset
    36
    return not (rschema.final or rschema.inlined or rschema.rule or rschema.type in skip_relations)
6ac4b1726e9f [schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10444
diff changeset
    37
10199
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    38
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    39
def schema2sql(dbhelper, schema, skip_entities=(), skip_relations=(), prefix=''):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    40
    """write to the output stream a SQL schema to store the objects
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    41
    corresponding to the given schema
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    42
    """
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    43
    output = []
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    44
    w = output.append
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    45
    for etype in sorted(schema.entities()):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    46
        eschema = schema.eschema(etype)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    47
        if eschema.final or eschema.type in skip_entities:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    48
            continue
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    49
        w(eschema2sql(dbhelper, eschema, skip_relations, prefix=prefix))
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    50
    for rtype in sorted(schema.relations()):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    51
        rschema = schema.rschema(rtype)
10481
6ac4b1726e9f [schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10444
diff changeset
    52
        if rschema_has_table(rschema, skip_relations):
6ac4b1726e9f [schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10444
diff changeset
    53
            w(rschema2sql(rschema))
10199
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    54
    return '\n'.join(output)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    55
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    56
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    57
def dropschema2sql(dbhelper, schema, skip_entities=(), skip_relations=(), prefix=''):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    58
    """write to the output stream a SQL schema to store the objects
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    59
    corresponding to the given schema
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    60
    """
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    61
    output = []
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    62
    w = output.append
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    63
    for etype in sorted(schema.entities()):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    64
        eschema = schema.eschema(etype)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    65
        if eschema.final or eschema.type in skip_entities:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    66
            continue
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    67
        stmts = dropeschema2sql(dbhelper, eschema, skip_relations, prefix=prefix)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    68
        for stmt in stmts:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    69
            w(stmt)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    70
    for rtype in sorted(schema.relations()):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    71
        rschema = schema.rschema(rtype)
10481
6ac4b1726e9f [schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10444
diff changeset
    72
        if rschema_has_table(rschema, skip_relations):
6ac4b1726e9f [schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10444
diff changeset
    73
            w(droprschema2sql(rschema))
10199
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    74
    return '\n'.join(output)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    75
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    76
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    77
def eschema_attrs(eschema, skip_relations):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    78
    attrs = [attrdef for attrdef in eschema.attribute_definitions()
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    79
             if not attrdef[0].type in skip_relations]
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    80
    attrs += [(rschema, None)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    81
              for rschema in eschema.subject_relations()
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    82
              if not rschema.final and rschema.inlined]
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    83
    return attrs
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    84
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    85
def unique_index_name(eschema, columns):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    86
    return u'unique_%s' % md5((eschema.type +
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    87
                              ',' +
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    88
                              ','.join(sorted(columns))).encode('ascii')).hexdigest()
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    89
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    90
def iter_unique_index_names(eschema):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    91
    for columns in eschema._unique_together or ():
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    92
        yield columns, unique_index_name(eschema, columns)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    93
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    94
def dropeschema2sql(dbhelper, eschema, skip_relations=(), prefix=''):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    95
    """return sql to drop an entity type's table"""
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    96
    # not necessary to drop indexes, that's implictly done when
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    97
    # dropping the table, but we need to drop SQLServer views used to
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    98
    # create multicol unique indices
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
    99
    statements = []
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   100
    tablename = prefix + eschema.type
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   101
    if eschema._unique_together is not None:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   102
        for columns, index_name in iter_unique_index_names(eschema):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   103
            cols  = ['%s%s' % (prefix, col) for col in columns]
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   104
            sqls = dbhelper.sqls_drop_multicol_unique_index(tablename, cols, index_name)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   105
            statements += sqls
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   106
    statements += ['DROP TABLE %s;' % (tablename)]
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   107
    return statements
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   108
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   109
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   110
def eschema2sql(dbhelper, eschema, skip_relations=(), prefix=''):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   111
    """write an entity schema as SQL statements to stdout"""
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   112
    output = []
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   113
    w = output.append
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   114
    table = prefix + eschema.type
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   115
    w('CREATE TABLE %s(' % (table))
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   116
    attrs = eschema_attrs(eschema, skip_relations)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   117
    # XXX handle objectinline physical mode
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   118
    for i in range(len(attrs)):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   119
        rschema, attrschema = attrs[i]
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   120
        if attrschema is not None:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   121
            sqltype = aschema2sql(dbhelper, eschema, rschema, attrschema,
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   122
                                  indent=' ')
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   123
        else: # inline relation
10201
989bbadbcd8d Add foreign key for inline relations
Julien Cristau <julien.cristau@logilab.fr>
parents: 10199
diff changeset
   124
            sqltype = 'integer REFERENCES entities (eid)'
10199
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   125
        if i == len(attrs) - 1:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   126
            w(' %s%s %s' % (prefix, rschema.type, sqltype))
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   127
        else:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   128
            w(' %s%s %s,' % (prefix, rschema.type, sqltype))
10443
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   129
    for rschema, aschema in attrs:
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   130
        if aschema is None:  # inline relation
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   131
            continue
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   132
        attr = rschema.type
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   133
        rdef = rschema.rdef(eschema.type, aschema.type)
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   134
        for constraint in rdef.constraints:
10444
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   135
            cstrname, check = check_constraint(eschema, aschema, attr, constraint, dbhelper, prefix=prefix)
10443
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   136
            if cstrname is not None:
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   137
                w(', CONSTRAINT %s CHECK(%s)' % (cstrname, check))
10199
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   138
    w(');')
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   139
    # create indexes
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   140
    for i in range(len(attrs)):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   141
        rschema, attrschema = attrs[i]
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   142
        if attrschema is None or eschema.rdef(rschema).indexed:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   143
            w(dbhelper.sql_create_index(table, prefix + rschema.type))
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   144
    for columns, index_name in iter_unique_index_names(eschema):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   145
        cols  = ['%s%s' % (prefix, col) for col in columns]
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   146
        sqls = dbhelper.sqls_create_multicol_unique_index(table, cols, index_name)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   147
        for sql in sqls:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   148
            w(sql)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   149
    w('')
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   150
    return '\n'.join(output)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   151
10444
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   152
def as_sql(value, dbhelper, prefix):
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   153
    if isinstance(value, Attribute):
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   154
        return prefix + value.attr
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   155
    elif isinstance(value, TODAY):
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   156
        return dbhelper.sql_current_date()
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   157
    elif isinstance(value, NOW):
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   158
        return dbhelper.sql_current_timestamp()
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   159
    else:
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   160
        # XXX more quoting for literals?
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   161
        return value
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   162
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   163
def check_constraint(eschema, aschema, attr, constraint, dbhelper, prefix=''):
10443
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   164
    # XXX should find a better name
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   165
    cstrname = 'cstr' + md5(eschema.type + attr + constraint.type() +
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   166
                            (constraint.serialize() or '')).hexdigest()
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   167
    if constraint.type() == 'BoundaryConstraint':
10444
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   168
        value = as_sql(constraint.boundary, dbhelper, prefix)
10443
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   169
        return cstrname, '%s%s %s %s' % (prefix, attr, constraint.operator, value)
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   170
    elif constraint.type() == 'IntervalBoundConstraint':
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   171
        condition = []
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   172
        if constraint.minvalue is not None:
10444
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   173
            value = as_sql(constraint.minvalue, dbhelper, prefix)
10443
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   174
            condition.append('%s%s >= %s' % (prefix, attr, value))
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   175
        if constraint.maxvalue is not None:
10444
fb7c1013189e [schema2sql] support NOW and TODAY in check constraints
Julien Cristau <julien.cristau@logilab.fr>
parents: 10443
diff changeset
   176
            value = as_sql(constraint.maxvalue, dbhelper, prefix)
10443
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   177
            condition.append('%s%s <= %s' % (prefix, attr, value))
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   178
        return cstrname, ' AND '.join(condition)
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   179
    elif constraint.type() == 'StaticVocabularyConstraint':
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   180
        sample = next(iter(constraint.vocabulary()))
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   181
        if not isinstance(sample, string_types):
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   182
            values = ', '.join(str(word) for word in constraint.vocabulary())
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   183
        else:
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   184
            # XXX better quoting?
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   185
            values = ', '.join("'%s'" % word.replace("'", "''") for word in constraint.vocabulary())
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   186
        return cstrname, '%s%s IN (%s)' % (prefix, attr, values)
2d3834df64ab [schema2sql] insert some constraints into the backend
Julien Cristau <julien.cristau@logilab.fr>
parents: 10204
diff changeset
   187
    return None, None
10199
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   188
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   189
def aschema2sql(dbhelper, eschema, rschema, aschema, creating=True, indent=''):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   190
    """write an attribute schema as SQL statements to stdout"""
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   191
    attr = rschema.type
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   192
    rdef = rschema.rdef(eschema.type, aschema.type)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   193
    sqltype = type_from_constraints(dbhelper, aschema.type, rdef.constraints,
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   194
                                    creating)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   195
    if SET_DEFAULT:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   196
        default = eschema.default(attr)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   197
        if default is not None:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   198
            if aschema.type == 'Boolean':
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   199
                sqltype += ' DEFAULT %s' % dbhelper.boolean_value(default)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   200
            elif aschema.type == 'String':
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   201
                sqltype += ' DEFAULT %r' % str(default)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   202
            elif aschema.type in ('Int', 'BigInt', 'Float'):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   203
                sqltype += ' DEFAULT %s' % default
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   204
            # XXX ignore default for other type
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   205
            # this is expected for NOW / TODAY
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   206
    if creating:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   207
        if rdef.uid:
10204
f8ccae1e271d Add reference from etype table's eid column to the entities table
Julien Cristau <julien.cristau@logilab.fr>
parents: 10202
diff changeset
   208
            sqltype += ' PRIMARY KEY REFERENCES entities (eid)'
10199
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   209
        elif rdef.cardinality[0] == '1':
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   210
            # don't set NOT NULL if backend isn't able to change it later
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   211
            if dbhelper.alter_column_support:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   212
                sqltype += ' NOT NULL'
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   213
    # else we're getting sql type to alter a column, we don't want key / indexes
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   214
    # / null modifiers
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   215
    return sqltype
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   216
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   217
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   218
def type_from_constraints(dbhelper, etype, constraints, creating=True):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   219
    """return a sql type string corresponding to the constraints"""
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   220
    constraints = list(constraints)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   221
    unique, sqltype = False, None
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   222
    size_constrained_string = dbhelper.TYPE_MAPPING.get('SizeConstrainedString', 'varchar(%s)')
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   223
    if etype == 'String':
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   224
        for constraint in constraints:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   225
            if isinstance(constraint, SizeConstraint):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   226
                if constraint.max is not None:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   227
                    sqltype = size_constrained_string % constraint.max
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   228
            elif isinstance(constraint, UniqueConstraint):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   229
                unique = True
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   230
    if sqltype is None:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   231
        sqltype = dbhelper.TYPE_MAPPING[etype]
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   232
    if creating and unique:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   233
        sqltype += ' UNIQUE'
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   234
    return sqltype
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   235
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   236
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   237
_SQL_SCHEMA = """
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   238
CREATE TABLE %(table)s (
10202
aaabcb64f77f Use foreign keys for relations tables
Julien Cristau <julien.cristau@logilab.fr>
parents: 10201
diff changeset
   239
  eid_from INTEGER NOT NULL REFERENCES entities (eid),
aaabcb64f77f Use foreign keys for relations tables
Julien Cristau <julien.cristau@logilab.fr>
parents: 10201
diff changeset
   240
  eid_to INTEGER NOT NULL REFERENCES entities (eid),
10199
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   241
  CONSTRAINT %(table)s_p_key PRIMARY KEY(eid_from, eid_to)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   242
);
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   243
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   244
CREATE INDEX %(table)s_from_idx ON %(table)s(eid_from);
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   245
CREATE INDEX %(table)s_to_idx ON %(table)s(eid_to);"""
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   246
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   247
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   248
def rschema2sql(rschema):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   249
    assert not rschema.rule
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   250
    return _SQL_SCHEMA % {'table': '%s_relation' % rschema.type}
10481
6ac4b1726e9f [schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10444
diff changeset
   251
10199
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   252
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   253
def droprschema2sql(rschema):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   254
    """return sql to drop a relation type's table"""
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   255
    # not necessary to drop indexes, that's implictly done when dropping
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   256
    # the table
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   257
    return 'DROP TABLE %s_relation;' % rschema.type
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   258
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   259
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   260
def grant_schema(schema, user, set_owner=True, skip_entities=(), prefix=''):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   261
    """write to the output stream a SQL schema to store the objects
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   262
    corresponding to the given schema
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   263
    """
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   264
    output = []
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   265
    w = output.append
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   266
    for etype in sorted(schema.entities()):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   267
        eschema = schema.eschema(etype)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   268
        if eschema.final or etype in skip_entities:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   269
            continue
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   270
        w(grant_eschema(eschema, user, set_owner, prefix=prefix))
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   271
    for rtype in sorted(schema.relations()):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   272
        rschema = schema.rschema(rtype)
10481
6ac4b1726e9f [schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10444
diff changeset
   273
        if rschema_has_table(rschema, skip_relations=()):  # XXX skip_relations should be specified
6ac4b1726e9f [schema2sql] properly consider skip_relations parameters.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10444
diff changeset
   274
            w(grant_rschema(rschema, user, set_owner))
10199
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   275
    return '\n'.join(output)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   276
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   277
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   278
def grant_eschema(eschema, user, set_owner=True, prefix=''):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   279
    output = []
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   280
    w = output.append
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   281
    etype = eschema.type
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   282
    if set_owner:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   283
        w('ALTER TABLE %s%s OWNER TO %s;' % (prefix, etype, user))
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   284
    w('GRANT ALL ON %s%s TO %s;' % (prefix, etype, user))
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   285
    return '\n'.join(output)
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   286
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   287
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   288
def grant_rschema(rschema, user, set_owner=True):
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   289
    output = []
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   290
    if set_owner:
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   291
        output.append('ALTER TABLE %s_relation OWNER TO %s;' % (rschema.type, user))
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   292
    output.append('GRANT ALL ON %s_relation TO %s;' % (rschema.type, user))
218c28bff695 Steal schema2sql module from yams
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   293
    return '\n'.join(output)