니들웍스 주제: 1.8RC PostgreSQL 지원 문제.
setup에서 테이블 생성이 안됩니다. SQL에 문제가 있습니다.
정상동작하도록 한 패치는 다음과 같습니다.
comptibility.PostgreSQL.sql은 PHP의 pgsql 모듈의 파싱 문제로 쿼트 내부의 ;를 인식해서 별도 쿼리로 판단하는 문제입니다.
initialize.PostgreSQL.sql은 문법 자체가 틀렸습니다.
--- compatibility.PostgreSQL.sql.orig 2009-10-23 01:34:38.000000000 +0900
+++ compatibility.PostgreSQL.sql 2009-10-23 01:34:54.000000000 +0900
@@ -4,21 +4,21 @@
' LANGUAGE 'SQL';
CREATE OR REPLACE FUNCTION unix_timestamp() RETURNS integer AS '
SELECT
-ROUND(EXTRACT( EPOCH FROM abstime(now()) ))::int4 AS result;
+ROUND(EXTRACT( EPOCH FROM abstime(now()) ))::int4 AS result
' LANGUAGE 'SQL';
CREATE OR REPLACE FUNCTION unix_timestamp(timestamp with time zone) RETURNS integer AS '
SELECT
-ROUND(EXTRACT( EPOCH FROM ABSTIME($1) ))::int4 AS result;
+ROUND(EXTRACT( EPOCH FROM ABSTIME($1) ))::int4 AS result
' LANGUAGE 'SQL';
CREATE OR REPLACE FUNCTION dayofmonth(timestamp without time zone) RETURNS numeric AS '
SELECT
-to_number(to_char($1, \'DD\'),\'99\') AS result;
+to_number(to_char($1, \'DD\'),\'99\') AS result
' LANGUAGE 'SQL';
CREATE OR REPLACE FUNCTION year(timestamp without time zone) RETURNS numeric AS '
SELECT
-to_number(to_char($1, \'YYYY\'),\'99\') AS result;
+to_number(to_char($1, \'YYYY\'),\'99\') AS result
' LANGUAGE 'SQL';
CREATE OR REPLACE FUNCTION month(timestamp without time zone) RETURNS numeric AS '
SELECT
-to_number(to_char($1, \'MM\'),\'99\') AS result;
-' LANGUAGE 'SQL';
\ No newline at end of file
+to_number(to_char($1, \'MM\'),\'99\') AS result
+' LANGUAGE 'SQL';
--- initialize.PostgreSQL.sql.orig 2009-10-23 01:31:44.000000000 +0900
+++ initialize.PostgreSQL.sql 2009-10-23 01:35:57.000000000 +0900
@@ -120,8 +120,8 @@
contentformatter varchar(32) DEFAULT '' NOT NULL,
contenteditor varchar(32) DEFAULT '' NOT NULL,
location varchar(255) NOT NULL default '/',
- latitude float default NULL;
- longitude float default NULL;
+ latitude float default NULL,
+ longitude float default NULL,
password varchar(32) default NULL,
acceptcomment integer NOT NULL default '1',
accepttrackback integer NOT NULL default '1',
@@ -150,8 +150,8 @@
contentformatter varchar(32) DEFAULT '' NOT NULL,
contenteditor varchar(32) DEFAULT '' NOT NULL,
location varchar(255) NOT NULL default '/',
- latitude float default NULL;
- longitude float default NULL;
+ latitude float default NULL,
+ longitude float default NULL,
password varchar(32) default NULL,
created integer NOT NULL default 0,
PRIMARY KEY (blogid, id, created)
@@ -399,8 +399,8 @@
PRIMARY KEY (userid,name)
) [##_charset_##];
CREATE TABLE [##_dbPrefix_##]Widgets (
- id int(11) default 1 NOT NULL,
- blogid int(11) default 1 NOT NULL,
+ id integer default 1 NOT NULL,
+ blogid integer default 1 NOT NULL,
title varchar(64) default 'Widget' NOT NULL,
author varchar(32) default 'Textcube' NOT NULL,
email varchar(32) DEFAULT NULL,
@@ -410,8 +410,8 @@
authorlink varchar(128) DEFAULT NULL,
authorlocation varchar(32) DEFAULT NULL,
authorphoto varchar(128) DEFAULT NULL,
- height int(11) DEFAULT NULL,
- scrolling int(1) default 0,
+ height integer DEFAULT NULL,
+ scrolling integer default 0,
feature varchar(32) default 'opensocial',
content text NOT NULL,
PRIMARY KEY (id)추가로, 설치 완료 후 ORDER BY RAND() 부분에서 오류가 발생합니다.
STATEMENT: SELECT t.name, count(*) AS cnt, t.id FROM tc_Tags t,
tc_TagRelations r,
tc_Entries e
WHERE r.entry = e.id AND e.visibility > 0 AND t.id = r.tag AND r.blogid = 1 AND e.blogid = 1
GROUP BY r.tag, t.name, t.id
ORDER BY RAND() LIMIT 30
ERROR: function rand() does not exist at character 61
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
STATEMENT: SELECT * FROM tc_Feeds WHERE modified < 1256222286 ORDER BY RAND() LIMIT 1
ERROR: function rand() does not exist at character 8
PostgreSQL 랜덤 함수는 Random()이며, Rand()가 존재하지 않기 때문에 발생하는 문제입니다.
이 부분은 http://www.phpbuilder.com/board/showthr … t=10338930처럼, ORDER BY RAND() 보다는 PHP에서 랜덤값을 뽑아서 처리하는 게 더 효율적이므로 그런 방향으로 수정이 이루어지길 바랍니다.