[REF] tools: add functions for SQL schema manipulation

This helps factoring out a certain number of similar queries, and removing a
few methods from `BaseModel`.

13 files changed, 221 insertions, 235 deletions

diff --git a/odoo/addons/base/ir/ir_actions.py b/odoo/addons/base/ir/ir_actions.py
index ae5d74a7af6..472fdeafa0c 100644
--- a/odoo/addons/base/ir/ir_actions.py
+++ b/odoo/addons/base/ir/ir_actions.py
@@ -385,9 +385,8 @@ class IrActionsActWindowView(models.Model):
     @api.model_cr_context
     def _auto_init(self):
         res = super(IrActionsActWindowView, self)._auto_init()
-        self._cr.execute('SELECT indexname FROM pg_indexes WHERE indexname = \'act_window_view_unique_mode_per_action\'')
-        if not self._cr.fetchone():
-            self._cr.execute('CREATE UNIQUE INDEX act_window_view_unique_mode_per_action ON ir_act_window_view (act_window_id, view_mode)')
+        tools.create_unique_index(self._cr, 'act_window_view_unique_mode_per_action',
+                                  self._table, ['act_window_id', 'view_mode'])
         return res
 
 
diff --git a/odoo/addons/base/ir/ir_attachment.py b/odoo/addons/base/ir/ir_attachment.py
index 528df926e5d..0e7e9284f44 100644
--- a/odoo/addons/base/ir/ir_attachment.py
+++ b/odoo/addons/base/ir/ir_attachment.py
@@ -291,10 +291,8 @@ class IrAttachment(models.Model):
     @api.model_cr_context
     def _auto_init(self):
         res = super(IrAttachment, self)._auto_init()
-        self._cr.execute('SELECT indexname FROM pg_indexes WHERE indexname = %s', ('ir_attachment_res_idx',))
-        if not self._cr.fetchone():
-            self._cr.execute('CREATE INDEX ir_attachment_res_idx ON ir_attachment (res_model, res_id)')
-            self._cr.commit()
+        tools.create_index(self._cr, 'ir_attachment_res_idx',
+                           self._table, ['res_model', 'res_id'])
         return res
 
     @api.model
diff --git a/odoo/addons/base/ir/ir_filters.py b/odoo/addons/base/ir/ir_filters.py
index bfed5939fa6..fb6a69f398e 100644
--- a/odoo/addons/base/ir/ir_filters.py
+++ b/odoo/addons/base/ir/ir_filters.py
@@ -3,7 +3,7 @@
 
 import ast
 
-from odoo import api, fields, models, _
+from odoo import api, fields, models, tools, _
 from odoo.exceptions import UserError
 
 
@@ -150,8 +150,6 @@ class IrFilters(models.Model):
     def _auto_init(self):
         result = super(IrFilters, self)._auto_init()
         # Use unique index to implement unique constraint on the lowercase name (not possible using a constraint)
-        self._cr.execute("SELECT indexname FROM pg_indexes WHERE indexname = 'ir_filters_name_model_uid_unique_action_index'")
-        if not self._cr.fetchone():
-            self._cr.execute("""CREATE UNIQUE INDEX "ir_filters_name_model_uid_unique_action_index" ON ir_filters
-                                (lower(name), model_id, COALESCE(user_id,-1), COALESCE(action_id,-1))""")
+        tools.create_unique_index(self._cr, 'ir_filters_name_model_uid_unique_action_index',
+            self._table, ['lower(name)', 'model_id', 'COALESCE(user_id,-1)', 'COALESCE(action_id,-1)'])
         return result
diff --git a/odoo/addons/base/ir/ir_model.py b/odoo/addons/base/ir/ir_model.py
index 723b99b9a32..e5acaee9d87 100644
--- a/odoo/addons/base/ir/ir_model.py
+++ b/odoo/addons/base/ir/ir_model.py
@@ -143,12 +143,11 @@ class IrModel(models.Model):
     def _drop_table(self):
         for model in self:
             table = self.env[model.model]._table
-            self._cr.execute('select relkind from pg_class where relname=%s', (table,))
-            result = self._cr.fetchone()
-            if result and result[0] == 'v':
-                self._cr.execute('DROP view %s' % table)
-            elif result and result[0] == 'r':
-                self._cr.execute('DROP TABLE %s CASCADE' % table)
+            kind = tools.table_kind(self._cr, table)
+            if kind == 'v':
+                self._cr.execute('DROP VIEW "%s"' % table)
+            elif kind == 'r':
+                self._cr.execute('DROP TABLE "%s" CASCADE' % table)
         return True
 
     @api.multi
@@ -466,14 +465,9 @@ class IrModelFields(models.Model):
             if field.name in models.MAGIC_COLUMNS:
                 continue
             model = self.env[field.model]
-            self._cr.execute('SELECT relkind FROM pg_class WHERE relname=%s', (model._table,))
-            relkind = self._cr.fetchone()
-            self._cr.execute("""SELECT column_name FROM information_schema.columns
-                                WHERE table_name=%s AND column_name=%s""",
-                             (model._table, field.name))
-            column_name = self._cr.fetchone()
-            if column_name and (relkind and relkind[0] == 'r'):
-                self._cr.execute('ALTER table "%s" DROP column "%s" cascade' % (model._table, field.name))
+            if tools.column_exists(self._cr, model._table, field.name) and \
+                    tools.table_kind(self._cr, model._table) == 'r':
+                self._cr.execute('ALTER TABLE "%s" DROP COLUMN "%s" CASCADE' % (model._table, field.name))
             if field.state == 'manual' and field.ttype == 'many2many':
                 rel_name = field.relation_table or model._fields[field.name].relation
                 tables_to_drop.add(rel_name)
@@ -946,15 +940,14 @@ class IrModelRelation(models.Model):
                 # as installed modules have defined this element we must not delete it!
                 continue
 
-            self._cr.execute("SELECT 1 FROM information_schema.tables WHERE table_name=%s", (name,))
-            if self._cr.fetchone():
+            if tools.table_exists(self._cr, name):
                 to_drop.add(name)
 
         self.unlink()
 
         # drop m2m relation tables
         for table in to_drop:
-            self._cr.execute('DROP TABLE %s CASCADE' % table,)
+            self._cr.execute('DROP TABLE "%s" CASCADE' % table,)
             _logger.info('Dropped table %s', table)
 
         self._cr.commit()
@@ -1185,12 +1178,10 @@ class IrModelData(models.Model):
     @api.model_cr_context
     def _auto_init(self):
         res = super(IrModelData, self)._auto_init()
-        self._cr.execute("SELECT indexname FROM pg_indexes WHERE indexname = 'ir_model_data_module_name_uniq_index'")
-        if not self._cr.fetchone():
-            self._cr.execute('CREATE UNIQUE INDEX ir_model_data_module_name_uniq_index ON ir_model_data (module, name)')
-        self._cr.execute("SELECT indexname FROM pg_indexes WHERE indexname = 'ir_model_data_model_res_id_index'")
-        if not self._cr.fetchone():
-            self._cr.execute('CREATE INDEX ir_model_data_model_res_id_index ON ir_model_data (model, res_id)')
+        tools.create_unique_index(self._cr, 'ir_model_data_module_name_uniq_index',
+                                  self._table, ['module', 'name'])
+        tools.create_index(self._cr, 'ir_model_data_model_res_id_index',
+                           self._table, ['model', 'res_id'])
         return res
 
     @api.multi
diff --git a/odoo/addons/base/ir/ir_translation.py b/odoo/addons/base/ir/ir_translation.py
index de639e10abf..4cbd41b013a 100644
--- a/odoo/addons/base/ir/ir_translation.py
+++ b/odoo/addons/base/ir/ir_translation.py
@@ -255,19 +255,9 @@ class IrTranslation(models.Model):
     @api.model_cr_context
     def _auto_init(self):
         res = super(IrTranslation, self)._auto_init()
-        cr = self._cr
-
-        cr.execute("SELECT indexname FROM pg_indexes WHERE indexname LIKE 'ir_translation_%'")
-        indexes = [row[0] for row in cr.fetchall()]
-
         # Add separate md5 index on src (no size limit on values, and good performance).
-        if 'ir_translation_src_md5' not in indexes:
-            cr.execute('CREATE INDEX ir_translation_src_md5 ON ir_translation (md5(src))')
-            cr.commit()
-
-        if 'ir_translation_ltn' not in indexes:
-            cr.execute('CREATE INDEX ir_translation_ltn ON ir_translation (name, lang, type)')
-            cr.commit()
+        tools.create_index(self._cr, 'ir_translation_src_md5', self._table, ['md5(src)'])
+        tools.create_index(self._cr, 'ir_translation_ltn', self._table, ['name', 'lang', 'type'])
         return res
 
     @api.model
diff --git a/odoo/addons/base/ir/ir_ui_view.py b/odoo/addons/base/ir/ir_ui_view.py
index cd3eb73f96e..9233b08f329 100644
--- a/odoo/addons/base/ir/ir_ui_view.py
+++ b/odoo/addons/base/ir/ir_ui_view.py
@@ -79,9 +79,8 @@ class ViewCustom(models.Model):
     @api.model_cr_context
     def _auto_init(self):
         res = super(ViewCustom, self)._auto_init()
-        self._cr.execute("SELECT indexname FROM pg_indexes WHERE indexname = 'ir_ui_view_custom_user_id_ref_id'")
-        if not self._cr.fetchone():
-            self._cr.execute("CREATE INDEX ir_ui_view_custom_user_id_ref_id ON ir_ui_view_custom (user_id, ref_id)")
+        tools.create_index(self._cr, 'ir_ui_view_custom_user_id_ref_id',
+                           self._table, ['user_id', 'ref_id'])
         return res
 
 
@@ -348,9 +347,8 @@ actual arch.
     @api.model_cr_context
     def _auto_init(self):
         res = super(View, self)._auto_init()
-        self._cr.execute('SELECT indexname FROM pg_indexes WHERE indexname = \'ir_ui_view_model_type_inherit_id\'')
-        if not self._cr.fetchone():
-            self._cr.execute('CREATE INDEX ir_ui_view_model_type_inherit_id ON ir_ui_view (model, inherit_id)')
+        tools.create_index(self._cr, 'ir_ui_view_model_type_inherit_id',
+                           self._table, ['model', 'inherit_id'])
         return res
 
     def _compute_defaults(self, values):
diff --git a/odoo/addons/base/ir/ir_values.py b/odoo/addons/base/ir/ir_values.py
index 8614390990f..1134a196d9a 100644
--- a/odoo/addons/base/ir/ir_values.py
+++ b/odoo/addons/base/ir/ir_values.py
@@ -149,9 +149,8 @@ class IrValues(models.Model):
     @api.model_cr_context
     def _auto_init(self):
         res = super(IrValues, self)._auto_init()
-        self._cr.execute("SELECT indexname FROM pg_indexes WHERE indexname = 'ir_values_key_model_key2_res_id_user_id_idx'")
-        if not self._cr.fetchone():
-            self._cr.execute("CREATE INDEX ir_values_key_model_key2_res_id_user_id_idx ON ir_values (key, model, key2, res_id, user_id)")
+        tools.create_index(self._cr, 'ir_values_key_model_key2_res_id_user_id_idx',
+                           self._table, ['key', 'model', 'key2', 'res_id', 'user_id'])
         return res
 
     @api.model
diff --git a/odoo/fields.py b/odoo/fields.py
index 1f65a6b61ff..6aff8c14c0c 100644
--- a/odoo/fields.py
+++ b/odoo/fields.py
@@ -16,7 +16,8 @@ import psycopg2
 
 from odoo.sql_db import LazyCursor
 from odoo.tools import float_precision, float_repr, float_round, frozendict, \
-                       html_sanitize, human_size, pg_varchar, ustr, OrderedSet
+                       html_sanitize, human_size, pg_varchar, table_exists, \
+                       table_kind, ustr, OrderedSet
 from odoo.tools import DEFAULT_SERVER_DATE_FORMAT as DATE_FORMAT
 from odoo.tools import DEFAULT_SERVER_DATETIME_FORMAT as DATETIME_FORMAT
 from odoo.tools.translate import html_translate, _
@@ -2263,18 +2264,15 @@ class Many2many(_RelationalMulti):
         if not rel.startswith('x_'):
             IMR = model.env['ir.model.relation']
             model.pool.post_init(IMR._reflect_relation, model, rel, self._module)
-        cr.execute("SELECT relname FROM pg_class WHERE relkind IN ('r','v') AND relname=%s", (rel,))
-        if not cr.dictfetchall():
+        if not table_exists(cr, rel):
             if self.comodel_name not in model.env:
                 raise UserError(_('Many2many comodel does not exist: %r') % (self.comodel_name,))
             comodel = model.env[self.comodel_name]
             cr.execute('CREATE TABLE "%s" ("%s" INTEGER NOT NULL, "%s" INTEGER NOT NULL, UNIQUE("%s","%s"))' % (rel, id1, id2, id1, id2))
             # create foreign key references with ondelete=cascade, unless the targets are SQL views
-            cr.execute("SELECT relkind FROM pg_class WHERE relkind IN ('v') AND relname=%s", (comodel._table,))
-            if not cr.fetchall():
+            if table_kind(cr, comodel._table) != 'v':
                 model.pool.post_init(model._m2o_add_foreign_key_unchecked, rel, id2, comodel, 'cascade', self._module)
-            cr.execute("SELECT relkind FROM pg_class WHERE relkind IN ('v') AND relname=%s", (model._table,))
-            if not cr.fetchall():
+            if table_kind(cr, model._table) != 'v':
                 model.pool.post_init(model._m2o_add_foreign_key_unchecked, rel, id1, model, 'cascade', self._module)
 
             cr.execute('CREATE INDEX ON "%s" ("%s")' % (rel, id1))
diff --git a/odoo/models.py b/odoo/models.py
index ecf6c4acc08..d0ebaa8a82b 100644
--- a/odoo/models.py
+++ b/odoo/models.py
@@ -615,12 +615,7 @@ class BaseModel(object):
     @api.model
     @ormcache()
     def _is_an_ordinary_table(self):
-        self.env.cr.execute("""\
-            SELECT  1
-            FROM    pg_class
-            WHERE   relname = %s
-            AND     relkind = %s""", [self._table, 'r'])
-        return bool(self.env.cr.fetchone())
+        return tools.table_kind(self.env.cr, self._table) == 'r'
 
     def __export_xml_id(self):
         """ Return a valid xml_id for the record ``self``. """
@@ -2053,13 +2048,7 @@ class BaseModel(object):
                 _logger.debug("column %s is in the table %s but not in the corresponding object %s",
                               row['attname'], self._table, self._name)
             if row['attnotnull']:
-                cr.execute('ALTER TABLE "%s" ALTER COLUMN "%s" DROP NOT NULL' % (self._table, row['attname']))
-                _schema.debug("Table '%s': column '%s': dropped NOT NULL constraint",
-                              self._table, row['attname'])
-
-    @api.model_cr
-    def _drop_constraint(self, source_table, constraint_name):
-        self._cr.execute("ALTER TABLE %s DROP CONSTRAINT %s" % (source_table, constraint_name))
+                tools.drop_not_null(self._table, row['attname'])
 
     # checked version: for direct m2o starting from ``self``
     def _m2o_add_foreign_key_checked(self, source_field, dest_model, ondelete):
@@ -2112,18 +2101,14 @@ class BaseModel(object):
                 if cons['ondelete_rule'] != POSTGRES_CONFDELTYPES.get((ondelete or 'set null').upper(), 'a')\
                     or cons['foreign_table'] != dest_model._table:
                     # Wrong FK: drop it and recreate
-                    _schema.debug("Table '%s': dropping obsolete FK constraint: '%s'",
-                                  source_table, cons['constraint_name'])
-                    self._drop_constraint(source_table, cons['constraint_name'])
+                    tools.drop_constraint(self._cr, source_table, cons['constraint_name'])
                 else:
                     # it's all good, nothing to do!
                     return
             else:
                 # Multiple FKs found for the same field, drop them all, and re-create
                 for cons in constraints:
-                    _schema.debug("Table '%s': dropping duplicate FK constraints: '%s'",
-                                  source_table, cons['constraint_name'])
-                    self._drop_constraint(source_table, cons['constraint_name'])
+                    tools.drop_constraint(self._cr, source_table, cons['constraint_name'])
 
         # (re-)create the FK
         self._m2o_add_foreign_key_checked(source_field, dest_model, ondelete)
@@ -2187,26 +2172,26 @@ class BaseModel(object):
         cr = self._cr
         parent_store_compute = False
         update_custom_fields = self._context.get('update_custom_fields', False)
-        create = not self._table_exist()
+        must_create_table = not tools.table_exists(cr, self._table)
 
         if self._auto:
 
-            if create:
-                self._create_table()
+            if must_create_table:
+                tools.create_model_table(cr, self._table, self._description)
                 has_rows = False
             else:
                 cr.execute('SELECT 1 FROM "%s" LIMIT 1' % self._table)
                 has_rows = cr.rowcount
 
             if self._parent_store:
-                if not self._parent_columns_exist():
+                if not tools.column_exists(cr, self._table, 'parent_left'):
                     self._create_parent_columns()
                     parent_store_compute = True
 
             self._check_removed_columns(log=False)
 
             # retrieve existing database columns
-            column_data = self._select_column_data()
+            column_data = tools.table_columns(cr, self._table)
 
             for name, field in self._fields.iteritems():
                 if name == 'id':
@@ -2232,9 +2217,7 @@ class BaseModel(object):
                     if not res and hasattr(field, 'oldname'):
                         res = column_data.get(field.oldname)
                         if res:
-                            cr.execute('ALTER TABLE "%s" RENAME "%s" TO "%s"' % (self._table, field.oldname, name))
-                            res['attname'] = name
-                            _schema.debug("Table '%s': renamed column '%s' to '%s'", self._table, field.oldname, name)
+                            tools.rename_column(cr, self._table, field.oldname, name)
 
                     # The column already exists in database. Possibly change its
                     # type, rename it, drop it or change its constraints.
@@ -2257,33 +2240,12 @@ class BaseModel(object):
                                 ('float8', 'monetary', column_type[1], '::' + column_type[1]),
                             ]
                             if f_pg_type == 'varchar' and field.type == 'char' and f_pg_size and (field.size is None or f_pg_size < field.size):
-                                try:
-                                    with cr.savepoint():
-                                        cr.execute('ALTER TABLE "%s" ALTER COLUMN "%s" TYPE %s' % (self._table, name, column_type[1]), log_exceptions=False)
-                                except psycopg2.NotSupportedError:
-                                    # In place alter table cannot be done because a view is depending of this field.
-                                    # Do a manual copy. This will drop the view (that will be recreated later)
-                                    cr.execute('ALTER TABLE "%s" RENAME COLUMN "%s" TO temp_change_size' % (self._table, name))
-                                    cr.execute('ALTER TABLE "%s" ADD COLUMN "%s" %s' % (self._table, name, column_type[1]))
-                                    cr.execute('UPDATE "%s" SET "%s"=temp_change_size::%s' % (self._table, name, column_type[1]))
-                                    cr.execute('ALTER TABLE "%s" DROP COLUMN temp_change_size CASCADE' % (self._table,))
-                                _schema.debug("Table '%s': column '%s' (type varchar) changed size from %s to %s",
-                                              self._table, name, f_pg_size or 'unlimited', field.size or 'unlimited')
+                                tools.convert_column(cr, self._table, name, column_type[1])
                             for c in casts:
                                 if (f_pg_type == c[0]) and (field.type == c[1]):
                                     if f_pg_type != column_type[0]:
                                         converted = True
-                                        try:
-                                            with cr.savepoint():
-                                                cr.execute('ALTER TABLE "%s" ALTER COLUMN "%s" TYPE %s' % (self._table, name, c[2]), log_exceptions=False)
-                                        except psycopg2.NotSupportedError:
-                                            # can't do inplace change -> use a casted temp column
-                                            cr.execute('ALTER TABLE "%s" RENAME COLUMN "%s" TO __temp_type_cast' % (self._table, name))
-                                            cr.execute('ALTER TABLE "%s" ADD COLUMN "%s" %s' % (self._table, name, c[2]))
-                                            cr.execute('UPDATE "%s" SET "%s"= __temp_type_cast%s' % (self._table, name, c[3]))
-                                            cr.execute('ALTER TABLE "%s" DROP COLUMN  __temp_type_cast CASCADE' % (self._table,))
-                                        _schema.debug("Table '%s': column '%s' changed type from %s to %s",
-                                                      self._table, name, c[0], c[1])
+                                        tools.convert_column(cr, self._table, name, column_type[1])
                                     break
 
                             if f_pg_type != column_type[0]:
@@ -2291,46 +2253,27 @@ class BaseModel(object):
                                     i = 0
                                     while True:
                                         newname = name + '_moved' + str(i)
-                                        cr.execute("SELECT count(1) FROM pg_class c,pg_attribute a " \
-                                            "WHERE c.relname=%s " \
-                                            "AND a.attname=%s " \
-                                            "AND c.oid=a.attrelid ", (self._table, newname))
-                                        if not cr.fetchone()[0]:
+                                        if not tools.column_exists(cr, self._table, newname):
                                             break
                                         i += 1
                                     if f_pg_notnull:
-                                        cr.execute('ALTER TABLE "%s" ALTER COLUMN "%s" DROP NOT NULL' % (self._table, name))
-                                    cr.execute('ALTER TABLE "%s" RENAME COLUMN "%s" TO "%s"' % (self._table, name, newname))
-                                    cr.execute('ALTER TABLE "%s" ADD COLUMN "%s" %s' % (self._table, name, column_type[1]))
-                                    cr.execute("COMMENT ON COLUMN %s.\"%s\" IS %%s" % (self._table, name), (field.string,))
-                                    _schema.warning("Table `%s`: column `%s` has changed type (DB=%s, def=%s), data moved to column `%s`",
-                                                    self._table, name, f_pg_type, field.type, newname)
+                                        tools.drop_not_null(cr, self._table, name)
+                                    tools.rename_column(cr, self._table, name, newname)
+                                    tools.create_column(cr, self._table, name, column_type[1], field.string)
 
                             # if the field is required and hasn't got a NOT NULL constraint
                             if field.required and f_pg_notnull == 0:
                                 if has_rows:
                                     self._init_column(name)
                                 # add the NOT NULL constraint
-                                try:
-                                    with cr.savepoint():
-                                        cr.execute('ALTER TABLE "%s" ALTER COLUMN "%s" SET NOT NULL' % (self._table, name), log_exceptions=False)
-                                        _schema.debug("Table '%s': column '%s': added NOT NULL constraint",
-                                                      self._table, name)
-                                except Exception:
-                                    msg = "Table '%s': unable to set a NOT NULL constraint on column '%s' !\n"\
-                                        "If you want to have it, you should update the records and execute manually:\n"\
-                                        "ALTER TABLE %s ALTER COLUMN %s SET NOT NULL"
-                                    _schema.warning(msg, self._table, name, self._table, name)
+                                tools.set_not_null(cr, self._table, name)
                             elif not field.required and f_pg_notnull == 1:
-                                cr.execute('ALTER TABLE "%s" ALTER COLUMN "%s" DROP NOT NULL' % (self._table, name))
-                                _schema.debug("Table '%s': column '%s': dropped NOT NULL constraint",
-                                              self._table, name)
+                                tools.drop_not_null(cr, self._table, name)
                             # Verify index
                             indexname = '%s_%s_index' % (self._table, name)
-                            cr.execute("SELECT indexname FROM pg_indexes WHERE indexname = %s and tablename = %s", (indexname, self._table))
-                            res2 = cr.dictfetchall()
-                            if not res2 and field.index:
-                                cr.execute('CREATE INDEX "%s_%s_index" ON "%s" ("%s")' % (self._table, name, self._table, name))
+                            has_index = tools.index_exists(cr, indexname)
+                            if not has_index and field.index:
+                                tools.create_index(cr, indexname, self._table, [name])
                                 if field.type == 'text':
                                     # FIXME: for fields.text columns we should try creating GIN indexes instead (seems most suitable for an ERP context)
                                     msg = "Table '%s': Adding (b-tree) index for %s column '%s'."\
@@ -2338,10 +2281,8 @@ class BaseModel(object):
                                         " because there is a length limit for indexable btree values!\n"\
                                         "Use a search view instead if you simply want to make the field searchable."
                                     _schema.warning(msg, self._table, field.type, name)
-                            if res2 and not field.index:
-                                cr.execute('DROP INDEX "%s_%s_index"' % (self._table, name))
-                                msg = "Table '%s': dropping index for column '%s' of type '%s' as it is not required anymore"
-                                _schema.debug(msg, self._table, name, field.type)
+                            if has_index and not field.index:
+                                tools.drop_index(cr, indexname, self._table)
 
                             if field.type == 'many2one':
                                 comodel = self.env[field.comodel_name]
@@ -2350,10 +2291,7 @@ class BaseModel(object):
 
                     else:
                         # the column doesn't exist in database, create it
-                        cr.execute('ALTER TABLE "%s" ADD COLUMN "%s" %s' % (self._table, name, field.column_type[1]))
-                        cr.execute("COMMENT ON COLUMN %s.\"%s\" IS %%s" % (self._table, name), (field.string,))
-                        _schema.debug("Table '%s': added column '%s' with definition=%s",
-                                      self._table, name, field.column_type[1])
+                        tools.create_column(cr, self._table, name, field.column_type[1], field.string)
 
                         # initialize it
                         if has_rows:
@@ -2372,28 +2310,15 @@ class BaseModel(object):
                             if comodel._auto and comodel._table != 'ir_actions':
                                 self.pool.post_init(self._m2o_add_foreign_key_checked, name, comodel, field.ondelete)
                         if field.index:
-                            cr.execute('CREATE INDEX "%s_%s_index" ON "%s" ("%s")' % (self._table, name, self._table, name))
+                            indexname = '%s_%s_index' % (self._table, name)
+                            tools.create_index(cr, indexname, self._table, [name])
                         if field.required:
-                            try:
-                                with cr.savepoint():
-                                    cr.execute('ALTER TABLE "%s" ALTER COLUMN "%s" SET NOT NULL' % (self._table, name))
-                                    _schema.debug("Table '%s': column '%s': added a NOT NULL constraint",
-                                                  self._table, name)
-                            except Exception:
-                                msg = "WARNING: unable to set column %s of table %s not null !\n"\
-                                    "Try to re-run: openerp-server --update=module\n"\
-                                    "If it doesn't work, update records and execute manually:\n"\
-                                    "ALTER TABLE %s ALTER COLUMN %s SET NOT NULL"
-                                _logger.warning(msg, name, self._table, self._table, name, exc_info=True)
-
-        else:
-            cr.execute("SELECT relname FROM pg_class WHERE relkind IN ('r','v') AND relname=%s", (self._table,))
-            create = not bool(cr.fetchone())
+                            tools.set_not_null(cr, self._table, name)
 
         if self._auto:
             self._add_sql_constraints()
 
-        if create:
+        if must_create_table:
             self._execute_sql()
 
         if parent_store_compute:
@@ -2407,54 +2332,21 @@ class BaseModel(object):
         pass
 
     @api.model_cr
-    def _table_exist(self):
-        query = "SELECT relname FROM pg_class WHERE relkind IN ('r','v') AND relname=%s"
-        self._cr.execute(query, (self._table,))
-        return self._cr.rowcount
-
-    @api.model_cr
-    def _create_table(self):
-        self._cr.execute('CREATE TABLE "%s" (id SERIAL NOT NULL, PRIMARY KEY(id))' % (self._table,))
-        self._cr.execute("COMMENT ON TABLE \"%s\" IS %%s" % self._table, (self._description,))
-        _schema.debug("Table '%s': created", self._table)
-
-    @api.model_cr
-    def _parent_columns_exist(self):
-        query = """ SELECT c.relname FROM pg_class c, pg_attribute a
-                    WHERE c.relname=%s AND a.attname=%s AND c.oid=a.attrelid """
-        self._cr.execute(query, (self._table, 'parent_left'))
-        return self._cr.rowcount
-
-    @api.model_cr
     def _create_parent_columns(self):
-        self._cr.execute('ALTER TABLE "%s" ADD COLUMN "parent_left" INTEGER' % (self._table,))
-        self._cr.execute('ALTER TABLE "%s" ADD COLUMN "parent_right" INTEGER' % (self._table,))
+        tools.create_column(self._cr, self._table, 'parent_left', 'INTEGER')
+        tools.create_column(self._cr, self._table, 'parent_right', 'INTEGER')
         if 'parent_left' not in self._fields:
             _logger.error("add a field parent_left on model %s: parent_left = fields.Integer('Left Parent', index=True)", self._name)
-            _schema.debug("Table '%s': added column '%s' with definition=%s", self._table, 'parent_left', 'INTEGER')
         elif not self._fields['parent_left'].index:
             _logger.error('parent_left field on model %s must be indexed! Add index=True to the field definition)', self._name)
         if 'parent_right' not in self._fields:
             _logger.error("add a field parent_right on model %s: parent_right = fields.Integer('Left Parent', index=True)", self._name)
-            _schema.debug("Table '%s': added column '%s' with definition=%s", self._table, 'parent_right', 'INTEGER')
         elif not self._fields['parent_right'].index:
             _logger.error("parent_right field on model %s must be indexed! Add index=True to the field definition)", self._name)
         if self._fields[self._parent_name].ondelete not in ('cascade', 'restrict'):
             _logger.error("The field %s on model %s must be set as ondelete='cascade' or 'restrict'", self._parent_name, self._name)
 
     @api.model_cr
-    def _select_column_data(self):
-        # attlen is the number of bytes necessary to represent the type when
-        # the type has a fixed size. If the type has a varying size attlen is
-        # -1 and atttypmod is the size limit + 4, or -1 if there is no limit.
-        query = """ SELECT c.relname, a.attname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, t.typname,
-                           CASE WHEN a.attlen=-1 THEN (CASE WHEN a.atttypmod=-1 THEN 0 ELSE a.atttypmod-4 END) ELSE a.attlen END as size
-                    FROM pg_class c, pg_attribute a, pg_type t
-                    WHERE c.relname=%s AND c.oid=a.attrelid AND a.atttypid=t.oid """
-        self._cr.execute(query, (self._table,))
-        return {row['attname']: row for row in self._cr.dictfetchall()}
-
-    @api.model_cr
     def _add_sql_constraints(self):
         """
 
@@ -2468,37 +2360,16 @@ class BaseModel(object):
         def cons_text(txt):
             return txt.lower().replace(', ',',').replace(' (','(')
 
-        def drop(name, definition):
-            try:
-                with cr.savepoint():
-                    cr.execute('ALTER TABLE "%s" DROP CONSTRAINT "%s"' % (self._table, name))
-                    _schema.debug("Table '%s': dropped constraint '%s'. Reason: its definition changed to '%s'",
-                                  self._table, name, definition)
-            except Exception:
-                _schema.warning("Table '%s': unable to drop constraint '%s'!", self._table, definition)
-
-        def add(name, definition):
-            query = 'ALTER TABLE "%s" ADD CONSTRAINT "%s" %s' % (self._table, name, definition)
-            try:
-                with cr.savepoint():
-                    cr.execute(query)
-                    _schema.debug("Table '%s': added constraint '%s' with definition=%s",
-                                  self._table, name, definition)
-            except Exception:
-                _schema.warning("Table '%s': unable to add constraint '%s'!\n"
-                                "If you want to have it, you should update the records and execute manually:\n%s",
-                                self._table, definition, query)
-
         def process(key, definition):
             conname = '%s_%s' % (self._table, key)
-            cr.execute("SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname=%s", (conname,))
-            if not cr.rowcount:
+            has_definition = tools.constraint_definition(cr, conname)
+            if not has_definition:
                 # constraint does not exists
-                add(conname, definition)
-            elif cons_text(definition) != cons_text(cr.fetchone()[0]):
+                tools.add_constraint(cr, self._table, conname, definition)
+            elif cons_text(definition) != cons_text(has_definition):
                 # constraint exists but its definition may have changed
-                drop(conname, definition)
-                add(conname, definition)
+                tools.drop_constraint(cr, self._table, conname)
+                tools.add_constraint(cr, self._table, conname, definition)
 
         for (key, definition, _) in self._sql_constraints:
             if foreign_key_re.match(definition):
diff --git a/odoo/modules/db.py b/odoo/modules/db.py
index f2592f947e2..7106ff82b69 100644
--- a/odoo/modules/db.py
+++ b/odoo/modules/db.py
@@ -12,8 +12,7 @@ def is_initialized(cr):
     The database can be initialized with the 'initialize' function below.
 
     """
-    cr.execute("SELECT relname FROM pg_class WHERE relkind='r' AND relname='ir_module_module'")
-    return len(cr.fetchall()) > 0
+    return odoo.tools.table_exists(cr, 'ir_module_module')
 
 def initialize(cr):
     """ Initialize a database with for the ORM.
diff --git a/odoo/modules/registry.py b/odoo/modules/registry.py
index 5d20ff20dab..a6ab4ead1b4 100644
--- a/odoo/modules/registry.py
+++ b/odoo/modules/registry.py
@@ -15,7 +15,7 @@ import threading
 import odoo
 from .. import SUPERUSER_ID
 from odoo.tools import (assertion_report, lazy_classproperty, config,
-                        lazy_property, topological_sort, OrderedSet)
+                        lazy_property, table_exists, topological_sort, OrderedSet)
 from odoo.tools.lru import LRU
 
 _logger = logging.getLogger(__name__)
@@ -333,7 +333,7 @@ class Registry(Mapping):
         # make sure all tables are present
         missing = [name
                    for name, model in env.items()
-                   if not model._abstract and not model._table_exist()]
+                   if not model._abstract and not table_exists(cr, model._table)]
         if missing:
             _logger.warning("Models have no table: %s.", ", ".join(missing))
             # recreate missing tables following model dependencies
@@ -345,7 +345,7 @@ class Registry(Mapping):
             cr.commit()
             # check again, and log errors if tables are still missing
             for name, model in env.items():
-                if not model._abstract and not model._table_exist():
+                if not model._abstract and not table_exists(cr, model._table):
                     _logger.error("Model %s has no table.", name)
 
     def clear_caches(self):
diff --git a/odoo/service/db.py b/odoo/service/db.py
index 5e74c6d38b3..a9968963fb8 100644
--- a/odoo/service/db.py
+++ b/odoo/service/db.py
@@ -354,8 +354,7 @@ def list_db_incompatible(databases):
     server_version = '.'.join(map(str, version_info[:2]))
     for database_name in databases:
         with closing(db_connect(database_name).cursor()) as cr:
-            cr.execute("SELECT 1 FROM information_schema.tables WHERE table_name='ir_module_module'")
-            if cr.fetchone():
+            if odoo.tools.table_exists(cr, 'ir_module_module'):
                 cr.execute("SELECT latest_version FROM ir_module_module WHERE name=%s", ('base',))
                 base_version = cr.fetchone()
                 if not base_version or not base_version[0]:
diff --git a/odoo/tools/sql.py b/odoo/tools/sql.py
index 0c67657db8d..6bdcbb05303 100644
--- a/odoo/tools/sql.py
+++ b/odoo/tools/sql.py
@@ -1,6 +1,152 @@
 # -*- coding: utf-8 -*-
 # Part of Odoo. See LICENSE file for full copyright and licensing details.
 
+import logging
+import psycopg2
+
+_schema = logging.getLogger('odoo.schema')
+
+def table_exists(cr, tablename):
+    """ Return whether the given table exists. """
+    query = "SELECT 1 FROM pg_class WHERE relkind IN ('r','v') AND relname=%s"
+    cr.execute(query, (tablename,))
+    return cr.rowcount
+
+def table_kind(cr, tablename):
+    """ Return the kind of a table: 'r' for ordinary tables, 'v' for views. """
+    cr.execute("SELECT relkind FROM pg_class WHERE relname=%s", (tablename,))
+    return cr.fetchone()[0] if cr.rowcount else None
+
+def create_model_table(cr, tablename, comment=None):
+    """ Create the table for a model. """
+    cr.execute('CREATE TABLE "{}" (id SERIAL NOT NULL, PRIMARY KEY(id))'.format(tablename))
+    if comment:
+        cr.execute('COMMENT ON TABLE "{}" IS %s'.format(tablename), (comment,))
+    _schema.debug("Table %r: created", tablename)
+
+def table_columns(cr, tablename):
+    """ Return a dict mapping column names to their configuration. The latter is
+        a dict with the following keys: `relname` (table name), `attname`
+        (column name), `attlen`, `atttypmod`, `attnotnull` (whether it has a NOT
+        NULL constraint), `atthasdef` (whether it has a default value),
+        `typname` (data type name), `size` (varchar size).
+    """
+    # attlen is the number of bytes necessary to represent the type when the
+    # type has a fixed size. If the type has a varying size attlen is -1 and
+    # atttypmod is the size limit + 4, or -1 if there is no limit.
+    query = """ SELECT c.relname, a.attname, a.attlen, a.atttypmod,
+                       a.attnotnull, a.atthasdef, t.typname,
+                       CASE WHEN a.attlen=-1 THEN (
+                           CASE WHEN a.atttypmod=-1 THEN 0 ELSE a.atttypmod-4 END
+                       ) ELSE a.attlen END as size
+                FROM pg_class c, pg_attribute a, pg_type t
+                WHERE c.relname=%s AND c.oid=a.attrelid AND a.atttypid=t.oid """
+    cr.execute(query, (tablename,))
+    return {row['attname']: row for row in cr.dictfetchall()}
+
+def column_exists(cr, tablename, columnname):
+    """ Return whether the given column exists. """
+    query = """ SELECT 1 FROM pg_class c, pg_attribute a
+                WHERE c.relname=%s AND a.attname=%s AND c.oid=a.attrelid """
+    cr.execute(query, (tablename, columnname))
+    return cr.rowcount
+
+def create_column(cr, tablename, columnname, columntype, comment=None):
+    """ Create a column with the given type. """
+    cr.execute('ALTER TABLE "{}" ADD COLUMN "{}" {}'.format(tablename, columnname, columntype))
+    if comment:
+        cr.execute('COMMENT ON COLUMN "{}"."{}" IS %s'.format(tablename, columnname), (comment,))
+    _schema.debug("Table %r: added column %r of type %s", tablename, columnname, columntype)
+
+def rename_column(cr, tablename, columnname1, columnname2):
+    """ Rename the given column. """
+    cr.execute('ALTER TABLE "{}" RENAME COLUMN "{}" TO "{}"'.format(tablename, columnname1, columnname2))
+    _schema.debug("Table %r: renamed column %r to %r", tablename, columnname1, columnname2)
+
+def convert_column(cr, tablename, columnname, columntype):
+    """ Convert the column to the given type. """
+    try:
+        with cr.savepoint():
+            cr.execute('ALTER TABLE "{}" ALTER COLUMN "{}" TYPE {}'.format(tablename, columnname, columntype),
+                       log_exceptions=False)
+    except psycopg2.NotSupportedError:
+        # can't do inplace change -> use a casted temp column
+        query = 'ALTER TABLE "{0}" RENAME COLUMN "{1}" TO __temp_type_cast; ' \
+                'ALTER TABLE "{0}" ADD COLUMN "{1}" {2}; ' \
+                'UPDATE "{0}" SET "{1}"= __temp_type_cast::{2}' \
+                'ALTER TABLE "{0}" DROP COLUMN  __temp_type_cast CASCADE'
+        cr.execute(query.format(tablename, columntype, columntype))
+    _schema.debug("Table %r: column %r changed to type %s", tablename, columntype, columntype)
+
+def set_not_null(cr, tablename, columnname):
+    """ Add a NOT NULL constraint on the given column. """
+    query = 'ALTER TABLE "{}" ALTER COLUMN "{}" SET NOT NULL'.format(tablename, columnname)
+    try:
+        with cr.savepoint():
+            cr.execute(query)
+            _schema.debug("Table %r: column %r: added constraint NOT NULL", tablename, columnname)
+    except Exception:
+        msg = "Table %r: unable to set NOT NULL on column %r!\n" \
+              "If you want to have it, you should update the records and execute manually:\n%s"
+        _schema.warning(msg, tablename, columnname, query, exc_info=True)
+
+def drop_not_null(cr, tablename, columnname):
+    """ Drop the NOT NULL constraint on the given column. """
+    cr.execute('ALTER TABLE "{}" ALTER COLUMN "{}" DROP NOT NULL'.format(tablename, columnname))
+    _schema.debug("Table %r: column %r: dropped constraint NOT NULL", tablename, columnname)
+
+def constraint_definition(cr, constraintname):
+    """ Return the given constraint's definition. """
+    cr.execute("SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname=%s", (constraintname,))
+    return cr.fetchone()[0] if cr.rowcount else None
+
+def add_constraint(cr, tablename, constraintname, definition):
+    """ Add a constraint on the given table. """
+    query = 'ALTER TABLE "{}" ADD CONSTRAINT "{}" {}'.format(tablename, constraintname, definition)
+    try:
+        with cr.savepoint():
+            cr.execute(query)
+            _schema.debug("Table %r: added constraint %r as %s", tablename, constraintname, definition)
+    except Exception:
+        msg = "Table %r: unable to add constraint %r!\n" \
+              "If you want to have it, you should update the records and execute manually:\n%s"
+        _schema.warning(msg, tablename, constraintname, query, exc_info=True)
+
+def drop_constraint(cr, tablename, constraintname):
+    """ drop the given constraint. """
+    try:
+        with cr.savepoint():
+            cr.execute('ALTER TABLE "{}" DROP CONSTRAINT "{}"'.format(tablename, constraintname))
+            _schema.debug("Table %r: dropped constraint %r", tablename, constraintname)
+    except Exception:
+        _schema.warning("Table %r: unable to drop constraint %r!", tablename, constraintname)
+
+def index_exists(cr, indexname):
+    """ Return whether the given index exists. """
+    cr.execute("SELECT 1 FROM pg_indexes WHERE indexname=%s", (indexname,))
+    return cr.rowcount
+
+def create_index(cr, indexname, tablename, expressions):
+    """ Create the given index unless it exists. """
+    if index_exists(cr, indexname):
+        return
+    args = ', '.join(expressions)
+    cr.execute('CREATE INDEX "{}" ON "{}" ({})'.format(indexname, tablename, args))
+    _schema.debug("Table %r: created index %r (%s)", tablename, indexname, args)
+
+def create_unique_index(cr, indexname, tablename, expressions):
+    """ Create the given index unless it exists. """
+    if index_exists(cr, indexname):
+        return
+    args = ', '.join(expressions)
+    cr.execute('CREATE UNIQUE INDEX "{}" ON "{}" ({})'.format(indexname, tablename, args))
+    _schema.debug("Table %r: created index %r (%s)", tablename, indexname, args)
+
+def drop_index(cr, indexname, tablename):
+    """ Drop the given index if it exists. """
+    cr.execute('DROP INDEX IF EXISTS "{}"'.format(indexname))
+    _schema.debug("Table %r: dropped index %r", tablename, indexname)
+
 def drop_view_if_exists(cr, viewname):
     cr.execute("DROP view IF EXISTS %s CASCADE" % (viewname,))
     cr.commit()