Sqlite

Johdanto

Katso myös sivu Mac vinkit, otsikko iVocabulary > Lessonin palauttaminen.

MacOS

https://sqlite.org

• Sqlite3 tulee valmiiksi asennettuna esim. Catalinassa.

Sqlite3 päivitys

(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

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.

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

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 kytke iPhone USB-kaapelilla tietokoneeseen. Avaa Finder ja hyväksy puhelin luotettavaksi sekä Finderissa että puhelimen ponnahdusikkunassa. Varmuuskopioinnin voi aloittaa Finderissa ja iPhone ilmestyy myös Musicin vasempaan palkkiin. 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. Kopioi tiedosto Downloads-hakemistoon. Kyseessä on sqlite-tietokanta.
3. Anna seuraavat komennot pääteohjelmassa käyttäjähakemistossasi eli yksi ylemänpä kuin Downloads-hakemisto. Yleensä se, mihin pääteohjelma aukeaa. Tekstitiedosto muodostuu Downloads-hakemistoon. 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

Tietokannasta tietojen noutaminen, muuntaminen ja vienti csv-tiedostoon

• iPhone Runkeeper
  • Esimerkki Ruby-ohjelmointikielellä kirjoitetusta yksinkertaisesta koodista, joka hakee tietokannasta määrätyt tiedot, muuntaa niitä selkeämpään esitysmuotoon ja muodostaa .csv-tiedoston, jonka voi viedä Exceliin. Ks. sivu iPhone.
• Ohjelma suoritetaan tekemällä siitä .rb -päätteinen tiedosto esimerkiksi tekstieditorilla, asettamalla se .sqlite-tietokannan kanssa samaan hakemistoon ja antamalla pääteohjelmassa komento ruby tiedostonnimi.rb
• Oheisessa koodissa kannattaa kiinnittää huomiota kuinka helppoa ruby-keiellä on kirjoittaa tietoa tiedostoon, hakea määrätyistä tauluista tietoja, muuntaa päivämäärien esitysmuotoa ja matkan esitysmuotoa ymmärrettävään muotoon.
  • Koodissa käytetään tiedostossa "a":ta, eli jos koodin ajaa uudestaan, se kirjoittaa aina tiedoston perään eikä siis tuhoa aikaisempaa. Katso myös https://gist.github.com/moritzh/2937136, joka sisältää Runkeeper-koodin, jolla tietokannan liikuntasuorituksista voidaan muokata jokaisesta oma gpx-tiedosto, joka voidaan avata karttaohjelmassa ja nähdä suorituksen reitti. Siinä jokaisesta liikuntasuorituksesta tehdään oma tiedosto.
  • Koodissa tehdään 1) aluksi määrityksiä 2) muodostetaan csv-tiedosto ja kirjoitetaan sinne taulujen otsikot ja suljetaan 3) Avataan tietokanta ja haetaan liikuntatapahtuman id-tunnus trips-taulusta 4) haetaan liikuntatapahtumn tiedot trips-taulusta 5) kirjoitetaan liikuntatapahtuma csv-tauluun. Ohjelmassa käytetään kahta "looppia".

require 'date'
require 'rubygems'
require 'sqlite3'

# Suomen aikavyöhyke 
ENV['TZ'] = 'EEST'

# Tiedoston muodostaminen ja otsikkorivin kirjoittaminen
output = "Tapahtuma%Aloitus%Lopetus%Matka-km%Tyyppi%Nousu-m%Fiilis(1-5)%Kalorit%Lisatieto\r\n"
file= File.open("tiedot.csv", "w")
file.write(output)
file.close

# Avataan tietokanta
db = SQLite3::Database.new( "RunKeeper.sqlite" )

# Haetaan liikuntatapahtuma
  db.execute( "select trip_id from trips" ) do |trip|
     
output=  <<EOF;
EOF
      
  db.execute("select trip_id,start_date,distance,activity_type,calories,notes,end_date,totalClimb,primaryFeedback from trips where trip_id = #{trip[0]};") do |components|

  id = components[0]
  sdate = (Time.at(components[1])).strftime('%a, %d %b %Y %H:%M:%S')
  dist = (components[2]/1000).to_f.round(3)
  type = components[3]
  cal = components[4].to_f.round
  note = components[5]
  edate = (Time.at(components[6])).strftime('%a, %d %b %Y %H:%M:%S')
  climb = components[7].to_f.round
  feed = components[8]

  output<< "#{id}%#{sdate}%#{edate}%#{dist}%#{type}%#{climb}%#{feed}%#{cal}%#{note}\r\n"
end

# Tiedoston avaus ja rivin lisäys
file= File.open("tiedot.csv", "a")
file.write(output)
file.close
end