Copyright: 0.91 (preview release)


FieldInfo component

 TFieldInfo component

version

0.91   preview release

purpose

A universal query information container designed to be used by custom query builders as a source for information on the relevant database, tables and fields on which queries can be performed.

The principle of offering user-friendly information is built in, in that every table and field defined has a UserName aswell as a SQLName.

Low level access is provided via the sub-properties and methods of the Tables, Joins, Fields.

Set Strict to prevent duplicates from being added. With a FieldItem, no FieldUsage attributes can be set if any of the key properties SQLName, FieldType or Table are unassigned.

See the structure chart for the big picture.

NB:  This has been compiled as a 32 bit component.

properties

DatabaseName, Tables, Fields, Joins, Strict

methods

ChangeNotify, FieldFreeNotify, TableFreeNotify,

events

OnChange, OnTablesChange, OnFieldsChange, OnJoinsChange

files

qiField.dcu	TFieldInfo

qiFldsEd.dcu/dfm	Fields editor

qiTblsRd.dcu/dfm	Tables editor

qiJnsEd.dcu/dfm	Joins editor

qiAddEd.dcu/dfm	Fields/Tables sub-editor

qiAddJn.dcu/dfm	Joins sub-editor

qiField.hlp	FieldInfo help reference (this file)


Included utility routines

The following routines included in qiInfo.dcu can come in use.

StripExt

Strips the extension from a file name.

function StripExt(const S: string): string;

QualifyStr

Adds a table alias to a field name. The alias is derived from the TableChar constant ‘T’, plus the list index of the table plus one. Thus a field named ‘NAME’ of a table referenced in first place in the tables list will be returned as ‘T1.NAME’.

function QualifyStr(Qualify: Boolean; Table: TTableItem): string;

AsUserName

Returns an attempt at creating a user friendly name for a table or field. For example a field name ‘LAST_NAME’ or ‘LastName’ will be returned as ‘Last Name’.

function AsUserName(const S: string): string;

SQLOperatorStr

Returns the SQL string representation of the operator.

function SQLOperatorStr(Operator: TQueryOperator): string;

UserOperatorStr

Returns the user-orientated string representation of the operator.

function UserOperatorStr(Operator: TQueryOperator): string;

FieldTypeStr

Returns the string representation of the field type enumerated constant, eg if FieldType is ftFloatField you get “ftFloatField”.

function FieldTypeStr(FieldType: TFieldType): string;

JoinCompatable

Indicates whether the two field types are legally (not logically) join compatable.

function JoinCompatable(Type1, Type2: TFieldType): Boolean;


FieldInfo structure chart

FieldInfo

      |

      |- - - DatabaseName

      |

      |- - - Tables [n] = TableItem

      |	 |- - - SQLName

      |	 |- - - UserName

      |	 |- - - TableRef

      |	 |- - - Master

      |

      |- - -Fields [n] = FieldItem

      |	|- - -SQLName

      |	|- - -UserName

      |	|- - -Table

      |	|- - -FieldType

      |	|- - -FieldUsage

      |	|- - -FieldRef

      |	|- - -CalcFormula

      |

      |- - -Joins [n] = JoinItem

         |- - -Table1

         |- - -Table2

         |- - -Field1

         |- - -Field2

         |- - -Operator


FieldItem object

Holds information on a database table field.

type

TFieldItem

properties

SQLName, UserName, Table, FieldType, FieldUsage, FieldRef, CalcFormula, Tag, EditFlag, Obj

methods

SQLNameStr

owner

TFieldList


TableItem object

Holds information on a database table.

type

TTableItem

properties

SQLName, UserName, TableRef, Master, Tag, EditFlag, Obj

owner

TTableList


JoinItem object

Holds information on a database table join.

type

TJoinItem

properties

Table1, Table2, Field1, Field2, Operator, Tag

methods

SQLJoinStr

owner

TTableList


EditFlag property

For use in the editing of the relevent items. efNew flags a newly added item, efOld an existing item, efDelete an existing item that should be deleted if the edit is finalized.

type

TEditFlag = (efNew, efOld, efDelete)

stage

depends on the above types

access

read 	write

owner

TTableItem, TJoinItem, TFieldItem


Tag property

Can be used in much the same way as a component tag.

type

Longint

stage

depends on the owner types

access

read 	write

owner

TTableItem, TJoinItem, TFieldItem


Obj property

Acts as a general purpose holder. Useful for storing extended information. Any component using this property should first check that it is not in use by another, and if so raise an exception to that effect to warn the user of a clash, rather than overwriting it.

type

TObject

stage

run time only

access

read 	write

owner

TTableItem, TJoinItem, TFieldItem


DatabaseName property

The path or alias of the database to be used (see TTable, TQuery, etc), eg "DBDEMOS"

type

string

stage

design time 	run time

access

read 	write


Tables property

The persistent container of table items. Use the Tables Editor at design time via the Tables property or the “Tables…” context menu item.

type

TTableList

stage

design time 	run time

access

read 	write

properties

Items (default), Count

methods

Add, AddNew, AddFrom, AddFromTable, Insert, Delete, Remove, Clear, Move, Exchange, IndexOf, BySQLName, ByUserName, ByTableRef, First, Last


Fields property

The persistent container of field items. Use the Fields Editor at design time via the Fields property or “Fields…” context menu item.

type

TFieldList

stage

design time 	run time

access

read 	write

properties

Items (default), Count

methods

Add, AddNew, AddFrom, AddFromTable, Insert, Delete, Remove, Clear, Move, Exchange, IndexOf, BySQLName, BySQLNameAndTable, ByUserName, ByFieldRef, First, Last


Joins property

The persistent container of table join items. Use the Joins Editor at design time via the Joins property or the “Joins…” context menu item.

type

TJoinList

stage

design time 	run time

access

read 	write

properties

Items (default), Count

methods

Add, AddNew, AddFrom, Insert, Delete, Remove, Clear, Move, Exchange, IndexOf, First, Last, JoinOf


SQLName property

The actual name of the table or field. Used in SQL query scripts.

type

string

stage

design time	run time

access

read 	write

owner

TTableItem, TFieldItem


UserName property

The user friendly version name of the table or field. Used as the user interface name.

type

string

stage

design time	run time

access

read 	write

owner

TTableItem, TFieldItem


TableRef property

Optionally references a dataset component (TTable, TQuery).

type

TDataSet

stage

design time	run time

access

read 	write

owner

TTableItem


FieldRef property

Optionally references a TField component. If assigned, any user of the field itemshould make use of the TField’s properties where appropriate, for example, when showing the results of a user query, properties of the referenced TField should be assigned to the properties of the dynamically created query TField (DisplayWidth, DisplayFormat, etc).

type

TField

stage

design time	run time

access

read 	write

owner

TFieldItem


Master property

If set, signals that the table item should always be included in the SQL query script, even if no fields belonging to it are used in any way. This can help where other tables do not have appropriate fields on which joins can be defined. Usaully there is a table in a database that contains foreign keys to most other tables. This table would make an ideal master, avoiding the situation of including tables with no join definitions which leads to an exponental result set. In other words, any joins defined with the master table can be used as a sort of bridging join between two other tables with no coommon join definition - each will have a join to the master table.

type

Boolean

stage

design time	run time

access

read 	write

owner

TTableItem


Operator property

The type of SQL operator used. Covers ‘=’, ‘<>’, ‘>’, ‘<’, ’>=’, ’<=’, ‘BETWEEN’, ‘IN’, ‘LIKE’, ‘= TRUE’, ‘IS NULL’. This property is only used in join defintions in TFieldInfo, where only the first six are legal (almost always ‘=’).

type

TQueryOperator = (qoEquals, qoNotEquals, qoSmaller, qoLarger, qoSmallerEquals, qoLargerEquals, qoBetween, qoIn, qoLike, qoTrue, qoNull);

stage

design time	run time

access

read 	write

owner

TJoinItem


Field1 and Field2 properties

Refer to the join fields in a table join definition.

type

TFieldItem

stage

design time	run time

access

read 	write

owner

TJoinItem


Table1 and Table2 properties

Refer to the join tables in a table join definition.

type

TTableItem

stage

design time	run time

access

read only  (refers to the join field’s Table property)

owner

TJoinItem


Table property

References a table item.

type

TTableItem

stage

design time	run time

access

read	write

owner

TFieldItem, TJoinItem


Field property

References a field item.

type

TFieldItem

stage

design time (join item only)	run time

access

read 	write

owner

TJoinItem


FieldType property

The type of field represented by a field item. Corresponds to TField-derivative types. Useful in determining what query operators can be used with the field and if values should be wrapped in quotations.

type

TFieldType (defined in DB - see Delphi VCL help)

stage

design time	run time

access

read 	write

owner

TFieldItem


FieldUsage property

Indicates where the field can be used -

fuSelect	selectable

fuCalc	can be used in an aggregation, ie SUM, COUNT, etc

fuGroup   	can be grouped on

fuFilter	can be filtered on

fuOrder	can be ordered on

fuLookup	its values can be looked up

Useful for restricting what the user can do, as for example if a field item’s fuFilter attribute is not set, that field should not be included in the list of fields that the user can choose for defining a filter on.

Note that all these flags will be cleared and not settable while any of the key properties SQLName, FieldType and Table are unassigned, or if FieldType is of an unsupported type - those larger than ftDateTime in the TFieldTypes definition except ftAutoInc.

type

TFieldUses = set of TFieldUseage = [fuSelect, fuCalc, fuGroup, fuFilter, fuOrder, fuLookup]

stage

design time	run time

access

read 	write

owner

TFieldItem


CalcFormula property

Defines how a calculated field is derived. This should substitute the SQLName in a SQL SELECT clause, though the SQLName should be added so that it can be sorted on, for example ‘STK_SOLD * SELL_PR AS VAL_SOLD’ where ‘VAL_SOLD’ is the SQLNAME.

Should also replace SQLName in a values lookup.

NOTE:  If the fields are to be qualified with their table names, ensure that these are included manually in CalcFormula as it is awkward for a routine to add them, especially as the fields used may not be from the same table!

type

string

stage

design time	run time

access

read 	write

owner

TFieldItem


Strict property

Currently causes the list propeties’ add and insert methods to reject duplicate items.

type

Boolean

stage

design time	run time

access

read	write


Add method

Adds the item to the list, returning its index in the list.

declaration

function Add(<AItem>: <TItem>): Integer;

<TList>  TJoinList/TTableList/TFieldList

<AItem>  AJoin/ATable/AField

<TItem>  TJoinItem/TTableItem/TFieldItem


AddNew method

Returns a newly created item and adds it to the list.

declaration

function <TList>.AddNew: <TItem>;

<TList>  TJoinList/TTableList/TFieldList

<TItem>  TJoinItem/TTableItem/TFieldItem


Insert method

Inserts an item into the list at a particular place.

declaration

procedure <TList>.Insert(Index: Integer; <AItem>: <TItem>);

<TList>  TJoinList/TTableList/TFieldList

<AItem>  AJoin/ATable/AField

<TItem>  TJoinItem/TTableItem/TFieldItem


Delete method

Removes the item at the specified index, moving all subsequent items up one place. If ToFree is True then the item is also destroyed.

declaration

procedure <TList>.Delete(Index: Integer; ToFree: Boolean);

<TList>  TJoinList/TTableList/TFieldList


Remove method

Removes the specified item from the list, returning the position it was in.

If ToFree is True then the item is also destroyed.

declaration

function <TList>.Remove(<AItem>: <TItem>; ToFree: Boolean): Index;

<TList>  TJoinList/TTableList/TFieldList

<AItem>  AJoin/ATable/AField

<TItem>  TJoinItem/TTableItem/TFieldItem


Clear method

Clears the list of all items. If ToFree is True then the items are also destroyed.

declaration

procedure <TList>.Clear(ToFree: Boolean);

<TList>  TJoinList/TTableList/TFieldList


First method

Returns the first item in the list.

declaration

function <TList>.First: <TItem>;

<TList>  TJoinList/TTableList/TFieldList

<TItem>  TJoinItem/TTableItem/TFieldItem


Last method

Returns the last item in the list.

declaration

function <TList>.Last: <TItem>;

<TList>  TJoinList/TTableList/TFieldList

<TItem>  TJoinItem/TTableItem/TFieldItem


Move method

Moves the position of an item within the list.

declaration

procedure <TList>.Move(CurIndex, NewIndex: Integer);

<TList>  TJoinList/TTableList/TFieldList


Exchange method

Exchanges the positions of two items within the list.

declaration

procedure <TList>.Exchange(Index1, Index2: Integer);

<TList>  TJoinList/TTableList/TFieldList


IndexOf method

Returns the position of the item in the list.

declaration

function <Tlist>.IndexOf(<AItem>: <TItem>): Integer;

<TList>  TJoinList/TTableList/TFieldList

<AItem>  AJoin/ATable/AField

<TItem>  TJoinItem/TTableItem/TFieldItem


AddFrom method

Creates a new item, assigns the supplied property values, adds it to the list, and returns it.

declaration

function TJoinList.AddFrom(AField1, Afield2: TFieldItem, AOperator: TQueryOperator): TJoinItem;

function TTableList.AddFrom(ASQLName, AUserName: string; AMaster: Boolean; ATableRef: TDataSet): TTableItem;

function TFieldList.AddFrom(ASQLName, AUserName, ACalcFormula: string; Atable: TTableItem, AFieldType: TFieldType, AFieldUsage: TFieldUsage, AFieldRef: TField; AToShow: Boolean): TFieldItem;


AddFromTable method

Creates a new table item, assigning its SQLName and UserName from the TTable, adding the TTable to its TableRef property and returns the table item.

declaration

function TTableList.IndexOf(ATable: TTable): TTableItem;


AddFromField method

Creates a new field item, assigning its SQLName and UserName and FieldType from the TField, adding the TField to its FieldRef property and returns the field item.

declaration

function TFieldList.IndexOf(AField: TField): TFieldItem;


BySQLName method

Returns the first item with a matching SQLName, else nil.

declaration

function <TList>.BySQLName(AName: string): <TItem>;

<TList>  TTableList/TFieldList

<TItem>  TTableItems/TFieldItem


BySQLNameAndTable method

Returns the first item with a matching SQLName and Table.SQLName, else nil.

declaration

function TFieldList.BySQLName(SQLName, TableName: string): TFieldItem;


ByUserName method

Returns the first item with a matching UserName, else nil.

declaration

function <TList>.ByUserName(AName: string): <TItem>;

<TList>  TTableList/TFieldList

<TItem>  TTableItems/TFieldItem


ByTableRef method

Returns the first item with a matching TableRef, else nil.

declaration

function TTableList.ByTableRef(ATableRef: TDataSet): TTableItem;


ByFieldRef method

Returns the first item with a matching FieldRef, else nil.

declaration

function TFieldList.ByFieldRef(AFieldRef: TField): TFieldItem;


JoinOf method

Returns the first join item including both specified table items, else nil if none.

declaration

function TJoinList.JoinOf(ATable1, ATable2: TTableItem): TJoinItem;


TableFreeNotify method

If you are destroying a table item that has been removed from Tables then call this method with the table item as the parameter so that all references to it in other properties (Joins, Fields) can be deleted.  If you delete it via Tables.Delete, Tables.Remove or Tables.Clear this will be done for you by Tables.

declaration

procedure TFieldInfo.TableFreeNotify(Sender: TObject);


FieldFreeNotify method

If you are destroying a field item that has been removed from Fields then call this method with the field item as the parameter so that all references to it in Joins can be deleted (At run time also call the FieldFreeNotify method of any attached QueryInfo component if they contain items that possibly reference the deleteted field).  If you delete it via Fields.Delete, Fields.Remove or Fields.Clear this will be done for you by Fields.

declaration

procedure TFieldInfo.FieldFreeNotify(Sender: TObject);


SQLJoinStr method

Returns the SQL join clause string of the represented join item.

declaration

function TJoinItem.SQLJoinStr(Qualify: Boolean): string;


SQLNameStr method

Returns the SQL “SELECT” clause string of the represented field item.

declaration

function TFieldItem.SQLSelectStr(Qualify: Boolean): string;


UserNameStr method

Returns the user-orientated select string of the represented field item.

declaration

function TFieldItem.UserNameStr: string;


Qualify parameter

Specified whether any field name used should be qualified by the alias of the table to which it belongs.


Assign method

Assigns the properties of the supplied item.

declaration

procedure <TItem>.Assign(<AItem>: <TItem>);

<TList>   TJoinList/TTableList/TFieldList

<AItem>  AJoin/ATable/AField

<TItem>   TJoinItem/TTableItem/TFieldItem


Fields property editor

Field items list box

Lists the defined field items by their Table.SQLName and SQLName properties. If a name is undefined, lists it as “<Unknown>”. No attempt at preventing undefined or invalid key properties, or duplications is made. Items can be re-ordered via dragging or the arrow buttons.

“Add Manually” button

Adds a new blank field item definition. All values have to be entered manually.

“Include TFields…” button

Brings up a list of defined TField components. Those selected are added to the field items as follows:  SQLName = FieldName;  UserName = DisplayLabel if not the same as FieldName, else is manufactured by the AsUserName utility routine;  FieldType = DataType;  FieldRef = the selected TField component;  if the DataSet is a TTable component then Table = TTable(DataSet) - which is automatically added to the TablesTables list property if not already there. If the fields are already included, they will not appear here.

First define persistent TFields, add them, then if those persistent TFields are not wanted, just delete them again. This way you are basically guaranteed valid field information, plus letting Delphi do the hard work and this editor the mundane work for you!

Field items can be moved via the move buttons (repeat if held down) or by drag-drop (autoscrolls).

“Add via BDE…” button

Uses the BDE to retrieve all fields in all tables of the the database specified in DatabaseName, providing that they are not already included, and assigns the relevant properties much as above.

Delete button

Deletes and destroys a field item.

Clear button

Deletes all field items.

SQLName edit box

Give the formal database name of the represented field here. Note that as you type, a user version is created in the UserName edit box.

UserName edit box

Give the field name that you want your user to see.

FieldType combo box

Select the relevant field type moniker from the drop-down list.

FieldUsage check boxes

Use these to control what the user can do with the defined field. Double-click on the “FieldUsage” label to toggle all enabled check boxes at once.

FieldRef combo box

To associate the field item with a relevant TField component, select it from the drop-down list of defined TFields. Press delete on your keyboard to clear.

Table combo box

Lists all the SQLNames of the tables currently defined in the Tables list property. Select the table that the represented field belongs to. Press delete on your keyboard to clear.

CalcFormula edit box

If you are defining a calculated field, put the calculation formula here to provide the information on how to generate its values - sort of complements/overrides SQLName.


Tables property editor

Tables list box

Lists the defined table items.

“Add Manually…” button

Adds new blank table items.

“Include Ttables…” button

Adds new table items derived from those selected from the list presented. SQLName gets TableName and UserName is assigned via the AsUserName utility routine. TableRef is assigned the TTable component.

“Add via BDE…” button

Uses the BDE to retrieve all tables found in the database specified in DatabaseName that are not already included. Those selected are added much as above.

Delete button

Deletes and destroys a table item.

Clear button

Deletes all table items.

SQLName edit box

Typing in a value here automatically creates a user version in the UserName edit box.

UserName edit box

Give the name that you want your user to see.

TableRef combo box

Can be used to keep a reference to a corresponding dataset component - just select from the drop down list. Press delete on your keyboard to clear.

Master check box

If checked, indicates that it is a master table.


Joins property editor

The main dialog shows defined joins. The “Add…” button executes a dialog for defining a new join. Select the tables from the combo boxes, then select the fields to join and the join operator (invariably “=”). The OK button is only enabled if the selected tables are not the same and the selected fields are join compatable. It is up to you to select fields that are logically joinable - generally it would be a key of one table (eg ItemNo) and have the same name in the other table (a foreign key).


OnChange property
OnChange event

Notifies of a change in any of Fields, Tables or Joins.  Activated by the ChangeNotifyChangeNotify method by a list property editor after editing.  See OnTablesChange, OnFieldsChange, OnJoinsChange for more specific notification.

type

TNotifyEvent

stage

design time	run time

access

read	write


OnFieldsChange property
OnFieldsChange event

Notifies of a change in the Fields.  Activated by the ChangeNotify method by a Fields list property editor after editing.

type

TNotifyEvent

stage

design time	run time

access

read	write


OnTablesChange property
OnTablesChange event

Notifies of a change in the Tables.  Activated by the ChangeNotify method by a Filters list property editor after editing.

type

TNotifyEvent

stage

design time	run time

access

read	write


OnJoinsChange property
OnJoinsChange event

Notifies of a change in the Joins.  Activated by the ChangeNotify method by a Grouping list property editor after editing.

type

TNotifyEvent

stage

design time	run time

access

read	write


ChangeNotify method

Must be called by the editor of any of the Fields, Tables or Joins properties, passing the property edited, after changing them in order ti invoke the relevant notification events - OnTablesChange, OnFieldsChange, OnJoinsChange. OnChange will be invoked, plus the notifier of the property passed as Sender, if any. (OnChange will not be invoked by passing Lookup, only its specific notifier)

Declaration

TQueryInfo.ChangeNotify(Sender: TObject);