patches/index_patch.py

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