def renderTable(self, table, createIndexes=True): """ Renders all SQL statements for the given table. Args: table (sqlalchemy.sql.schema.Table): The Table instance to render createIndexes (bool): Whether or not it should create table indexes Returns: str: The compiled SQL statements """ ddl = [] ddl.append('--\n-- Structure for table "{}"\n--\n'.format(_(table.name))) ddl.append(self._compiler.createTable(table)) if table._records: ddl.append('\n--\n-- Data for table "{}"\n--\n'.format(_(table.name))) ddl.append(self._compiler.inserts(table, table._records.values())) if table.foreign_keys and self._dialect.supports_alter: ddl.append('\n--\n-- Constraints for table "{}"\n--\n'.format(_(table.name))) ddl.append(self._compiler.addConstraints(table)) if createIndexes and table.indexes: ddl.append('\n--\n-- Indexes for table "{}"\n--\n'.format(_(table.name))) ddl.append(self._compiler.createIndexes(table)) return '\n'.join(ddl)
def _compileForeignKeyConstraint(self) -> str: """ Compiles the DDL statement for a table foreign key constraint element. Returns: The compiled DDL statement for the table foreign key constraint element """ ddl = '' if self.constraint.name is not None: ddl += 'CONSTRAINT {name} '.format( name=self.naming_convention[type(self.constraint)]) \ .format(table_name=i18n._(self.constraint.table.name), column_name=i18n._(self.constraint.column_keys[0])) ddl += 'FOREIGN KEY ({columns}) REFERENCES {ref_table} ({ref_columns})' \ .format(columns=', '.join(i18n._(element.parent.name) for element in self.constraint.elements), ref_table=i18n._( list(self.constraint.elements)[0].column.table.name), ref_columns=', '.join( i18n._(element.column.name) for element in self.constraint.elements)) # Cascading if self.constraint.ondelete is not None: ddl += ' ON DELETE {}'.format(self.constraint.ondelete) if self.constraint.onupdate is not None: ddl += ' ON UPDATE {}'.format(self.constraint.onupdate) return ddl
def foreignKeyConstraint(self, constraint): """ Compiles a given foreign key constraint element for use in CREATE TABLE and ALTER TABLE statements. Args: constraint (sqlalchemy.sql.schema.ForeignKeyConstraint): The ForeignKeyConstraint instance to compile Returns: str: The DDL for the foreign key constraint element """ ddl = '' if constraint.name is not None: ddl += 'CONSTRAINT {} '.format(_(constraint.name)) remote_table = list(constraint.elements)[0].column.table ddl += 'FOREIGN KEY ({}) REFERENCES {} ({})'.format( ', '.join(_(element.parent.name) for element in constraint.elements), _(remote_table.name), ', '.join(_(element.column.name) for element in constraint.elements)) return ddl
def encode(self, data, **options) -> FileStream: """ Encodes the data into a XML file-like stream. Args: data: The data to encode **options: The encoding options Returns: A XML file-like stream Raises: geodatabr.encoders.EncodeError: If data fails to encode """ try: database = Element('database', name=_('dataset_name')) for table_name, rows in iter(data.items()): database.append(Comment(' Table {} '.format(table_name))) table = SubElement(database, 'table', name=table_name) for row_data in iter(rows.values()): row = SubElement(table, 'row') for column_name, column_value in iter(row_data.items()): SubElement(row, 'field', name=column_name).text =\ column_value xml_data = xml_str(database, **dict(self.options, **options)) return FileStream(xml_data.decode()) except Exception: raise EncodeError
def renderDatasetFiles(self) -> str: """ Renders the dataset files info. Returns: The dataset files info """ files = list(self._dataset_dir.files( pattern=i18n._('dataset_name') + '*')) grouped_files = itertools.groupby( sorted(files, key=lambda file: file.format.type), key=lambda file: file.format.type) listing = [] for dataset_type, dataset_files in grouped_files: listing.append(self._markdown.header(dataset_type, depth=4)) headers = ['File', 'Format', 'Size'] alignment = ['<', '^', '>'] rows = [] for dataset_file in dataset_files: dataset_format = '-' if dataset_file.format: dataset_format = self._markdown.link( dataset_file.format.info, dataset_file.format.friendlyName) rows.append([self._markdown.code(dataset_file.name), dataset_format, '{:9,d}'.format(dataset_file.size)]) listing.append(self._markdown.table([headers] + rows, alignment)) return '\n'.join(listing)
def createTable(self, table): """ Compiles a CREATE TABLE statement for a given table. Args: table (sqlalchemy.sql.schema.Table): The Table instance to compile Returns: str: The DDL for the CREATE TABLE statement """ ddl = 'CREATE ' if table._prefixes: ddl += ' '.join(table._prefixes) + ' ' ddl += 'TABLE {} ('.format(_(table.name)) ddl += self.createColumns(table) constraints_ddl = self.createConstraints(table) if constraints_ddl: ddl += ',\n ' + constraints_ddl ddl += '\n);' return ddl
def compile(self) -> str: """ Compiles the DDL statements for the table constraints. Returns: The compiled DDL statements for the table constraints """ # pylint: disable=protected-access if not self.use_alter: return ',\n '.join( str(Constraint(constraint, dialect=self.dialect.name)) for constraint in self.table._sorted_constraints if (not getattr(constraint, 'use_alter', False) or not self.dialect.supports_alter)) if not self.table.foreign_keys or not self.dialect.supports_alter: return '' ddl = '\n\n--\n-- Constraints for table "{table}"\n--\n\n{constraints}' return ddl.format( table=i18n._(self.table.name), constraints='\n'.join( str(Constraint(constraint, dialect=self.dialect.name)) for constraint in self.table._sorted_constraints if (isinstance(constraint, schema.ForeignKeyConstraint) and getattr(constraint, 'use_alter', False))))
def insert(self, table, record): """ Compiles a INSERT statement for a given table and record. Args: table (sqlalchemy.sql.schema.Table): The Table instance to compile record (geodatabr.core.types.OrderedMap): The table record mapping Returns: str: The DML for the INSERT statements """ def _compileLiterals(record): return ', '.join( table.columns.get(column).type.literal_processor( dialect=self._dialect)(value) for column, value in record.items()) dml = 'INSERT INTO ' + _(table.name) if any(isinstance(record, _type) for _type in (list, set, tuple)) \ and self._dialect.supports_multivalues_insert: dml += ' VALUES {};'.format( ', '.join('({})'.format(_compileLiterals(_record)) for _record in record)) else: dml += ' VALUES ({});'.format(_compileLiterals(record)) return dml
def handle(self, args: argparse.Namespace): """ Handles the command. Args: args: The command arguments """ if not args.format: self._parser.error( 'You need to give the output format you want to encode.') i18n.Translator.locale = args.locale logger = logging.logger() try: encoder = encoders.EncoderFactory.fromFormat(args.format) serializer = serializers.Serializer(**encoder.serializationOptions) entity_map = dict(zip(schema.TABLES, schema.ENTITIES)) logger.info('Encoding dataset to %s format...', encoder.format.friendlyName) dataset_dir = io.Directory(io.Path.DATA_DIR / args.locale) dataset_dir.create(parents=True) with dataset_dir: if encoder.format.isFlatFile: for table in args.tables: table_name = i18n._(table) entity = (entity_map.get(table),) encoder.encodeToFile( serializer.serialize(entity).get(table_name), '{dataset_name}-{table_name}{extension}'.format( dataset_name=i18n._('dataset_name'), table_name=table_name, extension=encoder.format.extension)) return encoder.encodeToFile( serializer.serialize(tuple(entity_map.get(table) for table in args.tables)), i18n._('dataset_name') + encoder.format.extension) except encoders.EncodeError: self._parser.error('Failed to encode dataset.') except KeyboardInterrupt: self._parser.terminate('Encoding was canceled.')
def _compilePrimaryKeyConstraint(self) -> str: """ Compiles the DDL statement for a table primary key constraint element. Returns: The compiled DDL statement for the table primary key constraint element """ ddl = '' if self.constraint.name is not None: ddl += 'CONSTRAINT {name} '.format( name=self.naming_convention[type(self.constraint)]) \ .format(table_name=i18n._(self.constraint.table.name)) return ddl + 'PRIMARY KEY ({columns})'.format(columns=', '.join( i18n._(column.name) for column in self.constraint.columns))
def _compileUniqueConstraint(self) -> str: """ Compiles the DDL statement for a table unique constraint element. Returns: The compiled DDL statement for the table unique constraint element """ ddl = '' if self.constraint.name is not None: ddl += 'CONSTRAINT {name} '.format( name=self.naming_convention[type(self.constraint)]) \ .format(table_name=i18n._(self.constraint.table.name)) ddl += 'UNIQUE {columns}'.format(columns=', '.join( i18n._(column.name) for column in self.constraint)) return ddl
def serialize(self) -> OrderedMap: """ Serializes the dataset records. Returns: The serialized dataset records mapping """ records = OrderedMap() for entity in Entities: table = str(entity.__table__.name) _records = self.__records__[table] if not _records: continue if self._options.localize: table = _(table) records[table] = OrderedMap() for _record in _records: _record = _record.serialize() record = OrderedMap() for column in entity.__table__.columns: column = str(column.name) value = _record[column] if self._options.localize: column = _(column) record[column] = str( value) if self._options.forceStr else value row_id = str( record.id) if self._options.forceStrKeys else record.id if not self._options.includeKey: del record.id records[table][row_id] = record return records
def createIndex(self, index): """ Compiles a CREATE INDEX statement for a given index. Args: index (sqlalchemy.sql.schema.Index): The Index instance to compile Returns: str: The DDL for the CREATE INDEX statement """ ddl = 'CREATE ' if index.unique: ddl += 'UNIQUE ' ddl += 'INDEX {} ON {} ({});'.format( _(index.name), _(index.table.name), ', '.join(_(column.name) for column in index.expressions)) return ddl
def addConstraint(self, constraint): """ Compiles a ALTER TABLE ADD CONSTRAINT statement for a given constraint. Args: constraint: The constraint instance to compile Returns: str: The DDL for the ALTER TABLE ADD CONSTRAINT statement """ return 'ALTER TABLE {} ADD {};'.format(_(constraint.table.name), self.constraint(constraint))
def serialize(self, entities: Iterator[Entity]) -> types.OrderedMap: """ Serializes the dataset rows. Args: entities: The list of entities to serialize Returns: The serialized dataset rows mapping """ rows = types.OrderedMap() for entity in entities: table_name = str(entity.__table__.name) repository = RepositoryFactory.fromEntity(entity) _rows = repository.findAll() if not _rows: continue if self._options.localize: table_name = i18n._(table_name) rows[table_name] = types.List() for _row in _rows: row = types.OrderedMap() for column, value in _row.serialize().items(): if self._options.localize: column = i18n._(column) if self._options.forceStr or column == 'name': value = str(value) row[column] = value rows[table_name].append(row) return rows
def primaryKeyConstraint(self, constraint): """ Compiles a given primary key constraint element for use in CREATE TABLE and ALTER TABLE statements. Args: constraint (sqlalchemy.sql.schema.PrimaryKeyConstraint): The PrimaryKeyConstraint instance to compile Returns: str: The DDL for the primary key constraint element """ ddl = '' if constraint.name is not None: ddl += 'CONSTRAINT {} '.format(_(constraint.name)) ddl += 'PRIMARY KEY ({})'.format( ', '.join(_(_constraint.name) for _constraint in constraint.columns)) return ddl
def compile(self) -> str: """ Compiles the DML statement for the table row. Returns: The compiled DML statement for the table row """ def _compile_literals(row): return ', '.join( self.table.columns.get(column).type.literal_processor( dialect=self.dialect)(value) for column, value in row.items()) if (any(isinstance(self.row, _type) for _type in (list, set, tuple)) and self.dialect.supports_multivalues_insert): return 'INSERT INTO {table} VALUES {values};'.format( table=i18n._(self.table.name), values=', '.join('({})'.format(_compile_literals(row)) for row in self.row)) return 'INSERT INTO {table} VALUES ({values});'.format( table=i18n._(self.table.name), values=_compile_literals(self.row))
def compile(self) -> str: """ Compiles the DDL statement for the table index element. Returns: The compiled DDL statement for the table index element """ # pylint: disable=protected-access ddl = 'CREATE ' if self.index.unique: ddl += 'UNIQUE ' ddl += 'INDEX {name} ON {table_name} ({column_names});' return ddl.format(name=self.naming_convention[type(self.index)], table_name=i18n._(self.index.table.name), column_names=', '.join( i18n._(column.name) for column in self.index.expressions)) \ .format(table_name=i18n._(self.index.table.name), column_name=i18n._(self.index.expressions[0].name))
def encode(self, data, **options) -> io.BinaryFileStream: """ Encodes the data into a XML file-like stream. Args: data: The data to encode **options: The encoding options Returns: A XML file-like stream Raises: geodatabr.core.encoders.EncodeError: If data fails to encode """ # pylint: disable=protected-access try: dataset = etree.Element(i18n._('dataset_name')) entities = { i18n._(entity.__table__.name): i18n._(entity._name) for entity in schema.ENTITIES } for table_name, rows in iter(data.items()): table = etree.SubElement(dataset, table_name) for row in rows: etree.SubElement(table, entities.get(table_name), row) xml_data = etree.tostring(dataset, **dict(self.options, **options)) # Workaround for hard-coded single quoting xml_declaration = xml_data[:xml_data.find(b'?>') + 2] xml_data = xml_data.replace(xml_declaration, xml_declaration.replace(b"'", b'"')) return io.BinaryFileStream(xml_data) except Exception: raise encoders.EncodeError
def encode(self, data, **options) -> io.BinaryFileStream: """ Encodes the data into a XML file-like stream. Args: data: The data to encode **options: The encoding options Returns: A XML file-like stream Raises: geodatabr.core.encoders.EncodeError: If data fails to encode """ # pylint: disable=protected-access try: dataset = etree.Element(i18n._('dataset_name')) entities = {i18n._(entity.__table__.name): i18n._(entity._name) for entity in schema.ENTITIES} for table_name, rows in iter(data.items()): table = etree.SubElement(dataset, table_name) for row in rows: etree.SubElement(table, entities.get(table_name), row) xml_data = etree.tostring(dataset, **dict(self.options, **options)) # Workaround for hard-coded single quoting xml_declaration = xml_data[:xml_data.find(b'?>') + 2] xml_data = xml_data.replace(xml_declaration, xml_declaration.replace(b"'", b'"')) return io.BinaryFileStream(xml_data) except Exception: raise encoders.EncodeError
def compile(self) -> str: """ Compiles the DML statements for the table rows. Returns: The compiled DML statements for the table rows """ if not self.table.rows: return '' return '\n\n--\n-- Data for table "{table}"\n--\n\n{rows}'.format( table=i18n._(self.table.name), rows='\n'.join( str(Row(self.table, row, dialect=self.dialect.name)) for row in self.table.rows))
def compile(self) -> str: """ Compiles the DDL statements for the table indexes. Returns: The compiled DDL statements for the table indexes """ # pylint: disable=protected-access if not self.table.indexes: return '' return '\n\n--\n-- Indexes for table "{table}"\n--\n\n{indexes}' \ .format(table=i18n._(self.table.name), indexes='\n'.join( str(Index(index, dialect=self.dialect.name)) for index in self.table._sorted_indexes))
def compile(self) -> str: """ Compiles the DDL statement for the table constraint element. Returns: The compiled DDL statement for the table constraint element """ ddl = { schema.PrimaryKeyConstraint: self._compilePrimaryKeyConstraint, schema.ForeignKeyConstraint: self._compileForeignKeyConstraint, schema.UniqueConstraint: self._compileUniqueConstraint, }.get(type(self.constraint))() # Inline constraints if (not getattr(self.constraint, 'use_alter', False) or not self.dialect.supports_alter): return ddl return 'ALTER TABLE {table} ADD {constraint};'.format(table=i18n._( self.constraint.table.name), constraint=ddl)
def compile(self) -> str: """ Compiles the DDL statement for the table column element. Returns: The compiled DDL statement for the table column element """ ddl = '{name} {type}'.format(name=i18n._(self.column.name), type=self.column.type) if self.column.default is not None: ddl += ' DEFAULT ' + self.column.default if not self.column.nullable: ddl += ' NOT NULL' if self.column.constraints: ddl += ' '.join( str(Constraint(constraint, dialect=self.dialect.name)) for constraint in self.column.constraints) return ddl
def serialize(self) -> List: """ Serializes the dataset records. Returns: The serialized dataset records list """ records = List() for entity in Entities: for record in self.__records__[entity.__table__.name]: records.append( OrderedMap([(_(key), value) for key, value in record.serialize( flatten=True).items()])) records.sort(key=lambda record: ( record.get('state_id') or 0, record.get('mesoregion_id') or 0, record.get('microregion_id') or 0, record.get( 'municipality_id') or 0, record.get('district_id') or 0, record.get('subdistrict_id') or 0)) return records
def renderDatasetFiles(self) -> str: """ Renders the dataset files info. Returns: The dataset files info """ files = list( self._dataset_dir.files(pattern=i18n._('dataset_name') + '*')) grouped_files = itertools.groupby(sorted( files, key=lambda file: file.format.type), key=lambda file: file.format.type) listing = [] for dataset_type, dataset_files in grouped_files: listing.append(self._markdown.header(dataset_type, depth=4)) headers = ['File', 'Format', 'Size'] alignment = ['<', '^', '>'] rows = [] for dataset_file in dataset_files: dataset_format = '-' if dataset_file.format: dataset_format = self._markdown.link( dataset_file.format.info, dataset_file.format.friendlyName) rows.append([ self._markdown.code(dataset_file.name), dataset_format, '{:9,d}'.format(dataset_file.size) ]) listing.append(self._markdown.table([headers] + rows, alignment)) return '\n'.join(listing)
def encodeToFile(self, data, filename: str = 'auto', **options): """ Encodes the data into a file. Args: data: The data to encode filename: The filename to write, if any **options: The encoding options Raises: geodatabr.encoders.EncodeError: If data fails to encode """ data = self.encode(data, **options).read() encoder_format = self.format() if not filename: return sys.stdout.write(data + '\n') if filename == 'auto': filename = _('dataset_name') + encoder_format.extension with File(filename) \ .open('wb' if encoder_format.isBinary else 'w') as _file: _file.write(data)
def createColumn(self, column): """ Compiles a given column element for use in CREATE TABLE statement. Args: column (sqlalchemy.sql.schema.Column): The Column instance to compile Returns: str: The DDL for the column element """ ddl = '{} {}'.format(_(column.name), column.type) if column.default is not None: ddl += ' DEFAULT ' + column.default if not column.nullable: ddl += ' NOT NULL' if column.constraints: ddl += ' '.join(self.constraint(constraint) for constraint in column.constraints) return ddl
def compile(self) -> str: """ Compiles the DDL/DML statements for the table. Returns: The compiled DDL/DML statements for the table """ ddl = '--\n-- Structure for table "{table}"\n--\n\n' \ 'CREATE TABLE {table} (\n{columns}' \ .format(table=i18n._(self.table.name), columns=ColumnCollection(self.table, dialect=self.dialect.name)) if self.table.constraints: ddl += ',\n ' + str( ConstraintCollection(self.table, dialect=self.dialect.name)) return ddl + '\n);{rows}{constraints}{indexes}'.format( rows=str(RowCollection(self.table, dialect=self.dialect.name)), constraints=str( ConstraintCollection( self.table, use_alter=True, dialect=self.dialect.name)), indexes=str(IndexCollection(self.table, dialect=self.dialect.name)))