if not(exists(select * from systable where UCase(table_name)=UCase('Counters')))
then
  create table Counters(
    "id" integer not null,
    "counter" integer not null default 0,
    "name" varchar(20) not null,
    primary key("id")
  );
<<<<<<< .mine
  insert into "Counters"("id", "name") values (1, 'Main Counter');
=======
  insert into "Counters"("id", "name") values ('1', 'Main Counter');
>>>>>>> .r37
end if;

GO

if not(exists(select * from systable where UCase(table_name)=UCase('Handles')))
then
  create table "Handles"
  (
    "id" integer not null default autoincrement,
    "name" varchar(64) not null,
    "driver" varchar(20) not null,
    "options" text,
    primary key ("id")
  );
  insert into "Handles"("name", "driver") values ('_', '_');
  insert into "Handles"("name", "driver") values ('FTP Client', 'FTP');
  insert into "Handles"("name", "driver") values ('Sybase Connection', 'Sybase');
  insert into "Handles"("name", "driver") values ('NaVision Connection', 'NaVision');
end if;

GO

if not(exists(select * from systable where UCase(table_name)=UCase('Objects')))
then
  create table "Objects"
  (
    "id" integer not null default autoincrement,
    "name" varchar(64) not null,
    "description" varchar(64) not null,
    "handle_id" integer not null default 1,
    "address" varchar(255),
    "username" varchar(64),
    "password" varchar(64),
    "options" text,
    primary key("id")
  );

  alter table Objects add not null foreign key "fk_Handles_id" ("handle_id")
  references "Handles" ("id")
  on update cascade
  on delete set default;
end if;

GO

if not(exists(select * from systable where UCase(table_name)=UCase('Tasks')))
then
  create table Tasks(
    "id" integer not null default autoincrement,
    "source_id" integer not null,
    "dest_id" integer not null,
    "active" integer default 1,
    "status" integer not null default 0,
    "units_id" integer default 1,
    "units_value" integer default 1,
    "interval" numeric(16,0),
    "next_run" timestamp,
    "source_conn_status" integer default 3,
    "dest_conn_status" integer default 3,
    "source_status" integer default 3,
    "dest_status" integer default 3,
    primary key("id")
  );

  ALTER TABLE Tasks
  ADD NOT NULL FOREIGN KEY "fk_Source_id" ("source_id")
  REFERENCES "Objects" ("id")
  ON UPDATE CASCADE
  ON DELETE CASCADE;

  ALTER TABLE Tasks
  ADD NOT NULL FOREIGN KEY "fk_Dest_id" ("dest_id")
  REFERENCES "Objects" ("id")
  ON UPDATE CASCADE
  ON DELETE CASCADE
end if;

GO

if not(exists(select * from systable where UCase(table_name)=UCase('Registers')))
then
  create table Registers(
    "Name" varchar(120) not null,
    "Description" long varchar,
    "IsUnique" tinyint not null,
    "N" integer not null,
    primary key("N")
  );
  insert into "Registers" ("Name", "Description", "IsUnique", "N") values('Application.Local.Name', 'Локално име на приложението.', 1, 1);
  insert into "Registers" ("Name", "Description", "IsUnique", "N") values('Database.Version', 'Current version of the database.', 1, 2);
end if;

GO

if not(exists(select * from systable where UCase(table_name)=UCase('Registry')))
then
  create table Registry(
    "Register" integer not null,
    "Owner" varchar(60) not null default '_',
    "Value" varchar(200) not null,
    primary key("Register", "Owner")
  );

  ALTER TABLE Registry
  ADD NOT NULL FOREIGN KEY "REG_Registers_N" ("Register")
  REFERENCES "Registers" ("N")
  ON UPDATE CASCADE
  ON DELETE CASCADE;
  
  insert into "Registry" ("Register", "Owner", "Value") values(1, '_', 'New Client');
  insert into "Registry" ("Register", "Owner", "Value") values(2, '_', '0');
end if;

GO

if not(exists(select * from systable where UCase(table_name)=UCase('SvoTipove')))
then
  create table SvoTipove(
    "DanTip" char(1) not null,
    "KratkoIme" char(30) not null,
    "Opisanie" long varchar,
    primary key("DanTip")
    );
  insert into "SvoTipove" ("DanTip", "KratkoIme", "Opisanie") values('D', 'Дата', '');
  insert into "SvoTipove" ("DanTip", "KratkoIme", "Opisanie") values('H', 'Час', '');
  insert into "SvoTipove" ("DanTip", "KratkoIme", "Opisanie") values('K', 'Дата и Час', '');
  insert into "SvoTipove" ("DanTip", "KratkoIme", "Opisanie") values('T', 'Текст', 'Произволен текст (до 60 знака).');
  insert into "SvoTipove" ("DanTip", "KratkoIme", "Opisanie") values('V', 'Число', '');
end if;

GO

if not(exists(select * from systable where UCase(table_name)=UCase('SvoObjects')))
then
  create table SvoObjects(
    "N" integer not null,
    "Ime" varchar(40) not null,
    "Opisanie" long varchar,
    "DanTip" char(1),
    "Nachalo" long varchar,
    "Syzdatel" integer default 1,
    "Zadyljitelno" tinyint default 0,
    "PickList" long varchar,
    "LookUpTable" varchar(255),
    "LookUpKeyField" varchar(50),
    "LookUpResultField" varchar(50),
    "Active" integer default 1,
    "GetValue" varchar(255),
    "SetValue" varchar(255),
    primary key("N")
    );

  ALTER TABLE SvoObjects
  ADD FOREIGN KEY "SVO_SvoTipove_Tip" ("DanTip")
  REFERENCES "SvoTipove" ("DanTip")
  ON UPDATE CASCADE
end if;

GO

if not(exists(select * from systable where UCase(table_name)=UCase('SvoObjectsStn')))
then
  create table SvoObjectsStn(
    "Glava" integer not null,
    "Svoistvo" integer not null,
    "Stoinost" varchar(100),
    primary key("Glava", "Svoistvo")
    );

  ALTER TABLE SvoObjectsStn
  ADD NOT NULL FOREIGN KEY "SVOS_SvoObjects_N" ("Svoistvo")
  REFERENCES "SvoObjects" ("N")
  ON UPDATE CASCADE
  ON DELETE CASCADE;

  ALTER TABLE SvoObjectsStn
  ADD NOT NULL FOREIGN KEY "SVOS_Objects_id" ("Glava")
  REFERENCES "Objects" ("id")
  ON UPDATE CASCADE
  ON DELETE CASCADE;
end if;

GO

if not(exists(select * from systable where UCase(table_name)=UCase('RegFunctions')))
then
  create table RegFunctions(
    "id" integer not null default autoincrement,
    "name" varchar(64) not null,
    primary key("id")
  );
end if;

GO

if not(exists(select * from systable where UCase(table_name)=UCase('Statuses')))
then
  create table Statuses(
    "name" varchar(10) not null,
    "id" integer not null,
    primary key("id")
  );
  insert into "Statuses"("id", "name") values ('1', 'OK');
  insert into "Statuses"("id", "name") values ('2', 'Error');
  insert into "Statuses"("id", "name") values ('3', 'N/A');
  insert into "Statuses"("id", "name") values ('4', 'Idle');
  insert into "Statuses"("id", "name") values ('5', 'Running');
  
end if;

GO

if not(exists(select * from systable where UCase(table_name)=UCase('ExportType')))
then
  create table ExportType(
    "HandleID" integer not null,
    "id" integer not null,
    "name" varchar(30) not null,
    "command" long varchar not null,
    "Priority" integer not null,
    primary key("id")
  );

  ALTER TABLE ExportType
  ADD NOT NULL FOREIGN KEY "Handles" ("HandleID")
  REFERENCES "Handles" ("id")
  ON UPDATE CASCADE
  ON DELETE CASCADE
end if;

GO

if not(exists(select * from systable where UCase(table_name)=UCase('ExportLastDates')))
then
  create table ExportLastDates(
    "ExpActive" integer not null default 1,
    "RowID" varchar(24) default 0,
    "Counter" integer default 0,
    "ObjectID" integer not null,
    "ExportType_id" integer not null,
    "ExportTypeTask" integer not null,
    "LastDate" timestamp
  );

  ALTER TABLE ExportLastDates
  ADD NOT NULL FOREIGN KEY "Objects" ("ObjectID")
  REFERENCES "Objects" ("id")
  ON DELETE CASCADE;

  ALTER TABLE ExportLastDates
  ADD NOT NULL FOREIGN KEY "ExportType" ("ExportType_id")
  REFERENCES "ExportType" ("id")
  ON UPDATE CASCADE
  ON DELETE CASCADE
end if;

GO