SQLite: Difference between revisions

From Wildsong
Jump to navigationJump to search
Brian Wilson (talk | contribs)
mNo edit summary
Brian Wilson (talk | contribs)
mNo edit summary
 
(4 intermediate revisions by the same user not shown)
Line 1: Line 1:
I am using SQLite for a web + Android app that will track and map my MPGs.
Also commonly known as "sqlite3".


== Schema ==
== Working with Python and SQLAlchemy ==
=== log ===
 
For day to day stuff, I use Python and [[SQLAlchemy]] to talk to databases. Look for "SQLite Manager".
 
I also use the command line program, called "sqlite3".
 
=== You need to know about this ===
 
There is a plugin for Firefox that lets me dig around in sqlite databases.
Once installed you access it under Tools->SQLite Manager and it lets you view schema and contents of a local sqlite file.
If you create a database programmatically it is very handy to be able to check the schema turned out as you expected
and to directly hack on the data in its tables.
 
== Yaris app ==
 
I started writing an app that uses SQLite to track and map my [[Yaris]] MPGs.
I was planning on putting it on my [[Garmin Monterra]]. Not doing any Android work right now though!
 
=== Schema ===
==== log ====
  id key
  id key
  date datetime
  date datetime
Line 14: Line 32:
  comment text ex: gas station name and address
  comment text ex: gas station name and address


=== journal ===
==== journal ====
  id key
  id key
  date datetime
  date datetime
Line 21: Line 39:
  comment text
  comment text
   
   
=== trip ===
==== trip ====
  id key
  id key
  date datetime
  date datetime
Line 28: Line 46:
  comment text
  comment text


== Reports ==
=== Reports ===


Calculated
Calculated
  mpg
  mpg
  average mpg
  average mpg
[[Category: Android]]

Latest revision as of 19:04, 11 June 2015

Also commonly known as "sqlite3".

Working with Python and SQLAlchemy

For day to day stuff, I use Python and SQLAlchemy to talk to databases. Look for "SQLite Manager".

I also use the command line program, called "sqlite3".

You need to know about this

There is a plugin for Firefox that lets me dig around in sqlite databases. Once installed you access it under Tools->SQLite Manager and it lets you view schema and contents of a local sqlite file. If you create a database programmatically it is very handy to be able to check the schema turned out as you expected and to directly hack on the data in its tables.

Yaris app

I started writing an app that uses SQLite to track and map my Yaris MPGs. I was planning on putting it on my Garmin Monterra. Not doing any Android work right now though!

Schema

log

id key
date datetime
odometer decimal(7,1) 
mileage decimal(5,1) distance traveled since last fillup
gallons decimal(5,3)
price decimal(6,3) price per gallon
cost decimal(6,2) total purchase
lat text
lon text
comment text ex: gas station name and address

journal

id key
date datetime
odometer decimal(7,1) 
weight int(11)
comment text

trip

id key
date datetime
start decimal(7,1)
stop decimal(7,1)
comment text

Reports

Calculated

mpg
average mpg