site-reviews / plugin / Database.php

Database.php in Site Reviews trunk, at plugin/Database.php

324 lines 10.1 KB

Raw Download Zip

1	<?php
2
3	namespace GeminiLabs\SiteReviews;
4
5	use GeminiLabs\SiteReviews\Database\Query;
6	use GeminiLabs\SiteReviews\Database\Search\SearchAssignedPosts;
7	use GeminiLabs\SiteReviews\Database\Search\SearchAssignedUsers;
8	use GeminiLabs\SiteReviews\Database\Search\SearchPosts;
9	use GeminiLabs\SiteReviews\Database\Search\SearchUsers;
10	use GeminiLabs\SiteReviews\Database\Tables;
11	use GeminiLabs\SiteReviews\Helpers\Arr;
12	use GeminiLabs\SiteReviews\Helpers\Cast;
13	use GeminiLabs\SiteReviews\Helpers\Str;
14	use GeminiLabs\SiteReviews\Modules\Sanitizer;
15
16	class Database
17	{
18	use Deprecated;
19
20	protected \wpdb $db;
21
22	public function __construct()
23	{
24	global $wpdb;
25	$this->db = $wpdb;
26	}
27
28	/**
29	* Use this before bulk insert (see: $this->finishTransaction()).
30	*/
31	public function beginTransaction(string $table): void
32	{
33	if (glsr(Tables::class)->isSqlite()) {
34	$this->dbQuery("BEGIN TRANSACTION;");
35	} elseif (glsr(Tables::class)->isInnodb($table)) {
36	$this->dbQuery("START TRANSACTION;");
37	} elseif (glsr(Tables::class)->isMyisam($table)) {
38	$this->dbQuery("SET autocommit = 0;");
39	}
40	}
41
42	public function dbGetCol(string $sql): array
43	{
44	return $this->logErrors($this->db->get_col($sql));
45	}
46
47	/**
48	* Query result in format specified by $output or null on failure.
49	*
50	* @return array\|object\|null
51	*/
52	public function dbGetResults(string $sql, string $output = 'OBJECT')
53	{
54	$output = Str::restrictTo(['ARRAY_A', 'ARRAY_N', 'OBJECT', 'OBJECT_K'], $output, \OBJECT);
55	return $this->logErrors($this->db->get_results($sql, $output));
56	}
57
58	/**
59	* Query result in format specified by $output or null on failure.
60	*
61	* @return array\|object\|void\|null
62	*/
63	public function dbGetRow(string $sql, string $output)
64	{
65	$output = Str::restrictTo(['ARRAY_A', 'ARRAY_N', 'OBJECT'], $output, \OBJECT);
66	return $this->logErrors($this->db->get_row($sql, $output));
67	}
68
69	/**
70	* Query result as string, or null on failure.
71	*/
72	public function dbGetVar(string $sql): ?string
73	{
74	return $this->logErrors($this->db->get_var($sql));
75	}
76
77	/**
78	* True for CREATE, ALTER, TRUNCATE and DROP queries.
79	* Number of rows affected/selected for all other queries.
80	* False on error.
81	*
82	* @return int\|bool
83	*/
84	public function dbQuery(string $sql)
85	{
86	return $this->logErrors($this->db->query($sql));
87	}
88
89	/**
90	* True for CREATE, ALTER, TRUNCATE and DROP queries.
91	* Number of rows affected/selected for all other queries.
92	* False on error.
93	*
94	* @return int\|bool
95	*/
96	public function dbSafeQuery(string $sql)
97	{
98	if (glsr(Tables::class)->isSqlite()) {
99	return $this->dbQuery($sql);
100	}
101	$this->db->query('SET foreign_key_checks = 0');
102	$result = $this->logErrors($this->db->query($sql));
103	$this->db->query('SET foreign_key_checks = 1');
104	return $result;
105	}
106
107	/**
108	* Number of rows deleted. False on error.
109	*
110	* @return int\|false
111	*/
112	public function delete(string $table, array $where)
113	{
114	$result = $this->db->delete(glsr(Tables::class)->table($table), $where);
115	glsr(Query::class)->sql($this->db->last_query); // for logging use only
116	return $this->logErrors($result);
117	}
118
119	/**
120	* Number of rows deleted. False on error.
121	*
122	* @param string\|string[] $keys
123	*
124	* @return int\|false
125	*/
126	public function deleteMeta($keys, string $table = 'postmeta')
127	{
128	$table = glsr(Tables::class)->table($table);
129	$metaKeys = glsr(Query::class)->escValuesForInsert(Arr::convertFromString($keys));
130	$sql = glsr(Query::class)->sql("
131	DELETE FROM {$table} WHERE meta_key IN {$metaKeys}
132	");
133	return $this->dbQuery($sql);
134	}
135
136	/**
137	* Use this after bulk insert (see: $this->beginTransaction()).
138	*/
139	public function finishTransaction(string $table): void
140	{
141	if (glsr(Tables::class)->isSqlite()) {
142	$this->dbQuery("COMMIT;");
143	} elseif (glsr(Tables::class)->isInnodb($table)) {
144	$this->dbQuery("COMMIT;");
145	} elseif (glsr(Tables::class)->isMyisam($table)) {
146	$this->dbQuery("SET autocommit = 1;");
147	}
148	}
149
150	/**
151	* Number of rows inserted. False on error.
152	*
153	* @return int\|false
154	*/
155	public function insert(string $table, array $data)
156	{
157	$this->db->insert_id = 0;
158	$table = glsr(Tables::class)->table($table);
159	$fields = glsr(Query::class)->escFieldsForInsert(array_keys($data));
160	$values = glsr(Query::class)->escValuesForInsert($data);
161	if (glsr(Tables::class)->isSqlite()) {
162	$sql = glsr(Query::class)->sql("INSERT OR IGNORE INTO {$table} {$fields} VALUES {$values}");
163	} else {
164	$sql = glsr(Query::class)->sql("INSERT IGNORE INTO {$table} {$fields} VALUES {$values}");
165	}
166	return $this->dbQuery($sql);
167	}
168
169	/**
170	* Number of rows inserted. False on error.
171	*
172	* @return int\|false
173	*/
174	public function insertBulk(string $table, array $values, array $fields)
175	{
176	$this->db->insert_id = 0;
177	$data = [];
178	foreach ($values as $value) {
179	$value = array_intersect_key($value, array_flip($fields)); // only keep field values
180	if (count($value) === count($fields)) {
181	$value = array_merge(array_flip($fields), $value); // make sure the order is correct
182	$data[] = glsr(Query::class)->escValuesForInsert($value);
183	}
184	}
185	$table = glsr(Tables::class)->table($table);
186	$fields = glsr(Query::class)->escFieldsForInsert($fields);
187	$values = implode(',', $data);
188	if (glsr(Tables::class)->isSqlite()) {
189	$sql = glsr(Query::class)->sql("INSERT OR IGNORE INTO {$table} {$fields} VALUES {$values}");
190	} else {
191	$sql = glsr(Query::class)->sql("INSERT IGNORE INTO {$table} {$fields} VALUES {$values}");
192	}
193	return $this->dbQuery($sql);
194	}
195
196	public function isMigrationNeeded(): bool
197	{
198	$postTypes = wp_count_posts(glsr()->post_type);
199	$postCount = Arr::get($postTypes, 'publish');
200	if (empty($postCount)) {
201	return false;
202	}
203	$sql = glsr(Query::class)->sql("SELECT COUNT(*) FROM table\|ratings WHERE is_approved = 1");
204	return empty($this->dbGetVar($sql));
205	}
206
207	/**
208	* @param mixed $result
209	*
210	* @return mixed
211	*/
212	public function logErrors($result = null)
213	{
214	if ($this->db->last_error) {
215	glsr_log()->error($this->db->last_error);
216	glsr_trace();
217	}
218	return $result;
219	}
220
221	public function posts(array $args = []): array
222	{
223	$args = wp_parse_args($args, [
224	'order' => 'ASC',
225	'orderby' => 'title',
226	// @see MainController::parseAssignedPostTypesInQuery
227	'post_type' => glsr()->prefix.'assigned_posts',
228	'posts_per_page' => 50,
229	'search_columns' => [
230	'post_title',
231	],
232	'suppress_filters' => true,
233	]);
234	$posts = get_posts($args);
235	$results = [];
236	foreach ($posts as $post) {
237	$results[$post->ID] = sanitize_text_field($post->post_title) ?: _x('(no title)', 'admin-text', 'site-reviews');
238	}
239	natcasesort($results);
240	return $results;
241	}
242
243	public function searchAssignedPosts(string $searchTerm): SearchAssignedPosts
244	{
245	return glsr(SearchAssignedPosts::class)->search($searchTerm);
246	}
247
248	public function searchAssignedUsers(string $searchTerm): SearchAssignedUsers
249	{
250	return glsr(SearchAssignedUsers::class)->search($searchTerm);
251	}
252
253	public function searchPosts(string $searchTerm): SearchPosts
254	{
255	return glsr(SearchPosts::class)->search($searchTerm);
256	}
257
258	public function searchUsers(string $searchTerm): SearchUsers
259	{
260	return glsr(SearchUsers::class)->search($searchTerm);
261	}
262
263	public function terms(array $args = []): array
264	{
265	$args = wp_parse_args($args, [
266	'count' => false,
267	'fields' => 'id=>name',
268	'hide_empty' => false,
269	'taxonomy' => glsr()->taxonomy,
270	]);
271	$terms = get_terms($args);
272	if (is_wp_error($terms)) {
273	glsr_log()->error($terms->get_error_message());
274	glsr_trace();
275	return [];
276	}
277	return $terms;
278	}
279
280	/**
281	* Number of rows updated. False on error.
282	*
283	* @return int\|false
284	*/
285	public function update(string $table, array $data, array $where)
286	{
287	$result = $this->db->update(glsr(Tables::class)->table($table), $data, $where);
288	glsr(Query::class)->sql($this->db->last_query); // for logging use only
289	return $this->logErrors($result);
290	}
291
292	public function users(array $args = []): array
293	{
294	$args = wp_parse_args($args, [
295	'fields' => ['ID', 'display_name', 'user_login', 'user_nicename'],
296	'number' => 50, // only get the first 50 users!
297	'orderby' => 'display_name',
298	'search' => '',
299	'search_columns' => ['display_name', 'user_nicename'],
300	'search_wild' => '',
301	]);
302	if (!empty($args['search_wild'])) {
303	$args['search'] = "{$args['search_wild']}";
304	}
305	$results = [];
306	$users = get_users($args);
307	foreach ($users as $user) {
308	$name = glsr(Sanitizer::class)->sanitizeUserName($user);
309	$results[$user->ID] = $name;
310	}
311	return $results;
312	}
313
314	public function version(): string
315	{
316	$dbVersion = Cast::toString(get_option(glsr()->prefix.'db_version'));
317	if (version_compare($dbVersion, Application::DB_VERSION, '>')) { // version should never be higher than plugin database version
318	update_option(glsr()->prefix.'db_version', '1.0'); // setting it to a low version will trigger the plugin migration
319	$dbVersion = '1.0';
320	}
321	return $dbVersion;
322	}
323	}
324