Database cqrlogXXX

        CREATE TABLE cqrlog_main (
            id_cqrlog_main INT AUTO_INCREMENT PRIMARY KEY,
            qsodate DATE NOT NULL,
            time_on VARCHAR(5) NOT NULL,
            time_off VARCHAR(5) DEFAULT '',
            callsign VARCHAR(20) NOT NULL,
            freq NUMERIC(10,4) NOT NULL,
            mode VARCHAR(10) NOT NULL,
            rst_s VARCHAR(20) DEFAULT '',
            rst_r VARCHAR(20) DEFAULT '',
            name VARCHAR(40) DEFAULT '',
            qth VARCHAR(60) DEFAULT '',
            qsl_s VARCHAR(4) DEFAULT '',
            qsl_r VARCHAR(3) DEFAULT '',
            qsl_via VARCHAR(30) DEFAULT '',
            iota VARCHAR(6) DEFAULT '',
            pwr VARCHAR(10) DEFAULT '',
            itu INT DEFAULT 0,
            waz INT DEFAULT 0,
            loc VARCHAR(6) DEFAULT '',
            my_loc VARCHAR(6) DEFAULT '',
            county VARCHAR(30) DEFAULT '',
            award VARCHAR(50) DEFAULT '',
            remarks VARCHAR(200) DEFAULT '',
            adif INTEGER DEFAULT 0,
            /*dxcc_ref VARCHAR(20) DEFAULT '',*/
            band VARCHAR(6) DEFAULT '',
            qso_dxcc INT DEFAULT 0,
            profile INT DEFAULT 0,
            idcall VARCHAR(20) DEFAULT '',
            state VARCHAR(4) DEFAULT '',
            lotw_qslsdate DATE,
            lotw_qslrdate DATE,
            lotw_qsls VARCHAR(3) DEFAULT '' not null,
            lotw_qslr VARCHAR(3) DEFAULT '' not null,
            cont VARCHAR(3) DEFAULT '',
            qsls_date VARCHAR(10),
            qslr_date VARCHAR(10),
            club_nr1 VARCHAR(100) DEFAULT '',
            club_nr2 VARCHAR(100) DEFAULT '',
            club_nr3 VARCHAR(100) DEFAULT '',
            club_nr4 VARCHAR(100) DEFAULT '',
            club_nr5 VARCHAR(100) DEFAULT '',
            eqsl_qsl_sent varchar(1) default '' not null,
            eqsl_qslsdate date default null,
            eqsl_qsl_rcvd varchar(1) default '' not null,
            eqsl_qslrdate date default null
);
CREATE INDEX main_index ON cqrlog_main(qsodate DESC,time_on DESC);
CREATE INDEX callsign ON cqrlog_main(callsign);
CREATE INDEX name ON cqrlog_main(name);
CREATE INDEX qth ON cqrlog_main(QTH);
CREATE INDEX adif ON cqrlog_main(adif);
CREATE INDEX idcall ON cqrlog_main(idcall);
CREATE INDEX band ON cqrlog_main(band);
CREATE INDEX club_nr1 ON cqrlog_main(club_nr1);
CREATE INDEX club_nr2 ON cqrlog_main(club_nr2);
CREATE INDEX club_nr3 ON cqrlog_main(club_nr3);
CREATE INDEX club_nr4 ON cqrlog_main(club_nr4);
CREATE INDEX club_nr5 ON cqrlog_main(club_nr5);


CREATE TABLE notes(
            id_notes INT AUTO_INCREMENT PRIMARY KEY,
            callsign VARCHAR(20) DEFAULT '',
            longremarks VARCHAR(256) DEFAULT ''
);
CREATE INDEX callsign ON notes(callsign);

CREATE TABLE profiles (
            id_profiles INT AUTO_INCREMENT PRIMARY KEY,
            nr INT NOT NULL,
            locator VARCHAR(6) DEFAULT '',
            qth VARCHAR(250) DEFAULT '',
            rig VARCHAR(250) DEFAULT '',
            remarks VARCHAR(250) DEFAULT '',
            visible INT DEFAULT 1
);
CREATE INDEX nr ON profiles(nr);

CREATE TABLE long_note (
            id_long_note INT AUTO_INCREMENT PRIMARY KEY,
            note LONGTEXT
);

CREATE TABLE version (
            major INTEGER DEFAULT 0,
            minor INTEGER DEFAULT 9,
            releas INTEGER DEFAULT 4
);

CREATE TABLE club1 (
   id_club1 INT AUTO_INCREMENT PRIMARY KEY,
   club_nr VARCHAR(100) DEFAULT '',
   clubcall VARCHAR(100) DEFAULT '',
   fromdate DATE,
   todate DATE
);
CREATE INDEX club_nr ON club1(club_nr);
CREATE INDEX fromdate ON club1(fromdate);
CREATE INDEX todate ON club1(todate);
CREATE INDEX clubcall ON club1(clubcall);

CREATE TABLE club2 (
   id_club2 INT AUTO_INCREMENT PRIMARY KEY,
   club_nr VARCHAR(100) DEFAULT '',
   clubcall VARCHAR(100) DEFAULT '',
   fromdate DATE,
   todate DATE
);
CREATE INDEX club_nr ON club2(club_nr);
CREATE INDEX fromdate ON club2(fromdate);
CREATE INDEX todate ON club2(todate);
CREATE INDEX clubcall ON club2(clubcall);

CREATE TABLE club3 (
   id_club3 INT AUTO_INCREMENT PRIMARY KEY,
   club_nr VARCHAR(100) DEFAULT '',
   clubcall VARCHAR(100) DEFAULT '',
   fromdate DATE,
   todate DATE
);
CREATE INDEX club_nr ON club3(club_nr);
CREATE INDEX fromdate ON club3(fromdate);
CREATE INDEX todate ON club3(todate);
CREATE INDEX clubcall ON club3(clubcall);

CREATE TABLE club4 (
   id_club4 INT AUTO_INCREMENT PRIMARY KEY,
   club_nr VARCHAR(100) DEFAULT '',
   clubcall VARCHAR(100) DEFAULT '',
   fromdate DATE,
   todate DATE
);
CREATE INDEX club_nr ON club4(club_nr);
CREATE INDEX fromdate ON club4(fromdate);
CREATE INDEX todate ON club4(todate);
CREATE INDEX clubcall ON club4(clubcall);

CREATE TABLE club5 (
   id_club5 INT AUTO_INCREMENT PRIMARY KEY,
   club_nr VARCHAR(100) DEFAULT '',
   clubcall VARCHAR(100) DEFAULT '',
   fromdate DATE,
   todate DATE
);
CREATE INDEX club_nr ON club5(club_nr);
CREATE INDEX fromdate ON club5(fromdate);
CREATE INDEX todate ON club5(todate);
CREATE INDEX clubcall ON club5(clubcall);


CREATE TABLE zipcode1 (
   id_zipcode1 INT AUTO_INCREMENT PRIMARY KEY,
   zip VARCHAR(20) DEFAULT '',
   county VARCHAR(100) DEFAULT ''
);
CREATE INDEX zip ON zipcode1(zip);

CREATE TABLE zipcode2 (
   id_zipcode2 INT AUTO_INCREMENT PRIMARY KEY,
   zip VARCHAR(20) DEFAULT '',
   county VARCHAR(100) DEFAULT ''
);
CREATE INDEX zip ON zipcode2(zip);

CREATE TABLE zipcode3 (
   id_zipcode3 INT AUTO_INCREMENT PRIMARY KEY,
   zip VARCHAR(20) DEFAULT '',
   county VARCHAR(100) DEFAULT ''
);
CREATE INDEX zip ON zipcode3(zip);

CREATE TABLE cqrlog_config (
  id_cqrlog__config INT AUTO_INCREMENT PRIMARY KEY,
  config_file LONGTEXT
);
INSERT INTO cqrlog_config (config_file) VALUES ('');

CREATE TABLE dxcc_id
(
  id_dxcc INT AUTO_INCREMENT PRIMARY KEY,
  adif INT DEFAULT 0,
  dxcc_ref VARCHAR(16) NOT NULL,
  country VARCHAR(100) NOT NULL
);
CREATE INDEX adif ON dxcc_id(adif);

CREATE TABLE db_version
(
  nr SMALLINT NOT NULL
);

CREATE TABLE log_changes (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  id_cqrlog_main int(11) NULL,
  cmd varchar(10) NOT NULL,
  qsodate date NULL,
  time_on varchar(5) NULL,
  callsign varchar(20) NULL,
  mode varchar(10) NULL,
  band varchar(6) NULL,
  freq numeric(10,4) NULL,
  old_qsodate date NULL,
  old_time_on varchar(5) NULL,
  old_callsign varchar(20) NULL,
  old_mode varchar(10) NULL,
  old_band varchar(6) NULL,
  old_freq numeric(10,4) NULL,
  upddeleted int(1) default 1
) COLLATE 'utf8_bin';

ALTER TABLE log_changes
ADD INDEX id_cqrlog_main (id_cqrlog_main);

ALTER TABLE log_changes
ADD FOREIGN KEY (id_cqrlog_main) REFERENCES cqrlog_main (id_cqrlog_main) ON DELETE SET NULL ON UPDATE CASCADE;

CREATE TABLE upload_status (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  logname varchar(30) NOT NULL,
  id_log_changes int(11) NULL,
  FOREIGN KEY (id_log_changes) REFERENCES log_changes (id) ON DELETE SET NULL
) COLLATE 'utf8_bin';

CREATE TABLE call_alert (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  callsign varchar(20) NOT NULL,
  band varchar(6) NULL,
  mode varchar(6) NULL
) COLLATE 'utf8_bin';

ALTER TABLE call_alert ADD INDEX (id);
ALTER TABLE call_alert ADD INDEX (callsign);

CREATE TABLE freqmem (
       id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
       freq numeric(10,4) NOT NULL,
       mode varchar(6) NOT NULL,
       bandwidth int NOT NULL
) COLLATE 'utf8_bin';

CREATE VIEW view_cqrlog_main_by_qsodate AS SELECT id_cqrlog_main,qsodate,time_on,time_off,callsign, freq,mode,rst_s,rst_r,name,qth,qsl_s,qsl_r,qsl_via,iota,pwr,itu,waz,loc,my_loc,county,
award,remarks, band, dxcc_id.dxcc_ref AS dxcc_ref ,qso_dxcc, profile,idcall, state, lotw_qslsdate, lotw_qslrdate,lotw_qsls, lotw_qslr, cont, qsls_date,qslr_date,club_nr1,club_nr2,club_nr3,
club_nr4,club_nr5,eqsl_qsl_sent,eqsl_qslsdate,eqsl_qsl_rcvd,eqsl_qslrdate,concat(qsl_r,lotw_qslr,eqsl_qsl_rcvd) as qslr,dxcc_id.country FROM cqrlog_main JOIN dxcc_id ON dxcc_id.adif = cqrlog_main.adif order by qsodate DESC, time_on DESC;

CREATE VIEW view_cqrlog_main_by_callsign AS SELECT id_cqrlog_main,qsodate,time_on,time_off,callsign, freq,mode,rst_s,rst_r,name,qth,qsl_s,qsl_r,qsl_via,iota,pwr,itu,waz,loc,my_loc,county,
award,remarks, band, dxcc_id.dxcc_ref AS dxcc_ref ,qso_dxcc, profile,idcall, state, lotw_qslsdate, lotw_qslrdate,lotw_qsls, lotw_qslr, cont, qsls_date,qslr_date,club_nr1,club_nr2,club_nr3,
club_nr4,club_nr5,eqsl_qsl_sent,eqsl_qslsdate,eqsl_qsl_rcvd,eqsl_qslrdate,concat(qsl_r,lotw_qslr,eqsl_qsl_rcvd) as qslr,dxcc_id.country FROM cqrlog_main JOIN dxcc_id ON dxcc_id.adif = cqrlog_main.adif order by callsign;

CREATE TRIGGER `cqrlog_main_ai` AFTER INSERT ON `cqrlog_main` FOR EACH ROW
insert into log_changes(id_cqrlog_main,cmd,qsodate,time_on,callsign,mode,freq,band) values
  (NEW.id_cqrlog_main,'INSERT',NEW.qsodate,NEW.time_on,NEW.callsign,NEW.mode,NEW.freq,NEW.band);

CREATE TRIGGER `cqrlog_main_bd` BEFORE DELETE ON `cqrlog_main` FOR EACH ROW
insert into log_changes(id_cqrlog_main,cmd,old_qsodate, old_time_on, old_callsign, old_mode, old_band,old_freq)
values (OLD.id_cqrlog_main,'DELETE',OLD.qsodate, OLD.time_on, OLD.callsign, OLD.mode, OLD.band, OLD.freq);

CREATE TRIGGER `cqrlog_main_bu` BEFORE UPDATE ON `cqrlog_main` FOR EACH ROW
insert into log_changes(id_cqrlog_main,cmd,old_qsodate, old_time_on, old_callsign, old_mode, old_band,old_freq,
qsodate, time_on, callsign, mode, band,freq)
values (OLD.id_cqrlog_main,'UPDATE',OLD.qsodate, OLD.time_on, OLD.callsign, OLD.mode, OLD.band,OLD.freq,
NEW.qsodate, NEW.time_on, NEW.callsign, NEW.mode, NEW.band, NEW.freq);

Database cqrlog_common

CREATE DATABASE cqrlog_common DEFAULT CHARACTER SET = utf8  DEFAULT COLLATE = utf8_bin;

CREATE TABLE bands (
       id_bands INT AUTO_INCREMENT PRIMARY KEY,
       band VARCHAR(10) DEFAULT '',
       b_begin NUMERIC(10,4),
       b_end NUMERIC(10,4),
       cw NUMERIC(10,4),
       rtty NUMERIC(10,4),
       ssb NUMERIC(10,4),
       rx_offset numeric(10,4) default 0,
       tx_offset numeric(10,4) default 0  
);
CREATE INDEX band ON bands(band);

CREATE TABLE dxclusters (
      id_dxclusters INT AUTO_INCREMENT PRIMARY KEY,
      description VARCHAR(100) DEFAULT '',
      address VARCHAR(100) DEFAULT '',
      port VARCHAR(10) DEFAULT '',
      dxcuser VARCHAR(12) DEFAULT '',
      dxcpass VARCHAR(20) DEFAULT ''
);
CREATE INDEX description ON dxclusters(description);

CREATE TABLE iota_list (
      id_iota_list INT AUTO_INCREMENT PRIMARY KEY,
      iota_nr VARCHAR(6) DEFAULT '',
      island_name VARCHAR(250) DEFAULT '',
      dxcc_ref VARCHAR(15) DEFAULT '',
      pref VARCHAR(15) DEFAULT ''
);
CREATE INDEX iota_nr ON iota_list(iota_nr);
CREATE INDEX pref ON iota_list(pref);


CREATE TABLE dxcc_ref (
            id_dxcc_ref INT AUTO_INCREMENT PRIMARY KEY,
            pref VARCHAR(20) NOT NULL,
            name VARCHAR(100) NOT NULL,
            cont VARCHAR(6) NOT NULL,
            utc VARCHAR(12)NOT NULL,
            lat VARCHAR(10) NOT NULL,
            longit VARCHAR(10) NOT NULL,
            itu VARCHAR(20) NOT NULL,
            waz VARCHAR(20) NOT NULL,
            adif SMALLINT NOT NULL DEFAULT 0,
            deleted SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX pref ON dxcc_ref(pref);
CREATE INDEX adif ON dxcc_ref(adif);

CREATE TABLE qslmgr (
            id_qslmgr INT AUTO_INCREMENT PRIMARY KEY,
            callsign VARCHAR(20),
            qsl_via VARCHAR(20),
            fromdate DATE
);
CREATE INDEX callsign ON qslmgr(callsign);
CREATE INDEX fromdate ON qslmgr(fromdate);

CREATE TABLE log_list (
            id_log_list INT AUTO_INCREMENT PRIMARY KEY,
            log_nr SMALLINT NOT NULL,
            log_name VARCHAR(50) DEFAULT '',
            auto_open SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX log_nr ON log_list(log_nr);

CREATE TABLE db_version
(
  nr SMALLINT NOT NULL
);