Sqlite Column Aliasing

December 26, 2023 Post a Comment

Premise I recently ran into a bug in a select statement in my code. It was fairly trivial to fix after I realized what was going on, but I'm interested in finding a way to make su

Solution 1:

One thing that I've done before is to move the commas to the beginning of the line. This allows some benefits. First, you can instantly see if there are any commas missing. Second, you can add a new column at the end without having to modify the previously last line.

Missing:

select
  the
, quick
, brown
  fox
, jumped
, over
, the
, lazy
, dog
from table_name;

Not missing:

select
  the
, quick
, brown
, fox
, jumped
, over
, the
, lazy
, dog
from table_name;

Solution 2:

You could wrap your SQL calls in a function that would either:

Iterate over the columns in the result set, checking for column names containing a space

Accept both the SQL statement and an integer intended number of columns, then check the result set to make sure the number of columns matches what you intended.

Solution 3:

I have the same problem that you do. I have used make and the perl script to do a "lint" like check on my code for a long time. It has helped prevent a number of mistakes like this. In the makefile I have:

lint_code:
    perl lint_code.pl <file_1.php

The perl file is:

$st = 0;
$line_no = 0;
while (<>)
{
   $line_no++;
   $st = 1if ( /start-sql/ );
   $st = 0if ( /end-sql/ );
   $st = 2if ( $st == 1 && /select/ );
   $st = 3if ( $st == 2 && /from/ );
   if ( $st == 2 && /^[ \t]+[a-zA-Z][a-zA-Z0-9]*[ \t*]$/ )
   {
      if ( ! /select/ )
      {
         printf ( "Possible Error: Line: $line_no\n" );
      }
   }
}

I surround my select statements with comments //start-sql and //end-sql. I hope this helps. I have changed the regular expression to reflect how you formatted your SQL as I have been using a different format (with the commas in the front).

As a part of my build/test process I run a set of checks over the code. This is a less than perfect solution but it has helped me.

(I am having a little difficulty with the stackoverflow rich text editor changing my code. Hopefully I will learn how to properly use it.)

Solution 4:

write a comma before the name

first
,short
,medium
,longlonglong
,...

first,
short,
medium,
longlonglong,
...

also makes it really easy to see the list of sql select arguments

works in any IDE :)

Solution 5:

If you have columns with similar names, distinguished only by suffix numbers, you've already lost. You have a bad database design.

And most modern developers use SQL generators or ORMs these days, instead of writing this "assembly language" SQL.

Lab Sql