245 lines
9.6 KiB
JavaScript
Executable File
245 lines
9.6 KiB
JavaScript
Executable File
CSV2SQL.SQLServer = 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_sql_server_reserved_words"), () => {
|
|
self.add_reserved_words(csv2sql.settings_get("sql_server_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 => (
|
|
"if (select top 1 1 from sys.databases where name = '" + name + "') is null create database " + word(name) + "\n" +
|
|
"go\n" +
|
|
"use " + word(name)
|
|
);
|
|
|
|
this.table_remover = data => tab + "if object_id(N'dbo." + data.table + "', N'U') is not null drop table dbo." + word(data.table);
|
|
|
|
const attributes = attribute => (
|
|
attribute.type == "varchar" ? attribute.length[0] == attribute.length[1] ? "char" : "varchar" :
|
|
attribute.type == "bool" ? "bit" :
|
|
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 + "id integer not null identity(1, 1)");
|
|
|
|
data.order.forEach(key => {
|
|
|
|
const attribute = data.attributes[key];
|
|
|
|
sql += (sql ? "," : "") + "\n" + tab + tab + word(key) + " " + attributes(attribute);
|
|
|
|
});
|
|
|
|
data.deleted_attribute && (sql += ",\n" + tab + tab + "deleted datetime");
|
|
data.date_in_attribute && (sql += ",\n" + tab + tab + "date_in datetime not null constraint " + csv2sql.to_snake(data.table) + "_date_in default getdate()");
|
|
data.date_out_attribute && (sql += ",\n" + tab + tab + "date_out datetime");
|
|
|
|
data.id_attribute && (sql += ",\n" + tab + tab + "constraint " + csv2sql.to_snake(data.table) + "_id primary key(id)");
|
|
|
|
return tab + "if object_id(N'dbo." + data.table + "', N'U') is null create table dbo." + word(data.table) + "(" + sql + "\n" + tab + ")"
|
|
};
|
|
|
|
this.table_updater = data => {
|
|
|
|
let sql = "";
|
|
|
|
data.order.forEach(key => {
|
|
|
|
const attribute = data.attributes[key];
|
|
|
|
sql += ((sql ? "\n\n" : "") +
|
|
tab + "if (select top 1 1 from information_schema.columns where table_catalog = '" + data.database + "' and table_name = '" + data.table + "' and column_name = '" + key + "') is null \n" +
|
|
tab + tab + "alter table " + word(data.table) + " add " + word(key) + " " + attributes(attribute)
|
|
);
|
|
|
|
});
|
|
|
|
data.deleted_attribute && (sql += ("\n\n" +
|
|
tab + "if (select top 1 1 from information_schema.columns where table_catalog = '" + data.database + "' and table_name = '" + data.table + "' and column_name = 'deleted') is null \n" +
|
|
tab + tab + "alter table " + word(data.table) + " add deleted datetime"
|
|
));
|
|
data.date_in_attribute && (sql += ("\n\n" +
|
|
tab + "if (select top 1 1 from information_schema.columns where table_catalog = '" + data.database + "' and table_name = '" + data.table + "' and column_name = 'date_in') is null \n" +
|
|
tab + tab + "alter table " + word(data.table) + " add date_in datetime not null constraint " + csv2sql.to_snake(data.table) + "_date_in default getdate()"
|
|
));
|
|
data.date_out_attribute && (sql += ("\n\n" +
|
|
tab + "if (select top 1 1 from information_schema.columns where table_catalog = '" + data.database + "' and table_name = '" + data.table + "' and column_name = 'date_out') is null \n" +
|
|
tab + tab + "alter table " + word(data.table) + " add date_out datetime"
|
|
));
|
|
|
|
return sql;
|
|
};
|
|
|
|
this.create_filler = data => {
|
|
|
|
const header = "insert into dbo." + word(data.table) + "(" + data.order.map(key => word(key)).join(", ") + ") values";
|
|
let sql = "";
|
|
|
|
if(data.uniques.length){}else{
|
|
|
|
let subsql = "";
|
|
const division = csv2sql.settings_get("sql_server_insert_division");
|
|
|
|
data.data.forEach((tuple, i) => {
|
|
|
|
let tuple_sql = "";
|
|
|
|
if(!(i % division)){
|
|
subsql && (sql += (sql ? "\n" : "") + tab + header + subsql);
|
|
subsql = "";
|
|
};
|
|
|
|
data.order.forEach(key => tuple_sql += (tuple_sql ? ", " : "") + (
|
|
tuple[key] === null ? "null" :
|
|
["integer", "bigint", "float"].includes(data.attributes[key].type) ? tuple[key] :
|
|
["bool"].includes(data.attributes[key].type) ? tuple[key] ? "1" : "0" :
|
|
"'" + tuple[key].replace(/'/, "''") + "'"
|
|
));
|
|
|
|
subsql += (subsql ? "," : "") + "\n" + tab + tab + "(" + tuple_sql + ")";
|
|
|
|
});
|
|
|
|
subsql && (sql += (sql ? "\n" : "") + tab + header + subsql);
|
|
|
|
};
|
|
|
|
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);
|
|
|
|
if(variables.remover_tables){
|
|
sql += ("\n\n" +
|
|
"if object_id(N'dbo.csv2sql_" + key + "tables_remove', N'P') is not null drop procedure dbo.csv2sql_" + key + "tables_remove\n" +
|
|
"go\n" +
|
|
"create procedure dbo.csv2sql_" + key + "tables_remove as begin \n\n" +
|
|
variables.remover_tables + "\n\n" +
|
|
"end\n" +
|
|
"go"
|
|
);
|
|
callers += "\nexecute dbo.csv2sql_" + key + "tables_remove";
|
|
};
|
|
|
|
if(variables.creator_tables){
|
|
sql += ("\n\n" +
|
|
"if object_id(N'dbo.csv2sql_" + key + "tables_create', N'P') is not null drop procedure dbo.csv2sql_" + key + "tables_create\n" +
|
|
"go\n" +
|
|
"create procedure dbo.csv2sql_" + key + "tables_create as begin \n\n" +
|
|
variables.creator_tables + "\n\n" +
|
|
"end\n" +
|
|
"go"
|
|
);
|
|
callers += "\nexecute dbo.csv2sql_" + key + "tables_create";
|
|
};
|
|
|
|
if(variables.updater_tables){
|
|
sql += ("\n\n" +
|
|
"if object_id(N'dbo.csv2sql_" + key + "tables_update', N'P') is not null drop procedure dbo.csv2sql_" + key + "tables_update\n" +
|
|
"go\n" +
|
|
"create procedure dbo.csv2sql_" + key + "tables_update as begin \n\n" +
|
|
variables.updater_tables + "\n\n" +
|
|
"end\n" +
|
|
"go"
|
|
);
|
|
callers += "\nexecute dbo.csv2sql_" + key + "tables_update";
|
|
};
|
|
|
|
if(variables.filler_tables){
|
|
sql += ("\n\n" +
|
|
"if object_id(N'dbo.csv2sql_" + key + "tables_fill', N'P') is not null drop procedure dbo.csv2sql_" + key + "tables_fill\n" +
|
|
"go\n" +
|
|
"create procedure dbo.csv2sql_" + key + "tables_fill as begin \n\n" +
|
|
variables.filler_tables + "\n\n" +
|
|
"end\n" +
|
|
"go"
|
|
);
|
|
callers += "\nexecute dbo.csv2sql_" + key + "tables_fill";
|
|
};
|
|
|
|
callers && (sql += "\n" + callers);
|
|
|
|
return sql;
|
|
};
|
|
|
|
construct();
|
|
|
|
}; |