[Planner Dev] Re: [Planner] Database work plan

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

Alvaro del Castillo wrote:
> Hi guys!
>
> I have started this morning to work in the database backend in order to
> solve the issues we have. Once the database backend is in good shape we
> can start thinking in how to use it to implement things like sharing
> plans between users or using some web inteface to modify plan data.
> Also, we can restart the issue of adding support for other databases
> (MySQL seems to be the more loved ;-)).

I wouldn't say more loved - I'm bemused why its always seen as better
than Postgres but I think the reason is that it also runs on Windows.
Nuf said !.

The issue with mysql support is cursors and foreign keys. I think from
around mysql 5.0 onwards we may be OK (with InnoDB tables).


> The first thing is to have clear all the problems we have. I have tested
> right now the actual state of the backend in CVS and try to access
> bugs.gnome.org to see the bugs, but the service seems to be down right
> now.
>
> This email from Lincoln seems to be a very good report about the
> database status.

See also my bugzillas !.

> http://lists.imendio.com/pipermail/planner-dev/2004-March/000183.html
>
> and I think this issues aren't bugs yet:
>
> ------------------------
> 5) Other bugs (haven't done bugzillas yet);
>
> - No database maintenance - you can't delete a project.

Or upgrade etc., Here was my overkill display of what
I'd want with the "Manage Database" on the bottom left
and the "Manage Project" on the top right,
http://bugzilla.gnome.org/attachment.cgi?id=29036&action=view

.. yup, I got carried away with Glade.

> - Saving a project back onto the database adds a new entry
> 	but no other identification as to which is which.
> - The database load orders resource names differently
> 	from a file-open (at least on my machine).

We should ignore the order of load anyway and simply implement
the resource column sorting,
http://bugzilla.gnome.org/show_bug.cgi?id=138915

> - Opening up a project from a file and then changing a resource
> 	unit for an assignment updates gantt correctly
> 	but opening up a project from a database and then
> 	changing a resource unit value for an assignment
> 	doesn't update the gantt [?] value. It does once you
> 	change the assignment. My guess is a signal not being
> 	attached for notify::assignment in the parts that
> 	build up the project from a database.
> - changing an assignment unit value doesn't actually mark
> 	a project as changed when you do a File Close.
> ----------------------------
>
> The more critical bug I think it is that if you open a project from
> database and then save it to the database, it creates a new project in
> the database, and doesn't update the loaded one.
>
> Other issue I wil try to solve is the initial creation for the database.
> If the database scheme isn't in the database the user says, or if the
> database doesn't exists, we will try to create the database and create
> all the schemas for it, so the user doesn't need the initial database
> stuff setup. She only needs to have a user to access database that can
> create databases.

Attached (planner-acc1.png) is a diagram of what I was thinking about
inside the  Planner database for permissions. As far As I know postgres
grants only extend to the table and not to a row. As a row (or rows) is
a project in Planner we can't used postgres to restrict access to a
particular project but must create our own extra layer of user checks
which effectively add checks to projects.

Its not perfect if someone has database access rights but
its to help stop friendly fire not some dolt who has a
postgresql password and decides to access postgres from
an pgadmin style application to mangle the Planner database.

The "planner" table (it could have another name which you
manage via command line or stored in some gconf ) contains the
current DTD reference ( see bugzilla http://bugzilla.gnome.org/show_bug.cgi?id=136736 )
as well as a flag to say to use the users table and what the
table prefix is. See bugzilla,
http://bugzilla.gnome.org/show_bug.cgi?id=137547

I wanted Planner to read that for every database read/write
and then proceed on that basis.

I was thinking that a Project_access table which has unique
keys of a project and a planner_grps (groups). The planner_grp
has many members (planner_users) and they have unique
passwords.

A planner_users (user_id) can only be a member of one planner_grps
entry.

The permissions for a project are defined for each planner_grps
entry with defaults being assigned (copied from the planner_grps)
row.

The idea being that for a project you assign various groups
and each has certain rights unique to that project e.g.
I may have a SPAIN_USERS group and a UK_USERS group. A example
project (MADRID_DEMO) will have SPAIN_USERS having can-save
but UK_USERS will not have that flag set but would only have
read set.

That was my starting idea ;) I wasn't fully clear if I wanted
to have a user as a member of just one or of many groups. It
would be easy to do many in the database (make the primary key
from the user_id AND the grp_id and not just the user_id)

Any thoughts on all of this ?.

Rgds,
Lincoln.

> I propose that after talking about all the database issues, we report in
> the bugzilla all the work to be done and start working on it.
>
> Cheers
>
> -- Alvaro
>
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Planner mailing list
> Planner lists imendio com
> http://lists.imendio.com/mailman/listinfo/planner