Passed
Push — master ( 2da942...d633d7 )
by Paul
15:03 queued 05:40
created

Database::dbGetResults()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 4
Code Lines 2

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 3
CRAP Score 1

Importance

Changes 0
Metric Value
cc 1
eloc 2
nc 1
nop 2
dl 0
loc 4
ccs 3
cts 3
cp 1
crap 1
rs 10
c 0
b 0
f 0
1
<?php
2
3
namespace GeminiLabs\SiteReviews;
4
5
use GeminiLabs\SiteReviews\Database\Query;
6
use GeminiLabs\SiteReviews\Database\SqlSchema;
7
use GeminiLabs\SiteReviews\Helpers\Arr;
8
use GeminiLabs\SiteReviews\Helpers\Cast;
9
use GeminiLabs\SiteReviews\Helpers\Str;
10
use WP_Query;
11
use WP_User_Query;
12
13
/**
14
 * @property array $mappedDeprecatedMethods
15
 */
16
class Database
17
{
18
    use Deprecated;
19
20
    protected $db;
21
22 22
    public function __construct()
23
    {
24 22
        global $wpdb;
25 22
        $this->db = $wpdb;
26 22
        $this->mappedDeprecatedMethods = [
27
            'get' => 'meta',
28
            'getTerms' => 'terms',
29
            'set' => 'metaSet',
30
        ];
31 22
    }
32
33
    /**
34
     * Use this before bulk insert (see: $this->finishTransaction()).
35
     * @param string $table
36
     * @return void
37
     */
38 7
    public function beginTransaction($table)
39
    {
40 7
        $sql = glsr(SqlSchema::class)->isInnodb($table)
41 7
            ? 'START TRANSACTION;'
42 7
            : 'SET autocommit = 0;';
43 7
        $this->dbQuery($sql);
44 7
    }
45
46
    /**
47
     * @param string $sql
48
     * @return array
49
     */
50 1
    public function dbGetCol($sql)
51
    {
52 1
        return $this->logErrors($this->db->get_col($sql));
53
    }
54
55
    /**
56
     * @param string $sql
57
     * @param string $output
58
     * @return array|object|null
59
     */
60 14
    public function dbGetResults($sql, $output)
61
    {
62 14
        $output = Str::restrictTo(['ARRAY_A', 'ARRAY_N', 'OBJECT', 'OBJECT_K'], $output, OBJECT);
63 14
        return $this->logErrors($this->db->get_results($sql, $output));
64
    }
65
66
    /**
67
     * @param string $sql
68
     * @param string $output
69
     * @return array|object|void|null
70
     */
71 14
    public function dbGetRow($sql, $output)
72
    {
73 14
        $output = Str::restrictTo(['ARRAY_A', 'ARRAY_N', 'OBJECT'], $output, OBJECT);
74 14
        return $this->logErrors($this->db->get_row($sql, $output));
75
    }
76
77
    /**
78
     * @param string $sql
79
     * @return string|null
80
     */
81 1
    public function dbGetVar($sql)
82
    {
83 1
        return $this->logErrors($this->db->get_var($sql));
84
    }
85
86
    /**
87
     * @param string $sql
88
     * @return int|bool
89
     */
90 22
    public function dbQuery($sql)
91
    {
92 22
        return $this->logErrors($this->db->query($sql));
93
    }
94
95
    /**
96
     * @param string $table
97
     * @return int|false
98
     */
99 3
    public function delete($table, array $where)
100
    {
101 3
        $result = $this->db->delete(glsr(Query::class)->table($table), $where);
102 3
        glsr(Query::class)->sql($this->db->last_query); // for logging use only
103 3
        return $this->logErrors($result);
104
    }
105
106
    /**
107
     * @return int|bool
108
     */
109 14
    public function deleteInvalidPostAssignments()
110
    {
111 14
        return $this->dbQuery(sprintf(
112 14
            glsr(Query::class)->sql("
113
                DELETE t
114
                FROM %s AS t
115
                LEFT JOIN %s AS r ON t.rating_id = r.ID
116 14
                LEFT JOIN {$this->db->posts} AS f ON t.post_id = f.ID
117
                WHERE (r.ID IS NULL OR f.ID IS NULL)
118
            "),
119 14
            glsr(Query::class)->table('assigned_posts'),
120 14
            glsr(Query::class)->table('ratings')
121
        ));
122
    }
123
124
    /**
125
     * @return int|bool
126
     */
127 14
    public function deleteInvalidTermAssignments()
128
    {
129 14
        return $this->dbQuery(sprintf(
130 14
            glsr(Query::class)->sql("
131
                DELETE t
132
                FROM %s AS t
133
                LEFT JOIN %s AS r ON t.rating_id = r.ID
134 14
                LEFT JOIN {$this->db->term_taxonomy} AS f ON t.term_id = f.term_id
135
                WHERE (r.ID IS NULL OR f.term_id IS NULL) OR f.taxonomy != '%s'
136
            "),
137 14
            glsr(Query::class)->table('assigned_terms'),
138 14
            glsr(Query::class)->table('ratings'),
139 14
            glsr()->taxonomy
140
        ));
141
    }
142
143
    /**
144
     * @return int|bool
145
     */
146 14
    public function deleteInvalidUserAssignments()
147
    {
148 14
        return $this->dbQuery(sprintf(
149 14
            glsr(Query::class)->sql("
150
                DELETE t
151
                FROM %s AS t
152
                LEFT JOIN %s AS r ON t.rating_id = r.ID
153 14
                LEFT JOIN {$this->db->users} AS f ON t.user_id = f.ID
154
                WHERE (r.ID IS NULL OR f.ID IS NULL)
155
            "),
156 14
            glsr(Query::class)->table('assigned_users'),
157 14
            glsr(Query::class)->table('ratings')
158
        ));
159
    }
160
161
    /**
162
     * @param string|string[] $keys
163
     * @param string $table
164
     * @return int|bool
165
     */
166 7
    public function deleteMeta($keys, $table = 'postmeta')
167
    {
168 7
        $table = glsr(Query::class)->table($table);
169 7
        $metaKeys = glsr(Query::class)->escValuesForInsert(Arr::convertFromString($keys));
170 7
        $sql = glsr(Query::class)->sql("
171 7
            DELETE FROM {$table} WHERE meta_key IN {$metaKeys}
172
        ");
173 7
        return $this->dbQuery($sql);
174
    }
175
176
    /**
177
     * Search SQL filter for matching against post title only.
178
     * @see http://wordpress.stackexchange.com/a/11826/1685
179
     * @param string $search
180
     * @return string
181
     * @filter posts_search
182
     */
183
    public function filterSearchByTitle($search, WP_Query $query)
184
    {
185
        if (empty($search) || empty($query->get('search_terms'))) {
186
            return $search;
187
        }
188
        $n = empty($query->get('exact'))
189
            ? '%'
190
            : '';
191
        $search = [];
192
        foreach ((array) $query->get('search_terms') as $term) {
193
            $search[] = $this->db->prepare("{$this->db->posts}.post_title LIKE %s", $n.$this->db->esc_like($term).$n);
194
        }
195
        if (!is_user_logged_in()) {
196
            $search[] = "{$this->db->posts}.post_password = ''";
197
        }
198
        return ' AND '.implode(' AND ', $search);
199
    }
200
201
    /**
202
     * Use this after bulk insert (see: $this->beginTransaction()).
203
     * @param string $table
204
     * @return void
205
     */
206 7
    public function finishTransaction($table)
207
    {
208 7
        $sql = glsr(SqlSchema::class)->isInnodb($table)
209 7
            ? 'COMMIT;'
210 7
            : 'SET autocommit = 1;';
211 7
        $this->dbQuery($sql);
212 7
    }
213
214
    /**
215
     * @param string $table
216
     * @return int|bool
217
     */
218 14
    public function insert($table, array $data)
219
    {
220 14
        $this->db->insert_id = 0;
221 14
        $table = glsr(Query::class)->table($table);
222 14
        $fields = glsr(Query::class)->escFieldsForInsert(array_keys($data));
223 14
        $values = glsr(Query::class)->escValuesForInsert($data);
224 14
        $sql = glsr(Query::class)->sql("INSERT IGNORE INTO {$table} {$fields} VALUES {$values}");
225 14
        $result = $this->dbQuery($sql);
226 14
        return empty($result) ? false : $result;
227
    }
228
229
    /**
230
     * @param string $table
231
     * @return int|false
232
     */
233
    public function insertBulk($table, array $values, array $fields)
234
    {
235
        $this->db->insert_id = 0;
236
        $data = [];
237
        foreach ($values as $value) {
238
            $value = array_intersect_key($value, array_flip($fields)); // only keep field values
239
            if (count($value) === count($fields)) {
240
                $value = array_merge(array_flip($fields), $value); // make sure the order is correct
241
                $data[] = glsr(Query::class)->escValuesForInsert($value);
242
            }
243
        }
244
        $table = glsr(Query::class)->table($table);
245
        $fields = glsr(Query::class)->escFieldsForInsert($fields);
246
        $values = implode(',', $data);
247
        $sql = glsr(Query::class)->sql("INSERT IGNORE INTO {$table} {$fields} VALUES {$values}");
248
        return $this->dbQuery($sql);
249
    }
250
251
    /**
252
     * @return bool
253
     */
254
    public function isMigrationNeeded()
255
    {
256
        $table = glsr(Query::class)->table('ratings');
257
        $postCount = wp_count_posts(glsr()->post_type)->publish;
258
        if (empty($postCount)) {
259
            return false;
260
        }
261
        $sql = glsr(Query::class)->sql("SELECT COUNT(*) FROM {$table} WHERE is_approved = 1");
262
        return empty($this->dbGetVar($sql));
263
    }
264
265
    /**
266
     * @param mixed $result
267
     * @return mixed
268
     */
269 22
    public function logErrors($result = null)
270
    {
271 22
        if ($this->db->last_error) {
272
            glsr_log()->error($this->db->last_error);
273
        }
274 22
        return $result;
275
    }
276
277
    /**
278
     * @param int $postId
279
     * @param string $key
280
     * @param bool $single
281
     * @return mixed
282
     */
283
    public function meta($postId, $key, $single = true)
284
    {
285
        $key = Str::prefix($key, '_');
286
        $postId = Cast::toInt($postId);
287
        return get_post_meta($postId, $key, $single);
288
    }
289
290
    /**
291
     * @param int $postId
292
     * @param string $key
293
     * @param mixed $value
294
     * @return int|bool
295
     */
296 1
    public function metaSet($postId, $key, $value)
297
    {
298 1
        $key = Str::prefix($key, '_');
299 1
        $postId = Cast::toInt($postId);
300 1
        return update_metadata('post', $postId, $key, $value); // update_metadata works with revisions
301
    }
302
303
    /**
304
     * @param string $searchTerm
305
     * @return void|string
306
     */
307
    public function searchPosts($searchTerm)
308
    {
309
        $args = [
310
            'post_status' => 'publish',
311
            'post_type' => 'any',
312
        ];
313
        if (is_numeric($searchTerm)) {
314
            $args['post__in'] = [$searchTerm];
315
        } else {
316
            $args['orderby'] = 'relevance';
317
            $args['posts_per_page'] = 10;
318
            $args['s'] = $searchTerm;
319
        }
320
        add_filter('posts_search', [$this, 'filterSearchByTitle'], 500, 2);
321
        $search = new WP_Query($args);
322
        remove_filter('posts_search', [$this, 'filterSearchByTitle'], 500);
323
        if ($search->have_posts()) {
324
            $results = '';
325
            while ($search->have_posts()) {
326
                $search->the_post();
327
                $results .= glsr()->build('partials/editor/search-result', [
328
                    'ID' => get_the_ID(),
329
                    'permalink' => esc_url((string) get_permalink()),
330
                    'title' => esc_attr(get_the_title()),
331
                ]);
332
            }
333
            wp_reset_postdata();
334
            return $results;
335
        }
336
    }
337
338
    /**
339
     * @param string $searchTerm
340
     * @return void|string
341
     */
342
    public function searchUsers($searchTerm)
343
    {
344
        $args = [
345
            'fields' => ['ID', 'user_login', 'display_name'],
346
            'number' => 10,
347
            'orderby' => 'display_name',
348
        ];
349
        if (is_numeric($searchTerm)) {
350
            $args['include'] = [$searchTerm];
351
        } else {
352
            $args['search'] = '*'.$searchTerm.'*';
353
            $args['search_columns'] = ['user_login', 'user_nicename', 'display_name'];
354
        }
355
        $users = (new WP_User_Query($args))->get_results();
356
        if (!empty($users)) {
357
            return array_reduce($users, function ($carry, $user) {
358
                return $carry.glsr()->build('partials/editor/search-result', [
359
                    'ID' => $user->ID,
360
                    'permalink' => esc_url(get_author_posts_url($user->ID)),
361
                    'title' => esc_attr($user->display_name.' ('.$user->user_login.')'),
362
                ]);
363
            });
364
        }
365
    }
366
367
    /**
368
     * @return array
369
     */
370 7
    public function terms(array $args = [])
371
    {
372 7
        $args = wp_parse_args($args, [
373 7
            'count' => false,
374 7
            'fields' => 'id=>name',
375
            'hide_empty' => false,
376 7
            'taxonomy' => glsr()->taxonomy,
377
        ]);
378 7
        $terms = get_terms($args);
379 7
        if (is_wp_error($terms)) {
380
            glsr_log()->error($terms->get_error_message());
381
            return [];
382
        }
383 7
        return $terms;
384
    }
385
386
    /**
387
     * @param string $table
388
     * @return int|bool
389
     */
390
    public function update($table, array $data, array $where)
391
    {
392
        $result = $this->db->update(glsr(Query::class)->table($table), $data, $where);
393
        glsr(Query::class)->sql($this->db->last_query); // for logging use only
394
        return $this->logErrors($result);
395
    }
396
397
    /**
398
     * @param string $compareToVersion
399
     * @return bool|string
400
     */
401 21
    public function version($compareToVersion = null)
402
    {
403 21
        $dbVersion = Cast::toString(get_option(glsr()->prefix.'db_version'));
404 21
        return isset($compareToVersion)
405 21
            ? version_compare($dbVersion, Cast::toString($compareToVersion), '>=')
406 21
            : $dbVersion;
407
    }
408
}
409