Sequence handling on repository import

Classic Servoy
1

Hello,

I have had and reported this before, but now it just happened again and it is very annoying:

  1. We are working on a solution in a Repository on Machine A.
  2. The database we are working on is on machine B. All primary keys are database sequences and set accordingly in the solution.
  3. Now we export the repository and import into a brand new Repository on machine C; the database still sits on Machine B.

When we do that, (almost?) all primary keys (we have more than 100!) are set to Servoy sequence.

This happens on MS SQL Server 2000.

Thanks for investigating.

2

The logic behind this action was that if you import a solution where it uses a DIFFERENT backend database, it is not possible to create database sequences for existing tables. Thus, if the table exists in the database the only safe option is to revert to Servoy sequences.

However, realizing that in general if you import and the table exists in the backend database it is either the SAME database or a one in which the table was most probably created by a PREVIOUS import (and therefore also has the database sequence), it seems actually a better solution to just keep the database sequence on the column and warn the user that he/she must make sure it exists in the backend.

In other words, in next release this issue has been fixed and if you reimport your solution (the existing .servoy file), it should work.

3

Thanks! That does sound like the best solution.

4

Hello, this looks like it has been fixed to the worse. I just imported a solution with 2.1.1 build 313 into a brand new repository using the same database connection as the original version and ALL primary keys are set to Servoy sequence.

I am currently busy changing 134 tables by hand, clicking 134 times on OK when Servoy warns me that it will not do the changes for me. Couldn’t we just do nothing about the sequences when importing a repository? I think that would be better…

5

patrick:
Hello, this looks like it has been fixed to the worse. I just imported a solution with 2.1.1 build 313 into a brand new repository using the same database connection as the original version and ALL primary keys are set to Servoy sequence.
… Couldn’t we just do nothing about the sequences when importing a repository? I think that would be better…

I agree with patrick here; it appears that not only were all db ident keys were changed to Servoy seqs, but that any id fields in n-m tables were also changed to Servoy Seq (from none). My client data is in the same place as before (MySQL) with db_identities on all tables, since I am bulk loading data from an external source.
Luckily I don’t have 134 tables, but 25+ is still more than I wanted to fix…

6

Aha, it looks like I fixed a different problem. It is the Servoy REPOSITORY which contains information about the sequences on tables, not the database. Unfortunately when you create a CLEAN repository, this means that all primary keys are set to the default, namely Servoy sequence. (There is no other way to go about this. See Note 1.)

When you then import your solution, it actually DOES what you ask!! Namely, it sees that the tables already HAVE sequences in the repository, and does NOT override them with the values found in the import. Therefore, all your tables will end up with Servoy sequences.

What I can do, and I cannot think of any other solution, is to give the user the CHOICE to OVERRIDE sequences when doing an import. Note that this very dangerous, since OTHER solutions could be using these tables/columns as well.

Thus normally you would like to leave existing sequences in a repository as the are, unless you are creating new tables (and this is, what Servoy currently does). However, in your case you want to OVERRIDE the existing sequences, and that currently is not an option. This is what I will add.

Here I think it is best that I ask once per server that the solution uses, if it should override sequences (should the question arise). Asking for every column will result in having to do 1000 clicks, and not asking per server is too coarse grained.

Is this a good solution for you?

Note 1: Actually there is a possible solution for this as well, namely it could be possible to specify the default sequence for a server in the servoy.properties file. This default could be either Servoy sequence or db identity. (Note that it CANNOT be db sequence, since there is no way we can tell WHICH sequence should be queried for inserts). I will consider adding this option as well.

7

Hello Sebastiaan,

I am not 100% sure I understand you right. What I’d expect Srevoy to do is this:

I have a repository that knows everything about my solution including the underlaying database. So when I import a solution into whatsoever repository I would expect that the result reflects what is stored in my repository.

The only exception I see is on an update, where two versions of the repository differ. There, of course, Servoy has to do something about it. But in all other situations I would expect Servoy to leave its hands off the sequence settings.

I cannot imagine a situation for example, where I develop a solution in my office using Servoy sequences and my customer wants to use DB sequences. Since the use of either one requires some thinking when designing the solution, I don’t think this will ever happen. So when I import my solution wherever, I want exactly my sequence handling to be the result. As a matter of fact I believe that most developers sooner or later will end up using DB sequences, because it is the only way to work in an environment where different applications are using the database.

But recently, we had some more really bad experiences with merging repositories in general. We are in a situation right now, where we have a certain pressure to get things done and are basically not able to put two or more people to work at the same time. This is getting a real problem.

I would be grateful, if someone could explain exactly what happens on either merge setting or how we are supposed to deal with this issue or when we can expect some sort of multi developer support. This is currently our biggest problem wasting at times hours of work.

Thanks!

8

I have a repository that knows everything about my solution including the underlaying database. So when I import a solution into whatsoever repository I would expect that the result reflects what is stored in my repository.

This is actually pretty impossible. First of all, the underlying database can vary in at least 2 ways:

  1. In general, you do not develop on the same database INSTANCE as you deploy. This means that sequence numbers CERTAINLY do not correspond. Thus your repository cannot know EVERYTHING about the underlying database.

  2. The backend database need not be the same at all, i.e., one person can run the same solution on Oracle, the other on Sybase, the other on mysql. These databases might not even support the same kind of sequences.

Furthermore, a different solution might already be running on some tables that you also want to use, except designed around a DIFFERENT usage of sequences that you have. It is better to be careful about just overriding sequences, since it can break existing solutions.

When you create a NEW repository, it does not know very much about the underlying database except what the database can tell it. Thus Servoy fills in some sane defaults.

In general you do NOT want to override sequences of an existing repository, since they reflect the proper settings for that specific database instance.

I agree, that in your case you DO want to override sequences. Therefore I suggested an option while doing an import, that allows you to do this.

Finally, merging a solution is a tricky business. It’s probably better to discuss that on a different thread, as it is unrelated to these issues.

9
  1. In general, you do not develop on the same database INSTANCE as you deploy. This means that sequence numbers CERTAINLY do not correspond. Thus your repository cannot know EVERYTHING about the underlying database.

I agree that I don’t develop on the same database therefore I do not have the same squence NUMBERS, but I suppose that in 99% of all cases I will have the same sequence TYPE. How will I be able for example to work with a solution that thinks a sequence is a Servoy sequence while the database has its own. I will get an error every time I insert a record. So I have to take care of that before I import anything, don’t I?

But let’s not make this a philosophic discussion. If you implement a way where the sequence settings of the repository to be imported are used as they are in that repository, I am happy :lol:.

Thanks!
Patrick

Where do you think the merging issue should be discussed? This is really getting a big problem!

10

On merging: A new thread on Issues and Bugs is fine, if it’s an issue or a bug :wink:

On sequences: I will implement what I described above.

11

It is currently implemented 2.1.2 to allow override during import.
The default sequence type is not implemented yet.