Clean up, fix and rename db tables.

This patch got a bit more involved than what was originally planned, but
since we're messing with the tables I decided to do it all right away.

- Moves the constraint definition to the CREATE TABLE statement for the
  concerts table. This replaces the existing KEY definition that it had.

- Make sure the venues table is created before the concerts table so
  that the above mentioned constraint definition works.

- Rename the tables. Use the wpdb-prefix and make the name a bit
  prettier. This caused some changes in the Concert and Venue classes,
  and for slightly silly reasons some test classes. The code actually
  turned out better (for the most part), but some refactoring can still
  be done. The column names remains unchanged for now.

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 ) {