fix_sequences.sql

国外的一套开源CRM

-- This script updates all the sequences to make sure they are higher than the highest used number.

----------
-- Create temp table to hold the existing max sequence values.
----------

--create table temp_seq_update (
--	seq_name,
--	seq_id,

----------
-- Write script to update sequences
----------

-- Account
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.account_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'Account\';'
	)
into outfile 'c:/temp/fix_account_sequence.sql'
from account a
where a.account_id < 'A';

source c:/temp/fix_account_sequence.sql;

-- Activity
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.activity_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'Activity\';'
	)
into outfile 'c:/temp/fix_activity_sequence.sql'
from Activity a
where a.activity_id < 'A';

source c:/temp/fix_activity_sequence.sql;

-- Address
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.address_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'Address\';'
	)
into outfile 'c:/temp/fix_address_sequence.sql'
from address a
where a.address_id < 'A';

source c:/temp/fix_address_sequence.sql;

-- Deal
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.deal_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'Deal\';'
	)
into outfile 'c:/temp/fix_deal_sequence.sql'
from deal a
where a.deal_id < 'A';

source c:/temp/fix_deal_sequence.sql;

-- EntityAcess
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.entity_access_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'EntityAcess\';'
	)
into outfile 'c:/temp/fix_entity_access_sequence.sql'
from entity_access a
where a.entity_access_id < 'A';

source c:/temp/fix_entity_access_sequence.sql;

-- ItIssue
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.issue_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'ItIssue\';'
	)
into outfile 'c:/temp/fix_it_issue_sequence.sql'
from it_issue a
where a.issue_id < 'A';

source c:/temp/fix_it_issue_sequence.sql;

-- ItIssueHistory
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.history_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'ItIssueHistory\';'
	)
into outfile 'c:/temp/fix_it_issue_history_sequence.sql'
from it_issue_history a
where a.history_id < 'A';

source c:/temp/fix_it_issue_history_sequence.sql;

-- LargeData
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.data_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'LargeData\';'
	)
into outfile 'c:/temp/fix_large_data_sequence.sql'
from large_data a
where a.data_id < 'A';

source c:/temp/fix_large_data_sequence.sql;

-- OpportunityProduct
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.opportunity_product_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'OpportunityProduct\';'
	)
into outfile 'c:/temp/fix_opportunity_product_sequence.sql'
from opportunity_product a
where a.opportunity_product_id < 'A';

source c:/temp/fix_opportunity_product_sequence.sql;

-- Party
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.party_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'Party\';'
	)
into outfile 'c:/temp/fix_party_sequence.sql'
from party a
where a.party_id < 'A';

source c:/temp/fix_party_sequence.sql;

-- Product
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.product_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'Product\';'
	)
into outfile 'c:/temp/fix_product_sequence.sql'
from product a
where a.product_id < 'A';

source c:/temp/fix_product_sequence.sql;

-- Role
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.role_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'Role\';'
	)
into outfile 'c:/temp/fix_role_sequence.sql'
from role a
where a.role_id < 'A';

source c:/temp/fix_role_sequence.sql;

-- TeamMember
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.team_member_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'TeamMember\';'
	)
into outfile 'c:/temp/fix_ream_member_sequence.sql'
from team_member a
where a.team_member_id < 'A';

source c:/temp/fix_ream_member_sequence.sql;

-- UiDisplayObject
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.display_object_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'UiDisplayObject\';'
	)
into outfile 'c:/temp/fix_ui_display_object_sequence.sql'
from ui_display_object a
where a.display_object_id < 'A';

source c:/temp/fix_ui_display_object_sequence.sql;

-- UiQuery
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.query_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'UiQuery\';'
	)
into outfile 'c:/temp/fix_ui_query_sequence.sql'
from ui_query a
where a.query_id < 'A';

source c:/temp/fix_ui_query_sequence.sql;

-- UiQueryValue
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.query_value_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'UiQueryValue\';'
	)
into outfile 'c:/temp/fix_ui_query_value_sequence.sql'
from ui_query_value a
where a.query_value_id < 'A';

source c:/temp/fix_ui_query_value_sequence.sql;

-- UiScreen
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.screen_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'UiScreen\';'
	)
into outfile 'c:/temp/fix_ui_screen_sequence.sql'
from ui_screen a
where a.screen_id < 'A';

source c:/temp/fix_ui_screen_sequence.sql;

-- UiScreenSection
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.section_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'UiScreenSection\';'
	)
into outfile 'c:/temp/fix_ui_screen_section_sequence.sql'
from ui_screen_section a
where a.section_id < 'A';

source c:/temp/fix_ui_screen_section_sequence.sql;

-- UiEntity
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.entity_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'UiEntity\';'
	)
into outfile 'c:/temp/fix_ui_entity_sequence.sql'
from ui_entity a
where a.entity_id < 'A';

source c:/temp/fix_ui_entity_sequence.sql;

-- UiAttribute
select concat(
	'update sequence_value_item set seq_id = (TRUNCATE(',
	max(LPAD(a.attribute_id,10,'0')),
	'/10, 0) + 1) * 10 where seq_name = \'UiAttribute\';'
	)
into outfile 'c:/temp/fix_ui_attribute_sequence.sql'
from ui_attribute a
where a.attribute_id < 'A';

source c:/temp/fix_ui_attribute_sequence.sql;