--- /dev/null
+! Copyright (C) 2006 Chris Double.
+! See http://factorcode.org/license.txt for BSD license.
+USING: help sqlite tuple-db ;
+
+HELP: default-mapping
+{ $values { "class" "symbol for the tuple class" }
+ { "mapping" "a mapping object" }
+}
+{ $description "Given a tuple class, create a default mappings object. This is used to associate field names in the tuple with SQL statement field names, etc." }
+{ $see-also { "sqlite" "tuple-db" } set-mapping } ;
+
+HELP: set-mapping
+{ $values { "mapping" "a mapping object" }
+}
+{ $description "Store a database mapping so that the tuple-db system knows how to store instances of the tuple in the database." }
+{ $see-also { "sqlite" "tuple-db" } default-mapping } ;
+
+HELP: create-tuple-table
+{ $values { "db" "a database object" } { "class" "symbol for the tuple class" }
+}
+{ $description "Create the database table to store intances of the given tuple." }
+{ $see-also { "sqlite" "tuple-db" } default-mapping get-mapping } ;
+
+HELP: insert-tuple
+{ $values { "db" "a database object" } { "tuple" "an instance of a tuple" }
+}
+{ $description "Insert the tuple instance into the database. It is assumed that this tuple does not currently exist in the database." }
+{ $see-also { "sqlite" "tuple-db" } insert-tuple update-tuple find-tuples delete-tuple save-tuple } ;
+
+HELP: find-tuples
+{ $values { "db" "a database object" } { "tuple" "an instance of a tuple" }
+}
+{ $description "Return a sequence of all tuples in the database that match the tuple provided as a template. All fields in the tuple must match the entries in the database, except for those set to 'f'." }
+{ $see-also { "sqlite" "tuple-db" } insert-tuple update-tuple find-tuples delete-tuple save-tuple } ;
+
+HELP: update-tuple
+{ $values { "db" "a database object" } { "tuple" "an instance of a tuple" }
+}
+{ $description "Update the database record for this tuple instance. The tuple must have previously been obtained from the database, or inserted into it. It must have a delegate of 'persistent' with the key field set (which is done by the find and insert operations)." }
+{ $see-also { "sqlite" "tuple-db" } insert-tuple update-tuple find-tuples delete-tuple save-tuple } ;
+
+HELP: save-tuple
+{ $values { "db" "a database object" } { "tuple" "an instance of a tuple" }
+}
+{ $description "Insert or Update the tuple instance depending on whether it has a persistent delegate." }
+{ $see-also { "sqlite" "tuple-db" } insert-tuple update-tuple find-tuples delete-tuple save-tuple } ;
+
+HELP: delete-tuple
+{ $values { "db" "a database object" } { "tuple" "an instance of a tuple" }
+}
+{ $description "Delete this tuple instance from the database. The tuple must have previously been obtained from the database, or inserted into it. It must have a delegate of 'persistent' with the key field set (which is done by the find and insert operations)." }
+{ $see-also { "sqlite" "tuple-db" } insert-tuple update-tuple find-tuples delete-tuple save-tuple } ;
+
+ARTICLE: { "sqlite" "tuple-db-loading" } "Loading"
+"The quickest way to get up and running with this library is to load it as a module:"
+{ $code "\"sqlite\" require\nUSE: sqlite\nUSE: tuple-db\n" }
+"Some simple tests can be run to check that everything is working ok:"
+{ $code "\"sqlite\" test-module" } ;
+
+ARTICLE: { "sqlite" "tuple-db-usage" } "Basic Usage"
+"This library can be used for storing simple Factor tuples in a sqlite database. In its current form the tuples must not contain references to other tuples and should not have a delegate set."
+$terpri
+"This document will use the following tuple for demonstration purposes:"
+{ $code "TUPLE: person name surname phone ;" }
+"The sqlite database to store tuples must be created, or an existing one opened. This is done using the " { $link sqlite-open } " word. If the database does not exist then it is created. The examples in this document store the database pointer in a variable called 'db':"
+{ $code "SYMBOL: db\n\"example.db\" sqlite-open db set-global" } ;
+
+ARTICLE: { "sqlite" "tuple-db-mappings" } "Tuple Mappings"
+"Each tuple has a 'mapping' tuple associated with it. The 'mapping' stores information about what table the tuple will be stored in, the datatypes of the tuple slots, etc. A mapping must be created before a tuple can be stored in a database. A default mapping is easily created using " { $link default-mapping } ". Given the tuple class, this will use reflection to get the slots of it, assume that all slots are of database type 'text', and store the tuple objects in a table with the same name as the tuple."
+$terpri
+"The following shows how to create the default mapping for the 'person' tuple, and how to register that mapping so the 'tuple-db' system can know how to handle 'person' instances:"
+{ $code "person default-mapping set-mapping" } ;
+
+ARTICLE: { "sqlite" "tuple-db-create" } "Creating the table"
+"The table used to store tuple instances may need to be created. This can be done manually using the external sqlite program or via " { $link create-tuple-table } ":"
+{ $code "db get person create-tuple-table" }
+"The SQL used to create the table is produced internally by " { $link create-sql } ". This is a generic word dispatched on the mapping object, and could be specialised if needed. If you wish to see the SQL used to create the table, use the following code:"
+{ $code "person get-mapping create-sql .\n => \"create table person (name text,surname text,phone text);\"" } ;
+
+ARTICLE: { "sqlite" "tuple-db-insert" } "Inserting instances"
+"The " { $link insert-tuple } " word will store instances of a tuple into the database table defined by its mapping object:"
+{ $code "db get \"John\" \"Smith\" \"123-456-789\" <person> insert-tuple" }
+{ $link insert-tuple } " internally uses the " { $link insert-sql } " word to produce the SQL used to store the tuple. Like " { $link create-sql } ", it is a generic word specialized on the mapping object. You can call it directly to see what SQL is generated:"
+{ $code "person get-mapping insert-sql .\n => \"insert into person values(:name,:surname,:phone);\"" }
+"Notice that the SQL uses named parameters. These parameters are bound to the values stored in the tuple object when the SQL is compiled. This helps prevent SQL injection techniques."
+$terpri
+"When " { $link insert-sql } " is run, it adds a delegate to the tuple being stored. The delegate is of type 'persistent' and holds the row id of the tuple in its 'key' slot. This way the exact record can be updated or retrieved later. The following demonstates this fact:"
+{ $code "\"Mandy\" \"Jones\" \"987-654-321\" <person> dup .\n => T{ person f \"Mandy\" \"Jones\" \"987-654-321\" }\ndb get over insert-tuple .\n => T{ person T{ persistent ... 2 } \"Mandy\" \"Jones\" \"987-654-321\" }" }
+"The '2' in the above example is the row id of the record inserted. We can go into the 'sqlite' command and view this record:"
+{ $code " $ sqlite3 example.db\n SQLite version 3.0.8\n Enter \".help\" for instructions\n sqlite> select ROWID,* from person;\n 1|John|Smith|123-456-789\n 2|Mandy|Jones|987-654-321\n sqlite>" } ;
+
+ARTICLE: { "sqlite" "tuple-db-finding" } "Finding instances"
+"The " { $link find-tuples } " word is used to return tuples populated with data already existing in the database. As well as the database objcet, it takes a tuple that should be populated only with the fields that should be matched in the database. All fields you do not wish to match against should be set to 'f':"
+{ $code "db get f \"Smith\" f <person> find-tuples .\n => { T{ person # \"John\" \"Smith\" \"123-456-789\" } }\ndb get \"Mandy\" f f <person> find-tuples .\n => { T{ person # \"Mandy\" \"Jones\" \"987-654-321\" } }\ndb get \"Joe\" f f <person> find-tuples .\n => { }" }
+"Notice that if no matching tuples are found then an empty sequence is returned. The returned tuples also have their delegate set to 'persistent' with the correct row id set as the key. This can be used to later update the tuples with new information and store them in the database." ;
+
+ARTICLE: { "sqlite" "tuple-db-updating" } "Updating instances"
+"Given a tuple that has the 'persistent' delegate with the row id set as the key, you can update this specific record using " { $link update-tuple } ":"
+{ $code "db get f \"Smith\" f <person> find-tuples dup .\n => { T{ person # \"John\" \"Smith\" \"123-456-789\" } }\nfirst { \"999-999-999\" swap set-person-phone ] keep dup .\n => T{ person T{ persistent f # \"1\" } \"John\" \"Smith\" \"999-999-999\" ...\n db get swap update-tuple" }
+"Using the 'sqlite' command from the system shell you can see the record was updated:"
+{ $code " $ sqlite3 example.db\n SQLite version 3.0.8\n Enter \".help\" for instructions\n sqlite> select ROWID,* from person;\n 1|John|Smith|999-999-999\n 2|Mandy|Jones|987-654-321\n sqlite>" } ;
+
+ARTICLE: { "sqlite" "tuple-db-inserting-or-updating" } "Inserting or Updating instances"
+"The " { $link save-tuple } " word can be used to insert a tuple if it has not already been stored in the database, or update it if it already exists. Whether to insert or update is decided by the existance of the 'persistent' delegate:"
+{ $code "\"Mary\" \"Smith\" \"111-111-111\" <person> dup .\n => T{ person f \"Mary\" \"Smith\" \"111-111-111\" }\n! This will insert the tuple\ndb get over save-tuple dup .\n => T{ person T{ persistent f # \"3\" } \"Mary\" \"Smith\" \"111-111-111\" ...\n[ \"222-222-222\" swap set-person-phone ] keep dup .\n => T{ person T{ persistent f # \"3\" } \"Mary\" \"Smith\" \"222-222-222\" ...\n! This will update the tuple\ndb get over save-tuple .\n => T{ person T{ persistent f # \"3\" } \"Mary\" \"Smith\" \"222-222-222\" ..." } ;
+
+ARTICLE: { "sqlite" "tuple-db-deleting" } "Deleting instances"
+"Given a tuple with the delegate set to 'persistent' (ie. One already stored in the database) you can delete it from the database with " { $link delete-tuple } ":"
+{ $code "db get f \"Smith\" f <person> find-tuples [ db get swap delete-tuple ] each" } ;
+
+ARTICLE: { "sqlite" "tuple-db-closing" } "Closing the database"
+"It's important to close the sqlite database when you've finished using it. The word for this is " { $link sqlite-close } ":"
+{ $code "db get sqlite-close" } ;
+
+ARTICLE: { "sqlite" "tuple-db" } "Tuple Database Library"
+"The version of sqlite required by this library is version 3 or greater. This library allows storing Factor tuples in a sqlite database. It provides words to create, read update and delete these entries as well as simple searching."\
+$terpri
+"The library is in a very early state and is likely to change quite a bit in the near future. Its most notable omission is it cannot currently handle relationships between tuples."
+{ $subsection { "sqlite" "tuple-db-loading" } }
+{ $subsection { "sqlite" "tuple-db-usage" } }
+{ $subsection { "sqlite" "tuple-db-mappings" } }
+{ $subsection { "sqlite" "tuple-db-create" } }
+{ $subsection { "sqlite" "tuple-db-insert" } }
+{ $subsection { "sqlite" "tuple-db-finding" } }
+{ $subsection { "sqlite" "tuple-db-updating" } }
+{ $subsection { "sqlite" "tuple-db-inserting-or-updating" } }
+{ $subsection { "sqlite" "tuple-db-deleting" } }
+{ $subsection { "sqlite" "tuple-db-closing" } }
+;
+
+++ /dev/null
-<html>
- <head>
- <title>Tuple Database Library</title>
- <style>
- body { background: white; color: black; }
- p { margin-left: 10%; margin-right: 10%;
- font: normal 100% Verdana, Arial, Helvetica; }
- td { margin-left: 10%; margin-right: 10%;
- font: normal 100% Verdana, Arial, Helvetica; }
- table { margin-left: 10%; margin-right: 10%; }
- ul { margin-left: 10%; margin-right: 10%;
- font: normal 100% Verdana, Arial, Helvetica; }
- ol { margin-left: 10%; margin-right: 10%;
- font: normal 100% Verdana, Arial, Helvetica; }
- h1 { text-align: center; margin-bottom: 0; margin-top: 1em; }
- h2 { margin: 0 5% 0 7.5%; font-size: 120%; font-style: italic; }
- h3 { border: 2px solid blue; border-width: 2px 0.5em 2px 0.5em;
- padding: 0.2em 0.2em 0.2em 0.5em; background: #fafafa;
- margin-left: 10%; margin-right: 10%; margin-top: 2em;
- font-size: 100%; }
- .note { border: 2px solid blue; border-width: 2px 2px 2px 2em;
- padding: 0.5em 0.5em 0.5em 1em; background: #ffe; }
- .code { border: 1px solid black; border-width: 1px;
- padding: 0.5em; background: #ffe;
- margin-left: 10%; margin-right: 10%; }
- blockquote { margin-left: 25%; margin-right: 25%;
- font-style: italic; }
- .highlite { color: red; }
- .footer { margin-top: 2.5em; border-top: 1px solid gray; color:
- #AAA; font-size: 85%; padding-top: 0.33em; }
- #copyright { text-align: center; color: #AAA;
- font-size: 65%; }
- </style>
- </head>
- <body>
- <h1>Tuple Database Library</h1>
-<h1>Overview</h1>
-<p class="note">The version of sqlite supported by this library is
- version 3 or greater.</p>
-<p>This library allows storing Factor tuples in a sqlite database. It
-provides words to create, read update and delete these entries as well
-as simple searching.</p>
-<p>The library is in a very early state and is likely to change quite
-a bit in the near future. Its most notable omission is it cannot currently
-handle relationships between tuples. This feature is currently being
-worked on.</p>
-<h1>Loading</h1>
-<p>The quickest way to get up and running with this library is to
-load it as a module:</p>
-<pre class="code">
-"sqlite" require
-USE: sqlite
-USE: tuple-db
-</pre>
-<p>Some simple tests can be run to check that everything is working
-ok:</p>
-<pre class="code">
-"sqlite" test-module
-</pre>
-<h1>Basic Usage</h1>
-<p>This library can be used for storing simple Factor tuples in a
-sqlite database. In its current form the tuples must not contain
-references to other tuples and should not have a delegate set.</p>
-<p>This document will use the following tuple for demonstration
-purposes:</p>
-<pre class="code">
-TUPLE: person name surname phone ;
-</pre>
-<p>The sqlite database to store tuples must be created, or an existing
-one opened. This
-is done using the 'sqlite-open word. If the database does not exist
-then it is created. The examples in this document
-store the database pointer in a variable called 'db':</p>
-<pre class="code">
-SYMBOL: db
-"example.db" sqlite-open db set
-</pre>
-<h2>Tuple Mappings</h2>
-<p>Each tuple has a 'mapping' tuple associated with it. The 'mapping'
-stores information about what table the tuple will be stored in, the
-datatypes of the tuple slots, etc. A mapping must be created before a
-tuple can be stored in a database. A default mapping is easily created
-using the 'default-mapping' word. Given the tuple class, this will use
-reflection to get the slots of it, assume that all slots are of
-database type 'text', and store the tuple objects in a table with the
-same name as the tuple.</p>
-<p>The following shows how to create the default mapping for the
-'person' tuple, and how to register that mapping so the 'tuple-db'
-system can know how to handle 'person' instances:</p>
-<pre class="code">
-person default-mapping set-mapping
-</pre>
-<h2>Creating the table</h2>
-<p>The table used to store tuple instances may need to be
-created. This can be done manually using 'sqlite' or via the
-'create-tuple-table' word:</p>
-<pre class="code">
-<span class="highlite">! create-tuple-table ( db class -- )</span>
-db get person create-tuple-table
-</pre>
-<p>The SQL used to create the table is produced by the 'create-sql'
-word. This is a generic word dispatched on the mapping object, and
-could be specialised if needed. If you wish to see the SQL used to
-create the table, use the following code:</p>
-<pre class="code">
-<span class="highlite">! M: mapping create-sql ( mapping -- string )</span>
-person get-mapping create-sql .
- => "create table person (name text,surname text,phone text);"
-</pre>
-<h2>Inserting instances</h2>
-<p>The 'insert-tuple' word will store instances of a tuple
-into the database table defined by its mapping object. It's as
-simple as:</p>
-<pre class="code">
-<span class="highlite">! insert-tuple ( db tuple -- )</span>
-db get "John" "Smith" "123-456-789" <person> insert-tuple
-</pre>
-<p>'insert-tuple' internally uses the 'insert-sql' word to produce
-the SQL used to store the tuple. Like 'create-sql', 'insert-sql' is
-a generic word specialized on the mapping object. You can call it
-directly to see what SQL is generated:</p>
-<pre class="code">
-<span class="highlite">! M: mapping insert-sql ( mapping -- string )</span>
-person get-mapping insert-sql .
- => "insert into person values(:name,:surname,:phone);"
-</pre>
-<p>Notice that the SQL uses named parameters. This parameters are bound to the values stored in the tuple object when the SQL is compiled. This helps prevent SQL injection techniques.</p>
-<p>When the 'insert-sql' word is run, it adds a delegate to the tuple being stored. The delegate is of type 'persistent' and holds the row id of the tuple in its 'key' slot. This way the exact record can be updated or retrieved later. The following demonstates this fact:</p>
-<pre class="code">
-"Mandy" "Jones" "987-654-321" <person> dup .
- => << person f "Mandy" "Jones" "987-654-321" >>
-db get over insert-tuple .
- => << person
- << persistent ... <span class="highlite">"2"</span> >>
- "Mandy" "Jones" "987-654-321"
- >>
-</pre>
-<p>The '2' highlited in the above example is the row id of the record inserted. We can go into the 'sqlite' command and view this record:</p>
-<pre class="code">
- $ sqlite3 example.db
- SQLite version 3.0.8
- Enter ".help" for instructions
- sqlite> select ROWID,* from person;
- 1|John|Smith|123-456-789
- <span class="highlite">2|Mandy|Jones|987-654-321</span>
- sqlite>
-</pre>
-<h2>Finding Instances</h2>
-<p>The 'find-tuples' word is used to return tuples populated with data already
-existing in the database. As well as the database pointer, it takes a tuple that should be populated only with the fields that should be matched in the database. All fields you do not wish to match against should be set to 'f':</p>
-<pre class="code">
-<span class="highlite">! find-tuples ( db tuple -- seq )</span>
-db get f "Smith" f <person> find-tuples short.
- => [ << person # "John" "Smith" "123-456-789" >> ]
-db get "Mandy" f f <person> find-tuples short.
- => [ << person # "Mandy" "Jones" "987-654-321" >> ]
-db get "Joe" f f <person> find-tuples short.
- => f
-</pre>
-<p>Notice that if no matching tuples are found then 'f' is returned. The returned tuples also have their delegate set to 'persistent' with the correct row id set as the key. This can be used to later update the tuples with new information and store them in the database.</p>
-<h2>Updating Instances</h2>
-<p>Given a tuple that has the 'persistent' delegate with the row id
-set as the key, you can update this specific record using the
-'update-tuple' word:</p>
-<pre class="code">
-<span class="highlite">! update-tuple ( db tuple -- )</span>
-db get f "Smith" f <person> find-tuples dup short.
- => [ << person # "John" "Smith" "123-456-789" >> ]
-first [ "999-999-999" swap set-person-phone ] keep dup short.
- => << person << persistent f # "1" >> "John" "Smith" "999-999-999" ...
- db get swap update-tuple
-</pre>
-<p>Using the 'sqlite' command from the system shell you can see the
-record was updated:</p>
-<pre class="code">
- $ sqlite3 example.db
- SQLite version 3.0.8
- Enter ".help" for instructions
- sqlite> select ROWID,* from person;
- 1|John|Smith|999-999-999
- <span class="highlite">2|Mandy|Jones|987-654-321</span>
- sqlite>
-</pre>
-<h2>Inserting or Updating</h2>
-<p>The 'save-tuple' word can be used to insert a tuple if it has not
-already been stored in the database, or update it if it already
-exists. Whether to insert or update is decided by the existance of the
-'persistent' delegate:</p>
-<pre class="code">
-<span class="highlite">! save-tuple ( db tuple -- )</span>
-"Mary" "Smith" "111-111-111" <person> dup short.
- => << person f "Mary" "Smith" "111-111-111" >>
-! This will insert the tuple
-db get over save-tuple dup short.
- => << person << persistent f # "3" >> "Mary" "Smith" "111-111-111" ...
-[ "222-222-222" swap set-person-phone ] keep dup short.
- => << person << persistent f # "3" >> "Mary" "Smith" "222-222-222" ...
-! This will update the tuple
-db get over save-tuple short.
- => << person << persistent f # "3" >> "Mary" "Smith" "222-222-222" ...
-</pre>
-<h2>Deleting</h2>
-<p>Given a tuple with the delegate set to 'persistent' (ie. One
-already stored in the database) you can delete it from the database
-with the 'delete-tuple' word:</p>
-<pre class="code">
-<span class="highlite">! delete-tuple ( db tuple -- )</span>
-db get f "Smith" f <person> find-tuples [
- db get swap delete-tuple
-] each
-</pre>
-<h2>Closing the database</h2>
-<p>It's important to close the sqlite database when you've finished
-using it. The word for this is 'sqlite-close':</p>
-<pre class="code">
-<span class="highlite">! sqlite-close ( db -- )</span>
-db get sqlite-close
-</pre>
-
-<p class="footer">
-News and updates to this software can be obtained from the authors
-weblog: <a href="http://radio.weblogs.com/0102385">Chris Double</a>.</p>
-<p id="copyright">Copyright (c) 2004, Chris Double. All Rights Reserved.</p>
-</body> </html>
projects / factor.git / commitdiff