dsTable

This is a short document describing dsTable. By reading this document you should get a better understanding of what dsTable is and what it can do.

You can test this demo on Access, Firebird, MySQL, Pervasive SQLite or VistaDB database. Databases are already there for Access and Firebird. For MySQL, you will need to create a database with name TeamSupport. For Pervasive you need to create folder with name DBBTRV in the application folder. For SQLite you will need sqlite3.dll on the same folder as exe is. After that just set the right parameters in TeamSupport.ini and you are ready to go.

Start the demo application. You should see one MDI form already on the screen. That is events form. Let’s say event is everything that can occur in a company. A phone call, idea, something to do, etc. We will only use here few of them as a demo.

To fill some demo data, select Actions|Insert test data and press refresh on the events form.

Let’s start by showing some simple things, yet powerful. Select columns button on the events form to setup columns for events table.

  Notice how you can expand things in front of Client and Person.

  That means these two fields are connected to some other table. If you expand things by pressing plus in front of Client, you will see all the fields in the Clients table.

  You can select any of them to be seen in the grid. That kind of fields are called lookup fields. Further on, notice that even at that point, you can expand things in front of Zip. That means you can show as many lookups as you want, as deep as you want. Be aware though that things are getting slower and slower by adding lookups from many tables and lookups from tables that are deeper than one level.

Ok, select Name of the Client and Name of the Zip as shown on the picture and press ok.

You should see lookup fields in the grid now. They have a small blue dot in the column header showing that those are lookup columns.

All this is possible because dsTables are all managed from one place, that is from Delphi. They are aware of each other. And they know how to handle calls like this:

Table.FieldByName(‘Client.Zip’).AsString. We will see that later on.

Select now Data|Customers. Set windows like those on the picture above to see events form in the background and select edit button (F9 shortcut). Edit the record, change the name of the client and press post button (F10 shortcut). Notice how things are refreshed even in events form.

With relationships, the story is similar. You define some properties on the fields that can have lookups behind and if you change the ClientID, dsTable generates tasks that should be done to change old ClientID in all the tables that includes that ClientID. Or records can be deleted or left as they are. It all depends on the property you set.

As already said, all things are managed from one place, that is Delphi IDE. There can be a component editor to make comments on the table and fields. There can even be a component editor for relationships like the one on the picture. It is just a sample window to show you all the possibilities.

Further on, as things are managed from one place, even changing tables is a matter of seconds. Adding new field for example. You don’t need to change table struct in some other application, generate sql script, run that script on the database, go back to Delphi, add TField on TDataset and add editors. You just add field on dsTable in Delphi and add editor on the right place, everything else is done for you by dsTable. That means, as you start your application, dsTable should know how to check struct and if struct is not ok, existing table is upgraded (one way is to rename existing table, create new table and copy all the data to the new table).

dsTable uses dsDatabase to connect to any database desired. The functionality of dsDatabase is slightly different that usual databases in Delphi. You write your own dsDatabase by using standard components for access like TQuery, TTable, TADOQuery, TmySQLQuery, etc. The use of those components is very similar so new dsDatabases should be written quite quickly.

Let’s see now some other things that dsTable supports. First of all, rights. You define rights on dsTable and dsFields. That means you can define whether user can insert, edit or delete records and you can define whether user can see or edit individual fields.

Select Try|Select rights to set some rights. Then you can select Data|Zip and you should not see Name column in the grid or in the columns setup. Further on you should not be able to delete records and if you edit one of the events in the events form by pressing F9, you should see field (editor) Author read only, so you can’t edit it.

Next thing are colors. You use dsColors to define colors and you connect dsTable with dsColors and write OnGetGridDrawData. From that point, every table will know how to color rows in the grid. You can see that in events form. Even drop down grid in edits will show the right color as it asks dsTable for the color.

Open Data|List2 to see how LookupFilter is used dinamicly on Person field. You'll have to select customer first to see persons that belong to some customer. If you look at the sources, you will see that this filter is assigned in datamodule as OnGetLookup event for field Person on table List2. Notice how this is only done once and it will be used on all tables that are of struct List2, that is, on all tables that List2 struct is assigned.

Additionaly you will notice how Persons don't have form to edit them. That makes Persons as a non editable table, but you can still select from that table if you press lookup button on some field. Persons will be shown in default form. This is the code:

GlobalLookupForm := TGridForm;

GlobalLookupForm is variable in dsTable unit. It is used for all tables that don't have LookupForm assigned. For some other tables, lookup forms are assigned like this:

DataModule1.Customers.RegisterLookupForm(TCustomersForm);
DataModule1.Zip.RegisterLookupForm(TZipForm);

Controls that are used here are derived from standard DB controls. They have some more code behind to be able to work with dsTable. Nothing much.

if Datasource.Dataset.Owner is TdsTable then...

Some nice things about editors. Edit knows how to locate lookup fields and how to show lookup form or drop down grid. When validation is called it knows what to validate, uses colors and hint to show what is wrong (try to insert empty record into Zip table to see validation). DBEdit gets it's values from dsField as for Edits, you can call SetEditFromField. That brings many good things with it as you only need for example to define allowed values once, in dsField with script for example, after that all edits will use that.
Notice how things get filtered with this grid. Just put cursor into right column and start to type. If field is of type string, every character will narrow your choice. If field is integer for example, you will get a quick filter dialog to enter values from and to. Or if you are on the field that has lookup behind, press Ctrl+Space to get quick filter form and notice how this edit already knows some things about lookup. That is all possible because you ask dsTable some things and then you know how to setup things to work perfect.

Now let’s get back to Table.FieldByName(‘Client.Zip’).AsString. In the events form select special (that little hammer) and design report. Select File|Open and open Report01.rpt. Select Client.Zip.Name field. That is TdsrDBText and this is the way, how to get any field to report that is somehow connected with this table trough lookups. No need for programmer to write SQL joins. Perhaps you should close designer now and select print in events form. Find that report again and press ok too see how things are printed.

dsTable is ment for small to middle size applications where speed of development and changing applications is essential even if speed of applications and size is sometimes put aside. Size is not a problem these days any more and with faster and faster machines, speed of applications is not so important as before. At the moment I'm working and testing next dsDatabases. Access via TADOQuery, Pervasive vis TBTRVTable, VistaDB via TVDBTable, Firebird via TIBOQuery in MySQL via TmySQLQuery.

There are even more things that can be done but this is just a small demo to present things. I’m in fact looking for interested people or companies to develop things further on.