/** * Opens a SQLite database; automatically creates the database if it does not exist yet. * Also automatically installs the tables needed by ThumbsUp: items and votes. * Returns an SQLiteDatabase object, which represents an open SQLite database. * Note: using a method like this allows you to lazy load the database, the database will * only be touched if/when this method is called for the first time. * * @return object SQLiteDatabase */ public static function db() { // Database not opened yet if (self::$db === NULL) { // Open or create the database if it does not exist yet self::$db = new SQLiteDatabase(THUMBSUP_DATABASE, 0666, $error) or exit($error); // Retrieve the names of all tables in the database $tables = (array) self::$db->singleQuery('SELECT name FROM sqlite_master WHERE type = \'table\''); // Create the items table if it does not exist if (!in_array('items', $tables)) { self::$db->queryExec("\n\t\t\t\t\tCREATE TABLE items (\n\t\t\t\t\t\tid INTEGER AUTOINCREMENT,\n\t\t\t\t\t\tname TEXT,\n\t\t\t\t\t\tclosed INTEGER,\n\t\t\t\t\t\tdate INTEGER,\n\t\t\t\t\t\tPRIMARY KEY (id),\n\t\t\t\t\t\tUNIQUE (name) )"); } // Create the votes table if it does not exist if (!in_array('votes', $tables)) { self::$db->queryExec("\n\t\t\t\t\tCREATE TABLE votes (\n\t\t\t\t\t\tid INTEGER AUTOINCREMENT,\n\t\t\t\t\t\titem_id INTEGER,\n\t\t\t\t\t\trating INTEGER,\n\t\t\t\t\t\tip TEXT,\n\t\t\t\t\t\tdate INTEGER,\n\t\t\t\t\t\tPRIMARY KEY (id) )"); } } // Return SQLiteDatabase object return self::$db; }
/** * Completely deletes an item, with all votes. * TODO wordpress handles. * @return void */ public function action_delete() { // Here to track down any references. wp_die("You don't need me anymore. Deleting items is Wordpress's job"); // We need an item id if (empty($_POST['post_id'])) { $error = 'No item ID posted.'; } else { // Clean item id $post_id = (int) $_POST['post_id']; // Delete both the item and the votes for it ThumbsUp_Database::db()->queryExec("\n\t\t\t\tDELETE FROM items\n\t\t\t\tWHERE id = {$post_id};\n\t\t\t\tDELETE FROM votes\n\t\t\t\tWHERE post_id = {$post_id}"); } if ($this->is_ajax) { // Return an error, or null if everything went fine exit(json_encode(isset($error) ? $error : NULL)); } // If this is not an AJAX request, print the error if (!empty($error)) { $this->template->error = $error; } // Show the item overview again $this->action_dashboard(); }
/** * Loads a previous vote from the user for the current item; stores it in item[vote]. * item[vote] = array with vote data (rating, date) if a previous vote was found * = TRUE if the user previously voted, but we couldn't retrieve the rating * = FALSE if the user has note voted on the item yet, for as far as we know * * @return boolean TRUE if a new/previous vote is found; FALSE otherwise */ protected function load_vote() { // No item loaded yet if (empty($this->item['item_id'])) { return FALSE; } global $current_user; // A new vote was cast for the current item if (isset($this->new_vote['item_id']) and $this->new_vote['item_id'] == $this->item['item_id']) { // Transfer new vote data to the item property $this->item['vote'] = $this->new_vote; // This vote is only new, if it was really registered $this->item['vote']['new'] = empty($this->new_vote['error']); // The item id is stored in item[id] already unset($this->item['vote']['item_id']); // Vote found return TRUE; } // TODO Implement Session Manager or regular ip search. // Currently the config is false so this part shouldn't run. // Config can hold if session manager is enabled, as well as // allow people to vote anonomously. // We can only retrieve a previous vote via IP if ($this->config['ip_check'] and !empty($this->ip)) { // TODO $vote = ThumbsUp_Database::db()->arrayQuery("\n\t\t\t\tSELECT rating, ip, date\n\t\t\t\tFROM votes\n\t\t\t\tWHERE item_id = {$this->item['ID']} AND ip = '{$this->ip}'\n\t\t\t\tLIMIT 1", SQLITE_ASSOC); } else { global $blog_id, $wpdb; //get_currentuserinfo(); $select = $wpdb->prepare("SELECT rating, user_id, date\n\t\t\t\tFROM {$this->tblname}\n\t\t\t\tWHERE item_id=%d AND user_id=%d AND blog_id=%d", $this->item['item_id'], $current_user->ID, $blog_id); $vote = $wpdb->get_row($select); } // Previous vote found // TODO I believe this should work the same for the wpdb results. if (!empty($vote)) { $this->item['vote'] = current($vote); $this->item['vote']['new'] = FALSE; // Vote found return TRUE; } // If no previous vote was found via IP or user, we can still look at the cookie just to // determine whether the user already voted on this item. We won't we able to look up // the rating, though, so we set item[vote] to a boolean. return $this->item['vote'] = isset($this->cookie[$this->item['item_id']]); }
/** * Loads and calculates the vote results for the current item. Stores them in item[results]. * * @return boolean TRUE if results were loaded; FALSE otherwise */ protected function load_results() { // No item loaded yet if (empty($this->item['id'])) { return FALSE; } // Join-query to retrieve vote results $results = ThumbsUp_Database::db()->arrayQuery("\n\t\t\tSELECT\n\t\t\t\tCOUNT(1) AS total_votes,\n\t\t\t\tSUM(v.rating) AS positive_votes,\n\t\t\t\tMAX(v.date) AS last_vote_date\n\t\t\tFROM items i\n\t\t\tJOIN votes v ON i.id = v.item_id\n\t\t\tWHERE i.id = {$this->item['id']}\n\t\t\tGROUP BY i.id", SQLITE_ASSOC); // Nobody voted on this item yet if (empty($results)) { // Initialize result array manually $this->item['results'] = array('total_votes' => 0, 'positive_votes' => 0, 'votes_balance' => 0, 'negative_votes' => 0, 'positive_percentage' => 0, 'negative_percentage' => 0, 'last_vote_date' => NULL); // We're done return TRUE; } // Load the results into the class $this->item['results'] = current($results); // Add our own extra result data, calculated by PHP instead of SQL $this->item['results']['negative_votes'] = $this->item['results']['total_votes'] - $this->item['results']['positive_votes']; $this->item['results']['votes_balance'] = $this->item['results']['positive_votes'] - $this->item['results']['negative_votes']; $this->item['results']['positive_percentage'] = $this->item['results']['positive_votes'] / $this->item['results']['total_votes'] * 100; $this->item['results']['negative_percentage'] = 100 - $this->item['results']['positive_percentage']; return TRUE; }