| Nabin Hait | fe11736 | 2011-07-10 12:01:19 +0530 | [diff] [blame] | 1 | """ |
| 2 | This patch removes wrong indexs and add proper indexes in tables |
| 3 | """ |
| 4 | |
| 5 | |
| 6 | def create_proper_index(): |
| 7 | from webnotes.modules.export_module import export_to_files |
| 8 | |
| 9 | dt_index_fields={ |
| 10 | 'Purchase Receipt Detail': ['prevdoc_docname', 'item_code', 'warehouse', 'prevdoc_detail_docname'], |
| 11 | 'Period Closing Voucher': ['closing_account_head', 'fiscal_year'], |
| 12 | 'Lead': ['lead_name', 'status', 'transaction_date'], |
| 13 | 'Time Sheet Detail': ['app_name'], |
| 14 | 'Item Specification Detail': [], |
| 15 | 'Budget Detail': ['fiscal_year', 'account'], |
| 16 | 'Grade': [], |
| 17 | 'RV Tax Detail': ['parenttype', 'account_head'], |
| 18 | 'TDS Category Account': ['account_head'], |
| 19 | 'Role': [], |
| 20 | 'Leave Allocation': ['leave_type', 'employee', 'fiscal_year'], |
| 21 | 'Branch': [], |
| 22 | 'Department': [], |
| 23 | 'Contact Detail': [], |
| 24 | 'Territory': ['lft', 'rgt', 'parent_territory'], |
| 25 | 'Item Tax': ['tax_type'], |
| 26 | 'Bin': ['warehouse', 'item_code'], |
| 27 | 'PPW Detail': ['warehouse'], |
| 28 | 'Sales Partner': ['partner_name'], |
| 29 | 'Default Home Page': ['home_page', 'role'], |
| 30 | 'Custom Field': ['dt'], |
| 31 | 'DocFormat': ['format'], |
| 32 | 'DocType Mapper': ['from_doctype', 'to_doctype'], |
| 33 | 'Brand': [], |
| 34 | 'Order Lost Reason': [], |
| 35 | 'Journal Voucher': ['posting_date', 'voucher_type'], |
| 36 | 'TDS Return Acknowledgement': ['date_of_receipt', 'acknowledgement'], |
| 37 | 'BOM Report Detail': ['item_code'], |
| 38 | 'Quotation Detail': ['item_code'], |
| 39 | 'Update Delivery Date Detail': ['sales_order_no'], |
| 40 | 'Advance Adjustment Detail': ['journal_voucher'], |
| 41 | 'Authorization Rule': ['approving_user', 'system_user', 'system_role', 'approving_role'], |
| 42 | 'DocPerm': ['permlevel', 'role'], |
| 43 | 'Stock Entry Detail': ['item_code', 't_warehouse', 's_warehouse'], |
| 44 | 'Stock Entry': ['posting_date', 'delivery_note_no', 'purchase_receipt_no', 'production_order'], |
| 45 | 'Price List': [], |
| 46 | 'KRA Sheet': [], |
| 47 | 'Production Order': ['status', 'project_name', 'production_item'], |
| 48 | 'Account': ['lft', 'rgt', 'parent_account'], |
| 49 | 'Earn Deduction Detail': [], |
| 50 | 'Indent': ['status', 'transaction_date'], |
| 51 | 'Tag Detail': [], |
| 52 | 'SS Deduction Detail': ['d_type'], |
| 53 | 'Batch': ['item'], |
| 54 | 'Deduction Type': [], |
| 55 | 'Project': ['project_name', 'customer'], |
| 56 | 'UserRole': ['role'], |
| 57 | 'DocField': ['label', 'fieldtype', 'fieldname'], |
| 58 | 'Property Setter': ['doc_type', 'doc_name', 'property'], |
| 59 | 'Appraisal': ['status', 'employee'], |
| 60 | 'Letter Head': [], |
| 61 | 'Follow up': ['follow_up_by'], |
| 62 | 'Project Cost Breakup': [], |
| 63 | 'Table Mapper Detail': [], |
| 64 | 'Campaign': [], |
| 65 | 'Static Parameter Detail': [], |
| 66 | 'Leave Type': [], |
| 67 | 'Account Balance': ['period', 'start_date', 'end_date', 'account'], |
| 68 | 'Absent Days Detail': [], |
| 69 | 'Tag': [], |
| 70 | 'Raw Materials Supplied': ['raw_material'], |
| 71 | 'Project Activity Update': [], |
| 72 | 'PR Raw Material Detail': [], |
| 73 | 'Bank Reconciliation Detail': ['voucher_id'], |
| 74 | 'Sales Order': ['quotation_no', 'project_name', 'customer', 'posting_date'], |
| 75 | 'Chapter VI A Detail': [], |
| 76 | 'Experience In Company Detail': [], |
| 77 | 'Order Reconciliation Detail': ['sales_order_no'], |
| 78 | 'Attendance': ['employee', 'att_date'], |
| 79 | 'Previous Experience Detail': [], |
| 80 | 'Earning Detail': ['e_type'], |
| 81 | 'Sales Order Detail': ['item_code', 'prevdoc_docname', 'reserved_warehouse'], |
| 82 | 'KRA Template': [], |
| 83 | 'Budget Distribution': ['fiscal_year'], |
| 84 | 'Workstation': ['warehouse'], |
| 85 | 'Period': [], |
| 86 | 'Training Session Details': [], |
| 87 | 'Other Charges': [], |
| 88 | 'State': [], |
| 89 | 'Bulk Rename Tool': [], |
| 90 | 'Landed Cost Master Detail': [], |
| 91 | 'Employee': ['employee_name', 'designation', 'department'], |
| 92 | 'Terms And Conditions': [], |
| 93 | 'TC Detail': [], |
| 94 | 'UOM': [], |
| 95 | 'Supplier Type': [], |
| 96 | 'Project Milestone': [], |
| 97 | 'Landed Cost Master': [], |
| 98 | 'Budget Distribution Detail': [], |
| 99 | 'Form 16A Ack Detail': [], |
| 100 | 'Campaign Expense': [], |
| 101 | 'Time Sheet': ['employee_name', 'time_sheet_date'], |
| 102 | 'File Group': ['parent_group'], |
| 103 | 'Maintenance Visit Detail': ['item_code', 'service_person'], |
| 104 | 'Support Ticket Response': [], |
| 105 | 'PV Detail': ['item_code', 'purchase_order', 'po_detail', 'purchase_receipt', 'pr_detail', 'expense_head', 'cost_center'], |
| 106 | 'Timesheet Detail': ['project_name', 'task_id', 'customer_name'], |
| 107 | 'Holiday List Detail': [], |
| 108 | 'Workflow Rule Detail': [], |
| 109 | 'Module Def': ['module_seq', 'module_page'], |
| 110 | 'Term': [], |
| 111 | 'PF Detail': ['item_code'], |
| 112 | 'POS Setting': ['user', 'territory'], |
| 113 | 'QA Specification Detail': [], |
| 114 | 'Support Ticket': ['customer', 'allocated_to', 'status'], |
| 115 | 'Project Activity': ['project'], |
| 116 | 'Customer Group': ['lft', 'rgt', 'parent_customer_group'], |
| 117 | 'Return Detail': ['item_code'], |
| 118 | 'Series Detail': [], |
| 119 | 'Event Role': ['role'], |
| 120 | 'Contact': ['employee_id'], |
| 121 | 'BOM Material': ['item_code', 'bom_no'], |
| 122 | 'Invest 80 Declaration Detail': [], |
| 123 | 'PO Raw Material Detail': [], |
| 124 | 'Industry Type': [], |
| 125 | 'Declaration Detail': [], |
| 126 | 'Holiday List': ['fiscal_year'], |
| 127 | 'Sales Person': ['lft', 'rgt', 'parent_sales_person'], |
| 128 | 'RV Detail': ['item_code', 'sales_order', 'so_detail', 'delivery_note', 'dn_detail', 'cost_center', 'income_account'], |
| 129 | 'Module Def Item': [], |
| 130 | 'TDS Category': [], |
| 131 | 'DocTrigger': [], |
| 132 | 'Print Format': ['standard'], |
| 133 | 'Installed Item Details': ['prevdoc_docname', 'item_code'], |
| 134 | 'Form 16A Tax Detail': [], |
| 135 | 'Event': ['event_date', 'event_type'], |
| 136 | 'Currency': [], |
| 137 | 'Service Quotation Detail': ['item_code'], |
| 138 | 'Warehouse Type': ['warehouse_type'], |
| 139 | 'Sales BOM': ['item_group'], |
| 140 | 'IT Checklist': ['employee'], |
| 141 | 'Purchase Other Charges': [], |
| 142 | 'Company': [], |
| 143 | 'Call Log': [], |
| 144 | 'Professional Training Details': [], |
| 145 | 'Warehouse': ['warehouse_type'], |
| 146 | 'Competitor': [], |
| 147 | 'Mode of Payment': [], |
| 148 | 'Training Session': ['customer'], |
| 149 | 'Cost Center': ['lft', 'rgt', 'parent_cost_center'], |
| 150 | 'Timesheet': ['status', 'timesheet_date'], |
| 151 | 'Form 16A': ['party_no'], |
| 152 | 'Sales BOM Detail': ['item_code'], |
| 153 | 'Answer': ['question'], |
| 154 | 'Supplier': [], |
| 155 | 'Installation Note': ['delivery_note_no', 'customer', 'inst_date'], |
| 156 | 'Expense Voucher': ['approval_status', 'employee'], |
| 157 | 'Target Detail': ['from_date', 'to_date', 'fiscal_year'], |
| 158 | 'Page Role': ['role'], |
| 159 | 'Partner Target Detail': ['fiscal_year', 'item_group'], |
| 160 | 'Shipping Address': ['customer'], |
| 161 | 'Indent Detail': ['item_code', 'warehouse'], |
| 162 | 'TDS Payment Detail': [], |
| 163 | 'Market Segment': [], |
| 164 | 'Comment Widget Record': [], |
| 165 | 'Service Order Detail': ['item_code', 'prevdoc_docname'], |
| 166 | 'TDS Payment': ['from_date', 'to_date', 'tds_category'], |
| 167 | 'Lead Email CC Detail': [], |
| 168 | 'User Setting-Role User': [], |
| 169 | 'Salary Slip': ['month', 'year', 'employee'], |
| 170 | 'Maintenance Schedule Detail': ['item_code', 'scheduled_date'], |
| 171 | 'Employment Type': [], |
| 172 | 'Advance Allocation Detail': ['journal_voucher'], |
| 173 | 'Quotation': ['customer', 'transaction_date'], |
| 174 | 'Deduction Detail': ['d_type'], |
| 175 | 'Bill Of Materials': ['item', 'project_name'], |
| 176 | 'Earning Type': [], |
| 177 | 'Designation': [], |
| 178 | 'BOM Replace Utility Detail': ['parent_bom'], |
| 179 | 'Question': [], |
| 180 | 'Stock Ledger Entry': ['item_code', 'warehouse', 'posting_date', 'posting_time'], |
| 181 | 'Educational Qualifications Detail': [], |
| 182 | 'BOM Operation': [], |
| 183 | 'Item Group': ['lft', 'rgt', 'parent_item_group'], |
| 184 | 'Workflow Action Detail': [], |
| 185 | 'User Setting-Profile': [], |
| 186 | 'Customer Issue': ['item_code', 'customer', 'complaint_date'], |
| 187 | 'Feed': [], |
| 188 | 'Purchase Tax Detail': ['account_head'], |
| 189 | 'GL Mapper Detail': [], |
| 190 | 'TDS Detail': [], |
| 191 | 'PRO Detail': ['item_code', 'source_warehouse'], |
| 192 | 'DocType Label': [], |
| 193 | 'Receivable Voucher': ['posting_date', 'debit_to', 'project_name'], |
| 194 | 'GL Entry': ['posting_date', 'account', 'voucher_no'], |
| 195 | 'Serial No': ['status', 'warehouse'], |
| 196 | 'Delivery Note': ['posting_date', 'project_name', 'customer'], |
| 197 | 'UOM Conversion Detail': ['uom'], |
| 198 | 'Search Criteria': ['criteria_name'], |
| 199 | 'Salary Structure': [], |
| 200 | 'Educational Qualifications': ['qualification'], |
| 201 | 'TDS Rate Chart': ['applicable_from', 'applicable_to'], |
| 202 | 'GL Mapper': [], |
| 203 | 'Announcement': [], |
| 204 | 'Call Log Details': [], |
| 205 | 'Enquiry': ['lead', 'customer', 'transaction_date'], |
| 206 | 'Flat BOM Detail': ['item_code'], |
| 207 | 'Landed Cost Detail': ['account_head'], |
| 208 | 'Field Mapper Detail': ['from_field', 'to_field'], |
| 209 | 'File Data': [], |
| 210 | 'Question Tag': [], |
| 211 | 'QA Inspection Report': ['item_code', 'purchase_receipt_no', 'report_date'], |
| 212 | 'Appraisal Detail': [], |
| 213 | 'POS Settings': ['territory'], |
| 214 | 'Delivery Note Detail': ['item_code', 'prevdoc_docname', 'warehouse', 'prevdoc_detail_docname'], |
| 215 | 'Profile': [], |
| 216 | 'Other Income Detail': [], |
| 217 | 'Product': ['item_code', 'stock_warehouse'], |
| 218 | 'PO Detail': ['prevdoc_docname', 'item_code', 'prevdoc_detail_docname', 'warehouse'], |
| 219 | 'Module Def Role': ['role'], |
| 220 | 'Sales Team': ['sales_person'], |
| 221 | 'Enquiry Detail': ['item_code'], |
| 222 | 'DocType': [], |
| 223 | 'Compaint Note': ['nature_of_complaint', 'compliance_date'], |
| 224 | 'Maintenance Schedule': ['customer', 'sales_order_no'], |
| 225 | 'Event User': ['person'], |
| 226 | 'Stock Reconciliation': ['reconciliation_date'], |
| 227 | 'Purchase Receipt': ['posting_date', 'supplier', 'project_name'], |
| 228 | 'Complaint Detail': ['item_name'], |
| 229 | 'Address': ['customer', 'supplier'], |
| 230 | 'Ticket': ['request_date', 'allocated_to', 'category', 'customer', 'project'], |
| 231 | 'Territory Target Detail': ['month', 'fiscal_year'], |
| 232 | 'LC PR Detail': ['purchase_receipt_no'], |
| 233 | 'Customer': ['customer_name', 'customer_group'], |
| 234 | 'PP SO Detail': [], |
| 235 | 'PP Detail': ['document_date', 'item_code', 'parent_item'], |
| 236 | 'User Setting-Role Permission': [], |
| 237 | 'Custom Script': ['dt'], |
| 238 | 'Country': [], |
| 239 | 'DefaultValue': [], |
| 240 | 'Ledger Detail': [], |
| 241 | 'SS Earning Detail': ['e_type'], |
| 242 | 'SMS Log': [], |
| 243 | 'Expense Type': [], |
| 244 | 'Item': ['item_group'], |
| 245 | 'Fiscal Year': [], |
| 246 | 'ToDo Item': ['role'], |
| 247 | 'Payable Voucher': ['posting_date', 'credit_to', 'project_name', 'supplier'], |
| 248 | 'Journal Voucher Detail': ['account', 'against_voucher', 'against_invoice', 'against_jv'], |
| 249 | 'Online Contact': [], |
| 250 | 'Page': ['module'], |
| 251 | 'Leave Application': ['employee', 'leave_type', 'from_date', 'to_date'], |
| 252 | 'Expense Voucher Detail': ['expense_type'], |
| 253 | 'Maintenance Visit': ['customer', 'sales_order_no', 'customer_issue_no'], |
| 254 | 'Ref Rate Detail': ['price_list_name', 'ref_currency'], |
| 255 | 'Receiver Detail': [], |
| 256 | 'Naming Series Options': ['doc_type'], |
| 257 | 'Activity Type': [], |
| 258 | 'PRO PP Detail': [], |
| 259 | 'Delivery Note Packing Detail': ['item_code', 'parent_item', 'warehouse'], |
| 260 | 'Workflow Rule': ['select_form'], |
| 261 | 'File': ['file_group'], |
| 262 | 'Item Maintenance Detail': ['item_code', 'start_date', 'end_date', 'prevdoc_docname'], |
| 263 | 'Purchase Order': ['supplier', 'project_name', 'posting_date'], |
| 264 | 'Print Heading': [], |
| 265 | 'TDS Rate Detail': ['category'] |
| 266 | } |
| 267 | |
| 268 | for dt in dt_index_fields.keys(): |
| 269 | current_index = sql("show indexes from `tab%s`" % dt) |
| 270 | |
| 271 | proper_index = dt_index_fields[dt] |
| 272 | |
| 273 | for d in current_index: |
| 274 | if d[4] not in ['name', 'parent', 'parenttype']: |
| 275 | if d[4] not in proper_index: |
| 276 | sql("ALTER TABLE `tab%s` DROP INDEX %s" % (dt, d[4])) |
| 277 | sql("start transaction") |
| 278 | sql("UPDATE `tabDocField` SET search_index = 0 WHERE fieldname = '%s' AND parent = '%s'" % (d[4], dt)) |
| 279 | sql("commit") |
| 280 | else: |
| 281 | proper_index.remove(d[4]) |
| 282 | |
| 283 | for d in proper_index: |
| 284 | sql("ALTER TABLE `tab%s` ADD INDEX ( `%s` ) " % (dt, d)) |
| 285 | sql("start transaction") |
| 286 | sql("UPDATE `tabDocField` SET search_index = 1 WHERE fieldname = '%s' AND parent = '%s'" % (d, dt)) |
| 287 | sql("commit") |
| 288 | |
| 289 | sql("start transaction") |
| 290 | dt_module = sql("select module from `tabDocType` where name = '%s'" % dt)[0][0] |
| 291 | export_to_files(record_list = [['DocType', dt]], record_module = dt_module) |
| 292 | sql("commit") |