/dotnet/Sync/LocalDb.cs

  1using System;
  2using System.Collections.Generic;
  3using System.Linq;
  4using System.Text;
  5
  6using System.Data.SQLite;
  7
  8using ActivityInfo.Api;
  9using System.Windows.Forms;
 10
 11namespace ActivityInfo.Sync
 12{
 13    class LocalDb
 14    {
 15        private ActivityInfoService service;
 16        private SQLiteConnection connection;
 17
 18        private List<SyncRegion>.Enumerator regionEnum;
 19        private bool currentRegionIsComplete = true;
 20
 21        public LocalDb(ActivityInfoService service)
 22        {
 23            this.service = service;
 24
 25            var builder = new SQLiteConnectionStringBuilder();
 26            builder.DataSource = Application.UserAppDataPath + "\\ActivityInfo.db";
 27
 28            connection = new SQLiteConnection(builder.ToString());
 29            connection.Open();
 30
 31            executeSQL("create table if not exists sync_regions (id TEXT, localVersion INTEGER)");
 32            executeSQL("create table if not exists sync_history (lastUpdate INTEGER)");
 33
 34            regionEnum = service.GetSyncRegions().List.GetEnumerator();
 35        }
 36
 37        public SQLiteConnection Connection
 38        {
 39            get
 40            {
 41                return connection;
 42            }
 43        }
 44
 45        public bool NextSyncStep(SyncLogger logger)
 46        {
 47            if (currentRegionIsComplete)
 48            {
 49                if (!regionEnum.MoveNext())
 50                {
 51                    return false;
 52                }
 53            }
 54
 55            SyncRegion region = regionEnum.Current;
 56            string localVersion = queryLocalVersion(region.Id);
 57
 58            logger.log(1, "Requesting updates for region '" + region.Id + "', localVersion = " + localVersion);
 59
 60            SyncRegionUpdates updates;
 61
 62           
 63            updates = service.GetSyncRegionUpdates(region.Id, localVersion);
 64
 65            if (updates.Sql != null)
 66            {
 67                foreach (SyncRegionUpdate update in updates.Sql)
 68                {
 69                    executeUpdate(logger, update);
 70                }
 71            }
 72
 73            updateLocalVersion(region.Id, updates.Version);
 74            currentRegionIsComplete = updates.Complete;
 75
 76
 77            logger.log(1, "    now at version " + updates.Version + " [complete=" + updates.Complete + "]");
 78
 79            return true;
 80        }
 81        
 82
 83        private int executeUpdate(SyncLogger logger, SyncRegionUpdate update)
 84        {
 85            logger.log(2, update.Statement);
 86
 87            SQLiteCommand cmd = new SQLiteCommand(connection);
 88            cmd.CommandText = update.Statement;
 89
 90            if (update.Executions == null)
 91            {
 92                return cmd.ExecuteNonQuery();
 93            }
 94            else
 95            {
 96                int rowsAffected = 0;
 97                foreach (ParameterSet set in update.Executions)
 98                {
 99                    logger.log(3, string.Join(",", set));
100
101                    cmd.Parameters.Clear();
102                    foreach (string param in set)
103                    {
104                        SQLiteParameter p = new SQLiteParameter();
105                        p.Value = param;
106
107                        cmd.Parameters.Add(p);
108                    }
109                    rowsAffected += cmd.ExecuteNonQuery();
110                }
111                return rowsAffected;
112            }
113        }
114
115        private int executeSQL(String sql)
116        {
117            SQLiteCommand cmd = new SQLiteCommand(sql, connection);
118            return cmd.ExecuteNonQuery();
119        }
120
121        private String queryLocalVersion(String regionId)
122        {
123            SQLiteCommand cmd = new SQLiteCommand(connection);
124            cmd.CommandText = "select localVersion from sync_regions where id = :id";
125            cmd.Parameters.Add(new SQLiteParameter("id", regionId));
126
127            object result = cmd.ExecuteScalar();
128            return result == null ? null : result.ToString();
129        }
130
131        private void updateLocalVersion(string regionId, string localVersion)
132        {
133            SQLiteCommand cmd = new SQLiteCommand(connection);
134            cmd.CommandText = "update sync_regions set localVersion = :localVersion where id = :id";
135            cmd.Parameters.Add(new SQLiteParameter("localVersion", localVersion));
136            cmd.Parameters.Add(new SQLiteParameter("id", regionId));
137
138            if(cmd.ExecuteNonQuery() == 0) {
139                cmd = new SQLiteCommand(connection);
140                cmd.CommandText = "insert into sync_regions (id, localVersion) values (:id, :localVersion)";
141                cmd.Parameters.Add(new SQLiteParameter("id", regionId));
142                cmd.Parameters.Add(new SQLiteParameter("localVersion", localVersion));
143                cmd.ExecuteNonQuery();
144            }
145        }
146    }  
147}