import os import re import json SCHEMA_DIR = "./subdolder" # Adjust to point to directory containing your .js models OUTPUT_DIR = "./metadata" os.makedirs(OUTPUT_DIR, exist_ok=True) # Ground Truth TableConfig mapping Sequelize Tokens -> True Postgres physical table names TABLE_CONFIG_DICTIONARY = { "Language": "agri_languages", "Users": "agri_users", "UserAddress": "agri_user_address", "File": "agri_files", "Category": "agri_category", "CategoryTaxonomy": "agri_category_taxonomy", "Land": "agri_land", "LandUserRelationship": "agri_land_user_rels", "State": "agri_states", "District": "agri_districts", "Block": "agri_block", "Mouza": "agri_mouza", "Subdivision": "agri_subdivisions", "CropRelated": "agri_crop_related", "CropTypeName": "agri_crop_type_name", "CropNameVariety": "agri_crop_name_variety", "CropNameGrowthStage": "agri_crop_name_growth_stage", "CropUserRelationship": "agri_crop_user_rels", "CropHistory": "agri_crop_history", "FarmerAgentRelated": "agri_farmer_agent_related", "FarmerAgentRelatedRels": "agri_farmer_agent_related_rels", "Roles": "agri_roles", "Permissions": "agri_permissions", "RolePermission": "agri_role_permissions", "Modules": "agri_modules", "Unit": "agri_units", "HarvestCycle": "agri_harvest_cycle", "UserActivity": "agri_user_activities", "ActivityRels": "agri_activity_rels", "ActivityPipes": "agri_activity_pipes", "SeasonDates": "agri_season_dates", "CoUserLandRels": "agri_co_user_land_rels", "LandUserLatLongs": "agri_land_user_latlongs", "UserCategoryRels": "agri_user_category_rels", "UserCropRels": "agri_user_crop_rels", "UsersLog": "agri_users_log", "Location": "locations", "Companies": "agri_company", "CompanyOptionRels": "agri_companyoptionrels", "CropGradePrice": "agri_cropgradeprice" } # ORM Normalization Map to eliminate missing underscores TABLE_NAME_NORMALIZER = { "agri_croprelated": "agri_crop_related", "agri_useractivity": "agri_user_activities", "agri_landuserrelationship": "agri_land_user_rels", "agri_activityrels": "agri_activity_rels", "agri_activitypipes": "agri_activity_pipes", "agri_seasondates": "agri_season_dates", "agri_cropuserrelationship": "agri_crop_user_rels", "agri_landuserlatlongs": "agri_land_user_latlong", "agri_useraddress": "agri_user_address", "agri_farmeragentrelated": "agri_farmer_agent_related", "agri_couserlandrels": "agri_co_user_land_rels" } def normalize_table(tbl_name): clean = tbl_name.strip() return TABLE_NAME_NORMALIZER.get(clean, clean) def parse_sequelize_model(content, file_name): """ Parses Sequelize JavaScript models to extract: 1. Table Token / Physical Name 2. Model Columns 3. Primary Key 4. Model Associations (.associate) """ # 1. Identify Table Name Token table_match = re.search(r'sequelize\.define\(\s*(?:TableNames\.([a-zA-Z0-9_]+)|["\']([a-zA-Z0-9_]+)["\'])', content) table_token = None if table_match: table_token = table_match.group(1) or table_match.group(2) physical_table = None if table_token in TABLE_CONFIG_DICTIONARY: physical_table = TABLE_CONFIG_DICTIONARY[table_token] elif table_token: physical_table = normalize_table(table_token) else: # Fallback to filename base_name = file_name.replace('.js', '') physical_table = normalize_table(base_name) # 2. Extract Columns # Matches field definitions: colName: { type: ... } or colName: DataTypes.X cols = set(re.findall(r'([a-zA-Z0-9_]+)\s*:\s*(?:\{\s*type\s*:|DataTypes\.)', content)) excluded = {'id', 'type', 'allowNull', 'defaultValue', 'primaryKey', 'comment', 'autoIncrement', 'paranoid', 'freezeTableName', 'timestamps', 'createdAt', 'updatedAt', 'hooks'} valid_cols = sorted([c for c in cols if c not in excluded]) # 3. Detect Primary Key pk_match = re.search(r'([a-zA-Z0-9_]+)\s*:\s*\{[^}]*primaryKey\s*:\s*true', content) primary_key = pk_match.group(1) if pk_match else ("user_id" if physical_table == "agri_users" else "rel_id" if physical_table == "agri_land_user_rels" else "id") if primary_key not in valid_cols: valid_cols.insert(0, primary_key) # 4. Extract Associations from .associate associations = [] assoc_match = re.search(r'\.associate\s*=\s*function\s*\(([^)]+)\)\s*\{([^}]+)\}', content, re.DOTALL) if assoc_match: assoc_body = assoc_match.group(2) rel_pattern = re.finditer( r'\.(belongsTo|hasMany)\s*\(\s*models(?:\[\s*TableNames\.([a-zA-Z0-9_]+)\s*\]|\.([a-zA-Z0-9_]+))\s*,\s*(\{([^}]+)\})', assoc_body ) for m in rel_pattern: assoc_type = m.group(1) target_token = m.group(2) or m.group(3) options = m.group(5) target_table = TABLE_CONFIG_DICTIONARY.get(target_token, normalize_table(target_token)) fk_match = re.search(r'foreignKey\s*:\s*["\']([a-zA-Z0-9_]+)["\']', options) tk_match = re.search(r'(?:targetKey|sourceKey)\s*:\s*["\']([a-zA-Z0-9_]+)["\']', options) fk = fk_match.group(1) if fk_match else None tk = tk_match.group(1) if tk_match else "id" if fk: if assoc_type == "belongsTo": src = physical_table tgt = target_table cond = f"ON {src}.{fk} = {tgt}.{tk}" else: src = target_table tgt = physical_table cond = f"ON {src}.{fk} = {tgt}.{tk}" associations.append({ "join_key": f"{src} -> {tgt}", "condition": cond, "primary_table": tgt if assoc_type == "belongsTo" else src, "foreign_table": src if assoc_type == "belongsTo" else tgt, "primary_key": tk, "foreign_key": fk }) return physical_table, valid_cols, primary_key, associations def run_metadata_pipeline(): raw_metadata = {} query_paths = {} relationships = [] # 1. SCAN DIRECTORY AND PARSE JS MODELS for root, _, files in os.walk(SCHEMA_DIR): for file in files: if file.endswith('.js') and not file.startswith('index'): path = os.path.join(root, file) with open(path, 'r', encoding='utf-8', errors='ignore') as f: content = f.read() table_name, cols, pk, assoc = parse_sequelize_model(content, file) if table_name: table_name = normalize_table(table_name) raw_metadata[table_name] = { "source_file": file, "columns": cols, "primary_key": pk, "associations": assoc } for a in assoc: query_paths[a["join_key"]] = { "join_strategy": "INNER JOIN", "condition": a["condition"] } relationships.append({ "primary_table": normalize_table(a["primary_table"]), "foreign_table": normalize_table(a["foreign_table"]), "cardinality": "belongsTo", "logical_keys": { "primary_key": a["primary_key"], "foreign_key": a["foreign_key"] }, "through_table": None, "alias_defined": None }) # Fallback default join paths for core joins to guarantee dynamic traversal DEFAULT_PATHS = { "agri_user_address -> agri_users": "ON agri_user_address.user_id = agri_users.user_id", "agri_land_user_rels -> agri_users": "ON agri_land_user_rels.user_id = agri_users.user_id", "agri_land_user_rels -> agri_activity_pipes": "ON agri_land_user_rels.rel_id = agri_activity_pipes.plot_fid", "agri_user_activities -> agri_land_user_rels": "ON agri_user_activities.plot_id = agri_land_user_rels.rel_id", "agri_user_activities -> agri_category": "ON agri_user_activities.act_master_id = agri_category.fid", "agri_user_activities -> agri_season_dates": "ON agri_user_activities.season_date_fid = agri_season_dates.sd_id", "agri_season_dates -> agri_crop_related": "ON agri_season_dates.season_fid = agri_crop_related.fid", "agri_districts -> agri_states": "ON agri_districts.state_id = agri_states.id", "agri_subdivisions -> agri_districts": "ON agri_subdivisions.district_id = agri_districts.id", "agri_block -> agri_subdivisions": "ON agri_block.subdivision_id = agri_subdivisions.id" } for p, cond in DEFAULT_PATHS.items(): if p not in query_paths: query_paths[p] = {"join_strategy": "INNER JOIN", "condition": cond} # 2. BUILD tables.json tables_json = [{"table_name": t, "source_file": data["source_file"]} for t, data in raw_metadata.items()] # 3. BUILD columns.json columns_json = [] for t, data in raw_metadata.items(): for col in data["columns"]: columns_json.append({ "table_name": t, "column_name": col, "is_logical_key": col.endswith('_id') or col.endswith('_fid') or col in ['id', 'user_id', 'rel_id', 'fid', 'cu_rel_id'] }) # 4. BUILD catalog.json DYNAMICALLY CORE_SCOPES = { "farmer": "agri_users", "plot": "agri_land_user_rels", "pipe": "agri_activity_pipes", "activity": "agri_user_activities" } catalog_json = {"entities": {}} for scope_name, primary_table in CORE_SCOPES.items(): if primary_table in raw_metadata: outputs = {} # Direct Primary Columns for c in raw_metadata[primary_table]["columns"]: outputs[f"{scope_name}_{c}"] = {"table": primary_table, "column": c} # Associated Foreign Columns for assoc in raw_metadata[primary_table].get("associations", []): assoc_tbl = normalize_table(assoc["foreign_table"] if assoc["primary_table"] == primary_table else assoc["primary_table"]) if assoc_tbl in raw_metadata: for ac in raw_metadata[assoc_tbl]["columns"]: outputs[f"{assoc_tbl}_{ac}"] = {"table": assoc_tbl, "column": ac} catalog_json["entities"][scope_name] = { "table": primary_table, "count_column": raw_metadata[primary_table]["primary_key"], "outputs": outputs } # SAVE ALL JSON FILES with open(os.path.join(OUTPUT_DIR, "tables.json"), 'w') as f: json.dump(tables_json, f, indent=2) with open(os.path.join(OUTPUT_DIR, "columns.json"), 'w') as f: json.dump(columns_json, f, indent=2) with open(os.path.join(OUTPUT_DIR, "query_paths.json"), 'w') as f: json.dump(query_paths, f, indent=2) with open(os.path.join(OUTPUT_DIR, "relationships.json"), 'w') as f: json.dump(relationships, f, indent=2) with open(os.path.join(OUTPUT_DIR, "catalog.json"), 'w') as f: json.dump(catalog_json, f, indent=2) print(f"✅ Fixed Metadata Pipeline Generated Successfully! Scanned {len(tables_json)} active tables.") # --- ADD THIS PATCH FUNCTION HERE --- TABLE_NAME_NORMALIZER = { "agri_landuserrelationship": "agri_land_user_rels", "agri_croprelated": "agri_crop_related", "agri_useractivity": "agri_user_activities", "agri_activityrels": "agri_activity_rels", "agri_activitypipes": "agri_activity_pipes", "agri_seasondates": "agri_season_dates", "agri_cropuserrelationship": "agri_crop_user_rels", "agri_landuserlatlongs": "agri_land_user_latlongs", "agri_useraddress": "agri_user_address", "agri_farmeragentrelated": "agri_farmer_agent_related", "agri_couserlandrels": "agri_co_user_land_rels", "agri_categorytaxonomy": "agri_category_taxonomy" } def fix_relationships_and_aliases(): # 1. Populate relationships.json directly from query_paths.json query_paths_file = os.path.join(OUTPUT_DIR, "query_paths.json") if os.path.exists(query_paths_file): with open(query_paths_file, "r") as f: query_paths = json.load(f) relationships = [] for path_key, path_info in query_paths.items(): src, tgt = [t.strip() for t in path_key.split("->")] cond = path_info["condition"] match = re.search(r"ON\s+(\w+)\.(\w+)\s*=\s*(\w+)\.(\w+)", cond, re.IGNORECASE) if match: t1, k1, t2, k2 = match.groups() primary_tbl = t2 if t2 == tgt else t1 primary_key = k2 if t2 == tgt else k1 foreign_tbl = t1 if t1 == src else t2 foreign_key = k1 if t1 == src else k2 relationships.append({ "primary_table": TABLE_NAME_NORMALIZER.get(primary_tbl, primary_tbl), "foreign_table": TABLE_NAME_NORMALIZER.get(foreign_tbl, foreign_tbl), "cardinality": "belongsTo", "logical_keys": { "primary_key": primary_key, "foreign_key": foreign_key }, "through_table": None, "alias_defined": None }) with open(os.path.join(OUTPUT_DIR, "relationships.json"), "w") as f: json.dump(relationships, f, indent=2) # 2. Normalize alias table targets if aliases.json exists aliases_file = os.path.join(OUTPUT_DIR, "aliases.json") if os.path.exists(aliases_file): with open(aliases_file, "r") as f: aliases = json.load(f) for alias_key, alias_val in aliases.items(): raw_tbl = alias_val.get("resolves_to_table", "") alias_val["resolves_to_table"] = TABLE_NAME_NORMALIZER.get(raw_tbl, raw_tbl) with open(aliases_file, "w") as f: json.dump(aliases, f, indent=2) print("🎉 relationships.json successfully populated and aliases normalized!") # --- END OF PATCH --- if __name__ == "__main__": run_metadata_pipeline() # or run_unified_metadata_pipeline(SCHEMA_DIR) fix_relationships_and_aliases() # <-- Call the patch right after running the main pipeline # import os # import re # import json # # Adjust paths to match your project directory layout # SCHEMA_DIR = "./subdolder" # OUTPUT_DIR = "./metadata" # os.makedirs(OUTPUT_DIR, exist_ok=True) # def run_unified_metadata_pipeline(directory): # if not os.path.exists(directory): # print(f"❌ Directory '{directory}' not found.") # return # raw_tables_columns = {} # raw_tables_metadata = {} # query_paths_json = {} # # GROUND TRUTH TABLE CONFIG MAP (Directly extracted from TableConfig.js) # TABLE_CONFIG_DICTIONARY = { # "Language": "agri_languages", # "LoggingMapping": "agri_login_mapping", # "Users": "agri_users", # "UserAddress": "agri_user_address", # "File": "agri_files", # "Category": "agri_category", # "PostCategoryRelationship": "agri_post_category_rels", # "CategoryTaxonomy": "agri_category_taxonomy", # "Land": "agri_land", # "LandUserRelationship": "agri_land_user_rels", # "Post": "agri_post", # "Comment": "agri_comments", # "Country": "agri_country", # "State": "agri_states", # "District": "agri_districts", # "Block": "agri_block", # "Mouza": "agri_mouza", # "Subdivision": "agri_subdivisions", # "CropRelated": "agri_crop_related", # "CropTypeName": "agri_crop_type_name", # "CropNameVariety": "agri_crop_name_variety", # "CropNameGrowthStage": "agri_crop_name_growth_stage", # "CropUserRelationship": "agri_crop_user_rels", # "CropHistory": "agri_crop_history", # "CropHistoryProblemInfo": "agri_crop_history_problem_info", # "CropHistoryFinancialInfo": "agri_crop_history_financial_info", # "ProblemRelated": "agri_problem_related", # "ProblemMap": "agri_problem_map", # "Question": "agri_questions", # "Vas": "agri_vas", # "VasUserRelationShip": "agri_vas_user_rels", # "Admin": "agri_admin", # "Host": "agri_host", # "HostDbRelations": "agri_host_db_rels", # "Pop": "agri_pop", # "Stl": "agri_soil_test_labs", # "StlRelation": "agri_soil_test_rels", # "Log": "agri_log", # "Solution": "agri_solutions", # "QuestionSolutionRels": "agri_question_solution_rels", # "StlResult": "agri_soil_test_results", # "NpkCalculator": "agri_npk_calculator", # "PesticideCalculator": "agri_pesticide_calculator", # "AgriScientificFarming": "agri_scientific_farming", # "PopStaticMenu": "sf_static_menu", # "CropTypeStaticMenuRels": "crop_type_static_menu_rels", # "StaticSubmenu": "static_submenu", # "PackageOfPractice": "package_of_practice", # "FarmerAgentRelated": "agri_farmer_agent_related", # "Options": "agri_options", # "FarmerAgentRelatedRels": "agri_farmer_agent_related_rels", # "Roles": "agri_roles", # "Permissions": "agri_permissions", # "RolePermission": "agri_role_permissions", # "Modules": "agri_modules", # "PredefinedSols": "agri_predefined_sols", # "Products": "bs_products", # "ProductCategory": "bs_product_category", # "ProductImages": "bs_product_images", # "ShoppingCart": "bs_shopping_cart", # "Orders": "bs_orders", # "OrderProducts": "bs_order_products", # "OrderDetail": "bs_order_details", # "UserDeliveryAddress": "bs_user_delivery_addressess", # "BSUsers": "bs_users", # "BSUserAddress": "bs_user_address", # "GrowthStageSolutions": "agri_growth_stage_solutions", # "CropLifeCycle": "agri_crop_life_cycle", # "GrowthStageSolsMap": "agri_solution_growth_rel", # "CropVarietySolsMap": "agri_solution_variety_rel", # "SeveritySolsMap": "agri_solution_severity_rel", # "MarketProductCategory": "mk_product_categories", # "MarketProducts": "mk_products", # "MarketProductCategoryRels": "mk_product_category_rels", # "MarketCropPriceType": "mk_crop_price_type", # "MarketCropPrice": "mk_crop_prices", # "Unit": "agri_units", # "Currencies": "agri_currencies", # "CurrencyHistories": "agri_currency_histories", # "NpkCalculatorGrowthStageRels": "agri_npk_calc_growth_stage_rels", # "PredefinedPlots": "agri_predefined_plots", # "ShcOptions": "agri_shc_options", # "ShcRangeRels": "agri_shc_range_rels", # "UsersLog": "agri_users_log", # "ShcNpkDataLogic": "agri_shc_npk_data_logic", # "Companies": "agri_companies", # "HarvestCycle": "agri_harvest_cycle", # "EmployeeTypes": "agri_employee_types", # "ContactFormData": "agri_contactForm_data", # "AccountEntities": "agri_account_entities", # "AccountHeads": "agri_account_heads", # "AccountTransactions": "agri_account_transactions", # "Loan": "agri_loans", # "LoanEntity": "agri_loan_entity", # "Insurance": "agri_insurance", # "InputOutputEntity": "agri_input_output_entity", # "InputOutput": "agri_input_output", # "InputOutputEntityTypeName": "agri_input_output_entity_type_name", # "Pond": "agri_pond", # "FishType": "agri_fish_type", # "Fish": "agri_fish", # "FishTypeMap": "agri_fish_type_map", # "PondFishMap": "agri_pond_fish_map", # "IO": "agri_io", # "PondInsurance": "agri_pond_insurance", # "PondInsuranceRels": "agri_pond_insurance_rels", # "PondFishProblem": "agri_pond_fish_problem", # "PondFishProblemRels": "agri_pond_fish_problem_rels", # "WantToSell": "agri_want_to_sell", # "WantToSellFishMap": "agri_want_to_sell_fish_map", # "PondFishSolution": "agri_pond_fish_solution", # "PondFishSolutionRels": "agri_pond_fish_solution_rels", # "MicroFile": "agri_micro_files", # "InsurancePlotMap": "agri_insurance_plot_map", # "UserPensionDetails": "agri_user_pension_details", # "UserLicDetails": "agri_user_lic_details", # "UserTemp": "upload_farmer", # "UploadFarmerDetails": "upload_farmer_details", # "InputOutputReq": "agri_input_output_reqs", # "LoanPlotMap": "agri_loan_plot_map", # "ProductPrice": "agri_product_prices", # "SupplytoFarmers": "agri_supply_to_farmers", # "AccountLedgers": "agri_account_ledgers", # "ProcureFarmers": "agri_procure_farmers", # "UserCropRels": "agri_user_crop_rels", # "UserCategoryRels": "agri_user_category_rels", # "ActivityRels": "agri_activity_rels", # "UserActivity": "agri_user_activities", # "Inventory": "agri_inventory", # "Processing": "agri_processing", # "Sales": "agri_sales", # "Batch": "agri_batch", # "BatchMaterial": "agri_batch_material", # "BatchActivityRels": "agri_batch_activity_rels", # "Aggregations": "agri_aggregations", # "AdminCompRels": "agri_admin_comp_rels", # "SkuProducts": "agri_sku_products", # "SkuProductRels": "agri_sku_product_rels", # "PostCropRels": "agri_post_crop_rels", # "PostLocationRels": "agri_post_location_rels", # "PostUserRels": "agri_post_user_rels", # "CustomerPostPurchases": "customer_post_purchases", # "CompanyOptionRels": "agri_comp_opt_rels", # "InventoryHistory": "agri_inventory_histories", # "ProcureQC": "agri_procure_qcs", # "SettingRelated": "agri_settings_relateds", # "InventoryFree": "agri_inventory_frees", # "InventoryStores": "agri_inventory_stores", # "InvFreeHistory": "agri_inv_free_histories", # "LandUserLatLongs": "agri_land_user_latlongs", # "CustomerOrders": "customer_orders", # "UserInputProducts": "user_input_products", # "UserInputProdVariance": "user_input_product_vars", # "ProcureVendors": "agri_procure_vendors", # "Transport": "transports", # "Location": "locations", # "ProcureLocTrans": "agri_procure_loc_trans", # "SalesReport": "agri_sales_reports", # "SalesReportProd": "agri_sales_report_prods", # "Bom": "agri_bom", # "CropGradePrice": "agri_crop_grade_price", # "CropGradePriceHistory": "agri_crgrade_price_history", # "Checker": "agri_checker", # "AggrLatLong": "agri_aggr_lat_long", # "AggrOutPutMarketPlace": "aggr_output_mp", # "AggrOutPutUnits": "aggr_output_units", # "AggrInputOutputReq": "aggr_input_output_reqs", # "AggrLandUserRels": "aggr_land_user_rels", # "AggrUnitCompRels": "aggr_unit_comp_rels", # "AggrMicroUnits": "aggr_micro_units", # "AggrDashboard": "aggr_dashboard", # "ContractorRels": "agri_contractor_rels", # "CarbonFootPrint": "agri_carbon_footprint", # "DigitalAdaption": "agri_digital_adaption", # "ExtInputOutputReq": "ext_input_output_reqs", # "ExtCompanies": "ext_companies", # "TcModules": "agri_tc_modules", # "TcWeightages": "agri_tc_weightages", # "TcPoints": "agri_tc_points", # "TcTransaction": "agri_tc_transaction", # "ActivityPipes": "agri_activity_pipes", # "CarbonIntervension": "agri_carbon_interv", # "SeasonDates": "agri_season_dates", # "CoUserLandRels": "agri_co_user_land_rels", # "QrCodes": "agri_qr_codes", # "DirectSales": "agri_direct_sales", # "CropFertiPestiRels": "agri_crop_fertipesti_rels" # } # # Bypasses framework layout amnesia by applying explicit physical joins # EXACT_JOIN_CONDITIONS = { # "agri_user_address -> agri_users": "ON agri_user_address.user_id = agri_users.user_id", # "agri_user_address -> agri_states": "ON agri_user_address.state = agri_states.id", # "agri_user_address -> agri_districts": "ON agri_user_address.district = agri_districts.id", # "agri_user_address -> agri_subdivisions": "ON agri_user_address.subdivision = agri_subdivisions.id", # "agri_user_address -> agri_block": "ON agri_user_address.block = agri_block.id", # "agri_user_address -> agri_mouza": "ON agri_user_address.mouza = agri_mouza.id", # "agri_land_user_rels -> agri_users": "ON agri_land_user_rels.user_id = agri_users.user_id", # "agri_land_user_rels -> agri_activity_pipes": "ON agri_land_user_rels.rel_id = agri_activity_pipes.plot_fid", # "agri_land_user_rels -> agri_crop_user_rels": "ON agri_land_user_rels.rel_id = agri_crop_user_rels.land_rel_id", # "agri_land_user_rels -> agri_user_crop_rels": "ON agri_land_user_rels.rel_id = agri_user_crop_rels.land_rel_id", # "agri_crop_user_rels -> agri_users": "ON agri_crop_user_rels.user_id = agri_users.user_id", # "agri_crop_user_rels -> agri_activity_pipes": "ON agri_crop_user_rels.cu_rel_id = agri_activity_pipes.cu_rel_fid", # "agri_user_crop_rels -> agri_users": "ON agri_user_crop_rels.user_id = agri_users.user_id", # "agri_user_activities -> agri_users": "ON agri_user_activities.user_id = agri_users.user_id", # "agri_user_activities -> agri_land_user_rels": "ON agri_user_activities.plot_id = agri_land_user_rels.rel_id", # "agri_districts -> agri_states": "ON agri_districts.state_id = agri_states.id", # "agri_subdivisions -> agri_districts": "ON agri_subdivisions.district_id = agri_districts.id", # "agri_block -> agri_subdivisions": "ON agri_block.subdivision_id = agri_subdivisions.id" # } # tableName_re = re.compile(r"tableName\s*:\s*TableNames\.([a-zA-Z0-9_]+)") # inline_prop_re = re.compile(r"\b([a-zA-Z0-9_]+)\s*:\s*\{.*?type") # literal_field_re = re.compile(r"[\"']([a-zA-Z0-9_]+)[\"']\s*,\s*[\"'](ASC|DESC)[\"']") # EXCLUDED_WORDS = {'id', 'true', 'false', 'null', 'undefined', 'raw', 'where', 'type'} # for root, _, files in os.walk(directory): # for file in files: # if file.endswith('.js'): # file_path = os.path.join(root, file) # try: # with open(file_path, 'r', encoding='utf-8', errors='ignore') as f: # content = f.read() # # Extract Table Token Variable # table_match = tableName_re.search(content) # if not table_match: # continue # token_key = table_match.group(1) # if token_key not in TABLE_CONFIG_DICTIONARY: # continue # table_name = TABLE_CONFIG_DICTIONARY[token_key] # # Column analysis layer # raw_discovered = [] # raw_discovered.extend(inline_prop_re.findall(content)) # raw_discovered.extend(literal_field_re.findall(content)) # valid_columns = set() # for col in raw_discovered: # if isinstance(col, tuple): # col = col[0] # col_clean = col.strip() # if col_clean and col_clean not in EXCLUDED_WORDS and not col_clean.replace('_', '').isdigit(): # valid_columns.add(col_clean) # columns_list = sorted(list(valid_columns)) # # Apply custom identity configurations # if table_name == "agri_users": # if "user_id" not in columns_list: columns_list.insert(0, "user_id") # if "userName" not in columns_list: columns_list.insert(1, "userName") # elif "id" not in columns_list: # columns_list.insert(0, "id") # raw_tables_columns[table_name] = columns_list # raw_tables_metadata[table_name] = {"file_source": file, "columns": columns_list} # except Exception as e: # print(f"⚠️ Error reading file {file}: {str(e)}") # # Construct and compile schema layers # tables_json = [{"table_name": t, "source_file": data["file_source"]} for t, data in raw_tables_metadata.items()] # columns_json = [] # for table, columns in raw_tables_columns.items(): # for col in columns: # columns_json.append({ # "table_name": table, # "column_name": col, # "is_logical_key": col.endswith('_id') or col.endswith('_fid') or col in ['id', 'user_id', 'rel_id', 'fid', 'cu_rel_id'] # }) # for path_key, condition in EXACT_JOIN_CONDITIONS.items(): # query_paths_json[path_key] = { # "join_strategy": "INNER JOIN", # "condition": condition # } # with open(os.path.join(OUTPUT_DIR, "tables.json"), 'w') as f: # json.dump(tables_json, f, indent=2) # with open(os.path.join(OUTPUT_DIR, "columns.json"), 'w') as f: # json.dump(columns_json, f, indent=2) # with open(os.path.join(OUTPUT_DIR, "query_paths.json"), 'w') as f: # json.dump(query_paths_json, f, indent=2) # print(f"🎉 Complete Metadata Dictionary Layer Generated Successfully for {len(tables_json)} active structural tables!") # if __name__ == "__main__": # run_unified_metadata_pipeline(SCHEMA_DIR)