REBOL and database stuff
[1/12] from: mat:plothatching at: 30-Mar-2003 15:52
Howdy,
I suppose this is a bit of a longshot given how quiet things are these
days on the REBOL front... Anyhow - I've been tasked with setting up a
bulk mail system for an advertising client. I didn't really like much
of the (copious) commercial software to do this so I've decided to do
it myself in REBOL.
The major bit of this is database handling. For something similar in
the past I used db.r from "Jamey Cribbs" and enjoyed the elegance of
that system. However I seem to have a couple of odd things happening
with that and the latest REBOL version and I doubt flat-file databases
would be good with 5,000 fields.
So I've dusted off REBOL/Command and had a quick toy with talking to a
proper MySQL database. Despite knowing virtually nothing about MySQL
(or indeed databases in general), I seem to have it up and running
even with the basic documentation concerning REBOL/Command's database
features (on rebol.com).
It struck me though, had someone used this seriously they may have
written some easier more REBOLesque handling functions as an interface
between REBOL and SQL queries. After all that's what I'll end up doing
if this proves to be a negative.
Has anyone done anything like that? Or perhaps you've just done a fair
bit of database manipulation with REBOL/Command and MySQL and you
wouldn't mind tossing me the script to take a 'butchers' at as we say
in my parts.
Thanks folks.
Regards,
Mat Bettinson - +44-(0)20-83401514.
[2/12] from: mat:plothatching at: 30-Mar-2003 17:48
Hello Mat,
MB> Has anyone done anything like that? Or perhaps you've just done a fair
MB> bit of database manipulation with REBOL/Command and MySQL and you
MB> wouldn't mind tossing me the script to take a 'butchers' at as we say
MB> in my parts.
Maybe it might help if I had a specific query :)
> The next example inserts multiple REBOL values into an SQL statement:
> fname: "Johnny"
<<quoted lines omitted: 5>>
> } fname lname title phone
> ]
This demonstrates how you insert REBOL variables into an SQL
statement. I have no trouble with that although it is a bit weird and
not very REBOL like.
Unfortunately one doesn't appear to be able to substitute 'table1' for
an ? there, so you can't formate a specific table request.
I thought, "No problem, I'll just construct the SQL query myself" and
I did that, inserted it to the port and it barfed. So maybe I was
missing something there.
Has anyone else done a workaround for that?
Maybe I should have just slapped all my data into one big table rather
than having many. A little counter-intuitive to me though.
Regards,
Mat Bettinson - +44-(0)20-83401514.
[3/12] from: petr:krenzelok:trz:cz at: 30-Mar-2003 18:49
Hi,
sorry, no framework here, but I mady myself few functions:
- function for getting db structure - sending 'collumns query
- then I build object of db structure
- and have 'map-rec functions, which is able to map returned records to
object for better navigation later in script ...
But as far as SELECT is involved, it can be tricky, as you may need
various joins etc., so queries may be rather complex. Maybe you could
elaborate a bit what exactly you need to achieve?
-pekr-
[4/12] from: greggirwin:mindspring at: 30-Mar-2003 10:41
Hi Mat,
MB>> Has anyone done anything like that? Or perhaps you've just done a fair
MB>> bit of database manipulation with REBOL/Command and MySQL and you
MB>> wouldn't mind tossing me the script to take a 'butchers' at as we say
MB>> in my parts.
Have you looked at DocKimbel's MySQL interface. People seem to like
that a lot, though I've done nothing more than read the docs on it
myself.
>> The next example inserts multiple REBOL values into an SQL statement:
>>
<<quoted lines omitted: 6>>
>> } fname lname title phone
>> ]
Here's an example for Doc's MySQL:
author: "Elan"
title: "Rebol Official Guide"
insert db ["insert into mybooks values (?,?)" author title]
MB> Unfortunately one doesn't appear to be able to substitute 'table1' for
MB> an ? there, so you can't formate a specific table request.
What about using COMPOSE, or REPLACE with a template query string, to
create your SQL statement? Not sure why it might have choked on the
one you created.
MB> Maybe I should have just slapped all my data into one big table rather
MB> than having many. A little counter-intuitive to me though.
You mentioend having 5,000 fields in your first message. What the heck
do you have in there!? ;) Can you provide an idea of what fields are
important, how many records it needs to handle, and how you need it to
work (e.g. do you get lots of updates from somewhere, mostly filtering
to send groups of emails, etc.)
What would you want in a native REBOL database?
-- Gregg
[5/12] from: mat:plothatching at: 30-Mar-2003 19:13
Hello Gregg,
GI> Have you looked at DocKimbel's MySQL interface. People seem to like
GI> that a lot, though I've done nothing more than read the docs on it
GI> myself.
I actually stumbled across it yes. I just thought since I have a
REBOL/Command license that might be preferable.
Certainly not too late for me to shift!
GI> Here's an example for Doc's MySQL:
GI> author: "Elan"
GI> title: "Rebol Official Guide"
GI> insert db ["insert into mybooks values (?,?)" author title]
Yeah same sort of thing as REBOL/command. Identical actually.
Hmm, so... how's he talking to SQL. Does this also require /command?
MB>> Unfortunately one doesn't appear to be able to substitute 'table1' for
MB>> an ? there, so you can't formate a specific table request.
GI> What about using COMPOSE, or REPLACE with a template query string, to
GI> create your SQL statement? Not sure why it might have choked on the
GI> one you created.
You know on reflection, that may have been my fault. It ought to work
shouldn't it? So I'll retry that me thinks.
MB>> Maybe I should have just slapped all my data into one big table rather
MB>> than having many. A little counter-intuitive to me though.
GI> You mentioend having 5,000 fields in your first message. What the heck
GI> do you have in there!? ;) Can you provide an idea of what fields are
GI> important, how many records it needs to handle, and how you need it to
GI> work (e.g. do you get lots of updates from somewhere, mostly filtering
GI> to send groups of emails, etc.)
Right this is my fault for being a total database weenie. I meant
5000 records, not 5000 fields. There's not much in the way of actual
fields. Just name, e-mail, and various fields which have to do with
tracking the user, whether their e-mail has bounced and whether
they've asked to be unsubscribed.
I guess that's not really important now we've established the rows
don't have 5000 fields :)
GI> What would you want in a native REBOL database?
Nothing that's not in that lovely db.r really. It's just that it'd
probably get very slow or break with 5000 records.
My immediate requirement is 3000 records and I had set about doing
that with db.r and suspected it would work. But I thought better to do
the work now, for when I need to do one later in the month with 50,000
records.
I also thought it'd be pretty fast. However just inserting a new row
into a datbase with less than ten fields... it's only doing 2-3 a
second. Can that be entirely down to the MySQL server being on another
machine? Surely it's faster than that?
Regards,
Mat Bettinson - +44-(0)20-83401514.
[6/12] from: tim:johnsons-web at: 30-Mar-2003 11:42
* Mat Bettinson <[mat--plothatching--com]> [030330 10:02]:
> Hello Gregg,
> GI> Have you looked at DocKimbel's MySQL interface. People seem to like
<<quoted lines omitted: 4>>
> Certainly not too late for me to shift!
> GI> Here's an example for Doc's MySQL:
If you can set up mysql, DocKimbel's mysql protocol is absolutley
wonderful..... works with /core
If you are not familiar with sql, you will find it easy to
learn and the mysql mailing list very helpful.....
--
Tim Johnson <[tim--johnsons-web--com]>
http://www.alaska-internet-solutions.com
http://www.johnsons-web.com
[7/12] from: tim:johnsons-web at: 30-Mar-2003 11:45
... and forgot to mention .... DocKimbel also has a PostreSQL
driver now too..
http://rebol.softinnov.org/mysql/ ;; last time I checked.
--
Tim Johnson <[tim--johnsons-web--com]>
http://www.alaska-internet-solutions.com
http://www.johnsons-web.com
[8/12] from: gchiu:compkarori at: 31-Mar-2003 12:04
On Sun, 30 Mar 2003 15:52:45 +0100
Mat Bettinson <[mat--plothatching--com]> wrote:
>I suppose this is a bit of a longshot given how quiet
>things are these
>days on the REBOL front... Anyhow - I've been tasked with
>setting up a
>bulk mail system for an advertising client. I didn't
>really like much
Eeek! A nascent spammer.
--
Graham Chiu
http://www.compkarori.com/cerebrus
[9/12] from: greggirwin:mindspring at: 30-Mar-2003 19:55
Hi Mat,
MB> Certainly not too late for me to shift!
Shouldn't be too different, at least to see which works best for you,
and as Tim said, it works with Core.
MB> I guess that's not really important now we've established the rows
MB> don't have 5000 fields :)
:) Well, I haven't used db.r, but I just glanced at it and it loads
each line individually it seems, and creates just an empty block to
load them into as a starting point. If loading the database is what's
slow for you, just changing those things might help quite a bit. Also,
if doing lots of inserts deletes, using a list! would be faster for
that. It does a linear search to delete a record, so that could
probably be sped up as well. Same for update. Selecting records could
probably be sped up as well, but I'd have to spend more time to see
what he's actually doing.
MB> Nothing that's not in that lovely db.r really. It's just that it'd
MB> probably get very slow or break with 5000 records.
Gotta profile it to be sure, unless you already know exactly what
parts are slow about it.
MB> My immediate requirement is 3000 records and I had set about doing
MB> that with db.r and suspected it would work. But I thought better to do
MB> the work now, for when I need to do one later in the month with 50,000
MB> records.
For 50K records you probably want a true DB.
MB> I also thought it'd be pretty fast. However just inserting a new row
MB> into a datbase with less than ten fields... it's only doing 2-3 a
MB> second. Can that be entirely down to the MySQL server being on another
MB> machine? Surely it's faster than that?
Dunno. I've heard that MySQL can peform pretty well and I'd have to
guess that not many big folks would use it if that was as fast as it
went.
-- Gregg
[10/12] from: tim:johnsons-web at: 30-Mar-2003 19:10
* Gregg Irwin <[greggirwin--mindspring--com]> [030330 18:43]:
> Hi Mat,
> MB> Certainly not too late for me to shift!
<<quoted lines omitted: 27>>
> guess that not many big folks would use it if that was as fast as it
> went.
Using rebol with mysql has given me better performance than "C"
with a compiled in DBMS. Talking about hundreds of thousands
of records....
The nice thing about using rebol to "drive" mysql is that one is
using one fine product to exploit another.
Interoperability at it's best.
I've put together a couple of text-based dbms with rebol and
after a small learning curve with mysql - I wouldn't go back
to them.
And from what DocKimbel has told me, the PostreSQL driver
gives even faster performance.
tim
--
Tim Johnson <[tim--johnsons-web--com]>
http://www.alaska-internet-solutions.com
http://www.johnsons-web.com
[11/12] from: mat:plothatching at: 31-Mar-2003 8:33
Hello Tim,
TJ> Using rebol with mysql has given me better performance than "C"
TJ> with a compiled in DBMS. Talking about hundreds of thousands
TJ> of records....
Absolutely, I'm sure it's just something I'm doing wrong. One of the
factors may be that I have MySQL on the slowest hard drive known to
man. :)
TJ> I've put together a couple of text-based dbms with rebol and
TJ> after a small learning curve with mysql - I wouldn't go back
TJ> to them.
Yep. Which is what makes me think that some sort of helper functions
between the raw SQL queries and your code would be quite nice.
TJ> And from what DocKimbel has told me, the PostreSQL driver
TJ> gives even faster performance.
Thanks for that. I've installed MySQL and it works though, so I'm
reluctant to fix things that aint broke.
My performance is pretty bad. The server is no mean machine to be sure
and it's not on the same machine as the REBOL, but they're on the same
network. Quite odd. I guess I'll shift the REBOL to run on the same
machine and that ought to help.
Regards,
Mat Bettinson - +44-(0)20-83401514.
[12/12] from: mat::plothatching::com at: 31-Mar-2003 8:28
Hello Graham,
>>bulk mail system for an advertising client. I didn't
>>really like much
GC> Eeek! A nascent spammer.
Heh, no. This is really big name clients, brands you would recognise,
that people have typed their e-mail address in on their sites. All
the money in the world wouldn't make me spam :)
Regards,
Mat Bettinson - +44-(0)20-83401514.
Notes
- Quoted lines have been omitted from some messages.
View the message alone to see the lines that have been omitted