Re: [SQLHat] Proposal

On Wed, Apr 07, 2004 at 01:21:33AM +0100, Wez Furlong wrote: > I'm not sure how well this is going to work out as an extension, but > I do know that my advanced editor (vim) can highlight PHP and SQL at > the same time using heredocs: > > $sql = <<<SQL > SELECT * from foo where bar=$bar > SQL; > > I don't want people to start voting on extensions based on their > perceived usefulness (since that just wastes time); I'd rather the > author sat down and thought about it a bit to decide if it really > will be useful to others before taking it further. > > The other point that needs mentioning is licensing: we can't have > PHP extensions licensed under the GPL (consult the archives for more > detail on this issue). > > Sorry if this sounds negative, Thank you for your remarks, Wez. You understand that the extension I am suggesting has just a protype that happened to be written in PHP. While we were discussing the library at PEAR, I got more and more convinced that it would be better if SQLHat was a PHP-extension instead of PHP class library. You should not doubt that I've considered the usefulness of SQLHat and the project is a result from my considerable experience as a developer. I started the project to make my life easier and then I decided to share it so that I make other developers's life easier. Yes, we can use heredocs but that doesn't solve all the problems. I'd summarize my points. The main problem comes from the fact that the SQL-code is mixed with the program code. * Taking care of quotes is error-prone. * Not being able to use syntax highlighting (yes, vim recognizes some things) * Testing a query without testing the code is a problem esp if we have integrated query parameters. * Migrating to another programming language is a problem as for the SQL-code - different quotation, and different parameters. The solution: * Isolating the SQL-queries in separate program files - each one in a different .SQL file. * Introducing SQL parameter definitions with type specification. Additional features: * We can extend the SQL with "IF" statement for conditionally including fragments of a SQL-query. * Definition of identifiers like table and column names for example. Benefits: * We can use syntax highlighting from an advanced editor that is SQL-aware. * We do not have to deal with quoting the queries as strings. * Testing the queries without testing of the rest of the code is possible. * Migration to a different programming language is possible while keeping the SQL-files intact. * No need for recompilation when modifying SQL-code since SQLHat will be available in other programming languages. SQLHat allows using query parameters and introduces 3 type definitions - string, int and float. The last thing we add is the opportunity to wrap fragments of the query in blocks and to include them conditionally depending on the value of a condition. Before sending a query to the database manager it is read from the file, prepared for applying parameters and processed with an associate array of parameters. I'll try to extend the idea and go beyond the "perceived usefulness" by providing a snapshot of the extension, the way I see it. I would be thankful for any remarks. Until now I haven't thought of how SQLHat will exactly look as a PHP-extension but here is how I see it: Exposed functions ~~~~~~~~~~~~~~~~~ array sqlhat_load_query(string query_file_name) ===> returns parsed query stack string sqlhat_prepare_query(array query_stack, array params) ===> returns the prepared query string sqlhat_process_query(array query_stack, array params) ===> returns the processed query ready for execution array sqlhat_get_parameter_definition(array query_stack) ===> returns assiciative array of the parameter that contains information for their names and types array sqlhat_null(void) ===> returns sqlhat_null value array sqlhat_not_null(void) ===> returns sqlhat not null value bool sqlhat_is_condition(int value) ===> returns true if value corresponds to condition type definition bool sqlhat_is_identificator(int value) ===> returns true if value corresponds to condition type definition bool sqlhat_is_string(int value) ===> returns true if value corresponds to condition type definition bool sqlhat_is_int(int value) ===> returns true if value corresponds to condition type definition bool sqlhat_is_float(int value) ===> returns true if value corresponds to condition type definition Example ~~~~~~~ The shortest way to use the library is as follows: $stack = sqlhat_load_query('example.sql'); $params['year'] = 1991; $params['height'] = 5; $params['admin'] = sqlhat_not_null(); $query = sqlhat_process_query($stack, $params); // now we can send the $query to the database manager In case we want to investigate the names and types of the query parameters we can do the following: $stack = sqlhat_load_query('example.sql'); $pdef = sqlhat_get_parameter_definition($stack); $type = $pdef['admin']; if(sqlhat_is_condition($type)) { // do stuff.. } Considering the license I'm eager to discuss the issue. Regards, -- Kamen A. TOMOV :. http://www.cybuild.com/ .: