Menu
▾
▴
phpwiki-checkins
|
From: Geoffrey T. D. <da...@us...> - 2001-11-17 00:49:31
|
Update of /cvsroot/phpwiki/phpwiki/lib/WikiDB/backend
In directory usw-pr-cvs1:/tmp/cvs-serv24928/lib/WikiDB/backend
Modified Files:
PearDB.php mysql.php
Log Message:
For maximum portability, use old fashioned comma join instead of
"INNER JOIN ... USING(...)" and "INNER JOIN ... ON ...".
Fix for older MySQL's which don't support the "CASE WHEN ... END"
syntax. The fix is complicated by the fact that Postgres doesn't
support MySQL's "IF(,,)" syntax.
Index: PearDB.php
===================================================================
RCS file: /cvsroot/phpwiki/phpwiki/lib/WikiDB/backend/PearDB.php,v
retrieving revision 1.8
retrieving revision 1.9
diff -C2 -r1.8 -r1.9
*** PearDB.php 2001/11/07 21:12:47 1.8
--- PearDB.php 2001/11/17 00:49:29 1.9
***************
*** 41,44 ****
--- 41,49 ----
'nonempty_tbl' => $prefix . 'nonempty');
+ $this->_expressions
+ = array('maxmajor' => "MAX(CASE WHEN minor_edit=0 THEN version END)",
+ 'maxminor' => "MAX(CASE WHEN minor_edit<>0 THEN version END)",
+ 'maxversion' => "MAX(version)");
+
$this->_lock_count = 0;
}
***************
*** 70,75 ****
extract($this->_table_names);
return $dbh->getOne(sprintf("SELECT $page_tbl.id"
! . " FROM $nonempty_tbl INNER JOIN $page_tbl USING(id)"
! . " WHERE pagename='%s'",
$dbh->quoteString($pagename)));
}
--- 75,81 ----
extract($this->_table_names);
return $dbh->getOne(sprintf("SELECT $page_tbl.id"
! . " FROM $nonempty_tbl, $page_tbl"
! . " WHERE $nonempty_tbl.id=$page_tbl.id"
! . " AND pagename='%s'",
$dbh->quoteString($pagename)));
}
***************
*** 79,83 ****
extract($this->_table_names);
return $dbh->getCol("SELECT pagename"
! . " FROM $nonempty_tbl INNER JOIN $page_tbl USING(id)");
}
--- 85,90 ----
extract($this->_table_names);
return $dbh->getCol("SELECT pagename"
! . " FROM $nonempty_tbl, $page_tbl"
! . " WHERE $nonempty_tbl.id=$page_tbl.id");
}
***************
*** 179,185 ****
return
(int)$dbh->getOne(sprintf("SELECT latestversion"
! . " FROM $page_tbl"
! . " INNER JOIN $recent_tbl USING(id)"
! . " WHERE pagename='%s'",
$dbh->quoteString($pagename)));
}
--- 186,192 ----
return
(int)$dbh->getOne(sprintf("SELECT latestversion"
! . " FROM $page_tbl, $recent_tbl"
! . " WHERE $page_tbl.id=$recent_tbl.id"
! . " AND pagename='%s'",
$dbh->quoteString($pagename)));
}
***************
*** 191,197 ****
return
(int)$dbh->getOne(sprintf("SELECT version"
! . " FROM $version_tbl"
! . " INNER JOIN $page_tbl USING(id)"
! . " WHERE pagename='%s'"
. " AND version < %d"
. " ORDER BY version DESC"
--- 198,204 ----
return
(int)$dbh->getOne(sprintf("SELECT version"
! . " FROM $version_tbl, $page_tbl"
! . " WHERE $version_tbl.id=$page_tbl.id"
! . " AND pagename='%s'"
. " AND version < %d"
. " ORDER BY version DESC"
***************
*** 229,235 ****
$result = $dbh->getRow(sprintf("SELECT $fields"
! . " FROM $page_tbl"
! . " INNER JOIN $version_tbl USING(id)"
! . " WHERE pagename='%s' AND version=%d",
$dbh->quoteString($pagename), $version),
DB_FETCHMODE_ASSOC);
--- 236,243 ----
$result = $dbh->getRow(sprintf("SELECT $fields"
! . " FROM $page_tbl, $version_tbl"
! . " WHERE $page_tbl.id=$version_tbl.id"
! . " AND pagename='%s'"
! . " AND version=%d",
$dbh->quoteString($pagename), $version),
DB_FETCHMODE_ASSOC);
***************
*** 400,407 ****
$result = $dbh->query("SELECT $want.*"
! . " FROM $link_tbl"
! . " INNER JOIN $page_tbl AS linker ON linkfrom=linker.id"
! . " INNER JOIN $page_tbl AS linkee ON linkto=linkee.id"
! . " WHERE $have.pagename='$qpagename'"
//. " GROUP BY $want.id"
. " ORDER BY $want.pagename",
--- 408,414 ----
$result = $dbh->query("SELECT $want.*"
! . " FROM $link_tbl, $page_tbl AS linker, $page_tbl AS linkee"
! . " WHERE linkfrom=linker.id AND linkto=linkee.id"
! . " AND $have.pagename='$qpagename'"
//. " GROUP BY $want.id"
. " ORDER BY $want.pagename",
***************
*** 420,424 ****
else {
$result = $dbh->query("SELECT $page_tbl.*"
! . " FROM $nonempty_tbl INNER JOIN $page_tbl USING(id)"
. " ORDER BY pagename");
}
--- 427,432 ----
else {
$result = $dbh->query("SELECT $page_tbl.*"
! . " FROM $nonempty_tbl $page_tbl"
! . " WHERE $nonempty_tbl.id=$page_tbl.id"
. " ORDER BY pagename");
}
***************
*** 434,446 ****
extract($this->_table_names);
! $table = "$nonempty_tbl INNER JOIN $page_tbl USING(id)";
$fields = "$page_tbl.*";
$callback = '_sql_match_clause';
if ($fullsearch) {
! $table .= (" INNER JOIN $recent_tbl ON $page_tbl.id=$recent_tbl.id"
! . " INNER JOIN $version_tbl"
! . " ON $page_tbl.id=$version_tbl.id"
! . " AND latestversion=version" );
$fields .= ",$version_tbl.*";
$callback = '_fullsearch_sql_match_clause';
--- 442,457 ----
extract($this->_table_names);
! $table = "$nonempty_tbl, $page_tbl";
! $join_clause = "$nonempty_tbl.id=$page_tbl.id";
$fields = "$page_tbl.*";
$callback = '_sql_match_clause';
if ($fullsearch) {
! $table .= ", $recent_tbl,";
! $join_clause .= " AND $page_tbl.id=$recent_tbl.id";
!
! $table .= ", $version_tbl";
! $join_clause .= " AND $page_tbl.id=$version_tbl.id AND latestversion=version";
!
$fields .= ",$version_tbl.*";
$callback = '_fullsearch_sql_match_clause';
***************
*** 451,455 ****
$result = $dbh->query("SELECT $fields FROM $table"
! . " WHERE $search_clause"
. " ORDER BY pagename");
--- 462,467 ----
$result = $dbh->query("SELECT $fields FROM $table"
! . " WHERE $join_clause"
! . " AND ($search_clause)"
. " ORDER BY pagename");
***************
*** 478,482 ****
$limitclause = $limit ? " LIMIT $limit" : '';
$result = $dbh->query("SELECT $page_tbl.*"
! . " FROM $nonempty_tbl INNER JOIN $page_tbl USING(id)"
. " ORDER BY hits DESC"
. " $limitclause");
--- 490,495 ----
$limitclause = $limit ? " LIMIT $limit" : '';
$result = $dbh->query("SELECT $page_tbl.*"
! . " FROM $nonempty_tbl, $page_tbl"
! . " WHERE $nonempty_tbl.id=$page_tbl.id"
. " ORDER BY hits DESC"
. " $limitclause");
***************
*** 505,509 ****
if ($include_all_revisions) {
// Include all revisions of each page.
! $table = "$page_tbl INNER JOIN $version_tbl USING(id)";
if ($exclude_major_revisions) {
--- 518,523 ----
if ($include_all_revisions) {
// Include all revisions of each page.
! $table = "$page_tbl, $version_tbl";
! $join_clause = "$page_tbl.id=$version_tbl.id";
if ($exclude_major_revisions) {
***************
*** 517,522 ****
}
else {
! $table = ( "$page_tbl INNER JOIN $recent_tbl USING(id)"
! . " INNER JOIN $version_tbl ON $version_tbl.id=$page_tbl.id");
if ($exclude_major_revisions) {
--- 531,538 ----
}
else {
! $table = "$page_tbl, $recent_tbl";
! $join_clause = "$page_tbl.id=$recent_tbl.id";
! $table .= ", $version_tbl";
! $join_clause .= " AND $version_tbl.id=$page_tbl.id";
if ($exclude_major_revisions) {
***************
*** 535,544 ****
$limitclause = $limit ? " LIMIT $limit" : '';
! $whereclause = $pick ? " WHERE " . join(" AND ", $pick) : '';
// FIXME: use SQL_BUFFER_RESULT for mysql?
$result = $dbh->query("SELECT $page_tbl.*,$version_tbl.*"
. " FROM $table"
! . $whereclause
. " ORDER BY mtime DESC"
. $limitclause);
--- 551,562 ----
$limitclause = $limit ? " LIMIT $limit" : '';
! $where_clause = $join_clause;
! if ($pick)
! $where_clause .= " AND " . join(" AND ", $pick);
// FIXME: use SQL_BUFFER_RESULT for mysql?
$result = $dbh->query("SELECT $page_tbl.*,$version_tbl.*"
. " FROM $table"
! . " WHERE $where_clause"
. " ORDER BY mtime DESC"
. $limitclause);
***************
*** 550,557 ****
$dbh = &$this->_dbh;
extract($this->_table_names);
!
! $maxmajor = "MAX(CASE WHEN minor_edit=0 THEN version END)";
! $maxminor = "MAX(CASE WHEN minor_edit<>0 THEN version END)";
! $maxversion = "MAX(version)";
$pageid = (int)$pageid;
--- 568,572 ----
$dbh = &$this->_dbh;
extract($this->_table_names);
! extract($this->_expressions);
$pageid = (int)$pageid;
***************
*** 584,591 ****
$dbh->query("INSERT INTO $nonempty_tbl (id)"
. " SELECT $recent_tbl.id"
! . " FROM $recent_tbl INNER JOIN $version_tbl"
! . " ON $recent_tbl.id=$version_tbl.id"
! . " AND version=latestversion"
! . " WHERE content<>''"
. ( $pageid ? " AND $recent_tbl.id=$pageid" : ""));
--- 599,606 ----
$dbh->query("INSERT INTO $nonempty_tbl (id)"
. " SELECT $recent_tbl.id"
! . " FROM $recent_tbl, $version_tbl"
! . " WHERE $recent_tbl.id=$version_tbl.id"
! . " AND version=latestversion"
! . " AND content<>''"
. ( $pageid ? " AND $recent_tbl.id=$pageid" : ""));
Index: mysql.php
===================================================================
RCS file: /cvsroot/phpwiki/phpwiki/lib/WikiDB/backend/mysql.php,v
retrieving revision 1.1
retrieving revision 1.2
diff -C2 -r1.1 -r1.2
*** mysql.php 2001/09/18 19:16:23 1.1
--- mysql.php 2001/11/17 00:49:29 1.2
***************
*** 8,11 ****
--- 8,22 ----
{
/**
+ * Constructor.
+ */
+ function WikiDB_backend_mysql($dbparams) {
+ $this->WikiDB_backend_PearDB($dbparams);
+
+ // Older MySQL's don't have CASE WHEN ... END
+ $this->_expressions['maxmajor'] = "MAX(IF(minor_edit=0,version,NULL))";
+ $this->_expressions['maxminor'] = "MAX(IF(minor_edit<>0,version,NULL))";
+ }
+
+ /**
* Pack tables.
*/
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X