function _db_create_tables($OLD, $NEW, $database, &$count)
{
$count = 0;
$query = "";
foreach ($NEW as $newtable => $newdef) {
if (@$newdef["VIEW"]) {
$query .= _db_create_view($NEW, $newtable, $newdef["VIEW"]);
continue;
}
if ($newdef["DB"] != $database) {
$query .= "/* skipping table '{$newtable}', not in database '{$database}' */\n\n";
continue;
}
if (!db_access_table($newtable, "w")) {
$query .= "/* skipping table '{$newtable}', no write access */\n";
continue;
}
if (!@$newdef["SCHEMA_CONTROL"]) {
$query .= "/* skipping table '{$newtable}', no schema control over it */\n";
continue;
}
$singular = _db_singular($newtable, $NEW);
$primary = _db_primary($newtable, $NEW);
$secondary = $singular . "_name";
// !!! not generic!
$restrict = @$newdef["USER_RESTRICT"];
$tablename = $newtable;
if ($restrict) {
$tablename .= "_unrestr";
}
$tablename .= "_tp";
// completely new table or delta?
if (!isset($OLD[$newtable])) {
$count++;
$index = "";
$query .= "create table if not exists {$tablename} (\n";
foreach ($newdef["FIELDS"] as $field => $value) {
if (@$value["VIRTUAL"]) {
$query .= "/* omitting VIRTUAL {$field} */\n";
continue;
}
$query .= __db_create_field($field, $value);
$query .= ",\n";
}
$query .= " primary key({$primary}";
if ($newdef["TEMPORAL"]) {
$query .= ", " . $NEW[$newtable]["FIELDNAME_VERSION"];
}
$query .= "),\n";
$indices = _db_gen_indices($NEW, $newdef, $newtable, $secondary);
foreach ($indices as $dummy => $index) {
$query .= __db_create_index($index, false) . ",\n";
}
$engine = isset($DEF[$newtable]["ENGINE"]) ? $DEF[$newtable]["ENGINE"] : "";
if (!$engine) {
$engine = "myisam";
}
$query = preg_replace("/,\\s*\\Z/m", "\n", $query);
$query .= ") engine={$engine};\n";
$query .= __db_create_tpview($NEW, $newtable, $restrict);
} else {
// isset($OLD[$newtable]) => use "alter table"
$olddef = $OLD[$newtable];
$flag = 0;
$flag_col = 0;
$after = "";
foreach ($newdef["FIELDS"] as $field => $value) {
if (@$value["VIRTUAL"]) {
//$query .= "/* omitting VIRTUAL $field */\n";
continue;
}
if (isset($value["CHANGE_FROM"])) {
$count++;
$oldfield = $value["CHANGE_FROM"];
$query .= "alter table {$tablename}\n";
$query .= " change column " . $oldfield . " " . __db_create_field($field, $value) . " {$after};\n";
unset($olddef["FIELDS"][$oldfield]);
$flag++;
$flag_col++;
} elseif (isset($olddef["FIELDS"][$field])) {
$oldvalue = $olddef["FIELDS"][$field];
$diff = false;
foreach (array("SQL_TYPE", "DEFAULT", "BETWEEN", "LENGTH", "REGEX") as $test) {
if (isset($value[$test]) && (!isset($oldvalue[$test]) || $value[$test] != $oldvalue[$test])) {
$diff = true;
}
}
if ($diff) {
$count++;
$query .= "alter table {$tablename}\n";
$query .= " modify column " . __db_create_field($field, $value) . " {$after};\n";
$flag++;
$flag_col++;
}
if (!isset($value["DEFAULT"]) && isset($oldvalue["DEFAULT"])) {
$query .= "alter table {$tablename}\n";
$query .= " alter column " . $field . " drop default;\n";
$flag++;
$flag_col++;
}
} else {
// create new column
$count++;
$query .= "alter table {$tablename}\n";
$query .= " add column" . __db_create_field($field, $value) . " {$after};\n";
$flag++;
$flag_col++;
}
$after = "after {$field}";
}
$oldindices = _db_gen_indices($OLD, $olddef, $newtable, $secondary);
$newindices = _db_gen_indices($NEW, $newdef, $newtable, $secondary);
foreach ($newindices as $index) {
if (!in_array($index, $oldindices)) {
$count++;
$query .= "alter table {$tablename}\n";
$query .= " add" . __db_create_index($index, false) . ";\n";
$flag++;
}
}
foreach ($oldindices as $index) {
if (!in_array($index, $newindices)) {
$count++;
$query .= "alter table {$tablename}\n";
$query .= " drop" . __db_create_index($index, true) . ";\n";
$flag++;
}
}
foreach ($olddef["FIELDS"] as $field => $value) {
if (@$value["VIRTUAL"]) {
//$query .= "/* omitting OLD VIRTUAL $field */\n";
continue;
}
if (!isset($newdef["FIELDS"][$field])) {
$count++;
$query .= "alter table {$tablename}\n";
$query .= " drop column {$field};\n";
$flag++;
$flag_col++;
}
}
if ($flag_col) {
// whenever the *_tp table changes, mysql seems to require
// recreation of the view (otherwise the old definition would remain)
$count++;
$query .= __db_create_tpview($NEW, $newtable, $restrict);
} elseif ($flag) {
$query .= "\n";
}
}
}
foreach ($OLD as $oldtable => $olddef) {
if (!isset($NEW[$newtable])) {
$count++;
$query .= "drop table " . $oldtable . "_tp\n";
$count++;
$query .= "drop view " . $oldtable . "\n\n";
}
}
return $query;
}
function engine_run_once()
{
global $ENGINE;
poll_childs();
foreach ($ENGINE as $statefield => $deflist) {
if ($statefield == "GLOBAL.GLOBAL") {
continue;
}
$tmp = explode(".", $statefield);
$tablename = $tmp[0];
$fieldname = $tmp[1];
$primary = _db_primary($tablename);
$cond = make_selectcond($tablename, $fieldname, $deflist);
if (!$cond) {
continue;
}
$data = db_read($tablename, null, $cond, null, 0, 0);
foreach ($data as $rec) {
$cache = array();
foreach ($deflist as $def) {
$joinwith = $def["bp_joinwith"];
if (!@$cache[$joinwith]) {
if ($joinwith) {
$subcond = $cond;
foreach (split(",", $primary) as $pri) {
$subcond[$pri] = $rec[$pri];
}
$subdata = db_read("{$tablename},{$joinwith}", null, $subcond, null, 0, 0);
} else {
$subdata = array($rec);
}
$cache[$joinwith] = $subdata;
}
}
foreach ($cache as $subdata) {
foreach ($subdata as $subrec) {
treat_rec($subrec, $deflist);
}
}
}
}
}
function _db_make_idcond($table, $row)
{
global $ERROR;
global $debug;
$primary = _db_primary($table);
if ($debug) {
echo "_db_make_idcond table='{$table}' row=";
print_r($row);
echo "<br>\n";
}
$ok = true;
foreach (split(",", $primary) as $pr) {
if (!array_key_exists($pr, @$row)) {
$ok = false;
break;
}
}
$cond = array();
if ($row && $ok) {
foreach (split(",", $primary) as $pr) {
$cond[$pr] = @$row[$pr];
}
} else {
$keys = split(",", _db_unique($table));
if (!$keys) {
$ERROR = "primary key '{$primary}' is missing in your data, and other keys are not available";
return null;
}
foreach ($keys as $key) {
if (!$row || !array_key_exists($key, $row)) {
$ERROR = "id-key '{$key}' is missing in your data, and primary key '{$primary}' is also missing";
return null;
}
$cond[$key] = $row[$key];
}
}
return $cond;
}
function app_display_table($tp_table)
{
global $SCHEMA;
global $TOOL;
global $ERROR;
global $debug;
_db_temporal($tp_table, $table);
if (!db_access_table($table, "r")) {
tpl_error_permission_denied(null);
return;
}
app_tools($table);
$cond = array();
if (isset($SCHEMA[$table]["TOOLS"]["tool_search"]) && ($str = trim($TOOL["tool_search"])) && ($field = $TOOL["tool_search_field"])) {
if ($debug) {
echo "adding search condition '{$field}'<br>\n";
}
$cond["{$field}%"] = "%{$str}%";
}
$page_size = @$TOOL["tool_page_size"];
if (!$page_size || $page_size < 0) {
$page_size = 1;
}
$page_start = @$TOOL["tool_page_start"];
if (!$page_start || $page_start < 0) {
$page_start = 1;
}
if (!isset($SCHEMA[$table]["TOOLS"]["tool_page"])) {
$page_size = 0;
}
// xxx not generic!!!
if (@$SCHEMA[$table]["TOOLS"]["tool_level"]) {
$prio = @$TOOL["tool_level"];
//$cond["#class_name in (select class_name from classes where class_prio >= $prio)"] = true;
}
$tmp = db_read($tp_table, null, $cond, @$TOOL["order"], ($page_start - 1) * $page_size, $page_size);
if ($ERROR) {
tpl_error(array("ERROR" => $ERROR));
return;
}
$data = _app_prepare_data($table, $tmp, "invalid");
if (db_access_table($table, "w")) {
$data["EXTRAHEAD"] = "extra_3buttons_head";
$primary = _db_primary($table);
$data["EXTRA"]["button_edit"] = $primary;
$data["EXTRA"]["button_clone"] = $primary;
$data["EXTRA"]["button_delete"] = $primary;
}
tpl_display_table($data);
}
function _db_pass_typeinfo($MYSCHEMA)
{
// augment with additional info
$res = $MYSCHEMA;
foreach ($MYSCHEMA as $table => $tinfo) {
// default links from the primary and secondary keys to SELF
$make_ref = array();
$primary = _db_primary($table, $MYSCHEMA);
if ($primary) {
foreach (split(",", $primary) as $pr) {
if (!@$res[$table]["FIELDS"][$pr]["DISPLAY_TYPE"]) {
$res[$table]["FIELDS"][$pr]["DISPLAY_TYPE"] = "hidden";
}
}
$make_ref = array_merge(array($primary), @$tinfo["UNIQUE"]);
}
foreach ($make_ref as $fields) {
foreach (split(",", $fields) as $field) {
if (!@$tinfo["FIELDS"][$field]) {
continue;
}
if (!@$res[$table]["FIELDS"][$field]["TPL_DISPLAY"]) {
$res[$table]["FIELDS"][$field]["TPL_DISPLAY"] = "display_ref";
}
/* set to default */
}
}
if (!@$tinfo["TOOLS"]) {
$res[$table]["TOOLS"] = array("tool_search" => true, "tool_history" => true, "tool_page" => true);
/* set to default */
}
// check all FIELDS
foreach ($tinfo["FIELDS"] as $field => $finfo) {
// add default REF_LINKS for the first possible REFERENCES
if (@$finfo["REFERENCES"] && !array_key_exists("REF_LINKS", $finfo)) {
foreach ($finfo["REFERENCES"] as $link => $dummy) {
$split = preg_split("/\\./", $link);
$ref_table = $split[0];
$ref_field = $split[1];
// only create a default runtime link when the reference identifies target tuples _uniquely_
if ($ref_field == @$MYSCHEMA[$ref_table]["PRIMARY"] || array_search($ref_field, @$MYSCHEMA[$ref_table]["UNIQUE"]) !== false) {
$finfo["REF_LINKS"] = $link;
$res[$table]["FIELDS"][$field]["REF_LINKS"] = $link;
if (!@$finfo["POOL_DATA"]) {
$pool = array("TABLE" => $ref_table, "ORDER" => $ref_field);
$finfo["POOL_DATA"] = $pool;
$res[$table]["FIELDS"][$field]["POOL_DATA"] = $pool;
}
if (!$finfo["SIZE"]) {
$finfo["SIZE"] = 4;
$res[$table]["FIELDS"][$field]["SIZE"] = 4;
}
//"SHOW_FIELD" => "profile_name",
break;
}
}
}
// set some default values
$res[$table]["FIELDS"][$field]["MINLEN"] = 0;
/* set to dummy */
$res[$table]["FIELDS"][$field]["MAXLEN"] = 999;
/* set to dummy */
$res[$table]["FIELDS"][$field]["SIZE"] = 40;
/* set to default */
$type = @$finfo["TYPE"];
$res[$table]["FIELDS"][$field]["SQL_TYPE"] = $type;
//echo "matching type '$type'\n";
if (array_search($field, split(",", $primary)) !== false) {
$restype = "hidden";
} elseif (preg_match("/^(?:date)?time(stamp)?\$/", $type)) {
$restype = "string";
} elseif (preg_match("/^((?:tiny|small|medium)?int([\\(][0-9]+[\\)])?|bigint|decimal\\([0-9]+,[0-9]+\\))\$/", $type)) {
$restype = "int";
$res[$table]["FIELDS"][$field]["SIZE"] = 10;
/* set to default */
} elseif (preg_match("/^bool(ean)?\$/", $type)) {
$restype = "bool";
$res[$table]["FIELDS"][$field]["MAXLEN"] = null;
$res[$table]["FIELDS"][$field]["SIZE"] = null;
if (!@$res[$table]["FIELDS"][$field]["TPL_DISPLAY"]) {
$res[$table]["FIELDS"][$field]["TPL_DISPLAY"] = "display_bool";
}
} elseif (preg_match("/^(var)?char[\\(]([0-9]+)[\\)]\$/", $type, $matches)) {
$restype = "string";
if (!$matches[1]) {
$res[$table]["FIELDS"][$field]["MINLEN"] = $matches[2];
}
$res[$table]["FIELDS"][$field]["MAXLEN"] = $matches[2];
} elseif (preg_match("/^(tiny|small|medium|long)?text\$/", $type, $matches)) {
$restype = "text";
$res[$table]["FIELDS"][$field]["SIZE"] = 80;
/* set to default */
$res[$table]["FIELDS"][$field]["LINES"] = 4;
/* set to default */
$res[$table]["FIELDS"][$field]["MAXLEN"] = null;
if (!@$res[$table]["FIELDS"][$field]["TPL_DISPLAY"]) {
$res[$table]["FIELDS"][$field]["TPL_DISPLAY"] = "display_text";
}
} elseif (preg_match("/^(tiny|small|medium|long)?blob\$/", $type, $matches)) {
$restype = "blob";
if (!@$res[$table]["FIELDS"][$field]["TPL_DISPLAY"]) {
$res[$table]["FIELDS"][$field]["TPL_DISPLAY"] = "display_blob";
}
} elseif ($finfo) {
$restype = "virtual";
} else {
die("ERROR in schema: cannot translate SQL type '{$type}'\n");
}
if ($ref = @$finfo["REF_LINKS"]) {
//echo "Aha: table=$table field=$field<br>\n";
$restype = "selector";
$split = preg_split("/\\./", $ref);
//print_r($split); echo "<br>\n";
$res[$table]["FIELDS"][$field]["REF_TABLE"] = $split[0];
$res[$table]["FIELDS"][$field]["REF_FIELD"] = $split[1];
$res[$table]["FIELDS"][$field]["REF_FIELDS"] = split(",", $split[1]);
}
// Finally, store the result
$res[$table]["FIELDS"][$field]["DISPLAY_TYPE"] = $restype;
if (isset($finfo["LENGTH"])) {
$res[$table]["FIELDS"][$field]["MINLEN"] = $finfo["LENGTH"][0];
$res[$table]["FIELDS"][$field]["MAXLEN"] = $finfo["LENGTH"][1];
}
if ($fields = @$finfo["EXTRA_FIELD"]) {
$res[$table]["FIELDS"][$field]["EXTRA_FIELD"] = split(",", $fields);
} else {
$res[$table]["FIELDS"][$field]["EXTRA_FIELD"] = array();
}
foreach (array("SUB_DATA", "POOL_DATA") as $qtype) {
if ($sub_query = @$finfo[$qtype]) {
if (!@$sub_query["FIELD"]) {
// initialize with reasonable defaults: everything mentioned in JOINFIELDS and ORDER
$all = array();
if ($add = @$sub_query["JOINFIELDS"]) {
$all = explode(",", $add);
}
if ($add = @$sub_query["ORDER"]) {
$all = array_unique(array_merge(explode(",", $add), $all));
}
$res[$table]["FIELDS"][$field][$qtype]["FIELD"] = implode(",", $all);
}
if ($qtype == "SUB_DATA") {
if (!isset($finfo["VIRTUAL"])) {
$res[$table]["FIELDS"][$field]["VIRTUAL"] = true;
}
if (!isset($finfo["TPL_DISPLAY"])) {
$res[$table]["FIELDS"][$field]["TPL_DISPLAY"] = "display_reflist";
}
if (!isset($finfo["TPL_INPUT"])) {
$res[$table]["FIELDS"][$field]["TPL_INPUT"] = "input_sublist";
}
}
}
}
// user settings always take precedence
foreach (array("SIZE", "SORT", "LINES", "TPL_INPUT", "TPL_DISPLAY") as $further) {
if (@$finfo[$further]) {
$res[$table]["FIELDS"][$field][$further] = $finfo[$further];
}
}
}
}
return $res;
}
function _mysql_make_idcond_base($qstruct, $row)
{
global $ERROR;
$table = $qstruct["TABLE"];
if (!($cond = @$qstruct["COND"])) {
if (!$row && isset($qstruct["DATA"])) {
$primary = _db_primary($table);
$subcond = array();
foreach ($qstruct["DATA"] as $row) {
$subsubcond = array();
foreach (split(",", $primary) as $primfield) {
$subsubcond[$primfield] = $row[$primfield];
}
$subcond[] = $subsubcond;
}
$cond = array($subcond);
} else {
$cond = _db_make_idcond($table, $row);
}
}
$idcond = array("TABLE" => array($table), "FIELD" => array(), "COND" => $cond, "ORDER" => array(), "START" => 0, "COUNT" => 0);
if ($ERROR) {
$ERROR = "in table '{$table}': {$ERROR}";
}
return $idcond;
}