Check-in [585524dc59]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Added description of some ideas to implement the data layer. Probably far too complex for now.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 585524dc5977416aa57533de96257399e2e4eb39
User & Date: MCO 2014-07-04 13:03:29
Original Comment: Added description opf some ideas to implement the data layer. Probably far too complex for now.
Context
2014-07-07
18:32
Added form for project properties. check-in: 846e5a063a user: MCO tags: trunk
2014-07-04
13:03
Added description of some ideas to implement the data layer. Probably far too complex for now. check-in: 585524dc59 user: MCO tags: trunk
12:07
Added filter panel for activities, and Execute for actProjectAdd. check-in: 2280e19a4f user: MCO tags: trunk
Changes

Added Olam/src/SQL/projects.pas.



























































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
type
  (*
    TAutoDB is a class that can generate an SQLite table definition, and all the 
    relevant SQL statements to read, write and delete it. It uses RTTI to determine
    its own properties.
    *)
  TAutoDB = class
  private
    function ConvertDelphiToSQLiteCase(const Name: string): string;
  protected
    // Use RTTI to generate everything from the current class
    function GenerateTableName: string; virtual;
    function GenerateFields: TStringList; virtual;
    function GenerateSQLSelect: string; virtual;
    function GenerateSQLInsert: string; virtual;
    function GenerateSQLUpdate: string; virtual;
  public
    procedure Load(const Connection);
    procedure Save(const Connection);
  published
    property Table: string  read GenerateTableName;
    property ID: Int64      read FID                write SetID;
  end;
  
  TAutoDBList<T: TAutoDB> = class
  public
    constructor Create(Criteria = '');
    property Count: Int64;
    property Item[const Index: Integer]: T  read GetItem;
  end;
  
  TAutoDBLink<TParent,TChild> = class(TAutoDB)
  public
    property Parent: TParent;
    property Child: TChild;
  end;
  
  TContact = class(TAutoDB)
  published
    property DisplayName: string;
    property Code: string;
    property Title: string;
    property Initials: string;
    property FirstName: string;
    property LastNamePrefix: string;
    property LastName: string;
    property PhoneWork: string;
    property PhoneMobile: string;
    property EmailWork: string;
    property EmailHome: string;
    property Photo: TStream;
    property PhotoContentType: string;
    property Remarks: string;
    property DateCreated: TDateTime;
    property DateModified: TDateTime;
  end;
  
  TProjectContactList = class;
  
  TProject = class(TAutoDB)
  published
    property Name: string;
    property Lead: TContact;
    property Description: string;
    property Contacts: TProjectContactList;
    property DateCreated: TDateTime;
    property DateModified: TDateTime;
  end;
  
  TProjectContact = class(TAutoDBLink<TProject,TContact>)
  published
    property Ordinal: Int64;
    property Role: string;
    property Remarks: string;
  end;
  
  TProjectContactList = class(TAutoDBList<TProjectContact>);

Added Olam/src/SQL/projects.sql.





















































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
CREATE TABLE Contact
( id                    INTEGER PRIMARY KEY
, display_name          TEXT    NOT NULL    COLLATE NOCASE
, code                  TEXT                COLLATE NOCASE
, title                 TEXT                COLLATE NOCASE
, initials              TEXT                COLLATE NOCASE
, first_name            TEXT                COLLATE NOCASE
, last_name_prefix      TEXT                COLLATE NOCASE
, last_name             TEXT                COLLATE NOCASE
, phone_work            TEXT                COLLATE NOCASE
, phone_mobile          TEXT                COLLATE NOCASE
, email_work            TEXT                COLLATE NOCASE
, email_home            TEXT                COLLATE NOCASE
, photo                 BLOB
, photo_content_type    TEXT                COLLATE NOCASE
, remarks               TEXT                COLLATE NOCASE
, date_created          DATE    NOT NULL    DEFAULT CURRENT_TIMESTAMP
, date_modified         DATE
);


CREATE TABLE Project
( id                    INTEGER PRIMARY KEY
, name                  TEXT    NOT NULL    COLLATE NOCASE
, lead                  INTEGER             REFERENCES Contact
, description           TEXT                COLLATE NOCASE
, date_created          DATE    NOT NULL    DEFAULT CURRENT_TIMESTAMP
, date_modified         DATE
);

CREATE TABLE ProjectContact
( id                    INTEGER PRIMARY KEY
, project_id            INTEGER NOT NULL    REFERENCES Project
, contact_id            INTEGER NOT NULL    REFERENCES Contact
, ordinal               INTEGER
, role                  TEXT                COLLATE NOCASE
, remarks               TEXT                COLLATE NOCASE
);

CREATE TABLE Phase
( id                    INTEGER PRIMARY KEY
, code                  TEXT    NOT NULL    UNIQUE  COLLATE NOCASE
, name                  TEXT    NOT NULL    COLLATE NOCASE
, description           TEXT                COLLATE NOCASE
, budget                FLOAT
, date_min              DATE
, date_max              DATE
, tariff                FLOAT
, remarks               TEXT                COLLATE NOCASE
);

CREATE TABLE ProjectPhase
( id                    INTEGER PRIMARY KEY
, project_id            INTEGER NOT NULL    REFERENCES Project
, phase_id              INTEGER NOT NULL    REFERENCES Phase
, UNIQUE(project_id, phase_id)
);