def postprocess_sp6tree(tree: Sp6TreeMeta, output_metadata, connection):
call_next_method(tree, output_metadata, connection)
meta = tree.__class__
output_table = output_metadata.tables[tree.output_record._meta.full_name]
treedefid_col = meta.treedefitem_table.c[get_primary_key_col(
meta.treedef_table).name]
scratch_table = Table(tree.output_record._meta.name + 'ConversionScratch',
output_metadata,
Column('id', postgresql.UUID, nullable=False),
Column('p_id', postgresql.UUID, nullable=True),
Column('name', Text, nullable=False),
Column('rank', Text, nullable=False),
schema=tree.output_record._meta.schema._meta.name)
scratch_table.drop(connection, checkfirst=True)
scratch_table.create(connection)
query = select([get_primary_key_col(tree.source_table),
tree.source_table.c.ParentID,
tree.source_table.c.Name,
meta.treedefitem_table.c.Name]) \
.select_from(tree.source_table.join(meta.treedefitem_table)) \
.where(treedefid_col == meta.treedef_id_value)
table_uuid = gen_table_uuid(tree.source_table)
print(table_uuid)
data = [
dict(id=gen_row_uuid(table_uuid, row[0]),
p_id=gen_row_uuid(table_uuid, row[1]),
name=row[2],
rank=row[3]) for row in query.execute()
]
connection.execute(scratch_table.insert(), data)
roots = select([scratch_table.c.id,
postgresql.hstore(scratch_table.c.rank, scratch_table.c.name) \
.label('tree_structure')]) \
.where(scratch_table.c.p_id == None) \
.cte(recursive=True)
node = scratch_table.alias('node')
nodes = roots.alias('nodes')
nodes = nodes.union_all(
select([node.c.id,
nodes.c.tree_structure + postgresql.hstore(
node.c.rank, node.c.name)]) \
.select_from(node.join(nodes, node.c.p_id == nodes.c.id)))
query = select([nodes.c.tree_structure
]).where(nodes.c.id == output_table.c.uuid)
update = output_table.update().values(tree_structure=query)
connection.execute(update)
scratch_table.drop(connection)
def postprocess_sp6tree(tree: Sp6TreeMeta, output_metadata, connection):
call_next_method(tree, output_metadata, connection)
meta = tree.__class__
output_table = output_metadata.tables[tree.output_record._meta.full_name]
treedefid_col = meta.treedefitem_table.c[get_primary_key_col(meta.treedef_table).name]
scratch_table = Table(tree.output_record._meta.name + 'ConversionScratch', output_metadata,
Column('id', postgresql.UUID, nullable=False),
Column('p_id', postgresql.UUID, nullable=True),
Column('name', Text, nullable=False),
Column('rank', Text, nullable=False),
schema=tree.output_record._meta.schema._meta.name)
scratch_table.drop(connection, checkfirst=True)
scratch_table.create(connection)
query = select([get_primary_key_col(tree.source_table),
tree.source_table.c.ParentID,
tree.source_table.c.Name,
meta.treedefitem_table.c.Name]) \
.select_from(tree.source_table.join(meta.treedefitem_table)) \
.where(treedefid_col == meta.treedef_id_value)
table_uuid = gen_table_uuid(tree.source_table)
print(table_uuid)
data = [dict(id = gen_row_uuid(table_uuid, row[0]),
p_id = gen_row_uuid(table_uuid, row[1]),
name = row[2],
rank = row[3])
for row in query.execute()]
connection.execute(scratch_table.insert(), data)
roots = select([scratch_table.c.id,
postgresql.hstore(scratch_table.c.rank, scratch_table.c.name) \
.label('tree_structure')]) \
.where(scratch_table.c.p_id == None) \
.cte(recursive=True)
node = scratch_table.alias('node')
nodes = roots.alias('nodes')
nodes = nodes.union_all(
select([node.c.id,
nodes.c.tree_structure + postgresql.hstore(
node.c.rank, node.c.name)]) \
.select_from(node.join(nodes, node.c.p_id == nodes.c.id)))
query = select([nodes.c.tree_structure]).where(nodes.c.id == output_table.c.uuid)
update = output_table.update().values(tree_structure=query)
connection.execute(update)
scratch_table.drop(connection)
def main(argv=sys.argv):
if len(argv) < 2:
usage(argv)
config_uri = argv[1]
map_id = argv[2]
setup_logging(config_uri)
settings = get_appsettings(config_uri)
engine = engine_from_config(settings, 'sqlalchemy.')
DBSession.configure(bind=engine)
# fill AdminZoneFinance with new variable set in MAPS_CONFIG
map_ids = [MAPS_CONFIG.keys()] if map_id == 'ALL' else [map_id]
for map_id in map_ids:
config = MAPS_CONFIG[map_id]
q = DBSession.query(AdminZoneFinance.data).filter(config['sql_filter'])
store = AdminZoneFinance.data
q.update(
{store: store + hstore(map_id, cast(config['sql_variable'], String))},
synchronize_session=False
)
transaction.commit()
def main(argv=sys.argv):
if len(argv) < 2:
usage(argv)
config_uri = argv[1]
map_id = argv[2]
setup_logging(config_uri)
settings = get_appsettings(config_uri)
engine = engine_from_config(settings, 'sqlalchemy.')
DBSession.configure(bind=engine)
# fill AdminZoneFinance with new variable set in MAPS_CONFIG
map_ids = [MAPS_CONFIG.keys()] if map_id == 'ALL' else [map_id]
for map_id in map_ids:
config = MAPS_CONFIG[map_id]
q = DBSession.query(AdminZoneFinance.data).filter(config['sql_filter'])
store = AdminZoneFinance.data
q.update(
{
store:
store + hstore(map_id, cast(config['sql_variable'], String))
},
synchronize_session=False)
transaction.commit()