Textpattern CMS

#1 2006-01-28 21:39:49

plw

New Member

Registered: 2006-01-28

Posts: 3

Some PostgreSQL fixes

Textpattern from SVN vs. PostgreSQL

After some glitches on the installation (psql did not like most of the update scripts, sorry didn’t record the logs) and some patching here and there it now
runs flawlessly on the psql backend.

Diffs which made it work here (I did not remove the comments in the code for reference):
These patches are just geared towards making it on psql work and need more polishing, but
at least they show a way how it works on psql.

[I’ve put the diffs on <a href=“http://doorslam.net/txp-diffs_280106.txt”>http://doorslam.net/txp-diffs_280106.txt</a> for proper viewing.]

<pre> Index: textpattern/update/_to_4.1.0.php =============================================================== —- textpattern/update/_to_4.1.0.php (revision 1228) +++ textpattern/update/_to_4.1.0.php (working copy) -14,13 +14,14 // user-specific preferences safe_upgrade_table(‘txp_prefs_user’, array( ‘id’ => DB_AUTOINC.’ PRIMARY KEY’, - ‘user’ => “varchar(64) NOT NULL default ‘’”, - ‘name’ => “varchar(255) NOT NULL default ‘’”, + ‘name’ => “varchar(64) NOT NULL default ‘’”, +// delete user as its reserved SQL, just use name for it ‘val’ => “varchar(255) NOT NULL default ‘’”, ));

+// delete user as its reserved SQL // unique index on user+name -safe_upgrade_index(‘txp_prefs_user’, ‘user_idx’, ‘unique’, ‘user, name’); +safe_upgrade_index(‘txp_prefs_user’, ‘user_idx’, ‘unique’, ‘name’); safe_upgrade_table(‘txp_element’, array( ‘name’ => ‘varchar(64) NOT NULL PRIMARY KEY’, </pre>

<pre>
Index: textpattern/include/txp_diag.php
===============================================================
—- textpattern/include/txp_diag.php (revision 1228)
+++ textpattern/include/txp_diag.php (working copy)
-183,7 +183,8 );

if ($permlink_mode != ‘messy’) { - $rs = safe_column(“name”,“txp_section”, “1”); + // XXX plw psql doesnt like 1 + $rs = safe_column(“name”,“txp_section”, “true”); foreach ($rs as $name) { if ($name and file_exists($path_to_site.'/'.$name)) $fail['old_placeholder_exists'] = gTxt('old_placeholder').": {$path_to_site}/{$name}"; @ -270,7 +271,8 gTxt(‘server_time’).cs.strftime(‘%Y-%m-%d %H:%M:%S’).n,

- ‘MySQL’.cs.mysql_get_server_info().n,
+ // ‘MySQL’.cs.mysql_get_server_info().n,
+ // XXX plw sometimes does not run on MySQL

gTxt(‘locale’).cs.$locale.n, </pre>

<pre>
Index: textpattern/include/txp_list.php
===============================================================
—- textpattern/include/txp_list.php (revision 1228)
+++ textpattern/include/txp_list.php (working copy)
-63,13 +63,15 ); $criteria = $critsql[$method]; $limit = 500;
- } else $criteria = 1;
+ } else $criteria = ‘true’;

$rs = safe_rows_start( “*, unix_timestamp(Posted) as uPosted”, “textpattern”, - “$criteria order by $sort $dir limit $offset, $limit” + // “$criteria order by $sort $dir limit $offset, $limit” + // XXX plw: psql likes separate limit/offset + “$criteria order by $sort $dir limit $limit offset $offset” ); echo (!$crit) ? list_nav_form($page,$numPages,$sort,$dir) : ‘’, list_searching_form($crit,$method); </pre>

<pre>
Index: textpattern/include/txp_log.php
===============================================================
—- textpattern/include/txp_log.php (revision 1228)
+++ textpattern/include/txp_log.php (working copy)
-33,12 +33,14 pagetop(gTxt(‘visitor_logs’)); extract(get_prefs());

- safe_delete(“txp_log”, “time < date_sub(now(),interval “.
- $expire_logs_after.” day)”);
+ // XXX plw: psql can (and has to!) do this without date_sub
+ safe_delete(“txp_log”, “time < now() – interval ‘”.
+ $expire_logs_after.” day’”);

$page = gps(‘page’);

- $total = getCount(‘txp_log’,“1”);
+ // XXX plw: true vs 1
+ $total = getCount(‘txp_log’,“true”); $limit = 50; $numPages = ceil($total/$limit); $page = (!$page) ? 1 : $page;
</pre>

The following is strange as the original version set markup_body to markup_default which was
nowhere defined in the source tree. This resulted in <tt>txprawxhtml</tt> being the standard
<b>markup_type</b> on composing articles, which in turn confused users who had set use_textile
in their prefs but apparently could not get it working. The below patches the behaviour to default
to txp_textile, though I guess this just needs some more glue to other code in the tree which might
set <b>markup_default</b> correctly wrt. to <b>use_textile</b> in the prefs.
<pre>
Index: textpattern/include/txp_article.php
===============================================================
—- textpattern/include/txp_article.php (revision 1228)
+++ textpattern/include/txp_article.php (working copy)
-32,6 +32,9 5 => gTxt(‘sticky’), );

+// plw markup default as txptextile
+$markup_default = ‘txptextile’;
+ if (!defined(‘LEAVE_TEXT_UNTOUCHED’)) define(‘LEAVE_TEXT_UNTOUCHED’, 0); if (!defined(‘USE_TEXTILE’)) define(‘USE_TEXTILE’, 1); if (!defined(‘CONVERT_LINEBREAKS’)) define(‘CONVERT_LINEBREAKS’, 2);
-226,7 +229,7 //——————————————————————————————— function article_edit($message=”“) {
- global $txpcfg,$txp_user,$vars;
+ global $txpcfg,$txp_user,$vars,$markup_default;

extract(get_prefs()); extract(gpsa(array(‘view’,‘from_view’,‘step’))); -287,8 +290,9 $GLOBALS[‘step’] = $step; if ($step==‘create’) { - $markup_body = $markup_default; - $markup_excerpt = $markup_default; + // plw convert markup_default + $markup_body = $markup_default; + $markup_excerpt = $markup_default; } if ($step!=‘create’) { </pre>

<pre>
Index: textpattern/publish.php
===============================================================
—- textpattern/publish.php (revision 1228)
+++ textpattern/publish.php (working copy)
-504,7 +504,8 foreach ($words as $w) { $rlike[] = “ and (Title “.db_rlike().“ ‘“.doSlash(preg_quote($w)).”’ or Body “.db_rlike().” ‘”.doSlash(preg_quote($w)).”’)”; }
- $search = “ and “ . join(’ and ‘, $rlike) . “ $s_filter”;
+ // XXX plw: there was an ‘and’ too much it seems
+ $search = join(’ and ‘, $rlike) . “ $s_filter”;

// searchall=0 can be used to show search results for the current section only if ($searchall) $section = ‘’; -844,7 +845,8 // ——————————————————————————————- function lastMod() { - $last = safe_field(“unix_timestamp(val)”, “txp_prefs”, “name=‘lastmod’ and prefs_id=1”); + // XXX plw: psql wants explicit conversion to timestamp + $last = safe_field(“unix_timestamp(val::timestamp without time zone)”, “txp_prefs”, “name=‘lastmod’ and prefs_id=1”); return gmdate(“D, d M Y H:i:s \G\M\T”,$last); } </pre>

<pre>
Index: textpattern/lib/txplib_prefs.php
===============================================================
—- textpattern/lib/txplib_prefs.php (revision 1228)
+++ textpattern/lib/txplib_prefs.php (working copy)
-27,7 +27,8 global $txp_user; if (empty($txp_user)) return array();

- $r = safe_rows_start(‘name, val’, ‘txp_prefs_user’, “user=’”.doSlash($txp_user).”’”);
+ // XXX plw: user is a reserved SQL word, use name
+ $r = safe_rows_start(‘name, val’, ‘txp_prefs_user’, “name=’”.doSlash($txp_user).”’”); if ($r) { $out = array(); while ($a = nextRow($r)) {
-46,6 +47,7

if (empty($prefs[$name]) or $prefs[$name] != $val) { $GLOBALS[$name] = $prefs[$name] = $val; + // XXX plw: same, use name instead of user return safe_upsert(‘txp_prefs’, “val=’”.doSlash($val).”’”, array(‘prefs_id=1’, “name=’”.doSlash($name).”’”)); } return true; -61,7 +63,7 if (empty($prefs[$name]) or $prefs[$name] != $val) { $GLOBALS[$name] = $prefs[$name] = $val; - return safe_upsert(‘txp_prefs_user’, “val=’”.doSlash($val).”’”, array(“user=’”.doSlash($txp_user).”’”, “name=’”.doSlash($name).”’”)); + return safe_upsert(‘txp_prefs_user’, “val=’”.doSlash($val).”’”, array(“name=’”.doSlash($txp_user).”’”, “name=’”.doSlash($name).”’”)); } return true; } </pre>

There must’ve been an error somewhere as there is just no relation <b>oid</b> anywhere in the db. I know the fix
is bogus, but just for reference with oid it did not work.

<pre>
Index: textpattern/lib/mdb/pg.php
===============================================================
—- textpattern/lib/mdb/pg.php (revision 1228)
+++ textpattern/lib/mdb/pg.php (working copy)
-146,7 +146,9

function db_match($cols, $against) {

- return ‘oid as score’;
+ // return ‘oid as score’;
+ // pw this is crap but works w/ uid
+ return ‘uid as score’; }

function db_rlike() { </pre>

<pre>
Index: textpattern/publish/taghandlers.php
===============================================================
—- textpattern/publish/taghandlers.php (revision 1228)
+++ textpattern/publish/taghandlers.php (working copy)
-298,7 +298,8 $rs = safe_rows_start( “*, id as thisid, unix_timestamp(Posted) as posted”, “textpattern”,
- “Status = 4 and Posted <= now() $catq order by $sortby $sortdir limit 0,$limit”
+ // XXX plw: psql likes separate offset/limit
+ “Status = 4 and Posted <= now() $catq order by $sortby $sortdir limit $limit offset 0” );

if ($rs) { -325,7 +326,8 ‘labeltag’ => ‘’ ),$atts));

- $rs = safe_rows_start(“*”,‘txp_discuss’,“visible=1 order by posted desc limit 0,$limit”);
+ // XXX plw: psql likes separate offset/limit
+ $rs = safe_rows_start(“*”,‘txp_discuss’,“visible=1 order by posted desc limit $limit offset 0”);

if ($rs) { while ($a = nextRow($rs)) { -368,7 +370,8 $q = array(“select *, id as thisid, unix_timestamp(Posted) as posted from “.PFX.“textpattern where Status=4”, ($cat_condition) ? “and (“. $cat_condition. “)” :’‘, - “and Posted <= now() order by Posted desc limit 0,$limit”); + // XXX plw: psql likes separate offset/limit + “and Posted <= now() order by Posted desc limit $limit offset 0”); $rs = getRows(join(’ ‘,$q));

</pre>

<pre>
Index: textpattern/publish/atom.php
===============================================================
—- textpattern/publish/atom.php (revision 1228)
+++ textpattern/publish/atom.php (working copy)
-28,7 +28,7 $area = doSlash(gps(‘area’)); extract(doSlash(gpsa(array(‘category’,‘section’,‘limit’))));

- $last = fetch(‘unix_timestamp(val)’,‘txp_prefs’,‘name’,‘lastmod’);
+ $last = fetch(‘unix_timestamp(val::timestamp without time zone)’,‘txp_prefs’,‘name’,‘lastmod’);

$sitename .= ($section) ? ‘ – ‘.$section : ‘’; $sitename .= ($category) ? ‘ – ‘.$category : ‘’; </pre>

<pre>
Index: textpattern/publish/comment.php
===============================================================
—- textpattern/publish/comment.php (revision 1228)
+++ textpattern/publish/comment.php (working copy)
-327,7 +327,9 if (!$nonce && !preg_match(‘#^[a-zA-Z0-9]*$#’,$nonce)) return false; // delete expired nonces
- safe_delete(“txp_discuss_nonce”, “issue_time < date_sub(now(),interval 10 minute)”);
+ // safe_delete(“txp_discuss_nonce”, “issue_time < date_sub(now(),interval 10 minute)”);
+ // psql vs date_sub
+ safe_delete(“txp_discuss_nonce”, “issue_time < now() – interval 10 minute”); // check for nonce return (safe_row(“*”, “txp_discuss_nonce”, “nonce=’”.doslash($nonce).”’ and used=‘0’”)) ? true : false; }
</pre>

<pre>
Index: textpattern/publish/rss.php
===============================================================
—- textpattern/publish/rss.php (revision 1228)
+++ textpattern/publish/rss.php (working copy)
-120,7 +120,7 @ob_start(“ob_gzhandler”); }

- $last = fetch(‘unix_timestamp(val)’,‘txp_prefs’,‘name’,‘lastmod’);
+ $last = fetch(‘unix_timestamp(val::timestamp without time zone)’,‘txp_prefs’,‘name’,‘lastmod’); $expires = gmdate(‘D, d M Y H:i:s \G\M\T’, time()+(3600*1)); header(“Expires: $expires”); $hims = serverset(‘HTTP_IF_MODIFIED_SINCE’);
</pre>

hth,
I’m willing to test/help/fix more wrt. the psql backend.

paul