What's the best way of check the table structure?

papelhigienico · Post by **papelhigienico** » 05.12.2011, 23:05

What's the best way of check a table structure across multiples database servers using ZeosLib ?

Before start my program, I wan't check if the table on database is the same of a pre-configured metadata, and if it's wrong, I want know what's wrong.

trupka · Post by **trupka** » 14.12.2011, 10:55

I have had similar problem with application upgrades because db schema changes often as well. My solution is based on concept of packages (like linux rpm/dpk) and works well most of the time.
Do you have complete control over database or someone else can change structure independently?

papelhigienico · Post by **papelhigienico** » 14.12.2011, 14:05

I'm developing a component set that will manage tables on database with specific usability (like register temperature, pressure, alarms, events, ect), aimed to be used by developers (that will have access to these tables and access to do something wrong). I think that final users cannot change these tables, but I don't know what features the application developer will let available to final user.

I'm thinking something like store the table structure into the application, and every time that these components are created, it'll check the structure. If something is wrong, the table will be renamed and another table will be created.

The way that I used to check the table structure with metadata, is doing a "select * from tablename limit 1" and checking each field with the structure that was stored on application.

I'm think that Zeos does this (I can be wrong), but I' don't found anything about this.

Post by **EgonHugeist** » 16.12.2011, 01:35

Sounds very interesting! I'am developing a similar Component to make all Database-Objects Persistent and using a Driver-based VersionControl-System, i'm interrestet in Knowlege-Exchange...

There are 3 Solutions depending your Proplem:

First Way:
Use TZSQLMetadata-Component and set the Metadatype to mdTables to get the Tables and afterward change Metadatype to mdColumns + TableName to get the Columns of the specified Table...

Second Way:
Use AConnection.GetMetadat.GetTables(const Catalog: string; const SchemaPattern: string;
const TableNamePattern: string; const Types: TStringDynArray): IZResultSet;
this is very, very Fast cause of the predifined Colums of the IZResultSet;

take a look at ZDbcIntf.pas go to IZDatabaseMetadata. There you can find all Metadatatype-functions resulting a IZResultset;

the last work on it was my http://zeos.firmos.at/viewtopic.php?t=3140&highlight= thread i think. So Zeos is strong but not complete to get the complete Metadata.

Third way:

Get IZConnection from ZDbcIntfs.TZDriverManager with the API of second way. Its small, fast and absolute not Persistent it's an Interface.

Let's work on it and hope anybody of the team-members will commit the Work...

Regards

trupka · Post by **trupka** » 21.12.2011, 17:34

papelhigienico,
Some time ago I tried this technique with storing data inside app and recreate table when necessary but this didn't work well for me:

- it's complicated to maintain code for multiple databases.
- if end users can change database structure you have 1001 problem e.g:
It's enough to create some procedure / view which depends on your table and dropping/renaming your table becomes impossible in many cases (dependecy issue). On the other side - if someone adds additional nullable fields to your table that usually won't be obstacle - app can still use same table.

Here's what I'm doing now:
- Since I don't need all structure information to know that my table is unchanged, I only calculate hash for fields I need and, separately, required fields (on DataSet level). Looks something like:

Code: Select all

function DataSetHash(const DataSet: TDataSet): cardinal;
var
    I: integer;
    S: string;
begin
    for I := 0 to DataSet.FieldCount -1 do
        S := S + DataSet.fields[I].FieldName +
            IntToStr(DataSet.Fields[I].size);
    Result := ElfHash(S);
end;

Hash is database/platform independent and it's easy to keep it inside application. If hash doesn't match app says "Hey! Someone messed up my tables! Dear user, if you know what are you doing, please fixit else call support or something.."
Actual patching databases is another story (with different problems). If there interest, I can post something about technique I'm using.

papelhigienico · Post by **papelhigienico** » 22.12.2011, 12:44

Hi trupka!

The problem of keep up to date some tables across multiple database servers is not a problem to me. I'm developing a component set, and on my head, developers that will use these components will work with one "managed" database by application, but it can choose between a lot of database servers. Maybe it's exactly what you say...

If you want know if the table was changed or not, hashing is a good choice. But to reconstruct the tables, I need to store the table structure inside the app, right?

Post by **EgonHugeist** » 23.12.2011, 03:54

papelhigienico, trupka, i fully agree

the Hash is a nice idea, to check if something is differnt and brings out a uique key.

But there is no option to make the Metadata persistent to up or downgrade, validate, re-/create, move or selfly reconstruct the Database.

So there a Components i never used who should do this. The keyword is NHibernate. There are two projects on sourceforge.net (InstanceControl and another). I never checked out these componenents cause i looked to the SQL-statemants. The point was they are weak. But take a look by your own.

The way i started was ZeosDriver-based, versioned, persistent Control-system. It supports Ancestors of each objects and i hope it will be able to upgrade, validate em self. So i made a lot of Interfaces for each Metadataobject and have to fill the procedures with the right SQL-Syntax. I completed assigning of the whole Metadata-structure of the Firebird/Interbase-Databases. Every possible information was read out and is persistent now. The main idea was, if i make a new project-release i don't have to check up if i've or somone else had made changes on the database. The component selfly reconstructs the structure and it compares it by differnt ways. Move to another DB-System will be possible too..

Also i started a Designtime-package for the component and it can selfly Update the DATABASE at DesignTime so i need no longer an external GUI like FlameRobin or the MySQL-Workbench(when i'm ready here) Nearly 13.000 lines i've done yet and there is still no end... Memory-Leaks, not ready procedures and so on.

Zeos supports a lot of Metadata-information but No (Check, unique, foreign)Constraints, Roles, external-Functions, triggers are not complete and so on.

papelhigienico · Post by **papelhigienico** » 23.12.2011, 14:05

EgonHugeist

I'm doing the same here. My idea is more simple because I'll check the same tables/indexes/fk always, the only thing that changes are the name of some tables because it can be configured by the developer.

Post by **EgonHugeist** » 23.12.2011, 15:53

papelhigienico,
Example of my way i prefered:

Version: 1.0.0.0
Tables
1-10 with specified column.

Version: 1.0.5.0
Tables 1-12 (2 additional one name changed and 2 Column-sizes where changed)
+Procedures
+Changed Foreign keys...
Version: x.x.x.x
.
.
.

Version: 1.1.0.900
One of the tables above no longer exists
Tables 2-12 and some changed colums
additional Triggers
new indices, which combintate columns which no longer named like in Version x.x.x.x

because it can be configured by the developer.

Sure. It can and how du your handle the changes (additional/ changes Tables, Fields, Procedures, Indices, Triggers) your brand new Project need's ?

Example: customer1 has an very old version 1.0.0.0 and you want it to upgrade to Version: xxx and later to Version: 1.1.0.900 or jump the step upgrade to Version: xxx.

if i understand you right than you have to do it by implement some code who is able to upgrade your changes, right? And that was the reason i started. i don't want to do this any longer. There must be an intelligent solution which is able to do it by himself.

New project-release means an new persitent-version of the structure if needed -> then upgrade.
+ check/validate structure anytime you want...

papelhigienico · Post by **papelhigienico** » 23.12.2011, 19:20

EgonHugeist

I'll explain: I'm developing a project called PascalSCADA. It's a set of components that allows Lazarus/Delphi developers make applications that will exchange data with PLC's/Industrial devices (temperature, pressure, tank level, status of some machinery).

In certain cases, some of these variables must be stored. So, in my head, the developer will insert a "historic" component on their project and link it with the variables to store. It can stores other data, like alarms, production recipes, each one will be a component on project. Some components like historic, can exists more than one on application, each one with your own table, where the table name will be supplied by the developer.

Theoretically, the tables created by these components never will be changed, but we are developers, and if you have a way to do your work more fastest or to avoid complications, you will do it! I don't know if some of these changes will be done on these system tables and because of this, I need check the table structure.

At this point, the project PascalSCADA don't uses views and procedures (and triggers too? I don't know yet) on database.

Sure. It can and how du your handle the changes (additional/ changes Tables, Fields, Procedures, Indices, Triggers) your brand new Project need's ?

So, I must check only tables created/managed by these components and theirs foreign keys, fields, indexes and triggers. Others tables created by the developers, will be ignored.

if i understand you right than you have to do it by implement some code who is able to upgrade your changes, right? And that was the reason i started. i don't want to do this any longer. There must be an intelligent solution which is able to do it by himself.

No, I'm doing a code to correct possible changes made by an careless developer on these system tables or drop/rename if the table are unrecognizable. At this time, I don't thought on changes made by an version upgrade and your idea of versioned metadata make me dream with it

Post by **EgonHugeist** » 23.12.2011, 20:58

papelhigienico,

I took a look at your project on SourceForge.net... Seems like you're a real developer for OpenSource not only a CodeGetter so i'm thinking about sharing my findings. I wrote a pn to MDeams if Zeos is interestet in an additional component called ZDatabaseDesigner. It's based on the complete ZDbc-IDE like your Project is based on the ZeosComponents but like i wrote its in a Alpha of the Alpha-States. Anyhow the Mainstructure and the most abstract Classes ar ready and the first driverbased-interfacees to get Firebird/Interbase-Metadata too. There i wanna change some things internal like the followings:

the inclomplete metadata of Zeos needs some ExecuteQuery-Statements and if they are not predefined they do start the ZSQLParser and do get all FieldInformations( for the ZVariant ) about my ExecuteQuery-Statements. That means it could take some time to complete the assigning of the metadata. To speed them up i do not want to add addidinal MetaDatatypes like in my http://zeos.firmos.at/viewtopic.php?t=3140&highlight= thread cause ZeosAccess do not realy need it. So the solution would be to write an internal function with the PreDefinedColumnInfo the IZResultSet results to make access fast as possible and add it to the Zeos project. No complicated thing but to do. Thats the reason i wrote the 3 ways getting the your informations this thread was base before.

Example: to get the complete Metadatainformations of all possible Objects (Userdata(+Systemdata only at designtime) of realy everything) could take up to 10 seconds with no idle-time.
That's an amazing Problem.

Also i plan an XML-Ex/Import-function. The reason is what if the Developer which is using that Component drops it from the Formular? All Informations about every Version will be lost cause of its persistence. The actual Version is reassigned fastly but older Persistent-Saved versions? There the Persistance is a Problem so i do need a workaround to handle it. How do you handle it?

MyStructur:

DesignerComponent
|
SomeDatabases
|
TheDatabase(choose your Driver)
|
SomeVersions
|
TheVersion(store connect and versioned informations)
|
!Unknown-Metadataobjects!

Means One4All not 10 or more Components needed and no ZComponet needed only internal Zeos-API

If you use this you'll be able to use StoredProcedures, ExtarnalFuntions, Triggers and so on. The Component gives you the possibility to handle every ZoesAccessable Databases if they support it...

papelhigienico · Post by **papelhigienico** » 29.12.2011, 01:37

EgonHugeist

CodeGetter, nice language expression

I'm doing some source drafts. The first idea is:

The Database
>>Tables
>>>>One Table
>>>>>>Fields
>>>>>>>>One Field (name, type, nullable, default value)
>>>>>>Primary Key (name, fields of Pk)
>>>>>>Unique indexes (name, fields)
>>>>>>Foreign Keys (source table, source field1 => target field 1, ... , source field n => target field n)

As I said, I started a lot of source drafts, nothing working yet. I erased a lot of sources. The first functional version will work with Postgres. After I'll do it for MySQL, Firebird and SQLite (all open source databases). I see that some sql to get metadata on PG are missing on Zeos, but I'll fix it.

papelhigienico · Post by **papelhigienico** » 29.12.2011, 01:46

And here (Brasil) the end of year is pathetic, anybody works... And people that are stopped makes you stop too...

Post by **EgonHugeist** » 29.12.2011, 22:04

Sound's very good if you can fix the Metadata. I'm thinking of adding a mdCharacterSet and mdCollation type to get the specified CharacterSet and/or Collation for Field/Table/database objects. Then i wanna make a Property-Editor ready to select them in Designtime. So Zeos can use it and i need it in my Component to select it for Fields/Tables/Database. It seems Zeos messes this in mdTable/mdColumn metadata.... Don't you use it in your Component?

Could you make the mdTrigger-Type ready for PostgreSQL.

My favourite Database-System is Firebird and MySQL so i'll add it there, if i've the time.. (hope this weekend)

Post by **mdaems** » 30.12.2011, 17:20

EgonHugeist,
Could you please describe a little what the basic functionality of this component is?
Is the main function to be an 'auto-db-upgrade' component. Like something you put in your datamodule and it checks your database structure on startup to see if a schema change is needed? That would be a terrific thing to have.

But keep in mind the zeoslib layered design. eg. There should be no protocol tests on component level. But you can have dbc calls that return they are not implemented for the connection protocol they are using.

And yes, I know there are two db specific components. These should be merged and db-specific code should move to the dbc layer, but I don't know how to do it myself. I hope we can avoid it with your component from the start.

Mark