Differences

This shows you the differences between two versions of the page.

--- user:databaze [2018/04/01 18:55] – [Struktura databáze GeoGetu] gord
+++ user:databaze [2024/07/14 10:55] (current) – mikrom
@@ Line 9: / Line 9: @@
   * [[user:sql_konzole|SQL konzole]]
-  * některý z pluginů [[user:skript:geojarry|GeoJarry]] (jen SELECT, nejlépe vracející ID), [[user:skript:smartfilter|SmartFilter]] (jen SELECT, nejlépe vracející ID), [[user:skript:combine|Combine]], modul [[user:skript:stator:modules:stator-customtables|SQLTable]] generátoru statistik [[user:skript:stator|Stator]]
+  * Některý z pluginů:
-  * pokud používáte prohlížeč Mozilla Firefox, tak lze doporučit doplněk [[https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/|SQLite Manager]]
+    * [[user:skript:combine|Combine]]
-  * použití některé aplikace pro správu SQL databází. Mezi opravdu jednoduché patří [[http://www.singular.gr/sqlite/|Sqlite3Explorer]] a [[http://sqlitebrowser.sourceforge.net/|SQLite Database Browser]]. Pro sofistikovanější práci lze použít např. [[http://www.sqliteexpert.com/download.html|SQLite Expert Personal]], [[http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index|SQLiteSpy]], [[http://sqliteadmin.orbmu2k.de/|SQLite Administrator]] nebo [[http://sqlitestudio.pl/|SQLiteStudio]] (k němu napsal [[http://www.geocaching.cz/blog/53/entry-458-sqlgg02-geocachingov%C3%BD-den%C3%ADk/|blogový příspěvek]] Tarmara). Dokonce existuje i nějaký doplněk pro TotalCommander/UnrealCommander [[http://totalcmd.net/plugring/sqliteviewer.html|SQLite Viewer]].
+    * [[user:skript:smartfilter|SmartFilter]] (jen SELECT, nejlépe vracející ID)
+    * modul [[user:skript:stator:modules:stator-customtables|SQLTable]] generátoru statistik [[user:skript:stator|Stator]]
+    * [[user:skript:geojarry|GeoJarry]] (jen SELECT, nejlépe vracející ID)
+  * Pokud používáte prohlížeč Mozilla Firefox, tak lze doporučit doplněk [[https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager-webext/|SQLite Manager]]
+  * Použití některé aplikace pro správu SQLite databází.
+    * Mezi opravdu jednoduché patří
+      * [[http://www.singular.gr/sqlite/|Sqlite3Explorer]]
+      * [[https://sqlitebrowser.org/|SQLite Database Browser]]
+    * Pro sofistikovanější práci lze použít např.
+      * [[http://www.sqliteexpert.com/index.html|SQLite Expert Personal]]
+      * [[https://www.yunqa.de/delphi/products/sqlitespy/index|SQLiteSpy]]
+      * [[http://sqliteadmin.orbmu2k.de/|SQLite Administrator]]
+      * [[https://sqlitestudio.pl/|SQLiteStudio]] (k němu napsal [[https://www.geocaching.cz/blog/53/entry-458-sqlgg02-geocachingov%C3%BD-den%C3%ADk/|blogový příspěvek]] Tarmara).
+      * [[https://www.mitec.cz/sqliteq.html|SQLite Query]] (v roce 2020 jsem objevil i program od českého autora)
+    * Dokonce existuje i nějaký doplněk pro TotalCommander/UnrealCommander [[https://totalcmd.net/plugring/sqliteviewer.html|SQLite Viewer]].
 <WRAP round info>
-Upozornění: v nedaleké budoucnosti jsou plánovány velké změny ve struktuře databáze. Může tedy dojít k tomu, že dříve připravený SQL dotaz nebude pracovat správně v některé z příštích verzí databáze. (Verze databáze je uvedena ve vlastnostech databaze (**PRAGMA user_version**).
+Upozornění: V některých případech může být potřeba kontrolovat, zda databáze je opravdu GeoGetí databází. Aplikace, které je databáze určena, je nastavena ve vlastnostech databáze. SQL ''PRAGMA application_ID'' by měl vrátit hodnotu ''1845946112''.
 </WRAP>
-//Pro úplnost je třeba zmínit, že Geoget vlastně používá databází více, ale uživatel pracuje přímo jen s databází bodů, která je popsaná níže. Ostatní databáze jsou používány interně a jejich plnění daty uživatel téměř nemá jak ovlivnit. Jde o soubory (místo ''geoget'' použijte jméno souboru s použitou databází bodů)://
+<WRAP round info>
+Upozornění: je vhodné kontrolovat si verzi databáze, zda plugin nebo aplikace pracuje se správnou databázovou strukturou. Může tedy dojít k tomu, že dříve připravený SQL dotaz nebude pracovat správně v některé z příštích verzí databáze. Verze databáze je uvedena ve vlastnostech databáze ''PRAGMA user_version''. Aktuální verze databáze je 3 (pro verzi GeoGetu 2.11).
+</WRAP>
+//Pro úplnost je třeba zmínit, že GeoGet vlastně používá databází více, ale uživatel pracuje přímo jen s databází bodů, která je popsaná níže. Ostatní databáze jsou používány interně a jejich plnění daty uživatel téměř nemá jak ovlivnit. Jde o soubory (místo ''geoget'' použijte jméno souboru s použitou databází bodů)://
   * ''*.imc'' (obrázky k bodům)
   * ''*.map'' (náhledové mapky k bodům)
   * ''*.cache'' (mezipaměť obrázků, pluginů, polygonů, ...)
 ===== Diskuze =====
 Diskuze o se nachází na stránkách [[http://www.geocaching.cz/forum/viewthread.php?forum_id=20&thread_id=18571|Geocaching.cz]].
 ===== Struktura databáze GeoGetu =====
+Struktura databáze odpovídá verzi, která je uvedena v ''PRAGMA user_version'', v současnosti (GeoGet od verze 2.6) je platná hodnota 2.
-Struktura databáze odpovída verzi, která je uvedena v ''PRAGMA user_version'', v současnosti (GeoGet od verze 2.6) je platná hodnota 2.
 ^ Tabulka ^ Sloupec ^ Popis dat |
@@ Line 56: / Line 74: @@
 | ::: | ''country'' | země |
 | ::: | ''state'' | území |
-| ::: | ''gs_cacheid'' | groundspeak ID keše (hodnota odpovídá GC kódu a lze ji z GC kódu vypočítat, naopak z této kodnoty je možné vypočítat GC kód) |
+| ::: | ''gs_cacheid'' | groundspeak ID keše (hodnota odpovídá GC kódu a lze ji z GC kódu vypočítat, naopak z této hodnoty je možné vypočítat GC kód) |
 | ::: | ''gs_stateid'' | groundspeak ID státu |
 | ::: | ''gs_ownerid'' | grounspeak ID autora |
@@ Line 81: / Line 99: @@
 | ''geotag'' | ''key'' ||
 | ::: | ''id'' | ID bodu (GCxxxx) |
-| ::: | ''flag'' | rezervováno pro budoucí potřebu označování druhu tagu (0, od verze 2.8 (asi) obsahuje datum a cas aktualizace tagu <wrap important> <sup>1)</sup></wrap>)|
+| ::: | ''flag'' | rezervováno pro budoucí potřebu označování druhu tagu (0, od verze 2.8 (asi) obsahuje datum a čas aktualizace tagu <wrap important> <sup>1)</sup></wrap>)|
 | ::: | ''ptrkat'' | kategorie (klíč, ukazatel na sloupec ''geotagcategory.key'') |
 | ::: | ''ptrvalue'' | hodnota (klíč, ukazatel na sloupec ''geotagvalue.key'') |
@@ Line 90: / Line 108: @@
 | ::: | ''value'' | hodnota tagu |
 | ::: | ''flag'' | rezervováno pro budoucí potřebu označování druhu tagu |
-| ''metadata'' | ''key'' ||
+| ''metadata''<wrap important> <sup>4)</sup></wrap> | ''key'' ||
-| ::: | ''name'' | jmáno uložené hodnoty |
+| ::: | ''name'' | jméno uložené hodnoty |
 | ::: | ''value'' | vlastní hodnota |
 | ''waypoint'' | ''key'' ||
@@ Line 104: / Line 122: @@
 | ::: | ''cmt'' | popis waypointu z gc.com |
 | ::: | ''comment'' | poznámka k waypointu |
-| ::: | ''flag'' | **bit 0:** ''1'' - importovaný, ''0'' - ručně zadaný (to zároven znamená "Nepřepisovat při importu")|
+| ::: | ''flag'' | **bit 0:** ''1'' - importovaný, ''0'' - ručně zadaný (to zároveň znamená "Nepřepisovat při importu")|
 | ::: | ::: | **bit 1:** ''1'' (hodnota=2) - z importovaných korigovaných souřadnic <sup>2.6.5</sup> |
 | ::: | ::: | **bit 2:** ''1'' (hodnota=4) - navštívený waypoint <sup>2.9.13</sup> |
@@ Line 132: / Line 150: @@
 <wrap important> <sup>3)</sup></wrap> Zvláštní důležitost mají první 2 znaky, tzv. [[#prefix|Prefix]].
+<wrap important> <sup>4)</sup></wrap> tabulka ''metadata'' je obsahuje různé hodnoty jedinečné pro databázi. Je možné doplňovat i vlastní hodnoty, ale v tom případě je důležité věnovat zvláštní pozornost jménu proměnné. Doporučené jméno je složené ve tvaru ''plugin.proměnná'', např. ''stator.gcczId''. GeoGet používá následující proměnné (platné pro verzi 2.11)
+|create_application|aplikace a její verze, která databázi vytvořila|
+|create_level|verze databáze, na jaké verzi byla databáze vytvořena. Aktuální verze databáze je dostupná přes funkce databáze, viz. SQL příkaz ''PRAGMA user_version;'' a poznámka na začátku této stránky|
+|create_dt|datum a čas vytvoření databáze v numerickém tvaru|
+|create_datetime|datum a čas vytvoření databáze ve tvaru YYYY-MM-DD hh:mm:ss.ssss|
+|upgrade_application|aplikace a její verze, které databázi aktualizovala|
+|upgrade_dt|datum a čas posledního povýšení databáze (číslo)|
+|upgrade_datetime|datum a čas posledního povýšení databáze (string)|
+|MyFindsTS|datum a čas posledního načítání logů přes API|
 ===== SQLite APPLICATION_ID =====
@@ Line 156: / Line 187: @@
 ^ Prefix ^ Význam |
 | GC | keš ze serveru [[http://geocaching.com|Geocaching]] |
-| OC | keš ze serveru [[http://opencaching.com|Opencaching]] (existuji i narodní servery) |
+| OC | keš ze serveru [[http://opencaching.com|Opencaching]] (existuji i národní servery) |
 | WM | [[http://waymarking.com|Waymark]]  |
 | GS | objekt ze serveru [[http://geospy.com|GeoSpy]] |
@@ Line 162: / Line 193: @@
 | MU | Munzee (databáze vytvářená pluginem [[user:skript:munzees|Munzee]])|
 | LO | ŘOPík ([[user:skript:rop|databáze objektů lehkého opevnění]] poskytnutá HaLuMou) |
-| TA | turistická známka - česko (data získaná exportem ze [[http://k8.kreteni.eu/tz/tz.php|serveru K8]]) [[http://www.geoget.cz/doku.php/user:skript:tznamky|plugin]] |
+| TA | turistická známka - Česko (data získaná exportem ze [[http://k8.kreteni.eu/tz/tz.php|serveru K8]]) [[http://www.geoget.cz/doku.php/user:skript:tznamky|plugin]] |
 | TV | turistická vizitka (data získaná importem ze serveru http://www.turisticky-denik.cz [[http://www.geoget.cz/doku.php/user:skript:wanderbook|přes jejich API]]) |
 | TN | turistická nálepka (data získaná exportem ze [[http://k8.kreteni.eu/tz/tz.php|serveru K8]]) [[http://www.geoget.cz/doku.php/user:skript:tnalepky|plugin]] |
@@ Line 169: / Line 200: @@
 ===== Přístup ke komprimovaným sloupcům =====
-Databáze obsahuje v některých sloupcích data, která jsou komprimovaná pomocí [[http://zlib.net/|knihovny zlib]], aby se uštetřilo nějaké to místo na disku.
+Databáze obsahuje v některých sloupcích data, která jsou komprimovaná pomocí [[http://zlib.net/|knihovny zlib]], aby se ušetřilo nějaké to místo na disku.
 Protože knihovna je integrovaná do **GeoGetu**, je její použití ve skriptech snadné. Cesty jsou 2:
@@ Line 178: / Line 209: @@
 Problém může nastat při použití jiné aplikace než je **GeoGet**. V tom případě je nutné si nějak poradit sám. Ideální asi je vytvořit si vlastní UDF funkci a tu zaregistrovat (UDF funkce jsou v SQLite asociovány s aplikací, ne s databází). Jednu [[http://www.geocaching.cz/topic/18571-geoget-sql/?p=512297|takovou knihovnu nabízí]] Pe_Bo. Jinou možností je načíst komprimované hodnoty jako ''blobtext'' a pak si je vlastní funkcí v programu rozbalit.
+===== Spolupráce GeoGetí databáze s jinou databází =====
-===== Spolupráce Geogetí databáze s jinou databází =====
+<wrap tip>Pomocí SQL příkazu ATTACH</wrap> je možné propojit GeoGetí databázi s jinou a obě používat najednou.
-<wrap tip>Pomocí SQL příkazu ATTACH</wrap> je možné propojit geogetí databázi s jinou a obě používat najednou.
 <wrap tip>Při použití SQLite Studia</wrap> není třeba používat explicitně ATTACH příkazu, ale prefixem se lze odkazovat na připojené databáze přímo v SQLite studiu. Asi se tam implicitně ten ATTACH používá na pozadí, protože pak fungují příkazy napříč databázemi.
@@ Line 193: / Line 223: @@
 Limit 2GB se dá v [[user:geogetini|ini]] ručně změnit pomocí položky ''dblimit''. (v jednotkách MiB)
+===== Přístup  TAGům =====
+Protože se předpokládá, že většina hodnot tagů má mnohonásobné použití, není u keše použita (uložena) přímo hodnota (=dlouhý text), ale jen číslo,
+které ukazuje na ten konkrétní dlouhý text. Místo opakujícího se dlouhého textu se tedy opakuje jen číslo. Úspora místa je ale vyvážena trochu komplikovanějším přístupem k hodnotám TAGů. Protože text není uložen přímo u keše, je potřeba "spolupráce" tří tabulek:
+  * **geotagcategory** - seznam kategorií (''value''=text kategorie) a jejich číselných hodnot (''key'')
+  * **geotagvalue** - seznam hodnot tagů (''value''=text hodnoty), jejich číselných hodnot (''key'')
+  * **geotag** - přidělená kategorie (''ptrkat'', obsahuje ''key'' kategorie) a hodnota tagu (''ptvalue'', obsahuje ''key'' hodnoty) k jednotlivé keši (''id'', obsahuje GC kód keše)
+Jak bylo zmíněno, hodnoty jsou vždy uloženy jako text. Pokud tedy bude nutné chápat text jako číselnou hodnotu (např. pro porovnávaní nadmořských výšek), bude nutné text převést na číslo. To lze udělat dvěma způsoby:
+  * k hodnotě přičíst nebo odečíst 0, tím dojde k automatickému převodu
+  * použitím fráze ''CAST (text as decimal)''
+**SQL s vysvětlivkami**
+Nejjednodušší SQL příkaz
+<code SQL>
+  SELECT id, ptrkat, ptrvalue FROM geotag
+  WHERE id='GC29NEN'
+</code>
+bohužel nedává očekávaný výsledek. Jak bylo řečeno výše, výsledkem bude pouze výpis ukazatelů na texty kategorie a hodnoty tagu, nikoli vlastní texty. Navíc bude výpis obsahovat všechny tagy všech kategorií, které jsou keši přiřazeny.
+Abychom se dostali k textům a mohli podle nich případně i vybírat, je potřeba spojit ''id'', ''ptrkat'' a ''ptrvalue'' a vypsat ''value'' z tabulky **geotagvalue**.
+<code SQL>
+  SELECT gc.id, gtv.value FROM geocache gc
+    LEFT JOIN geotag gt ON gc.id=gt.id
+    LEFT JOIN geotagcategory gtc ON gt.ptrkat=gtc.key
+    LEFT JOIN geotagvalue gtv ON gt.ptrvalue=gtv.key
+  WHERE gc.id='GC29NEN' AND gtc.value='elevation'
+</code>
+Pokud budeme chtít vybrat všechny "živé" a nenalezené keše, které mají počet favoritních bodů větší než 90, a seřadíme je podle jejich počtu:
+<code SQL>
+  SELECT gc.id, gtv.value FROM geocache gc
+    LEFT JOIN geotag gt ON gc.id=gt.id
+    LEFT JOIN geotagcategory gtc ON gt.ptrkat=gtc.key
+    LEFT JOIN geotagvalue gtv ON gt.ptrvalue=gtv.key
+  WHERE gc.cachestatus=0           -- jen aktivni kese
+    AND gc.dtfound<1               -- jen nenalezene
+    -- sem muzeme pridat dalsi pozadovane podminky
+    -- ...
+    -- podminky pro tagy:
+    AND gtc.value='favorites'      -- kategorie tagu FP
+    AND (gtv.value+0)>90           -- hodnota (text) je jako cislo>90
+  -- serazeni
+  ORDER BY (gtv.value+0) DESC      -- hodnoty jako cislo seradime sestupne
+</code>
 ===== Příklady SQL dotazů =====
@@ Line 228: / Line 309: @@
 );
 </code>
 ==== Seznam keší, kde má uživatel DNF log ====
 <code sql>
 SELECT id FROM geolog WHERE finder="%GG_OWNER%" and type like "Didn't find it"
@@ Line 243: / Line 322: @@
   AND dtfound<=0
 </code>
 ==== Seznam keší, na kterých má uživatel GeoGetu vícenásobný nález ====
@@ Line 334: / Line 412: @@
 ==== Kombinace a počty keší, které je třeba odlovit pro Nté vyplnění matrixu ====
-V ukázkovém SQL je N=3. Výsledek ale není možné zobrazit přímo v Geogetu, protože není vrácen GC kód keše, ale jen kombinace a počet. Geoget tedy nemá co zobrazit.
+V ukázkovém SQL je N=3. Výsledek ale není možné zobrazit přímo v GeoGetu, protože není vrácen GC kód keše, ale jen kombinace a počet. GeoGet tedy nemá co zobrazit.
 <code SQL>
@@ Line 362: / Line 440: @@
  NOT IN (SELECT DISTINCT(difficulty||'-'||terrain) FROM geocache WHERE dtfound>0)
 </code>
 ==== Seznam okresů, v kterých ještě nemám nalezenou žádnou kešku ====