Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 5 of 5 total |
Database locations |
Mon, Apr 14 2008 2:47 PM | Permanent Link |
"keith crusius" | I want to have all my tables and whatever system tables (cat, cfg) necessary
located in a single directory. I want to point my program to that directory from any computer on the network and that's it. The program should be able to see all the data. The config file just needs to look for the tables in whatever directory it exists in. That's simplicity, but I can't seem to get that working. Anything else seems like a configuration nightmare times hundreds of installations. Please try to explain again what I need to do to operate similar to how dbisam operated. I need the end users to simply download the program, install it on one computer on the network where the data files will be created and then all the other networked computers simply create a shortcut to the program and at most each user points to the data installation directory. Is this not possible or am I missing the obvious. TIA... |
Mon, Apr 14 2008 4:00 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Keith,
<< I want to have all my tables and whatever system tables (cat, cfg) necessary located in a single directory. I want to point my program to that directory from any computer on the network and that's it. The program should be able to see all the data. >> If you look at the CDCollector example application that comes with ElevateDB, you will see how this is done. In the data module, there is code that creates the database and the tables, views, and stored procedures on the fly, if they don't exist already. This is all determined by the .exe path of the application - the database is stored relative to the application path. << The config file just needs to look for the tables in whatever directory it exists in. That's simplicity, >> Sure, it's simple, but it's also not possible. The configuration file can only go by what your application tells it in terms of the database location. The key is to make sure to use UNC paths if you think that the path names will use different drive mappings on different computers. This will ensure that the database can be found properly from any computer on the network that has the proper rights to the shared UNC location. Here's the code from the CDCollector application that illustrates this: 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 COMPRESSION 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; The only difference for you would be the possible inclusion of the ExpandUNCFileName call for the CREATE DATABASE statement: SQL.Text:='CREATE DATABASE "CDCollector" PATH '+ Engine.QuotedSQLStr(ExtractFilePath(ExpandUNCFileName(Application.ExeName))+'data'); -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Apr 15 2008 9:48 AM | Permanent Link |
"keith crusius" | Thank you! I'll study the examples...
|
Tue, Apr 15 2008 10:51 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | keith
This is what I do procedure Tdm.DataModuleCreate(Sender: TObject); var sl: TStringList; Here: string; begin Here := IncludeTrailingPathDelimiter(ExtractFilePath(ParamStr(0))); if FileExists(Here + 'TfR.ini') then begin sl := TStringList.Create; sl.LoadFromFile(Here + 'TfR.ini'); Here := sl[0]; sl.Free; end; Engine.ConfigPath := Here; Engine.TempTablesPath := GetWindowsTempPath; TfRSession.LocalConfigPath := Here; TfRSession.LocalTempTablesPath := GetWindowsTempPath; end; Roy Lambert [Team Elevate] |
Tue, Apr 15 2008 6:25 PM | Permanent Link |
"keith crusius" | thanks. it's all starting to make a little more sense now...
|
This web page was last updated on Wednesday, May 15, 2024 at 08:40 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |