public void UpdateDatabaseWithArteProps(string ConnectionString)
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(ConnectionString);
conn.Open();
System.Data.SQLite.SQLiteTransaction transaction = conn.BeginTransaction();
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(conn);
command.Transaction = transaction;
foreach (var a in ArteList)
{
string UpdateNames = "UPDATE Text SET IdentifyString = \"" + a.Type.ToString() + ";\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString("X6") + "]\"";
Console.WriteLine(UpdateNames);
command.CommandText = UpdateNames;
command.ExecuteNonQuery();
string UpdateDescs = "UPDATE Text SET IdentifyString = \"Description;\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.DescStringDicId + " / 0x" + a.DescStringDicId.ToString("X6") + "]\"";
Console.WriteLine(UpdateDescs);
command.CommandText = UpdateDescs;
command.ExecuteNonQuery();
if (a.Type == Arte.ArteType.Generic)
{
string UpdateStatus = "UPDATE Text SET status = 4, updated = 1, updatedby = \"[HyoutaTools]\", updatedtimestamp = " + TimeUtils.DateTimeToUnixTime(DateTime.UtcNow) + " WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString("X6") + "]\"";
Console.WriteLine(UpdateStatus);
command.CommandText = UpdateStatus;
command.ExecuteNonQuery();
}
}
command.Dispose();
transaction.Commit();
conn.Close();
conn.Dispose();
}
internal void ReinsertCodeIntoDatabase(string ConnectionString)
{
SQLiteConnection Connection = new SQLiteConnection(ConnectionString);
Connection.Open();
using (SQLiteTransaction Transaction = Connection.BeginTransaction())
using (SQLiteCommand CopyCommand = new SQLiteCommand(Connection))
using (SQLiteCommand Command = new SQLiteCommand(Connection)) {
SQLiteParameter EnglishIDParam = new SQLiteParameter();
SQLiteParameter CodeTextParam = new SQLiteParameter();
SQLiteParameter CopyIdParam = new SQLiteParameter();
Command.CommandText = "UPDATE Text SET english = ?, UpdatedBy = 'HyoutaTools', UpdatedTimestamp = " + TimeUtils.DateTimeToUnixTime(DateTime.UtcNow) + " WHERE ID = ?";
Command.Parameters.Add(CodeTextParam);
Command.Parameters.Add(EnglishIDParam);
CopyCommand.CommandText = "INSERT INTO History(ID, english, comment, status, UpdatedBy, UpdatedTimestamp) SELECT ID, english, comment, status, UpdatedBy, UpdatedTimestamp FROM Text WHERE ID = ?";
CopyCommand.Parameters.Add(CopyIdParam);
int ENID = 1;
string TextToInsert = "";
foreach (ScriptEntry s in this.ScriptData)
{
if (s.Type == 0x02)
{
CopyIdParam.Value = ENID;
CopyCommand.ExecuteNonQuery();
CodeTextParam.Value = TextToInsert;
EnglishIDParam.Value = ENID;
Command.ExecuteNonQuery();
ENID++;
ENID++;
TextToInsert = "";
continue;
}
TextToInsert = TextToInsert + s.FormatForGraceNote() + '\n';
}
if (TextToInsert != null)
{
CopyIdParam.Value = ENID;
CopyCommand.ExecuteNonQuery();
CodeTextParam.Value = TextToInsert;
EnglishIDParam.Value = ENID;
Command.ExecuteNonQuery();
ENID++;
}
Transaction.Commit();
}
Connection.Close();
return;
}
/// <summary>Utility Function to insert Entries into a GraceNote database. Usually used when ripping text from a game file.</summary>
/// <param name="Entries">The Entries to insert. Required values in the entries are: TextJP, TextEN, Comment, Status, PointerRef, IdentifyString, and IdentifyPointerRef. The rest is filled automatically.</param>
public static void InsertSQL(GraceNoteDatabaseEntry[] Entries, String ConnectionString, String ConnectionStringGracesJapanese)
{
SQLiteConnection Connection = new SQLiteConnection(ConnectionString);
SQLiteConnection ConnectionGracesJapanese = new SQLiteConnection(ConnectionStringGracesJapanese);
Connection.Open();
ConnectionGracesJapanese.Open();
using (SQLiteTransaction Transaction = Connection.BeginTransaction())
using (SQLiteTransaction TransactionGracesJapanese = ConnectionGracesJapanese.BeginTransaction())
using (SQLiteCommand CommandInsertEntry = new SQLiteCommand(Connection))
using (SQLiteCommand CommandInsertJapanese = new SQLiteCommand(ConnectionGracesJapanese))
using (SQLiteCommand CommandGetMaxJapaneseID = new SQLiteCommand(ConnectionGracesJapanese))
using (SQLiteCommand CommandSearchJapanese = new SQLiteCommand(ConnectionGracesJapanese)) {
SQLiteParameter JapaneseIDParam = new SQLiteParameter();
SQLiteParameter JapaneseParam = new SQLiteParameter();
SQLiteParameter EnglishIDParam = new SQLiteParameter();
SQLiteParameter StringIDParam = new SQLiteParameter();
SQLiteParameter EnglishParam = new SQLiteParameter();
SQLiteParameter CommentParam = new SQLiteParameter();
SQLiteParameter EnglishStatusParam = new SQLiteParameter();
SQLiteParameter PointerRefParam = new SQLiteParameter();
SQLiteParameter IdentifyStringParam = new SQLiteParameter();
SQLiteParameter IdentifyPointerRefParam = new SQLiteParameter();
SQLiteParameter UpdatedTimestampParam = new SQLiteParameter();
SQLiteParameter JapaneseSearchParam = new SQLiteParameter();
CommandInsertJapanese.CommandText = "INSERT INTO Japanese (ID, string, debug) VALUES (?, ?, 0)";
CommandInsertJapanese.Parameters.Add(JapaneseIDParam);
CommandInsertJapanese.Parameters.Add(JapaneseParam);
CommandInsertEntry.CommandText = "INSERT INTO Text (ID, StringID, english, comment, updated, status, PointerRef, IdentifyString, IdentifyPointerRef, UpdatedBy, UpdatedTimestamp)"
+ " VALUES (?, ?, ?, ?, 0, ?, ?, ?, ?, \"HyoutaTools\", ?)";
CommandInsertEntry.Parameters.Add(EnglishIDParam);
CommandInsertEntry.Parameters.Add(StringIDParam);
CommandInsertEntry.Parameters.Add(EnglishParam);
CommandInsertEntry.Parameters.Add(CommentParam);
CommandInsertEntry.Parameters.Add(EnglishStatusParam);
CommandInsertEntry.Parameters.Add(PointerRefParam);
CommandInsertEntry.Parameters.Add(IdentifyStringParam);
CommandInsertEntry.Parameters.Add(IdentifyPointerRefParam);
CommandInsertEntry.Parameters.Add(UpdatedTimestampParam);
CommandGetMaxJapaneseID.CommandText = "SELECT MAX(ID)+1 FROM Japanese";
CommandSearchJapanese.CommandText = "SELECT ID FROM Japanese WHERE string = ? AND debug = 0";
CommandSearchJapanese.Parameters.Add(JapaneseSearchParam);
int JPID;
object JPMaxIDObject = CommandGetMaxJapaneseID.ExecuteScalar();
int JPMaxID;
try {
JPMaxID = Int32.Parse(JPMaxIDObject.ToString());
} catch (System.FormatException) {
// there's no ID in the database, just start with 0
JPMaxID = 0;
}
int ENID = 1;
foreach (GraceNoteDatabaseEntry e in Entries)
{
// fetch GracesJapanese ID or generate new & insert new text
JapaneseSearchParam.Value = e.TextJP;
object JPIDobj = CommandSearchJapanese.ExecuteScalar();
if (JPIDobj != null)
{
JPID = (int)JPIDobj;
}
else
{
JPID = JPMaxID++;
JapaneseIDParam.Value = JPID;
JapaneseParam.Value = e.TextJP;
CommandInsertJapanese.ExecuteNonQuery();
}
// insert text into English table
EnglishIDParam.Value = ENID;
StringIDParam.Value = JPID;
EnglishParam.Value = e.TextEN;
CommentParam.Value = e.Comment;
EnglishStatusParam.Value = e.Status;
PointerRefParam.Value = e.PointerRef;
IdentifyStringParam.Value = e.IdentifyString;
IdentifyPointerRefParam.Value = e.IdentifyPointerRef;
UpdatedTimestampParam.Value = TimeUtils.DateTimeToUnixTime(DateTime.Now);
CommandInsertEntry.ExecuteNonQuery();
ENID++;
}
Transaction.Commit();
TransactionGracesJapanese.Commit();
}
ConnectionGracesJapanese.Close();
Connection.Close();
return;
}