0_cleanupad.sql

大家共享愉快, 共享愉快, 共享愉快, 共享愉快,共享愉快

/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html 
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either 
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: 0_CleanUpAD.sql,v 1.27 2005/07/25 18:31:49 jjanke Exp $
 ***
 * Title:	Clean up Application Dictionary
 * Description:
 *		=> Run TempTables_Create/Drop <=	first
 *                                          otherwise you'll delete all views
 * Please check EVERY statement before execution. 
 * This is a script desiged for the reference database not extensions.
 * (e.g. assigns ownership to system, etc.)
 ************************************************************************/
 
SELECT (SELECT COUNT(*) FROM USER_Procedures) "Procedures", 
(SELECT COUNT(*) FROM USER_Triggers) "Triggers" FROM DUAL;

SELECT 'Clean up Application Dictionary' FROM DUAL;

-- Create temp table of all colums in AD and DB
SELECT '<<< Create temp table of all colums in AD and DB >>>' FROM DUAL;

CREATE OR REPLACE VIEW temp_db_columns AS
	SELECT t.TableName, c.ColumnName, t.AD_Table_ID, c.AD_Column_ID, 
		uc.Data_Type, COALESCE(uc.Char_Col_Decl_Length,uc.Data_Length) AS DataLength,
		uc.Data_Precision, uc.Data_Scale, uc.Nullable, c.ColumnSQL,
        CASE WHEN uc.Data_Type IS NULL AND c.ColumnSQL IS NULL THEN 'Y' ELSE 'N' END AS IsNotInDB
	FROM AD_Table t
      INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID)
      LEFT JOIN User_Tab_Columns uc ON (uc.Table_Name=UPPER(t.TableName)
		AND uc.Column_Name=UPPER(c.ColumnName));

-- Delete columns not in DB = translated fields
SELECT	Name "Delete fields not in DB", AD_Field_ID
FROM	AD_Field f
WHERE NOT EXISTS 
	(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=f.AD_Column_ID AND db.IsNotInDB='N');

DELETE	AD_Field_Trl
WHERE	AD_Field_ID IN 
	(SELECT AD_Field_ID FROM AD_Field f WHERE NOT EXISTS 
		(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=f.AD_Column_ID AND db.IsNotInDB='N'));

-- Delete fields not in DB
DELETE	AD_Field
WHERE	AD_Field_ID IN 
	(SELECT AD_Field_ID FROM AD_Field f WHERE NOT EXISTS 
		(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=f.AD_Column_ID AND db.IsNotInDB='N'));

-- Delete columns not in DB
SELECT	Name "Delete columns not in DB", AD_Column_ID
FROM	AD_Column c
WHERE NOT EXISTS 
	(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=c.AD_Column_ID AND db.IsNotInDB='N'); 

-- column used in table reference
DELETE	AD_Ref_Table
WHERE	AD_Key IN 
	(SELECT AD_Column_ID FROM AD_Column c WHERE NOT EXISTS 
		(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=c.AD_Column_ID AND db.IsNotInDB='N'));
DELETE	AD_Ref_Table
WHERE	AD_Display IN
	(SELECT AD_Column_ID FROM AD_Column c WHERE NOT EXISTS 
		(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=c.AD_Column_ID AND db.IsNotInDB='N'));

-- remove references
UPDATE	AD_Tab
SET		AD_Column_ID = NULL
WHERE	AD_Column_ID IN
	(SELECT AD_Column_ID FROM AD_Column c WHERE NOT EXISTS 
		(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=c.AD_Column_ID AND db.IsNotInDB='N'));

DELETE	AD_PrintFormatItem
WHERE	AD_Column_ID IN 
	(SELECT AD_Column_ID FROM AD_Column c WHERE NOT EXISTS 
		(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=c.AD_Column_ID AND db.IsNotInDB='N'));

DELETE	AD_Column
WHERE	AD_Column_ID IN 
	(SELECT AD_Column_ID FROM AD_Column c WHERE NOT EXISTS 
		(SELECT * FROM temp_db_columns db WHERE db.AD_Column_ID=c.AD_Column_ID AND db.IsNotInDB='N'));

-- Delete all AD_Tab rows of tables not in DB = translated tabs
SELECT	Name "Delete Tabs w tables not in DB", AD_Tab_ID
FROM	AD_Tab 
WHERE	AD_Table_ID IN
	(SELECT AD_Table_ID FROM AD_Table t WHERE NOT EXISTS
		(SELECT * FROM temp_db_columns db WHERE db.AD_Table_ID=t.AD_Table_ID AND db.IsNotInDB='N'));

DELETE	AD_Tab_Trl
WHERE	AD_Tab_ID IN 
	(SELECT AD_Tab_ID FROM AD_Tab
	WHERE AD_Table_ID IN
		(SELECT AD_Table_ID FROM AD_Table t WHERE NOT EXISTS
			(SELECT * FROM temp_db_columns db WHERE db.AD_Table_ID=t.AD_Table_ID AND db.IsNotInDB='N')));

DELETE	AD_Tab
WHERE	AD_Table_ID IN
	(SELECT AD_Table_ID FROM AD_Table t WHERE NOT EXISTS
		(SELECT * FROM temp_db_columns db WHERE db.AD_Table_ID=t.AD_Table_ID AND db.IsNotInDB='N'));


-- Delete all AD_Table rows of tables not in DB
SELECT	Name "Delete Tables not in DB", AD_Table_ID
FROM	AD_Table t
WHERE NOT EXISTS
	(SELECT * FROM temp_db_columns db WHERE db.AD_Table_ID=t.AD_Table_ID AND db.IsNotInDB='N');

DELETE	AD_Table t
WHERE NOT EXISTS
	(SELECT * FROM temp_db_columns db WHERE db.AD_Table_ID=t.AD_Table_ID AND db.IsNotInDB='N');

-- Update: IsMandatory
SELECT '<<< Update: IsMandatory >>>' FROM DUAL;

UPDATE	AD_Column c
SET		IsMandatory = 'Y'
WHERE	IsMandatory = 'N' 
	AND EXISTS 
		(SELECT * FROM temp_db_columns db 
		WHERE c.AD_Column_ID=db.AD_Column_ID AND Nullable='N');

--	set AD_Client_ID/AD_Org_ID to TableDirect
SELECT '<<< set AD_Client_ID, AD_Org_ID to TableDir >>>' FROM DUAL;

UPDATE	AD_Column
SET		AD_Reference_ID = 19
WHERE	(UPPER(ColumnName) = 'AD_CLIENT_ID' OR UPPER(ColumnName) = 'AD_ORG_ID') 
	AND AD_Reference_ID NOT IN (13, 18, 19);

--	set _ID to Type ID (DisplayType)
SELECT '<<< set _ID to Type ID >>>' FROM DUAL;

Update	AD_Column
  Set	AD_Reference_ID = 13    --  ID
WHERE	ColumnName LIKE '%\_ID' ESCAPE '\' 
	AND AD_Reference_ID not in (13, 17, 18, 19, 21, 25, 27, 30, 31, 32, 33, 35)
	AND ColumnName NOT IN ('Record_ID', 'Find_ID')
    -- Buttons with Process
    AND NOT (AD_Reference_ID=28 AND AD_Process_ID IS NOT NULL);

--	set Record_ID/Find_ID
UPDATE	AD_Column
  SET	AD_Reference_ID = 22	--	Number
WHERE	ColumnName = 'Find_ID'
  AND AD_Reference_ID <> 22;
UPDATE	AD_Column
  SET	AD_Reference_ID = 28	-- Button
WHERE	AD_Reference_ID NOT IN (28, 11)	--	Integer 
  AND	ColumnName = 'Record_ID';

--	set UpdatedBy, CreatedBy to Table
SELECT '<<< set UpdatedBy, CreatedBy to Table & User >>>' FROM DUAL;

UPDATE	AD_Column
  SET	AD_Reference_ID = 18,   --  Table
		AD_Reference_Value_ID = 110
WHERE	UPPER(ColumnName) IN ('CREATEDBY', 'UPDATEDBY') 
	AND AD_Reference_ID != 18;

--	set Updated, Created to DateTime
SELECT '<<< set Updated, Created to DateTime >>>' FROM DUAL;

UPDATE	AD_Column
SET		AD_Reference_ID = 16    --  DateTime
WHERE	ColumnName IN ('Created', 'Updated') 
	AND AD_Reference_ID != 16;

--	set Updated, Created to not updateable
SELECT '<<< set Updated, Created to not updateable >>>' FROM DUAL;

UPDATE	AD_Column
  SET	IsUpdateable = 'N'
WHERE	UPPER(ColumnName) IN ('CREATEDBY', 'UPDATEDBY','CREATED', 'UPDATED')
	AND IsUpdateable != 'N';


--	set _Acct
SELECT '<<< set _Acct >>>' FROM DUAL;

UPDATE	AD_Column
SET		AD_Reference_ID = 25,
		AD_Reference_Value_ID = NULL
WHERE	ColumnName LIKE '%\_Acct' ESCAPE '\'
	AND AD_Reference_ID != 25;

--	set IsActive
SELECT '<<< set IsActive >>>' FROM DUAL;

UPDATE	AD_Column
SET		DefaultValue = 'Y'
WHERE	ColumnName = 'IsActive' and DefaultValue IS Null;

--  Set Replication Strategy
SELECT '<<< Set Replication Strategy to Local >>>' FROM DUAL;

UPDATE	AD_Table
SET		ReplicationType = 'L'
WHERE	ReplicationType IS Null;

--  Clean up Field SortNo
SELECT '<<< Clean up Field SortNo >>>' FROM DUAL;

UPDATE	AD_Field
SET		SortNo = Null
WHERE	SortNo = 0;

--
SELECT '<<< Create Identifiers for Column Name for tables w/o identifier >>>' FROM DUAL;
UPDATE	AD_Column c
SET		IsIdentifier = 'Y',
		SeqNo = 1
WHERE	ColumnName = 'Name'
AND	NOT EXISTS
	(SELECT * FROM AD_Table t, AD_Column c2
	 WHERE t.AD_Table_ID=c2.AD_Table_ID
		AND c2.IsIdentifier='Y');

SELECT '<<< Create Identifiers for Column DocumentNo for tables w/o identifier >>>' FROM DUAL;
UPDATE	AD_Column c
SET		IsIdentifier = 'Y',
		SeqNo = 1
WHERE	ColumnName = 'DocumentNo'
AND	NOT EXISTS
	(SELECT * FROM AD_Table t, AD_Column c2
	 WHERE t.AD_Table_ID=c2.AD_Table_ID
		AND c2.IsIdentifier='Y');

SELECT '<<< Create Identifiers for Column Line for tables w/o identifier >>>' FROM DUAL;
UPDATE	AD_Column c
SET		IsIdentifier = 'Y',
		SeqNo = 1
WHERE	ColumnName = 'Line'
AND	NOT EXISTS
	(SELECT * FROM AD_Table t, AD_Column c2
	 WHERE t.AD_Table_ID=c2.AD_Table_ID
		AND c2.IsIdentifier='Y');

--  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 INNER JOIN AD_Column c2 ON (t.AD_Table_ID=c2.AD_Table_ID)
	 WHERE c.AD_Column_ID=c2.AD_Column_ID AND c2.IsKey='Y') 
  AND c.ColumnName<>'AD_ChangeLog_ID';

UPDATE	AD_Column c
SET		Iskey = 'Y', AD_Reference_ID=13
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 INNER JOIN AD_Column c2 ON (t.AD_Table_ID=c2.AD_Table_ID)
	 WHERE c.AD_Column_ID=c2.AD_Column_ID AND c2.IsKey='Y')
  AND c.ColumnName<>'AD_ChangeLog_ID';

 
SELECT '<<< Key Columns not updateable >>>' FROM DUAL;
UPDATE	AD_Column
  SET	IsUpdateable = 'N'