[geeklog-cvs] geeklog: more sql revisions
geeklog-cvs at lists.geeklog.net
geeklog-cvs at lists.geeklog.net
Tue Jan 26 15:53:31 EST 2010
changeset 7624:d84e2e6d7158
url: http://project.geeklog.net/cgi-bin/hgwebdir.cgi/geeklog/rev/d84e2e6d7158
user: stan <yankees26an at gmail.com>
date: Tue Jun 30 00:05:56 2009 -0400
description:
more sql revisions
diffstat:
public_html/lib-common.php | 22 ++++++++++++++--------
public_html/stats.php | 7 +++++--
public_html/users.php | 21 ++++++++++++++++-----
public_html/usersettings.php | 26 +++++++++++++++++++-------
system/classes/story.class.php | 9 ++++++++-
system/lib-comment.php | 23 ++++++++++++-----------
system/lib-sessions.php | 4 ++--
7 files changed, 76 insertions(+), 36 deletions(-)
diffs (273 lines):
diff -r 09f77b7a02ed -r d84e2e6d7158 public_html/lib-common.php
--- a/public_html/lib-common.php Fri Jun 26 12:53:14 2009 -0400
+++ b/public_html/lib-common.php Tue Jun 30 00:05:56 2009 -0400
@@ -68,7 +68,7 @@
* Must make sure that the function hasn't been disabled before calling it.
*
*/
-if( function_exists('fuckset_error_handler') )
+if( function_exists('set_error_handler') )
{
if( PHP_VERSION >= 5 )
{
@@ -3261,7 +3261,10 @@
{
global $_TABLES, $_CONF;
- $sql = "SELECT sid,tid,title,comments,UNIX_TIMESTAMP(date) AS day FROM {$_TABLES['stories']} WHERE (perm_anon = 2) AND (frontpage = 1) AND (date <= NOW()) AND (draft_flag = 0)" . COM_getTopicSQL( 'AND', 1 ) . " ORDER BY featured DESC, date DESC LIMIT {$_CONF['limitnews']}, {$_CONF['limitnews']}";
+ $sql['mysql'] = "SELECT sid,tid,title,comments,UNIX_TIMESTAMP(date) AS day FROM {$_TABLES['stories']} WHERE (perm_anon = 2) AND (frontpage = 1) AND (date <= NOW()) AND (draft_flag = 0)" . COM_getTopicSQL( 'AND', 1 ) . " ORDER BY featured DESC, date DESC LIMIT {$_CONF['limitnews']}, {$_CONF['limitnews']}";
+ $sql['mssql'] = "SELECT sid,tid,title,comments,UNIX_TIMESTAMP(date) AS day FROM {$_TABLES['stories']} WHERE (perm_anon = 2) AND (frontpage = 1) AND (date <= NOW()) AND (draft_flag = 0)" . COM_getTopicSQL( 'AND', 1 ) . " ORDER BY featured DESC, date DESC LIMIT {$_CONF['limitnews']}, {$_CONF['limitnews']}";
+ $sql['pgsql'] = "SELECT sid,tid,title,comments,date_part('epoch',date) AS day FROM {$_TABLES['stories']} WHERE (perm_anon = 2) AND (frontpage = 1) AND (date <= NOW()) AND (draft_flag = 0)" . COM_getTopicSQL( 'AND', 1 ) . " ORDER BY featured DESC, date DESC LIMIT {$_CONF['limitnews']}, {$_CONF['limitnews']}";
+
$result = DB_query( $sql );
$nrows = DB_numRows( $result );
@@ -5197,15 +5200,16 @@
*/
function COM_updateSpeedlimit($type = 'submit', $property = '')
{
- global $_TABLES;
+ global $_TABLES,$_DB_dbms;
if (empty($property)) {
$property = $_SERVER['REMOTE_ADDR'];
}
$property = addslashes($property);
-
- DB_save($_TABLES['speedlimit'], 'ipaddress,date,type',
- "'$property',UNIX_TIMESTAMP(),'$type'");
+ $time = mktime();
+
+ $sql = "UPDATE {$_TABLES['speedlimit']} SET ipaddress = '$property', date = '$time', type = '$type'";
+ DB_query($sql);
}
/**
@@ -5223,7 +5227,8 @@
if (!empty($type)) {
$sql .= "(type = '$type') AND ";
}
- $sql .= "(date < UNIX_TIMESTAMP() - $speedlimit)";
+ $time = mktime();
+ $sql .= "(date < $time - $speedlimit)";
DB_query($sql);
}
@@ -6871,7 +6876,8 @@
if ($_CONF['cron_schedule_interval'] > 0) {
if ((DB_getItem($_TABLES['vars'], 'value', "name='last_scheduled_run'")
+ $_CONF['cron_schedule_interval']) <= time()) {
- DB_query("UPDATE {$_TABLES['vars']} SET value=UNIX_TIMESTAMP() WHERE name='last_scheduled_run'");
+ $time = ($_DB_dbms=='pgsql')? 'date_part(\'epoch\',NOW())':'UNIX_TIMESTAMP()';
+ DB_query("UPDATE {$_TABLES['vars']} SET value=$time WHERE name='last_scheduled_run'");
PLG_runScheduledTask();
}
}
diff -r 09f77b7a02ed -r d84e2e6d7158 public_html/stats.php
--- a/public_html/stats.php Fri Jun 26 12:53:14 2009 -0400
+++ b/public_html/stats.php Tue Jun 30 00:05:56 2009 -0400
@@ -79,8 +79,11 @@
if ($_CONF['lastlogin']) {
// if we keep track of the last login date, count the number of users
- // that have logged in during the last 4 weeks
- $result = DB_query ("SELECT COUNT(*) AS count FROM {$_TABLES['users']} AS u,{$_TABLES['userinfo']} AS i WHERE (u.uid > 1) AND (u.uid = i.uid) AND (lastlogin <> '') AND (lastlogin >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 28 DAY)))");
+ // that have logged in during the last 4 weeks
+ $sql['pgsql'] = "SELECT COUNT(*) AS count FROM {$_TABLES['users']} AS u,{$_TABLES['userinfo']} AS i WHERE (u.uid > 1) AND (u.uid = i.uid) AND (lastlogin <> '') AND (lastlogin::int4 >= date_part('epoch', INTERVAL '28 DAY'))";
+ $sql['mysql'] = "SELECT COUNT(*) AS count FROM {$_TABLES['users']} AS u,{$_TABLES['userinfo']} AS i WHERE (u.uid > 1) AND (u.uid = i.uid) AND (lastlogin <> '') AND (lastlogin >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 28 DAY)))";
+ $sql['mssql'] = "SELECT COUNT(*) AS count FROM {$_TABLES['users']} AS u,{$_TABLES['userinfo']} AS i WHERE (u.uid > 1) AND (u.uid = i.uid) AND (lastlogin <> '') AND (lastlogin >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 28 DAY)))";
+ $result = DB_query ($sql);
list($active_users) = DB_fetchArray ($result);
} else {
// otherwise, just count all users with status 'active'
diff -r 09f77b7a02ed -r d84e2e6d7158 public_html/users.php
--- a/public_html/users.php Fri Jun 26 12:53:14 2009 -0400
+++ b/public_html/users.php Tue Jun 30 00:05:56 2009 -0400
@@ -171,7 +171,7 @@
$user_templates->set_var ('user_photo', $photo);
$user_templates->set_var ('lang_membersince', $LANG04[67]);
- $user_templates->set_var ('user_regdate', $A['regdate']);
+ $user_templates->set_var ('user_regdate', $A['regdate']);
$user_templates->set_var ('lang_email', $LANG04[5]);
$user_templates->set_var ('user_id', $user);
$user_templates->set_var ('lang_sendemail', $LANG04[81]);
@@ -209,8 +209,14 @@
// list of last 10 stories by this user
if (sizeof ($tids) > 0) {
- $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";
+ $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['mysql'] .= " ORDER BY unixdate DESC LIMIT 10";
+ $sql['pgsql'] .= " ORDER BY unixdate DESC LIMIT 10";
+ $sql['mssql'] .= " ORDER BY unixdate DESC LIMIT 10";
+
$result = DB_query ($sql);
$nrows = DB_numRows ($result);
} else {
@@ -257,7 +263,9 @@
$sidList = "'$sidList'";
// then, find all comments by the user in those stories
- $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['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_poart('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 NOTE: Using a HAVING clause is usually faster than a where if the
// field is part of the select
@@ -267,7 +275,10 @@
if (!empty ($sidList)) {
$sql .= " HAVING sid in ($sidList)";
}
- $sql .= " ORDER BY unixdate DESC LIMIT 10";
+ $sql['mysql'] .= " ORDER BY unixdate DESC LIMIT 10";
+ $sql['pgsql'] .= " ORDER BY unixdate DESC LIMIT 10";
+ $sql['mssql'] .= " ORDER BY unixdate DESC LIMIT 10";
+
$result = DB_query($sql);
$nrows = DB_numRows($result);
diff -r 09f77b7a02ed -r d84e2e6d7158 public_html/usersettings.php
--- a/public_html/usersettings.php Fri Jun 26 12:53:14 2009 -0400
+++ b/public_html/usersettings.php Tue Jun 30 00:05:56 2009 -0400
@@ -1209,8 +1209,12 @@
// list of last 10 stories by this user
if (sizeof ($tids) > 0) {
- $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";
+ $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";
$result = DB_query ($sql);
$nrows = DB_numRows ($result);
} else {
@@ -1253,23 +1257,31 @@
$sidArray[] = $S['sid'];
}
}
-
+ unset($sql);
+ $sql = array();
$sidList = implode("', '",$sidArray);
$sidList = "'$sidList'";
// then, find all comments by the user in those stories
- $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['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 NOTE: Using a HAVING clause is usually faster than a where if the
// field is part of the select
// if (!empty ($sidList)) {
// $sql .= " AND (sid in ($sidList))";
// }
if (!empty ($sidList)) {
- $sql .= " HAVING sid in ($sidList)";
+ $sql['mysql'] .= " HAVING sid in ($sidList)";
+ $sql['pgsql'] .= " HAVING sid in ($sidList)";
+ $sql['mssql'] .= " HAVING sid in ($sidList)";
+
}
- $sql .= " ORDER BY unixdate DESC LIMIT 10";
+ $sql['mysql'] .= " ORDER BY unixdate DESC LIMIT 10";
+ $sql['mssql'] .= " ORDER BY unixdate DESC LIMIT 10";
+ $sql['pgsql'] .= " ORDER BY unixdate DESC LIMIT 10";
+
$result = DB_query($sql);
$nrows = DB_numRows($result);
if ($nrows > 0) {
diff -r 09f77b7a02ed -r d84e2e6d7158 system/classes/story.class.php
--- a/system/classes/story.class.php Fri Jun 26 12:53:14 2009 -0400
+++ b/system/classes/story.class.php Tue Jun 30 00:05:56 2009 -0400
@@ -430,8 +430,15 @@
$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, "
+ . "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 = 'SELECT STRAIGHT_JOIN s.*, UNIX_TIMESTAMP(s.date) AS unixdate, '
+ $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, '
. '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 09f77b7a02ed -r d84e2e6d7158 system/lib-comment.php
--- a/system/lib-comment.php Fri Jun 26 12:53:14 2009 -0400
+++ b/system/lib-comment.php Tue Jun 30 00:05:56 2009 -0400
@@ -1120,19 +1120,16 @@
COM_errorLog("CMT_saveComment: $uid from {$_SERVER['REMOTE_ADDR']} tried "
. 'to submit a comment with invalid $title and/or $comment.');
$ret = 5;
- } elseif (($_CONF['commentsubmission'] == 1) &&
- !SEC_hasRights('comment.submit')) {
- // comment into comment submission table enabled
+ } elseif ( $_CONF['commentsubmission'] == 1 && !SEC_hasRights('comment.submit') ) {
+ //comment into comment submission table enabled
if (isset($name)) {
- DB_save($_TABLES['commentsubmissions'],
- 'sid,uid,name,comment,date,title,pid,ipaddress,type',
- "'$sid',$uid,'$name','$comment',NOW(),'$title',$pid,'{$_SERVER['REMOTE_ADDR']}','$type'");
+ DB_save ( $_TABLES['commentsubmissions'], 'sid,uid,name,comment,date,title,pid,ipaddress',
+ "'$sid',$uid,'$name','$comment',now(),'$title',$pid,'{$_SERVER['REMOTE_ADDR']}'");
} else {
- DB_save($_TABLES['commentsubmissions'],
- 'sid,uid,comment,date,title,pid,ipaddress,type',
- "'$sid',$uid,'$comment',NOW(),'$title',$pid,'{$_SERVER['REMOTE_ADDR']}','$type'");
+ DB_save ( $_TABLES['commentsubmissions'], 'sid,uid,comment,date,title,pid,ipaddress',
+ "'$sid',$uid,'$comment',now(),'$title',$pid,'{$_SERVER['REMOTE_ADDR']}'");
}
-
+
$ret = -1;
} elseif ($pid > 0) {
DB_lockTable ($_TABLES['comments']);
@@ -1658,7 +1655,10 @@
$sql = "UPDATE {$_TABLES['stories']} SET commentcode = 1 WHERE commentcode = 0 " . $sql;
DB_query($sql);
}
- $sql = "UPDATE {$_TABLES['stories']} SET commentcode = 1 WHERE UNIX_TIMESTAMP(comment_expire) < UNIX_TIMESTAMP() AND UNIX_TIMESTAMP(comment_expire) <> 0";
+
+ $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";
DB_query($sql);
}
@@ -1694,6 +1694,7 @@
/**
* Moves comment from submission table to comments table
*
+ * @param int cid comment id
* @copyright Jared Wenerd 2008
* @author Jared Wenerd, wenerd87 AT gmail DOT com
* @param string $cid comment id
diff -r 09f77b7a02ed -r d84e2e6d7158 system/lib-sessions.php
--- a/system/lib-sessions.php Fri Jun 26 12:53:14 2009 -0400
+++ b/system/lib-sessions.php Tue Jun 30 00:05:56 2009 -0400
@@ -279,8 +279,8 @@
$result = DB_query($sql);
if ($result) {
if ($_CONF['lastlogin'] == true) {
- // Update userinfo record to record the date and time as lastlogin
- DB_query("UPDATE {$_TABLES['userinfo']} SET lastlogin = UNIX_TIMESTAMP() WHERE uid=$userid");
+ // Update userinfo record to record the date and time as lastlogin
+ DB_query("UPDATE {$_TABLES['userinfo']} SET lastlogin = '".mktime()."' WHERE uid=$userid");
}
if ($_SESS_VERBOSE) COM_errorLog("Assigned the following session id: $sessid",1);
if ($_SESS_VERBOSE) COM_errorLog("*************leaving SESS_newSession*****************",1);
More information about the geeklog-cvs
mailing list