Re: [Planner Dev] Database schemas, versions and upgrading paths

["lincoln phipps openmutual net" <lincoln phipps openmutual net>]

Alvaro del Castillo wrote:

> Hi!
>
>
>
> > This is where you rent a server and you pay per postgres database
> > or the case where your company gives you one database. What you
> > need to do is therefore prefix each table so that it doesn't clash
> > with existing applications that you already have hosted inside that
> > one database.
> >
> > To stop this possibility of a clash you prefix the table name
> > with something. Most/all database applications like the various
> > 'nukes can do this.
> > ......
>
>
> Ah, ok, I have worked in slash project for 2 years and I understand the
> problem. So when the user access the first time the database stuff, she
> will be asked for a username, password and host.
>
> If the user get access to the database server, she won't have a database
> yet. So the first thing the code will do is to create a database for her
> projects. And this is the moment where name clashes could appear. A
> solution could be to ask the user for the database name and if the name
> is being used, ask her for another name. What do you think?

Alvaro ,

here is my suggested workflow for starters.....

Step 1 ) Check if the database exists. If NO then go to step 6) If YES
then go to Step 2).

Step 2) If the database exists then the user needs to be prompted do
they want to use this same database.

If YES then the sql needs to try to get the base table I'd suggested we
call this "planner" by default or it could be anything (maybe changed
at command line like this...

	planner --basetable=planner

If that exists then retieve its contents and get the current
tables_layout version and table prefix and go to step 3).

If the base table doesn't exist then offer to add the new Planner layout
inside this existing database. If Yes then go to Step 7).
If NO then exit(WONT USE DATABASE).

Step 3). If the table_layout is old then offer to upgrade. If Yes then go
to Step 4). If NO then exit(NOT UPGRADED).

If tables_layout is current then go to Step 5).

Step 4) Upgrade the tables as per the current rules that we're planning
based on the old and new versions. Go to step 5)

Step 5) Exit (UPGRADE OK).

Step 6) If here then create the new postgres database then go to step 7).

Step 7) Add the base table and the planner tables who's names are prefixed
with the table prefix e.g. "planner_project" as opposed to just
simply "project". Set the planner -> tables_layout to be the current layout
e.g. 0.11 or whatever we use. Go to to step 8).

Step 8 ) Exit (INSTALL OK).


> I am codign right now this logic. For the moment, when the sql storage
> module is loaded:
>
> 1. See if database exists and the version the user used to save the data
> in the database. For example:
>
> - Jane has a database "planner" with last version used 0.11 and several
> projects.
>
> - Jane the upgrades planner for 1.0 version and starts planner.
>
> - planner database code checks database status and see that the projects
> were saved using 0.11 and planner is now in 1.0.
>
> - planner will try to find a sql file: upgrade-0.11-1.0.sql
>
> - if planner doesn't file this file it hopes that no upgrade is needed
> for the database.
>
> - if planner find it the load the sql file and executes it in the
> databse.
>
> Maybe, I will code some logic like it planner finds upgrade-0.12-1.0.sql
> but no upgrade-0.11-1.0.sql it will emit a warn because no upgrade is
> present for 0.11 but is present one for a newer version (0.12).
>
> 2. If no database exist we can ask the user for a name for the database
> and create it for her.
>
>
> Something I am missing?
>
> Cheers
>
> -- Alvaro
>
>
>
>
> > > > But that'll come later - right now if we can simply create or
> > > > delete a database and upgrade an existing database via Planner
> > > > then that would be a great step forward for people.
> > >
> > >
> > > This is my first target. Really the first thing is to create the tables
> > > without user careing about it.
> >
> > Yup, cool, thats what we're after !.
> >
> >
> > > Cheers
> > >
> > > -- Alvaro
> >
> > _______________________________________________
> > Planner-dev mailing list
> > Planner-dev lists imendio com
> > http://lists.imendio.com/mailman/listinfo/planner-dev
>
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Planner-dev mailing list
> Planner-dev lists imendio com
> http://lists.imendio.com/mailman/listinfo/planner-dev