summaryrefslogtreecommitdiffstats
path: root/includes
diff options
context:
space:
mode:
Diffstat (limited to 'includes')
-rw-r--r--includes/admin/register_db_tables.php68
-rw-r--r--includes/concert.php73
-rw-r--r--includes/venue.php18
3 files changed, 68 insertions, 91 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,