I would like to know if there are better / other ways to do database updates (in development)
I have a SaaS construction and my webservers hosts databases for several customers.
My application is divided into several modules to keep some logical parts together.
So far, so good.
But our development is a continuing story as well. And sometimes during development a database change is needed (for example new columns are added to a table). One of our programmers is only making changes in module A.
This developer is making his changes in Servoy Developer (4.1.4) and Eclipse and hasn’t commit his changes yet
On the other hand sometimes in another module B that uses the same database, an update must be made. And this update must be installed quickly on the production webserver.
For the forms & methods there are no problems. The update can be made.
But since database changes are committed directly into the database, I have to update all databases on the webserver, while this database change is not needed for the quick update that I have to do.
Because ‘Export solution’ looks to the actual database, which means that is sees the new added column.
The only workaround I see is that the quick fix must be made directly on the webserver (or on a test server, which should be a copy of the webserver) and that is not really a desired solution.
This means that I have to make the change twice. Once in development and one on the webserver.
Some way I would like to see that database changes (like new columns) can also be commited and then the database is changed.
Because in Eclipse all is done locally within the workspace except the add of a new column.
Is it technically possible to execute the database change on commit?
Does someone has a workaround for such a situation?
When I talk about commit, I mean commit using Teamsharing.
Could it be a solution to write to a local repository database (each developer has own database) and on commit write the changes into the central repository?
Because in the current situation I can not work offline neither.
I need a VPN connection to the office to start developer
It’s recommended to work with a test server (with a test database).
Or even setup a local database that you as a developer use and when you commit code and your schema changes to your teamserver (for non-Servoy files an SVN server is needed, unless you want to put your non-servoy files in a module as ‘media’).
This allows you to work offline as well.
As for the schema changes you could use tools like LiquiBase or other (visual) tools that are out there.
And when you deploy you export the solution and import it into the production server and Servoy will make the changes for you in the production database. If you don’t want Servoy to do this then you could make the schema changes yourself, restart the Servoy server and then import the new release of your solution.
why don’t you switch to svn-repository?
There’s much better tracking on your changes, it’s faster than default TeamRepository. (even over the internet it’s fast)
I don’t really get you point about database changes not being committed…
Every workspace has a ‘resource’ folder where the ‘dbi’ files are located.
If 1 developer changes a dbi file (automatically done when adding a column) this should be commited as well to the repository.
When another developer syncs with the repository, he should get the dbi change as well. (otherwise it should show up in the problem pane)
Anyway: solution exports do contain just the structure of the tables that are used by the forms in this particular solution… (in v.5.0 there are options to always export all referenced tables)
martinh:
Because in the current situation I can not work offline neither.
I need a VPN connection to the office to start developer
This shouldn’t be a problem:
your sourcecode is in your local workspace > this can’t be the problem
your database server connection, may reference the servers in your office > this is a problem, but solvable
Solution:
take a backup of the database(s) and put them into the database folder of your servoy folder
create a 2nd properties file, which will reference your local databases (you can name this anything, in this example: ‘servoy.properties.local’)
create a 2nd shortcut to the Servoy executable, but change the ‘target’ reference like this:
\developer\servoy.exe -data “” -vmargs -Xms40m -Xmx256m -XX:MaxPermSize=256M -Dproperty-file=servoy.properties.local
Make sure the ‘local’ properties file will launch the db engine as well, otherwise you’ll end up with the eclipse environment & offline databases… (I always alter the properties file, from the default servoy install)
Now you can work offline, a few ‘tips’ & ‘traps’
tip: open up the port used by the TeamProvider/SVN/whatever, or use your existing vpn connection for this. Now you’ll be able to normally ‘sync’ your workspace.
trap: remember you’ll work with an ‘offline’ database. Which makes database changes (structure & data) a little tricky.
mboegem:
I don’t really get you point about database changes not being committed…
Every workspace has a ‘resource’ folder where the ‘dbi’ files are located.
If 1 developer changes a dbi file (automatically done when adding a column) this should be commited as well to the repository.
Hi Marc,
This is exactly the problem. When a developer adds an new column to a database using Servoy DatabaseServer, then this change is not only in the local .dbi file but also directly added to the database that is linked to that .dbi file
So this new column is not added to the central repository when developer uses TeamSharing commit, but directly when column is added
Which is a problem because now I have to update all my other databases (clones, but I’m still on version 4.1) because otherwise when running solutions I get errors because those clone databases do not have this new column even when they don’t use it. Because this new column is in the master , it must be in the clone as well. Which makes me not happy when I have to make a small fix, while colleagues are developping and adding columns.
When export solution, the solution contains those new columns and so on my production server, these new columns are added into the master database as well
Cost me a lot of extra time to update the clone databases on the production server and that for new fields that are not used by the solution running on production server.
Maybe Servoy should ignore new columns during Export Solution until the new column is commited by Teamsharing. That would solve a lot of these problems.
I think Servoy still has another problem as well, because Developer takes about 20 minutes to start or doesn’t start at all, when you have database servers that are linked to the SQL Server.
I would expect that Servoy ignores that a connection can not be made, but somewhere Servoy is doing something with the databases.
So no way that you can open Developer offline.
I’ll consider your suggestions but if Servoy ignores the connect failure (looks like they use 5 minute timeout per database, making 20 minutes voor 4 databases ), then I can change the Dataserver properties and link them to a local database.
ROCLASI:
It’s recommended to work with a test server (with a test database).
Or even setup a local database that you as a developer use and when you commit code and your schema changes to your teamserver (for non-Servoy files an SVN server is needed, unless you want to put your non-servoy files in a module as ‘media’)…
A test server wouldn’t solve my problem, unless I make fixes directly into the test server.
When having a test server, you use import solution
And import solution, means export solution from developer
And export solution contain the new unwanted columns
ROCLASI:
As for the schema changes you could use tools like LiquiBase or other (visual) tools that are out there.
And when you deploy you export the solution and import it into the production server and Servoy will make the changes for you in the production database. If you don’t want Servoy to do this then you could make the schema changes yourself, restart the Servoy server and then import the new release of your solution.
Hope this helps.
The schema changes itself are not the problem. I have a system similar like Danny showed during Servoy Camp.
The problem is the time that it costs me to install a hotfix on the webserver, when new (unused) columns are added to the master database
martinh:
I would expect that Servoy ignores that a connection can not be made, but somewhere Servoy is doing something with the databases.
So no way that you can open Developer offline.
If I’m not mistaken you can change those settings… (at least the nr. of re-tries) can’t remember where I’ve seen it though…
martinh:
I’ll consider your suggestions but if Servoy ignores the connect failure (looks like they use 5 minute timeout per database, making 20 minutes voor 4 databases ), then I can change the Dataserver properties and link them to a local database
Why would you like to change this over and over again if it’s just a matter of 1 property file & 1 shortcut?
It’ll definitely take more time to startup eclipse with offline databases…