Details | Last modification | View Log | RSS feed
| Rev | Author | Line No. | Line |
|---|---|---|---|
| 2498 | - | 1 | |
| 2 | namespace GMap.NET.CacheProviders |
||
| 3 | { |
||
| 4 | #if PostgreSQL |
||
| 5 | using System; |
||
| 6 | using System.Diagnostics; |
||
| 7 | using System.IO; |
||
| 8 | using Npgsql; |
||
| 9 | using NpgsqlTypes; |
||
| 10 | using GMap.NET.MapProviders; |
||
| 11 | |||
| 12 | /// <summary> |
||
| 13 | /// image cache for postgresql server |
||
| 14 | /// </summary> |
||
| 15 | public class PostgreSQLPureImageCache : PureImageCache, IDisposable |
||
| 16 | { |
||
| 17 | string connectionString = string.Empty; |
||
| 18 | public string ConnectionString |
||
| 19 | { |
||
| 20 | get |
||
| 21 | { |
||
| 22 | return connectionString; |
||
| 23 | } |
||
| 24 | set |
||
| 25 | { |
||
| 26 | if(connectionString != value) |
||
| 27 | { |
||
| 28 | connectionString = value; |
||
| 29 | |||
| 30 | if(Initialized) |
||
| 31 | { |
||
| 32 | Dispose(); |
||
| 33 | Initialize(); |
||
| 34 | } |
||
| 35 | } |
||
| 36 | } |
||
| 37 | } |
||
| 38 | |||
| 39 | NpgsqlCommand cmdInsert; |
||
| 40 | NpgsqlCommand cmdFetch; |
||
| 41 | NpgsqlConnection cnGet; |
||
| 42 | NpgsqlConnection cnSet; |
||
| 43 | |||
| 44 | bool initialized = false; |
||
| 45 | |||
| 46 | /// <summary> |
||
| 47 | /// is cache initialized |
||
| 48 | /// </summary> |
||
| 49 | public bool Initialized |
||
| 50 | { |
||
| 51 | get |
||
| 52 | { |
||
| 53 | lock(this) |
||
| 54 | { |
||
| 55 | return initialized; |
||
| 56 | } |
||
| 57 | } |
||
| 58 | private set |
||
| 59 | { |
||
| 60 | lock(this) |
||
| 61 | { |
||
| 62 | initialized = value; |
||
| 63 | } |
||
| 64 | } |
||
| 65 | } |
||
| 66 | |||
| 67 | /// <summary> |
||
| 68 | /// inits connection to server |
||
| 69 | /// </summary> |
||
| 70 | /// <returns></returns> |
||
| 71 | public bool Initialize() |
||
| 72 | { |
||
| 73 | lock(this) |
||
| 74 | { |
||
| 75 | if(!Initialized) |
||
| 76 | { |
||
| 77 | #region prepare postgresql & cache table |
||
| 78 | |||
| 79 | try |
||
| 80 | { |
||
| 81 | // different connections so the multi-thread inserts and selects don't collide on open readers. |
||
| 82 | this.cnGet = new NpgsqlConnection(connectionString); |
||
| 83 | this.cnGet.Open(); |
||
| 84 | this.cnSet = new NpgsqlConnection(connectionString); |
||
| 85 | this.cnSet.Open(); |
||
| 86 | |||
| 87 | bool tableexists = false; |
||
| 88 | using(NpgsqlCommand cmd = new NpgsqlCommand()) |
||
| 89 | { |
||
| 90 | cmd.CommandText = "SELECT COUNT(*) FROM information_schema.tables WHERE table_name='GMapNETcache'"; |
||
| 91 | cmd.Connection = cnGet; |
||
| 92 | object cnt = cmd.ExecuteScalar(); |
||
| 93 | tableexists = ((long)cnt == 1); |
||
| 94 | } |
||
| 95 | |||
| 96 | if(!tableexists) |
||
| 97 | { |
||
| 98 | using(NpgsqlCommand cmd = new NpgsqlCommand()) |
||
| 99 | { |
||
| 100 | cmd.Connection = cnGet; |
||
| 101 | |||
| 102 | // create tile-cache table |
||
| 103 | cmd.CommandText = "CREATE TABLE \"GMapNETcache\" ( \n" |
||
| 104 | + " \"Type\" integer NOT NULL, \n" |
||
| 105 | + " \"Zoom\" integer NOT NULL, \n" |
||
| 106 | + " \"X\" integer NOT NULL, \n" |
||
| 107 | + " \"Y\" integer NOT NULL, \n" |
||
| 108 | + " \"Tile\" bytea NOT NULL, \n" |
||
| 109 | + " CONSTRAINT \"PK_GMapNETcache\" PRIMARY KEY ( \"Type\", \"Zoom\", \"X\", \"Y\" ) )"; |
||
| 110 | cmd.ExecuteNonQuery(); |
||
| 111 | |||
| 112 | // allows out-of-line storage but not compression of tile data |
||
| 113 | // see http://www.postgresql.org/docs/9.0/static/storage-toast.html |
||
| 114 | cmd.CommandText = "ALTER TABLE \"GMapNETcache\" \n" |
||
| 115 | + " ALTER COLUMN \"Tile\" SET STORAGE EXTERNAL"; |
||
| 116 | cmd.ExecuteNonQuery(); |
||
| 117 | |||
| 118 | // select pk index for cluster operations |
||
| 119 | cmd.CommandText = "ALTER TABLE \"GMapNETcache\" \n" |
||
| 120 | + " CLUSTER ON \"PK_GMapNETcache\""; |
||
| 121 | cmd.ExecuteNonQuery(); |
||
| 122 | } |
||
| 123 | } |
||
| 124 | |||
| 125 | this.cmdFetch = new NpgsqlCommand("SELECT \"Tile\" FROM \"GMapNETcache\" WHERE \"X\"=@x AND \"Y\"=@y AND \"Zoom\"=@zoom AND \"Type\"=@type", cnGet); |
||
| 126 | this.cmdFetch.Parameters.Add("@x", NpgsqlDbType.Integer); |
||
| 127 | this.cmdFetch.Parameters.Add("@y", NpgsqlDbType.Integer); |
||
| 128 | this.cmdFetch.Parameters.Add("@zoom", NpgsqlDbType.Integer); |
||
| 129 | this.cmdFetch.Parameters.Add("@type", NpgsqlDbType.Integer); |
||
| 130 | this.cmdFetch.Prepare(); |
||
| 131 | |||
| 132 | this.cmdInsert = new NpgsqlCommand("INSERT INTO \"GMapNETcache\" ( \"X\", \"Y\", \"Zoom\", \"Type\", \"Tile\" ) VALUES ( @x, @y, @zoom, @type, @tile )", cnSet); |
||
| 133 | this.cmdInsert.Parameters.Add("@x", NpgsqlDbType.Integer); |
||
| 134 | this.cmdInsert.Parameters.Add("@y", NpgsqlDbType.Integer); |
||
| 135 | this.cmdInsert.Parameters.Add("@zoom", NpgsqlDbType.Integer); |
||
| 136 | this.cmdInsert.Parameters.Add("@type", NpgsqlDbType.Integer); |
||
| 137 | this.cmdInsert.Parameters.Add("@tile", NpgsqlDbType.Bytea); |
||
| 138 | this.cmdInsert.Prepare(); |
||
| 139 | |||
| 140 | Initialized = true; |
||
| 141 | } |
||
| 142 | catch(Exception ex) |
||
| 143 | { |
||
| 144 | this.initialized = false; |
||
| 145 | Debug.WriteLine(ex.Message); |
||
| 146 | } |
||
| 147 | |||
| 148 | #endregion |
||
| 149 | } |
||
| 150 | |||
| 151 | return Initialized; |
||
| 152 | } |
||
| 153 | } |
||
| 154 | |||
| 155 | #region IDisposable Members |
||
| 156 | |||
| 157 | public void Dispose() |
||
| 158 | { |
||
| 159 | lock(cmdInsert) |
||
| 160 | { |
||
| 161 | if(cmdInsert != null) |
||
| 162 | { |
||
| 163 | cmdInsert.Dispose(); |
||
| 164 | cmdInsert = null; |
||
| 165 | } |
||
| 166 | |||
| 167 | if(cnSet != null) |
||
| 168 | { |
||
| 169 | cnSet.Dispose(); |
||
| 170 | cnSet = null; |
||
| 171 | } |
||
| 172 | } |
||
| 173 | |||
| 174 | lock(cmdFetch) |
||
| 175 | { |
||
| 176 | if(cmdFetch != null) |
||
| 177 | { |
||
| 178 | cmdFetch.Dispose(); |
||
| 179 | cmdFetch = null; |
||
| 180 | } |
||
| 181 | |||
| 182 | if(cnGet != null) |
||
| 183 | { |
||
| 184 | cnGet.Dispose(); |
||
| 185 | cnGet = null; |
||
| 186 | } |
||
| 187 | } |
||
| 188 | |||
| 189 | Initialized = false; |
||
| 190 | } |
||
| 191 | |||
| 192 | #endregion |
||
| 193 | |||
| 194 | #region PureImageCache Members |
||
| 195 | |||
| 196 | public bool PutImageToCache(byte[] tile, int type, GPoint pos, int zoom) |
||
| 197 | { |
||
| 198 | bool ret = true; |
||
| 199 | |||
| 200 | if(Initialize()) |
||
| 201 | { |
||
| 202 | try |
||
| 203 | { |
||
| 204 | lock(cmdInsert) |
||
| 205 | { |
||
| 206 | cmdInsert.Parameters["@x"].Value = pos.X; |
||
| 207 | cmdInsert.Parameters["@y"].Value = pos.Y; |
||
| 208 | cmdInsert.Parameters["@zoom"].Value = zoom; |
||
| 209 | cmdInsert.Parameters["@type"].Value = type; |
||
| 210 | cmdInsert.Parameters["@tile"].Value = tile; |
||
| 211 | cmdInsert.ExecuteNonQuery(); |
||
| 212 | } |
||
| 213 | } |
||
| 214 | catch(Exception ex) |
||
| 215 | { |
||
| 216 | Debug.WriteLine(ex.ToString()); |
||
| 217 | ret = false; |
||
| 218 | Dispose(); |
||
| 219 | } |
||
| 220 | } |
||
| 221 | |||
| 222 | return ret; |
||
| 223 | } |
||
| 224 | |||
| 225 | public PureImage GetImageFromCache(int type, GPoint pos, int zoom) |
||
| 226 | { |
||
| 227 | PureImage ret = null; |
||
| 228 | |||
| 229 | if(Initialize()) |
||
| 230 | { |
||
| 231 | try |
||
| 232 | { |
||
| 233 | object odata = null; |
||
| 234 | lock(cmdFetch) |
||
| 235 | { |
||
| 236 | cmdFetch.Parameters["@x"].Value = pos.X; |
||
| 237 | cmdFetch.Parameters["@y"].Value = pos.Y; |
||
| 238 | cmdFetch.Parameters["@zoom"].Value = zoom; |
||
| 239 | cmdFetch.Parameters["@type"].Value = type; |
||
| 240 | odata = cmdFetch.ExecuteScalar(); |
||
| 241 | } |
||
| 242 | |||
| 243 | if(odata != null && odata != DBNull.Value) |
||
| 244 | { |
||
| 245 | byte[] tile = (byte[])odata; |
||
| 246 | if(tile != null && tile.Length > 0) |
||
| 247 | { |
||
| 248 | if(GMapProvider.TileImageProxy != null) |
||
| 249 | { |
||
| 250 | ret = GMapProvider.TileImageProxy.FromArray(tile); |
||
| 251 | } |
||
| 252 | } |
||
| 253 | tile = null; |
||
| 254 | } |
||
| 255 | } |
||
| 256 | catch(Exception ex) |
||
| 257 | { |
||
| 258 | Debug.WriteLine(ex.ToString()); |
||
| 259 | ret = null; |
||
| 260 | Dispose(); |
||
| 261 | } |
||
| 262 | } |
||
| 263 | |||
| 264 | return ret; |
||
| 265 | } |
||
| 266 | |||
| 267 | int PureImageCache.DeleteOlderThan(DateTime date, int ? type) |
||
| 268 | { |
||
| 269 | throw new NotImplementedException(); |
||
| 270 | } |
||
| 271 | |||
| 272 | #endregion |
||
| 273 | } |
||
| 274 | #endif |
||
| 275 | } |