if (!db_column_exists($table, $column)) { db_column_add($table, $column, $type); echo draw_li($column . ' was added to ' . $table); } else { echo draw_li($column . ' already existed in ' . $table); } } echo '</ul>'; //rename old columns echo draw_h3('Rename Old Columns'); echo '<ul>'; //columns to be renamed $columns = array(array('table' => 'channels', 'before' => 'title_en', 'after' => 'title'), array('table' => 'links', 'before' => 'text', 'after' => 'title'), array('table' => 'pages', 'before' => 'isInstancePage', 'after' => 'is_hidden'), array('table' => 'pages', 'before' => 'name', 'after' => 'title'), array('table' => 'pages', 'before' => 'helpText', 'after' => 'description'), array('table' => 'users', 'before' => 'image', 'after' => 'image_large')); foreach ($columns as $c) { extract($c); if (!db_column_exists($table, $after)) { db_column_rename($table, $before, $after); echo draw_li($table . '.' . $before . ' was renamed to ' . $after); } else { echo draw_li($after . ' already existed in ' . $table); } } echo '</ul>'; //fix column types echo draw_h3('Fix Column Types'); echo '<ul>'; $tables = db_tables(); foreach ($tables as $t) { $columns = db_columns($t); //die(draw_array($columns)); if (!stristr($t, '_to_') && !stristr($t, '_2_') && $t != 'it_system_status' && $t != 'docs_views' && $t != 'ldcodes' && $t != 'queries_executions' && $t != 'web_income_tables_values' && $columns[0]['type'] == 'int' && !$columns[0]['auto']) {
function db_upgrade_schema($db, $db_type, $display_results) { $db->beginTransaction(); /* //PHP PDO check if table or column exists //check if table exists SELECT * FROM sqlite_master WHERE type='table' and name='v_cdr' //check if column exists SELECT * FROM sqlite_master WHERE type='table' and name='v_cdr' and sql like '%caller_id_name TEXT,%' //aditional information http://www.sqlite.org/faq.html#q9 //postgresql //list all tables in the database select tablename from pg_tables where schemaname='public'; //check if table exists select * from pg_tables where schemaname='public' and tablename = 'v_groups' //check if column exists SELECT attname FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'v_cdr') AND attname = 'caller_id_name'; //mysql //list all tables in the database SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'fusionpbx' //check if table exists SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'fusionpbx' and TABLE_NAME = 'v_groups' //check if column exists SELECT * FROM information_schema.COLUMNS where TABLE_SCHEMA = 'fusionpbx' and TABLE_NAME = 'v_cdr' and COLUMN_NAME = 'context' //oracle //check if table exists SELECT TABLE_NAME FROM ALL_TABLES */ //schema sqlite db //--- begin: create the sqlite db file ----------------------------------------- $filename = $_SERVER["DOCUMENT_ROOT"] . PROJECT_PATH . '/includes/install/sql/sqlite.sql'; $file_contents = file_get_contents($filename); unset($filename); try { //$dbschema = new PDO('sqlite:'.$dbfilepath.'/'.$dbfilename); //sqlite 3 $dbschema = new PDO('sqlite::memory:'); //sqlite 3 $dbschema->beginTransaction(); } catch (PDOException $error) { print "error: " . $error->getMessage() . "<br/>"; die; } //replace \r\n with \n then explode on \n $file_contents = str_replace("\r\n", "\n", $file_contents); //loop line by line through all the lines of sql code $stringarray = explode("\n", $file_contents); $x = 0; foreach ($stringarray as $sql) { try { $dbschema->query($sql); } catch (PDOException $error) { echo "error: " . $error->getMessage() . " sql: {$sql}<br/>"; //die(); } $x++; } unset($file_contents, $sql); $dbschema->commit(); //--- end: create the sqlite db ----------------------------------------- if ($display_results) { echo "<strong>Database Type: " . $db_type . "</strong><br /><br />"; } //declare the variable(s) $sqlupdate = ''; //list all tables if ($display_results) { echo "<table width='100%' border='0' cellpadding='20' cellspacing='0'>\n"; echo "<tr>\n"; echo "<th>Table</th>\n"; echo "<th>Exists</th>\n"; echo "<th>Details</th>\n"; echo "<tr>\n"; } $var_id = $_GET["id"]; $sql = ""; $sql .= "SELECT * FROM sqlite_master WHERE type='table' "; $prepstatement = $dbschema->prepare(check_sql($sql)); $prepstatement->execute(); $result = $prepstatement->fetchAll(); foreach ($result as &$row) { $table_name = $row["name"]; $sql = trim($row["sql"]); $sql = str_replace("\r\n", " ", $sql); $sql = str_replace("\n", " ", $sql); $sql = str_replace(" ", " ", $sql); //echo "table name: $table_name<br />\n"; if ($display_results) { echo "<tr>\n"; } //check if the table exists if (db_table_exists($db, $db_type, $dbname, $table_name)) { if ($display_results) { echo "<td valign='top' class='rowstyle1'><strong>table</strong><br />{$table_name}</td>\n"; echo "<td valign='top' class='rowstyle1' style='background-color:#00FF00;'><strong>exists</strong><br />true</td>\n"; } if (preg_match("/\\(.*\\)/", $sql, $matches)) { //print_r($matches); if ($display_results) { echo "<td class='rowstyle1'>\n"; } //remove the ( ) brackets $column_list = trim($matches[0], " ()"); //explode the data into a column array with column name and datatype $column_list_array = explode(",", $column_list); //explode using the space to seperate the name and data type $x = 0; foreach ($column_list_array as &$row) { $row_array = explode(" ", trim($row)); //print_r($row_array); $column_array[$x]['column_name'] = $row_array[0]; if (count($row_array) == 2) { $column_array[$x]['column_data_type'] = $row_array[1]; } if (count($row_array) == 3) { $column_array[$x]['column_data_type'] = $row_array[1] . ' ' . $row_array[2]; } if (count($row_array) == 4) { $column_array[$x]['column_data_type'] = $row_array[1] . ' ' . $row_array[2] . ' ' . $row_array[3]; } $x++; } unset($column_list_array); //show the list of columns if ($display_results) { echo "<table border='0' cellpadding='10' cellspacing='0'>\n"; echo "<tr>\n"; echo "<th>name</th>\n"; echo "<th>type</th>\n"; echo "<th>exists</th>\n"; echo "</tr>\n"; } foreach ($column_array as &$column_row) { //print_r($column_row); if ($display_results) { echo "<tr>\n"; echo "<td class='rowstyle1' width='200'>" . $column_row['column_name'] . "</td>\n"; echo "<td class='rowstyle1'>" . $column_row['column_data_type'] . "</td>\n"; } if (db_column_exists($db, $db_type, $dbname, $table_name, $column_row['column_name'])) { if ($display_results) { echo "<td class='rowstyle1' style='background-color:#00FF00;'>true</td>\n"; echo "<td> </td>\n"; } } else { $tmpsql = "alter table " . $table_name . " add " . $column_row['column_name'] . " " . $column_row['column_data_type'] . "\n"; $sqlupdate .= $tmpsql; if ($display_results) { echo "<td class='rowstyle1' style='background-color:#FF0000;'>false</td>\n"; echo "<td class='rowstyle1' style='background-color:#FF0000;'>{$tmpsql}</td>\n"; } } if ($display_results) { echo "</tr>\n"; } } unset($column_array); if ($display_results) { echo "</table>\n"; } if ($display_results) { echo "</td>\n"; } } else { if ($display_results) { echo "<td class='rowstyle1'>\n"; echo "\t{$sql}"; echo "</td>\n"; } } } else { if ($db_type == "sqlite") { $sqlupdate .= $sql . "\n"; } if ($db_type == "pgsql") { $sql = str_replace("INTEGER PRIMARY KEY", "SERIAL", $sql); $sql = str_replace("NUMBER", "NUMERIC", $sql); $sqlupdate .= $sql . "\n"; } if ($db_type == "mysql") { $sql = str_replace("INTEGER PRIMARY KEY", "INT NOT NULL AUTO_INCREMENT PRIMARY KEY", $sql); $sql = str_replace("NUMBER", "NUMERIC", $sql); $sqlupdate .= $sql . "\n"; } if ($display_results) { echo "<td valign='top' class='rowstyle1'><strong>table</strong><br />{$table_name}</td>\n"; echo "<td valign='top' class='rowstyle1' style='background-color:#FF0000;'><strong>exists</strong><br />false</td>\n"; } } if ($display_results) { //echo "<br />\n"; echo "</tr>\n"; } } unset($prepstatement); if ($display_results) { if (strlen($sqlupdate) > 0) { echo "<tr>\n"; echo "<td class='rowstyle1' colspan='3'>\n"; echo "<pre>\n"; echo $sqlupdate; echo "</pre>\n"; echo "</td>\n"; echo "</tr>\n"; } echo "</table>\n"; } //loop line by line through all the lines of sql code $udpate_array = explode("\n", $sqlupdate); $x = 0; foreach ($udpate_array as $sql) { try { $db->query($sql); } catch (PDOException $error) { if ($display_results) { echo "error: " . $error->getMessage() . " sql: {$sql}<br/>"; } //die(); } $x++; } unset($file_contents, $sqlupdate, $sql); $db->commit(); }
function db_get_settings() { global $soa_use_active, $rr_use_active; global $soa_use_recursive; global $rr_extended_data, $rr_datalen; global $soa_use_xfer; global $soa_use_update_acl; global $soa_use_also_notify; global $soa_table_name, $rr_table_name; global $soa_active_types, $rr_active_types; global $soa_recursive_types; global $db_valid_types; global $allow_ixfr; $db_valid_types = db_get_known_types(); $soa_use_active = db_column_exists($soa_table_name, "active"); $soa_use_recursive = db_column_exists($soa_table_name, "recursive"); $soa_use_xfer = db_column_exists($soa_table_name, "xfer"); $soa_use_update_acl = db_column_exists($soa_table_name, "update_acl"); $soa_use_also_notify = db_column_exists($soa_table_name, "also_notify"); $rr_extended_data = db_column_exists($rr_table_name, "edata"); $rr_datalen = db_get_column_width($rr_table_name, 'data'); $rr_use_active = db_column_exists($rr_table_name, "active"); $allow_ixfr = $rr_use_active && db_column_exists($rr_table_name, "stamp") && db_column_exists($rr_table_name, "serial"); /* Get possible column values for 'active' column */ if ($soa_use_active) { $soa_active_types = db_get_active_types($soa_table_name, 0); } if ($soa_use_recursive) { $soa_recursive_types = db_get_recursive_types($soa_table_name); } if ($rr_use_active) { $rr_active_types = db_get_active_types($rr_table_name, 1); } }
function hmib_check_upgrade() { global $config, $database_default; include_once $config['library_path'] . '/database.php'; include_once $config['library_path'] . '/functions.php'; // Let's only run this check if we are on a page that actually needs the data $files = array('plugins.php', 'hmib.php'); if (isset($_SERVER['PHP_SELF']) && !in_array(basename($_SERVER['PHP_SELF']), $files)) { return; } $version = plugin_hmib_version(); $current = $version['version']; $old = db_fetch_cell("SELECT version FROM plugin_config WHERE directory='hmib'"); if ($current != $old) { if (api_plugin_is_enabled('hmib')) { # may sound ridiculous, but enables new hooks api_plugin_enable_hooks('hmib'); } db_execute("UPDATE plugin_config SET version='{$current}' WHERE directory='hmib'"); db_execute("UPDATE plugin_config SET \n\t\t\tversion='" . $version['version'] . "', \n\t\t\tname='" . $version['longname'] . "', \n\t\t\tauthor='" . $version['author'] . "', \n\t\t\twebpage='" . $version['url'] . "' \n\t\t\tWHERE directory='" . $version['name'] . "' "); $columns = db_fetch_assoc('SHOW columns FROM plugin_hmib_hrSWRun_last_seen'); foreach ($columns as $c) { $cols[] = $c[0]; } if (!db_column_exists('plugin_hmib_hrSWRun_last_seen', 'total_time')) { db_execute("ALTER TABLE plugin_hmib_hrSWRun_last_seen ADD COLUMN `total_time` BIGINT unsigned not null default '0' AFTER `name`"); } } }