Servoy sequence handling

Classic Servoy
1

Hello all,

it’s not the first time I try to put this to the agenda. I just happened to run into the same problem. We develop on MS SQL Server and use DB identity for reasons. We deploy our solutions on all kinds of different databases that do not have DB identities, but named sequences (triggers). In the situation I am in now I want to deploy to PostgreSQL and I’d be fine with Servoy sequences. In older versions Servoy fell back to Servoy sequences if the provided sequence type was not supported by the target database. In 3.5 this does not seem to be the case anymore. Instead, I get DB errors when creating new records.

We have almost 200 tables. I currently see no other way than opening developer and clicking on 200 tables, find the pk column, click properties and choose Servoy sequence. This is a job for idiots.

What I think is needed is this:

  1. Urgently separate sequence handling from updating auto enter values

This is one of the biggest quirks, in my eyes. I cannot see a single situation where I don’t want auto enter values (like a 1 in a column, the server datetime or whatever) NOT to be updated. What situation would that be? If I have a solution running for example on PostgreSQL with Servoy sequencing, I can only choose between bad and evil when updating with a solution from MS SQL with DB Identities. Either I loose newly created auto enters or destroy half of the solution because Servoy overrides the sequence settings with something useless in Postgres.

  1. Make an option somewhere “Use Servoy sequences for all tables of this server”.

This would allow me to at least fall back to Servoy sequences independent from whatever auto enters and would save me hours of silly clicking on 200 tables. Even if in the case of one table or two I would have to make a manual correction…

  1. Make an option to set the sequencing for a whole server to one of the following
  • Servoy sequences
  • DB Identities
  • Named database sequence with a given pattern (for example “seq_tableName”)

If, for some reason, I deploy my solution on Oracle for example and CANNOT use Servoy sequences (because other apps also write to the tables), I will have to walk over 200 tables and manually enter a sequence name.

While 3. would be a great addition for the above situation, I consider the other two points crucial.

Maybe somebody else has ideas in this area?

I now I have to dig into the Servoy repository to see how I can set Servoy sequences to all tables, which is not really fun to do.

Thanks for taking the time to read this
Patrick

P.S.: Further reading for example here http://forum.servoy.com/viewtopic.php?p=43121

2

I second this also!
Came across the exact same thing, this week!

is it allready posted in the support page?

3

Also a “third” to Patrick’s comments and Harjo’s “second-the-motion” … same issues here on Postgres and with a strong desire to use Servoy sequences to allow deployment across multiple target db’s.

I strongly agree with Patrick’s comments to separate Auto Enter and Sequences. The marrying of two logically distinct operations creates a significant deployment hurdle and invites inefficiency and deployment mistakes while the solution data providers are “adjusted” for different deployment targets. “Identity” and “Auto Enter for a Column” are conceptually and architecturally distinct operations and should not be merged into a blended operation.

On a positive note, and to encourage Engineering, Servoy is 98+% there in this specific area and is very close to achieving a very elegant and solid deployment model. There has been much solid and well-thought work done to date. Keep up the good work!

I have noted the Servoy Server option to re-synch sequences.

Appreciate your efforts to resolve this important area.

Best, Michael

4

In the meantime I have checked the repository and sort of understand how the “mixture” of auto enter and sequence settings came about. Of course, it can still be handled separately. I managed to set everything to Servoy sequences and nothing seems broken. So anybody in need of this unsupported operation can contact me directly, until this might have been addressed by Servoy. It also seems simple to generate a query that will set named DB sequences…

5

Please, please do something about that :twisted:! I am frequently updating a repository that comes from MS SQL on a PostgreSQL. The sequences are set to DBIdentity. I can only choose between loosing my newly created auto enter settings or complete corruption of my solution because 3.5 sets all sequences to DBIdentity, which does not exist in PostgreSQL!

Now I have to fire a query to the repository every time I update. Kick all users, restart the server and resynch all sequences. That’s no fun!

6

I have made a case quite a while ago (#84432), by the way. In older versions Servoy would at least not override a sequence setting that is not supported on the target DB, but fall back to Servoy sequence. This is really a nightmare now.

7

Can anyone of Servoy confirm ,if this is on the wishlist soon?

We are bumping into this all the time, and it is a hell of a job, to fix all the sequence-settings.

8

Hi Patrick,

I support your call strongly.

We also depend on Servoy handling sequences properly. All our projects use PostgreSQL databases with sequences, so if this is not supported properly we will not be able to move to 3.5.

As we are having some problems with 2.2.x this will soon become a major issue for us. We cannot check and fix sequences on a 100+ tables after every import.

9

Yup,

2nd and 3rd and 4th to the above motions … it’s a concern for me in advanced architectural/design stage at this end (and into development). I am wondering if I have made the right call on my choice for sequence deployment method (ie - Servoy managed or db managed).

Michael

10

PostgreSQL user here (Hey! Who knew!?) so I underscribe the sentiments and concerns.