Notes

SQL SERVER:

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'ALTER TABLE [' + Table_Name + '] ADD IS_Public bit;'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'PAG%'

OPEN cmds
WHILE 1 = 1
BEGIN
FETCH cmds INTO @cmd
IF @@fetch_status != 0 BREAK
EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

-------------------------------------------------------
DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'ALTER TABLE [' + Table_Name + '] ADD OCRFLAG NUMERIC (2,0);'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'PAG%'

OPEN cmds
WHILE 1 = 1
BEGIN
FETCH cmds INTO @cmd
IF @@fetch_status != 0 BREAK
EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds


ALTER TABLE PAGxxxxx ADD PAGE_DEL numeric(1);
ALTER TABLE PAGxxxxx ADD UPD_PRSN varchar(50);
-------------------------------------------------

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'ALTER TABLE [' + Table_Name + '] ADD Role_ID int;'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'DOC%' and Table_Name not in ('DocumentClassView','DOC_TEMP','DOCS','DOCSOPT')

OPEN cmds
WHILE 1 = 1
BEGIN
FETCH cmds INTO @cmd
IF @@fetch_status != 0 BREAK
EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds
-------------------------------------------------
CREATE TABLE [dbo].[IMLK_FLDS_STAMP_POSITION](
[DOC_ID] [numeric](5, 0) NOT NULL,
[FLD_SER] [numeric](9, 0) NOT NULL,
[Y_POS_EN] [numeric](10, 0) NULL,
[X_POS_EN] [numeric](10, 0) NULL,
[X_POS_AR] [numeric](10, 0) NULL,
[Y_POS_AR] [numeric](10, 0) NULL,
[VISABLE_EN] [bit] NULL,
[FONT_SIZE_AR] [numeric](2, 0) NULL,
[VISABLE_AR] [bit] NOT NULL,
[SPECIAL_CHAR_AR] [nvarchar](5) NULL,
[FONT_SIZE_EN] [numeric](2, 0) NULL,
[FONT_STYLE_AR] [nvarchar](50) NULL,
[FONT_STYLE_EN] [nvarchar](50) NULL,
[SPECIAL_CHAR_EN] [nvarchar](5) NULL
) ON [PRIMARY]
GO
-------------------------------------------------
CREATE TABLE [dbo].[IMLK_Indexes_Mapping_Source_Config](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Source_Type] [nvarchar](10) not NULL,
[Source_Connection] [nvarchar](MAX) not NULL,
[Source_Alias] [nvarchar](20) not NULL,
[Source_Table] [nvarchar](50) not NULL,
CONSTRAINT [PK_IMLK_Indexes_Mapping_Source_Config] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]
-------------------------------------------------
CREATE TABLE [dbo].[IMLK_Indexes_Mapping_Master_Tables_Config]
(
[Source_ID] int NOT NULL,
[Source_EN_Name] [nvarchar](MAX) ,
[Source_AR_Name] [nvarchar](MAX) ,
[Column_DB_Name] [nvarchar](MAX) not NULL,
[EN_Name] [nvarchar](MAX) ,
[AR_Name] [nvarchar](MAX) ,
[Column_Type] [nvarchar](20) ,
[Max_Length] numeric(5,0) ,
[Date_Format] [nvarchar](20) ,
[DDL_Query] [nvarchar](MAX) ,
[DDL_Display_Column] [nvarchar](MAX) ,
[DDL_Foreign_key] [nvarchar](MAX) ,
[Is_Primary] bit ,
[IsNull] bit ,
[Show_in_Search] bit ,
[Show_in_Grid] bit ,
[Show_DDL_as_Number] bit ,
)
-------------------------------------------------
CREATE TABLE [dbo].[IMLK_Indexes_Mapping_Master_Detail_Tables_Config]
(
[Master_Source_ID] int NOT NULL,
[Detail_Source_ID] int NOT NULL,
[Detail_EN_Name] [nvarchar](MAX) ,
[Detail_AR_Name] [nvarchar](MAX) ,
[Column_DB_Name] [nvarchar](MAX) not NULL,
[EN_Name] [nvarchar](MAX) ,
[AR_Name] [nvarchar](MAX) ,
[Show_Sum_in_Master] bit ,
[Master_Foreign_key] [nvarchar](MAX) ,
[Column_Type] [nvarchar](20)
)
-------------------------------------------------
CREATE TABLE [dbo].[IMLK_Indexes_Mapping_Config](
[Doc_ID] numeric(5, 0) NOT NULL,
[Doc_Index_ID] numeric(9, 0) not NULL,
[Source_Table_ID] int not NULL,
[Source_Table_Coulmn_Name] [nvarchar](50) not NULL,
[Where_Fields] [nvarchar](MAX) ,
[Lookup_Query] [nvarchar](MAX),
[Show_Insert_Screen_If_Empty] bit
)
-------------------------------------------------
CREATE TABLE ClientInformation
(
HasOCR bit,
OCRVendor nvarchar(15)
)

-------------------------------------------------

CREATE TABLE PublicDocumentClassView
(
DocumentID numeric(5, 0) not null,
IndexesID varchar(50),
PRIMARY KEY (DocumentID)
)

-------------------------------------------------

CREATE TABLE [dbo].[IMLK_CLIENT_ADDONS](
[FeatureID] [int] NOT NULL,
[FeatureStatus] [bit] NOT NULL,
[Client] [varchar](255) NULL,
[Description] [varchar](255) NULL
)

-------------------------------------------------

CREATE TABLE [dbo].[FileAnnotations](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [numeric](4, 0) NULL,
[DocumentClassID] [numeric](5, 0) NULL,
[DocumentInstanceID] [numeric](10, 0) NULL,
[FileID] [numeric](4, 0) NULL,
[Annotations] [nvarchar](max) NULL,
[VersionID] [numeric](5, 0) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

-------------------------------------------------

CREATE TABLE DocumentClassView
(
DocumentID numeric(5, 0) not null,
IndexesID varchar(50),
PRIMARY KEY (DocumentID)
)
-------------------------------------------------
CREATE TABLE [dbo].[IMLK_Roles_User_Security]
(
[User_ID] [int] NOT NULL,
[Doc_ID] [int] NOT NULL,
[Role_ID] [int] NOT NULL
)
------------------------------------------------

CREATE TABLE [dbo].[IMLK_Roles_Group_Security]
(
[Group_ID] [int] NOT NULL,
[Doc_ID] [int] NOT NULL,
[Role_ID] [int] NOT NULL
)
-------------------------------------------------
CREATE TABLE [dbo].[IMLK_Roles](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Role_Name] [nvarchar](50) not NULL,
[Role_Order] [int] NOT NULL,
CONSTRAINT [PK_IMLK_Roles] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]
-------------------------------------------------

ALTER TABLE IMLK_STATIONS alter COLUMN WINDOWS_VER nvarchar(100);
ALTER TABLE docs add files_filter varchar(50);
ALTER TABLE docs add OCR_Type varchar(50);
ALTER TABLE docs add IS_Public bit;
ALTER TABLE LOG_SYS ADD ARDescription VARCHAR(Max);
ALTER TABLE LOG_SYS ADD Description VARCHAR(Max);
ALTER TABLE LOG_SYS ADD GroupMembers VARCHAR(Max);
ALTER TABLE IMLKSYS add PAGRECYCLE numeric(1);
Alter table IMLK_ROUTETEMP add TEMP_STAT numeric(4);
Alter table docs add Has_Roles bit
Alter table docs add Has_Batch_Name bit
Alter table docs add Has_File_Classification bit
Alter table docs add BarcodeZone numeric(4,0)
Alter table docs add BarcodeFormat varchar(40)
ALTER TABLE IMLKSYS ADD FlexibleAddNew numeric(1,0);


**********************************************************************************

ORACLE:

ALTER TABLE LOG_SYS ADD GroupMembers VARCHAR2(4000);
ALTER TABLE LOG_SYS ADD ARDescription VARCHAR2(4000);
ALTER TABLE LOG_SYS ADD Description VARCHAR2(4000);
ALTER TABLE docs ADD OCR_Type varchar2(50);
ALTER TABLE IMLK_STATIONS MODIFY WINDOWS_VER varchar2(100);
ALTER TABLE docs add files_filter varchar(50);
ALTER TABLE docs add IS_Public number(1) ;
Alter table docs add Has_Roles number(1) ;
Alter table docs add Has_Batch_Name number(1) ;
Alter table docs add Has_File_Classification number(1) ;
Alter table docs add BarcodeZone number(4,0);
Alter table docs add BarcodeFormat varchar2(40);
ALTER TABLE IMLKSYS ADD FlexibleAddNew number(1,0);

-----------------------------------------------------
*****************************************************
CREATE TABLE IMLK_Indexes_Mapping_Source_Config
(
ID Number(10) NOT NULL,
Source_Type Nvarchar2(10) not NULL,
Source_Connection VARCHAR2(4000) not NULL,
Source_Alias Nvarchar2(20) not NULL,
Source_Table Nvarchar2(50) not NULL,
CONSTRAINT PK_IMLK_Indexes_Mapping_Source_Config PRIMARY KEY
(
ID
)
);
-----------------------------------------------------
CREATE TABLE IMLK_Indexes_Mapping_Master_Tables_Config
(
Source_ID number(10) NOT NULL,
Source_EN_Name VARCHAR2(4000) ,
Source_AR_Name VARCHAR2(4000) ,
Column_DB_Name VARCHAR2(4000) not NULL,
EN_Name VARCHAR2(4000) ,
AR_Name VARCHAR2(4000) ,
Column_Type Nvarchar2(20) ,
Max_Length number(5,0) ,
Date_Format Nvarchar2(20) ,
DDL_Query VARCHAR2(4000) ,
DDL_Display_Column VARCHAR2(4000) ,
DDL_Foreign_key VARCHAR2(4000) ,
Is_Primary number(1) ,
IsNull number(1) ,
Show_in_Search number(1) ,
Show_in_Grid number(1) ,
Show_DDL_as_Number number(1)
);
-----------------------------------------------------
CREATE TABLE IMLK_Indexes_Mapping_Master_Detail_Tables_Config
(
Master_Source_ID number(10) NOT NULL,
Detail_Source_ID number(10) NOT NULL,
Detail_EN_Name VARCHAR2(4000) ,
Detail_AR_Name VARCHAR2(4000) ,
Column_DB_Name VARCHAR2(4000) not NULL,
EN_Name VARCHAR2(4000) ,
AR_Name VARCHAR2(4000) ,
Show_Sum_in_Master number(1) ,
Master_Foreign_key VARCHAR2(4000) ,
Column_Type Nvarchar2(20)
);
-----------------------------------------------------
CREATE TABLE IMLK_Indexes_Mapping_Config(
Doc_ID number(5, 0) NOT NULL,
Doc_Index_ID number(9, 0) not NULL,
Source_Table_ID number(10) not NULL,
Source_Table_Coulmn_Name Nvarchar2(50) not NULL,
Where_Fields VARCHAR2(4000) ,
Lookup_Query VARCHAR2(4000) ,
Show_Insert_Screen_If_Empty number(1)
);
-----------------------------------------------------
CREATE TABLE IMLK_Roles(
ID int NOT NULL,
Role_Name Nvarchar2(50) not NULL,
Role_Order int NOT NULL,
CONSTRAINT PK_IMLK_Roles PRIMARY KEY
(
ID
)
);
-----------------------------------------------------
CREATE TABLE IMLK_Roles_User_Security
(
User_ID int NOT NULL,
Doc_ID int NOT NULL,
Role_ID int NOT NULL
);
------------------------------------------------
CREATE TABLE IMLK_Roles_Group_Security
(
Group_ID int NOT NULL,
Doc_ID int NOT NULL,
Role_ID int NOT NULL
);
*****************************************************
-----------------------------------------------------
CREATE TABLE DocumentClassView
(
DocumentID number (5, 0) not null,
IndexesID varchar(50),
PRIMARY KEY (DocumentID)
);
-----------------------------------------------------
CREATE TABLE PublicDocumentClassView
(
DocumentID numeric(5, 0) not null,
IndexesID varchar(50),
PRIMARY KEY (DocumentID)
);
-----------------------------------------------------
create table PublicDocumentClassBySiteID
(
SiteID numeric(5, 0) not null,
DocumentID varchar(50),
PRIMARY KEY (SiteID)
);
-----------------------------------------------------
create table PublicDocumentClassIndexes
(
DocumentID numeric(5, 0) not null,
IndexesID varchar(50),
PRIMARY KEY (DocumentID)
);

-----------------------------------------------------
CREATE TABLE IMLK_CLIENT_ADDONS(
FeatureID int NOT NULL,
FeatureStatus number(1,0) NOT NULL,
Client varchar(255) NULL,
Description varchar(255) NULL
);
-----------------------------------------------------
CREATE TABLE FileAnnotations
(
FileAnnotationsID NUMBER NOT NULL PRIMARY KEY,
UserID NUMBER (4, 0) NOT NULL,
DocumentClassID NUMBER (5, 0) NOT NULL,
DocumentInstanceID NUMBER (10, 0) NOT NULL,
FileID NUMBER (4, 0) NOT NULL,
Annotations CLOB NULL,
VersionID NUMBER (5, 0) NOT NULL
);
-----------------------------------------------------
Drop SEQUENCE FileAnnotationsID;

CREATE SEQUENCE FileAnnotationsID
START WITH 1;

CREATE OR REPLACE TRIGGER FILEANNOTATIONS_seq
BEFORE INSERT
ON FILEANNOTATIONS
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
SELECT FileAnnotationsID.NEXTVAL INTO :NEW.FileAnnotationsID FROM DUAL;
END FILEANNOTATIONS_seq;
------------------------------------------------
begin
for rec in (SELECT DISTINCT (TABLE_NAME)
FROM all_tab_columns
WHERE TABLE_NAME LIKE 'PAG%'
AND TABLE_NAME NOT IN
(SELECT DISTINCT (TABLE_NAME)
FROM all_tab_columns
WHERE TABLE_NAME LIKE 'PAG%'
AND COLUMN_NAME LIKE '%OCRFLAG%'
AND LOWER (all_tab_columns.OWNER) = 'freezone')
AND LOWER (all_tab_columns.OWNER) = 'freezone'

)
loop
execute immediate 'ALTER TABLE ' || rec.table_name ||' ADD OCRFLAG number(2,0)';
end loop;
end;

ALTER TABLE PAGxxxxx ADD PAGE_DEL numeric(1);
ALTER TABLE PAGxxxxx ADD UPD_PRSN varchar(50);
------------------------------------------------
begin
for rec in (select table_name
from all_tables
where table_name like 'DOC%'
)
loop
execute immediate 'ALTER TABLE ' || rec.table_name ||' ADD Role_ID int';
end loop;
end;