/// <summary>
/// Generates the create table SQL.
/// </summary>
/// <returns>The create table SQL.</returns>
/// <param name="tableName">Table name.</param>
/// <param name="configs">Configs.</param>
public string GenerateCreateTableSQL(string tableName, AttributeMappingConfig<ColumnAttribute>[] configs)
{
StringBuilder stringBuilder = new StringBuilder ();
stringBuilder.AppendFormat ("CREATE TABLE '{0}' (", tableName);
for (int i = 0; i < configs.Length; i++) {
if (configs [i] == null) {
continue;
}
StringBuilder temp = new StringBuilder ();
temp.AppendFormat ("'{0}' ", configs [i].name);
if (configs [i].t == null || configs [i].t.Type == "") {
temp.Append (ConvertToSQLType (configs [i].type));
} else {
temp.Append (configs [i].t.Type);
}
if (configs [i].t != null) {
temp.Append (ConvertToSQLConstraints (configs [i].t.Constraints));
}
if (i < configs.Length - 1) {
temp.Append (", ");
}
stringBuilder.Append (temp);
}
stringBuilder.Append (");");
return stringBuilder.ToString ();
}
protected Dictionary<string, object> CreateAttributeJson (Dictionary<string, object> data,
AttributeMappingConfig<ColumnAttribute>[] configs)
{
Dictionary<string, object> dic = new Dictionary<string, object> ();
foreach (AttributeMappingConfig<ColumnAttribute> config in configs) {
if (Util.IsValueType (config.type)) {
if (data.ContainsKey (config.name)) {
dic.Add (config.name, data [config.name]);
}
} else {
if (!config.type.IsArray) {
continue;
}
Type type = config.type.GetElementType ();
string value = "";
if (config.t != null) {
ColumnAttribute column = config.t as ColumnAttribute;
if (column != null) {
if (column.Value == "" && column.Type != "") {
value = config.name;
} else if (joinType != null) {
Type tempType = joinType (data [column.Type].ToString ());
type = tempType == null ? type : tempType;
value = column.Value;
}
}
}
Dictionary<string, object> join = new Dictionary<string, object> ();
AttributeMappingConfig<ColumnAttribute>[] tempConfigs = Reflection.FieldAMCRetrieve<ColumnAttribute> (config.type.GetElementType ());
foreach (AttributeMappingConfig<ColumnAttribute> tempConfig in tempConfigs) {
if (tempConfig.t != null) {
ColumnAttribute column = tempConfig.t as ColumnAttribute;
if (column != null) {
if (column.CheckConstraints (DataConstraints.PK)) {
join.Add (tempConfig.name, data [value]);
break;
}
if (data.ContainsKey (column.Value)) {
join.Add (tempConfig.name, data [column.Value]);
}
}
}
}
if (join.Count > 0) {
dic.Add (config.name, CreateAttributeJson (type, join));
}
}
}
return dic;
}
protected IEnumerator CreateTable()
{
EditorUtility.DisplayProgressBar("[Exce -> Sqlite DB] Converter",
string.Format("{0}({1}/{2})",
listSheet [index].SheetName,
index,
listSheet.Count),
(float)index / (float)listSheet.Count);
Action innerCreateTable = () => {
if (index < listSheet.Count - 1)
{
index++;
EditorUtil.StartCoroutine(CreateTable());
}
else
{
if (stringBuilder.ToString() != "")
{
EditorUtil.CreateTextFile(Path.GetFileNameWithoutExtension(dbName), stringBuilder.ToString(), dbPath, false, ".sql");
}
Debug.Log("saved name : " + path);
EditorUtility.ClearProgressBar();
AssetDatabase.Refresh();
}
};
ISheet sheet = listSheet [index];
if (sheet.LastRowNum <= 1)
{
innerCreateTable();
yield break;
}
IRow titleRow = sheet.GetRow(0);
if (titleRow == null)
{
innerCreateTable();
yield break;
}
AttributeMappingConfig <ColumnAttribute>[] configs = new AttributeMappingConfig <ColumnAttribute> [titleRow.LastCellNum];
List <Dictionary <string, object> > list = new List <Dictionary <string, object> > ();
for (int i = 1; i <= sheet.LastRowNum; i++)
{
Dictionary <string, object> dic = new Dictionary <string, object> ();
for (int j = 0; j < titleRow.LastCellNum; j++)
{
ICell titleCell = titleRow.GetCell(j);
ICell valueCell = sheet.GetRow(i).GetCell(j);
Type type = maker.AddData(titleCell, valueCell, dic);
if (type == null)
{
continue;
}
if (configs [j] == null)
{
configs [j] = new AttributeMappingConfig <ColumnAttribute> ();
configs [j].name = titleCell.StringCellValue;
configs [j].type = type;
}
}
if (dic.Count > 0)
{
list.Add(dic);
}
}
string dropQuery = query.GenerateDropTableSQL(sheet.SheetName);
stringBuilder.Append(dropQuery);
stringBuilder.AppendLine();
string tableQuery = query.GenerateCreateTableSQL(sheet.SheetName, configs);
stringBuilder.Append(tableQuery);
stringBuilder.AppendLine();
for (int i = 0; i < list.Count; i++)
{
string[] column = list [i].Keys.ToArray();
string[] value = Array.ConvertAll(list [i].Values.ToArray(), x => x.ToString());
string insertQuery = query.GenerateInsertSQL(sheet.SheetName, column, value);
stringBuilder.Append(insertQuery);
stringBuilder.AppendLine();
}
query.ExecuteNonQuery(tableQuery);
query.INSERT_BATCH(sheet.SheetName, list);
yield return(null);
if (splitFlag)
{
EditorUtil.CreateTextFile(sheet.SheetName, stringBuilder.ToString(), dbPath, false, ".sql");
stringBuilder = new StringBuilder();
}
innerCreateTable();
}
protected IEnumerator CreateTable()
{
EditorUtility.DisplayProgressBar ("[Exce -> Sqlite DB] Converter",
string.Format ("{0}({1}/{2})",
listSheet [index].SheetName,
index,
listSheet.Count),
(float)index / (float)listSheet.Count);
Action innerCreateTable = () => {
if (index < listSheet.Count - 1) {
index++;
EditorUtil.StartCoroutine (CreateTable ());
} else {
if (stringBuilder.ToString () != "") {
EditorUtil.CreateTextFile (Path.GetFileNameWithoutExtension (dbName), stringBuilder.ToString (), dbPath, false, ".sql");
}
Debug.Log ("saved name : " + path);
EditorUtility.ClearProgressBar ();
AssetDatabase.Refresh ();
}
};
ISheet sheet = listSheet [index];
if (sheet.LastRowNum <= 1) {
innerCreateTable ();
yield break;
}
IRow titleRow = sheet.GetRow (0);
if (titleRow == null) {
innerCreateTable ();
yield break;
}
AttributeMappingConfig<ColumnAttribute>[] configs = new AttributeMappingConfig<ColumnAttribute>[titleRow.LastCellNum];
List<Dictionary<string, object>> list = new List<Dictionary<string, object>> ();
for (int i = 1; i <= sheet.LastRowNum; i++) {
Dictionary<string, object> dic = new Dictionary<string, object> ();
for (int j = 0; j < titleRow.LastCellNum; j++) {
ICell titleCell = titleRow.GetCell (j);
ICell valueCell = sheet.GetRow (i).GetCell (j);
Type type = maker.AddData (titleCell, valueCell, dic);
if (type == null) {
continue;
}
if (configs [j] == null) {
configs [j] = new AttributeMappingConfig<ColumnAttribute> ();
configs [j].name = titleCell.StringCellValue;
configs [j].type = type;
}
}
if (dic.Count > 0) {
list.Add (dic);
}
}
string dropQuery = query.GenerateDropTableSQL (sheet.SheetName);
stringBuilder.Append (dropQuery);
stringBuilder.AppendLine ();
string tableQuery = query.GenerateCreateTableSQL (sheet.SheetName, configs);
stringBuilder.Append (tableQuery);
stringBuilder.AppendLine ();
for (int i = 0; i < list.Count; i++) {
string[] column = list [i].Keys.ToArray ();
string[] value = Array.ConvertAll (list [i].Values.ToArray (), x => x.ToString ());
string insertQuery = query.GenerateInsertSQL (sheet.SheetName, column, value);
stringBuilder.Append (insertQuery);
stringBuilder.AppendLine ();
}
query.ExecuteNonQuery (tableQuery);
query.INSERT_BATCH (sheet.SheetName, list);
yield return null;
if (splitFlag) {
EditorUtil.CreateTextFile (sheet.SheetName, stringBuilder.ToString (), dbPath, false, ".sql");
stringBuilder = new StringBuilder ();
}
innerCreateTable ();
}
