Postgres, ZEOS and Visual PlanIt - unable to create a table

[kkuba]

I have a serious problem with Turbo Visual PlanIt.  I am using Lazauras 2.2 (TVPlanIt from OPM), ZEOS 7.2.14 and Postgress 14.2 PSQL is from this Postgress version

Prepares the simplest possible example.
1. I create a database in Postgres 14.
2. I am placing and configuring Zconnection. I verify the connection to the base, everything works fine.
3. I am placing TVpZeoseDatastore and set the connection to Zconnection and then check AutoCreate property.
4. The entire IDE is crashing down. After closing the damaged environment, the IDE project cannot be loaded any more. Reloading the entire IDE crashes again.

For testing, I repeated the operation in CodeTyphon 7.7. In CT there is ZEOS 8.0 configured and the compiler is FPC 3.31. Unfortunately, the problem is exactly the same. The whole environment breaks down. The advantage of CT is a better error message - which I attached.

The problem is very difficult to diagnose for me because not only the application is damaged, but the entire Lazaurus is damaged. Thanks in advance for any help.

[wp]

After closing the damaged environment, the IDE project cannot be loaded any more. Reloading the entire IDE crashes again.

This is very unusual. Could it be that you set up the IDE to automatically re-load the project of the last session? "Tools" > "Options" > "Environment" > "IDE Startup" > "Open last project and packages at start". Since you cannot fix this in the IDE due to it crashing you must edit the config file manually: Find the folder in which Lazarus saves the user profile settings. Normally this is in your home directory (Windows: c:\users\<your login name>\appdata\local\lazarus, Linux: ~/.lazarus); in case of a secondary installation it is the folder specified in the lazarus.cfg file of the Lazarus installation, or as commandline argument (--pcp=.... or --primary-config-path=...). Open the file environmentoptions.xml in an editor and find the node <AutoSave>. Set the attribute OpenLastProjectAtStart to false. Now Lazarus should start again.

As for the TvPlanIt issue: Since I am not very experienced with the large database systems they are not tested very well. I am aware that the AutoCreate and AutoConnect properties is problematic sometimes. Do not set them at designtime because a fatal bug will crash the IDE. Better to set TDatastore.AutoCreate in the form's OnCreate handler, or even better call TDatastore.CreateTables and connect manually (Connected := true). If there is a crash it will affect the application only, not the IDE, and debugging will be much easier.

Did you try the demo project in examples/datastores/zeos?

[paweld]

As for the error, it occurs because PostgreSql does not support the AUTOINCREMENT keyword.
To fix this problem you need to edit the structures in the vpzeosds.pas file, instead of INTEGER AUTOINCREMENT use SERIAL - attached is the file customized for this RDBMS.

As for the problem with Lazarus, as @wp wrote, you have the option to open the last checked project and this causes errors. You can also solve the problem by renaming the directory with that project and then launching the IDE - if it doesn't find the project, it will start with an empty new project

[wp]

As for the error, it occurs because PostgreSql does not support the AUTOINCREMENT keyword.
To fix this problem you need to edit the structures in the vpzeosds.pas file, instead of INTEGER AUTOINCREMENT use SERIAL - attached is the file customized for this RDBMS.

Thanks for this information. I am not a heavy user of ZEOS, but isn't ZEOS designed such as to avoid these differences in SQL dialects? Or does ZEOS provide its own "general-purpose" AUTOINCREMENT keyword which is valid for all database systems?

[af0815]

IMHO no, ZEOS breaks the differences between Delphi and Lazarus. But i have seen, not breaking the differnces between  DB with autoincrements or with generators or SQL have simply nothing. There is no 'general purpose' autoincrement. And there are a lot of differences between the DBs if you dive deeper. And it is not a problem of ZEOS, you can see this in SQLdb or you hook in windows on ADO or ODBC. ODBC ther is the question - on Linux (which implementation) or Windows. And .........

I have never found a common autoincrement working for MS-SQL, Interbase, Sqlite and DBase working :-) I have no knowledge about Oracle-DB or Postgres. But you have always to respect the specialities of a DB-Sever (and its Version) if you make more than 'select col1,col2,col3 from mytable' :-)

[paweld]

In the given file, the structure is called directly on the data engine as a sql script. So, it is the user himself who has to adjust the structure to the appropriate RDBMS.

- AUTOINCREMENT is for sure in SQLite and MySQL/MariaDB.
- In MSSQL there is IDENTITY.
- PostgreSql has SERIAL.
- and in Firebird you have to write your own generator: https://www.firebirdfaq.org/faq29/
I am not familiar with Postgres and Firebird, so maybe someone proficient in this RDBMS's can comment.

[af0815]

On SQlite there is an Info (https://www.sqlite.org/autoinc.html)

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.

So it should avoid :-) The situation for Autoinc/identity/whatever is not so simple :-)

[dsiders]

In the given file, the structure is called directly on the data engine as a sql script. So, it is the user himself who has to adjust the structure to the appropriate RDBMS.

- AUTOINCREMENT is for sure in SQLite and MySQL/MariaDB.
- In MSSQL there is IDENTITY.
- PostgreSql has SERIAL.
- and in Firebird you have to write your own generator: https://www.firebirdfaq.org/faq29/
I am not familiar with Postgres and Firebird, so maybe someone proficient in this RDBMS's can comment.

Firebird has an IDENTITY column type:

https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-ddl-enhance.html#:~:text=An%20identity%20column%20is%20a,omitted%20in%20an%20INSERT%20statement.&text=When%20defining%20a%20column%2C%20the,See%20Tracker%20ticket%20CORE%2D4199.

It works well in my applications developed using IBX for Lazarus. I have not used SqlDb or ZEOS for Firebird access.

[kkuba]

Thank you very much for your help. I should note that there is a use of "AUTOINCREMET", but as an excuse, I was confused that the entire IDE had crashed. By the way, the IDE shouldn't work that way, but that's a topic for some other investigation.
It seems to me that TVPlanIt can be easily improved, it is enough to check which protocol has been selected in ZConnection and then enter SERIAL or AUTOINCEMENT accordingly.
But new problems have arrived. A new screen is attached.
I guess I'm unlucky to be the first person to run this with Postgres. But I'm not giving up ...
What this time? Something about the field names this time?

[paweld]

Funkcja sprawdzająca czy istnieją tabele, nie wykrywa ich poprawnie i następuje próba ponownej instalacji co kończy się błędem. Popatrzę na to jutro.
---
The function that checks if tables exist, does not detect them correctly and a reinstall attempt is made which ends in an error. I will look at this tomorrow.

[paweld]

Unfortunately I cannot confirm this error. In my case, the component correctly detects if the tables exist before reinstalling them.
Try to connect the component to a new database, or delete the tables in the existing one:
- Contacts
- Events
- Resources
- Tasks

@wp: attached modified version of VpZeosDs.pas taking into account setting the structure depending on RDBMS, for now only sqlite, mysql, pstgres and mssql

[kkuba]

 In my opinion, the problem persists.You had too little patience to catch him. 
To verify it:
1. Create a project with the AutoCreate property active in the VpZESODataStore
2. Exit the IDE
3. Enter the IDE, the table will be created - it's ok.
4. Exit the IDE
5. Enter the IDE, the project is broken, the IDE is crashing
After unchecking the AutoCreate with existing table property, a new kind of error is generated. Overall it doesn't work and there's no way around it. I tried various other combinations - AutoConnect checked with existing table  - it generated error, not checked - it generated error too, but different. You've probably given a good cause for the problem, but something still needs to be improved.

[paweld]

I repeated the steps you provided and still have no errors.
I also try to force create tables (VpZeosDatastore1.CreateTables) and still everything is ok.

I check on Lazrus 2.3 (trunk), FPC 3.2.2 x86, TvPlanIt from SVN, Zeos 8, Postgresql 14.2, Windows 10 x64

[kkuba]

After your post, I checked it again. You're right, because the problem is somewhere in Lazaurus 2.2. The same code compiled in CodeTyphon 7.7 works. A bit weak because I have too big a project to transfer it to the trunk for this reason. But I figured I would extract this piece into a separate file and make it in CT because it works there. As you can see, we are waiting for Lazarus 2.4;)

@paweld To jakieś wstrętne dziadostwo w RTL lub ZEOS 7.2. Szkoda mi czasu na szukanie co jest grane. Skompiluje sobie ten kawałek gdzieś indziej. Bardzo dziękuje za pomoc, bo bym się nie pozbierał, a nie znajduje innego komponentu z planerem do Lazarusa. A złośliwie mam dużą bazę w Postgresie

[paweld]

Polecam korzystać z Lazarus zamiast CodeTyphon. I przede wszystkim zaktualizuj komponenty do najnowszej wersji:
- ZEOS do 8, można pobrać stąd: https://github.com/marsupilami79/zeoslib/archive/refs/heads/8.0-patches.zip
- TVPlanIt - w OPM jest wersja z 2019 roku, od tego czasu było trochę poprawek: https://sourceforge.net/code-snapshots/svn/l/la/lazarus-ccr/svn/lazarus-ccr-svn-r8252-components-tvplanit.zip

Planera możesz znaleźć też w płatnej paczce komponentów TMS FNC UI Pack ( https://www.tmssoftware.com/site/tmsfncuipack.asp?s=fncplanner#features )
---
I recommend using Lazarus instead of CodeTyphon. And above all, update the components to the latest version:
- ZEOS to 8, you can download from here: https://github.com/marsupilami79/zeoslib/archive/refs/heads/8.0-patches.zip
- TVPlanIt - there is from 2019 version in OPM, there have been some fixes since then: https://sourceforge.net/code-snapshots/svn/l/la/lazarus-ccr/svn/lazarus-ccr-svn-r8252-components-tvplanit.zip

You can also find the planner in the paid component pack TMS FNC UI Pack ( https://www.tmssoftware.com/site/tmsfncuipack.asp?s=fncplanner#features )