HUOM! Voit itse lisätä ja muuttaa sisältöä muokkaa-painikkeiden avulla

 Olet itse vastuussa tämän teoksen käytöstä aiheutuvista vahingoista Lue lisää.

Sqlite

Kohteesta Wikikko - kansan taitopankki
Siirry navigaatioon Siirry hakuun

Johdanto[muokkaa]

MacOS[muokkaa]

https://sqlite.org
  • Sqlite3 tulee valmiiksi asennettuna esim. Catalinassa.

Sqlite3 päivitys[muokkaa]

(7/2020 Catalina)

  1. Katso asennettu versio pääteohjelmassa komennolla: sqlite3
  2. Poistu kirjoittamalla: .quit
  3. Siirry osoitteeseen https://sqlite.org ja lataa ja pura uusin 'Precompiled Binaries for Mac OS X (x86)'-versio.
  4. Siirry hakemistoon, jossa puretut tiedostot ovat ja kopioi tiedosto hakemistoon: sudo mv sqlite3 /usr/local/bin/
    1. Huomaa, että alkuperäinen sqlite3 on hakemistossa /usr/bin, joka on SIP-suojattu (System Integrity Protection) ja sinne ei tulisi asentaa omia ohjelmia. Joten kopioiaan sqlite /usr/local/bin -hakemistoon, johon voi asentaa "omia" ohjelmia ja ilman SIP:n kytkemistä pois päältä.
  5. Anna komento: sudo chmod +x /usr/local/bin/sqlite3
  6. Anna komento: sqlite3 Jos avautuu kuitenkin vanha alkuperäinen sqlite3, tarkoittaa se, että syöttämiäsi komentoja haetaan /usr/bin -hakemistosta.
    1. Tee seuraava eli asenna .bash_profile-tiedostoon polku. MacOS hakee tiedostoja siinä järjestyksessä missä ne ovat PATH:ssa eli sijoita /usr/local/bin aiemmin kuin /usr/bin, jolloin MacOS tarkastaa ensin onko tiedostosta "omaa" versiota usr/local/bin -hakemistossa ja jos ei ole käyttää /usr/bin -hakemistossa olevaa alkuperäistä. Ohessa kuvattu rimpsu on Catalinan alkuperäiseen asennukseen lisätyt /usr-polut.
cd ; nano .bash_profile
export PATH=/opt/local/bin:/opt/local/sbin:/usr/local/bin:/usr/bin:$PATH
ctrl-x ja sitten y
source ~/.bash_profile
  1. Anna komento: sqlite3
    1. Jos avautuu ponnahdusikkuna, jossa lukee "sqlite3" cannot be opened because the developer cannot be verified.
      1. Avaa Järjestelmäasetukset > Security & Privacy > General-välilehti.
      2. Klikkaa ponnahdusikkunassa Cancel
      3. General-välilehdelle ilmestyy alaosaan: "sqlite3" was blocked from use because it is not from an identified developer." Klikkaa painiketta 'Allow Anyway'
      4. Anna uudestaan pääteohjelmassa komento: sqlite3, jolloin avautuvaan ponnahdusikkunaan on ilmestynyt painike 'Open'. Klikkaa sitä.
    2. Tarkista vastaako sqlite3 uutta asennettua versiota.

Sqlite pikakäyttöohje, esimerkkinä tekstiviestien (SMS) vienti tiedostoon[muokkaa]

Haluaisitko viedä kaikki macOS Messages-sovelluksen viestit csv- tai tekstitiedostoon ja siitä esimerkiksi Excel-tiedostoon? Riippuen siitä mikä iOS-versio sinulla on käytössä (tai jostain muustakin), kaikki puhelimen viestit eivät välttämättä ole synkronoituneet macOS:n Messages-sovellukseen. Tätä ilmenee vähintään vanhoissa viestiketjuissa kokonaisuudessan tai sitten viestiketjuista puuttuu vanhemmat viestit.


  • A. macOS-viestien vienti tiedostoon (Catalina):

Anna seuraavat komennot pääteohjelmassa käyttäjähakemistossasi eli yksi ylemänpä kuin Downloads-hakemisto. Yleensä se, mihin pääteohjelma aukeaa.

    • Teksti (txt)-tiedostoon lataukset-hakemistoon:
sqlite3 -separator "|" ./Library/Messages/chat.db "select id, datetime(date/1000000000 + 978307200,'unixepoch','localtime'), text from message inner join handle on handle.rowid = message.handle_id;" >> ./Downloads/Viestit-`date +%Y-%m-%d-%H-%M`.txt
    • Csv-tiedostoon:
sqlite3 -header -csv -separator "|" ./Library/Messages/chat.db "select id, datetime(date/1000000000 + 978307200,'unixepoch','localtime'), text from message inner join handle on handle.rowid = message.handle_id;" >> ./Downloads/Viestit-`date +%Y-%m-%d-%H-%M`.csv


  • B. iPhone:ssa olevien viestien vienti tiedostoon.
  1. Viestit pitää ensin saada kopioitua macOS-järjestelmään, jos ne eivät ole kokonaisuudessaan automaattisesti synkronoituneet. Eräs vaihtoehto on poistaa käytöstä iPhonessa varmuuskopiointi iCloudiin. Tämän jälkeen avata Music ja kytkeä iPhone USB-kaapelilla tietokoneeseen. iPhone ilmestyy hetken kuluttua vasempaan palkkiin ja kytkeä ja varmuuskopioida puhelin tietokoneeseen. Tämän jälkeen iPhonen voi taas asettaa varmuuskopioimaan pilvipalveluun.
  2. iPhonen varmuuskopio löytyy hakemistosta ~/Library/Application Support/MobileSync/Backup (käyttäjän kirjasto) ja sieltä viimeisimmän varmuuskopiohakemiston 3d -hakemistosta nimellä 3d0d7e5fb2ce288813306e4d4636395e047a3d28. Kyseessä on sqlite-tietokanta. Kopioi tiedosto Downloads-hakemistoon.
  3. Anna seuraavat komennot pääteohjelmassa käyttäjähakemistossasi eli yksi ylemänpä kuin Downloads-hakemisto. Yleensä se, mihin pääteohjelma aukeaa. Vastaavasti jos haluat viedä csv-tiedostoon katso kohdasta A.
sqlite3 -separator "|" ./Downloads/3d0d7e5fb2ce288813306e4d4636395e047a3d28 "select id, datetime(date/1000000000 + 978307200,'unixepoch','localtime'), text from message inner join handle on handle.rowid = message.handle_id;" >> ./Downloads/Viestit-`date +%Y-%m-%d-%H-%M`.txt


  • Csv-tiedoston vienti Exceliin. Jostain syystä vienti ei ole täydellinen vaan osa riveistä sijoittuu väärin.
  1. Avataan uusi Excel-taulukko > File > Import > CSV file > Import, haetaan em. csv-tiedosto, Get Data > File origin: Unicode (UTF-8), Delimited, Next> > Delimiters: Other, lisätään alt-7 eli |-merkki, Next> Valitaan puhelinnumero-sarake ja sille Column data Format: Text, valitaan päivämäärä-sarake ja muodoksi Date, valitaan viesti-sarake ja muodoksi Text. Sarakkeiden valinnat kannattaa tarkastaa, ne täytyy valita määrätyssä järjestyksessä? > Finish > OK

Seuraavassa ohjeessa opastetaan miten Sqlite-tietokantaan kytkeydytään ja sieltä haetaan tietoa.

  1. Kopioidaan pääteohjelmalla tiedostot sisältävä Sqlite-tietokanta Downloads-hakemistoon: cp Library/Messages/chat.db Downloads
  2. Avataan sqlite3-sovelluksella em. tietokanta: sqlite3 chat.db
  3. sqlite3 Downloads/chat.db Edellä käytetään polkua kun ei olle siirrytty Downloads-hakemistoon, mutta tietokannan avauskomento on siis: sqlite3 tietokantatiedosto
    1. Komentoriville ilmestyy sqlite> johon voidaan syöttää komentoja, jotka alkavat pistellä tai tietokantakomentoja.
    2. Huomaa, että komennon jälkeen tulee olla puolipiste; Sitä ei kuitenkaan tarvita . -komentojen jälkeen.
    3. Jos ilmestyy ...> , siitä pääsee tarvittaessa pois .quit tai ctrl-z Tämä tapahtuu esim. kun unohtaa puolipisteen lopusta.
  4. Selvitetään mitä tauluja tietokannassa on: .tables
    1. Viestit sijaitsevat taulussa message ja viestien puhelinnumerot taulussa handle.
  5. Selvitetään mitä sarakkeita taulussa on: PRAGMA table_info(message);
    1. Selvitetään myös mitä sarakkeita on taulussa handle: PRAGMA table_info(handle);
  6. Katsotaan mitä rivejä määrätyssä sarakkeessa on: select text from message; Eli nyt nähdään kaikki Messages-ohjelman viestit. :)
  7. Haetaan samalla kyseisestä taulusta kahden sarakkeen tiedot, sarakkeet erotetaan pilkulla: select handle_id, text from message; Jokaisella viestillä on handle_id -sarakkeessa numero, joka identifioi kenen kanssa on keskusteltu.
  8. Katsotaan mitä puhelinnumeroja on olemassa ja niiden id-numero toisesta handle -taulusta: select rowid, id from handle; Jokaisella keskustelukumppanilla on siis oma id-numero ja se tallennetaan taulun juoksevaan numerointiin rowid. Ts. keskustelukumppani saa ensimmäisen viestin tullessa koko viestijärjestelmään (iPhone, MacOS) seuraavan vapaana olevan järjestysnumeron ja tätä numeroa käytetään sitten myöhemmissäkinkeskuteluissa.
  9. Yhdistetään kahden taulun tiedot vertailemalla: select id, text from message inner join handle on handle.rowid = message.handle_id; Toisin sanoen tuotetaan listaus, jossa tulostuu sekä viesti että lähettäjän puhelinnumero tai muu tunniste. Ensimmäisessä osassa hakua määritellään mitä tietoja halutaan tulostaa kummastakin taulusta vaikka haku viittaakin vain message -tauluun eli tulosteessa on myös tieto handle -taulusta. Jälkimmäisessä osassa tehdään vertailu. Tauluihin ja sarakkeisiin viitataan seuraavasti: taulu.sarake
  10. Lisätään vielä viestin päivämäärä: select id, datetime(date/1000000000 + 978307200,'unixepoch','localtime'), text from message inner join handle on handle.rowid = message.handle_id; Päivämäärä on erikoisessa muodossa, mutta se muunnetaan oheisella tavalla.
  11. Viedään haun tulos tekstitiedostoon. Syötettäessä seuraavat komennot, sqlite-siirtyy "tilaan", jossa tiedosto avataan samaan hakemistoon, jossa avattu tietokanta on ja kaikki haut ennen .exit -komentoa vievät tulokset tiedostoon. Separator on valinnainen, jos sitä ei aseteta, käytetään automaattisesti pilkkua.
sqlite> .mode insert
.separator "|"
.output tiedostonimi.txt
select id, datetime(date/1000000000 + 978307200,'unixepoch','localtime'), text from message inner join handle on handle.rowid = message.handle_id;
.exit