[chronojump] Transactions on creation of Sport and Speciallity. First boot reduced from 23s to 15

[Xavier de Blas <xaviblas src gnome org>]

commit 9a53b516b8701e1f3a1a117fc4659e9d0da58802
Author: Xavier de Blas <xaviblas gmail com>
Date:   Sun Feb 15 23:44:43 2015 +0100

    Transactions on creation of Sport and Speciallity. First boot reduced from 23s to 15

 src/sqlite/speciallity.cs |   35 +++++++++++++++++++++++------------
 src/sqlite/sport.cs       |   41 ++++++++++++++++++++++++++++++-----------
 2 files changed, 53 insertions(+), 23 deletions(-)
---
diff --git a/src/sqlite/speciallity.cs b/src/sqlite/speciallity.cs
index 3ddbe49..64431b8 100644
--- a/src/sqlite/speciallity.cs
+++ b/src/sqlite/speciallity.cs
@@ -45,17 +45,27 @@ class SqliteSpeciallity : Sqlite
        {
                conversionSubRateTotal = Speciallities.Length;
                conversionSubRate = 0;
-               foreach(string myString in Speciallities) {
-                       string [] strFull = myString.Split(new char[] {':'});
-                       string sportName = strFull[0];
-                       string speciallityEnglishName = strFull[1];
-                       int sportID = SqliteSport.SelectID(sportName);
-                       Insert(true, sportID, speciallityEnglishName);
-                       conversionSubRate ++;
+
+               using(SqliteTransaction tr = dbcon.BeginTransaction())
+               {
+                       using (SqliteCommand dbcmdTr = dbcon.CreateCommand())
+                       {
+                               dbcmdTr.Transaction = tr;
+       
+                               foreach(string myString in Speciallities) {
+                                       string [] strFull = myString.Split(new char[] {':'});
+                                       string sportName = strFull[0];
+                                       string speciallityEnglishName = strFull[1];
+                                       int sportID = SqliteSport.SelectID(sportName);
+                                       Insert(true, dbcmdTr, sportID, speciallityEnglishName);
+                                       conversionSubRate ++;
+                               }
+                       }
+                       tr.Commit();
                }
        }
 
-       public static int Insert(bool dbconOpened, int sportID, string speciallityName)
+       public static int Insert(bool dbconOpened, SqliteCommand mycmd, int sportID, string speciallityName)
        {
                if(! dbconOpened)
                        Sqlite.Open();
@@ -63,14 +73,15 @@ class SqliteSpeciallity : Sqlite
                string myString = "INSERT INTO " + Constants.SpeciallityTable + 
                        " (uniqueID, sportID, name) VALUES (NULL, " + sportID + ", '" + speciallityName + 
"')"; 
                
-               dbcmd.CommandText = myString;
-               dbcmd.ExecuteNonQuery();
+               mycmd.CommandText = myString;
+               LogB.SQL(mycmd.CommandText.ToString());
+               mycmd.ExecuteNonQuery();
 
                //int myLast = dbcon.LastInsertRowId;
                //http://stackoverflow.com/questions/4341178/getting-the-last-insert-id-with-sqlite-net-in-c
                myString = @"select last_insert_rowid()";
-               dbcmd.CommandText = myString;
-               int myLast = Convert.ToInt32(dbcmd.ExecuteScalar()); // Need to type-cast since 
`ExecuteScalar` returns an object.
+               mycmd.CommandText = myString;
+               int myLast = Convert.ToInt32(mycmd.ExecuteScalar()); // Need to type-cast since 
`ExecuteScalar` returns an object.
                
                if(! dbconOpened)
                        Sqlite.Close();
diff --git a/src/sqlite/sport.cs b/src/sqlite/sport.cs
index a055a76..8ca2417 100644
--- a/src/sqlite/sport.cs
+++ b/src/sqlite/sport.cs
@@ -46,18 +46,36 @@ class SqliteSport : Sqlite
        {
                conversionSubRateTotal = sportsChronojump.Length;
                conversionSubRate = 0;
-               foreach(string sportString in sportsChronojump) {
-                       //put in db only english name
-                       string [] sportFull = sportString.Split(new char[] {':'});
-                       //Sport sport = new Sport(sportFull[0]);
-                       Insert(true, "-1", sportFull[0], false,                 //dbconOpened, not user 
defined 
-                                       Util.StringToBool(sportFull[2]), sportFull[3]); //hasSpeciallities, 
graphLink
-                       conversionSubRate ++;
+
+               using(SqliteTransaction tr = dbcon.BeginTransaction())
+               {
+                       using (SqliteCommand dbcmdTr = dbcon.CreateCommand())
+                       {
+                               dbcmdTr.Transaction = tr;
+       
+                               foreach(string sportString in sportsChronojump) {
+                                       //put in db only english name
+                                       string [] sportFull = sportString.Split(new char[] {':'});
+                                       //Sport sport = new Sport(sportFull[0]);
+                                       Insert(true, dbcmdTr, "-1", sportFull[0], false,                
//dbconOpened, not user defined 
+                                                       Util.StringToBool(sportFull[2]), sportFull[3]); 
//hasSpeciallities, graphLink
+                                       conversionSubRate ++;
+                               }
+                       }
+                       tr.Commit();
                }
        }
 
+       //called from some Chronojump methods
+       //adds dbcmd to be used on next Insert method
        public static int Insert(bool dbconOpened, string uniqueID, string name, bool userDefined, bool 
hasSpeciallities, string graphLink)
        {
+               return Insert(dbconOpened, dbcmd, uniqueID, name, userDefined, hasSpeciallities, graphLink);
+
+       }
+       //Called from initialize
+       public static int Insert(bool dbconOpened, SqliteCommand mycmd, string uniqueID, string name, bool 
userDefined, bool hasSpeciallities, string graphLink)
+       {
                if(! dbconOpened)
                        Sqlite.Open();
 
@@ -68,15 +86,16 @@ class SqliteSport : Sqlite
                        " (uniqueID, name, userDefined, hasSpeciallities, graphLink) VALUES (" + uniqueID + 
", '" + name + "', " + 
                        Util.BoolToInt(userDefined) + ", " + Util.BoolToInt(hasSpeciallities) + ", '" + 
graphLink + "')";
                
-               dbcmd.CommandText = myString;
-               dbcmd.ExecuteNonQuery();
+               mycmd.CommandText = myString;
+               LogB.SQL(mycmd.CommandText.ToString());
+               mycmd.ExecuteNonQuery();
                
 
                //int myLast = dbcon.LastInsertRowId;
                //http://stackoverflow.com/questions/4341178/getting-the-last-insert-id-with-sqlite-net-in-c
                myString = @"select last_insert_rowid()";
-               dbcmd.CommandText = myString;
-               int myLast = Convert.ToInt32(dbcmd.ExecuteScalar()); // Need to type-cast since 
`ExecuteScalar` returns an object.
+               mycmd.CommandText = myString;
+               int myLast = Convert.ToInt32(mycmd.ExecuteScalar()); // Need to type-cast since 
`ExecuteScalar` returns an object.
                
                if(! dbconOpened)
                        Sqlite.Close();