server/sqlutils.py
author Nicolas Chauvat <nicolas.chauvat@logilab.fr>
Fri, 31 Jul 2009 23:26:52 +0200
changeset 2615 1ea41b7c0836
parent 2512 106b2a05dc88
child 2616 4501ee760eec
permissions -rw-r--r--
F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
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
1977
606923dff11b big bunch of copyright / docstring update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1954
diff changeset
     4
:copyright: 2001-2009 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
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
    11
from os.path import exists
1016
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
    12
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
    13
from datetime import datetime, date, timedelta
1016
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
    14
1783
b81f9761907c mx flag has moved
sylvain.thenault@logilab.fr
parents: 1619
diff changeset
    15
import logilab.common as lgc
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    16
from logilab.common.shellutils import ProgressBar
1016
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
    17
from logilab.common import db
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    18
from logilab.common.adbh import get_adv_func_helper
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    19
from logilab.common.sqlgen import SQLGenerator
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    20
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    21
from indexer import get_indexer
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    22
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    23
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
    24
from cubicweb.utils import todate, todatetime
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    25
from cubicweb.common.uilib import remove_html_tags
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
    26
from cubicweb.toolsutils import restrict_perms_to_user
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    27
from cubicweb.server import SQL_CONNECT_HOOKS
1132
96752791c2b6 pylint cleanup
sylvain.thenault@logilab.fr
parents: 1026
diff changeset
    28
from cubicweb.server.utils import crypt_password
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    29
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    30
1783
b81f9761907c mx flag has moved
sylvain.thenault@logilab.fr
parents: 1619
diff changeset
    31
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
    32
SQL_PREFIX = 'cw_'
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    33
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    34
2396
8bfb99d7bbcc [cw-ctl] improve dialog messages
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 1977
diff changeset
    35
def sqlexec(sqlstmts, cursor_or_execute, withpb=True, pbtitle='', delimiter=';'):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    36
    """execute sql statements ignoring DROP/ CREATE GROUP or USER statements
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    37
    error. If a cnx is given, commit at each statement
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    38
    """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    39
    if hasattr(cursor_or_execute, 'execute'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    40
        execute = cursor_or_execute.execute
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    41
    else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    42
        execute = cursor_or_execute
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    43
    sqlstmts = sqlstmts.split(delimiter)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    44
    if withpb:
2396
8bfb99d7bbcc [cw-ctl] improve dialog messages
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 1977
diff changeset
    45
        pb = ProgressBar(len(sqlstmts), title=pbtitle)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    46
    for sql in sqlstmts:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    47
        sql = sql.strip()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    48
        if withpb:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    49
            pb.update()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    50
        if not sql:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    51
            continue
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    52
        # some dbapi modules doesn't accept unicode for sql string
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    53
        execute(str(sql))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    54
    if withpb:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    55
        print
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    56
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    57
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    58
def sqlgrants(schema, driver, user,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    59
              text_index=True, set_owner=True,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    60
              skip_relations=(), skip_entities=()):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    61
    """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
    62
    schema
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    63
    """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    64
    from yams.schema2sql import grant_schema
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    65
    from cubicweb.server.sources import native
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    66
    output = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    67
    w = output.append
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    68
    w(native.grant_schema(user, set_owner))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    69
    w('')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    70
    if text_index:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    71
        indexer = get_indexer(driver)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    72
        w(indexer.sql_grant_user(user))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    73
        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
    74
    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
    75
    return '\n'.join(output)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    76
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
    77
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
    78
def sqlschema(schema, driver, text_index=True,
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    79
              user=None, set_owner=False,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    80
              skip_relations=('has_text', 'identity'), skip_entities=()):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    81
    """return the system sql schema, according to the given parameters"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    82
    from yams.schema2sql import schema2sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    83
    from cubicweb.server.sources import native
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    84
    if set_owner:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    85
        assert user, 'user is argument required when set_owner is true'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    86
    output = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    87
    w = output.append
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    88
    w(native.sql_schema(driver))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    89
    w('')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    90
    if text_index:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    91
        indexer = get_indexer(driver)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    92
        w(indexer.sql_init_fti())
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    93
        w('')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    94
    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
    95
    w(schema2sql(dbhelper, schema, prefix=SQL_PREFIX,
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    96
                 skip_entities=skip_entities, skip_relations=skip_relations))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    97
    if dbhelper.users_support and user:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    98
        w('')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    99
        w(sqlgrants(schema, driver, user, text_index, set_owner,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   100
                    skip_relations, skip_entities))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   101
    return '\n'.join(output)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   102
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   103
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   104
def sqldropschema(schema, driver, text_index=True,
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   105
                  skip_relations=('has_text', 'identity'), skip_entities=()):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   106
    """return the sql to drop the schema, according to the given parameters"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   107
    from yams.schema2sql import dropschema2sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   108
    from cubicweb.server.sources import native
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   109
    output = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   110
    w = output.append
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   111
    w(native.sql_drop_schema(driver))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   112
    w('')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   113
    if text_index:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   114
        indexer = get_indexer(driver)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   115
        w(indexer.sql_drop_fti())
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   116
        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
   117
    w(dropschema2sql(schema, prefix=SQL_PREFIX,
1954
9b20f3504af8 cleanup
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1953
diff changeset
   118
                     skip_entities=skip_entities,
9b20f3504af8 cleanup
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1953
diff changeset
   119
                     skip_relations=skip_relations))
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   120
    return '\n'.join(output)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   121
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   122
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   123
def sql_source_backup(source, sqladapter, confirm, backupfile,
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   124
                      askconfirm=False):
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   125
    if exists(backupfile):
2512
106b2a05dc88 [cleanup] started to improve cubicweb-ctl dialog messages consistency
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 2510
diff changeset
   126
        if not confirm('Backup file %s exists, overwrite it?' % backupfile):
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   127
            return
2512
106b2a05dc88 [cleanup] started to improve cubicweb-ctl dialog messages consistency
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 2510
diff changeset
   128
    elif askconfirm and not confirm('Backup %s database?'
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   129
                                    % source.repo.config.appid):
2512
106b2a05dc88 [cleanup] started to improve cubicweb-ctl dialog messages consistency
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 2510
diff changeset
   130
        print '-> no backup done.'
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   131
        return
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   132
    # should close opened connection before backuping
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   133
    source.close_pool_connections()
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   134
    try:
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   135
        sqladapter.backup_to_file(backupfile, confirm)
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   136
    finally:
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   137
        source.open_pool_connections()
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   138
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   139
def sql_source_restore(source, sqladapter, confirm, backupfile, drop=True,
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   140
                       askconfirm=False):
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   141
    if not exists(backupfile):
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   142
        raise Exception("backup file %s doesn't exist" % backupfile)
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   143
    app = source.repo.config.appid
2512
106b2a05dc88 [cleanup] started to improve cubicweb-ctl dialog messages consistency
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 2510
diff changeset
   144
    if askconfirm and not confirm('Restore %s %s database from %s ?'
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   145
                                  % (app, source.uri, backupfile)):
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   146
        return
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   147
    # should close opened connection before restoring
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   148
    source.close_pool_connections()
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   149
    try:
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   150
        sqladapter.restore_from_file(backupfile, confirm, drop=drop)
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   151
    finally:
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   152
        source.open_pool_connections()
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   153
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   154
1016
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   155
try:
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   156
    from mx.DateTime import DateTimeType, DateTimeDeltaType
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   157
except ImportError:
1953
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1783
diff changeset
   158
    DateTimeType = DateTimeDeltaType = None
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   159
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   160
class SQLAdapterMixIn(object):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   161
    """Mixin for SQL data sources, getting a connection from a configuration
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   162
    dictionary and handling connection locking
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   163
    """
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   164
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   165
    def __init__(self, source_config):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   166
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   167
            self.dbdriver = source_config['db-driver'].lower()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   168
            self.dbname = source_config['db-name']
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   169
        except KeyError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   170
            raise ConfigurationError('missing some expected entries in sources file')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   171
        self.dbhost = source_config.get('db-host')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   172
        port = source_config.get('db-port')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   173
        self.dbport = port and int(port) or None
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   174
        self.dbuser = source_config.get('db-user')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   175
        self.dbpasswd = source_config.get('db-password')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   176
        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
   177
        self.dbapi_module = db.get_dbapi_compliant_module(self.dbdriver)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   178
        self.binary = self.dbapi_module.Binary
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   179
        self.dbhelper = self.dbapi_module.adv_func_helper
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   180
        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
   181
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   182
    def get_connection(self, user=None, password=None):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   183
        """open and return a connection to the database"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   184
        if user or self.dbuser:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   185
            self.info('connecting to %s@%s for user %s', self.dbname,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   186
                      self.dbhost or 'localhost', user or self.dbuser)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   187
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   188
            self.info('connecting to %s@%s', self.dbname,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   189
                      self.dbhost or 'localhost')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   190
        cnx = self.dbapi_module.connect(self.dbhost, self.dbname,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   191
                                        user or self.dbuser,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   192
                                        password or self.dbpasswd,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   193
                                        port=self.dbport)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   194
        init_cnx(self.dbdriver, cnx)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   195
        #self.dbapi_module.type_code_test(cnx.cursor())
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   196
        return cnx
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   197
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   198
    def backup_to_file(self, backupfile, confirm):
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   199
        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
   200
                                           self.dbuser, backupfile,
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   201
                                           keepownership=False)
2615
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   202
        backupdir = os.path.dirname(backupfile)
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   203
        if not os.path.exists(backupdir):
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   204
            if confirm('%s does not exist. Create it?' % backupdir,
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   205
                       abort=False, shell=False):
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   206
                os.mkdir(backupdir)
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   207
            else:
2615
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   208
                print '-> failed to backup instance'
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   209
                return
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   210
        if os.system(cmd):
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   211
            print '-> error trying to backup with command', cmd
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   212
            if not confirm('Continue anyway?', default_is_yes=False):
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   213
                raise SystemExit(1)
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   214
        else:
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   215
            print '-> backup file',  backupfile
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   216
            restrict_perms_to_user(backupfile, self.info)
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   217
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   218
    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
   219
        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
   220
                                                  self.dbuser, backupfile,
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   221
                                                  self.encoding,
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   222
                                                  keepownership=False,
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   223
                                                  drop=drop):
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   224
            while True:
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   225
                print cmd
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   226
                if os.system(cmd):
2615
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   227
                    print '-> error while restoring the base'
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   228
                    answer = confirm('Continue anyway?',
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   229
                                     shell=False, abort=False, retry=True)
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   230
                    if not answer:
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   231
                        raise SystemExit(1)
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   232
                    if answer == 1: # 1: continue, 2: retry
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   233
                        break
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   234
                else:
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   235
                    break
2615
1ea41b7c0836 F [dialog] offer to create backup. refactor to use l.c.shellutils.ASK
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2512
diff changeset
   236
        print '-> database restored.'
2493
9806571ea790 major refactoring of database dump/restore:
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2396
diff changeset
   237
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   238
    def merge_args(self, args, query_args):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   239
        if args is not None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   240
            args = dict(args)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   241
            for key, val in args.items():
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   242
                # convert cubicweb binary into db binary
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   243
                if isinstance(val, Binary):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   244
                    val = self.binary(val.getvalue())
1016
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   245
                # XXX <3.2 bw compat
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   246
                elif type(val) is DateTimeType:
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   247
                    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
   248
                         DeprecationWarning)
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   249
                    val = datetime(val.year, val.month, val.day,
1026
4c097dbaf560 avoid deprecation warning
sylvain.thenault@logilab.fr
parents: 1016
diff changeset
   250
                                   val.hour, val.minute, int(val.second))
1016
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   251
                elif type(val) is DateTimeDeltaType:
26387b836099 use datetime instead of mx.DateTime
sylvain.thenault@logilab.fr
parents: 0
diff changeset
   252
                    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
   253
                         DeprecationWarning)
1026
4c097dbaf560 avoid deprecation warning
sylvain.thenault@logilab.fr
parents: 1016
diff changeset
   254
                    val = timedelta(0, int(val.seconds), 0)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   255
                args[key] = val
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   256
            # should not collide
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   257
            args.update(query_args)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   258
            return args
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   259
        return query_args
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
    def process_result(self, cursor):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   262
        """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
   263
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   264
        descr = cursor.description
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   265
        encoding = self.encoding
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   266
        process_value = self.dbapi_module.process_value
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   267
        binary = Binary
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   268
        results = cursor.fetchall()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   269
        for i, line in enumerate(results):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   270
            result = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   271
            for col, value in enumerate(line):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   272
                if value is None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   273
                    result.append(value)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   274
                    continue
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   275
                result.append(process_value(value, descr[col], encoding, binary))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   276
            results[i] = result
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   277
        return results
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   278
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   279
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   280
    def preprocess_entity(self, entity):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   281
        """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
   282
        to insert/update an entity into a SQL database
0
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
        attrs = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   285
        eschema = entity.e_schema
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   286
        for attr, value in entity.items():
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   287
            rschema = eschema.subject_relation(attr)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   288
            if rschema.is_final():
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   289
                atype = str(entity.e_schema.destination(attr))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   290
                if atype == 'Boolean':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   291
                    value = self.dbhelper.boolean_value(value)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   292
                elif atype == 'Password':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   293
                    # if value is a Binary instance, this mean we got it
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   294
                    # from a query result and so it is already encrypted
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   295
                    if isinstance(value, Binary):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   296
                        value = value.getvalue()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   297
                    else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   298
                        value = crypt_password(value)
1619
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   299
                # 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
   300
                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
   301
                    value = todatetime(value)
e4845b54a704 force proper date/datetime according to type (necessary for sqlite at least)
sylvain.thenault@logilab.fr
parents: 1408
diff changeset
   302
                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
   303
                    value = todate(value)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   304
                elif isinstance(value, Binary):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   305
                    value = self.binary(value.getvalue())
1408
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   306
                # XXX <3.2 bw compat
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   307
                elif type(value) is DateTimeType:
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   308
                    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
   309
                         DeprecationWarning)
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   310
                    value = datetime(value.year, value.month, value.day,
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   311
                                   value.hour, value.minute, int(value.second))
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   312
                elif type(value) is DateTimeDeltaType:
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   313
                    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
   314
                         DeprecationWarning)
6bf19f175ea5 mx.DateTime fixes
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 1263
diff changeset
   315
                    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
   316
            attrs[SQL_PREFIX+str(attr)] = value
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   317
        return attrs
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
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   320
from logging import getLogger
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   321
from cubicweb import set_log_methods
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   322
set_log_methods(SQLAdapterMixIn, getLogger('cubicweb.sqladapter'))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   323
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   324
def init_sqlite_connexion(cnx):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   325
    # XXX should not be publicly exposed
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   326
    #def comma_join(strings):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   327
    #    return ', '.join(strings)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   328
    #cnx.create_function("COMMA_JOIN", 1, comma_join)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   329
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   330
    class concat_strings(object):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   331
        def __init__(self):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   332
            self.values = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   333
        def step(self, value):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   334
            if value is not None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   335
                self.values.append(value)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   336
        def finalize(self):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   337
            return ', '.join(self.values)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   338
    # 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
   339
    # some time
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   340
    cnx.create_aggregate("CONCAT_STRINGS", 1, concat_strings)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   341
    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
   342
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   343
    def _limit_size(text, maxsize, format='text/plain'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   344
        if len(text) < maxsize:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   345
            return text
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   346
        if format in ('text/html', 'text/xhtml', 'text/xml'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   347
            text = remove_html_tags(text)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   348
        if len(text) > maxsize:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   349
            text = text[:maxsize] + '...'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   350
        return text
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   351
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   352
    def limit_size3(text, format, maxsize):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   353
        return _limit_size(text, maxsize, format)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   354
    cnx.create_function("LIMIT_SIZE", 3, limit_size3)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   355
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   356
    def limit_size2(text, maxsize):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   357
        return _limit_size(text, maxsize)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   358
    cnx.create_function("TEXT_LIMIT_SIZE", 2, limit_size2)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   359
    import yams.constraints
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   360
    if hasattr(yams.constraints, 'patch_sqlite_decimal'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   361
        yams.constraints.patch_sqlite_decimal()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   362
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   363
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   364
sqlite_hooks = SQL_CONNECT_HOOKS.setdefault('sqlite', [])
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   365
sqlite_hooks.append(init_sqlite_connexion)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   366
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   367
def init_cnx(driver, cnx):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   368
    for hook in SQL_CONNECT_HOOKS.get(driver, ()):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   369
        hook(cnx)