--- a/server/__init__.py	Thu Oct 18 15:52:05 2012 +0200
+++ b/server/__init__.py	Tue Oct 18 16:55:16 2011 +0200
@@ -30,6 +30,7 @@
 from logilab.common.modutils import LazyObject
 from logilab.common.textutils import splitstrip
 from logilab.common.registry import yes
+from logilab import database
 
 from yams import BASE_GROUPS
 
@@ -162,7 +163,8 @@
     from cubicweb.dbapi import in_memory_repo_cnx
     from cubicweb.server.repository import Repository
     from cubicweb.server.utils import manager_userpasswd
-    from cubicweb.server.sqlutils import sqlexec, sqlschema, sqldropschema
+    from cubicweb.server.sqlutils import sqlexec, sqlschema, sql_drop_all_user_tables
+    from cubicweb.server.sqlutils import _SQL_DROP_ALL_USER_TABLES_FILTER_FUNCTION as drop_filter
     # configuration to avoid db schema loading and user'state checking
     # on connection
     config.creating = True
@@ -179,13 +181,21 @@
     sqlcursor = sqlcnx.cursor()
     execute = sqlcursor.execute
     if drop:
-        _title = '-> drop tables '
-        dropsql = sqldropschema(schema, driver)
-        try:
-            sqlexec(dropsql, execute, pbtitle=_title)
-        except Exception as ex:
-            print '-> drop failed, skipped (%s).' % ex
-            sqlcnx.rollback()
+        helper = database.get_db_helper(driver)
+        dropsql = sql_drop_all_user_tables(helper, sqlcursor)
+        # We may fail dropping some tables because of table dependencies, in a first pass.
+        # So, we try a second drop sequence to drop remaining tables if needed.
+        # Note that 2 passes is an arbitrary choice as it seems enougth for our usecases.
+        # (looping may induce infinite recursion when user have no right for example)
+        # Here we try to keep code simple and backend independant. That why we don't try to
+        # distinguish remaining tables (wrong right, dependencies, ...).
+        failed = sqlexec(dropsql, execute, cnx=sqlcnx,
+                         pbtitle='-> dropping tables (first pass)')
+        if failed:
+            failed = sqlexec(failed, execute, cnx=sqlcnx,
+                             pbtitle='-> dropping tables (second pass)')
+            remainings = filter(drop_filter, helper.list_tables(sqlcursor))
+            assert not remainings, 'Remaining tables: %s' % ', '.join(remainings)
     _title = '-> creating tables '
     print _title,
     # schema entities and relations tables

--- a/server/sqlutils.py	Thu Oct 18 15:52:05 2012 +0200
+++ b/server/sqlutils.py	Tue Oct 18 16:55:16 2011 +0200
@@ -20,8 +20,10 @@
 __docformat__ = "restructuredtext en"
 
 import os
+import re
 import subprocess
 from datetime import datetime, date
+from itertools import ifilter
 
 from logilab import database as db, common as lgc
 from logilab.common.shellutils import ProgressBar
@@ -49,27 +51,53 @@
 
 
 def sqlexec(sqlstmts, cursor_or_execute, withpb=not os.environ.get('APYCOT_ROOT'),
-            pbtitle='', delimiter=';'):
+            pbtitle='', delimiter=';', cnx=None):
     """execute sql statements ignoring DROP/ CREATE GROUP or USER statements
-    error. If a cnx is given, commit at each statement
+    error.
+
+    :sqlstmts_as_string: a string or a list of sql statements.
+    :cursor_or_execute: sql cursor or a callback used to execute statements
+    :cnx: if given, commit/rollback at each statement.
+
+    :withpb: if True, display a progresse bar
+    :pbtitle: a string displayed as the progress bar title (if `withpb=True`)
+
+    :delimiter: a string used to split sqlstmts (if it is a string)
+
+    Return the failed statements (same type as sqlstmts)
     """
     if hasattr(cursor_or_execute, 'execute'):
         execute = cursor_or_execute.execute
     else:
         execute = cursor_or_execute
-    sqlstmts = sqlstmts.split(delimiter)
+    sqlstmts_as_string = False
+    if isinstance(sqlstmts, basestring):
+        sqlstmts_as_string = True
+        sqlstmts = sqlstmts.split(delimiter)
     if withpb:
         pb = ProgressBar(len(sqlstmts), title=pbtitle)
+    failed = []
     for sql in sqlstmts:
         sql = sql.strip()
         if withpb:
             pb.update()
         if not sql:
             continue
-        # some dbapi modules doesn't accept unicode for sql string
-        execute(str(sql))
+        try:
+            # some dbapi modules doesn't accept unicode for sql string
+            execute(str(sql))
+        except Exception, err:
+            if cnx:
+                cnx.rollback()
+            failed.append(sql)
+        else:
+            if cnx:
+                cnx.commit()
     if withpb:
         print
+    if sqlstmts_as_string:
+        failed = delimiter.join(failed)
+    return failed
 
 
 def sqlgrants(schema, driver, user,
@@ -137,6 +165,23 @@
     return '\n'.join(output)
 
 
+_SQL_DROP_ALL_USER_TABLES_FILTER_FUNCTION = re.compile('^(?!(sql|pg)_)').match
+def sql_drop_all_user_tables(driver_or_helper, sqlcursor):
+    """Return ths sql to drop all tables found in the database system."""
+    if not getattr(driver_or_helper, 'list_tables', None):
+        dbhelper = db.get_db_helper(driver_or_helper)
+    else:
+        dbhelper = driver_or_helper
+
+    cmds = [dbhelper.sql_drop_sequence('entities_id_seq')]
+    # for mssql, we need to drop views before tables
+    if hasattr(dbhelper, 'list_views'):
+        cmds += ['DROP VIEW %s;' % name
+                 for name in ifilter(_SQL_DROP_ALL_USER_TABLES_FILTER_FUNCTION, dbhelper.list_views(sqlcursor))]
+    cmds += ['DROP TABLE %s;' % name
+             for name in ifilter(_SQL_DROP_ALL_USER_TABLES_FILTER_FUNCTION, dbhelper.list_tables(sqlcursor))]
+    return '\n'.join(cmds)
+
 class SQLAdapterMixIn(object):
     """Mixin for SQL data sources, getting a connection from a configuration
     dictionary and handling connection locking