dolibarr 23.0.3
database-tables.php
Go to the documentation of this file.
1<?php
2/* Copyright (C) 2003 Rodolphe Quiedeville <rodolphe@quiedeville.org>
3 * Copyright (C) 2004-2021 Laurent Destailleur <eldy@users.sourceforge.net>
4 * Copyright (C) 2004 Sebastien Di Cintio <sdicintio@ressource-toi.org>
5 * Copyright (C) 2004 Benoit Mortier <benoit.mortier@opensides.be>
6 * Copyright (C) 2005-2012 Regis Houssin <regis.houssin@inodbox.com>
7 * Copyright (C) 2024 MDW <mdeweerd@users.noreply.github.com>
8 * Copyright (C) 2024 Frédéric France <frederic.france@free.fr>
9 *
10 * This program is free software; you can redistribute it and/or modify
11 * it under the terms of the GNU General Public License as published by
12 * the Free Software Foundation; either version 3 of the License, or
13 * (at your option) any later version.
14 *
15 * This program is distributed in the hope that it will be useful,
16 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 * GNU General Public License for more details.
19 *
20 * You should have received a copy of the GNU General Public License
21 * along with this program. If not, see <https://www.gnu.org/licenses/>.
22 */
23
29if (! defined('CSRFCHECK_WITH_TOKEN')) {
30 define('CSRFCHECK_WITH_TOKEN', '1'); // Force use of CSRF protection with tokens even for GET
31}
32
33// Load Dolibarr environment
34require '../../main.inc.php';
42require_once DOL_DOCUMENT_ROOT.'/core/lib/admin.lib.php';
43require_once DOL_DOCUMENT_ROOT.'/core/lib/files.lib.php';
44
45$langs->load("admin");
46
47if (!$user->admin) {
48 accessforbidden();
49}
50
51$table = GETPOST('table', 'aZ09');
52$action = GETPOST('action', 'aZ09');
53
54
55/*
56 * Actions
57 */
58
59$logsql = '';
60$resultsql = true;
61
62if ($action == 'convert') { // Convert engine into innodb
63 $sql = "ALTER TABLE ".$db->sanitize($table)." ENGINE=INNODB";
64 $logsql .= $sql.'<br>';
65 $resultsql = $db->query($sql);
66}
67if ($action == 'convertutf8') {
68 $collation = 'utf8_unicode_ci';
69 $defaultcollation = $db->getDefaultCollationDatabase();
70 if (preg_match('/general/', $defaultcollation)) {
71 $collation = 'utf8_general_ci';
72 }
73 $sql = "ALTER TABLE ".$db->sanitize($table)." CHARACTER SET utf8 COLLATE ".$db->sanitize($collation); // Set the default value on table
74 $logsql .= $sql.'<br>';
75 $resql1 = $db->query($sql);
76 if (!$resql1) {
77 setEventMessages($db->lasterror(), null, 'warnings');
78 $resultsql = $resql1;
79 } else {
80 $sql = "ALTER TABLE ".$db->sanitize($table)." CONVERT TO CHARACTER SET utf8 COLLATE ".$db->sanitize($collation); // Switch fields (may fails due to foreign key)
81 $logsql .= $sql.'<br>';
82 $resql2 = $db->query($sql);
83 if (!$resql2) {
84 setEventMessages($db->lasterror(), null, 'warnings');
85 $resultsql = $resql2;
86 }
87 }
88}
89if ($action == 'convertutf8mb4') {
90 $collation = 'utf8mb4_unicode_ci';
91 $defaultcollation = $db->getDefaultCollationDatabase();
92 if (preg_match('/general/', $defaultcollation)) {
93 $collation = 'utf8mb4_general_ci';
94 }
95 $sql = "ALTER TABLE ".$db->sanitize($table)." CHARACTER SET utf8mb4 COLLATE ".$db->sanitize($collation); // Set the default value on table
96 $logsql .= $sql.'<br>';
97 $resql1 = $db->query($sql);
98 if (!$resql1) {
99 setEventMessages($db->lasterror(), null, 'warnings');
100 $resultsql = $resql1;
101 } else {
102 $sql = "ALTER TABLE ".$db->sanitize($table)." CONVERT TO CHARACTER SET utf8mb4 COLLATE ".$db->sanitize($collation); // Switch fields (may fails due to foreign key)
103 $logsql .= $sql.'<br>';
104 $resql2 = $db->query($sql);
105 if (!$resql2) {
106 setEventMessages($db->lasterror(), null, 'warnings');
107 $resultsql = $resql2;
108 }
109 }
110}
111if ($action == 'convertdynamic') {
112 $sql = "ALTER TABLE ".$db->sanitize($table)." ROW_FORMAT=DYNAMIC;";
113 $logsql .= $sql.'<br>';
114 $resultsql = $db->query($sql);
115}
116
117
118/*
119 * View
120 */
121
122llxHeader('', '', '', '', 0, 0, '', '', '', 'mod-admin page-database_tables');
123
124$linkback = '<a href="'.DOL_URL_ROOT.'/admin/system/database.php?restore_lastsearch_values=1">'.img_picto($langs->trans("GoBack"), 'back', 'class="pictofixedwidth"').'<span class="hideonsmartphone">'.$langs->trans("GoBack").'</span></a>';
125
126print load_fiche_titre($langs->trans("Tables")." ".ucfirst($conf->db->type), $linkback, 'title_setup');
127
128if ($logsql) {
129 print info_admin($logsql.' '.(empty($resultsql) ? ' => KO '.$db->lasterror() : ' => OK'));
130}
131
132// Define request to get table description
133$base = 0;
134if (preg_match('/mysql/i', $conf->db->type)) {
135 $sql = "SHOW TABLE STATUS";
136 $base = 1;
137} elseif ($conf->db->type == 'pgsql') {
138 $sql = "SELECT conname, contype FROM pg_constraint;";
139 $base = 2;
140} elseif ($conf->db->type == 'mssql') {
141 //$sqls[0] = "";
142 //$base=3;
143} elseif ($conf->db->type == 'sqlite' || $conf->db->type == 'sqlite3') {
144 //$sql = "SELECT name, type FROM sqlite_master";
145 $base = 4;
146}
147
148
149if (!$base) {
150 print $langs->trans("FeatureNotAvailableWithThisDatabaseDriver");
151} else {
152 $i = 0;
153 if ($base == 1) {
154 print '<div class="div-table-responsive-no-min">';
155 print '<table class="noborder">';
156 print '<tr class="liste_titre">';
157 print '<td>#</td>';
158 print '<td>'.$langs->trans("TableName").'</td>';
159 print '<td colspan="2">'.$langs->trans("Type").'</td>';
160 print '<td>'.$langs->trans("Format").'</td>';
161 print '<td class="right">'.$langs->trans("NbOfRecord").'</td>';
162 print '<td class="right">Avg_row_length</td>';
163 print '<td class="right">Data_length</td>';
164 print '<td class="right">Max_Data_length</td>';
165 print '<td class="right">Index_length</td>';
166 print '<td class="right">Increment</td>';
167 print '<td class="right">Last check</td>';
168 print '<td class="right">Collation</td>';
169 print "</tr>\n";
170
171 $arrayoffilesrich = dol_dir_list(DOL_DOCUMENT_ROOT.'/install/mysql/tables/', 'files', 0, '\.sql$');
172 $arrayoffiles = array();
173 $arrayoftablesautocreated = array();
174 foreach ($arrayoffilesrich as $value) {
175 //print $shortsqlfilename.' ';
176 $shortsqlfilename = preg_replace('/\-[a-z]+\./', '.', $value['name']);
177 $arrayoffiles[$value['name']] = $shortsqlfilename;
178 if ($value['name'] == $shortsqlfilename && ! preg_match('/\.key\.sql$/', $value['name'])) {
179 // This is a sql file automatically created
180 $arrayoftablesautocreated[$value['name']] = $shortsqlfilename;
181 }
182 }
183
184 // Now loop on tables really found into database
185 $sql = "SHOW TABLE STATUS";
186
187 $resql = $db->query($sql);
188 if ($resql) {
189 $num = $db->num_rows($resql);
190 $i = 0;
191 while ($i < $num) {
192 $obj = $db->fetch_object($resql);
193
194 print '<tr class="oddeven">';
195
196 print '<td>'.($i + 1).'</td>';
197 print '<td class="tdoverflowmax300" title="'.dol_escape_htmltag($obj->Name).'"><a href="dbtable.php?table='.urlencode($obj->Name).'">'.$obj->Name.'</a>';
198 $tablename = preg_replace('/^'.MAIN_DB_PREFIX.'/', 'llx_', $obj->Name);
199
200 if (in_array($tablename.'.sql', $arrayoffiles)) {
201 if (in_array($tablename.'.sql', $arrayoftablesautocreated)) {
202 $img = "info";
203 } else {
204 $img = "info_black";
205 print img_picto($langs->trans("NotAvailableByDefaultEnabledOnModuleActivation"), $img, 'class="small opacitymedium"');
206 }
207 } else {
208 $img = "info_black";
209 print img_picto($langs->trans("ExternalModule"), $img, 'class="small"');
210 }
211 print '</td>';
212 print '<td>'.$obj->Engine.'</td>';
213 if (isset($obj->Engine) && $obj->Engine == "MyISAM") {
214 print '<td><a class="reposition" href="database-tables.php?action=convert&table='.urlencode($obj->Name).'&token='.newToken().'">'.$langs->trans("Convert").' InnoDb</a></td>';
215 } else {
216 print '<td>&nbsp;</td>';
217 }
218 print '<td>';
219 print $obj->Row_format;
220 if (isset($obj->Row_format) && (in_array($obj->Row_format, array("Compact")))) {
221 print '<br><a class="reposition" href="database-tables.php?action=convertdynamic&table='.urlencode($obj->Name).'&token='.newToken().'">'.$langs->trans("Convert").' Dynamic</a>';
222 }
223 print '</td>';
224 print '<td class="right">'.$obj->Rows.'</td>';
225 print '<td class="right">'.$obj->Avg_row_length.'</td>';
226 print '<td class="right">'.$obj->Data_length.'</td>';
227 print '<td class="right">'.$obj->Max_data_length.'</td>';
228 print '<td class="right">'.$obj->Index_length.'</td>';
229 print '<td class="right">'.$obj->Auto_increment.'</td>';
230 print '<td class="right">'.$obj->Check_time.'</td>';
231 print '<td class="right nowraponall">'.$obj->Collation;
232 // Link to convert collation
233 if (isset($obj->Collation)) {
234 print '<br><span class="opacitymedium small">'.$langs->trans("ConvertInto");
235 if (!in_array($obj->Collation, array("utf8_unicode_ci"))) {
236 print ' <a class="reposition" href="database-tables.php?action=convertutf8&table='.urlencode($obj->Name).'&token='.newToken().'" title="Charset utf8 + Collation utf8_unicode_ci">utf8</a>';
237 }
238 if (!in_array($obj->Collation, array("utf8mb4_unicode_ci"))) {
239 print ' <a class="reposition" href="database-tables.php?action=convertutf8mb4&table='.urlencode($obj->Name).'&token='.newToken().'" title="Charset utf8mb4 + Collation utf8mb4_unicode_ci">utf8mb4</a>';
240 }
241 print '</span>';
242 }
243 print '</td>';
244 print '</tr>';
245 $i++;
246 }
247 }
248 print '</table>';
249 print '</div>';
250 }
251
252 if ($base == 2) {
253 print '<div class="div-table-responsive-no-min">';
254 print '<table class="noborder">';
255 print '<tr class="liste_titre">';
256
257 print '<td>#</td>';
258 print '<td>'.$langs->trans("TableName").'</td>';
259 print '<td>Nb of tuples</td>';
260 print '<td>Nb index fetcher.</td>';
261 print '<td>Nb tuples insert</td>';
262 print '<td>Nb tuples modify</td>';
263 print '<td>Nb tuples delete</td>';
264 print "</tr>\n";
265
266 $sql = "SELECT relname, seq_tup_read, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del";
267 $sql .= " FROM pg_stat_user_tables ORDER BY relname";
268
269 $resql = $db->query($sql);
270 if ($resql) {
271 $num = $db->num_rows($resql);
272 $i = 0;
273 while ($i < $num) {
274 $row = $db->fetch_row($resql);
275 print '<tr class="oddeven">';
276 print '<td>'.($i + 1).'</td>';
277 print '<td>'.$row[0].'</td>';
278 print '<td class="right">'.$row[1].'</td>';
279 print '<td class="right">'.$row[2].'</td>';
280 print '<td class="right">'.$row[3].'</td>';
281 print '<td class="right">'.$row[4].'</td>';
282 print '<td class="right">'.$row[5].'</td>';
283 print '</tr>';
284 $i++;
285 }
286 }
287 print '</table>';
288 print '</div>';
289 }
290
291 if ($base == 4) {
292 // Sqlite by PDO or by Sqlite3
293 print '<div class="div-table-responsive-no-min">';
294 print '<table class="noborder">';
295 print '<tr class="liste_titre">';
296 print '<td>#</td>';
297 print '<td>'.$langs->trans("TableName").'</td>';
298 print '<td>'.$langs->trans("NbOfRecord").'</td>';
299 print "</tr>\n";
300
301 $sql = "SELECT name, type FROM sqlite_master where type='table' and name not like 'sqlite%' ORDER BY name";
302 $resql = $db->query($sql);
303
304 if ($resql) {
305 while ($row = $db->fetch_row($resql)) {
306 $rescount = $db->query("SELECT COUNT(*) FROM ".$row[0]);
307 if ($rescount) {
308 $row_count = $db->fetch_row($rescount);
309 $count = $row_count[0];
310 } else {
311 $count = '?';
312 }
313
314 print '<tr class="oddeven">';
315 print '<td>'.($i + 1).'</td>';
316 print '<td>'.$row[0].'</td>';
317 print '<td>'.$count.'</td>';
318 print '</tr>';
319 }
320 }
321
322 print '</table>';
323 print '</div>';
324 }
325}
326
327// End of page
328llxFooter();
329$db->close();
llxFooter
llxFooter($comment='', $zone='private', $disabledoutputofmessages=0)
Empty footer.
Definition wrapper.php:91
llxHeader
if(!defined('NOREQUIRESOC')) if(!defined( 'NOREQUIRETRAN')) if(!defined('NOTOKENRENEWAL')) if(!defined( 'NOREQUIREMENU')) if(!defined('NOREQUIREHTML')) if(!defined( 'NOREQUIREAJAX')) llxHeader($head='', $title='', $help_url='', $target='', $disablejs=0, $disablehead=0, $arrayofjs='', $arrayofcss='', $morequerystring='', $morecssonbody='', $replacemainareaby='', $disablenofollow=0, $disablenoindex=0)
Empty header.
Definition wrapper.php:73
dol_dir_list
dol_dir_list($utf8_path, $types="all", $recursive=0, $filter="", $excludefilter=null, $sortcriteria="name", $sortorder=SORT_ASC, $mode=0, $nohook=0, $relativename="", $donotfollowsymlinks=0, $nbsecondsold=0)
Scan a directory and return a list of files/directories.
Definition files.lib.php:64
setEventMessages
setEventMessages($mesg, $mesgs, $style='mesgs', $messagekey='', $noduplicate=0, $attop=0)
Set event messages in dol_events session object.
Definition functions.lib.php:11205
img_picto
img_picto($titlealt, $picto, $moreatt='', $pictoisfullpath=0, $srconly=0, $notitle=0, $alt='', $morecss='', $marginleftonlyshort=2, $allowothertags=array())
Show picto whatever it's its name (generic function)
Definition functions.lib.php:5577
newToken
newToken()
Return the value of token currently saved into session with name 'newtoken'.
Definition functions.lib.php:15451
GETPOST
GETPOST($paramname, $check='alphanohtml', $method=0, $filter=null, $options=null, $noreplace=0)
Return value of a param into GET or POST supervariable.
Definition functions.lib.php:1033
load_fiche_titre
load_fiche_titre($title, $morehtmlright='', $picto='generic', $pictoisfullpath=0, $id='', $morecssontable='', $morehtmlcenter='', $morecssonpicto='widthpictotitle')
Load a title with picto.
Definition functions.lib.php:7209
info_admin
info_admin($text, $infoonimgalt=0, $nodiv=0, $admin='1', $morecss='hideonsmartphone', $textfordropdown='', $picto='')
Show information in HTML for admin users or standard users.
Definition functions.lib.php:6782
accessforbidden
accessforbidden($message='', $printheader=1, $printfooter=1, $showonlymessage=0, $params=null)
Show a message to say access is forbidden and stop program.
Definition security.lib.php:1358
Generated on Mon Jun 22 2026 20:45:20 for dolibarr by Doxygen 1.11.0