mORMot Open Source

coblongpamor

Member

From: Bali-Indonesia

Registered: 2010-11-07

Posts: 130

Website

How to Populate Database Object

i want to pupulate Database Object especially TSQLRecord and Coresponding Fields and Dependencies,
and then fill it to specific table like follow:

  TSQLTableList=class(TSQLRecord)
  protected
  public
   AppVersion:TAppVersion;//default avUltimate
   ClassName:RawUTF8;
   ClassParent:RawUTF8;
   SQLTableName:RawUTF8;
   CSVFieldVersion:RawUTF8;
  end;

  TSQLFieldList=class(TSQLRecord)
  protected
  public
   Owner:TSQLTableList;
   FieldName:RawUTF8;
   FieldType:RawUTF8;//translated from TSQLFieldType
   Bound:TRect;//Set bound to Editor
   Defendencies:RawUTF8;//translated from Object e.g TSQLRecord or Enumeration
   ///.
   //.
  end;

if i have Table definition like this:

TSQLItem= class(TSQLBaseMaster)
  private
  protected
  public
     fItemIsSold:Boolean;
     fItemIsBought:Boolean;
     fItemIsInventoried:Boolean;

     fIncomeAccount:TSQLAccount;
     fExpenseAccount:TSQLAccount;
     fInventoryAccount:TSQLAccount;

     fSellingUnit:TSQLItemUnit;
     fSellUnitMeasure:Double;
     fBuyingUnit:TSQLItemUnit;
     fBuyUnitMeasure:Double;

     fCurrency:TSQLCurrency;
     fQuantityOnHand:Double;
     fAverageCost:Currency;
     fValueOnHand:Currency;

     fItemIsTaxedWhenSold:Boolean;
     fSellingTaxCode:TSQLTaxCode;
     fBaseSellingPrice:Currency;
     fPriceIsTaxInclusive:Boolean;
     fTaxInclusiveSellingPrice:Currency;
     fTaxExclusiveSellingPrice:Currency;

     fItemIsTaxedWhenBought:Boolean;
     fBuyingTaxCode:TSQLTaxCode;
     fTaxInclusiveBuyingPrice:Currency;
     fTaxExclusiveBuyingPrice:Currency;

     fItemCategory: TSQLItemCategory;
     fItemGroup: TSQLItemGroup;
  published
    .
  end;

the TSQLTableList will contained as such:

AppVersion        ClassName    ClassParent        SQLTableName    CSVFieldVersion
avUltimate        TSQLItem    TSQLMasterBase    Item            ID,Number,Name,ItemIsSold,ItemIsBought,ItemIsInventoried,IncomeAccount,ExpenseAccount,InventoryAccount,SellingUnit,SellUnitMeasure,BuyingUnit,BuyUnitMeasure,Currency,QuantityOnHand,AverageCost,ValueOnHand,ItemIsTaxedWhenSold,SellingTaxCode,BaseSellingPrice,PriceIsTaxInclusive,TaxInclusiveSellingPrice,TaxExclusiveSellingPrice,ItemIsTaxedWhenBought,BuyingTaxCode,TaxInclusiveBuyingPrice,TaxExclusiveBuyingPrice,ItemCategory,ItemGroup
avProfessional    TSQLItem    TSQLMasterBase    Item            ID,Number,Name,ItemIsSold,ItemIsBought,ItemIsInventoried,IncomeAccount,ExpenseAccount,InventoryAccount,SellingUnit,SellUnitMeasure,BuyingUnit,BuyUnitMeasure,Currency,QuantityOnHand,AverageCost,ValueOnHand,ItemIsTaxedWhenSold,SellingTaxCode,BaseSellingPrice,PriceIsTaxInclusive,TaxInclusiveSellingPrice,TaxExclusiveSellingPrice,ItemIsTaxedWhenBought,BuyingTaxCode,TaxInclusiveBuyingPrice,TaxExclusiveBuyingPrice,ItemCategory,ItemGroup
avStandard        TSQLItem    TSQLMasterBase    Item            ID,Number,Name,SellingUnit,SellUnitMeasure,BuyingUnit,BuyUnitMeasure,QuantityOnHand,AverageCost,ValueOnHand,BaseSellingPrice,ItemCategory,ItemGroup
avSimple        TSQLItem    TSQLMasterBase    Item            ID,Number,Name,SellingUnit,SellUnitMeasure,BuyingUnit,BuyUnitMeasure,QuantityOnHand,AverageCost,ValueOnHand,BaseSellingPrice,ItemCategory,ItemGroup

avUltimate row is the row that automatically populated,
and three next row is filled manualy..

how to do that...?

thanks...

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: How to Populate Database Object

I don't get exactly your point.
Why are you defining so many TSQLRecord?
TSQLRecord are to be defined for handling published properties value persistence in the DB, and expose some business objects.
I don't understand your point.

If you want to play with RTTI, you may take a look at the TSQLRecord.RecordProps property: it will contain all TSQLRecord field and table name information, and much more.

Perhaps you want to have a "variable" field layout.
In the current state of the ORM, it's not possible, sorry.
But what you can do is making a hierarchy of TSQLRecord, thinking OOP and polymorphism, and you'll find out your way into implementing this.

coblongpamor

Member

From: Bali-Indonesia

Registered: 2010-11-07

Posts: 130

Website

Re: How to Populate Database Object

i'm sorry for not clear of my question.

i hope with code sample can make it more clear.

in dataset class perspective, i can do like this:

(...)
Database:TDatabase;
Table:TTable;
TableName:string;

FieldType:TFieldType;
FieldName:string;
i,j:integer;
begin
  for i:=0 to Database.TableCount-1 do
  begin
    Table:=Database.Tables[i];
     TableName:=Table.TableName;
     for j:=0 to Table.FieldCount-1 do
     begin
        FieldName:=Table.Fields[i].FieldName;
        FieldType:=Table.Fields[i].FieldType;
        (....)
     end;
  end;
end;

the point is i want to populate all Table(TSQLRecord) and Published Field Properties that registered in Database;
maybe something like this:

(...)
Database:TSQLDatabase;
Table:TSQLRecord;
TableName:RawUTF8;

FieldType:TSQLFieldType;
FieldName:RawUTF8;
i,j:integer;
begin
  for i:=0 to Database.TableCount-1 do
  begin
    Table:=Database.Tables[i];
     TableName:=Table.SQLTableName;
     for j:=0 to Table.FieldCount-1 do
     begin
        
        (....)
     end;
  end;
end;

how is exactly do that with our framework.?
please give me simple code how to use TSQLRecord.RecordProps with this perspective, so i can learn RTTI step by step.

thank you..

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: How to Populate Database Object

You'll have to start from the TSQLModel instance, not the database.
See the MVC paragraphs in the documentation about what we call a "Model".

In order to follow the MVC pattern, the TSQLModel instance is to be used when you have to deal at table level.
For instance, don't try to use low-level TSQLDataBase.GetTableNames or TSQLDataBase.GetFieldNames methods in your code.
In fact, the tables declared in the Model may not be available in the SQLite3 database schema, but may have been defined as TSQLRestServerStaticInMemory instance via the TSQLRestServer.StaticDataCreate method.

So, in order to access all tables properties, you may instead use code like this:

var i: integer;
     Props: TSQLRecordProperties;
begin
  for i := 0 to high(Model.TableProps) do begin
    Props := Model.TableProps[i];
    // now you can access Props.SQLTableName or Props.Fields[] ...
  end;
end;

In fact, the Model.TableProps[] array maps Model.Tables[].RecordProps, and allow fast direct access to all the needed ORM properties of every TSQLRecord handled by this model, as retrieved from RTTI.

See TSQLRecordProperties fields and methods to see all the available information.

coblongpamor

Member

From: Bali-Indonesia

Registered: 2010-11-07

Posts: 130

Website

Re: How to Populate Database Object

i think i more understand now, and i'll learn the documentation.
thanks..