[phpslash-users] Porting to postgres

[Zot O'C. <zo...@zo...>]

Well I have a site (to test something) working under postgres.

Here were the issues in porting (I will add as I remember)

sql file:

Big chnages are 

KEY -> UNIQUE (for some reason key would not work)

Comments are '--' not #


Datetime is a pure mysql function.  I managed by writing a postgres
function in perl.  This requires super user privs.....

If we could move datetime to php and use a timestamp, that would go
away.

int(11) is not allowed.  Making it int would solve it both ways.

timestamp(14) is not allowed. Making it timestamp would work.
[note I dislike that MySQL has formatting in the DB.  That is pretty
much a un-RDBMS think to do]

I had to create triggers on the timestamp fields for 

psl_block:
last_update timestamp(14),

became
last_update timestamp DEFAULT now(),

And I added 
CREATE FUNCTION update_timestamp_psl_block() RETURNS OPAQUE AS'
  BEGIN
    NEW.last_update := now();
    RETURN NEW;
  END;
  ' LANGUAGE 'plpgsql';


CREATE TRIGGER psl_block_trigger BEFORE UPDATE
         ON psl_block FOR EACH ROW
         EXECUTE PROCEDURE update_timestamp_psl_block();


the lanuage must be loaded (should be always).  I could probably do that
in any language.

The insert of times had to change, timestamp in postgres is a big int
like 20001204173038.


>   KEY id (id),
>   UNIQUE id_2 (id,name)
became
<   UNIQUE (id),
<   UNIQUE (id,name)

Unique in postgres does not take a name....

Key goes after the column headers.

tinyiny(1) became smallint

timestamp function (incomplete)

CREATE FUNCTION unix_timestamp (timestamp) RETURNS float8 AS
'SELECT date_part(\'epoch\', $1) AS RESULT;' LANGUAGE 'sql';

CREATE FUNCTION from_unixtimestamp (float8) RETURNS timestamp AS 'SELECT
timestamp ($1) AS RESULT;' LANGUAGE 'sql';
CREATE FUNCTION from_unixtimestamp (int4) RETURNS timestamp AS 'SELECT
timestamp ($1) AS RESULT;' LANGUAGE 'sql';
CREATE FUNCTION from_unixtime (int4) RETURNS timestamp AS 'SELECT
timestamp ($1) AS RESULT;' LANGUAGE 'sql';

-- date_format(time,\"%W %M %d, \@ %h:%i%p\")
--date_format(time,\"%e\")
-- date_format(time,\"%d-%b-%Y\") 
-- date_format(story.time,\"%W, %M %d \@%h:%m\")

CREATE FUNCTION date_format (timestamp, text) RETURNS text AS '
  SELECT to_char($1, my_to_pg_dateformat($2)) AS RESULT;' LANGUAGE
'sql';


CREATE FUNCTION my_to_pg_dateformat (text) RETURNS text AS '
   $b = shift;

   $b =~ s/%W/Day/;
   $b =~ s/%M/Month/;
   $b =~ s/%d/MM/;
   $b =~ s/%h/HH12/;
   $b =~ s/%i/MI/;
   $b =~ s/%p/AM/;
   $b =~ s/%e/DD/;
   $b =~ s/%b/Mon/;
   $b =~ s/%Y/YYYY/;
   $b =~ s/%m/DD/;
   return $b ;
' LANGUAGE 'plperl';


This requires plperl which is not standard.  It converts from
"dateformat" to a postgres call to to_char(timestamp, formatstring).  If
I cared I would add the rest of the functions to my_to_pg_dateformat;

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

php code

The biggest issues
Using " instead of ' for date strings
examples:

Comment.class:
     date_format(date,\"%W, %M %d \@%h:%i%p\") AS time
to 
     date_format(date,'%W, %M %d \@%h:%i%p') AS time

search.php3
//zot $q .= " AND (story.title LIKE \"%$query%\"
              //zot OR story.intro_text LIKE \"%$query%\"
if ($query != "")
     $q .= " AND (story.title LIKE '%$query%'
              OR story.intro_text LIKE '%$query%'
                ) ";

I think mysql uses ' just fine.

Postgres got the LIMIT fields backwards (just dumb). This requires a db
check. LIMIT is only in 7.0 and up and should be fixed.  In other
projects we did a "if $db_type='postgres' blah"



There are problably a few more things, but not too many.

The big issues were timestamp and dateformat.  The rest are real
managable.

I cannot post a link to the site (no public site yet), but it does work
real well.




-- 
Zot O'Connor

http://www.ZotConsulting.com
http://www.WhiteKnightHackers.com