DbHelper.php — Matomo Analytics – Powerful, Privacy-First Insights for WordPress 4.6.0

matomo / app / core / DbHelper.php

matomo / app / core Last commit date

API 4 years ago Access 5 years ago Application 4 years ago Archive 4 years ago ArchiveProcessor 4 years ago Archiver 5 years ago AssetManager 4 years ago Auth 5 years ago Category 5 years ago CliMulti 4 years ago Columns 5 years ago Concurrency 4 years ago Config 5 years ago Container 4 years ago CronArchive 4 years ago DataAccess 4 years ago DataFiles 5 years ago DataTable 4 years ago Db 4 years ago DeviceDetector 5 years ago Email 5 years ago Exception 4 years ago Http 5 years ago Intl 4 years ago Mail 5 years ago Measurable 5 years ago Menu 5 years ago Metrics 4 years ago Notification 4 years ago Period 4 years ago Plugin 4 years ago ProfessionalServices 5 years ago Report 5 years ago ReportRenderer 5 years ago Scheduler 4 years ago Segment 5 years ago Session 4 years ago Settings 5 years ago Tracker 4 years ago Translation 4 years ago UpdateCheck 5 years ago Updater 4 years ago Updates 4 years ago Validators 5 years ago View 4 years ago ViewDataTable 5 years ago Visualization 5 years ago Widget 5 years ago .htaccess 6 years ago Access.php 4 years ago Archive.php 4 years ago ArchiveProcessor.php 4 years ago AssetManager.php 5 years ago Auth.php 5 years ago AuthResult.php 5 years ago BaseFactory.php 5 years ago Cache.php 5 years ago CacheId.php 5 years ago CliMulti.php 4 years ago Common.php 4 years ago Config.php 4 years ago Console.php 5 years ago Context.php 5 years ago Cookie.php 4 years ago CronArchive.php 4 years ago DataArray.php 4 years ago DataTable.php 4 years ago Date.php 4 years ago Db.php 4 years ago DbHelper.php 4 years ago Development.php 5 years ago ErrorHandler.php 5 years ago EventDispatcher.php 5 years ago ExceptionHandler.php 4 years ago FileIntegrity.php 5 years ago Filechecks.php 5 years ago Filesystem.php 4 years ago FrontController.php 4 years ago Http.php 5 years ago IP.php 5 years ago Log.php 5 years ago LogDeleter.php 4 years ago Mail.php 4 years ago Metrics.php 5 years ago NoAccessException.php 5 years ago Nonce.php 4 years ago Notification.php 5 years ago NumberFormatter.php 5 years ago Option.php 5 years ago Period.php 5 years ago Piwik.php 4 years ago Plugin.php 4 years ago Profiler.php 5 years ago ProxyHeaders.php 5 years ago ProxyHttp.php 4 years ago QuickForm2.php 5 years ago RankingQuery.php 5 years ago ReportRenderer.php 5 years ago Segment.php 5 years ago Sequence.php 5 years ago Session.php 4 years ago SettingsPiwik.php 5 years ago SettingsServer.php 5 years ago Singleton.php 5 years ago Site.php 5 years ago SupportedBrowser.php 4 years ago TCPDF.php 5 years ago Theme.php 5 years ago Timer.php 5 years ago Tracker.php 5 years ago Twig.php 4 years ago Unzip.php 5 years ago UpdateCheck.php 5 years ago Updater.php 5 years ago UpdaterErrorException.php 5 years ago Updates.php 5 years ago Url.php 4 years ago UrlHelper.php 4 years ago Version.php 4 years ago View.php 4 years ago bootstrap.php 5 years ago dispatch.php 5 years ago testMinimumPhpVersion.php 5 years ago

DbHelper.php

329 lines

1	<?php
2	/**
3	* Matomo - free/libre analytics platform
4	*
5	* @link https://matomo.org
6	* @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
7	*
8	*/
9	namespace Piwik;
10
11	use Exception;
12	use Piwik\Db\Schema;
13	use Piwik\DataAccess\ArchiveTableCreator;
14
15	/**
16	* Contains database related helper functions.
17	*/
18	class DbHelper
19	{
20	/**
21	* Get list of tables installed
22	*
23	* @param bool $forceReload Invalidate cache
24	* @return array Tables installed
25	*/
26	public static function getTablesInstalled($forceReload = true)
27	{
28	return Schema::getInstance()->getTablesInstalled($forceReload);
29	}
30
31	/**
32	* Returns `true` if a table in the database, `false` if otherwise.
33	*
34	* @param string $tableName The name of the table to check for. Must be prefixed.
35	* Avoid using user input, as the variable will be used in a query unescaped.
36	* @return bool
37	* @throws \Exception
38	*/
39	public static function tableExists($tableName)
40	{
41	$tableName = str_replace(['%', '_', "'"], ['\%', '\_', '_'], $tableName);
42	return Db::get()->query(sprintf("SHOW TABLES LIKE '%s'", $tableName))->rowCount() > 0;
43	}
44
45	/**
46	* Get list of installed columns in a table
47	*
48	* @param string $tableName The name of a table.
49	*
50	* @return array Installed columns indexed by the column name.
51	*/
52	public static function getTableColumns($tableName)
53	{
54	return Schema::getInstance()->getTableColumns($tableName);
55	}
56
57	/**
58	* Creates a new table in the database.
59	*
60	* Example:
61	* ```
62	* $tableDefinition = "`age` INT(11) NOT NULL AUTO_INCREMENT,
63	* `name` VARCHAR(255) NOT NULL";
64	*
65	* DbHelper::createTable('tablename', $tableDefinition);
66	* ``
67	*
68	* @param string $nameWithoutPrefix The name of the table without any piwik prefix.
69	* @param string $createDefinition The table create definition
70	*
71	* @api
72	*/
73	public static function createTable($nameWithoutPrefix, $createDefinition)
74	{
75	Schema::getInstance()->createTable($nameWithoutPrefix, $createDefinition);
76	}
77
78	/**
79	* Returns true if Piwik is installed
80	*
81	* @since 0.6.3
82	*
83	* @return bool True if installed; false otherwise
84	*/
85	public static function isInstalled()
86	{
87	try {
88	return Schema::getInstance()->hasTables();
89	} catch (Exception $e) {
90	return false;
91	}
92	}
93
94	/**
95	* Truncate all tables
96	*/
97	public static function truncateAllTables()
98	{
99	Schema::getInstance()->truncateAllTables();
100	}
101
102	/**
103	* Creates an entry in the User table for the "anonymous" user.
104	*/
105	public static function createAnonymousUser()
106	{
107	Schema::getInstance()->createAnonymousUser();
108	}
109
110	/**
111	* Records the Matomo version a user used when installing this Matomo for the first time
112	*/
113	public static function recordInstallVersion()
114	{
115	Schema::getInstance()->recordInstallVersion();
116	}
117
118	/**
119	* Returns which Matomo version was used to install this Matomo for the first time.
120	*/
121	public static function getInstallVersion(): string
122	{
123	return Schema::getInstance()->getInstallVersion() ?? '0';
124	// need string as usage is usually
125	// version_compare(DbHelper::getInstallVersion(),'4.0.0-b1', '<') or similar
126	// and PHP 8.1 throws a deprecation warning otherwise
127	// @see https://github.com/matomo-org/matomo/pull/17989#issuecomment-921298360
128	}
129
130	public static function wasMatomoInstalledBeforeVersion($version)
131	{
132	$installVersion = self::getInstallVersion();
133	if (empty($installVersion)) {
134	return true; // we assume yes it was installed
135	}
136	return true === version_compare($version, $installVersion, '>');
137	}
138
139	/**
140	* Create all tables
141	*/
142	public static function createTables()
143	{
144	Schema::getInstance()->createTables();
145	}
146
147	/**
148	* Drop database, used in tests
149	*/
150	public static function dropDatabase($dbName = null)
151	{
152	if (defined('PIWIK_TEST_MODE') && PIWIK_TEST_MODE) {
153	Schema::getInstance()->dropDatabase($dbName);
154	}
155	}
156
157
158	/**
159	* Checks the database server version against the required minimum
160	* version.
161	*
162	* @see config/global.ini.php
163	* @since 0.4.4
164	* @throws Exception if server version is less than the required version
165	*/
166	public static function checkDatabaseVersion()
167	{
168	Db::get()->checkServerVersion();
169	}
170
171	/**
172	* Disconnect from database
173	*/
174	public static function disconnectDatabase()
175	{
176	Db::get()->closeConnection();
177	}
178
179	/**
180	* Create database
181	*
182	* @param string\|null $dbName
183	*/
184	public static function createDatabase($dbName = null)
185	{
186	Schema::getInstance()->createDatabase($dbName);
187	}
188
189	/**
190	* Returns if the given table has an index with the given name
191	*
192	* @param string $table
193	* @param string $indexName
194	*
195	* @return bool
196	* @throws Exception
197	*/
198	public static function tableHasIndex($table, $indexName)
199	{
200	$result = Db::get()->fetchOne('SHOW INDEX FROM '.$table.' WHERE Key_name = ?', [$indexName]);
201	return !empty($result);
202	}
203
204	/**
205	* Returns the default database charset to use
206	*
207	* Returns utf8mb4 if supported, with fallback to utf8
208	*
209	* @return string
210	* @throws Tracker\Db\DbException
211	*/
212	public static function getDefaultCharset()
213	{
214	$result = Db::get()->fetchRow("SHOW CHARACTER SET LIKE 'utf8mb4'");
215
216	if (empty($result)) {
217	return 'utf8'; // charset not available
218	}
219
220	$result = Db::get()->fetchRow("SHOW VARIABLES LIKE 'character_set_database'");
221
222	if (!empty($result) && $result['Value'] === 'utf8mb4') {
223	return 'utf8mb4'; // database has utf8mb4 charset, so assume it can be used
224	}
225
226	$result = Db::get()->fetchRow("SHOW VARIABLES LIKE 'innodb_file_per_table'");
227
228	if (empty($result) \|\| $result['Value'] !== 'ON') {
229	return 'utf8'; // innodb_file_per_table is required for utf8mb4
230	}
231
232	return 'utf8mb4';
233	}
234
235	/**
236	* Returns sql queries to convert all installed tables to utf8mb4
237	*
238	* @return array
239	*/
240	public static function getUtf8mb4ConversionQueries()
241	{
242	$allTables = DbHelper::getTablesInstalled();
243
244	$queries = [];
245
246	foreach ($allTables as $table) {
247	$queries[] = "ALTER TABLE `$table` CONVERT TO CHARACTER SET utf8mb4;";
248	}
249
250	return $queries;
251	}
252
253	/**
254	* Get the SQL to create Piwik tables
255	*
256	* @return array array of strings containing SQL
257	*/
258	public static function getTablesCreateSql()
259	{
260	return Schema::getInstance()->getTablesCreateSql();
261	}
262
263	/**
264	* Get the SQL to create a specific Piwik table
265	*
266	* @param string $tableName Unprefixed table name.
267	* @return string SQL
268	*/
269	public static function getTableCreateSql($tableName)
270	{
271	return Schema::getInstance()->getTableCreateSql($tableName);
272	}
273
274	/**
275	* Deletes archive tables. For use in tests.
276	*/
277	public static function deleteArchiveTables()
278	{
279	foreach (ArchiveTableCreator::getTablesArchivesInstalled() as $table) {
280	Log::debug("Dropping table $table");
281
282	Db::query("DROP TABLE IF EXISTS `$table`");
283	}
284
285	ArchiveTableCreator::refreshTableList($forceReload = true);
286	}
287
288	/**
289	* Adds a MAX_EXECUTION_TIME hint into a SELECT query if $limit is bigger than 1
290	*
291	* @param string $sql query to add hint to
292	* @param int $limit time limit in seconds
293	* @return string
294	*/
295	public static function addMaxExecutionTimeHintToQuery($sql, $limit)
296	{
297	if ($limit <= 0) {
298	return $sql;
299	}
300
301	$sql = trim($sql);
302	$pos = stripos($sql, 'SELECT');
303	if ($pos !== false) {
304
305	$timeInMs = $limit * 1000;
306	$timeInMs = (int) $timeInMs;
307	$maxExecutionTimeHint = ' /+ MAX_EXECUTION_TIME('.$timeInMs.') / ';
308
309	$sql = substr_replace($sql, 'SELECT ' . $maxExecutionTimeHint, $pos, strlen('SELECT'));
310	}
311
312	return $sql;
313	}
314
315	/**
316	* Returns true if the string is a valid database name for MySQL. MySQL allows + in the database names.
317	* Database names that start with a-Z or 0-9 and contain a-Z, 0-9, underscore(_), dash(-), plus(+), and dot(.) will be accepted.
318	* File names beginning with anything but a-Z or 0-9 will be rejected (including .htaccess for example).
319	* File names containing anything other than above mentioned will also be rejected (file names with spaces won't be accepted).
320	*
321	* @param string $dbname
322	* @return bool
323	*/
324	public static function isValidDbname($dbname)
325	{
326	return (0 !== preg_match('/(^[a-zA-Z0-9]+([a-zA-Z0-9\_\.\-\+]*))$/D', $dbname));
327	}
328
329	}