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`");
}
}
}
