Wednesday, 11 April 2012

How to use Rebdb

RebDB is a simple database script, you can access to all the most common database function using it. You can downland RebDB from: http://www.dobeash.com/RebDB/RebDB-203.zip

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
Use the question mark for more information:

>> ? 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



See also

For more information see also:

No comments:

Post a comment