0_cleanupad.sql

Java写的ERP系统

--  tables w/o Key
SELECT	ColumnName "<<< Create KeyCol >>>" 
FROM	AD_Column c
WHERE	UPPER(ColumnName) = (Select UPPER(TableName) || '_ID' from ad_table t 
						where c.ad_table_id=t.ad_table_id)
AND	NOT EXISTS
	(SELECT * FROM AD_Table t, AD_Column c2
	 WHERE t.AD_Table_ID=c2.AD_Table_ID
	 AND c.AD_Column_ID=c2.AD_Column_ID AND c2.IsKey='Y');

UPDATE	AD_Column c
SET		Iskey = 'Y'
WHERE	UPPER(ColumnName) = (Select UPPER(TableName) || '_ID' from ad_table t 
						where c.ad_table_id=t.ad_table_id)
AND	NOT EXISTS
	(SELECT * FROM AD_Table t, AD_Column c2
	 WHERE t.AD_Table_ID=c2.AD_Table_ID
	 AND c.AD_Column_ID=c2.AD_Column_ID AND c2.IsKey='Y');


SELECT '<<< Key/Parent Columns not updateable >>>' FROM DUAL;
UPDATE	AD_Column
  SET	IsUpdateable = 'N' 
WHERE	IsUpdateable='Y' AND (IsKey='Y' OR IsParent='Y');


SELECT '<<< Parent Fields Read/Only >>>' FROM DUAL;
UPDATE AD_Field
  SET IsReadOnly='Y'
WHERE IsReadOnly='N'
  AND AD_Column_ID IN (SELECT AD_Column_ID FROM AD_Column WHERE IsParent='Y');

-- Make Parent Fields r/w for tabs with two parents and a defined actual parent column
UPDATE	AD_Field f
  SET	IsReadOnly='N'
WHERE	IsReadOnly='Y' 
  AND EXISTS
	(SELECT * FROM AD_Column c WHERE c.AD_Column_ID=f.AD_Column_ID AND c.IsParent='Y')
  AND AD_Tab_ID IN
	(SELECT AD_Tab_ID
	FROM AD_Tab
	WHERE AD_Column_ID IS NOT NULL
	-- tables with two parent fields
	AND AD_Table_ID IN
		(SELECT AD_Table_ID FROM AD_Table t
		WHERE (SELECT COUNT(*) FROM AD_Column c WHERE c.AD_Table_ID=t.AD_Table_ID AND IsParent='Y')>1)
	);

-- Make Actual Parent fields r/o in tabs with two parent fields
UPDATE	AD_Field f
  SET	IsReadOnly='Y'
WHERE	IsReadOnly='N' 
  AND (AD_Tab_ID, AD_Column_ID) IN
	(SELECT AD_Tab_ID, AD_Column_ID
	FROM AD_Tab
	WHERE AD_Column_ID IS NOT NULL
	-- tables with two parent fields
	AND AD_Table_ID IN
		(SELECT AD_Table_ID FROM AD_Table t
		WHERE (SELECT COUNT(*) FROM AD_Column c WHERE c.AD_Table_ID=t.AD_Table_ID AND IsParent='Y')>1)
	);

SELECT '<<< COMMIT >>>' FROM DUAL;
COMMIT;

--
SELECT '<<< Set Validation Rules for TableDir Client & Org >>>' FROM DUAL;

UPDATE AD_Column
SET AD_Val_Rule_ID = 103
WHERE UPPER(ColumnName)='AD_CLIENT_ID' AND AD_Val_Rule_ID IS NULL AND AD_Reference_ID=19;

UPDATE AD_Column
SET AD_Val_Rule_ID=104
WHERE UPPER(ColumnName)='AD_ORG_ID' AND AD_Val_Rule_ID IS NULL AND AD_Reference_ID=19;

UPDATE AD_Column
SET IsUpdateable='Y'
WHERE IsUpdateable IS NULL;

UPDATE AD_Column
SET IsUpdateable = 'N'
WHERE (UPPER(ColumnName)='AD_CLIENT_ID' OR UPPER(ColumnName)='AD_ORG_ID')
AND IsUpdateable <> 'N';


-- 
SELECT '<<< Set System ownership >>>' FROM DUAL;

UPDATE AD_Table SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Column SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
--
UPDATE AD_Window SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Tab SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Field SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Window_Trl SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Tab_Trl SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Field_Trl SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
--
UPDATE AD_Reference SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Ref_List SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Ref_Table SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Reference_Trl SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Ref_List_Trl SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Val_Rule SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
--
UPDATE AD_Element SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Element_Trl SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
--
UPDATE AD_Menu SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
UPDATE AD_Menu_Trl SET AD_Client_ID=0, AD_Org_ID=0 WHERE AD_Client_ID!=0 OR AD_Org_ID!=0;
--

SELECT '<<< COMMIT >>>' FROM DUAL;
COMMIT;

--
SELECT '<<< Sync ColumnName & TableName_ID for TableDir >>>' FROM DUAL;

UPDATE AD_Element e
SET ColumnName = (SELECT t.TableName||'_ID' FROM AD_Table t 
	WHERE UPPER(e.ColumnName)=UPPER(t.TableName)||'_ID' 
	 AND NOT e.ColumnName=t.TableName||'_ID')
WHERE EXISTS (SELECT * FROM AD_Table t 
	WHERE UPPER(e.ColumnName)=UPPER(t.TableName)||'_ID' 
	 AND NOT e.ColumnName=t.TableName||'_ID');

SELECT '<<< COMMIT >>>' FROM DUAL;
COMMIT;

--
-- Update all: String length
SELECT '<<< Update Field length >>>' FROM DUAL;

-- sets all numeric to 22 and date to 7
UPDATE	AD_Column c
SET		FieldLength = (SELECT DataLength FROM temp_db_columns db 
						WHERE c.ad_column_id=db.ad_column_id)
WHERE	FieldLength <> (SELECT DataLength FROM temp_db_columns db 
						WHERE c.ad_column_id=db.ad_column_id);

SELECT '<<< Set Display Length >>>' FROM DUAL;

update ad_field set displaylength = 1		-- Checks, Radio
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id in (20, 24))
and displaylength != 1;

update ad_field set displaylength = 11		-- integer
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id = 11)
and displaylength != 11;

update ad_field set displaylength = 14		-- date fields
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id = 15)
and displaylength != 14;

update ad_field set displaylength = 20		-- datetime
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id = 16)
and displaylength != 20;

update ad_field set displaylength = 26		-- amount, number, quantity
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id in (12, 22, 29))
and displaylength != 26;

update ad_field set displaylength = 26		-- account, location, search
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id in (25, 21, 30))
and displaylength != 26;

update ad_field set displaylength = 14		-- starting point for picks
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id in (13,17,18,19,26,27))
and displaylength != 14;

update ad_field set displaylength = 60		-- Text
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id = 14)
and displaylength != 60;

update ad_field set displaylength = 60		-- Long Strings
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id = 10)
and displaylength > 60;

update ad_field set displaylength = 23		-- starting point for button
where ad_column_id in (select ad_column_id from ad_column where ad_reference_id = 28)
and displaylength != 23;

update ad_field set displaylength = 20		-- document no
where name = 'Document No' and displaylength != 20;

update ad_field set displaylength = 20		-- value, password, alias
where (name like 'Value%' or name = 'Password' or name = 'Alias')
and displaylength > 20;


-- Normalize Length

update ad_field set displaylength = 5
where displaylength != 5 and (displaylength > 1 and displaylength < 6);

update ad_field set displaylength = 11		-- integers = 11
where displaylength is null or displaylength = 0 or displaylength = 10 or displaylength = 12;

update ad_field set displaylength = 17
where displaylength = 16 or displaylength = 18;

update ad_field set displaylength = 20
where displaylength = 19 or displaylength = 21;

update ad_field set displaylength = 23
where displaylength = 22 or displaylength = 24;

update ad_field set displaylength = 26		-- numbers = 26
where displaylength = 25 or displaylength = 27;

update ad_field set displaylength = 29
where displaylength > 29 and displaylength < 35;

select DisplayLength "Odd DisplayLength", Name from ad_field
where displaylength not in (1, 5, 6, 8, 11,14,17,20,23,26,29, 40, 60)
order by 1;
--

SELECT '<<< COMMIT >>>' FROM DUAL;
COMMIT;

-- Fini
SELECT '<<< FINI - Dropping Views >>>' FROM DUAL;
DROP VIEW Temp_db_columns;
--
COMMIT;