Deploying an ElevateDB application is a fairly simple process, but can seem like a daunting task for those that haven't spent a lot of time with the product. Complicating the deployment process further is the fact that ElevateDB is extremely flexible and allows for several different methods of deployment. This article will break down the deployment process for ElevateDB client applications and the ElevateDB Server into simple, easy-to-use steps.

Before going any further, you should at least become familiar with the general architecture of ElevateDB, especially how it uses the configuration path to store the configuration file. You can find out more information on the architecture here in the manual:

ElevateDB Architecture


Client Applications Requirements

---

ElevateDB is available for many different development environments. The following list outlines the client application deployment requirements for each supported development environment:


Client Application Installation and Configuration

---

The first step one must take when designing an application with ElevateDB is deciding how the application should be installed and configured. ElevateDB applications can be installed via a simple copy operation, by using a setup program such as InstallShield or Inno Setup, or by using an MSI package and the Windows installer services. Instead of spending time on each specific installation method, we'll instead cover the basic steps that need to be completed for an ElevateDB application to be functional, and then you can take that information and apply it to your specific installation method.

The following steps must take place before an ElevateDB application can be used:

• Specific Installation Requirements
  This was covered above, and would include any required registry keys/values.



• Determining the Client Application Type
  ElevateDB can be used in a client application with local sessions that directly access ElevateDB databases, and/or with remote sessions that access ElevateDB databases via an ElevateDB Server. Both local and remote sessions can be used in the same client application.



• Creating and Populating the Configuration and Databases
  For client applications that use local sessions, ElevateDB must be configured so that it uses a specific configuration file path where the database, user/role, job, and store definitions are stored. For remote sessions this is not necessary since this configuration is handled by the ElevateDB Server installation and configuration process. Most applications will need/want to set up standard database, user/role, job, and store definitions as part of the installation and configuration.

VCL/LCL Framework Requirements

---

As indicated above, most developers will choose to compile ElevateDB directly into their applications that use the VCL and LCL component frameworks. The VCL is available in, and can be deployed using, runtime packages. ElevateDB is also provided as a single runtime package. Since runtime packages are just a special type of DLL, the only requirement for the ElevateDB runtime package is that it be present in the same path as the main application, or somewhere in the system path so that it can be found when the application needs to load it.

.NET Data Provider Requirements

---

The ElevateDB .NET Data Provider comes with utilities that handle the installation and removal of the appropriate .NET Data Provider entries to/from the global machine.config for the appropriate version of .NET. It is recommended that you use these utilities to ensure proper installation of the .NET Data Provider entries in the machine.config file (XML format). However, this process is completely optional, and is only required if you intend to use the database-agnostic factory classes with .NET. Most applications that target ElevateDB only will not need these machine.config entries. You can find out more information on these utilities here in the ElevateDB manual:

.NET Data Provider Installation and Distribution


If you don't wish to use the provided utilities, then the required entries for the machine.config file are as follows:

<system.data>
   <DbProviderFactories>
      <add name="ElevateDB Data Provider"
           invariant="Elevate.ElevateDB.Data"
           description="ElevateDB 2 .Net Data Provider"
           type="Elevate.ElevateDB.Data.EDBProviderFactory, Elevate.ElevateDB.Data,
                 Version=2.3.14.0, Culture=neutral, PublicKeyToken=cf9bc1202c75e9e2"/>
   </DbProviderFactories>
</system.data>

With the advent of the .NET 4.0 framework with Visual Studio 2010, you need to update the machine.config for both .NET 2.0 and .NET 4.0. The locations of the machine.config file are as follows:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319

ODBC Driver Requirements

---

The Elevate ODBC Driver requires some core registry keys/values before it can be used on any given Windows installation. The ElevateDB DAC product installation automatically installs the ODBC Driver and adds these registry keys, but you may want to use a custom installation program that seamlessly installs the driver along with your application. The following is a listing of the required registry keys/values for the driver installation in Windows Registry Editor export format:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ElevateDB 2 ODBC Driver]
"APILevel"="3.00"
"ConnectFunctions"="YYY"
"Driver"="<InstallDir>\\edbodbc.dll"
"DriverODBCVer"="03.00"
"FileExtns"="*.EDBTbl,*.EDBIdx,*.EDBBlb"
"FileUsage"="1"
"SQLLevel"="0"
"Setup"="<InstallDir>\\edbodbc.dll"
"UsageCount"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers]
"ElevateDB 2 ODBC Driver"="Installed"

Some notes:

• The ODBCINST.INI and ODBCINST.INI\ODBC Drivers registry keys are standard, and should always be present on any Windows installation.



• You can use a different name other than "ElevateDB 2 ODBC Driver" for the main registry key and the registry value under the ODBC Drivers key. Just be sure to use the same name in both places.



• The <InstallDir> strings are just placeholders, so be sure to replace them with the actual location of the edbodbc.dll file when creating the registry entries.



• The edbodbc.dll file does not need to be in the system path or installed into the Windows system directory structure. Specifying the location in the above registry entries are enough to allow the ODBC Driver Manager to find the ElevateDB ODBC Driver properly.



• When installing the ODBC driver on a 64-bit version of Windows, be sure to use the Wow6432Node key under the HKEY_LOCAL_MACHINE\SOFTWARE key. The ElevateDB ODBC Driver is a 32-bit DLL, so it needs to be installed into the 32-bit registry key space for 64-bit versions of Windows.

You can optionally choose to add system or user DSN (Data Source Names) when installing your application, or you can choose to use connection strings only in your application. If you do choose to use DSNs and want to automate their installation, please refer to this section of the manual for more information on the registry entries that are required:

Data Source Registry Entries

If you intend to use the ElevateDB ODBC Driver with the ODBC.NET Data Provider under .NET, please be sure to use the Unicode version of the ElevateDB ODBC Driver found in the following installation location:

<InstallDir>\libs\edbodbcunicode

.NET is 100% Unicode, so the Unicode version of the driver is required for proper operation.

Creating and Populating the Configuration and Databases

---

When installing an ElevateDB application that will use local sessions, you can choose to set up the configuration and database(s) during installation, or automatically during application startup. The benefits of each are outlined below:


Regardless of which method the you choose, you must execute the following steps when setting up the configuration and database(s):

• Set the desired configuration settings
  This includes setting the configuration path, and can optionally include customizing the various aspects of the engine such as file extensions, root names for the configuration file and catalogs, the encryption password, the engine signature, large file support, etc.



• Create any users, roles, databases, jobs, and/or stores
  At a bare minimum, you will need to create at least one database, but you may also create your user security scheme, jobs for executing scheduled SQL scripts, and stores for use with replication and file transfers.



• Create all database objects in the database(s) and populate any tables
  Once you have defined a database, you can then populate it with tables, views, procedures, and functions. You can also automatically populate any tables that you wish to have pre-populated, such as lookup tables.

VCL/LCL Framework Example

---

The following is a Delphi/Lazarus example of setting up the configuration and creating the necessary database objects. In this example, the configuration path is set to the same path as the application, and the database is created in a subdirectory, which may not be ideal if the application is installed in the Program Files directory in Windows. In such a case, you should consider storing the configuration and database(s) in a directory that will be writable for all users.

You can find the complete source code to this example in the:

<InstallDir>\examples\cdcollector\source

installation location.

const

   COLLATION = 'ANSI';

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 "'+COLLATION+'_CI",');
         SQL.Add('"StartedPerforming" INTEGER,');
         SQL.Add('"StoppedPerforming" INTEGER,');
         SQL.Add('"Comments" CLOB COLLATE "'+COLLATION+'_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 "'+COLLATION+'_CI",');
         SQL.Add('"Comments" CLOB COLLATE "'+COLLATION+'_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 "'+COLLATION+'_CI",');
         SQL.Add('"Comments" CLOB COLLATE "'+COLLATION+'_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 "'+COLLATION+'_CI" UNIQUE,');
         SQL.Add('"Artist" VARCHAR(30) COLLATE "'+COLLATION+'_CI" NOT NULL,');
         SQL.Add('"Genre" VARCHAR(20) COLLATE "'+COLLATION+'_CI" NOT NULL,');
         SQL.Add('"Year" INTEGER NOT NULL,');
         SQL.Add('"Label" VARCHAR(30) COLLATE "'+COLLATION+'_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 "'+COLLATION+'_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 "'+COLLATION+'_CI" NOT NULL,');
         SQL.Add('"Length" INTERVAL MINUTE TO SECOND NOT NULL,');
         SQL.Add('"Comments" CLOB COLLATE "'+COLLATION+'_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 "'+COLLATION+'_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;
end;

.NET Data Provider Example

---

The following is a C# example of setting up the configuration and creating the necessary database objects. In this example, the configuration path is set to the same path as the application, and the database is created in a subdirectory, which may not be ideal if the application is installed in the Program Files directory in Windows. In such a case, you should consider storing the configuration and database(s) in a directory that will be writable for all users.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Elevate.ElevateDB.Data;

namespace CDCollector
{
 class DataModule
 {

  public DataModule()
  {
   String ApplicationPath = Path.GetDirectoryName(Application.ExecutablePath);
   String CRLF = "\r\n";

   // Set up the connection - notice that we're using the system-defined Configuration database initially
   EDBConnection DataConnection = new EDBConnection(@"TYPE=LOCAL;" +
                                                    "CONFIGPATH=" + ApplicationPath + ";" +
                                                    "DATABASE=Configuration;" +
                                                    "UID=Administrator;" +
                                                    "PWD=EDBDefault");
   // Open the connection
   DataConnection.Open();

   EDBCommand DataCommand = new EDBCommand();

   DataCommand.Connection = DataConnection;

   // Check to see if the database exists or whether it needs to be created
   DataCommand.CommandText = "SELECT * FROM Databases WHERE Name='CDCollector'";
   if (DataCommand.Execute() == 0)
   {
       // Database doesn't exist and we need to create it
       DataCommand.CommandText = "CREATE DATABASE CDCollector PATH '" +
                                 ApplicationPath + Path.DirectorySeparatorChar + "data'";
       DataCommand.ExecuteNonQuery();
   }

   // 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.

   DataConnection.ChangeDatabase("CDCollector");

   DataCommand.CommandText = "SELECT * FROM Information.Tables WHERE Name='Artists'";
   if (DataCommand.Execute() == 0)
   {
       DataCommand.CommandText = "CREATE TABLE Artists " + CRLF +
                                "( " + CRLF +
                                "Name VARCHAR(30) COLLATE UNI_CI, " + CRLF +
                                "StartedPerforming INTEGER, " + CRLF +
                                "StoppedPerforming INTEGER, " + CRLF +
                                "Comments CLOB COLLATE UNI_CI, " + CRLF +
                                "CONSTRAINT Name PRIMARY KEY (Name)" +
                                ")" +
                                "DESCRIPTION 'Contains artists featured in the CD collection'";
       DataCommand.ExecuteNonQuery();
   }

   DataCommand.CommandText = "SELECT * FROM Information.Tables WHERE Name='Labels'";
   if (DataCommand.Execute() == 0)
   {
       DataCommand.CommandText = "CREATE TABLE Labels " + CRLF +
                                "( " + CRLF +
                                "Name VARCHAR(30) COLLATE UNI_CI, " + CRLF +
                                "Comments CLOB COLLATE UNI_CI, " + CRLF +
                                "CONSTRAINT Name PRIMARY KEY (Name) " + CRLF +
                                ") " + CRLF +
                                "DESCRIPTION 'Contains record labels featured in the collection'";
       DataCommand.ExecuteNonQuery();
   }

   DataCommand.CommandText = "SELECT * FROM Information.Tables WHERE Name='Genres'";
   if (DataCommand.Execute() == 0)
   {
       DataCommand.CommandText = "CREATE TABLE Genres " + CRLF +
                                "( " + CRLF +
                                "Name VARCHAR(20) COLLATE UNI_CI, " + CRLF +
                                "Comments CLOB COLLATE UNI_CI, " + CRLF +
                                "CONSTRAINT Name PRIMARY KEY (Name) " + CRLF +
                                ") " + CRLF +
                                "DESCRIPTION 'Contains genres featured in the CD collection'";
       DataCommand.ExecuteNonQuery();
   }

   DataCommand.CommandText = "SELECT * FROM Information.Tables WHERE Name='Albums'";
   if (DataCommand.Execute() == 0)
   {
       DataCommand.CommandText = "CREATE TABLE Albums " + CRLF +
                                "( " + CRLF +
                                "No INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1), " + CRLF +
                                "Name VARCHAR(50) COLLATE UNI_CI UNIQUE, " + CRLF +
                                "Artist VARCHAR(30) COLLATE UNI_CI NOT NULL, " + CRLF +
                                "Genre VARCHAR(20) COLLATE UNI_CI NOT NULL, " + CRLF +
                                "Year INTEGER NOT NULL, " + CRLF +
                                "Label VARCHAR(30) COLLATE UNI_CI NOT NULL, " + CRLF +
                                "Imported BOOLEAN DEFAULT FALSE  NOT NULL, " + CRLF +
                                "NumDiscs INTEGER DEFAULT 1  NOT NULL, " + CRLF +
                                "CoverArt BLOB COMPRESSION 6, " + CRLF +
                                "Comments CLOB COLLATE UNI_CI, " + CRLF +
                                "PurchasedOn DATE, " + CRLF +
                                "PurchasePrice DECIMAL(20,2), " + CRLF +
                                "CONSTRAINT No PRIMARY KEY (No), " + CRLF +
                                "CONSTRAINT Artist FOREIGN KEY (Artist) REFERENCES Artists (Name), " + CRLF +
                                "CONSTRAINT Genre FOREIGN KEY (Genre) REFERENCES Genres (Name), " + CRLF +
                                "CONSTRAINT Label FOREIGN KEY (Label) REFERENCES Labels (Name) " + CRLF +
                                ") " + CRLF +
                                "DESCRIPTION 'Contains CD albums in the collection'";
       DataCommand.ExecuteNonQuery();
   }

   DataCommand.CommandText = "SELECT * FROM Information.Tables WHERE Name='Tracks'";
   if (DataCommand.Execute() == 0)
   {
       DataCommand.CommandText = "CREATE TABLE Tracks " + CRLF +
                                "( " + CRLF +
                                "AlbumNo INTEGER, " + CRLF +
                                "DiscNo INTEGER, " + CRLF +
                                "TrackNo INTEGER, " + CRLF +
                                "TrackName VARCHAR(50) COLLATE UNI_CI NOT NULL, " + CRLF +
                                "Length INTERVAL MINUTE TO SECOND NOT NULL, " + CRLF +
                                "Comments CLOB COLLATE UNI_CI, " + CRLF +
                                "CONSTRAINT TrackNo PRIMARY KEY (AlbumNo, DiscNo, TrackNo), " + CRLF +
                                "CONSTRAINT Album FOREIGN KEY (AlbumNo) REFERENCES Albums (No) " + CRLF +
                                ") " + CRLF +
                                "DESCRIPTION 'Contains CD album tracks'";
       DataCommand.ExecuteNonQuery();
   }

   DataCommand.CommandText = "SELECT * FROM Information.Views WHERE Name='PurchaseInformation'";
   if (DataCommand.Execute() == 0)
   {
       DataCommand.CommandText = "CREATE VIEW PurchaseInformation AS " + CRLF +
                                "SELECT COUNT(Name) AS NumAlbums, " + CRLF +
                                "SUM(PurchasePrice) AS TotalPurchases, " + CRLF +
                                "AVG(PurchasePrice) AS AveragePurchasePrice " + CRLF +
                                "FROM Albums " + CRLF +
                                "DESCRIPTION 'Total number of albums, purchase price, and average purchase price for the CD collection'";
       DataCommand.ExecuteNonQuery();
   }

   DataCommand.CommandText = "SELECT * FROM Information.Views WHERE Name='PurchaseHistory'";
   if (DataCommand.Execute() == 0)
   {
       DataCommand.CommandText = "CREATE VIEW PurchaseHistory AS " + CRLF +
                                "SELECT EXTRACT(YEAR FROM PurchasedOn) AS Year, " + CRLF +
                                "COUNT(Name) AS NumAlbums, " + CRLF +
                                "SUM(PurchasePrice) AS TotalPurchases " + CRLF +
                                "FROM Albums " + CRLF +
                                "GROUP BY Year " + CRLF +
                                "DESCRIPTION 'History of album purchases by year'";
       DataCommand.ExecuteNonQuery();
   }

   DataCommand.CommandText = "SELECT * FROM Information.Views WHERE Name='ListeningTimes'";
   if (DataCommand.Execute() == 0)
   {
       DataCommand.CommandText = "CREATE VIEW ListeningTimes AS " + CRLF +
                                "SELECT Albums.No, " + CRLF +
                                "Albums.Name, " + CRLF +
                                "Albums.Artist, " + CRLF +
                                "COUNT(Tracks.TrackNo) AS NumTracks, " + CRLF +
                                "AVG(Tracks.Length) AS AvgLength, " + CRLF +
                                "CAST(SUM(Tracks.Length) AS INTERVAL HOUR TO SECOND) AS TotalTime " + CRLF +
                                "FROM Albums INNER JOIN Tracks ON Tracks.AlbumNo=Albums.No " + CRLF +
                                "GROUP BY Albums.No " + CRLF +
                                "ORDER BY Albums.Name " + CRLF +
                                "DESCRIPTION 'Total listening times in hours, minutes, and seconds for each album'";
       DataCommand.ExecuteNonQuery();
   }

   DataCommand.CommandText = "SELECT * FROM Information.Procedures WHERE Name='Summaries'";
   if (DataCommand.Execute() == 0)
   {
       DataCommand.CommandText = "CREATE PROCEDURE Summaries(IN SummaryType CHAR(1) COLLATE UNI_CI) " + CRLF +
                                "BEGIN " + CRLF +
                                "DECLARE Result CURSOR WITH RETURN FOR Stmt; " + CRLF +
                                "" +
                                "CASE SummaryType " + CRLF +
                                "-- Genres summary " + CRLF +
                                "WHEN 'G' THEN " + CRLF +
                                "   BEGIN " + CRLF +
                                "   PREPARE Stmt FROM 'SELECT Genre AS Name, COUNT(Name) AS NumAlbums, " + CRLF +
                                "                      SUM(PurchasePrice) AS TotalPurchases " + CRLF +
                                "                      FROM Albums " + CRLF +
                                "                      GROUP BY Genre'; " + CRLF +
                                "   OPEN Result; " + CRLF +
                                "   END; " + CRLF +
                                "-- Labels summary " + CRLF +
                                "WHEN 'L' THEN " + CRLF +
                                "   BEGIN " + CRLF +
                                "   PREPARE Stmt FROM 'SELECT Label AS Name, COUNT(Name) AS NumAlbums, " + CRLF +
                                "                      SUM(PurchasePrice) AS TotalPurchases " + CRLF +
                                "                      FROM Albums " + CRLF +
                                "                      GROUP BY Label'; " + CRLF +
                                "   OPEN Result; " + CRLF +
                                "   END; " + CRLF +
                                "-- Artists summary " + CRLF +
                                "WHEN 'A' THEN " + CRLF +
                                "   BEGIN " + CRLF +
                                "   PREPARE Stmt FROM 'SELECT Artist AS Name, COUNT(Name) AS NumAlbums, " + CRLF +
                                "                      SUM(PurchasePrice) AS TotalPurchases " + CRLF +
                                "                      FROM Albums " + CRLF +
                                "                      GROUP BY Artist'; " + CRLF +
                                "   OPEN Result; " + CRLF +
                                "   END; " + CRLF +
                                "END CASE; " + CRLF +
                                "END " + CRLF +
                                "DESCRIPTION 'Produces a summary of the number of albums and total album purchases by genre, label, or artist'";
       DataCommand.ExecuteNonQuery();
   }
  }
 }
}

ODBC Driver Example

---

Because most ODBC-compliant applications use different methods and techniques for exposing the ODBC functionality to the end user or developer, it is not possible to detail them all here. However, the techniques for creating the configuration and database objects are exactly like those found in the .NET Data Provider example above:

• Open a connection using the Configuration database as the default database



• Create any database, user/role, job, and/or store objects



• Switch to the desired database(s) that you just created



• Create any database objects (tables, views, functions, and procedures) in the database

ElevateDB Server Requirements

---

The ElevateDB Server is contained within a single executable, called edbsrvr.exe by default, that can run as both a normal application and a Windows service. It uses a single .ini file for its startup configuration that is named based upon the root name of the executable, and is called edbsrvr.ini by default. For more information on configuring and starting the ElevateDB Server, including details about the location and contents of the edbsrvr.ini file, please see the following topic in the manual:

Starting and Configuring the ElevateDB Server


Apart from the .ini startup configuration file, the only other requirement is that you ensure that the ElevateDB Server can properly accept requests on the desired port by making sure that any firewalls that are running on the target machine are configured to allow incoming connections on the IP address and port that the ElevateDB Server is listening on. By default, the ElevateDB Server binds to, and listens for, incoming connections on port 12010 for all available IP addresses.

The following links will show you how to use the netsh command under Windows XP or higher to enable/disable the Windows Firewall for the ElevateDB Server and the port that is listening on:

Administering Windows Firewall with Netsh

How to use the "netsh advfirewall firewall" under Windows Vista and Higher

ElevateDB Server Installation and Configuration

---

The following steps should be taken when installing the ElevateDB Server with your application:

• Change the Executable Name (Optional)
  If you do not want to risk any conflicts with other applications that use the ElevateDB Server under Windows, you should consider renaming the edbsrvr.exe executable to a name that more closely resembles your application. Just remember that the ElevateDB Server will then use the same root name for the .ini startup configuration file.



• Install the ElevateDB Server
  The recommended location of the ElevateDB Server executable is the standard Program Files directory under Windows. Just remember that your installation program will need to have the proper privileges in order to copy any files into the Program Files directory.



• Create the Startup Configuration .INI File
  Although the ElevateDB Server will create the startup configuration .ini file automatically, you will usually need to customize the ElevateDB Server startup configuration .ini file during installation with the desired settings. At a bare minimum, you will need to set the configuration path used by the ElevateDB Server. The configuration path should normally be set to a location that has full create/read/write/delete privileges for the user account under which the ElevateDB Server will be running. See the Starting and Configuring the ElevateDB Server manual topic for a reference to the startup configuration .ini file options.



• Set up the ElevateDB Server as a Windows Service (Optional)
  Although this item is listed as optional, it is recommended that you always install the ElevateDB Server as a Windows service under Windows Vista or higher. It is also recommended that you configure the ElevateDB Server to not interact with the desktop. Windows Vista and higher have deprecated the ability of services to directly interact with the desktop as an important security measure, and the ElevateDB Server can be administered remotely via the ElevateDB Manager, so the user interface is only useful for modifying the startup configuration .ini file. See the Starting and Configuring the ElevateDB Server manual topic for information on installing the ElevateDB Server as a Windows service.



• Create Configuration and Database Objecs (Optional)
  Although this item is listed as optional, you will normally want to go ahead and create the necessary configuration (databases, users/roles, jobs, and stores) objects and database objects (tables, views, functions, and procedures) during the installation of the ElevateDB Server. The recommended way to do this is to simply write a client application that uses a local session/connection to execute the steps outlined above for creating these objects, and execute that application during the installation process, passing any necessary information such as the configuration path using command-line parameters.



• Start the ElevateDB Server (Optional)
  This item is optional. If the installation will be running the ElevateDB Server as a normal application, then you can just place a shortcut link on the Windows desktop or Start menu and let the end user start up the ElevateDB Server. If the installation will be running the ElevateDB Server as a Windows service, then you can start up the server automatically by following the instructions in the Starting and Configuring the ElevateDB Server manual topic.

Summary

---

Hopefully we've been able to give you a little better understanding of how to deploy your ElevateDB applications. As always, if you have any questions at all, please feel free to post them in the ElevateDB newsgroup here:

ElevateDB General Support Forum

This will allow everyone to share in any new ideas or comments that you may have regarding this article.