byterock

It is putter about some more day here in the Moose-Pen

I was puttering about today with my tests and I discovered something funny on some implications of SQL and it is to do with that 'AS' when playing with 'alias'. Now I knew it was an optional 'key-word' according to the SQL standard what I did not know is a number of DBs will syntax error when an optional key-word is used. So this SQL

SELECT sys_users.last_name AS last
FROM people AS sys_users

is correct to standard there are a number of DBs out there that only this will work

SELECT sys_users.last_name last FROM people sys_users

That makes the above one almost 100% universal that I can tell. I originally wanted that 'AS' in there to make the generated SQL much more readable but I have come to realize that '99.9995%' of the time the end user Database::Accessor will never care what the end SQL is just that it works.

I guess I better fix that now before I go on to much further. I could just drop alias from the system all together and just go with view names but alias come in handy when you are joining the same table twice in query like this one

SELECT u.user_name,ur.role_name,ul.level_name FROM user u LEFT JOIN user_profile ur ON u.user_role=ur.role_id LEFT JOIN user_profile ul ON u.user_level=ul.level_id

The first thing it to change this test in 15_alias.t;

--$tests->[0]->{retrieve}->{sql}="SELECT sys_users.last_name AS last, sys_users.first_name AS first FROM people sys_users";

++$tests->[0]->{retrieve}->{sql}="SELECT sys_users.last_name last, sys_users.first_name first FROM people sys_users";

and then fix my Driver::DBI so the above will pass and all I think I have to do is take out one line in the '_elements_sql' sub

$sql .= join(" ",

         "",

--         Database::Accessor::Driver::DBI::SQL::AS, 

         $field->alias())

      if ($field->alias());

and while I am here I think I will rename that '_elements_sql' to '_fields_sql' to make it more SQL like, I am not going to add I what I did here as it really is just a book-keeping issue. After the change I still get a 100% pass so far so good.

One thing I did discover while reading though the SQL standard is that this SQL;

SELECT user.first_name “First name” FROM users;

is fully valid though a few DBs that I have encounter (informix) will barf on it. I was thinking of making the code change for this in Database::Accessor but I decided against this as this is a purely SQL issue as I know in MongoDB this could never be an issue.

First there is the test which I accomplish by just adding another item to my test hash

 {

        caption  => 'Field alias with spaces',

        key      => 'elements',

        elements => [

            {

                name  => 'last_name',

                alias => 'Last Name'

            },

            {

                name  => 'first_name',

                alias => 'First Name'

            }

        ],

        create => {

            container => {

                first_name => 'Bill',

                last_name  => 'Bloggings'

            },

            sql =>

              "INSERT INTO people ( first_name, last_name ) VALUES( ?, ? )",

            params => [ 'Bill', 'Bloggings' ]

        },

        retrieve => {

            sql =>

'SELECT sys_users.last_name "Last Name", sys_users.first_name "First Name" FROM people sys_users'

        },

        update => {

            container => { first_name => 'Robert' },

            sql       => "UPDATE people SET first_name = ?",

            params    => ['Robert']

        },

        delete => { sql => "DELETE FROM people" },


    },

Next the changes to my code. I was thinking something just as simple as

$sql .= join(" ",

         "",

         '"'

         .$field->alias()

         .'"')

      if ($field->alias());

but in some DBs that will preserve the case as entered which may lead to some problems with where the DB may be set up to always to return field name only in once case. So I am going to go with;

  if ($field->alias()) {

      my $alias = $field->alias();

      $alias = '"'

              . $alias

              .'"'

        if (index($alias," ") !=-1);

        

      $sql .= join(" ",

         "",

         $alias);

     }

      

     push(@fields,$sql);

and only add in the double quotes when there is a space in the '$alias'.

After that change I still getting a full pass so a good days work.