[server] *init_repository* lookup the database instead of the schema to drop tables (closes #810743)

So, tables are dropped even if the schema was changed.

note:if mssql, drop views before tables.
note:because of table dependencies we try a second drop sequence
authorAlain Leufroy <alain.leufroy@logilab.fr>
changeset1f3757ef3762
branchstable
phasepublic
hiddenno
parent revision#9d5cfbde9856 [book] remove XXX to an old discussion about appengine
child revision#203fb56685ca [devtools/qunit] get rid of unused variable
files modified by this revision
server/__init__.py
server/sqlutils.py
# HG changeset patch
# User Alain Leufroy <alain.leufroy@logilab.fr>
# Date 1318949716 -7200
# Tue Oct 18 16:55:16 2011 +0200
# Branch stable
# Node ID 1f3757ef3762e31337c19112a5dac4542cc83aed
# Parent 9d5cfbde98566837e5d051f2b5529c3f0b49bf95
[server] *init_repository* lookup the database instead of the schema to drop tables (closes #810743)

So, tables are dropped even if the schema was changed.

:note: if mssql, drop views before tables.
:note: because of table dependencies we try a second drop sequence

diff --git a/server/__init__.py b/server/__init__.py
@@ -28,10 +28,11 @@
1  from glob import glob
2 
3  from logilab.common.modutils import LazyObject
4  from logilab.common.textutils import splitstrip
5  from logilab.common.registry import yes
6 +from logilab import database
7 
8  from yams import BASE_GROUPS
9 
10  from cubicweb import CW_SOFTWARE_ROOT
11  from cubicweb.appobject import AppObject
@@ -160,11 +161,12 @@
12      a initial user)
13      """
14      from cubicweb.dbapi import in_memory_repo_cnx
15      from cubicweb.server.repository import Repository
16      from cubicweb.server.utils import manager_userpasswd
17 -    from cubicweb.server.sqlutils import sqlexec, sqlschema, sqldropschema
18 +    from cubicweb.server.sqlutils import sqlexec, sqlschema, sql_drop_all_user_tables
19 +    from cubicweb.server.sqlutils import _SQL_DROP_ALL_USER_TABLES_FILTER_FUNCTION as drop_filter
20      # configuration to avoid db schema loading and user'state checking
21      # on connection
22      config.creating = True
23      config.consider_user_state = False
24      config.cubicweb_appobject_path = set(('hooks', 'entities'))
@@ -177,17 +179,25 @@
25      driver = source['db-driver']
26      sqlcnx = repo.system_source.get_connection()
27      sqlcursor = sqlcnx.cursor()
28      execute = sqlcursor.execute
29      if drop:
30 -        _title = '-> drop tables '
31 -        dropsql = sqldropschema(schema, driver)
32 -        try:
33 -            sqlexec(dropsql, execute, pbtitle=_title)
34 -        except Exception as ex:
35 -            print '-> drop failed, skipped (%s).' % ex
36 -            sqlcnx.rollback()
37 +        helper = database.get_db_helper(driver)
38 +        dropsql = sql_drop_all_user_tables(helper, sqlcursor)
39 +        # We may fail dropping some tables because of table dependencies, in a first pass.
40 +        # So, we try a second drop sequence to drop remaining tables if needed.
41 +        # Note that 2 passes is an arbitrary choice as it seems enougth for our usecases.
42 +        # (looping may induce infinite recursion when user have no right for example)
43 +        # Here we try to keep code simple and backend independant. That why we don't try to
44 +        # distinguish remaining tables (wrong right, dependencies, ...).
45 +        failed = sqlexec(dropsql, execute, cnx=sqlcnx,
46 +                         pbtitle='-> dropping tables (first pass)')
47 +        if failed:
48 +            failed = sqlexec(failed, execute, cnx=sqlcnx,
49 +                             pbtitle='-> dropping tables (second pass)')
50 +            remainings = filter(drop_filter, helper.list_tables(sqlcursor))
51 +            assert not remainings, 'Remaining tables: %s' % ', '.join(remainings)
52      _title = '-> creating tables '
53      print _title,
54      # schema entities and relations tables
55      # can't skip entities table even if system source doesn't support them,
56      # they are used sometimes by generated sql. Keeping them empty is much
diff --git a/server/sqlutils.py b/server/sqlutils.py
@@ -18,12 +18,14 @@
57  """SQL utilities functions and classes."""
58 
59  __docformat__ = "restructuredtext en"
60 
61  import os
62 +import re
63  import subprocess
64  from datetime import datetime, date
65 +from itertools import ifilter
66 
67  from logilab import database as db, common as lgc
68  from logilab.common.shellutils import ProgressBar
69  from logilab.common.date import todate, todatetime, utcdatetime, utctime
70  from logilab.database.sqlgen import SQLGenerator
@@ -47,31 +49,57 @@
71      print ' '.join(cmd)
72      return subprocess.call(cmd)
73 
74 
75  def sqlexec(sqlstmts, cursor_or_execute, withpb=not os.environ.get('APYCOT_ROOT'),
76 -            pbtitle='', delimiter=';'):
77 +            pbtitle='', delimiter=';', cnx=None):
78      """execute sql statements ignoring DROP/ CREATE GROUP or USER statements
79 -    error. If a cnx is given, commit at each statement
80 +    error.
81 +
82 +    :sqlstmts_as_string: a string or a list of sql statements.
83 +    :cursor_or_execute: sql cursor or a callback used to execute statements
84 +    :cnx: if given, commit/rollback at each statement.
85 +
86 +    :withpb: if True, display a progresse bar
87 +    :pbtitle: a string displayed as the progress bar title (if `withpb=True`)
88 +
89 +    :delimiter: a string used to split sqlstmts (if it is a string)
90 +
91 +    Return the failed statements (same type as sqlstmts)
92      """
93      if hasattr(cursor_or_execute, 'execute'):
94          execute = cursor_or_execute.execute
95      else:
96          execute = cursor_or_execute
97 -    sqlstmts = sqlstmts.split(delimiter)
98 +    sqlstmts_as_string = False
99 +    if isinstance(sqlstmts, basestring):
100 +        sqlstmts_as_string = True
101 +        sqlstmts = sqlstmts.split(delimiter)
102      if withpb:
103          pb = ProgressBar(len(sqlstmts), title=pbtitle)
104 +    failed = []
105      for sql in sqlstmts:
106          sql = sql.strip()
107          if withpb:
108              pb.update()
109          if not sql:
110              continue
111 -        # some dbapi modules doesn't accept unicode for sql string
112 -        execute(str(sql))
113 +        try:
114 +            # some dbapi modules doesn't accept unicode for sql string
115 +            execute(str(sql))
116 +        except Exception, err:
117 +            if cnx:
118 +                cnx.rollback()
119 +            failed.append(sql)
120 +        else:
121 +            if cnx:
122 +                cnx.commit()
123      if withpb:
124          print
125 +    if sqlstmts_as_string:
126 +        failed = delimiter.join(failed)
127 +    return failed
128 
129 
130  def sqlgrants(schema, driver, user,
131                text_index=True, set_owner=True,
132                skip_relations=(), skip_entities=()):
@@ -135,10 +163,27 @@
133      w('')
134      w(native.sql_drop_schema(driver))
135      return '\n'.join(output)
136 
137 
138 +_SQL_DROP_ALL_USER_TABLES_FILTER_FUNCTION = re.compile('^(?!(sql|pg)_)').match
139 +def sql_drop_all_user_tables(driver_or_helper, sqlcursor):
140 +    """Return ths sql to drop all tables found in the database system."""
141 +    if not getattr(driver_or_helper, 'list_tables', None):
142 +        dbhelper = db.get_db_helper(driver_or_helper)
143 +    else:
144 +        dbhelper = driver_or_helper
145 +
146 +    cmds = [dbhelper.sql_drop_sequence('entities_id_seq')]
147 +    # for mssql, we need to drop views before tables
148 +    if hasattr(dbhelper, 'list_views'):
149 +        cmds += ['DROP VIEW %s;' % name
150 +                 for name in ifilter(_SQL_DROP_ALL_USER_TABLES_FILTER_FUNCTION, dbhelper.list_views(sqlcursor))]
151 +    cmds += ['DROP TABLE %s;' % name
152 +             for name in ifilter(_SQL_DROP_ALL_USER_TABLES_FILTER_FUNCTION, dbhelper.list_tables(sqlcursor))]
153 +    return '\n'.join(cmds)
154 +
155  class SQLAdapterMixIn(object):
156      """Mixin for SQL data sources, getting a connection from a configuration
157      dictionary and handling connection locking
158      """
159