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