public byte[] ExportSQL(FileManager fileManager, string tablePrefix = "")
{
string[] sqlReserved = new string[] { "group", "condition", "left", "right", "default", "key", "force", "analyze", "kill", "usage", "order" };
StringWriter stringWriter = new StringWriter();
string tableName = String.Format("{0}{1}", tablePrefix, StringId.ToLower());
stringWriter.WriteLine(String.Format("CREATE TABLE {0} (", tableName));
stringWriter.WriteLine("\tid INT NOT NULL PRIMARY KEY,");
String columnDec = "\t{0} {1}{2}";
int colCount = 1;
ObjectDelegator objectDelegator;
if (fileManager == null || !fileManager.DataFileDelegators.ContainsKey(StringId))
{
objectDelegator = new ObjectDelegator(Attributes.RowType.GetFields());
FieldInfo headerField = DataType.GetField("header", BindingFlags.Instance | BindingFlags.NonPublic);
objectDelegator.AddField(headerField);
}
else
{
objectDelegator = fileManager.DataFileDelegators[StringId];
}
int noColumns = objectDelegator.FieldCount - 1; // remove header
foreach (ObjectDelegator.FieldDelegate field in objectDelegator)
{
if (field.Name == "header") continue; // dont want this
String columnName = field.Name;
String dataType = String.Empty;
String formatted = String.Empty;
if (sqlReserved.Where(str => str == columnName.ToLower()).Any() == true)
{
columnName = "a" + columnName;
}
// Special types
OutputAttribute excelOutput = GetExcelAttribute(field.Info);
if (excelOutput != null)
{
if (excelOutput.IsScript || excelOutput.IsSecondaryString || excelOutput.IsStringOffset) dataType = "TEXT";
else if (excelOutput.IsBitmask) dataType = "BIGINT";
}
if (dataType == String.Empty)
{
if (field.FieldType == typeof(int)) dataType = "INT";
else if (field.FieldType == typeof(float)) dataType = "DECIMAL";
else if (field.FieldType == typeof(byte)) dataType = "TINYINT";
else if (field.FieldType == typeof(short)) dataType = "SMALLINT";
else if (field.FieldType == typeof(uint) || field.FieldType == typeof(Int64)) dataType = "BIGINT";
else if (field.FieldType == typeof(string))
{
MarshalAsAttribute marshalAs = (MarshalAsAttribute)field.Info.GetCustomAttributes(typeof(MarshalAsAttribute), false).First();
dataType = String.Format("VARCHAR({0})", marshalAs.SizeConst);
}
else if (field.FieldType.BaseType == typeof(Array)) { dataType = "TEXT"; }
}
formatted = String.Format(columnDec, columnName, dataType, colCount < noColumns ? "," : String.Empty);
stringWriter.WriteLine(formatted);
colCount++;
}
stringWriter.WriteLine(");");
stringWriter.WriteLine("INSERT INTO {0}", tableName);
stringWriter.WriteLine("VALUES");
colCount = 1;
int rowCount = 0;
int noRows = Rows.Count;
StringWriter valueString = new StringWriter();
foreach (Object rowObject in Rows)
{
valueString.Write(String.Format("\t({0},", rowCount)); // write the id
foreach (ObjectDelegator.FieldDelegate field in objectDelegator)
{
if (field.Name == "header") continue; // dont want this
OutputAttribute excelOutput = GetExcelAttribute(field.Info);
Object objValue = field.GetValue(rowObject);
bool valueParsed = false;
if (excelOutput != null)
{
if (excelOutput.IsScript)
{
int[] scriptTable = ReadScriptTable((int)objValue);
if (scriptTable != null)
{
int offset = (int)objValue;
int[] scriptbuffer = ReadScriptTable(offset);
String scriptString = FileTools.ArrayToStringGeneric(scriptbuffer, ",");
try
{
if (fileManager != null)
{
ExcelScript excelScript = new ExcelScript(fileManager);
scriptString = excelScript.Decompile(_scriptBuffer, offset);
scriptString = StringToSQLString(scriptString);
scriptString = EncapsulateString(scriptString);
valueString.Write(scriptString);
}
else
{
valueString.Write(scriptString);
}
}
catch (Exception e)
{
valueString.Write(EncapsulateString(scriptString));
Debug.WriteLine(e.ToString());
scriptString = "ScriptError(" + scriptString + ")";
}
}
else
{
valueString.Write("\"\"");
}
valueParsed = true;
}
else if (excelOutput.IsSecondaryString)
{
if ((int)objValue != -1)
{
string secString = ReadSecondaryStringTable((int)objValue);
valueString.Write(String.Format("\"{0}\"", secString));
}
else
{
valueString.Write("\"\"");
}
valueParsed = true;
}
else if (excelOutput.IsStringOffset)
{
string offString = ReadStringTable((int)objValue);
offString = StringToSQLString(offString);
offString = String.Format("\"{0}\"", offString);
valueString.Write(offString);
valueParsed = true;
}
else if (excelOutput.IsBitmask)
{
valueString.Write((uint)objValue);
valueParsed = true;
}
}
if (valueParsed == false)
{
if (field.FieldType == typeof(string))
{
string strValue = EncapsulateString(StringToSQLString((objValue.ToString())));
valueString.Write(strValue);
}
else if (field.FieldType.BaseType == typeof(Array))
{
string strValue = EncapsulateString(((Array)objValue).ToString(","));
valueString.Write(strValue);
}
else if (field.FieldType == typeof(float))
{
string strValue = ((float)objValue).ToString("r");
valueString.Write(strValue);
}
else
{
valueString.Write(objValue);
}
}
if (colCount < noColumns) valueString.Write(",");
else valueString.Write(")");
colCount++;
}
if (rowCount < noRows - 1) valueString.WriteLine(",");
else valueString.WriteLine(";");
rowCount++;
colCount = 1;
}
stringWriter.Write(valueString.ToString());
byte[] buffer = FileTools.StringToASCIIByteArray(stringWriter.ToString());
return buffer;
}
public byte[] ExportCSV(FileManager fileManager, IEnumerable<String> columnNames = null)
{
if (Attributes.IsEmpty) return new byte[0];
//// init stuffs
byte[] csvBuffer = new byte[1024];
int csvOffset = 0;
bool isProperties = (StringId == "PROPERTIES" || StringId == "_TCv4_PROPERTIES");
ObjectDelegator objectDelegator;
if (fileManager == null || !fileManager.DataFileDelegators.ContainsKey(StringId))
{
objectDelegator = new ObjectDelegator(Attributes.RowType.GetFields());
FieldInfo headerField = DataType.GetField("header", BindingFlags.Instance | BindingFlags.NonPublic);
objectDelegator.AddField(headerField);
}
else
{
objectDelegator = fileManager.DataFileDelegators[StringId];
}
//// header row
// tables can have different header values
String tableHeaderStr = String.Format("{0}({1})", StringId, FileTools.ObjectToStringGeneric(_excelFileHeader, ","));
// rest of columns
List<String> columnsList = new List<String> { tableHeaderStr };
foreach (ObjectDelegator.FieldDelegate fieldDelegate in objectDelegator)
{
if (columnNames == null)
{
if (!fieldDelegate.IsPublic) continue;
}
else if (!columnNames.Contains(fieldDelegate.Name))
{
continue;
}
columnsList.Add(fieldDelegate.Name);
}
// excel table type-specific columns
if (Attributes.HasStats) columnsList.Add("Stats");
if (isProperties) columnsList.Add("Script");
// column header row
String[] columns = columnsList.ToArray();
int colCount = columns.Length;
int rowCount = Count + 1; // +1 for column headers
//// csv generation
String[][] strings = new string[rowCount][];
strings[0] = columns;
int col = -1;
foreach (ObjectDelegator.FieldDelegate fieldDelegate in objectDelegator)
{
if (!columns.Contains(fieldDelegate.Name) && fieldDelegate.Name != "header") continue;
col++;
OutputAttribute excelAttribute = GetExcelAttribute(fieldDelegate.Info);
//if (fieldDelegate.Name == "spawnFromMonsterUnitType")
//{
// int bp = 0;
//}
int row = 0;
foreach (Object rowObject in Rows)
{
String[] rowStr = strings[++row];
if (rowStr == null)
{
rowStr = new String[colCount];
strings[row] = rowStr;
}
if (fieldDelegate.Name == "header")
{
RowHeader rowHeader = (RowHeader)fieldDelegate.GetValue(rowObject);
rowStr[col] = FileTools.ObjectToStringGeneric(rowHeader, ",");
continue;
}
if (fieldDelegate.Name == "code")
{
int code;
if (fieldDelegate.FieldType == typeof(short))
{
code = (int)(short)fieldDelegate.GetValue(rowObject); // yes, that extra (int) is *needed* to cast the short correctly
}
else
{
code = (int)fieldDelegate.GetValue(rowObject);
}
if (StringId == "REGION") // can't export region code values as chars due to weird chars
{
rowStr[col] = "\"" + code + "\"";
}
else
{
rowStr[col] = "\"" + _CodeToString(code) + "\"";
}
continue;
}
bool isArray = (fieldDelegate.FieldType.BaseType == typeof(Array));
if (excelAttribute != null)
{
if (excelAttribute.IsTableIndex && fileManager != null)
{
int[] indexValues;
Object indexObj = fieldDelegate.GetValue(rowObject);
if (isArray)
{
indexValues = (int[])indexObj;
}
else
{
indexValues = new[] { (int)indexObj };
}
String[] indexStrs = new String[indexValues.Length];
for (int i = 0; i < indexStrs.Length; i++)
{
if (indexValues[i] == -1) // empty string/no code
{
indexStrs[i] = "-1";
continue;
}
String tableStringId = excelAttribute.TableStringId;
String negative = String.Empty;
if (indexValues[i] < 0)
{
indexValues[i] *= -1;
negative = "-";
}
String indexStr = null;
if (fileManager.DataTableHasColumn(tableStringId, "code"))
{
int code = fileManager.GetExcelIntFromStringId(tableStringId, indexValues[i], "code");
if (code != 0) indexStr = _CodeToString(code);
}
else if (fileManager.DataTableHasColumn(tableStringId, "name"))
{
indexStr = fileManager.GetExcelStringFromStringId(tableStringId, indexValues[i], "name");
}
if (indexStr == null)
{
indexStr = fileManager.GetExcelStringFromStringId(tableStringId, indexValues[i]);
}
indexStrs[i] = negative + indexStr;
}
rowStr[col] = "\"" + String.Join(",", indexStrs) + "\"";
continue;
}
if (excelAttribute.IsStringOffset)
{
int offset = (int)fieldDelegate.GetValue(rowObject);
if (offset != -1)
{
rowStr[col] = ReadStringTable(offset);
}
continue;
}
if (excelAttribute.IsScript)
{
int offset = (int)fieldDelegate.GetValue(rowObject);
if ((offset == 0))
{
FileTools.WriteToBuffer(ref csvBuffer, ref csvOffset, FileTools.StringToASCIIByteArray("0"));
continue;
}
int[] buffer = ReadScriptTable(offset);
if (buffer == null) throw new Exceptions.ScriptFormatException("The script bytes were unable to be read using ReadScriptTable.", offset);
String scriptString = FileTools.ArrayToStringGeneric(buffer, ",");
if (fileManager != null)
{
if (offset == 9325 /*from DataTable export*/ || offset == 9649 /*from Object export*/ && StringId == "SKILLS") // todo: not sure what's with this script...
{
/* Compiled Bytes:
* 26,30,700,6,26,1,399,358,669,616562688,711,26,62,3,17,669,641728512,26,8,711,26,62,3,17,358,669,322961408,26,5,700,6,26,1,399,358,388,0
*
* Ending Stack (FIFO):
* SetStat669('sfx_attack_pct', 'all', 30 * ($sklvl - 1))
* SetStat669('sfx_duration_pct', 'all', get_skill_level(@unit, 'Shield_Mastery'))
* SetStat669('damage_percent_skill', 8 * get_skill_level(@unit, 'Shield_Mastery'))
* SetStat669('damage_percent_skill', 8 * get_skill_level(@unit, 'Shield_Mastery')) + 5 * ($sklvl - 1)
*
* The last SetStat has strange overhang - decompiling wrong?
* Or is it "supposed" to be there?
* i.e. It's actually decompiling correctly, but because I've assumed such scripts to be wrong (as the end +5... segment is useless) we get the Stack exception
*/
scriptString = "ScriptError(" + scriptString + ")";
}
else
{
try
{
ExcelScript excelScript = new ExcelScript(fileManager);
scriptString = "\"" + excelScript.Decompile(_scriptBuffer, offset, scriptString, StringId, row, col, fieldDelegate.Name) + "\"";
}
catch (Exception e)
{
Debug.WriteLine(e.ToString());
scriptString = "ScriptError(" + scriptString + ")";
}
}
}
rowStr[col] = scriptString;
continue;
}
if (excelAttribute.IsSecondaryString)
{
int index = (int)fieldDelegate.GetValue(rowObject);
if (index != -1)
{
rowStr[col] = SecondaryStrings[index];
}
continue;
}
if (excelAttribute.IsBitmask)
{
rowStr[col] = "\"" + fieldDelegate.GetValue(rowObject) + "\"";
continue;
}
}
Object outValue = fieldDelegate.GetValue(rowObject);
if (isArray)
{
rowStr[col] = ((Array)outValue).ToString(",");
}
else if (fieldDelegate.FieldType == typeof(float))
{
rowStr[col] = ((float)outValue).ToString("r");
}
//else if (fieldDelegate.FieldType.BaseType == typeof(Enum)) // might as well export enums as their string representations
//{
// rowStr[col] = ((UInt32)outValue).ToString();
//}
else
{
rowStr[col] = outValue.ToString();
}
}
}
// stats
if (Attributes.HasStats)
{
col++;
int row = -1;
foreach (String[] rowStr in strings)
{
if (row == -1) // columns header row
{
row++;
continue;
}
rowStr[col] = StatsBuffer[row++].ToString(",");
}
}
// properties scripts
if (isProperties)
{
// not point in doing this
//if (tableHeader.Unknown1 != 2 || scriptRow == ExcelFunctions.Count - 1) // need 1 extra row for some reason
col++;
int row = -1;
foreach (String[] rowStr in strings)
{
if (row == -1)
{
row++;
continue;
}
if (row >= ExcelFunctions.Count) break;
ExcelFunction excelScript = ExcelFunctions[row++];
String excelScriptFunction = String.Empty;
foreach (ExcelFunction.Parameter paramater in excelScript.Parameters)
{
excelScriptFunction += String.Format("\n{0},{1},{2},{3}", paramater.Name, paramater.Unknown, paramater.TypeId, paramater.TypeValues.ToString(","));
}
if (excelScript.ScriptByteCode != null)
{
int offset = 0;
excelScriptFunction += "\n" + FileTools.ByteArrayToInt32Array(excelScript.ScriptByteCode, ref offset, excelScript.ScriptByteCode.Length / 4).ToString(",") + "\n";
}
rowStr[col] = excelScriptFunction;
}
}
//// join string arrays and create byte array
String[] rows = new String[rowCount];
col = 0;
foreach (String[] rowStr in strings)
{
rows[col] = String.Join("\t", rowStr);
col++;
}
String csvString = String.Join(Environment.NewLine, rows);
return csvString.ToASCIIByteArray();
}
