function get_foreign_keys_constraints_query($table_name)
{
/*
$q = "SELECT a.table_name, a.column_name, a.constraint_name, c.owner, c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
FROM all_cons_columns a
JOIN all_constraints c ON a.owner = c.owner
AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'
AND a.table_name = '".strtoupper(totally_escape($table_name))."'";
*/
$q = "SELECT c_list.CONSTRAINT_NAME as NAME,\nsubstr(c_src.COLUMN_NAME, 1, 20) as SRC_COLUMN,\nc_dest.TABLE_NAME as DEST_TABLE,\nsubstr(c_dest.COLUMN_NAME, 1, 20) as DEST_COLUMN\nFROM ALL_CONSTRAINTS c_list, ALL_CONS_COLUMNS c_src, ALL_CONS_COLUMNS c_dest\nWHERE c_list.CONSTRAINT_NAME = c_src.CONSTRAINT_NAME\nAND c_list.R_CONSTRAINT_NAME = c_dest.CONSTRAINT_NAME\nAND c_list.CONSTRAINT_TYPE = 'R'\nAND c_src.TABLE_NAME = '" . strtoupper(totally_escape($table_name)) . "'\nGROUP BY c_list.CONSTRAINT_NAME, c_src.TABLE_NAME,\n c_src.COLUMN_NAME, c_dest.TABLE_NAME, c_dest.COLUMN_NAME;";
return $q;
}
FOREIGN KEY ("DT")
REFERENCES "MARKS" ("MARK_DATE")
*/
$fkq = "ALTER TABLE \"" . totally_escape($table_name) . "\" ADD FOREIGN KEY (";
if (isset($_POST["foreign_key_columns"]) || !isset($_POST["foreign_key_columns"][$i])) {
$list = "";
foreach ($_POST["foreign_key_columns"][$i] as $cname) {
if ($list == "") {
$list = "\"" . $cname . "\"";
} else {
$list .= ", \"" . $cname . "\"";
}
}
$fkq .= $list;
}
$fkq .= ") REFERENCES \"" . totally_escape($_POST["foreign_key_table"][$i]) . "\" (";
if (isset($_POST["foreign_key_other_columns"]) || !isset($_POST["foreign_key_other_columns"][$i])) {
$list = "";
foreach ($_POST["foreign_key_other_columns"][$i] as $cname) {
if ($list == "") {
$list = "\"" . $cname . "\"";
} else {
$list .= ", \"" . $cname . "\"";
}
}
$fkq .= $list;
}
$fkq .= ")";
if (odbc_exec($client->get_connection(), $fkq) === false) {
$rollback_needed = true;
$rollback_error_message = get_odbc_error();
$list .= ", \"" . $cname . "\"";
}
}
$fkq .= $list;
}
$fkq .= ")";
if (odbc_exec($client->get_connection(), $fkq) === false) {
$rollback_needed = true;
$rollback_error_message = $fkq . "\n\n" . get_odbc_error();
break;
}
}
}
//check if rollback needed
if ($rollback_needed === true) {
odbc_exec($client->get_connection(), "DROP TABLE " . totally_escape($table_name));
//we don't care whether it's successful
//but as table is not dropped after rollback, that might fix our problem
}
if ($rollback_needed === true) {
if (odbc_exec($client->get_connection(), "ROLLBACK;") === false) {
die("Error occurred. Was unable rollback the transaction:\n\n" . $rollback_error_message . "\n\n" . get_odbc_error());
}
die("Error occurred. Transaction was rollbacked.\n\n" . $rollback_error_message);
}
if (odbc_exec($client->get_connection(), "COMMIT;") === false) {
$err = get_odbc_error();
if (odbc_exec($client->get_connection(), "ROLLBACK;") === false) {
die("Was unable to both commit and rollback the transaction:\n\n" . $err . "\n\n" . get_odbc_error());
}
die("Was unable to both commit the transaction. It was rollbacked.\n\n" . $err);
<?php
//check auth
include_once "../functions/client.php";
include_once "../functions/utils.php";
$client = new client();
if (!$client->logged_in()) {
die("false");
}
//check POST
$table_name = null;
$fields_count = 0;
$rowid = null;
if ($_POST) {
$table_name = totally_escape($_POST["table_name"]);
$fields_count = $_POST["fields_count"];
$rowid = totally_escape($_POST["rowid"]);
}
if ($table_name == null) {
die("false");
}
//TODO check table_name is one word
//prepare statement
if ($rowid == null) {
$query = "INSERT INTO " . $table_name . " VALUES(";
for ($i = 1; $i < $fields_count; ++$i) {
$query .= "?, ";
}
$query .= "?);";
} else {
$colnames = odbc_exec($client->get_connection(), "SELECT column_name, data_type, data_length FROM ALL_TAB_COLUMNS WHERE table_name = '" . strtoupper($table_name) . "';");
$q2 = "";
$tabs = array("tables" => "Tables", "compose_report" => "Compose report", "execute_query" => "Execute query");
$tab = key($tabs);
$action = "list";
$target = "";
$rowid = "";
foreach ($_GET as $k => $v) {
$k = totally_escape($k);
if ($k === "action") {
$action = totally_escape($v);
} else {
if ($k === "target") {
$target = totally_escape($v);
} else {
if ($k === "rowid") {
$rowid = totally_escape($v);
} else {
foreach ($tabs as $tab_name => $tab_title) {
if ($k === $tab_name) {
$tab = $k;
}
}
}
}
}
}
$found = false;
foreach ($tabs as $tab_name => $tab_title) {
if ($tab == $tab_name) {
$found = true;
break;
ID INTEGER NOT NULL PRIMARY KEY,
student_ID INTEGER NOT NULL UNIQUE,
group_ID INTEGER NOT NULL UNIQUE,
policy INTEGER NOT NULL CHECK(policy >= 100000 AND policy <= 999999),
FOREIGN KEY (student_ID) REFERENCES students (ID),
FOREIGN KEY (group_ID) REFERENCES groups (ID)
);
*/
?>
<div id="save_message" style="display: none;"></div>
<form id='table_form'>
<?php
echo "<input type='" . ($target == "" ? "text" : "hidden") . "' placeholder='table name' name='table_name' value='" . totally_escape($target) . "' class='create_table_table_name_field'/>";
if ($target != "") {
echo "<h1>" . totally_escape($target) . "</h1>";
echo "<input type='hidden' name='mode' value='editing'/>";
}
?>
<input type='hidden' name='fields_count' id='fields_count' value='0'/>
<input type='hidden' name='foreign_keys_count' id='foreign_keys_count' value='0'/>
<table id="table_columns" class="results_table">
<tr>
<th>Column Name</th>
<th>Type</th>
<th style="max-width: 50pt;">Precision</th>
<th style="max-width: 50pt;">Length</th>
<th style="min-width: 55pt; max-width: 55pt;">Not NULL</th>
<th style="max-width: 50pt;">Unique</th>
<th style="max-width: 50pt;">Primary</th>
}
$query .= " FROM " . $table_name . " WHERE rownum <= ?;";
//prepare statement
$statement = odbc_prepare($client->get_connection(), $query);
if ($statement === false) {
return $query . "\n\n" . get_odbc_error();
}
$items = array();
$items[] = (int) $rownum;
$result = odbc_execute($statement, $items);
if ($result === false) {
return $query . "\n\n" . get_odbc_error();
}
return $statement;
}
$result = get_report($client, totally_escape($_POST["target"]), $_POST["show"], $_POST["rownum"]);
if (is_string($result)) {
echo "<div class=\"error_message\">" . $result . "</div>";
} else {
make_results_table($result, false, null);
}
} else {
//show form
?>
<div id="form_message" style="display: none;"></div>
<form action="" method="post" class="report_form" id="report_form">
<p>Compose report of <input type='number' name='rownum' value='50' min='1'/> rows from table
<select id="report_target" name="target" onchange="show_fields();">
<option value=""><select table></option>
<?php
//check auth
include_once "../functions/client.php";
include_once "../functions/utils.php";
$client = new client();
if (!$client->logged_in()) {
die("false");
}
//check POST
$table_name = null;
if ($_POST) {
$table_name = totally_escape($_POST["target"]);
}
if ($table_name == null) {
die("false");
}
//TODO check table_name is one word
//select column names
$colnames = odbc_exec($client->get_connection(), "SELECT column_name, data_type, data_length FROM ALL_TAB_COLUMNS WHERE table_name = '" . strtoupper($table_name) . "';");
if ($colnames === false) {
die("false");
}
$q2 = "";
while (odbc_fetch_row($colnames)) {
if ($q2 == "") {
$q2 .= '["' . odbc_result($colnames, 1) . '"';
} else {
$q2 .= ', "' . odbc_result($colnames, 1) . '"';
}
}
$q2 .= "]";
