dolibarr  21.0.0-alpha
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 
27 if (! 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
32 require '../../main.inc.php';
33 require_once DOL_DOCUMENT_ROOT.'/core/lib/admin.lib.php';
34 require_once DOL_DOCUMENT_ROOT.'/core/lib/files.lib.php';
35 
36 $langs->load("admin");
37 
38 if (!$user->admin) {
40 }
41 
42 $table = GETPOST('table', 'aZ09');
43 $action = GETPOST('action', 'aZ09');
44 
45 
46 /*
47  * Actions
48  */
49 
50 if ($action == 'convert') { // Convert engine into innodb
51  $sql = "ALTER TABLE ".$db->sanitize($table)." ENGINE=INNODB";
52  $db->query($sql);
53 }
54 if ($action == 'convertutf8') {
55  $sql = "ALTER TABLE ".$db->sanitize($table)." CHARACTER SET utf8 COLLATE utf8_unicode_ci";
56  $db->query($sql);
57 }
58 if ($action == 'convertutf8mb4') {
59  $sql = "ALTER TABLE ".$db->sanitize($table)." CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
60  $db->query($sql);
61 }
62 if ($action == 'convertdynamic') {
63  $sql = "ALTER TABLE ".$db->sanitize($table)." ROW_FORMAT=DYNAMIC;";
64  $db->query($sql);
65 }
66 
67 
68 /*
69  * View
70  */
71 
72 llxHeader('', '', '', '', 0, 0, '', '', '', 'mod-admin page-database_tables');
73 
74 print load_fiche_titre($langs->trans("Tables")." ".ucfirst($conf->db->type), '', 'title_setup');
75 
76 
77 // Define request to get table description
78 $base = 0;
79 if (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 
94 if (!$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
272 llxFooter();
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
if(isModEnabled('invoice') && $user->hasRight('facture', 'lire')) if((isModEnabled('fournisseur') &&!getDolGlobalString('MAIN_USE_NEW_SUPPLIERMOD') && $user->hasRight("fournisseur", "facture", "lire"))||(isModEnabled('supplier_invoice') && $user->hasRight("supplier_invoice", "lire"))) if(isModEnabled('don') && $user->hasRight('don', 'lire')) if(isModEnabled('tax') && $user->hasRight('tax', 'charges', 'lire')) if(isModEnabled('invoice') &&isModEnabled('order') && $user->hasRight("commande", "lire") &&!getDolGlobalString('WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER')) $sql
Social contributions to pay.
Definition: index.php:751
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.