00001 <?php
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022 require_once("./cfg/config.inc");
00023 require_once("xml.php");
00024
00038 class DB {
00039
00041 private $appdb_dblink;
00043 private $bugzilla_dblink;
00045 private $XML;
00046
00047 public:
00056 function __construct(){
00057
00059 global $appdb_host;
00060 global $appdb_base;
00061 global $appdb_user;
00062 global $appdb_pass;
00063
00064 global $bugzilla_host;
00065 global $bugzilla_base;
00066 global $bugzilla_user;
00067 global $bugzilla_pass;
00068
00070
00076 $this->appdb_dblink = mysql_pconnect($appdb_host, $appdb_user, $appdb_pass) or die("[ee] Could not connect to appdb database: " . mysql_error());
00077 mysql_select_db($appdb_base, $this->appdb_dblink) or die("[ee] Could not select appdb database");
00078
00080 $this->bugzilla_dblink = mysql_pconnect($bugzilla_host, $bugzilla_user, $bugzilla_pass) or die("[ee] Could not connect to appdb database: " . mysql_error());
00081 mysql_select_db($bugzilla_base, $this->bugzilla_dblink) or die("[ee] Could not select appdb database");
00082
00084 $this->XML = new XMLExport();
00085 return;
00086 }
00087
00102 function exportAppByName($search, $page = 1){
00105 $search = substr($search, 0, 20);
00106
00108 $page = (int)$page;
00109 if ($page <= 0){
00110 $sql_page = 0;
00111 } else {
00112 $sql_page = ($page - 1) * 10;
00113 }
00114
00115 global $appdb_base;
00116
00118 $search = mysql_real_escape_string($search, $this->appdb_dblink);
00119 $query = "SELECT COUNT(appId) FROM {$appdb_base}.appFamily WHERE appName LIKE \"%${search}%\" AND state='accepted'";
00120 $result = mysql_query($query, $this->appdb_dblink);
00121 if (!$result) {
00122 die("serachAppByName: Invalid query");
00123 }
00124 if (mysql_num_rows($result) > 0){
00126 $row = mysql_fetch_row($result);
00127 $xml_view = $this->XML->openHeader(1);
00128 $xml_view .= $this->XML->createPages($page, ceil($row[0]/10));
00129 }
00130
00132 mysql_free_result($result);
00133
00135 $query = "SELECT appId, appName, description, catId, webPage FROM {$appdb_base}.appFamily WHERE appName LIKE \"%${search}%\" AND state='accepted' LIMIT ${sql_page}, 9";
00136 $result = mysql_query($query, $this->appdb_dblink);
00137 if (!$result) {
00138 die("serachAppByName: Invalid query");
00139 }
00140
00141 if (mysql_num_rows($result) > 0){
00142 $xml_view .= "\n <app-list>";
00143 while ($row = mysql_fetch_assoc($result)) {
00146 $appver_list = $this->getVersion($row['appId']);
00148 $xml_view .= $this->XML->createAppInfo($row['appId'], 0, $row['appName'], $row['description'], $row['catId'], $row['webPage'], $appver_list);
00149 }
00150 $xml_view .= "\n </app-list>";
00151 } else {
00153 $xml_view = $this->XML->openHeader(6);
00154 $xml_view .= "<message>No matches found</message>";
00155 }
00156
00158 $xml_view .= $this->XML->closeHeader();
00159
00161 mysql_free_result($result);
00162 return $xml_view;
00163 }
00164
00166
00174 function exportAppById($appid){
00176 $appid = (int)$appid;
00177 if ($appid <= 0)
00178 return;
00179
00181 $xml_view = $this->XML->openHeader(3);
00184 $xml_view .= $this->getApp($appid);
00186 $xml_view .= $this->XML->closeHeader();
00187
00188 return $xml_view;
00189 }
00190
00192
00201 function exportTestResults($appid, $verid = 0, $testid = 0){
00203 $appid = (int)$appid;
00204 if ($appid < 0)
00205 return;
00206
00208 $verid = (int)$verid;
00209 if ($verid < 0)
00210 return;
00211
00213 $testid = (int)$testid;
00214 if ($testid < 0)
00215 return;
00216
00218 $xml_view = $this->XML->openHeader(4);
00221 $xml_view .= $this->getApp($appid, $verid, $testid);
00223 $xml_view .= $this->XML->closeHeader();
00224
00225 return $xml_view;
00226 }
00227
00229
00236 function exportCategory($catid){
00238 $catid = (int)$catid;
00239 if ($catid < 0)
00240 return;
00241
00242 global $appdb_base;
00243
00245 $xml_view = $this->XML->openHeader(5);
00246 $xml_view .= "\n <category-list>";
00247 $xml_view .= $this->XML->createCategoryInfo(0, "Main", "");
00248 if ($catid>0){
00251 $xml_view .= $this->getCategory($catid);
00252 }
00253 $xml_view .= "\n </category-list>";
00254
00256 $query = "SELECT catId, catName, catDescription FROM {$appdb_base}.appCategory WHERE catParent={$catid}";
00257 $result = mysql_query($query, $this->appdb_dblink);
00258 if (!$result) {
00259 die("serachCategoryById: Invalid query");
00260 }
00261
00262 if (mysql_num_rows($result) > 0){
00263 $xml_view .= "\n <subcategory-list>";
00264 while ($row = mysql_fetch_assoc($result)){
00266 $xml_view .= $this->XML->createCategoryInfo($row['catId'], $row['catName'], $row['catDescription']);
00267 }
00268 $xml_view .= "\n </subcategory-list>";
00269 }
00271 mysql_free_result($result);
00272
00274 $query = "SELECT appId, appName, description FROM {$appdb_base}.appFamily WHERE state='accepted' and catId={$catid}";
00275 $result = mysql_query($query, $this->appdb_dblink);
00276 if (!$result) {
00277 die("serachCategoryById: Invalid query");
00278 }
00279
00280 if (mysql_num_rows($result) > 0){
00281 $xml_view .= "\n <app-list>";
00282 while ($row = mysql_fetch_assoc($result)){
00284 $xml_view .= $this->XML->createCategoryAppInfo($row['appId'], $row['appName'], $row['description']);
00285 }
00286 $xml_view .= "\n </app-list>";
00287 }
00288
00290 $xml_view .= $this->XML->closeHeader();
00292 mysql_free_result($result);
00293 return $xml_view;
00294 }
00295
00296 private:
00298
00305 function getVersion($appid){
00307 $appid = (int)$appid;
00308 if ($appid <= 0)
00309 return;
00310
00311 global $appdb_base;
00312
00314 $query = "SELECT versionId, versionName, rating, ratingRelease FROM {$appdb_base}.appVersion WHERE appId={$appid} AND state='accepted'";
00315
00316 $result = mysql_query($query, $this->appdb_dblink);
00317 if (!$result) {
00318 die("getVersion: Invalid query");
00319 }
00320
00321 if (mysql_num_rows($result) > 0){
00322 while ($row = mysql_fetch_assoc($result)) {
00324 $xml_view .= $this->XML->createAppVersionInfo($row['versionId'], $row['versionName'], $row['rating'], $row['ratingRelease']);
00325 }
00326 }
00327
00329 mysql_free_result($result);
00330 return $xml_view;
00331 }
00332
00334
00345 function getApp($appid, $verid=0, $testid=0){
00347 $appid = (int)$appid;
00348 if ($appid <= 0)
00349 return;
00350
00352 $verid = (int)$verid;
00353 $testid = (int)$testid;
00354
00355 global $appdb_base;
00356
00358 $query = "SELECT appId, appName, description, catId, webPage FROM {$appdb_base}.appFamily WHERE appId={$appid} AND state='accepted' LIMIT 0, 1";
00359 $result = mysql_query($query, $this->appdb_dblink);
00360 if (!$result) {
00361 die("getApp: Invalid app info query");
00362 }
00363
00364 if (mysql_num_rows($result) > 0){
00365 $row = mysql_fetch_assoc($result);
00367 $category_list = $this->XML->createCategoryInfo(0, "Main", "");
00370 $category_list .= $this->getCategory($row['catId']);
00371
00372 if ($verid<=0){
00375 $versions_list = $this->getVersion($row['appId']);
00376 } else {
00382 $test_results = $this->getTestResults($verid, $testid);
00383 $comment_list = $this->getNotes($appid, $verid);
00384 $comment_list .= $this->getComments($verid);
00385 $bugs_list = $this->getBugs($verid);
00386 }
00388 $xml_view .= $this->XML->createAppInfo($row['appId'], $verid, $row['appName'], $row['description'], $row['catId'], $row['webPage'], $versions_list, $category_list, $test_results, $comment_list, $verinfo, $bugs_list);
00389 }
00390
00392 mysql_free_result($result);
00393 return $xml_view;
00394 }
00395
00397
00405 function getTestResults($verid, $testid){
00407 $verid = (int)$verid;
00408 $testid = (int)$testid;
00409
00410 if (($testid <= 0) and ($verid <= 0))
00411 return;
00412
00413 global $appdb_base;
00414
00416 if ($testid <= 0){
00417 $query = "SELECT testingId, testedRating, testedRelease, whatWorks, whatDoesnt, whatNotTested, comments, versionId FROM {$appdb_base}.testResults WHERE versionId={$verid} AND state='accepted' ORDER BY submitTime DESC LIMIT 0 , 1";
00418 } else {
00419 $query = "SELECT testingId, testedRating, testedRelease, whatWorks, whatDoesnt, whatNotTested, comments, versionId FROM {$appdb_base}.testResults WHERE testingId={$testid} AND state='accepted' LIMIT 0 , 1";
00420 }
00421
00422 $result = mysql_query($query, $this->appdb_dblink);
00423 if (!$result) {
00424 die("getTestResults: Invalid app info query");
00425 }
00426
00427 if (mysql_num_rows($result) > 0){
00428 $row = mysql_fetch_array($result);
00430 $query = "SELECT versionName, license FROM {$appdb_base}.appVersion WHERE versionId={$row[7]}";
00431 $result = mysql_query($query, $this->appdb_dblink);
00432 if (!$result) {
00433 die("getTestResults: Invalid app info query");
00434 }
00435 if (mysql_num_rows($result) > 0){
00436 $verrow = mysql_fetch_array($result);
00437 }
00438
00440 $xml_view = $this->XML->createAppTestingInfo($row[0], $row[1], $row[2], $row[3], $row[4], $row[5], $row[6], $verrow[0], $verrow[1]);
00443 $xml_view .= $this->getTestResults_Top5($verid, $row[0]);
00444 }
00445
00447 mysql_free_result($result);
00448 return $xml_view;
00449 }
00450
00452
00459 function getCategory($catid){
00461 $catid = (int)$catid;
00462 if ($catid < 0)
00463 return;
00464
00465 global $appdb_base;
00466
00468 $query = "SELECT catId, catName, catDescription, catParent FROM {$appdb_base}.appCategory WHERE catId={$catid} LIMIT 0, 1";
00469 $result = mysql_query($query, $this->appdb_dblink);
00470 if (!$result) {
00471 die("getCategory: Invalid category info query");
00472 }
00473
00474 if (mysql_num_rows($result) > 0){
00475 $row = mysql_fetch_assoc($result);
00476 if ($row['catParent']>0){
00478 $xml_view .= $this->getCategory($row['catParent']);
00479 }
00481 $xml_view .= $this->XML->createCategoryInfo($row['catId'], $row['catName'], $row['catDescription']);
00482 }
00484 mysql_free_result($result);
00485 return $xml_view;
00486 }
00487
00489
00497 function getTestResults_Top5($verid, $curtest){
00499 $verid = (int)$verid;
00500 if ($verid < 0)
00501 return;
00502
00503 $curtest = (int)$curtest;
00504 if ($curtest < 0)
00505 return;
00506
00507 global $appdb_base;
00509 $query = "SELECT testingId, testedRating, testedRelease, DATE_FORMAT(testedDate, '%e %b %Y'), (SELECT name FROM {$appdb_base}.distributions WHERE {$appdb_base}.distributions.distributionId={$appdb_base}.testResults.distributionId), installs, runs FROM {$appdb_base}.testResults WHERE versionId={$verid} AND state='accepted' ORDER BY submitTime DESC LIMIT 0 , 5";
00510
00511 $result = mysql_query($query, $this->appdb_dblink);
00512 if (!$result) {
00513 die("getTestResults_Top5: Invalid app info query!");
00514 }
00515
00516 if (mysql_num_rows($result) > 0){
00517 $xml_view = "\n <test-list>";
00518 while ($row = mysql_fetch_array($result)){
00519 if ($curtest==$row[0]){
00520 $curr=1;
00521 } else {
00522 $curr=0;
00523 }
00525 $xml_view .= $this->XML->createTop5TestResults($row[0], $row[1], $row[2], $row[3], $row[4], $row[5], $row[6], $curr);
00526 }
00527 $xml_view .= "\n </test-list>";
00528 }
00530 mysql_free_result($result);
00531 return $xml_view;
00532 }
00533
00535
00543 function getComments($verid, $parent=0){
00544 $verid = (int)$verid;
00545 if ($verid <= 0)
00546 return;
00547
00548 $parent = (int)$parent;
00549 if ($parent < 0)
00550 return;
00551
00552 global $appdb_base;
00554 $query = "SELECT commentId, parentId, (SELECT realname FROM {$appdb_base}.user_list WHERE userid={$appdb_base}.appComments.userId), subject, body, DATE_FORMAT(time, '%e %b %Y %H:%i:%s') FROM {$appdb_base}.appComments WHERE versionId={$verid} AND parentId={$parent} ORDER BY time DESC";
00555 $result = mysql_query($query, $this->appdb_dblink);
00556 if (!$result) {
00557 die("getComments: Invalid comments info query");
00558 }
00559
00560 if (mysql_num_rows($result) > 0){
00561
00562 while ($row = mysql_fetch_array($result)){
00564 $xml_view .= $this->XML->createComment($row[0], $row[1], $row[2], $row[3], $row[4], $row[5]);
00566 $xml_view .= $this->getComments($verid, $row[0]);
00567 }
00568 }
00570 mysql_free_result($result);
00571 return $xml_view;
00572 }
00573
00575
00583 function getNotes($appid, $verid){
00584 $appid = (int)$appid;
00585 if ($appid <= 0)
00586 return;
00587
00588 $verid = (int)$verid;
00589 if ($verid <= 0)
00590 return;
00591
00592 global $appdb_base;
00594 $query = "SELECT noteTitle, noteDesc, DATE_FORMAT(submitTime, '%e %b %Y %H:%i:%s'), (SELECT realname FROM {$appdb_base}.user_list WHERE userid={$appdb_base}.appNotes.submitterId) FROM {$appdb_base}.appNotes WHERE versionId={$verid} OR (appId={$appid} AND versionId=0) ORDER BY noteId";
00595 $result = mysql_query($query, $this->appdb_dblink);
00596 if (!$result) {
00597 die("getComments: Invalid comments info query " . $query);
00598 }
00599
00600 if (mysql_num_rows($result) > 0){
00601 while ($row = mysql_fetch_array($result)){
00603 $xml_view .= $this->XML->createComment(0, 0, $row[5], $row[0], $row[1], $row[2]);
00604 }
00605 }
00607 mysql_free_result($result);
00608 return $xml_view;
00609 }
00610
00612
00619 function getBugs($appid){
00620 $appid = (int)$appid;
00621 if ($appid <= 0)
00622 return;
00623
00624 global $bugzilla_base;
00625 global $appdb_base;
00626
00628 $query = "SELECT bug_id FROM {$appdb_base}.buglinks WHERE versionId={$appid} AND state='accepted' ORDER BY bug_id";
00629 $result = mysql_query($query, $this->appdb_dblink);
00630 if (!$result) {
00631 die("getBugs: Invalid comments info query");
00632 }
00633
00634 if (mysql_num_rows($result) > 0){
00635 while ($row = mysql_fetch_array($result)){
00637 $bug_query = "SELECT bug_id, bug_status, resolution, short_desc FROM {$bugzilla_base}.bugs WHERE bugs.bug_id={$row[0]} AND (bugs.bug_status='NEW' OR bugs.bug_status='UNCONFIRMED') LIMIT 0, 1";
00638 $bug_result = mysql_query($bug_query, $this->bugzilla_dblink);
00639 if (!$bug_result) {
00640 die("bug getBugs: Invalid comments info query");
00641 }
00642
00643 if (mysql_num_rows($bug_result) > 0){
00644 $bug_row = mysql_fetch_array($bug_result);
00645 $xml_view .= $this->XML->createBugList($bug_row[0], $bug_row[1], $bug_row[2], $bug_row[3]);
00646 }
00648 mysql_free_result($bug_result);
00649 }
00650 }
00652 mysql_free_result($result);
00653 return $xml_view;
00654 }
00655
00656 };
00657
00658 ?>