unit cpMain;
{$mode objfpc}{$H+}
interface
uses
Classes, SysUtils, sqlite3conn, sqldb, FileUtil, Forms, Controls, Graphics,
Dialogs, StdCtrls;
type
{ TMainForm }
TMainForm = class(TForm)
BtnCreate: TButton;
InfoLabel: TLabel;
SQLite3Connection: TSQLite3Connection;
SQLTransaction: TSQLTransaction;
procedure BtnCreateClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
{ private declarations }
procedure CreateRecordIDs;
procedure CreateResources;
procedure CreateContacts;
procedure CreateEvents;
procedure CreateTasks;
public
{ public declarations }
procedure CreateDB(AFileName: String);
end;
var
MainForm: TMainForm;
implementation
{$R *.lfm}
const
DB_NAME = 'TvPlanit_Demo.db';
// More information on the use of these values is below.
// They need not be set as constants in your application. They can be any valid value
APPLICATION_ID = 1189021115; // must be a 32-bit Unsigned Integer (Longword 0 .. 4294967295)
USER_VERSION = 23400001; // must be a 32-bit Signed Integer (LongInt -2147483648 .. 2147483647)
{ TMainForm }
procedure TMainForm.BtnCreateClick(Sender: TObject);
begin
CreateDB(DB_NAME);
end;
procedure TMainForm.CreateDB(AFileName: String);
var
newFile : Boolean;
begin
SQLite3Connection.Close; // Ensure the connection is closed when we start
if FileExists(AFileName) then
DeleteFile(AFileName);
// Create the database and the tables
try
SQLite3Connection.DatabaseName := AFileName;
SQLite3Connection.Open;
SQLTransaction.Active := true;
// Per the SQLite Documentation (edited for clarity):
// The pragma user_version is used to set or get the value of the user-version.
// The user-version is a big-endian 32-bit signed integer stored in the database header at offset 60.
// The user-version is not used internally by SQLite. It may be used by applications for any purpose.
// http://www.sqlite.org/pragma.html#pragma_schema_version
SQLite3Connection.ExecuteDirect('PRAGMA user_version = ' + IntToStr(user_version) + ';');
// Per the SQLite Documentation:
// The application_id PRAGMA is used to query or set the 32-bit unsigned big-endian
// "Application ID" integer located at offset 68 into the database header.
// Applications that use SQLite as their application file-format should set the
// Application ID integer to a unique integer so that utilities such as file(1) can
// determine the specific file type rather than just reporting "SQLite3 Database".
// A list of assigned application IDs can be seen by consulting the magic.txt file
// in the SQLite source repository.
// http://www.sqlite.org/pragma.html#pragma_application_id
SQLite3Connection.ExecuteDirect('PRAGMA application_id = ' + IntToStr(application_id) + ';');
CreateContacts;
CreateEvents;
CreateTasks;
CreateResources;
CreateRecordIDs;
SQLTransaction.Commit;
SQLite3Connection.Close;
InfoLabel.Caption := Format('Database "%s" created successfully.', [AFileName]);
except
Infolabel.Caption := Format('Unable to create database "%s".', [AFileName]);
end;
end;
procedure TMainForm.FormCreate(Sender: TObject);
begin
InfoLabel.Caption := '';
InfoLabel.Show;
end;
procedure TMainForm.CreateRecordIDs;
begin
SQLite3Connection.ExecuteDirect(
'CREATE TABLE "RecordIDs" (' +
'"ResourceID" INT NOT NULL, ' +
'"EventID" INT NOT NULL, ' +
'"TaskID" INT NOT NULL, ' +
'"ContactID" INT NOT NULL );'
);
SQLite3Connection.ExecuteDirect(
'CREATE INDEX "RecordIDs_ResourceID_idx" ON "RecordIDs" ("ResourceID");'
);
SQLite3Connection.ExecuteDirect(
'CREATE INDEX "RecordIDs_EventID_idx" ON "RecordIDs" ("EventID");'
);
SQLite3Connection.ExecuteDirect(
'CREATE INDEX "RecordIDs_TaskID_idx" ON "RecordIDs" ("TaskID");'
);
SQLite3Connection.ExecuteDirect(
'CREATE INDEX "RecordIDs_ContactID_idx" ON "RecordIDs" ("ContactID");'
);
end;
procedure TMainForm.CreateEvents;
begin
SQLite3Connection.ExecuteDirect(
'CREATE TABLE Events (' +
'"RecordID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' +
'"ResourceID" INT NOT NULL, ' +
'"StartTime" DATETIME NOT NULL, ' +
'"EndTime" DATETIME NOT NULL, ' +
'"Description" VARCHAR (255), ' +
'"Notes" VARCHAR (1024), ' +
'"Category" INT, ' +
'"AllDayEvent" BOOLEAN, ' +
'"DingPath" VARCHAR (255), ' +
'"AlarmSet" BOOLEAN, ' +
'"AlarmAdv" INT, ' +
'"AlarmAdvType" INT, ' +
'"SnoozeTime" DATETIME, ' +
'"RepeatCode" INT, ' +
'"RepeatRangeEnd" DATETIME, ' +
'"CustomInterval" INT, ' +
'"UserField0" VARCHAR (100), ' +
'"UserField1" VARCHAR (100), ' +
'"UserField2" VARCHAR (100), ' +
'"UserField3" VARCHAR (100), ' +
'"UserField4" VARCHAR (100), ' +
'"UserField5" VARCHAR (100), ' +
'"UserField6" VARCHAR (100), ' +
'"UserField7" VARCHAR (100), ' +
'"UserField8" VARCHAR (100), ' +
'"UserField9" VARCHAR (100) ); '
);
SQLite3Connection.ExecuteDirect(
'CREATE UNIQUE INDEX "Events_RecordID_idx" ON "Events" ("RecordID");'
);
SQLite3Connection.ExecuteDirect(
'CREATE INDEX "Events_ResourceID_idx" ON "Events" ("ResourceID");'
);
end;
procedure TMainForm.CreateResources;
begin
SQLite3Connection.ExecuteDirect(
'CREATE TABLE Resources (' +
'"ResourceID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, '+
'"Description" VARCHAR (255), ' +
'"Notes" VARCHAR (1024), ' +
'"ImageIndex" INT, ' +
'"ResourceActive" BOOLEAN, ' +
'"UserField0" VARCHAR (100), ' +
'"UserField1" VARCHAR (100), ' +
'"UserField2" VARCHAR (100), ' +
'"UserField3" VARCHAR (100), ' +
'"UserField4" VARCHAR (100), ' +
'"UserField5" VARCHAR (100), ' +
'"UserField6" VARCHAR (100), ' +
'"UserField7" VARCHAR (100), ' +
'"UserField8" VARCHAR (100), ' +
'"UserField9" VARCHAR (100) );'
);
SQLite3Connection.ExecuteDirect(
'CREATE UNIQUE INDEX "Resources_ResourceID_idx" ON "Resources" ("ResourceID");'
);
end;
procedure TMainForm.CreateTasks;
begin
SQLite3Connection.ExecuteDirect(
'CREATE TABLE Tasks (' +
'"RecordID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' +
'"ResourceID" INTEGER NOT NULL, ' +
'"Complete" BOOLEAN, ' +
'"Description" VARCHAR(255), ' +
'"Details" VARCHAR(1024), ' +
'"CreatedOn" DATETIME, ' +
'"Priority" INTEGER, ' +
'"Category" INTEGER, ' +
'"CompletedOn" DATETIME, ' +
'"DueDate" DATETIME, ' +
'"UserField0" VARCHAR(100), ' +
'"UserField1" VARCHAR(100), ' +
'"UserField2" VARCHAR(100), ' +
'"UserField3" VARCHAR(100), ' +
'"UserField4" VARCHAR(100), ' +
'"UserField5" VARCHAR(100), ' +
'"UserField6" VARCHAR(100), ' +
'"UserField7" VARCHAR(100), ' +
'"UserField8" VARCHAR(100), ' +
'"UserField9" VARCHAR(100) );'
);
SQLite3Connection.ExecuteDirect(
'CREATE UNIQUE INDEX "Tasks_RecordID_idx" ON "Tasks" ("RecordID");'
);
SQLite3Connection.ExecuteDirect(
'CREATE INDEX "Tasks_ResourceID_idx" ON "Tasks" ("ResourceID");'
);
end;
procedure TMainForm.CreateContacts;
begin
SQLite3Connection.ExecuteDirect(
'CREATE TABLE "Contacts" (' +
'"RecordID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, '+
'"ResourceID" INTEGER NOT NULL, ' +
'"FirstName" VARCHAR(50), '+
'"LastName" VARCHAR(50), '+
'"Birthdate" DATETIME, '+
'"Anniversary" DATETIME, '+
'"Title" VARCHAR(50), '+
'"Company" VARCHAR(50), '+
'"Job_Position" VARCHAR(30), '+
'"Address" VARCHAR(100), '+
'"City" VARCHAR(50), '+
'"State" VARCHAR(25), '+
'"Zip" VARCHAR(10), '+
'"Country" VARCHAR(25), '+
'"Note" VARCHAR(1024), '+
'"Phone1" VARCHAR(25), '+
'"Phone2" VARCHAR(25), '+
'"Phone3" VARCHAR(25), '+
'"Phone4" VARCHAR(25), '+
'"Phone5" VARCHAR(25), '+
'"PhoneType1" INT, '+
'"PhoneType2" INT, '+
'"PhoneType3" INT, '+
'"PhoneType4" INT, '+
'"PhoneType5" INT, '+
'"Category" INT, '+
'"EMail" VARCHAR (100), '+
'"Custom1" VARCHAR (100), '+
'"Custom2" VARCHAR (100), '+
'"Custom3" VARCHAR (100), '+
'"Custom4" VARCHAR (100), '+
'"UserField0" VARCHAR (100), '+
'"UserField1" VARCHAR (100), '+
'"UserField2" VARCHAR (100), '+
'"UserField3" VARCHAR (100), '+
'"UserField4" VARCHAR (100), '+
'"UserField5" VARCHAR (100), '+
'"UserField6" VARCHAR (100), '+
'"UserField7" VARCHAR (100), '+
'"UserField8" VARCHAR (100), '+
'"UserField9" VARCHAR (100) );'
);
SQLite3Connection.ExecuteDirect(
'CREATE UNIQUE INDEX "Contacts_RecordID_idx" ON "Contacts" ("RecordID");'
);
SQLite3Connection.ExecuteDirect(
'CREATE INDEX "Contacts_ResourceID_idx" ON "Contacts" ("ResourceID");'
);
end;
end.
Recent
