4 * MS SQL extensions for the ADODB DB backend.
6 require_once 'lib/WikiDB/backend/ADODB.php';
8 class WikiDB_backend_ADODB_mssqlnative
9 extends WikiDB_backend_ADODB
14 function WikiDB_backend_ADODB_mssqlnative($dbparams)
16 // Lowercase Assoc arrays
17 define('ADODB_ASSOC_CASE', 0);
19 // Backend constructor
20 $this->WikiDB_backend_ADODB($dbparams);
22 // Empty strings in MSSQL? NULLS?
23 $this->_expressions['notempty'] = "NOT LIKE ''";
24 //doesn't work if content is of the "text" type http://msdn2.microsoft.com/en-us/library/ms188074.aspx
25 $this->_expressions['iscontent'] = "dbo.hasContent({$this->_table_names['version_tbl']}.content)";
27 $this->_prefix = isset($dbparams['prefix']) ? $dbparams['prefix'] : '';
36 // Do nothing here -- Leave that for the DB
37 // Cost Based Optimizer tuning vary from version to version
43 function _sql_match_clause($word)
45 $word = preg_replace('/(?=[%_\\\\])/', "\\", $word);
46 $word = $this->_dbh->qstr("%$word%");
47 return "LOWER(pagename) LIKE $word";
50 // Fulltext -- case sensitive :-\
51 function _fullsearch_sql_match_clause($word)
53 $word = preg_replace('/(?=[%_\\\\])/', "\\", $word);
54 $wordq = $this->_dbh->qstr("%$word%");
55 return "LOWER(pagename) LIKE $wordq "
56 . "OR CHARINDEX(content, '$word') > 0";
62 function _serialize($data)
66 assert(is_array($data));
67 return addslashes(serialize($data));
73 function _unserialize($data)
75 return empty($data) ? array() : unserialize(stripslashes($data));
80 * on DEBUG: delete old, deleted links from page
82 function set_links($pagename, $links)
84 // FIXME: optimize: mysql can do this all in one big INSERT/REPLACE.
87 extract($this->_table_names);
89 $this->lock(array('link'));
90 $pageid = $this->_get_pageid($pagename, true);
92 $oldlinks = $dbh->getAssoc("SELECT $link_tbl.linkto as id, page.pagename FROM $link_tbl"
93 . " JOIN page ON ($link_tbl.linkto = page.id)"
94 . " WHERE linkfrom=$pageid");
95 // Delete current links,
96 $dbh->Execute("DELETE FROM $link_tbl WHERE linkfrom=$pageid");
97 // and insert new links. Faster than checking for all single links
99 foreach ($links as $link) {
100 $linkto = $link['linkto'];
101 if (isset($link['relation']))
102 $relation = $this->_get_pageid($link['relation'], true);
105 if ($linkto === "") { // ignore attributes
109 if (isset($linkseen[$linkto]) and !$relation) {
113 $linkseen[$linkto] = true;
115 $linkid = $this->_get_pageid($linkto, true);
118 $dbh->Execute("INSERT INTO $link_tbl (linkfrom, linkto, relation)"
119 . " VALUES ($pageid, $linkid, $relation)");
121 $dbh->Execute("INSERT INTO $link_tbl (linkfrom, linkto)"
122 . " VALUES ($pageid, $linkid)");
124 if ($oldlinks and array_key_exists($linkid, $oldlinks)) {
125 // This was also in the previous page
126 unset($oldlinks[$linkid]);
130 // purge page table: delete all non-referenced pages
131 // for all previously linked pages, which have no other linkto links
132 if (DEBUG and $oldlinks) {
133 // trigger_error("purge page table: delete all non-referenced pages...", E_USER_NOTICE);
134 foreach ($oldlinks as $id => $name) {
135 // ...check if the page is empty and has no version
137 $result = $dbh->getRow("SELECT $page_tbl.id FROM $page_tbl"
138 . " LEFT JOIN $nonempty_tbl ON ($nonempty_tbl.id = $page_tbl.id)" //'"id" is not a recognized table hints option'
139 . " LEFT JOIN $version_tbl ON ($version_tbl.id = $page_tbl.id)" //'"id" is not a recognized table hints option'
140 . " WHERE $nonempty_tbl.id is NULL"
141 . " AND $version_tbl.id is NULL"
142 . " AND $page_tbl.id=$id");
143 $linkto = $dbh->getRow("SELECT linkfrom FROM $link_tbl WHERE linkto=$id");
144 if ($result and empty($linkto)) {
145 trigger_error("delete empty and non-referenced link $name ($id)", E_USER_NOTICE);
146 $dbh->Execute("DELETE FROM $recent_tbl WHERE id=$id"); // may fail
147 $dbh->Execute("DELETE FROM $link_tbl WHERE linkto=$id");
148 $dbh->Execute("DELETE FROM $page_tbl WHERE id=$id"); // this purges the link
153 $this->unlock(array('link'));
157 /* get all oldlinks in hash => id, relation
158 check for all new links
160 function set_links1($pagename, $links)
164 extract($this->_table_names);
166 $this->lock(array('link'));
167 $pageid = $this->_get_pageid($pagename, true);
169 $oldlinks = $dbh->getAssoc("SELECT $link_tbl.linkto as linkto, $link_tbl.relation, page.pagename"
171 . " JOIN page ON ($link_tbl.linkto = page.id)"
172 . " WHERE linkfrom=$pageid");
174 * X => [1,0 2,0 1,1] X => [1,1 3,0]
175 * => delete 1,0 2,0 + insert 3,0
178 foreach ($links as $link) {
179 $linkto = $link['linkto'];
180 if ($link['relation'])
181 $relation = $this->_get_pageid($link['relation'], true);
185 if (isset($linkseen[$linkto]) and !$relation) {
189 $linkseen[$linkto] = true;
191 $linkid = $this->_get_pageid($linkto, true);
194 // find linkfrom,linkto,relation triple in oldlinks
195 foreach ($oldlinks as $l) {
196 if ($relation) { // relation NOT NULL
197 if ($l['linkto'] == $linkid and $l['relation'] == $relation) {
207 $dbh->Execute("INSERT INTO $link_tbl (linkfrom, linkto, relation)"
208 . " VALUES ($pageid, $linkid, $relation)");
210 $dbh->Execute("INSERT INTO $link_tbl (linkfrom, linkto)"
211 . " VALUES ($pageid, $linkid)");
215 if (array_key_exists($linkid, $oldlinks)) {
216 // This was also in the previous page
217 unset($oldlinks[$linkid]);
221 // purge page table: delete all non-referenced pages
222 // for all previously linked pages...
223 if (DEBUG and $oldlinks) {
224 // trigger_error("purge page table: delete all non-referenced pages...", E_USER_NOTICE);
225 foreach ($oldlinks as $id => $name) {
226 // ...check if the page is empty and has no version
228 if ($dbh->getRow("SELECT $page_tbl.id FROM $page_tbl"
229 . " LEFT JOIN $nonempty_tbl ON ($nonempty_tbl.id = $page_tbl.id)" //'"id" is not a recognized table hints option'
230 . " LEFT JOIN $version_tbl ON ($version_tbl.id = $page_tbl.id)" //'"id" is not a recognized table hints option'
231 . " WHERE $nonempty_tbl.id is NULL"
232 . " AND $version_tbl.id is NULL"
233 . " AND $page_tbl.id=$id")
235 trigger_error("delete empty and non-referenced link $name ($id)", E_USER_NOTICE);
236 $dbh->Execute("DELETE FROM $page_tbl WHERE id=$id"); // this purges the link
237 $dbh->Execute("DELETE FROM $recent_tbl WHERE id=$id"); // may fail
242 $this->unlock(array('link'));
252 // c-hanging-comment-ender-p: nil
253 // indent-tabs-mode: nil