patch for indexing
diff --git a/patches/index_patch.py b/patches/index_patch.py
new file mode 100644
index 0000000..8f6c4f2
--- /dev/null
+++ b/patches/index_patch.py
@@ -0,0 +1,292 @@
+"""
+ This patch removes wrong indexs and add proper indexes in tables
+"""
+
+
+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']
+ }
+
+ for dt in dt_index_fields.keys():
+ 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")
+
+ sql("start transaction")
+ dt_module = sql("select module from `tabDocType` where name = '%s'" % dt)[0][0]
+ export_to_files(record_list = [['DocType', dt]], record_module = dt_module)
+ sql("commit")