TGTable

Purpose

This component is only used to store table metadata : description, fields, index, constraints.

TGTable is not used to store or retrieve data.

To create a new table

This is for a new functionnality in the application (only in the trunk version).

In order to have sources easy to read, and for easier compatibility with different DBMS, avoid spaces in table names and fields. This way, quotes are not required.

• Create the table and all it's fields, index and constraints with MySql Query Browser, MySql Workbench, or any other MySql client (not PostgreSQL).
  • The following types can be used only. If other types are necessary, some development must be done.
    • Integer Autoinc
    • Integer, smallint
    • Varchar
    • Timestamp
    • Date, Datetime
    • LongText
  • Always start with an Id field, Autoinc and Primary key. In Gestinux datamodel all relations are made between such Id fields (there are exceptions).
  • Always end with 2 fields :
    • UpdateDate DATETIME NULL
    • UpdateUser VARCHAR(40) NULL
• Drop a TGTable object in Datamodule, with connexion set to open the previous database.
  • Create a new computed field AutoInc
  • Because of a bug, correct it's wrong name to <TableName>Id
  • Set it Required and set the FieldKind to fkData.
  • Add all other fields as usual.
  • Copy and paste the index and constraint instructions in the property Properties, and remove quotes and commas at the end of each line
  • In unitcheckdatabase.pas, add Lazarus instruction : TGTable.Check for the new table, to trigger automatic creation or reorganization.
• To test, select another database not containing the new table, or delete the new table in the database where it was just created.
  • Run the reorganization : the table should be re-created with the specified properties
  • Check the structure of the table in the query browser
• After a table is created, the minimal database version number must be incremented in the datamodule. This advice a reorganization the first time the database is opened with the new program.

To add fields, index, constraints in an existing table

• Add the fields, the new index, the new constraints with MySql Query Browser
• Add fields in the TGTable object, add the index and constraint definitions in the TGTable.properties.
• If necessary, make a query to be excuted after the reorganization to populate the new field with values from other tables.
• Run the reorganization : the fields, index or constraints should be added.

Deleting fields, index or constraints

This is not and will not be automated.

The rule is

• version N must accept a database created in version N-1 with automatic reorganization.
• version N-1 must work on a database created by version N with automatic reorganization.
• version N+2 will not work on a database created by version N, and version N will not work on a database created by version N+2.

When the difference is greater than 1, installing intermediate version is required. They are available for [download]. This is checked when a database is connected for the first time.

If an index is no more useful, the AfterConnect event of the datamodule can call TGTable.DeleteIndex. No error while be raised if the index doesn't exists.

If a constraint is no more useful, you can execute after the reorganization a SQL Script to delete it. A method similar to DeleteIndex should be written.

If a field is no more useful, it should be kept and updated until the n+2 stable version is out, to allow backward compatibility. Never re-use an previously used name.

To read or insert data

Do not use TGTable. To read data, use only TGQuery. To insert, update or delete, use methods of TGDbConnection