Zeoslib Portal

Posted: **13.06.2008, 14:04**

Following http://zeos.firmos.at/viewtopic.php?t=1878, I would like to request a couple additions to the TZTable component, similar to what is available in Delphi's TTable:

The first is a read-only Exists property, which would return whether a table with the corresponding name exists in the database. This may also be implemented as a method, perhaps like so:

Code: Select all

function TZTable.Exists: Boolean;
var
  TableList: TStringList;
begin
  TableList := TStringList.Create;
  try
    if (not Assigned(Connection)) then
      raise SomeException.Create('no connection'); // <-- adjust this line
    {TODO: handle closed connection somehow}

    Connection.GetTableNames(TableName, TableList);
    TableList.CaseSensitive := False;
    Result := (TableList.IndexOf(TableName) >= 0); // how to handle wildcard chars?
  finally
    TableList.Free;
  end;
end;

The second request is a CreateTable method, which would use the various TZTable properties to construct the appropriate 'create table' query and then run it. It should also automatically clear or even update the appropriate parts of the metadata cache.

This may require some additional infrastructure to allow the full definition of a table, including indexes and other constraints (don't know what is missing, if anything), but maybe it can be at least partially implemented even with the existing infrastructure...

Posted: **13.06.2008, 15:12**

What about creating me a patch for the first one. This should be relatively easy to do, isn't it?

Concerning the second request : this is database server dependent code. We would have to implement a database dependent sql generator. (for a sample : see the Free pascal implementation for SQLite) Of course, this could be done once using ansi SQL and raise 'unsupported' for servers who do not support this syntax.
I'm wondering where in the zeoslib structure we should add this SQL generator. All TDataset handling should be done at the Component Level. The SQL creation is on the Dbc Level. I believe the column data is passed using TZColumnInfo objects. But I'm afraid they are only passed from dbc to component level at the moment. Not sure the conversion from dataset.columndefs to the internal Zeoslib structure already happens. in the other direction is already implemnted for the moment.
Do you think you would find some time to think about this?

Mark

Posted: **13.06.2008, 17:43**

I've never created patches before, but I'll give it a try.. Gotta learn to do it sooner or later I suppose - might as well be now.

How should I handle a closed connection? Also, what about wildcard characters in the table name (e.g. names like MY_TABLE)? I would opt to treat the wildcards as regular characters, going on the assumption that the TZTable represents a single specific table, but perhaps there are some other considerations?

As for the second, I'll have a look, but I'm afraid I'm still a total newbie when it comes to zeoslib...

Posted: **13.06.2008, 21:00**

Ok, here's the patch for table.exists.

Posted: **14.06.2008, 02:22**

Regarding the table.CreateTable. I read the overview of the Zeos DBO architecture, and took a look at the code. My findings are:

- Unit ZDatasetUtils contains three utility functions for converting between dataset- and dbc- style field information. Of particular interest is the ConvertFieldsToColumnInfo function, which takes a TFields class and returns an array of TZColumnInfo. It currently supports only a small subset of the information that TZColumnInfo can hold, but I believe that in principle it can be extended with more complete support.
- TDataSet has an (undocumented) CreateFields method which uses the (documented) TFieldDef.CreateField method to initialze the Fields from the FieldDefs.
- TZAbstractRODataset.InternalOpen uses the CreateFields method it inherits from TDataSet in order to initialize its Fields array, and then passes the Fields array to ConvertFieldsToColumnInfo. The resulting array of TZColumnInfo is then passed to TZRowAccessor.Create for further processing.

Therefore, IMO the following approach may be reasonable:

- Somewhere in the Dbc define a CreateCreateTableStatement method (or maybe CreateCreateTableQuery), which would accept an array of TZColumnInfo and some general information like the table and database names etc, and would return the appropriate Statement (or sql query string, which can then be assigned to a Statement). I would guess this should probably be on the driver or connection level.
- In TZAbstractTable.CreateTable, use the inherited CreateField if necessary, and then pass the Fields array to ConvertFieldsToColumnInfo, similar to what TZAbstractRODataset.InternalOpen does. Pass the returned array of TZColumnInfo and some additional info to CreateCreateTableStatement, and then execute the statement.
- If this does not provide enough separation between the various layers (depending on whether CreateCreateTableStatement or CreateCreateTableQuery is used, and where it is placed), use some intermediate method to provide the necessary separation.

Does this sound reasonable?

Posted: **15.06.2008, 22:55**

This sounds very reasonable. Did you design zeoslib???

I'll look at the other patch. when I finished reading the other new posts...

Mark

Posted: **15.06.2008, 23:53**

ZTable.Exists is a property now. SVN Revision 379.

Mark

Posted: **25.12.2017, 08:57**

Creating generic routine is not that simple as it could seem...
1) Backticks are not supported by all DBC's, f.ex. Firebird complaints on them. Moreover (for FB; I've no idea regarding other drivers) when you create a DB object with quoted name it remains quoted meaning all further operations will require quoting. That's unlikely what a user wants.
2) Why tables without PK raise exceptions?
3) SQL types must be obtained from TField classes using DBC-specific routines because not all DBС's support the types you used.
and finally
4) I still can't realize the sense of creating a table with field set taken from already existing table. Cloning from one connection to another? Probably. But I guess it should be done on metadata level rather than on TTable's.

Zeoslib Portal

TZTable.Exists and TZTable.CreateTable

TZTable.Exists and TZTable.CreateTable

Re: TZTable.Exists and TZTable.CreateTable