server/sqlutils.py
author Sylvain Thénault <sylvain.thenault@logilab.fr>
Fri, 22 Jan 2010 08:49:16 +0100
changeset 4322 f65743cc53e4
parent 4314 e12ee4a009bc
child 4342 b4e186da08f2
permissions -rw-r--r--
first draft for a simple hooks based custom attribute storage, with a BytesFileSystemStorage POC implementation. Basically: * a dictionary contains maps from which attribute of which entity types are mapped to which custom storage * hooks check for one of these entity type being added/modified/deleted * read is based on the sql generator callback mecanism (used in vcsfile for instance) * all storages have the same basic interface (read, add, update, delete), and should be pluggable in a transparent way (except at migration time when one want to change from a storage to another) * the sample BytesFileSystemStorage: * may store Bytes attributes content of any entity type as file on the file system * is based on one FSPATH rql/sql function and another _fsopen only available in sql * has a dumb file name allocation algorithm
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     1
"""SQL utilities functions and classes.
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     2
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     3
:organization: Logilab
4212
ab6573088b4a update copyright: welcome 2010
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 4195
diff changeset
     4
:copyright: 2001-2010 LOGILAB S.A. (Paris, FRANCE), license is LGPL v2.
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     5
:contact: http://www.logilab.fr/ -- mailto:contact@logilab.fr
1977
606923dff11b big bunch of copyright / docstring update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1954
diff changeset
     6
:license: GNU Lesser General Public License, v2.1 - http://www.gnu.org/licenses
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     7
"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     8
__docformat__ = "restructuredtext en"
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     9
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
    10
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
    11
import subprocess
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
    12
from os.path import exists
1016
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
    13
from warnings import warn
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
    14
from datetime import datetime, date, timedelta
1016
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
    15
1783
b81f9761907c mx flag has moved
sylvain.thenault@logilab.fr
parents: 1619
diff changeset
    16
import logilab.common as lgc
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    17
from logilab.common.shellutils import ProgressBar
1016
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
    18
from logilab.common import db
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    19
from logilab.common.adbh import get_adv_func_helper
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    20
from logilab.common.sqlgen import SQLGenerator
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    21
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    22
from indexer import get_indexer
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    23
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    24
from cubicweb import Binary, ConfigurationError
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
    25
from cubicweb.utils import todate, todatetime
4023
eae23c40627a drop common subpackage
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3689
diff changeset
    26
from cubicweb.uilib import remove_html_tags
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
    27
from cubicweb.toolsutils import restrict_perms_to_user
2596
d02eed70937f [R repo, schema] use VIRTUAL_RTYPES const
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2512
diff changeset
    28
from cubicweb.schema import PURE_VIRTUAL_RTYPES
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    29
from cubicweb.server import SQL_CONNECT_HOOKS
1132
96752791c2b6 pylint cleanup
sylvain.thenault@logilab.fr
parents: 1026
diff changeset
    30
from cubicweb.server.utils import crypt_password
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    31
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    32
1783
b81f9761907c mx flag has moved
sylvain.thenault@logilab.fr
parents: 1619
diff changeset
    33
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
    34
SQL_PREFIX = 'cw_'
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    35
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    36
3623
9b838e2d72bb avoid progress bar when test launched from apycot
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2759
diff changeset
    37
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
    38
            pbtitle='', delimiter=';'):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    39
    """execute sql statements ignoring DROP/ CREATE GROUP or USER statements
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    40
    error. If a cnx is given, commit at each statement
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    41
    """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    42
    if hasattr(cursor_or_execute, 'execute'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    43
        execute = cursor_or_execute.execute
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    44
    else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    45
        execute = cursor_or_execute
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    46
    sqlstmts = sqlstmts.split(delimiter)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    47
    if withpb:
2396
8bfb99d7bbcc [cw-ctl] improve dialog messages
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 1977
diff changeset
    48
        pb = ProgressBar(len(sqlstmts), title=pbtitle)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    49
    for sql in sqlstmts:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    50
        sql = sql.strip()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    51
        if withpb:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    52
            pb.update()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    53
        if not sql:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    54
            continue
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    55
        # some dbapi modules doesn't accept unicode for sql string
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    56
        execute(str(sql))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    57
    if withpb:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    58
        print
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    59
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    60
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    61
def sqlgrants(schema, driver, user,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    62
              text_index=True, set_owner=True,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    63
              skip_relations=(), skip_entities=()):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    64
    """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
    65
    schema
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    66
    """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    67
    from yams.schema2sql import grant_schema
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    68
    from cubicweb.server.sources import native
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    69
    output = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    70
    w = output.append
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    71
    w(native.grant_schema(user, set_owner))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    72
    w('')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    73
    if text_index:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    74
        indexer = get_indexer(driver)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    75
        w(indexer.sql_grant_user(user))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    76
        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
    77
    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
    78
    return '\n'.join(output)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    79
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
    80
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
    81
def sqlschema(schema, driver, text_index=True,
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    82
              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
    83
              skip_relations=PURE_VIRTUAL_RTYPES, skip_entities=()):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    84
    """return the system sql schema, according to the given parameters"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    85
    from yams.schema2sql import schema2sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    86
    from cubicweb.server.sources import native
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    87
    if set_owner:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    88
        assert user, 'user is argument required when set_owner is true'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    89
    output = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    90
    w = output.append
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    91
    w(native.sql_schema(driver))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    92
    w('')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    93
    if text_index:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    94
        indexer = get_indexer(driver)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    95
        w(indexer.sql_init_fti())
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    96
        w('')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    97
    dbhelper = get_adv_func_helper(driver)
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
    98
    w(schema2sql(dbhelper, schema, prefix=SQL_PREFIX,
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    99
                 skip_entities=skip_entities, skip_relations=skip_relations))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   100
    if dbhelper.users_support and user:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   101
        w('')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   102
        w(sqlgrants(schema, driver, user, text_index, set_owner,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   103
                    skip_relations, skip_entities))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   104
    return '\n'.join(output)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   105
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   106
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   107
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
   108
                  skip_relations=PURE_VIRTUAL_RTYPES, skip_entities=()):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   109
    """return the sql to drop the schema, according to the given parameters"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   110
    from yams.schema2sql import dropschema2sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   111
    from cubicweb.server.sources import native
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   112
    output = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   113
    w = output.append
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   114
    w(native.sql_drop_schema(driver))
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
    if text_index:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   117
        indexer = get_indexer(driver)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   118
        w(indexer.sql_drop_fti())
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   119
        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
   120
    w(dropschema2sql(schema, prefix=SQL_PREFIX,
1954
9b20f3504af8 cleanup
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1953
diff changeset
   121
                     skip_entities=skip_entities,
9b20f3504af8 cleanup
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1953
diff changeset
   122
                     skip_relations=skip_relations))
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   123
    return '\n'.join(output)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   124
1016
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   125
try:
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   126
    from mx.DateTime import DateTimeType, DateTimeDeltaType
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   127
except ImportError:
1953
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1783
diff changeset
   128
    DateTimeType = DateTimeDeltaType = None
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   129
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   130
class SQLAdapterMixIn(object):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   131
    """Mixin for SQL data sources, getting a connection from a configuration
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   132
    dictionary and handling connection locking
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   133
    """
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   134
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   135
    def __init__(self, source_config):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   136
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   137
            self.dbdriver = source_config['db-driver'].lower()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   138
            self.dbname = source_config['db-name']
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   139
        except KeyError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   140
            raise ConfigurationError('missing some expected entries in sources file')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   141
        self.dbhost = source_config.get('db-host')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   142
        port = source_config.get('db-port')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   143
        self.dbport = port and int(port) or None
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   144
        self.dbuser = source_config.get('db-user')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   145
        self.dbpasswd = source_config.get('db-password')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   146
        self.encoding = source_config.get('db-encoding', 'UTF-8')
1016
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   147
        self.dbapi_module = db.get_dbapi_compliant_module(self.dbdriver)
4177
f0ab2b6d3553 SqlServer: support single sign on / Windows credential authentication
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents: 4176
diff changeset
   148
        self.dbdriver_extra_args = source_config.get('db-extra-arguments')
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   149
        self.binary = self.dbapi_module.Binary
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   150
        self.dbhelper = self.dbapi_module.adv_func_helper
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   151
        self.sqlgen = SQLGenerator()
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   152
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   153
    def get_connection(self, user=None, password=None):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   154
        """open and return a connection to the database"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   155
        if user or self.dbuser:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   156
            self.info('connecting to %s@%s for user %s', self.dbname,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   157
                      self.dbhost or 'localhost', user or self.dbuser)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   158
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   159
            self.info('connecting to %s@%s', self.dbname,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   160
                      self.dbhost or 'localhost')
4179
3a0b2f14e17b only give extra_args if there is something to be fed (breaks some db connectors otherwise)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4177
diff changeset
   161
        extra = {}
3a0b2f14e17b only give extra_args if there is something to be fed (breaks some db connectors otherwise)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4177
diff changeset
   162
        if self.dbdriver_extra_args:
3a0b2f14e17b only give extra_args if there is something to be fed (breaks some db connectors otherwise)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4177
diff changeset
   163
            extra = {'extra_args': self.dbdriver_extra_args}
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   164
        cnx = self.dbapi_module.connect(self.dbhost, self.dbname,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   165
                                        user or self.dbuser,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   166
                                        password or self.dbpasswd,
4177
f0ab2b6d3553 SqlServer: support single sign on / Windows credential authentication
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents: 4176
diff changeset
   167
                                        port=self.dbport,
4179
3a0b2f14e17b only give extra_args if there is something to be fed (breaks some db connectors otherwise)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 4177
diff changeset
   168
                                        **extra)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   169
        init_cnx(self.dbdriver, cnx)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   170
        #self.dbapi_module.type_code_test(cnx.cursor())
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   171
        return cnx
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   172
2759
23d7a75693f8 R refactor backup and use tar.gz to store all sources
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2739
diff changeset
   173
    def backup_to_file(self, backupfile):
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   174
        cmd = self.dbhelper.backup_command(self.dbname, self.dbhost,
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   175
                                           self.dbuser, backupfile,
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   176
                                           keepownership=False)
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
   177
        if subprocess.call(cmd):
2759
23d7a75693f8 R refactor backup and use tar.gz to store all sources
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2739
diff changeset
   178
            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
   179
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   180
    def restore_from_file(self, backupfile, confirm, drop=True):
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   181
        for cmd in self.dbhelper.restore_commands(self.dbname, self.dbhost,
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   182
                                                  self.dbuser, backupfile,
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   183
                                                  self.encoding,
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   184
                                                  keepownership=False,
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   185
                                                  drop=drop):
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
   186
            if subprocess.call(cmd):
4195
86dcaf6bb92f closes #601987
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4179
diff changeset
   187
                print '-> Failed command: %s' % cmd
86dcaf6bb92f closes #601987
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4179
diff changeset
   188
                if not confirm('Continue anyway?', default='n'):
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:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   193
            args = dict(args)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   194
            for key, val in args.items():
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):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   197
                    val = self.binary(val.getvalue())
1016
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   198
                # XXX <3.2 bw compat
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   199
                elif type(val) is DateTimeType:
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   200
                    warn('found mx date time instance, please update to use datetime',
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   201
                         DeprecationWarning)
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   202
                    val = datetime(val.year, val.month, val.day,
1026
4c097dbaf560 avoid deprecation warning
sylvain.thenault@logilab.fr
parents: 1016
diff changeset
   203
                                   val.hour, val.minute, int(val.second))
1016
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   204
                elif type(val) is DateTimeDeltaType:
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   205
                    warn('found mx date time instance, please update to use datetime',
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   206
                         DeprecationWarning)
1026
4c097dbaf560 avoid deprecation warning
sylvain.thenault@logilab.fr
parents: 1016
diff changeset
   207
                    val = timedelta(0, int(val.seconds), 0)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   208
                args[key] = val
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   209
            # should not collide
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   210
            args.update(query_args)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   211
            return args
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   212
        return query_args
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   213
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   214
    def process_result(self, cursor):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   215
        """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
   216
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   217
        descr = cursor.description
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   218
        encoding = self.encoding
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   219
        process_value = self.dbapi_module.process_value
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   220
        binary = Binary
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   221
        results = cursor.fetchall()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   222
        for i, line in enumerate(results):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   223
            result = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   224
            for col, value in enumerate(line):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   225
                if value is None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   226
                    result.append(value)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   227
                    continue
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   228
                result.append(process_value(value, descr[col], encoding, binary))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   229
            results[i] = result
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   230
        return results
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   231
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   232
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   233
    def preprocess_entity(self, entity):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   234
        """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
   235
        to insert/update an entity into a SQL database
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   236
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   237
        attrs = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   238
        eschema = entity.e_schema
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   239
        for attr, value in entity.items():
3689
deb13e88e037 follow yams 0.25 api changes to improve performance
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3623
diff changeset
   240
            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
   241
            if rschema.final:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   242
                atype = str(entity.e_schema.destination(attr))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   243
                if atype == 'Boolean':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   244
                    value = self.dbhelper.boolean_value(value)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   245
                elif atype == 'Password':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   246
                    # if value is a Binary instance, this mean we got it
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   247
                    # from a query result and so it is already encrypted
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   248
                    if isinstance(value, Binary):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   249
                        value = value.getvalue()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   250
                    else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   251
                        value = crypt_password(value)
4176
42247d70105b SQL Server port: convert Password to Binary
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents: 3689
diff changeset
   252
                    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
   253
                # 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
   254
                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
   255
                    value = todatetime(value)
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   256
                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
   257
                    value = todate(value)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   258
                elif isinstance(value, Binary):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   259
                    value = self.binary(value.getvalue())
1408
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   260
                # XXX <3.2 bw compat
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   261
                elif type(value) is DateTimeType:
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   262
                    warn('found mx date time instance, please update to use datetime',
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   263
                         DeprecationWarning)
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   264
                    value = datetime(value.year, value.month, value.day,
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   265
                                   value.hour, value.minute, int(value.second))
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   266
                elif type(value) is DateTimeDeltaType:
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   267
                    warn('found mx date time instance, please update to use datetime',
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   268
                         DeprecationWarning)
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   269
                    value = timedelta(0, int(value.seconds), 0)
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
   270
            attrs[SQL_PREFIX+str(attr)] = value
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   271
        return attrs
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   272
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   273
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   274
from logging import getLogger
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   275
from cubicweb import set_log_methods
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   276
set_log_methods(SQLAdapterMixIn, getLogger('cubicweb.sqladapter'))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   277
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   278
def init_sqlite_connexion(cnx):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   279
    # XXX should not be publicly exposed
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   280
    #def comma_join(strings):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   281
    #    return ', '.join(strings)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   282
    #cnx.create_function("COMMA_JOIN", 1, comma_join)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   283
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   284
    class concat_strings(object):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   285
        def __init__(self):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   286
            self.values = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   287
        def step(self, value):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   288
            if value is not None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   289
                self.values.append(value)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   290
        def finalize(self):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   291
            return ', '.join(self.values)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   292
    # renamed to GROUP_CONCAT in cubicweb 2.45, keep old name for bw compat for
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   293
    # some time
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   294
    cnx.create_aggregate("CONCAT_STRINGS", 1, concat_strings)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   295
    cnx.create_aggregate("GROUP_CONCAT", 1, concat_strings)
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   296
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   297
    def _limit_size(text, maxsize, format='text/plain'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   298
        if len(text) < maxsize:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   299
            return text
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   300
        if format in ('text/html', 'text/xhtml', 'text/xml'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   301
            text = remove_html_tags(text)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   302
        if len(text) > maxsize:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   303
            text = text[:maxsize] + '...'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   304
        return text
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   305
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   306
    def limit_size3(text, format, maxsize):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   307
        return _limit_size(text, maxsize, format)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   308
    cnx.create_function("LIMIT_SIZE", 3, limit_size3)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   309
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   310
    def limit_size2(text, maxsize):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   311
        return _limit_size(text, maxsize)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   312
    cnx.create_function("TEXT_LIMIT_SIZE", 2, limit_size2)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   313
    import yams.constraints
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   314
    if hasattr(yams.constraints, 'patch_sqlite_decimal'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   315
        yams.constraints.patch_sqlite_decimal()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   316
4322
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   317
    def fspath(eid, etype, attr):
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   318
        try:
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   319
            cu = cnx.cursor()
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   320
            cu.execute('SELECT X.cw_%s FROM cw_%s as X '
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   321
                       'WHERE X.cw_eid=%%(eid)s' % (attr, etype),
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   322
                       {'eid': eid})
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   323
            return cu.fetchone()[0]
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   324
        except:
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   325
            import traceback
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   326
            traceback.print_exc()
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   327
            raise
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   328
    cnx.create_function('fspath', 3, fspath)
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   329
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   330
    def _fsopen(fspath):
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   331
        if fspath:
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   332
            try:
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   333
                return buffer(file(fspath).read())
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   334
            except:
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   335
                import traceback
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   336
                traceback.print_exc()
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   337
                raise
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   338
    cnx.create_function('_fsopen', 1, _fsopen)
f65743cc53e4 first draft for a simple hooks based custom attribute storage,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4314
diff changeset
   339
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   340
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   341
sqlite_hooks = SQL_CONNECT_HOOKS.setdefault('sqlite', [])
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   342
sqlite_hooks.append(init_sqlite_connexion)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   343
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   344
def init_cnx(driver, cnx):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   345
    for hook in SQL_CONNECT_HOOKS.get(driver, ()):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   346
        hook(cnx)