def add_column(self, column_name, sql_type, default=None, comment=''):
database = Transaction().database
column_type = database.sql_type(sql_type)
match = VARCHAR_SIZE_RE.match(sql_type)
field_size = int(match.group(1)) if match else None
self._add_raw_column(column_name, column_type, default, field_size,
comment)
def _domain_column(self, operator, column, key=None):
database = Transaction().database
column = database.json_get(super()._domain_column(operator, column),
key)
if operator.endswith('like'):
column = Cast(column, database.sql_type('VARCHAR').base)
if self.search_unaccented and operator.endswith('ilike'):
column = database.unaccent(column)
return column
def column_is_type(self, column_name, type_, *, size=-1):
db_type = self._columns[column_name]['typname'].upper()
database = Transaction().database
base_type = database.sql_type(type_).base.upper()
if base_type == 'VARCHAR' and (size is None or size >= 0):
same_size = self._columns[column_name]['size'] == size
else:
same_size = True
return base_type == db_type and same_size
def add_column(self, column_name, sql_type, default=None, comment=''):
cursor = Transaction().connection.cursor()
database = Transaction().database
column_type = database.sql_type(sql_type)
match = VARCHAR_SIZE_RE.match(sql_type)
field_size = int(match.group(1)) if match else None
def add_comment():
if comment and self.is_owner:
cursor.execute(
SQL('COMMENT ON COLUMN {}.{} IS %s').format(
Identifier(self.table_name), Identifier(column_name)),
(comment, ))
if self.column_exist(column_name):
if (column_name in ('create_date', 'write_date')
and column_type[1].lower() != 'timestamp(6)'):
# Migrate dates from timestamp(0) to timestamp
cursor.execute(
SQL('ALTER TABLE {} ALTER COLUMN {} TYPE timestamp'
).format(Identifier(self.table_name),
Identifier(column_name)))
add_comment()
base_type = column_type[0].lower()
if base_type != self._columns[column_name]['typname']:
if (self._columns[column_name]['typname'], base_type) in [
('varchar', 'text'),
('text', 'varchar'),
('date', 'timestamp'),
('int4', 'int8'),
('int4', 'float8'),
('int4', 'numeric'),
('int8', 'float8'),
('int8', 'numeric'),
('float8', 'numeric'),
]:
self.alter_type(column_name, base_type)
else:
logger.warning(
'Unable to migrate column %s on table %s '
'from %s to %s.', column_name, self.table_name,
self._columns[column_name]['typname'], base_type)
if (base_type == 'varchar'
and self._columns[column_name]['typname'] == 'varchar'):
# Migrate size
from_size = self._columns[column_name]['size']
if field_size is None:
if from_size:
self.alter_size(column_name, base_type)
elif from_size == field_size:
pass
elif from_size and from_size < field_size:
self.alter_size(column_name, column_type[1])
else:
logger.warning(
'Unable to migrate column %s on table %s '
'from varchar(%s) to varchar(%s).', column_name,
self.table_name,
from_size if from_size and from_size > 0 else "",
field_size)
return
column_type = column_type[1]
cursor.execute(
SQL('ALTER TABLE {} ADD COLUMN {} {}').format(
Identifier(self.table_name), Identifier(column_name),
SQL(column_type)))
add_comment()
if default:
# check if table is non-empty:
cursor.execute('SELECT 1 FROM "%s" limit 1' % self.table_name)
if cursor.rowcount:
# Populate column with default values:
cursor.execute(
SQL('UPDATE {} SET {} = %s').format(
Identifier(self.table_name), Identifier(column_name)),
(default(), ))
self._update_definitions(columns=True)
def sql_type(self):
database = Transaction().database
return database.sql_type(self._sql_type)
def add_column(self, column_name, sql_type, default=None, comment=''):
cursor = Transaction().connection.cursor()
database = Transaction().database
column_type = database.sql_type(sql_type)
match = VARCHAR_SIZE_RE.match(sql_type)
field_size = int(match.group(1)) if match else None
if self.column_exist(column_name):
base_type = column_type[0].lower()
convert = {
'char': 'varchar',
'signed integer': 'bigint',
}
base_type = convert.get(base_type, base_type)
if base_type != self._columns[column_name]['typname']:
if (self._columns[column_name]['typname'], base_type) in (
('varchar', 'text'),
('text', 'varchar'),
('date', 'timestamp'),
('bigint', 'double'),
('int', 'bigint'),
('tinyint', 'bool'),
('decimal', 'numeric'),
):
self.alter_type(column_name, base_type)
else:
logger.warning(
'Unable to migrate column %s on table %s '
'from %s to %s.', column_name, self.table_name,
self._columns[column_name]['typname'], base_type)
if (base_type == 'varchar'
and self._columns[column_name]['typname'] == 'varchar'):
# Migrate size
from_size = self._columns[column_name]['size']
if field_size is None:
if from_size != 255:
self.alter_size(column_name, base_type)
elif self._columns[column_name]['size'] == field_size:
pass
else:
logger.warning(
'Unable to migrate column %s on table %s '
'from varchar(%s) to varchar(%s).', column_name,
self.table_name,
from_size if from_size and from_size > 0 else 255,
field_size)
return
column_type = column_type[1]
cursor.execute('ALTER TABLE `%s` ADD COLUMN `%s` %s' %
(self.table_name, column_name, column_type))
if default:
# check if table is non-empty:
cursor.execute('SELECT 1 FROM `%s` limit 1' % self.table_name)
if cursor.rowcount:
# Populate column with default values:
cursor.execute(
'UPDATE `' + self.table_name + '` '
'SET `' + column_name + '` = %s', (default(), ))
self._update_definitions(columns=True)
