[geeklog-cvs] geeklog: removed date_part's and fixed install.php
geeklog-cvs at lists.geeklog.net
geeklog-cvs at lists.geeklog.net
Tue Jan 26 15:54:04 EST 2010
changeset 7657:17dc91afcb72
url: http://project.geeklog.net/cgi-bin/hgwebdir.cgi/geeklog/rev/17dc91afcb72
user: stan <yankees26an at gmail.com>
date: Mon Aug 31 17:27:27 2009 -0400
description:
removed date_part's and fixed install.php
diffstat:
public_html/admin/install/index.php | 55 ++++++++++++++-------------
public_html/index.php | 14 +++---
public_html/users.php | 22 +++-------
public_html/usersettings.php | 13 ++----
system/classes/story.class.php | 4 +-
system/lib-comment.php | 5 +-
system/lib-story.php | 2 +-
system/lib-syndication.php | 2 +-
8 files changed, 53 insertions(+), 64 deletions(-)
diffs (241 lines):
diff -r b50896e5b901 -r 17dc91afcb72 public_html/admin/install/index.php
--- a/public_html/admin/install/index.php Mon Aug 31 14:51:04 2009 -0400
+++ b/public_html/admin/install/index.php Mon Aug 31 17:27:27 2009 -0400
@@ -417,31 +417,34 @@
require_once $siteconfig_path;
require_once $_CONF['path_system'] . 'lib-database.php';
- //Create a func to check if plpgsql is already installed
- DB_query("CREATE OR REPLACE FUNCTION make_plpgsql()
- RETURNS VOID LANGUAGE SQL AS $$
- CREATE LANGUAGE plpgsql;
- $$;
- SELECT
- CASE
- WHEN EXISTS( SELECT 1 FROM pg_catalog.pg_language WHERE lanname='plpgsql')
- THEN NULL
- ELSE make_plpgsql() END;");
- //Create a function to check if table exists
- DB_query("CREATE OR REPLACE FUNCTION check_table(varchar, varchar)
- RETURNS boolean AS $$
- DECLARE
- v_cnt integer;
- v_tbl boolean;
- BEGIN
- SELECT count(1) INTO v_cnt FROM pg_tables where tablename = $1 and
- schemaname = $2;
- IF v_cnt > 0 THEN
- v_tbl = 'true';
- END IF;
- return v_tbl;
- END;
- $$ LANGUAGE 'plpgsql'");
+ if($_DB_dbms=='pgsql')
+ {
+ //Create a func to check if plpgsql is already installed
+ DB_query("CREATE OR REPLACE FUNCTION make_plpgsql()
+ RETURNS VOID LANGUAGE SQL AS $$
+ CREATE LANGUAGE plpgsql;
+ $$;
+ SELECT
+ CASE
+ WHEN EXISTS( SELECT 1 FROM pg_catalog.pg_language WHERE lanname='plpgsql')
+ THEN NULL
+ ELSE make_plpgsql() END;");
+ //Create a function to check if table exists
+ DB_query("CREATE OR REPLACE FUNCTION check_table(varchar, varchar)
+ RETURNS boolean AS $$
+ DECLARE
+ v_cnt integer;
+ v_tbl boolean;
+ BEGIN
+ SELECT count(1) INTO v_cnt FROM pg_tables where tablename = $1 and
+ schemaname = $2;
+ IF v_cnt > 0 THEN
+ v_tbl = 'true';
+ END IF;
+ return v_tbl;
+ END;
+ $$ LANGUAGE 'plpgsql'");
+ }
// Check if GL is already installed
if (INST_checkTableExists('vars')) {
@@ -824,7 +827,7 @@
{
global $_TABLES, $_DB_dbms;
- if (($_DB_dbms == 'mysql') || ($_DB_dbms == 'mssql' || $_DB_dbms== 'pgsql')) {
+ if (($_DB_dbms == 'mysql') || ($_DB_dbms == 'mssql') || ($_DB_dbms== 'pgsql')) {
// let's try and personalize the Admin account a bit ...
diff -r b50896e5b901 -r 17dc91afcb72 public_html/index.php
--- a/public_html/index.php Mon Aug 31 14:51:04 2009 -0400
+++ b/public_html/index.php Mon Aug 31 17:27:27 2009 -0400
@@ -277,7 +277,7 @@
}
}
-$msql = array();
+$msql = array();
$msql['mysql']="SELECT STRAIGHT_JOIN s.*, UNIX_TIMESTAMP(s.date) AS unixdate, "
. 'UNIX_TIMESTAMP(s.expire) as expireunix, '
. $userfields . ", t.topic, t.imageurl "
@@ -292,12 +292,12 @@
. "FROM {$_TABLES['stories']} AS s, {$_TABLES['users']} AS u, "
. "{$_TABLES['topics']} AS t WHERE (s.uid = u.uid) AND (s.tid = t.tid) AND"
. $sql . "ORDER BY featured DESC, date DESC LIMIT $offset, $limit";
-$msql['pgsql']="SELECT s.*, date_part('epoch',s.date) AS unixdate, "
- . 'date_part(\'epoch\',s.expire) as expireunix, '
- . $userfields . ", t.topic, t.imageurl "
- . "FROM {$_TABLES['stories']} AS s, {$_TABLES['users']} AS u, "
- . "{$_TABLES['topics']} AS t WHERE (s.uid = u.uid) AND (s.tid = t.tid) AND "
- . $sql . "ORDER BY featured DESC, date DESC LIMIT $limit OFFSET $offset";
+$msql['pgsql']="SELECT s.*, UNIX_TIMESTAMP(s.date) AS unixdate,
+ UNIX_TIMESTAMP(s.expire) as expireunix,
+ {$userfields} . , t.topic, t.imageurl
+ FROM {$_TABLES['stories']} AS s, {$_TABLES['users']} AS u,
+ {$_TABLES['topics']} AS t WHERE (s.uid = u.uid) AND (s.tid = t.tid) AND
+ {$sql} ORDER BY featured DESC, date DESC LIMIT {$limit} OFFSET {$offset}";
$result = DB_query ($msql);
diff -r b50896e5b901 -r 17dc91afcb72 public_html/users.php
--- a/public_html/users.php Mon Aug 31 14:51:04 2009 -0400
+++ b/public_html/users.php Mon Aug 31 17:27:27 2009 -0400
@@ -209,13 +209,10 @@
// list of last 10 stories by this user
if (sizeof ($tids) > 0) {
- $sql['mysql'] = "SELECT sid,title,UNIX_TIMESTAMP(date) AS unixdate FROM {$_TABLES['stories']} WHERE (uid = $user) AND (draft_flag = 0) AND (date <= NOW()) AND (tid IN ($topics))" . COM_getPermSQL ('AND');
- $sql['pgsql'] = "SELECT sid,title,date_part('epoch',date) AS unixdate FROM {$_TABLES['stories']} WHERE (uid = $user) AND (draft_flag = 0) AND (date <= NOW()) AND (tid IN ($topics))" . COM_getPermSQL ('AND');
- $sql['mssql'] = "SELECT sid,title,UNIX_TIMESTAMP(date) AS unixdate FROM {$_TABLES['stories']} WHERE (uid = $user) AND (draft_flag = 0) AND (date <= NOW()) AND (tid IN ($topics))" . COM_getPermSQL ('AND');
+ $sql = "SELECT sid,title,UNIX_TIMESTAMP(date) AS unixdate FROM {$_TABLES['stories']} WHERE (uid = $user) AND (draft_flag = 0) AND (date <= NOW()) AND (tid IN ($topics))" . COM_getPermSQL ('AND');
- $sql['mysql'] .= " ORDER BY unixdate DESC LIMIT 10";
- $sql['pgsql'] .= " ORDER BY unixdate DESC LIMIT 10";
- $sql['mssql'] .= " ORDER BY unixdate DESC LIMIT 10";
+ $sql .= " ORDER BY unixdate DESC LIMIT 10";
+
$result = DB_query ($sql);
$nrows = DB_numRows ($result);
@@ -264,9 +261,7 @@
$sidList = "'$sidList'";
// then, find all comments by the user in those stories
- $sql['mysql'] = "SELECT sid,title,cid,UNIX_TIMESTAMP(date) AS unixdate FROM {$_TABLES['comments']} WHERE (uid = $user) GROUP BY sid,title,cid,UNIX_TIMESTAMP(date)";
- $sql['pgsql'] = "SELECT sid,title,cid,date_part('epoch',date) AS unixdate FROM {$_TABLES['comments']} WHERE (uid = $user) GROUP BY sid,title,cid,date_part('epoch',date)";
- $sql['mssql'] = "SELECT sid,title,cid,UNIX_TIMESTAMP(date) AS unixdate FROM {$_TABLES['comments']} WHERE (uid = $user) GROUP BY sid,title,cid,UNIX_TIMESTAMP(date)";
+ $sql = "SELECT sid,title,cid,UNIX_TIMESTAMP(date) AS unixdate FROM {$_TABLES['comments']} WHERE (uid = $user) GROUP BY sid,title,cid,UNIX_TIMESTAMP(date)";
// SQL NOTE: Using a HAVING clause is usually faster than a where if the
// field is part of the select
@@ -274,14 +269,11 @@
// $sql .= " AND (sid in ($sidList))";
// }
if (!empty ($sidList)) {
- $sql['mysql'] .= " HAVING sid in ($sidList)";
- $sql['mssql'] .= " HAVING sid in ($sidList)";
- $sql['pgsql'] .= " HAVING sid in ($sidList)";
+ $sql .= " HAVING sid in ($sidList)";
+
}
- $sql['mysql'] .= " ORDER BY unixdate DESC LIMIT 10";
- $sql['pgsql'] .= " ORDER BY unixdate DESC LIMIT 10";
- $sql['mssql'] .= " ORDER BY unixdate DESC LIMIT 10";
+ $sql .= " ORDER BY unixdate DESC LIMIT 10";
$result = DB_query($sql);
diff -r b50896e5b901 -r 17dc91afcb72 public_html/usersettings.php
--- a/public_html/usersettings.php Mon Aug 31 14:51:04 2009 -0400
+++ b/public_html/usersettings.php Mon Aug 31 17:27:27 2009 -0400
@@ -1209,12 +1209,9 @@
// list of last 10 stories by this user
if (sizeof ($tids) > 0) {
- $sql['mysql'] = "SELECT sid,title,UNIX_TIMESTAMP(date) AS unixdate FROM {$_TABLES['stories']} WHERE (uid = $user) AND (draft_flag = 0) AND (date <= NOW()) AND (tid IN ($topics))" . COM_getPermSQL ('AND');
- $sql['mssql'] = "SELECT sid,title,UNIX_TIMESTAMP(date) AS unixdate FROM {$_TABLES['stories']} WHERE (uid = $user) AND (draft_flag = 0) AND (date <= NOW()) AND (tid IN ($topics))" . COM_getPermSQL ('AND');
- $sql['pgsql'] = "SELECT sid,title,date_part('epoch',date) AS unixdate FROM {$_TABLES['stories']} WHERE (uid = $user) AND (draft_flag = 0) AND (date <= NOW()) AND (tid IN ($topics))" . COM_getPermSQL ('AND');
- $sql['mysql'] .= " ORDER BY unixdate DESC LIMIT 10";
- $sql['mssql'] .= " ORDER BY unixdate DESC LIMIT 10";
- $sql['pgsql'] .= " ORDER BY unixdate DESC LIMIT 10";
+ $sql = "SELECT sid,title,UNIX_TIMESTAMP(date) AS unixdate FROM {$_TABLES['stories']} WHERE (uid = $user) AND (draft_flag = 0) AND (date <= NOW()) AND (tid IN ($topics))" . COM_getPermSQL ('AND');
+
+ $sql .= " ORDER BY unixdate DESC LIMIT 10";
$result = DB_query ($sql);
$nrows = DB_numRows ($result);
} else {
@@ -1263,9 +1260,7 @@
$sidList = "'$sidList'";
// then, find all comments by the user in those stories
- $sql['mysql'] = "SELECT sid,title,cid,UNIX_TIMESTAMP(date) AS unixdate FROM {$_TABLES['comments']} WHERE (uid = $user) GROUP BY sid,title,cid,UNIX_TIMESTAMP(date)";
- $sql['mssql'] = "SELECT sid,title,cid,UNIX_TIMESTAMP(date) AS unixdate FROM {$_TABLES['comments']} WHERE (uid = $user) GROUP BY sid,title,cid,UNIX_TIMESTAMP(date)";
- $sql['pgsql'] = "SELECT sid,title,cid,date_part('epoch',date) AS unixdate FROM {$_TABLES['comments']} WHERE (uid = $user) GROUP BY sid,title,cid,date_part('epoch',date)";
+ $sql = "SELECT sid,title,cid,UNIX_TIMESTAMP(date) AS unixdate FROM {$_TABLES['comments']} WHERE (uid = $user) GROUP BY sid,title,cid,UNIX_TIMESTAMP(date)";
// SQL NOTE: Using a HAVING clause is usually faster than a where if the
// field is part of the select
// if (!empty ($sidList)) {
diff -r b50896e5b901 -r 17dc91afcb72 system/classes/story.class.php
--- a/system/classes/story.class.php Mon Aug 31 14:51:04 2009 -0400
+++ b/system/classes/story.class.php Mon Aug 31 17:27:27 2009 -0400
@@ -431,14 +431,14 @@
$sql['mssql'] =
"SELECT STRAIGHT_JOIN s.sid, s.uid, s.draft_flag, s.tid, s.date, s.title, CAST(s.introtext AS text) AS introtext, CAST(s.bodytext AS text) AS bodytext, s.hits, s.numemails, s.comments, s.trackbacks, s.related, s.featured, s.show_topic_icon, s.commentcode, s.trackbackcode, s.statuscode, s.expire, s.postmode, s.frontpage, s.owner_id, s.group_id, s.perm_owner, s.perm_group, s.perm_members, s.perm_anon, s.advanced_editor_mode, " . " UNIX_TIMESTAMP(s.date) AS unixdate, UNIX_TIMESTAMP(s.expire) as expireunix, UNIX_TIMESTAMP(s.comment_expire) as cmt_expire_unix, " . "u.username, u.fullname, u.photo, u.email, t.topic, t.imageurl " . "FROM {$_TABLES['stories']} AS s, {$_TABLES['users']} AS u, {$_TABLES['topics']} AS t " . "WHERE (s.uid = u.uid) AND (s.tid = t.tid) AND (sid = '$sid')";
$sql['pgsql']
- = "SELECT s.*, date_part('epoch',s.date) AS unixdate, date_part('epoch',s.expire) as expireunix, date_part('epoch',s.comment_expire) as cmt_expire_unix, "
+ = "SELECT s.*, UNIX_TIMESTAMP(s.date) AS unixdate, UNIX_TIMESTAMP(s.expire) as expireunix, UNIX_TIMESTAMP(s.comment_expire) as cmt_expire_unix, "
. "u.username, u.fullname, u.photo, u.email, t.topic, t.imageurl " . "FROM {$_TABLES['stories']} AS s, {$_TABLES['users']} AS u, {$_TABLES['topics']} AS t " . "WHERE (s.uid = u.uid) AND (s.tid = t.tid) AND (sid = '$sid')";
} elseif (!empty($sid) && ($mode == 'editsubmission')) {
$sql['mysql'] = 'SELECT STRAIGHT_JOIN s.*, UNIX_TIMESTAMP(s.date) AS unixdate, '
. 'u.username, u.fullname, u.photo, u.email, t.topic, t.imageurl, t.group_id, ' . 't.perm_owner, t.perm_group, t.perm_members, t.perm_anon ' . 'FROM ' . $_TABLES['storysubmission'] . ' AS s, ' . $_TABLES['users'] . ' AS u, ' . $_TABLES['topics'] . ' AS t WHERE (s.uid = u.uid) AND' . ' (s.tid = t.tid) AND (sid = \'' . $sid . '\')';
$sql['mssql'] = 'SELECT STRAIGHT_JOIN s.*, UNIX_TIMESTAMP(s.date) AS unixdate, '
. 'u.username, u.fullname, u.photo, u.email, t.topic, t.imageurl, t.group_id, ' . 't.perm_owner, t.perm_group, t.perm_members, t.perm_anon ' . 'FROM ' . $_TABLES['storysubmission'] . ' AS s, ' . $_TABLES['users'] . ' AS u, ' . $_TABLES['topics'] . ' AS t WHERE (s.uid = u.uid) AND' . ' (s.tid = t.tid) AND (sid = \'' . $sid . '\')';
- $sql['pgsql'] = 'SELECT s.*, date_part(\'epoch\',s.date) AS unixdate, '
+ $sql['pgsql'] = 'SELECT s.*, UNIX_TIMESTAMP(s.date) AS unixdate, '
. 'u.username, u.fullname, u.photo, u.email, t.topic, t.imageurl, t.group_id, ' . 't.perm_owner, t.perm_group, t.perm_members, t.perm_anon ' . 'FROM ' . $_TABLES['storysubmission'] . ' AS s, ' . $_TABLES['users'] . ' AS u, ' . $_TABLES['topics'] . ' AS t WHERE (s.uid = u.uid) AND' . ' (s.tid = t.tid) AND (sid = \'' . $sid . '\')';
} elseif ($mode == 'edit') {
$this->_sid = COM_makesid();
diff -r b50896e5b901 -r 17dc91afcb72 system/lib-comment.php
--- a/system/lib-comment.php Mon Aug 31 14:51:04 2009 -0400
+++ b/system/lib-comment.php Mon Aug 31 17:27:27 2009 -0400
@@ -1662,9 +1662,8 @@
DB_query($sql);
}
- $sql['mysql'] = "UPDATE {$_TABLES['stories']} SET commentcode = 1 WHERE UNIX_TIMESTAMP(comment_expire) < UNIX_TIMESTAMP() AND UNIX_TIMESTAMP(comment_expire) <> 0";
- $sql['mssql'] = "UPDATE {$_TABLES['stories']} SET commentcode = 1 WHERE UNIX_TIMESTAMP(comment_expire) < UNIX_TIMESTAMP() AND UNIX_TIMESTAMP(comment_expire) <> 0";
- $sql['pgsql'] = "UPDATE {$_TABLES['stories']} SET commentcode = 1 WHERE date_part('epoch',comment_expire) < '".mktime()."' AND date_part('epoch',comment_expire) <> 0";
+ $sql = "UPDATE {$_TABLES['stories']} SET commentcode = 1 WHERE UNIX_TIMESTAMP(comment_expire) < UNIX_TIMESTAMP() AND UNIX_TIMESTAMP(comment_expire) <> 0";
+
DB_query($sql);
}
diff -r b50896e5b901 -r 17dc91afcb72 system/lib-story.php
--- a/system/lib-story.php Mon Aug 31 14:51:04 2009 -0400
+++ b/system/lib-story.php Mon Aug 31 17:27:27 2009 -0400
@@ -1485,7 +1485,7 @@
$sql['mssql'] =
"SELECT STRAIGHT_JOIN s.sid, s.uid, s.draft_flag, s.tid, s.date, s.title, CAST(s.introtext AS text) AS introtext, CAST(s.bodytext AS text) AS bodytext, s.hits, s.numemails, s.comments, s.trackbacks, s.related, s.featured, s.show_topic_icon, s.commentcode, s.trackbackcode, s.statuscode, s.expire, s.postmode, s.frontpage, s.owner_id, s.group_id, s.perm_owner, s.perm_group, s.perm_members, s.perm_anon, s.advanced_editor_mode, " . " UNIX_TIMESTAMP(s.date) AS unixdate, UNIX_TIMESTAMP(s.expire) as expireunix, " . "u.username, u.fullname, u.photo, u.email, t.topic, t.imageurl " . "FROM {$_TABLES['stories']} AS s, {$_TABLES['users']} AS u, {$_TABLES['topics']} AS t " . "WHERE (s.uid = u.uid) AND (s.tid = t.tid)" . COM_getPermSQL('AND', $_USER['uid'], 2, 's') . $order . $limit;
- $sql['pgsql'] = "SELECT s.*, date_part('epoch', s.date) AS unixdate, date_part('epoch', s.expire) as expireunix, u.username, u.fullname, u.photo, u.email, t.topic, t.imageurl FROM stories s, users u, topics t WHERE (s.uid = u.uid) AND (s.tid = t.tid) FROM {$_TABLES['stories']} AS s, {$_TABLES['users']} AS u, {$_TABLES['topics']} AS t WHERE (s.uid = u.uid) AND (s.tid = t.tid)" . COM_getPermSQL('AND', $_USER['uid'], 2, 's') . $order . $limit_pgsql;
+ $sql['pgsql'] = "SELECT s.*, UNIX_TIMESTAMP(s.date) AS unixdate, UNIX_TIMESTAMP(s.expire) as expireunix, u.username, u.fullname, u.photo, u.email, t.topic, t.imageurl FROM stories s, users u, topics t WHERE (s.uid = u.uid) AND (s.tid = t.tid) FROM {$_TABLES['stories']} AS s, {$_TABLES['users']} AS u, {$_TABLES['topics']} AS t WHERE (s.uid = u.uid) AND (s.tid = t.tid)" . COM_getPermSQL('AND', $_USER['uid'], 2, 's') . $order . $limit_pgsql;
$result = DB_query($sql);
$count = 0;
diff -r b50896e5b901 -r 17dc91afcb72 system/lib-syndication.php
--- a/system/lib-syndication.php Mon Aug 31 14:51:04 2009 -0400
+++ b/system/lib-syndication.php Mon Aug 31 17:27:27 2009 -0400
@@ -387,7 +387,7 @@
$where .= ' AND frontpage = 1';
}
- $result = DB_query( "SELECT sid,tid,uid,title,introtext,bodytext,postmode,date_part('epoch',date) AS modified,commentcode,trackbackcode FROM {$_TABLES['stories']} WHERE draft_flag = 0 AND date <= NOW() $where AND perm_anon > 0 ORDER BY date DESC $limitsql" );
+ $result = DB_query( "SELECT sid,tid,uid,title,introtext,bodytext,postmode,UNIX_TIMESTAMP(date) AS modified,commentcode,trackbackcode FROM {$_TABLES['stories']} WHERE draft_flag = 0 AND date <= NOW() $where AND perm_anon > 0 ORDER BY date DESC $limitsql" );
$content = array();
$sids = array();
More information about the geeklog-cvs
mailing list