236 lines
9.0 KiB
JavaScript
236 lines
9.0 KiB
JavaScript
|
CSV2SQL.MySQL = function(csv2sql, input){
|
||
|
|
|
||
|
|
const self = this,
|
||
|
|
reserved_words = [];
|
||
|
|
let started = false,
|
||
|
|
tab;
|
||
|
|
|
||
|
|
const construct = () => {};
|
||
|
|
|
||
|
|
this.start = callback => {
|
||
|
|
|
||
|
|
const end = status => csv2sql.is_function(callback) && callback(status);
|
||
|
|
|
||
|
|
if(started){
|
||
|
|
end(false);
|
||
|
|
return false;
|
||
|
|
};
|
||
|
|
started = true;
|
||
|
|
|
||
|
|
tab = csv2sql.settings_get(["mysql_tabulation", "tabulation"]);
|
||
|
|
|
||
|
|
self.add_reserved_words(csv2sql.settings_get("default_mysql_reserved_words"), () => {
|
||
|
|
self.add_reserved_words(csv2sql.settings_get("mysql_reserved_words"), () => {
|
||
|
|
end(true);
|
||
|
|
});
|
||
|
|
});
|
||
|
|
|
||
|
|
return true;
|
||
|
|
};
|
||
|
|
|
||
|
|
this.add_reserved_words = (inputs, callback) => {
|
||
|
|
|
||
|
|
let loaded = 0;
|
||
|
|
const total = (csv2sql.is_array(inputs) ? inputs : csv2sql.is_string(inputs) ? inputs = [inputs] : inputs = []).length,
|
||
|
|
end = () => ++ loaded == total && csv2sql.is_function(callback) && callback();
|
||
|
|
|
||
|
|
if(total){
|
||
|
|
for(let i = 0; i < total; i ++){
|
||
|
|
if(csv2sql.is_array(inputs[i]))
|
||
|
|
self.add_reserved_words(inputs[i], end);
|
||
|
|
else if(csv2sql.is_string(inputs[i]) && inputs[i]){
|
||
|
|
if(['[', '{'].includes(inputs[i].trim()[0])){
|
||
|
|
try{
|
||
|
|
self.add_reserved_words(csv2sql.json_decode(inputs[i]), end);
|
||
|
|
}catch(exception){
|
||
|
|
end();
|
||
|
|
};
|
||
|
|
continue;
|
||
|
|
};
|
||
|
|
if(/^[a-z0-9_]+$/.test(inputs[i])){
|
||
|
|
!reserved_words.includes(inputs[i]) && reserved_words.push(inputs[i]);
|
||
|
|
end();
|
||
|
|
continue;
|
||
|
|
};
|
||
|
|
csv2sql.ajax.get(inputs[i], response => {
|
||
|
|
try{
|
||
|
|
self.add_reserved_words(csv2sql.json_decode(response), end);
|
||
|
|
}catch(exception){
|
||
|
|
end();
|
||
|
|
};
|
||
|
|
});
|
||
|
|
}else
|
||
|
|
end();
|
||
|
|
};
|
||
|
|
}else{
|
||
|
|
loaded --;
|
||
|
|
end();
|
||
|
|
};
|
||
|
|
|
||
|
|
};
|
||
|
|
|
||
|
|
const word = this.word = name => reserved_words.includes(name) ? "`" + name + "`" : name;
|
||
|
|
|
||
|
|
this.database_creator = name => (
|
||
|
|
"create database if not exists " + word(name) + " character set utf8mb4 collate utf8mb4_general_ci;\n" +
|
||
|
|
"use " + word(name) + ";"
|
||
|
|
);
|
||
|
|
|
||
|
|
this.table_remover = data => tab + tab + "drop table if exists " + word(data.table) + ";";
|
||
|
|
|
||
|
|
const attributes = attribute => (
|
||
|
|
attribute.type == "varchar" ? attribute.length[0] == attribute.length[1] ? "char" : "varchar" :
|
||
|
|
attribute.type
|
||
|
|
) + (
|
||
|
|
attribute.type == "varchar" ? "(" + attribute.length[1] + ")" :
|
||
|
|
""
|
||
|
|
) + (attribute.null ? "" : " not null");
|
||
|
|
|
||
|
|
this.table_creator = data => {
|
||
|
|
|
||
|
|
let sql = "";
|
||
|
|
|
||
|
|
data.id_attribute && (sql += "\n" + tab + tab + tab + "id integer not null auto_increment");
|
||
|
|
|
||
|
|
data.order.forEach(key => {
|
||
|
|
|
||
|
|
const attribute = data.attributes[key];
|
||
|
|
|
||
|
|
sql += (sql ? "," : "") + "\n" + tab + tab + tab + word(key) + " " + attributes(attribute);
|
||
|
|
|
||
|
|
});
|
||
|
|
|
||
|
|
data.deleted_attribute && (sql += ",\n" + tab + tab + tab + "deleted datetime");
|
||
|
|
data.date_in_attribute && (sql += ",\n" + tab + tab + tab + "date_in datetime not null default now()");
|
||
|
|
data.date_out_attribute && (sql += ",\n" + tab + tab + tab + "date_out datetime");
|
||
|
|
|
||
|
|
data.id_attribute && (sql += ",\n" + tab + tab + tab + "constraint " + csv2sql.to_snake(data.table) + "_id primary key(id)");
|
||
|
|
|
||
|
|
return tab + tab + "create table if not exists " + word(data.table) + "(" + sql + "\n" + tab + tab + ");"
|
||
|
|
};
|
||
|
|
|
||
|
|
this.table_updater = data => {
|
||
|
|
|
||
|
|
let sql = "";
|
||
|
|
|
||
|
|
data.order.forEach(key => {
|
||
|
|
|
||
|
|
const attribute = data.attributes[key];
|
||
|
|
|
||
|
|
sql += ((sql ? "\n\n" : "") +
|
||
|
|
tab + tab + "if (select 1 from information_schema.columns where table_schema = '" + data.database + "' && table_name = '" + data.table + "' && column_name = '" + key + "' limit 1) is null then \n" +
|
||
|
|
tab + tab + tab + "alter table " + word(data.table) + " add column " + word(key) + " " + attributes(attribute) + ";\n" +
|
||
|
|
tab + tab + "end if;"
|
||
|
|
);
|
||
|
|
|
||
|
|
});
|
||
|
|
|
||
|
|
data.deleted_attribute && (sql += ("\n\n" +
|
||
|
|
tab + tab + "if (select 1 from information_schema.columns where table_schema = '" + data.database + "' && table_name = '" + data.table + "' && column_name = 'deleted' limit 1) is null then \n" +
|
||
|
|
tab + tab + tab + "alter table " + word(data.table) + " add column deleted datetime;\n" +
|
||
|
|
tab + tab + "end if;"
|
||
|
|
));
|
||
|
|
data.date_in_attribute && (sql += ("\n\n" +
|
||
|
|
tab + tab + "if (select 1 from information_schema.columns where table_schema = '" + data.database + "' && table_name = '" + data.table + "' && column_name = 'date_in' limit 1) is null then \n" +
|
||
|
|
tab + tab + tab + "alter table " + word(data.table) + " add column date_in datetime not null default now();\n" +
|
||
|
|
tab + tab + "end if;"
|
||
|
|
));
|
||
|
|
data.date_out_attribute && (sql += ("\n\n" +
|
||
|
|
tab + tab + "if (select 1 from information_schema.columns where table_schema = '" + data.database + "' && table_name = '" + data.table + "' && column_name = 'date_out' limit 1) is null then \n" +
|
||
|
|
tab + tab + tab + "alter table " + word(data.table) + " add column date_out datetime;\n" +
|
||
|
|
tab + tab + "end if;"
|
||
|
|
));
|
||
|
|
|
||
|
|
return sql;
|
||
|
|
};
|
||
|
|
|
||
|
|
this.create_filler = data => {
|
||
|
|
|
||
|
|
const header = "insert into " + word(data.table) + "(" + data.order.map(key => word(key)).join(", ") + ") values";
|
||
|
|
let sql = "";
|
||
|
|
|
||
|
|
if(data.uniques.length){}else{
|
||
|
|
|
||
|
|
data.data.forEach(tuple => {
|
||
|
|
|
||
|
|
let tuple_sql = "";
|
||
|
|
|
||
|
|
data.order.forEach(key => tuple_sql += (tuple_sql ? ", " : "") + (
|
||
|
|
csv2sql.null_or_undefined(tuple[key]) ? "null" :
|
||
|
|
["integer", "bigint", "float"].includes(data.attributes[key].type) ? tuple[key] :
|
||
|
|
["bool"].includes(data.attributes[key].type) ? tuple[key] ? "true" : "false" :
|
||
|
|
"'" + tuple[key].replace(/([\\''])/g, "\\$1") + "'"
|
||
|
|
));
|
||
|
|
|
||
|
|
sql += (sql ? "," : "") + "\n" + tab + tab + tab + "(" + tuple_sql + ")";
|
||
|
|
|
||
|
|
});
|
||
|
|
|
||
|
|
sql = tab + tab + header + sql + ";";
|
||
|
|
|
||
|
|
};
|
||
|
|
|
||
|
|
return sql;
|
||
|
|
};
|
||
|
|
|
||
|
|
this.create_file = variables => {
|
||
|
|
|
||
|
|
let sql = "",
|
||
|
|
callers = "";
|
||
|
|
const key = variables.key ? variables.key + "_" : "";
|
||
|
|
|
||
|
|
sql += (
|
||
|
|
(variables.creator_database ? variables.creator_database : "use " + word(variables.database) + ";") + "\n\n" +
|
||
|
|
"delimiter ;^"
|
||
|
|
);
|
||
|
|
|
||
|
|
if(variables.remover_tables){
|
||
|
|
sql += ("\n\n" +
|
||
|
|
tab + "drop procedure if exists csv2sql_" + key + "tables_remove;^\n" +
|
||
|
|
tab + "create procedure csv2sql_" + key + "tables_remove() begin \n\n" +
|
||
|
|
variables.remover_tables + "\n\n" +
|
||
|
|
tab + "end;^"
|
||
|
|
);
|
||
|
|
callers += "\n" + tab + "call csv2sql_" + key + "tables_remove();^";
|
||
|
|
};
|
||
|
|
|
||
|
|
if(variables.creator_tables){
|
||
|
|
sql += ("\n\n" +
|
||
|
|
tab + "drop procedure if exists csv2sql_" + key + "tables_create;^\n" +
|
||
|
|
tab + "create procedure csv2sql_" + key + "tables_create() begin \n\n" +
|
||
|
|
variables.creator_tables + "\n\n" +
|
||
|
|
tab + "end;^"
|
||
|
|
);
|
||
|
|
callers += "\n" + tab + "call csv2sql_" + key + "tables_create();^";
|
||
|
|
};
|
||
|
|
|
||
|
|
if(variables.updater_tables){
|
||
|
|
sql += ("\n\n" +
|
||
|
|
tab + "drop procedure if exists csv2sql_" + key + "tables_update;^\n" +
|
||
|
|
tab + "create procedure csv2sql_" + key + "tables_update() begin \n\n" +
|
||
|
|
variables.updater_tables + "\n\n" +
|
||
|
|
tab + "end;^"
|
||
|
|
);
|
||
|
|
callers += "\n" + tab + "call csv2sql_" + key + "tables_update();^";
|
||
|
|
};
|
||
|
|
|
||
|
|
if(variables.filler_tables){
|
||
|
|
sql += ("\n\n" +
|
||
|
|
tab + "drop procedure if exists csv2sql_" + key + "tables_fill;^\n" +
|
||
|
|
tab + "create procedure csv2sql_" + key + "tables_fill() begin \n\n" +
|
||
|
|
variables.filler_tables + "\n\n" +
|
||
|
|
tab + "end;^"
|
||
|
|
);
|
||
|
|
callers += "\n" + tab + "call csv2sql_" + key + "tables_fill();^";
|
||
|
|
};
|
||
|
|
|
||
|
|
callers && (sql += "\n" + callers);
|
||
|
|
|
||
|
|
sql += "\n\ndelimiter ;";
|
||
|
|
|
||
|
|
return sql;
|
||
|
|
};
|
||
|
|
|
||
|
|
construct();
|
||
|
|
|
||
|
|
};
|