[Phpgroupware-developers] Database buglist (fwd)

[ceb]

i dont see a reason to send this mail just to a few of us...

---------- Forwarded message ----------
Date: Mon, 28 Apr 2003 10:56:10 +0200
From: Kai Hofmann <address@hidden>
To: "'address@hidden'" <address@hidden>,
     "'address@hidden'" <address@hidden>,
     "'address@hidden'" <address@hidden>,
     "'address@hidden'" <address@hidden>
Cc: team-applications <address@hidden>
Subject: Database buglist

Hello,

as mentioned on the developer list here follows the buglist of problems with
the database that I found
during reverse engineering. If you agree I can/will also post this on the
developer list, but for now
we thing this should first discussed internally.
I have no email address from jengo and skeeter - so please feel free to
forward this mail to them as well as
to others for which this might be important.

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

TimeTrack

At first in the timetrack module file hourslist.php there are join
references to a column ab_id in the addressbook table
- the coloumn ab_id does NOT exist in this table.  maybe the "id" column is
the correct one?

     . "left join phpgw_addressbook as a on j.contact_id = a.ab_id "
     . "left join phpgw_addressbook as a on j.contact_id = a.ab_id "
     . "left join phpgw_addressbook as a on j.contact_id = a.ab_id "
./timetrack/hourslist.php

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

Admin

within the admin module there are two different relations between phpgw_log
and phpgw_accounts.
An if statement decides between the two variants.
This is absolutly evil! - only one column should be used for relations
between tables!

                                $parms['where'] = array('phpgw_log.log_user
= phpgw_accounts.account_id');
                                $parms['where'][] = 'phpgw_log.log_id =
phpgw_accounts.account_id';
./admin/inc/class.solog.inc.php

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

Registration

The SQL statement I found here is wrong - because in SQL

phpgw_addressbook.lid='*$account_lid*'

is not what you want - correct it must be:

phpgw_addressbook.lid like '%$account_lid%'

last but not least referencing two tables in this way is absolutly evil!
primary and foreign keys should always
be of the same type without a pattern matching!

                        $GLOBALS['phpgw']->db->query("select * from
phpgw_accounts, phpgw_addressbook where account_lid='$account_lid' and
phpgw_addressbook.lid='*$account_lid*'",__LINE__,__FILE__);
./registration/inc/class.soreg.inc.php

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

Property

Within the property module database part there are tables and coloumns that
are not named in english language -
this should be avoided!

There is NO template_id coloumn within the fm_wo_template_hours table:

  fm_wo_template.id=fm_wo_template_hours.template_id
./property/inc/class.sotemplate.inc.php

---

Within fm_wo_wrkorders there are NO coloumns property_id and apartment_id :

  $sql= "fm_apartment,fm_wo_wrkorders where fm_apartment.property_id  =
fm_wo_wrkorders.property_id and fm_apartment.apartment_id =
fm_wo_wrkorders.apartment_id
./property/inc/class.soXport.inc.php

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

Projects

This join statement is wrong - there is no connection between the two tables
- should be:
"phpgw_p_projects.id = join phpgw_p_hours.project_id WHERE "

                        $this->db->query("SELECT
title,num,phpgw_p_projects.id as id FROM phpgw_p_projects $join
phpgw_p_hours ON "
                                                        .
"phpgw_p_hours.employee='" . $account_id . "' GROUP BY
title,num,phpgw_p_projects.id",__LINE__,__FILE__);
./projects/inc/class.sostatistics.inc.php


---

Withinthe following sql statement
"phpgw_p_hours.activity_id=phpgw_p_projectactivities.activity_id" should be
replaced with
"phpgw_p_activities.id=phpgw_p_projectactivities.activity_id":

        $this->db->query("SELECT phpgw_p_hours.id as
id,phpgw_p_hours.hours_descr,phpgw_p_activities.descr,phpgw_p_hours.status,
"
                                                .
"phpgw_p_hours.start_date,phpgw_p_hours.end_date,phpgw_p_hours.minutes,phpgw
_p_hours.minperae,phpgw_p_hours.billperae,"
                                                . "phpgw_p_hours.employee
FROM phpgw_p_hours " . $this->return_join() . " phpgw_p_activities ON "
                                                .
"phpgw_p_hours.activity_id=phpgw_p_activities.id " . $this->return_join() .
" phpgw_p_projectactivities ON "
                                                .
"phpgw_p_hours.activity_id=phpgw_p_projectactivities.activity_id WHERE
(phpgw_p_hours.status='done' OR "
                                                .
"phpgw_p_hours.status='closed') AND (phpgw_p_hours.project_id='" .
$project_id . "'" . $parent_hours . ") AND "
                                                .
"phpgw_p_projectactivities.project_id='" . $project_id . "' AND
phpgw_p_projectactivities.billable='Y' "
                                                . "AND
phpgw_p_projectactivities.activity_id=phpgw_p_hours.activity_id " .
$ordermethod,__LINE__,__FILE__);
./projects/inc/class.sobilling.inc.php

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

The phpgw_weather_images table does NOT exist:

                ."phpgw_weather_images.links_id=phpgw_weather_links.links_id
"
./weather/inc/links.inc.php


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

Bookkeeping

Within this module there are a lot of "old" (?) table names that are not
within the database - as it seems they all are
converted to short 1 or 2 character labels:


NO tables: phpgw_bk_categories, phpgw_bk_entries, phpgw_bk_acl, phpgw_bk_gu
and maybe others!!!



 $phpgw->db->query("SELECT * FROM phpgw_bk_categories WHERE
income_expense='$income_expense' order by $order");
./bookkeeping/categories.php

     $sql="insert into phpgw_bk_categories (description,income_expense)
values ('$description','$income_expense')";
/bookkeeping/category_add.php

      $sql="delete from phpgw_bk_categories where category_id=$category_id";
    $phpgw->db->query("select description from phpgw_bk_categories WHERE
category_id='$category_id'");
./bookkeeping/category_delete.php

    $sql="update phpgw_bk_categories set description='$description' where
category_id=$category_id";
    $sql="select description from phpgw_bk_categories where
category_id=$category_id";
./bookkeeping/category_edit.php

    $sql="select category_id,description from phpgw_bk_categories where
income_expense='$income_expense' order by description asc";
./bookkeeping/entry_add.php

                      "from phpgw_bk_entries e, phpgw_bk_categories c WHERE
entry_id='$entry_id' AND e.category_id=c.category_id");
./bookkeeping/entry_delete.php

    $sql2="select category_id,description from phpgw_bk_categories where
income_expense='$income_expense' order by description asc";
./bookkeeping/entry_edit.php

                    "from phpgw_bk_entries e, phpgw_bk_categories c WHERE
entry_id='$entry_id' AND e.category_id=c.category_id");
./bookkeeping/entry_view.php

    $sql="select category_id,description from phpgw_bk_categories where
income_expense='$income_expense' order by description asc";
    $sql="select category_id,description from phpgw_bk_categories where
income_expense='$income_expense' order by description asc";
./bookkeeping/statistics.php

      $sql2="select sum(e.amount) as amount, c.description as description
from phpgw_bk_entries e, phpgw_bk_categories c ".
./bookkeeping/reports/reports.php

                $sql = "SELECT module FROM phpgw_bk_acl WHERE
bookkeeping_id='" . $phpgw_info['bookkeeping']['id'] . "' GROUP BY module";
                $sql = "SELECT a.bookkeeping_id FROM phpgw_bk_acl a,
phpgw_bk_gu g WHERE a.group_id = g.group_id AND g.user_id='" .
$phpgw_info['user']['id'] . "' GROUP BY a.module";
./bookkeeping/inc/class.bookkeeping.inc.php

                $sql = "SELECT a.bookkeeping_id FROM phpgw_bk_acl a,
phpgw_bk_gu g WHERE a.group_id = g.group_id AND g.user_id='" .
$phpgw_info['user']['id'] . "' GROUP BY a.module";
./bookkeeping/inc/class.bookkeeping.inc.php

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

News Admin

using the accounts table here is useless because there is no connection
between these two tables (missing where) - so the join produces a lot of
overhead!!!

            $db->query("select * from phpgw_news,accounts where
news_status='Active' order by news_date "
./news_admin/website/export.php

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

MediaDB

The mediadb module looks like it has the same problems than the bookkeeping
module (renamed table names) because a grep on the table names
found in the database shows up nearly no SQL statements!!!!!

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

INV

within the INV module there are two "primary" keys within the
phpgw_inv_products table named con and id - this is again absolutly evil
because they are used both in different select statements.

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

Netsaint

during installing netsaint I got the following error message:

Fatal error: Call to a member function on a non-object in
./netsaint/setup/default_records.inc.php on line 13

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

Last but not least please let me say that the whole database is badly
designed and to much work on the data is done in the API.
This will slow down the application!

Also other things that will slow down phpGW on the database level are
missing(!!) indexes on "foreign keys" and other often used coloumns!!!

Another bad thing are relations between tables based on (var)char coloumns -
this will also slow down the thing!

Last but not least it seems that to much data is doubled because each module
invents the wheel again! This should be avoided.


Please let me know how we will go further on these things.


Greetings

   Kai Hofmann


P.S.: I am just working on a SQL style guide - that might help improve the
database for the case that you will accept it.


-- 
*****    Open Source und Linux im professionellen Einsatz    *****
**  komplexe Mailserver, Groupware, Office: sprechen Sie uns an **
Dipl.-Inform. Kai Hofmann                    Team Softwarelösungen
pro|business AG, EXPO Plaza 1 (Deutscher Pavillon), 30539 Hannover
E-Mail: address@hidden,   Tel.: 0511/60066-332, Fax: -355
WWW: http://www.probusiness.de/