/************************************************************
*
* ElevateDB Reverse-Engineered script for the
* Admin database (client)
*
* 2 local stores needed:
* Replication_Client_In
* Replication_Client_Out
*
* 2 remote stores needed:
* Replication_Server_In
* Replication_Server_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_Client_UploadFiles" ()
BEGIN


   DECLARE DevicesCursor CURSOR FOR DevicesStmt;
   DECLARE CurrentDevicesID INTEGER DEFAULT -1;
   DECLARE CurrentDeviceName VARCHAR DEFAULT '''';
   
   DECLARE DatabasesCursor CURSOR FOR DatabasesStmt;
   DECLARE CurrentDatabaseName VARCHAR DEFAULT '''';

   DECLARE UpdateFilesCursor CURSOR FOR UpdateFilesStmt;
   
   DECLARE ListOfTables CLOB;

   DECLARE CurrentFileName VARCHAR DEFAULT '''';

   DECLARE ClientOutStore VARCHAR DEFAULT ''Replication_Client_Out'';
   DECLARE ServerOutStore VARCHAR DEFAULT ''Replication_Server_Out'';
   DECLARE CurrentStore VARCHAR DEFAULT '''';

   ----------------------------------------------------------------------------------
   -- Collect all update files and store them in store "Replication_Client_Out"
   ----------------------------------------------------------------------------------

   PREPARE DevicesStmt FROM ''SELECT DevicesID, DeviceName FROM Devices'';
   OPEN DevicesCursor;
   FETCH FIRST FROM DevicesCursor (''DevicesID'',''DeviceName'') INTO CurrentDevicesID,CurrentDeviceName;
   
   WHILE NOT EOF(DevicesCursor) DO
    
      PREPARE DatabasesStmt FROM ''SELECT DatabaseName FROM Databases where DevicesID = ?'';
      OPEN DatabasesCursor using CurrentDevicesID;
         FETCH FIRST FROM DatabasesCursor (''DatabaseName'') INTO CurrentDatabaseName;
   
         WHILE NOT EOF(DatabasesCursor) DO
   
            SET ListOfTables = GetPublishedTables(CurrentDatabaseName);
            SET CurrentFileName = CurrentDeviceName + ''_'' + CurrentDatabaseName + ''_'' + REPLACE('':'', ''-'', CAST(CURRENT_TIMESTAMP AS VARCHAR));
            SET CurrentStore = ClientOutStore;

            EXECUTE IMMEDIATE ''SAVE UPDATES FOR DATABASE "'' + CurrentDatabaseName + ''"
                     AS "'' + CurrentFileName + ''"
                     TO STORE "'' + CurrentStore + ''"
                     Tables '' + ListOfTables + '' IF NOT EMPTY'';
   
            FETCH NEXT FROM DatabasesCursor (''DatabaseName'') INTO CurrentDatabaseName;
   
         END WHILE;
   
      FETCH NEXT FROM DevicesCursor (''DevicesID'',''DeviceName'') INTO CurrentDevicesID,CurrentDeviceName;
 
   END WHILE;

   ---------------------------------------------------------------------------------
   -- UPLOAD -----------------------------------------------------------------------
   ---------------------------------------------------------------------------------

   EXECUTE IMMEDIATE ''SET UPDATES STORE TO "'' + ClientOutStore + ''"'';

   PREPARE UpdateFilesStmt FROM ''SELECT * FROM Configuration.Updates ORDER BY CreatedOn'';
   OPEN UpdateFilesCursor;

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


      EXECUTE IMMEDIATE ''COPY FILE "'' + CurrentFileName + ''.EDBUpd" IN STORE
                        "'' + ClientOutStore + ''" TO "'' +
                        CurrentFileName + ''.EDBUpd" IN STORE
                        "'' + ServerOutStore + ''"'';

      ----------------------------------------------------------------------------------
      -- Delete file after sucessful updload
      ----------------------------------------------------------------------------------

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


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

   END WHILE;


END

VERSION 1.00';

EXECUTE IMMEDIATE 'CREATE PROCEDURE "Replication_Client_DownloadFiles" ()
BEGIN

   DECLARE DevicesCursor CURSOR FOR DevicesStmt;
   DECLARE CurrentDevicesID INTEGER DEFAULT -1;
   DECLARE CurrentDeviceName VARCHAR DEFAULT '''';
   
   DECLARE DatabasesCursor CURSOR FOR DatabasesStmt;
   DECLARE CurrentDatabase VARCHAR DEFAULT '''';

   DECLARE UpdateFilesCursor CURSOR FOR UpdateFilesStmt;
   
   DECLARE ListOfTables CLOB;

   DECLARE CurrentFileName VARCHAR DEFAULT '''';

   DECLARE ClientInStore VARCHAR DEFAULT ''Replication_Client_In'';
   DECLARE ClientOutStore VARCHAR DEFAULT ''Replication_Client_Out'';
   DECLARE ServerInStore VARCHAR DEFAULT ''Replication_Server_In'';
   DECLARE ServerOutStore VARCHAR DEFAULT ''Replication_Server_Out'';
   DECLARE CurrentStore VARCHAR DEFAULT '''';

   ------------------------------------------------------------------------------------
   -- Get list of all update files from RemoteStore
   -- and load updates
   ----------------------------------------------------------------------------------

   ----------------------------------------------------------------------------------
   -- Table "Devices" contains only one record - the client computer
   ----------------------------------------------------------------------------------

   Execute Immediate ''Select DevicesID, upper(DeviceName) as DeviceName into ?,? from devices'' using CurrentDevicesID,CurrentDeviceName;
  
   EXECUTE IMMEDIATE ''SET FILES STORE TO "'' + ServerInStore + ''"'';
   PREPARE UpdateFilesStmt FROM ''SELECT * FROM Configuration.FILES where (OCCURS(? IN UPPER(Name)) > 0) ORDER BY CreatedOn'';
   OPEN UpdateFilesCursor using CurrentDeviceName;

   IF ROWCOUNT(UpdateFilesCursor) > 0 THEN
    
      PREPARE DatabasesStmt FROM ''SELECT Upper(DatabaseName) as Databasename FROM Databases where DevicesID = ?'';
      OPEN DatabasesCursor using CurrentDevicesID;
      FETCH FIRST FROM DatabasesCursor (''DatabaseName'') INTO CurrentDatabase;
   
      WHILE NOT EOF(DatabasesCursor) DO
        
         ----------------------------------------------------------------------------------
         -- Loop through all databases to be replicated and read the filenames,
         -- if the filename contains the current databasename, load the update
         ----------------------------------------------------------------------------------
   
         FETCH FIRST FROM UpdateFilesCursor (''Name'') INTO CurrentFileName;
   
         WHILE NOT EOF(UpdateFilesCursor) DO

            ----------------------------------------------------------------------------------
            -- Copy each found file into local In-Store
            ----------------------------------------------------------------------------------
   
            IF (OCCURS(CurrentDatabase IN UPPER(CurrentFileName)) > 0) THEN
   
               EXECUTE IMMEDIATE ''COPY FILE "'' + CurrentFileName + ''" IN STORE
                                 "'' + ServerInStore + ''" TO "'' +
                                 CurrentFileName + ''" IN STORE
                                 "'' + ClientInStore + ''"'';
               ----------------------------------------------------------------------------------
               -- Delete file on remoter server after sucessful download
               ----------------------------------------------------------------------------------
   
               EXECUTE IMMEDIATE ''DELETE FILE "'' + CurrentFileName + ''" FROM STORE
                                 "'' + ServerInStore + ''"'';
   
            END IF;
   
            FETCH NEXT FROM UpdateFilesCursor (''Name'') INTO CurrentFileName;
   
         END WHILE;

         -----------------------------------------------------------------------------------------
         -- load updates
         -----------------------------------------------------------------------------------------
   
         EXECUTE IMMEDIATE ''SET UPDATES STORE TO "'' + ClientInStore + ''"'';
         CLOSE UpdateFilesCursor;
         PREPARE UpdateFilesStmt FROM ''SELECT * FROM Configuration.UPDATES ORDER BY CreatedOn'';
         OPEN UpdateFilesCursor;
   
         FETCH FIRST FROM UpdateFilesCursor (''Name'') INTO CurrentFileName;
   
         WHILE NOT EOF(UpdateFilesCursor) DO
   
            EXECUTE IMMEDIATE ''LOAD UPDATES FOR DATABASE "'' + CurrentDatabase + ''"
                               FROM "'' + CurrentFileName + ''" IN STORE
                               "'' + ClientInStore + ''"'';
   
            ----------------------------------------------------------------------------------
            -- Delete file from local store after loading
            ----------------------------------------------------------------------------------

            EXECUTE IMMEDIATE ''DELETE FILE "'' + CurrentFileName + ''.EDBUpd" FROM STORE
                               "'' + ClientInStore + ''"'';
   
            FETCH NEXT FROM UpdateFilesCursor (''Name'') INTO CurrentFileName;
   
         END WHILE;
   
   
         FETCH NEXT FROM DatabasesCursor (''DatabaseName'') INTO CurrentDatabase;
   
      END WHILE;

     
   END IF;

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
************************************************************/