1 <?php // rcs_id('$Id$');
3 * Copyright 2004 Reini Urban
5 * This file is (not yet) part of PhpWiki.
7 * PhpWiki is free software; you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation; either version 2 of the License, or
10 * (at your option) any later version.
12 * PhpWiki is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
17 * You should have received a copy of the GNU General Public License
18 * along with PhpWiki; if not, write to the Free Software
19 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
23 * Accessors for a local imdb database.
24 * Import it via amdbfront.
25 * Get the dsn alias from lib/plugin/SqlResult.ini
31 $ini = parse_ini_file(FindFile("config/SqlResult.ini"));
33 if ($DBParams['dbtype'] == 'SQL') {
34 $this->_dbh = DB::connect($dsn);
35 $this->_dbtype = "PearDB";
37 if ($DBParams['dbtype'] != 'ADODB') {
38 // require_once('lib/WikiDB/adodb/adodb-errorhandler.inc.php');
39 require_once('lib/WikiDB/adodb/adodb-pear.inc.php');
41 $parsed = parseDSN($dsn);
42 $this->_dbh = &ADONewConnection($parsed['phptype']);
43 $conn = $this->_dbh->Connect($parsed['hostspec'],$parsed['username'],
44 $parsed['password'], $parsed['database']);
45 $this->_dbtype = "ADODB";
47 $this->_title_1_1 = array
48 ("business","moviebudgets","colorinfo","mpaaratingsreasons");
49 $this->_title_1_n = array
50 ("akatitles","alternateversions",
51 "miscellaneouscompanies","moviecountries",
52 "certificates","completecast","completecrew","crazycredits",
53 "genres","goofs","keywords","movielinks","plot","quotes","ratings","soundtracks",
54 "specialeffectscompanies",
55 "taglines","trivia","distributors","language","laserdisc","literature",
56 "locations","miscellaneouscompanies",
57 "productioncompanies","releasedates","runningtimes","soundmix","technical"
61 /* key accessors. return a hash */
62 function title($title_id) {
63 $result = $this->_dbh->genericSqlIter("SELECT m.title, m.date FROM movies as m WHERE m.title_id = '$title_id'");
64 return $result->next();
66 function name($name_id) {
67 $result = $this->_dbh->genericSqlIter
68 ("SELECT n.name, b.* "
70 ." LEFT JOIN biographies AS b USING (name_id)"
71 ." WHERE n.name_id = '$name_id' ");
72 return $result->next();
75 /* main movie information, with just the top names */
76 /* 1:1 title info: moviebudgets, colorinfo, business? */
77 function movie($title_id) {
78 $result = $this->_dbh->genericSqlIter("SELECT m.title, m.date"
80 ." WHERE m.title_id = '$title_id'");
81 $movie = $result->next();
82 $movie['title_id'] = $title_id;
83 /* add the individual results to hash */
84 foreach (array_merge($this->_title_1_1,$this->_title_1_n) as $accessor) {
85 if (method_exists($this,$accessor))
86 $iter = $this->$accessor($title_id);
88 $iter = $this->_titleQuery($accessor, $accessor, $title_id);
89 while ($row = $iter->next()) {
90 $movie[$accessor][] = $row;
93 // add the names also?
97 function movie_main($title_id) {
98 return movie($title_id);
100 /* full movie information, with full cast and crew */
101 function movie_full($title_id) {
102 $movie = $this->movie($title_id);
103 /* add the individual results to hash */
104 foreach (array_merge($this->_cast,$this->_crew) as $accessor) {
105 if (method_exists($this,$accessor))
106 $iter = $this->$accessor($title_id);
108 $iter = $this->_titleQuery($accessor, $accessor, $title_id);
109 while ($row = $iter->next()) {
110 $movie[$accessor][] = $row;
116 /* combined movie information */
117 function movie_combined($title_id) {
118 $movie = $this->movie($title_id);
119 /* add the individual results to hash */
120 foreach (array_merge($this->_combined) as $accessor) {
121 if (method_exists($this,$accessor))
122 $iter = $this->$accessor($title_id);
124 $iter = $this->_titleQuery($accessor, $accessor, $title_id);
125 while ($row = $iter->next()) {
126 $movie[$accessor][] = $row;
132 /* movie company_credits information */
133 function movie_company_credits($title_id) {
134 $movie = $this->movie($title_id);
135 /* add the individual results to hash */
136 foreach (array_merge($this->_company_credits) as $accessor) {
137 if (method_exists($this,$accessor))
138 $iter = $this->$accessor($title_id);
140 $iter = $this->_titleQuery($accessor, $accessor, $title_id);
141 while ($row = $iter->next()) {
142 $movie[$accessor][] = $row;
148 /* 1:n title subselects: possibly multiple rows per title */
149 /* accessors with same field and tablename are not needed */
150 function _titleQuery($field, $table, $title_id) {
151 return $this->_dbh->genericSqlIter
152 ("SELECT $field FROM $table WHERE title_id = '$title_id'");
154 function akatitles($title_id) {
155 return $this->_titleQuery("akatitle", "akatitles", $title_id);
157 function business($title_id) {
158 return $this->_titleQuery("*", "business", $title_id);
160 function moviebudgets($title_id) {
161 return $this->_titleQuery("b.currency, b.budget, b.info", "moviebudgets as b", $title_id);
163 function completecast($title_id) {
164 return $this->_titleQuery("cast", "completecast", $title_id);
166 function completecrew($title_id) {
167 return $this->_titleQuery("crew", "completecrew", $title_id);
169 function genres($title_id) {
170 return $this->_titleQuery("genre", "genres", $title_id);
173 function goofs($title_id) {
174 return $this->_titleQuery("text", "goofs", $title_id);
177 function keywords($title_id) {
178 return $this->_titleQuery("keyword", "keywords", $title_id);
180 // (ml_id, description) values (1, 'followed by');
181 function movielinks($title_id) {
182 return $this->_dbh->genericSqlIter
183 ("SELECT m.title,ml.description, mref.title AS title_ref"
184 ." FROM movielinks AS l, ml, movies as m, movies AS mref"
185 ." WHERE l.title_ref = mref.title_id AND l.title_id = mref.title_id"
186 ." AND l.ml_id = ml.ml_id AND l.title_id = '$title_id'");
189 function plot($title_id) {
190 return $this->_titleQuery("*", "plot", $title_id);
193 function quotes($title_id) {
194 return $this->_titleQuery("*", "quotes", $title_id);
197 function ratings($title_id) {
198 return $this->_titleQuery("*", "ratings", $title_id);
200 function soundtracks($title_id) {
201 return $this->_titleQuery("*", "soundtracks", $title_id);
203 function specialeffectscompanies($title_id) {
204 return $this->_titleQuery("*", "specialeffectscompanies", $title_id);
207 function taglines($title_id) {
208 return $this->_titleQuery("content", "taglines", $title_id);
211 function trivia($title_id) {
212 return $this->_titleQuery("content", "trivia", $title_id);
214 function distributors($title_id) {
215 return $this->_titleQuery("distributor", "distributors", $title_id);
217 function language($title_id) {
218 return $this->_titleQuery("language", "language", $title_id);
220 function laserdisc($title_id) {
221 return $this->_titleQuery("content", "laserdisc", $title_id);
223 function literature($title_id) {
224 return $this->_titleQuery("literature", "literature", $title_id);
226 function locations($title_id) {
227 return $this->_titleQuery("location", "locations", $title_id);
229 function miscellaneouscompanies($title_id) {
230 return $this->_titleQuery("company", "miscellaneouscompanies", $title_id);
232 function mpaaratingsreasons($title_id) {
233 return $this->_titleQuery("mpaarating", "mpaaratingsreasons", $title_id);
235 function productioncompanies($title_id) {
236 return $this->_titleQuery("company", "productioncompanies", $title_id);
238 function soundmix($title_id) {
239 return $this->_titleQuery("soundmix", "soundmix", $title_id);
241 function technical($title_id) {
242 return $this->_titleQuery("technical", "technical", $title_id);
244 function releasedates($title_id) {
245 return $this->_dbh->genericSqlIter
246 ("SELECT c.country,r.releasedate,r.info"
247 ." FROM country as c, releasedates AS r"
248 ." WHERE c.country_id = r.country_id AND r.title_id = '$title_id'");
250 function runningtimes($title_id) {
251 return $this->_dbh->genericSqlIter
252 ("SELECT c.country,r.time,,r.info"
253 ." FROM country as c, runningtimes AS r"
254 ." WHERE c.country_id = r.country_id AND r.title_id = '$title_id'");
256 function moviecountries($title_id) {
257 return $this->_dbh->genericSqlIter
258 ("SELECT c.country FROM country AS c, moviecountries AS m"
259 ." WHERE c.country_id = m.country_id AND m.title_id = '$title_id'");
261 function certificates($title_id) {
262 return $this->_dbh->genericSqlIter
263 ("SELECT co.country, c.certificate, c.info"
264 ." FROM country as co, certificates as c"
265 ." WHERE co.country_id = c.country_id AND c.title_id = '$title_id'"
266 ." GROUP BY c.country_id");
269 /* 1:n name subselects: possibly multiple rows per name */
271 create table akanames (name_id integer unsigned not null, akaname varchar(255) not null);
272 create table biographies (name_id integer unsigned not null, RN text, NK text, DB text, DD text, HT text, BG text, BO text, BT text, PI text, OW text, TR text, QU text, SA text, WN text, SP text, TM text, IT text, AT text, PT text, CV text, AG text, primary key (name_id));
273 create table guestappearances (name_id integer unsigned not null, title_id integer unsigned not null, role varchar(255));
274 create table characters (name_id integer unsigned not null, title_id integer unsigned not null, role varchar(255), position integer unsigned, job_id tinyint unsigned not null);
276 function akanames($name_id) {
277 return $this->_dbh->genericSqlIter
278 ("SELECT akanames FROM akanames WHERE name_id = '$name_id'");
280 function guestappearances($name_id) {
281 return $this->_dbh->genericSqlIter
282 ("SELECT g.role, m.movie, m.date"
283 ." FROM guestappearances as g"
284 ." WHERE g.name_id = '$name_id' AND g.title_id = m.title_id");
286 function biographies($name_id) {
287 return $this->_dbh->genericSqlIter
288 ("SELECT n.name, b.*"
290 ." LEFT JOIN biographies as b USING (name_id)"
291 ." WHERE n.name_id = '$name_id' ");
294 /* Search functions */
295 function searchTitle($title) {
296 return $this->_search($title, '_sql_title_clause',
297 "SELECT m.title_id, m.title, m.date".
300 "ORDER BY m.date DESC");
303 function searchName($name) {
304 return $this->_search($name, '_sql_name_clause',
305 "SELECT n.name_id, n.name, j.description, c.role, m.title_id, m.title".
306 " FROM names as n, jobs as j, characters as c, movies as m".
307 " WHERE n.name_id = c.name_id".
308 " AND m.title_id = c.title_id".
309 " AND c.job_id = j.job_id".
311 "GROUP BY m.title_id ORDER BY j.description");
315 // quote the LIKE argument and construct the WHERE clause
316 function _sql_match_clause($field, $word) {
317 //not sure if we need this. ADODB may do it for us
318 $word = preg_replace('/(?=[%_\\\\])/', "\\", $word);
319 // (we need it for at least % and _ --- they're the wildcard characters
320 // for the LIKE operator, and we need to quote them if we're searching
321 // for literal '%'s or '_'s. --- I'm not sure about \, but it seems to
323 $word = $this->_dbh->qstr("%".strtolower($word)."%");
324 return "LOWER($field) LIKE $word";
327 function _sql_title_clause($word) {
328 return $this->_sql_match_clause("title",$word);
330 function _sql_name_clause($word) {
331 return $this->_sql_match_clause("name",$word);
334 function _search($what, $callback_fn, $query, $order = '') {
335 include_once("lib/TextSearchQuery.php");
336 // $dbh = $GLOBALS['request']->getDbh();
337 //TODO: check if the db is mysql resp. capable of google like search.
338 // postgresql tsearch2 for example
339 // See TextSearchQuery.php
340 $search = new TextSearchQuery($what);
341 $callback = new WikiMethodCb($this, $callback_fn);
342 $search_clause = $search->makeSqlClause($callback);
343 $result = $this->_dbh->genericSqlIter($query . " " . $search_clause . " " . $order);
347 // all movies with actor:
348 SELECT m.title, m.date, n.name, c.role
349 FROM movies as m, names as n, jobs as j, characters as c
350 WHERE n.name LIKE "%%where%%"
351 AND m.title_id = c.title_id
352 AND n.name_id = c.name_id
353 AND c.job_id = j.job_id
354 AND j.description = 'Actor'
364 // c-hanging-comment-ender-p: nil
365 // indent-tabs-mode: nil