Database library proposal : human to slq

[eluser]xwero[/eluser]
Strange thoughts happen when you read rss

First my eye got caught 10 Days Later: Early Impressions of CakePHP vs. CodeIgniter but when i saw xquery i was intrigued and the first thing is saw was Babysteps with xQuery where i saw the line

Give me all games that belong to this league

And i began to wonder why do we want to use cut up sql statements

Code:

$this->select(array('field1','field2'))->get('table');

If you could do following

Code:

$this->human_to_sql('get field1 and field2 from table')

Because there are only 4 statements why not try to make them as natural as possible.

I'm not a native English speaker, Dutch is my mother tongue, so i would like to make the human to sql language aware.
But in English is was thinking about following sentences
- select : get (limit) all/(fields) from table that have (field value)
- insert : add (field value) to table
- update : change table with (field value) to (value)
- delete : remove all table/remove from table where (field value)

I don't know yet how to make this working but i will figure out a way

All comments are appreciated!

[eluser]gtech[/eluser]
whoooaaa somthing new, nice to think outside the box, sounds like a good topic for discussion so I'll be polite and reply to your suggestion.

I don't know if its something Id ever use mainly because you would still have to learn a syntax even though it makes sense for anyone to read it straight away, you still have to be aware of what is an acceptable sentance and what is not.

Also you would have to be careful as language can be interpreted in different ways, how would you do table joins?

if you don't want to cut up SQL statement why not just use the $this->db->query(<sql statement>). Another reason for using arrays, is you can easily build them up, append to them remove from them etc, its a little bit more complex to append fields to sentances.

However there may be a place for it... It may be useful for a beginner who does not understand SQL, and would also be good for language translation.

[eluser]CI Lee[/eluser]
This might have a place if you were planning on releasing an app to the world...

I see where you are going with this.

And its interesting at the very least. Not applicable directly to what I do with CI, however gaging by forum questions it would be helpful to some I am sure.

-Lee

[eluser]xwero[/eluser]
@gtech : I write most of my sql statements, so that is not the point.

Now i need to come up with standard sentences, and i could use some help for other languages too.

For a join i was thinking about :

get field1 from table1 and field2 from table2 if table1 field3 is the same as table2 field3

I have to decide if synonyms are allowed but that will be not in the proof of concept code.

Making this work i am thinking about splitting up the sentence using the white spaces because that is the only common thing between languages and then using keywords to do the translation.

It's not even of the drawing board so any help, visions or ideas are welcome.

[eluser]xwero[/eluser]
I think i would keep arrays to escape the values so it would be something like :

human_to_sql('add field1 value1 to table1',array(1))

To make it more readable there could even be a list of tables with the fieldnames to make aliases.

[eluser]Crafter[/eluser]
I think the whole idea of SQL is to alllow human readable database manupilation commands to be passed thorugh to the database.

Compare your

Code:

$this->human_to_sql('get field1 and field2 from table') ;

to the SQL standard

Code:

$this->raw_sql('select field1, field2 from table');

I'm not knocking your effort, and I applaud you thinking out of the box, but I suspect if you keep going, you'll end up with something that closely resembles SQL

[eluser]gtech[/eluser]
Crafter I see what you saying.. I think one advantage xweros proposal gives is that it will support multiple languages (I guess the sentance order will need to change from language to language) as SQL is English driven, what would be good is if it could accept multiple variations on a sentance, but that sounds a bit of a nightmare to code and open to misinterpretation especially on larger queries.

As an idea you could set up a poll on who would find it useful (I have seen polls on the CI forum pages), it only takes a few people to find it useful for it to be a goer I guess.

[eluser]xwero[/eluser]
Thank you for the responses.

It's true the 'human' statements are going to look like a normal sql statement, but they don't have to. Maybe i'm to used to writing sql that a natural sentence could turn out to be something different. I will give some sql statements and i hope you guys would write them in full.

I'm also inviting not native english speakers or multilingualists to write full sentences of the statements in other languages than English.

1. select * from table

2. select field1,field2 from table

3. select distinct field from table

4. select count(*) from table

5. select max(field) from table

6. select field from table where field=value

7. select field from table order by field

8. select field from table limit 10

9. select field from table limit 10,10

10. select field from table group by field

11. select max(field) from table group by field2 having max(field) > 10;

12. select t1.field1,t2.field1 from table1 as t1, table2 as t2 where t2.field2=t1.field2

Try to make the sentences as natural as possible.

Because there are several sql dialects used by the different databases i think i'm going to use the CI database library to get the proof of concept code working as soon as possible.

Even if nobody is interested it's still a go, in my free time i like to write code nobody uses

[eluser]xwero[/eluser]
Crafter you are right about sql being able to write human readable database statements but sometimes it can be confusing. With long statements i write the full statement and translate it into sql.

You could look at it as a helper for lazy sql writers or as a learning tool for sql beginners.

[eluser]CI miller[/eluser]
my 1 cent:

This adds some extra overhead to the application that is not needed. SQL is very readable and if you really want to use other language just make some word mapping and do some search and replace. And commenting the code is always very helpful. If you think an SQL statement is too complicated you can make a comment.
This human_to_sql only translates SQL into another language. You still need to know the meanings of the words to make sense of any sentence, so why not use a more widely used language?

At this pace pretty soon people will want to have a computer that reads their mind and translate it into fully functional bug-free software.