[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Phpgroupware-developers] Follow Up: Questions from New User/Developer
From: |
Ashleigh Gordon |
Subject: |
[Phpgroupware-developers] Follow Up: Questions from New User/Developer |
Date: |
Fri, 22 Feb 2002 10:58:16 +1100 |
User-agent: |
Mozilla/5.0 (Windows; U; Win98; en-US; rv:0.9.8) Gecko/20020204 |
Thanks for quick help. From what I've found, it looks like postgresql
doesn't have a global parameter to have case insensitive searches. So
I've made a quick hack in the query string in
class.contacts_sql.inc.php, to make it case insensitive. Here it is:
/* Replace $sql .= " $f LIKE '%$query%' OR "; with */
$sql .= " $f LIKE '%$query%' OR $f LIKE lower('%$query%') OR $f
LIKE upper('%$query%') OR $f LIKE initcap('$query%') OR ";
This does makethe query rather large however, but it will do for now
I've also attached my update and insert triggers. In order to enable
triggers in postgresql, at the command prompt, type "createlang plpgsql
phpgroupware". I think that I should probably document this and some
other things to make it a bit easier for others. Who is currently
co-ordinating/working on the documentation? I think that I could
probably write the documentation for the addressbook now if you would like.
Ashleigh Gordon
/*When an organisations details are updated
this trigger will update them globally*/
drop function update_org_name();
CREATE FUNCTION update_org_name () RETURNS OPAQUE AS '
DECLARE
myInt int;
BEGIN
RAISE NOTICE ''update_org_name'';
select count(*)
into myInt
FROM phpgw_addressbook
WHERE org_name = NEW.org_name AND
adr_one_street = NEW.adr_one_street AND
adr_one_locality = NEW.adr_one_locality AND
adr_one_region = NEW.adr_one_region AND
adr_one_postalcode = NEW.adr_one_postalcode AND
adr_one_countryname = NEW.adr_one_countryname AND
adr_one_type = NEW.adr_one_type AND
label = NEW.label AND
url = NEW.url AND
pubkey = NEW.pubkey AND
note = NEW.note AND
adr_two_street = NEW.adr_one_street AND
adr_two_locality = NEW.adr_one_locality AND
adr_two_region = NEW.adr_one_region AND
adr_two_postalcode = NEW.adr_one_postalcode AND
adr_two_countryname = NEW.adr_one_countryname AND
adr_two_type = NEW.adr_one_type AND
org_unit = NEW.org_unit AND
tel_work = NEW.tel_work AND
tel_fax = NEW.tel_fax AND
tel_isdn = NEW.tel_isdn AND
tel_msg = NEW.tel_msg AND
cat_id = NEW.cat_id AND
email = NEW.email;
RAISE NOTICE ''Count is equal to %'', myInt;
IF myInt < 2 THEN
update phpgw_addressbook set
adr_one_street = NEW.adr_one_street,
adr_one_locality = NEW.adr_one_locality,
adr_one_region = NEW.adr_one_region,
adr_one_postalcode = NEW.adr_one_postalcode,
adr_one_countryname = NEW.adr_one_countryname,
adr_one_type = NEW.adr_one_type,
label = NEW.label,
url = NEW.url,
pubkey = NEW.pubkey,
note = NEW.note,
adr_two_street = NEW.adr_one_street,
adr_two_locality = NEW.adr_one_locality,
adr_two_region = NEW.adr_one_region,
adr_two_postalcode = NEW.adr_one_postalcode,
adr_two_countryname = NEW.adr_one_countryname,
adr_two_type = NEW.adr_one_type,
org_unit = NEW.org_unit,
tel_work = NEW.tel_work,
tel_fax = NEW.tel_fax,
tel_isdn = NEW.tel_isdn,
tel_msg = NEW.tel_msg,
cat_id = NEW.cat_id,
email = NEW.email
where org_name = NEW.org_name;
END IF;
RAISE NOTICE ''Details updated'';
return OLD;
END;
' LANGUAGE 'plpgsql';
drop trigger update_org_name ON phpgw_addressbook;
CREATE TRIGGER update_org_name AFTER UPDATE ON phpgw_addressbook
FOR EACH ROW EXECUTE PROCEDURE update_org_name();
/*When a new entry is made, this trigger will
populate the organisations details if it alreay exists*/
drop function insert_org_detail();
CREATE FUNCTION insert_org_detail () RETURNS OPAQUE AS '
DECLARE
myrec RECORD;
myInt int;
BEGIN
RAISE NOTICE ''insert_org_detail'';
select count(*)
into myInt
from phpgw_addressbook
where org_name = NEW.org_name;
RAISE NOTICE ''Count is equal to %'', myInt;
IF myInt > 1 THEN
RAISE NOTICE ''Inside IF statement'';
select *
into myrec
from phpgw_addressbook
where org_name = NEW.org_name AND
NEW.id != id;
RAISE NOTICE ''Myrec id is %'', myrec.id;
RAISE NOTICE ''New id is %'', NEW.id;
update phpgw_addressbook set
adr_one_street = myrec.adr_one_street,
adr_one_locality = myrec.adr_one_locality,
adr_one_region = myrec.adr_one_region,
adr_one_postalcode = myrec.adr_one_postalcode,
adr_one_countryname = myrec.adr_one_countryname,
adr_one_type = myrec.adr_one_type,
label = myrec.label,
url = myrec.url,
pubkey = myrec.pubkey,
note = myrec.note,
adr_two_street = myrec.adr_two_street,
adr_two_locality = myrec.adr_two_locality,
adr_two_region = myrec.adr_two_region,
adr_two_postalcode = myrec.adr_two_postalcode,
adr_two_countryname = myrec.adr_two_countryname,
adr_two_type = myrec.adr_two_type,
org_unit = myrec.org_unit,
tel_work = myrec.tel_work,
tel_fax = myrec.tel_fax,
tel_isdn = myrec.tel_isdn,
tel_msg = myrec.tel_msg,
cat_id = myrec.cat_id,
email = myrec.email
where id = NEW.id;
RAISE NOTICE ''Details entered in new contact'';
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
drop trigger insert_org_name ON phpgw_addressbook;
CREATE TRIGGER insert_org_name AFTER INSERT ON phpgw_addressbook
FOR EACH ROW EXECUTE PROCEDURE insert_org_detail();
- [Phpgroupware-developers] Follow Up: Questions from New User/Developer,
Ashleigh Gordon <=