/******************************************************************************/
/***         Generated by IBExpert 2013.2.20.1 10/12/2014 06:42:46          ***/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES UTF8;

SET CLIENTLIB 'C:\Program Files (x86)\Firebird\Firebird_2_5\bin\fbclient.dll';

CREATE DATABASE '127.0.0.1:C:\Projects\Romcenter4\DataAccess\Firebird\firebird_bin\TEMPLATE.BIN'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET UTF8 COLLATION UTF8;



/******************************************************************************/
/***                                Domains                                 ***/
/******************************************************************************/

CREATE DOMAIN CRC_SHA1 AS
VARCHAR(50);

CREATE DOMAIN FILEEXT AS
VARCHAR(10);

CREATE DOMAIN FILENAME AS
VARCHAR(241);

CREATE DOMAIN PATHNAME AS
VARCHAR(230);

CREATE DOMAIN RCBOOL AS
SMALLINT
DEFAULT 0;

CREATE DOMAIN RCSIZE AS
BIGINT;



/******************************************************************************/
/***                               Generators                               ***/
/******************************************************************************/

CREATE GENERATOR GENFILEID;
SET GENERATOR GENFILEID TO 21548;

CREATE GENERATOR GENFILEROMID;
SET GENERATOR GENFILEROMID TO 227678;

CREATE GENERATOR GENFILEROMPK;
SET GENERATOR GENFILEROMPK TO 198788;

CREATE GENERATOR GENGAMEID;
SET GENERATOR GENGAMEID TO 91462;

CREATE GENERATOR GENGAMEROMID;
SET GENERATOR GENGAMEROMID TO 456126;

CREATE GENERATOR GENPATHID;
SET GENERATOR GENPATHID TO 144;

CREATE GENERATOR GENROMID;
SET GENERATOR GENROMID TO 233084;



SET TERM ^ ; 



/******************************************************************************/
/***                           Stored Procedures                            ***/
/******************************************************************************/

CREATE PROCEDURE LIST_MERGE (
    SRCFILEID INTEGER,
    DSTFILEID INTEGER)
RETURNS (
    SRCROMNAME VARCHAR(241),
    DSTROMNAME VARCHAR(241),
    GAMEROMID INTEGER,
    PLUGINEXT VARCHAR(9),
    ROMSIG VARCHAR(48),
    ROMSIZE DOUBLE PRECISION,
    SRCROMID INTEGER)
AS
BEGIN
  SUSPEND;
END^





CREATE PROCEDURE P_ADDFILE (
    PART INTEGER)
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_CHANGEMODE
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_COPY_FILEROM (
    SRCFILEROMID INTEGER,
    DSTFILEID INTEGER,
    NEWFILEROMNAME VARCHAR(241),
    NEWFILEROMDATE VARCHAR(20))
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_CREATEVIEWSINDEXES
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_DF_FIXDATAS
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_EXPORT_DB
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_F_UPDATECASE
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_F_UPDATEEXPNAME
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_F_UPDATEFILETYPE
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_F_UPDATEGAMEID
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_F_UPDATESTATUS
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_FG_UPDATEFILEGAMES
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_FILTERGAMES
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_FR_ADDMISSING
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_FR_UPDATECASE
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_FR_UPDATEEXPNAME
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_FR_UPDATEGAMEROMID
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_FR_UPDATEROMID
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_FR_UPDATESTATUS
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_FR_UPDATESTATUSLVL
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_G_PREPAREGAMES
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_G_TRANSFERTGAMES
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_G_UPDATE
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_G_UPDATEBIOS
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_G_UPDATEGAMENAME
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_G_UPDATESTATUS
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_GR_TRANSFERTGAMEROMS
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_INITGAMESINFO
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_LOADDATAFILE (
    SECTION INTEGER)
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_R_TRANSFERTROMS
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_R_UPDATEGAMEIDENT
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_REBUILD_IDX
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_REMOVEFILE (
    FILEID INTEGER)
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_REMOVEFILEROM (
    FILEROMID INTEGER)
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_REMOVEPATH (
    PATHID INTEGER)
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_REMOVETMPITEMS
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_RESETFILES
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_RL_UPDATERELEASES
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_SETERROR (
    FILEID INTEGER,
    MSG VARCHAR(250))
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_TRANSFERTFILES
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_TRANSFERTRELEASES
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE P_UPDATEGAMESINFO
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE REFRESHALL
AS
BEGIN
  EXIT;
END^





CREATE PROCEDURE TEST
AS
BEGIN
  EXIT;
END^






SET TERM ; ^



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE CONFIG (
    PARAM  VARCHAR(30) NOT NULL,
    VAL    VARCHAR(200),
    INFO   VARCHAR(50)
);

CREATE TABLE DATAFILETMP (
    GAMENAME      FILENAME NOT NULL,
    CLONEOF       FILENAME,
    ROMOF         FILENAME,
    SAMPLEOF      FILENAME,
    DEVICEREF     FILENAME,
    GAMEDESC      FILENAME NOT NULL,
    GAMEYEAR      VARCHAR(6),
    MANUFACTURER  VARCHAR(80),
    ROMEXTENSION  FILENAME,
    ROMNAME       FILENAME NOT NULL,
    MERGEROMNAME  FILENAME,
    ROMSIZE       RCSIZE NOT NULL,
    SIGVALUE      VARCHAR(48),
    SHA1          VARCHAR(50),
    ROMTYPE       VARCHAR(10) NOT NULL,
    ROMSTATUS     VARCHAR(10) NOT NULL,
    SELECTED      RCBOOL DEFAULT 1 NOT NULL
);

CREATE TABLE DATERRORS (
    GAMEID    INTEGER,
    ROMID     INTEGER,
    ROMNAME   FILENAME,
    COMMENTS  VARCHAR(270)
);

CREATE TABLE FILEGAMES (
    PATHTYPE   VARCHAR(7),
    GAMEID     INTEGER NOT NULL,
    GAMEROMID  INTEGER NOT NULL
);

CREATE TABLE FILEROMS (
    EFID            INTEGER NOT NULL,
    FILEROMID       INTEGER,
    FILEID          INTEGER NOT NULL,
    GAMEROMID       INTEGER,
    EXPROMNAME      FILENAME,
    PLUGINEXT       VARCHAR(9),
    PARENTFORMAT    VARCHAR(6),
    ROMID           INTEGER,
    ROMPATH         VARCHAR(255),
    ROMNAME         FILENAME,
    ROMEXT          VARCHAR(9) DEFAULT '',
    ROMSIZE         RCSIZE,
    ROMSIG          VARCHAR(48),
    ROMTYPE         VARCHAR(10),
    ROMDATE         TIMESTAMP,
    ERROR           VARCHAR(200),
    ROMFULLNAME     COMPUTED BY (coalesce(rompath,'')||romname||romext),
    ISUNKNOWN       COMPUTED BY (case when gameromid is null then 1 else 0 end),
    ISMISSING       COMPUTED BY (case when fileromid is null then 1 else 0 end),
    ISAVAIL         RCBOOL,
    ISNOTUSED       RCBOOL,
    ISBADNAME       COMPUTED BY (case when ROMFULLNAME <> EXPROMNAME then 1 else 0 end),
    ISBADFOLDER     RCBOOL,
    ISFOLDERNEEDED  COMPUTED BY (case when romtype = 'disk' and parentformat <> 'folder' then 1 else 0 end),
    STATUSLVL       SMALLINT,
    NEEDREFRESH     RCBOOL DEFAULT 1
);

CREATE TABLE FILES (
    FILEID       INTEGER NOT NULL,
    GAMEID       INTEGER,
    PATHID       INTEGER,
    FILENAME     FILENAME NOT NULL,
    EXTENSION    VARCHAR(9),
    FILESIZE     RCSIZE NOT NULL,
    FILEFORMAT   VARCHAR(6),
    FILETYPE     VARCHAR(20),
    EXPFILENAME  FILENAME,
    STATUS       VARCHAR(20) Default 'na',
    FILEDATE     TIMESTAMP,
    ERROR        VARCHAR(255),
    ZIPCOMMENT   VARCHAR(100),
    NEEDREFRESH  RCBOOL DEFAULT 1
);

CREATE TABLE FILESTMP (
    PATHNAME      PATHNAME NOT NULL,
    PATHTYPE      VARCHAR(10) NOT NULL,
    FILENAME      FILENAME,
    EXTENSION     VARCHAR(9),
    FILEFORMAT    VARCHAR(9),
    FILEDATE      TIMESTAMP,
    FILESIZE      RCSIZE,
    FILETYPE      VARCHAR(20),
    ROMPATH       VARCHAR(255),
    ROMNAME       FILENAME,
    PLUGINEXT     VARCHAR(9),
    ROMDATE       TIMESTAMP,
    ROMSIZE       RCSIZE,
    ROMSIG        VARCHAR(48),
    VOLUMENAME    VARCHAR(255),
    VOLUMESERIAL  VARCHAR(20),
    ERROR         VARCHAR(200),
    DRIVE         VARCHAR(50) DEFAULT '',
    DRIVETYPE     VARCHAR(10) DEFAULT 'unknown',
    ZIPCOMMENT    VARCHAR(100),
    UNC           VARCHAR(255)
);

CREATE TABLE GAMEROMS (
    GAMEROMID  INTEGER NOT NULL,
    SHORTNAME  FILENAME,
    EXTENSION  VARCHAR(10),
    ROMNAME    COMPUTED BY (shortname||extension),
    ROMMERGE   INTEGER,
    ROMTYPE    VARCHAR(10) NOT NULL,
    GAMEID     INTEGER NOT NULL,
    ROMID      INTEGER,
    DUMPSTATUS  VARCHAR(10)
);

CREATE TABLE GAMES (
    GAMEID        INTEGER NOT NULL,
    SETID         INTEGER,
    NAME          FILENAME,
    GAMENUMBER    VARCHAR(10),
    CLONEOF       INTEGER,
    ROMOF         INTEGER,
    BIOSOF        INTEGER,
    SAMPLEOF      INTEGER,
    DESCRIPTION   VARCHAR(200) NOT NULL,
    GAMEYEAR      VARCHAR(6),
    MANUFACTURER  VARCHAR(80),
    GAMETYPE      VARCHAR(5),
    ORIGINALNAME  FILENAME NOT NULL,
    BADROM        RCBOOL,
    BADBIOS       RCBOOL,
    BADDISK       RCBOOL,
    NBROMS        INTEGER DEFAULT 0 NOT NULL,
    NBBIOS        INTEGER DEFAULT 0 NOT NULL,
    NBSAMPLES     INTEGER DEFAULT 0 NOT NULL,
    NBDISKS       INTEGER DEFAULT 0 NOT NULL,
    DUMPSTATUS    VARCHAR(10) DEFAULT 'good' NOT NULL
);

CREATE TABLE GAMESINFO (
    GAMEID     INTEGER NOT NULL,
    ROMTYPE    VARCHAR(20) NOT NULL,
    ROMMERGE   VARCHAR(10) NOT NULL,
    ISMISSING  SMALLINT DEFAULT -1 NOT NULL
);

CREATE TABLE LANGUAGES (
    LANGUAGEID  INTEGER NOT NULL,
    NAME        VARCHAR(241),
    PREFORDER   INTEGER
);

CREATE TABLE PATHS (
    PATHID         INTEGER NOT NULL,
    SHORTPATHNAME  PATHNAME NOT NULL,
    VOLUMENAME     VARCHAR(255),
    VOLUMESERIAL   VARCHAR(20),
    PATHTYPE       VARCHAR(10) NOT NULL,
    DRIVE          VARCHAR(50),
    DRIVETYPE      VARCHAR(10),
    PATHNAME       COMPUTED BY (drive||shortpathname),
    STATUS         VARCHAR(2),
    UNC            VARCHAR(255)
);

CREATE TABLE REGIONS (
    REGIONID   INTEGER NOT NULL,
    NAME       VARCHAR(241),
    PREFORDER  INTEGER
);

CREATE TABLE RELEASES (
    GAMEID      INTEGER NOT NULL,
    NAME        VARCHAR(241),
    REGIONID    INTEGER,
    LANGUAGEID  INTEGER,
    ISDEFAULT   RCBOOL NOT NULL
);

CREATE TABLE RELEASESTMP (
    GAMENAME     VARCHAR(241) NOT NULL,
    RELEASENAME  VARCHAR(241),
    REGION       VARCHAR(241),
    LANG         VARCHAR(241),
    ISDEFAULT    RCBOOL NOT NULL
);

CREATE TABLE ROMS (
    ROMID      INTEGER NOT NULL,
    GAMEIDENT  INTEGER,
    SIGVALUE   VARCHAR(48),
    SHA1       VARCHAR(50),
    ROMSIZE    RCSIZE NOT NULL,
    GETSHA1    RCBOOL NOT NULL,
    DUMPED     RCBOOL DEFAULT 1 NOT NULL
);

CREATE TABLE TMP (
    ID1   INTEGER,
    ID2   INTEGER,
    ID3   INTEGER,
    STR1  FILENAME,
    STR2  FILENAME
);



/******************************************************************************/
/***                                 Views                                  ***/
/******************************************************************************/


/* View: V_AVAILABILITY */
CREATE VIEW V_AVAILABILITY(
    ROMID,
    NBROMSFOUND)
AS
select r.romid, case when min(r.dumped) = 0 then 1 else count(fr.fileromid) end
from roms r
left join FILEROMS fr on fr.romid = r.romid
group by r.romid
;



/* View: V_DESTFILESFORROMS */
CREATE VIEW V_DESTFILESFORROMS(
    EFID,
    SRCFILEID,
    FILEROMID,
    SRCROMNAME,
    GAMEROMID,
    DSTPATH,
    DSTFILEID,
    DSTGAMEROMID,
    DSTFILENAME,
    EXTENSION,
    DSTROMNAME,
    ROMTYPE)
AS
-- Get the list of possible existing destination files roms to move (or remove)
-- Location (files, folder) proposed already exist on disk
-- Get the name of the rom in these files
select
  fr.FILEROMID||'_'||f.fileid||'_'||gr.gameromid,
  fr.fileid,
  fr.fileromid,
  fr.romname as srcromname,
  gr.gameromid,
  p.pathname as destpath,
  f.fileid as dstfileid,
  gr.gameromid,
  f.filename as destfilename,
  f.extension,
  gr.romname as dstromname,
  gr.romtype
  from fileroms fr
  join gameroms gr on gr.romid = fr.romid
  join filegames fg on gr.gameromid = fg.gameromid
  join games g on g.gameid = fg.gameid
  join files f on (f.gameid = fg.gameid) or (upper(f.filename) = upper(g.name))
  join paths p on p.pathid = f.pathid
  left join fileroms fr2 on fr2.fileid= f.fileid and fr2.romid = gr.romid
  where fr.fileromid is not null
  and (f.fileid <> fr.fileid)
  and f.status <> 'error' and p.pathtype <> 'samples'
  and (gr.romtype <> 'disk' or f.fileformat= 'folder')
  order by case when (fr2.fileromid is null) then 'n' else 'y' end,
  f.fileid desc,gametype desc
;



/* View: V_FILEROMS_TO_REMOVE */
CREATE VIEW V_FILEROMS_TO_REMOVE(
    PATHID,
    FILEID,
    FILEROMID,
    ROMNAME,
    ROMTYPE,
    ERROR,
    ISUNKNOWN,
    ISNOTUSED,
    NBAVAIL,
    PATHNAME,
    FILENAME,
    EXTENSION,
    FORMAT,
    FILESTATUS)
AS
select
f.PATHID,
f.FILEID,
fr.FILEROMID,
fr.ROMNAME,
fr.ROMTYPE,
fr.ERROR,
fr.isunknown,
fr.isnotused,
va.nbromsfound,
PATHNAME,
f.FILENAME,
f.EXTENSION,
f.FILEFORMAT,
f.status
from paths p
join files f on f.pathid=p.pathid
join fileroms fr on fr.fileid=f.fileid
left join v_availability va on va.romid = fr.romid
where fr.isnotused=1 or fr.isunknown=1 or fr.statuslvl = 10--error
;



/* View: V_FILEROMSLIST */
CREATE VIEW V_FILEROMSLIST(
    EFID,
    FILEID,
    FILEROMID,
    GAMEROMID,
    ROMFULLNAME,
    EXPROMNAME,
    ROMSIZE,
    ROMSIG,
    ROMTYPE,
    MERGED,
    DUMPSTATUS,
    PLUGINEXT,
    STATUS)
AS
select
    fr.FILEID||coalesce(fr.fileromid,'A')||coalesce(fr.gameromid,'B'),
    fr.FILEID,
    fr.FILEROMID        ,
    fr.gameromid,
    fr.ROMFULLNAME   ,
    fr.EXPROMNAME    ,
    fr.ROMSIZE       ,
    fr.ROMSIG        ,
    fr.ROMTYPE       ,
    case when gr.rommerge is not null then 1 else 0 end,
    gr.dumpstatus,
    fr.pluginext,
    case 
      when ERROR <> '' then 'error'
      when ISUNKNOWN = 1 then 'unknown'
      when ISMISSING = 1 and ISAVAIL = 0 then 'missing'
      when ISMISSING = 1 and ISAVAIL = 1 then 'missingavail'
      when ISNOTUSED = 1 then 'notused'
      when ISFOLDERNEEDED = 1 then 'folderneeded'
      when ISBADFOLDER = 1 then 'badfolder'
      when ISBADNAME = 1 then 'badname'
      else 'ok'
    end as info --missing, missingavail, badname, notused, unknown, error, folderneeded, badfolder
    from FILEROMS fr
    left join gameroms gr on gr.gameromid = fr.gameromid
;



/* View: V_FILESLIST */
CREATE VIEW V_FILESLIST(
    PATHID,
    FILEID,
    GAMEID,
    PATHNAME,
    FILENAME,
    DESCRIPTION,
    EXTENSION,
    FILESIZE,
    ZIPCOMMENT,
    ERROR,
    STATUS,
    DUMPSTATUS,
    EXPFILENAME,
    FILEFORMAT,
    FILETYPE,
    ROMSTATUS)
AS
select
  f.pathid, 
  f.fileid, 
  f.gameid, 
  p.pathname,
  f.filename, 
  g.description,
  f.extension, 
  f.filesize, 
  f.zipcomment, 
  f.error, 
  f.status,
  g.dumpstatus,
  f.expfilename,
  f.FILEFORMAT , 
  f.filetype, 
  case when f.error <> '' then 10 else coalesce(s.romstatus,0) end
  from FILES f 
  left join games g on g.gameid = f.gameid
  join paths p on p.pathid = f.pathid
  left join (select fr.fileid, min(coalesce(fr.statuslvl,0)) as romstatus  from FILEROMS fr  group by fr.fileid) s on s.fileid = f.fileid
;



/* View: V_FILES_TO_REMOVE */
CREATE VIEW V_FILES_TO_REMOVE(
    PATHID,
    FILEID,
    STATUS,
    ISEMPTY,
    FILEFORMAT)
AS
select vf.pathid, vf.fileid,vf.status,
case when vf.romstatus =0 and vf.filesize = 0 then 1 else 0 end as ISEMPTY,
vf.fileformat
from v_fileslist vf
where vf.status in ('unknown','error') --or (vf.romstatus = 0)
;



/* View: V_FILES_TO_RENAME */
CREATE VIEW V_FILES_TO_RENAME(
    PATHID,
    PATHTYPE,
    PATHNAME,
    PATHSTATUS,
    SRCFILEID,
    SRCFILENAME,
    SRCEXTENSION,
    SRCFILETYPE,
    NEWFOLDERNAME,
    SRCNEWNAME,
    SRCFILEFORMAT,
    SRCFILESTATUS,
    SRCFILEERROR,
    DSTFILEID,
    DSTFILETYPE,
    DSTSTATUS)
AS
select distinct F.PATHID,
                P.PATHTYPE,
                P.PATHNAME,
                P.status as PATHSTATUS,
                F.FILEID,
                F.FILENAME,
                F.EXTENSION,
                F.filetype,
                G.NAME as NEWFOLDERNAME,
                F.EXPFILENAME as SRCNEWNAME,
                F.FILEFORMAT as SRCFILEFORMAT,
                F.STATUS as SRCFILESTATUS,
                F.ERROR as SRCFILEERROR,
                DSTF.FILEID as DSTFILEID,
                DSTF.filetype,
                DSTF.status
from FILES F
join FILEROMS FR on FR.FILEID = F.FILEID
left join GAMEROMS GR on GR.GAMEROMID = FR.GAMEROMID and F.STATUS = 'folder needed'
join GAMES G on G.GAMEID = F.GAMEID
join PATHS P on P.PATHID = F.PATHID
left join FILES DSTF on DSTF.PATHID = F.PATHID and DSTF.FILENAME = G.NAME and DSTF.EXTENSION = F.EXTENSION
where F.STATUS in ('bad name', 'folder needed', 'wrong path')
order by p.PATHID, f.FILEID
;



/* View: V_GAMEROMSLIST */
CREATE VIEW V_GAMEROMSLIST(
    GAMEID,
    GAMEROMID,
    ROMNAME,
    CRC,
    SHA1,
    ROMSIZE,
    MERGED,
    ROMTYPE,
    ROMSTATUS,
    AVAIL)
AS
select gr.gameid,gr.gameromid, gr.romname, r.sigvalue as crc,r.sha1,r.romsize,
case when (gr.rommerge is null) then 0 else 1 end as merged,
gr.romtype,
gr.dumpstatus,
case when va.nbromsfound >0 then 1 else 0 end as avail
from gameroms gr
join roms r on r.romid = gr.romid
left join v_availability va on va.romid = gr.romid
where romstatus <> 'error'
;



/* View: V_ROMS_TO_RENAME */
CREATE VIEW V_ROMS_TO_RENAME(
    PATHID,
    FILEID,
    FILEROMID,
    FILENAME,
    FILEFORMAT,
    ROMFULLNAME,
    NEWROMNAME,
    PLUGINEXT)
AS
--Don't show single files rom! They must be in v_files_to_rename view.
select f.pathid,fr.fileid,fr.fileromid,
p.pathname||f.filename || f.extension as filename,
f.fileformat,
fr.romfullname,
fr.expromname,--gr.romname as newromname,
fr.pluginext
from paths p
join files f on f.pathid=p.pathid
join fileroms fr on fr.fileid = f.fileid
where fr.isbadname = 1 --and f.fileformat <> 'file'
order by f.pathid,fr.fileid
;


INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('name', 'mamep64 v0.151', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('plugin', 'arcade.dll', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('category', '', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('author', 'romcenter', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('email', 'help@romcenter.com', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('homepage', 'RomCenter', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('homepageurl', 'http://www.romcenter.com', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('reldate', '23/11/2013', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('comments', '', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('rommode', 's', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('biosmode', 's', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('lockrommode', 'false', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('lockbiosmode', 'false', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('setscase', 'database', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('romscase', 'database', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('fixromext', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('renamefiles', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('createdummy', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('findroms', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('removefiles', 'false', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('removeroms', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('renameroms', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('rezip', 'false', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('DB_version', '4.0061', 'Use ''.'' as decimal separator. Involve a db update.');
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('moveroms', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('createghostgames', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('testarchives', 'false', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('MoveSamples', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('corruptaction', 'leave', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('corruptpath', '', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('emulparam', '-rp "%4" "%1"', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('emulfilename', 'd:\Users\Eric\Documents\My Games\mameplus-0.151r5097-x64\mamep64.exe', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('emulversion', '', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('datversion', 'v0.151', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('allowromformats', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('samplemode', 'm', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('locksamplemode', 'false', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasbios', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hassamples', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasdisks', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasclones', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasbaddumps', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('haslanguages', 'false', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasdummies', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasghosts', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasregions', 'false', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasnumbers', 'false', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasreleases', 'false', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('usereleases', 'false', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('RemoveZipComments', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('description', 'mamep64 v0.151', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasverified', 'false', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasromextension', 'false', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('keepmain', 'false', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasbiosindat', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hassamplesindat', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasdisksindat', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasclonesindat', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasyear', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('hasmanufacturer', 'true', NULL);
INSERT INTO CONFIG (PARAM, VAL, INFO) VALUES ('preferredarchiveformat', '.zip', NULL);

COMMIT WORK;



/* Check constraints definition */

ALTER TABLE FILEGAMES ADD CONSTRAINT CHK1_FILETYPE CHECK (pathtype in ('roms','samples'));


/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE CONFIG ADD CONSTRAINT PK_CONFIG PRIMARY KEY (PARAM);
ALTER TABLE FILEGAMES ADD PRIMARY KEY (GAMEID, GAMEROMID);
ALTER TABLE FILEROMS ADD CONSTRAINT PK_FILEROMS PRIMARY KEY (EFID);
ALTER TABLE FILES ADD CONSTRAINT PKFILES_1 PRIMARY KEY (FILEID);
ALTER TABLE GAMEROMS ADD PRIMARY KEY (GAMEROMID);
ALTER TABLE GAMES ADD PRIMARY KEY (GAMEID);
ALTER TABLE LANGUAGES ADD CONSTRAINT PK_LANGUAGES PRIMARY KEY (LANGUAGEID);
ALTER TABLE PATHS ADD PRIMARY KEY (PATHID);
ALTER TABLE REGIONS ADD PRIMARY KEY (REGIONID);
ALTER TABLE ROMS ADD PRIMARY KEY (ROMID);


/******************************************************************************/
/***                              Foreign Keys                              ***/
/******************************************************************************/

ALTER TABLE FILEGAMES ADD CONSTRAINT INTEG_FG_GAMEID FOREIGN KEY (GAMEID) REFERENCES GAMES (GAMEID);
ALTER TABLE FILEGAMES ADD CONSTRAINT INTEG_FG_GAMEROMID FOREIGN KEY (GAMEROMID) REFERENCES GAMEROMS (GAMEROMID);
ALTER TABLE FILEROMS ADD CONSTRAINT FKFILEROMS_1 FOREIGN KEY (GAMEROMID) REFERENCES GAMEROMS (GAMEROMID);
ALTER TABLE FILEROMS ADD CONSTRAINT FKFILEROMS_2 FOREIGN KEY (FILEID) REFERENCES FILES (FILEID);
ALTER TABLE FILES ADD CONSTRAINT FKFILES_1 FOREIGN KEY (PATHID) REFERENCES PATHS (PATHID);
ALTER TABLE GAMEROMS ADD CONSTRAINT INTEG_GR_GAMEID FOREIGN KEY (GAMEID) REFERENCES GAMES (GAMEID);
ALTER TABLE GAMEROMS ADD CONSTRAINT INTEG_GR_ROMID FOREIGN KEY (ROMID) REFERENCES ROMS (ROMID);
ALTER TABLE GAMEROMS ADD CONSTRAINT INTEG_GR_ROMMERGE FOREIGN KEY (ROMMERGE) REFERENCES GAMEROMS (GAMEROMID);
ALTER TABLE GAMES ADD CONSTRAINT FK_GAMES_1 FOREIGN KEY (BIOSOF) REFERENCES GAMES (GAMEID)
  USING INDEX INTEG_G_BIOSOF;
ALTER TABLE GAMES ADD CONSTRAINT INTEG_G_CLONEOF FOREIGN KEY (CLONEOF) REFERENCES GAMES (GAMEID);
ALTER TABLE GAMES ADD CONSTRAINT INTEG_G_ROMOF FOREIGN KEY (ROMOF) REFERENCES GAMES (GAMEID);
ALTER TABLE GAMES ADD CONSTRAINT INTEG_G_SAMPLEOF FOREIGN KEY (SAMPLEOF) REFERENCES GAMES (GAMEID);
ALTER TABLE GAMESINFO ADD CONSTRAINT INTEG_GI_GAMEID FOREIGN KEY (GAMEID) REFERENCES GAMES (GAMEID);
ALTER TABLE RELEASES ADD CONSTRAINT FK_RELEASES_1 FOREIGN KEY (GAMEID) REFERENCES GAMES (GAMEID);
ALTER TABLE RELEASES ADD CONSTRAINT FK_RELEASES_2 FOREIGN KEY (REGIONID) REFERENCES REGIONS (REGIONID);
ALTER TABLE RELEASES ADD CONSTRAINT FK_RELEASES_3 FOREIGN KEY (LANGUAGEID) REFERENCES LANGUAGES (LANGUAGEID);
ALTER TABLE ROMS ADD CONSTRAINT INTEG_R_GAMEIDENT FOREIGN KEY (GAMEIDENT) REFERENCES GAMES (GAMEID);


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX DATAFILETMP_IDX1 ON DATAFILETMP (GAMENAME);
CREATE INDEX FILEROMS_IDX1 ON FILEROMS (ROMSIG);
ALTER INDEX FILEROMS_IDX1 INACTIVE;
CREATE UNIQUE INDEX FILEROMS_IDX2 ON FILEROMS (FILEROMID);
CREATE INDEX FILEROMS_IDX3 ON FILEROMS (ROMID);
CREATE INDEX FILEROMS_IDX4 ON FILEROMS (GAMEROMID);
CREATE INDEX FILEROMS_IDX5 ON FILEROMS (FILEID);
CREATE DESCENDING INDEX FILEROMS_IDX6 ON FILEROMS (NEEDREFRESH);
CREATE DESCENDING INDEX FILES_IDX1 ON FILES (NEEDREFRESH);
CREATE INDEX IDXFILES_1 ON FILES (FILENAME, EXTENSION);
CREATE INDEX IDX_FILESTMP_FILENAME ON FILESTMP (FILENAME, EXTENSION);
CREATE INDEX IDX_FILESTMP_ROMNAME ON FILESTMP (ROMNAME);
CREATE INDEX IDX_FILESTMP_ROMSIG ON FILESTMP (ROMSIG, ROMSIZE);
CREATE INDEX IDX_GAMEROMS_DUMPSTATUS ON GAMEROMS (DUMPSTATUS);
CREATE INDEX IDX_GR_UPPER_ROMNAME ON GAMEROMS COMPUTED BY (upper(romname));
CREATE INDEX IDX_GAMES_NAME ON GAMES (NAME);
CREATE INDEX IDX_GAMES_UPPERNAME ON GAMES COMPUTED BY (upper(name));
CREATE INDEX IDX_ROMS_SHA1 ON ROMS (SHA1);
CREATE INDEX IDX_ROMS_SIGVALUE ON ROMS (SIGVALUE, ROMSIZE);
CREATE INDEX TMP_IDX1 ON TMP (ID1);
CREATE INDEX TMP_IDX2 ON TMP (ID2);


/******************************************************************************/
/***                           Stored Procedures                            ***/
/******************************************************************************/


SET TERM ^ ;

ALTER PROCEDURE LIST_MERGE (
    SRCFILEID INTEGER,
    DSTFILEID INTEGER)
RETURNS (
    SRCROMNAME VARCHAR(241),
    DSTROMNAME VARCHAR(241),
    GAMEROMID INTEGER,
    PLUGINEXT VARCHAR(9),
    ROMSIG VARCHAR(48),
    ROMSIZE DOUBLE PRECISION,
    SRCROMID INTEGER)
AS
declare variable KEEPROMFORMAT varchar(5);
declare variable FILEROMID integer;
begin
  --copy unique roms from src file to dst file. It is not present in dest because
  --it is unique
  for
    select fileromid
    from fileroms fr
    where fileid=:SRCFILEID
    and fr.romid in (
    select romid from v_availability va where va.nbromsfound =1)
    and fileromid is not null
    into :fileromid
    do begin
      select frsrc.romfullname as srcromname,grdst.romname||frsrc.pluginext as dstromname,
      frsrc.gameromid,frsrc.pluginext,frsrc.romsig,frsrc.romsize,frsrc.romid as srcromid
      from fileroms frsrc
      left join gameroms grdst on grdst.gameromid = frsrc.gameromid
      where frsrc.fileromid = :fileromid
      into :srcromname,:dstromname,:gameromid, :pluginext,:romsig,:romsize,:srcromid;

      suspend;
    end
  end^


ALTER PROCEDURE P_ADDFILE (
    PART INTEGER)
AS
begin
  if (part = 1) then begin
    execute procedure p_rebuild_idx;
    execute procedure P_FR_UPDATEROMID; --87000 21s
    execute procedure p_rebuild_idx;
    execute procedure P_F_UPDATEGAMEID; --9000 2s
    execute procedure p_rebuild_idx;
  end
  else if (part = 2) then begin
    execute procedure P_FR_UPDATEGAMEROMID;-- 90000 28s
    execute procedure p_rebuild_idx;
  end
  else if (part = 3) then begin
    execute procedure P_F_UPDATEFILETYPE; -- 18000 4s
    execute procedure p_updategamesinfo;
    execute procedure p_rebuild_idx;
    execute procedure P_FR_UPDATEEXPNAME; --80000 18s, must occur before f_updateexpname
    execute procedure P_F_UPDATEEXPNAME; --8924 2s, must occur after fr_updateexpname
    execute procedure p_rebuild_idx;
  end
  else if (part = 4) then begin
    execute procedure P_FR_UPDATESTATUS; -- 128000 15s
  end
  else if (part = 5) then begin
    execute procedure P_F_UPDATESTATUS; -- 9000 1.5s
    execute procedure P_FR_ADDMISSING; --102000 12s
    execute procedure p_rebuild_idx;
  end
  else if (part = 6) then begin
    execute procedure P_FR_UPDATESTATUSLVL; --132000 17s
    execute procedure p_rebuild_idx;

    update FILES f set f.needrefresh = 0 where f.needrefresh = 1 ;
    update FILEROMS fr set fr.needrefresh = 0  where fr.needrefresh = 1;--84000, 8s

  end
end^


ALTER PROCEDURE P_CHANGEMODE
AS
declare variable rommode char(1);
declare variable biosmode char(1);
begin
  --set main games according to releases settings
  execute procedure p_rl_updatereleases;

  --update filegames
  execute procedure p_fg_updatefilegames;

  --update roms.gameident
  execute procedure p_r_updategameident;

  --update status, also update filetype (can change between bios and roms )
  execute procedure p_resetfiles;

  --update files.status
  --execute procedure p_refreshstatus;

end^


ALTER PROCEDURE P_COPY_FILEROM (
    SRCFILEROMID INTEGER,
    DSTFILEID INTEGER,
    NEWFILEROMNAME VARCHAR(241),
    NEWFILEROMDATE VARCHAR(20))
AS
declare variable FILEROMID integer;
begin
--If default date is sent, convert to null
if (:newfileromdate = '01/01/0001 00:00:00') then newfileromdate = null;

  select gen_id(genfileromid, 1) from rdb$database into :fileromid;

--create filerom
  insert into fileroms(fileromid,fileid,romid,rompath,romname,pluginext,romsize,romsig,romdate)
  select :fileromid,:dstfileid,fr.romid,coalesce(fr.rompath,''),:newfileromname
  ,fr.pluginext ,fr.romsize,fr.romsig,coalesce(:newfileromdate,fr.romdate)
  from fileroms fr
  where fr.fileromid = :srcfileromid;

  execute procedure p_fr_updateexpname;
  execute procedure p_fr_updategameromid;
  execute procedure p_fr_updatestatus;
  execute procedure p_fr_updatestatuslvl;
  update fileroms fr set fr.needrefresh = 0 where fr.fileromid = :fileromid;

  update files f set f.needrefresh = 1 where f.fileid = :dstfileid;
  execute procedure p_f_updatestatus;
  update files f set f.needrefresh = 0 where f.fileid = :dstfileid;
end^


ALTER PROCEDURE P_CREATEVIEWSINDEXES
AS
begin
update RDB$RELATION_FIELDS set RDB$NULL_FLAG = 1
where (RDB$FIELD_NAME = 'FILEID') and (RDB$RELATION_NAME = 'V_FILESLIST');

update RDB$RELATION_FIELDS set RDB$NULL_FLAG = 1
where (RDB$FIELD_NAME = 'EFID') and (RDB$RELATION_NAME = 'V_FILEROMSLIST');

update RDB$RELATION_FIELDS set RDB$NULL_FLAG = 1
where (RDB$FIELD_NAME = 'ROMID') and (RDB$RELATION_NAME = 'V_AVAILABILITY');

update RDB$RELATION_FIELDS set RDB$NULL_FLAG = 1
where (RDB$FIELD_NAME = 'GAMEROMID') and (RDB$RELATION_NAME = 'V_GAMEROMSLIST');

update RDB$RELATION_FIELDS set RDB$NULL_FLAG = 1
where (RDB$FIELD_NAME = 'SRCFILEID') and (RDB$RELATION_NAME = 'V_FILES_TO_RENAME');

update RDB$RELATION_FIELDS set RDB$NULL_FLAG = 1 where (RDB$FIELD_NAME = 'FILEROMID') and (RDB$RELATION_NAME = 'V_ROMS_TO_RENAME');

update RDB$RELATION_FIELDS set RDB$NULL_FLAG = 1 where (RDB$FIELD_NAME = 'FILEROMID') and (RDB$RELATION_NAME = 'V_FILEROMS_TO_REMOVE');
update RDB$RELATION_FIELDS set RDB$NULL_FLAG = 1 where (RDB$FIELD_NAME = 'FILEID') and (RDB$RELATION_NAME = 'V_FILEROMS_TO_REMOVE');

update RDB$RELATION_FIELDS set RDB$NULL_FLAG = 1 where (RDB$FIELD_NAME = 'FILEID') and (RDB$RELATION_NAME = 'V_FILES_TO_REMOVE');

update RDB$RELATION_FIELDS set RDB$NULL_FLAG = 1 where (RDB$FIELD_NAME = 'EFID') and (RDB$RELATION_NAME = 'V_DESTFILESFORROMS');

end^


ALTER PROCEDURE P_DF_FIXDATAS
AS
declare variable sampleof varchar(255);
begin
  /*
  Sometimes in mame, some games define a 'sampleof' file which does not exist in the dat
  ex: mame 0.141 : <game name="eforest" sourcefile="aristmk4.c" sampleof="aristmk4">
  aristmk4 is not a game and is not defined.
  We add these 'games' in the datafiletmp table.
  */

  --Create games for 'sampleof' not in games list
  for
        --find all sampleof not defined
        select distinct d1.sampleof
        from datafiletmp d1
        left join datafiletmp d2 on d2.gamename = d1.sampleof
        where d1.sampleof <>''
        and d2.gamename is null
  into :sampleof do
  begin
        --Create new games for sampleof
        insert into datafiletmp (gamename,cloneof,romof,sampleof, gamedesc,gameyear,manufacturer,romextension,romname,mergeromname,romsize,sigvalue,sha1,romtype,romstatus,selected)
        select d.sampleof,'','','',d.sampleof || ' samples',min(d.gameyear),'',d.romextension,d.romname,'',d.romsize,d.sigvalue,d.sha1,d.romtype,d.romstatus,'Y'
        from datafiletmp d
        where d.sampleof =:sampleof
        and romtype = 'sample'
        group by d.sampleof,d.romextension,d.romname,d.romsize,d.sigvalue,d.sha1,d.romtype,d.romstatus;
  end
end^


ALTER PROCEDURE P_EXPORT_DB
AS
begin

/*
--recreate tmp tables for export to another db
delete from datafiletmp;

--transfert all: 15 s
insert into datafiletmp (GAMENAME,CLONEOF,ROMOF,SAMPLEOF,GAMEDESC,GAMEYEAR,MANUFACTURER,ROMNAME,MERGEROMNAME,ROMSIZE,SIGVALUE,SHA1,ROMTYPE,ROMSTATUS)
select g.name,coalesce(g.cloneof,''),coalesce(g.romof,''),coalesce(g.sampleof,''),coalesce(g.description,''),
coalesce(g.gameyear,''),coalesce(g.manufacturer,''),gr.romname,coalesce(gr.rommerge,''),r.romsize,coalesce(r.sigvalue,''),coalesce(r.sha1,''),gr.romtype,gr.dumpstatus
from games g
join gameroms gr on gr.gameid = g.gameid
join roms r on r.romid = gr.romid;

--replace id by cloneof: 4s
update datafiletmp d
set cloneof = (select name from games g where g.gameid = d.cloneof)
where cloneof <> '';

--replace id by romof: 5s
update datafiletmp d
set romof = (select name from games g where g.gameid = d.romof)
where romof <> '';

--replace id by sampleof: 1s
update datafiletmp d
set sampleof = (select name from games g where g.gameid = d.sampleof)
where sampleof <> '';

--replace id by mergeromname: 4s
update datafiletmp d
set mergeromname = (select gr.romname from gameroms gr where gr.gameromid =d.mergeromname)
where mergeromname <> '';
*/
--export files
delete from filestmp;
insert into filestmp(PATHNAME,PATHTYPE,FILENAME,extension,fileformat,FILEDATE,FILESIZE,FILETYPE,rompath,ROMNAME,PLUGINEXT,ROMDATE,ROMSIZE,ROMSIG,VOLUMENAME,VOLUMESERIAL,ERROR,DRIVE,DRIVETYPE,ZIPCOMMENT,unc)
select p.shortpathname,p.pathtype,f.filename,f.extension,f.fileformat,f.filedate,f.filesize,f.filetype,
coalesce(fr.rompath,''),
coalesce(fr.romname,''),
coalesce(fr.pluginext,''),
fr.romdate,
coalesce(fr.romsize,-1),
coalesce(fr.romsig,''),p.volumename,p.volumeserial,
f.error,p.drive,p.drivetype,
coalesce(f.zipcomment,''),p.unc
from paths p
left join files f on f.pathid = p.pathid
left join fileroms fr on fr.fileid = f.fileid;

end^


ALTER PROCEDURE P_F_UPDATECASE
AS
begin
  update files f set f.needrefresh = 1;
  
  execute procedure P_F_updateexpname;
  execute procedure P_F_updatestatus;
  
  update files f set f.needrefresh = 0;
end^


ALTER PROCEDURE P_F_UPDATEEXPNAME
AS
declare variable SETSCASE char(10);
begin

  --Build tmp table
  delete from tmp;
  insert into tmp (id1,str1) --fileid, name+ ext
  select distinct f.fileid, g.name|| lower(f.extension)
  from FILES f
  join games g on g.gameid = f.gameid
  where f.needrefresh = 1;

  --for single files, use the fr.expromname
  update tmp t
  set t.str1 = (select lower(fr.expromname) from FILEROMS fr where fr.fileid = t.id1)
  where t.id1 in (select f.fileid from FILES f where f.fileformat = 'file');
  
  --tmp now have all expected filename for fileid in database case
  
select val from config c where c.param = 'setscase' into :setscase;
  -- if 'datatabse' case, we must restore all names
  if (setscase = 'database') then begin
    update FILES f
    set f.expfilename = (select t.str1 from tmp t where t.id1 = f.fileid)
    where f.fileid in (select id1 from tmp);
  end
  else 
  if (setscase = 'lowercase') then begin
    update FILES f
    set f.expfilename = (select lower( t.str1) from tmp t where t.id1 = f.fileid)
    where f.fileid in (select id1 from tmp);
  end
  else 
  if (setscase = 'uppercase') then begin
    update FILES f
    set f.expfilename = (select upper(t.str1) from tmp t where t.id1 = f.fileid)
    where f.fileid in (select id1 from tmp);
  end

  -- update FILES f set f.expfilename = upper(f.expfilename) where f.fileid in (select id1 from tmp);
  
  delete from tmp;

end^


ALTER PROCEDURE P_F_UPDATEFILETYPE
AS
declare variable SAMPLEMODE char(10);
declare variable NBITEMS integer;
declare variable FILEID integer;
begin
  --Files.filetype = roms/samples/bios/disk/unknown

  select count(*) from FILES f where f.needrefresh = 1
   into :nbitems;
  if (nbitems =0) then exit;

  update FILES f set filetype = null where f.needrefresh = 1;

  select lower(val) from config where param = 'samplemode'
  into :samplemode;

--sample types are only available in sample split mode
if (:samplemode = 's') then begin
  --tmp list fileid,pathid,romtype
  delete from tmp;
  insert into tmp(id1,id2,str1)
  select distinct f.fileid,f.pathid, gr.romtype
  from FILES f
  join FILEROMS fr on fr.fileid =f.fileid
  join gameroms gr on gr.gameromid=fr.gameromid
  join games g on g.sampleof = f.gameid--use sample of to limit to samples
  where f.needrefresh = 1;

  --files with only samples are 'samples'
  update FILES
  set filetype = 'samples'
  where fileid in
  (
    --files with only samples
    select id1
    from tmp t
    where id1 not in (select id1 from tmp t where str1 <> 'sample')
  );

  --any files with samples in sample path are 'samples'
  update FILES 
  set filetype = 'samples'
  where fileid in
  (
    select id1 from tmp t
  );
end

  --add remaining files to process in tmp
  delete from tmp;
  insert into tmp(id1)
  select f.fileid
  from FILES f
  where f.needrefresh = 1 and f.filetype is null;

for
  select distinct fr.fileid
  from tmp t
  join FILES f on f.fileid=t.id1
  join FILEROMS fr on fr.fileid=f.fileid
  join gameroms gr on gr.gameromid=fr.gameromid
  where gr.romtype = 'disk' and f.filetype is null
into :fileid do
begin
  update FILES set filetype = 'disk' where fileid = :fileid;
end

--bios files
update FILES
set filetype = 'bios'
where fileid in
(
select distinct f.fileid
from tmp t
join FILES f on f.fileid=t.id1
join games g on g.gameid=f.gameid
where g.gametype = 'bios'
)
and filetype is null;

--unknown
update FILES
set filetype = 'unknown'
where gameid is null
and fileid in (select id1 from tmp);

--remain is roms
update FILES
set filetype = 'roms'
where filetype is null
and fileid in (select id1 from tmp);

end^


ALTER PROCEDURE P_F_UPDATEGAMEID
AS
declare variable FILEID integer;
declare variable GAMEID integer;
declare variable FILEIDTMP integer;
declare variable NBNEW integer;
declare variable NAME char(250);
declare variable SETSCASE char(10);
begin
  -- update gameid and expfilename when gameid is null (needrefresh is not used here)
  --fr.romid must be set
  
  --prepare datas
  --copy fileid to pocess in tmp table
  delete from tmp;
  insert into tmp (id1)
  select f.fileid
  from FILES f
  where f.needrefresh = 1;

  --set gameid to null
  update FILES set gameid = null, expfilename = null where needrefresh = 1 and gameid is not null;
  
  --filename matching is preferred method, before gameident.
  
  --filename match gamename: files.filename > g.name > files.gameid
  for
    select distinct f.fileid,g.gameid
    from tmp t
    join FILES f on f.fileid = t.id1
    join games g on upper(g.name) = upper(f.filename)
    where f.gameid is null
    into :fileid,:gameid do
  begin
    update FILES set gameid = :gameid where fileid = :fileid;
  end

  --En mode fullmerge, seuls les main games sont representes: converti clones -> setid
  for
    select distinct f.fileid,g.setid
    from tmp t
    join FILES f on f.fileid = t.id1
    join games g on g.gameid = f.gameid
    join config c on c.param='rommode' and c.val='m'
    where f.gameid is null and g.setid <> g.gameid and g.gameid is not null
    into :fileid,:gameid do
  begin
    update FILES set gameid = :gameid where fileid = :fileid;
  end

  --gameident: FILES >> fr >> r > min(r.gameident) > files.gameid
  for
    select distinct f.fileid, min(r.gameident)
    from tmp t
    join FILES f on f.fileid = t.id1
    join FILEROMS fr on fr.fileid = f.fileid
    join roms r on r.romid = fr.romid
    where r.gameident is not null and f.gameid is null
    group by f.fileid
    into :fileid,:gameid do
  begin
    update FILES set gameid = :gameid where fileid = :fileid;
  end

  --unknown name, no roms with gameident: bios
  --f >> fr >> bios gr >> fg > min(bios game) > f.gameid
  for
    select distinct f.fileid, min(g.gameid)
    from tmp t
    join FILES f on f.fileid = t.id1
    join FILEROMS fr on (fr.fileid = f.fileid)
    join gameroms gr on gr.romid=fr.romid and gr.romtype='bios'
    join filegames fg on fg.gameromid = gr.gameromid
    join games g on g.gameid=fg.gameid and g.gametype = 'bios'
    where f.gameid is null
    group by f.fileid
    into :fileid,:gameid do
  begin
    update FILES set gameid = :gameid where fileid = :fileid;
  end

  -- in split samplemode, all FILES with samples in the sample path must be linked to the sampleof game, not clones
  for
    select distinct f.fileid, g.sampleof
    from tmp t
    join FILES f on f.fileid = t.id1
    join paths p on f.pathid = p.pathid and p.pathtype = 'samples'
    join FILEROMS fr on fr.fileid = f.fileid
    join gameroms gr on upper(gr.romname) = upper(fr.romfullname) and gr.romtype = 'sample'
    join games g on g.gameid = f.gameid
    join config c on c.param='samplemode' and c.val='s'
    where g.setid <> f.gameid and f.gameid is null
  into :fileid,:gameid do
  begin
    update FILES set gameid = :gameid where fileid = :fileid;
  end

  --unknown name, no roms with gameident and no bios: take game of first rom
  --f >> fr >> gr >> fg > min(gameid) > f.gameid
  for
    select f.fileid
    from tmp t
    join FILES f on f.fileid = t.id1
    where f.gameid is null
  into :fileid do
  begin
     update FILES f
     set f.gameid =
     (
      --get first rom
      select first 1 fg.gameid
      from FILEROMS fr
      join gameroms gr on gr.romid=fr.romid
      join filegames fg on fg.gameromid = gr.gameromid
      where fr.fileid = :fileid
      )
      where f.fileid = :fileid;
  end

  delete from tmp;
end^


ALTER PROCEDURE P_F_UPDATESTATUS
AS
declare variable SAMPLEMODE varchar(1);
declare variable NBITEMS integer;
declare variable FILEID integer;
declare variable KEEPROMFORMAT varchar(5);
begin
 --list all FILES to process (needrefresh) in tmp file.
  delete from tmp;
  --9000 0.3s
  insert into tmp(id1)
  select f.fileid from FILES f where f.needrefresh = 1;

  select count(*) from tmp into :nbitems;
  if (nbitems =0) then exit;

--'unknown'
update FILES f set status = 'unknown'
where needrefresh = 1
and fileid in 
(
  select fileid
  from tmp t
  join FILES f on f.fileid = t.id1
  where gameid is null or f.filetype = 'unknown'
);

--'wrong path'
select lower(val) from config where param = 'samplemode' into :samplemode;
if (:samplemode = 's') then begin

  --wrong path: samples in roms path or roms in samples paths (split sample mode only)
  update FILES set status = 'wrong path'
  where needrefresh = 1
  and fileid in
  (
  select fileid
  from tmp t
  join FILES f on f.fileid = t.id1
  join paths p on p.pathid = f.pathid
  where (filetype = 'samples' and pathtype <> 'samples')
     or (filetype <>'samples' and pathtype = 'samples')
);
end

--duplicated (single file only)
--update FILES f set status = 'duplicated'
--where status is null and f.FILEFORMAT  = 'file'
--and f.fileid in (select fileid from fileroms where status = 'duplicated') ;

--'folder needed': bad chd location. chd is as single file. It should be in a folder
update FILES 
set status = 'folder needed' 
where needrefresh = 1
and fileid in
  (
  select fileid
  from tmp t
  join FILES f on f.fileid = t.id1
  where filetype = 'disk' and FILEFORMAT = 'file'
);

--'bad name'
--1500 0.2s
update FILES f 
set status = 'bad name'
where needrefresh = 1
and fileid in
  (
  select fileid
  from tmp t
  join FILES f on f.fileid = t.id1
  where f.expfilename <> f.filename||f.extension
);

--error : error field has an error message
update FILES f
set status = 'error'
where needrefresh = 1
and fileid in
(
  select fileid
  from tmp t
  join FILES f on f.fileid = t.id1
  where error <> ''
);

--'ok'
--7300 3s
update FILES f set status = 'ok'
where needrefresh = 1
and fileid in
  (
  select fileid
  from tmp t
  join FILES f on f.fileid = t.id1
  where status is null
  );
  
  delete from tmp;

end^


ALTER PROCEDURE P_FG_UPDATEFILEGAMES
AS
declare variable ROMMODE char(1);
declare variable BIOSMODE char(1);
declare variable GAMEID integer;
declare variable NEWGAMEID integer;
declare variable GAMEROMID integer;
declare variable SAMPLEMODE char(1);
declare variable ROMNAME varchar(200);
declare variable ROMID integer;
declare variable KEEPMAIN varchar(6);
begin
--get the mode from config table
  select val
  from config
  where config.param = 'rommode'
  into :rommode;

  select val
  from config
  where config.param = 'biosmode'
  into :biosmode;

  select val
  from config
  where config.param = 'samplemode'
  into :samplemode;

  select val
  from config
  where config.param = 'keepmain'
  into :keepmain;

-- clear filegames
  delete from filegames;

-------- ROMS
--optimization: only change roms depending of previous rommode

  if (rommode = 'u') then
    -- rommode unmerged : all roms in clones
    insert into filegames(pathtype, gameid, gameromid)
    select 'roms', gr.gameid,gr.gameromid
    from gameroms gr
    where gr.romtype = 'rom';
  
  if (rommode = 's') then
    -- rommode split : specific roms in clones
    insert into filegames(pathtype,gameid, gameromid)
    select 'roms',gr.gameid,gr.gameromid
    from gameroms gr
    where gr.rommerge is null and gr.romtype = 'rom';

  if (rommode = 'm') then

    --insert all roms not merged in the 'setid' game
    insert into filegames(pathtype,gameid, gameromid)
    select 'roms',g.setid,gr.gameromid as gameromid
    from games g
    join gameroms gr on (g.gameid= gr.gameid) and gr.romtype = 'rom'
    and gr.rommerge is null;

    --remove duplicated roms (same game, romname, romid)
    for
      --all duplicated roms
      select fg.gameid,gr.romname,gr.romid
      from filegames fg
      join gameroms gr on (fg.gameromid= gr.gameromid)
      group by fg.gameid,gr.romname,gr.romid
      having count(gr.gameromid) >1
      into :gameid,:romname ,:romid
    do
      for --each dup rom
        --keep only one rom
        select skip 1 fg.gameid,fg.gameromid
        from filegames fg
        join gameroms gr on fg.gameid = :gameid and gr.gameromid = fg.gameromid and gr.romname = :romname and gr.romid = :romid
        join games g on g.gameid = gr.gameid
        order by case when :gameid = g.gameid then ' ' else '0' end,g.gameid
        into :gameid,:gameromid
      do delete from filegames where gameid = :gameid and gameromid = :gameromid;

    --remaining dup roms have same game, name, but diff crc
    --we replace the main gameid by the clone gameid
    for
      select fg.gameid, fg.gameromid,gr.gameid as newgameid
      from filegames fg
      join (
            --all duplicated roms with <> crc
            select fg.gameid,gr.romname
            from filegames fg
            join gameroms gr on (fg.gameromid= gr.gameromid)
            group by fg.gameid,gr.romname
            having count(gr.gameromid) >1
           ) t on t.gameid = fg.gameid
      join gameroms gr on gr.gameromid = fg.gameromid and gr.romname = t.romname
      join roms r on r.romid = gr.romid
      where fg.gameid <> gr.gameid
      into :gameid,:gameromid,:newgameid
    do update filegames set gameid = :newgameid where gameid = :gameid and gameromid = :gameromid;

-------- BIOS

  if (biosmode = 'u') then
    --biosmode unmerged : bios in mains and clones, not in bios file
    if (rommode = 'm') then --no clones, only main
      insert into filegames(pathtype,gameid, gameromid)
      select 'roms',gr.gameid,gr.gameromid
      from games g
      join gameroms gr on gr.gameid=g.gameid and g.gametype <> 'clone'
      where gr.romtype = 'bios' and g.gametype <> 'bios';
     else --mains and clones
      insert into filegames(gameid, gameromid)
      select gr.gameid,gr.gameromid
      from gameroms gr
      join games g on g.gameid = gr.gameid
      where gr.romtype = 'bios' and g.gametype <> 'bios';

  if (biosmode = 'm') then
    --biosmode merged : only in main games, not in clones or bios files
    insert into filegames(pathtype,gameid, gameromid)
    select 'roms',gr.gameid,gr.gameromid
    from games g
    join gameroms gr on gr.gameid=g.gameid
    where gr.romtype = 'bios' and g.gametype = 'main';

  --WARNING ! some bios roms are not used anywhere
  --in unmerged and merged mode, the bios file is deleted, and the roms are lost
  --Add these bios files as valid files in these cases
  if ((biosmode = 'u') or ((biosmode = 'm')) ) then
    insert into filegames(pathtype,gameid, gameromid)
    select 'roms',min(gr.gameid) as gameid,min(gr.gameromid) as gameromid
    from gameroms gr
    join games g on g.gameid = gr.gameid
    where gr.romtype = 'bios'
    group by gr.romid
    having count(gr.gameid) = 1;


  if (biosmode = 's') then
    --biosmode split : bios roms only in bios file, not in games or clone
    insert into filegames(pathtype,gameid, gameromid)
    select 'roms',gr.gameid,gr.gameromid
    from games g
    join gameroms gr on gr.gameid=g.gameid
    where gr.romtype = 'bios' and g.gametype = 'bios';
    

-------- SAMPLES

  if ((samplemode = 'u') and (rommode <> 'm')) then begin --samples in games and clone
    insert into filegames(pathtype,gameid, gameromid)
    select 'roms',g.gameid,min(gr.gameromid)
    from games g
    join gameroms gr on gr.gameid=g.gameid
    join games gm on gm.gameid = g.setid
    where gr.romtype = 'sample'
    group by g.gameid,gr.romname;
    --no more pathtype 'samples'
    --ne pas faire ca ici
    update paths set pathtype = 'roms' where pathtype = 'samples';
   end
   else begin
  --split mode = merge mode because the filename is the same, only the path makes the difference
  --samples in sampleof games (merge and split)

    --game 'sampleof' contains samples
    insert into filegames(pathtype,gameid, gameromid)
    select distinct 'samples', g.sampleof as gameid,grs.gameromid
    from gameroms gr
    join games g on g.gameid = gr.gameid
    join gameroms grs on grs.gameid = g.sampleof and grs.gameromid = gr.gameromid --use the gameromid of sampleof gameid
    WHERE gr.romtype = 'sample';

    --game sample of does not contain all samples ! (ex: invaders does not use any samples, but is sampleof many games and clones)
    --link samples with clone of sampleof
/*    insert into filegames(gameid, gameromid)
    select g.sampleof, min(gr.gameromid)
    from gameroms gr
    join games g on g.gameid = gr.gameid and g.cloneof = g.sampleof
    left join --not already in fg
    (select fg.gameid,fg.gameromid,gr.romname from filegames fg join gameroms gr on gr.gameromid = fg.gameromid and gr.romtype = 'sample')
    fg on fg.gameid = g.sampleof and fg.romname = gr.romname
    where gr.romtype = 'sample' and fg.gameromid is null and g.sampleof is not null
    group by g.sampleof,gr.romname;
    --link samples with other clone (??)
    insert into filegames
    select g.sampleof, min(gr.gameromid)
    from gameroms gr
    join games g on g.gameid = gr.gameid
    left join
    (select fg.gameid,fg.gameromid,gr.romname from filegames fg join gameroms gr on gr.gameromid = fg.gameromid and gr.romtype = 'sample')
    fg on fg.gameid = g.sampleof and fg.romname = gr.romname
    where gr.romtype = 'sample' and fg.gameromid is null and g.sampleof is not null
    group by g.sampleof,gr.romname;
*/
end
-------- other files : disks

  insert into filegames(pathtype, gameid, gameromid)
  select 'roms', gr.gameid,gr.gameromid
  from gameroms gr
  where gr.romtype not in ('rom','bios','sample');

  -------- remove clones in keepmain mode
  if (keepmain = 'true') then begin
    for
      select fg.gameid
      from filegames fg
      join games g on g.gameid = fg.gameid and g.gametype = 'clone'
      into :gameid
    do delete from filegames where gameid = :gameid;
  end

  --enlever la maj de path ligne 149
  --enlever
  execute procedure p_initgamesinfo;

  --enlever
  --update games.name
  execute procedure p_g_updategamename;

end^


ALTER PROCEDURE P_FILTERGAMES
AS
declare variable keepmain varchar(6);
begin
--keep only main games if keepmain is true
  select val from config where param='keepmain' into :keepmain;
  if (:keepmain = 'true') then begin
    --remove 'releases' clones records
    delete from releases where gameid in (select gameid from games where gametype = 'clone');
    delete from games where gametype = 'clone';
  end
end^


ALTER PROCEDURE P_FR_ADDMISSING
AS
declare variable SAMPLEMODE varchar(10);
begin
--Add ISMISSING fileroms for fileid having needrefresh set to 1 in fileroms table according to filegames table

--add missing
/*insert into FILEROMS
select
fr.fileromid,
f.fileid,
gr.gameromid,
fg.romname,
'',
'',
null,
null,
null,
null,
null,
null,
null,
null,
null,
case when av.nbromsfound > 1 then 1 else 0 end,
0,
0,
null,
0
from filegames fg
join games g on g.gameid= fg.gameid
join gameroms gr on gr.gameromid= fg.gameromid
join roms r on r.romid= gr.romid
join FILES f on f.gameid= fg.gameid
left join FILEROMS fr on fr.gameromid = fg.gameromid and fr.fileid = f.fileid
left join FILESDUMPSTATUS FD on FD.GAMEID = fG.GAMEID
left join v_availability av on av.romid = gr.romid
where
fr.fileromid is null;
*/
--get the sample mode from config table
  select val from config where config.param = 'samplemode' into :samplemode;

  --clean table: delete current missing
  delete from FILEROMS fr where fr.needrefresh = 1 and fr.ISMISSING = 1;

  if (samplemode = 's') then begin

    --NOT TESTED

    --split sample mode: samples and roms are in separate files.
    -- We use the 'pathtype' info from filegames to identify the correct fileid (rom or sample).
    insert into FILEROMS (efid, fileid, gameromid, romid, romtype, expromname, isavail,statuslvl)
    select gen_id(genfilerompk, 1), f.fileid,gr.gameromid,gr.romid,gr.romtype, gr.romname,case when av.nbromsfound >0 then 1 else 0 end,20
    from FILES f
    join paths p on p.pathid = f.pathid
    join filegames fg on fg.gameid = f.gameid and fg.pathtype = p.pathtype and f.error = ''
    join gameroms gr on gr.gameromid = fg.gameromid
    left join v_availability av on av.romid = gr.romid
    where f.needrefresh = 1 and f.FILEFORMAT  <> 'file';
  end
  else begin
    --unmerged or merged sample mode: no samples paths
    --102000 20s
    insert into FILEROMS (efid, fileid, gameromid, romid, romtype, expromname,romsize,romsig, isavail,statuslvl)
    select gen_id(genfilerompk, 1),f.fileid,gr.gameromid,gr.romid,gr.romtype,gr.romname,r.romsize,
    coalesce(r.sigvalue,r.sha1), case when av.nbromsfound >0 then 1 else 0 end,20
    from FILES f
    join filegames fg on fg.gameid = f.gameid and f.error = ''
    join gameroms gr on gr.gameromid = fg.gameromid
    join roms r on r.romid = gr.romid
    left join FILEROMS fr on fr.fileid = f.fileid and fr.gameromid = fg.gameromid
    left join v_availability av on av.romid = gr.romid
    where fr.fileromid is null
    and f.needrefresh = 1 and
    f.FILEFORMAT  <> 'file';
  end

  --update ISMISSING status

end^


ALTER PROCEDURE P_FR_UPDATECASE
AS
begin
  update fileroms fr set fr.needrefresh = 1;

  execute procedure P_FR_updateexpname;
  execute procedure P_FR_updatestatuslvl;
  
  update fileroms fr set fr.needrefresh = 0;
end^


ALTER PROCEDURE P_FR_UPDATEEXPNAME
AS
declare variable ROMNAME char(255);
declare variable GAMEROMID integer;
declare variable ROMSCASE char(10);
declare variable FILEROMID integer;
begin
  --add gameromid to process in tmp
  delete from tmp;

  --80200 5s
  insert into tmp (id1,str1,str2) --fileromid, name, ext
  select distinct fr.fileromid, gr.shortname, lower(coalesce(fr.pluginext,gr.extension))
  from FILEROMS fr
  join gameroms gr on gr.gameromid = fr.gameromid
  where fr.needrefresh = 1 and fr.fileromid is not null;

  --add expected rom name in 'database' romscase
  --80200 15s
  update FILEROMS fr
  set fr.expromname = (select t.str1 || t.str2 from tmp t where fr.fileromid = t.id1)
  where fr.needrefresh = 1 and fr.fileromid in (select id1 from tmp) ;

  -- We can have a rom in a clone with the same name as one in another clone, but a different crc !
  -- This is an error in dat, but it works in split or unmerged mode.
  -- In merge mode, we have a collision when we put then together.
  -- We rename the rom (romname -> romname_clonename)   
  -- These roms are listed in daterrors with 'merge collision'
  for
    select fr.fileromid,g.name || '_' || fr.expromname
    from games g
    join gameroms gr on g.gameid= gr.gameid
    join daterrors de on de.gameid = g.setid and gr.romname = de.romname
    join FILEROMS fr on fr.gameromid = gr.gameromid
    join tmp t on t.id1 = fr.fileromid
  into :fileromid,:romname do
  begin
    update FILEROMS fr set fr.expromname = :romname where fr.fileromid = :fileromid;
  end

  select val from config where param = 'romscase' into :romscase;

  --'database' romscase is the default value
  if (romscase = 'uppercase') then begin
    update FILEROMS fr
    set fr.expromname = upper(fr.expromname)
    where fr.needrefresh = 1 and fr.gameromid in (select id1 from tmp);
  end
  if (romscase = 'lowercase') then begin
    update FILEROMS fr
    set fr.expromname = lower(fr.expromname)
    where fr.needrefresh = 1 and fr.gameromid in (select id1 from tmp);
  end

  delete from tmp;
end^


ALTER PROCEDURE P_FR_UPDATEGAMEROMID
AS
declare variable ROMID integer;
declare variable GAMEROMID integer;
declare variable FILEROMID integer;
declare variable GAMEID integer;
declare variable FILEID integer;
declare variable ROMTYPE varchar(10);
begin
  --we know f.gameid and fr.romid

  --before starting, we link some more fileroms to romid.
  --in updateromid, we didn't know the f.gameid. Now we do.
  for
    select fr.fileromid,gr.romid,gr.gameromid, gr.romtype
    from FILEROMS fr
    join FILES f on f.fileid = fr.fileid
    join gameroms gr on upper(fr.romname) = upper(gr.romname)
    and f.gameid = gr.gameid
    join roms r on r.romid = gr.romid
    where fr.needrefresh = 1
    and fr.fileromid is not null
    and fr.romid is null
    and trim(coalesce(fr.romsig,'')) = ''
    into :fileromid,:romid,:gameromid,:romtype do
  begin
   update FILEROMS set gameromid = :gameromid, romid = :romid, romtype = :romtype where fileromid = :fileromid;
  end

  --duplicated roms used in one game

  --several roms with same crc in one game : we must assign only one filerom to only one rom
  --concerned roms are saved in daterrors table with a 'Duplicated roms' comment
  --if the fileroms names match real rom names, we simply do a name match.
  -- crc1, romname1 -> romname1, crc1
  -- crc1, romname2 -> romname2, crc1
  for
    select fr.fileromid, gr.gameromid, gr.romtype
    from daterrors de
    join FILEROMS fr on fr.romid = de.romid
    join FILES f on f.fileid = fr.fileid
    join gameroms gr on gr.romid = fr.romid
    where fr.needrefresh = 1 and fr.fileromid is not null and upper(gr.romname) = upper(fr.romname) and de.COMMENTS = 'Duplicated roms'
    and fr.romid is not null
    into :fileromid,:gameromid,:romtype do
  begin
   update FILEROMS set gameromid = :gameromid, romtype = :romtype where fileromid = :fileromid;
  end

  --for remaining dup fileroms, we can't use the name as it doesn't match
  --we just assign each dup filerom to a different rom.
  -- crc1, newname1 -> romname1, crc1
  -- crc1, newname2 -> romname2, crc1

  for
    select distinct fr.fileid,fr.fileromid, fr.romid, de.gameid--,  fr.fileromid, fr.gameromid, fr.romname
    from daterrors de
    join FILEROMS fr on fr.romid = de.romid
    join FILES f on f.fileid = fr.fileid
    join gameroms gr on gr.romid = fr.romid
    where fr.needrefresh = 1 and fr.fileromid is not null and de.COMMENTS = 'Duplicated roms'
    and fr.romid is not null
    into :fileid, :fileromid, :romid, :gameid do
  begin
    --1er gamerom encore dispo pour un filerom
    for
      select first 1 gr.gameromid, gr.romtype--,gr.romname, fr.fileromid
      from gameroms gr
      left join FILEROMS fr on fr.gameromid = gr.gameromid and fr.fileid = :fileid
      where fr.fileromid is null
      and gr.romid = :romid and gr.gameid = :gameid
      into :gameromid,:romtype do
    begin
     update FILEROMS set gameromid = :gameromid, romtype = :romtype where fileromid = :fileromid;
    end
  end

  --filerom in correct file: f.gameid > fg >> r = fr.romid > gameromid
  for
    select fr.fileromid, gr.gameromid, gr.romtype
    from FILEROMS fr
    join FILES f on f.fileid=fr.fileid
    join filegames fg on fg.gameid = f.gameid
    left join gameroms gr on gr.romid = fr.romid and fg.gameromid = gr.gameromid
    where fr.needrefresh = 1 and fr.fileromid is not null and fr.romid is not null
    and gr.gameromid is not null
    into :fileromid,:gameromid,:romtype do
  begin
   update FILEROMS set gameromid = :gameromid, romtype = :romtype where fileromid = :fileromid;
  end

  --add remaining gameromid to process in tmp
  delete from tmp;
  insert into tmp (id1)
  select fr.fileromid
  from FILEROMS fr
  where fr.needrefresh = 1 and fr.fileromid is not null and fr.gameromid is null;

  --good name and crc, but in wrong file: fr.romid >> gr > gr=fr.romname > gameromid
  for
    select fr.fileromid, gr.gameromid, gr.romtype
    from FILEROMS fr
    join tmp t on t.id1 = fr.fileromid
    join gameroms gr on gr.romid = fr.romid and upper(fr.romname) = upper(gr.romname)
    join filegames fg on fg.gameromid = gr.gameromid
    where fr.romid is not null
    into :fileromid,:gameromid,:romtype do
  begin
   update FILEROMS set gameromid = :gameromid, romtype = :romtype where fileromid = :fileromid;
  end

  --good crc and bad name in wrong file: fr.romid > min(gr) > gameromid
  for
    select fr.fileromid, min(gr.gameromid), min(gr.romtype)
    from FILEROMS fr
    join tmp t on t.id1 = fr.fileromid
    join gameroms gr on gr.romid = fr.romid
    join filegames fg on fg.gameromid = gr.gameromid
    where fr.romid is not null
    group by fr.fileromid
    into :fileromid,:gameromid,:romtype do
  begin
   update FILEROMS set gameromid = :gameromid, romtype = :romtype where fileromid = :fileromid;
  end

  delete from tmp;

end^


ALTER PROCEDURE P_FR_UPDATEROMID
AS
declare variable FILEROMID integer;
declare variable ROMID integer;
declare variable NBITEMS integer;
begin
  --identify fileroms.romid

  --crc+size match rom: fr.crc/size > r > fr.romid
  delete from tmp ;
  insert into tmp(id1,id2)
  select fr.fileromid, r.romid
  from FILEROMS fr
  join roms r on r.sigvalue = fr.romsig and r.romsize = fr.romsize
  where fr.needrefresh = 1 and fr.romsig is not null;

  update FILEROMS fr
  set fr.romid =(select id2 from tmp r where id1 = fr.fileromid)
  where fr.needrefresh = 1 and fr.fileromid in (select id1 from tmp);

  --sha1 match rom: fr.sha1 > r > fr.romid
  for
    select fr.fileromid, r.romid
    from FILEROMS fr
    join roms r on fr.romsig = r.sha1
    where fr.needrefresh = 1 and fr.romsig is not null
    into :fileromid,:romid do
  begin
    update FILEROMS set romid = :romid where fileromid=:fileromid;
  end


  --crc n/a, rom name match(sample/nodump): fr.romname >> gr > r > fr.romid
  --identify using gameident (unique name)
  --Other samples can be different with identical names. we need the gameid to
  --identify them
  for
    select distinct fr.fileromid, gr.romid
    from FILEROMS fr
    join gameroms gr on upper(fr.romname) = upper(gr.romname) --and gr.romtype = 'sample'
    join roms r on r.romid = gr.romid
    where fr.needrefresh = 1 and fr.fileromid is not null and fr.romid is null
    and r.gameident is not null --linked to only one rom
  into :fileromid,:romid do
  begin
    update FILEROMS set romid = :romid where fileromid=:fileromid;
  end

end^


ALTER PROCEDURE P_FR_UPDATESTATUS
AS
declare variable FILEROMID integer;
declare variable SAMPLEMODE varchar(10);
declare variable ROMID integer;
begin
--parentformat:
--84000 - 23s
update FILEROMS fr
set parentformat = (select f.FILEFORMAT  from FILES f where f.fileid = fr.fileid)
where fr.fileid is not null and fr.needrefresh = 1;
/*--romtype:
update FILEROMS fr
set romtype = (select gr.romtype from gameroms gr where gr.gameromid = fr.gameromid)
where fr.gameromid is not null;
*/

--bad folder
--get the sample mode from config table
  select val from config where config.param = 'samplemode' into :samplemode;

  if (samplemode = 's') then begin
    delete from tmp;
    insert into tmp(id1)
    select fr.fileromid
    from FILEROMS fr
    join FILES f on f.fileid = fr.fileid
    join paths p on p.pathid = f.pathid
    where fr.needrefresh = 1 and
    ((fr.romtype = 'sample' and p.pathtype = 'roms')
    or (fr.romtype = 'rom' and p.pathtype = 'samples'));
    
    update FILEROMS
    set ISBADFOLDER = 1
    where needrefresh = 1 and
    fileromid in
    (select t.id1 from tmp t);

    delete from tmp;
  end

  --notused:
  --84000, 7s
  update FILEROMS fr
  set ISNOTUSED = 1
  where needrefresh = 1 and
    fr.fileromid in (
    select fileromid
    from FILES f
    join FILEROMS fr on fr.fileid = f.fileid
    left join filegames fg on f.gameid = fg.gameid and fr.gameromid = fg.gameromid
    where fg.gameromid is null and fr.needrefresh = 1
  );

  --find if new roms have been added (to make them ISAVAIL to other files)
  --and make ISAVAIL to 1

  for
    select distinct va.romid
    from FILEROMS fr
    left join v_availability va on va.romid = fr.romid
    where fr.needrefresh = 1
    and va.nbromsfound > 0
    into :romid
  do begin
    update FILEROMS fr
    set fr.ISAVAIL = 1
    where fr.romid = :romid;
  end
end^


ALTER PROCEDURE P_FR_UPDATESTATUSLVL
AS
begin
  update FILEROMS fr set statuslvl = 30 where fr.needrefresh = 1;--default value (yellow)

  update FILEROMS fr set statuslvl = 20--red
  where fr.needrefresh = 1 and ISMISSING = 1 and ISAVAIL = 0;

  update FILEROMS fr set statuslvl = 40--green
  where fr.needrefresh = 1 and ISMISSING = 0 and ISBADNAME = 0 and ISUNKNOWN = 0 and fr.ISNOTUSED = 0 and fr.isbadfolder=0;

  update FILEROMS fr set statuslvl = 10--error
  where fr.needrefresh = 1 and error <> '';
end^


ALTER PROCEDURE P_G_PREPAREGAMES
AS
declare variable gamename char(200);
declare variable gameid integer;
declare variable keepmain varchar(6);
declare variable cloneid integer;
begin
------------------------
--Fix games datas before
------------------------
--todo: log changes in the error table
--games clone of unknown game : create new set
insert into games (gameid,name,originalname,description,gameyear,manufacturer,gametype)
select gen_id(gengameid, 1),d.cloneof,d.cloneof,d.cloneof,'??','??','main'
from datafiletmp d
left join games g on d.cloneof = g.name
where d.cloneof <> '' and g.gameid is null
group by d.cloneof;

--games merged in unknown romof : create new set
insert into games (gameid,name,originalname,description,gameyear,manufacturer,gametype)
select gen_id(gengameid, 1),d.cloneof,d.cloneof,d.cloneof,'??','??','main'
from datafiletmp d
left join games g on d.romof = g.name
where d.romof <> '' and g.gameid is null and d.mergeromname <> ''
group by d.cloneof;

--games merged in unknown sampleof : create new set
insert into games (gameid,name,originalname,description,gameyear,manufacturer,gametype)
select gen_id(gengameid, 1),d.sampleof,d.sampleof,d.sampleof,'??','??','main'
from datafiletmp d
left join games g on d.sampleof = g.name
where d.sampleof <> '' and g.gameid is null and d.mergeromname <> ''
group by d.sampleof;


-------------------
--Link games parent/child
-------------------

--set cloneof
  for
    select distinct d.gamename,g.gameid
    from datafiletmp d
    join games g on (d.cloneof = g.name)
    where g.name <> ''
    into :gamename,:gameid do
  begin
    update games set cloneof = :gameid where name = :gamename;
  end

--set romof
  for
    select distinct d.gamename,g.gameid
    from datafiletmp d
    join games g on (d.romof = g.name)
    where g.name <> ''
    into :gamename,:gameid do
  begin
    update games set romof = :gameid where name = :gamename;
  end

--set sampleof
  for
    select distinct d.gamename,g.gameid
    from datafiletmp d
    join games g on (d.sampleof = g.name)
    where g.name <> ''
    into :gamename,:gameid do
  begin
    update games set sampleof = :gameid where name = :gamename;
  end

------------------------
--Fix games datas after
------------------------
--if romof not set and cloneof set, set it to cloneof
update games g
set g.romof = g.cloneof
where g.cloneof is not null and g.romof is null;

--clone of clone (must be done before seting setid)
  for
    select g.gameid, c.cloneof
    from games g
    join games c on c.gameid = g.cloneof
    where c.gametype <> 'main'
    into :gameid,:cloneid do
  begin
    update games set cloneof = :cloneid where gameid = :gameid;
  end
--2nd level
  for
    select g.gameid, c.cloneof
    from games g
    join games c on c.gameid = g.cloneof
    where c.gametype <> 'main'
    into :gameid,:cloneid do
  begin
    update games set cloneof = :cloneid where gameid = :gameid;
  end



-----------
--set SetId
update games set setid = coalesce(cloneof,gameid);

--compute indexes
  execute procedure p_rebuild_idx;

end^


ALTER PROCEDURE P_G_TRANSFERTGAMES
AS
begin
--transfert all games
  insert into games (gameid,name,originalname,description,gameyear,manufacturer,gametype)
  select gen_id(gengameid, 1),gamename,gamename
  , min(d.gamedesc),min(d.gameyear),min(d.manufacturer),
  min(case when (d.cloneof = '') then 'main' else 'clone' end)
  from datafiletmp d
  group by d.gamename;

end^


ALTER PROCEDURE P_G_UPDATE
AS
declare variable GAMEID integer;
declare variable ROMSTATUS varchar(10);
declare variable ROMTYPE varchar(10);
begin
  --add sampleof also in sample sets
  update games
  set sampleof = gameid
  where gameid in(
  select distinct gr.gameid
  from gameroms gr
  join games g on g.gameid = gr.gameid and gr.romtype = 'sample'
  where g.sampleof is null
  );
  --some files can be a sample src (sampleof) without using themselves samples.
  --nex update will create the sampleof for these files (not handled above)
  update games
  set sampleof = gameid
  where sampleof is null and gameid in(
  select distinct g.sampleof
  from games g
  join games gs on gs.gameid = g.sampleof and gs.sampleof is null
  where g.sampleof is not null
  );

  -- set games bad status and dump
  for
    select distinct gr.gameid, gr.dumpstatus, gr.romtype
    from gameroms gr
    where gr.dumpstatus <> 'good'
    into :gameid, :romstatus, :romtype
  do begin
    if (romtype = 'rom') then begin
      update games g
      set g.badrom = 1
      where g.gameid = :gameid;
    end
    if (romtype = 'bios') then begin
      update games g
      set g.badbios = 1
      where g.gameid = :gameid;
    end
    if (romtype = 'disk') then begin
      update games g
      set g.baddisk = 1
      where g.gameid = :gameid;
    end
    if (romstatus = 'nodump') then begin
      update games g
      set g.dumpstatus = 'nodump'
      where g.gameid = :gameid and g.dumpstatus <> 'nodump';
    end
    if (romstatus = 'baddump') then begin
      update games g
      set g.dumpstatus = 'baddump'
      where g.gameid = :gameid and g.dumpstatus = 'good';
    end
  end

  update games g set g.nbroms = coalesce((
  select count(*)
  from gameroms gr
  where gr.romtype = 'rom'
  and gr.gameid = g.gameid
  group by gr.gameid
  ),0);
  
  update games g set g.nbbios = coalesce((
  select count(*)
  from gameroms gr
  where gr.romtype = 'bios'
  and gr.gameid = g.gameid
  group by gr.gameid
  ),0)
  where g.biosof is not null;
  
  update games g set g.nbsamples = coalesce((
  select count(*)
  from gameroms gr
  where gr.romtype = 'sample'
  and gr.gameid = g.gameid
  group by gr.gameid
  ),0)
  where g.sampleof is not null;
  
  update games g
  set g.nbdisks = coalesce((
    select count(*)
    from gameroms gr
    where gr.romtype = 'disk'
    and gr.gameid = g.gameid
    group by gr.gameid
  ),0)
  where g.gameid in (
    select gameid
    from gameroms gr
    where gr.romtype = 'disk'
  );

end^


ALTER PROCEDURE P_G_UPDATEBIOS
AS
declare variable gameromid integer;
declare variable gameid integer;
declare variable biosof integer;
begin

--set the gametype for bios. other gametype are set in p_transfertgames
  for
    select distinct gameid
    from games g
    join datafiletmp d on d.gamename = g.name
    where d.romtype = 'bios'
    into :gameid
  do update games g set gametype='bios' where gameid = :gameid;

--update gameroms.romtype (needed to init gamesinfo)
-- bios romtype for merged bios roms
  for
    select gr1.gameromid
    from gameroms gr
    join gameroms gr1 on (gr1.rommerge = gr.gameromid)
    where gr.romtype = 'bios'
    and gr1.romtype <> 'bios'
    into :gameromid
  do update gameroms set romtype = 'bios' where gameromid =:gameromid;

--init games.biosof
  --games with direct link to bios
  for
    select g1.gameid,gb.gameid as biosof
    from games g1
    join games g2 on g2.gameid = g1.romof
    join games gb on gb.gameid = g2.romof and gb.gametype = 'bios'
    where g1.biosof is null
    into :gameid,:biosof
  do update games g set g.biosof = :biosof where g.gameid = :gameid;

  --games with indirect link to bios
  for
    select g1.gameid, gb.gameid as biosof
    from games g1
    join games g2 on g2.gameid = g1.romof
    join games gb on gb.gameid = g2.romof and gb.gametype = 'bios'
    where g1.biosof is null
    into :gameid, :biosof
  do update games g set g.biosof = :biosof where g.gameid = :gameid;

  --Fill 'biosof' for main games using bios (biosof = romof)
  merge into games g
  using (
        select distinct g.gameid,g.romof as biosof
        from gameroms gr
        join games g on g.gameid=gr.gameid and g.romof is not null and g.biosof is null
        where gr.romtype='bios'
) b
  on (g.gameid = b.gameid)
  when matched then update set g.biosof = b.biosof;

end^


ALTER PROCEDURE P_G_UPDATEGAMENAME
AS
declare variable ROMMODE varchar(1);
declare variable USERELEASES varchar(5);
declare variable DEFAULTNAME varchar(255);
begin
--clean games.name
update games set name = null;

select lower(val) from config where param = 'rommode' into :rommode;
select lower(val) from config where param = 'usereleases' into :usereleases;

if ((:usereleases='true') and (:rommode = 'm')) then begin--
  -- mode merged, calculate zip filename for region and language
  update games fng
  set name =
  (
  select rl.name--,rl.gameid,rg.name as region,lg.name as language
  from
  releases rl
  join languages lg on lg.languageid = rl.languageid
  join (
    select rl.gameid,min(lg.preforder) as preforder
    from releases rl
    join languages lg on lg.languageid = rl.languageid
    group by rl.gameid
    ) lg1 on lg1.gameid = rl.gameid and lg.preforder = lg1.preforder
  join regions rg on rg.regionid = rl.regionid
  join (
    select rl.gameid,min(rg.preforder) as preforder
    from releases rl
    join regions rg on rg.regionid = rl.regionid
    group by rl.gameid
    ) rg1 on rg1.gameid = rl.gameid and rg.preforder = rg1.preforder
  where rl.gameid= fng.gameid
  )
  where fng.name is null;
  
  -- mode merged, calculate zip filename for region only
  update games fng
  set name =
  (select rl.name--,rl.gameid,rg.name as region
    from
    releases rl
    join regions rg on rg.regionid = rl.regionid
    join (
      select rl.gameid,min(rg.preforder) as preforder
      from releases rl
      join regions rg on rg.regionid = rl.regionid
      group by rl.gameid
    ) rg1 on rg1.gameid = rl.gameid and rg.preforder = rg1.preforder
    where rl.gameid= fng.gameid
  )
  where fng.name is null;
  
end
  --fill remaining gaps with default values (no releases info)
  select rl.name
  from releases rl
  where rl.isdefault = 1
  into :defaultname;

  update games fng
  set fng.name = :defaultname
  where fng.name is null;

  --fill remaining gaps (no releases info)
  update games
  set name = originalname
  where name is null;

end^


ALTER PROCEDURE P_G_UPDATESTATUS
AS
declare variable GAMEID integer;
declare variable ROMSTATUS varchar(10);
declare variable ROMTYPE varchar(10);
declare variable ROMID integer;
begin
-- set games bad status and dump
  for
    select distinct gr.gameid, gr.dumpstatus, gr.romtype
    from gameroms gr
    where gr.dumpstatus <> 'good'
    into :gameid, :romstatus, :romtype
  do begin
    if (romtype = 'rom') then begin
      update games g
      set g.badrom = 1
      where g.gameid = :gameid;
    end
    if (romtype = 'bios') then begin
      update games g
      set g.badbios = 1
      where g.gameid = :gameid;
    end
    if (romtype = 'disk') then begin
      update games g
      set g.baddisk = 1
      where g.gameid = :gameid;
    end
    if (romstatus = 'nodump') then begin
      update games g
      set g.dumpstatus = 'nodump'
      where g.gameid = :gameid and g.dumpstatus <> 'nodump';
    end
    if (romstatus = 'baddump') then begin
      update games g
      set g.dumpstatus = 'baddump'
      where g.gameid = :gameid and g.dumpstatus = 'good';
    end
    --faire les verified
  end

  --roms used as nodump and baddump/good in the same game/clones
  --change nodump to good
  --ex:
  -- kingdrby :    sg1_b.e1 good
  --   kingdrbb2 : sg1_b.e1 nodump
  for
    select gr.romid
    from gameroms gr
    join (select distinct romid from gameroms gr where gr.dumpstatus = 'nodump') r on r.romid = gr.romid
    group by gr.romid
    having count(distinct gr.dumpstatus) >1
  into :romid
  do begin
    update gameroms set dumpstatus = 'good' where romid = :romid;
    insert into daterrors (romid, comments)
    values(:romid,'DumpCollision');
  end
end^


ALTER PROCEDURE P_GR_TRANSFERTGAMEROMS
AS
declare variable ROMNAME varchar(200);
declare variable ROMID integer;
declare variable ROMSIZE double precision;
declare variable GAMEROMID integer;
declare variable GAMEID integer;
declare variable SIGVALUE varchar(48);
declare variable MERGEROMID integer;
declare variable SHA1 varchar(50);
declare variable CURRENTROM varchar(220);
declare variable DEVICENAME varchar(241);
declare variable STMT varchar(250);
declare variable NB integer;
begin
/*
--disables constraints, will be re-enabled at section 5
    stmt = 'ALTER TABLE ROMS DROP CONSTRAINT INTEG_R_GAMEIDENT';
    EXECUTE statement stmt;
    
    stmt = 'ALTER TABLE GAMES DROP CONSTRAINT FK_GAMES_1';
    EXECUTE statement stmt;
    stmt = 'ALTER TABLE GAMES DROP CONSTRAINT INTEG_G_CLONEOF';
    EXECUTE statement stmt;
    stmt = 'ALTER TABLE GAMES DROP CONSTRAINT INTEG_G_ROMOF';
    EXECUTE statement stmt;
    stmt = 'ALTER TABLE GAMES DROP CONSTRAINT INTEG_G_SAMPLEOF';
    EXECUTE statement stmt;
    
    stmt = 'ALTER TABLE GAMEROMS DROP CONSTRAINT INTEG_GR_GAMEID';
    EXECUTE statement stmt;
    stmt = 'ALTER TABLE GAMEROMS DROP CONSTRAINT INTEG_GR_ROMID';
    EXECUTE statement stmt;
    stmt = 'ALTER TABLE GAMEROMS DROP CONSTRAINT INTEG_GR_ROMMERGE';
    EXECUTE statement stmt;
*/

--Add all gameroms and link to roms with sha1
insert into gameroms (gameromid,shortname, extension, romtype,gameid,romid,dumpstatus)
select gen_id(gengameromid, 1),trim(d.romname),trim(d.romextension), d.romtype,g.gameid,r.romid,d.romstatus
 from datafiletmp d
 join games g on (d.gamename = g.name)
 left join roms r on r.sha1 = d.sha1 and d.sha1 <> ''
where d.romname <> '';

--Link remaining gameroms to roms with crc/size
for
   select gr.gameromid,r.romid
     from gameroms gr
     join games g on g.gameid = gr.gameid
     join datafiletmp d on (d.gamename = g.name and d.romname = gr.romname)
     join roms r on ((r.sigvalue = d.sigvalue and d.sigvalue <> '') and ((r.romsize = d.romsize) or (r.romsize = -1) ))
     where gr.romid is null
     into :gameromid,:romid do
begin
  --link to rom
  update gameroms  set romid = :romid where gameromid = :gameromid;
end

--Now, all items from datafiletmp are in gameroms

--include devices roms into games
--devices are linked using deviceref in each game.
--We physically put the device rom into game.
insert into gameroms (gameromid,shortname,extension, romtype,gameid,romid,dumpstatus)
select gen_id(gengameromid, 1),trim(dd.romname),trim(dd.romextension),'rom',g.gameid,r.romid,d.romstatus
 from datafiletmp d
 join datafiletmp dd on dd.gamename = d.deviceref
 left join roms r on ((r.sigvalue = dd.sigvalue and dd.sigvalue <> '') and ((r.romsize = dd.romsize) or (r.romsize = -1) )) or (r.sha1 = dd.sha1 and dd.sha1 <> '')
 join games g on (d.gamename = g.name)
where d.deviceref is not null;

--compute indexes
execute procedure p_rebuild_idx;

--mame 0.124 adds several time the same roms in a game (with different functions !)
--ex:
/*  <game name="raphero" sourcefile="nmk16.c">
    <description>Rapid Hero (Japan?)</description>
    <year>1994</year>
    <manufacturer>Media Trading Corp</manufacturer>
    <rom name="rhp94099.7" size="2097152" crc="0d99547e" sha1="..." region="sound1" offset="40000"/>
    <rom name="rhp94099.7" size="2097152" crc="0d99547e" sha1="..." region="sound2" offset="440000"/>
    <rom name="rhp94099.7" size="2097152" crc="0d99547e" sha1="..." region="sound2" offset="640000"/>
*/
--remove these extra roms, keep just one (stored in :gameromid)
for
  select gr.romname,gr.gameid, min(gr.gameromid) --keep one gamerom (min)
  from gameroms gr
  group by gr.romname,gr.gameid
  having count(gr.gameromid) > 1
  into :romname,:gameid,:gameromid do
begin
  --log error
  --insert into daterrors(gameid,comment) values(:gameid,'Rom ' || :romname || ' duplicated');
  --delete roms
  delete from gameroms where
  romname = :romname and gameid = :gameid
  and gameromid <> :gameromid;
end


--all remaining roms in datafiletmp have no signatures
--and no entries in roms table.
--for each roms in datafiletmp, we have to insert into roms and update link with games
-- and roms into gameroms

--create roms and update gameroms.romid for roms without signature

--start with roms in main game
--no sig, find rom using gamename and romname (unique in one single game, main or clone)
for
  select gr.gameromid,d.romsize,gen_id(genromid, 1) as romid
  from gameroms gr
  join games g on g.gameid = gr.gameid
  join datafiletmp d on d.gamename = g.name and d.romname = gr.romname
  where gr.romid is null and g.cloneof is null and g.sampleof is null and g.romof is null
  into :gameromid,:romsize,:romid do
begin
  --sigvalue and sha1 are empty (non empty values are processed in first query)
  --create rom
  insert into roms(romid,romsize) values(:romid,:romsize);
  --link to gamerom
  update gameroms  set romid = :romid where gameromid = :gameromid;
end

--roms in clones
for
  select gr.gameromid,r.romid
  from gameroms gr
  join games g on g.gameid = gr.gameid
  join gameroms gr2 on gr2.gameid = g.cloneof and gr2.romname = gr.romname and gr.romtype <> 'sample'
  join roms r on gr2.romid = r.romid
  where gr.romid is null
  into :gameromid, :romid
do update gameroms  set romid = :romid where gameromid = :gameromid;  --link to rom


--device roms with no crc/sha1
for
  select distinct d.gamename, d.romsize,d.romname
  from datafiletmp d
  join datafiletmp d1 on d1.deviceref = d.gamename
  join games g on g.name = d1.gamename
  join gameroms gr on gr.gameid = g.gameid and gr.romname = d.romname
  where d.romtype = 'device' and gr.romid is null
  and d.sigvalue = '' and d.sha1 = ''
  into :devicename, :romsize,:romname do
begin
  --create rom
  select gen_id(genromid, 1) from rdb$database into :romid;
  insert into roms(romid,romsize) values(:romid,:romsize);
  --link to all gameroms
  for
      select gr.gameromid
      from datafiletmp d
      join games g on g.name = d.gamename
      join gameroms gr on gr.gameid = g.gameid
      where d.deviceref = :devicename and gr.romname = :romname
      into :gameromid
  do update gameroms  set romid = :romid where gameromid = :gameromid; --link to rom
end

--samples in clones
for
  select gr.gameromid,r.romid
  from gameroms gr
  join games g on g.gameid = gr.gameid
  join gameroms gr2 on gr2.gameid = g.sampleof and gr2.romname = gr.romname
  join roms r on gr2.romid = r.romid
  where gr.romid is null
  into :gameromid, :romid
do update gameroms  set romid = :romid where gameromid = :gameromid; --link to rom

--bios in clones
for
  select gr.gameromid,r.romid
  from gameroms gr
  join games g on g.gameid = gr.gameid
  join gameroms gr2 on gr2.gameid = g.romof and gr2.romname = gr.romname and gr.romtype <> 'sample'
  join roms r on gr2.romid = r.romid
  where gr.romid is null
  into :gameromid, :romid
do update gameroms  set romid = :romid where gameromid = :gameromid; --link to rom

--link by sampleof
for
  select gr.romname, g.sampleof,max(d.sigvalue),max(d.sha1),max(d.romsize),gen_id(genromid, 1)
  from gameroms gr
  join games g on g.gameid = gr.gameid
  join datafiletmp d on d.gamename = g.name and d.romname = gr.romname
  where gr.romid is null and g.sampleof is not null
  group by gr.romname, g.sampleof
  into :romname,:gameid,:sigvalue,:sha1,:romsize,:romid do
begin
  if (:sigvalue = '') then sigvalue = null;
  if (:sha1 = '') then sha1 = null;
  --create rom
  insert into roms(romid,sigvalue,sha1,romsize) values(:romid,:sigvalue,:sha1,:romsize);
  --link to gamerom
  for
    select gr.gameromid
    from games g
    join gameroms gr on gr.gameid = g.gameid and g.sampleof = :gameid
    where gr.romname = :romname and gr.romid is null
    into :gameromid
  do update gameroms  set romid = :romid where gameromid = :gameromid;
end

--link by romof v1 v2
currentrom = '';
for --144 roms / 435 gameroms
  select gr.gameromid,gr.romname, g.romof,max(d.romsize)
  from gameroms gr
  join games g on g.gameid = gr.gameid
  join datafiletmp d on d.gamename = g.name and d.romname = gr.romname
  where gr.romid is null and g.romof is not null
  group by gr.gameromid,gr.romname, g.romof
  order by gr.romname,g.romof
  into :gameromid,:romname,:gameid,:romsize do
begin
  --new rom ?
  if ((currentrom = '') or (currentrom <> :romname||:gameid)) then begin
    romid = gen_id(genromid, 1);
    --create rom
    insert into roms(romid,romsize) values(:romid,:romsize);
    currentrom = :romname||:gameid;
  end
  --link to gamerom
  update gameroms  set romid = :romid where gameromid = :gameromid;
end

--update gameroms.rommerge (needed to init gamesinfo)
for
  select gro.gameromid, gr.gameromid
  from gameroms gro
  join datafiletmp d on (gro.romname = d.romname)
  join games go on (gro.gameid = go.gameid) and (go.name = d.gamename)
  join games g on (g.name = d.romof)
  join gameroms gr on (gr.gameid = g.gameid) and (gr.romname = d.mergeromname)
  group by gro.gameromid,gr.gameromid
  into :gameromid,:mergeromid do
begin
  update gameroms  set rommerge = :mergeromid where gameromid = :gameromid;
end

/*
   --re-enable constraints
        stmt = 'ALTER TABLE ROMS ADD CONSTRAINT INTEG_R_GAMEIDENT FOREIGN KEY (GAMEIDENT) REFERENCES GAMES (GAMEID)';
        EXECUTE statement stmt;
        stmt = 'ALTER TABLE GAMES ADD CONSTRAINT FK_GAMES_1 FOREIGN KEY (BIOSOF) REFERENCES GAMES (GAMEID) USING INDEX INTEG_G_BIOSOF';
        EXECUTE statement stmt;
        stmt = 'ALTER TABLE GAMES ADD CONSTRAINT INTEG_G_CLONEOF FOREIGN KEY (CLONEOF) REFERENCES GAMES (GAMEID)';
        EXECUTE statement stmt;
        stmt = 'ALTER TABLE GAMES ADD CONSTRAINT INTEG_G_ROMOF FOREIGN KEY (ROMOF) REFERENCES GAMES (GAMEID);';
        EXECUTE statement stmt;
        stmt = 'ALTER TABLE GAMES ADD CONSTRAINT INTEG_G_SAMPLEOF FOREIGN KEY (SAMPLEOF) REFERENCES GAMES (GAMEID)';
        EXECUTE statement stmt;
        stmt = 'ALTER TABLE GAMEROMS ADD CONSTRAINT INTEG_GR_GAMEID FOREIGN KEY (GAMEID) REFERENCES GAMES (GAMEID)';
        EXECUTE statement stmt;
        stmt = 'ALTER TABLE GAMEROMS ADD CONSTRAINT INTEG_GR_ROMID FOREIGN KEY (ROMID) REFERENCES ROMS (ROMID)';
        EXECUTE statement stmt;
        stmt = 'ALTER TABLE GAMEROMS ADD CONSTRAINT INTEG_GR_ROMMERGE FOREIGN KEY (ROMMERGE) REFERENCES GAMEROMS (GAMEROMID)';
        EXECUTE statement stmt;
  */
end^


ALTER PROCEDURE P_INITGAMESINFO
AS
begin

delete from gamesinfo;

--Init gamesinfo
insert into gamesinfo(gameid,romtype,rommerge)
select distinct gr.gameid,gr.romtype,case when gr.rommerge is null then 'main' else 'merge' end
from gameroms gr
join games g on g.gameid = gr.gameid;

end^


ALTER PROCEDURE P_LOADDATAFILE (
    SECTION INTEGER)
AS
declare variable CMD varchar(50);
declare variable ID integer;
begin
  --Start from the datafiletmp and releasestmp tables only
  --Load and init all other tables
  --Process is cut into 5 sections to be able to have progress info in rc

  --disable triggers
  --execute procedure p_disable_triggers;

  if (section = 1) then begin

    --check if source datas have bios, clones etc...
    --It is needed with the keepmain flag if we come back from keepmain game to all games
    update config set val = 'false' where param like 'has%';
  
    for select first 1 0 from datafiletmp d where romtype = 'bios' into :id do begin
      update config set val = 'true' where param = 'hasbiosindat';
    end
    
    for select first 1 0 from datafiletmp d where d.cloneof <> '' into :id do begin
      update config set val = 'true' where param = 'hasclonesindat';
    end
  
    for select first 1 0 from datafiletmp d where romtype = 'sample' into :id do begin
      update config set val = 'true' where param = 'hassamplesindat';
    end
  
    for select first 1 0 from datafiletmp d where romtype = 'disk' into :id do begin
      update config set val = 'true' where param = 'hasdisksindat';
    end

    --unlink files and romfiles
    --clear roms and games tables
    --import new games and roms
    --re-link files
  
    --unlink files
    update files set gameid = null;
    update fileroms set gameromid=null, romid = null;
  
  --clear roms and games tables
    --clear relations
--    delete from filesview;
    delete from daterrors;
    delete from filegames;
--    delete from filesdumpstatus;
    delete from gamesinfo;
    update games set cloneof = null,romof = null,sampleof=null,biosof=null;
    update gameroms set rommerge = null, romid = null;
    --clear tables
    delete from roms;
    delete from releases;--do not delete regions (keep preferences)
    delete from gameroms;
    delete from games;
  
  --transfert games from datafile
    execute procedure p_df_fixdatas; --fix pb in datafiletmp
    execute procedure p_g_transfertgames; /* 12 sec */
    execute procedure p_g_preparegames; /* 4 sec */
  end
  
  if (section = 2) then begin
  --transfert and update releases from releasestmp (use regions settings)
    execute procedure p_transfertreleases;
    execute procedure p_rl_updatereleases; --swap main-clones
  
  --remove unwanted games (keepmain)
    execute procedure p_filtergames;
  
  /*transfert roms from datafile */
    execute procedure p_r_transfertroms;     /* 10 sec */
  end
  if (section = 3) then begin
    execute procedure p_gr_transfertgameroms; /* 36 sec */
  end
  
    if (section = 4) then begin
      execute procedure p_g_updatebios; /*level 1 (ex: neogeo main)*/
      execute procedure p_g_updatebios; /*level 2 (ex: neogeo clones)*/
      execute procedure p_g_update ; /* */
    
      --Update games
      execute procedure p_fg_updatefilegames;
      execute procedure p_r_updategameident; /* need filegames*/
      execute procedure p_initgamesinfo;
      execute procedure p_updategamesinfo;  /* clean games list: remove inconsistencies*/
    
/*      execute procedure p_identfiles;
      execute procedure P_UPDATEGAMEROMID;
      execute procedure p_updatefilesdumpstatus;
      execute procedure p_updatefilesview;
      execute procedure p_updateromscount;
*/
    end
  
    if (section = 5) then begin

        --fill daterrors
      insert into daterrors(gameid,romid,comments)
      select gr.gameid,r.romid,'Duplicated roms'
      from filegames fg
      join gameroms gr on fg.gameromid = gr.gameromid
      join roms r on (r.romid = gr.romid)
      where r.sigvalue is not null
      group by gr.gameid,r.romid
      having count(distinct gr.romname) > 1;
    
      --error : ghost games
      insert into daterrors(gameid,comments)
      select fg.gameid,'ghost game'--g.name,g.gametype, count(fg.gameromid) as nbroms
      from filegames fg
      join games g on g.gameid= fg.gameid --and g.gametype <> 'bios'
      join gameroms gr on gr.gameromid = fg.gameromid --and gr.romtype <> 'sample'
      left join roms r on r.romid = gr.romid and r.gameident is not null
      group by fg.gameid,g.name,g.gametype
      having min(r.gameident) is null;

      --collision in merged rom mode
      insert into daterrors(gameid,romname,comments)
      select g.setid,gr.romname,'merge collision'--,count(distinct gr.romid)
      from games g
      join gameroms gr on g.gameid = gr.gameid
      group by g.setid,gr.romname
      having count(distinct gr.romid) >1;

      ---update config infos
      update config set val = 'false' where param like 'has%' and param not like 'has%dat' and param not in ('hasromextension');
  
      for select first 1 gameid from games where gametype = 'bios' into :id do begin
        update config set val = 'true' where param = 'hasbios';
      end
      
      for select first 1 gameid from games where gametype = 'clone' into :id do begin
        update config set val = 'true' where param = 'hasclones';
      end
      
      for select first 1 gameromid from gameroms where romtype = 'sample' into :id do begin
        update config set val = 'true' where param = 'hassamples';
      end
      
      for select first 1 gameromid from gameroms where romstatus not in ('good','verified') into :id do begin
        update config set val = 'true' where param = 'hasbaddumps';
      end
  
      for select first 1 gameromid from gameroms where romstatus = 'verified' into :id do begin
        update config set val = 'true' where param = 'hasverified';
      end
  
      for select first 1 gameromid from gameroms where romtype = 'disk' into :id do begin
        update config set val = 'true' where param = 'hasdisks';
      end
  
      for select first 1 gameromid from gameroms gr where gr.dumpstatus = 'nodump' into :id do begin
        update config set val = 'true' where param = 'hasdummies';
      end
  
        for select first 1 de.gameid from daterrors de where de.comments = 'ghost game' into :id do begin
          update config set val = 'true' where param = 'hasghosts';
        end
  
      for select first 1 regionid from regions into :id do begin
        update config set val = 'true' where param = 'hasregions';
      end
  
      for select first 1 languageid from languages into :id do begin
        update config set val = 'true' where param = 'haslanguages';
      end
  
      for select first 1 gameid from releases into :id do begin
        update config set val = 'true' where param = 'hasreleases';
      end
  
      for select first 1 gameid from games where gamenumber is not null into :id do begin
        update config set val = 'true' where param = 'hasnumbers';
      end
  
      for select first 1 gameid from games g where g.gameyear is not null into :id do begin
        update config set val = 'true' where param = 'hasyear';
      end
  
      for select first 1 gameid from games where manufacturer is not null into :id do begin
        update config set val = 'true' where param = 'hasmanufacturer';
      end

      --reset files datas
      execute procedure p_resetfiles;

    end
end^


ALTER PROCEDURE P_R_TRANSFERTROMS
AS
declare variable SIGVALUE varchar(48);
declare variable ROMSIZE bigint;
begin
/* create roms with signature only */
/* see transfertgameroms for others */

--roms with crc and sha1 (most of roms with mame)
  insert into roms(romid,sigvalue,sha1,romsize)
  select gen_id(genromid, 1),r.sigvalue,r.sha1,r.romsize
  from datafiletmp r
  join games g on g.name = r.gamename
  where r.sigvalue <> '' and r.sha1 <> ''
  group by r.sigvalue,r.sha1,r.romsize;

  --devices roms (currently not existing separatly in a file)
/*  insert into roms
  select gen_id(genromid, 1),null,d.sigvalue,d.sha1,d.romsize
  from datafiletmp d
  left join roms r on r.sigvalue = d.sigvalue and ((r.romsize = d.romsize) or (r.romsize = -1) )
  where (d.sigvalue <> '' or d.sha1 <> '') and d.romtype = 'device' and r.romid is null
  group by d.sigvalue,d.sha1,d.romsize;
*/

--compute indexes
  execute procedure p_rebuild_idx;

--roms with crc only, and not already in table
  insert into roms (romid,sigvalue,romsize)
  select gen_id(genromid, 1),d.sigvalue,d.romsize
  from datafiletmp d
  join games g on g.name = d.gamename
  left join roms r on r.sigvalue = d.sigvalue and ((r.romsize = d.romsize) or (r.romsize = -1) )
  where d.sigvalue <> '' and d.sha1 = ''
  and r.romid is null --rom not already in table
  group by d.sigvalue,d.romsize;

--roms with sha1 only, and not already in table (chd)
  insert into roms(romid,sha1,romsize)
  select gen_id(genromid, 1),d.sha1,d.romsize
  from datafiletmp d
  join games g on g.name = d.gamename
  left join roms r on r.sigvalue = d.sigvalue and ((r.romsize = d.romsize) or (r.romsize = -1) )
  where d.sigvalue = '' and d.sha1 <> ''
  and r.romid is null --rom not already in table
  group by d.sha1,d.romsize;

--Identifiy roms with same crc/size and set the 'getsha1' flag.
  for
    select sigvalue, romsize
    from roms
    where sha1 is not null and sigvalue is not null
    group by sigvalue, romsize
    having count(*)>1
    into :sigvalue,:romsize
do begin
    update roms
    set getsha1=1
    where sigvalue = :sigvalue
    and romsize = :romsize;
end

--Identifiy roms with no crc but a sha1 and set the 'getsha1' flag.
    update roms
    set getsha1=1
    where sha1 is not null and sigvalue is null;

--compute indexes
  execute procedure p_rebuild_idx;

end^


ALTER PROCEDURE P_R_UPDATEGAMEIDENT
AS
declare variable GAMEID integer;
declare variable ROMID integer;
begin

  --update dumped column (0 if nodump)
  update roms
  set dumped = 0
  where romid in (
  select romid
  from gameroms gr
  where gr.dumpstatus = 'nodump');

  update roms set gameident = null where gameident is not null;

--warning: identgame give the gameid accordingly to merge mode. 
--         Full merge greatly increase number of gameident

--update roms set gameident = null where gameident is not null;

--based romid (unique crc/size and sha1)
  for
    select r.romid,min(fg.gameid)
    from roms r
    join gameroms gr on gr.romid = r.romid
    join filegames fg on fg.gameromid = gr.gameromid
    where r.sigvalue is not null
    group by r.romid
    having count(distinct fg.gameid) = 1
    into :romid,:gameid do
  begin
    update roms  set gameident = :gameid
    where romid = :romid;
  end

  --based on name when crc/sha1 is not available (samples, nodump)
  for
    select min(gr.romid) as romid,min(gr.gameid) as gameid
    from roms r
    join gameroms gr on gr.romid = r.romid
    join filegames fg on fg.gameromid = gr.gameromid
    where r.sigvalue is null
    group by gr.romname
    having count(distinct fg.gameid) = 1
    into :romid,:gameid do
  begin
    update roms  set gameident = :gameid
    where romid = :romid;
  end
end^


ALTER PROCEDURE P_REBUILD_IDX
AS
declare variable IDX varchar(50);
declare variable STMT varchar(75);
BEGIN
  FOR 
    SELECT RDB$INDEX_NAME
    FROM RDB$INDICES
    INTO :IDX
  DO 
  BEGIN 
    STMT = 'SET STATISTICS INDEX ' || :IDX;
    EXECUTE STATEMENT :STMT;
  END 
END^


ALTER PROCEDURE P_REMOVEFILE (
    FILEID INTEGER)
AS
declare variable GAMEID integer;
declare variable ROMTYPE varchar(10);
declare variable ROMMERGE varchar(10);
declare variable ROMID integer;
begin
  --update gi.missing
  --update fr.isavail and fr.statuslvl for missing fileroms
  delete from tmp;

  --insert roms to remove in a tmp table
  insert into tmp(id1,id2,str1,str2)
  select fr.romid, gr.gameid, gr.romtype,
  case when gr.rommerge is null then 'main' else 'merge' end
  from files f
  join fileroms fr on fr.fileid=f.fileid
  join gameroms gr on gr.romid = fr.romid
  where
  f.fileid = :fileid;

  execute procedure p_removetmpitems;

  --remove fileroms
  delete from FILEROMS where FILEID = :fileid;

  --remove files
  delete from files where fileid = :fileid;
end^


ALTER PROCEDURE P_REMOVEFILEROM (
    FILEROMID INTEGER)
AS
declare variable NBAVAIL integer;
declare variable ISNOTUSED integer;
declare variable FILEID integer;
declare variable ROMID integer;
declare variable FORMAT varchar(20);
begin
  --remove rom

  --get fileroms infos
  select f.fileid,fr.romid,fr.isnotused, f.fileformat,va.nbromsfound
  from files f
  join fileroms fr on fr.fileid = f.fileid and fr.fileromid = :fileromid
  left join v_availability va on va.romid = fr.romid
  into :fileid,:romid,:isnotused,:format,:nbavail;

  if (format = 'file') then begin
    --remove file (filerom not zipped)
    delete from fileroms where fileromid = :fileromid;
    delete from files where fileid = :fileid;
  end
  else begin
    --if rom is used, convert it to missing
    if (isnotused = 1) then begin
       --not used, delete
       delete from fileroms where fileromid = :fileromid;
    end
    else begin
      update fileroms fr set
      fr.fileromid = null,
      fr.rompath = null,
      fr.romname = null,
      fr.romext = '',
      fr.pluginext = null,
      fr.parentformat = null,
      fr.romdate = null,
      fr.error = null,
      fr.isavail = 1,  --will be updated next
      fr.statuslvl = 30 --yellow
      where fr.fileromid = :fileromid;
    end
    --file status is dynamic
  end

  --if rom is unique, set isavail to 0
  if (nbavail = 1) then begin
    update fileroms fr set
    fr.isavail = 0,
    fr.statuslvl = 20 --red
    where fr.romid = :romid;
  end

end^


ALTER PROCEDURE P_REMOVEPATH (
    PATHID INTEGER)
AS
declare variable GAMEID integer;
declare variable ROMTYPE varchar(10);
declare variable ROMMERGE varchar(10);
declare variable ROMID integer;
begin
  --update gi.missing
  --update fr.isavail and fr.statuslvl for missing fileroms
  delete from tmp;

  --insert roms to remove in a tmp table
  insert into tmp(id1,id2,str1,str2)
  select fr.romid, gr.gameid, gr.romtype,
  case when gr.rommerge is null then 'main' else 'merge' end
  from files f
  join fileroms fr on fr.fileid=f.fileid
  join gameroms gr on gr.romid = fr.romid
  where
  f.pathid = :pathid;

  execute procedure p_removetmpitems;

  --remove fileroms
  delete from FILEROMS where FILEID in (select fileid from files where pathid = :PATHID);

  --remove files
  delete from files where pathid = :PATHID;

  --remove paths
  delete from paths where pathid = :PATHID;
end^


ALTER PROCEDURE P_REMOVETMPITEMS
AS
declare variable GAMEID integer;
declare variable ROMTYPE varchar(10);
declare variable ROMMERGE varchar(10);
declare variable ROMID integer;
begin
  
  --exclude non-unique roms
  delete from tmp t
  where t.id1 in
  (select va.romid
  from v_availability va
  where va.nbromsfound > 1
  and t.id1 = va.romid);

  --update ismissing, isavail and statuslvl
  for
    select distinct id1 ,id2, str1, str2 from tmp
    into :romid, :gameid, :romtype,:rommerge
  do begin
    update gamesinfo gi
    set ismissing = 1
    where gi.gameid = :gameid and gi.romtype = :romtype and gi.rommerge = :rommerge;

    --missing avail (yellow) fileroms must be updated to missing (red)
    update fileroms fr
    set fr.isavail = 0, --red
    fr.statuslvl = case when fr.statuslvl <> 10 then 20 else 10 end --lvl 30,40 -> 20 (10 is error)
    where fr.romid = :romid and fr.ismissing = 1;
  end

  delete from tmp;

end^


ALTER PROCEDURE P_RESETFILES
AS
begin
      --9s
      update fileroms set romid = null, parentformat = null, gameromid = null, expromname =null, isavail = 0,isnotused = 0, isbadfolder = 0, statuslvl = 0, needrefresh =1;
      delete from fileroms where fileromid is null;
      update files set status = null, gameid = null;
      --0.5s
      update files f set f.gameid = null,f.expfilename = null, f.filetype = null, status = null, needrefresh =1;
      execute procedure p_rebuild_idx;
end^


ALTER PROCEDURE P_RL_UPDATERELEASES
AS
declare variable usereleases varchar(5);
declare variable oldmain integer;
declare variable newmain integer;
declare variable currentsetid integer;
begin

  --swap main and clone depending of releases settings
  select trim(lower(val)) from config where param='usereleases' into :usereleases ;
  if (:usereleases = 'true') then begin
   currentsetid = -1;
   for
      select g.setid ,rl.gameid
      from releases rl
      join games g on g.gameid = rl.gameid
      left join regions rg on rg.regionid = rl.regionid
      left join languages lg on lg.languageid = rl.languageid
      order by g.setid,rg.preforder ,lg.preforder
    into :oldmain,:newmain
    do begin

      --take only the first line for each set
      if ((currentsetid <> oldmain ) and (newmain <> oldmain)) then begin
        --swap main and clone set
        update games g set cloneof = :newmain where setid = :oldmain;
        update games g set romof = :newmain where setid = :oldmain;
        update games g set setid = :newmain where setid = :oldmain;
        update games g set cloneof = null, romof = null, gametype = 'main' where gameid = :newmain;
        update games g set gametype = 'clone' where gameid = :oldmain;
      end
      currentsetid = oldmain;
   end
  end
end^


ALTER PROCEDURE P_SETERROR (
    FILEID INTEGER,
    MSG VARCHAR(250))
AS
declare variable GAMEID integer;
declare variable OLDERROR varchar(250);
declare variable ROMID integer;
declare variable ROMTYPE varchar(20);
declare variable ROMMERGE varchar(10);
begin
  --clear some files and fileroms status. Must be refreshed.

  if (msg is null) then msg = '';

  --get old error
  select trim(error) from files where fileid = :fileid into :olderror;

  --update new error
  update files set error = :msg where fileid = :fileid;
  update fileroms set error = :msg where fileid = :fileid;

  if ((olderror = '') and (msg = '')) then exit; --file checked and ok
  if ((olderror <> '') and (lower(msg) <> '')) then exit; --file already in error

  if (lower(msg) <> '') then begin --error in file, reset status
    update files set status = 'error', error = :msg where fileid = :fileid;
    update fileroms fr set fr.statuslvl = 10,fr.error = :msg where fileid = :fileid;

    --these roms are no more usable for fixing. v_availability are dynamic and won't list them anymore.
    --we have to update gi.ismissing, fr.isavail and fr.statuslvl anywhere

    --insert roms to remove in a tmp table
    delete from tmp;
    insert into tmp(id1,id2,str1,str2)
    select fr.romid, gr.gameid, gr.romtype,
    case when gr.rommerge is null then 'main' else 'merge' end
    from files f
    join fileroms fr on fr.fileid=f.fileid
    join gameroms gr on gr.romid = fr.romid
    where
    f.fileid = :fileid;
  
    --exclude non-unique roms
    delete from tmp t
    where t.id1 in
    (select va.romid
    from v_availability va
    where va.nbromsfound > 1
    and t.id1 = va.romid);
  
    --update ismissing, isavail and statuslvl
    for
      select distinct id1 ,id2, str1, str2 from tmp
      into :romid, :gameid, :romtype,:rommerge
    do begin
      update gamesinfo gi
      set ismissing = 0
      where gi.gameid = :gameid and gi.romtype = :romtype and gi.rommerge = :rommerge;
  
      --missing avail (yellow) fileroms must be updated to missing (red)
      update fileroms fr
      set fr.isavail = 1
      where fr.romid = :romid and fr.ismissing = 1;
    end
  
    delete from tmp;

    --update statuslvl (bad name, ok, notused)

    update fileroms set needrefresh = 1 where fileid = :fileid;
    execute procedure p_fr_updatestatuslvl;
    update fileroms set needrefresh = 0 where fileid = :fileid;

  end
  else begin --File now ok, calculate new status
    update files set status = null, error = :msg, needrefresh = 1 where fileid = :fileid;
    update fileroms set needrefresh = 1 where fileid = :fileid;

    --these roms are usable again for fixing. v_availability are dynamic.
    --we have to update gi.ismissing, fr.isavail and fr.statuslvl anywhere
    --insert roms to add in a tmp table
    delete from tmp;
    insert into tmp(id1,id2,str1,str2)
    select fr.romid, gr.gameid, gr.romtype,
    case when gr.rommerge is null then 'main' else 'merge' end
    from files f
    join fileroms fr on fr.fileid=f.fileid
    join gameroms gr on gr.romid = fr.romid
    where
    f.fileid = :fileid;
  end

end^


ALTER PROCEDURE P_TRANSFERTFILES
AS
begin

--paths
  insert into paths(pathid,shortpathname,volumename,volumeserial,pathtype,drive,drivetype,status,unc)
  select gen_id(genpathid, 1), ft.pathname,ft.volumename ,ft.volumeserial,ft.pathtype,ft.drive,ft.drivetype,'RW',ft.unc
  from filestmp ft
  left join paths p on p.unc = ft.unc
  where p.unc is null
  group by ft.pathname,ft.volumename ,ft.volumeserial,ft.pathtype,ft.drive,ft.drivetype,ft.unc;

--files
  insert into FILES(fileid, gameid,pathid,filename,extension,FILEFORMAT,filesize,filetype,status,filedate,error,zipcomment)
  select gen_id(genfileid, 1),null,p.pathid,ft.filename,ft.extension,ft.FILEFORMAT ,ft.filesize,ft.filetype,null,cast(ft.filedate as timestamp),ft.error,ft.zipcomment
  from filestmp ft
  join paths p on (p.unc = ft.unc) and (p.volumename = ft.volumename) and (p.volumeserial = ft.volumeserial)
  where ft.filename <> ''
  group by p.pathid,ft.filename,ft.extension,ft.FILEFORMAT ,ft.filesize,ft.filetype,ft.filedate,ft.error,ft.zipcomment;

--fileroms
  insert into FILEROMS(efid,fileromid,fileid,rompath,romname,pluginext,romsize,romsig,romdate,error)
  select gen_id(genfilerompk, 1), gen_id(genfileromid, 1),f.fileid,ft.rompath, ft.romname,coalesce(ft.pluginext,''),ft.romsize,ft.romsig,cast(ft.romdate as timestamp),case when (trim(ft.error) = '') then '' else trim(ft.error) end
  from filestmp ft
  join FILES f on (ft.filename = f.filename) and (ft.extension = f.extension)
  join paths p on (p.pathid = f.pathid) and (ft.pathname = p.shortpathname)  and (ft.volumename = p.volumename) and (ft.volumeserial = p.volumeserial)
  where coalesce(ft.rompath,'') || ft.romname  <> '';

  delete from filestmp;
end^


ALTER PROCEDURE P_TRANSFERTRELEASES
AS
declare variable ID integer;
declare variable NAME varchar(50);
begin

delete from releases;
delete from regions;
delete from languages;

--regions table
id = 0;
for
  select region
  from releasestmp
  where trim(gamename) <> ''
  group by region
  order by count(*) desc
  into :name do
begin
  id = id+1;
  insert into regions(regionid,name,preforder)
  values (:id,:name,:id);
end

--languages table
id = 0;
for
  select lang
  from releasestmp
  where trim(lang) <> ''
  group by lang
  order by count(*) desc
  into :name do
begin
  id = id+1;
  insert into languages(languageid,name,preforder)
  values (:id,:name,:id);
end

  delete from releases;

  --releases table
  insert into releases(gameid,name,regionid,languageid)
  select g.gameid, rt.releasename,rg.regionid,lg.languageid
  from releasestmp rt
  join games g on g.originalname = rt.gamename
  left join regions rg on rg.name = rt.region
  left join languages lg on lg.name = rt.lang;
end^


ALTER PROCEDURE P_UPDATEGAMESINFO
AS
declare variable GAMEID integer;
begin

  --invalidate gamesinfo
  for
  select distinct gi.gameid
  from gamesinfo gi
  join (select gameid from files f where f.needrefresh = 1) g on g.gameid = gi.gameid
  where gi.ismissing <> -1
  into :gameid
  do begin
    update gamesinfo set ismissing = -1 where gameid = :gameid;
  end

--48000, 13s
update gamesinfo gi
set ISMISSING = 1
where gi.ISMISSING = -1
and gi.gameid||'-'||gi.romtype||'-'||gi.rommerge
in
(
  select distinct gr.gameid||'-'||gr.romtype||'-'||case when gr.rommerge is null then 'main' else 'merge' end
  from gameroms gr
  join roms r on r.romid = gr.romid
  left join FILEROMS fr on fr.romid = gr.romid and fr.fileromid is not null-- and coalesce(fr.error,'') <> ''
  where fr.fileromid is null
  and gi.ISMISSING = -1
  and r.dumped = 1
  and gi.gameid= gr.gameid and gr.romtype = gi.romtype and gi.rommerge = case when gr.rommerge is null then 'main' else 'merge' end
  group by gr.gameid,gr.romtype,gr.rommerge
);

update gamesinfo gi
set ISMISSING = 0
where gi.ISMISSING = -1;

end^


ALTER PROCEDURE REFRESHALL
AS
begin
--reset fields
update FILEROMS set romid = null, parentformat = null, gameromid = null, expromname =null, ISAVAIL = 0, isnotused = 0, ISBADFOLDER = 0, statuslvl = 0, needrefresh =1;
update FILES f set f.gameid = null,f.expfilename = null, f.filetype = null, status = null, needrefresh =1;
update gamesinfo gi set gi.ISMISSING = -1;

--delete missings
delete from FILEROMS where fileromid is null;

--0.5s
execute procedure p_rebuild_idx;

execute procedure P_ADDFILE(1); --96000, 18s - 40, 3s
execute procedure P_ADDFILE(2); --90000, 15s - 35 1s
execute procedure P_ADDFILE(3); --107000, 18s - 50, 1.5s
execute procedure P_ADDFILE(4); --128000, 24s - 35, 1s
execute procedure P_ADDFILE(5); --111000, 19s - 5, 0.2s
execute procedure P_ADDFILE(6); --437000, 34s - 117, 1.2s
execute procedure P_UPDATEGAMESINFO;

end^


ALTER PROCEDURE TEST
AS
declare variable FILEID integer;
declare variable GAMEID integer;
declare variable ROMSTATUS varchar(10);
declare variable ROMTYPE varchar(10);
declare variable NEW_VAR integer;
declare variable ROMID integer;
begin

execute procedure P_TRANSFERTFILES;
execute procedure P_ADDFILE(1); --96000, 18s - 40, 3s
execute procedure P_ADDFILE(2); --90000, 15s - 35 1s
execute procedure P_ADDFILE(3); --107000, 18s - 50, 1.5s
execute procedure P_ADDFILE(4); --128000, 24s - 35, 1s
execute procedure P_ADDFILE(5); --111000, 19s - 5, 0.2s
execute procedure P_ADDFILE(6); --437000, 34s - 117, 1.2s

end^



SET TERM ; ^


/******************************************************************************/
/***                              Descriptions                              ***/
/******************************************************************************/

DESCRIBE DOMAIN FILEEXT
'File extension';

DESCRIBE DOMAIN RCSIZE
'Size of roms or files';



/******************************************************************************/
/***                              Descriptions                              ***/
/******************************************************************************/

DESCRIBE TABLE CONFIG
'RomCenter settings';

DESCRIBE TABLE GAMES
'List of games';

DESCRIBE TABLE GAMESINFO
'Status list used to display icons in the database node';



/******************************************************************************/
/***                              Descriptions                              ***/
/******************************************************************************/

DESCRIBE VIEW V_AVAILABILITY
'Give roms availability for each rom. If a rom is not dumped (not sig), availability is set to 1 (it can be generated as a dummy rom).';

DESCRIBE VIEW V_GAMEROMSLIST
'List of gameroms as displayed in gameroms view';



/******************************************************************************/
/***                              Descriptions                              ***/
/******************************************************************************/

DESCRIBE PROCEDURE P_COPY_FILEROM
'Copy fileromid to dstfile and update status.';

DESCRIBE PROCEDURE P_CREATEVIEWSINDEXES
'Required by ef to create keys';

DESCRIBE PROCEDURE P_FR_UPDATECASE
'Update fileroms.expname to match fileroms case setting. Update statuslvl.';

DESCRIBE PROCEDURE P_FR_UPDATESTATUS
'Update fr parentformat and is... flags for needrefresh fileroms. Also update isavail if needed for new roms added.';

DESCRIBE PROCEDURE P_F_UPDATECASE
'Update files.expname to match files case setting. Update status.';

DESCRIBE PROCEDURE P_F_UPDATESTATUS
'ok, bad name, folder needed, wrong path, unknown, error';

DESCRIBE PROCEDURE P_G_TRANSFERTGAMES
'Transfert games from datafiletmp to games';

DESCRIBE PROCEDURE P_REMOVEFILEROM
'Remove filerom (and file for single file), update status.';

DESCRIBE PROCEDURE P_REMOVETMPITEMS
'Remove fileroms specified in the tmp table. Should occur after abs p_removexxx procedure';

DESCRIBE PROCEDURE P_TRANSFERTFILES
'Transfert files from filestmp to paths, files and fileroms. Files are not analysed.';

DESCRIBE PROCEDURE P_UPDATEGAMESINFO
'Update gamesinfo table. Set ISMISSING column where ISMISSING = -1.';



/******************************************************************************/
/***                          Fields descriptions                           ***/
/******************************************************************************/

DESCRIBE FIELD GAMEYEAR TABLE DATAFILETMP
'Can be null or have ?? in it';

DESCRIBE FIELD ROMNAME TABLE DATAFILETMP
'Rom name with extension';

DESCRIBE FIELD ROMTYPE TABLE DATAFILETMP
'rom, bios, disk, sample';

DESCRIBE FIELD ROMSTATUS TABLE DATAFILETMP
'good, nodump, baddump';

DESCRIBE FIELD PATHTYPE TABLE FILEGAMES
'Type of the path where the gamerom must be stored (roms/samples). Used with
split sample mode.';

DESCRIBE FIELD EFID TABLE FILEROMS
'primary key. Required by entity framework.';

DESCRIBE FIELD ROMPATH TABLE FILESTMP
'Path of the rom inside the archive';

DESCRIBE FIELD ROMNAME TABLE FILESTMP
'Name of the rom without path, but with extension';

DESCRIBE FIELD PLUGINEXT TABLE FILESTMP
'Extension expected according to the plugin';

DESCRIBE FIELD DRIVE TABLE FILESTMP
'Drive letter or network path';

DESCRIBE FIELD DRIVETYPE TABLE FILESTMP
'Unknown, NoDrive, Floppy, Fixed, Network, CDROM, RAM';

DESCRIBE FIELD ZIPCOMMENT TABLE FILESTMP
'Zip file COMMENTS (limited to 100 chars)';

DESCRIBE FIELD SHORTNAME TABLE GAMEROMS
'Rom name without extension';

DESCRIBE FIELD ROMNAME TABLE GAMEROMS
'Rom name with extension from dat';

DESCRIBE FIELD ROMMERGE TABLE GAMEROMS
'Id of the merged rom';

DESCRIBE FIELD ROMTYPE TABLE GAMEROMS
'bios, disk, rom, sample';

DESCRIBE FIELD ROMSTATUS TABLE GAMEROMS
'verified, good, baddump, nodump';

DESCRIBE FIELD SETID TABLE GAMES
'Id of the main set';

DESCRIBE FIELD NAME TABLE GAMES
'Expected name of the file';

DESCRIBE FIELD GAMENUMBER TABLE GAMES
'Game GAMENUMBER from datafile';

DESCRIBE FIELD BIOSOF TABLE GAMES
'Use bios from gameid (not a dat field)';

DESCRIBE FIELD DESCRIPTION TABLE GAMES
'Full game name';

DESCRIBE FIELD GAMEYEAR TABLE GAMES
'ex: 1998, 19??';

DESCRIBE FIELD GAMETYPE TABLE GAMES
'Clone, main, bios or null';

DESCRIBE FIELD ORIGINALNAME TABLE GAMES
'Name from the datafile (needed for releases)';

