SJMySQLConnection class proposal

[joehoyle]

Whilst writing the PHP server API, in most cases it is a case of matching endpoint to MySQL queries. For example /databases?username=x&password=x... maps to a "SHOW DATABASES" MySQL query.

This is also the case for /tables, table_headers, rows etc. I think it would be better if this were actually kept in the Cappuccino side of things. So all we need to the backend is a way to connect and a way to query.

Just querying can be a little messy, we would probably want some helper methods like:

queryColumn
queryResults
query

This is so data that is returned via JSON can be mapped properly. This is how ez_sql works (the PHP MySQL wrapper class). If you know you want a column from a results set you can do:

$db->get_column( "SELECT user_name FROM users WHERE user_login LIKE '%me%'" );

With a straight query it would return a results set for each row, with the column as the only object within the row. get_column takes care of that and just returns an array of the column values which you selected.

Bottom line: would it be better to move all the MySQL queries / login into the App, this way we don't have to duplicate this stuff for the backends. Also, I am an advocate of doing as much as possible on the client side.

A SJMySQLConnection would only need a few methods.

• query:(CPSTRING)aQuery callback:aCallback
• getColumnForQuery ^^
• getResultsForQuery ^^

These methods would call [SJAPIRequest sendRequestToQuery:aQuery] it's self (with possible modification), so the general parts of the app are not making calls to SJAPIRequest but instead to SJMySQLConnection

What do you think about this?

[joehoyle]

I pushed an example of this to a new branch on my fork here: https://github.com/joehoyle/Sequel-J/commit/008ff0ece497f1c54e1b2bd8833c88692543fb93

[fernyb]

My idea is to call a web service and have a it return a well formatted response that doesn't need to be manipulated any more. And in the future my idea is to be able to query different types of databases like MongoDB, PostgreSQL, etc.. So keeping logic in the server side makes more sense as it can be tested better.

[joehoyle]

Ok that makes sense. Abstract from MySQL, works for me.