As described in the previous topic SQLight is used as the database. Delphi XE7 Professional includes the FireDAC components which serve as interface to the database. Since the interface components do not allow the automatic creation of tables and fields from the FieldDefinitions a SQL CREATE script is used instead. This script will be executed when a new database is generated.
All the tables use one field with the name UID as primary index field. Relational tables use fields with the name “…_ID” to connect to the main table, i.e. “CUSTOMER_ID” in the ORDER table. I use an INTEGER PRIMARY KEY AUTOINCREMENT as primary index field which provides fast lookups with SQLLight. (There are no secondary indexes defined in the database yet, to optimize sorting performance they can be added anytime later).
This is an excerpt of the SQL script which creates the tables for the WPCubed Calculus Multi-Demo. As mentioned in the introduction, only the bare minimum of tables and fields for this tasks are used by the application to not obscure what is essential for the task:
CREATE TABLE "COUNTRIES" (
"UID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"NAME" varchar(60),
"EU" INTEGER,
"VAT_RATE_REDUCED" REAL,
"VAT_RATE_REDUCED_LOW" REAL,
"VAT_RATE" REAL,
"CODE" varchar(6),
"DATE" DATE
);
CREATE TABLE "CUSTOMERS" (
"UID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"NAME" varchar(100), // We only have a limited number of adress
"ADR1" varchar(100), // field in this demo application
"ADR2" varchar(100),
"CITY" varchar(100),
"STATE" varchar(100),
"EMAIL" varchar(100),
"COUNTRY_ID" INTEGER,
"VAT_ID" varchar(20), // this is the VAT tax number
"COMMENT" varchar(100),
"REBATE" REAL
);
CREATE TABLE "PRODUCTS" (
"UID" INTEGER PRIMARY KEY AUTOINCREMENT,
"NAME" varchar(100),
"DESCRIPTION" varchar(200),
"PRICE" REAL,
"VAT_SELECT" INTEGER // Select VAT, 0=normal, 1=reduced
);
CREATE TABLE "ORDERS" (
"UID" INTEGER PRIMARY KEY AUTOINCREMENT,
"ORDER_DATE" DATE,
"ORDER_DATE_YEAR" INTEGER,
"ORDER_DATE_MONTH" INTEGER,
"CUSTOMER_ID" INTEGER,
"COUNTRY_ID" INTEGER,
"VAT_ID" varchar(100),
"EUTRADE" INTEGER,
"REFERENCE" varchar(100),
"CUSTOMER_ADR" varchar(300),
"VAT_RATE" REAL,
"VAT_RATE_REDUCED" REAL,
"VAT_RATE_REDUCED_LOW" REAL,
"VOID" Integer
);
CREATE TABLE "ORDERITEM" (
"UID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"ORDER_ID" INTEGER NOT NULL,
"CUSTOMER_ID" INTEGER NOT NULL,
"PRODUCT_ID" INTEGER NOT NULL,
"VAT_SELECT" INTEGER NOT NULL,
"COUNT" REAL,
"TEXT" varchar(100),
"UNIT_PRICE" REAL,
"REBATE_PERCENT" REAL,
"REBATE_ABSOLUT" REAL,
"PAYMENT_ID" INTEGER
);
These are the main component in the data module to set up the data connection:
As described in the introduction the demo was created to also integrate value added tax handling (VAT). To do so, I located the VAT rates (http://ec.europa.eu/taxation_customs/taxation/vat/how_vat_works/rates/index_en.htm) and created a constant record array to initialize the country table. Please use this table with care since the values may be outdated in the meantime.
type TEUCountry = record
name, en_name, code : String;
red1, red2, vat : Single;
end;
// VAT table of July 2014 - Source:
// http://ec.europa.eu/taxation_customs/taxation/vat/how_vat_works/rates/index_en.htm
// We did not copy the "parking rate" and the "super reduced rate"
const EUCountries : array[1..28] of TEUCountry
= ( (name:'Belgien'; en_name:'Belgium'; code:'BE'; red1:6; red2:12; vat:21),
(name:'Bulgarien'; en_name:'Bulgaria'; code:'BG'; red1:9; red2:9; vat:20),
(name:'Tschechische Republik'; en_name:'CzechRepublic'; code:'CZ'; red1:15; red2:15; vat:21),
(name:'Dänemark'; en_name:'Denmark'; code:'DK'; red1:25; red2:25; vat:25), // DK does not have a reduced rate!
(name:'Deutschland'; en_name:'Germany'; code:'DE'; red1:7; red2:7; vat:19),
(name:'Estland'; en_name:'Estonia'; code:'EE'; red1:9; red2:9; vat:20),
(name:'Griechenland'; en_name:'Greece'; code:'EL'; red1:6.5; red2:13; vat:23),
(name:'Spanien'; en_name:'Spain'; code:'ES'; red1:10; red2:10; vat:21),
(name:'Frankreich'; en_name:'France'; code:'FR'; red1:5.5; red2:10; vat:20),
(name:'Kroatien'; en_name:'Croatia'; code:'HR'; red1:5; red2:13; vat:25),
(name:'Irland'; en_name:'Ireland'; code:'IE'; red1:9; red2:13.5; vat:23),
(name:'Italien'; en_name:'Italy'; code:'IT'; red1:10; red2:10; vat:22),
(name:'Zypern'; en_name:'Cyprus'; code:'CY'; red1:5; red2:9; vat:19),
(name:'Lettland'; en_name:'Latvia'; code:'LV'; red1:12; red2:12; vat:21),
(name:'Litauen'; en_name:'Lithuania'; code:'LT'; red1:5; red2:9; vat:21),
(name:'Luxemburg'; en_name:'Luxembourg'; code:'LU'; red1:6; red2:12; vat:15),
(name:'Ungarn'; en_name:'Hungary'; code:'HU'; red1:5; red2:18; vat:27),
(name:'Malta'; en_name:'Malta'; code:'MT'; red1:5; red2:7; vat:18),
(name:'Niederlande'; en_name:'Netherlands'; code:'NL'; red1:6; red2:6; vat:21),
(name:'Österreich'; en_name:'Austria'; code:'AT'; red1:10; red2:10; vat:20),
(name:'Polen'; en_name:'Poland'; code:'PL'; red1:5; red2:8; vat:23),
(name:'Portugal'; en_name:'Portugal'; code:'PT'; red1:6; red2:13; vat:23),
(name:'Rumänien'; en_name:'Romania'; code:'RO'; red1:5; red2:9; vat:24),
(name:'Slowenien'; en_name:'Slovenia'; code:'SI'; red1:9.5; red2:9.5; vat:22),
(name:'Slowakei'; en_name:'Slovakia'; code:'SK'; red1:10; red2:10; vat:20),
(name:'Finnland'; en_name:'Finland'; code:'FI'; red1:10; red2:14; vat:24),
(name:'Schweden'; en_name:'Sweden'; code:'SE'; red1:6; red2:12; vat:25),
(name:'Vereinigtes Königreich'; en_name:'United Kingdom'; code:'UK'; red1:5; red2:5; vat:20) );
Adding the EU countries to the table is easy:
procedure TWPDataModule.AddEUCountries;
var i : Integer;
begin
for I := Low(EUCountries) to High(EUCountries) do
begin
tblCountry.Append;
tblCountryNAME.AsString := EUCountries[i].en_name;
tblCountryCODE.AsString := EUCountries[i].code;
tblCountryEU.AsInteger := 1;
tblCountryVAT_RATE_REDUCED.AsFloat := EUCountries[i].red2;
tblCountryVAT_RATE_REDUCED_LOW.AsFloat := EUCountries[i].red1;
tblCountryVAT_RATE.AsFloat := EUCountries[i].vat;
// This is the date the VAT value was valid – according to
// the quoted document.
tblCountryDATE.AsDateTime := EncodeDate( 2014, 7, 1); tblCountry.Post;
end;
end;
A database is opened or created with this method:
procedure TWPDataModule.DataOpen(Filename : String);
var createnew : Boolean;
begin
createnew := not FileExists(Filename);
with dbMain do begin
Close;
// create temporary connection definition
with Params do begin
Clear;
Add('DriverID=SQLite');
Add('Database=' + FileName);
end;
Open;
if createnew then
begin
FDQuery1.ExecSQL;
end;
try
tblCountry.Open;
tblCustomers.Open;
tblCustomersLookup.Open;
tblOrderItems.Open;
tblOrders.Open;
tblProducts.Open;
// … open other tables
except
Close;
createnew := false;
end;
if createnew then
begin
AddEUCountries;
AddSampleData;
end;
end;
end;
The Methods AddEUCountries fills the country table with the items of the VAT list above, AddSampleData adds some customer and product data so the program does not start completely empty.