function getColumns(knjdb_table $table)
{
if ($table->columns_changed) {
$f_gc = $this->knjdb->query("SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '" . $this->knjdb->sql($table->get("name")) . "' ORDER BY ORDINAL_POSITION");
while ($d_gc = $f_gc->fetch()) {
if (!$table->columns[$d_gc["COLUMN_NAME"]]) {
if ($d_gc["IS_NULLABLE"] == "NO") {
$notnull = "yes";
} else {
$notnull = "no";
}
$default = $d_gc["COLUMN_DEFAULT"];
if (substr($default, 0, 2) == "('") {
$default = substr($default, 2, -2);
} elseif (substr($default, 0, 1) == "(") {
$default = substr($default, 1, -1);
}
if (substr($default, 0, 1) == "(") {
//Fix two times!
$default = substr($default, 1, -1);
}
$primarykey = "no";
$autoincr = "no";
$type = $d_gc["DATA_TYPE"];
$table->columns[$d_gc["COLUMN_NAME"]] = new knjdb_column($table, array("name" => $d_gc["COLUMN_NAME"], "type" => $type, "maxlength" => $d_gc["CHARACTER_MAXIMUM_LENGTH"], "notnull" => $notnull, "default" => $default, "primarykey" => $primarykey, "autoincr" => $autoincr));
}
}
$table->columns_changed = false;
}
return $table->columns;
}
function getIndexes(knjdb_table $table){
if ($table->indexes_changed){
$f_gi = $this->knjdb->query("SHOW INDEX FROM " . $this->knjdb->connob->sep_table . $table->get("name") . $this->knjdb->connob->sep_table);
while($d_gi = $f_gi->fetch()){
if ($d_gi["Key_name"] != "PRIMARY"){
$key = $d_gi["Key_name"];
$index[$key]["name"] = $d_gi["Key_name"];
$index[$key]["columns"][] = $table->getColumn($d_gi["Column_name"]);
}
}
//Making keys to numbers (as in SQLite).
$return = array();
if ($index){
foreach($index AS $name => $value){
if (!$this->indexes[$name]){
$table->indexes[$name] = new knjdb_index($table, $value);
}
}
}
$table->indexes_changed = false;
}
return $table->indexes;
}
function getIndexes(knjdb_table $table)
{
if ($table->indexes_changed) {
$f_gi = $this->knjdb->query("\n\t\t\t\t\tSELECT\n\t\t\t\t\t\tid,\n\t\t\t\t\t\tname,\n\t\t\t\t\t\tindid,\n\t\t\t\t\t\tOBJECT_NAME(id) AS TableName\n\t\t\t\t\t\n\t\t\t\t\tFROM\n\t\t\t\t\t\tsysindexes\n\t\t\t\t\t\n\t\t\t\t\tWHERE\n\t\t\t\t\t\tOBJECT_NAME(sysindexes.id) = '" . $this->knjdb->sql($table->get("name")) . "'\n\t\t\t\t");
while ($d_gi = $f_gi->fetch()) {
$columns = array();
$f_gik = $this->knjdb->query("\n\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\tsyscolumns.name,\n\t\t\t\t\t\t\tOBJECT_NAME(syscolumns.id) AS TableName\n\t\t\t\t\t\t\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\tsysindexkeys,\n\t\t\t\t\t\t\tsyscolumns\n\t\t\t\t\t\t\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tsysindexkeys.id = '" . $this->knjdb->sql($d_gi["id"]) . "' AND\n\t\t\t\t\t\t\tsysindexkeys.indid = '" . $this->knjdb->sql($d_gi["indid"]) . "' AND\n\t\t\t\t\t\t\tsyscolumns.id = '" . $this->knjdb->sql($d_gi["id"]) . "' AND\n\t\t\t\t\t\t\tsyscolumns.colid = sysindexkeys.colid\n\t\t\t\t\t\t\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\tsyscolumns.name\n\t\t\t\t\t");
while ($d_gik = $f_gik->fetch()) {
$columns[$d_gik["name"]] = $table->getColumn($d_gik["name"]);
}
if (count($columns) > 0) {
//to avoid the actual system indexes with no columns which confuses...
$table->indexes[$d_gi["name"]] = new knjdb_index($table, array("name" => $d_gi["name"], "columns" => $columns));
}
}
$table->indexes_changed = false;
}
return $table->indexes;
}
function getIndexes(knjdb_table $table)
{
if ($table->indexes_changed) {
$f_gi = $this->knjdb->query("PRAGMA index_list(" . $table->get("name") . ")");
while ($d_gi = $f_gi->fetch()) {
if (strpos($d_gi["name"], "sqlite") !== false && strpos($d_gi["name"], "autoindex") !== false) {
//This is a SQLite-auto-index - do not show or add.
} elseif (!$table->indexes[$d_gi["name"]]) {
$index = array();
$index["name"] = $d_gi["name"];
$first = true;
$columns_text = "";
$f_gid = $this->knjdb->query("PRAGMA index_info('" . $d_gi["name"] . "')");
while ($d_gid = $f_gid->fetch()) {
$index["columns"][] = $table->getColumn($d_gid["name"]);
}
$table->indexes[$d_gi["name"]] = new knjdb_index($table, $index);
}
}
$table->indexes_changed = false;
}
return $table->indexes;
}
function removeColumn(knjdb_table $table, knjdb_column $column)
{
$sql = "ALTER TABLE " . $this->driver->sep_table . $table->get("name") . $this->driver->sep_table . " DROP COLUMN " . $this->driver->sep_col . $column->get("name") . $this->driver->sep_col;
$this->knjdb->query($sql);
unset($table->columns[$column->get("name")]);
}
function optimizeTable(knjdb_table $table){
$this->knjdb->query("OPTIMIZE TABLE " . $this->knjdb->conn->sep_table . $table->get("name") . $this->knjdb->conn->sep_table); //vacuum the entire database.
}
function truncateTable(knjdb_table $table)
{
$this->knjdb->query("DELETE FROM " . $this->knjdb->conn->sep_table . $table->get("name") . $this->knjdb->conn->sep_table);
}
function addColumns(knjdb_table $table, $columns)
{
//Again again... SQLite does not have a alter table... F*****g crap.
//Starting by creating a name for the temp-table.
$tempname = $table->get("name") . "_temp";
$tablename = $table->get("name");
//Editing the index-array for renamed columns.
$indexes = $table->getIndexes();
//Making SQL.
$oldcolumns = $table->getColumns();
$newcolumns = array();
foreach ($oldcolumns as $column) {
$newcolumns[] = $column->data;
}
foreach ($columns as $column) {
$newcolumns[] = $column;
}
$table->rename($tempname);
$this->knjdb->tables()->createTable($tablename, $newcolumns);
$newtable = $this->knjdb->getTable($tablename);
//If we are adding columns, the new columns are at their defaults, so we just have to add the old data.
//Making SQL for insert into new table.
$sql_insert = "INSERT INTO " . $this->knjdb->conn->sep_table . $tablename . $this->knjdb->conn->sep_table . " (";
//Creating the fields that should be insertet into for the SQL.
$first = true;
foreach ($oldcolumns as $column) {
if ($first == true) {
$first = false;
} else {
$sql_insert .= ", ";
}
$sql_insert .= $this->knjdb->conn->sep_col . $column->get("name") . $this->knjdb->conn->sep_col;
}
//If a new column has set "notnull" to be true, then we MUST insert into it (thanks evil devil).
foreach ($columns as $column) {
if ($column['notnull'] && !$column['default']) {
$sql_insert .= ", " . $this->knjdb->conn->sep_col . $column["name"] . $this->knjdb->conn->sep_col;
}
}
$sql_insert .= ") SELECT ";
$first = true;
foreach ($oldcolumns as $column) {
if ($first == true) {
$first = false;
} else {
$sql_insert .= ", ";
}
$sql_insert .= $this->knjdb->conn->sep_col . $column->get("name") . $this->knjdb->conn->sep_col;
}
//If a new column has set "notnull" to be true, then we MUST insert into it (thanks evil devil). So
//we are just emulating an empty string, which will be insertet.
foreach ($columns as $column) {
if ($column["notnull"] && !$column["default"]) {
$sql_insert .= ", '' AS " . $column["name"];
}
}
$sql_insert .= " FROM " . $this->knjdb->conn->sep_col . $tempname . $this->knjdb->conn->sep_col;
//Execute the insert-SQL.
$this->knjdb->query($sql_insert);
//Add indexes.
foreach ($indexes as $index) {
$cols = array();
foreach ($index->getColumns() as $col) {
$cols[] = $newtable->getColumn($col->get("name"));
}
$newtable->addIndex($cols);
}
//Drop the tempoary table.
$table->drop();
$newtable->columns_changed = true;
}
function removeColumn(knjdb_table $table, knjdb_column $column_remove)
{
$tablename = $table->get("name");
//Again... SQLite has no "ALTER TABLE".
$columns = $table->getColumns();
$indexes = $table->getIndexes();
$tempname = $tablename . "_temp";
$table->rename($tempname);
//Removing the specific column from the array.
$cols = array();
foreach ($columns as $key => $column) {
if ($column->get("name") != $column_remove->get("name")) {
$cols[] = $column->data;
}
}
$this->knjdb->tables()->createTable($tablename, $cols);
$newtable = $this->knjdb->getTable($tablename);
$sql_insert = "INSERT INTO " . $this->knjdb->conn->sep_table . $tablename . $this->knjdb->conn->sep_table . " SELECT ";
$first = true;
foreach ($columns as $column) {
if ($column->get("name") != $column_remove->get("name")) {
if ($first == true) {
$first = false;
} else {
$sql_insert .= ", ";
}
$sql_insert .= $this->knjdb->conn->sep_col . $column->get("name") . $this->knjdb->conn->sep_col;
$newcolumns[] = $value;
}
}
$sql_insert .= " FROM " . $this->knjdb->conn->sep_table . $tempname . $this->knjdb->conn->sep_table;
$this->knjdb->query($sql_insert);
//Creating indexes again from the array, that we saved at the beginning. In short terms this will rename the columns which have indexes to the new names, so that they wont be removed.
foreach ($indexes as $index) {
$cols = array();
foreach ($index->getColumns() as $column) {
$cols[] = $newtable->getColumn($column->get("name"));
}
}
//Drop the temp-table.
$table->drop();
unset($this->columns[$column_remove->get("name")]);
}
function removeIndex(knjdb_table $table, knjdb_index $index){
$sql = "DROP INDEX " . $this->knjdb->conn->sep_index . $index->get("name") . $this->knjdb->conn->sep_index;
$this->knjdb->query($sql);
$this->knjdb->query("VACUUM " . $this->knjdb->conn->sep_table . $table->get("name") . $this->knjdb->conn->sep_table);
unset($table->indexes[$index->get("name")]);
}
