/dotnet/Sync/LocalDb.cs
C# | 147 lines | 115 code | 32 blank | 0 comment | 9 complexity | cad3ad5880c45cfc8bb7a74acf7792a0 MD5 | raw file
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}