There is sqlite a database - a descriptor, containing the list of tables, the list of domains, the list of fields, the list of restrictions (primary and foreign keys), the list of indexes. I am connected from Delphi XE3 with the help of the built-in component to this basis. There is a separate module in which the classes TTableSpec, TFieldSpec, TConstraintSpec, TConstraintDetSpec are described. These classes correspond to records of above-mentioned sqlite of basis. In TTableSpec classes there are such FFields fields: TComponent which becomes the owner of objects like TFieldSpec which have been also unloaded from basis. After creation of objects by reading basis descriptor I found out that values of some object properties (for example, TFieldSpec) are not in that coding (the line Edit2.Text: = TFieldSpec (TConstraintDetailSpec (TConstraintSpec (TTableSpec (DBSchema.Tables.FindComponent(InputTableName)) of.Constraints.Components [i]).DetailList).FieldSpec).FieldName; in below-mentioned function of check that the field primary key of this table is).
function TfmSettings.IsPrimaryKey(InputTableName : string; InputFieldName: string):Boolean;
var
i : integer;
flag: boolean;
begin
flag:=False;
for i:=0 to TTableSpec(DBSchema.Tables.FindComponent(InputTableName)).Constraints.ComponentCount-1 do
begin
if ((TConstraintSpec(TTableSpec(DBSchema.Tables.FindComponent(InputTableName)).Constraints.Components[i]).ConstraintType='PRIMARY') and (TTableSpec(DBSchema.Tables.FindComponent(InputTableName)).Fields.FindComponent(InputFieldName).Name=TConstraintDetailSpec(TConstraintSpec(TTableSpec(DBSchema.Tables.FindComponent(InputTableName)).Constraints.Components[i]).DetailList).FieldName)) then
flag:=True;
Edit1.Text:=TConstraintSpec(TTableSpec(DBSchema.Tables.FindComponent(InputTableName)).Constraints.Components[i]).Name;
Edit2.Text:=TFieldSpec(TConstraintDetailSpec(TConstraintSpec(TTableSpec(DBSchema.Tables.FindComponent(InputTableName)).Constraints.Components[i]).DetailList).FieldSpec).FieldName;
Edit3.Text:=InputFieldName;
end;
Result:=flag;
end;
I give also a code of the procedures creating objects, connected with the wrong coding:
procedure CreationListOfFields(SQLConn: TSQLConnection; DBSchema : TDBSchemaSpec);
var
NameField : TField;
PositionField : TField;
DescriptionField : TField;
CanInputField : TField;
CanEditField : TField;
ShowInGridField : TField;
ShowInDetailsField : TField;
IsMeanField : TField;
AutocalculatedField : TField;
RequiredField : TField;
Name1 : TField;
Name2 : TField;
begin
SQLConn.Execute('select f.id, f.position, f.name, f.description, f.can_input, '
+' f.can_edit, f.show_in_grid, f.show_in_details, f.is_mean, f.autocalculated, f.required, t.name, d.name '
+' from fields f left join tables t on f.table_id=t.id '
+' left join domains d on f.domain_id=d.id order by t.name, d.name ', nil, results);
if not results.IsEmpty then
begin
results.First;
Name1:=results.FieldByName('name_1');
Name2:=results.FieldByName('name_2');
lastTable:=Name1.AsString;
TableSpec:=TTableSpec(DBSchema.Tables.FindComponent(lastTable));
lastDomain:=Name2.AsString;
DomainSpec:=TDomainSpec(DBSchema.Domains.FindComponent(lastDomain));
NameField:=results.FieldByName('name');
PositionField:=results.FieldByName('position');
DescriptionField:=results.FieldByName('description');
CanInputField:=results.FieldByName('can_input');
CanEditField:=results.FieldByName('can_edit');
ShowInGridField:=results.FieldByName('show_in_grid');
ShowInDetailsField:=results.FieldByName('show_in_details');
IsMeanField:=results.FieldByName('is_mean');
AutocalculatedField:=results.FieldByName('autocalculated');
RequiredField:=results.FieldByName('required');
while not results.Eof do
begin
if (Name1.AsString<>lastTable) then
begin
lastTable:=Name1.AsString;
TableSpec:=TTableSpec(DBSchema.Tables.FindComponent(lastTable));
end;
if (Name2.AsString<>lastDomain) then
begin
lastDomain:=Name2.AsString;
DomainSpec:=TDomainSpec(DBSchema.Domains.FindComponent(lastDomain));
end;
FieldSpec:=TFieldSpec.Create(TableSpec.Fields);
FieldSpec.Setup( DomainSpec, PositionField.AsInteger,
NameField.AsString, DescriptionField.AsString,
FieldToBoolean(CanInputField),FieldToBoolean(CanEditField),
FieldToBoolean(ShowInGridField), FieldToBoolean(ShowInDetailsField),
FieldToBoolean(IsMeanField),FieldToBoolean(AutocalculatedField),
FieldToBoolean(RequiredField));
TComponent(FieldSpec).Name:=NameField.AsString;
TableSpec.Fields.InsertComponent(FieldSpec);
results.Next;
end;
end;
end;
procedure CreationListOfConstrAndConstrDet(SQLConn : TSQLConnection; DBSchema : TDBSchemaSpec);
var
IDField : TField;
NameField : TField;
ConstrTypeField : TField;
ReferenceField : TField;
UniqueKeyIdField : TField;
HasValueEditField : TField;
CascadingDeleteField: TField;
ExpressionField : TField;
NameField1 : TField;
Name1 : TField;
begin
SQLConn.Execute('select c.id, c.name, constraint_type, reference, unique_key_id, has_value_edit, '
+ 'cascading_delete, expression, t.name from constraints c left join tables t on c.table_id=t.id order'
+' by t.name ', nil, results);
if not results.IsEmpty then
begin
results.First;
IDField:=results.FieldByName('ID');
NameField:=results.FieldByName('name');
ConstrTypeField:=results.FieldByName('constraint_type');
ReferenceField:=results.FieldByName('reference');
UniqueKeyIdField:=results.FieldByName('unique_key_id');
HasValueEditField:=results.FieldByName('has_value_edit');
CascadingDeleteField:=results.FieldByName('cascading_delete');
ExpressionField:=results.FieldByName('expression');
Name1:=results.FieldByName('name_1');
lastTable:=Name1.AsString;
TableSpec:=TTableSpec(DBSchema.Tables.FindComponent(lastTable));
while not results.Eof do
begin
if (Name1.AsString<>lastTable) then
begin
lastTable:=Name1.AsString;
TableSpec:=TTableSpec(DBSchema.Tables.FindComponent(lastTable));
end;
ConstraintSpec:=TConstraintSpec.Create(TableSpec.Constraints);
ConstraintSpec.Setup(IDField.AsInteger,NameField.AsString, ConstrTypeField.AsString,
ReferenceField.AsString, ConvertToInt(UniqueKeyIdField.AsString), FieldToBoolean(HasValueEditField),
FieldToBoolean(CascadingDeleteField), ExpressionField.AsString);
TComponent(ConstraintSpec).Name:=results.FieldByName('name').AsString;
TableSpec.Constraints.InsertComponent(ConstraintSpec);
SQLConn.Execute('select cd.id, f.name from constraint_details cd left join'
+' fields f on f.id=cd.field_id where cd.constraint_id = '+inttostr(ConstraintSpec.ID), nil, results1);
if not results1.IsEmpty then
begin
results1.First;
NameField1:=results1.FieldByName('name');
while not results1.Eof do
begin
FieldSpec:=TFieldSpec(TableSpec.Fields.FindComponent(NameField1.AsString));
ConstDetSpec:=TConstraintDetailSpec.Create(ConstraintSpec.DetailList);
ConstDetSpec.Setup(NameField1.AsString, FieldSpec);
ConstraintSpec.DetailList.InsertComponent(ConstDetSpec);
results1.Next;
end;
end;
results.Next;
end;
end;
end;
P.S. SQL queries are executed in sqlite normally. Gives out the necessary lines in the necessary coding (at least visually the result contains readable English and Russian characters).
