DAL development versus production

[annet]

A couple of month ago I raised the following question:

Every now and then simple changes to my model raise errors I do not
understand. My PostgreSQL database contains example records for
development purposes, so it is no big deal to drop and re-create some
tables. But what if these errors occur after deployment on a
production server.

After initial deployment, when I continue developing my application,
the database in my development environment will start to differ from
the one on the production server. When I redeploy my application on
the production server, the files that make up the model match my
development database, but not my production database. How does web2py
deal with these differences?


Since the question wasn't answered I post it again, at the time of
posting is was just me wondering about web2py's behaviour, now that I
am nearing the point of deployment on a production server the answer
to this question has become much more important.


I wonder whether it is a good idea to put the database model in the
init application and copy and paste it to the applications that make
use of the model and set migrate to False?


Best regards,

Annet

[Alexandre Andrade]

I had the same problem, and don't have a good answer to why this occurs.

Could be something specifically a bug with Postgresql? The problem don't occur which sqlite.

Besides, I always think the most annoying missing feature in web2py's DAL (and others orm's) is inability of use or adapt existent databases, without the boring redeclaring every field.

In a really "enterprise" environment, most of databases already exists, so most of development is around it.  

I think in create a kind of wizard application to facilitate this task.

It would use the following logic:

1.Define a connection
2. List all tables of database.
3. choose the databases to work
4. list all fields of choosen tables
5. auto-detect field relationships
6. manually define field relationships
7. check for necessary requirements for DAL and suggest solutions; the user choose the most appropriate solution and apply.
(for me, solutions are only: create or rename the necessary field, especially id, would be could if DAL could accept declare a existent autoincrement field as id, while this real name is not. A possible solution is Postgresql is create a view redeclaring the name the field = my_id as id.  )
8. DAL, as almost every orm, have a limited subset of fieldtypes, for compatibility for all databases. If a existent database have a non-standard DAL's fieldtype, is necessary define a solution how treat the data. So is necessary define a kind of conversion routine reading and writing for every non-standart fieldtype. The wizard would sugest some options or let the user define their own code.
9. Finally, everything checked, generates the db model, including migrate=False.


Something missing or any ideas?

2009/3/1 annet <jmve...@planet.nl>

--
Atenciosamente

--
=========================
Alexandre Andrade
Hipercenter.com

[Alexandre Andrade]

Correction:

I had the same problem, and don't have a good answer to why this occurs.

Could be something specifically a bug with Postgresql? The problem don't occur which sqlite.

Besides, I always think the most annoying missing feature in web2py's DAL (and others orm's) is inability of use or adapt existent databases, without the boring redeclaring every field.

In a really "enterprise" environment, most of databases already exists, so most of development is around it.  

I think in create a kind of wizard application to facilitate this task.

It would use the following logic:

1.Define a connection

2. List all databases
3. List all tables of database.
4. choose the tables to work
5. list all fields of choosen tables
6. auto-detect field relationships
7. manually define field relationships
8. check for necessary requirements for DAL and suggest solutions; the user choose the most appropriate solution and apply.

(for me, solutions are only: create or rename the necessary field, especially id, would be could if DAL could accept declare a existent autoincrement field as id, while this real name is not. A possible solution is Postgresql is create a view redeclaring the name the field = my_id as id.  )

9. DAL, as almost every orm, have a limited subset of fieldtypes, for compatibility for all databases. If a existent database have a non-standard DAL's fieldtype, is necessary define a solution how treat the data. So is necessary define a kind of conversion routine reading and writing for every non-standart fieldtype. The wizard would sugest some options or let the user define their own code.
10. Finally, everything checked, generates the db model, including migrate=False.

Something missing or any ideas?

2009/3/1 Alexandre Andrade <alexand...@gmail.com>

[mdipierro]

Hi Annet,

One type of error I can think of is change a in a field type.
postgresql may not be able to convert the data from old type (say a
string) to the new type (say a integer). I suggest always drop a
column first (remove the field) and then recreate it (add a new
field). Anyway, for various reason I would not suggest doing
migrations while in production because they may lock the database for
some time.

Massimo

[annet]

Alexandre and Massimo,

Thanks for your replies.

> Anyway, for various reason I would not suggest doing migrations while
> in production because they may lock the database for some time.

I am reading a bachelor in computer science so I am not a complete
novice when it comes to analysis and design, however, no matter how
well I designed my model, one day I will have to do migrations because
of changes in the domain (relevant world). How do I proceed in that
case?

What about the idea to put the database model in the init application

[mdipierro]

All I am saying is that if you need to do a migration, first take
web2py down, then run it on localhost only, they do the migration and
debug eventual errors. Do not change your model while your app is
being exposed to users.

There is nothing wrong in principle but there is always the
possibility of a typo or other minor error and you do not want to give
lots of tickets to your users.

Said this. I always change my models in production. We did it twice
with the PyCon registration site.

Massimo

[Yarko Tymciurak]

Annet -

We do migrations on live PyCon - and I do NOT recommend it; as Massimo points out the process you follow, and your awareness of what that means to a live site is crucial (we have so far only had one "problem" because of our bold and foolish process).

In general, I use rsync to take a snapshot of the live site models (both Massimo and I make changes, so important to keep these in sync) into a bzr repository.  Massimo makes an entire archive;  I try only the source and sql-logs etc.  (I didn't do the latter once, and it was scary!).

Then we make changes - as Massimo says - in a local environment;  not _all_ things are connected in a local environment (eg visa charges), so some care in setting up development versus deployment in the code-base is necessary.

Once the local works, it then is rsync'd  (less the devel switch) up to the bzr repository, and at an agreed on time (usually late, late at night when minimal activity on the server) a "all at once" rsync to the server and a quick check take care of it.

The really better idea is to have a development version of what you propose on your server, and make changes to that url; test; and then make a scheduled changover.

The point is all this requires care and planning.  The migrations are possible, and we have done them to a "live" system, and really never should have but managed to not get hurt by doing so.

web2py is very capable of the migrations, even if you're foolish enough to do them on a live system - but don't!   Plan your processes, watch the limitations of the underlying systems and test them;  plan your cut-over process.   web2py helps _a lot_ --- but if you expect _too_ much from it, you can get sloppy... and then you will be sorry.

Hope this view "behind the curtain" is helpful.

Yarko

[Yarko Tymciurak]

as I thought about what I'd written, I want to be sure to not mislead you with my web2py entusiasm:

• I think we did NOT actually migrate db tables after we went live...
  
• with two exceptions:
• I (much to Massimo's concern)  extended a table, added a boolean column (and had not problems;
• I added a table (which, during development with the Financial Aid manager - before anyone else used it - I effected DAL migrations a few times)

So that you are not mislead, it's not a good idea in a production environment in general (see my caveats above about testing on real server environment, and making planned cut-overs).

It does work, and I would not recommend it for 2 reasons:  it's not safe (if something goes wrong, you risk live data) - always design your migrate/merge process with fall back / recovery in mind (e.g. parallel test system which - when successful, you can cut-over to);  it has not seen enough production use, and different backends will behave differently.

Hope that puts a little more reality on it.

Regards,

Yarko