diff options
| -rw-r--r-- | includes/admin/register_db_tables.php | 68 | ||||
| -rw-r--r-- | includes/concert.php | 73 | ||||
| -rw-r--r-- | includes/venue.php | 18 | ||||
| -rw-r--r-- | tests/ConcertTest.php | 4 | ||||
| -rw-r--r-- | tests/ConcertsTableTest.php | 4 |
5 files changed, 72 insertions, 95 deletions
diff --git a/includes/admin/register_db_tables.php b/includes/admin/register_db_tables.php index 5ab42c7..ce0f25c 100644 --- a/includes/admin/register_db_tables.php +++ b/includes/admin/register_db_tables.php @@ -4,35 +4,30 @@ // // SPDX-License-Identifier: AGPL-3.0-or-later -if (!function_exists('giglogadmin_populate_countries')) { - function giglogadmin_populate_countries(): void - { - global $wpdb; - //not removing yet as I haven't yet checked where else it might be called - } -} - if ( !function_exists( "giglog_register_db_tables") ) { /** - * @return void + * Registers the tables used by the GiglogAdmin plugin */ - function giglog_register_db_tables() + function giglog_register_db_tables() : void { global $wpdb; - // Clean out obsolete tables if they exist. - $wpdb->query("DROP TABLE IF EXISTS " - . "wpg_bands, wpg_concertlogs, wpg_files, wpg_logchanges, wpg_pressstatus"); - /* not sure if DB version needed for now, leaving code here in case we decide to use it. To be removed if not - $db_version = get_option('giglogadmin_db_version'); - if ($db_version == 8) { - return; - } - */ + $tables = []; + $tables[] = + "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}giglogadmin_venues` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `wpgvenue_name` VARCHAR(500) NOT NULL, + `wpgvenue_city` VARCHAR(250) DEFAULT NULL, + `wpgvenue_address` VARCHAR(2000) DEFAULT NULL, + `wpgvenue_webpage` VARCHAR(200) DEFAULT NULL, + `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + `updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8;"; - $concerts_table = - "CREATE TABLE IF NOT EXISTS `wpg_concerts` ( + $tables[] = + "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}giglogadmin_concerts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `wpgconcert_name` VARCHAR(2000) NOT NULL, `venue` int(11) NOT NULL, @@ -45,44 +40,19 @@ if ( !function_exists( "giglog_register_db_tables") ) `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), - KEY `wpgconcert_venue` (`venue`) + CONSTRAINT `wpgconcert_venue` + FOREIGN KEY (`venue`) + REFERENCES `{$wpdb->prefix}giglogadmin_venues` (`id`) ON DELETE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8;"; - $venues_table = - "CREATE TABLE IF NOT EXISTS `wpg_venues` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `wpgvenue_name` VARCHAR(500) NOT NULL, - `wpgvenue_city` VARCHAR(250) DEFAULT NULL, - `wpgvenue_address` VARCHAR(2000) DEFAULT NULL, - `wpgvenue_webpage` VARCHAR(200) DEFAULT NULL, - `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - `updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - PRIMARY KEY (`id`) - ) ENGINE=InnoDB DEFAULT CHARSET=utf8;"; - - $tables = array( - $concerts_table, - $venues_table); - foreach($tables as $tabledef) { $result = $wpdb->query($tabledef); if ($result === false) { error_log("Registering table failed."); } } - - - $wpdb->query( - "ALTER TABLE `wpg_concerts` - ADD CONSTRAINT `wpgconcert_venue` - FOREIGN KEY (`venue`) - REFERENCES `wpg_venues` (`id`) ON DELETE NO ACTION;"); - - // update_option("giglogadmin_db_version", 8); } giglog_register_db_tables(); } - -?> diff --git a/includes/concert.php b/includes/concert.php index 102536b..8609694 100644 --- a/includes/concert.php +++ b/includes/concert.php @@ -37,22 +37,6 @@ if ( !class_exists('GiglogAdmin_Concert') ) { public const STATUS_ALL_APPROVED = 4; public const STATUS_REJECTED = 5; - // Table to translate from filter keys to db columns used by - // find_Concerts - private const KEY_TRANS_TABLE = [ - 'name' => 'wpgconcert_name', - 'date' => 'wpgconcert_date', - 'venue_id' => 'wpg_venues.id', - 'venue' => 'wpg_venues.wpgvenue_name', - 'city' => 'wpg_venues.wpgvenue_city', - 'currentuser' => 'wpgconcert_roles', - ]; - - private const BASE_QUERY = - 'SELECT wpg_concerts.*, wpg_venues.wpgvenue_name wpg_venues_wpgvenue_city ' - . 'FROM wpg_concerts ' - . 'LEFT JOIN wpg_venues ON wpg_concerts.venue = wpg_venues.id '; - /* * Constructs a new concert object from an array of attributes. @@ -99,19 +83,7 @@ if ( !class_exists('GiglogAdmin_Concert') ) { */ static function get( int $id ) : ?self { - global $wpdb; - - $query = self::BASE_QUERY - . 'WHERE ' . $wpdb->prepare('wpg_concerts.id = %d', $id); - - $res= $wpdb->get_row($query); - - if ( !$res) { - $wpdb->print_error( __METHOD__ ); - return null; - } - - return new GiglogAdmin_Concert($res); + return self::find_concerts(['id' => $id])[0]; } public static function create(string $name, int $venue_id, string $date, string $ticketlink, string $eventlink): ?self @@ -188,6 +160,21 @@ if ( !class_exists('GiglogAdmin_Concert') ) { return $need_update; } + // Table to translate from filter keys to db columns used by + // find_Concerts + private function translate_key($key) : string + { + return [ + 'id' => 'id', + 'name' => 'wpgconcert_name', + 'date' => 'wpgconcert_date', + 'venue_id' => $wpdb->prefix . 'giglogadmin_venues.id', + 'venue' => $wpdb->prefix . 'giglogadmin_venues.wpgvenue_name', + 'city' => $wpdb->prefix . 'giglogadmin_venues.wpgvenue_city', + 'currentuser' => 'wpgconcert_roles', + ][$key]; + } + /** * Return an array of concert objects optionally limited by a specified * filter. @@ -203,7 +190,22 @@ if ( !class_exists('GiglogAdmin_Concert') ) { { global $wpdb; - $query = self::BASE_QUERY; + $ct = "{$wpdb->prefix}giglogadmin_concerts"; + $vt = "{$wpdb->prefix}giglogadmin_venues"; + + $query = "SELECT {$ct}.*, {$vt}.wpgvenue_name, {$vt}.wpgvenue_city " + . "FROM {$ct} " + . "LEFT JOIN {$vt} ON {$ct}.venue = {$vt}.id "; + + $keymap = [ + 'id' => $wpdb->prefix . 'giglogadmin_concerts.id', + 'name' => 'wpgconcert_name', + 'date' => 'wpgconcert_date', + 'venue_id' => $wpdb->prefix . 'giglogadmin_venues.id', + 'venue' => $wpdb->prefix . 'giglogadmin_venues.wpgvenue_name', + 'city' => $wpdb->prefix . 'giglogadmin_venues.wpgvenue_city', + 'currentuser' => 'wpgconcert_roles', + ]; $where = []; foreach( $filter as $key => $value ) { @@ -212,15 +214,16 @@ if ( !class_exists('GiglogAdmin_Concert') ) { case 'date': case 'venue': case 'city': - array_push($where, $wpdb->prepare(self::KEY_TRANS_TABLE[$key] . '=%s', $value)); + array_push($where, $wpdb->prepare($keymap[$key] . '=%s', $value)); break; + case 'id': case 'venue_id': - array_push($where, $wpdb->prepare(self::KEY_TRANS_TABLE[$key] . '=%d', $value)); + array_push($where, $wpdb->prepare($keymap[$key] . '=%d', $value)); break; case 'currentuser': - array_push($where , $wpdb->prepare(self::KEY_TRANS_TABLE[$key] . ' like "%%%s%%"', $value)); + array_push($where , $wpdb->prepare($keymap[$key] . ' like "%%%s%%"', $value)); break; } } @@ -251,10 +254,10 @@ if ( !class_exists('GiglogAdmin_Concert') ) { ]; if ( $this->id !== NULL ) { - $res = $wpdb->update( 'wpg_concerts', $columns, [ 'id' => $this->id ] ); + $res = $wpdb->update( $wpdb->prefix . 'giglogadmin_concerts', $columns, [ 'id' => $this->id ] ); } else { - $res = $wpdb->insert('wpg_concerts', $columns); + $res = $wpdb->insert( $wpdb->prefix . 'giglogadmin_concerts', $columns); } if ( $res === false ) { diff --git a/includes/venue.php b/includes/venue.php index 47788d1..177ba2d 100644 --- a/includes/venue.php +++ b/includes/venue.php @@ -54,7 +54,7 @@ if ( !class_exists('GiglogAdmin_Venue') ) { { global $wpdb; - $query = $wpdb->prepare('SELECT * from wpg_venues WHERE id = %d', $id); + $query = $wpdb->prepare("SELECT * from {$wpdb->prefix}giglogadmin_venues WHERE id = %d", $id); $results = $wpdb->get_results($query); return $results ? new GiglogAdmin_Venue($results[0]) : NULL; @@ -74,7 +74,9 @@ if ( !class_exists('GiglogAdmin_Venue') ) { static function find_or_create(string $name, string $city = 'Oslo'): self { global $wpdb; - $venuesql = 'SELECT * FROM wpg_venues WHERE upper(wpgvenue_name)=upper("' . $name . '")'.' and wpgvenue_city="'.$city.'"'; + $venuesql = "SELECT * FROM {$wpdb->prefix}giglogadmin_venues " + . $wpdb->prepare("WHERE upper(wpgvenue_name)=upper(%s) and wpgvenue_city=%s", $name, $city); + $results = $wpdb->get_results($venuesql); if ($results) { @@ -88,7 +90,8 @@ if ( !class_exists('GiglogAdmin_Venue') ) { static function all_cities(): array { global $wpdb; - $results = $wpdb->get_results('select distinct wpgvenue_city from wpg_venues'); + $results = $wpdb->get_results( + "select distinct wpgvenue_city from {$wpdb->prefix}giglogadmin_venues"); return array_map(function ($r) { return $r->wpgvenue_city; }, $results); } @@ -102,7 +105,8 @@ if ( !class_exists('GiglogAdmin_Venue') ) { { global $wpdb; - $results = $wpdb->get_results("select * from wpg_venues ORDER BY wpgvenue_name"); + $results = $wpdb->get_results( + "select * from {$wpdb->prefix}giglogadmin_venues ORDER BY wpgvenue_name"); return array_map(function ($r) { return new GiglogAdmin_Venue($r); }, $results); } @@ -116,8 +120,8 @@ if ( !class_exists('GiglogAdmin_Venue') ) { static function venues_in_city(string $city): array { global $wpdb; - $q = $wpdb->prepare("select * from wpg_venues where wpgvenue_city=%s", $city); - $q .=" ORDER BY wpgvenue_name"; + $q = $wpdb->prepare("select * from {$wpdb->prefix}giglogadmin_venues where wpgvenue_city=%s", $city) + . " ORDER BY wpgvenue_name"; $results = $wpdb->get_results($q); return array_map(function ($r) { return new GiglogAdmin_Venue($r); }, $results); @@ -127,7 +131,7 @@ if ( !class_exists('GiglogAdmin_Venue') ) { { global $wpdb; - $wpdb->insert('wpg_venues', array( + $wpdb->insert($wpdb->prefix . 'giglogadmin_venues', array( 'id' => '', 'wpgvenue_name' => $this->name, 'wpgvenue_city' => $this->city, diff --git a/tests/ConcertTest.php b/tests/ConcertTest.php index 4f27f0f..dc2a712 100644 --- a/tests/ConcertTest.php +++ b/tests/ConcertTest.php @@ -69,8 +69,8 @@ final class ConcertTest extends WP_UnitTestCase global $wpdb; $tables = [ - "wpg_concerts", - "wpg_venues", + "{$wpdb->prefix}giglogadmin_concerts", + "{$wpdb->prefix}giglogadmin_venues", ]; foreach( $tables as $table ) { diff --git a/tests/ConcertsTableTest.php b/tests/ConcertsTableTest.php index 556698a..6f9d9d2 100644 --- a/tests/ConcertsTableTest.php +++ b/tests/ConcertsTableTest.php @@ -65,8 +65,8 @@ class ConcertsTableTest extends WP_UnitTestCase global $wpdb; $tables = [ - "wpg_concerts", - "wpg_venues", + "{$wpdb->prefix}giglogadmin_concerts", + "{$wpdb->prefix}giglogadmin_venues", ]; foreach( $tables as $table ) { |