World: r3wp

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

Nice, Thanks.

Is there any reason mysql will not accept a nested block in a text 
field?
I have no problem with sqlite storing the same data...

you mean molded nested block - text?

no, where can I find it?

ups.. wrong channel

for example:
[["edfefwf" 3.4.5]["gdegerg" 4.5.6]]

do you "mold" it? what kind of field do you try to store it in?

maybe sqlite molds it automagically ?

I've tried text and varchar

text has no issues in sqlite

SQL command is a string so IMHO you have to mold it and enquote it 
as normal text and then there should be no problems .. what kind 
of errors does it throw?

I molded it and it inserted into mysql but now I need to see if after 
selecting it that it behaves properly in my app....
thanks for the help

yeah, my app crashes I need to convert back to block...

yes you need to deserialize it after retrieval.

I'm using 'mold/only' saving to mysql and
'to-block'  to re-block it...
is there a cleaner way like an 'unmold'?

Also, i have some datestamp issues.
rebol attaches the zone code to now when getting the date/time

but when using mysql timestamp I do not get a time zone attached 
and its screwing me up.
Is there a way to add time zone to datestamp in mysql?

hm.. I think you use load to get rebol data back from string

load "[... ]"

right...duh!

From what I have been able to read about timestamps and mysql I might 
as well insert the client's time...
But then I would need to issure clietn's clocks are correct....
What is SOP for this situation?

insure

Quick question about computer time,,,
Eastern Time is GMT-5 which my computer clock is set to...
why does rebol give me time with -4:00?
7-Jun-2009/20:24:01-4:00

daylight saving?

ok, but what is rebol reading to get the time?

I just did an update to my server time and my other computer..

I used time.nist.gove for the server and time.windows.com for the 
other computer and they were two minutes apart