/// <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 = Util.DateTimeToUnixTime( DateTime.Now );
                    CommandInsertEntry.ExecuteNonQuery();

                    ENID++;
                }
                Transaction.Commit();
                TransactionGracesJapanese.Commit();
            }
            ConnectionGracesJapanese.Close();
            Connection.Close();

            return;
        }
        public static GraceNoteDatabaseEntry[] GetAllEntriesFromDatabase( String ConnectionString, String GracesJapaneseConnectionString )
        {
            List<GraceNoteDatabaseEntry> Entries = new List<GraceNoteDatabaseEntry>();

            SQLiteConnection Connection = new SQLiteConnection( ConnectionString );
            Connection.Open();
            SQLiteConnection ConnectionGJ = new SQLiteConnection( GracesJapaneseConnectionString );
            ConnectionGJ.Open();

            using ( SQLiteTransaction Transaction = Connection.BeginTransaction() )
            using ( SQLiteCommand Command = new SQLiteCommand( Connection ) )
            using ( SQLiteTransaction TransactionGJ = ConnectionGJ.BeginTransaction() )
            using ( SQLiteCommand CommandGJ = new SQLiteCommand( ConnectionGJ ) ) {
                Command.CommandText = "SELECT english, ID, StringID, status, comment, updated, PointerRef, IdentifyString, IdentifyPointerRef, UpdatedBy, UpdatedTimestamp " +
                                      "FROM Text ORDER BY ID";
                CommandGJ.CommandText = "SELECT string FROM Japanese WHERE ID = ?";
                SQLiteParameter StringIdParam = new SQLiteParameter();
                CommandGJ.Parameters.Add( StringIdParam );

                SQLiteDataReader r = Command.ExecuteReader();
                while ( r.Read() ) {
                    String SQLText;

                    try {
                        SQLText = r.GetString( 0 ).Replace( "''", "'" );
                    } catch ( System.InvalidCastException ) {
                        SQLText = null;
                    }

                    int ID = r.GetInt32( 1 );
                    int StringID = r.GetInt32( 2 );
                    int Status = r.GetInt32( 3 );
                    String Comment;
                    try {
                        Comment = r.GetString( 4 );
                    } catch ( System.InvalidCastException ) {
                        Comment = null;
                    }
                    int Updated = r.GetInt32( 5 );
                    int PointerRef = r.GetInt32( 6 );
                    String IdentifyString;
                    try {
                        IdentifyString = r.GetString( 7 );
                    } catch ( System.InvalidCastException ) {
                        IdentifyString = null;
                    }

                    int IdentifyPointerRef;
                    try {
                        IdentifyPointerRef = r.GetInt32( 8 );
                    } catch ( System.InvalidCastException ) {
                        IdentifyPointerRef = 0;
                    }

                    String UpdatedBy;
                    try {
                        UpdatedBy = r.GetString( 9 );
                    } catch ( System.InvalidCastException ) {
                        UpdatedBy = null;
                    }

                    int UpdatedTimestamp;
                    try {
                        UpdatedTimestamp = r.GetInt32( 10 );
                    } catch ( System.InvalidCastException ) {
                        UpdatedTimestamp = 0;
                    }

                    StringIdParam.Value = StringID;
                    String JPText;
                    try {
                        JPText = ( (string)CommandGJ.ExecuteScalar() ).Replace( "''", "'" );
                    } catch ( System.InvalidCastException ) {
                        JPText = null;
                    }

                    GraceNoteDatabaseEntry de = new GraceNoteDatabaseEntry();
                    de.TextEN = SQLText;
                    de.TextJP = JPText;
                    de.ID = ID;
                    de.JPID = StringID;
                    de.Status = Status;
                    de.Comment = Comment;
                    de.PointerRef = PointerRef;
                    de.IdentifyString = IdentifyString;
                    de.IdentifyPointerRef = IdentifyPointerRef;
                    de.UpdatedBy = UpdatedBy;
                    de.UpdatedTimestamp = UpdatedTimestamp;
                    de.Database = ConnectionString;

                    Entries.Add( de );
                }

                Transaction.Rollback();
                TransactionGJ.Rollback();
            }

            ConnectionGJ.Close();
            Connection.Close();

            return Entries.ToArray();
        }
예제 #3
0
        public static GraceNoteDatabaseEntry[] GetAllEntriesFromDatabase(String ConnectionString, String GracesJapaneseConnectionString)
        {
            List <GraceNoteDatabaseEntry> Entries = new List <GraceNoteDatabaseEntry>();

            SQLiteConnection Connection = new SQLiteConnection(ConnectionString);

            Connection.Open();
            SQLiteConnection ConnectionGJ = new SQLiteConnection(GracesJapaneseConnectionString);

            ConnectionGJ.Open();

            using (SQLiteTransaction Transaction = Connection.BeginTransaction())
                using (SQLiteCommand Command = new SQLiteCommand(Connection))
                    using (SQLiteTransaction TransactionGJ = ConnectionGJ.BeginTransaction())
                        using (SQLiteCommand CommandGJ = new SQLiteCommand(ConnectionGJ)) {
                            Command.CommandText = "SELECT english, ID, StringID, status, comment, updated, PointerRef, IdentifyString, IdentifyPointerRef, UpdatedBy, UpdatedTimestamp " +
                                                  "FROM Text ORDER BY ID";
                            CommandGJ.CommandText = "SELECT string FROM Japanese WHERE ID = ?";
                            SQLiteParameter StringIdParam = new SQLiteParameter();
                            CommandGJ.Parameters.Add(StringIdParam);

                            SQLiteDataReader r = Command.ExecuteReader();
                            while (r.Read())
                            {
                                String SQLText;

                                try {
                                    SQLText = r.GetString(0).Replace("''", "'");
                                } catch (System.InvalidCastException) {
                                    SQLText = null;
                                }

                                int    ID       = r.GetInt32(1);
                                int    StringID = r.GetInt32(2);
                                int    Status   = r.GetInt32(3);
                                String Comment;
                                try {
                                    Comment = r.GetString(4);
                                } catch (System.InvalidCastException) {
                                    Comment = null;
                                }
                                int    Updated    = r.GetInt32(5);
                                int    PointerRef = r.GetInt32(6);
                                String IdentifyString;
                                try {
                                    IdentifyString = r.GetString(7);
                                } catch (System.InvalidCastException) {
                                    IdentifyString = null;
                                }

                                int IdentifyPointerRef;
                                try {
                                    IdentifyPointerRef = r.GetInt32(8);
                                } catch (System.InvalidCastException) {
                                    IdentifyPointerRef = 0;
                                }

                                String UpdatedBy;
                                try {
                                    UpdatedBy = r.GetString(9);
                                } catch (System.InvalidCastException) {
                                    UpdatedBy = null;
                                }

                                int UpdatedTimestamp;
                                try {
                                    UpdatedTimestamp = r.GetInt32(10);
                                } catch (System.InvalidCastException) {
                                    UpdatedTimestamp = 0;
                                }


                                StringIdParam.Value = StringID;
                                String JPText;
                                try {
                                    JPText = ((string)CommandGJ.ExecuteScalar()).Replace("''", "'");
                                } catch (System.InvalidCastException) {
                                    JPText = null;
                                }


                                GraceNoteDatabaseEntry de = new GraceNoteDatabaseEntry();
                                de.TextEN             = SQLText;
                                de.TextJP             = JPText;
                                de.ID                 = ID;
                                de.JPID               = StringID;
                                de.Status             = Status;
                                de.Comment            = Comment;
                                de.PointerRef         = PointerRef;
                                de.IdentifyString     = IdentifyString;
                                de.IdentifyPointerRef = IdentifyPointerRef;
                                de.UpdatedBy          = UpdatedBy;
                                de.UpdatedTimestamp   = UpdatedTimestamp;
                                de.Database           = ConnectionString;

                                Entries.Add(de);
                            }

                            Transaction.Rollback();
                            TransactionGJ.Rollback();
                        }

            ConnectionGJ.Close();
            Connection.Close();

            return(Entries.ToArray());
        }