private string GenerateOneQueryFile(DataTableObject item, string attribute) { //===========get multiple=========== string vars = ""; foreach (var v in item.RowList) { vars += "@" + v.ColumnName + ","; } vars = vars.Remove(vars.Length - 1); string str = "function Get" + item.TableName + "By" + attribute + "($" + attribute + ")\n{\n"; str += "\t$query = \"CALL sp_" + item.TableName + "_BY_" + attribute + "(\" . $" + attribute + " . \")\";\n"; str += "\tif ($result = connect()->query($query)) {\n"; str += "\t $emparray = array();\n"; str += "\t while ($row = mysqli_fetch_assoc($result))\n\t{\n"; str += "\t $emparray[] = $row;\n"; str += "\t}\n"; str += "\t$result->free();\n"; str += "\tconnect()->close();\n"; str += "\treturn json_encode($emparray);}\n}"; return(str); }
private string GenerateInsert(DataTableObject item) { string variables = "", pass = "", passVariables = ""; foreach (var col in item.RowList) { if (col.DataType != null && col.ColumnName != "AddDate" && item.PrimaryKey != col.ColumnName) { variables += " '" + col.ColumnName + "' : " + "$('#" + col.ColumnName + "').val() ,"; pass += col.ColumnName.ToLower() + " ,"; passVariables += " '" + col.ColumnName + "' : " + col.ColumnName.ToLower() + " ,"; } } if (!String.IsNullOrEmpty(variables)) { variables = variables + "***"; variables = variables.Replace(",***", ""); pass = pass + "***"; pass = pass.Replace(",***", ""); passVariables = passVariables + "***"; passVariables = passVariables.Replace(",***", ""); } string str = "function create" + item.TableName.ToLower() + "()\n{"; str += "\n\tvar d = { " + variables + " };"; str += "\n\tvar data = JSON.stringify(d);"; str += "\n\t$.ajax({"; str += "\n\ttype: \"GET\","; str += "\n\turl: '" + site_root + "Create" + item.TableName + ".php',"; str += "\n\tdata: { 'data':data},"; str += "\n\tcontentType: \"application/json; charset=utf-8\","; str += "\n\tdataType: \"json\","; str += "\n\tsuccess: successFunc,"; str += "\n\terror: errorFunc"; str += "\n\t});\n}\n\n"; str += "function create" + item.TableName.ToLower() + "v(" + pass + ")\n{"; str += "\n\tvar d = { " + passVariables + " };"; str += "\n\tvar data = JSON.stringify(d);"; str += "\n\t$.ajax({"; str += "\n\ttype: \"GET\","; str += "\n\turl: '" + site_root + "Create" + item.TableName + ".php',"; str += "\n\tdata: { 'data':data},"; str += "\n\tcontentType: \"application/json; charset=utf-8\","; str += "\n\tdataType: \"json\","; str += "\n\tsuccess: successFunc,"; str += "\n\terror: errorFunc"; str += "\n\t});\n}"; if (item.TableName == "Identity") { str = GenerateIdentity(item); } return(str); }
/// <summary> /// Set column value parameter /// </summary> /// <param name="dataTable"></param> /// <param name="prefix"></param> /// <returns></returns> private string Set(DataTableObject dataTable, string prefix) { string str = ""; foreach (var row in dataTable.RowList) { str += row.ColumnName + " = " + prefix + row.ColumnName + ",\n"; } str = str.Substring(0, str.Length - 2); return(str); }
private string GenerateFetchQuery(DataTableObject item) { //Arrays Results string str = "function GetAll" + item.TableName + "()\n{"; str += "\n\t$query='CALL sp_" + item.TableName + "_Select();';\n"; str += "\t if ($result = connect()->query($query)) {\n"; str += "\t $emparray = array();\n"; str += "\t while ($row = mysqli_fetch_assoc($result))\n{\n"; str += "\t $emparray[] = $row;\n"; str += "\t }\n"; str += "\t $result->free();\n"; str += "\t connect()->close();\n"; str += "\t return json_encode($emparray);\n}\n}"; return(str); }
private string GenerateGetAll(DataTableObject item) { //Arrays Results string str = "<?php \n\n include_once 'global.php';\n\n "; str += "\n\t$query='CALL sp_" + item.TableName + "_Select();';\n"; str += "\t if ($result = connect()->query($query)) {\n"; str += "\t $emparray = array();\n"; str += "\t while ($row = mysqli_fetch_assoc($result))\n{\n"; str += "\t $emparray[] = $row;\n"; str += "\t }\n"; str += "\t $result->free();\n"; str += "\t connect()->close();\n"; str += "\t echo json_encode($emparray);\n}\n\n?>"; return(str); }
/// <summary> /// Get one result stored procedure /// </summary> /// <param name="dataTable"></param> /// <param name="attribute"></param> /// <param name="datatype"></param> /// <returns></returns> private string GenerateFetchOne(DataTableObject dataTable, string attribute, string datatype) { string str = ""; str += "DELIMITER $$\n"; str += "DROP PROCEDURE IF EXISTS sp_" + dataTable.TableName + "_BY_" + attribute + "$$\n"; str += "CREATE PROCEDURE sp_" + dataTable.TableName + "_BY_" + attribute + "(\n"; str += "IN p" + attribute + " " + datatype + "\n)\n"; str += "LANGUAGE SQL\n"; str += "DETERMINISTIC\n"; str += "SQL SECURITY DEFINER\n"; str += "COMMENT 'A procedure to select all'\n"; str += "BEGIN\n"; str += "SELECT * FROM " + dataTable.TableName + " WHERE " + attribute + " = p" + attribute + ";\n"; str += "END$$\n\n"; return(str); }
/// <summary> /// Select all the content from a table /// </summary> /// <param name="dataTable"></param> /// <returns></returns> private string GenerateFetchQuery(DataTableObject dataTable) { string str = ""; str += "DELIMITER $$\n"; str += "DROP PROCEDURE IF EXISTS sp_" + dataTable.TableName + "_Select$$\n"; str += "CREATE PROCEDURE sp_" + dataTable.TableName + "_Select()\n"; str += "LANGUAGE SQL\n"; str += "DETERMINISTIC\n"; str += "SQL SECURITY DEFINER\n"; str += "COMMENT 'A procedure to select all'\n"; str += "BEGIN\n"; str += "SELECT * FROM " + dataTable.TableName + ";\n"; str += "END$$\n\n"; return(str); }
/// <summary> /// Generate update table by primary stored procedure /// </summary> /// <param name="dataTable"></param> /// <returns></returns> private string GenerateUpdate(DataTableObject dataTable) { string str = "DELIMITER $$\n"; str += "DROP PROCEDURE IF EXISTS sp_Update_" + dataTable.TableName + "$$\n"; str += "CREATE PROCEDURE sp_Update_" + dataTable.TableName + "("; str += DeclareNoAuto(dataTable); str += ")\n"; str += "BEGIN\n"; str += "UPDATE " + dataTable.TableName + "\nSET\n"; str += Set(dataTable, "p"); str += "\nWHERE " + dataTable.PrimaryKey + " = p" + dataTable.PrimaryKey; str += ";\nEND$$"; return(str); }
/// <summary> /// Resolve default values: Some values can not be updated, such as record create date /// </summary> /// <param name="dataTable"></param> /// <param name="prefix"></param> /// <returns></returns> private string ColumnsNoAuto(DataTableObject dataTable, string prefix) { string str = ""; foreach (var row in dataTable.RowList) { var isForeign = row.ColumnName.StartsWith("FK_", StringComparison.InvariantCulture); var isPrimary = row.ColumnName.EndsWith("_PK", StringComparison.InvariantCulture); var datebool = row.ColumnName.ToLower().Contains("adddate"); if (!isPrimary && !isForeign && !datebool && row.DataType != null && row.DataType.ToString() != "timestamp" && dataTable.PrimaryKey != row.ColumnName) { str += prefix + "" + row.ColumnName + ",\n"; } } if (!String.IsNullOrEmpty(str)) { str = str.Substring(0, str.Length - 2); } return(str); }
/// <summary> /// Generate insert into a table stored procedure /// </summary> /// <param name="dataTable"></param> /// <returns></returns> private string GenerateInsert(DataTableObject dataTable) { string str = ""; str += "DELIMITER $$\n"; str += "DROP PROCEDURE IF EXISTS sp_Insert_" + dataTable.TableName + "$$\n"; str += "CREATE PROCEDURE sp_Insert_" + dataTable.TableName + "(\n"; str += DeclareNoAuto(dataTable); str += ")\n"; str += "BEGIN\n"; str += "INSERT INTO " + dataTable.TableName + "(\n"; str += ColumnsNoAuto(dataTable, ""); str += ")\n"; str += "VALUES(\n"; str += ColumnsNoAuto(dataTable, "p"); str += ");\nSELECT LAST_INSERT_ID();\n"; str += "END$$"; return(str); }
private string GenerateUpdate(DataTableObject item) { string variables = "", pass = ""; foreach (var col in item.RowList) { if (col.DataType != null && col.ColumnName != "AddDate" && item.PrimaryKey != col.ColumnName) { if (col.DataType.ToString() == "int" || col.DataType.ToString() == "decimal") { variables += "\t$" + col.ColumnName + " = " + "$data->" + col.ColumnName + ";\n"; pass += "\t$" + col.ColumnName + " . \",\" . "; } else if ((col.DataType.ToString() == "varchar" || col.DataType.ToString() == "date") || col.DataType.ToString() == "datetime") { variables += "\t$" + col.ColumnName + " = " + "$data->" + col.ColumnName + ";\n"; pass += "\t\"'\" . $" + col.ColumnName + " . \"'\" . \",\" . "; } } } if (!String.IsNullOrEmpty(pass)) { pass = pass + "***"; pass = pass.Replace(" . \",\" . ***", ""); } string str = "<?php\n\n"; str += "include_once 'global.php';\n"; str += "\n\t $data = $_GET['data'];\n"; str += "\n" + variables; str += "\n\t $query=\"CALL sp_Update_" + item.TableName + "(\" . " + pass + "\")\";\n"; str += "\t if (mysqli_query(connect(), $query)){\n"; str += "\t $last_id = mysqli_insert_id(connect());\n"; str += "\t echo $last_id;\n" + "\tmysqli_close(connect());}\n\n?>"; return(str); }
/// <summary> /// Generate delete record by primary id stored procedure /// </summary> /// <param name="dataTable"></param> /// <returns></returns> private string GenerateDelete(DataTableObject dataTable) { string declare = "", set = ""; declare += "IN pRecordStatus int,\n"; set = "RecordStatus = pRecordStatus\n"; string str = "DELIMITER $$\n"; str += "DROP PROCEDURE IF EXISTS sp_Delete_" + dataTable.TableName + "$$\n"; str += "CREATE PROCEDURE sp_Delete_" + dataTable.TableName + "(\n"; str += "IN pRecordStatus int"; str += ")\n"; str += "BEGIN\n"; str += "UPDATE " + dataTable.TableName + "\nSET\n"; str += set; str += "\nWHERE " + dataTable.PrimaryKey + " = p" + dataTable.PrimaryKey + ";"; str += "\nEND$$"; return(str); }
//private string GenerateGetAll(DataTableObject item) //{ // string str = ""; // //"function getall" + item.TableName.ToLower() + "()\n{"; // //str += "\n\t$.ajax({"; // //str += "\n\ttype: \"GET\","; // //str += "\n\turl: '" + site_root + "GetAll" + item.TableName + ".php',"; // //str += "\n\tcontentType: \"application/json; charset=utf-8\","; // //str += "\n\tdataType: \"json\","; // //str += "\n\tasync: true,"; // //str += "\n\tcomplete: completed,"; // //str += "\n\tsuccess: successFunc,"; // //str += "\n\terror: errorFunc"; // //str += "\n\t});\n}\n\n"; // str += "\n\tasync function getall" + item.TableName.ToLower() + "()\n{"; // str += "\n\tconst result = await $.ajax({"; // str += "\n\turl: '" + site_root + "GetAll" + item.TableName + ".php',"; // str += "\n\ttype: 'GET'"; // str += "\n\t});"; // str += "return result;"; // str += "\n\t}\n\n"; // return str; //} private string GenerateDelete(DataTableObject item) { string variables = "", pass = ""; foreach (var col in item.RowList) { if (col.DataType != null && col.ColumnName != "AddDate") { if (col.DataType.ToString() == "int" || col.DataType.ToString() == "decimal") { variables += "\t$" + col.ColumnName + " = " + "$data['" + col.ColumnName + "'];\n"; pass += "\t$" + col.ColumnName + " . \",\" . "; } else if ((col.DataType.ToString() == "varchar" || col.DataType.ToString() == "date") || col.DataType.ToString() == "datetime") { variables += "\t$" + col.ColumnName + " = " + "$data['" + col.ColumnName + "'];\n"; pass += "\t\"'\" . $" + col.ColumnName + " \"'\" . \",\" . "; } } } if (!String.IsNullOrEmpty(pass)) { pass = pass.Remove(pass.Length - 6); } string str = "<?php\n\n"; str += "\n\t $data = $_POST['data'];\n"; str += "\n" + variables; str += "\t $query=\"CALL sp_Delete_" + item.TableName + "(\" . " + pass + "\")\";\n"; str += "\t if (mysqli_query(connect(), $query)) \n{\n"; str += "\t $last_id = mysqli_insert_id(connect());\n"; str += "\t return json_encode($last_id);\n" + "\t mysqli_close(connect());}\n\n?>"; return(str); }
private string GenerateIdentity(DataTableObject item) { string columns = "", pass = ""; foreach (var col in item.RowList) { if (col.DataType != null) { if ((col.DataType.ToString() == "varchar" || col.DataType.ToString() == "date") || col.DataType.ToString() == "datetime") { columns += "'\" . $" + col.ColumnName + " . \"',"; pass += "$" + col.ColumnName + ","; } else { if (item.PrimaryKey != col.ColumnName) { if (col.DataType == null) { columns += "\" . $" + col.ColumnName + " . \","; pass += "$" + col.ColumnName + ","; } else { if (col.DataType.ToString().ToLower() != "timestamp") { if (col.ColumnName == "RecordStatus") { columns += "\" . 1 . \","; } else { columns += "\" . $" + col.ColumnName + " . \","; pass += "$" + col.ColumnName + ","; } } } } } } } if (!String.IsNullOrEmpty(columns)) { columns = columns.Trim().Remove(columns.Length - 1); } if (!String.IsNullOrEmpty(pass)) { pass = pass.Trim().Remove(pass.Length - 1); } string str = "function Create" + item.TableName + "(" + pass + ")\n{"; str += "\t //$data = json_decode($data);\n"; str += "\n\t $query=\"CALL sp_Insert_" + item.TableName + "(" + columns + ")\";\n"; str += "\t $result = mysqli_query(connect(), $query) or die('Query fail');// . mysqli_error());\n"; str += "\t$last_id = -1;\n"; str += "\t while ($row = mysqli_fetch_array($result))\n"; str += "\t { $last_id = $row[0]; }\n"; str += "\t return $last_id;\n"; str += "\t mysqli_close(connect());\n}"; return(str); }
private string GenerateInsert(DataTableObject item) { string variables = "\n", variables2 = "\n", pass = "", varDeclare = "\n"; foreach (var col in item.RowList) { if (col.DataType != null && col.ColumnName != "AddDate" && item.PrimaryKey != col.ColumnName) { if (col.DataType.ToString() == "float" || col.DataType.ToString() == "int" || col.DataType.ToString() == "decimal" || col.DataType.ToString() == "bit" || col.DataType.ToString() == "double") { varDeclare += "\t$" + col.ColumnName + " = null;\n"; variables += "\t$" + col.ColumnName + " = " + "$data->" + col.ColumnName + ";\n"; variables2 += "\t$" + col.ColumnName + " = " + "$data[0]->" + col.ColumnName + ";\n"; pass += "\t$" + col.ColumnName + " . \",\" . "; } else if ((col.DataType.ToString() == "mediumblob" || col.DataType.ToString() == "varchar" || col.DataType.ToString() == "date" || col.DataType.ToString() == "datetime") || col.DataType.ToString() == "datetime") { varDeclare += "\t$" + col.ColumnName + " = null;\n"; variables += "\t$" + col.ColumnName + " = " + "$data->" + col.ColumnName + ";\n"; variables2 += "\t$" + col.ColumnName + " = " + "$data[0]->" + col.ColumnName + ";\n"; pass += "\t\"'\" . $" + col.ColumnName + " . \"'\" . \",\" . "; } } } if (!String.IsNullOrEmpty(variables)) { variables = variables.Remove(variables.Length - 1); } if (!String.IsNullOrEmpty(pass)) { pass = pass + "***"; pass = pass.Replace(" . \",\" . ***", ""); } string baseCode = "$data1 = $_POST['data'];//website"; baseCode += "$data = null;"; baseCode += varDeclare; baseCode += "if ($data1 != null) \n"; baseCode += "{\n"; baseCode += "\t$data2 = json_encode($data1); \n"; baseCode += "\t$data = json_decode($data2);\n"; baseCode += variables2; baseCode += "} \n"; baseCode += "else\n"; baseCode += "{\n"; baseCode += "\t$data1 = file_get_contents('php://input');\n"; baseCode += "\t$data = json_decode($data1);\n"; baseCode += variables; baseCode += "\n}\n\n"; string str = "<?php\n\n"; str += "include_once 'global.php';\n"; str += baseCode; // str += "\n\t$d = file_get_contents('php://input');\n"; //str += "\n\t$data = json_decode($d);\n"; // str += variables +"\n\n"; str += "\t$query=\"CALL sp_Insert_" + item.TableName + "(\"." + pass + " . \")\";\n"; str += "\t $result = mysqli_query(connect(), $query) or die('Query fail');// . mysqli_error());\n"; str += "\t$last_id = -1;\n"; str += "\t while ($row = mysqli_fetch_array($result))\n"; str += "\t { $last_id = $row[0]; }\n"; str += "\t echo json_encode(array('ID' => $last_id), JSON_FORCE_OBJECT);\n"; str += "\t mysqli_close(connect());\n\n?>"; if (item.TableName == "Identity") { str = GenerateIdentity(item); } return(str); }
/// <summary> /// Read sql file and convert it to DataTableObject /// </summary> /// <param name="sqlLocation">Full file location</param> /// <returns></returns> public List <DataTableObject> Get(string sqlLocation) { if (!File.Exists(sqlLocation)) { throw new Exception("Specified MySql file does not exists"); } var list = new List <DataTableObject>(); var fileLines = File.ReadAllLines(sqlLocation); var dtCount = File.ReadAllText(sqlLocation).Split(new string[] { "create table" }, StringSplitOptions.None).Length; Boolean canStartCollecting = false; foreach (string line in fileLines) { if (!String.IsNullOrEmpty(line)) { if (line.ToLower().Contains(("create table").ToLower())) { objTable = new DataTableObject(); objTable.RowList = new List <DataRow>(); var temp = line.TrimStart().Split(' '); objTable.TableName = temp[2]; canStartCollecting = true; } if (canStartCollecting) { var str = line.TrimStart().Split(' '); if (!line.ToLower().Contains(("create table").ToLowerInvariant()) && !line.Contains(");") && line.Trim() != ")" && line.Trim() != "(") { if (line.ToLower().Contains(("AUTO_INCREMENT").ToLower())) { objTable.PrimaryKey = str[0]; objTable.PrimaryKeyDataType = str[1]; } if (str[0].ToLower() != "constraint" && str[0].Length != 0) { var row = new DataRow(); row.ColumnName = str[0]; row.WholeDataType = str[1]; if (str[1].ToLower().Contains(("varchar").ToLowerInvariant())) { row.DataType = "varchar"; row.Length = str[1].Split('(', ')')[1]; } else if (str[1].ToLower().Contains(("decimal").ToLowerInvariant())) { row.DataType = "varchar"; row.Length = str[1].Split('(', ')')[1]; } else { row.DataType = str[1]; } if (str.Length > 2) { row.IsNull = false; } else { row.IsNull = true; } objTable.RowList.Add(row); } else { if (str.Length > 5) { var row = new DataRow(); row.ColumnName = str[1]; foreach (var i in str[6]) { if (i != '(') { row.TableRef += i; } else { break; } } row.WholeDataType = str[1]; row.TableRefID = str[4].Split('(', ')')[1]; objTable.RowList.Add(row); } } } else { if (line.Contains(");")) { list.Add(objTable); canStartCollecting = false; objTable = null; } } } } } for (int i = 0; i < list.Count; i++) { var obj = list[i]; for (int f = 0; f < list.Count; f++) { var trr = list[f].TableName; for (int e = 0; e < list[f].RowList.Count; e++) { if ((list[i].TableName + list[i].PrimaryKey == list[f].RowList[e].ColumnName) || ("Parent" + list[i].TableName + list[i].PrimaryKey == list[f].RowList[e].ColumnName)) { list[f].RowList[e].TableRef = list[i].TableName; } } } } return(list); }