World: r3wp

I think the find clause here might work for you: http://www.dobeash.com/RebDB/sql-guide.html#section-4

Don't expect it to be fast though.

thank you very much BrianH

Ah, I just noticed all the "(br)" entries ... that'll teach me for 
changing make-doc versions! ;)

Fixed.

Is RebDB disk based or is the enitre DB loaded into memory?

Entire DB is loaded in memory. If that is a problem, use SQLite.

How much is involced in using sqLite? And how large is it?

225K..small enough

The REBOL driver is a little smaller than RebDB, but there is a dll 
that is about 200k. The databases are smaller, I think. Look in the 
SQLite group here for details.

. .

Ashley - how do I overcome this problem:  I need to reduce the record 
block prior to db-inserting it because it contains sub-blocks. I 
would like to use 'next inside for autoincs, however.....

hi ashley, i would like to know if you  would port rebdb to R3 ?

Kai: use "reduce ['next ...]
Claude: I will, but only when R3 goes Beta.

Latest version of RebDB appears to work under R3 with only 2 minor 
changes:

	1) Change the Umlaut u on line 3 to a normal u
	2) Change the 'return on line 214 to 'exit


Once I've had some time to run a few QA tests I'll upload these changes 
as 2.0.4 (along with a few other maintenance fixes).

cool

The other big news is that I've commenced an R3 specific rewrite 
of RebDB focusing more on the Storage Manager (as opposed to the 
Database Manager). Preliminary results, pre-optimization, look very 
promising:

Script: "RebDB server" Version: 2.0.3 Date: 13-Apr-2007
Rows ...... 10,000 * 2
RAM Used .. 1,225 Kb
Insert .... 0:00:05.02291
RAM Used .. 6,497 Kb
Delete1 ... 0:00:42.43421
RAM Used .. 5,346 Kb
Delete2 ... 0:01:13.110128
RAM Used .. 6,100 Kb

Script: "RebDB Storage Manager" Version: 3.0.0 Date: 1-Feb-2010
Rows ...... 10,000 * 2
RAM Used .. 1,029 Kb
Insert .... 0:00:00.689558
RAM Used .. 4,568 Kb
Delete1 ... 0:00:05.103824
RAM Used .. 1,991 Kb
Delete2 ... 0:13:47.026307
RAM Used .. 1,991 Kb


Delete1 is primary key-based (10,000 deletes), whilst Delete2 is 
query-based. Apart from the sluggish query performance, what's noticeable 
is the more efficient use of memory. I've opted for a mixed binary! 
storage design where fixed-width fields are stored in RAM in a single 
binary! with pointers into a disk binary. The idea is that you'll 
generally want fixed width records in memory to query against, with 
variable length records (BLOBs) accessed on disk less frequently 
(e.g. specific text/binary attachments).


All this is then wrapped up into an object so creating a table is 
as easy as:

	test1: db-create [integer! 8 string! 12 string! 15]


with all other commands (db-insert, db-update, db-delete, etc) working 
as under v2. Oh, I've also cut the code size from 1,300 lines to 
less than 400 (75% complete). The final goal is to deliver what RIF 
promised ... a simple storage mechanism that provides the basic building 
blocks required by higher-level database systems (akin to ISAM or 
VSAM files).

does it do joins? :-)

but honestly, Ashley ... absolutly great work. I was using RebDB 
initially, untill I needed few joins, and found out beauty of SQLite.

sounds great

great news !!!!

All new RebDB v3 released for REBOL3. To take it for a spin, try 
this:

import http://idisk.me.com/dobeash/Public/rebdb.r
help db-
test: db-load http://idisk.me.com/dobeash/Public/test.bin
help test
sql select test


Extensive documentation in the works (within a week) ... actually 
a large part of the doc deals with db design [from my POV] covering 
off on the trade-offs with fixed vs variable length records/fields, 
typed vs untyped columns and RAM vs speed optimization. Needless 
to say, I think I've got the balance about as good as is possible 
with pure REBOL mezz code. This has been a long time in the making 
...

Cool! You can repost in Announce group .... and it could go into 
rebolweek too :-)

What's the final code size?

Is it still pure ram based?

amacleod - I would expect it being only RAM based. However - even 
old version contained transaction log, so that in the case of power-loss, 
it automatically rolled all the changes in.

>> length? read/binary http://idisk.me.com/dobeash/Public/rebdb.r
connecting to: idisk.me.com
== 12771

... so it's already mid-sized REBOL app :-)

I was wondering in terms of number of lines as he mentioned above...compared 
to r2 version...

Still under 500 lines in my editor

A testament to R3 code?

With cloud tech so big now a cool product for rebol would be some 
kind of server/client DB with built in syncronization methods. I 
do not know if RebDB can be adapted to fit that bill...

this is very cool.. I hope I will be able to study a little what 
you did.. I was lately playing with creating a simple data storage. 
To learn more about things and because sqlite has some bad sides 
, and mysql is not appropriate for my particular usecase.

You can repost in Announce group ...
 Need to finish documentation first.
Is it still pure ram based?
 ... Yes, but with two important differences:


 1) Values are stored in a binary! (reducing RAM overhead by about 
 50%)

 2) Reading/Writing a table to disk is easier/faster (as data is already 
 in binary! ... no conversion required)


Think of RebDB v3 as not so much a "database system", but a module 
that adds a new datatype ... table! ... which can be easily acted 
upon like any other REBOL datatype.

old version contained transaction log

 ... all the higher-level stuff is gone. It's purely a Storage Manager 
 now that enables you to create higher-level DBMS systems.
A testament to R3 code?

 ... Definately. The new 'apply and 'assert words among others make 
 things so much easier.
I do not know if RebDB can be adapted to fit that bill

 ... it can. RebDB provides the building blocks for higher-level abstractions 
 (much like ISAM files were/are the building blocks for DBMS's such 
 as DB2 and MySQL).
I hope I will be able to study a little what you did.

 ... I'd wait for the documentation ... the code is highly optimized 
 and rather terse. Without the conceptual model it's rather hard to 
 grok at first glance.

Running from a script I get this error:

** Script error: sql does not allow word! for its 'arg1 argument
** Where: catch either either applier do
** Near: catch/quit either var [[do/next data var]] [data]e

Run it again I get this:

** Access error: cannot open: %test/rebdb/test-rebdb.r reason: -3
** Where: read case load applier do
** Near: read source case [
    binary? :content [content]
    string...

Typing "sql select test" in the console works. 

Great stuff as usual Ashley.

Yeah, the 'sql command is just a console wrapper for the underlying 
functions. I got tired of typing "db-select test [] []" ... but scripts 
should definately use the db- functions directly.


If you see some strange query results (wrong row(s) being returned 
or no rows returned at all) ... this is probably due to the binary 
equality bug I posted in !REBOL3. I'm putting some workaround code 
in (== instead of =) but working around < and > is a bit tricky! 
;)

Ashley - so RebDB3 is not full fledget DB anymore? I mean - if there 
is no functionality for rolling a log, what happens if there is a 
power outage for e.g.? Or you write to disk each time, so that RebDB 
is not memory-only DB anymore?

It works like any other REBOL datatype now, except a table! has structure 
and defined methods for data access. It's all in-memory ... the developer/user 
decides when/if they save/load data to/from disk and how often (and 
the disk write/reads are much faster as the data is binary! already).

RebDB 3.0.1 uploaded (same access instructions as before).


Draft documentation available at: http://www.dobeash.com/RebDB/rebdb3.html

Ashley, if you operating on binary! as a storage I think it would 
be possible to easily redirect the DB engine  to a file! port opened 
in binary mode no?

does that mean disk based possible?

Yes. I deliberately designed it so that the index binary (which ideally 
should always be memory resident) is as small as possible and that 
the data binary only requires seek and append operations (which are 
well suited to disk access). I need to do a few more benchmarks, 
but the next release should include a "data on disk" option.

Oops, just noticed I'd left the ";type:'module" header entry commented 
out ... fixed.

Ashley would you be so nice and write a little bit about indexing 
in RebDB? Do it work automatically  for all columns, or may the indexed 
columns be presetted? what in memory representation do you use (map, 
list, block?). Is indexing done automatically during insertion, or 
is it indexed by search? THX IA

Ie I understand from documentation basic index contains offsets to 
each respective record and is coded as binary, but where are the 
indexes used for quick search on columns?

May the hashes be used for record numbers? ie large non monotones 
integers?

http://www.dobeash.com/RebDB/rebdb3.html#section-4.6gives a brief 
but complete idea of how indexing works. The low-level implementation 
is quite simple:


 - The index is a binary! with each n bytes (n = idx-size, defaulting 
 to 2) representing on offset into the data binary! (start of row).

 - The index is sorted by/with a number of columns (left to right) 
 equal to the number of key columns (minimum of 1).
	- Updates and inserts that break this order set a reindex flag.

 - Lookup and seek both check the reindex flag prior to searching 
 the index binary! using a binary search ( http://en.wikipedia.org/wiki/Binary_search_algorithm
 ).

 - Lookup returns a single row (where # key values = # key columns)

 - Seek uses Lookup to return a range of values (where # key values 
 <> # key columns) and then does a linear match on each row


I'll devote a bit more time/space in the documentation to flesh this 
out with examples.

Thank you Ashley for brief description.

I've got strange results, don't know if intended:
do %rebdb.r
table: db-load %test.bin
db-select table [][]
== [1 1 "Name-1-1" 1 2 "Name-1-2" ...   ; this is OK

db-select table [2][]
== [1 2 3 4 ... ;this is OK

db-select table [2 3][]
== [1 "Name-1-1" 2 "Name-1-2" ... ;this is OK

db-select table [2 3][1 = c1]

== [1 1 "Name-1-1" 1 2 "Name-1-2"  ;first column should not be selected 
into result

db-select table [3][1 = c1]
== [1 #{0101} "Name-1-1" 1 #{0102} "Name-1-2" ... ;weird

Is it possible to put in more than one condition?