429 lines
17 KiB
JavaScript
Executable File
429 lines
17 KiB
JavaScript
Executable File
CSV2SQL = function(anp, input){
|
|
|
|
const self = this;
|
|
let started = false;
|
|
|
|
let mysql = this.mysql;
|
|
let sql_server = this.sql_server;
|
|
|
|
const construct = () => {
|
|
|
|
CSV2SQL.MySQL && (mysql = self.mysql = new CSV2SQL.MySQL(self, input));
|
|
CSV2SQL.SQLServer && (sql_server = self.sql_server = new CSV2SQL.SQLServer(self, input));
|
|
|
|
};
|
|
|
|
this.start = callback => {
|
|
|
|
const end = status => csv2sql.is_function(callback) && callback(status);
|
|
|
|
if(started){
|
|
end(false);
|
|
return;
|
|
};
|
|
started = true;
|
|
|
|
self.launch(self, ["mysql", "sql_server"], () => {
|
|
end(true);
|
|
});
|
|
|
|
return true;
|
|
};
|
|
|
|
this.create_subforms = item => {
|
|
|
|
const default_database = self.item_self.querySelector("[name=default_database]").value,
|
|
creator_database = self.item_self.querySelector("[name=default_creator_database]").checked,
|
|
remover_tables = self.item_self.querySelector("[name=default_remover_tables]").checked,
|
|
creator_tables = self.item_self.querySelector("[name=default_creator_tables]").checked,
|
|
updater_tables = self.item_self.querySelector("[name=default_updater_tables]").checked,
|
|
filler_tables = self.item_self.querySelector("[name=default_filler_tables]").checked,
|
|
uniques = self.item_self.querySelector("[name=default_uniques]").value,
|
|
id_attribute = self.item_self.querySelector("[name=default_id_attribute]").checked,
|
|
deleted_attribute = self.item_self.querySelector("[name=default_deleted_attribute]").checked,
|
|
date_in_attribute = self.item_self.querySelector("[name=default_date_in_attribute]").checked,
|
|
date_out_attribute = self.item_self.querySelector("[name=default_date_out_attribute]").checked,
|
|
fill_last_attribute = self.item_self.querySelector("[name=default_fill_last_attribute]").checked,
|
|
files = self.comp.files.get(item),
|
|
container = self.item_self.querySelector(".field[data-i18n=files_data] .content");
|
|
|
|
(self.comp.files.get(item) || []).forEach((file, i) => {
|
|
|
|
if(file){
|
|
|
|
if(container.querySelector("[data-i='" + i + "']"))
|
|
return;
|
|
|
|
const block = container.appendChild(document.createElement("div"));
|
|
let html;
|
|
|
|
block.innerHTML = html = self.comp.forms.create({
|
|
i18n : "file_data",
|
|
variables : {
|
|
i : i,
|
|
mime : file.mime,
|
|
name : file.name,
|
|
size : file.size
|
|
},
|
|
structure : [
|
|
{name : "database", type : "text", value : default_database},
|
|
{name : "table", type : "text", value : (file.name[0].toUpperCase() + file.name.replace(/\.csv$/i, "").substr(1)).replace(/[^a-z0-9]+([a-z])?/gi, (...arguments) => (arguments[1] || "").toUpperCase())},
|
|
{name : "creator_database", type : "checkbox", checked : creator_database},
|
|
{name : "remover_tables", type : "checkbox", checked : remover_tables},
|
|
{name : "creator_tables", type : "checkbox", checked : creator_tables},
|
|
{name : "updater_tables", type : "checkbox", checked : updater_tables},
|
|
{name : "filler_tables", type : "checkbox", checked : filler_tables},
|
|
{name : "uniques", type : "text", value : uniques, multiline : true},
|
|
{name : "id_attribute", type : "checkbox", checked : id_attribute},
|
|
{name : "deleted_attribute", type : "checkbox", checked : deleted_attribute},
|
|
{name : "date_in_attribute", type : "checkbox", checked : date_in_attribute},
|
|
{name : "date_out_attribute", type : "checkbox", checked : date_out_attribute},
|
|
{name : "fill_last_attribute", type : "checkbox", checked : fill_last_attribute}
|
|
]
|
|
});
|
|
block.setAttribute("data-i", i);
|
|
|
|
}else{
|
|
|
|
const subform = container.querySelector("[data-i='" + i + "']");
|
|
|
|
subform && subform.remove();
|
|
|
|
};
|
|
|
|
});
|
|
|
|
};
|
|
|
|
const get_files_data = values => {
|
|
|
|
const data = [],
|
|
default_database = csv2sql.item_self.querySelector("[name=default_database]").value;
|
|
|
|
values.files.forEach((file, i) => {
|
|
if(!file)
|
|
return;
|
|
|
|
const subform = csv2sql.item_self.querySelector(".field[data-i18n=files_data] .content [data-i='" + i + "']"),
|
|
fill_last_attribute = subform.querySelector("[name=fill_last_attribute]").checked;
|
|
|
|
if(!subform)
|
|
return;
|
|
|
|
const table = [],
|
|
tuples = csv2sql.utf8_decode(csv2sql.base64_decode(file.data)).split(/[\r\n]+/),
|
|
attributes = {},
|
|
order = [];
|
|
let subdata = {};
|
|
|
|
tuples[0].replace(/(^|,)(\s*"([^"]+)"|([^,]+))?/gm, (...arguments) => {
|
|
|
|
const name = arguments[3] || arguments[4] || "";
|
|
|
|
order.push(name);
|
|
attributes[name] = {
|
|
null : false,
|
|
type : "unknown",
|
|
length : [Number.MAX_SAFE_INTEGER, 0]
|
|
};
|
|
|
|
});
|
|
|
|
tuples.slice(1).forEach((tuple, k) => {
|
|
if(!tuple)
|
|
return;
|
|
|
|
let j = 0;
|
|
|
|
table.push({});
|
|
|
|
tuple.replace(/(^|,)(\s*"([^"]+)"|([^,]+))?/gm, (...arguments) => {
|
|
|
|
let value = arguments[3] || arguments[4] || "",
|
|
name = order[j];
|
|
const length = value.length,
|
|
type = (
|
|
value == "NULL" ? "null" :
|
|
/^[0-9]+$/.test(value) && value.length < 13 ? "integer" :
|
|
/^[0-9]+$/.test(value) && value.length < 19 ? "bigint" :
|
|
/^(([0-9]+)?\.[0-9]+|[0-9]+\.)$/.test(value) && value.length < 18 ? "float" :
|
|
/^(true|false)$/i.test(value) ? "boolean" :
|
|
"varchar"
|
|
),
|
|
is_null = value == "NULL";
|
|
|
|
if(!name){
|
|
if(fill_last_attribute){
|
|
name = order[order.length - 1];
|
|
value = table[k][name] + "," + value;
|
|
}else{
|
|
order.push(name = "column_" + j);
|
|
attributes[name] = {
|
|
type : "unknown",
|
|
length : [Number.MAX_SAFE_INTEGER, 0]
|
|
};
|
|
// console.log([k, name, value]);
|
|
};
|
|
};
|
|
|
|
if(!is_null){
|
|
length < attributes[name].length[0] && (attributes[name].length[0] = length);
|
|
length > attributes[name].length[1] && (attributes[name].length[1] = length);
|
|
};
|
|
|
|
is_null && !attributes[name].null && (attributes[name].null = true);
|
|
|
|
switch(attributes[name].type){
|
|
case "unknown":
|
|
type != "null" && (attributes[name].type = type);
|
|
break;
|
|
case "integer":
|
|
["float", "varchar"].includes(type) && (attributes[name].type = type);
|
|
break;
|
|
case "float":
|
|
case "boolean":
|
|
["varchar"].includes(type) && (attributes[name].type = type);
|
|
break;
|
|
};
|
|
|
|
table[k][name] = value == "NULL" ? null : value;
|
|
|
|
j ++;
|
|
|
|
});
|
|
|
|
});
|
|
|
|
subdata = {
|
|
database : subform.querySelector("[name=database]").value || default_database,
|
|
table : subform.querySelector("[name=table]").value,
|
|
file_name : file.name,
|
|
data : table,
|
|
attributes : attributes,
|
|
order : order,
|
|
creator_database : values.files_data[i].creator_database,
|
|
uniques : (values.uniques + " " + values.files_data[i].uniques).trim()
|
|
};
|
|
subdata.uniques = subdata.uniques ? subdata.uniques.split(/(\s|[\r\n])+/) : [];
|
|
|
|
[
|
|
"creator_database", "remover_tables", "creator_tables",
|
|
"updater_tables", "filler_tables", "id_attribute",
|
|
"deleted_attribute", "date_in_attribute", "date_out_attribute"
|
|
].forEach(key => subdata[key] = values.files_data[i][key]);
|
|
|
|
data.push(subdata);
|
|
|
|
});
|
|
|
|
return data;
|
|
};
|
|
|
|
this.validate_string = (string, empty) => (
|
|
string === undefined ? 1 << 0 :
|
|
string === null ? 1 << 1 :
|
|
!csv2sql.is_string(string) ? 1 << 2 :
|
|
!empty && !string ? 1 << 3 :
|
|
0);
|
|
|
|
this.validate_string_messages = key => [
|
|
key + "_undefined",
|
|
key + "_null",
|
|
key + "_not_string",
|
|
key + "_empty"
|
|
];
|
|
|
|
this.validate_files = (files, empty) => (
|
|
files === undefined ? 1 << 0 :
|
|
files === null ? 1 << 1 :
|
|
!csv2sql.is_array(files) ? 1 << 2 :
|
|
!empty && !files.length ? 1 << 3 :
|
|
0);
|
|
|
|
this.validate_files_messages = key => [
|
|
key + "_undefined",
|
|
key + "_null",
|
|
key + "_not_files",
|
|
key + "_empty"
|
|
];
|
|
|
|
this.validate_bool = value => (
|
|
value === undefined ? 1 << 0 :
|
|
value === null ? 1 << 1 :
|
|
!csv2sql.is_bool(value) ? 1 << 2 :
|
|
0);
|
|
|
|
this.validate_bool_messages = key => [
|
|
key + "_undefined",
|
|
key + "_null",
|
|
key + "_not_bool"
|
|
];
|
|
|
|
const validate = (values, conditions) => {
|
|
|
|
const messages = ["exception"];
|
|
let error = "",
|
|
i = 1,
|
|
ok;
|
|
|
|
try{
|
|
|
|
conditions.forEach(([key, type, empty, extra]) => {
|
|
|
|
const new_messages = self["validate_" + type + "_messages"](key),
|
|
new_error = self["validate_" + type](values[key], empty);
|
|
|
|
error = csv2sql.errors.set(error, new_error, i, 0);
|
|
[].push.apply(messages, new_messages);
|
|
|
|
i += new_messages.length;
|
|
|
|
!new_error && extra && extra.forEach(([invalid, message]) => {
|
|
error = csv2sql.errors.set(error, invalid ? 1 << 0 : 0, i ++, 0);
|
|
messages.push(message);
|
|
});
|
|
|
|
});
|
|
|
|
}catch(exception){
|
|
csv2sql.settings_get("process_exception_print") && console.error(exception);
|
|
ok = false;
|
|
error = csv2sql.errors.set(error, 1 << 0, 0, 0);
|
|
};
|
|
|
|
return [ok, error, messages];
|
|
};
|
|
|
|
const process = item => {
|
|
|
|
const [values, form] = csv2sql.comp.forms.get_values(item);
|
|
let [ok, error, messages] = validate(values, [
|
|
["default_database", "string", true],
|
|
["type", "string", false, [
|
|
[!["mysql", "sqlserver"].includes(values.type), "type_unkown"]
|
|
]],
|
|
["default_creator_database", "bool"],
|
|
["default_remover_tables", "bool"],
|
|
["default_creator_tables", "bool"],
|
|
["default_updater_tables", "bool"],
|
|
["default_filler_tables", "bool"],
|
|
["default_uniques", "string", true],
|
|
["default_id_attribute", "bool"],
|
|
["default_deleted_attribute", "bool"],
|
|
["default_date_in_attribute", "bool"],
|
|
["default_date_out_attribute", "bool"],
|
|
["default_fill_last_attribute", "bool"],
|
|
["files", "files", false],
|
|
["file_name", "string", true]
|
|
]);
|
|
|
|
if(ok = csv2sql.errors.validate(form, error, "main_menu_error", messages)){
|
|
|
|
values.files_data = {};
|
|
|
|
form.querySelectorAll(".field[data-i18n=files_data] .structure").forEach(structure => {
|
|
|
|
const i_key = structure.parentNode.parentNode.getAttribute("data-i"),
|
|
[subvalues, subform] = csv2sql.comp.forms.get_values(structure, true);
|
|
let [subok, suberror, submessages] = validate(values, [
|
|
["database", "string", true, [
|
|
[!values.default_database && !subvalues.database, "database_name_required"]
|
|
]],
|
|
["table", "string", false],
|
|
["creator_database", "bool"],
|
|
["remover_tables", "bool"],
|
|
["creator_tables", "bool"],
|
|
["updater_tables", "bool"],
|
|
["filler_tables", "bool"],
|
|
["uniques", "string", true],
|
|
["id_attribute", "bool"],
|
|
["deleted_attribute", "bool"],
|
|
["date_in_attribute", "bool"],
|
|
["date_out_attribute", "bool"],
|
|
["fill_last_attribute", "bool"]
|
|
]);
|
|
|
|
subok = csv2sql.errors.validate(structure.parentNode, suberror, "file_menu_error", submessages, null, {file_name : values.files[i_key].name});
|
|
|
|
!subok && (ok = false);
|
|
|
|
values.files_data[i_key] = subvalues;
|
|
|
|
});
|
|
|
|
};
|
|
|
|
return [ok, values, form];
|
|
};
|
|
|
|
this.download_files = (item, event) => {
|
|
|
|
const [ok, values, form] = process(item);
|
|
|
|
if(ok){
|
|
|
|
const zip = new JSZip(),
|
|
engine = self[values.type == "mysql" ? "mysql" : "sql_server"];
|
|
|
|
get_files_data(values).forEach(file => {
|
|
|
|
zip.file(file.file_name.replace(/\.csv$/i, "." + (values.type == "mysql" ? "my" : "transact") + ".sql"), engine.create_file({
|
|
database : file.database,
|
|
creator_database : file.creator_database ? engine.database_creator(file.database) : null,
|
|
remover_tables : file.remover_tables ? engine.table_remover(file) : null,
|
|
creator_tables : file.creator_tables ? engine.table_creator(file) : null,
|
|
updater_tables : file.updater_tables ? engine.table_updater(file) : null,
|
|
filler_tables : file.filler_tables ? engine.create_filler(file) : null,
|
|
key : self.to_snake(file.table)
|
|
}));
|
|
|
|
});
|
|
|
|
console.log([values, get_files_data(values)]);
|
|
|
|
// self.jszip_download(zip, values.file_name + ".zip");
|
|
|
|
};
|
|
|
|
};
|
|
|
|
this.download_one_file = (item, event) => {
|
|
|
|
const [ok, values, form] = process(item);
|
|
|
|
if(ok){
|
|
|
|
const engine = self[values.type == "mysql" ? "mysql" : "sql_server"];
|
|
let creator = "",
|
|
remover = "",
|
|
updater = "",
|
|
filler = "";
|
|
|
|
get_files_data(values).forEach(file => {
|
|
file.remover_tables && (remover += (remover ? "\n" : "") + engine.table_remover(file));
|
|
file.creator_tables && (creator += (creator ? "\n\n" : "") + engine.table_creator(file));
|
|
file.updater_tables && (updater += (updater ? "\n\n" : "") + engine.table_updater(file));
|
|
file.filler_tables && (filler += (filler ? "\n\n" : "") + engine.create_filler(file));
|
|
});
|
|
|
|
self.download(engine.create_file({
|
|
database : values.default_database,
|
|
creator_database : values.default_creator_database ? engine.database_creator(values.default_database) : null,
|
|
remover_tables : remover || null,
|
|
creator_tables : creator || null,
|
|
updater_tables : updater || null,
|
|
filler_tables : filler || null
|
|
}), "text/plain", values.file_name + "." + (values.type == "mysql" ? "my" : "transact") + ".sql");
|
|
|
|
};
|
|
|
|
};
|
|
|
|
this.to_snake = string => string.replace(/[^a-zA-Z0-9]+([a-zA-Z0-9])|([A-Z0-9]+)/g, (...arguments) => (
|
|
arguments[1] ? "_" + arguments[1] :
|
|
"_" + arguments[2]
|
|
)).toLowerCase().replace(/^_+|_+$/g, "");
|
|
|
|
construct();
|
|
|
|
}; |