[chronojump] Sqlite transaction on inserting multiple person (FAST!)

[Xavier de Blas <xaviblas src gnome org>]

commit 54166588668caab6d38e9c2bb86818c00e467255
Author: Xavier de Blas <xaviblas gmail com>
Date:   Sat Nov 1 15:32:16 2014 +0100

    Sqlite transaction on inserting multiple person (FAST!)

 src/gui/person.cs           |   81 +++++++++++++++++++++++++------------------
 src/person.cs               |   12 ++++++-
 src/personSession.cs        |    9 ++++-
 src/sqlite/person.cs        |    3 ++
 src/sqlite/personSession.cs |   44 +++++++++++++++++++++++
 5 files changed, 113 insertions(+), 36 deletions(-)
---
diff --git a/src/gui/person.cs b/src/gui/person.cs
index 943f8a7..5a8395c 100644
--- a/src/gui/person.cs
+++ b/src/gui/person.cs
@@ -2193,7 +2193,7 @@ public class PersonAddMultipleWindow {
                if (combinedErrorString.Length > 0) {
                        ErrorWindow.Show(combinedErrorString);
                } else {
-                       prepareAllNonBlankRows();
+                       processAllNonBlankRows();
                
                        PersonAddMultipleWindowBox.person_multiple_infinite.Hide();
                        PersonAddMultipleWindowBox = null;
@@ -2250,45 +2250,58 @@ public class PersonAddMultipleWindow {
 
        //inserts all the rows where name is not blank
        //all this names doesn't match with other in the database, and the weights are > 0 ( checked in 
checkEntries() )
-       void prepareAllNonBlankRows() 
+       void processAllNonBlankRows() 
        {
-               Sqlite.Open();
-               //the last is the first for having the first value inserted as currentPerson
-               for (int i = rows -1; i >= 0; i --) 
-                       if(((Gtk.Entry)entries[i]).Text.ToString().Length > 0)
-                               insertPerson (
-                                               ((Gtk.Entry)entries[i]).Text.ToString(), 
-                                               ((Gtk.RadioButton)radiosM[i]).Active, 
-                                               (double) ((Gtk.SpinButton)spins[i]).Value);
-               Sqlite.Close();
-       }
-
-       private void insertPerson (string name, bool male, double weight) 
-       {
-               string sex = Constants.F;
-               if(male) { sex = Constants.M; }
+               int maxPUniqueID = Sqlite.Max(Constants.PersonTable, "uniqueID", false);
+               int pID = maxPUniqueID + 1;
+               int maxPSUniqueID = Sqlite.Max(Constants.PersonSessionTable, "uniqueID", false);
+               int psID = maxPSUniqueID + 1;
+               string sex = "";
+               double weight = 0;
+                               
+               List <Person> persons = new List<Person>();
+               List <PersonSession> personSessions = new List<PersonSession>();
 
-               //now dateTime is undefined until user changes it
                DateTime dateTime = DateTime.MinValue;
 
-               currentPerson = new Person ( name, sex, dateTime, 
-                               Constants.RaceUndefinedID,
-                               Constants.CountryUndefinedID,
-                               "",                     //description
-                               Constants.ServerUndefinedID, true); //dbconOpened
+               //the last is the first for having the first value inserted as currentPerson
+               for (int i = rows -1; i >= 0; i --) 
+                       if(((Gtk.Entry)entries[i]).Text.ToString().Length > 0) 
+                       {
+                               sex = Constants.F;
+                               if(((Gtk.RadioButton)radiosM[i]).Active) { sex = Constants.M; }
+
+                               currentPerson = new Person(
+                                                       pID ++,
+                                                       ((Gtk.Entry)entries[i]).Text.ToString(), //name
+                                                       sex,
+                                                       dateTime,
+                                                       Constants.RaceUndefinedID,
+                                                       Constants.CountryUndefinedID,
+                                                       "",                                     //description
+                                                       Constants.ServerUndefinedID
+                                                       );
                                
-
-               new PersonSession (
-                               currentPerson.UniqueID, currentSession.UniqueID, 
-                               0, weight,              //height, weight        
-                               currentSession.PersonsSportID,
-                               currentSession.PersonsSpeciallityID,
-                               currentSession.PersonsPractice,
-                               "", true);                      //comments, dbconOpened
-
-               personsCreatedCount ++;
-       }
+                               persons.Add(currentPerson);
+                                               
+                               weight = (double) ((Gtk.SpinButton)spins[i]).Value;
+                               personSessions.Add(new PersonSession(
+                                                       psID ++,
+                                                       currentPerson.UniqueID, currentSession.UniqueID, 
+                                                       0, weight,              //height, weight        
+                                                       currentSession.PersonsSportID,
+                                                       currentSession.PersonsSpeciallityID,
+                                                       currentSession.PersonsPractice,
+                                                       "")                     //comments
+                                               );
+
+                               personsCreatedCount ++;
+                       }
        
+               //do the transaction    
+               SqlitePersonSessionTransaction psTr = new SqlitePersonSessionTransaction(persons, 
personSessions);
+       }
+
        public Button Button_accept 
        {
                set {
diff --git a/src/person.cs b/src/person.cs
index de0bf4e..a5066db 100644
--- a/src/person.cs
+++ b/src/person.cs
@@ -45,6 +45,7 @@ public partial class Person {
 
        //suitable when we load a person from the database for being the current Person
        //we know uniqueID
+       //used also in class PersonSessionTransaction where we define the uniqueID 
        public Person(int uniqueID, string name, string sex, DateTime dateBorn, 
                       int race, int countryID, string description, int serverUniqueID) 
        {
@@ -61,7 +62,7 @@ public partial class Person {
                this.description = description;
                this.serverUniqueID = serverUniqueID; //remember don't do this on server
        }
-       
+
        //typical constructor
        //used when we create new person 
        //we don't know uniqueID
@@ -114,6 +115,15 @@ public partial class Person {
                return "[uniqueID: " + uniqueID + "]" + name + ", " + ", " + sex + ", " + 
dateBorn.ToShortDateString() + ", " + description;
        }
        
+       public string ToSQLInsertString()
+       {
+               return uniqueID.ToString() + ", '"  + name + "', '" + sex + "', '" + 
+                       UtilDate.ToSql(dateBorn) + "', " + race + ", " + countryID + ", '" +
+                       description + "', '', '', " +  //future1, future2
+                       serverUniqueID;
+       }
+       
+       
        public override bool Equals(object evalString)
        {
                return this.ToString() == evalString.ToString();
diff --git a/src/personSession.cs b/src/personSession.cs
index 6d8b7ca..fac8f27 100644
--- a/src/personSession.cs
+++ b/src/personSession.cs
@@ -99,6 +99,14 @@ public partial class PersonSession {
        {
                return "[uniqueID: " + uniqueID + "]," + personID + ", " + ", " + sessionID + ", " + height + 
", " + weight + ", " + sportID + ", " + speciallityID + ", " + practice + ", " + comments;
        }
+       
+       public string ToSQLInsertString()
+       {
+               return uniqueID.ToString() + ", " + personID + ", " + sessionID + ", " + 
+                       Util.ConvertToPoint(height) + ", " + Util.ConvertToPoint(weight) + ", " +
+                       sportID + ", " + speciallityID + ", " + practice + ", '" + 
+                       comments + "', '', ''"; 
+       }
 
        //some "set"s are needed. If not data of personSession does not arrive to the server
 
@@ -151,4 +159,3 @@ public partial class PersonSession {
        ~PersonSession() {}
           
 }
-
diff --git a/src/sqlite/person.cs b/src/sqlite/person.cs
index f3ef70f..f640602 100644
--- a/src/sqlite/person.cs
+++ b/src/sqlite/person.cs
@@ -62,6 +62,9 @@ class SqlitePerson : Sqlite
                if(uniqueID == "-1")
                        uniqueID = "NULL";
 
+               // -----------------------
+               //ATTENTION: if this changes, change the Person.ToSQLInsertString()
+               // -----------------------
                string myString = "INSERT INTO " + Constants.PersonTable + 
                        " (uniqueID, name, sex, dateBorn, race, countryID, description, future1, future2, 
serverUniqueID) VALUES (" + uniqueID + ", '" +
                        name + "', '" + sex + "', '" + UtilDate.ToSql(dateBorn) + "', " + 
diff --git a/src/sqlite/personSession.cs b/src/sqlite/personSession.cs
index 1ac76ed..8bf7baa 100644
--- a/src/sqlite/personSession.cs
+++ b/src/sqlite/personSession.cs
@@ -24,6 +24,7 @@ using System.IO;
 using System.Collections; //ArrayList
 using Mono.Data.Sqlite;
 using Mono.Unix;
+using System.Collections.Generic; //List<T>
 
 
 class SqlitePersonSession : Sqlite
@@ -61,6 +62,9 @@ class SqlitePersonSession : Sqlite
                if(uniqueID == "-1")
                        uniqueID = "NULL";
 
+               // -----------------------
+               //ATTENTION: if this changes, change the PersonSession.ToSQLInsertString()
+               // -----------------------
                dbcmd.CommandText = "INSERT INTO " + Constants.PersonSessionTable + 
                        "(uniqueID, personID, sessionID, height, weight, " + 
                        "sportID, speciallityID, practice, comments, future1, future2)" + 
@@ -487,3 +491,43 @@ class SqlitePersonSession : Sqlite
 
 }
 
+
+//used to insert person and personSession in a single translation
+class SqlitePersonSessionTransaction : Sqlite
+{
+       public SqlitePersonSessionTransaction(List <Person> persons, List <PersonSession> personSessions) 
+       {
+               Log.WriteLine("Starting transaction");
+               Sqlite.Open();
+
+               using(SqliteTransaction tr = dbcon.BeginTransaction())
+               {
+                       using (SqliteCommand dbcmdTr = dbcon.CreateCommand())
+                       {
+                               dbcmdTr.Transaction = tr;
+                               
+                               foreach(Person p in persons) {
+                                       dbcmdTr.CommandText = 
+                                               "INSERT INTO " + Constants.PersonTable +
+                                               " (uniqueID, name, sex, dateBorn, race, countryID, 
description, future1, future2, serverUniqueID) " + 
+                                               " VALUES (" + p.ToSQLInsertString() + ")";
+                                       Log.WriteLine(dbcmdTr.CommandText.ToString());
+                                       dbcmdTr.ExecuteNonQuery();
+                               }
+                               foreach(PersonSession ps in personSessions) {
+                                       dbcmdTr.CommandText = 
+                                               "INSERT INTO " + Constants.PersonSessionTable +
+                                               "(uniqueID, personID, sessionID, height, weight, " + 
+                                               "sportID, speciallityID, practice, comments, future1, 
future2)" + 
+                                               " VALUES (" + ps.ToSQLInsertString() + ")";
+                                       Log.WriteLine(dbcmdTr.CommandText.ToString());
+                                       dbcmdTr.ExecuteNonQuery();
+                               }
+                       }
+                       tr.Commit();
+               }
+
+               Sqlite.Close();
+               Log.WriteLine("Ended transaction");
+       }
+}