Details | Last modification | View Log | RSS feed
Rev | Author | Line No. | Line |
---|---|---|---|
2498 | - | 1 | |
2 | namespace GMap.NET.CacheProviders |
||
3 | { |
||
4 | #if SQLite |
||
5 | |||
6 | using System.Collections.Generic; |
||
7 | using System.Data.Common; |
||
8 | using System.IO; |
||
9 | using System.Text; |
||
10 | using System; |
||
11 | using System.Diagnostics; |
||
12 | using System.Globalization; |
||
13 | using GMap.NET.MapProviders; |
||
14 | using System.Threading; |
||
15 | |||
16 | #if !MONO |
||
17 | using System.Data.SQLite; |
||
18 | using GMap.NET.Internals; |
||
19 | #else |
||
20 | using SQLiteConnection = Mono.Data.Sqlite.SqliteConnection; |
||
21 | using SQLiteTransaction = Mono.Data.Sqlite.SqliteTransaction; |
||
22 | using SQLiteCommand = Mono.Data.Sqlite.SqliteCommand; |
||
23 | using SQLiteDataReader = Mono.Data.Sqlite.SqliteDataReader; |
||
24 | using SQLiteParameter = Mono.Data.Sqlite.SqliteParameter; |
||
25 | #endif |
||
26 | |||
27 | /// <summary> |
||
28 | /// ultra fast cache system for tiles |
||
29 | /// </summary> |
||
30 | public class SQLitePureImageCache : PureImageCache |
||
31 | { |
||
32 | #if !PocketPC |
||
33 | #if !MONO |
||
34 | static SQLitePureImageCache() |
||
35 | { |
||
36 | AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve); |
||
37 | } |
||
38 | |||
39 | static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args) |
||
40 | { |
||
41 | if(args.Name.StartsWith("System.Data.SQLite", StringComparison.OrdinalIgnoreCase)) |
||
42 | { |
||
43 | string appDataDir = CacheLocator.GetApplicationDataFolderPath(); |
||
44 | if(string.IsNullOrEmpty(appDataDir)) |
||
45 | { |
||
46 | return null; |
||
47 | } |
||
48 | |||
49 | string dllDir = appDataDir + "DllCache" + Path.DirectorySeparatorChar; |
||
50 | string dll = dllDir + "SQLite_v98_NET" + Environment.Version.Major + "_" + (IntPtr.Size == 8 ? "x64" : "x86") + Path.DirectorySeparatorChar + "System.Data.SQLite.DLL"; |
||
51 | if(!File.Exists(dll)) |
||
52 | { |
||
53 | string dir = Path.GetDirectoryName(dll); |
||
54 | if(!Directory.Exists(dir)) |
||
55 | { |
||
56 | Directory.CreateDirectory(dir); |
||
57 | } |
||
58 | |||
59 | Debug.WriteLine("Saving to DllCache: " + dll); |
||
60 | |||
61 | if(Environment.Version.Major == 2) |
||
62 | { |
||
63 | using(MemoryStream gzipDll = new MemoryStream((IntPtr.Size == 8 ? Properties.Resources.System_Data_SQLite_x64_NET2_dll : Properties.Resources.System_Data_SQLite_x86_NET2_dll))) |
||
64 | { |
||
65 | using(var gs = new System.IO.Compression.GZipStream(gzipDll, System.IO.Compression.CompressionMode.Decompress)) |
||
66 | { |
||
67 | using(MemoryStream exctDll = new MemoryStream()) |
||
68 | { |
||
69 | byte[] tmp = new byte[1024 * 256]; |
||
70 | int r = 0; |
||
71 | while((r = gs.Read(tmp, 0, tmp.Length)) > 0) |
||
72 | { |
||
73 | exctDll.Write(tmp, 0, r); |
||
74 | } |
||
75 | File.WriteAllBytes(dll, exctDll.ToArray()); |
||
76 | } |
||
77 | } |
||
78 | } |
||
79 | } |
||
80 | else if(Environment.Version.Major == 4) |
||
81 | { |
||
82 | using(MemoryStream gzipDll = new MemoryStream((IntPtr.Size == 8 ? Properties.Resources.System_Data_SQLite_x64_NET4_dll : Properties.Resources.System_Data_SQLite_x86_NET4_dll))) |
||
83 | { |
||
84 | using(var gs = new System.IO.Compression.GZipStream(gzipDll, System.IO.Compression.CompressionMode.Decompress)) |
||
85 | { |
||
86 | using(MemoryStream exctDll = new MemoryStream()) |
||
87 | { |
||
88 | byte[] tmp = new byte[1024 * 256]; |
||
89 | int r = 0; |
||
90 | while((r = gs.Read(tmp, 0, tmp.Length)) > 0) |
||
91 | { |
||
92 | exctDll.Write(tmp, 0, r); |
||
93 | } |
||
94 | File.WriteAllBytes(dll, exctDll.ToArray()); |
||
95 | } |
||
96 | } |
||
97 | } |
||
98 | } |
||
99 | } |
||
100 | |||
101 | Debug.WriteLine("Assembly.LoadFile: " + dll); |
||
102 | |||
103 | return System.Reflection.Assembly.LoadFile(dll); |
||
104 | } |
||
105 | return null; |
||
106 | } |
||
107 | |||
108 | static int ping = 0; |
||
109 | |||
110 | /// <summary> |
||
111 | /// triggers dynamic sqlite loading |
||
112 | /// </summary> |
||
113 | public static void Ping() |
||
114 | { |
||
115 | if (++ping == 1) |
||
116 | { |
||
117 | Trace.WriteLine("SQLiteVersion: " + SQLiteConnection.SQLiteVersion + " | " + SQLiteConnection.SQLiteSourceId + " | " + SQLiteConnection.DefineConstants); |
||
118 | } |
||
119 | } |
||
120 | #endif |
||
121 | #endif |
||
122 | |||
123 | string cache; |
||
124 | string gtileCache; |
||
125 | string dir; |
||
126 | string db; |
||
127 | bool Created = false; |
||
128 | |||
129 | public string GtileCache |
||
130 | { |
||
131 | get |
||
132 | { |
||
133 | return gtileCache; |
||
134 | } |
||
135 | } |
||
136 | |||
137 | /// <summary> |
||
138 | /// local cache location |
||
139 | /// </summary> |
||
140 | public string CacheLocation |
||
141 | { |
||
142 | get |
||
143 | { |
||
144 | return cache; |
||
145 | } |
||
146 | set |
||
147 | { |
||
148 | cache = value; |
||
149 | |||
150 | gtileCache = Path.Combine(cache, "TileDBv5") + Path.DirectorySeparatorChar; |
||
151 | |||
152 | dir = gtileCache + GMapProvider.LanguageStr + Path.DirectorySeparatorChar; |
||
153 | |||
154 | // precreate dir |
||
155 | if(!Directory.Exists(dir)) |
||
156 | { |
||
157 | Directory.CreateDirectory(dir); |
||
158 | } |
||
159 | |||
160 | #if !MONO |
||
161 | SQLiteConnection.ClearAllPools(); |
||
162 | #endif |
||
163 | // make empty db |
||
164 | { |
||
165 | db = dir + "Data.gmdb"; |
||
166 | |||
167 | if(!File.Exists(db)) |
||
168 | { |
||
169 | Created = CreateEmptyDB(db); |
||
170 | } |
||
171 | else |
||
172 | { |
||
173 | Created = AlterDBAddTimeColumn(db); |
||
174 | } |
||
175 | |||
176 | CheckPreAllocation(); |
||
177 | |||
178 | //var connBuilder = new SQLiteConnectionStringBuilder(); |
||
179 | //connBuilder.DataSource = "c:\filePath.db"; |
||
180 | //connBuilder.Version = 3; |
||
181 | //connBuilder.PageSize = 4096; |
||
182 | //connBuilder.JournalMode = SQLiteJournalModeEnum.Wal; |
||
183 | //connBuilder.Pooling = true; |
||
184 | //var x = connBuilder.ToString(); |
||
185 | #if !MONO |
||
186 | ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768;Pooling=True", db); //;Journal Mode=Wal |
||
187 | #else |
||
188 | ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768,Pooling=True", db); |
||
189 | #endif |
||
190 | } |
||
191 | |||
192 | // clear old attachments |
||
193 | AttachedCaches.Clear(); |
||
194 | RebuildFinnalSelect(); |
||
195 | |||
196 | // attach all databases from main cache location |
||
197 | #if !PocketPC |
||
198 | var dbs = Directory.GetFiles(dir, "*.gmdb", SearchOption.AllDirectories); |
||
199 | #else |
||
200 | var dbs = Directory.GetFiles(dir, "*.gmdb"); |
||
201 | #endif |
||
202 | foreach(var d in dbs) |
||
203 | { |
||
204 | if(d != db) |
||
205 | { |
||
206 | Attach(d); |
||
207 | } |
||
208 | } |
||
209 | } |
||
210 | } |
||
211 | |||
212 | /// <summary> |
||
213 | /// pre-allocate 32MB free space 'ahead' if needed, |
||
214 | /// decreases fragmentation |
||
215 | /// </summary> |
||
216 | void CheckPreAllocation() |
||
217 | { |
||
218 | { |
||
219 | byte[] pageSizeBytes = new byte[2]; |
||
220 | byte[] freePagesBytes = new byte[4]; |
||
221 | |||
222 | lock(this) |
||
223 | { |
||
224 | using(var dbf = File.Open(db, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) |
||
225 | { |
||
226 | dbf.Seek(16, SeekOrigin.Begin); |
||
227 | |||
228 | #if (!PocketPC && !MONO) |
||
229 | dbf.Lock(16, 2); |
||
230 | dbf.Read(pageSizeBytes, 0, 2); |
||
231 | dbf.Unlock(16, 2); |
||
232 | |||
233 | dbf.Seek(36, SeekOrigin.Begin); |
||
234 | |||
235 | dbf.Lock(36, 4); |
||
236 | dbf.Read(freePagesBytes, 0, 4); |
||
237 | dbf.Unlock(36, 4); |
||
238 | #else |
||
239 | dbf.Read(pageSizeBytes, 0, 2); |
||
240 | dbf.Seek(36, SeekOrigin.Begin); |
||
241 | dbf.Read(freePagesBytes, 0, 4); |
||
242 | #endif |
||
243 | |||
244 | dbf.Close(); |
||
245 | } |
||
246 | } |
||
247 | |||
248 | if(BitConverter.IsLittleEndian) |
||
249 | { |
||
250 | Array.Reverse(pageSizeBytes); |
||
251 | Array.Reverse(freePagesBytes); |
||
252 | } |
||
253 | UInt16 pageSize = BitConverter.ToUInt16(pageSizeBytes, 0); |
||
254 | UInt32 freePages = BitConverter.ToUInt32(freePagesBytes, 0); |
||
255 | |||
256 | var freeMB = (pageSize * freePages) / (1024.0 * 1024.0); |
||
257 | |||
258 | #if !PocketPC |
||
259 | int addSizeMB = 32; |
||
260 | int waitUntilMB = 4; |
||
261 | #else |
||
262 | int addSizeMB = 4; // reduce due to test in emulator |
||
263 | int waitUntilMB = 2; |
||
264 | #endif |
||
265 | |||
266 | Debug.WriteLine("FreePageSpace in cache: " + freeMB + "MB | " + freePages + " pages"); |
||
267 | |||
268 | if(freeMB <= waitUntilMB) |
||
269 | { |
||
270 | PreAllocateDB(db, addSizeMB); |
||
271 | } |
||
272 | } |
||
273 | } |
||
274 | |||
275 | #region -- import / export -- |
||
276 | public static bool CreateEmptyDB(string file) |
||
277 | { |
||
278 | bool ret = true; |
||
279 | |||
280 | try |
||
281 | { |
||
282 | string dir = Path.GetDirectoryName(file); |
||
283 | if(!Directory.Exists(dir)) |
||
284 | { |
||
285 | Directory.CreateDirectory(dir); |
||
286 | } |
||
287 | |||
288 | using(SQLiteConnection cn = new SQLiteConnection()) |
||
289 | { |
||
290 | #if !MONO |
||
291 | cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768", file); |
||
292 | #else |
||
293 | cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768", file); |
||
294 | #endif |
||
295 | cn.Open(); |
||
296 | { |
||
297 | using(DbTransaction tr = cn.BeginTransaction()) |
||
298 | { |
||
299 | try |
||
300 | { |
||
301 | using(DbCommand cmd = cn.CreateCommand()) |
||
302 | { |
||
303 | cmd.Transaction = tr; |
||
304 | #if !PocketPC |
||
305 | cmd.CommandText = Properties.Resources.CreateTileDb; |
||
306 | #else |
||
307 | cmd.CommandText = GMap.NET.WindowsMobile.Properties.Resources.CreateTileDb; |
||
308 | #endif |
||
309 | cmd.ExecuteNonQuery(); |
||
310 | } |
||
311 | tr.Commit(); |
||
312 | } |
||
313 | catch(Exception exx) |
||
314 | { |
||
315 | #if MONO |
||
316 | Console.WriteLine("CreateEmptyDB: " + exx.ToString()); |
||
317 | #endif |
||
318 | Debug.WriteLine("CreateEmptyDB: " + exx.ToString()); |
||
319 | |||
320 | tr.Rollback(); |
||
321 | ret = false; |
||
322 | } |
||
323 | } |
||
324 | cn.Close(); |
||
325 | } |
||
326 | } |
||
327 | } |
||
328 | catch(Exception ex) |
||
329 | { |
||
330 | #if MONO |
||
331 | Console.WriteLine("CreateEmptyDB: " + ex.ToString()); |
||
332 | #endif |
||
333 | Debug.WriteLine("CreateEmptyDB: " + ex.ToString()); |
||
334 | ret = false; |
||
335 | } |
||
336 | return ret; |
||
337 | } |
||
338 | |||
339 | public static bool PreAllocateDB(string file, int addSizeInMBytes) |
||
340 | { |
||
341 | bool ret = true; |
||
342 | |||
343 | try |
||
344 | { |
||
345 | Debug.WriteLine("PreAllocateDB: " + file + ", +" + addSizeInMBytes + "MB"); |
||
346 | |||
347 | using(SQLiteConnection cn = new SQLiteConnection()) |
||
348 | { |
||
349 | #if !MONO |
||
350 | cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768", file); |
||
351 | #else |
||
352 | cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768", file); |
||
353 | #endif |
||
354 | cn.Open(); |
||
355 | { |
||
356 | using(DbTransaction tr = cn.BeginTransaction()) |
||
357 | { |
||
358 | try |
||
359 | { |
||
360 | using(DbCommand cmd = cn.CreateCommand()) |
||
361 | { |
||
362 | cmd.Transaction = tr; |
||
363 | cmd.CommandText = string.Format("create table large (a); insert into large values (zeroblob({0})); drop table large;", addSizeInMBytes * 1024 * 1024); |
||
364 | cmd.ExecuteNonQuery(); |
||
365 | } |
||
366 | tr.Commit(); |
||
367 | } |
||
368 | catch(Exception exx) |
||
369 | { |
||
370 | #if MONO |
||
371 | Console.WriteLine("PreAllocateDB: " + exx.ToString()); |
||
372 | #endif |
||
373 | Debug.WriteLine("PreAllocateDB: " + exx.ToString()); |
||
374 | |||
375 | tr.Rollback(); |
||
376 | ret = false; |
||
377 | } |
||
378 | } |
||
379 | cn.Close(); |
||
380 | } |
||
381 | } |
||
382 | } |
||
383 | catch(Exception ex) |
||
384 | { |
||
385 | #if MONO |
||
386 | Console.WriteLine("PreAllocateDB: " + ex.ToString()); |
||
387 | #endif |
||
388 | Debug.WriteLine("PreAllocateDB: " + ex.ToString()); |
||
389 | ret = false; |
||
390 | } |
||
391 | return ret; |
||
392 | } |
||
393 | |||
394 | private static bool AlterDBAddTimeColumn(string file) |
||
395 | { |
||
396 | bool ret = true; |
||
397 | |||
398 | try |
||
399 | { |
||
400 | if(File.Exists(file)) |
||
401 | { |
||
402 | using(SQLiteConnection cn = new SQLiteConnection()) |
||
403 | { |
||
404 | #if !MONO |
||
405 | cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768;Pooling=True", file); |
||
406 | #else |
||
407 | cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768,Pooling=True", file); |
||
408 | #endif |
||
409 | cn.Open(); |
||
410 | { |
||
411 | using(DbTransaction tr = cn.BeginTransaction()) |
||
412 | { |
||
413 | bool? NoCacheTimeColumn = null; |
||
414 | |||
415 | try |
||
416 | { |
||
417 | using(DbCommand cmd = new SQLiteCommand("SELECT CacheTime FROM Tiles", cn)) |
||
418 | { |
||
419 | cmd.Transaction = tr; |
||
420 | |||
421 | using(DbDataReader rd = cmd.ExecuteReader()) |
||
422 | { |
||
423 | rd.Close(); |
||
424 | } |
||
425 | NoCacheTimeColumn = false; |
||
426 | } |
||
427 | } |
||
428 | catch(Exception ex) |
||
429 | { |
||
430 | if(ex.Message.Contains("no such column: CacheTime")) |
||
431 | { |
||
432 | NoCacheTimeColumn = true; |
||
433 | } |
||
434 | else |
||
435 | { |
||
436 | throw ex; |
||
437 | } |
||
438 | } |
||
439 | |||
440 | try |
||
441 | { |
||
442 | if(NoCacheTimeColumn.HasValue && NoCacheTimeColumn.Value) |
||
443 | { |
||
444 | using(DbCommand cmd = cn.CreateCommand()) |
||
445 | { |
||
446 | cmd.Transaction = tr; |
||
447 | |||
448 | cmd.CommandText = "ALTER TABLE Tiles ADD CacheTime DATETIME"; |
||
449 | |||
450 | cmd.ExecuteNonQuery(); |
||
451 | } |
||
452 | tr.Commit(); |
||
453 | NoCacheTimeColumn = false; |
||
454 | } |
||
455 | } |
||
456 | catch(Exception exx) |
||
457 | { |
||
458 | #if MONO |
||
459 | Console.WriteLine("AlterDBAddTimeColumn: " + exx.ToString()); |
||
460 | #endif |
||
461 | Debug.WriteLine("AlterDBAddTimeColumn: " + exx.ToString()); |
||
462 | |||
463 | tr.Rollback(); |
||
464 | ret = false; |
||
465 | } |
||
466 | } |
||
467 | cn.Close(); |
||
468 | } |
||
469 | } |
||
470 | } |
||
471 | else |
||
472 | { |
||
473 | ret = false; |
||
474 | } |
||
475 | } |
||
476 | catch(Exception ex) |
||
477 | { |
||
478 | #if MONO |
||
479 | Console.WriteLine("AlterDBAddTimeColumn: " + ex.ToString()); |
||
480 | #endif |
||
481 | Debug.WriteLine("AlterDBAddTimeColumn: " + ex.ToString()); |
||
482 | ret = false; |
||
483 | } |
||
484 | return ret; |
||
485 | } |
||
486 | |||
487 | public static bool VacuumDb(string file) |
||
488 | { |
||
489 | bool ret = true; |
||
490 | |||
491 | try |
||
492 | { |
||
493 | using(SQLiteConnection cn = new SQLiteConnection()) |
||
494 | { |
||
495 | #if !MONO |
||
496 | cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=True;Page Size=32768", file); |
||
497 | #else |
||
498 | cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", file); |
||
499 | #endif |
||
500 | cn.Open(); |
||
501 | { |
||
502 | using(DbCommand cmd = cn.CreateCommand()) |
||
503 | { |
||
504 | cmd.CommandText = "vacuum;"; |
||
505 | cmd.ExecuteNonQuery(); |
||
506 | } |
||
507 | cn.Close(); |
||
508 | } |
||
509 | } |
||
510 | } |
||
511 | catch(Exception ex) |
||
512 | { |
||
513 | Debug.WriteLine("VacuumDb: " + ex.ToString()); |
||
514 | ret = false; |
||
515 | } |
||
516 | return ret; |
||
517 | } |
||
518 | |||
519 | public static bool ExportMapDataToDB(string sourceFile, string destFile) |
||
520 | { |
||
521 | bool ret = true; |
||
522 | |||
523 | try |
||
524 | { |
||
525 | if(!File.Exists(destFile)) |
||
526 | { |
||
527 | ret = CreateEmptyDB(destFile); |
||
528 | } |
||
529 | |||
530 | if(ret) |
||
531 | { |
||
532 | using(SQLiteConnection cn1 = new SQLiteConnection()) |
||
533 | { |
||
534 | #if !MONO |
||
535 | cn1.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", sourceFile); |
||
536 | #else |
||
537 | cn1.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", sourceFile); |
||
538 | #endif |
||
539 | |||
540 | cn1.Open(); |
||
541 | if(cn1.State == System.Data.ConnectionState.Open) |
||
542 | { |
||
543 | using(SQLiteConnection cn2 = new SQLiteConnection()) |
||
544 | { |
||
545 | #if !MONO |
||
546 | cn2.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", destFile); |
||
547 | #else |
||
548 | cn2.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", destFile); |
||
549 | #endif |
||
550 | cn2.Open(); |
||
551 | if(cn2.State == System.Data.ConnectionState.Open) |
||
552 | { |
||
553 | using(SQLiteCommand cmd = new SQLiteCommand(string.Format("ATTACH DATABASE \"{0}\" AS Source", sourceFile), cn2)) |
||
554 | { |
||
555 | cmd.ExecuteNonQuery(); |
||
556 | } |
||
557 | |||
558 | using(SQLiteTransaction tr = cn2.BeginTransaction()) |
||
559 | { |
||
560 | try |
||
561 | { |
||
562 | List<long> add = new List<long>(); |
||
563 | using(SQLiteCommand cmd = new SQLiteCommand("SELECT id, X, Y, Zoom, Type FROM Tiles;", cn1)) |
||
564 | { |
||
565 | using(SQLiteDataReader rd = cmd.ExecuteReader()) |
||
566 | { |
||
567 | while(rd.Read()) |
||
568 | { |
||
569 | long id = rd.GetInt64(0); |
||
570 | using(SQLiteCommand cmd2 = new SQLiteCommand(string.Format("SELECT id FROM Tiles WHERE X={0} AND Y={1} AND Zoom={2} AND Type={3};", rd.GetInt32(1), rd.GetInt32(2), rd.GetInt32(3), rd.GetInt32(4)), cn2)) |
||
571 | { |
||
572 | using(SQLiteDataReader rd2 = cmd2.ExecuteReader()) |
||
573 | { |
||
574 | if(!rd2.Read()) |
||
575 | { |
||
576 | add.Add(id); |
||
577 | } |
||
578 | } |
||
579 | } |
||
580 | } |
||
581 | } |
||
582 | } |
||
583 | |||
584 | foreach(long id in add) |
||
585 | { |
||
586 | using(SQLiteCommand cmd = new SQLiteCommand(string.Format("INSERT INTO Tiles(X, Y, Zoom, Type, CacheTime) SELECT X, Y, Zoom, Type, CacheTime FROM Source.Tiles WHERE id={0}; INSERT INTO TilesData(id, Tile) Values((SELECT last_insert_rowid()), (SELECT Tile FROM Source.TilesData WHERE id={0}));", id), cn2)) |
||
587 | { |
||
588 | cmd.Transaction = tr; |
||
589 | cmd.ExecuteNonQuery(); |
||
590 | } |
||
591 | } |
||
592 | add.Clear(); |
||
593 | |||
594 | tr.Commit(); |
||
595 | } |
||
596 | catch(Exception exx) |
||
597 | { |
||
598 | Debug.WriteLine("ExportMapDataToDB: " + exx.ToString()); |
||
599 | tr.Rollback(); |
||
600 | ret = false; |
||
601 | } |
||
602 | } |
||
603 | |||
604 | using(SQLiteCommand cmd = new SQLiteCommand("DETACH DATABASE Source;", cn2)) |
||
605 | { |
||
606 | cmd.ExecuteNonQuery(); |
||
607 | } |
||
608 | } |
||
609 | } |
||
610 | } |
||
611 | } |
||
612 | } |
||
613 | } |
||
614 | catch(Exception ex) |
||
615 | { |
||
616 | Debug.WriteLine("ExportMapDataToDB: " + ex.ToString()); |
||
617 | ret = false; |
||
618 | } |
||
619 | return ret; |
||
620 | } |
||
621 | #endregion |
||
622 | |||
623 | static readonly string singleSqlSelect = "SELECT Tile FROM main.TilesData WHERE id = (SELECT id FROM main.Tiles WHERE X={0} AND Y={1} AND Zoom={2} AND Type={3})"; |
||
624 | static readonly string singleSqlInsert = "INSERT INTO main.Tiles(X, Y, Zoom, Type, CacheTime) VALUES(@p1, @p2, @p3, @p4, @p5)"; |
||
625 | static readonly string singleSqlInsertLast = "INSERT INTO main.TilesData(id, Tile) VALUES((SELECT last_insert_rowid()), @p1)"; |
||
626 | |||
627 | string ConnectionString; |
||
628 | |||
629 | readonly List<string> AttachedCaches = new List<string>(); |
||
630 | string finnalSqlSelect = singleSqlSelect; |
||
631 | string attachSqlQuery = string.Empty; |
||
632 | string detachSqlQuery = string.Empty; |
||
633 | |||
634 | void RebuildFinnalSelect() |
||
635 | { |
||
636 | finnalSqlSelect = null; |
||
637 | finnalSqlSelect = singleSqlSelect; |
||
638 | |||
639 | attachSqlQuery = null; |
||
640 | attachSqlQuery = string.Empty; |
||
641 | |||
642 | detachSqlQuery = null; |
||
643 | detachSqlQuery = string.Empty; |
||
644 | |||
645 | int i = 1; |
||
646 | foreach(var c in AttachedCaches) |
||
647 | { |
||
648 | finnalSqlSelect += string.Format("\nUNION SELECT Tile FROM db{0}.TilesData WHERE id = (SELECT id FROM db{0}.Tiles WHERE X={{0}} AND Y={{1}} AND Zoom={{2}} AND Type={{3}})", i); |
||
649 | attachSqlQuery += string.Format("\nATTACH '{0}' as db{1};", c, i); |
||
650 | detachSqlQuery += string.Format("\nDETACH DATABASE db{0};", i); |
||
651 | |||
652 | i++; |
||
653 | } |
||
654 | } |
||
655 | |||
656 | public void Attach(string db) |
||
657 | { |
||
658 | if(!AttachedCaches.Contains(db)) |
||
659 | { |
||
660 | AttachedCaches.Add(db); |
||
661 | RebuildFinnalSelect(); |
||
662 | } |
||
663 | } |
||
664 | |||
665 | public void Detach(string db) |
||
666 | { |
||
667 | if(AttachedCaches.Contains(db)) |
||
668 | { |
||
669 | AttachedCaches.Remove(db); |
||
670 | RebuildFinnalSelect(); |
||
671 | } |
||
672 | } |
||
673 | |||
674 | #region PureImageCache Members |
||
675 | |||
676 | int preAllocationPing = 0; |
||
677 | |||
678 | bool PureImageCache.PutImageToCache(byte[] tile, int type, GPoint pos, int zoom) |
||
679 | { |
||
680 | bool ret = true; |
||
681 | if(Created) |
||
682 | { |
||
683 | try |
||
684 | { |
||
685 | using(SQLiteConnection cn = new SQLiteConnection()) |
||
686 | { |
||
687 | cn.ConnectionString = ConnectionString; |
||
688 | cn.Open(); |
||
689 | { |
||
690 | using(DbTransaction tr = cn.BeginTransaction()) |
||
691 | { |
||
692 | try |
||
693 | { |
||
694 | using(DbCommand cmd = cn.CreateCommand()) |
||
695 | { |
||
696 | cmd.Transaction = tr; |
||
697 | cmd.CommandText = singleSqlInsert; |
||
698 | |||
699 | cmd.Parameters.Add(new SQLiteParameter("@p1", pos.X)); |
||
700 | cmd.Parameters.Add(new SQLiteParameter("@p2", pos.Y)); |
||
701 | cmd.Parameters.Add(new SQLiteParameter("@p3", zoom)); |
||
702 | cmd.Parameters.Add(new SQLiteParameter("@p4", type)); |
||
703 | cmd.Parameters.Add(new SQLiteParameter("@p5", DateTime.Now)); |
||
704 | |||
705 | cmd.ExecuteNonQuery(); |
||
706 | } |
||
707 | |||
708 | using(DbCommand cmd = cn.CreateCommand()) |
||
709 | { |
||
710 | cmd.Transaction = tr; |
||
711 | |||
712 | cmd.CommandText = singleSqlInsertLast; |
||
713 | cmd.Parameters.Add(new SQLiteParameter("@p1", tile)); |
||
714 | |||
715 | cmd.ExecuteNonQuery(); |
||
716 | } |
||
717 | tr.Commit(); |
||
718 | } |
||
719 | catch(Exception ex) |
||
720 | { |
||
721 | #if MONO |
||
722 | Console.WriteLine("PutImageToCache: " + ex.ToString()); |
||
723 | #endif |
||
724 | Debug.WriteLine("PutImageToCache: " + ex.ToString()); |
||
725 | |||
726 | tr.Rollback(); |
||
727 | ret = false; |
||
728 | } |
||
729 | } |
||
730 | } |
||
731 | cn.Close(); |
||
732 | } |
||
733 | |||
734 | if(Interlocked.Increment(ref preAllocationPing) % 22 == 0) |
||
735 | { |
||
736 | CheckPreAllocation(); |
||
737 | } |
||
738 | } |
||
739 | catch(Exception ex) |
||
740 | { |
||
741 | #if MONO |
||
742 | Console.WriteLine("PutImageToCache: " + ex.ToString()); |
||
743 | #endif |
||
744 | Debug.WriteLine("PutImageToCache: " + ex.ToString()); |
||
745 | ret = false; |
||
746 | } |
||
747 | } |
||
748 | return ret; |
||
749 | } |
||
750 | |||
751 | PureImage PureImageCache.GetImageFromCache(int type, GPoint pos, int zoom) |
||
752 | { |
||
753 | PureImage ret = null; |
||
754 | try |
||
755 | { |
||
756 | using(SQLiteConnection cn = new SQLiteConnection()) |
||
757 | { |
||
758 | cn.ConnectionString = ConnectionString; |
||
759 | cn.Open(); |
||
760 | { |
||
761 | if(!string.IsNullOrEmpty(attachSqlQuery)) |
||
762 | { |
||
763 | using(DbCommand com = cn.CreateCommand()) |
||
764 | { |
||
765 | com.CommandText = attachSqlQuery; |
||
766 | int x = com.ExecuteNonQuery(); |
||
767 | //Debug.WriteLine("Attach: " + x); |
||
768 | } |
||
769 | } |
||
770 | |||
771 | using(DbCommand com = cn.CreateCommand()) |
||
772 | { |
||
773 | com.CommandText = string.Format(finnalSqlSelect, pos.X, pos.Y, zoom, type); |
||
774 | |||
775 | using(DbDataReader rd = com.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) |
||
776 | { |
||
777 | if(rd.Read()) |
||
778 | { |
||
779 | long length = rd.GetBytes(0, 0, null, 0, 0); |
||
780 | byte[] tile = new byte[length]; |
||
781 | rd.GetBytes(0, 0, tile, 0, tile.Length); |
||
782 | { |
||
783 | if(GMapProvider.TileImageProxy != null) |
||
784 | { |
||
785 | ret = GMapProvider.TileImageProxy.FromArray(tile); |
||
786 | } |
||
787 | } |
||
788 | tile = null; |
||
789 | } |
||
790 | rd.Close(); |
||
791 | } |
||
792 | } |
||
793 | |||
794 | if(!string.IsNullOrEmpty(detachSqlQuery)) |
||
795 | { |
||
796 | using(DbCommand com = cn.CreateCommand()) |
||
797 | { |
||
798 | com.CommandText = detachSqlQuery; |
||
799 | int x = com.ExecuteNonQuery(); |
||
800 | //Debug.WriteLine("Detach: " + x); |
||
801 | } |
||
802 | } |
||
803 | } |
||
804 | cn.Close(); |
||
805 | } |
||
806 | } |
||
807 | catch(Exception ex) |
||
808 | { |
||
809 | #if MONO |
||
810 | Console.WriteLine("GetImageFromCache: " + ex.ToString()); |
||
811 | #endif |
||
812 | Debug.WriteLine("GetImageFromCache: " + ex.ToString()); |
||
813 | ret = null; |
||
814 | } |
||
815 | |||
816 | return ret; |
||
817 | } |
||
818 | |||
819 | int PureImageCache.DeleteOlderThan(DateTime date, int? type) |
||
820 | { |
||
821 | int affectedRows = 0; |
||
822 | |||
823 | try |
||
824 | { |
||
825 | using(SQLiteConnection cn = new SQLiteConnection()) |
||
826 | { |
||
827 | cn.ConnectionString = ConnectionString; |
||
828 | cn.Open(); |
||
829 | { |
||
830 | using(DbCommand com = cn.CreateCommand()) |
||
831 | { |
||
832 | com.CommandText = string.Format("DELETE FROM Tiles WHERE CacheTime is not NULL and CacheTime < datetime('{0}')", date.ToString("s")); |
||
833 | if(type.HasValue) |
||
834 | { |
||
835 | com.CommandText += " and Type = " + type; |
||
836 | } |
||
837 | affectedRows = com.ExecuteNonQuery(); |
||
838 | } |
||
839 | } |
||
840 | } |
||
841 | } |
||
842 | catch(Exception ex) |
||
843 | { |
||
844 | #if MONO |
||
845 | Console.WriteLine("DeleteOlderThan: " + ex); |
||
846 | #endif |
||
847 | Debug.WriteLine("DeleteOlderThan: " + ex); |
||
848 | } |
||
849 | |||
850 | return affectedRows; |
||
851 | } |
||
852 | |||
853 | #endregion |
||
854 | } |
||
855 | #endif |
||
856 | } |