sqlrelay module for s9y

Hi,

The forum of s9y(board.s9y.org)  seems down.
So I post here.

I modified include/db/mysql.inc.php to make s9y can use
sqlrelay,
It works fine to me.
I don't have to worried about the connections of my
database.

It should work also for other db.(postgresql,sqlite...etc.)

Just add a line to serendipity_config_local.inc.php like
this:

$serendipity['dbPort'] = '9000';


9000 is the port number that sqlrelay listens to.

And don't forget to modify other settings like:
$serendipity['dbType']  = 'sqlrelay';   ==> because the
file is named sqlrelay.inc.php
$serendipity['dbHost']  = 'localhost';  ==> change to the
host that sqlrelay runs.
$serendipity['dbUser']  = 'relayuser';  ==> change to
what you defined in sqlrelay.conf
$serendipity['dbPass']  = 'relaypass';  ==> change to
what you defined in sqlrelay.conf


you can get sqlrelay.ini.php in this email as the
attachment.

I hope this modification will do some helps.

Dante Mason

------------------------------------------------------------
-------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and
a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
php-blog-devs mailing list
php-blog-devslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/php-blog-
devs

Hi Dante!

> The forum of s9y(board.s9y.org)  seems down.
> So I post here.

Thanks a lot! 

> I modified include/db/mysql.inc.php to make s9y can use
sqlrelay,
> It works fine to me.
> I don't have to worried about the connections of my
database.

I sadly haven't ever used sqlrelay yet, so I can't test it
right now.

However, a few questions about the sqlrelay.inc.php file:

1. Could you fetch the Port by parsing the
$serendipity['dbHost'] variable for a
thing like "localhost:9000"? This would then not
require a new dbPort variable
and make it better usable for other people that wouldn't
have to edit their
files.

2. You use a loop like:

for ($r_row=0; $r_row<sqlrcur_rowCount($cursor);
$r_row++) {

however, better would be to use:

for ($r_row = 0, $max_r = sqlrcur_rowCount($cursor) ; $r_row
< $max_r; $r_row++) {

because then, the row count would not need to be checked for
every loop. Same
applys to the second for-loop below it, and for all other
rows where you use a
for() loop with a count() variable (like in your
serendipity_db_query function
call below).

3. You issue two calls to sqlcur_getField:

$return_row[$r_row][$r_col]=sqlrcur_getField($cursor,$r_row,
$r_col);
$return_row[$r_row][sqlrcur_getColumnName($cursor,$r_col)]=s
qlrcur_getField($cursor,$r_row,$r_col);

however, performance-wise it would be better to use:

$return_row[$r_row][$r_col]=sqlrcur_getField($cursor,$r_row,
$r_col);
$return_row[$r_row][sqlrcur_getColumnName($cursor,$r_col)]=$
return_row[$r_row][$r_col];

because that saves you another function call to the DB
layer.

4. It seems you did not wrap the
serendipity_db_affected_Rows() function?
However this is required for some serendipity operations,
that will not work, if
the function does not properly return the number of
changed/affected rows.

5. The same as 4.) applies to the
serendipity_db_updated_rows() and
serendipity_db_matched_rows() function. You can search/grep
the s9y sourcecode
to find occurences of those two functions to see how/when
they are used.

6. The escapeSimple() method looks a bit too simple to me,
wouldn't things like
' break the parser and allow to inject SQL?

7. The serendipity_db_reconnect() function still uses a
mysql_query() command,
is this intentional?

Thanks a lot for your work, I'd love to add your code to the
core once the
questions/things above can be resolved?

Best regards,
Garvin

-- 
++ Garvin Hicking | Web-Entwickler [PHP]    | www.garv.in |
ICQ 21392242
++ Developer of   | www.phpMyAdmin.net      | www.s9y.org

++ Make me happy  | http://wishes.garv.in


------------------------------------------------------------
-------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and
a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
php-blog-devs mailing list
php-blog-devslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/php-blog-
devs

Hi Garvin !

Thanks for your advices, I'll modify them and post to here
again.

about 1.
Yes, that's a great idea. I'll do it.

about 2. and 3.
Ha, you are right again, I'll follow your advices.

about 4. and 5.
Due to the sqlrelay reference,
(http://sqlrelay.sourceforge.net/sqlrelay/api/php/doc.ht
ml)
"Not all databases support this call. Don't use it for
applications
which are designed to be portable across databases. -1 is
returned by
databases which don't support this option."

Yes, I could just use that function
"sqlrcur_affectedRows",
but this function needs a cursor as a parameter,
in fact, I would not know which cursor should be used.
Because once a sqlrelay connection established,
sqlrelay will require more than one cursor to do the db
things.
I think this would be a problem. I'll try to figure it out.


about 6.
sqlrelay does not implement any function
doing that "escape" or "quote" things
I try to use addslashes, It works fine.
But what if someone still use Big5 or GBK as the charset?
(Even in PEAR, it uses the native function like
"mysql_real_escape_string")
Anyway, I'll write another escape function for this.
Yes, the escapeSimple I write is too simple, and stupid.
:-p

about 7.
That's not intentional, that was my negligence. I'll fix it.
:-p


Due to sqlrelay is just a connection pool,
there are still some works need to do.
I'll try ny best.

Thanks for your response again.

Regards,
Dante Mason

On Thu, Oct 04, 2007 at 10:17:39AM +0200, Garvin Hicking
wrote:
> Hi Dante!
> 
> > The forum of s9y(board.s9y.org)  seems down.
> > So I post here.
> 
> Thanks a lot! 
> 
> > I modified include/db/mysql.inc.php to make s9y
can use sqlrelay,
> > It works fine to me.
> > I don't have to worried about the connections of
my database.
> 
> I sadly haven't ever used sqlrelay yet, so I can't test
it right now.
> 
> However, a few questions about the sqlrelay.inc.php
file:
> 
> 1. Could you fetch the Port by parsing the
$serendipity['dbHost'] variable for a
> thing like "localhost:9000"? This would then
not require a new dbPort variable
> and make it better usable for other people that
wouldn't have to edit their
> files.
> 
> 2. You use a loop like:
> 
> for ($r_row=0; $r_row<sqlrcur_rowCount($cursor);
$r_row++) {
> 
> however, better would be to use:
> 
> for ($r_row = 0, $max_r = sqlrcur_rowCount($cursor) ;
$r_row < $max_r; $r_row++) {
> 
> because then, the row count would not need to be
checked for every loop. Same
> applys to the second for-loop below it, and for all
other rows where you use a
> for() loop with a count() variable (like in your
serendipity_db_query function
> call below).
> 
> 3. You issue two calls to sqlcur_getField:
> 
>
$return_row[$r_row][$r_col]=sqlrcur_getField($cursor,$r_row,
$r_col);
>
$return_row[$r_row][sqlrcur_getColumnName($cursor,$r_col)]=s
qlrcur_getField($cursor,$r_row,$r_col);
> 
> however, performance-wise it would be better to use:
> 
>
$return_row[$r_row][$r_col]=sqlrcur_getField($cursor,$r_row,
$r_col);
>
$return_row[$r_row][sqlrcur_getColumnName($cursor,$r_col)]=$
return_row[$r_row][$r_col];
> 
> because that saves you another function call to the DB
layer.
> 
> 4. It seems you did not wrap the
serendipity_db_affected_Rows() function?
> However this is required for some serendipity
operations, that will not work, if
> the function does not properly return the number of
changed/affected rows.
> 
> 5. The same as 4.) applies to the
serendipity_db_updated_rows() and
> serendipity_db_matched_rows() function. You can
search/grep the s9y sourcecode
> to find occurences of those two functions to see
how/when they are used.
> 
> 6. The escapeSimple() method looks a bit too simple to
me, wouldn't things like
> ' break the parser and allow to inject SQL?
> 
> 7. The serendipity_db_reconnect() function still uses a
mysql_query() command,
> is this intentional?
> 
> Thanks a lot for your work, I'd love to add your code
to the core once the
> questions/things above can be resolved?
> 
> Best regards,
> Garvin
> 
> -- 
> ++ Garvin Hicking | Web-Entwickler [PHP]    |
www.garv.in | ICQ 21392242
> ++ Developer of   | www.phpMyAdmin.net      |
www.s9y.org
> 
> ++ Make me happy  | http://wishes.garv.in
> 

------------------------------------------------------------
-------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and
a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
php-blog-devs mailing list
php-blog-devslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/php-blog-
devs

Hi Dante!

> about 4. and 5. Due to the sqlrelay reference,
> (http://sqlrelay.sourceforge.net/sqlrelay/api/php/doc.ht
ml)
> "Not all databases support this call. Don't use it
for applications
> which are designed to be portable across databases. -1
is returned by databases
> which don't support this option."

Hm, but MySQl, PostgreSQL and SQLite all support this. So at
least for those
types IMHO it should be supported, as it's really required
for full operation by
serendipitys and its plugins.

> Yes, I could just use that function
"sqlrcur_affectedRows",
> but this function needs a cursor as a parameter, in
fact, I would not know which
> cursor should be used. Because once a sqlrelay
connection established,
> sqlrelay will require more than one cursor to do the db
things. I think this
> would be a problem. I'll try to figure it out.

Sadly this concept of cursors is a bit new to me. Maybe
there's a way to always
store the last cursor of the serendipity_db_query() function
somewhere? the
affected/matched/updated rows are always only queried for
the LAST executed
query, never for an older query....?

> about 6. sqlrelay does not implement any function doing
that "escape" or "quote"
> things I try to use addslashes, It works fine.
> But what if someone still use Big5 or GBK as the
charset?
> (Even in PEAR, it uses the native function like
"mysql_real_escape_string")
> Anyway, I'll write another escape function for this.
> Yes, the escapeSimple I write is too simple, and
stupid. :-p

Thanks, that's great. 

> Due to sqlrelay is just a connection pool,
> there are still some works need to do. I'll try ny
best.

Great work already, I'm eager to see this in the final
repository 

Best regards,
Garvin

-- 
++ Garvin Hicking | Web-Entwickler [PHP]    | www.garv.in |
ICQ 21392242
++ Developer of   | www.phpMyAdmin.net      | www.s9y.org

++ Make me happy  | http://wishes.garv.in


------------------------------------------------------------
-------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and
a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
php-blog-devs mailing list
php-blog-devslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/php-blog-
devs

Hi Dante!

Great work! I'll inspect your changes next week and try to
put them into the s9y
release. The changes you listed sound very good, I think
that should work out
fine. I'll also try to find the time to setup an sqlrelay
environment. We'll
declare that DB extension as "experimental" first.
I think the usage of sqlrelay
is still low, so having a sort of "testing"
extension to support that should be
fine.

Best regards,
Garvin
-- 
++ Garvin Hicking | Web-Entwickler [PHP]    | www.garv.in |
ICQ 21392242
++ Developer of   | www.phpMyAdmin.net      | www.s9y.org

++ Make me happy  | http://wishes.garv.in


------------------------------------------------------------
-------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and
a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
php-blog-devs mailing list
php-blog-devslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/php-blog-
devs

Hi Garvin,

I just made some modification to sqlrelay.inc.php.

This version works really fine on my server.
(sqlrelay + mysql on Debian etch)

1.
I also check the postgresql and sqlite,
about these 2 functions:
serendipity_db_matched_rows, serendipity_db_updated_rows,
they do the same things as serendipity_db_affected_rows
does.
So I let those 3 functions doing the same thing in
sqlrelay.inc.php.
The difference is using sqlrcur_affectedRows 
instead of the specific db function.
(ex: mysql_affected_rows, pg_affected_rows, sqlite_changes)

2.
But due to MySQL is so special,
I can not implement "mysql_info".
So I still use sqlrcur_affectedRows in matched_row and
updated_rows.
I hope that's too bad.

3.
As I mentioned in the last reply,
sqlr_affectedRows needs a cursor as the parameter.
I solved this by adding a "cursor reference id" in
object $serendipity
when a query sends.
I tested with adding reply and delete reply in articles,
It works.

4.
I added database type identification in many functions,
because different db has different sql statements to query.
something like "begin transaction", "IN (...)
clause", 
"limit statement" and "last insert
id"...etc.
Even the "serendipity_db_schema_import" are
different.

5.
about serendipity_db_escape_string,
I use the similar way as 4.
If the specific db funciton exists,I use it to escape
string.
If it doesn't, I use addslashes.
I tested this many times with lots of backslashes and single
quote,
it works fine.(at least in my machine, it works fine.)

6.
about the for-loop problem, I modified them as your
advices.

7.
I removed the $serendipity['dbPort'] variable,
Now it fetches the port and host 
by parsing the $serendipity['dbHost'] like
"localhost:9000"


I would express my appreciation if someone setups a 
sqlrelay environment to help me to test more(or find bugs).

Regards,

Dante Mason


------------------------------------------------------------
-------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and
a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
php-blog-devs mailing list
php-blog-devslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/php-blog-
devs

Hi Dante!

Sorry, it has taken be some time to get a glimpse on your
PHP module. I've
changed a few indentation/coding style things and just
committed your code to
our 1.3 SVN trunk. I haven't had the time to check the
module fully, but I think
it should basically work fine, so I leave the testing to
volunteers. 

Best regards,
Garvin

-- 
++ Garvin Hicking | Web-Entwickler [PHP]    | www.garv.in |
ICQ 21392242
++ Developer of   | www.phpMyAdmin.net      | www.s9y.org

++ Make me happy  | http://wishes.garv.in


------------------------------------------------------------
-------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and
a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
php-blog-devs mailing list
php-blog-devslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/php-blog-
devs