To access it you can use rebol commands or SQL commands, this guide will cover both solution.
First of all you have to load all the script this way:
>> do %db.r
Table of contents:
Using just rebol commands
In order to create a table, you have to use the command db-create, this way:
>> db-create my-table [ID Date Note]
== true
Ok, we create a table named my-table with the columns name: ID, Date and Note.
I didn't specify a type of value for each column, so if I want a description of the table using the db-desc command I obtain:
>> db-desc my-table
== [ID none! Date none! Note none!]
To insert values the command is: db-insert
db-insert my-table [next 1-Jan-2000 "Note"]
Note the use of the special word next to set a new value to the ID, this way we have a unique key (but it isn't necessary).
>> db-insert my-table [next 1-Jan-2000 "Note"]
== [1 1-Jan-2000 "Note"]
Remember that you have to insert always the correct numbers of items of the row:
>> db-insert my-table [next 1-Jan-2000 ]
** User Error: Invalid number of values
** Near: to error! :value
The command db-rows show you the numbers of row:
>> db-rows my-table
== 1
The command db-show shows the current statistics:
>> db-show
== [
"Host" "PC19"
"Address" 192.1.1.1
"Memory" 8023323
"Tables" 1
]
The command db-tables shows the current open tables:
>> db-tables
== [my-table 3 1 true 10-Apr-2012/17:54:47 10-Apr-2012/18:14:42 7 true]
It describes table name, columns, rows, sorted flag, first modify time, last modify time, hits, dirty flag.
Until you don't use db-commit, your changes aren't saved on the disks! So let's use it:
>> db-commit *
== true
Using the asterisk, we save all tables open, I could use also:
>> db-commit my-table
== true
To delete a table there is the db-drop command.
To make a query there is the db-select command:
>> db-select/where * my-table [id = 1]
== [1 1-Jan-2000 "Note"]
Examples:
>> db-insert my-table reduce ['next now to-string now]
== [2 10-Apr-2012/18:43:32+2:00 "10-Apr-2012/18:43:32+2:00"]
>> db-insert my-table reduce ['next now/time to-string now/date ]
== [3 18:43:52 "10-Apr-2012"]
>> db-select * my-table
== [1 1-Jan-2000 "Note" 2 10-Apr-2012/18:43:32+2:00 "10-Apr-2012/18:43:32+2:00" 3 18:43:52 "10-Apr
-2012"]
>> db-select/header * my-table
== [1 1-Jan-2000 "Note" 2 10-Apr-2012/18:43:32+2:00 "10-Apr-2012/18:43:32+2:00" 3 18:43:52 "10-Apr
-2012" [my-table ID Date Note]]
Using the refinement header you always know table name and columns.
The reserved functions of db.r are:
- db-desc
- db-describe
- db-rows
- db-show
- db-table?
- db-tables
- db-close
- db-commit
- db-create
- db-drop
- db-rollback
- db-lookup
- db-select
- db-delete
- db-insert
- db-truncate
- db-update
- db-replay
>> ? db-desc
USAGE:
DB-DESC 'table /header
DESCRIPTION:
Information about the columns of a table.
DB-DESC is a function value.
ARGUMENTS:
table -- (Type: word)
REFINEMENTS:
/header -- Append header block
Using SQL commands
The first thing to do is to load the db.r script and the SQL.r client:
>> do %db.r
== true
>> do %SQL.r
RebDB v2.0.3
RUN> commit *
true
login.sql ran in 0:00 second(s)
SQL>
Every time you launch SQL.r, it always run the SQL script login.sql. You can use it to do automatic tasks.
Now you are in the SQL cliet, so it accept the SQL grammar.
In order to create a table there is the command create:
SQL> create my-table2 [ID Date Note ]
true
The command describe show you how is made a table:
SQL> describe my-table2
Column Type
-------- ----
ID none
Date none
Note none
3 row(s) selected in 0:00 seconds
The type is just the first row types.
Let's try to insert some values:
SQL> insert into my-table2 values reduce ['next now/date "Note1" ]
ID Date Note
-- ----------- -----
1 11-Apr-2012 Note1
1 row(s) selected in 0:00 seconds
Remember that you are always in a rebol console, so you can mix rebol and SQL.
If we check the table now, here it is the result:
SQL> desc my-table2
Column Type
------ -------
ID integer
Date date
Note string
3 row(s) selected in 0:00 seconds
Here the reserved word list of the SQL client:
- avg
- by
- count
- desc
- distinct
- explain
- from
- group
- header
- having
- into
- joins
- max
- min
- on
- order
- replaces
- rowid
- set
- std
- sum
- table
- to
- values
- where
- with
Let's insert more data:
SQL> insert into my-table2 values reduce ['next now/date + 1 "Note2" ]
ID Date Note
-- ----------- -----
2 12-Apr-2012 Note2
1 row(s) selected in 0:00 seconds
SQL> insert into my-table2 values reduce ['next now/date + 3 "Note3" ]
ID Date Note
-- ----------- -----
3 14-Apr-2012 Note3
1 row(s) selected in 0:00 seconds
You can change one or more values with the update command:
SQL> update my-table2 set Date 14-Apr-2014 where [Note = "Note3" ]
1 row(s) updated in 0:00 seconds
With select you can retrieve the data:
SQL> select * my-table2
ID Date Note
-- ----------- -----
1 11-Apr-2012 Note1
2 12-Apr-2012 Note2
3 14-Apr-2014 Note3
3 row(s) selected in 0:00 seconds
SQL> select * my-table2 where [date > 1-1-2013]
ID Date Note
-- ----------- -----
3 14-Apr-2014 Note3
1 row(s) selected in 0:00 seconds
Remember: nothing is written on the disk, until you use commit:
SQL> commit *
true
The command rows show you the numbers of row of a table:
SQL> rows my-table2
3 row(s)
The command tables show you the open tables:
SQL> tables
Table Cols Rows Sorted? Loaded Accessed Hits Dirty?
--------- ---- ---- ------- -------------------- -------------------- ---- ------
my-table2 3 3 true 11-Apr-2012/13:04:37 11-Apr-2012/13:42:55 9 false
1 row(s) selected in 0:00 seconds
The command table? say to you if a table exits:
SQL> table? my-tabe2
false
SQL> table? my-table2
true
