| """ |
| This patch removes wrong indexs and add proper indexes in tables |
| """ |
| |
| import webnotes |
| sql = webnotes.conn.sql |
| from webnotes.utils import cint, cstr |
| |
| def create_proper_index(): |
| from webnotes.modules.export_module import export_to_files |
| |
| dt_index_fields={ |
| 'Purchase Receipt Detail': ['prevdoc_docname', 'item_code', 'warehouse', 'prevdoc_detail_docname'], |
| 'Period Closing Voucher': ['closing_account_head', 'fiscal_year'], |
| 'Lead': ['lead_name', 'status', 'transaction_date'], |
| 'Time Sheet Detail': ['app_name'], |
| 'Item Specification Detail': [], |
| 'Budget Detail': ['fiscal_year', 'account'], |
| 'Grade': [], |
| 'RV Tax Detail': ['parenttype', 'account_head'], |
| 'TDS Category Account': ['account_head'], |
| 'Role': [], |
| 'Leave Allocation': ['leave_type', 'employee', 'fiscal_year'], |
| 'Branch': [], |
| 'Department': [], |
| 'Contact Detail': [], |
| 'Territory': ['lft', 'rgt', 'parent_territory'], |
| 'Item Tax': ['tax_type'], |
| 'Bin': ['warehouse', 'item_code'], |
| 'PPW Detail': ['warehouse'], |
| 'Sales Partner': ['partner_name'], |
| 'Default Home Page': ['home_page', 'role'], |
| 'Custom Field': ['dt'], |
| 'DocFormat': ['format'], |
| 'DocType Mapper': ['from_doctype', 'to_doctype'], |
| 'Brand': [], |
| 'Order Lost Reason': [], |
| 'Journal Voucher': ['posting_date', 'voucher_type'], |
| 'TDS Return Acknowledgement': ['date_of_receipt', 'acknowledgement'], |
| 'BOM Report Detail': ['item_code'], |
| 'Quotation Detail': ['item_code'], |
| 'Update Delivery Date Detail': ['sales_order_no'], |
| 'Advance Adjustment Detail': ['journal_voucher'], |
| 'Authorization Rule': ['approving_user', 'system_user', 'system_role', 'approving_role'], |
| 'DocPerm': ['permlevel', 'role'], |
| 'Stock Entry Detail': ['item_code', 't_warehouse', 's_warehouse'], |
| 'Stock Entry': ['posting_date', 'delivery_note_no', 'purchase_receipt_no', 'production_order'], |
| 'Price List': [], |
| 'KRA Sheet': [], |
| 'Production Order': ['status', 'project_name', 'production_item'], |
| 'Account': ['lft', 'rgt', 'parent_account'], |
| 'Earn Deduction Detail': [], |
| 'Indent': ['status', 'transaction_date'], |
| 'Tag Detail': [], |
| 'SS Deduction Detail': ['d_type'], |
| 'Batch': ['item'], |
| 'Deduction Type': [], |
| 'Project': ['project_name', 'customer'], |
| 'UserRole': ['role'], |
| 'DocField': ['label', 'fieldtype', 'fieldname'], |
| 'Property Setter': ['doc_type', 'doc_name', 'property'], |
| 'Appraisal': ['status', 'employee'], |
| 'Letter Head': [], |
| 'Follow up': ['follow_up_by'], |
| 'Project Cost Breakup': [], |
| 'Table Mapper Detail': [], |
| 'Campaign': [], |
| 'Static Parameter Detail': [], |
| 'Leave Type': [], |
| 'Account Balance': ['period', 'start_date', 'end_date', 'account'], |
| 'Absent Days Detail': [], |
| 'Tag': [], |
| 'Raw Materials Supplied': ['raw_material'], |
| 'Project Activity Update': [], |
| 'PR Raw Material Detail': [], |
| 'Bank Reconciliation Detail': ['voucher_id'], |
| 'Sales Order': ['quotation_no', 'project_name', 'customer', 'posting_date'], |
| 'Chapter VI A Detail': [], |
| 'Experience In Company Detail': [], |
| 'Order Reconciliation Detail': ['sales_order_no'], |
| 'Attendance': ['employee', 'att_date'], |
| 'Previous Experience Detail': [], |
| 'Earning Detail': ['e_type'], |
| 'Sales Order Detail': ['item_code', 'prevdoc_docname', 'reserved_warehouse'], |
| 'KRA Template': [], |
| 'Budget Distribution': ['fiscal_year'], |
| 'Workstation': ['warehouse'], |
| 'Period': [], |
| 'Training Session Details': [], |
| 'Other Charges': [], |
| 'State': [], |
| 'Bulk Rename Tool': [], |
| 'Landed Cost Master Detail': [], |
| 'Employee': ['employee_name', 'designation', 'department'], |
| 'Terms And Conditions': [], |
| 'TC Detail': [], |
| 'UOM': [], |
| 'Supplier Type': [], |
| 'Project Milestone': [], |
| 'Landed Cost Master': [], |
| 'Budget Distribution Detail': [], |
| 'Form 16A Ack Detail': [], |
| 'Campaign Expense': [], |
| 'Time Sheet': ['employee_name', 'time_sheet_date'], |
| 'File Group': ['parent_group'], |
| 'Maintenance Visit Detail': ['item_code', 'service_person'], |
| 'Support Ticket Response': [], |
| 'PV Detail': ['item_code', 'purchase_order', 'po_detail', 'purchase_receipt', 'pr_detail', 'expense_head', 'cost_center'], |
| 'Timesheet Detail': ['project_name', 'task_id', 'customer_name'], |
| 'Holiday List Detail': [], |
| 'Workflow Rule Detail': [], |
| 'Module Def': ['module_seq', 'module_page'], |
| 'Term': [], |
| 'PF Detail': ['item_code'], |
| 'POS Setting': ['user', 'territory'], |
| 'QA Specification Detail': [], |
| 'Support Ticket': ['customer', 'allocated_to', 'status'], |
| 'Project Activity': ['project'], |
| 'Customer Group': ['lft', 'rgt', 'parent_customer_group'], |
| 'Return Detail': ['item_code'], |
| 'Series Detail': [], |
| 'Event Role': ['role'], |
| 'Contact': ['employee_id'], |
| 'BOM Material': ['item_code', 'bom_no'], |
| 'Invest 80 Declaration Detail': [], |
| 'PO Raw Material Detail': [], |
| 'Industry Type': [], |
| 'Declaration Detail': [], |
| 'Holiday List': ['fiscal_year'], |
| 'Sales Person': ['lft', 'rgt', 'parent_sales_person'], |
| 'RV Detail': ['item_code', 'sales_order', 'so_detail', 'delivery_note', 'dn_detail', 'cost_center', 'income_account'], |
| 'Module Def Item': [], |
| 'TDS Category': [], |
| 'DocTrigger': [], |
| 'Print Format': ['standard'], |
| 'Installed Item Details': ['prevdoc_docname', 'item_code'], |
| 'Form 16A Tax Detail': [], |
| 'Event': ['event_date', 'event_type'], |
| 'Currency': [], |
| 'Service Quotation Detail': ['item_code'], |
| 'Warehouse Type': ['warehouse_type'], |
| 'Sales BOM': ['item_group'], |
| 'IT Checklist': ['employee'], |
| 'Purchase Other Charges': [], |
| 'Company': [], |
| 'Call Log': [], |
| 'Professional Training Details': [], |
| 'Warehouse': ['warehouse_type'], |
| 'Competitor': [], |
| 'Mode of Payment': [], |
| 'Training Session': ['customer'], |
| 'Cost Center': ['lft', 'rgt', 'parent_cost_center'], |
| 'Timesheet': ['status', 'timesheet_date'], |
| 'Form 16A': ['party_no'], |
| 'Sales BOM Detail': ['item_code'], |
| 'Answer': ['question'], |
| 'Supplier': [], |
| 'Installation Note': ['delivery_note_no', 'customer', 'inst_date'], |
| 'Expense Voucher': ['approval_status', 'employee'], |
| 'Target Detail': ['from_date', 'to_date', 'fiscal_year'], |
| 'Page Role': ['role'], |
| 'Partner Target Detail': ['fiscal_year', 'item_group'], |
| 'Shipping Address': ['customer'], |
| 'Indent Detail': ['item_code', 'warehouse'], |
| 'TDS Payment Detail': [], |
| 'Market Segment': [], |
| 'Comment Widget Record': [], |
| 'Service Order Detail': ['item_code', 'prevdoc_docname'], |
| 'TDS Payment': ['from_date', 'to_date', 'tds_category'], |
| 'Lead Email CC Detail': [], |
| 'User Setting-Role User': [], |
| 'Salary Slip': ['month', 'year', 'employee'], |
| 'Maintenance Schedule Detail': ['item_code', 'scheduled_date'], |
| 'Employment Type': [], |
| 'Advance Allocation Detail': ['journal_voucher'], |
| 'Quotation': ['customer', 'transaction_date'], |
| 'Deduction Detail': ['d_type'], |
| 'Bill Of Materials': ['item', 'project_name'], |
| 'Earning Type': [], |
| 'Designation': [], |
| 'BOM Replace Utility Detail': ['parent_bom'], |
| 'Question': [], |
| 'Stock Ledger Entry': ['item_code', 'warehouse', 'posting_date', 'posting_time'], |
| 'Educational Qualifications Detail': [], |
| 'BOM Operation': [], |
| 'Item Group': ['lft', 'rgt', 'parent_item_group'], |
| 'Workflow Action Detail': [], |
| 'User Setting-Profile': [], |
| 'Customer Issue': ['item_code', 'customer', 'complaint_date'], |
| 'Feed': [], |
| 'Purchase Tax Detail': ['account_head'], |
| 'GL Mapper Detail': [], |
| 'TDS Detail': [], |
| 'PRO Detail': ['item_code', 'source_warehouse'], |
| 'DocType Label': [], |
| 'Receivable Voucher': ['posting_date', 'debit_to', 'project_name'], |
| 'GL Entry': ['posting_date', 'account', 'voucher_no'], |
| 'Serial No': ['status', 'warehouse'], |
| 'Delivery Note': ['posting_date', 'project_name', 'customer'], |
| 'UOM Conversion Detail': ['uom'], |
| 'Search Criteria': ['criteria_name'], |
| 'Salary Structure': [], |
| 'Educational Qualifications': ['qualification'], |
| 'TDS Rate Chart': ['applicable_from', 'applicable_to'], |
| 'GL Mapper': [], |
| 'Announcement': [], |
| 'Call Log Details': [], |
| 'Enquiry': ['lead', 'customer', 'transaction_date'], |
| 'Flat BOM Detail': ['item_code'], |
| 'Landed Cost Detail': ['account_head'], |
| 'Field Mapper Detail': ['from_field', 'to_field'], |
| 'File Data': [], |
| 'Question Tag': [], |
| 'QA Inspection Report': ['item_code', 'purchase_receipt_no', 'report_date'], |
| 'Appraisal Detail': [], |
| 'POS Settings': ['territory'], |
| 'Delivery Note Detail': ['item_code', 'prevdoc_docname', 'warehouse', 'prevdoc_detail_docname'], |
| 'Profile': [], |
| 'Other Income Detail': [], |
| 'Product': ['item_code', 'stock_warehouse'], |
| 'PO Detail': ['prevdoc_docname', 'item_code', 'prevdoc_detail_docname', 'warehouse'], |
| 'Module Def Role': ['role'], |
| 'Sales Team': ['sales_person'], |
| 'Enquiry Detail': ['item_code'], |
| 'DocType': [], |
| 'Compaint Note': ['nature_of_complaint', 'compliance_date'], |
| 'Maintenance Schedule': ['customer', 'sales_order_no'], |
| 'Event User': ['person'], |
| 'Stock Reconciliation': ['reconciliation_date'], |
| 'Purchase Receipt': ['posting_date', 'supplier', 'project_name'], |
| 'Complaint Detail': ['item_name'], |
| 'Address': ['customer', 'supplier'], |
| 'Ticket': ['request_date', 'allocated_to', 'category', 'customer', 'project'], |
| 'Territory Target Detail': ['month', 'fiscal_year'], |
| 'LC PR Detail': ['purchase_receipt_no'], |
| 'Customer': ['customer_name', 'customer_group'], |
| 'PP SO Detail': [], |
| 'PP Detail': ['document_date', 'item_code', 'parent_item'], |
| 'User Setting-Role Permission': [], |
| 'Custom Script': ['dt'], |
| 'Country': [], |
| 'DefaultValue': [], |
| 'Ledger Detail': [], |
| 'SS Earning Detail': ['e_type'], |
| 'SMS Log': [], |
| 'Expense Type': [], |
| 'Item': ['item_group'], |
| 'Fiscal Year': [], |
| 'ToDo Item': ['role'], |
| 'Payable Voucher': ['posting_date', 'credit_to', 'project_name', 'supplier'], |
| 'Journal Voucher Detail': ['account', 'against_voucher', 'against_invoice', 'against_jv'], |
| 'Online Contact': [], |
| 'Page': ['module'], |
| 'Leave Application': ['employee', 'leave_type', 'from_date', 'to_date'], |
| 'Expense Voucher Detail': ['expense_type'], |
| 'Maintenance Visit': ['customer', 'sales_order_no', 'customer_issue_no'], |
| 'Ref Rate Detail': ['price_list_name', 'ref_currency'], |
| 'Receiver Detail': [], |
| 'Naming Series Options': ['doc_type'], |
| 'Activity Type': [], |
| 'PRO PP Detail': [], |
| 'Delivery Note Packing Detail': ['item_code', 'parent_item', 'warehouse'], |
| 'Workflow Rule': ['select_form'], |
| 'File': ['file_group'], |
| 'Item Maintenance Detail': ['item_code', 'start_date', 'end_date', 'prevdoc_docname'], |
| 'Purchase Order': ['supplier', 'project_name', 'posting_date'], |
| 'Print Heading': [], |
| 'TDS Rate Detail': ['category'] |
| } |
| #sql("commit") # only required if run from login |
| exist_dt = [cstr(d[0]) for d in sql("select name from `tabDocType`")] |
| |
| for dt in [d for d in dt_index_fields.keys() if d in exist_dt]: |
| try: |
| current_index = sql("show indexes from `tab%s`" % dt) |
| |
| proper_index = dt_index_fields[dt] |
| |
| for d in current_index: |
| if d[4] not in ['name', 'parent', 'parenttype']: |
| if d[4] not in proper_index: |
| sql("ALTER TABLE `tab%s` DROP INDEX %s" % (dt, d[4])) |
| sql("start transaction") |
| sql("UPDATE `tabDocField` SET search_index = 0 WHERE fieldname = '%s' AND parent = '%s'" % (d[4], dt)) |
| sql("commit") |
| else: |
| proper_index.remove(d[4]) |
| |
| for d in proper_index: |
| sql("ALTER TABLE `tab%s` ADD INDEX ( `%s` ) " % (dt, d)) |
| sql("start transaction") |
| sql("UPDATE `tabDocField` SET search_index = 1 WHERE fieldname = '%s' AND parent = '%s'" % (d, dt)) |
| sql("commit") |
| except: |
| continue |