PostgreSQL and Servoy: Any positive or negative experiences?

[swingman]

Hi all,

I have moved my repositories to PostgreSQL and everything is working fine so far. I'm considering using PostgreSQL for everything new from now on since it is nice and simple to work with...
now...
Postgres has a lot of nice features like

- Table inheritance
- Rules :-)
- Functions
- Triggers
etc...
While Servoy likes to cache data...

Any firsthand experience out there of which PostgreSQL features work well with Servoy and which ones will confuse Servoy?
Any workarounds?

[jcompagner]

if you know the database altered data under the hood then you need to let servoy know that (databasemanager.refreshRecordFromDatabase())

[swingman]

Let's say the database alters some records, is there a way to send a message back to Servoy that it needs to refresh? or is it easier to just call a refresh after code which may cause an alteration?

[jcompagner]

no there is no such way. Databases can't push things back to there clients.

Maybe some trigger that executes a program that talks to a servoy server plugin? That could work with some databases.

[Harjo]

Is there anyone, who can or want to build such a plugin?

I have still some situations where I need Servoy as frontend and php for the website and where it is possible to update records from the web to Servoy clients.

(headless-client is in this specific situation not an option)

[swingman]

A refresh plugin sounds interesting.

One of my clients manages orders from his website on a Servoy-based system and I made him a "update" button which refreshes Servoy and brings in the latest orders... but, if he forgets to refresh -- he will not see the new orders :-(

Now, does refresh in Servoy refresh the entire database or just the data in the tables used by the current form?

[jcompagner]

if you make a plugin then currently the refresh can be for a specific server/table:

public void flushAllClientsCache(String server_name, String table_name);

or all if server/table or null.


You should see new orders automaticly because if you search for them again they will be found (so CTRL-J/Browse All). The refresh are mostly for rows that are changed by another system and servoy already has that row cached.

Also related foundsets could have a problem by getting new rows if they are already completely loaded.. Then also a refresh is needed.

[Harjo]

How much time do you think it will cost, to build such a plugin?
(for a basic java-developer and a for expirienced java-guru like you? )

[pbakker]

How does this topic fit in:
http://forum.servoy.com/viewtopic.php?t=4103&highlight=

If I refresh a foundset in a Client and it finds new records, it will load them into the Client where I did the refresh, but these new records do not get broadcasted to other Clients...

Paul

[jcompagner]

new records are never broadcasted. (except in related foundsets). Mostly new records are just found when doing a new search for them.


puur java such a plugin is pretty easy. But the database implementation can change for every database. IF it is even possible for a database..
And we still have one problem. Also records that servoy itself changes are seen by that trigger so also those are reported as changed. This behaviour is not very welcome.

[pbakker]

As for the last issue, that the DB trigger would also be called when Servoy updates:

You could have Servoy log into the DB with a specific User. Then the trigger can evaluate the User and if it's the Servoy user, do not take action.

But, that is not very generic, I have to add.

Paul