public function mysql_delete()
{
parent::mysql_delete();
$this->delete_object_links(TableIndexColumn::get_mysql_table());
}
public function mysql_save_from_post($post)
{
$simulation = false;
if (array_key_exists("save_simulation", $post) && $post['save_simulation'] == 1) {
$simulation = true;
}
$lid = parent::mysql_save_from_post($post);
if (!$simulation) {
$this->mysql_delete_TableColumn();
}
if (array_key_exists("cols", $post)) {
if ($simulation) {
$table_name = "`" . self::get_table_prefix() . "_temp_" . $lid . "`";
} else {
$table_name = "`" . self::get_table_prefix() . "_" . $lid . "`";
}
//table
if (!$simulation) {
$sql = "DROP TABLE IF EXISTS " . $table_name . ";";
mysql_query($sql);
}
$sql = "CREATE " . ($simulation ? "TEMPORARY" : "") . " TABLE " . $table_name . " (";
$i = 0;
$timestamp = false;
foreach ($post['cols'] as $col_json) {
$col = json_decode($col_json);
if ($i > 0) {
$sql .= ",";
}
if (!$timestamp && $col->type == "timestamp") {
$timestamp = true;
$col->defaultValue = "CURRENT_TIMESTAMP";
$col->attributes = "on update current_timestamp";
$post['cols'][$i] = json_encode($col);
}
$sql .= "`" . $col->name . "` " . TableColumn::get_column_definition($col->type, $col->lengthValues, $col->attributes, $col->nullable, 0, $col->defaultValue);
$i++;
}
$sql .= ") ENGINE = INNODB DEFAULT CHARSET=utf8;";
if (!mysql_query($sql)) {
$message = mysql_error();
return json_encode(array("result" => -6, "message" => $message));
}
//indexes
if (array_key_exists("indexes", $post)) {
foreach ($post['indexes'] as $index_json) {
$index = json_decode($index_json);
$columns = explode(",", $index->columns);
$cols = "";
foreach ($columns as $column) {
if ($cols != "") {
$cols .= ",";
}
$cols .= "`" . $column . "`";
}
$sql = sprintf("ALTER TABLE %s ADD %s(%s)", $table_name, $index->type, $cols);
if (!mysql_query($sql)) {
$message = mysql_error();
return json_encode(array("result" => -6, "message" => $message));
}
}
}
//auto increment
$auto_increment = false;
foreach ($post['cols'] as $col_json) {
$col = json_decode($col_json);
if ($col->auto_increment == 1) {
$auto_increment = true;
self::$auto_increment_row_comparer_field = $col->name;
$sql = sprintf("ALTER TABLE %s CHANGE `%s` `%s` %s", $table_name, $col->name, $col->name, TableColumn::get_column_definition($col->type, $col->lengthValues, $col->attributes, $col->nullable, $col->auto_increment, $col->defaultValue));
if (!mysql_query($sql)) {
$message = mysql_error();
return json_encode(array("result" => -6, "message" => $message));
}
}
}
//TableColumn
$sql = "START TRANSACTION";
mysql_query($sql);
$sql = sprintf("INSERT INTO `%s` (`index`,`name`,`Table_id`,`type`, `length`,`attributes`, `null`, `auto_increment`, `default_value`) VALUES ", TableColumn::get_mysql_table());
$i = 0;
foreach ($post['cols'] as $col_json) {
$col = json_decode($col_json);
if ($i > 0) {
$sql .= ",";
}
$sql .= "(";
$sql .= $i + 1 . ",'" . mysql_real_escape_string($col->name) . "'," . $lid . ",'" . mysql_real_escape_string($col->type) . "', '" . mysql_real_escape_string($col->lengthValues) . "', '" . mysql_real_escape_string($col->attributes) . "', " . mysql_real_escape_string($col->nullable) . "," . mysql_real_escape_string($col->auto_increment) . ",'" . mysql_real_escape_string($col->defaultValue) . "'";
$sql .= ")";
$i++;
}
if (!mysql_query($sql)) {
$message = mysql_error();
$sql = "ROLLBACK";
mysql_query($sql);
return json_encode(array("result" => -6, "message" => $message));
}
//TableIndex
if (array_key_exists("indexes", $post)) {
foreach ($post['indexes'] as $index_json) {
$index = json_decode($index_json);
$columns = explode(",", $index->columns);
$ti = new TableIndex();
$ti->Table_id = $lid;
$ti->type = $index->type;
$ti_lid = $ti->mysql_save();
$i = 0;
foreach ($columns as $col) {
$tc = TableColumn::from_property(array("Table_id" => $lid, "name" => $col), false);
$tic = new TableIndexColumn();
$tic->index = $i;
$tic->TableIndex_id = $ti_lid;
if ($tc != null) {
$tic->TableColumn_id = $tc->id;
}
$tic->mysql_save();
$i++;
}
}
}
//data
if (array_key_exists("rows", $post) && $post['rows'] != null && is_array($post['rows'])) {
if ($auto_increment) {
usort($post['rows'], "self::auto_increment_row_comparer");
}
$sql = "INSERT INTO " . $table_name . " (";
$i = 0;
foreach ($post['cols'] as $col_json) {
if ($i > 0) {
$sql .= ",";
}
$col = json_decode($col_json);
$sql .= "`" . $col->name . "`";
$i++;
}
$sql .= ") VALUES ";
for ($a = 0; $a < count($post['rows']); $a++) {
$row = json_decode($post['rows'][$a]);
if ($a > 0) {
$sql .= ",";
}
$sql .= "(";
$i = 0;
foreach ($post['cols'] as $col_json) {
$col = json_decode($col_json);
$col_name = $col->name;
if ($row->{$col_name} == "" && $col->nullable == 1) {
$row->{$col_name} = null;
}
if ($i > 0) {
$sql .= ",";
}
if ($row->{$col_name} !== null) {
$sql .= "'" . mysql_real_escape_string($row->{$col_name}) . "'";
} else {
$sql .= "NULL";
}
$i++;
}
$sql .= ")";
}
if (!mysql_query($sql)) {
$message = mysql_error();
$sql = "ROLLBACK";
mysql_query($sql);
return json_encode(array("result" => -6, "message" => $message));
}
}
}
if (!$simulation) {
$sql = "COMMIT";
mysql_query($sql);
} else {
$sql = "ROLLBACK";
mysql_query($sql);
}
//hash
$obj = static::from_mysql_id($lid);
if ($obj != null) {
$xml_hash = $obj->calculate_xml_hash();
$obj->xml_hash = $xml_hash;
$obj->mysql_save();
}
return $lid;
}