Re: Database Support - Conversion to Sqlite

[Chris Peachment <cpeachment ononbb com>]

Hi Maurice and Lincoln:

In the big scheme (schema?) of things, project management
is a subset of Enterprise Resource Planning (ERP). Each
task requires resources in the form of material and labour
effort for its completion.

The group for whom I work builds houses and apartments.
The project-task focus is self-evident and is supplemented
by material purchase and delivery scheduling issues. These
are not handled by a standalone PM system but there is
overlap in the data used. Project Management techniques
are an application of Master Production Scheduling (MPS)
specific to project rather than repetitive manufacture
and the resulting schedule can be the input of further
data processing.

Material Requirements Planning (MRP) starts with the order
(e.g. task or component of the Work Breakdown Structure)
and uses a Bill of Materials (BOM) to indicate all the
component material requirements in a time phased manner.
Since these items can be costed, we can get a better idea
of cash flow requirements and might alter the schedule to
fit the available cash. This is a manual form of load
levelling.

It is for this reason that I intend to use the Planner data
definition as part of a larger system. I cannot foresee the
complete system at this time but I can anticipate that the
generic labels in the Planner subset might cause problems
and/or confusion in the future.

Sql databases use an interpreter to parse the sql statement
and generate the needed actions upon the database. The
Structured Query Language (SQL) has reserved words that
cause syntax errors if they are out of sequence in the
statement. Textual data values require (single) quotation
marks to isolate the text contents from the interpreter.
So quoting textual data values has no effect on the use of
table and field names located at other points in the sql
statement.

As far as I know, backtick quoting for table and field
names is not standard sql but specific to MySql and was
adopted by Sqlite for compatibility. Apparently, PostGres
does not use them. ANSI standard sql requires that field
names which are also reserved words must be double quoted.
As you can see, the variability in database systems
implies this feature should be avoided if possible. That
means defining application specific table and field names.
This is often done with a three character prefix to the
identifier, but the length is arbitrary. Three letter
acronyms (TLA) might be hard wired into the human brain.

I think there is NO backward compatible method of fixing
this problem across various database products. Since the
use of sql by Planner is in its early days, this is the
time to fix it.


On the subject of the Priority field, I have rechecked the
0.11 and 0.13 schema files and discovered that it was
defined in both. Sorry for the misleading oversight.


Regards,

Chris

--------------------

On Sat, 2008-11-01 at 16:04 +0100, Lincoln Phipps wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> 
> 
> Maurice van der Pot wrote:
> > Hi Chris,
> > 
> > I don't know much about databases, so forgive me if my remarks do not
> > make sense. =)
> > 
> > 
> > On Wed, Oct 29, 2008 at 04:22:19PM -0400, Chris Peachment wrote:
> >> I am using Sqlite in place of PostGresSql
> >> for a variety of reasons not relevant here.
> >>
> >> Converting the version 0.14.3 database
> >> schema is relatively straightforward but it
> >> reveals a number of identifiers that might
> >> be reserved words in some database products
> >> and thus require back tick quoting or cause
> >> syntax errors.
> >>
> >> Specifically:
> >>
> >> -- day   as table name
> >> -- date  as field in day table
> >> -- start as field in task table
> >> -- work  as field in task table
> >> -- type  as field in predecessor table
> >> -- value as field in property table
> > 
> > Any backwards-compatible changes we should just do right away. I still
> > have a patch in the works to remove support for libgda < 3 and add
> > proper quoting of data. Are you saying this cannot always be solved by
> > quoting? Tell me more.
> > 
> >> In addition, the task priority field is
> >> supplied in the XML .planner file but is not
> >> in the database schema.
> > 
> > Can this be added in a backwards-compatible way? If so, can you provide
> > a patch?
> 
> I had provided the patch for the priority field back in 2004 when I had patched
> the code to use the priority field for the task see...
> 
> http://bugzilla.gnome.org/show_bug.cgi?id=137544
>  or the attachment...
> 
> http://bugzilla.gnome.org/attachment.cgi?id=26383
> 
> AFAIK we did increment the schema to a new version 0.11 to include
> the priority field.
> 
> Note that the priority field has had the intent to have the range
> as 0,1 ...9999
> 
> > 
> >> As a more general comment, I note that the
> >> names chosen for both tables and fields are
> >> often generic and at risk of name space
> >> collision when the Planner tables are
> >> integrated with other tables. Their generic
> >> nature might also be misleading in a bigger
> >> schema where other parts of the database
> >> has similar content.
> > 
> > Isn't the planner database the namespace? Why would any non-planner data
> > end up in the planner database?
> 
> 
> We always wanted to have table name prefixes just like every other
> nicely behaving database application.
> .....
> 
> 
> 
> Regards,
> Lincoln
> (Still lurking)