Tuple database tutorial
High-level tuple/database integration

Next:Database types

Let's make a tuple and store it in a database. To follow along, click on each code example and run it in the listener. If you forget to run an example, just start at the top and run them all again in order.

We're going to store books in this tutorial.
TUPLE: book id title author date-published edition cover-price condition ;

The title, author, and publisher should be strings; the date-published a timestamp; the edition an integer; the cover-price a float. These are the Factor types for which we will need to look up the corresponding Database types.

To actually bind the tuple slots to the database types, we'll use define-persistent.
USING: db.tuples db.types ; book "BOOK" { { "id" "ID" +db-assigned-id+ } { "title" "TITLE" VARCHAR } { "author" "AUTHOR" VARCHAR } { "date-published" "DATE_PUBLISHED" TIMESTAMP } { "edition" "EDITION" INTEGER } { "cover-price" "COVER_PRICE" DOUBLE } { "condition" "CONDITION" VARCHAR } } define-persistent

That's all we'll have to do with the database for this tutorial. Now let's make a book.
USING: calendar namespaces ; T{ book { title "Factor for Sheeple" } { author "Mister Stacky Pants" } { date-published T{ timestamp { year 2009 } { month 3 } { day 3 } } } { edition 1 } { cover-price 13.37 } } book set

Now we've created a book. Let's save it to the database.
USING: db db.sqlite fry io.files.temp ; : with-book-tutorial ( quot -- ) '[ "book-tutorial.db" temp-file <sqlite-db> _ with-db ] call ; inline [ book recreate-table book get insert-tuple ] with-book-tutorial

Is it really there?
[ T{ book { title "Factor for Sheeple" } } select-tuples . ] with-book-tutorial

Oops, we spilled some orange juice on the book cover.
book get "Small orange juice stain on cover" >>condition

Now let's save the modified book.
[ book get update-tuple ] with-book-tutorial

And select it again. You can query the database by any field -- just set it in the exemplar tuple you pass to select-tuples.
[ T{ book { title "Factor for Sheeple" } } select-tuples ] with-book-tutorial

Let's drop the table because we're done.
[ book drop-table ] with-book-tutorial

To summarize, the steps for using Factor's tuple database are:
Make a new tuple to represent your data
Map the Factor types to the database types with define-persistent
Make a custom database combinator (see Custom database combinators) to open your database and run a quotation
Create a table with create-table, ensure-table, or recreate-table
Start making and storing objects with insert-tuple, update-tuple, delete-tuples, and select-tuples