GeoGet

Complete geocaching solutions

User Tools

Site Tools


user:databaze

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
user:databaze [2019/01/21 19:00] – [Přístup TAGům] gorduser:databaze [2021/04/18 10:43] (current) – [Struktura databáze GeoGetu] gord
Line 9: Line 9:
  
   * [[user:sql_konzole|SQL konzole]]   * [[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]] [[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> <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 kontrolovatzda 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> </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)   * ''*.imc'' (obrázky k bodům)
   * ''*.map'' (náhledové mapky k bodům)   * ''*.map'' (náhledové mapky k bodům)
   * ''*.cache'' (mezipaměť obrázků, pluginů, polygonů, ...)   * ''*.cache'' (mezipaměť obrázků, pluginů, polygonů, ...)
 +
 ===== Diskuze ===== ===== 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]]. 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 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 | ^ Tabulka ^ Sloupec ^ Popis dat |
Line 56: Line 74:
 | ::: | ''country'' | země | | ::: | ''country'' | země |
 | ::: | ''state'' | ú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_stateid'' | groundspeak ID státu |
 | ::: | ''gs_ownerid'' | grounspeak ID autora | | ::: | ''gs_ownerid'' | grounspeak ID autora |
Line 81: Line 99:
 | ''geotag'' | ''key'' || | ''geotag'' | ''key'' ||
 | ::: | ''id'' | ID bodu (GCxxxx) | | ::: | ''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'') | | ::: | ''ptrkat'' | kategorie (klíč, ukazatel na sloupec ''geotagcategory.key'') |
 | ::: | ''ptrvalue'' | hodnota (klíč, ukazatel na sloupec ''geotagvalue.key'') | | ::: | ''ptrvalue'' | hodnota (klíč, ukazatel na sloupec ''geotagvalue.key'') |
Line 90: Line 108:
 | ::: | ''value'' | hodnota tagu | | ::: | ''value'' | hodnota tagu |
 | ::: | ''flag'' | rezervováno pro budoucí potřebu označování druhu 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 | | ::: | ''value'' | vlastní hodnota |
 | ''waypoint'' | ''key'' || | ''waypoint'' | ''key'' ||
Line 104: Line 122:
 | ::: | ''cmt'' | popis waypointu z gc.com | | ::: | ''cmt'' | popis waypointu z gc.com |
 | ::: | ''comment'' | poznámka k waypointu | | ::: | ''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 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> | | ::: | ::: | **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>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é. Dopočuč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 ===== ===== SQLite APPLICATION_ID =====
Line 156: Line 187:
 ^ Prefix ^ Význam | ^ Prefix ^ Význam |
 | GC | keš ze serveru [[http://geocaching.com|Geocaching]] | | 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]]  | | WM | [[http://waymarking.com|Waymark]]  |
 | GS | objekt ze serveru [[http://geospy.com|GeoSpy]] | | GS | objekt ze serveru [[http://geospy.com|GeoSpy]] |
Line 162: Line 193:
 | MU | Munzee (databáze vytvářená pluginem [[user:skript:munzees|Munzee]])| | 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) | | 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]]) | | 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]] | | 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 ===== ===== 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: 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. 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.  <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 194: Line 224:
  
 ===== Přístup  TAGům ===== ===== 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:
  
-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řimo hodnota (=dlouhý text), ale jen číslo, +  * **geotagcategory** - seznam kategorií (''value''=text kategorie) a jejich číselných hodnot (''key'')
-které ukazuje na ten konkretní douhý 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 čiselných hodnot (''key'')+
   * **geotagvalue** - seznam hodnot tagů (''value''=text hodnoty), 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)   * **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)
Line 216: Line 245:
 </code> </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šechy tagy všech kategorií, které jsou keši přiřazeny.+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**. 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**.
Line 245: Line 274:
   ORDER BY (gtv.value+0) DESC      -- hodnoty jako cislo seradime sestupne   ORDER BY (gtv.value+0) DESC      -- hodnoty jako cislo seradime sestupne
 </code> </code>
- 
- 
  
 ===== Příklady SQL dotazů ===== ===== Příklady SQL dotazů =====
Line 282: Line 309:
 ); );
 </code> </code>
- 
  
 ==== Seznam keší, kde má uživatel DNF log ==== ==== Seznam keší, kde má uživatel DNF log ====
- 
 <code sql> <code sql>
 SELECT id FROM geolog WHERE finder="%GG_OWNER%" and type like "Didn't find it" SELECT id FROM geolog WHERE finder="%GG_OWNER%" and type like "Didn't find it"
Line 297: Line 322:
   AND dtfound<=0   AND dtfound<=0
 </code> </code>
- 
  
 ==== Seznam keší, na kterých má uživatel GeoGetu vícenásobný nález ==== ==== Seznam keší, na kterých má uživatel GeoGetu vícenásobný nález ====
Line 388: Line 412:
  
 ==== Kombinace a počty keší, které je třeba odlovit pro Nté vyplnění matrixu ==== ==== 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> <code SQL>
Line 416: Line 440:
  NOT IN (SELECT DISTINCT(difficulty||'-'||terrain) FROM geocache WHERE dtfound>0)  NOT IN (SELECT DISTINCT(difficulty||'-'||terrain) FROM geocache WHERE dtfound>0)
 </code> </code>
- 
  
 ==== Seznam okresů, v kterých ještě nemám nalezenou žádnou kešku ==== ==== Seznam okresů, v kterých ještě nemám nalezenou žádnou kešku ====
user/databaze.1548093653.txt.gz · Last modified: 2019/01/21 19:00 by gord