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 along
18 * with PhpWiki; if not, write to the Free Software Foundation, Inc.,
19 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
23 * Accessors for a local imdb database.
24 * Import it via amdbfront.
25 * Get the dsn alias from lib/plugin/SqlResult.ini
33 $ini = parse_ini_file(FindFile("config/SqlResult.ini"));
35 if ($DBParams['dbtype'] == 'SQL') {
36 $this->_dbh = DB::connect($dsn);
37 $this->_dbtype = "PearDB";
39 if ($DBParams['dbtype'] != 'ADODB') {
40 // require_once('lib/WikiDB/adodb/adodb-errorhandler.inc.php');
41 require_once 'lib/WikiDB/adodb/adodb-pear.inc.php';
43 $parsed = parseDSN($dsn);
44 $this->_dbh = &ADONewConnection($parsed['phptype']);
45 $conn = $this->_dbh->Connect($parsed['hostspec'], $parsed['username'],
46 $parsed['password'], $parsed['database']);
47 $this->_dbtype = "ADODB";
49 $this->_title_1_1 = array
50 ("business", "moviebudgets", "colorinfo", "mpaaratingsreasons");
51 $this->_title_1_n = array
52 ("akatitles", "alternateversions",
53 "miscellaneouscompanies", "moviecountries",
54 "certificates", "completecast", "completecrew", "crazycredits",
55 "genres", "goofs", "keywords", "movielinks", "plot", "quotes", "ratings", "soundtracks",
56 "specialeffectscompanies",
57 "taglines", "trivia", "distributors", "language", "laserdisc", "literature",
58 "locations", "miscellaneouscompanies",
59 "productioncompanies", "releasedates", "runningtimes", "soundmix", "technical"
63 /* key accessors. return a hash */
64 function title($title_id)
66 $result = $this->_dbh->genericSqlIter("SELECT m.title, m.date FROM movies as m WHERE m.title_id = '$title_id'");
67 return $result->next();
70 function name($name_id)
72 $result = $this->_dbh->genericSqlIter
73 ("SELECT n.name, b.* "
75 . " LEFT JOIN biographies AS b USING (name_id)"
76 . " WHERE n.name_id = '$name_id' ");
77 return $result->next();
80 /* main movie information, with just the top names */
81 /* 1:1 title info: moviebudgets, colorinfo, business? */
82 function movie($title_id)
84 $result = $this->_dbh->genericSqlIter("SELECT m.title, m.date"
86 . " WHERE m.title_id = '$title_id'");
87 $movie = $result->next();
88 $movie['title_id'] = $title_id;
89 /* add the individual results to hash */
90 foreach (array_merge($this->_title_1_1, $this->_title_1_n) as $accessor) {
91 if (method_exists($this, $accessor))
92 $iter = $this->$accessor($title_id);
94 $iter = $this->_titleQuery($accessor, $accessor, $title_id);
95 while ($row = $iter->next()) {
96 $movie[$accessor][] = $row;
99 // add the names also?
103 function movie_main($title_id)
105 return movie($title_id);
108 /* full movie information, with full cast and crew */
109 function movie_full($title_id)
111 $movie = $this->movie($title_id);
112 /* add the individual results to hash */
113 foreach (array_merge($this->_cast, $this->_crew) as $accessor) {
114 if (method_exists($this, $accessor))
115 $iter = $this->$accessor($title_id);
117 $iter = $this->_titleQuery($accessor, $accessor, $title_id);
118 while ($row = $iter->next()) {
119 $movie[$accessor][] = $row;
125 /* combined movie information */
126 function movie_combined($title_id)
128 $movie = $this->movie($title_id);
129 /* add the individual results to hash */
130 foreach (array_merge($this->_combined) as $accessor) {
131 if (method_exists($this, $accessor))
132 $iter = $this->$accessor($title_id);
134 $iter = $this->_titleQuery($accessor, $accessor, $title_id);
135 while ($row = $iter->next()) {
136 $movie[$accessor][] = $row;
142 /* movie company_credits information */
143 function movie_company_credits($title_id)
145 $movie = $this->movie($title_id);
146 /* add the individual results to hash */
147 foreach (array_merge($this->_company_credits) as $accessor) {
148 if (method_exists($this, $accessor))
149 $iter = $this->$accessor($title_id);
151 $iter = $this->_titleQuery($accessor, $accessor, $title_id);
152 while ($row = $iter->next()) {
153 $movie[$accessor][] = $row;
159 /* 1:n title subselects: possibly multiple rows per title */
160 /* accessors with same field and tablename are not needed */
161 function _titleQuery($field, $table, $title_id)
163 return $this->_dbh->genericSqlIter
164 ("SELECT $field FROM $table WHERE title_id = '$title_id'");
167 function akatitles($title_id)
169 return $this->_titleQuery("akatitle", "akatitles", $title_id);
172 function business($title_id)
174 return $this->_titleQuery("*", "business", $title_id);
177 function moviebudgets($title_id)
179 return $this->_titleQuery("b.currency, b.budget, b.info", "moviebudgets as b", $title_id);
182 function completecast($title_id)
184 return $this->_titleQuery("cast", "completecast", $title_id);
187 function completecrew($title_id)
189 return $this->_titleQuery("crew", "completecrew", $title_id);
192 function genres($title_id)
194 return $this->_titleQuery("genre", "genres", $title_id);
198 function goofs($title_id)
200 return $this->_titleQuery("text", "goofs", $title_id);
204 function keywords($title_id)
206 return $this->_titleQuery("keyword", "keywords", $title_id);
209 // (ml_id, description) values (1, 'followed by');
210 function movielinks($title_id)
212 return $this->_dbh->genericSqlIter
213 ("SELECT m.title,ml.description, mref.title AS title_ref"
214 . " FROM movielinks AS l, ml, movies as m, movies AS mref"
215 . " WHERE l.title_ref = mref.title_id AND l.title_id = mref.title_id"
216 . " AND l.ml_id = ml.ml_id AND l.title_id = '$title_id'");
220 function plot($title_id)
222 return $this->_titleQuery("*", "plot", $title_id);
226 function quotes($title_id)
228 return $this->_titleQuery("*", "quotes", $title_id);
232 function ratings($title_id)
234 return $this->_titleQuery("*", "ratings", $title_id);
237 function soundtracks($title_id)
239 return $this->_titleQuery("*", "soundtracks", $title_id);
242 function specialeffectscompanies($title_id)
244 return $this->_titleQuery("*", "specialeffectscompanies", $title_id);
248 function taglines($title_id)
250 return $this->_titleQuery("content", "taglines", $title_id);
254 function trivia($title_id)
256 return $this->_titleQuery("content", "trivia", $title_id);
259 function distributors($title_id)
261 return $this->_titleQuery("distributor", "distributors", $title_id);
264 function language($title_id)
266 return $this->_titleQuery("language", "language", $title_id);
269 function laserdisc($title_id)
271 return $this->_titleQuery("content", "laserdisc", $title_id);
274 function literature($title_id)
276 return $this->_titleQuery("literature", "literature", $title_id);
279 function locations($title_id)
281 return $this->_titleQuery("location", "locations", $title_id);
284 function miscellaneouscompanies($title_id)
286 return $this->_titleQuery("company", "miscellaneouscompanies", $title_id);
289 function mpaaratingsreasons($title_id)
291 return $this->_titleQuery("mpaarating", "mpaaratingsreasons", $title_id);
294 function productioncompanies($title_id)
296 return $this->_titleQuery("company", "productioncompanies", $title_id);
299 function soundmix($title_id)
301 return $this->_titleQuery("soundmix", "soundmix", $title_id);
304 function technical($title_id)
306 return $this->_titleQuery("technical", "technical", $title_id);
309 function releasedates($title_id)
311 return $this->_dbh->genericSqlIter
312 ("SELECT c.country,r.releasedate,r.info"
313 . " FROM country as c, releasedates AS r"
314 . " WHERE c.country_id = r.country_id AND r.title_id = '$title_id'");
317 function runningtimes($title_id)
319 return $this->_dbh->genericSqlIter
320 ("SELECT c.country,r.time,,r.info"
321 . " FROM country as c, runningtimes AS r"
322 . " WHERE c.country_id = r.country_id AND r.title_id = '$title_id'");
325 function moviecountries($title_id)
327 return $this->_dbh->genericSqlIter
328 ("SELECT c.country FROM country AS c, moviecountries AS m"
329 . " WHERE c.country_id = m.country_id AND m.title_id = '$title_id'");
332 function certificates($title_id)
334 return $this->_dbh->genericSqlIter
335 ("SELECT co.country, c.certificate, c.info"
336 . " FROM country as co, certificates as c"
337 . " WHERE co.country_id = c.country_id AND c.title_id = '$title_id'"
338 . " GROUP BY c.country_id");
341 /* 1:n name subselects: possibly multiple rows per name */
343 create table akanames (name_id integer unsigned not null, akaname varchar(255) not null);
344 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));
345 create table guestappearances (name_id integer unsigned not null, title_id integer unsigned not null, role varchar(255));
346 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);
348 function akanames($name_id)
350 return $this->_dbh->genericSqlIter
351 ("SELECT akanames FROM akanames WHERE name_id = '$name_id'");
354 function guestappearances($name_id)
356 return $this->_dbh->genericSqlIter
357 ("SELECT g.role, m.movie, m.date"
358 . " FROM guestappearances as g"
359 . " WHERE g.name_id = '$name_id' AND g.title_id = m.title_id");
362 function biographies($name_id)
364 return $this->_dbh->genericSqlIter
365 ("SELECT n.name, b.*"
367 . " LEFT JOIN biographies as b USING (name_id)"
368 . " WHERE n.name_id = '$name_id' ");
371 /* Search functions */
372 function searchTitle($title)
374 return $this->_search($title, '_sql_title_clause',
375 "SELECT m.title_id, m.title, m.date" .
376 " FROM movies as m" .
378 "ORDER BY m.date DESC");
381 function searchName($name)
383 return $this->_search($name, '_sql_name_clause',
384 "SELECT n.name_id, n.name, j.description, c.role, m.title_id, m.title" .
385 " FROM names as n, jobs as j, characters as c, movies as m" .
386 " WHERE n.name_id = c.name_id" .
387 " AND m.title_id = c.title_id" .
388 " AND c.job_id = j.job_id" .
390 "GROUP BY m.title_id ORDER BY j.description");
394 // quote the LIKE argument and construct the WHERE clause
395 function _sql_match_clause($field, $word)
397 //not sure if we need this. ADODB may do it for us
398 $word = preg_replace('/(?=[%_\\\\])/', "\\", $word);
399 // (we need it for at least % and _ --- they're the wildcard characters
400 // for the LIKE operator, and we need to quote them if we're searching
401 // for literal '%'s or '_'s. --- I'm not sure about \, but it seems to
403 $word = $this->_dbh->qstr("%" . strtolower($word) . "%");
404 return "LOWER($field) LIKE $word";
407 function _sql_title_clause($word)
409 return $this->_sql_match_clause("title", $word);
412 function _sql_name_clause($word)
414 return $this->_sql_match_clause("name", $word);
417 function _search($what, $callback_fn, $query, $order = '')
419 include_once 'lib/TextSearchQuery.php';
420 // $dbh = $GLOBALS['request']->getDbh();
421 //TODO: check if the db is mysql resp. capable of google like search.
422 // postgresql tsearch2 for example
423 // See TextSearchQuery.php
424 $search = new TextSearchQuery($what);
425 $callback = new WikiMethodCb($this, $callback_fn);
426 $search_clause = $search->makeSqlClause($callback);
427 $result = $this->_dbh->genericSqlIter($query . " " . $search_clause . " " . $order);
431 // all movies with actor:
432 SELECT m.title, m.date, n.name, c.role
433 FROM movies as m, names as n, jobs as j, characters as c
434 WHERE n.name LIKE "%%where%%"
435 AND m.title_id = c.title_id
436 AND n.name_id = c.name_id
437 AND c.job_id = j.job_id
438 AND j.description = 'Actor'
448 // c-hanging-comment-ender-p: nil
449 // indent-tabs-mode: nil