dolibarr 20.0.0
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 $sql = "ALTER TABLE ".$db->sanitize($table)." CHARACTER SET utf8 COLLATE utf8_unicode_ci";
56 $db->query($sql);
57}
58if ($action == 'convertutf8mb4') {
59 $sql = "ALTER TABLE ".$db->sanitize($table)." CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
60 $db->query($sql);
61}
62if ($action == 'convertdynamic') {
63 $sql = "ALTER TABLE ".$db->sanitize($table)." ROW_FORMAT=DYNAMIC;";
64 $db->query($sql);
65}
66
67
68/*
69 * View
70 */
71
72llxHeader('', '', '', '', 0, 0, '', '', '', 'mod-admin page-database_tables');
73
74print load_fiche_titre($langs->trans("Tables")." ".ucfirst($conf->db->type), '', 'title_setup');
75
76
77// Define request to get table description
78$base = 0;
79if (preg_match('/mysql/i', $conf->db->type)) {
80 $sql = "SHOW TABLE STATUS";
81 $base = 1;
82} elseif ($conf->db->type == 'pgsql') {
83 $sql = "SELECT conname, contype FROM pg_constraint;";
84 $base = 2;
85} elseif ($conf->db->type == 'mssql') {
86 //$sqls[0] = "";
87 //$base=3;
88} elseif ($conf->db->type == 'sqlite' || $conf->db->type == 'sqlite3') {
89 //$sql = "SELECT name, type FROM sqlite_master";
90 $base = 4;
91}
92
93
94if (!$base) {
95 print $langs->trans("FeatureNotAvailableWithThisDatabaseDriver");
96} else {
97 if ($base == 1) {
98 print '<div class="div-table-responsive-no-min">';
99 print '<table class="noborder">';
100 print '<tr class="liste_titre">';
101 print '<td>#</td>';
102 print '<td>'.$langs->trans("TableName").'</td>';
103 print '<td colspan="2">'.$langs->trans("Type").'</td>';
104 print '<td>'.$langs->trans("Format").'</td>';
105 print '<td class="right">'.$langs->trans("NbOfRecord").'</td>';
106 print '<td class="right">Avg_row_length</td>';
107 print '<td class="right">Data_length</td>';
108 print '<td class="right">Max_Data_length</td>';
109 print '<td class="right">Index_length</td>';
110 print '<td class="right">Increment</td>';
111 print '<td class="right">Last check</td>';
112 print '<td class="right">Collation</td>';
113 print "</tr>\n";
114
115 $arrayoffilesrich = dol_dir_list(DOL_DOCUMENT_ROOT.'/install/mysql/tables/', 'files', 0, '\.sql$');
116 $arrayoffiles = array();
117 $arrayoftablesautocreated = array();
118 foreach ($arrayoffilesrich as $value) {
119 //print $shortsqlfilename.' ';
120 $shortsqlfilename = preg_replace('/\-[a-z]+\./', '.', $value['name']);
121 $arrayoffiles[$value['name']] = $shortsqlfilename;
122 if ($value['name'] == $shortsqlfilename && ! preg_match('/\.key\.sql$/', $value['name'])) {
123 // This is a sql file automatically created
124 $arrayoftablesautocreated[$value['name']] = $shortsqlfilename;
125 }
126 }
127
128 // Now loop on tables really found into database
129 $sql = "SHOW TABLE STATUS";
130
131 $resql = $db->query($sql);
132 if ($resql) {
133 $num = $db->num_rows($resql);
134 $i = 0;
135 while ($i < $num) {
136 $obj = $db->fetch_object($resql);
137
138 print '<tr class="oddeven">';
139
140 print '<td>'.($i+1).'</td>';
141 print '<td class="tdoverflowmax300" title="'.dol_escape_htmltag($obj->Name).'"><a href="dbtable.php?table='.urlencode($obj->Name).'">'.$obj->Name.'</a>';
142 $tablename = preg_replace('/^'.MAIN_DB_PREFIX.'/', 'llx_', $obj->Name);
143
144 if (in_array($tablename.'.sql', $arrayoffiles)) {
145 if (in_array($tablename.'.sql', $arrayoftablesautocreated)) {
146 $img = "info";
147 } else {
148 $img = "info_black";
149 print img_picto($langs->trans("NotAvailableByDefaultEnabledOnModuleActivation"), $img, 'class="small opacitymedium"');
150 }
151 } else {
152 $img = "info_black";
153 print img_picto($langs->trans("ExternalModule"), $img, 'class="small"');
154 }
155 print '</td>';
156 print '<td>'.$obj->Engine.'</td>';
157 if (isset($obj->Engine) && $obj->Engine == "MyISAM") {
158 print '<td><a class="reposition" href="database-tables.php?action=convert&table='.urlencode($obj->Name).'&token='.newToken().'">'.$langs->trans("Convert").' InnoDb</a></td>';
159 } else {
160 print '<td>&nbsp;</td>';
161 }
162 print '<td>';
163 print $obj->Row_format;
164 if (isset($obj->Row_format) && (in_array($obj->Row_format, array("Compact")))) {
165 print '<br><a class="reposition" href="database-tables.php?action=convertdynamic&table='.urlencode($obj->Name).'&token='.newToken().'">'.$langs->trans("Convert").' Dynamic</a>';
166 }
167 print '</td>';
168 print '<td class="right">'.$obj->Rows.'</td>';
169 print '<td class="right">'.$obj->Avg_row_length.'</td>';
170 print '<td class="right">'.$obj->Data_length.'</td>';
171 print '<td class="right">'.$obj->Max_data_length.'</td>';
172 print '<td class="right">'.$obj->Index_length.'</td>';
173 print '<td class="right">'.$obj->Auto_increment.'</td>';
174 print '<td class="right">'.$obj->Check_time.'</td>';
175 print '<td class="right nowraponall">'.$obj->Collation;
176 // Link to convert collation
177 if (isset($obj->Collation)) {
178 print '<br><span class="opacitymedium small">'.$langs->trans("ConvertInto");
179 if (!in_array($obj->Collation, array("utf8_unicode_ci"))) {
180 print ' <a class="reposition" href="database-tables.php?action=convertutf8&table='.urlencode($obj->Name).'&token='.newToken().'">utf8</a>';
181 }
182 if (!in_array($obj->Collation, array("utf8mb4_unicode_ci"))) {
183 print ' <a class="reposition" href="database-tables.php?action=convertutf8mb4&table='.urlencode($obj->Name).'&token='.newToken().'">utf8mb4</a>';
184 }
185 print '</span>';
186 }
187 print '</td>';
188 print '</tr>';
189 $i++;
190 }
191 }
192 print '</table>';
193 print '</div>';
194 }
195
196 if ($base == 2) {
197 print '<div class="div-table-responsive-no-min">';
198 print '<table class="noborder">';
199 print '<tr class="liste_titre">';
200
201 print '<td>#</td>';
202 print '<td>'.$langs->trans("TableName").'</td>';
203 print '<td>Nb of tuples</td>';
204 print '<td>Nb index fetcher.</td>';
205 print '<td>Nb tuples insert</td>';
206 print '<td>Nb tuples modify</td>';
207 print '<td>Nb tuples delete</td>';
208 print "</tr>\n";
209
210 $sql = "SELECT relname, seq_tup_read, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del";
211 $sql .= " FROM pg_stat_user_tables ORDER BY relname";
212
213 $resql = $db->query($sql);
214 if ($resql) {
215 $num = $db->num_rows($resql);
216 $i = 0;
217 while ($i < $num) {
218 $row = $db->fetch_row($resql);
219 print '<tr class="oddeven">';
220 print '<td>'.($i+1).'</td>';
221 print '<td>'.$row[0].'</td>';
222 print '<td class="right">'.$row[1].'</td>';
223 print '<td class="right">'.$row[2].'</td>';
224 print '<td class="right">'.$row[3].'</td>';
225 print '<td class="right">'.$row[4].'</td>';
226 print '<td class="right">'.$row[5].'</td>';
227 print '</tr>';
228 $i++;
229 }
230 }
231 print '</table>';
232 print '</div>';
233 }
234
235 if ($base == 4) {
236 // Sqlite by PDO or by Sqlite3
237 print '<div class="div-table-responsive-no-min">';
238 print '<table class="noborder">';
239 print '<tr class="liste_titre">';
240 print '<td>#</td>';
241 print '<td>'.$langs->trans("TableName").'</td>';
242 print '<td>'.$langs->trans("NbOfRecord").'</td>';
243 print "</tr>\n";
244
245 $sql = "SELECT name, type FROM sqlite_master where type='table' and name not like 'sqlite%' ORDER BY name";
246 $resql = $db->query($sql);
247
248 if ($resql) {
249 while ($row = $db->fetch_row($resql)) {
250 $rescount = $db->query("SELECT COUNT(*) FROM ".$row[0]);
251 if ($rescount) {
252 $row_count = $db->fetch_row($rescount);
253 $count = $row_count[0];
254 } else {
255 $count = '?';
256 }
257
258 print '<tr class="oddeven">';
259 print '<td>'.($i+1).'</td>';
260 print '<td>'.$row[0].'</td>';
261 print '<td>'.$count.'</td>';
262 print '</tr>';
263 }
264 }
265
266 print '</table>';
267 print '</div>';
268 }
269}
270
271// End of page
272llxFooter();
273$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.
accessforbidden($message='', $printheader=1, $printfooter=1, $showonlymessage=0, $params=null)
Show a message to say access is forbidden and stop program.