PHP dbconnection::queryArray方法代码示例

 public static function getOverlaysForGame($pack)
 {
     $sql_overlays = dbconnection::queryArray("SELECT * FROM overlays WHERE game_id = '{$pack->game_id}'");
     $overlays = array();
     for ($i = 0; $i < count($sql_overlays); $i++) {
         if ($ob = overlays::overlayObjectFromSQL($sql_overlays[$i])) {
             $overlays[] = $ob;
         }
     }
     return new return_package(0, $overlays);
 }

 public static function getGroupsForGame($pack)
 {
     $sql_groups = dbconnection::queryArray("SELECT * FROM groups WHERE game_id = '{$pack->game_id}'");
     $groups = array();
     for ($i = 0; $i < count($sql_groups); $i++) {
         if ($ob = groups::groupObjectFromSQL($sql_groups[$i])) {
             $groups[] = $ob;
         }
     }
     return new return_package(0, $groups);
 }

 public static function getNoteCommentsForNote($pack)
 {
     $sql_note_comments = dbconnection::queryArray("SELECT note_comments.*, users.user_name, users.display_name FROM note_comments LEFT JOIN users ON note_comments.user_id = users.user_id WHERE game_id = '{$pack->game_id}' AND note_id = '{$pack->note_id}' ORDER BY note_comments.created ASC");
     $note_comments = array();
     for ($i = 0; $i < count($sql_note_comments); $i++) {
         if (!($ob = note_comments::noteCommentObjectFromSQL($sql_note_comments[$i]))) {
             continue;
         }
         $note_comments[] = $ob;
     }
     return new return_package(0, $note_comments);
 }

 public static function getLeaderboard($pack)
 {
     $insts = dbconnection::queryArray("SELECT * FROM instances WHERE game_id = '{$pack->game_id}' AND object_type = 'ITEM' AND object_id = '{$pack->item_id}' AND owner_type = 'USER' ORDER BY qty DESC LIMIT 10;");
     $entries = array();
     for ($i = 0; $i < count($insts); $i++) {
         $inst = $insts[$i];
         $user = dbconnection::queryObject("SELECT * FROM users WHERE user_id = '{$inst->owner_id}';");
         $entries[$i] = new stdClass();
         $entries[$i]->qty = $inst->qty;
         if ($user) {
             $entries[$i]->user_id = $user->user_id;
             $entries[$i]->user_name = $user->user_name;
             $entries[$i]->display_name = $user->display_name;
         } else {
             $entries[$i]->user_id = 0;
             $entries[$i]->user_name = "(user not found)";
             $entries[$i]->display_name = "(user not found)";
         }
     }
     return new return_package(0, $entries);
 }

 public static function upgrade($pack)
 {
     //find existing upgrades
     $upgrade_files = scandir("db/upgrades", 0);
     $existing_upgrades = array();
     for ($i = 0; $i < count($upgrade_files); $i++) {
         $file = $upgrade_files[$i];
         if (!preg_match("@\\d\\d*\\.\\d\\d*\\.sql@is", $file)) {
             continue;
         }
         //file of form #.#.sql
         $maj = intval(substr($file, 0, strpos($file, ".")));
         $min = intval(substr($file, strpos($file, ".") + 1, -4));
         if (!isset($existing_upgrades[$maj])) {
             $existing_upgrades[$maj] = array();
         }
         $existing_upgrades[$maj][$min] = true;
     }
     //find completed upgrades
     $upgrade_records = dbconnection::queryArray("SELECT * FROM db_upgrades ORDER BY version_major ASC, version_minor ASC");
     $completed_upgrades = array();
     for ($i = 0; $i < count($upgrade_records); $i++) {
         $record = $upgrade_records[$i];
         $maj = $record->version_major;
         $min = $record->version_minor;
         if (!isset($completed_upgrades[$maj])) {
             $completed_upgrades[$maj] = array();
         }
         $completed_upgrades[$maj][$min] = true;
     }
     //find and perform incomplete upgrades
     for ($i = 0; $i < count($existing_upgrades); $i++) {
         for ($j = 0; $j < count($existing_upgrades[$i]); $j++) {
             if (!isset($completed_upgrades[$i][$j])) {
                 db::applyUpgrade(0, $i, $j);
             }
         }
     }
     return new return_package(0);
 }

 public static function deleteWebHook($pack)
 {
     $webhook = dbconnection::queryObject("SELECT * FROM web_hooks WHERE web_hook_id = '{$pack->web_hook_id}'");
     $pack->auth->game_id = $webhook->game_id;
     $pack->auth->permission = "read_write";
     if (!editors::authenticateGameEditor($pack->auth)) {
         return new return_package(6, NULL, "Failed Authentication");
     }
     dbconnection::query("DELETE FROM web_hooks WHERE web_hook_id = '{$pack->web_hook_id}' LIMIT 1");
     //cleanup
     $reqPack = dbconnection::queryObject("SELECT * FROM requirement_root_packages WHERE requirement_root_package_id = '{$webhook->requirement_root_package_id}'");
     if ($reqPack) {
         $pack->requirement_root_package_id = $reqPack->requirement_root_package_id;
         requirements::deleteRequirementPackage($pack);
     }
     $reqAtoms = dbconnection::queryArray("SELECT * FROM requirement_atoms WHERE requirement = 'PLAYER_HAS_RECEIVED_INCOMING_WEB_HOOK' AND content_id = '{$pack->web_hook_id}'");
     for ($i = 0; $i < count($reqAtoms); $i++) {
         $pack->requirement_atom_id = $reqAtoms[$i]->requirement_atom_id;
         requirements::deleteRequirementAtom($pack);
     }
     games::bumpGameVersion($pack);
     return new return_package(0);
 }

 public static function deleteTab($pack)
 {
     $tab = dbconnection::queryObject("SELECT * FROM tabs WHERE tab_id = '{$pack->tab_id}'");
     $pack->auth->game_id = $tab->game_id;
     $pack->auth->permission = "read_write";
     if (!editors::authenticateGameEditor($pack->auth)) {
         return new return_package(6, NULL, "Failed Authentication");
     }
     dbconnection::query("DELETE FROM tabs WHERE tab_id = '{$pack->tab_id}' LIMIT 1");
     //cleanup
     $options = dbconnection::queryArray("SELECT * FROM dialog_options WHERE link_type = 'EXIT_TO_TAB' AND link_id = '{$pack->tab_id}'");
     for ($i = 0; $i < count($options); $i++) {
         $pack->dialog_option_id = $options[$i]->dialog_option_id;
         dialogs::deleteDialogOption($pack);
     }
     $reqPack = dbconnection::queryObject("SELECT * FROM requirement_root_packages WHERE requirement_root_package_id = '{$tab->requirement_root_package_id}'");
     if ($reqPack) {
         $pack->requirement_root_package_id = $reqPack->requirement_root_package_id;
         requirements::deleteRequirementPackage($pack);
     }
     games::bumpGameVersion($pack);
     return new return_package(0);
 }

 public static function getMediaForGame($pack)
 {
     $sql_medias = dbconnection::queryArray("SELECT * FROM media WHERE (game_id = '{$pack->game_id}' OR (game_id = 0 AND user_id = 0))");
     $medias = array();
     for ($i = 0; $i < count($sql_medias); $i++) {
         if ($ob = media::mediaObjectFromSQL($sql_medias[$i])) {
             $medias[] = $ob;
         }
     }
     return new return_package(0, $medias);
 }

 public static function getUsersForFuzzySearch($pack)
 {
     $sql_users = dbconnection::queryArray("SELECT * FROM users WHERE user_name LIKE '%{$pack->search}%' OR display_name LIKE '%{$pack->search}%' OR email LIKE '%{$pack->search}%'");
     $users = array();
     for ($i = 0; $i < count($sql_users); $i++) {
         if ($ob = users::userObjectFromSQL($sql_users[$i])) {
             $users[] = $ob;
         }
     }
     return new return_package(0, $users);
 }

 public static function cleanOrphans($pack)
 {
     $tables = array();
     $tables[] = "dialog_characters";
     $tables[] = "dialog_options";
     $tables[] = "dialog_scripts";
     $tables[] = "dialogs";
     $tables[] = "event_packages";
     $tables[] = "events";
     $tables[] = "factories";
     $tables[] = "game_comments";
     $tables[] = "instances";
     $tables[] = "items";
     $tables[] = "media";
     $tables[] = "note_comments";
     $tables[] = "note_likes";
     $tables[] = "notes";
     $tables[] = "object_tags";
     $tables[] = "overlays";
     $tables[] = "plaques";
     $tables[] = "quests";
     $tables[] = "requirement_and_packages";
     $tables[] = "requirement_atoms";
     $tables[] = "requirement_root_packages";
     $tables[] = "scenes";
     $tables[] = "tabs";
     $tables[] = "tags";
     $tables[] = "triggers";
     $tables[] = "user_game_scenes";
     $tables[] = "user_games";
     $tables[] = "web_hooks";
     $tables[] = "web_pages";
     for ($i = 0; $i < count($tables); $i++) {
         $arr = dbconnection::queryArray("SELECT {$tables[$i]}.game_id as game_id, games.game_id as n_game_id FROM {$tables[$i]} LEFT JOIN games ON {$tables[$i]}.game_id = games.game_id WHERE games.game_id IS NULL GROUP BY game_id;");
         for ($j = 0; $j < count($arr); $j++) {
             if ($tables[$i] == "media" && $arr[$j]->game_id == 0) {
                 continue;
             }
             //allow default media
             if ($pack->execute) {
                 dbconnection::query("DELETE FROM {$tables[$i]} WHERE game_id = '{$arr[$j]->game_id}';");
             } else {
                 //dry run
                 echo "DELETE FROM {$tables[$i]} WHERE game_id = '{$arr[$j]->game_id}'; (game_id = '{$arr[$j]->game_id}')\n";
             }
         }
     }
     //instances
     $types = array();
     $tables = array();
     $ids = array();
     $types[] = "PLAQUE";
     $tables[] = "plaques";
     $ids[] = "plaque_id";
     $types[] = "ITEM";
     $tables[] = "items";
     $ids[] = "item_id";
     $types[] = "DIALOG";
     $tables[] = "dialogs";
     $ids[] = "dialog_id";
     $types[] = "WEB_PAGE";
     $tables[] = "web_pages";
     $ids[] = "web_page_id";
     $types[] = "NOTE";
     $tables[] = "notes";
     $ids[] = "note_id";
     $types[] = "FACTORY";
     $tables[] = "factories";
     $ids[] = "factory_id";
     for ($i = 0; $i < count($types); $i++) {
         $arr = dbconnection::queryArray("SELECT instances.* FROM instances LEFT JOIN {$tables[$i]} ON instances.object_id = {$tables[$i]}.{$ids[$i]} WHERE instances.object_type = '{$types[$i]}' AND {$tables[$i]}.{$ids[$i]} IS NULL;");
         for ($j = 0; $j < count($arr); $j++) {
             if ($pack->execute) {
                 dbconnection::query("DELETE FROM instances WHERE instance_id = '{$arr[$j]->instance_id}';");
             } else {
                 //dry run
                 echo "DELETE FROM instances WHERE instance_id = '{$arr[$j]->instance_id}'; (game_id = '{$arr[$j]->game_id}')\n";
             }
         }
     }
     //triggers
     $arr = dbconnection::queryArray("SELECT triggers.* FROM triggers LEFT JOIN instances ON triggers.instance_id = instances.instance_id WHERE instances.instance_id IS NULL;");
     for ($j = 0; $j < count($arr); $j++) {
         if ($pack->execute) {
             dbconnection::query("DELETE FROM triggers WHERE trigger_id = '{$arr[$j]->trigger_id}';");
         } else {
             //dry run
             echo "DELETE FROM triggers WHERE trigger_id = '{$arr[$j]->trigger_id}'; (game_id = '{$arr[$j]->game_id}')\n";
         }
     }
     //requirements
     // \/ query to manually see requirement tree in SQL. nice for debugging.
     //SELECT ratom.requirement_atom_id, rand.requirement_and_package_id, rroot.requirement_root_package_id FROM requirement_atoms as ratom LEFT JOIN requirement_and_packages as rand ON ratom.requirement_and_package_id = rand.requirement_and_package_id LEFT JOIN requirement_root_packages as rroot ON rand.requirement_root_package_id = rroot.requirement_root_package_id WHERE rand.game_id = 3259;
     $arr = dbconnection::queryArray("SELECT requirement_and_packages.* FROM requirement_and_packages LEFT JOIN requirement_root_packages ON requirement_and_packages.requirement_root_package_id = requirement_root_packages.requirement_root_package_id WHERE requirement_root_packages.requirement_root_package_id IS NULL;");
     for ($j = 0; $j < count($arr); $j++) {
         if ($pack->execute) {
             dbconnection::query("DELETE FROM requirement_and_packages WHERE requirement_and_package_id = '{$arr[$j]->requirement_and_package_id}';");
         } else {
             //dry run
             echo "DELETE FROM requirement_and_packages WHERE requirement_and_package_id = '{$arr[$j]->requirement_and_package_id}'; (game_id = '{$arr[$j]->game_id}')\n";
//.........这里部分代码省略.........

 public static function deleteFactory($pack)
 {
     $factory = dbconnection::queryObject("SELECT * FROM factories WHERE factory_id = '{$pack->factory_id}'");
     $pack->auth->game_id = $factory->game_id;
     $pack->auth->permission = "read_write";
     if (!editors::authenticateGameEditor($pack->auth)) {
         return new return_package(6, NULL, "Failed Authentication");
     }
     dbconnection::query("DELETE FROM factories WHERE factory_id = '{$pack->factory_id}' LIMIT 1");
     //cleanup
     $instances = dbconnection::queryArray("SELECT * FROM instances WHERE factory_id = '{$pack->factory_id}'");
     for ($i = 0; $i < count($instances); $i++) {
         $pack->instance_id = $instances[$i]->instance_id;
         instances::deleteInstance($pack);
     }
     $instances = dbconnection::queryArray("SELECT * FROM instances WHERE object_type = 'FACTORY' AND object_id = '{$pack->factory_id}'");
     for ($i = 0; $i < count($instances); $i++) {
         $pack->instance_id = $instances[$i]->instance_id;
         instances::deleteInstance($pack);
     }
     $reqPack = dbconnection::queryObject("SELECT * FROM requirement_root_packages WHERE requirement_root_package_id = '{$factory->requirement_root_package_id}'");
     if ($reqPack) {
         $pack->requirement_root_package_id = $reqPack->requirement_root_package_id;
         requirements::deleteRequirementPackage($pack);
     }
     $reqPack = dbconnection::queryObject("SELECT * FROM requirement_root_packages WHERE requirement_root_package_id = '{$factory->trigger_requirement_root_package_id}'");
     if ($reqPack) {
         $pack->requirement_root_package_id = $reqPack->requirement_root_package_id;
         requirements::deleteRequirementPackage($pack);
     }
     games::bumpGameVersion($pack);
     return new return_package(0);
 }

 public static function deleteNote($pack)
 {
     $note = dbconnection::queryObject("SELECT * FROM notes WHERE note_id = '{$pack->note_id}'");
     $pack->auth->game_id = $note->game_id;
     $pack->auth->permission = "read_write";
     if (($pack->auth->user_id != $note->user_id || !users::authenticateUser($pack->auth)) && !editors::authenticateGameEditor($pack->auth)) {
         return new return_package(6, NULL, "Failed Authentication");
     }
     // Cleanup related items.
     $noteComments = dbconnection::queryArray("SELECT * FROM note_comments WHERE note_id = '{$pack->note_id}'");
     for ($i = 0; $i < count($note_comments); $i++) {
         $pack->note_comment_id = $noteComments[$i]->note_comment_id;
         note_comments::deleteNoteComment($pack);
     }
     // NOTE duplicated from tags.php/instances.php/triggers.php due to amf framework public methods being accessible via url.
     $tags = dbconnection::queryArray("SELECT * FROM object_tags WHERE object_type = 'NOTE' AND object_id = '{$pack->note_id}'");
     for ($i = 0; $i < count($tags); $i++) {
         $pack->object_tag_id = $tags[$i]->object_tag_id;
         dbconnection::query("DELETE FROM object_tags WHERE object_tag_id = '{$pack->object_tag_id}' LIMIT 1");
     }
     $instances = dbconnection::queryArray("SELECT * FROM instances WHERE object_type = 'NOTE' AND object_id = '{$pack->note_id}'");
     for ($i = 0; $i < count($instances); $i++) {
         $pack->instance_id = $instances[$i]->instance_id;
         dbconnection::query("DELETE FROM instances WHERE instance_id = '{$pack->instance_id}' LIMIT 1");
         $triggers = dbconnection::queryArray("SELECT * FROM triggers WHERE instance_id = '{$pack->instance_id}'");
         for ($i = 0; $i < count($triggers); $i++) {
             $pack->trigger_id = $triggers[$i]->trigger_id;
             dbconnection::query("DELETE FROM triggers WHERE trigger_id = '{$pack->trigger_id}' LIMIT 1");
             // TODO fix and clean the rest of the hierarchy (requirement package)
         }
     }
     // After everything is cleaned up.
     dbconnection::query("DELETE FROM notes WHERE note_id = '{$pack->note_id}' LIMIT 1");
     games::bumpGameVersion($pack);
     return new return_package(0);
 }

 public static function deleteEventPackage($pack)
 {
     $pack->auth->game_id = dbconnection::queryObject("SELECT * FROM event_packages WHERE event_package_id = '{$pack->event_package_id}'")->game_id;
     $pack->auth->permission = "read_write";
     if (!editors::authenticateGameEditor($pack->auth)) {
         return new return_package(6, NULL, "Failed Authentication");
     }
     dbconnection::query("DELETE FROM event_packages WHERE event_package_id = '{$pack->event_package_id}' LIMIT 1");
     //cleanup
     $events = dbconnection::queryArray("SELECT * FROM events WHERE event_package_id = '{$pack->event_package_id}'");
     for ($i = 0; $i < count($events); $i++) {
         $pack->event_id = $events[$i]->event_id;
         events::deleteEvent($pack);
     }
     dbconnection::query("UPDATE dialog_scripts SET event_package_id = 0 WHERE event_package_id = '{$pack->event_package_id}'");
     dbconnection::query("UPDATE plaques SET event_package_id = 0 WHERE event_package_id = '{$pack->event_package_id}'");
     dbconnection::query("UPDATE quests SET active_event_package_id = 0 WHERE active_event_package_id = '{$pack->event_package_id}'");
     dbconnection::query("UPDATE quests SET complete_event_package_id = 0 WHERE complete_event_package_id = '{$pack->event_package_id}'");
     $instances = dbconnection::queryArray("SELECT * FROM instances WHERE object_type = 'EVENT_PACKAGE' AND object_id = '{$pack->event_package_id}'");
     for ($i = 0; $i < count($instances); $i++) {
         $pack->instance_id = $instances[$i]->instance_id;
         instances::deleteInstance($pack);
     }
     games::bumpGameVersion($pack);
     return new return_package(0);
 }

 public static function noauth_deleteTrigger($pack)
 {
     //and this "fixes" the security risk...
     if (strpos($_SERVER['REQUEST_URI'], 'noauth') !== false) {
         return new return_package(6, NULL, "Attempt to bypass authentication externally.");
     }
     dbconnection::query("DELETE FROM triggers WHERE trigger_id = '{$pack->trigger_id}' LIMIT 1");
     //cleanup
     $instances = dbconnection::queryArray("SELECT * FROM instances WHERE instance_id = '{$trigger->instance_id}'");
     for ($i = 0; $i < count($instances); $i++) {
         $pack->instance_id = $instances[$i]->instance_id;
         instances::noauth_deleteInstance($pack);
     }
     $reqPack = dbconnection::queryObject("SELECT * FROM requirement_root_packages WHERE requirement_root_package_id = '{$trigger->requirement_root_package_id}'");
     if ($reqPack) {
         $pack->requirement_root_package_id = $reqPack->requirement_root_package_id;
         requirements::noauth_deleteRequirementPackage($pack);
     }
     games::bumpGameVersion($pack);
     return new return_package(0);
 }

 public static function deleteWebPage($pack)
 {
     $webpage = dbconnection::queryObject("SELECT * FROM web_pages WHERE web_page_id = '{$pack->web_page_id}'");
     $pack->auth->game_id = $webpage->game_id;
     $pack->auth->permission = "read_write";
     if (!editors::authenticateGameEditor($pack->auth)) {
         return new return_package(6, NULL, "Failed Authentication");
     }
     dbconnection::query("DELETE FROM web_pages WHERE web_page_id = '{$pack->web_page_id}' LIMIT 1");
     //cleanup
     $options = dbconnection::queryArray("SELECT * FROM dialog_options WHERE link_type = 'EXIT_TO_WEB_PAGE' AND link_id = '{$pack->web_page_id}'");
     for ($i = 0; $i < count($options); $i++) {
         $pack->dialog_option_id = $options[$i]->dialog_option_id;
         dialogs::deleteDialogOption($pack);
     }
     $tabs = dbconnection::queryArray("SELECT * FROM tabs WHERE type = 'WEB_PAGE' AND content_id = '{$pack->web_page_id}'");
     for ($i = 0; $i < count($tabs); $i++) {
         $pack->tab_id = $tabs[$i]->tab_id;
         tabs::deleteTab($pack);
     }
     $tags = dbconnection::queryArray("SELECT * FROM object_tags WHERE object_type = 'WEB_PAGE' AND object_id = '{$pack->web_page_id}'");
     for ($i = 0; $i < count($tags); $i++) {
         $pack->object_tag_id = $tags[$i]->object_tag_id;
         tags::deleteObjectTag($pack);
     }
     $instances = dbconnection::queryArray("SELECT * FROM instances WHERE object_type = 'WEB_PAGE' AND object_id = '{$pack->web_page_id}'");
     for ($i = 0; $i < count($instances); $i++) {
         $pack->instance_id = $instances[$i]->instance_id;
         instances::deleteInstance($pack);
     }
     $factories = dbconnection::queryArray("SELECT * FROM factories WHERE object_type = 'WEB_PAGE' AND object_id = '{$pack->web_page_id}'");
     for ($i = 0; $i < count($factories); $i++) {
         $pack->factory_id = $factories[$i]->factory_id;
         factories::deleteFactory($pack);
     }
     $reqAtoms = dbconnection::queryArray("SELECT * FROM requirement_atoms WHERE requirement = 'PLAYER_VIEWED_WEB_PAGE' AND content_id = '{$pack->web_page_id}'");
     for ($i = 0; $i < count($reqAtoms); $i++) {
         $pack->requirement_atom_id = $reqAtoms[$i]->requirement_atom_id;
         requirements::deleteRequirementAtom($pack);
     }
     games::bumpGameVersion($pack);
     return new return_package(0);
 }