unit VisSeqcDBCheck;

interface

uses 
  dbutils,
  pFIBDatabase, 
  pFIBQuery, 
  Classes, 
  dialogs, 
  sysutils;

  procedure CheckDatabase(AQuery: TpFIBQuery);



implementation

uses FIBQuery;

var
  Passed: boolean = false;


procedure CheckDatabase(AQuery: TpFIBQuery);
begin
  dbutils.qry := AQuery;

  if Passed then exit;
  Passed := true;



  // Проверка наличия таблицы ROOTS
  if not table_exist('ROOTS') then begin
    do_sql('CREATE GENERATOR GEN_ROOTS_ID');

    do_sql('CREATE TABLE ROOTS (     '+
           '    ID INTEGER NOT NULL, '+
           '    CODE INTEGER,        '+
           '    NAME VARCHAR(256),   '+
           '    SUBROOT INTEGER DEFAULT 0,'+
           '    LAST_DEVICE_ID INTEGER,   '+
           '    DESCR VARCHAR(1024)) ');

    do_sql('alter table ROOTS add constraint PK_ROOTS primary key (ID)');

    do_sql('CREATE TRIGGER ROOTS_BI FOR ROOTS                   '#13#10+
           'ACTIVE BEFORE INSERT POSITION 0                     '#13#10+
           'AS                                                  '#13#10+
           'DECLARE VARIABLE m INTEGER;                         '#13#10+
           'DECLARE VARIABLE i INTEGER;                         '#13#10+
           'BEGIN                                               '#13#10+
           '  IF (NEW.ID IS NULL) THEN                          '#13#10+
           '    NEW.ID = GEN_ID(GEN_ROOTS_ID,1);                '#13#10+
           '  if (new.code is null) then                        '#13#10+
           '  begin                                             '#13#10+
           '    i=1;                                            '#13#10+
           '    for select code from roots order by code into :m'#13#10+
           '    do                                              '#13#10+
           '    begin                                           '#13#10+
           '      if (i=m) then                                 '#13#10+
           '        i=i+1;                                      '#13#10+
           '      else                                          '#13#10+
           '        break;                                      '#13#10+
           '    end                                             '#13#10+
           '    new.code = i;                                   '#13#10+
           '  end                                               '#13#10+
           'END                                                 ');
  end;


  // Проверка наличия таблицы ROOTS
  if not table_exist('ROOTDEVICES') then begin
    do_sql('CREATE GENERATOR GEN_ROOTDEVICES_ID');

    do_sql('CREATE TABLE ROOTDEVICES (      '+
           '    ID INTEGER NOT NULL,        '+
           '    ROOT_ID INTEGER NOT NULL,   '+
           '    DEVICE_ID INTEGER NOT NULL, '+
           '    TIMESTART INTEGER,          '+
           '    TIMESTOP INTEGER,           '+
           '    ORDERNUM INTEGER)           ');

    do_sql('alter table ROOTDEVICES add constraint PK_ROOTDEVICES primary key (ID)');

    do_sql('CREATE TRIGGER ROOTDEVICES_BI FOR ROOTDEVICES                                '#13#10+
           'ACTIVE BEFORE INSERT POSITION 0                                              '#13#10+
           'AS                                                                           '#13#10+
           'DECLARE VARIABLE m INTEGER;                                                  '#13#10+
           'BEGIN                                                                        '#13#10+
           '  IF (NEW.ID IS NULL) THEN                                                   '#13#10+
           '    NEW.ID = GEN_ID(GEN_ROOTDEVICES_ID,1);                                   '#13#10+
           '  if (new.ordernum is null) then                                             '#13#10+
           '    select max(ordernum) from rootdevices where root_id=new.root_id into :m; '#13#10+
           '  if (m is null) then m = 0;                                                 '#13#10+
           '  new.ordernum = m+1;                                                        '#13#10+
           'END                                                                          ');



    do_sql('CREATE PROCEDURE MAKE_ROOTS_DESCR (                        '#13#10+
           '    root_id integer)                                       '#13#10+
           'as                                                         '#13#10+
           'declare variable s varchar(1024);                          '#13#10+
           'declare variable ss varchar(128);                          '#13#10+
           'begin                                                      '#13#10+
           '  s = '''';                                                '#13#10+
           '  for select d.tagname from devices d, rootdevices rd      '#13#10+
           '      where (d.id=rd.device_id) and (rd.root_id=:root_id)  '#13#10+
           '      order by rd.ordernum into :ss                        '#13#10+
           '  do begin                                                 '#13#10+
           '    if (s='''') then                                       '#13#10+
           '      s = ss;                                              '#13#10+
           '    else                                                   '#13#10+
           '      s = s || ''-'' || ss;                                '#13#10+
           '  end                                                      '#13#10+
           '  update roots set descr=:s where id=:root_id;             '#13#10+
           'END ');

    do_sql('CREATE PROCEDURE MAKE_ALL_ROOTS_DESCR       '#13#10+
           'as                                          '#13#10+
           'declare variable id integer;                '#13#10+
           'begin                                       '#13#10+
           '  for select id from roots into :id do      '#13#10+
           '    execute procedure make_roots_descr(id); '#13#10+
           '                                            '#13#10+
           'end; ');

           
    do_sql('CREATE TRIGGER ROOTDEVICES_BD2 FOR ROOTDEVICES              '#13#10+
           'ACTIVE AFTER DELETE POSITION 2                              '#13#10+
           'AS                                                          '#13#10+
           'DECLARE VARIABLE id INTEGER;                                '#13#10+
           'DECLARE VARIABLE ordnum INTEGER;                            '#13#10+
           'begin                                                       '#13#10+
           '  select first 1 device_id, ordernum                        '#13#10+
           '        from rootdevices                                    '#13#10+
           '        where root_id=old.root_id                           '#13#10+
           '        order by ordernum                                   '#13#10+
           '        into :id, :ordnum;                                  '#13#10+
           '                                                            '#13#10+
           '  update roots set last_device_id=:id where id=old.root_id; '#13#10+
           '  execute procedure MAKE_ROOTS_DESCR(old.root_id);          '#13#10+
           'end                                                         ');




    do_sql('CREATE TRIGGER ROOTDEVICES_AIU1 FOR ROOTDEVICES             '#13#10+
           'ACTIVE AFTER INSERT OR UPDATE POSITION 1                    '#13#10+
           'AS                                                          '#13#10+
           'DECLARE VARIABLE id INTEGER;                                '#13#10+
           'DECLARE VARIABLE ordnum INTEGER;                            '#13#10+
           'begin                                                       '#13#10+
           '  select first 1 device_id, ordernum                        '#13#10+
           '        from rootdevices                                    '#13#10+
           '        where root_id=new.root_id                           '#13#10+
           '        order by ordernum                                   '#13#10+
           '        into :id, :ordnum;                                  '#13#10+
           '                                                            '#13#10+
           '  update roots set last_device_id=:id where id=new.root_id; '#13#10+
           '  execute procedure MAKE_ROOTS_DESCR(new.root_id);          '#13#10+
           'end                                                         ');




    do_sql('alter table ROOTDEVICES         '+
           'add constraint FK_ROOTDEVICES_1 '+
           'foreign key (ROOT_ID)           '+
           'references ROOTS(ID)            '+
           'on delete CASCADE               '+
           'on update CASCADE               ');

    do_sql('alter table ROOTDEVICES         '+
           'add constraint FK_ROOTDEVICES_2 '+
           'foreign key (DEVICE_ID)         '+
           'references DEVICES(ID)          '+
           'on delete CASCADE               '+
           'on update CASCADE               ');

  end;


  // Проверка наличия таблицы SUBROOTS
  if not table_exist('SUBROOTS') then begin
    do_sql('CREATE GENERATOR GEN_SUBROOTS_ID');

    do_sql('CREATE TABLE SUBROOTS (          '+ 
           '    ID          INTEGER NOT NULL,'+ 
           '    ROOT_ID     INTEGER NOT NULL,'+ 
           '    SUBROOT_ID  INTEGER NOT NULL)');

    do_sql('ALTER TABLE SUBROOTS ADD CONSTRAINT FK_SUBROOTS_1 FOREIGN KEY (ROOT_ID) REFERENCES ROOTS (ID) ON DELETE CASCADE ON UPDATE CASCADE');
    do_sql('ALTER TABLE SUBROOTS ADD CONSTRAINT FK_SUBROOTS_2 FOREIGN KEY (SUBROOT_ID) REFERENCES ROOTS (ID) ON DELETE CASCADE ON UPDATE CASCADE');

    do_sql('CREATE TRIGGER SUBROOTS_BI FOR SUBROOTS'#13#10+
           'ACTIVE BEFORE INSERT POSITION 0        '#13#10+
           'AS                                     '#13#10+
           'BEGIN                                  '#13#10+
           '  IF (NEW.ID IS NULL) THEN             '#13#10+
           '    NEW.ID = GEN_ID(GEN_SUBROOTS_ID,1); '#13#10+
           'END                                    ');
  
  end;


  
  if not field_exist('ROOTS', 'SUBROOT') then
    do_sql('ALTER TABLE ROOTS ADD SUBROOT INTEGER DEFAULT 0');

  if not field_exist('ROOTS', 'ROOTBEGIN') then
    do_sql('ALTER TABLE ROOTS ADD ROOTBEGIN INTEGER DEFAULT 0');

  if not field_exist('ROOTS', 'ROOTEND') then
    do_sql('ALTER TABLE ROOTS ADD ROOTEND INTEGER DEFAULT 0');

  if not field_exist('ROOTDEVICES', 'FL_ARMO') then
    do_sql('ALTER TABLE ROOTDEVICES ADD FL_ARMO SMALLINT DEFAULT 0');

  if not field_exist('ROOTDEVICES', 'FL_TRANSPARENT') then
    do_sql('ALTER TABLE ROOTDEVICES ADD FL_TRANSPARENT SMALLINT DEFAULT 0');

  if not field_exist('ROOTDEVICES', 'FL_CTRLPROD') then
    do_sql('ALTER TABLE ROOTDEVICES ADD FL_CTRLPROD SMALLINT DEFAULT 0');

  if not field_exist('ROOTDEVICES', 'FL_PROD') then
    do_sql('ALTER TABLE ROOTDEVICES ADD FL_PROD SMALLINT DEFAULT 0');

  if not field_exist('ROOTDEVICES', 'DLY_PRODSTOP') then
    do_sql('ALTER TABLE ROOTDEVICES ADD DLY_PRODSTOP SMALLINT DEFAULT 0');

  if not field_exist('ROOTDEVICES', 'FL_CTRLONLY') then
    do_sql('ALTER TABLE ROOTDEVICES ADD FL_CTRLONLY SMALLINT DEFAULT 0');

  if not field_exist('ROOTDEVICES', 'FL_SOLEOWNER') then begin
    do_sql('ALTER TABLE ROOTDEVICES ADD FL_SOLEOWNER SMALLINT DEFAULT 0');
    do_sql('update rootdevices set fl_soleowner=0 where fl_soleowner is null');
  end;



// ZCTA


  if not table_exist('ZCTA_ROUTS') then begin
    do_sql('CREATE GENERATOR GEN_ZCTA_ROUTS_ID');

    do_sql('CREATE TABLE ZCTA_ROUTS ('+
           'ID INTEGER NOT NULL,'+
           'CODE INTEGER,'+
           'NAME VARCHAR(256),'+
           'LAST_DEVICE_ID INTEGER,'+
           'DESCR VARCHAR(1024),'+
           'DLY_ALARM SMALLINT DEFAULT 0,'+
           'DLY_STOPFLOW SMALLINT DEFAULT 0,'+
           'DLY_STOPROUT SMALLINT DEFAULT 0,'+
           'STEPSIZE SMALLINT DEFAULT 1,'+
           'STEPTIME SMALLINT DEFAULT 1)');

    do_sql('alter table ZCTA_ROUTS add constraint PK_ZCTA_ROUTS primary key (ID)');

    do_sql(      'CREATE TRIGGER ZCTA_ROUTS_BI FOR ZCTA_ROUTS'+
           #13#10'ACTIVE BEFORE INSERT POSITION 0'+
           #13#10'AS'+
           #13#10'DECLARE VARIABLE m INTEGER;'+
           #13#10'DECLARE VARIABLE i INTEGER;'+
           #13#10'BEGIN'+
           #13#10'  IF (NEW.ID IS NULL) THEN'+
           #13#10'    NEW.ID = GEN_ID(GEN_ZCTA_ROUTS_ID,1);'+
           #13#10'  if (new.code is null) then'+
           #13#10'  begin'+
           #13#10'    i=1;'+
           #13#10'    for select code from ZCTA_ROUTS order by code into :m'+
           #13#10'    do'+
           #13#10'    begin'+
           #13#10'      if (i=m) then'+
           #13#10'        i=i+1;'+
           #13#10'      else'+
           #13#10'        break;'+
           #13#10'    end'+
           #13#10'    new.code = i;'+
           #13#10'  end'+
           #13#10'END');
  end;


  if not table_exist('ZCTA_ROUTDEVICES') then begin
    do_sql('CREATE GENERATOR GEN_ZCTA_ROUTDEVICES_ID');

    do_sql('CREATE TABLE ZCTA_ROUTDEVICES ('+
           'ID INTEGER NOT NULL,'+
           'ROUT_ID INTEGER NOT NULL,'+
           'DEVICE_ID INTEGER NOT NULL,'+
           'ORDERNUM INTEGER,'+
           'FL_ALARM SMALLINT DEFAULT 0,'+
           'FL_STOPFLOW SMALLINT DEFAULT 0,'+
           'FL_STOPROUT SMALLINT DEFAULT 0)');

    do_sql('alter table ZCTA_ROUTDEVICES add constraint PK_ZCTA_ROUTDEVICES primary key (ID)');

    do_sql('CREATE TRIGGER ZCTA_ROUTDEVICES_BI FOR ZCTA_ROUTDEVICES'+
     #13#10'ACTIVE BEFORE INSERT POSITION 0'+
     #13#10'AS'+
     #13#10'DECLARE VARIABLE m INTEGER;'+
     #13#10'BEGIN'+
     #13#10'  IF (NEW.ID IS NULL) THEN'+
     #13#10'    NEW.ID = GEN_ID(GEN_ZCTA_ROUTDEVICES_ID,1);'+
     #13#10'  if (new.ordernum is null) then'+
     #13#10'    select max(ordernum) from ZCTA_ROUTDEVICES where rout_id=new.rout_id into :m; '+
     #13#10'  if (m is null) then m = 0;'+
     #13#10'  new.ordernum = m+1;'+
     #13#10'END');



    do_sql('CREATE PROCEDURE MAKE_ZCTA_ROUTS_DESCR ('+
     #13#10'rout_id integer)'+
     #13#10'as'+
     #13#10'declare variable s varchar(1024);'+
     #13#10'declare variable ss varchar(128);'+
     #13#10'begin'+
     #13#10'  s = '''';'+
     #13#10'  for select d.tagname from devices d, ZCTA_ROUTDEVICES rd'+
     #13#10'      where (d.id=rd.device_id) and (rd.rout_id=:rout_id)'+
     #13#10'      order by rd.ordernum into :ss'+
     #13#10'  do begin'+
     #13#10'    if (s='''') then'+
     #13#10'      s = ss;'+
     #13#10'    else'+
     #13#10'      s = s || ''-'' || ss;'+
     #13#10'  end'+
     #13#10'  update ZCTA_ROUTS set descr=:s where id=:rout_id;'+
     #13#10'END');

    do_sql('CREATE PROCEDURE MAKE_ALL_ZCTA_ROUTS_DESCR'+
     #13#10'as'+
     #13#10'declare variable id integer;'+
     #13#10'begin'+
     #13#10'  for select id from ZCTA_ROUTS into :id do'+
     #13#10'    execute procedure make_ZCTA_ROUTS_descr(id);'+
     #13#10'end; ');


    do_sql('CREATE TRIGGER ZCTA_ROUTDEVICES_BD2 FOR ZCTA_ROUTDEVICES'+
     #13#10'ACTIVE AFTER DELETE POSITION 2'+
     #13#10'AS'+
     #13#10'DECLARE VARIABLE id INTEGER;'+
     #13#10'DECLARE VARIABLE ordnum INTEGER;'+
     #13#10'begin'+
     #13#10'  select first 1 device_id, ordernum'+
     #13#10'        from ZCTA_ROUTDEVICES'+
     #13#10'        where rout_id=old.rout_id'+
     #13#10'        order by ordernum'+
     #13#10'        into :id, :ordnum;'+
     #13#10'  update ZCTA_ROUTS set last_device_id=:id where id=old.rout_id;'+
     #13#10'  execute procedure MAKE_ZCTA_ROUTS_DESCR(old.rout_id);'+
     #13#10'end');




    do_sql('CREATE TRIGGER ZCTA_ROUTDEVICES_AIU1 FOR ZCTA_ROUTDEVICES'+
     #13#10'ACTIVE AFTER INSERT OR UPDATE POSITION 1'+
     #13#10'AS'+
     #13#10'DECLARE VARIABLE id INTEGER;'+
     #13#10'DECLARE VARIABLE ordnum INTEGER;'+
     #13#10'begin'+
     #13#10'  select first 1 device_id, ordernum'+
     #13#10'        from ZCTA_ROUTDEVICES'+
     #13#10'        where rout_id=new.rout_id'+
     #13#10'        order by ordernum'+
     #13#10'        into :id, :ordnum;'+
     #13#10'  update ZCTA_ROUTS set last_device_id=:id where id=new.rout_id;'+
     #13#10'  execute procedure MAKE_ZCTA_ROUTS_DESCR(new.rout_id);'+
     #13#10'end');



    do_sql('alter table ZCTA_ROUTDEVICES '+
           'add constraint FK_ZCTA_ROUTDEVICES_1 '+
           'foreign key (ROUT_ID) '+
           'references ZCTA_ROUTS(ID) '+
           'on delete CASCADE '+
           'on update CASCADE ');


    do_sql('alter table ZCTA_ROUTDEVICES '+
           'add constraint FK_ZCTA_ROUTDEVICES_2 '+
           'foreign key (DEVICE_ID) '+
           'references DEVICES(ID) '+
           'on delete CASCADE '+
           'on update CASCADE ');

  end;


  if not table_exist('ZCTA_DEVDIRS') then begin
    do_sql('CREATE GENERATOR GEN_ZCTA_DEVDIRS_ID');

    do_sql('CREATE TABLE ZCTA_DEVDIRS ('+
           'ID INTEGER NOT NULL,'+
           'DEVICE_ID INTEGER NOT NULL,'+
           'DIRNAME VARCHAR(64),'+
           'DIRNUM SMALLINT)');

    do_sql('alter table ZCTA_DEVDIRS add constraint PK_ZCTA_DEVDIRS primary key (ID)');


    do_sql('ALTER TABLE ZCTA_DEVDIRS ADD CONSTRAINT FK_ZCTA_DEVDIRS_1 FOREIGN KEY (DEVICE_ID) REFERENCES DEVICES (ID) ON DELETE CASCADE ON UPDATE CASCADE');

    do_sql('CREATE TRIGGER ZCTA_DEVDIRS_BI FOR ZCTA_DEVDIRS'+
     #13#10'ACTIVE BEFORE INSERT POSITION 0'+
     #13#10'AS'+
     #13#10'BEGIN'+
     #13#10'  IF (NEW.ID IS NULL) THEN'+
     #13#10'    NEW.ID = GEN_ID(GEN_ZCTA_DEVDIRS_ID,1);'+
     #13#10'END');

  end;

  if not field_exist('ZCTA_ROUTDEVICES', 'DIR_ID') then
    do_sql('ALTER TABLE ZCTA_ROUTDEVICES ADD DIR_ID INTEGER DEFAULT 0');

end;

end.
