/** * Obtient la description d'une table ou vue MySQL * * Récupère la définition d'une table ou d'une vue avec colonnes, indexes, etc. * au même format que la définition des tables SPIP, c'est à dire * un tableau avec les clés * * - `field` (tableau colonne => description SQL) et * - `key` (tableau type de clé => colonnes) * * @param string $nom_table Nom de la table SQL * @param string $serveur Nom de la connexion * @param bool $requeter Exécuter la requête, sinon la retourner * @return array|string * - chaîne vide si pas de description obtenue * - string Texte de la requête si demandé * - array description de la table sinon */ function spip_mysql_showtable($nom_table, $serveur = '', $requeter = true) { $s = spip_mysql_query("SHOW CREATE TABLE `{$nom_table}`", $serveur, $requeter); if (!$s) { return ''; } if (!$requeter) { return $s; } list(, $a) = mysqli_fetch_array($s, MYSQLI_NUM); if (preg_match(_MYSQL_RE_SHOW_TABLE, $a, $r)) { $desc = $r[1]; // extraction d'une KEY éventuelle en prenant garde de ne pas // relever un champ dont le nom contient KEY (ex. ID_WHISKEY) if (preg_match("/^(.*?),([^,]*\\sKEY[ (].*)\$/s", $desc, $r)) { $namedkeys = $r[2]; $desc = $r[1]; } else { $namedkeys = ""; } $fields = array(); foreach (preg_split("/,\\s*`/", $desc) as $v) { preg_match("/^\\s*`?([^`]*)`\\s*(.*)/", $v, $r); $fields[strtolower($r[1])] = $r[2]; } $keys = array(); foreach (preg_split('/\\)\\s*(,|$)/', $namedkeys) as $v) { if (preg_match("/^\\s*([^(]*)\\(([^(]*(\\(\\d+\\))?)\$/", $v, $r)) { $k = str_replace("`", '', trim($r[1])); $t = strtolower(str_replace("`", '', $r[2])); if ($k && !isset($keys[$k])) { $keys[$k] = $t; } else { $keys[] = $t; } } } spip_mysql_free($s); return array('field' => $fields, 'key' => $keys); } $res = spip_mysql_query("SHOW COLUMNS FROM `{$nom_table}`", $serveur); if ($res) { $nfields = array(); $nkeys = array(); while ($val = spip_mysql_fetch($res)) { $nfields[$val["Field"]] = $val['Type']; if ($val['Null'] == 'NO') { $nfields[$val["Field"]] .= ' NOT NULL'; } if ($val['Default'] === '0' || $val['Default']) { if (preg_match('/[A-Z_]/', $val['Default'])) { $nfields[$val["Field"]] .= ' DEFAULT ' . $val['Default']; } else { $nfields[$val["Field"]] .= " DEFAULT '" . $val['Default'] . "'"; } } if ($val['Extra']) { $nfields[$val["Field"]] .= ' ' . $val['Extra']; } if ($val['Key'] == 'PRI') { $nkeys['PRIMARY KEY'] = $val["Field"]; } else { if ($val['Key'] == 'MUL') { $nkeys['KEY ' . $val["Field"]] = $val["Field"]; } else { if ($val['Key'] == 'UNI') { $nkeys['UNIQUE KEY ' . $val["Field"]] = $val["Field"]; } } } } spip_mysql_free($res); return array('field' => $nfields, 'key' => $nkeys); } return ""; }
function spip_mysql_showtable($nom_table, $serveur='',$requeter=true) { $s = spip_mysql_query("SHOW CREATE TABLE `$nom_table`", $serveur, $requeter); if (!$s) return ''; if (!$requeter) return $s; list(,$a) = mysql_fetch_array($s ,MYSQL_NUM); if (preg_match("/^[^(),]*\((([^()]*\([^()]*\)[^()]*)*)\)[^()]*$/", $a, $r)){ $dec = $r[1]; if (preg_match("/^(.*?),([^,]*KEY.*)$/s", $dec, $r)) { $namedkeys = $r[2]; $dec = $r[1]; } else $namedkeys = ""; $fields = array(); foreach(preg_split("/,\s*`/",$dec) as $v) { preg_match("/^\s*`?([^`]*)`\s*(.*)/",$v,$r); $fields[strtolower($r[1])] = $r[2]; } $keys = array(); foreach(preg_split('/\)\s*,?/',$namedkeys) as $v) { if (preg_match("/^\s*([^(]*)\((.*)$/",$v,$r)) { $k = str_replace("`", '', trim($r[1])); $t = strtolower(str_replace("`", '', $r[2])); if ($k && !isset($keys[$k])) $keys[$k] = $t; else $keys[] = $t; } } spip_mysql_free($s); return array('field' => $fields, 'key' => $keys); } $res = spip_mysql_query("SHOW COLUMNS FROM `$nom_table`", $serveur); if($res) { $nfields = array(); $nkeys = array(); while($val = spip_mysql_fetch($res)) { $nfields[$val["Field"]] = $val['Type']; if($val['Null']=='NO') { $nfields[$val["Field"]] .= ' NOT NULL'; } if($val['Default'] === '0' || $val['Default']) { if(preg_match('/[A-Z_]/',$val['Default'])) { $nfields[$val["Field"]] .= ' DEFAULT '.$val['Default']; } else { $nfields[$val["Field"]] .= " DEFAULT '".$val['Default']."'"; } } if($val['Extra']) $nfields[$val["Field"]] .= ' '.$val['Extra']; if($val['Key'] == 'PRI') { $nkeys['PRIMARY KEY'] = $val["Field"]; } else if($val['Key'] == 'MUL') { $nkeys['KEY '.$val["Field"]] = $val["Field"]; } else if($val['Key'] == 'UNI') { $nkeys['UNIQUE KEY '.$val["Field"]] = $val["Field"]; } } spip_mysql_free($res); return array('field' => $nfields, 'key' => $nkeys); } return ""; }