World: r3wp

Thanks olds...I think that will making everything simpler for me 
too.

Oldes

I think there is no limit on number of tables. A database in MySQL 
is implemented as a directory containing files that correspond to 
tables in the database.

I still can't create db but never mind as I'm going with tables...Thanks 
again

I got it!

insert db [create-db "test2"] works flawlessly here....You've probably 
connected to the server with a user that didn't had enough rights 
for creating databases, but it's hard to figure out from a "does 
not seem to work" issue description.


I start to better understand Carl's willings for filtered communication 
channels that improve the signal to noise ratio in order to save 
some valuable working time...

I've run into situations where MySQL will fail to do DDL without 
throwing an error, at least not one the mysql:// driver notices. 
Haven't tested with the /Command driver or qcmysql:// though.

The DDL in question is create table statements where some obscure 
semantic rule is violated. By obscure, I mean it took me a day to 
track down the error in the MySQL manuals.

You should try with the official command line mysql client. If you 
notice a different behaviour than the mysql:// driver, please report 
it here.

Does the /Command client support MySQL 5.1?

Don't know, I think it doesn't support anything above 3.x but I can't 
find the full /Command feature list on rebol.com to confirm.

Btw, I've never tested my driver with a 5.1.x server.

Yeah, but you're driver has the source. Also, in the ancient times 
before COmmand 2.x and your mysql:// I did a library interface, it's 
still on rebol.org

I use your driver with 5.1.x no problem 8-)

Good to know! Thanks.

for large query results is there a way to show a progress bar as 
an indicator of the download progress?

Is there some type of callback method like "read-net"?

No, there's no such callback. But you can easily add one in my driver 
by inserting a call to "read-net" in the FOREVER loop inside READ-ROWS 
function. Can you describe briefly the situation where you need to 
get large query results and display a progress bar (just curious)?

I'm hosting a large number of text docs broken up by sections in 
a mysql db. Once all the material is on line the changes (and thus 
downloads) wil be smal for the most part. But in the mean time large 
amounts of material are still to be uploaded to the db and when a 
client finds this new material it will download it and any changes 
to previous material. The material contains a lot of image data too 
so it could add up. Right now I have about 40 megs of data and I 
have about 10X more to upload.


If its too tuff for me I will just display some animated gif but 
I would prefer something more tangible..

I'll look into your suggestion, Thanks Doc...


BTW I tried the latest mysql-protocol and it broke my app. I have 
not had a chance to look into it but I think you changed some syntax 
for selects??
I'm using Version: 1.1.2 at the moment.

Thanks for the additional info. The main change in 1.2.0 is the new 
READ syntax, and it's documented here : http://softinnov.org/rebol/mysql-usage.html#sect2.2.

Doc, I played around with read-net but no luck.

Would not read-net need to know total size of query result to work?
Does the server send this info first?


also, read-net wants a URL. Where am I pointing it to? Is this the 
same as the db port i'm using?

I'm just understanding now what you're trying to do. READ-NET is 
not what you want to use, it's for downloading files! You have to 
write your own progress bar function. About the total size of result 
set, AFAIK, MySQL server doesn't send that information, it just marks 
the last record of a result set.

Thanks anyway...

Has anyone released a sphinx Rebol client api implementation?

There is this API document being worked on http://sphinxclient.codeplex.com/SourceControl/ListDownloadableCommits.aspx

I have made a primitive client libs to the SOLR search engine, if 
that helps anyone

it's not much though - but I can share without problem if anyone 
needs

Can you put it on rebol.org in the library.

This is interesting http://sphinxsearch.com/news/37.html.. you can 
now connect to the sphinx searchd daemon using the mysql binary protocol 
... so doc's mysql driver can also now connect to sphinx??

I will put it on rebol.org

Maarten has one that's waiting Reichart approval to get released 
8)

That was a year ago ...

Here's an interesting conversation !  http://www.sphx.org/forum/view.html?id=1210

Interesting indeed. I'll pick this up with Reichart; we are all in 
favour, it merely is a matter of time (pressure). The libs are nice, 
but I am sure others can help improving them.

is doc's mysql protocol the binary one they talk about?

I think so.

wow ... two for the price of one!

.

hi doc, 
is there any trick or encoding to prevent 
inserting errors due to strings containing 
special characters like ' or /  etc?

Hi TomBon, when using prepared statements, special characters are 
encoded in passed values.


send-sql db ["UPDATE table SET field=?" string]	; string value will 
be encoded here

ahh...thx doc. I formed the insert classical without question marks.

There's also an alternative approach, the SQL-ESCAPE encoding function 
of MySQL driver is exported in the global context, so you can use 
it when forming SQL queries directly :

send-sql db join "UPDATE table SET field=" sql-escape string


The prepared statement approach is recommended, because the driver 
will care about that for you automatically (so less risks that you 
forget to encode one value, opening a hole for SQL injection attacks).

yes, will use the prepared statement. it is also more elegant. with 
escaping I need to handle all fields to be save.

btw. many thx to provide such a cool, free and very important driver!

Thanks :-)

Need some advice on db structure.
I'm not sure which would be better for this senario:


I want to store user generated data which could consist of thousands 
of rows. But I also will want to be able to search across each users 
data.


I could create a seperate table for each user and join tables when 
searching through all user data or I could make one large database 
with a user field to seperate out that user's data when needed.

There could be many hundred users.

What is SOP in such a case?

Is the users' data freeformat, or fixed fields?

ie do they each have the same data fields (but with different values), 
or can they define whatever data they want to store?

Each table will be the exact same stucture

I guess fixed fields

Then it'd be normal to have one large table.

But (and I iguess this is your question): are there worthwhile (perhaps 
essential) performance improvements by denormalising? Perhaps partitioning 
different users on different drives or servers.
Sadly, normalisation theory is silent on that subject.

Can you experiment with some generated fullsize data and see?

No, not really...

One table would be much easier and I do not think the db would get 
so large that I would see major performance problems. (I always underestimate 
the speed of these db's.)

Thanks for the advice, Sunanda.

One table will be much easier to manage. Two thoughts:


If you use Views to create "virtual tables" for each user, then you 
can (fairly) easily change the underlying structure without disrupting 
the application too much should you need to for performance reasons.


If your data does grow large, then partitioning my help performance 
-- ie having one table split across multiple drives according to 
data values (such as user name):
http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html