Re: [Planner Dev] Planner database version

[Jani tiainen <redetin luukku com>]

Alvaro del Castillo wrote:
> Hi!
>
> The database create/upgrade patch only misses actually a way to know the
> version for planner database. Thinking about how we can save in database
> the version (needed for the upgrade system), I finally think that the
> best thing we can do is to store it in "property" table as a "text"
> property.
>
> property records are related to projects in table "project_to_property"
> so it could be clear that if a property isn't in project_to_property, it
> is a global property for all planner projects, like the database
> version.
>
> But we can't do it that way because properties types are related to
> projects. So if we want to use a property to store the database version,
> we need a pseudo-project in which to store the global properties for
> planner. I am not sure how "hackish" it sounds to you. For me yes, it is
> a bit hackish but the other solution will be to create a separate table:
>
> CREATE TABLE property_global (
>        	prop_id    	  serial,
> 	prop_name	  text NOT NULL,
> 	value		  text,
> 	PRIMARY KEY (prop_id)
> ); 

Well I've used (in commercial products) similar solution (altough 
datatypes in creation seems not to be standard SQL so it must be 
something speciall for db engine in question)

Only thing I would add is category, and order, it then would enable 
ordered lists and such...

so it would be something like this (at least in nearly in standard SQL 
format, not sure because I use Oracle that has more efficient, 
non-standard types available):

CREATE TABLE property_global (
	prop_id		decimal(10) NOT NULL,
	prop_name	varchar(100) NOT NULL,
	prop_category	varchar(30) NOT NULL,
	prop_order	decimal(3),
	prop_value	varchar(100),
	PRIMARY_KEY (prop_id)
);

so you can do "SELECT prop_id, prop_name from propterty_global where 
prop_category = 'FOOBAR' ORDER BY prop_order";

-- Jani Tiainen