Tim

In the next build would you please include code to create tables, database etc from scratch. I, and I know others, distribute an exe which then creates the tables etc. and it would be nice to know how the master does it.

Roy Lambert

Roy,

<< In the next build would you please include code to create tables,
database etc from scratch. I, and I know others, distribute an exe which
then creates the tables etc. and it would be nice to know how the master
does it. >>

Did you look in the data module (data.pas) code ?

procedure TCDCollectorDataModule.CDCollectorEngineBeforeStart(Sender:
TObject);
begin
  { Always be sure to set ConfigPath property to correct location.
    TempTablesPath property is where temporary tables are stored }
  with CDCollectorEngine do
     begin
     ConfigPath:=ExtractFilePath(Application.ExeName);
     TempTablesPath:=GetTempTablesPath;
     end;
end;

procedure TCDCollectorDataModule.DataModuleCreate(Sender: TObject);
begin
  { Activate the engine.  The engine can also be referenced by the generic
    Engine variable in the edbcomps unit }
  CDCollectorEngine.Active:=True;
  { Now connect the session }
  CDCollectorSession.Connected:=True;
  { Check to see if the database exists or whether it needs to be
    created }
  with ConfigurationQuery do
     begin
     SQL.Text:='SELECT * FROM Databases WHERE
Name='+Engine.QuotedSQLStr('CDCollector');
     Open;
     if (RecordCount=0) then
        begin
        { Database doesn't exist and we need to create it }
        Close;
        SQL.Text:='CREATE DATABASE "CDCollector" PATH '+
                   Engine.QuotedSQLStr(ExtractFilePath(Application.ExeName)+'data');
        ExecSQL;
        end
     else
        Close;
     end;
  { Now we can open the CDCollector database and start to check the
metadata for
    the database.  If anything is missing, we create it now in the proper
order
    so as to respect any foreign key <--> primary key RI relationships.

    NOTE the use of doubled-up single quotes when embedding string
constants in
    the SQL. }
  with CatalogQuery do
     begin
     SQL.Text:='SELECT * FROM Information.Tables WHERE Name=''Artists''';
     Open;
     if (RecordCount=0) then
        begin
        Close;
        SQL.Clear;
        SQL.Add('CREATE TABLE "Artists"');
        SQL.Add('(');
        SQL.Add('"Name" VARCHAR(30) COLLATE "ANSI_CI",');
        SQL.Add('"StartedPerforming" INTEGER,');
        SQL.Add('"StoppedPerforming" INTEGER,');
        SQL.Add('"Comments" CLOB COLLATE "ANSI_CI",');
        SQL.Add('CONSTRAINT "Name" PRIMARY KEY ("Name")');
        SQL.Add(')');
        SQL.Add('DESCRIPTION ''Contains artists featured in the CD
collection''');
        ExecSQL;
        end
     else
        Close;
     SQL.Text:='SELECT * FROM Information.Tables WHERE Name=''Labels''';
     Open;
     if (RecordCount=0) then
        begin
        Close;
        SQL.Clear;
        SQL.Add('CREATE TABLE "Labels"');
        SQL.Add('(');
        SQL.Add('"Name" VARCHAR(30) COLLATE "ANSI_CI",');
        SQL.Add('"Comments" CLOB COLLATE "ANSI_CI",');
        SQL.Add('CONSTRAINT "Name" PRIMARY KEY ("Name")');
        SQL.Add(')');
        SQL.Add('DESCRIPTION ''Contains record labels featured in the
collection''');
        ExecSQL;
        end
     else
        Close;
     SQL.Text:='SELECT * FROM Information.Tables WHERE Name=''Genres''';
     Open;
     if (RecordCount=0) then
        begin
        Close;
        SQL.Clear;
        SQL.Add('CREATE TABLE "Genres"');
        SQL.Add('(');
        SQL.Add('"Name" VARCHAR(20) COLLATE "ANSI_CI",');
        SQL.Add('"Comments" CLOB COLLATE "ANSI_CI",');
        SQL.Add('CONSTRAINT "Name" PRIMARY KEY ("Name")');
        SQL.Add(')');
        SQL.Add('DESCRIPTION ''Contains genres featured in the CD
collection''');
        ExecSQL;
        end
     else
        Close;
     SQL.Text:='SELECT * FROM Information.Tables WHERE Name=''Albums''';
     Open;
     if (RecordCount=0) then
        begin
        Close;
        SQL.Clear;
        SQL.Add('CREATE TABLE "Albums"');
        SQL.Add('(');
        SQL.Add('"No" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0,
INCREMENT BY 1),');
        SQL.Add('"Name" VARCHAR(50) COLLATE "ANSI_CI" UNIQUE,');
        SQL.Add('"Artist" VARCHAR(30) COLLATE "ANSI_CI" NOT NULL,');
        SQL.Add('"Genre" VARCHAR(20) COLLATE "ANSI_CI" NOT NULL,');
        SQL.Add('"Year" INTEGER NOT NULL,');
        SQL.Add('"Label" VARCHAR(30) COLLATE "ANSI_CI" NOT NULL,');
        SQL.Add('"Imported" BOOLEAN DEFAULT FALSE  NOT NULL,');
        SQL.Add('"NumDiscs" INTEGER DEFAULT 1  NOT NULL,');
        SQL.Add('"CoverArt" BLOB COMPRESS 6,');
        SQL.Add('"Comments" CLOB COLLATE "ANSI_CI",');
        SQL.Add('"PurchasedOn" DATE,');
        SQL.Add('"PurchasePrice" DECIMAL(20,2),');
        SQL.Add('CONSTRAINT "No" PRIMARY KEY ("No"),');
        SQL.Add('CONSTRAINT "Artist" FOREIGN KEY ("Artist") REFERENCES
"Artists" ("Name"),');
        SQL.Add('CONSTRAINT "Genre" FOREIGN KEY ("Genre") REFERENCES
"Genres" ("Name"),');
        SQL.Add('CONSTRAINT "Label" FOREIGN KEY ("Label") REFERENCES
"Labels" ("Name")');
        SQL.Add(')');
        SQL.Add('DESCRIPTION ''Contains CD albums in the collection''');
        ExecSQL;
        end
     else
        Close;
     SQL.Text:='SELECT * FROM Information.Tables WHERE Name=''Tracks''';
     Open;
     if (RecordCount=0) then
        begin
        Close;
        SQL.Clear;
        SQL.Add('CREATE TABLE "Tracks"');
        SQL.Add('(');
        SQL.Add('"AlbumNo" INTEGER,');
        SQL.Add('"DiscNo" INTEGER,');
        SQL.Add('"TrackNo" INTEGER,');
        SQL.Add('"TrackName" VARCHAR(50) COLLATE "ANSI_CI" NOT NULL,');
        SQL.Add('"Length" INTERVAL MINUTE TO SECOND NOT NULL,');
        SQL.Add('"Comments" CLOB COLLATE "ANSI_CI",');
        SQL.Add('CONSTRAINT "TrackNo" PRIMARY KEY ("AlbumNo", "DiscNo",
"TrackNo"),');
        SQL.Add('CONSTRAINT "Album" FOREIGN KEY ("AlbumNo") REFERENCES
"Albums" ("No")');
        SQL.Add(')');
        SQL.Add('DESCRIPTION ''Contains CD album tracks''');
        ExecSQL;
        end
     else
        Close;
     SQL.Text:='SELECT * FROM Information.Views WHERE
Name=''PurchaseInformation''';
     Open;
     if (RecordCount=0) then
        begin
        Close;
        SQL.Clear;
        SQL.Add('CREATE VIEW "PurchaseInformation" AS');
        SQL.Add('SELECT COUNT(Name) AS NumAlbums,');
        SQL.Add('SUM(PurchasePrice) AS TotalPurchases,');
        SQL.Add('AVG(PurchasePrice) AS AveragePurchasePrice');
        SQL.Add('FROM Albums');
        SQL.Add('DESCRIPTION ''Total number of albums, purchase price, and
average purchase price for the CD collection''');
        ExecSQL;
        end
     else
        Close;
     SQL.Text:='SELECT * FROM Information.Views WHERE
Name=''PurchaseHistory''';
     Open;
     if (RecordCount=0) then
        begin
        Close;
        SQL.Clear;
        SQL.Add('CREATE VIEW "PurchaseHistory" AS');
        SQL.Add('SELECT EXTRACT(YEAR FROM PurchasedOn) AS Year,');
        SQL.Add('COUNT(Name) AS NumAlbums,');
        SQL.Add('SUM(PurchasePrice) AS TotalPurchases');
        SQL.Add('FROM Albums');
        SQL.Add('GROUP BY Year');
        SQL.Add('DESCRIPTION ''History of album purchases by year''');
        ExecSQL;
        end
     else
        Close;
     SQL.Text:='SELECT * FROM Information.Views WHERE
Name=''ListeningTimes''';
     Open;
     if (RecordCount=0) then
        begin
        Close;
        SQL.Clear;
        SQL.Add('CREATE VIEW "ListeningTimes" AS');
        SQL.Add('SELECT Albums.No,');
        SQL.Add('Albums.Name,');
        SQL.Add('Albums.Artist,');
        SQL.Add('COUNT(Tracks.TrackNo) AS NumTracks,');
        SQL.Add('AVG(Tracks.Length) AS AvgLength,');
        SQL.Add('CAST(SUM(Tracks.Length) AS INTERVAL HOUR TO SECOND) AS
TotalTime');
        SQL.Add('FROM Albums INNER JOIN Tracks ON
Tracks.AlbumNo=Albums.No');
        SQL.Add('GROUP BY Albums.No');
        SQL.Add('ORDER BY Albums.Name');
        SQL.Add('DESCRIPTION ''Total listening times in hours, minutes, and
seconds for each album''');
        ExecSQL;
        end
     else
        Close;
     SQL.Text:='SELECT * FROM Information.Procedures WHERE
Name=''Summaries''';
     Open;
     if (RecordCount=0) then
        begin
        Close;
        SQL.Clear;
        SQL.Add('CREATE PROCEDURE Summaries(IN "SummaryType" CHAR(1)
COLLATE ANSI_CI)');
        SQL.Add('BEGIN');
        SQL.Add('DECLARE Result CURSOR WITH RETURN FOR Stmt;');
        SQL.Add('');
        SQL.Add('CASE SummaryType');
        SQL.Add('-- Genres summary');
        SQL.Add('WHEN ''G'' THEN');
        SQL.Add('   BEGIN');
        SQL.Add('   PREPARE Stmt FROM ''SELECT Genre AS Name, COUNT(Name)
AS NumAlbums,');
        SQL.Add('                      SUM(PurchasePrice) AS
TotalPurchases');
        SQL.Add('                      FROM Albums');
        SQL.Add('                      GROUP BY Genre'';');
        SQL.Add('   OPEN Result;');
        SQL.Add('   END;');
        SQL.Add('-- Labels summary');
        SQL.Add('WHEN ''L'' THEN');
        SQL.Add('   BEGIN');
        SQL.Add('   PREPARE Stmt FROM ''SELECT Label AS Name, COUNT(Name)
AS NumAlbums,');
        SQL.Add('                      SUM(PurchasePrice) AS
TotalPurchases');
        SQL.Add('                      FROM Albums');
        SQL.Add('                      GROUP BY Label'';');
        SQL.Add('   OPEN Result;');
        SQL.Add('   END;');
        SQL.Add('-- Artists summary');
        SQL.Add('WHEN ''A'' THEN');
        SQL.Add('   BEGIN');
        SQL.Add('   PREPARE Stmt FROM ''SELECT Artist AS Name, COUNT(Name)
AS NumAlbums,');
        SQL.Add('                      SUM(PurchasePrice) AS
TotalPurchases');
        SQL.Add('                      FROM Albums');
        SQL.Add('                      GROUP BY Artist'';');
        SQL.Add('   OPEN Result;');
        SQL.Add('   END;');
        SQL.Add('END CASE;');
        SQL.Add('END');
        SQL.Add('DESCRIPTION ''Produces a summary of the number of albums
and total album purchases by genre, label, or artist''');
        ExecSQL;
        end
     else
        Close;
     end;
  { Now open the tables }
  ArtistsTable.Open;
  LabelsTable.Open;
  GenresTable.Open;
  AlbumsTable.Open;
  TracksTable.Open;
end;

--
Tim Young
Elevate Software
www.elevatesoft.com