Menu
▾
▴
[phpwiki-checkins] SF.net SVN: phpwiki:[7485] trunk
|
From: <ru...@us...> - 2010-06-07 14:05:39
|
Revision: 7485
http://phpwiki.svn.sourceforge.net/phpwiki/?rev=7485&view=rev
Author: rurban
Date: 2010-06-07 14:05:29 +0000 (Mon, 07 Jun 2010)
Log Message:
-----------
Add mssqlnative support by chrisdpucci, patch 2910309
This patch adds native support for MS SQL Server 2005+ to phpWiki 1.3.14 using the Native SQL Server Driver for PHP.
More information regarding the Native SQL Server Driver for PHP can be found here:
http://www.microsoft.com/downloads/details.aspx?FamilyId=61BF87E0-D031-466B-B09A-6597C21A2E2A&displaylang=en
whitespace reformatted
Modified Paths:
--------------
trunk/configurator.php
Added Paths:
-----------
trunk/doc/INSTALL.mssqlnative
trunk/lib/WikiDB/adodb/drivers/adodb-mssqlnative.inc.php
trunk/lib/WikiDB/backend/ADODB_mssqlnative.php
trunk/schemas/sqlsrv-destroy.sql
trunk/schemas/sqlsrv-initialize.sql
Modified: trunk/configurator.php
===================================================================
--- trunk/configurator.php 2010-06-07 13:50:12 UTC (rev 7484)
+++ trunk/configurator.php 2010-06-07 14:05:29 UTC (rev 7485)
@@ -1,6 +1,6 @@
<?php // -*-php-*- $Id$
/*
- * Copyright 2002,2003,2005 $ThePhpWikiProgrammingTeam
+ * Copyright 2002,2003,2005,2008-2010 $ThePhpWikiProgrammingTeam
* Copyright 2002 Martin Geisler <gim...@gi...>
* Copyright 2008-2009 Marc-Etienne Vargenau, Alcatel-Lucent
*
@@ -173,7 +173,7 @@
pre { font-size: 120%; }
td { border: thin solid black }
tr { border: none }
-div.hint { background-color: #eeeeee; }
+div.hint { border: thin solid red, background-color: #eeeeee; }
tr.hidden { border: none; display: none; }
td.part { background-color: #eeeeee; color: inherit; }
td.instructions { background-color: #ffffee; width: <?php echo $tdwidth ?>px; color: inherit; }
@@ -634,12 +634,14 @@
array('mysql' => "MySQL",
'pgsql' => "PostgreSQL",
'mssql' => "Microsoft SQL Server",
+ 'mssqlnative' => "Microsoft SQL Server (native)",
'oci8' => "Oracle 8",
'mysqli' => "mysqli (only ADODB)",
'mysqlt' => "mysqlt (only ADODB)",
'ODBC' => "ODBC (only ADODB or PDO)",
'firebird' => "Firebird (only PDO)",
- 'oracle' => "Oracle (only PDO)"), "
+ 'oracle' => "Oracle (only PDO)",
+), "
SQL DB types. The DSN hosttype.");
$properties["SQL User"] =
@@ -2233,6 +2235,29 @@
}
}
+/*
+class _ini_set
+extends _variable {
+ function value() {
+ global $HTTP_POST_VARS;
+ if ($v = $HTTP_POST_VARS[$this->config_item_name])
+ return $v;
+ else {
+ return ini_get($this->get_config_item_name);
+ }
+ }
+ function _config_format($value) {
+ return sprintf("ini_set('%s', '%s');", $this->get_config_item_name(), $value);
+ }
+ function _get_config_line($posted_value) {
+ if ($posted_value && ! $posted_value == $this->default_value)
+ return "\n" . $this->_config_format($posted_value);
+ else
+ return "\n;" . $this->_config_format($this->default_value);
+ }
+}
+*/
+
class boolean_define
extends _define {
@@ -2487,7 +2512,7 @@
}
if ($fp) {
- fputs($fp, utf8_encode($config));
+ fputs($fp, $config);
fclose($fp);
echo "<p>The configuration was written to <code><b>$config_file</b></code>.</p>\n";
if ($new_filename) {
Added: trunk/doc/INSTALL.mssqlnative
===================================================================
--- trunk/doc/INSTALL.mssqlnative (rev 0)
+++ trunk/doc/INSTALL.mssqlnative 2010-06-07 14:05:29 UTC (rev 7485)
@@ -0,0 +1,50 @@
+Installing phpwiki with MS SQL Server Native Driver for ADOdb
+-----------------------------------------------------------
+
+Microsoft has been working hard to get support for their products
+into Open Source projects. The MS SQL Server 2005 Driver for ADOdb
+is one step in that direction. The following is instructions on how
+to get and install the new mssql php driver for ADOdb as well as
+how to get the new driver to work with phpWiki.
+
+As of the writing of this walkthrough, the latest version of the PHP
+driver for SQL Server 2005 is the May 2008 Community Technical Preview.
+If you don't already have a copy of the SQL Server 2005 for PHP
+driver installed on your server, you can get a copy at:
+http://www.microsoft.com/sql/technologies/php/default.mspx
+
+This assumes that you have PHP 5 installed as well as a working copy of
+any edition of SQL Server 2005 or SQL Server 2000 (including Express
+Edition). If you do not have a copy of the free SQL Server 2005 Express
+Edition you can download it from:
+http://go.microsoft.com/fwlink/?LinkId=64064
+
+1. If you do not have a database created already, create one using
+ the SQL Server Management tool. You can get the free Express version at:
+ http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-
+ 4E3D-94B8-5A0F62BF7796&displaylang=en
+
+2. If necessary create a user for that database which has the rights
+ to select, insert, update, delete. For more information on how to use
+ SQL Server 2005 you can download SQL Server 2005 Books Online at:
+ http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-
+ 4220-B133-29C1E0B6585F&displaylang=en
+
+3. Create the tables and functions inside your database by running the SQL
+ file included with the phpWiki project located at:
+ schemas/sqlsrv-initialize.sql
+
+4. Edit the DATABASE settings in config/config.ini to reflect your settings.
+
+ a) DATABASE_TYPE should be set to 'ADODB' (case sensitive)
+ b) DATABASE_DSN should be set to something like:
+ 'mssqlnative://username:password@pathtosqlserver/databasename'
+ c) Note that if you set DATABASE_PREFIX to a non-empty string, you will
+ have to edit schemas/sqlsrv-initialize.sql before you perform step
+ three (above). You might also edit schemas/sqlsrv-destroy.sql at the
+ same time, so you don't forget.
+ d) USE_SAFE_DBSESSION should be set to 'true'
+
+ Note: DATABASE_DIRECTORY and DATABASE_DBA_HANDLER are ignored for mssql.
+
+That's it. phpWiki should work now.
Added: trunk/lib/WikiDB/adodb/drivers/adodb-mssqlnative.inc.php
===================================================================
--- trunk/lib/WikiDB/adodb/drivers/adodb-mssqlnative.inc.php (rev 0)
+++ trunk/lib/WikiDB/adodb/drivers/adodb-mssqlnative.inc.php 2010-06-07 14:05:29 UTC (rev 7485)
@@ -0,0 +1,921 @@
+<?php
+/*
+V5.10 10 Nov 2009 (c) 2000-2009 John Lim (jlim#natsoft.com). All rights reserved.
+ Released under both BSD license and Lesser GPL library license.
+ Whenever there is any discrepancy between the two licenses,
+ the BSD license will take precedence.
+Set tabs to 4 for best viewing.
+
+ Latest version is available at http://adodb.sourceforge.net
+
+ Native mssql driver. Requires mssql client. Works on Windows.
+ http://www.microsoft.com/sql/technologies/php/default.mspx
+ To configure for Unix, see
+ http://phpbuilder.com/columns/alberto20000919.php3
+
+ $stream = sqlsrv_get_field($stmt, $index, SQLSRV_SQLTYPE_STREAM(SQLSRV_ENC_BINARY));
+ stream_filter_append($stream, "convert.iconv.ucs-2/utf-8"); // Voila, UTF-8 can be read directly from $stream
+
+*/
+// security - hide paths
+if (!defined('ADODB_DIR')) die();
+
+if (!function_exists('sqlsrv_configure')) {
+ die("mssqlnative extension not installed");
+}
+
+if (!function_exists('sqlsrv_set_error_handling')) {
+ function sqlsrv_set_error_handling($constant) {
+ sqlsrv_configure("WarningsReturnAsErrors", $constant);
+ }
+}
+if (!function_exists('sqlsrv_log_set_severity')) {
+ function sqlsrv_log_set_severity($constant) {
+ sqlsrv_configure("LogSeverity", $constant);
+ }
+}
+if (!function_exists('sqlsrv_log_set_subsystems')) {
+ function sqlsrv_log_set_subsystems($constant) {
+ sqlsrv_configure("LogSubsystems", $constant);
+ }
+}
+
+
+//----------------------------------------------------------------
+// MSSQL returns dates with the format Oct 13 2002 or 13 Oct 2002
+// and this causes tons of problems because localized versions of
+// MSSQL will return the dates in dmy or mdy order; and also the
+// month strings depends on what language has been configured. The
+// following two variables allow you to control the localization
+// settings - Ugh.
+//
+// MORE LOCALIZATION INFO
+// ----------------------
+// To configure datetime, look for and modify sqlcommn.loc,
+// typically found in c:\mssql\install
+// Also read :
+// http://support.microsoft.com/default.aspx?scid=kb;EN-US;q220918
+// Alternatively use:
+// CONVERT(char(12),datecol,120)
+//
+// Also if your month is showing as month-1,
+// e.g. Jan 13, 2002 is showing as 13/0/2002, then see
+// http://phplens.com/lens/lensforum/msgs.php?id=7048&x=1
+// it's a localisation problem.
+//----------------------------------------------------------------
+
+
+// has datetime converstion to YYYY-MM-DD format, and also mssql_fetch_assoc
+if (ADODB_PHPVER >= 0x4300) {
+// docs say 4.2.0, but testing shows only since 4.3.0 does it work!
+ ini_set('mssql.datetimeconvert',0);
+} else {
+ global $ADODB_mssql_mths; // array, months must be upper-case
+ $ADODB_mssql_date_order = 'mdy';
+ $ADODB_mssql_mths = array(
+ 'JAN'=>1,'FEB'=>2,'MAR'=>3,'APR'=>4,'MAY'=>5,'JUN'=>6,
+ 'JUL'=>7,'AUG'=>8,'SEP'=>9,'OCT'=>10,'NOV'=>11,'DEC'=>12);
+}
+
+//---------------------------------------------------------------------------
+// Call this to autoset $ADODB_mssql_date_order at the beginning of your code,
+// just after you connect to the database. Supports mdy and dmy only.
+// Not required for PHP 4.2.0 and above.
+function AutoDetect_MSSQL_Date_Order($conn)
+{
+ global $ADODB_mssql_date_order;
+ $adate = $conn->GetOne('select getdate()');
+ if ($adate) {
+ $anum = (int) $adate;
+ if ($anum > 0) {
+ if ($anum > 31) {
+ //ADOConnection::outp( "MSSQL: YYYY-MM-DD date format not supported currently");
+ } else
+ $ADODB_mssql_date_order = 'dmy';
+ } else
+ $ADODB_mssql_date_order = 'mdy';
+ }
+}
+
+class ADODB_mssqlnative extends ADOConnection {
+ var $databaseType = "mssqlnative";
+ var $dataProvider = "mssqlnative";
+ var $replaceQuote = "''"; // string to use to replace quotes
+ var $fmtDate = "'Y-m-d'";
+ var $fmtTimeStamp = "'Y-m-d H:i:s'";
+ var $hasInsertID = true;
+ var $substr = "substring";
+ var $length = 'len';
+ var $hasAffectedRows = true;
+ var $poorAffectedRows = false;
+ var $metaDatabasesSQL = "select name from sys.sysdatabases where name <> 'master'";
+ var $metaTablesSQL="select name,case when type='U' then 'T' else 'V' end from sysobjects where (type='U' or type='V') and (name not in ('sysallocations','syscolumns','syscomments','sysdepends','sysfilegroups','sysfiles','sysfiles1','sysforeignkeys','sysfulltextcatalogs','sysindexes','sysindexkeys','sysmembers','sysobjects','syspermissions','sysprotects','sysreferences','systypes','sysusers','sysalternates','sysconstraints','syssegments','REFERENTIAL_CONSTRAINTS','CHECK_CONSTRAINTS','CONSTRAINT_TABLE_USAGE','CONSTRAINT_COLUMN_USAGE','VIEWS','VIEW_TABLE_USAGE','VIEW_COLUMN_USAGE','SCHEMATA','TABLES','TABLE_CONSTRAINTS','TABLE_PRIVILEGES','COLUMNS','COLUMN_DOMAIN_USAGE','COLUMN_PRIVILEGES','DOMAINS','DOMAIN_CONSTRAINTS','KEY_COLUMN_USAGE','dtproperties'))";
+ var $metaColumnsSQL = # xtype==61 is datetime
+ "select c.name,t.name,c.length,
+ (case when c.xusertype=61 then 0 else c.xprec end),
+ (case when c.xusertype=61 then 0 else c.xscale end)
+ from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='%s'";
+ var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE
+ var $hasGenID = true;
+ var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)';
+ var $sysTimeStamp = 'GetDate()';
+ var $maxParameterLen = 4000;
+ var $arrayClass = 'ADORecordSet_array_mssqlnative';
+ var $uniqueSort = true;
+ var $leftOuter = '*=';
+ var $rightOuter = '=*';
+ var $ansiOuter = true; // for mssql7 or later
+ var $identitySQL = 'select SCOPE_IDENTITY()'; // 'select SCOPE_IDENTITY'; # for mssql 2000
+ var $uniqueOrderBy = true;
+ var $_bindInputArray = true;
+ var $_dropSeqSQL = "drop table %s";
+
+ function ADODB_mssqlnative()
+ {
+ if ($this->debug) {
+ error_log("<pre>");
+ sqlsrv_set_error_handling( SQLSRV_ERRORS_LOG_ALL );
+ sqlsrv_log_set_severity( SQLSRV_LOG_SEVERITY_ALL );
+ sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
+ sqlsrv_configure('warnings_return_as_errors', 0);
+ } else {
+ sqlsrv_set_error_handling(0);
+ sqlsrv_log_set_severity(0);
+ sqlsrv_log_set_subsystems(SQLSRV_LOG_SYSTEM_ALL);
+ sqlsrv_configure('warnings_return_as_errors', 0);
+ }
+ }
+
+ function ServerInfo()
+ {
+ global $ADODB_FETCH_MODE;
+ if ($this->fetchMode === false) {
+ $savem = $ADODB_FETCH_MODE;
+ $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
+ } else
+ $savem = $this->SetFetchMode(ADODB_FETCH_NUM);
+ $arrServerInfo = sqlsrv_server_info($this->_connectionID);
+ $arr['description'] = $arrServerInfo['SQLServerName'].' connected to '.$arrServerInfo['CurrentDatabase'];
+ $arr['version'] = $arrServerInfo['SQLServerVersion'];//ADOConnection::_findvers($arr['description']);
+ return $arr;
+ }
+
+ function IfNull( $field, $ifNull )
+ {
+ return " ISNULL($field, $ifNull) "; // if MS SQL Server
+ }
+
+ function _insertid()
+ {
+ // SCOPE_IDENTITY()
+ // Returns the last IDENTITY value inserted into an IDENTITY column in
+ // the same scope. A scope is a module -- a stored procedure, trigger,
+ // function, or batch. Thus, two statements are in the same scope if
+ // they are in the same stored procedure, function, or batch.
+ return $this->GetOne($this->identitySQL);
+ }
+
+ function _affectedrows()
+ {
+ return sqlsrv_rows_affected($this->_queryID);
+ }
+
+ function CreateSequence($seq='adodbseq',$start=1)
+ {
+ if($this->debug) error_log("<hr>CreateSequence($seq,$start)");
+ sqlsrv_begin_transaction($this->_connectionID);
+ $start -= 1;
+ $this->Execute("create table $seq (id int)");//was float(53)
+ $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
+ if (!$ok) {
+ if($this->debug) error_log("<hr>Error: ROLLBACK");
+ sqlsrv_rollback($this->_connectionID);
+ return false;
+ }
+ sqlsrv_commit($this->_connectionID);
+ return true;
+ }
+
+ function GenID($seq='adodbseq',$start=1)
+ {
+ if($this->debug) error_log("<hr>GenID($seq,$start)");
+ sqlsrv_begin_transaction($this->_connectionID);
+ $ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1");
+ if (!$ok) {
+ $this->Execute("create table $seq (id int)");
+ $ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
+ if (!$ok) {
+ if($this->debug) error_log("<hr>Error: ROLLBACK");
+ sqlsrv_rollback($this->_connectionID);
+ return false;
+ }
+ sqlsrv_commit($this->_connectionID);
+ return $start;
+ }
+ $num = $this->GetOne("select id from $seq");
+ sqlsrv_commit($this->_connectionID);
+ if($this->debug) error_log(" Returning: $num");
+ return $num;
+ }
+
+ // Format date column in sql string given an input format that understands Y M D
+ function SQLDate($fmt, $col=false)
+ {
+ if (!$col) $col = $this->sysTimeStamp;
+ $s = '';
+
+ $len = strlen($fmt);
+ for ($i=0; $i < $len; $i++) {
+ if ($s) $s .= '+';
+ $ch = $fmt[$i];
+ switch($ch) {
+ case 'Y':
+ case 'y':
+ $s .= "datename(yyyy,$col)";
+ break;
+ case 'M':
+ $s .= "convert(char(3),$col,0)";
+ break;
+ case 'm':
+ $s .= "replace(str(month($col),2),' ','0')";
+ break;
+ case 'Q':
+ case 'q':
+ $s .= "datename(quarter,$col)";
+ break;
+ case 'D':
+ case 'd':
+ $s .= "replace(str(day($col),2),' ','0')";
+ break;
+ case 'h':
+ $s .= "substring(convert(char(14),$col,0),13,2)";
+ break;
+
+ case 'H':
+ $s .= "replace(str(datepart(hh,$col),2),' ','0')";
+ break;
+
+ case 'i':
+ $s .= "replace(str(datepart(mi,$col),2),' ','0')";
+ break;
+ case 's':
+ $s .= "replace(str(datepart(ss,$col),2),' ','0')";
+ break;
+ case 'a':
+ case 'A':
+ $s .= "substring(convert(char(19),$col,0),18,2)";
+ break;
+
+ default:
+ if ($ch == '\\') {
+ $i++;
+ $ch = substr($fmt,$i,1);
+ }
+ $s .= $this->qstr($ch);
+ break;
+ }
+ }
+ return $s;
+ }
+
+
+ function BeginTrans()
+ {
+ if ($this->transOff) return true;
+ $this->transCnt += 1;
+ if ($this->debug) error_log('<hr>begin transaction');
+ sqlsrv_begin_transaction($this->_connectionID);
+ return true;
+ }
+
+ function CommitTrans($ok=true)
+ {
+ if ($this->transOff) return true;
+ if ($this->debug) error_log('<hr>commit transaction');
+ if (!$ok) return $this->RollbackTrans();
+ if ($this->transCnt) $this->transCnt -= 1;
+ sqlsrv_commit($this->_connectionID);
+ return true;
+ }
+ function RollbackTrans()
+ {
+ if ($this->transOff) return true;
+ if ($this->debug) error_log('<hr>rollback transaction');
+ if ($this->transCnt) $this->transCnt -= 1;
+ sqlsrv_rollback($this->_connectionID);
+ return true;
+ }
+
+ function SetTransactionMode( $transaction_mode )
+ {
+ $this->_transmode = $transaction_mode;
+ if (empty($transaction_mode)) {
+ $this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
+ return;
+ }
+ if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
+ $this->Execute("SET TRANSACTION ".$transaction_mode);
+ }
+
+ /*
+ Usage:
+
+ $this->BeginTrans();
+ $this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables
+
+ # some operation on both tables table1 and table2
+
+ $this->CommitTrans();
+
+ See http://www.swynk.com/friends/achigrik/SQL70Locks.asp
+ */
+ function RowLock($tables,$where,$col='top 1 null as ignore')
+ {
+ if (!$this->transCnt) $this->BeginTrans();
+ return $this->GetOne("select $col from $tables with (ROWLOCK,HOLDLOCK) where $where");
+ }
+
+ function SelectDB($dbName)
+ {
+ $this->database = $dbName;
+ $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions
+ if ($this->_connectionID) {
+ $rs = $this->Execute('USE '.$dbName);
+ if($rs) {
+ return true;
+ } else return false;
+ }
+ else return false;
+ }
+
+ function ErrorMsg()
+ {
+ $retErrors = sqlsrv_errors(SQLSRV_ERR_ALL);
+ if($retErrors != null) {
+ foreach($retErrors as $arrError) {
+ $this->_errorMsg .= "SQLState: ".$arrError[ 'SQLSTATE']."\n";
+ $this->_errorMsg .= "Error Code: ".$arrError[ 'code']."\n";
+ $this->_errorMsg .= "Message: ".$arrError[ 'message']."\n";
+ }
+ } else {
+ $this->_errorMsg = "No errors found";
+ }
+ return $this->_errorMsg;
+ }
+
+ function ErrorNo()
+ {
+ if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode;
+ $err = sqlsrv_errors(SQLSRV_ERR_ALL);
+ if($err[0]) return $err[0]['code'];
+ else return -1;
+ }
+
+ // returns true or false
+ function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
+ {
+ if (!function_exists('sqlsrv_connect')) return null;
+ $connectionInfo = array("Database"=>$argDatabasename,'UID'=>$argUsername,'PWD'=>$argPassword);
+ if ($this->debug) error_log("<hr>connecting... hostname: $argHostname params: ".var_export($connectionInfo,true));
+ //if ($this->debug) error_log("<hr>_connectionID before: ".serialize($this->_connectionID));
+ if(!($this->_connectionID = sqlsrv_connect($argHostname,$connectionInfo))) {
+ if ($this->debug) error_log( "<hr><b>errors</b>: ".print_r( sqlsrv_errors(), true));
+ return false;
+ }
+ //if ($this->debug) error_log(" _connectionID after: ".serialize($this->_connectionID));
+ //if ($this->debug) error_log("<hr>defined functions: <pre>".var_export(get_defined_functions(),true)."</pre>");
+ return true;
+ }
+
+ // returns true or false
+ function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
+ {
+ //return null;//not implemented. NOTE: Persistent connections have no effect if PHP is used as a CGI program. (FastCGI!)
+ return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
+ }
+
+ function Prepare($sql)
+ {
+ $stmt = sqlsrv_prepare( $this->_connectionID, $sql);
+ if (!$stmt) return $sql;
+ return array($sql,$stmt);
+ }
+
+ // returns concatenated string
+ // MSSQL requires integers to be cast as strings
+ // automatically cast every datatype to VARCHAR(255)
+ // @author David Rogers (introspectshun)
+ function Concat()
+ {
+ $s = "";
+ $arr = func_get_args();
+
+ // Split single record on commas, if possible
+ if (sizeof($arr) == 1) {
+ foreach ($arr as $arg) {
+ $args = explode(',', $arg);
+ }
+ $arr = $args;
+ }
+
+ array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";'));
+ $s = implode('+',$arr);
+ if (sizeof($arr) > 0) return "$s";
+
+ return '';
+ }
+
+ /*
+ Unfortunately, it appears that mssql cannot handle varbinary > 255 chars
+ So all your blobs must be of type "image".
+
+ Remember to set in php.ini the following...
+
+ ; Valid range 0 - 2147483647. Default = 4096.
+ mssql.textlimit = 0 ; zero to pass through
+
+ ; Valid range 0 - 2147483647. Default = 4096.
+ mssql.textsize = 0 ; zero to pass through
+ */
+ function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
+ {
+
+ if (strtoupper($blobtype) == 'CLOB') {
+ $sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
+ return $this->Execute($sql) != false;
+ }
+ $sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where";
+ return $this->Execute($sql) != false;
+ }
+
+ // returns query ID if successful, otherwise false
+ function _query($sql,$inputarr=false)
+ {
+ $this->_errorMsg = false;
+ if (is_array($inputarr)) {
+ $rez = sqlsrv_query($this->_connectionID,$sql,$inputarr);
+ } else if (is_array($sql)) {
+ $rez = sqlsrv_query($this->_connectionID,$sql[1],$inputarr);
+ } else {
+ $rez = sqlsrv_query($this->_connectionID,$sql);
+ }
+ if ($this->debug) error_log("<hr>running query: ".var_export($sql,true)."<hr>input array: ".var_export($inputarr,true)."<hr>result: ".var_export($rez,true));//"<hr>connection: ".serialize($this->_connectionID)
+ //fix for returning true on anything besides select statements
+ if (is_array($sql)) $sql = $sql[1];
+ $sql = ltrim($sql);
+ if(stripos($sql, 'SELECT') !== 0 && $rez !== false) {
+ if ($this->debug) error_log(" isn't a select query, returning boolean true");
+ return true;
+ }
+ //end fix
+ if(!$rez) $rez = false;
+ return $rez;
+ }
+
+ // returns true or false
+ function _close()
+ {
+ if ($this->transCnt) $this->RollbackTrans();
+ $rez = @sqlsrv_close($this->_connectionID);
+ $this->_connectionID = false;
+ return $rez;
+ }
+
+ // mssql uses a default date like Dec 30 2000 12:00AM
+ function UnixDate($v)
+ {
+ return ADORecordSet_array_mssql::UnixDate($v);
+ }
+
+ function UnixTimeStamp($v)
+ {
+ return ADORecordSet_array_mssql::UnixTimeStamp($v);
+ }
+
+ function &MetaIndexes($table,$primary=false)
+ {
+ $table = $this->qstr($table);
+
+ $sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno,
+ CASE WHEN I.indid BETWEEN 1 AND 254 AND (I.status & 2048 = 2048 OR I.Status = 16402 AND O.XType = 'V') THEN 1 ELSE 0 END AS IsPK,
+ CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
+ FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id
+ INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid
+ INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
+ WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
+ ORDER BY O.name, I.Name, K.keyno";
+
+ global $ADODB_FETCH_MODE;
+ $save = $ADODB_FETCH_MODE;
+ $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
+ if ($this->fetchMode !== FALSE) {
+ $savem = $this->SetFetchMode(FALSE);
+ }
+
+ $rs = $this->Execute($sql);
+ if (isset($savem)) {
+ $this->SetFetchMode($savem);
+ }
+ $ADODB_FETCH_MODE = $save;
+
+ if (!is_object($rs)) {
+ return FALSE;
+ }
+
+ $indexes = array();
+ while ($row = $rs->FetchRow()) {
+ if (!$primary && $row[5]) continue;
+
+ $indexes[$row[0]]['unique'] = $row[6];
+ $indexes[$row[0]]['columns'][] = $row[1];
+ }
+ return $indexes;
+ }
+
+ function MetaForeignKeys($table, $owner=false, $upper=false)
+ {
+ global $ADODB_FETCH_MODE;
+
+ $save = $ADODB_FETCH_MODE;
+ $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
+ $table = $this->qstr(strtoupper($table));
+
+ $sql =
+ "select object_name(constid) as constraint_name,
+ col_name(fkeyid, fkey) as column_name,
+ object_name(rkeyid) as referenced_table_name,
+ col_name(rkeyid, rkey) as referenced_column_name
+ from sysforeignkeys
+ where upper(object_name(fkeyid)) = $table
+ order by constraint_name, referenced_table_name, keyno";
+
+ $constraints =& $this->GetArray($sql);
+
+ $ADODB_FETCH_MODE = $save;
+
+ $arr = false;
+ foreach($constraints as $constr) {
+ //print_r($constr);
+ $arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3];
+ }
+ if (!$arr) return false;
+
+ $arr2 = false;
+
+ foreach($arr as $k => $v) {
+ foreach($v as $a => $b) {
+ if ($upper) $a = strtoupper($a);
+ $arr2[$a] = $b;
+ }
+ }
+ return $arr2;
+ }
+
+ //From: Fernando Moreira <FMo...@im...>
+ function MetaDatabases()
+ {
+ $this->SelectDB("master");
+ $rs =& $this->Execute($this->metaDatabasesSQL);
+ $rows = $rs->GetRows();
+ $ret = array();
+ for($i=0;$i<count($rows);$i++) {
+ $ret[] = $rows[$i][0];
+ }
+ $this->SelectDB($this->database);
+ if($ret)
+ return $ret;
+ else
+ return false;
+ }
+
+ // "Stein-Aksel Basma" <ba...@ac...>
+ // tested with MSSQL 2000
+ function &MetaPrimaryKeys($table)
+ {
+ global $ADODB_FETCH_MODE;
+
+ $schema = '';
+ $this->_findschema($table,$schema);
+ if (!$schema) $schema = $this->database;
+ if ($schema) $schema = "and k.table_catalog like '$schema%'";
+
+ $sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
+ information_schema.table_constraints tc
+ where tc.constraint_name = k.constraint_name and tc.constraint_type =
+ 'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
+
+ $savem = $ADODB_FETCH_MODE;
+ $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
+ $a = $this->GetCol($sql);
+ $ADODB_FETCH_MODE = $savem;
+
+ if ($a && sizeof($a)>0) return $a;
+ $false = false;
+ return $false;
+ }
+
+
+ function &MetaTables($ttype=false,$showSchema=false,$mask=false)
+ {
+ if ($mask) {
+ $save = $this->metaTablesSQL;
+ $mask = $this->qstr(($mask));
+ $this->metaTablesSQL .= " AND name like $mask";
+ }
+ $ret =& ADOConnection::MetaTables($ttype,$showSchema);
+
+ if ($mask) {
+ $this->metaTablesSQL = $save;
+ }
+ return $ret;
+ }
+}
+
+/*--------------------------------------------------------------------------------------
+ Class Name: Recordset
+--------------------------------------------------------------------------------------*/
+
+class ADORecordset_mssqlnative extends ADORecordSet {
+
+ var $databaseType = "mssqlnative";
+ var $canSeek = false;
+ var $fieldOffset = 0;
+ // _mths works only in non-localised system
+
+ function ADORecordset_mssqlnative($id,$mode=false)
+ {
+ if ($mode === false) {
+ global $ADODB_FETCH_MODE;
+ $mode = $ADODB_FETCH_MODE;
+
+ }
+ $this->fetchMode = $mode;
+ return $this->ADORecordSet($id,$mode);
+ }
+
+
+ function _initrs()
+ {
+ global $ADODB_COUNTRECS;
+ if ($this->connection->debug) error_log("(before) ADODB_COUNTRECS: {$ADODB_COUNTRECS} _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
+ /*$retRowsAff = sqlsrv_rows_affected($this->_queryID);//"If you need to determine the number of rows a query will return before retrieving the actual results, appending a SELECT COUNT ... query would let you get that information, and then a call to next_result would move you to the "real" results."
+ error_log("rowsaff: ".serialize($retRowsAff));
+ $this->_numOfRows = ($ADODB_COUNTRECS)? $retRowsAff:-1;*/
+ $this->_numOfRows = -1;//not supported
+ $fieldmeta = sqlsrv_field_metadata($this->_queryID);
+ $this->_numOfFields = ($fieldmeta)? count($fieldmeta):-1;
+ if ($this->connection->debug) error_log("(after) _numOfRows: {$this->_numOfRows} _numOfFields: {$this->_numOfFields}");
+ }
+
+
+ //Contributed by "Sven Axelsson" <sve...@bo...>
+ // get next resultset - requires PHP 4.0.5 or later
+ function NextRecordSet()
+ {
+ if (!sqlsrv_next_result($this->_queryID)) return false;
+ $this->_inited = false;
+ $this->bind = false;
+ $this->_currentRow = -1;
+ $this->Init();
+ return true;
+ }
+
+ /* Use associative array to get fields array */
+ function Fields($colname)
+ {
+ if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname];
+ if (!$this->bind) {
+ $this->bind = array();
+ for ($i=0; $i < $this->_numOfFields; $i++) {
+ $o = $this->FetchField($i);
+ $this->bind[strtoupper($o->name)] = $i;
+ }
+ }
+
+ return $this->fields[$this->bind[strtoupper($colname)]];
+ }
+
+ /* Returns: an object containing field information.
+ Get column information in the Recordset object. fetchField() can be used in order to obtain information about
+ fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
+ fetchField() is retrieved. */
+
+ function &FetchField($fieldOffset = -1)
+ {
+ if ($this->connection->debug) error_log("<hr>fetchfield: $fieldOffset, fetch array: <pre>".print_r($this->fields,true)."</pre> backtrace: ".adodb_backtrace(false));
+ if ($fieldOffset != -1) $this->fieldOffset = $fieldOffset;
+ $arrKeys = array_keys($this->fields);
+ if(array_key_exists($this->fieldOffset,$arrKeys) && !array_key_exists($arrKeys[$this->fieldOffset],$this->fields)) {
+ $f = false;
+ } else {
+ $f = $this->fields[ $arrKeys[$this->fieldOffset] ];
+ if($fieldOffset == -1) $this->fieldOffset++;
+ }
+
+ if (empty($f)) {
+ $f = false;//PHP Notice: Only variable references should be returned by reference
+ }
+ return $f;
+ }
+
+ function _seek($row)
+ {
+ return false;//There is no support for cursors in the driver at this time. All data is returned via forward-only streams.
+ }
+
+ // speedup
+ function MoveNext()
+ {
+ if ($this->connection->debug) error_log("movenext()");
+ //if ($this->connection->debug) error_log("eof (beginning): ".$this->EOF);
+ if ($this->EOF) return false;
+
+ $this->_currentRow++;
+ if ($this->connection->debug) error_log("_currentRow: ".$this->_currentRow);
+
+ if ($this->_fetch()) return true;
+ $this->EOF = true;
+ //if ($this->connection->debug) error_log("eof (end): ".$this->EOF);
+
+ return false;
+ }
+
+
+ // INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4
+ // also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot!
+ function _fetch($ignore_fields=false)
+ {
+ if ($this->connection->debug) error_log("_fetch()");
+ if ($this->fetchMode & ADODB_FETCH_ASSOC) {
+ if ($this->fetchMode & ADODB_FETCH_NUM) {
+ if ($this->connection->debug) error_log("fetch mode: both");
+ $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_BOTH);
+ } else {
+ if ($this->connection->debug) error_log("fetch mode: assoc");
+ $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_ASSOC);
+ }
+
+ if (ADODB_ASSOC_CASE == 0) {
+ foreach($this->fields as $k=>$v) {
+ $this->fields[strtolower($k)] = $v;
+ }
+ } else if (ADODB_ASSOC_CASE == 1) {
+ foreach($this->fields as $k=>$v) {
+ $this->fields[strtoupper($k)] = $v;
+ }
+ }
+ } else {
+ if ($this->connection->debug) error_log("fetch mode: num");
+ $this->fields = @sqlsrv_fetch_array($this->_queryID,SQLSRV_FETCH_NUMERIC);
+ }
+ if(is_array($this->fields) && array_key_exists(1,$this->fields) && !array_key_exists(0,$this->fields)) {//fix fetch numeric keys since they're not 0 based
+ $arrFixed = array();
+ foreach($this->fields as $key=>$value) {
+ if(is_numeric($key)) {
+ $arrFixed[$key-1] = $value;
+ } else {
+ $arrFixed[$key] = $value;
+ }
+ }
+ //if($this->connection->debug) error_log("<hr>fixing non 0 based return array, old: ".print_r($this->fields,true)." new: ".print_r($arrFixed,true));
+ $this->fields = $arrFixed;
+ }
+ if(is_array($this->fields)) {
+ foreach($this->fields as $key=>$value) {
+ if (is_object($value) && method_exists($value, 'format')) {//is DateTime object
+ $this->fields[$key] = $value->format("Y-m-d\TH:i:s\Z");
+ }
+ }
+ }
+ if($this->fields === null) $this->fields = false;
+ if ($this->connection->debug) error_log("<hr>after _fetch, fields: <pre>".print_r($this->fields,true)." backtrace: ".adodb_backtrace(false));
+ return $this->fields;
+ }
+
+ /* close() only needs to be called if you are worried about using too much memory while your script
+ is running. All associated result memory for the specified result identifier will automatically be freed. */
+ function _close()
+ {
+ $rez = sqlsrv_free_stmt($this->_queryID);
+ $this->_queryID = false;
+ return $rez;
+ }
+
+ // mssql uses a default date like Dec 30 2000 12:00AM
+ function UnixDate($v)
+ {
+ return ADORecordSet_array_mssqlnative::UnixDate($v);
+ }
+
+ function UnixTimeStamp($v)
+ {
+ return ADORecordSet_array_mssqlnative::UnixTimeStamp($v);
+ }
+}
+
+
+class ADORecordSet_array_mssqlnative extends ADORecordSet_array {
+ function ADORecordSet_array_mssqlnative($id=-1,$mode=false)
+ {
+ $this->ADORecordSet_array($id,$mode);
+ }
+
+ // mssql uses a default date like Dec 30 2000 12:00AM
+ function UnixDate($v)
+ {
+
+ if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v);
+
+ global $ADODB_mssql_mths,$ADODB_mssql_date_order;
+
+ //Dec 30 2000 12:00AM
+ if ($ADODB_mssql_date_order == 'dmy') {
+ if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
+ return parent::UnixDate($v);
+ }
+ if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
+
+ $theday = $rr[1];
+ $themth = substr(strtoupper($rr[2]),0,3);
+ } else {
+ if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})|" ,$v, $rr)) {
+ return parent::UnixDate($v);
+ }
+ if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
+
+ $theday = $rr[2];
+ $themth = substr(strtoupper($rr[1]),0,3);
+ }
+ $themth = $ADODB_mssql_mths[$themth];
+ if ($themth <= 0) return false;
+ // h-m-s-MM-DD-YY
+ return mktime(0,0,0,$themth,$theday,$rr[3]);
+ }
+
+ function UnixTimeStamp($v)
+ {
+
+ if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v);
+
+ global $ADODB_mssql_mths,$ADODB_mssql_date_order;
+
+ //Dec 30 2000 12:00AM
+ if ($ADODB_mssql_date_order == 'dmy') {
+ if (!preg_match( "|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4}) +([0-9]{1,2}):([0-9]{1,2}) *([apAP]{0,1})|"
+ ,$v, $rr)) return parent::UnixTimeStamp($v);
+ if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
+
+ $theday = $rr[1];
+ $themth = substr(strtoupper($rr[2]),0,3);
+ } else {
+ if (!preg_match( "|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4}) +([0-9]{1,2}):([0-9]{1,2}) *([apAP]{0,1})|"
+ ,$v, $rr)) return parent::UnixTimeStamp($v);
+ if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
+
+ $theday = $rr[2];
+ $themth = substr(strtoupper($rr[1]),0,3);
+ }
+
+ $themth = $ADODB_mssql_mths[$themth];
+ if ($themth <= 0) return false;
+
+ switch (strtoupper($rr[6])) {
+ case 'P':
+ if ($rr[4]<12) $rr[4] += 12;
+ break;
+ case 'A':
+ if ($rr[4]==12) $rr[4] = 0;
+ break;
+ default:
+ break;
+ }
+ // h-m-s-MM-DD-YY
+ return mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]);
+ }
+}
+
+/*
+Code Example 1:
+
+select object_name(constid) as constraint_name,
+ object_name(fkeyid) as table_name,
+ col_name(fkeyid, fkey) as column_name,
+ object_name(rkeyid) as referenced_table_name,
+ col_name(rkeyid, rkey) as referenced_column_name
+from sysforeignkeys
+where object_name(fkeyid) = x
+order by constraint_name, table_name, referenced_table_name, keyno
+
+Code Example 2:
+select constraint_name,
+ column_name,
+ ordinal_position
+from information_schema.key_column_usage
+where constraint_catalog = db_name()
+and table_name = x
+order by constraint_name, ordinal_position
+
+http://www.databasejournal.com/scripts/article.php/1440551
+*/
+
+?>
\ No newline at end of file
Added: trunk/lib/WikiDB/backend/ADODB_mssqlnative.php
===================================================================
--- trunk/lib/WikiDB/backend/ADODB_mssqlnative.php (rev 0)
+++ trunk/lib/WikiDB/backend/ADODB_mssqlnative.php 2010-06-07 14:05:29 UTC (rev 7485)
@@ -0,0 +1,249 @@
+<?php // -*-php-*-
+// rcs_id('$Id: ADODB_mssql.php,v 1.2 2005/10/10 19:42:15 rurban Exp $');
+
+/**
+ * MS SQL extensions for the ADODB DB backend.
+ */
+require_once('lib/WikiDB/backend/ADODB.php');
+
+class WikiDB_backend_ADODB_mssqlnative
+extends WikiDB_backend_ADODB
+{
+ /**
+ * Constructor.
+ */
+ function WikiDB_backend_ADODB_mssqlnative($dbparams) {
+ // Lowercase Assoc arrays
+ define('ADODB_ASSOC_CASE',0);
+
+ // Backend constructor
+ $this->WikiDB_backend_ADODB($dbparams);
+
+ // Empty strings in MSSQL? NULLS?
+ $this->_expressions['notempty'] = "NOT LIKE ''";
+ //doesn't work if content is of the "text" type http://msdn2.microsoft.com/en-us/library/ms188074.aspx
+ $this->_expressions['iscontent'] = "dbo.hasContent({$this->_table_names['version_tbl']}.content)";
+
+ $this->_prefix = isset($dbparams['prefix']) ? $dbparams['prefix'] : '';
+
+ }
+
+ /**
+ * Pack tables.
+ */
+ function optimize() {
+ // Do nothing here -- Leave that for the DB
+ // Cost Based Optimizer tuning vary from version to version
+ return 1;
+ }
+
+ // Search callabcks
+ // Page name
+ function _sql_match_clause($word) {
+ $word = preg_replace('/(?=[%_\\\\])/', "\\", $word);
+ $word = $this->_dbh->qstr("%$word%");
+ return "LOWER(pagename) LIKE $word";
+ }
+
+ // Fulltext -- case sensitive :-\
+ function _fullsearch_sql_match_clause($word) {
+ $word = preg_replace('/(?=[%_\\\\])/', "\\", $word);
+ $wordq = $this->_dbh->qstr("%$word%");
+ return "LOWER(pagename) LIKE $wordq "
+ . "OR CHARINDEX(content, '$word') > 0";
+ }
+
+ /**
+ * Serialize data
+ */
+ function _serialize($data) {
+ if (empty($data))
+ return '';
+ assert(is_array($data));
+ return addslashes(serialize($data));
+ }
+
+ /**
+ * Unserialize data
+ */
+ function _unserialize($data) {
+ return empty($data) ? array() : unserialize(stripslashes($data));
+ }
+
+ /*
+ * Update link table.
+ * on DEBUG: delete old, deleted links from page
+ */
+ function set_links($pagename, $links) {
+ // FIXME: optimize: mysql can do this all in one big INSERT/REPLACE.
+
+ $dbh = &$this->_dbh;
+ extract($this->_table_names);
+
+ $this->lock(array('link'));
+ $pageid = $this->_get_pageid($pagename, true);
+
+ $oldlinks = $dbh->getAssoc("SELECT $link_tbl.linkto as id, page.pagename FROM $link_tbl"
+ ." JOIN page ON ($link_tbl.linkto = page.id)"
+ ." WHERE linkfrom=$pageid");
+ // Delete current links,
+ $dbh->Execute("DELETE FROM $link_tbl WHERE linkfrom=$pageid");
+ // and insert new links. Faster than checking for all single links
+ if ($links) {
+ foreach ($links as $link) {
+ $linkto = $link['linkto'];
+ if ($link['relation'])
+ $relation = $this->_get_pageid($link['relation'], true);
+ else
+ $relation = 0;
+ if ($linkto === "") { // ignore attributes
+ continue;
+ }
+ // avoid duplicates
+ if (isset($linkseen[$linkto]) and !$relation) {
+ continue;
+ }
+ if (!$relation) {
+ $linkseen[$linkto] = true;
+ }
+ $linkid = $this->_get_pageid($linkto, true);
+ assert($linkid);
+ if ($relation) {
+ $dbh->Execute("INSERT INTO $link_tbl (linkfrom, linkto, relation)"
+ . " VALUES ($pageid, $linkid, $relation)");
+ } else {
+ $dbh->Execute("INSERT INTO $link_tbl (linkfrom, linkto)"
+ . " VALUES ($pageid, $linkid)");
+ }
+ if ($oldlinks and array_key_exists($linkid, $oldlinks)) {
+ // This was also in the previous page
+ unset($oldlinks[$linkid]);
+ }
+ }
+ }
+ // purge page table: delete all non-referenced pages
+ // for all previously linked pages, which have no other linkto links
+ if (DEBUG and $oldlinks) {
+ // trigger_error("purge page table: delete all non-referenced pages...", E_USER_NOTICE);
+ foreach ($oldlinks as $id => $name) {
+ // ...check if the page is empty and has no version
+ if($id != '') {
+ $result = $dbh->getRow("SELECT $page_tbl.id FROM $page_tbl"
+ . " LEFT JOIN $nonempty_tbl ON ($nonempty_tbl.id = $page_tbl.id)"//'"id" is not a recognized table hints option'
+ . " LEFT JOIN $version_tbl ON ($version_tbl.id = $page_tbl.id)"//'"id" is not a recognized table hints option'
+ . " WHERE $nonempty_tbl.id is NULL"
+ . " AND $version_tbl.id is NULL"
+ . " AND $page_tbl.id=$id");
+ $linkto = $dbh->getRow("SELECT linkfrom FROM $link_tbl WHERE linkto=$id");
+ if ($result and empty($linkto)) {
+ trigger_error("delete empty and non-referenced link $name ($id)", E_USER_NOTICE);
+ $dbh->Execute("DELETE FROM $recent_tbl WHERE id=$id"); // may fail
+ $dbh->Execute("DELETE FROM $link_tbl WHERE linkto=$id");
+ $dbh->Execute("DELETE FROM $page_tbl WHERE id=$id"); // this purges the link
+ }
+ }
+ }
+ }
+ $this->unlock(array('link'));
+ return true;
+ }
+
+ /* get all oldlinks in hash => id, relation
+ check for all new links
+ */
+ function set_links1($pagename, $links) {
+
+ $dbh = &$this->_dbh;
+ extract($this->_table_names);
+
+ $this->lock(array('link'));
+ $pageid = $this->_get_pageid($pagename, true);
+
+ $oldlinks = $dbh->getAssoc("SELECT $link_tbl.linkto as linkto, $link_tbl.relation, page.pagename"
+ ." FROM $link_tbl"
+ ." JOIN page ON ($link_tbl.linkto = page.id)"
+ ." WHERE linkfrom=$pageid");
+ /* old new
+ * X => [1,0 2,0 1,1] X => [1,1 3,0]
+ * => delete 1,0 2,0 + insert 3,0
+ */
+ if ($links) {
+ foreach ($links as $link) {
+ $linkto = $link['linkto'];
+ if ($link['relation'])
+ $relation = $this->_get_pageid($link['relation'], true);
+ else
+ $relation = 0;
+ // avoid duplicates
+ if (isset($linkseen[$linkto]) and !$relation) {
+ continue;
+ }
+ if (!$relation) {
+ $linkseen[$linkto] = true;
+ }
+ $linkid = $this->_get_pageid($linkto, true);
+ assert($linkid);
+ $skip = 0;
+ // find linkfrom,linkto,relation triple in oldlinks
+ foreach ($oldlinks as $l) {
+ if ($relation) { // relation NOT NULL
+ if ($l['linkto'] == $linkid and $l['relation'] == $relation) {
+ // found and skip
+ $skip = 1;
+ }
+ }
+ }
+ if (! $skip ) {
+ if ($update) {
+ }
+ if ($relation) {
+ $dbh->Execute("INSERT INTO $link_tbl (linkfrom, linkto, relation)"
+ . " VALUES ($pageid, $linkid, $relation)");
+ } else {
+ $dbh->Execute("INSERT INTO $link_tbl (linkfrom, linkto)"
+ . " VALUES ($pageid, $linkid)");
+ }
+ }
+
+ if (array_key_exists($linkid, $oldlinks)) {
+ // This was also in the previous page
+ unset($oldlinks[$linkid]);
+ }
+ }
+ }
+ // purge page table: delete all non-referenced pages
+ // for all previously linked pages...
+ if (DEBUG and $oldlinks) {
+ // trigger_error("purge page table: delete all non-referenced pages...", E_USER_NOTICE);
+ foreach ($oldlinks as $id => $name) {
+ // ...check if the page is empty and has no version
+ if($id != '') {
+ if ($dbh->getRow("SELECT $page_tbl.id FROM $page_tbl"
+ . " LEFT JOIN $nonempty_tbl ON ($nonempty_tbl.id = $page_tbl.id)"//'"id" is not a recognized table hints option'
+ . " LEFT JOIN $version_tbl ON ($version_tbl.id = $page_tbl.id)"//'"id" is not a recognized table hints option'
+ . " WHERE $nonempty_tbl.id is NULL"
+ . " AND $version_tbl.id is NULL"
+ . " AND $page_tbl.id=$id"))
+ {
+ trigger_error("delete empty and non-referenced link $name ($id)", E_USER_NOTICE);
+ $dbh->Execute("DELETE FROM $page_tbl WHERE id=$id"); // this purges the link
+ $dbh->Execute("DELETE FROM $recent_tbl WHERE id=$id"); // may fail
+ }
+ }
+ }
+ }
+ $this->unlock(array('link'));
+ return true;
+ }
+
+};
+
+// (c-file-style: "gnu")
+// Local Variables:
+// mode: php
+// tab-width: 8
+// c-basic-offset: 4
+// c-hanging-comment-ender-p: nil
+// indent-tabs-mode: nil
+// End:
+?>
Added: trunk/schemas/sqlsrv-destroy.sql
===================================================================
--- trunk/schemas/sqlsrv-destroy.sql (rev 0)
+++ trunk/schemas/sqlsrv-destroy.sql 2010-06-07 14:05:29 UTC (rev 7485)
@@ -0,0 +1,17 @@
+-- $Id: mssql-destroy.sql,v 1.3 2005/09/28 19:27:23 rurban Exp $
+
+DROP TABLE page;
+DROP TABLE version;
+DROP TABLE recent;
+DROP TABLE nonempty;
+DROP TABLE link;
+DROP TABLE session;
+
+DROP TABLE pref;
+--DROP TABLE user;
+DROP TABLE member;
+
+-- wikilens theme
+DROP TABLE rating;
+
+DROP TABLE accesslog;
Added: trunk/schemas/sqlsrv-initialize.sql
===================================================================
--- trunk/schemas/sqlsrv-initialize.sql (rev 0)
+++ trunk/schemas/sqlsrv-initialize.sql 2010-06-07 14:05:29 UTC (rev 7485)
@@ -0,0 +1,144 @@
+-- $Id: sqlsrv-initialize.sql,v 1.5 2006/12/08 08:04:10 rurban Exp $
+-- UNTESTED!
+
+-- the CREATE FUNCTION section must be run as a seperate query. cut/paste run before executing the remainder
+-- of the contents of this file
+CREATE FUNCTION hasContent
+ (@ContentField varchar(max))
+RETURNS bit
+AS
+BEGIN
+
+IF @ContentField NOT LIKE ''
+ RETURN 1
+RETURN 0
+
+END
+-- end seperate section
+
+
+CREATE TABLE page (
+ id INT NOT NULL ,
+ pagename VARCHAR(100) NOT NULL,
+ hits INT NOT NULL DEFAULT 0,
+ pagedata TEXT NOT NULL DEFAULT '',
+ cached_html TEXT NOT NULL DEFAULT '', -- added with 1.3.11
+ PRIMARY KEY (id),
+ UNIQUE (pagename)
+);
+--SET IDENTITY_INSERT page ON;
+
+CREATE TABLE version (
+ id INT NOT NULL,
+ version INT NOT NULL,
+ mtime INT NOT NULL,
+ minor_edit TINYINT DEFAULT 0,
+ content varchar(max) NOT NULL DEFAULT '',--can't be text
+ versiondata TEXT NOT NULL DEFAULT '',
+ PRIMARY KEY (id,version)
+);
+CREATE INDEX version_mtime ON version (mtime);
+
+CREATE TABLE recent (
+ id INT NOT NULL,
+ latestversion INT,
+ latestmajor INT,
+ latestminor INT,
+ PRIMARY KEY (id)
+);
+
+CREATE TABLE nonempty (
+ id INT NOT NULL,
+ PRIMARY KEY (id)
+);
+
+CREATE TABLE link (
+ linkfrom INT NOT NULL,
+ linkto INT NOT NULL,
+ relation INT
+);
+CREATE INDEX linkfrom ON link (linkfrom);
+CREATE INDEX linkto ON link (linkto);
+
+CREATE TABLE session (
+ sess_id CHAR(32) NOT NULL DEFAULT '',
+ sess_data IMAGE NOT NULL,
+ sess_date BIGINT NOT NULL,
+ sess_ip CHAR(40) NOT NULL,
+ PRIMARY KEY (sess_id)
+);
+CREATE INDEX sessdate_index ON session (sess_date);
+CREATE INDEX sessip_index ON session (sess_ip);
+
+-- Optional DB Auth and Prefs
+-- For these tables below the default table prefix must be used
+-- in the DBAuthParam SQL statements also.
+
+CREATE TABLE pref (
+ userid CHAR(48) NOT NULL,
+ prefs TEXT NULL DEFAULT '',
+ passwd CHAR(48) DEFAULT '',
+ groupname CHAR(48) DEFAULT 'users',
+ PRIMARY KEY (userid)
+);
+
+-- update to 1.3.12: (see lib/upgrade.php)
+-- ALTER TABLE pref ADD passwd CHAR(48) BINARY DEFAULT '';
+-- ALTER TABLE pref ADD groupname CHAR(48) BINARY DEFAULT 'users';
+
+-- deprecated since 1.3.12. only useful for seperate databases.
+-- better use the extra pref table where such users can be created easily
+-- without password.
+--CREATE TABLE user (
+-- userid CHAR(48) NOT NULL,
+-- passwd CHAR(48) DEFAULT '',
+-- prefs TEXT NULL DEFAULT '',
+-- groupname CHAR(48) DEFAULT 'users'
+--);
+
+-- Use the member table, if you need it for n:m user-group relations,
+-- and adjust your DBAUTH_AUTH_ SQL statements.
+CREATE TABLE member (
+ userid CHAR(48) NOT NULL,
+ groupname CHAR(48) NOT NULL DEFAULT 'users'
+);
+CREATE INDEX member_userid ON member (userid);
+CREATE INDEX member_groupname ON member (groupname);
+
+-- only if you plan to use the wikilens theme
+CREATE TABLE rating (
+ dimension smallINT NOT NULL,
+ raterpage INT NOT NULL,
+ rateepage INT NOT NULL,
+ ratingvalue FLOAT NOT NULL,
+ rateeversion INT NOT NULL,
+ tstamp bigint NOT NULL,
+ PRIMARY KEY (dimension, raterpage, rateepage)
+);
+CREATE INDEX rating_dimension ON rating (dimension);
+CREATE INDEX rating_raterpage ON rating (raterpage);
+CREATE INDEX rating_rateepage ON rating (rateepage);
+
+-- if ACCESS_LOG_SQL > 0
+-- only if you need fast log-analysis (spam prevention, recent referrers)
+-- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
+CREATE TABLE accesslog (
+ time_stamp BIGINT,
+ remote_host VARCHAR(255),
+ remote_user VARCHAR(50),
+ request_method VARCHAR(10),
+ request_line text,
+ request_args text,
+ request_file text,
+ request_uri text,
+ request_time CHAR(28),
+ status INT,
+ bytes_sent INT,
+ referer text,
+ agent text,
+ request_duration FLOAT
+);
+CREATE INDEX log_time ON accesslog (time_stamp);
+CREATE INDEX log_host ON accesslog (remote_host);
+-- create extra indices on demand (usually referer. see plugin/AccessLogSql)
+
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
