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)