Pharo-sqlite-example.st

Metacello new
	repository: 'github://juliendelplanque/SQLite3/src';
	baseline: 'SQLite3';
	load.

"Calibre Example"
connection := SQLite3Connection on: FileLocator home / 'Dropbox (Personal)'/'Calibre Library'/'metadata.db'.
connection open.

connection tableNames. 
cursor := connection execute: 'SELECT * FROM books'.
books := cursor rows.

title := 'Design Patterns%'.
cursor := connection execute: 'SELECT * FROM books WHERE title LIKE ', title surroundedBySingleQuotes.
book := cursor rows first.
"OR"
book := books detect: [ :e | (e atColumnNamed: #title) = 'Gurdjieff Remembered' ].
bookID := book atColumnNamed: #id.

cursor := connection execute: 'SELECT * FROM books_authors_link WHERE book = ', bookID asString.
authorLinks := cursor rows.

authorID := authorLinks first atColumnNamed: #author.
cursor := connection execute: 'SELECT * FROM authors WHERE id = ', authorID asString.
authors := cursor rows.
authorName := authors first atColumnNamed: #name.

cursor := connection execute: 'SELECT * FROM identifiers WHERE book = ', bookID asString.
ids := cursor rows.

bookFolder := '/Users/sean/Dropbox (Personal)/Calibre Library' asFileReference / authorName / (title, ' (', bookID asString, ')').

"Big join but doesn't seem to capture multiple authors. From https://stackoverflow.com/a/14900484/424245"
cursor := connection execute: 'SELECT id, title,
               (SELECT name FROM books_authors_link AS bal JOIN authors ON(author = authors.id) WHERE book = books.id) authors,
               (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
               (SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,
               (SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,
               (SELECT name FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
               (SELECT format FROM data WHERE data.book=books.id) formats,
               isbn,
               path,
               pubdate
        FROM books where id = 140'.
cursor next.
	
connection close.

"GnuCash Example"
connection := SQLite3Connection on: FileLocator home / 'Documents'/'GnuCash'/'Data'/'Personal-sqlite3.gnucash'.
connection open.
cursor := connection execute: 'SELECT * FROM transactions where ;'.

cursor := connection execute: 'INSERT INTO transactions(post_date,enter_date,description) VALUES (:post_date, :enter_date, :description);' with: { 
	':post_date' -> '2016-11-06 11:00:00'.
	':enter_date' -> '2016-11-09 21:33:53'.
	':description' -> 'New vendor'. } asDictionary.
	
connection close.