adbh: new alter_column_support flag, sql_set_null_allowed and sql_change_col_type methods

2 files changed, 35 insertions, 6 deletions

diff --git a/ChangeLog b/ChangeLog
index 5cbbe23..ed95ae0 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,6 +1,11 @@
 ChangeLog for logilab.common
 ============================
 
+ --
+   * adbh: new alter_column_support flag, sql_set_null_allowed and
+     sql_change_col_type methods
+
+
 2009-05-28  -- 0.40.1
    * date: handle both mx.DateTime and datetime representations
    * db: use sqlite native module's Binary, not StringIO
diff --git a/adbh.py b/adbh.py
index a4d9430..b8de178 100644
--- a/adbh.py
+++ b/adbh.py
@@ -107,6 +107,7 @@ class _GenericAdvFuncHelper:
     users_support = True
     groups_support = True
     ilike_support = True
+    alter_column_support = True
 
     FUNCTIONS = {
         # aggregat functions
@@ -224,6 +225,15 @@ INSERT INTO %s VALUES (0);''' % (seq_name, seq_name)
         return ('UPDATE %s SET last=last+1;' % seq_name,
                 'SELECT last FROM %s;' % seq_name)
 
+    def sql_change_col_type(self, table, column, coltype, null_allowed):
+        return 'ALTER TABLE %s ALTER COLUMN %s TYPE %s' % (
+            table, column, coltype)
+
+    def sql_set_null_allowed(self, table, column, coltype, null_allowed):
+        cmd = null_allowed and 'SET' or 'DROP'
+        return 'ALTER TABLE %s ALTER COLUMN %s %s NOT NULL' % (
+            table, column, cmd)
+
     def sql_temporary_table(self, table_name, table_schema,
                             drop_on_commit=True):
         return "CREATE TEMPORARY TABLE %s (%s);" % (table_name, table_schema)
@@ -420,6 +430,7 @@ class _SqliteAdvFuncHelper(_GenericAdvFuncHelper):
     ilike_support = False
     union_parentheses_support = False
     intersect_all_support = False
+    alter_column_support = False
 
     def sql_create_index(self, table, column, unique=False):
         idx = self._index_name(table, column, unique)
@@ -470,10 +481,12 @@ class _MyAdvFuncHelper(_GenericAdvFuncHelper):
                        keepownership=True):
         """return a command to backup the given database"""
         # XXX compress
-        host_option = ''
         if dbhost is not None:
             host_option = '-h %s' % dbhost
-        return 'mysqldump %s -u %s -p -r %s %s' % (host_option, dbuser, backupfile, dbname)
+        else:
+            host_option = ''
+        return 'mysqldump %s -u %s -p -r %s %s' % (host_option, dbuser,
+                                                   backupfile, dbname)
 
     def restore_commands(self, dbname, dbhost, dbuser, backupfile,
                          encoding='utf-8', keepownership=True, drop=True):
@@ -496,10 +509,10 @@ class _MyAdvFuncHelper(_GenericAdvFuncHelper):
     def sql_temporary_table(self, table_name, table_schema,
                             drop_on_commit=True):
         if not drop_on_commit:
-            return "CREATE TEMPORARY TABLE %s (%s);" % (table_name,
-                                                        table_schema)
-        return "CREATE TEMPORARY TABLE %s (%s) ON COMMIT DROP;" % (table_name,
-                                                                   table_schema)
+            return "CREATE TEMPORARY TABLE %s (%s);" % (
+                table_name, table_schema)
+        return "CREATE TEMPORARY TABLE %s (%s) ON COMMIT DROP;" % (
+            table_name, table_schema)
 
     def sql_create_database(self, dbname, encoding='utf-8'):
         sql = "CREATE DATABASE %(dbname)s"
@@ -507,6 +520,17 @@ class _MyAdvFuncHelper(_GenericAdvFuncHelper):
             sql += " CHARACTER SET %(encoding)s"
         return sql % locals()
 
+    def sql_change_col_type(self, table, column, coltype, null_allowed):
+        if null_allowed:
+            cmd = 'DEFAULT'
+        else:
+            cmd = 'NOT'
+        return 'ALTER TABLE %s MODIFY COLUMN %s %s NULL' % (
+            table, column, coltype, cmd)
+
+    def sql_set_null_allowed(self, table, column, coltype, null_allowed):
+        return self.sql_change_col_type(table, column, coltype, null_allowed)
+
     def create_database(self, cursor, dbname, owner=None, encoding='utf-8'):
         """create a new database"""
         cursor.execute(self.sql_create_database(dbname, encoding))