internal static bool AddOrUpdatedAndGetAutoIncrementingKey(SqlTable table, ref object[] allColumnValues, bool lazy)
{
if(table != null && allColumnValues != null && allColumnValues.Length > 1 && allColumnValues[0].IsDefaultValue()) {
try {
Debug.Assert(allColumnValues.Length == (table.primaryKeyHeaders?.Length ?? 0) + (table.additionalKeyHeaders?.Length ?? 0));
Debug.Assert(table.primaryKeyHeaders[0].isAutoIncrement);
NullNullableDefaultValues(table, ref allColumnValues);
string[] vars = GetVars(allColumnValues.Length - 1);
string sql = "insert into " + table.tableName;
sql += " (" + table.additionalKeyHeaders.ToCsv() + ") values (";
sql += vars.ToCsv(",", "?");
sql += ")ON DUPLICATE KEY UPDATE " + table.primaryKeyHeaders[0] + "=last_insert_id(" + table.primaryKeyHeaders[0] + "),";
sql += table.additionalKeyHeaders?.AppendStrings(vars, "=?").ToCsv();
sql += ";select last_insert_id()";
allColumnValues[0] = ExecuteScalar(sql, allColumnValues.SubArray(1));
return allColumnValues[0] != null;
} catch(Exception e) {
Log.exception(e);
}
}
Debug.Fail();
return false;
}
internal static bool AddOrUpdate(SqlTable table, ref object[] allColumnValues, bool lazy)
{
if(table != null && allColumnValues != null && allColumnValues.Length > 0) {
try {
Debug.Assert(allColumnValues.Length == (table.primaryKeyHeaders?.Length ?? 0) + (table.additionalKeyHeaders?.Length ?? 0));
NullNullableDefaultValues(table, ref allColumnValues);
string[] vars = GetVars((table.primaryKeyHeaders?.Length ?? 0) + (table.additionalKeyHeaders?.Length ?? 0));
string sql = "insert into " + table.tableName;
sql += " (" + table.primaryKeyHeaders.Append(table.additionalKeyHeaders).ToCsv();
sql += ") values (" + vars.ToCsv(",", "?") + ")";
sql += " ON DUPLICATE KEY UPDATE ";
string[] keyValues = table.additionalKeyHeaders?.AppendStrings(vars.SubArray((uint)(table.primaryKeyHeaders?.Length ?? 0)), "=?");
sql += keyValues.ToCsv();
return ExecuteNonQuery(sql, allColumnValues) == 1;
} catch(Exception e) {
Log.exception(e);
}
}
Debug.Fail();
return false;
}
internal static bool Delete(SqlTable table, string join, string where, object[] vars, bool lazy)
{
if(table != null && where != null && vars != null) {
try {
Debug.Assert(where.Trim().Length > 0);
Debug.Assert(vars.Length > 0);
string sql = "delete from " + table.tableName;
if(join != null) {
sql += " " + join;
}
sql += " where " + where;
return ExecuteNonQuery(sql, vars) > 0;
} catch(Exception e) {
Log.exception(e);
}
}
Debug.Fail();
return false;
}
static void NullNullableDefaultValues(SqlTable table, ref object[] allValues)
{
for(int i = 0; i < (table.additionalKeyHeaders?.Length ?? 0); i++) {
if(table.additionalKeyHeaders[i].isNullable && allValues[(table.primaryKeyHeaders?.Length ?? 0) + i].IsDefaultValue()) {
allValues[(table.primaryKeyHeaders?.Length ?? 0) + i] = null;
}
}
}