server/sqlutils.py
author Sylvain Thénault <sylvain.thenault@logilab.fr>
Tue, 07 Sep 2010 22:58:31 +0200
branchstable
changeset 6184 da580218a5b3
parent 5768 1e73a466aa69
child 6142 8bc6eac1fac1
child 6284 c35b2ebeb3c9
permissions -rw-r--r--
[c-c db-check] add missing import
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
5421
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
     1
# copyright 2003-2010 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
     2
# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
     3
#
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
     4
# This file is part of CubicWeb.
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
     5
#
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
     6
# CubicWeb is free software: you can redistribute it and/or modify it under the
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
     7
# terms of the GNU Lesser General Public License as published by the Free
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
     8
# Software Foundation, either version 2.1 of the License, or (at your option)
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
     9
# any later version.
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
    10
#
5424
8ecbcbff9777 replace logilab-common by CubicWeb in disclaimer
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5421
diff changeset
    11
# CubicWeb is distributed in the hope that it will be useful, but WITHOUT
5421
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
    12
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
    13
# FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for more
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
    14
# details.
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
    15
#
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
    16
# You should have received a copy of the GNU Lesser General Public License along
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5214
diff changeset
    17
# with CubicWeb.  If not, see <http://www.gnu.org/licenses/>.
5768
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5625
diff changeset
    18
"""SQL utilities functions and classes."""
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    19
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    20
__docformat__ = "restructuredtext en"
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    21
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
    22
import os
4298
2ca56131079e enable cubicweb-ctl db-dump and db-restore on Windows with SQL Server
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents: 4212
diff changeset
    23
import subprocess
4719
aaed3f813ef8 kill dead/useless code as suggested by pylint
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4622
diff changeset
    24
from datetime import datetime, date
1016
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
    25
4849
3827b9ee77ac missing rename
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4848
diff changeset
    26
from logilab import database as db, common as lgc
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    27
from logilab.common.shellutils import ProgressBar
4466
8b0ca7904820 moved generic datetime manipulation function to lgc
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4436
diff changeset
    28
from logilab.common.date import todate, todatetime
4848
41f84eea63c9 rename logilab.db into logilab.database
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4831
diff changeset
    29
from logilab.database.sqlgen import SQLGenerator
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    30
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    31
from cubicweb import Binary, ConfigurationError
4023
eae23c40627a drop common subpackage
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3689
diff changeset
    32
from cubicweb.uilib import remove_html_tags
2596
d02eed70937f [R repo, schema] use VIRTUAL_RTYPES const
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2512
diff changeset
    33
from cubicweb.schema import PURE_VIRTUAL_RTYPES
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    34
from cubicweb.server import SQL_CONNECT_HOOKS
1132
96752791c2b6 pylint cleanup
sylvain.thenault@logilab.fr
parents: 1026
diff changeset
    35
from cubicweb.server.utils import crypt_password
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
    36
from rql.utils import RQL_FUNCTIONS_REGISTRY
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    37
1783
b81f9761907c mx flag has moved
sylvain.thenault@logilab.fr
parents: 1619
diff changeset
    38
lgc.USE_MX_DATETIME = False
1251
af40e615dc89 introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents: 0
diff changeset
    39
SQL_PREFIX = 'cw_'
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    40
4353
7db69db4913c command may now officially be either a string or a list, don't make think it's for backward compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4342
diff changeset
    41
def _run_command(cmd):
4342
b4e186da08f2 handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4322
diff changeset
    42
    """backup/restore command are string w/ lgc < 0.47, lists with earlier versions
b4e186da08f2 handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4322
diff changeset
    43
    """
b4e186da08f2 handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4322
diff changeset
    44
    if isinstance(cmd, basestring):
4353
7db69db4913c command may now officially be either a string or a list, don't make think it's for backward compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4342
diff changeset
    45
        print '->', cmd
7db69db4913c command may now officially be either a string or a list, don't make think it's for backward compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4342
diff changeset
    46
        return subprocess.call(cmd, shell=True)
4342
b4e186da08f2 handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4322
diff changeset
    47
    print ' '.join(cmd)
b4e186da08f2 handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4322
diff changeset
    48
    return subprocess.call(cmd)
b4e186da08f2 handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4322
diff changeset
    49
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    50
3623
9b838e2d72bb avoid progress bar when test launched from apycot
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2759
diff changeset
    51
def sqlexec(sqlstmts, cursor_or_execute, withpb=not os.environ.get('APYCOT_ROOT'),
9b838e2d72bb avoid progress bar when test launched from apycot
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2759
diff changeset
    52
            pbtitle='', delimiter=';'):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    53
    """execute sql statements ignoring DROP/ CREATE GROUP or USER statements
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    54
    error. If a cnx is given, commit at each statement
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    55
    """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    56
    if hasattr(cursor_or_execute, 'execute'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    57
        execute = cursor_or_execute.execute
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    58
    else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    59
        execute = cursor_or_execute
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    60
    sqlstmts = sqlstmts.split(delimiter)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    61
    if withpb:
2396
8bfb99d7bbcc [cw-ctl] improve dialog messages
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 1977
diff changeset
    62
        pb = ProgressBar(len(sqlstmts), title=pbtitle)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    63
    for sql in sqlstmts:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    64
        sql = sql.strip()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    65
        if withpb:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    66
            pb.update()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    67
        if not sql:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    68
            continue
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    69
        # some dbapi modules doesn't accept unicode for sql string
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    70
        execute(str(sql))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    71
    if withpb:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    72
        print
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    73
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    74
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    75
def sqlgrants(schema, driver, user,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    76
              text_index=True, set_owner=True,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    77
              skip_relations=(), skip_entities=()):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    78
    """return sql to give all access privileges to the given user on the system
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    79
    schema
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    80
    """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    81
    from yams.schema2sql import grant_schema
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    82
    from cubicweb.server.sources import native
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    83
    output = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    84
    w = output.append
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    85
    w(native.grant_schema(user, set_owner))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    86
    w('')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    87
    if text_index:
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
    88
        dbhelper = db.get_db_helper(driver)
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
    89
        w(dbhelper.sql_grant_user_on_fti(user))
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    90
        w('')
1251
af40e615dc89 introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents: 0
diff changeset
    91
    w(grant_schema(schema, user, set_owner, skip_entities=skip_entities, prefix=SQL_PREFIX))
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    92
    return '\n'.join(output)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    93
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
    94
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
    95
def sqlschema(schema, driver, text_index=True,
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    96
              user=None, set_owner=False,
2596
d02eed70937f [R repo, schema] use VIRTUAL_RTYPES const
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2512
diff changeset
    97
              skip_relations=PURE_VIRTUAL_RTYPES, skip_entities=()):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    98
    """return the system sql schema, according to the given parameters"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    99
    from yams.schema2sql import schema2sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   100
    from cubicweb.server.sources import native
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   101
    if set_owner:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   102
        assert user, 'user is argument required when set_owner is true'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   103
    output = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   104
    w = output.append
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   105
    w(native.sql_schema(driver))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   106
    w('')
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   107
    dbhelper = db.get_db_helper(driver)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   108
    if text_index:
4913
083b4d454192 server/web api for accessing to deleted_entites
Katia Saurfelt <katia.saurfelt@logilab.fr>
parents: 4899
diff changeset
   109
        w(dbhelper.sql_init_fti().replace(';', ';;'))
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   110
        w('')
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   111
    w(schema2sql(dbhelper, schema, prefix=SQL_PREFIX,
4913
083b4d454192 server/web api for accessing to deleted_entites
Katia Saurfelt <katia.saurfelt@logilab.fr>
parents: 4899
diff changeset
   112
                 skip_entities=skip_entities,
083b4d454192 server/web api for accessing to deleted_entites
Katia Saurfelt <katia.saurfelt@logilab.fr>
parents: 4899
diff changeset
   113
                 skip_relations=skip_relations).replace(';', ';;'))
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   114
    if dbhelper.users_support and user:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   115
        w('')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   116
        w(sqlgrants(schema, driver, user, text_index, set_owner,
4913
083b4d454192 server/web api for accessing to deleted_entites
Katia Saurfelt <katia.saurfelt@logilab.fr>
parents: 4899
diff changeset
   117
                    skip_relations, skip_entities).replace(';', ';;'))
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   118
    return '\n'.join(output)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   119
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   120
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   121
def sqldropschema(schema, driver, text_index=True,
2596
d02eed70937f [R repo, schema] use VIRTUAL_RTYPES const
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2512
diff changeset
   122
                  skip_relations=PURE_VIRTUAL_RTYPES, skip_entities=()):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   123
    """return the sql to drop the schema, according to the given parameters"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   124
    from yams.schema2sql import dropschema2sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   125
    from cubicweb.server.sources import native
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   126
    output = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   127
    w = output.append
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   128
    if text_index:
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   129
        dbhelper = db.get_db_helper(driver)
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   130
        w(dbhelper.sql_drop_fti())
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   131
        w('')
1251
af40e615dc89 introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   132
    w(dropschema2sql(schema, prefix=SQL_PREFIX,
1954
9b20f3504af8 cleanup
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1953
diff changeset
   133
                     skip_entities=skip_entities,
9b20f3504af8 cleanup
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1953
diff changeset
   134
                     skip_relations=skip_relations))
5214
3285b6e3b930 fix cwctl db-init -d on SQL Server
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents: 5013
diff changeset
   135
    w('')
3285b6e3b930 fix cwctl db-init -d on SQL Server
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents: 5013
diff changeset
   136
    w(native.sql_drop_schema(driver))
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   137
    return '\n'.join(output)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   138
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   139
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   140
class SQLAdapterMixIn(object):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   141
    """Mixin for SQL data sources, getting a connection from a configuration
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   142
    dictionary and handling connection locking
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   143
    """
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   144
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   145
    def __init__(self, source_config):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   146
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   147
            self.dbdriver = source_config['db-driver'].lower()
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   148
            dbname = source_config['db-name']
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   149
        except KeyError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   150
            raise ConfigurationError('missing some expected entries in sources file')
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   151
        dbhost = source_config.get('db-host')
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   152
        port = source_config.get('db-port')
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   153
        dbport = port and int(port) or None
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   154
        dbuser = source_config.get('db-user')
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   155
        dbpassword = source_config.get('db-password')
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   156
        dbencoding = source_config.get('db-encoding', 'UTF-8')
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   157
        dbextraargs = source_config.get('db-extra-arguments')
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   158
        self.dbhelper = db.get_db_helper(self.dbdriver)
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   159
        self.dbhelper.record_connection_info(dbname, dbhost, dbport, dbuser,
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   160
                                             dbpassword, dbextraargs,
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   161
                                             dbencoding)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   162
        self.sqlgen = SQLGenerator()
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   163
        # copy back some commonly accessed attributes
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   164
        dbapi_module = self.dbhelper.dbapi_module
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   165
        self.OperationalError = dbapi_module.OperationalError
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   166
        self.InterfaceError = dbapi_module.InterfaceError
5605
2604545d7dd9 [win32 SQLServer] connection lost detection
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents: 5424
diff changeset
   167
        self.DbapiError = dbapi_module.Error
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   168
        self._binary = dbapi_module.Binary
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   169
        self._process_value = dbapi_module.process_value
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   170
        self._dbencoding = dbencoding
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   171
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   172
    def get_connection(self):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   173
        """open and return a connection to the database"""
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   174
        return self.dbhelper.get_connection()
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   175
4893
15ae9a33a7f2 [db backup] fix name error in backup_to_file: we've to pass .confirm all along the chain as for restore
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4854
diff changeset
   176
    def backup_to_file(self, backupfile, confirm):
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   177
        for cmd in self.dbhelper.backup_commands(backupfile,
4342
b4e186da08f2 handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4322
diff changeset
   178
                                                 keepownership=False):
4353
7db69db4913c command may now officially be either a string or a list, don't make think it's for backward compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4342
diff changeset
   179
            if _run_command(cmd):
7db69db4913c command may now officially be either a string or a list, don't make think it's for backward compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4342
diff changeset
   180
                if not confirm('   [Failed] Continue anyway?', default='n'):
4342
b4e186da08f2 handle lgc.adbh api changes within regards of backup/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4322
diff changeset
   181
                    raise Exception('Failed command: %s' % cmd)
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   182
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   183
    def restore_from_file(self, backupfile, confirm, drop=True):
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   184
        for cmd in self.dbhelper.restore_commands(backupfile,
4854
b06d2a3b27d9 logilab.db compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   185
                                                  keepownership=False,
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   186
                                                  drop=drop):
4353
7db69db4913c command may now officially be either a string or a list, don't make think it's for backward compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4342
diff changeset
   187
            if _run_command(cmd):
7db69db4913c command may now officially be either a string or a list, don't make think it's for backward compat
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4342
diff changeset
   188
                if not confirm('   [Failed] Continue anyway?', default='n'):
4195
86dcaf6bb92f closes #601987
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4179
diff changeset
   189
                    raise Exception('Failed command: %s' % cmd)
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   190
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   191
    def merge_args(self, args, query_args):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   192
        if args is not None:
4474
55fe19813bb7 kill mx compat code (dropped since 3.2), more efficient merge_args implementation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4466
diff changeset
   193
            newargs = {}
55fe19813bb7 kill mx compat code (dropped since 3.2), more efficient merge_args implementation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4466
diff changeset
   194
            for key, val in args.iteritems():
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   195
                # convert cubicweb binary into db binary
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   196
                if isinstance(val, Binary):
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   197
                    val = self._binary(val.getvalue())
4474
55fe19813bb7 kill mx compat code (dropped since 3.2), more efficient merge_args implementation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4466
diff changeset
   198
                newargs[key] = val
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   199
            # should not collide
4474
55fe19813bb7 kill mx compat code (dropped since 3.2), more efficient merge_args implementation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4466
diff changeset
   200
            newargs.update(query_args)
55fe19813bb7 kill mx compat code (dropped since 3.2), more efficient merge_args implementation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4466
diff changeset
   201
            return newargs
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   202
        return query_args
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   203
5625
6ee2a7b6f194 [external storage] refactor to give session to storage's callback (needed by vcsfile storage)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5605
diff changeset
   204
    def process_result(self, cursor, column_callbacks=None, session=None):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   205
        """return a list of CubicWeb compliant values from data in the given cursor
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   206
        """
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   207
        # use two different implementations to avoid paying the price of
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   208
        # callback lookup for each *cell* in results when there is nothing to
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   209
        # lookup
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   210
        if not column_callbacks:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   211
            return self._process_result(cursor)
5625
6ee2a7b6f194 [external storage] refactor to give session to storage's callback (needed by vcsfile storage)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5605
diff changeset
   212
        assert session
6ee2a7b6f194 [external storage] refactor to give session to storage's callback (needed by vcsfile storage)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5605
diff changeset
   213
        return self._cb_process_result(cursor, column_callbacks, session)
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   214
5625
6ee2a7b6f194 [external storage] refactor to give session to storage's callback (needed by vcsfile storage)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5605
diff changeset
   215
    def _process_result(self, cursor):
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   216
        # begin bind to locals for optimization
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   217
        descr = cursor.description
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   218
        encoding = self._dbencoding
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   219
        process_value = self._process_value
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   220
        binary = Binary
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   221
        # /end
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   222
        results = cursor.fetchall()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   223
        for i, line in enumerate(results):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   224
            result = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   225
            for col, value in enumerate(line):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   226
                if value is None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   227
                    result.append(value)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   228
                    continue
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   229
                result.append(process_value(value, descr[col], encoding, binary))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   230
            results[i] = result
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   231
        return results
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   232
5625
6ee2a7b6f194 [external storage] refactor to give session to storage's callback (needed by vcsfile storage)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5605
diff changeset
   233
    def _cb_process_result(self, cursor, column_callbacks, session):
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   234
        # begin bind to locals for optimization
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   235
        descr = cursor.description
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   236
        encoding = self._dbencoding
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   237
        process_value = self._process_value
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   238
        binary = Binary
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   239
        # /end
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   240
        results = cursor.fetchall()
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   241
        for i, line in enumerate(results):
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   242
            result = []
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   243
            for col, value in enumerate(line):
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   244
                if value is None:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   245
                    result.append(value)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   246
                    continue
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   247
                cbstack = column_callbacks.get(col, None)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   248
                if cbstack is None:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   249
                    value = process_value(value, descr[col], encoding, binary)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   250
                else:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   251
                    for cb in cbstack:
5625
6ee2a7b6f194 [external storage] refactor to give session to storage's callback (needed by vcsfile storage)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5605
diff changeset
   252
                        value = cb(self, session, value)
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   253
                result.append(value)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   254
            results[i] = result
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   255
        return results
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4965
diff changeset
   256
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   257
    def preprocess_entity(self, entity):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   258
        """return a dictionary to use as extra argument to cursor.execute
1251
af40e615dc89 introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   259
        to insert/update an entity into a SQL database
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   260
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   261
        attrs = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   262
        eschema = entity.e_schema
4965
04543ed0bbdc [source] only consider edited_attributes in source.preprocess_entity()
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 4913
diff changeset
   263
        for attr in entity.edited_attributes:
04543ed0bbdc [source] only consider edited_attributes in source.preprocess_entity()
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 4913
diff changeset
   264
            value = entity[attr]
3689
deb13e88e037 follow yams 0.25 api changes to improve performance
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3623
diff changeset
   265
            rschema = eschema.subjrels[attr]
deb13e88e037 follow yams 0.25 api changes to improve performance
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3623
diff changeset
   266
            if rschema.final:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   267
                atype = str(entity.e_schema.destination(attr))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   268
                if atype == 'Boolean':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   269
                    value = self.dbhelper.boolean_value(value)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   270
                elif atype == 'Password':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   271
                    # if value is a Binary instance, this mean we got it
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   272
                    # from a query result and so it is already encrypted
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   273
                    if isinstance(value, Binary):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   274
                        value = value.getvalue()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   275
                    else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   276
                        value = crypt_password(value)
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   277
                    value = self._binary(value)
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   278
                # XXX needed for sqlite but I don't think it is for other backends
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   279
                elif atype == 'Datetime' and isinstance(value, date):
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   280
                    value = todatetime(value)
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   281
                elif atype == 'Date' and isinstance(value, datetime):
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   282
                    value = todate(value)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   283
                elif isinstance(value, Binary):
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   284
                    value = self._binary(value.getvalue())
1251
af40e615dc89 introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   285
            attrs[SQL_PREFIX+str(attr)] = value
4965
04543ed0bbdc [source] only consider edited_attributes in source.preprocess_entity()
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 4913
diff changeset
   286
        attrs[SQL_PREFIX+'eid'] = entity.eid
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   287
        return attrs
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   288
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   289
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   290
from logging import getLogger
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   291
from cubicweb import set_log_methods
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   292
set_log_methods(SQLAdapterMixIn, getLogger('cubicweb.sqladapter'))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   293
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   294
def init_sqlite_connexion(cnx):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   295
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   296
    class group_concat(object):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   297
        def __init__(self):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   298
            self.values = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   299
        def step(self, value):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   300
            if value is not None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   301
                self.values.append(value)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   302
        def finalize(self):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   303
            return ', '.join(self.values)
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   304
    cnx.create_aggregate("GROUP_CONCAT", 1, group_concat)
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   305
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   306
    def _limit_size(text, maxsize, format='text/plain'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   307
        if len(text) < maxsize:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   308
            return text
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   309
        if format in ('text/html', 'text/xhtml', 'text/xml'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   310
            text = remove_html_tags(text)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   311
        if len(text) > maxsize:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   312
            text = text[:maxsize] + '...'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   313
        return text
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   314
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   315
    def limit_size3(text, format, maxsize):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   316
        return _limit_size(text, maxsize, format)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   317
    cnx.create_function("LIMIT_SIZE", 3, limit_size3)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   318
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   319
    def limit_size2(text, maxsize):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   320
        return _limit_size(text, maxsize)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   321
    cnx.create_function("TEXT_LIMIT_SIZE", 2, limit_size2)
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   322
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   323
    import yams.constraints
4831
c5aec27c1bf7 [repo] use logilab.db instead of lgc.adbh/lgc.db/lgc.sqlgen/indexer, test new date extranction functions
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   324
    yams.constraints.patch_sqlite_decimal()
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   325
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   326
sqlite_hooks = SQL_CONNECT_HOOKS.setdefault('sqlite', [])
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   327
sqlite_hooks.append(init_sqlite_connexion)