Ciaran,
I see that you've used a quoted "user" to get around the SQL keyword
restriction for creating that database.
Does the +#$config['db']['quote_identifiers'] = true; handle the differences in
quoting the boolean datatypes?
Also be aware that with the advent of PostgreSQL 8.3 a lot of auto-casting for
data comparisons is dropped. So with 8.3 you have to be a lot more careful
about making sure everything is the right datatype. (e.g. I had to rewrite the
tag calculation code because of this.)
I think you'll also run in to troubles with your serial datatypes. At the
beginning I had issues with the code looking at two different serial sequences.
The Postgres database would create one index automatically and the
DB_DataObject would be looking for a sequence index with the name to be
provided by MySQL. So I created sequences with the MySQL names and then
switched the serial datatypes to default nextval('mysqlname_seq') Not sure if
this is still true, but something to keep an eye on.
As for full text indexing, the contrib/tsearch2 has been brought in to the core
with 8.3.
I add a column to profile "textsearch tsvector", then add 2 indexes:
create index textsearch_idx on profile using gin(textsearch);
create index noticecontent_idx on notice using gin(to_tsvector('english',content
));
(i defaulted to 'english' for testing and this was originally done before all
of the multi-language items were inserted)
and finally I add a trigger on profile to update "textsearch"
create trigger textsearchupdate before insert or update on profile for each row
execute procedure tsvector_update_trigger(textsearch, 'pg_catalog.english',
nickname, fullname, location, bio, homepage);
and in peoplesearch.php I modify the query
$profile->whereAdd('MATCH(nickname, fullname, location, bio, hom
epage) ' . 'against (\''.addslashes($q).'\')');
to
$profile->whereAdd('textsearch @@ plainto_tsquery(\''.addslashes($q).'\')');
and in noticesearch.php I modify the query
$notice->whereAdd('MATCH(content) against (\''.addslashes($q).'\
')');
to
$notice->whereAdd('to_tsvector(\'english\', content) @@ plainto_tsquery(\''.adds
lashes($q).'\')');
Thomas Legg
----- Original Message ----
From: Ciaran Gultnieks <[EMAIL PROTECTED]>
To: [email protected]
Sent: Tuesday, September 9, 2008 10:37:43 PM
Subject: [Laconica-dev] PostgreSQL Support
I've just committed an initial version of PostgreSQL support to my
repository.
Some work on this was done against an earlier version of Laconica by
Thomas Legg - see
http://www.the-eleven.com/tlegg/index.php?/archives/46-PostgreSQL-backed-Laconica.html
(although the site is down at the time of writing). However, I have
started from scratch because: a) I wanted to know exactly what changes
were involved and why, b) the database and code have changed
significantly since then, and c) that version involved changing table
names and data types, while I wanted to make as few changes as possible
to keep things compatible and maintainable going forward.
I have a test installation up and running and so far everything seems
good except for the fulltext search which I haven't attempted to convert
yet. More testing is necessary, so if anyone would like to help out you
just need the current darcs version of laconica along with this patch:
<http://darcs.ciarang.com/darcsweb.cgi?r=laconica;a=commit;h=20080909072224-f6e2c-881bd2e7fb032f336fecae9ca5ce527dd821d3dc.gz>
Instructions for setup are as normal except:
1. Create the database using "create dbname with encoding 'utf8'"
2. Use the db/laconica_pg.sql as the create script
3. In config.php, set ['db']['quote_identifiers'] = true and
['db']['type'] = 'pgsql'
4. Set the database connection string accordingly, i.e. pgsql:// instead
of mysql://
I'm using PostgreSQL 8.1, which I think is probably the minimum
requirement (at least for the way I've done things).
Cheers,
Ciaran
_______________________________________________
Laconica-dev mailing list
[email protected]
http://mail.laconi.ca/mailman/listinfo/laconica-dev
_______________________________________________
Laconica-dev mailing list
[email protected]
http://mail.laconi.ca/mailman/listinfo/laconica-dev
