SQLite Forum

Novice question about employing more complex SQL versus procedural code
Login

Novice question about employing more complex SQL versus procedural code

(1.1) By Gary (1codedebugger) on 2020-12-22 03:12:58 edited from 1.0 [link] [source]

I was reading several posts yesterday, as I have recently become accustomed to doing, even though nearly all of them are well beyond my current skill level. Sometimes I read them for the humor alone; but, more importantly, in a recent one Dr. Hipp commented:

For years, I've been giving talks that encourage developers to embrace the power of the SQL query language - to move beyond simple SELECT and INSERT statements mixed with a pile of procedural code and instead write complex queries that give the answer directly....a few lines (or a few dozen lines) of SQL can replace thousands and thousands of lines of procedural code. Doing so reduces the number of bugs (which are roughly proportional to the number of lines of code) and also often result in faster solutions as well.

I figured that was all well beyond my simple usage of SQLite, but have been working in C with snprintf to build JSON strings from query results to pass back to the requesting application and have many lines of code to build the strings and just a few lines for the SQLite parts. Thus, I thought, perhaps it does apply to me and I ought to start learning a better way.

Would you tell me please, for example, if this query returns twenty-five rows, can SQL be used to convert the result to a single row something like [{"k":id,"n":name,"p":"path"},{"k":id,"n":name,"p":"path"}...]? Or, is looping through the result rows and writing to a character buffer just as efficient? Or, is this task not a good use case for Dr. Hipp's advice.

How does one know when it is better to employ a more complex query that might build a bunch of intermediate tables to provide a more finalized result versus employing that which appears simpler and more in one's control? By in control mean that, to a novice, it seems that a small change in query criteria can result in a complex query's intermediate steps growing and that's sort of difficult to see.

Thank you.

select id, path, name
from media
where id in (
select media_id
from media_tags
where tag_id in (
select id
from tags
where name in ( 'name_1', 'name_2', 'name_8', 'name_23', 'name_52' ) )
group by media_id
having count( media_id ) = 5 );

(2) By jake on 2020-12-22 03:44:00 in reply to 1.1 [link] [source]

Would you tell me please, for example, if this query returns twenty-five rows, can SQL be used to convert the result to a single row something like [{"k":id,"n":name,"p":"path"},{"k":id,"n":name,"p":"path"}...]?

select json_group_array(json_object('k', id, 'p', path, 'n', name)) 
from media
where id in (
select media_id
from media_tags
where tag_id in (
select id
from tags
where name in ( 'name_1', 'name_2', 'name_8', 'name_23', 'name_52' ) )
group by media_id
having count( media_id ) = 5 );

(3) By Gary (1codedebugger) on 2020-12-22 07:18:46 in reply to 2 [link] [source]

Oh, boy, am I stupid! I read about the JSON extension and compiled my sqlite3.o file with the extension enabled and then forgot all about it.

Thank you. That saves a few loops.

(4) By Ryan Smith (cuz) on 2020-12-22 10:56:33 in reply to 3 [link] [source]

If you are not afraid to do a bit of string concatenation, you can even get that without the JSON extensions, something like this (adapted from Jake's example):

SELECT GROUP_CONCAT('{"k":"', id, '", "p":"', path, '", "n":"', name,'"}')) 
  FROM media
 WHERE id IN (
    select media_id
      from media_tags
     where tag_id in (
        select id
          from tags
         where name in ( 'name_1', 'name_2', 'name_8', 'name_23', 'name_52'
    )
)
 GROUP BY media_id
HAVING count( media_id ) = 5 )
;

Of course if you have strings with possibly JASON character contamination you will have to sanitize that yourself too, with something like:

in stead of simply:
SELECT ... , path, ...
do:
SELECT ... , replace(replace(path, '/','\/'), '"','\"'), ...

or some such construct.

(Yes, the JSON extension makes this much easier, just showing that this could be achieved even in a pre-JSON SQLite version).

(5) By Gary (1codedebugger) on 2020-12-22 20:06:51 in reply to 4 [source]

Thank you for these examples and reminder that there are built-in aggregate functions in SQLite.

I had my little tool nearly finished in a different application but ran in to some problems with data storage limits and structure and decided to try writing a C utility to use SQLite in a limited manner. But now that I see how much better it is to code in C, including the SQLite APIs, I think that, if I can get a better grasp of how to integrate the two properly, I can likely move much of my code from the other application into C and use the other application for the UI only.

I was anxious to use SQLite to sort of patch up my data-storage problems and finish my project but, now, think it would be well worth the time to slow down and restructure everything to take full advantage of both C and SQLite.

Thanks again because these examples, although likely simple for most on this forum, do more than just answer a specific question in that they point one to a broader set of options to employ elsewhere. I appreciate it.

(6) By Gary (1codedebugger) on 2020-12-26 00:19:00 in reply to 4 [link] [source]

I couldn't get the query to work as posted, but only as:
GROUP_CONCAT( '{"k":"' || id || '","p":"' || path || '","n":"' || name || '"}' )

Is it possible to provide more than two arguments to GROUP_CONCAT?

(7) By Keith Medcalf (kmedcalf) on 2020-12-26 01:02:08 in reply to 6 [link] [source]

No.

The first argument (required) is the data element to concatenate.

The second (optional) parameter is the "separator sequence" to be used between elements (default is ',').

(8) By jake on 2020-12-26 02:54:27 in reply to 6 [link] [source]

This is an example of different SQL engines having different implementations of the same function. The multi-argument version in Ryan's example is supported in MySQL.

Another related example is the difference in the MySQL and SQL Server implementations of the scalar Concat(x1, x2, ...) function (not supported in SQLite). Both implicitly cast the arguments to a string datatype, except for the case of NULL which in MySQL nullifies the whole result, but in SQL Server is treated as an empty string.

e.g.:

-- MySQL
SELECT Concat('a', NULL, 'b');
-- result: NULL

-- SQL Server
SELECT Concat('a', NULL, 'b');
-- result: 'ab'