/************************************************************
*
* ElevateDB Reverse-Engineered script for the
* Admin database located on the remote server
*
* 3 local Stores needed:
* Replication_Updates
* Replication_In
* Replication_out
*
************************************************************/

SCRIPT
BEGIN

/************************************************************
* Tables
************************************************************/

EXECUTE IMMEDIATE 'CREATE TABLE "Devices"
(
"DevicesID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"DeviceName" VARCHAR(20) COLLATE "DEU_CI" NOT NULL
)
VERSION 1.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

EXECUTE IMMEDIATE 'CREATE TABLE "Databases"
(
"DatabasesID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"DevicesID" INTEGER NOT NULL,
"DatabaseName" VARCHAR(50) COLLATE "DEU_CI" NOT NULL
)
VERSION 1.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';


/************************************************************
* Table Rows
************************************************************/


/************************************************************
* Views
************************************************************/

EXECUTE IMMEDIATE 'CREATE VIEW "DeviceList" AS
select DeviceName, Databasename from
Devices d left outer join Databases b
on d.DevicesID = b.DevicesID order by Devicename

VERSION 1.00';


/************************************************************
* Functions
************************************************************/

EXECUTE IMMEDIATE 'CREATE FUNCTION "GetPublishedTables" (IN "FDatabaseName" VARCHAR(50) COLLATE DEU_CI)
RETURNS CLOB COLLATE DEU_CI
BEGIN

   DECLARE FList CLOB;

   Execute Immediate ''select list(name) into ? from '' + FDatabaseName + ''.information.tables where published'' using FList;

   RETURN FList;

END


VERSION 1.00';


/************************************************************
* Procedures
************************************************************/

EXECUTE IMMEDIATE 'CREATE PROCEDURE "Replication_Server_ExportFiles" ()
BEGIN

   DECLARE ServerOutStore VARCHAR DEFAULT ''Replication_Out'';
   DECLARE ServerUpdateStore VARCHAR DEFAULT ''Replication_Updates'';

   DECLARE UpdateCursor CURSOR FOR UpdateStmt;
   DECLARE UpdateFile VARCHAR DEFAULT '''';
   DECLARE CurrentFileName VARCHAR DEFAULT '''';
   DECLARE FListOfTables CLOB;

   DECLARE DatabasesCursor CURSOR FOR DatabasesStmt;
   DECLARE CurrentDatabase VARCHAR DEFAULT ''''; 

   DECLARE DevicesCursor CURSOR FOR DevicesStmt; 
   DECLARE CurrentDevicesID INTEGER DEFAULT -1;
   DECLARE CurrentDevice VARCHAR DEFAULT '''';
   
   -------------------------------------------------------------------------------------------------------
   -- Get all databases to replicate
   -------------------------------------------------------------------------------------------------------
   
   PREPARE DatabasesStmt FROM ''SELECT distinct Databasename from Databases'';
   OPEN DatabasesCursor;
    
   -------------------------------------------------------------------------------------------------------
   -- Loop through all databases and store update files of each database in store "Replication_Updates"
   -------------------------------------------------------------------------------------------------------

   FETCH FIRST FROM DatabasesCursor (''Databasename'') INTO CurrentDatabase;
   WHILE NOT EOF(DatabasesCursor) DO     

      -------------------------------------------------------------------------------------------------------
      -- List of all published tables of the current database is returned by a function
      -------------------------------------------------------------------------------------------------------

      SET FListOfTables = GetPublishedTables(CurrentDatabase);    
      SET UpdateFile =  CurrentDatabase + ''_'' + REPLACE('':'', ''-'', CAST(CURRENT_TIMESTAMP AS VARCHAR));
      
      -------------------------------------------------------------------------------------------------------
      -- store update file
      -------------------------------------------------------------------------------------------------------
   
      EXECUTE IMMEDIATE ''SAVE UPDATES FOR DATABASE "'' + CurrentDatabase + ''"
                        AS "'' + UpdateFile + ''"
                        TO STORE "'' + ServerUpdateStore + ''"
                        Tables '' + FListOfTables + '' IF NOT EMPTY'';
   
      FETCH NEXT FROM DatabasesCursor (''Databasename'') INTO CurrentDatabase;

   END WHILE;

   -------------------------------------------------------------------------------------------------------
   -- Get all devices (clients) 
   -------------------------------------------------------------------------------------------------------

   PREPARE DevicesStmt FROM ''SELECT * from Devices'';
   OPEN DevicesCursor;
   
   -------------------------------------------------------------------------------------------------------
   -- Loop through all devices, rename update files in store "Replication_Updates"
   -- and copy them in store "Replication_Out" 
   -------------------------------------------------------------------------------------------------------

   FETCH FIRST FROM DevicesCursor (''DevicesID'',''DeviceName'') INTO CurrentDevicesID,CurrentDevice;
   WHILE NOT EOF(DevicesCursor) DO
       
      -------------------------------------------------------------------------------------------------------
      -- Get all databases to replicate for the current device
      -------------------------------------------------------------------------------------------------------   
      CLOSE DatabasesCursor;
      PREPARE DatabasesStmt FROM ''SELECT Databasename from Databases where DevicesID = ?'';
      OPEN DatabasesCursor using CurrentDevicesID;
      
      -------------------------------------------------------------------------------------------------------
      -- Loop through all databases and store updatefile in store "Replication_Updates"
      -------------------------------------------------------------------------------------------------------

      FETCH FIRST FROM DatabasesCursor (''Databasename'') INTO CurrentDatabase;
      WHILE NOT EOF(DatabasesCursor) DO
         
         --------------------------------------------------------------------------------------------------- 
         -- Add devicename to the filename and copy renamed file to store "Replication_Out"
         ---------------------------------------------------------------------------------------------------

         EXECUTE IMMEDIATE ''SET UPDATES STORE TO "'' + ServerUpdateStore + ''"'';
         PREPARE UpdateStmt FROM ''SELECT * FROM Configuration.Updates where (OCCURS(? IN UPPER(Name)) > 0)'';
         OPEN UpdateCursor using UPPER(CurrentDatabase);
         
         FETCH FIRST FROM UpdateCursor (''Name'') INTO CurrentFileName;
         WHILE NOT EOF(UpdateCursor) DO
            
            SET UpdateFile = CurrentDevice + ''_'' + CurrentFileName;
             
            -------------------------------------------------------------------------------------------------
            -- Copy file
            -------------------------------------------------------------------------------------------------
            EXECUTE IMMEDIATE ''COPY FILE "'' + CurrentFileName + ''.EDBUpd" IN STORE
                              "'' + ServerUpdateStore + ''" TO "'' +
                              UpdateFile + + ''.EDBUpd" IN STORE
                              "'' + ServerOutStore + ''"'';

            FETCH NEXT FROM UpdateCursor (''Name'') INTO CurrentFileName;
         
         END WHILE;
         ---------------------------------------------------------------------------------------------------
         -- File was renamed and copied
         ---------------------------------------------------------------------------------------------------

         FETCH NEXT FROM DatabasesCursor (''Databasename'') INTO CurrentDatabase;
   
      END WHILE;   
      FETCH NEXT FROM DevicesCursor (''DevicesID'',''DeviceName'') INTO CurrentDevicesID,CurrentDevice;

   END WHILE;

   ---------------------------------------------------------------------------------------------------
   -- Done - delete all Files from store "Replication_Updates"
   ---------------------------------------------------------------------------------------------------

   CALL EmptyStore(ServerUpdateStore);

END

VERSION 1.00';

EXECUTE IMMEDIATE 'CREATE PROCEDURE "Replication_Server_ImportFiles" ()
BEGIN

   DECLARE CurrentDatabase VARCHAR DEFAULT '''';
   DECLARE UpdateStore VARCHAR DEFAULT ''Replication_In'';
 
   DECLARE UpdateCursor CURSOR FOR UpdateStmt;
   DECLARE UpdateFile VARCHAR DEFAULT '''';  

   DECLARE DatabasesCursor CURSOR FOR DatabasesStmt;


   PREPARE DatabasesStmt FROM ''SELECT distinct UPPER(DatabaseName) as Databasename FROM Databases'';
   OPEN DatabasesCursor;
   FETCH FIRST FROM DatabasesCursor (''DatabaseName'') INTO CurrentDatabase;

   WHILE NOT EOF(DatabasesCursor) DO

      EXECUTE IMMEDIATE ''SET UPDATES STORE TO "'' + UpdateStore + ''"'';
      PREPARE UpdateStmt FROM ''SELECT * FROM Configuration.UPDATES where (OCCURS(? IN UPPER(Name)) > 0) ORDER BY CreatedOn'';
      OPEN UpdateCursor using CurrentDatabase;
   
      IF ROWCOUNT(UpdateCursor) > 0 THEN 

         FETCH FIRST FROM UpdateCursor (''Name'') INTO UpdateFile;
         WHILE NOT EOF(UpdateCursor) DO
   
            EXECUTE IMMEDIATE ''LOAD UPDATES FOR DATABASE "'' + CurrentDatabase + ''"
                                  FROM "'' + UpdateFile + ''" IN STORE
                                  "'' + UpdateStore + ''" '';
   
            EXECUTE IMMEDIATE ''DELETE FILE "'' + UpdateFile + ''.EDBUpd" FROM STORE
                               "'' + UpdateStore + ''"''; 

            FETCH NEXT FROM UpdateCursor (''Name'') INTO UpdateFile;
         END WHILE;

      END IF;

      FETCH NEXT FROM DatabasesCursor (''DatabaseName'') INTO CurrentDatabase;

   END WHILE;


END

VERSION 1.00';

EXECUTE IMMEDIATE 'CREATE PROCEDURE "EmptyStore" (IN "FStoreName" VARCHAR(50) COLLATE DEU_CI)
BEGIN 

   DECLARE FileCursor CURSOR FOR FileStmt; 
   DECLARE CurrentFileName VARCHAR DEFAULT '''';  

   EXECUTE IMMEDIATE ''SET UPDATES STORE TO "'' + FStoreName + ''"'';
   PREPARE FileStmt FROM ''SELECT * FROM Configuration.Updates'';
   OPEN FileCursor;

   FETCH FIRST FROM FileCursor (''Name'') INTO CurrentFileName;
   WHILE NOT EOF(FileCursor) DO

      EXECUTE IMMEDIATE ''DELETE FILE "'' + CurrentFileName + ''.EDBUpd" FROM STORE
                  "'' + FStoreName + ''"'';

      FETCH NEXT FROM FileCursor (''Name'') INTO CurrentFileName;

   END WHILE;

END

VERSION 1.00';


/************************************************************
* Table triggers, indexes, and constraints
************************************************************/

-- Creating non-foreign key constraints for Devices table

EXECUTE IMMEDIATE 'ALTER TABLE "Devices"
ADD CONSTRAINT "Primary Key" PRIMARY KEY ("DevicesID")';

-- Creating non-foreign key constraints for Databases table

EXECUTE IMMEDIATE 'ALTER TABLE "Databases"
ADD CONSTRAINT "Primary Key" PRIMARY KEY ("DatabasesID")';

-- Creating foreign key constraints for Databases table

EXECUTE IMMEDIATE 'ALTER TABLE "Databases"
ADD CONSTRAINT "DevicesID" FOREIGN KEY ("DevicesID")
REFERENCES "Devices" ("DevicesID")
ON UPDATE NO ACTION
ON DELETE NO ACTION';

END

/************************************************************
* End of generated SQL
************************************************************/