HSQLDB script for Ametys

CREATE CACHED TABLE "Groups" (
  "Id" int PRIMARY KEY NOT NULL IDENTITY,
  "Label" VARCHAR(200)
);

CREATE CACHED TABLE "Groups_Users" (
  "Group_Id" int NOT NULL,
  "Login" VARCHAR (200) NOT NULL,
  PRIMARY KEY ("Group_Id", "Login")
 );

CREATE CACHED TABLE "Users" (
  "login" varchar(64) PRIMARY KEY NOT NULL,
  "firstname" varchar(64) default NULL,
  "lastname" varchar(64) NOT NULL,
  "email" varchar(64) DEFAULT NULL,
  "password" varchar(128)  NOT NULL
);

CREATE CACHED TABLE "Newsletter_Subscribers" (
        "Email" VARCHAR(255) not null,
        "Site_Name" VARCHAR(48) NOT NULL,
        "Category" VARCHAR(64) NOT NULL,
        "Subscribed_At" date not null,
        "Token" VARCHAR(64) NOT NULL,
        PRIMARY KEY("Email", "Site_Name", "Category")
);

CREATE CACHED TABLE "Rights_Profile" (
        "Id" int PRIMARY KEY NOT NULL IDENTITY,
        "Label" VARCHAR(200),
        "Context" VARCHAR(200)
);

CREATE CACHED TABLE "Rights_ProfileRights" (
        "Profile_Id" int NOT NULL,
        "Right_Id" VARCHAR(200) NOT NULL,
        PRIMARY KEY("Profile_Id", "Right_Id")
);

CREATE CACHED TABLE "Rights_GroupRights" (
        "Profile_Id" int NOT NULL,
        "Group_Id" VARCHAR(200) NOT NULL,
        "Context" VARCHAR(200) NOT NULL,
        PRIMARY KEY("Profile_Id", "Group_Id", "Context")
);

CREATE CACHED TABLE "Rights_UserRights" (
        "Profile_Id" int NOT NULL,
        "Login" VARCHAR(200) NOT NULL,
        "Context" VARCHAR(200) NOT NULL,
        PRIMARY KEY("Profile_Id", "Login", "Context")
);

CREATE CACHED TABLE "UserPreferences" (
  "login" varchar(32) NOT NULL,
  "context" varchar(128) NOT NULL,
  "data" BLOB,
  PRIMARY KEY("login", "context")
);

CREATE CACHED TABLE "Cache_RA_HTTPServer" (
  "Unique_Id" VARCHAR(255) NOT NULL,
  "Site" VARCHAR(255) NOT NULL,
  "Request_Date" TIMESTAMP NOT NULL,
  "Method" VARCHAR(255) NOT NULL,
  "Path_Hash" VARCHAR(255) NOT NULL,
  "Path" VARCHAR(4000) NOT NULL,
  "Query_String" VARCHAR(4000) NOT NULL,
  "Ori_Status_Code" CHAR(3) NOT NULL,
  "Ret_Status_Code" CHAR(3) NOT NULL,
  "Cache_Hit" SMALLINT NOT NULL,
  "Created_At" TIMESTAMP NOT NULL,
  "Processed" SMALLINT DEFAULT 0 NOT NULL,
  PRIMARY KEY ("Unique_Id")
);

CREATE CACHED TABLE "Cache_RA_Front" (
  "Unique_Id" VARCHAR(255) NOT NULL,
  "Internal_Uuid" VARCHAR(255) NOT NULL,
  "Site" VARCHAR(255) NOT NULL,
  "Ametys_Path_Hash" VARCHAR(255) NOT NULL,
  "Ametys_Path" VARCHAR(4000) NOT NULL,
  "Cacheable" SMALLINT NOT NULL,
  "Cache_Hit_1" SMALLINT NOT NULL,
  "Cache_Hit_2" SMALLINT NOT NULL,
  "Created_At" TIMESTAMP NOT NULL,
  "Processed" SMALLINT DEFAULT 0 NOT NULL
);

CREATE CACHED TABLE "Cache_RA_Back" (
  "Internal_Uuid" VARCHAR(255) NOT NULL,
  "Page_Id" VARCHAR(255) NOT NULL,
  "Page_Path_Hash" VARCHAR(255) NOT NULL,
  "Page_Path" VARCHAR(4000) NOT NULL,
  "Rendering_Context" VARCHAR(255) NOT NULL,
  "Workspace_JCR" VARCHAR(255) NOT NULL,
  "Cacheable" SMALLINT NOT NULL,
  "Created_At" TIMESTAMP NOT NULL,
  "Processed" SMALLINT DEFAULT 0 NOT NULL
);

CREATE CACHED TABLE "Cache_RA_Back_Page_Element" (
  "Internal_Uuid" VARCHAR(255) NOT NULL,
  "Page_Element_Id" VARCHAR(255) NOT NULL,
  "Page_Element_Type" VARCHAR(255) NOT NULL,
  "Page_Id" VARCHAR(255) NOT NULL,
  "Rendering_Context" VARCHAR(255) NOT NULL,
  "Workspace_JCR" VARCHAR(255) NOT NULL,
  "Cacheable" SMALLINT NOT NULL,
  "Cache_Hit" SMALLINT NOT NULL,
  "Created_At" TIMESTAMP NOT NULL,
  "Processed" SMALLINT DEFAULT 0 NOT NULL
);

CREATE CACHED TABLE "Cache_Stats_Front" (
  "Server_Site" VARCHAR(255) DEFAULT '-' NOT NULL,
  "Server_Path_Hash" VARCHAR(255) DEFAULT '-' NOT NULL,
  "Server_Path" VARCHAR(4000),
  "Server_Hits" INT DEFAULT 0 NOT NULL,
  "Server_Cache_Hits" INT DEFAULT 0 NOT NULL,
  "Front_Site" VARCHAR(255) DEFAULT '-' NOT NULL,
  "Front_Path_Hash" VARCHAR(255) DEFAULT '-' NOT NULL,
  "Front_Path" VARCHAR(4000),
  "Front_Cacheable" SMALLINT DEFAULT 0 NOT NULL,
  "Front_Hits" INT DEFAULT 0 NOT NULL,
  "Front_Cache_Hits_1" INT DEFAULT 0 NOT NULL,
  "Front_Cache_Hits_2" INT DEFAULT 0 NOT NULL,
  "Created_At" TIMESTAMP NOT NULL,
  "Updated_At" TIMESTAMP NOT NULL,
  UNIQUE ("Server_Site", "Server_Path_Hash", "Front_Site", "Front_Path_Hash")
);

CREATE CACHED TABLE "Cache_Stats_Back" (
  "Page_Id" VARCHAR(255) NOT NULL,
  "Page_Path_Hash" VARCHAR(255) NOT NULL,
  "Page_Path" VARCHAR(4000) NOT NULL,
  "Rendering_Context" VARCHAR(255) NOT NULL,
  "Workspace_JCR" VARCHAR(255) NOT NULL,
  "Cacheable" SMALLINT NOT NULL,
  "Hits" INT NOT NULL,
  "Created_At" TIMESTAMP NOT NULL,
  "Updated_At" TIMESTAMP NOT NULL,
  PRIMARY KEY ("Page_Id", "Rendering_Context", "Workspace_JCR")
);

CREATE CACHED TABLE "Cache_Stats_Back_Page_Element" (
  "Page_Element_Id" VARCHAR(255) NOT NULL,
  "Page_Id" VARCHAR(255) NOT NULL,
  "Rendering_Context" VARCHAR(255) NOT NULL,
  "Workspace_JCR" VARCHAR(255) NOT NULL,
  "Cacheable" SMALLINT NOT NULL,
  "Hits" INT NOT NULL,
  "Cache_Hits" INT NOT NULL,
  "Created_At" TIMESTAMP NOT NULL,
  "Updated_At" TIMESTAMP NOT NULL,
  PRIMARY KEY ("Page_Element_Id", "Page_Id", "Rendering_Context", "Workspace_JCR")
);

CREATE CACHED TABLE "FOUsers" (
  "login" varchar(255) PRIMARY KEY NOT NULL,
  "firstname" varchar(64) default NULL,
  "lastname" varchar(64) NOT NULL,
  "email" varchar(64) DEFAULT NULL,
  "password" varchar(128)  NOT NULL
);

CREATE CACHED TABLE "FOUserPreferences" (
  "login" varchar(255) NOT NULL,
  "context" varchar(128) NOT NULL,
  "data" BLOB,
  PRIMARY KEY("login", "context")
);

CREATE CACHED TABLE "Users_Temp" (
  "site" varchar(255) NOT NULL,
  "email" varchar(255) NOT NULL,
  "firstname" varchar(64) NOT NULL,
  "lastname" varchar(64) NOT NULL,
  "subscription_date" TIMESTAMP NOT NULL,
  "token" varchar(128) NOT NULL,
  PRIMARY KEY ("site", "email"),
  UNIQUE ("token")
);

CREATE CACHED TABLE "Users_PasswordChange" (
  "site" varchar(255) NOT NULL,
  "login" varchar(255) NOT NULL,
  "request_date" TIMESTAMP NOT NULL,
  "token" varchar(128) NOT NULL,
  PRIMARY KEY("site", "login")
);