--
-- File generated with SQLiteStudio v3.4.17 on Sat Jul 19 13:50:47 2025
--
-- Text encoding used: UTF-8
--
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

-- Table: Category
CREATE TABLE IF NOT EXISTS Category (
    CatName TEXT PRIMARY KEY
               UNIQUE
               NOT NULL
);

INSERT INTO Category (
                         CatName
                     )
                     VALUES (
                         'Sequence'
                     );

INSERT INTO Category (
                         CatName
                     )
                     VALUES (
                         'Sequence - Database similarity'
                     );

INSERT INTO Category (
                         CatName
                     )
                     VALUES (
                         'Sequence - pairwise similarity'
                     );


-- Table: ComType
CREATE TABLE IF NOT EXISTS ComType (
    Type TEXT PRIMARY KEY
            UNIQUE
            NOT NULL
);

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'command'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'interactive'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'gui'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        '_birch_'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'birchadmin'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'bldna'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'blnalign'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'blprotein'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'blpalign'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'bldata'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'blmarker'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'blnfetch'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'blpfetch'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'blreads'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'blncbi'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'bltable'
                    );

INSERT INTO ComType (
                        Type
                    )
                    VALUES (
                        'bltree'
                    );


-- Table: File
CREATE TABLE IF NOT EXISTS File (
    Description TEXT,
    PathOrURL   TEXT PRIMARY KEY,
    Action      TEXT
);

INSERT INTO File (
                     Description,
                     PathOrURL,
                     Action
                 )
                 VALUES (
                     'Manual',
                     'bachrest.txt',
                     '$DBFileViewer'
                 );

INSERT INTO File (
                     Description,
                     PathOrURL,
                     Action
                 )
                 VALUES (
                     'Manual',
                     'numseq.txt',
                     '$DBFileViewer'
                 );

INSERT INTO File (
                     Description,
                     PathOrURL,
                     Action
                 )
                 VALUES (
                     'Manual',
                     'blastn.txt',
                     '$DBFileViewer'
                 );

INSERT INTO File (
                     Description,
                     PathOrURL,
                     Action
                 )
                 VALUES (
                     'Manual',
                     'blastx.txt',
                     '$DBFileViewer'
                 );

INSERT INTO File (
                     Description,
                     PathOrURL,
                     Action
                 )
                 VALUES (
                     'Manual',
                     'ribosome.txt',
                     '$DBFileViewer'
                 );

INSERT INTO File (
                     Description,
                     PathOrURL,
                     Action
                 )
                 VALUES (
                     'Manual',
                     'ssearch.txt',
                     '$DBFileViewer'
                 );

INSERT INTO File (
                     Description,
                     PathOrURL,
                     Action
                 )
                 VALUES (
                     'Manual',
                     'fasta.txt',
                     '$DBFileViewer'
                 );


-- Table: package
CREATE TABLE IF NOT EXISTS package (
    Name TEXT PRIMARY KEY
            UNIQUE
            NOT NULL
);

INSERT INTO package (
                        Name
                    )
                    VALUES (
                        'fasta'
                    );

INSERT INTO package (
                        Name
                    )
                    VALUES (
                        'fsap'
                    );

INSERT INTO package (
                        Name
                    )
                    VALUES (
                        'BLAST'
                    );

INSERT INTO package (
                        Name
                    )
                    VALUES (
                        'xylem'
                    );


-- Table: PkgProg
CREATE TABLE IF NOT EXISTS PkgProg (
    Package TEXT REFERENCES package (Name) ON UPDATE CASCADE
                 NOT NULL,
    Program TEXT REFERENCES program (Name) 
                 NOT NULL
);

INSERT INTO PkgProg (
                        Package,
                        Program
                    )
                    VALUES (
                        'fsap',
                        'numseq'
                    );

INSERT INTO PkgProg (
                        Package,
                        Program
                    )
                    VALUES (
                        'fsap',
                        'bachrest'
                    );

INSERT INTO PkgProg (
                        Package,
                        Program
                    )
                    VALUES (
                        'fasta',
                        'ssearch3'
                    );

INSERT INTO PkgProg (
                        Package,
                        Program
                    )
                    VALUES (
                        'fasta',
                        'fasta'
                    );

INSERT INTO PkgProg (
                        Package,
                        Program
                    )
                    VALUES (
                        'xylem',
                        'ribosome'
                    );

INSERT INTO PkgProg (
                        Package,
                        Program
                    )
                    VALUES (
                        'BLAST',
                        'blastx'
                    );

INSERT INTO PkgProg (
                        Package,
                        Program
                    )
                    VALUES (
                        'BLAST',
                        'blastn'
                    );


-- Table: Platform
CREATE TABLE IF NOT EXISTS Platform (
    Name TEXT UNIQUE
            NOT NULL
            PRIMARY KEY
);

INSERT INTO Platform (
                         Name
                     )
                     VALUES (
                         'linux-x86_64'
                     );

INSERT INTO Platform (
                         Name
                     )
                     VALUES (
                         'linux-arm64'
                     );

INSERT INTO Platform (
                         Name
                     )
                     VALUES (
                         'osx-x86_64'
                     );

INSERT INTO Platform (
                         Name
                     )
                     VALUES (
                         'macos-arm64'
                     );


-- Table: ProgCat
CREATE TABLE IF NOT EXISTS ProgCat (
    Program  TEXT REFERENCES program (Name) 
                  NOT NULL,
    Category TEXT REFERENCES Category (CatName) 
                  NOT NULL
);

INSERT INTO ProgCat (
                        Program,
                        Category
                    )
                    VALUES (
                        'numseq',
                        'Sequence'
                    );

INSERT INTO ProgCat (
                        Program,
                        Category
                    )
                    VALUES (
                        'bachrest',
                        'Sequence'
                    );

INSERT INTO ProgCat (
                        Program,
                        Category
                    )
                    VALUES (
                        'blastn',
                        'Sequence - Database similarity'
                    );

INSERT INTO ProgCat (
                        Program,
                        Category
                    )
                    VALUES (
                        'blastx',
                        'Sequence - Database similarity'
                    );

INSERT INTO ProgCat (
                        Program,
                        Category
                    )
                    VALUES (
                        'ribosome',
                        'Sequence'
                    );

INSERT INTO ProgCat (
                        Program,
                        Category
                    )
                    VALUES (
                        'ssearch3',
                        'Sequence - pairwise similarity'
                    );

INSERT INTO ProgCat (
                        Program,
                        Category
                    )
                    VALUES (
                        'fasta',
                        'Sequence - pairwise similarity'
                    );


-- Table: ProgLaunch
CREATE TABLE IF NOT EXISTS ProgLaunch (
    Program TEXT REFERENCES program (Name) 
                 NOT NULL,
    Command TEXT REFERENCES ComType (Type),
    Params  TEXT
);

INSERT INTO ProgLaunch (
                           Program,
                           Command,
                           Params
                       )
                       VALUES (
                           'numseq',
                           'bldna',
                           'DNA/RNA --> numseq'
                       );

INSERT INTO ProgLaunch (
                           Program,
                           Command,
                           Params
                       )
                       VALUES (
                           'bachrest',
                           'bldna',
                           'DNA/RNA --> bachrest'
                       );

INSERT INTO ProgLaunch (
                           Program,
                           Command,
                           Params
                       )
                       VALUES (
                           'numseq',
                           'interactive',
                           'numseq'
                       );

INSERT INTO ProgLaunch (
                           Program,
                           Command,
                           Params
                       )
                       VALUES (
                           'blastn',
                           'command',
                           'blastn <params>'
                       );


-- Table: ProgPlat
CREATE TABLE IF NOT EXISTS ProgPlat (
    Program  TEXT REFERENCES program (Name) 
                  NOT NULL,
    Platform TEXT REFERENCES Platform (Name) 
                  NOT NULL
);

INSERT INTO ProgPlat (
                         Program,
                         Platform
                     )
                     VALUES (
                         'numseq',
                         'linux-x86_64'
                     );

INSERT INTO ProgPlat (
                         Program,
                         Platform
                     )
                     VALUES (
                         'numseq',
                         'linux-arm64'
                     );

INSERT INTO ProgPlat (
                         Program,
                         Platform
                     )
                     VALUES (
                         'numseq',
                         'osx-x86_64'
                     );

INSERT INTO ProgPlat (
                         Program,
                         Platform
                     )
                     VALUES (
                         'numseq',
                         'macos-arm64'
                     );

INSERT INTO ProgPlat (
                         Program,
                         Platform
                     )
                     VALUES (
                         'bachrest',
                         'linux-x86_64'
                     );

INSERT INTO ProgPlat (
                         Program,
                         Platform
                     )
                     VALUES (
                         'bachrest',
                         'linux-arm64'
                     );

INSERT INTO ProgPlat (
                         Program,
                         Platform
                     )
                     VALUES (
                         'bachrest',
                         'osx-x86_64'
                     );

INSERT INTO ProgPlat (
                         Program,
                         Platform
                     )
                     VALUES (
                         'blastn',
                         'linux-x86_64'
                     );

INSERT INTO ProgPlat (
                         Program,
                         Platform
                     )
                     VALUES (
                         'blastn',
                         'linux-arm64'
                     );

INSERT INTO ProgPlat (
                         Program,
                         Platform
                     )
                     VALUES (
                         'blastn',
                         'osx-x86_64'
                     );

INSERT INTO ProgPlat (
                         Program,
                         Platform
                     )
                     VALUES (
                         'blastn',
                         'macos-arm64'
                     );

INSERT INTO ProgPlat (
                         Program,
                         Platform
                     )
                     VALUES (
                         'blastx',
                         'linux-x86_64'
                     );

INSERT INTO ProgPlat (
                         Program,
                         Platform
                     )
                     VALUES (
                         'blastx',
                         'linux-arm64'
                     );

INSERT INTO ProgPlat (
                         Program,
                         Platform
                     )
                     VALUES (
                         'blastx',
                         'osx-x86_64'
                     );

INSERT INTO ProgPlat (
                         Program,
                         Platform
                     )
                     VALUES (
                         'blastx',
                         'macos-arm64'
                     );


-- Table: program
CREATE TABLE IF NOT EXISTS program (
    Name          TEXT PRIMARY KEY
                       NOT NULL
                       UNIQUE,
    Description   TEXT,
    Documentation TEXT,
    Install       TEXT DEFAULT 'BIRCH',
    Platforms     TEXT,
    FOREIGN KEY (
        Documentation
    )
    REFERENCES File (PathOrURL),
    CONSTRAINT CheckInstall CHECK (Install = "BIRCH" OR
                                   Install = "local") 
);

INSERT INTO program (
                        Name,
                        Description,
                        Documentation,
                        Install,
                        Platforms
                    )
                    VALUES (
                        'numseq',
                        'print sequences',
                        'numseq.txt',
                        'BIRCH',
                        'LX,LA,OX,MA'
                    );

INSERT INTO program (
                        Name,
                        Description,
                        Documentation,
                        Install,
                        Platforms
                    )
                    VALUES (
                        'bachrest',
                        'rest. site search',
                        'bachrest.txt',
                        'BIRCH',
                        'LX,LA,OX,MA'
                    );

INSERT INTO program (
                        Name,
                        Description,
                        Documentation,
                        Install,
                        Platforms
                    )
                    VALUES (
                        'blastn',
                        'DNA vs. DNA database',
                        'blastn.txt',
                        'BIRCH',
                        'LX,LA,OX,MA'
                    );

INSERT INTO program (
                        Name,
                        Description,
                        Documentation,
                        Install,
                        Platforms
                    )
                    VALUES (
                        'blastx',
                        'trans. DNA vs. protein',
                        'blastx.txt',
                        'BIRCH',
                        'LX,LA,OX,MA'
                    );

INSERT INTO program (
                        Name,
                        Description,
                        Documentation,
                        Install,
                        Platforms
                    )
                    VALUES (
                        'ribosome',
                        'translate DNA to protein',
                        'ribosome.txt',
                        'BIRCH',
                        'LX,LA,OX,MA'
                    );

INSERT INTO program (
                        Name,
                        Description,
                        Documentation,
                        Install,
                        Platforms
                    )
                    VALUES (
                        'ssearch3',
                        'Smith Waterman alignment',
                        'ssearch.txt',
                        'BIRCH',
                        'LX,LA,OX,MA'
                    );

INSERT INTO program (
                        Name,
                        Description,
                        Documentation,
                        Install,
                        Platforms
                    )
                    VALUES (
                        'fasta',
                        'DNA, protein database search',
                        'fasta.txt',
                        'local',
                        'LX,LA,OX,MA'
                    );


-- Index: program.idx
CREATE INDEX IF NOT EXISTS [program.idx] ON program (
    "Name" ASC
);


-- View: BLASTprogs
CREATE VIEW IF NOT EXISTS BLASTprogs AS
    SELECT Name,
           Description
      FROM program,
           PkgProg
     WHERE (PkgProg.Program = program.Name) AND
           (PkgProg.Package = "BLAST");


COMMIT TRANSACTION;
PRAGMA foreign_keys = on;
