dbMerge.crmscript

#setLanguageLevel 3;

/*
  Script to merge one SuperOffice database into another. This script must be executed in the SLAVE database. It will generate a SQL script which will do the following:
  1. Find first available sequence number in MASTER and bump all ID's in SLAVE to be above this value.
  2. Create extra tables and extra fields in MASTER as they are in SLAVE.
  3. Copy all data from SLAVE to MASTER.
  
  You need to maintain the following:
  1. Update tableDefinitionString below, and make sure "fields" is correct for the tables where it is present (the ones that support extra fields)
  2. Update tableDefinitionString below and add new tables as required. Please keep them alphabetical.
  
  The table struct should be evident, but here are some specifics:
  - update: Array of fields that should be updated in this table for every row. Each field is "feld=addField" which becomes "update table set field = field + addField"
  - updateWhere: These are fields that should be updated, but with a where clause. Each element is an object with an "update" and a "where", and the update field is like above.
  - fields: This is a comma separated list of all fields to copy for this table. Required for tables which supports extra fields, as SLAVE and MASTER tables may differ in structure.
  - registeredAssociate: if "true", then we update the two colums registered_associate_id and updated_associate_id as well
  - dbiAgent: if "true", then we update the dbi_agent_id column as well
  - dbMerge: array of columns that should have appended "(DB_Merge)" to indicate this is an entry from the SLAVE database
  - serviceTable: if "true", then this is a service table where the primary key is "id" and not "table_id".
  - noCopy: if "true", then we do not copy data from this table.
  
  ISSUES:
  - Udef
  - Ticket.tags
  
  (c) 2020 Sverre Hjelm, Kodesentralen AS
*/

// Config values
String slaveDB = "merge_slave";
String masterDB = "merge_master";
String slaveSchema = "crm7";
String masterSchema = "crm7";

String tableDefinitionString = '
[
  {
    "name": "address",
    "update": ["address_id=@address"],
    "registeredAssociate": "true",
    "updateWhere": [
      { "update": "owner_id=@contact", "where": "atype_idx < 16384"},
      { "update": "owner_id=@person", "where": "atype_idx > 16384"}
    ]
  },
  {
    "name": "appointment",
    "update": ["appointment_id=@appointment","associate_id=@associate","group_idx=@usergroup"],
    "registeredAssociate": "true",
    "updateWhere": [
      { "update": "task_idx=@task", "where": "type in (0, 1, 2, 3, 6, 7)"},
      { "update": "task_idx=@doctmpl", "where": "type in (4, 5, 8, 9)"},
      { "update": "mother_id=@appointment", "where": "mother_id > 0"},
      { "update": "recurrenceruleid=@recurrencerule", "where": "recurrenceruleid > 0"},
      { "update": "contact_id=@contact", "where": "contact_id > 0"},
      { "update": "person_id=@person", "where": "person_id > 0"},
      { "update": "project_id=@project", "where": "project_id > 0"},
      { "update": "document_id=@document", "where": "document_id > 0"},
      { "update": "emailid=@email", "where": "emailid > 0"},
      { "update": "sale_id=@sale", "where": "sale_id > 0"},
      { "update": "text_id=@text", "where": "text_id > 0"},
      { "update": "invitedPersonId=@person", "where": "invitedPersonId > 0"},
      { "update": "suggestedAppointmentId=@appointment", "where": "suggestedAppointmentId > 0"},
      { "update": "suggestedDocumentId=@appointment", "where": "suggestedDocumentId > 0"}
    ]
  },
  {
    "name": "associate",
    "update": ["associate_id=@associate","group_idx=@usergroup","person_id=@person","ejuserid=@ejuser"],
    "dbMerge": ["name"],
    "registeredAssociate": "true"
  },
  {
    "name": "attachment",
    "update": ["id=@attachment"],
    "serviceTable": "true"
  },
  {
    "name": "business",
    "update": ["business_id=@business"],
    "dbMerge": ["name"],
    "registeredAssociate": "true"
  },
  {
    "name": "category",
    "update": ["category_id=@category"],
    "dbMerge": ["name"],
    "registeredAssociate": "true"
  },
  {
    "name": "category_membership",
    "update": ["id=@category_membership", "category_id=@ej_category","user_id=@ejuser"],
    "serviceTable": "true"
  },
  {
    "name": "comptr",
    "update": ["comptr_id=@comptr"],
    "dbMerge": ["name"],
    "registeredAssociate": "true"
  },
  {
    "name": "contact",
    "update": ["business_idx=@business","category_idx=@category","contact_id=@contact","associate_id=@associate","text_id=@text","group_id=@usergroup"],
    "fields": "contact_id, name, kananame, department, number1, number2, associate_id, country_id, business_idx, category_idx, xstop, nomailing, registered, registered_associate_id, updated, updated_associate_id, text_id, mother_id, userdef_id, orgNr, soundEx, source, userdef2_id, activeInterests, updatedCount, group_id, tzLocationId, ticketPriorityId, deleted, supportLanguageId, supportAssociateId, supportPersonId, dbi_agent_id, dbi_key, dbi_last_syncronized, dbi_last_modified, activeErpLinks, DeletedDate",
    "registeredAssociate": "true"
  },
  {
    "name": "contactinterest",
    "update": ["cinterest_idx=@contint","contactinterest_id=@contactinterest","contact_id=@contact"],
    "registeredAssociate": "true"
  },
  {
    "name": "contint",
    "update": ["contint_id=@contint"],
    "dbMerge": ["name"],
    "registeredAssociate": "true"
  },
  {
    "name": "credited",
    "update": ["credited_id=@credited"],
    "dbMerge": ["name"],
    "registeredAssociate": "true"
  },
  {
    "name": "cust_category",
    "update": ["id=@cust_category", "language_id=@cust_lang","category_id=@ej_category"],
    "updateWhere": [
      {"update": "parent_id=@cust_category", "where": "parent_id > 0"}  
    ],
    "dbMerge": ["name"],
    "serviceTable": "true"
  },
  {
    "name": "cust_lang",
    "update": ["id=@cust_lang"],
    "dbMerge": ["name"],
    "serviceTable": "true"
  },
  {
    "name": "dataright",
    "update": ["dataright_id=@dataright","roleid=@role"],
    "registeredAssociate": "true",
    "noCopy": "true"
  },
  {
    "name": "dbi_agent",
    "update": ["id=@dbi_agent"],
    "serviceTable": "true"
  },
  {
    "name": "dbi_agent_field",
    "update": ["id=@dbi_agent_field", "agent_id=@dbi_agent"],
    "serviceTable": "true"
  },
  {
    "name": "dbi_agent_schedule",
    "update": ["id=@dbi_agent_schedule", "agent_id=@dbi_agent","script_id=@ejscript","schedule_id=@schedule"],
    "serviceTable": "true"
  },
  {
    "name": "doc_document",
    "update": ["id=@doc_document", "folder_id=@doc_folder","attachment_id=@attachment","created_by=@ejuser","changed_by=@ejuser"],
    "serviceTable": "true"
  },
  {
    "name": "doc_folder",
    "update": ["id=@doc_folder", "created_by=@ejuser"],
    "updateWhere": [
      { "update": "folder_id=@doc_folder", "where": "folder_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "doctmpl",
    "update": ["doctmpl_id=@doctmpl"],
    "dbMerge": ["name"]
  },
  {
    "name": "document",
    "update": ["document_id=@document","text_id=@text","appointment_id=@appointment"],
    "registeredAssociate": "true"
  },
  {
    "name": "eab_entry",
    "update": ["id=@eab_entry", "folder_id=@eab_folder"],
    "dbiAgent": "true",
    "serviceTable": "true"
  },
  {
    "name": "eab_folder",
    "update": ["id=@eab_folder", "folder_id=@eab_folder"],
    "serviceTable": "true"
  },
  {
    "name": "ej_category",
    "update": ["id=@ej_category", "category_master=@ejuser"],
    "fields": "id, parent_id, name, category_master, fullname, flags, delegate_method, delegate_counter, external_name, closing_status, msg_closing_status, assignment_lag, reply_template, notification_email",
    "updateWhere": [
      { "update": "parent_id=@ej_category", "where": "parent_id > 0"}
    ],
    "dbMerge": ["name"],
    "serviceTable": "true"
  },
  {
    "name": "ej_message",
    "update": ["id=@ej_message", "ticket_id=@ticket"],
    "updateWhere": [
      { "update": "customer_id=@person", "where": "customer_id > 0"}
    ],
    "fields": "id, ticket_id, search_title, created_at, created_by, type, message_category, author, slevel, message_id, time_spent, time_charge, body, html_body, email_header, debug_info, mail_sorter, customer_id, body_attachment, html_body_attachment, html_body_inline_converted, inbox, important, language, sentiment, sentimentConfidence",
    "serviceTable": "true"
  },
  {
    "name": "ejscript",
    "update": ["id=@ejscript"],
    "updateWhere": [
      { "update": "hierarchy_id=@hierarchy", "where": "hierarchy_id > 0"}
    ],
    "dbMerge": ["unique_identifier"],
    "serviceTable": "true"
  },
  {
    "name": "ejsel_ejsel",
    "update": ["id=@ejsel_ejsel", "parent=@ejselection","child=@ejselection"],
    "serviceTable": "true"
  },
  {
    "name": "ejsel_meta_result",
    "update": ["id=@ejsel_meta_result", "ejselection=@ejselection","owner=@ejuser"],
    "serviceTable": "true"
  },
  {
    "name": "ejsel_result_set",
    "update": ["id=@ejsel_result_set", "meta_result=@ejsel_meta_result"],
    "serviceTable": "true"
  },
  {
    "name": "ejselection",
    "update": ["id=@ejselection"],
    "updateWhere": [
      { "update": "hierarchy_id=@hierarchy", "where": "hierarchy_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "ejuser",
    "update": ["id=@ejuser", "profile_id=@profile"],
    "updateWhere": [
      { "update": "picture_id=@attachment", "where": "picture_id > 0"}
    ],
    "fields": "id, loginname, usergroup, username, password, firstname, middlename, lastname, email, access_level, status, signature, notify_mask, email_mask, sms_mask, language, dictionary, default_category, textareasize, flags, last_category, profile_id, hidden_bag_bitset, notify_external_lock, notify_external_source, login_attempts, last_login_attempt, idle_time, default_user, sms, new_gui, dbi_agent_id, dbi_key, dbi_last_syncronized, dbi_last_modified, minute_offset, date_format, simultaneous_login, picture_id, not_present, default_status_new_ticket, default_status_add_message, num_expanded_messages, date_format_string, time_format_string, date_format_long_string, chat_status",
    "dbiAgent": "true",
    "serviceTable": "true"
  },
  {
    "name": "email",
    "update": ["email_id=@email"],
    "updateWhere": [
      { "update": "contact_id=@contact", "where": "contact_id > 0"},
      { "update": "person_id=@person", "where": "person_id > 0"}
    ],
    "registeredAssociate": "true"
  },
  {
    "name": "ext_datasource",
    "update": [],
    "serviceTable": "true"
  },
  {
    "name": "ext_field",
    "update": ["id=@ext_field", "ext_table=@ext_table"],
    "serviceTable": "true"
  },
  {
    "name": "ext_table",
    "update": ["id=@ext_table", "ext_datasource=@ext_datasource"],
    "serviceTable": "true"
  },
  {
    "name": "external_document",
    "update": ["id=@external_document", "attachment_id=@attachment"],
    "updateWhere": [
      { "update": "hierarchy_id=@hierarchy", "where": "hierarchy_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "extra_fields",
    "update": ["id=@extra_fields"],
    "updateWhere": [
      { "update": "extra_table=@extra_tables", "where": "extra_table > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "extra_tables",
    "update": ["id=@extra_tables"],
    "updateWhere": [
      { "update": "hierarchy_id=@hierarchy", "where": "hierarchy_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "extra_tables_result",
    "update": ["id=@extra_tables_result", "extra_table=@extra_tables"],
    "serviceTable": "true"
  },
  {
    "name": "functionrightrolelink",
    "update": ["functionrightrolelink_id=@functionrightrolelink","roleid=@role"],
    "registeredAssociate": "true",
    "noCopy": "true"
  },
  {
    "name": "hierarchy",
    "update": ["id=@hierarchy"],
  	"updateWhere": [
      { "update": "parent_id=@hierarchy", "where": "parent_id > 0"}
    ],
    "serviceTable": "true",
    "dbMerge": ["name"]
  },
  {
    "name": "invoice",
    "update": ["id=@invoice"],
  	"updateWhere": [
      { "update": "customer_id=@person", "where": "customer_id > 0"},
      { "update": "company_id=@contact", "where": "company_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "invoice_entry",
    "update": ["id=@invoice_entry", "message_id=@ej_message"],
    "serviceTable": "true"
  },
  {
    "name": "invoice_sum",
    "update": ["id=@invoice_sum"],
  	"updateWhere": [
      { "update": "customer_id=@person", "where": "customer_id > 0"},
      { "update": "company_id=@contact", "where": "company_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "kb_attachment",
    "update": ["id=@kb_attachment", "entry_id=@kb_entry","attachment_id=@attachment"],
    "serviceTable": "true"
  },
  {
    "name": "kb_category",
    "update": ["id=@kb_category"],
  	"updateWhere": [
      { "update": "parent_id=@kb_category", "where": "parent_id > 0"}
    ],
    "fields": "id, parent_id, name, fullname, description, sort_order, access_level, inherited_access_level, created_by, access_check, dbi_agent_id, dbi_key, dbi_last_syncronized, dbi_last_modified",
    "dbiAgent": "true",
    "serviceTable": "true"
  },
  {
    "name": "kb_category_log",
    "update": ["id=@kb_category_log"],
  	"updateWhere": [
      { "update": "kb_category_id=@kb_category", "where": "kb_category_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "kb_entry",
    "update": ["id=@kb_entry"],
  	"updateWhere": [
      { "update": "category_id=@kb_category", "where": "category_id > 0"}
    ],
    "fields": "id, category_id, title, question, answer, keywords, access_level, created_by, sort_order, last_changed, read_count, score, score_weight, flags, status, expire_date, workflow_phase, dbi_agent_id, dbi_key, dbi_last_syncronized, dbi_last_modified",
    "dbiAgent": "true",
    "serviceTable": "true"
  },
  {
    "name": "kb_entry_comment",
    "update": ["id=@kb_entry_comment", "kb_entry_id=@kb_entry","customer_id=@person"],
    "dbiAgent": "true",
    "serviceTable": "true"
  },
  {
    "name": "kb_entry_keyword",
    "update": ["id=@kb_entry_keyword", "kb_entry_id=@kb_entry"],
    "serviceTable": "true"
  },
  {
    "name": "kb_entry_log",
    "update": ["id=@kb_entry_log", "kb_entry_id=@kb_entry"],
    "serviceTable": "true"
  },
  {
    "name": "kb_group",
    "update": ["id=@kb_group", "kb_category_id=@kb_category","parent_id=@ej_category"],
    "dbiAgent": "true",
    "serviceTable": "true"
  },
  {
    "name": "kb_group_entry",
    "update": ["id=@kb_group_entry", "kb_group_id=@kb_group","kb_entry_id=@kb_entry"],
    "serviceTable": "true"
  },
  {
    "name": "kb_http_link",
    "update": ["id=@kb_http_link", "entry_id=@kb_entry"],
    "serviceTable": "true"
  },
  {
    "name": "mail_in_filter",
    "update": ["id=@mail_in_filter"],
  	"updateWhere": [
      { "update": "category_id=@ej_category", "where": "category_id > 0"},
      { "update": "reply_template_id=@reply_template", "where": "reply_template_id > 0"},
      { "update": "language_id=@cust_lang", "where": "language_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "message_customers",
    "update": ["id=@message_customers", "message_id=@ej_message","customer_id=@person"],
    "serviceTable": "true"
  },
  {
    "name": "message_header",
    "update": ["id=@message_header", "message_id=@ej_message"],
    "serviceTable": "true"
  },
  {
    "name": "ms_filter",
    "update": ["id=@ms_filter"],
  	"updateWhere": [
      { "update": "new_priority=@ticket_priority", "where": "new_priority > 0"},
      { "update": "new_category=@ej_category", "where": "new_category > 0"},
      { "update": "new_owner=@ejuser", "where": "new_owner > 1"},
      { "update": "reply_template=@reply_template", "where": "reply_template > 0"},
      { "update": "body_template=@reply_template_body", "where": "body_template > 0"},
      { "update": "autofaq_reply_category=@reply_template_folder", "where": "autofaq_reply_category > 0"},
      { "update": "ejscript=@ejscript", "where": "ejscript > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "ms_filter_mail",
    "update": ["id=@ms_filter_mail", "mail_sorter_id=@ms_filter","mail_in_filter_id=@mail_in_filter"],
    "serviceTable": "true"
  },
  {
    "name": "ms_substitute",
    "update": ["id=@ms_substitute", "ms_filter_id=@ms_filter"],
    "serviceTable": "true"
  },
  {
    "name": "ms_trashbin",
    "update": ["id=@ms_trashbin", "mail_sorter_id=@ms_filter","mail_in_filter_id=@mail_in_filter"],
    "serviceTable": "true"
  },
  {
    "name": "outbox",
    "update": ["id=@outbox"],
  	"updateWhere": [
      { "update": "ticket_id=@ticket", "where": "ticket_id > 0"},
      { "update": "message_id=@ej_message", "where": "message_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "outgoing_message",
    "update": ["id=@outgoing_message"],
    "serviceTable": "true"
  },
  {
    "name": "ownercontactlink",
    "update": ["ownercontactlink_id=@ownercontactlink","contact_id=@contact"],
    "registeredAssociate": "true"
  },
  {
    "name": "persint",
    "update": ["persint_id=@persint"],
    "dbMerge": ["name"]
  },
  {
    "name": "person",
    "update": ["position_idx=@perspos","person_id=@person","group_id=@usergroup"],
  	"updateWhere": [
      { "update": "contact_id=@contact", "where": "contact_id > 0"},
      { "update": "text_id=@text", "where": "text_id > 0"}  
    ],
    "fields": "person_id, contact_id, rank, lastname, firstname, mrmrs, title, text_id, position_idx, year_of_birth, month_of_birth, day_of_birth, phone_present, userdef_id, registered, registered_associate_id, updated, updated_associate_id, person_number, kanalname, kanafname, post1, post2, post3, usepersonaddress, middleName, source, nomailing, country_id, userdef2_id, retired, activeInterests, updatedCount, associate_id, group_id, salutation, department, initials, gender, business_idx, category_idx, tzLocationId, sentInfo, showContactTickets, ticketPriorityId, supportLanguageId, supportAssociateId, dbi_agent_id, dbi_key, dbi_last_syncronized, dbi_last_modified, blockEmarketing, activeErpLinks, DeletedDate",
    "registeredAssociate": "true"
  },
  {
    "name": "personinterest",
    "update": ["pinterest_idx=@persint","personinterest_id=@personinterest","person_id=@person"],
    "registeredAssociate": "true"
  },
  {
    "name": "perspos",
    "update": ["perspos_id=@perspos"],
    "dbMerge": ["name"]
  },
  {
    "name": "phone",
    "update": ["phone_id=@phone"],
    "registeredAssociate": "true",
    "updateWhere": [
      { "update": "owner_id=@contact", "where": "ptype_idx < 16384"},
      { "update": "owner_id=@person", "where": "ptype_idx > 16384"}
    ]
  },
  {
    "name": "pmembtype",
    "update": ["pmembtype_id=@pmembtype"],
    "dbMerge": ["name"],
    "registeredAssociate": "true"
  },
  {
    "name": "prob",
    "update": ["prob_id=@prob"],
    "dbMerge": ["name"]
  },
  {
    "name": "profile",
    "update": ["id=@profile", "user_id=@ejuser"],
    "serviceTable": "true"
  },
  {
    "name": "project",
    "update": ["type_idx=@projtype","status_idx=@projstatus","project_id=@project","associate_id=@associate","group_id=@usergroup","postittext_id=@text"],
    "updateWhere": [
      { "update": "text_id=@text", "where": "text_id > 0"}
    ],
    "registeredAssociate": "true"
  },
  {
    "name": "projectmember",
    "update": ["mtype_idx=@pmembtype","projectmember_id=@projectmember","project_id=@project"],
    "updateWhere": [
      { "update": "contact_id=@contact", "where": "contact_id > 0"},
      { "update": "person_id=@person", "where": "person_id > 0"},
      { "update": "text_id=@text", "where": "text_id > 0"}
    ],
    "registeredAssociate": "true"
  },
  {
    "name": "projstatus",
    "update": ["projstatus_id=@projstatus"],
    "dbMerge": ["name"],
    "registeredAssociate": "true"
  },
  {
    "name": "projtype",
    "update": ["projtype_id=@projtype"],
    "dbMerge": ["name"],
    "registeredAssociate": "true"
  },
  {
    "name": "reason",
    "update": ["reason_id=@reason"],
    "dbMerge": ["name"],
    "registeredAssociate": "true"
  },
  {
    "name": "recurrencerule",
    "update": ["recurrencerule_id=@recurrencerule"],
    "registeredAssociate": "true"
  },
  {
    "name": "refcounts",
    "update": ["refcounts_id=@refcounts","recordid=@doctmpl"],
    "registeredAssociate": "true"
  },
  {
    "name": "relationdefinition",
    "update": ["reldef_id=@relationdefinition"],
    "dbMerge": ["name"],
    "primaryKey": "reldef_id"
  },
  {
    "name": "relations",
    "update": ["relation_id=@relations","reldef_id=@relationdefinition"],
    "registeredAssociate": "true",
    "primaryKey": "relation_id",
    "updateWhere": [
      { "update": "source_record=@contact", "where": "source_table = 5"},
      { "update": "destination_record=@contact", "where": "destination_table = 5"},
      { "update": "source_record=@person", "where": "source_table = 6"},
      { "update": "destination_record=@person", "where": "destination_table = 6"}
    ]
  },
  {
    "name": "reply_template",
    "update": ["id=@reply_template", "owned_by=@ejuser"],
    "updateWhere": [
      { "update": "folder_id=@reply_template_folder", "where": "folder_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "reply_template_attachment",
    "update": ["id=@reply_template_attachment", "entry_id=@reply_template","attachment_id=@attachment"],
    "serviceTable": "true"
  },
  {
    "name": "reply_template_body",
    "update": ["id=@reply_template_body", "reply_template_id=@reply_template","language_id=@cust_lang"],
    "serviceTable": "true"
  },
  {
    "name": "reply_template_folder",
    "update": ["id=@reply_template_folder"],
    "updateWhere": [
      { "update": "parent_id=@reply_template_folder", "where": "parent_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "role",
    "update": ["role_id=@role"],
    "dbMerge": ["name"],
    "registeredAssociate": "true",
    "noCopy": "true"
  },
  {
    "name": "s_attachment",
    "update": ["id=@s_attachment", "attachment_id=@attachment","message_id=@s_message"],
    "serviceTable": "true"
  },
  {
    "name": "s_bounce_shipment",
    "update": ["id=@s_bounce_shipment", "shipment_id=@s_shipment","customer_id=@person"],
    "serviceTable": "true"
  },
  {
    "name": "s_link",
    "update": ["id=@s_link", "script_id=@ejscript","ticket_category=@ej_category","ticket_priority=@ticket_priority","ticket_owned_by=@ejuser","remove_from_selection=@ejselection","add_to_selection=@ejselection"],
    "serviceTable": "true"
  },
  {
    "name": "s_link_customer",
    "update": ["id=@s_link_customer", "customer_id=@person","link_id=@s_link","shipment_id=@s_shipment"],
    "serviceTable": "true"
  },
  {
    "name": "s_link_customer_statical",
    "update": ["id=@s_link_customer_statical", "link_id=@s_link","shipment_id=@s_shipment"],
    "serviceTable": "true"
  },
  {
    "name": "s_list",
    "update": ["id=@s_list", "folder_id=@doc_folder"],
    "dbiAgent": "true",
    "serviceTable": "true"
  },
  {
    "name": "s_list_customer",
    "update": ["id=@s_list_customer", "list_id=@s_list","customer_id=@person"],
    "serviceTable": "true"
  },
  {
    "name": "s_list_element",
    "update": ["id=@s_list_element", "list_id=@s_list"],
    "serviceTable": "true"
  },
  {
    "name": "s_list_shipment",
    "update": ["id=@s_list_shipment", "list_id=@s_list","shipment_id=@s_shipment"],
    "serviceTable": "true"
  },
  {
    "name": "s_message",
    "update": ["id=@s_message"],
    "updateWhere": [
      { "update": "folder_id=@doc_folder", "where": "folder_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "s_picture_entry",
    "update": ["id=@s_picture_entry", "attachment_id=@attachment"],
    "updateWhere": [
      { "update": "folder_id=@s_picture_folder", "where": "folder_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "s_picture_folder",
    "update": ["id=@s_picture_folder"],
    "updateWhere": [
      { "update": "parent_id=@s_picture_folder", "where": "parent_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "s_sent_message",
    "update": ["id=@s_sent_message", "shipment_id=@s_shipment","message_id=@s_message","customer_id=@person"],
    "serviceTable": "true"
  },
  {
    "name": "s_shipment",
    "update": ["id=@s_shipment", "message_id=@s_message","ticket_owner=@ejuser","ticket_category=@ej_category"],
    "updateWhere": [
      { "update": "folder_id=@doc_folder", "where": "folder_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "s_shipment_addr",
    "update": ["id=@s_shipment_addr", "shipment_id=@s_shipment"],
    "updateWhere": [
      { "update": "contact_id=@contact", "where": "contact_id > 0"},
      { "update": "customer_id=@person", "where": "customer_id > 0"},
      { "update": "ticket_id=@ticket", "where": "ticket_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "s_smtp_servers",
    "update": ["id=@s_smtp_servers", "shipment_id=@s_shipment"],
    "serviceTable": "true"
  },
  {
    "name": "s_washing_list",
    "update": ["id=@s_washing_list", "list_id=@s_list"],
    "serviceTable": "true"
  },
  {
    "name": "sale",
    "update": ["comptr_id=@comptr","sale_id=@sale","associate_id=@associate","group_idx=@usergroup"],
    "updateWhere": [
      { "update": "credited_id=@credited", "where": "credited_id > 0"},
      { "update": "reason_id=@reason", "where": "reason_id > 0"},
      { "update": "source_id=@source", "where": "source_id > 0"},
      { "update": "contact_id=@contact", "where": "contact_id > 0"},
      { "update": "person_id=@person", "where": "person_id > 0"},
      { "update": "appointment_id=@appointment", "where": "appointment_id > 0"},
      { "update": "text_id=@text", "where": "text_id > 0"},
      { "update": "project_id=@project", "where": "project_id > 0"}
    ],
    "registeredAssociate": "true"
  },
  {
    "name": "schedule",
    "update": ["id=@schedule"],
    "updateWhere": [
      { "update": "after_schedule_id=@schedule", "where": "after_schedule_id > 0"}
    ],
    "dbMerge": ["name"],
    "serviceTable": "true"
  },
  {
    "name": "scheduled_task",
    "update": ["id=@scheduled_task", "script_id=@ejscript","schedule_id=@schedule"],
    "serviceTable": "true"
  },
  {
    "name": "screen_definition",
    "update": ["id=@screen_definition"],
    "updateWhere": [
      { "update": "hierarchy_id=@hierarchy", "where": "hierarchy_id > 0"}
    ],
    "serviceTable": "true"
  },
  {
    "name": "screen_definition_action",
    "update": ["id=@screen_definition_action", "screen_definition=@screen_definition","ejscript=@ejscript"],
    "serviceTable": "true"
  },
  {
    "name": "screen_definition_element",
    "update": ["id=@screen_definition_element", "screen_definition=@screen_definition"],
    "serviceTable": "true"
  },
  {
    "name": "screen_definition_language",
    "update": ["id=@screen_definition_language", "screen_definition=@screen_definition"],
    "serviceTable": "true"
  },
  {
    "name": "searchcat",
    "update": ["searchcat_id=@searchcat"],
    "dbMerge": ["name"],
    "registeredAssociate": "true"
  },
  {
    "name": "searchcriteria",
    "update": ["searchcriteria_id=@searchcriteria"],
    "registeredAssociate": "true",
    "updateWhere": [
      { "update": "ownerId=@selection", "where": "ownerTable=23"}
    ]
  },
  {
    "name": "searchcriteriagroup",
    "update": ["searchcriteriagroup_id=@searchcriteriagroup", "searchcriteria_id=@searchcriteria"],
    "registeredAssociate": "true"
  },
  {
    "name": "searchcriterion",
    "update": ["searchcriterion_id=@searchcriterion", "searchcriteriagroup_id=@searchcriteriagroup"],
    "registeredAssociate": "true"
  },
  {
    "name": "searchcriterionvalue",
    "update": ["searchcriterionvalue_id=@searchcriterionvalue", "searchcriterion_id=@searchcriterion"],
    "registeredAssociate": "true"
  },
  {
    "name": "selection",
    "dbMerge": ["name"],
    "update": ["selection_id=@selection","associate_id=@associate"],
    "registeredAssociate": "true",
    "updateWhere": [
      { "update": "leftSelectionId=@selection", "where": "leftSelectionId > 0"},
      { "update": "rightSelectionId=@selection", "where": "rightSelectionId > 0"}
    ]
  },
  {
    "name": "selectionmember",
    "update": ["selectionmember_id=@selectionmember","selection_id=@selection"],
    "updateWhere": [
      { "update": "contact_id=@contact", "where": "contact_id > 0"},
      { "update": "person_id=@person", "where": "person_id > 0"},
      { "update": "targetRecordId=@contact", "where": "targetTableNumber=5"},
      { "update": "targetRecordId=@sale", "where": "targetTableNumber=13"},
      { "update": "targetRecordId=@project", "where": "targetTableNumber=11"},
      { "update": "targetRecordId=@person", "where": "targetTableNumber=6"},
      { "update": "targetRecordId=@appointment", "where": "targetTableNumber=9"},
      { "update": "targetRecordId=@document", "where": "targetTableNumber=10"}
    ]
  },
  {
    "name": "sms",
    "update": ["id=@sms", "outbox_id=@outbox","shipment_id=@s_shipment","customer_id=@person","message_id=@ej_message","outgoing_message_id=@outgoing_message"],
    "serviceTable": "true"
  },
  {
    "name": "source",
    "update": ["source_id=@source"],
    "dbMerge": ["name"],
    "registeredAssociate": "true"
  },
  {
    "name": "system_script",
    "update": ["id=@system_script", "script_id=@ejscript"],
    "serviceTable": "true"
  },
  {
    "name": "task",
    "update": ["task_id=@task"],
    "dbMerge": ["name"]
  },
  {
    "name": "text",
    "update": ["text_id=@text"],
    "registeredAssociate": "true",
    "updateWhere": [
      { "update": "owner_id=@contact", "where": "type=1"},
      { "update": "owner_id=@person", "where": "type=2"},
      { "update": "owner_id=@appointment", "where": "type=4"},
      { "update": "owner_id=@document", "where": "type=5"},
      { "update": "owner_id=@project", "where": "type=6"},
      { "update": "owner_id=@sale", "where": "type=7"},
      { "update": "owner_id=@project", "where": "type=11"},
      { "update": "owner_id=@projectmember", "where": "type=13"},
      { "update": "owner_id=@appointment", "where": "type=15"}
    ]
  },
  {
    "name": "ticket",
    "update": ["id=@ticket","created_by=@ejuser","owned_by=@ejuser","category=@ej_category","orig_category=@ej_category","ticket_status=@ticket_status","priority=@ticket_priority","orig_priority=@ticket_priority"],
    "updateWhere": [
      { "update": "cust_id=@person", "where": "cust_id > 0"},
      { "update": "filter_id=@mail_in_filter", "where": "filter_id > 0"},
      { "update": "connect_id=@ticket", "where": "connect_id > 0"}
    ],
    "fields": "id, connect_id, title, created_at, last_changed, activate, closed_at, created_by, author, owned_by, category, orig_category, slevel, status, ticket_status, cust_id, priority, orig_priority, alert_level, alert_timeout, alert_stop, read_by_owner, first_read_by_owner, first_read_by_user, read_by_customer, filter_id, display_filter, replied_at, time_to_reply, time_to_close, real_time_to_reply, real_time_to_close, read_status, has_attachment, deadline, filter_address, dbi_agent_id, dbi_key, dbi_last_syncronized, dbi_last_modified, origin, time_spent_internally, time_spent_externally, time_spent_queue, real_time_spent_internally, real_time_spent_externally, real_time_spent_queue, time_spent_calculated, num_replies, num_messages, ticket_type, from_address, form_submission_id, tags, contact_id",
    "dbiAgent": "true",
    "serviceTable": "true"
  },
  {
    "name": "ticket_attachment",
    "update": ["id=@ticket_attachment", "attachment_id=@attachment","message_id=@ej_message"],
    "serviceTable": "true"
  },
  {
    "name": "ticket_customers",
    "update": ["id=@ticket_customers", "ticket_id=@ticket","customer_id=@person"],
    "serviceTable": "true"
  },
  {
    "name": "ticket_log",
    "update": ["id=@ticket_log", "ticket_id=@ticket"],
    "serviceTable": "true"
  },
  {
    "name": "ticket_log_action",
    "update": ["id=@ticket_log_action", "ticket_id=@ticket","org_ticket_id=@ticket","message_id=@ej_message","user_id=@ejuser","customer_id=@person"],
    "serviceTable": "true"
  },
  {
    "name": "ticket_log_change",
    "update": ["id=@ticket_log_change", "ticket_id=@ticket","message_id=@ej_message","action_id=@ticket_log_action","extra_field_id=@extra_fields"],
    "serviceTable": "true"
  },
  {
    "name": "ticket_priority",
    "update": ["id=@ticket_priority"],
    "serviceTable": "true",
    "dbMerge": ["name"]
  },
  {
    "name": "ticket_status",
    "update": ["id=@ticket_status"],
    "serviceTable": "true",
    "dbMerge": ["name"]
  },
  {
    "name": "ticket_status_history",
    "update": ["id=@ticket_status_history", "ticket_id=@ticket","ticket_status=@ticket_status"],
    "serviceTable": "true"
  },
  {
    "name": "url",
    "update": ["url_id=@url"],
    "updateWhere": [
      { "update": "contact_id=@contact", "where": "contact_id > 0"},
      { "update": "person_id=@person", "where": "person_id > 0"}
    ],
    "registeredAssociate": "true"
  },
  {
    "name": "user_attribute",
    "update": ["id=@user_attribute", "user_id=@ejuser"],
    "serviceTable": "true"
  },
  {
    "name": "user_candidate",
    "update": ["id=@user_candidate", "person_id=@person"],
    "serviceTable": "true"
  },
  {
    "name": "usergroup",
    "update": ["usergroup_id=@usergroup"],
    "dbMerge": ["name"],
    "registeredAssociate": "true"
  },
  {
    "name": "usergrouplink",
    "update": ["usergrouplink_id=@usergrouplink","assoc_id=@associate","usergroup_id=@usergroup"],
    "registeredAssociate": "true",
    "noCopy": "true"
  },
  {
    "name": "userrolelink",
    "update": ["userrolelink_id=@userrolelink","associate_id=@associate","role_id=@role"],
    "registeredAssociate": "true",
    "noCopy": "true"
  },
  {
    "name": "visiblefor",
    "update": ["visiblefor_id=@visiblefor","forgroupid=@usergroup","forassocid=@associate"],
    "registeredAssociate": "true",
    "updateWhere": [
      { "update": "recordid=@appointment", "where": "tableid=9"},
      { "update": "appointmentId=@appointment", "where": "tableid=9"},
      { "update": "recordid=@contact", "where": "tableid=5"},
      { "update": "recordid=@person", "where": "tableid=6"},
      { "update": "recordid=@project", "where": "tableid=11"},
      { "update": "recordid=@sale", "where": "tableid=13"},
      { "update": "saleid=@sale", "where": "tableid=13"},
      { "update": "recordid=@selection", "where": "tableid=23"},
      { "update": "selectionId=@selection", "where": "tableid=23"}
    ]
  }
]
';
  
struct UpdateWhere {
  String update;
  String where;
};

struct Table {
  String name;
  String[] update;
  String fields;
  String registeredAssociate;
  String[] dbMerge;
  String dbiAgent;
  String serviceTable;
  String primaryKey;
  String noCopy;
  UpdateWhere[] updateWhere;  
};

// Arrays created from strings above
Table[] tables;
tables.fromXMLNode(parseJSON2(tableDefinitionString));

String setStatement(String field, String add) {
  if (add.beginsWith("@"))
    add = add.toUpper();
  return field + " = " + field + " + " + add;
}

String createUpdateStatement(Table table) {
  String query;

  for (Integer i = 0; i < table.update.length(); i++)
    query += setStatement(table.update[i].until("="), table.update[i].after("=")) + ", ";
  
  for (Integer i = 0; i < table.dbMerge.length(); i++)
    query += setStatement(table.dbMerge[i], "'(DB_Merge)'") + ", ";

  if (table.dbiAgent == "true")
    query += setStatement("dbi_agent_id", "@DBI_AGENT") + ", ";

  if (table.registeredAssociate == "true") {
    query += setStatement("registered_associate_id", "@ASSOCIATE") + ", ";
    query += setStatement("updated_associate_id", "@ASSOCIATE") + ", ";
  }
  
  query = query.stripTrailing(" ,");
  
  if (query != "") 
    query = "UPDATE " + slaveDB + "." + slaveSchema + "." + table.name + " SET " + query + ";\n";
  

  for (Integer i = 0; i < table.updateWhere.length(); i++) {
    String update = table.updateWhere[i].update;
    String where = table.updateWhere[i].where;
    query += "UPDATE " + slaveDB + "." + slaveSchema + "." + table.name + " SET " + setStatement(update.until("="), update.after("=")) + " WHERE " + where + ";\n";
  }
  
  return query;
}

String declareSequenceVariables() {
  String sql;
  sql += "\n";
  sql += "-- ---------------------------- DECLARE VARIABLES\n";
  sql += "DECLARE ";
  for (Integer i = 0; i < tables.length(); i++) {
    sql += "@" + tables[i].name.toUpper() + " varchar(30),";
    if (i % 10 == 10) sql += "\n";
  }
  sql = sql.stripTrailing(" \n,");
  sql += ",@LEGALBASE varchar(30), @SHIPMENTTYPE varchar(30), @SHIPMENTTYPERESERVATION varchar(30), @CONSENTSOURCE varchar(30), @CONSENTPURPOSE varchar(30), ";
  sql += "@LEGALBASE_RANK varchar (30), @SHIPMENTTYPE_RANK varchar(30), @CONSENTSOURCE_RANK varchar(30), @CONSENTPURPOSE_RANK varchar(30), @CONSENTPERSON varchar(30), @STR_MAX INT\n";
  sql += "\n";
  return sql;
}

String getPrimaryKey(Table table) {
  if (table.primaryKey != "")
    return table.primaryKey;
  
  if (table.serviceTable == "true")
    return "id";
  
  // else
  return table.name + "_id";
}

String getSequenceValues() {
  String sql = "";
  sql += "\n";
  sql += "-- ---------------------------- GET NEW SEQUENCE VALUES\n";
  sql += "PRINT 'Incremented id values from slave database:'\n";
  for (Integer i = 0; i < tables.length(); i++) {
    sql += "SET @" + tables[i].name.toUpper() + " = (SELECT next_id FROM " + masterDB + "." + masterSchema + ".sequence WHERE TableName='" + tables[i].name.toUpper() + "')\n";
    sql += "PRINT '" + tables[i].name.toUpper() + ": ' + @" + tables[i].name.toUpper() + "\n";
  }

  for (Integer i = 0; i < tables.length(); i++) {
      sql += "UPDATE " + masterDB + "." + masterSchema + ".sequence SET next_id = next_id + (select 1 + max(" + getPrimaryKey(tables[i]) + ") from " + slaveDB + "." + slaveSchema + "." + tables[i].name + ") WHERE TableName='" + tables[i].name.toUpper() + "'\n"; 
  }
  
  sql += "SET @LEGALBASE = (SELECT next_id FROM crm7.SEQUENCE WHERE TableName = 'LEGALBASE')\n";
  sql += "SET @LEGALBASE_RANK = (SELECT MAX(rank) from crm7.LEGALBASE)\n";
  sql += "SET @SHIPMENTTYPE = (SELECT next_id FROM crm7.SEQUENCE WHERE TableName = 'SHIPMENTTYPE')\n";
  sql += "SET @SHIPMENTTYPE_RANK = (SELECT MAX(rank) from crm7.SHIPMENTTYPE)\n";
  sql += "SET @SHIPMENTTYPERESERVATION = (SELECT next_id FROM crm7.SEQUENCE WHERE TableName = 'SHIPMENTTYPERESERVATION')\n";
  sql += "SET @CONSENTSOURCE = (SELECT next_id FROM crm7.SEQUENCE WHERE TableName = 'CONSENTSOURCE')\n";
  sql += "SET @CONSENTSOURCE_RANK = (SELECT MAX(rank) from crm7.CONSENTSOURCE)\n";
  sql += "SET @CONSENTPURPOSE = (SELECT next_id FROM crm7.SEQUENCE WHERE TableName = 'CONSENTPURPOSE')\n";
  sql += "SET @CONSENTPURPOSE_RANK = (SELECT MAX(rank) from crm7.CONSENTPURPOSE)\n";
  sql += "SET @CONSENTPERSON = (SELECT next_id FROM crm7.SEQUENCE WHERE TableName = 'CONSENTPERSON')\n";

  sql += "\n";
  return sql;
}

Void addTableField(String table, String field) {
  for (Integer i = 0; i < tables.length(); i++) {
    if (tables[i].name == table)
      tables[i].fields += ", " + field;
  }
}

Void addTableUpdate(String table, String update) {
  for (Integer i = 0; i < tables.length(); i++) {
    if (tables[i].name == table)
      tables[i].update.pushBack(update);
  }
}

String getExtraFieldDatabaseType(Integer type) {
  String dataType;
  
  if (type == 10) dataType = "nvarchar(255)";
  else if (type == 2) dataType = "ntext";
  else if (type == 15) dataType = "float";
  else if (type == 3 || type == 4 || type == 5) dataType = "datetime";
  else if (type == 2) dataType = "nclob";
  else if (type == 6) dataType = "smallint";
  else dataType = "integer";
  
  return dataType;
}


String loadExtraFields() {
  Map createTable;
  
  String sql = "";
  sql += "-- Extra tables and fields\n";

  SearchEngine extraTables;
  extraTables.addFields("extra_tables", "id,table_name");
  for (extraTables.execute(); !extraTables.eof(); extraTables.next()) {
    Table newTable;
    newTable.name = extraTables.getField("extra_tables.table_name");
    newTable.fields = "id, last_changed, dbi_agent_id, dbi_key, dbi_last_syncronized, dbi_last_modified";
    newTable.serviceTable = "true";
    tables.pushBack(newTable);
    
    String create = "CREATE TABLE " + masterDB + "." + masterSchema + "." + newTable.name + " ([id] [int] IDENTITY(1,1) NOT NULL,[last_changed] [datetime] NULL,[dbi_agent_id] [int] NOT NULL,[dbi_key] [nvarchar](255) NOT NULL,[dbi_last_syncronized] [datetime] NOT NULL,[dbi_last_modified] [datetime] NOT NULL";
    createTable.insert(newTable.name, create);
  }
  
  SearchEngine extraFields;
  extraFields.addFields("extra_fields", "id,domain,extra_table.table_name,field_name,type");
  for (extraFields.execute(); !extraFields.eof(); extraFields.next()) {
    String fieldName = extraFields.getField("extra_fields.field_name");
    String tableName = extraFields.getField("extra_fields.extra_table.table_name");
    Integer domain = extraFields.getField("extra_fields.domain").toInteger();
    Integer type = extraFields.getField("extra_fields.type").toInteger();
    String table;

    if (domain == 1) table = "person";
    else if (domain == 2) table = "contact";
    else if (domain == 4) table = "ticket";
    else if (domain == 8) table = "ej_message";
    else if (domain == 16) table = tableName;
    else if (domain == 32) table = "ejuser";
    else if (domain == 64) table = "ej_category";
    else if (domain == 128) table = "kb_entry";
    else if (domain == 256) table = "kb_category";
      
    // Add to table.fields so that data is copied later on
    addTableField(table, fieldName);

    // Add to table.update so that FK's are bumped
    if (type == 16) 
      addTableUpdate(table, fieldName + "=@PERSON");
    else if (type == 17) 
      addTableUpdate(table, fieldName + "=@CONTACT");
    else if (type == 18) 
      addTableUpdate(table, fieldName + "=@TICKET");
    else if (type == 19) 
      addTableUpdate(table, fieldName + "=@EJUSER");
    else if (type == 22) 
      addTableUpdate(table, fieldName + "=@ATTACHMENT");
    else if (type == 24) 
      addTableUpdate(table, fieldName + "=@EJ_CATEGORY");
    else if (type == 25) 
      addTableUpdate(table, fieldName + "=@TICKET_PRIORITY");
    else if (type == 26) 
      addTableUpdate(table, fieldName + "=@KB_ENTRY");
    else if (type == 27) 
      addTableUpdate(table, fieldName + "=@SALE");
    else if (type == 28) 
      addTableUpdate(table, fieldName + "=@PROJECT");
    else if (type == 29) 
      addTableUpdate(table, fieldName + "=@APPOINTMENT");
    else if (type == 30) 
      addTableUpdate(table, fieldName + "=@CATEGORY");
    else if (type == 31) 
      addTableUpdate(table, fieldName + "=@BUSINESS");
    

    // Create column 
    if (domain == 16) {
      String create = ", " + fieldName + " " + getExtraFieldDatabaseType(type); // TODO: Add index support
      
      String tmp = createTable.get(table);
      tmp += create;
      createTable.insert(table, tmp);      
    }
    else
    {
      sql += "ALTER TABLE " + masterDB + "." + masterSchema + "." + table + " ADD " + fieldName + " " + getExtraFieldDatabaseType(type) + ";\n"; // TODO: Add index support
    }
  }
  
  // Add all create statements for extra tables stored in map
  for (createTable.first(); !createTable.eof(); createTable.next())
    sql += createTable.getVal() + ");\n";
  
  sql += "GO\n";
  
  return sql;
}

String bumpSlaveIds() {
  String sql = "";
  sql += "\n";
  sql += "-- ---------------------------- BUMP IDs IN SLAVE DATABASE\n";  
  for (Integer i = 0; i < tables.length(); i++) {
    sql += createUpdateStatement(tables[i]);
  }  
  
  sql += "update " + slaveDB + "." + slaveSchema + ".contact set userdef_id=0;\n";
  sql += "update " + slaveDB + "." + slaveSchema + ".contact set userdef2_id=0;\n";
  sql += "update " + slaveDB + "." + slaveSchema + ".person set userdef_id=0;\n";
  sql += "update " + slaveDB + "." + slaveSchema + ".person set userdef2_id=0;\n";
  sql += "update " + slaveDB + "." + slaveSchema + ".sale set userdef_id=0;\n";
  sql += "update " + slaveDB + "." + slaveSchema + ".sale set userdef2_id=0;\n";
  sql += "update " + slaveDB + "." + slaveSchema + ".project set userdef_id=0;\n";
  sql += "update " + slaveDB + "." + slaveSchema + ".project set userdef2_id=0;\n";
  sql += "update " + slaveDB + "." + slaveSchema + ".document set userdef_id=0;\n";
  sql += "update " + slaveDB + "." + slaveSchema + ".document set userdef2_id=0;\n";
  sql += "update " + slaveDB + "." + slaveSchema + ".appointment set userdef_id=0;\n";
  sql += "update " + slaveDB + "." + slaveSchema + ".appointment set userdef2_id=0;\n";
  
  return sql;
}

String copyData() {
  String sql = "";
  sql += "\n";
  sql += "-- ---------------------------- COPY DATA FROM SLAVE TO MASTER\n";
  for (Integer i = 0; i < tables.length(); i++) {
    
    if (tables[i].noCopy != "true") {
      String srcFields = tables[i].fields;
      if (srcFields == "")
        srcFields = "*";

      String targetFields = tables[i].fields;
      if (targetFields != "")
        targetFields = "(" + targetFields + ")";

      Bool identityInsert = tables[i].name.beginsWith("y_");

      if (identityInsert)
        sql += "SET IDENTITY_INSERT " + masterDB + "." + masterSchema + "." + tables[i].name + " ON\n";
      sql += "INSERT INTO " + masterDB + "." + masterSchema + "." + tables[i].name + " " + targetFields + " SELECT " + srcFields + " FROM " + slaveDB + "." + slaveSchema + "." + tables[i].name + "\n";
      if (identityInsert)
        sql += "SET IDENTITY_INSERT " + masterDB + "." + masterSchema + "." + tables[i].name + " OFF\n";
    }
  }  
  return sql;
}

String query;
query += loadExtraFields();
query += declareSequenceVariables();
query += getSequenceValues();
query += bumpSlaveIds();
query += copyData();
printLine(query);