World: r3wp

Thought it was about time I started trying some of the great work 
done by the community and stop relying on the slightly less than 
friendly flat-file data storage I've been using.  RebDB is very interesting 
so far.

I think it's a little inconvenient that I can't order by a column 
that I'm not selecting. For example "db-select/order [name] mydb 
lasttime" fails :( Is it possible to work around this, so I don't 
have to select both name and lasttime, because it clutters my code?

If RebDB supported this then it would be no more efficient than doing 
it in your own code (i.e. RebDB would have to retrieve the column 
data required to sort on and then go through another process to discard 
it from the result set). Doing this in your own code is usually rather 
simple, as in:

	foreach [col-i-want col-to-discard] RebDB-query [
		do something with col-i-want
	]

or just using 'extract in simpler cases.

no problems.. didn't know about extract so now I'm using that in 
conjunction with random/only :)

I don't think I understand the purpose of "predicate" in db-update, 
where you don't wan't to use /where..

ie anyone wanna explain? :)

That one got me too.  You don't use /where if you are using a key 
value   db-update database field "Value" 3   will hit the first record 
with key 3.   "key "in this sense means first field in the order 
of the column names.  A block without /where would be implied [value-to-compare-to-first-field 
value-to-compare-to-second-field-value...]  It can be a little weird 
when there are duplicates and you don't use secondary keys to filter. 
There is more to it or course.  But I always use /where syntax.  
:)

thanks alot mate!

The "logic" is that db-update (and db-delete) are logical companions 
to both db-lookup and db-select ... so they have to operate comfortably 
in either mode (key or predicate). It is quite possible to have a 
db that *only* operates in "key" mode and *never* uses predicates! 
;)

I've only used MySQL a little, and quite a while ago, but I _think_ 
update (without where) affected the entire table there. Could be 
wrong though :)


Anyhow, I really love RebDB (And RebGUI also). It's really easy to 
use, and my program would probably be alot messier if I wrote my 
own data management functions :P

You are correct, with a standard SQL DB a delete or update statement 
without a where clause will affect every row in the table. With RebDB 
you can achieve the same thing by providing a where clause as follows:

	where [rowid > 0]


and for delete operations don't overlook the truncate (or drop) functions.

Works great.. Thanks!


You know, a really cool feature for RebDB would be to use tables 
stored on an ftp instead of locally. I think it would be possible 
since it's RAM based and you control your commits, but I could be 
wrong :) Now I'll have to sync my database manually every time I 
change computer

RebDB does have a built in client / server mode if that will help. 
  db-client.r  can talk with SQL.r from just about anywhere.  Or 
try  set path  with an ftp url, it may just work, never tried but 
looking at the code and the use of base-dir it seems like a reasonable 
chance of success.

Correct,

	db/base-dir: ftp://user:[pwd-:-ftp-:-site-:-com]/public_html/
	db/lines?: false


The second line will help minimize the amount of traffic by turning 
line breaks off.

Is it possible to have some tables locally and some on the ftp? Maybe 
I can make a copy of "db" and use it in parallell?

Table location is a global setting, so no. You can run two copies 
of %db.r (with different base-dir settings), but they won't share 
the same memory space.

That will be good enough for me, since I just want to synchronize 
each row in a table every now and then.

This ftp based storage is kind of neat.  I have to update my users' 
tables, and need to provide a script that updates their firebird 
tables.  I can embed the data in the update script, but this sounds 
a much better idea.

Ashley are you planning to release 2.0.3 as a production version?

I find that the db/base-dir is getting set to the directory from 
which the db.r is loaded.  Is this behaviour intentional?

>> what-dir

== %/Users/peter/Desktop/RebDBTest
/
>> do %~/Code/Library/Rebol/RebDB-202/db.r

Script: "RebDB server" (14-Jan-2006
)
== none

>> what-di
r
== %/Users/peter/Desktop/RebDBTest
/
>> db/base-di
r
== %/Users/peter/Code/Library/Rebol/RebDB-202/

Sorry about the formatting..as you might guess I'm running AltME 
on a Mac.

Same here ... and yes that behaviour is intentional. 2.0.3 is stable, 
it just requires some doc updates. I'll see about releasing it in 
a day or two.

Thanks no rush on my part.

2.0.3 released. Release notes can be found here:

	http://www.dobeash.com/RebDB/release-notes.html

Ashley, which is easiest to use with RebGUI? RebDB or your sqlite.r? 
 Also, how do you decide which one you will use for a particular 
project?

Depends on how much data and how much complexity you need. RebDB 
is good for under a million rows where the column data is short (i.e. 
not storing 1MB strings). SQLite is a better choice for lots/big 
rows or where you need non-trivial joins/views. I'd use RebDB for 
simple apps like a "Contacts" DB, and SQLite for anything requiring 
half a dozen or more tables.

Ok, thanks. That make the choice easier.

Whoa.  2.0.3; that was a pleasant surprise.  Thanks Ashley.

hi,  how a can do a select with "like "     =>  select * from table 
 where name like 'RA%'

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