Re: [Phpgroupware-developers] SQL garbage in contacts backend

[Brian Johnson]

I don't see any problem with the majority (all but the big one at the end) of
the SQL examples you posted

The concat((CASE WHEN contact_addr.add1 IS NULL THEN '' ELSE contact_addr.add1
END) is necessary since concat with a null produces a NULL and the CASE system
is the only method that works in all the supported database to check if it is
a null value or not

You will find that the sql builder also uses a different concat command
depending on what db you are using since there is not one common function on
all that databases that produces the same results



Markus Kaemmerer (address@hidden) wrote:
>
> Hi,
>
> while working on synchonization support we found, that sync of 300
> Items from my PocketPC (connected through USB) to my local phpGW
> installation takes about 30 minutes (the same with ActiveSync and
> Outlook takes about 30 seconds).
>
> One reason for this is, that the contacts backend. During the tests we
> found, that there are the indizis missing on nearly all tables. An
> later I found that for one vCard export there the following SQL
> statements are executed.
>
> The first 5 SQL statements are fetching the WHOLE table and the last
> 3500 bytes SQL statement is nealy self describing :-).  In this
> statement there are things like: "(CASE WHEN ' ' IS NULL THEN '' ELSE
> ' ' END)". The SQL builder should suppress such things.
>
> These things sould be fixed before phpGW can be used in larger
> environments or with synchronizing.
>
> ---
>
> SELECT contact_comm_descr.comm_descr_id AS comm_descr_id,
> contact_comm_descr.descr AS comm_description,
> contact_comm_descr.comm_type_id AS comm_type FROM
> phpgw_contact_comm_descr AS contact_comm_descr
>
> SELECT contact_comm_type.comm_type_id AS comm_type_id,
> contact_comm_type.type AS comm_type_description,
> contact_comm_type.active AS comm_active, contact_comm_type.class AS
> comm_class FROM phpgw_contact_comm_type AS contact_comm_type
>
> SELECT contact_addr_type.addr_type_id AS addr_type_id,
> contact_addr_type.description AS addr_description FROM
> phpgw_contact_addr_type AS contact_addr_type
>
> SELECT contact_note_type.note_type_id AS note_type_id,
> contact_note_type.description AS note_description FROM
> phpgw_contact_note_type AS contact_note_type
>
> SELECT contact_types.contact_type_id AS contact_type_id,
> contact_types.contact_type_descr AS contact_type_descr,
> contact_types.contact_type_table AS contact_type_table FROM
> phpgw_contact_types AS contact_types
>
> SELECT contact_central.owner AS owner FROM phpgw_contact AS
> contact_central WHERE ((contact_central.contact_id = 233))
>
> SELECT contact_central.contact_id AS contact_id,
> contact_central.access AS access, contact_central.owner AS owner,
> concat((CASE WHEN first_name IS NULL THEN '' ELSE first_name END),
> (CASE WHEN ' ' IS NULL THEN '' ELSE ' ' END), (CASE WHEN middle_name
> IS NULL THEN '' ELSE middle_name END), (CASE WHEN ' ' IS NULL THEN ''
> ELSE ' ' END), (CASE WHEN last_name IS NULL THEN '' ELSE last_name
> END)) AS per_full_name, contact_person.first_name AS per_first_name,
> contact_person.middle_name AS per_middle_name,
> contact_person.last_name AS per_last_name, contact_person.suffix AS
> per_suffix, contact_person.prefix AS per_prefix,
> contact_person.birthday AS per_birthday, contact_person.pubkey AS
> per_pubkey, contact_person.title AS per_title,
> contact_person.department AS per_department, contact_person.initials
> AS per_initials, contact_person.sound AS per_sound,
> contact_person.active AS per_active, contact_person.modified_by AS
> per_modby, contact_person.modified_on AS per_modon, contact_org.name
> AS org_name, contact_addr.contact_addr_id AS key_addr_id,
> contact_addr.add1 AS addr_add1, contact_addr.add2 AS addr_add2,
> contact_addr.add3 AS addr_add3, concat((CASE WHEN contact_addr.add1 IS
> NULL THEN '' ELSE contact_addr.add1 END), (CASE WHEN contact_addr.add2
> IS NULL THEN '' ELSE contact_addr.add2 END), (CASE WHEN
> contact_addr.add3 IS NULL THEN '' ELSE contact_addr.add3 END)) AS
> addr_address, contact_addr.postal_code AS addr_postal_code,
> contact_addr.city AS addr_city, contact_addr.state AS addr_state,
> contact_addr.country AS addr_country, contact_addr.preferred AS
> addr_preferred, contact_addr_type.description AS addr_description,
> contact_comm_descr.descr AS comm_description, contact_comm_type.type
> AS comm_type_description, contact_comm.comm_data AS comm_data,
> contact_note.contact_note_id AS key_note_id, contact_note.note_text AS
> note_text, contact_note_type.description AS note_description,
> contact_others.other_value AS other_value, contact_others.other_name
> AS other_name FROM phpgw_contact AS contact_central LEFT  JOIN
> phpgw_contact_note AS contact_note ON contact_note.contact_id =
> contact_central.contact_id LEFT  JOIN phpgw_contact_note_type AS
> contact_note_type ON contact_note_type.note_type_id =
> contact_note.note_type_id LEFT  JOIN phpgw_contact_addr AS
> contact_addr ON contact_addr.contact_id = contact_central.contact_id
> RIGHT  JOIN phpgw_contact_addr_type AS contact_addr_type ON
> contact_addr_type.addr_type_id = contact_addr.addr_type_id LEFT  JOIN
> phpgw_contact_comm AS contact_comm ON contact_comm.contact_id =
> contact_central.contact_id LEFT  JOIN phpgw_contact_comm_descr AS
> contact_comm_descr ON contact_comm_descr.comm_descr_id =
> contact_comm.comm_descr_id LEFT  JOIN phpgw_contact_comm_type AS
> contact_comm_type ON contact_comm_type.comm_type_id =
> contact_comm_descr.comm_type_id LEFT  JOIN phpgw_contact_others AS
> contact_others ON contact_others.contact_id =
> contact_central.contact_id INNER  JOIN phpgw_contact_person AS
> contact_person ON contact_person.person_id =
> contact_central.contact_id LEFT  JOIN phpgw_contact_org_person AS
> contact_org_person ON contact_org_person.person_id =
> contact_person.person_id LEFT  JOIN phpgw_contact_org AS contact_org
> ON contact_org.org_id = contact_org_person.org_id WHERE
> contact_central.contact_id = 233
>
>
>
>
> Markus
>
>
> **********************************************
>            address@hidden 2004
>
> *     Halle 1, Stand 3k4 (Magirus Deutschland GmbH)
> *     Halle 1, Stand 7f2 (EMC Deutschland GmbH)
> *     Halle 6, Stand D46 (Land Niedersachsen)
> **********************************************
>
>
> _______________________________________________
> Phpgroupware-developers mailing list
> address@hidden
> http://mail.gnu.org/mailman/listinfo/phpgroupware-developers
>