On Wednesday 12 October 2005 08:59, Zac Ariel wrote:
I've been having a considerable deal of difficulty making the latest release of Roundcube play nice with PostgreSQL. After fixing up my own stuff ups with permissions (it kinda helps if the database user actually has permission to look at the table you want to select from ;-)), I found that postgres.initial.sql is missing the 'ip' column (from the 'session' table). Doing the below fixed this for me:
ALTER TABLE session ADD ip character varying(15) NOT NULL; ALTER TABLE session ALTER COLUMN ip SET DEFAULT ''::character varying;
However, it's still not working, and the current problem has got me stumped. I think it's due to the php being used, but my code-foo ain't that good. I receive this in the browser:
-----ooooo----- DB Error in /var/www/roundcubemail/program/include/rcube_db.inc (124): DB Error: unknown error DB Error in /var/www/roundcubemail/program/include/rcube_db.inc (210): DB Error: unknown error Query: SELECT vars, ip, UNIX_TIMESTAMP(changed) AS changed FROM session WHERE sess_id='74bfac980c44f3d2db46c4b0c49d22f0'
Fatal error: Call to a member function on a non-object in /var/www/roundcubemail/program/include/rcube_db.inc on line 202 -----ooooo-----
.and in the Postgres log I get this:
-----ooooo----- 2005-10-12 16:49:16 [15138] ERROR: function unix_timestamp(timestamp with time zone) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. -----ooooo-----
Um ... any ideas? Google hasn't been especially useful, save referring to PHP information that I don't pretend to understand. What other information can I provide that would be useful?
hum ... there is no unix_timestamp() function in PostgreSQL. I don't really look what kind of value is $changed ... but if it's a classical timestamp format like YYYY-MM-DD HH-MI ... you can make a PostgreSQL function like this :
CREATE OR REPLACE FUNCTION unix_timestamp(timestamp with time zone) RETURNS integer AS $$ DECLARE my_date ALIAS FOR $1; my_timestamp RECORD; BEGIN SELECT EXTRACT(EPOCH FROM my_date::timestamp with time zone)::integer as tms INTO my_timestamp; RETURN my_timestamp.tms; END; $$ LANGUAGE plpgsql;
With this a simple select like : select unix_timestamp(now());
will return :
unix_timestamp
1129104749
So in your case $changed could be in different formats like : select unix_timestamp('2005-12-10'); select unix_timestamp('2005-12-10 10:08:09');
But I don't know what exactly is $changed ... if it's alreay an unix timestamp made in PHP ... you could do a simpler function to avoid this like :
CREATE OR REPLACE FUNCTION unix_timestamp(integer) RETURNS integer AS $$ DECLARE my_date ALIAS FOR $1; BEGIN RETURN my_date; END; $$ LANGUAGE plpgsql;
select unix_timestamp(1134205689); unix_timestamp
1134205689
Just to simulate the MySQL call ...
Hope this help ...
Hervé Piedvache
NOUVELLE ADRESSE - NEW ADDRESS : Elma Ingénierie Informatique 3 rue d'Uzès F-75002 - Paris - France Pho. 33-144949901 Fax. 33-144882747