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