VpZeosDs and Visual PlanIt

[kkuba]

I would like to come back to the Zeos and TVPlainIn case. I looked at the fixes in the code, in general I think it's nice now, but I still have doubts.

I will start with a huge request @WP to update OPM. Thanks a lot for the corrections, but now code is very different from the current version of the component in OPM.

First of All, I was wrong in the previous post but the code is quite complicated. However, it seems to me that there is still something to improve in the code VpZeosDs.

1.  SetConnection -   if Autoconnect or wasConnected then Connected := True. Is this correct at the end?
The code is not readable for me, but maybe I am too old ...

Code: Pascal  [Select][+][-]

1. procedure TVpZeosDatastore.SetConnection(const AValue: TZConnection);
2. var
3.   wasConnected: boolean;
4. begin
5.   (..)
6.  
7.   wasConnected := (AValue <> nil) and AValue.Connected and
8.     (FConnection <> nil) and FConnection.Connected;  
9.   if not Connected then
10.     SetTableConnections(FConnection);
11.  
12.   if AutoCreate and (FConnection <> nil) then
13.     CreateTables;
14.  
15.   if Autoconnect or wasConnected then
16.     Connected := True;
17. end;
18.  

2. SetConnected - Probably code is good, but not very legible. Shouldn't it be something else?

Code: Pascal  [Select][+][-]

1. procedure TVpZeosDatastore.SetConnected(const AValue: boolean);
2. var
3.   canLoad: boolean = False;
4. begin
5.   if (AValue = FALSE) or (FConnection = nil) then
6.     exit;
7.   if AutoCreate and not TablesExist then
8.     CreateTables;
9.   FConnection.Connected := TRUE;

(...)

Same, but better to read.
Maybe someone more experienced than me will look at it if I am wrong.

BTW, In my opinion it doesn't work properly with ZEOS 7.2.14, I don't know why, but you need to install a newer version 8.x. With version 7.x, bugs arise in different places in the application.

[wp]

Well, it's been some time since I wrote this, and I do not remember the details. But I do remember that the requirement to handle AutoCreate, AutoConnect and manually setting Connected and Connection at both design and runtime was quite challenging, and I really refrain from changing anything without being forced to by a bug, just because some new code has better legibility. It's too easy to introduce regressions. Never change a winning team...

1.  SetConnection -   if Autoconnect or wasConnected then Connected := True. Is this correct at the end?

Why not? You change the connection, and at the end you want the new connection to be active if AutoConnect is ON, or if the old connection had been active.

2. SetConnected - Probably code is good, but not very legible. Shouldn't it be something else?

Code: Pascal  [Select][+][-]

1. procedure TVpZeosDatastore.SetConnected(const AValue: boolean);
2. var
3.   canLoad: boolean = False;
4. begin
5.   if (AValue = FALSE) or (FConnection = nil) then
6.     exit;
7.   if AutoCreate and not TablesExist then
8.     CreateTables;
9.   FConnection.Connected := TRUE;

(...)

Same, but better to read.

No. It has the side effect that once Connected has been set to true it cannot be turned off any more (because you exit the method in its first line if AValue (= the new value of Connected) is false).

I will start with a huge request @WP to update OPM. Thanks a lot for the corrections, but now code is very different from the current version of the component in OPM.

I do not intend to bring every change from the development repository into OPM immediately. It's quite some work to prepare and test the package. But I agree that a new release of TvPlanIt is overdue, and I try to set up one in the next days.

[kkuba]

Thanks for the clarification. Does it work for you with ZEUS 7.x? I gave up a bit with this component, because after using it, there are other problems. This time with the creation of resources. See the pictures below. An attempt to create a resource what happened and how the program is doing with the created entry in the database. I guess I'm unlucky to be using Postgres, and it doesn't work. On the other hand, I do not want to replace ZEOS with version 8.x in a large project because it is better not to fix something that works (as you wrote). I guess it's giving up for now... Maybe the new version will improve something globally,  I will wait when you finish it.

[wp]

Looking at the svn commit messages, I see that there were some changes in TvPlanIt related to FPC 3.2, and I do confirm some crashes of the OPM version with FPC 3.2+ which seem to be gone with the development version of TvPlanIt. So, did you ever try to install the development version of TvPlanit ("download snapshot" from https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/tvplanit/) and check whether it solves your issues?

[kkuba]

What you see above is Lazarus 2.2.2, Postrgres 14.3, ZEOS 7.2.14 and  TvPlanIt Snapshot [r8283]. So the developer version is TVPlanIt, the rest are stable versions

[wp]

I checked all sample programs coming with TvPlanIt whether there is an error when the database file does not yet exist at first start (there is an issue with TvPlanIt from OPM and FPC 3.2). No problem with Laz-main (or 2.2.2)/FPC 3.2.2, except for the datastore demo for ZEOS/Firebird for which the demo project must be restarted after creation of a resource (otherwise events, tasks and contacts added afterwards will not be persistent - I guess this is due to my missing competence with Firebird). Other than that I do not see an issue with ZEOS 7.2.x.

Your screenshots are not helpful since I don't see where these bugs are occurring. Please try to set up a small demo project which shows the errors and post it here (don't send me your "big" application - I am not motivated to debug an unknown application...)

What is your operating system? What is the easiest way how to set up Postgres on Windows?

[kkuba]

@WP By writing what's below, I think I found the problem. And it looks like that despite setting the autoconnect property, VpZeosDatastore1.Connected : = true should be added in FormCreate
It started to work for me. My fault for trying to cut corners. But as an excuse, in the description on the website, it says that this should be added if AutoConnect is not set. However, the problem with AutoCreate remains. It does not recognize that the tables are created, so after the first crash of the application, the property needs to be turned off. Then everything starts to work.

Nevertheless, you really helped, I wouldn't have discovered the problem myself

Original post:
There is no point in sending the program because the problem arises without it. Maybe it's something for me, but it's strange, because for testing purposes I have the simplest possible configuration.
1. To recreate the problem, I would try to run Postgres "somewhere in the network". I used the official Dockerhub container on Debian on another machine.
2. Remember to add Postgres DLL files to the Lazarus directory and the application directory (if you do not know the current list of required files, I will give it to you)
3. Lazaurus is running on Windows 10 Pro x64 21H2
3. Do the following sequence in Lazarus:
- Put ZConnection, set protocol, host, user and port. Select Connected and see if it works.
- If OK, put VPZeosDataStore and set Connection, AutoConnect and Autocreate
- Place a VpWeekView and set the DataStore.
Launch the application. The program will crash immediately. However, the table will build, so you can disable AutoCreate and restart it. Now it works, but creating a resource won't work for a change. Again, there is an entry in the table, but that's all.

If I have not written something very clearly or forgot something, ask me. Of course I can do something wrong, but I'm out of ideas.

All the code looks like this:

Code: Pascal  [Select][+][-]

1. unit ChPMain;
2. {$mode objfpc}{$H+}
3. interface
4. uses
5.   Classes, SysUtils, Forms, Controls, Graphics, Dialogs, ZConnection, VpZeosDs,
6.   VpWeekView;
7. type
8.   { TChBPMain }
9.   TChBPMain = class(TForm)
10.     VpWeekView1: TVpWeekView;
11.     VpZeosDatastore1: TVpZeosDatastore;
12.     ZConnection1: TZConnection;
13.   private
14.   public
15.   end;
16. var
17.   ChBPMain: TChBPMain;
18.  
19. implementation
20. {$R *.lfm}
21. end.
22.  

[wp]

if you do not know the current list of required files, I will give it to you

yes, please.

[kkuba]

Maybe you will manage to discover the problem with autoconnect property, as I wrote above.
Postgres 14 requires:
libcrypto-1_1-x64.dll
libiconv-2.dll
libintl-9.dll
libpq.dll
libssl-1_1-x64.dll
libwinpthread-1.dll

[wp]

Still trying to learn elemental PostgreSQL...

Did you study the sample project in (tvplanit)/examples/datastores/zeos? As you can see in the FormCreate procedure, Firebird requires extra code to create the database, unlike SQLite3. Maybe this is the same with PostgreSQL?

Or did you create the database outside your application?

[kkuba]

Postgres is cool ... I don't think that's the problem.
As I wrote above, the database is created - AutoCreate works. However, the component does not notice that the tables are created. It's like TVpZeosDatastore.TablesExist is not working properly - I don't know why - the code seems to be correct.
However, I confirm that, as I wrote, FormCreate must contain the line:
VpZeosDatastore1.Connected := true;
I don't know why - since AutoConnect is set.
Them after the first crash, set AutoCreate to off, and then everything starts working fine.

[wp]

I found now that PostgreSQL seems to create the tables with all-lower-case names. But TvPlanIt passes them with the first letter in upper-case. Please check whether setting them to lower-case (i.e. "contacts" rather than "Contacts" etc) in TVpDatastore.Create (in VpZeosDS) solves the issue? It does for me (Unfortunately, SQLite3 seems to be case-sensitive regarding the table names, and thus setting them to lower-case will break existing applications...)

As I wrote above, the database is created - AutoCreate works.
...
FormCreate must contain the line:
VpZeosDatastore1.Connected := true;

Autocreate refers to the tables, not to the database. I must create the database in pgAdmin4 before I can run the zeos demo. Otherwise it crashes with a "database ... does not exist" error.
I cannot imagine that I can connect to a non-existing database.

[kkuba]

Sorry for the inaccuracy. Of course the base is made externally, in my case as you can see with phpPGgadmin. I should have written the tables to be set up correctly. I have been at work since the morning, dealing with the law, not it - hence the symptoms of my overwork.
Below is what was created by Auto Create - and it's done well. In my opinion, detection of the creation of tables does not work well. The message suggests that the component wants to create it a second time - even that it has already been created.

[wp]

I think I found a solution: Add the following function to VpZeosDs and replace in CreateAllTable the <table>.Exists by the new TableExists(tablename):

Code: Pascal  [Select][+][-]

1. function TVpZeosDatastore.TableExists(ATableName: String): Boolean;
2. var
3.   L: TStringList;
4. begin
5.   Result := false;
6.   if not FConnection.Connected then
7.     exit;
8.  
9.   L := TStringList.Create;
10.   try
11.     L.CaseSensitive := false;
12.     FConnection.GetTableNames('', L);
13.     Result := L.IndexOf(ATablename) <> -1;;
14.   finally
15.     L.Free;
16.   end;
17. end;
18.  
19. procedure TVpZeosDatastore.CreateAllTables;
20. var
21.   wasConnected: Boolean;
22. begin
23.   wasConnected := FConnection.Connected;
24.   try
25.     if FContactsTable.Connection = nil then begin
26.       FConnection.Connected := false;
27.       SetTableConnections(FConnection);
28.     end;
29.     FConnection.Connected := true;
30.     if not TableExists(ContactsTableName) then CreateTable(ContactsTableName);
31.     if not TableExists(EventsTableName) then CreateTable(EventsTableName);
32.     if not TableExists(ResourceTableName) then CreateTable(ResourceTableName);
33.     if not TableExists(TasksTableName) then CreateTable(TasksTableName);
34.   finally
35.     FConnection.Connected := wasConnected;
36.   end;
37. end;

[kkuba]

I confirm this. This has been fixed. Works now. Thanks a lot.