|
From: <we...@us...> - 2003-03-14 02:38:09
|
Update of /cvsroot/php-blog/jBlog
In directory sc8-pr-cvs1:/tmp/cvs-serv18118
Modified Files:
db.sql jBlog_admin_entries.inc.php
jBlog_admin_installer.inc.php jBlog_config.inc.php
jBlog_db.inc.php jBlog_db_mysql.inc.php
jBlog_db_sqlite.inc.php jBlog_functions.inc.php
Added Files:
jBlog_db_postgres.inc.php
Log Message:
More abstraction work.
Add postgres support - it works.
sqlite does not work (seems like the extension itself needs some work).
*** UPDATE YOUR SCHEMA ***
- password varchar(20) default null,
+ password varchar(32) default null,
passwords are now stored as md5("mypassword"); you will need to update the
encrypted form of your password too.
Although the db.sql looks quite different, it's still more or less the same.
If you need to change the schema, dump to a separate file and merge in the
changes using the special {} markers and separate keys into CREATE INDEX
statements for more portability.
The calendar code should be a little more cpu friendly now.
--- NEW FILE: jBlog_db_postgres.inc.php ---
<?php # $Id: jBlog_db_postgres.inc.php,v 1.1 2003/03/14 02:38:05 wez Exp $
function jBlog_db_connect()
{
global $jBlog;
$jBlog['dbConn'] = pg_connect(sprintf("host=%s dbname=%s user=%s password=%s", $jBlog['dbHost'], $jBlog['dbName'], $jBlog['dbUser'], $jBlog['dbPass']));
return $jBlog['dbConn'];
}
function jBlog_db_escape_string($string)
{
return pg_escape_string($string);
}
function jBlog_db_limit($start, $offset)
{
return "$offset, $start";
}
function jBlog_db_affected_rows()
{
global $jBlog;
return pg_affected_rows($jBlog['dbLastResult']);
}
function jBlog_db_insert_id()
{
global $jBlog;
return pg_last_oid($jBlog['dbLastResult']);
}
function jBlog_db_query($sql, $single = false, $result_type = "both")
{
global $jBlog;
static $type_map = array(
'assoc' => PGSQL_ASSOC,
'num' => PGSQL_NUM,
'both' => PGSQL_BOTH
);
$jBlog['dbLastResult'] = pg_query($jBlog['dbConn'], $sql);
if (!$jBlog['dbLastResult']) {
print pg_last_error($jBlog['dbConn']);
if (function_exists("debug_backtrace")) {
highlight_string(var_export(debug_backtrace(), 1));
}
return false;
}
if ($jBlog['dbLastResult'] === true) {
return true;
}
$result_type = $type_map[$result_type];
$n = pg_num_rows($jBlog['dbLastResult']);
switch ($n) {
case 0:
if ($single) {
return false;
}
return true;
case 1:
if ($single) {
return pg_fetch_array($jBlog['dbLastResult'], 0, $result_type);
}
default:
$rows = array();
for ($i = 0; $i < $n; $i++) {
$rows[] = pg_fetch_array($jBlog['dbLastResult'], $i, $result_type);
}
return $rows;
}
}
function jBlog_db_schema_import($query)
{
static $search = array('{AUTOINCREMENT}', '{UNSIGNED}', '{FULLTEXT}', 'int(1)', 'int(10)', 'int(11)', 'int(4)');
static $replace = array('SERIAL primary key', '', '', 'int2', 'int4', 'int4', 'int4');
return jBlog_db_query(str_replace($search, $replace, $query));
}
function jBlog_db_probe($hash, &$errs)
{
global $jBlog;
$jBlog['dbConn'] = pg_connect(sprintf("host=%s dbname=%s user=%s password=%s", $hash['dbHost'], $hash['dbName'], $hash['dbUser'], $hash['dbPass']));
if (!$jBlog['dbConn']) {
$errs[] = "Could not connect to database; check your settings.";
return false;
}
return true;
}
?>
Index: db.sql
===================================================================
RCS file: /cvsroot/php-blog/jBlog/db.sql,v
retrieving revision 1.18
retrieving revision 1.19
diff -u -d -r1.18 -r1.19
--- db.sql 13 Mar 2003 18:37:05 -0000 1.18
+++ db.sql 14 Mar 2003 02:38:05 -0000 1.19
@@ -4,7 +4,7 @@
create table {PREFIX}authors (
username varchar(20) default null,
- password varchar(20) default null,
+ password varchar(32) default null,
authorid {AUTOINCREMENT},
mail_comments int(1) default '1',
mail_trackbacks int(1) default '1',
@@ -17,18 +17,19 @@
create table {PREFIX}comments (
id {AUTOINCREMENT},
- entry_id int(10) unsigned not null default '0',
- timestamp int(10) unsigned default null,
+ entry_id int(10) {UNSIGNED} not null default '0',
+ timestamp int(10) {UNSIGNED} default null,
title varchar(150) default null,
author varchar(80) default null,
email varchar(200) default null,
url varchar(200) default null,
ip varchar(15) default null,
body text,
- type varchar(100) default 'regular',
- fulltext key body (body)
+ type varchar(100) default 'regular'
);
+CREATE {FULLTEXT} INDEX body on {PREFIX}comments (body);
+
#
# table structure for table '{PREFIX}entries'
#
@@ -36,24 +37,25 @@
create table {PREFIX}entries (
id {AUTOINCREMENT},
title varchar(200) default null,
- timestamp int(10) unsigned default null,
+ timestamp int(10) {UNSIGNED} default null,
body text,
- comments int(4) unsigned default '0',
+ comments int(4) {UNSIGNED} default '0',
extended text,
exflag int(1) default null,
author varchar(20) default null,
authorid int(11) default null,
- categoryid int(11) default null,
- fulltext key title (title,body,extended)
+ categoryid int(11) default null
);
+CREATE {FULLTEXT} INDEX title on {PREFIX}entries (title,body,extended);
+
#
# table structure for table '{PREFIX}references'
#
create table {PREFIX}references (
id {AUTOINCREMENT},
- entry_id int(10) unsigned not null default '0',
+ entry_id int(10) {UNSIGNED} not null default '0',
link text,
name text
);
@@ -64,7 +66,7 @@
CREATE TABLE {PREFIX}referrers (
url varchar(128) NOT NULL default '',
- day date NOT NULL default '0000-00-00',
+ day date NOT NULL,
count int(11) NOT NULL default '0',
PRIMARY KEY (url,day)
);
@@ -87,14 +89,15 @@
CREATE TABLE {PREFIX}suppress (
url varchar(128) default NULL,
ip varchar(15) default NULL,
- last timestamp(14) NOT NULL,
- unique key (url, ip)
+ last timestamp NOT NULL
);
+CREATE unique INDEX url on {PREFIX}suppress (url, ip);
+
CREATE TABLE {PREFIX}plugins (
name varchar(128) not null,
- placement enum ('left', 'right', 'hide') not null default 'right',
- sort_order int(4) not null,
+ placement varchar(6) not null default 'right',
+ sort_order int(4) not null default '0',
PRIMARY KEY(name)
);
Index: jBlog_admin_entries.inc.php
===================================================================
RCS file: /cvsroot/php-blog/jBlog/jBlog_admin_entries.inc.php,v
retrieving revision 1.10
retrieving revision 1.11
diff -u -d -r1.10 -r1.11
--- jBlog_admin_entries.inc.php 13 Mar 2003 12:44:39 -0000 1.10
+++ jBlog_admin_entries.inc.php 14 Mar 2003 02:38:05 -0000 1.11
@@ -20,7 +20,7 @@
$perPage = 30;
// Fetch the entries
- $entries = jBlog_fetchEntries(false, false, $jBlog["GET"]["offset"].", $perPage");
+ $entries = jBlog_fetchEntries(false, false, jBlog_db_limit($jBlog["GET"]["offset"], $perPage));
// Prepare table
echo "<div class='jBlog_admin_list'>";
Index: jBlog_admin_installer.inc.php
===================================================================
RCS file: /cvsroot/php-blog/jBlog/jBlog_admin_installer.inc.php,v
retrieving revision 1.12
retrieving revision 1.13
diff -u -d -r1.12 -r1.13
--- jBlog_admin_installer.inc.php 13 Mar 2003 18:37:06 -0000 1.12
+++ jBlog_admin_installer.inc.php 14 Mar 2003 02:38:05 -0000 1.13
@@ -259,16 +259,17 @@
} else {
$mail_comments = 0;
}
+ $enc_pass = md5($_POST['pass']);
$query = "INSERT INTO $_POST[dbPrefix]authors
(username, password, email, mail_comments)
VALUES('$_POST[user]',
- password('$_POST[pass]'),
+ '$enc_pass',
'$_POST[email]',
$mail_comments) ";
jBlog_db_query($query);
$text = jBlog_db_escape_string(file_get_contents('./jBlog.css'));
- $query = "INSERT into $_POST[dbPrefix]css values(NULL, 'default', '$text')";
+ $query = "INSERT into $_POST[dbPrefix]css (name, data) values ('default', '$text')";
$res = jBlog_db_query($query);
if (is_string($res)) {
echo $res;
Index: jBlog_config.inc.php
===================================================================
RCS file: /cvsroot/php-blog/jBlog/jBlog_config.inc.php,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -d -r1.4 -r1.5
--- jBlog_config.inc.php 13 Mar 2003 12:44:39 -0000 1.4
+++ jBlog_config.inc.php 14 Mar 2003 02:38:05 -0000 1.5
@@ -56,7 +56,7 @@
// Connect to database
-if (!is_resource(jBlog_db_connect())) {
+if (!jBlog_db_connect()) {
die ("jBlog error: unable to connect to database - exiting");
}
Index: jBlog_db.inc.php
===================================================================
RCS file: /cvsroot/php-blog/jBlog/jBlog_db.inc.php,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -d -r1.1 -r1.2
--- jBlog_db.inc.php 13 Mar 2003 18:37:07 -0000 1.1
+++ jBlog_db.inc.php 14 Mar 2003 02:38:05 -0000 1.2
@@ -18,6 +18,9 @@
$where .= " AND ";
$where .= "$k='" . jBlog_db_escape_string($v) . "'";
}
+ if (strlen($where)) {
+ $where = " WHERE $where";
+ }
return jBlog_db_query("UPDATE $jBlog[dbPrefix]$table SET $set $where");
}
@@ -31,7 +34,7 @@
foreach ($values as $k => $v) {
if (strlen($vals))
$vals .= ", ";
- $vals .= "$k='" . jBlog_db_escape_string($v) . "'";
+ $vals .= "'" . jBlog_db_escape_string($v) . "'";
}
return jBlog_db_query("INSERT INTO $jBlog[dbPrefix]$table ($names) values ($vals)");
}
Index: jBlog_db_mysql.inc.php
===================================================================
RCS file: /cvsroot/php-blog/jBlog/jBlog_db_mysql.inc.php,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -d -r1.4 -r1.5
--- jBlog_db_mysql.inc.php 13 Mar 2003 18:48:45 -0000 1.4
+++ jBlog_db_mysql.inc.php 14 Mar 2003 02:38:05 -0000 1.5
@@ -15,7 +15,9 @@
'num' => MYSQL_NUM,
'both' => MYSQL_BOTH
);
-
+
+ //highlight_string(var_export($sql, 1));
+
$c = mysql_db_query($jBlog['dbName'], $sql);
if (!$c) {
print mysql_error();
@@ -64,6 +66,11 @@
return mysql_escape_string($string);
}
+function jBlog_db_limit($start, $offset)
+{
+ return "$start, $offset";
+}
+
function jBlog_db_connect()
{
global $jBlog;
@@ -74,8 +81,8 @@
function jBlog_db_schema_import($query)
{
- static $search = array('{AUTOINCREMENT}');
- static $replace = array('int(11) not null auto_increment primary key');
+ static $search = array('{AUTOINCREMENT}', '{UNSIGNED}', '{FULLTEXT}');
+ static $replace = array('int(11) not null auto_increment primary key', 'unsigned', 'FULLTEXT');
return jBlog_db_query(str_replace($search, $replace, $query));
}
Index: jBlog_db_sqlite.inc.php
===================================================================
RCS file: /cvsroot/php-blog/jBlog/jBlog_db_sqlite.inc.php,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -d -r1.1 -r1.2
--- jBlog_db_sqlite.inc.php 13 Mar 2003 18:37:07 -0000 1.1
+++ jBlog_db_sqlite.inc.php 14 Mar 2003 02:38:05 -0000 1.2
@@ -9,10 +9,10 @@
function jBlog_db_escape_string($string)
{
- static $replace = array('%00', '%25');
- static $search = array("\x00", '%');
+ static $search = array("\x00", '%', '\'', '\"');
+ static $replace = array('%00', '%25', '\\\'', '\\\"');
- return addslashes(str_replace($search, $replace, $string));
+ return str_replace($search, $replace, $string);
}
function jBlog_db_affected_rows()
@@ -46,8 +46,13 @@
global $jBlog;
$res = sqlite_exec($sql, $jBlog['dbConn']);
-
if (!$res) {
+// if (function_exists("debug_backtrace")) {
+// highlight_string(var_export(debug_backtrace(), 1));
+// }
+ var_dump($res);
+ var_dump($sql);
+
return "problem with query";
}
@@ -100,7 +105,7 @@
global $jBlog;
$jBlog['dbConn'] = sqlite_open($jBlog['jBlogPath'] . "sqlite.db");
- if (is_resource($jBlog['dbConn'])) {
+ if ($jBlog['dbConn']) {
return true;
}
$errs[] = "Unable to open \"$jBlog[jBlogPath]sqlite.db\" - check permissions!";
@@ -109,8 +114,8 @@
function jBlog_db_schema_import($query)
{
- static $search = array('{AUTOINCREMENT}');
- static $replace = array('INTEGER PRIMARY KEY');
+ static $search = array('{AUTOINCREMENT}', '{UNSIGNED}', '{FULLTEXT}');
+ static $replace = array('INTEGER PRIMARY KEY', '', '');
return jBlog_db_query(str_replace($search, $replace, $query));
Index: jBlog_functions.inc.php
===================================================================
RCS file: /cvsroot/php-blog/jBlog/jBlog_functions.inc.php,v
retrieving revision 1.67
retrieving revision 1.68
diff -u -d -r1.67 -r1.68
--- jBlog_functions.inc.php 13 Mar 2003 22:18:23 -0000 1.67
+++ jBlog_functions.inc.php 14 Mar 2003 02:38:05 -0000 1.68
@@ -89,18 +89,21 @@
if ($month <12) {
$nextMonth = $month+1;
- $nextYear = $year;}
- else {
+ $nextYear = $year;
+ } else {
$nextMonth = 1;
- $nextYear = $year+1;}
+ $nextYear = $year+1;
+ }
+
+ $endts = mktime(0, 0, 0, $month + 1, 1, $year);
// Find out about diary entries
- $querystring = "SELECT FROM_UNIXTIME(timestamp, '%e') as day, timestamp FROM ".$jBlog["dbPrefix"]."entries WHERE FROM_UNIXTIME(timestamp,'%c') LIKE $month AND FROM_UNIXTIME(timestamp,'%Y') LIKE $year";
+ $querystring = "SELECT timestamp from $jBlog[dbPrefix]entries WHERE timestamp >= $ts and timestamp <= $endts";
$rows = jBlog_db_query($querystring);
$activeDays = array();
if (is_array($rows)) {
foreach ($rows as $row) {
- $activeDays[$row["day"]] = $row["timestamp"];
+ $activeDays[date("j", $row["timestamp"])] = $row["timestamp"];
}
}
@@ -185,15 +188,25 @@
if ($full === true)
$body = ',body, extended';
- if (is_numeric($range)) {
- $and = "AND FROM_UNIXTIME(timestamp, '%Y%m%d') LIKE '$range%' ";
- }
+ if (is_numeric($range)) {
+ $year = substr($range, 0, 4);
+ $month = substr($range, 4, 2);
+ $day = substr($range, 6, 2);
+
+ $startts = mktime(0, 0, 0, $month, $day, $year);
+ $endts = mktime(0, 0, 0, $month, $day+1, $year);
+
+ $and = " AND timestamp >= $startts AND timestamp <= $endts ";
+ }
if (!empty($limit)) {
$limit = "LIMIT $limit";
}
- $query = "SELECT e.*, a.username, c.category_name FROM {$jBlog['dbPrefix']}entries e, {$jBlog['dbPrefix']}authors a left join {$jBlog['dbPrefix']}category c ON e.categoryid = c.categoryid WHERE e.authorid = a.authorid " . $and .
+ $query = "SELECT e.*, a.username, c.category_name FROM
+ ({$jBlog['dbPrefix']}entries AS e LEFT JOIN {$jBlog['dbPrefix']}category c ON e.categoryid = c.categoryid)
+ LEFT JOIN {$jBlog['dbPrefix']}authors a ON
+ e.authorid = a.authorid " . $and .
"ORDER BY timestamp DESC $limit";
$ret = jBlog_db_query($query);
if (is_string($ret)) {
@@ -814,35 +827,40 @@
$newEntry = 0;
$exflag = 0;
-
- if (!is_numeric($entry["id"])) $entry["id"] = "NULL";
- if (!is_numeric($entry["timestamp"])) $entry["timestamp"] = time();
+
+ if (!is_numeric($entry["timestamp"])) {
+ $entry["timestamp"] = time();
+ }
+
if(strlen($entry["extended"])) {
$exflag = 1;
}
- $querystring = "REPLACE INTO ".$jBlog["dbPrefix"]."entries
- (id, title, timestamp, body, extended, comments, exflag, author, authorid, categoryid)
- VALUES (
- ".$entry["id"].",
- '".jBlog_db_escape_string($entry["title"])."',
- ".$entry["timestamp"].",
- '".jBlog_db_escape_string($entry["body"])."',
- '".jBlog_db_escape_string($entry["extended"])."',
- '0', '$exflag',
- '".jBlog_db_escape_string($jBlog["user"])."',
- '".$jBlog["authorid"]."'," .
- (int)$entry['categoryid'] .
- ")";
- $query = jBlog_db_query($querystring);
- if (is_string($query)) {
- return $query;
- }
+ $entry['exflag'] = $exflag;
+ $entry['author'] = $jBlog['user'];
+ $entry['authorid'] = $jBlog['authorid'];
+
+ if (!is_numeric($entry["id"])) {
+ /* we need to insert */
+
+ unset($entry['id']);
+ $entry['comments'] = 0;
+
+ $res = jBlog_db_insert("entries", $entry);
- if ($entry['id'] == "NULL") {
+ $entry['id'] = jBlog_db_insert_id();
$newEntry = 1;
- $entry['id'] = jBlog_db_insert_id();
- }
+ } else {
+ /* we need to update */
+
+ $res = jBlog_db_update("entries", array('id'=>$entry['id']), $entry);
+ $newEntry = 0;
+ }
+
+ if (is_string($res)) {
+ return $res;
+ }
+
if($exflag) {
jBlog_handle_references($entry['id'], $jBlog['blogTitle'], $entry['title'], $entry['extended'], $newEntry);
}
@@ -912,22 +930,21 @@
$newEntry = 0;
$exflag = 0;
- if (!is_numeric($css["cssid"])) $css["cssid"] = "NULL";
- $querystring = "REPLACE INTO ".$jBlog["dbPrefix"]."css
- (cssid, name, data)
- VALUES (
- ".$css["cssid"].",
- '".jBlog_db_escape_string($css["name"])."',
- '".jBlog_db_escape_string($css["data"])."')";
- $query = jBlog_db_query($querystring);
- if (is_string($query)) {
- return $query;
+
+ if ($css["cssid"] == 0) {
+
+ unset($css['cssid']);
+ $res = jBlog_db_insert('css', $css);
+ $newEntry = 1;
+ $css['cssid'] = jBlog_db_insert_id();
+ } else {
+ $res = jBlog_db_update('css', array('cssid' => $css['cssid']), $css);
}
- if ($css['cssid'] == "NULL") {
- $newEntry = 1;
- $css['cssid'] = jBlog_db_insert_id();
- }
- return $css['cssid'];
+
+ if (is_string($res)) {
+ return $res;
+ }
+ return (int)$css['cssid'];
}
/**
@@ -1330,13 +1347,14 @@
$jBlog['authorid'] = $_SESSION['jBlogAuthorid'];
return 1;
}
+ $password = md5($password);
$query = "SELECT
*
FROM
$jBlog[dbPrefix]authors
WHERE
username = '$username'
- AND password = PASSWORD('$password')";
+ AND password = '$password'";
$row = jBlog_db_query($query, true);
if (is_string($row)) {
print $row;
@@ -1366,7 +1384,8 @@
WHERE ip = '$_SERVER[REMOTE_ADDR]'
AND url = '$_SERVER[HTTP_REFERER]'
AND last > now() - 900";
- $suppressu = "REPLACE INTO $jBlog[dbPrefix]suppress
+ $suppressp = "DELETE FROM $jBlog[dbPrefix]suppress where url='$_SERVER[HTTP_REFERER]' and ip='$_SERVER[REMOTE_ADDR]'";
+ $suppressu = "INSERT INTO $jBlog[dbPrefix]suppress
(url, ip, last)
VALUES ('$_SERVER[HTTP_REFERER]', '$_SERVER[REMOTE_ADDR]', now())";
$update = "UPDATE $jBlog[dbPrefix]referrers
@@ -1381,6 +1400,7 @@
jBlog_db_query($suppressu);
return;
}
+ jBlog_db_query($suppressp);
jBlog_db_query($suppressu);
jBlog_db_query($update);
if (jBlog_db_affected_rows() == 0) {
@@ -1397,7 +1417,7 @@
function jBlog_displayTopReferrers($limit=10) {
global $jBlog;
- $query = "SELECT url, sum(count) total
+ $query = "SELECT url, sum(count) AS total
FROM $jBlog[dbPrefix]referrers
GROUP BY url
ORDER BY total desc
@@ -1415,8 +1435,9 @@
function jBlog_set_config_var($name, $val) {
global $jBlog;
- $val = jBlog_db_escape_string($val);
- $r = jBlog_db_query("REPLACE INTO $jBlog[dbPrefix]config (name,value) values('$name','$val')");
+
+ jBlog_db_query("DELETE FROM $jBlog[dbPrefix]config where name='" . jBlog_db_escape_string($name) . "'");
+ $r = jBlog_db_insert("config", array("name"=>$name, "value"=>$val));
if (is_string($r)) {
echo $r;
}
|
