def build_delete_sp_sql(class_hierarchy, entity_keys, class_name):
table_name = get_table_name(class_name)
sql = """create procedure Delete{0}({1}) as
set nocount on
BEGIN TRY
BEGIN TRAN
{2}
COMMIT TRAN
END TRY
BEGIN CATCH
IF(@@TRANCOUNT > 0)
ROLLBACK TRAN;
THROW;
END CATCH
"""
params = ""
primary_key = ""
delete_sql = ""
parent_table, parent_class, (field_name, field_type, size, member_name,
index_info,
null_spec) = entity_keys[class_hierarchy[0]]
field_type, size = ('NVARCHAR',
'MAX') if field_type == 'JSON' else (field_type, size)
if not size:
params = '@{0} as [{1}]'.format(field_name, field_type)
else:
params = '@{0} as [{1}] ({2})'.format(field_name, field_type, size)
for class_name in class_hierarchy:
tbl = get_table_name(class_name)
delete_sql = "\n\t\tdelete from [{0}] where [{1}] = @{1}".format(
tbl, field_name) + delete_sql
sql = sql.format(table_name, params, delete_sql)
return sql
def build_get_sp_sql(hierarchy_class, entity_keys, class_name):
sql = """create procedure Get{0}s(@filter as nvarchar(256))
as
set nocount on
declare @sql nvarchar(4000)
set @sql = N'select {1} from {2} where ' + @filter
{3}
insert into #temp_{4} execute sp_executesql @sql
select * from #temp_{4}
drop table #temp_{4}
"""
parent_table, parent_class, (field_name, field_type, size, member_name,
index_info,
null_spec) = entity_keys[hierarchy_class[0]]
primary_key = field_name
table_name = get_table_name(class_name)
fields = ""
fields_temp_table = ""
for cls in hierarchy_class:
table_field_spec = get_table_spec(cls)
for field_spec in table_field_spec:
field_name, field_type, size, member_name, index_info, null_spec = field_spec
field_type, size = (
'NVARCHAR', 'MAX') if field_type == 'JSON' else (field_type,
size)
if not size:
fields_temp_table = fields_temp_table + '\n\t[{0}] [{1}],'.format(
field_name, field_type)
else:
fields_temp_table = fields_temp_table + '\n\t[{0}] [{1}] ({2}),'.format(
field_name, field_type, size)
fields = fields + '[' + get_table_name(
cls) + '].' + field_name + ', '
fields = fields[:-2]
inner_joins = ""
for i in range(1, len(hierarchy_class)):
tbl = get_table_name(hierarchy_class[i])
inner_joins = inner_joins + "\n\t\tinner join [{0}] on [{0}].{1} = [{2}].{1}".format(
tbl, primary_key, parent_table)
table_sql = "create table #temp_{0}(".format(table_name)
table_sql = table_sql + fields_temp_table[:-1] + ')'
sql = sql.format(table_name, fields,
'[' + parent_table + '] ' + inner_joins, table_sql,
table_name)
return sql
def create_table(hierarchy):
table_name = get_table_name(class_name)
sql, secondary_indexes = build_create_table_sql(
hierarchy, entity_keys, class_name)
db.execute(sql)
# secondry indexes
if len(secondary_indexes) > 0:
for sec_index_info in secondary_indexes:
field_name, unique_spec = sec_index_info
sql = """CREATE {0} NONCLUSTERED INDEX[IX_{1}] ON [{2}]
(
[{1}]
ASC
)WITH(PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, SORT_IN_TEMPDB=OFF, DROP_EXISTING=OFF, ONLINE=OFF,
ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON)
ON[PRIMARY]""".format(
'UNIQUE' if unique_spec == 'U' else '', field_name,
table_name)
db.execute(sql)
# constraint for child tables
if (len(hierarchy) > 1):
parent_table, parent_class, (
field_name, field_type, size, member_name, index_type,
null_spec) = entity_keys[hierarchy[0]]
sql_constraint = """ALTER TABLE [{0}] WITH CHECK ADD CONSTRAINT [FK_{0}_{1}] FOREIGN KEY([{2}])
REFERENCES [{1}] ([{2}])
ALTER TABLE [{0}] CHECK CONSTRAINT [FK_{0}_{1}]""".format(
table_name, parent_table, field_name)
db.execute(sql_constraint)
def build_sql(class_name, level):
table_name = get_table_name(class_name)
table_field_spec = get_table_spec(class_name)
params = ""
update_part_sql = ""
insert_params = ""
insert_param_values = ""
primary_key = ""
if level > 0:
parent_table, parent_class, (
field_name, field_type, size, member_name, index_info,
null_spec) = entity_keys[class_hierarchy[0]]
update_part_sql = update_part_sql + '[{0}] = @{1}, '.format(
field_name, field_name)
insert_params = insert_params + '[{0}], '.format(field_name)
insert_param_values = insert_param_values + '@{0}, '.format(
field_name)
primary_key = field_name
for field_spec in table_field_spec:
field_name, field_type, size, member_name, index_info, null_spec = field_spec
field_type, size = (
'NVARCHAR', 'MAX') if field_type == 'JSON' else (field_type,
size)
if index_info:
if (index_info[0] == 'PK'):
primary_key = field_name
if not size:
params = params + '@{0} as [{1}], '.format(
field_name, field_type)
else:
params = params + '@{0} as [{1}] ({2}), '.format(
field_name, field_type, size)
update_part_sql = update_part_sql + '[{0}] = @{1}, '.format(
field_name, field_name)
insert_params = insert_params + '[{0}], '.format(field_name)
insert_param_values = insert_param_values + '@{0}, '.format(
field_name)
update_part_sql = update_part_sql[:-2]
insert_params = insert_params[:-2]
insert_param_values = insert_param_values[:-2]
update_sql = "\n\t\tupdate [{0}] set {1} where [{2}] = @{2}".format(
table_name, update_part_sql, primary_key)
insert_sql = """\n\t\tinsert into [{0}] ({1})
values ({2})""".format(table_name, insert_params, insert_param_values)
return params, update_sql, insert_sql, primary_key
def build_create_table_sql(hierarchy, entity_keys, class_name):
table_name = get_table_name(class_name)
table_field_spec = get_table_spec(class_name)
def build_field_sql(field_name, field_type, size, null_spec):
if not size:
return '\n\t[{0}] [{1}] {2},'.format(field_name, field_type,
null_spec)
return '\n\t[{0}] [{1}] ({2}) {3},'.format(field_name, field_type,
size, null_spec)
sql = "create table [{0}](".format(table_name)
fields = ""
primary_key = None
secondary_indexes = []
process_hierarchy = True
for field_spec in table_field_spec:
if process_hierarchy:
if len(hierarchy) > 1:
parent = hierarchy[0]
parent_table, parent_class, (field_name, field_type, size,
member_name, index_info,
null_spec) = entity_keys[parent]
fields = fields + build_field_sql(field_name, field_type, size,
null_spec)
secondary_indexes.append((field_name, 'U'))
process_hierarchy = False
field_name, field_type, size, member_name, index_info, null_spec = field_spec
field_type, size = ('NVARCHAR',
'MAX') if field_type == 'JSON' else (field_type,
size)
if index_info:
if (index_info[0] == 'PK'):
primary_key = (field_name, field_type, size, index_info,
null_spec)
elif (index_info[0] == 'SK'):
secondary_indexes.append((field_name, index_info[1]))
fields = fields + build_field_sql(field_name, field_type, size,
null_spec)
## ther is a primary key
pk_sql = ""
if primary_key:
pk_sql = '\nCONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED \n([{1}] ASC) \nWITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]'.format(
table_name, primary_key[0])
sql = sql + fields + pk_sql + '\n) ON [PRIMARY]'
return sql, secondary_indexes
def clear_db_objects(classes):
for class_name in classes:
table_name = get_table_name(class_name)
sql_drop = "if exists (select * from sys.objects where name = '{0}' and type = 'u') drop table [{1}] ".format(
table_name, table_name)
db.execute(sql_drop)
sql_drop = "if exists (select * from sys.objects where name = 'Store{0}' and type = 'P') drop procedure Store{1} ".format(
table_name, table_name)
db.execute(sql_drop)
sql_drop = "if exists (select * from sys.objects where name = 'Delete{0}' and type = 'P') drop procedure Delete{1} ".format(
table_name, table_name)
db.execute(sql_drop)
sql_drop = "if exists (select * from sys.objects where name = 'Get{0}s' and type = 'P') drop procedure Get{1}s ".format(
table_name, table_name)
db.execute(sql_drop)
def get_entity_keys():
entity_keys = {}
classes = []
for class_name in GENERATION_SPEC.keys():
parent_class = get_parent_class(class_name)
table_name = get_table_name(class_name)
table_field_spec = get_table_spec(class_name)
classes = [class_name] + classes
pk_field_list = [
field_spec for field_spec in table_field_spec
if field_spec[4] and field_spec[4][0] == 'PK'
]
field_name, field_type, size, member_name, index_type, null_spec = (
None, None, None, None, None, None)
if (len(pk_field_list) == 1):
field_name, field_type, size, member_name, index_type, null_spec = pk_field_list[
0]
entity_keys[class_name] = (table_name, parent_class,
(field_name, field_type, size, member_name,
index_type, null_spec))
return entity_keys, classes
def build_store_entity(entity, class_hierarchy):
table_name = get_table_name(entity)
python_src = """from util.json import to_collection_json, to_json
{0}
{1}
def store_{2}(logger, cursor, entity):
try:
sql = \"\"\"
{3}
\"\"\"
params = ({4})
cursor.execute(sql, params)
except Exception as e:
logger.error("error while storing...")
logger.error(e)
raise
"""
python_src_composite_map = """def additional_fields_to_json(entity):
json_map = dict(){0}
return to_json(json_map)
"""
import_map_src = ""
if get_entity_table_map_type(
class_hierarchy[0]) == EntityTableMapType.COMPOSITE:
_, _, package_map = get_composite_map_spec(table_name)[0]
for key in package_map:
import_map_src = import_map_src + '\nfrom {0} import {1}'.format(
package_map[key], key)
params = ""
param_values = ""
for class_name in class_hierarchy:
member_spec = get_table_spec(class_name)
for field_spec in member_spec:
field_name, field_type, size, member_name, index_info, null_spec = field_spec
params = params + '\n\t\t\t\t@{0} = ?,'.format(field_name)
if member_name != "_":
if field_type != 'JSON':
param_values = param_values + 'entity.{0},\n '.format(
member_name)
else:
param_values = param_values + 'to_collection_json(entity.{0}),\n '.format(
member_name)
else: ## is a composite
param_values = param_values + 'additional_fields_to_json(entity),\n '
params = params[:-1]
param_values = param_values[:-14]
call_sp = "execute dbo.Store{0} {1}".format(entity, params)
if get_entity_table_map_type(class_name) == EntityTableMapType.COMPOSITE:
composite_src = ""
composite_spec = get_composite_map_spec(table_name)[1]
if_statement = 'if'
for key in composite_spec:
field_maps = ""
for field_spec in composite_spec[key]:
source_field, target_field = field_spec
field_maps = field_maps + ' json_map["{0}"] = entity.{1}\n'.format(
target_field, source_field)
field_maps = field_maps[:-1]
composite_src = composite_src + """\n {0} type(entity) == {1}:
{2}""".format(if_statement, key, field_maps)
if_statement = 'elif'
python_src_composite_map = python_src_composite_map.format(
composite_src)
else:
python_src_composite_map = ""
python_src = python_src.format(import_map_src, python_src_composite_map,
entity.lower(), call_sp, param_values)
return python_src
def build_get_entity(entity, class_hierarchy):
def build_composite_additional_fields_src(class_name, table_name, i):
composite_src = ""
if get_entity_table_map_type(
class_name) == EntityTableMapType.COMPOSITE:
json_map_src = "\n json_map = to_object(row[{0}])\n".format(
str(i))
composite_spec = get_composite_map_spec(table_name)[1]
if_statement = ' if'
for key in composite_spec:
field_maps = ""
for field_spec in composite_spec[key]:
source_field, target_field = field_spec
field_maps = field_maps + ' entity.{0} = json_map["{1}"]\n'.format(
source_field, target_field)
field_maps = field_maps[:-1]
composite_src = composite_src + """\n {0} type(entity) == {1}:
{2}""".format(if_statement, key, field_maps)
if_statement = ' elif'
return json_map_src + composite_src
table_name = get_table_name(entity)
python_src = """from util.json import to_collection_items, to_object
import importlib
{0}
from {1} import {2}
def get_{3}s(logger, connection, filter_sql):
entities = []
{4}
def read(row):
{5}
try:
cursor = connection.cursor()
sql = "execute dbo.Get{6}s @filter = ?"
params = (filter_sql)
cursor.execute(sql, params)
rows = cursor.fetchall()
list(map(read, rows))
cursor.close()
return entities
except Exception as e:
logger.error("error while getting staging entities...")
logger.error(e)
raise
"""
import_map_src = ""
if get_entity_table_map_type(
class_hierarchy[0]) == EntityTableMapType.COMPOSITE:
_, _, package_map = get_composite_map_spec(table_name)[0]
for key in package_map:
import_map_src = import_map_src + '\nfrom {0} import {1}'.format(
package_map[key], key)
entry_map_src = ""
modules_map_src = ""
if get_entity_table_map_type(
class_hierarchy[0]) == EntityTableMapType.COMPOSITE:
_, _, package_map = get_composite_map_spec(table_name)[0]
for key in package_map:
entry_map_src = entry_map_src + "'{0}':'{1}', ".format(
key, package_map[key])
modules_map_src = "\n module_map = {" + entry_map_src + "}"
map_src = ""
i = 0
for class_name in class_hierarchy:
member_spec = get_table_spec(class_name)
for field_spec in member_spec:
field_name, field_type, size, member_name, index_info, null_spec = field_spec
if member_name != "_":
if field_type != 'JSON':
map_src = map_src + "\n entity.{0} = row[{1}]".format(
member_name, str(i))
else:
map_src = map_src + "\n entity.{0} = to_collection_items(row[{1}])".format(
member_name, str(i))
else: # composite process
map_src = map_src + build_composite_additional_fields_src(
class_name, table_name, i)
i = i + 1
if get_entity_table_map_type(class_name) == EntityTableMapType.COMPOSITE:
_, index_type_entity, _ = get_composite_map_spec(table_name)[0]
map_src = """module = importlib.import_module(module_map[row[{0}]])
class_ = getattr(module, row[{0}])
entity = class_()
{1}\n entities.append(entity)""".format(index_type_entity,
map_src)
else:
map_src = "entity = {0}(){1}\n entities.append(entity)".format(
entity, map_src)
python_src = python_src.format(import_map_src,
get_module_name(class_name), entity,
entity.lower(), modules_map_src, map_src,
table_name)
return python_src
def build_store_sp_sql(class_hierarchy, entity_keys):
parent_table_name = class_hierarchy[0]
def build_sql(class_name, level):
table_name = get_table_name(class_name)
table_field_spec = get_table_spec(class_name)
params = ""
update_part_sql = ""
insert_params = ""
insert_param_values = ""
primary_key = ""
if level > 0:
parent_table, parent_class, (
field_name, field_type, size, member_name, index_info,
null_spec) = entity_keys[class_hierarchy[0]]
update_part_sql = update_part_sql + '[{0}] = @{1}, '.format(
field_name, field_name)
insert_params = insert_params + '[{0}], '.format(field_name)
insert_param_values = insert_param_values + '@{0}, '.format(
field_name)
primary_key = field_name
for field_spec in table_field_spec:
field_name, field_type, size, member_name, index_info, null_spec = field_spec
field_type, size = (
'NVARCHAR', 'MAX') if field_type == 'JSON' else (field_type,
size)
if index_info:
if (index_info[0] == 'PK'):
primary_key = field_name
if not size:
params = params + '@{0} as [{1}], '.format(
field_name, field_type)
else:
params = params + '@{0} as [{1}] ({2}), '.format(
field_name, field_type, size)
update_part_sql = update_part_sql + '[{0}] = @{1}, '.format(
field_name, field_name)
insert_params = insert_params + '[{0}], '.format(field_name)
insert_param_values = insert_param_values + '@{0}, '.format(
field_name)
update_part_sql = update_part_sql[:-2]
insert_params = insert_params[:-2]
insert_param_values = insert_param_values[:-2]
update_sql = "\n\t\tupdate [{0}] set {1} where [{2}] = @{2}".format(
table_name, update_part_sql, primary_key)
insert_sql = """\n\t\tinsert into [{0}] ({1})
values ({2})""".format(table_name, insert_params, insert_param_values)
return params, update_sql, insert_sql, primary_key
sql = """create procedure Store{0}({1}) as
set nocount on
BEGIN TRY
BEGIN TRAN
if exists(select * from [{0}] where [{2}] = @{2})
begin
{3}
end
else
begin
{4}
end
COMMIT TRAN
END TRY
BEGIN CATCH
IF(@@TRANCOUNT > 0)
ROLLBACK TRAN;
THROW;
END CATCH
"""
params = ""
update_sql = ""
insert_sql = ""
primary_key = ""
level = 0
for class_name in class_hierarchy:
sql_parts = build_sql(class_name, level)
params = params + sql_parts[0]
update_sql = update_sql + sql_parts[1]
insert_sql = insert_sql + sql_parts[2]
if level == 0:
primary_key = sql_parts[3]
level = level + 1
params = params[:-2]
sql = sql.format(get_table_name(class_name), params, primary_key,
update_sql, insert_sql)
return sql