Mailing List Archive: 49091 messages
  • Home
  • Script library
  • AltME Archive
  • Mailing list
  • Articles Index
  • Site search
 

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