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 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)))
