Recent

Author Topic: Best practice to store long SQL files with you project  (Read 2606 times)

Okoba

  • Sr. Member
  • ****
  • Posts: 478
Best practice to store long SQL files with you project
« on: September 22, 2023, 02:46:39 pm »
Hello,

I want to ask your opinion about what is the best way to store multi line SQL in your code? I dont like converting the code to Pascal string as it will be as ugly as this sample:
Code: Pascal  [Select][+][-]
  1. 'SELECT' + #13#10 +
  2. 'A,' + #13#10 +
  3. 'B,' + #13#10 +
  4. 'C,' + #13#10 +
  5. 'FROM' + #13#10 +
  6. 'Test' + #13#10 +
  7. 'WHERE' + #13#10 +
  8. 'A > C' + #13#10 +
  9. 'And' + #13#10 +
  10. 'C + B = 2'

I thought about resources but they are a little complicated.
Is there other ways like including the text at compile time like .inc files?
I appericate any ideas.

rvk

  • Hero Member
  • *****
  • Posts: 5651
Re: Best practice to store long SQL files with you project
« Reply #1 on: September 22, 2023, 03:10:08 pm »
Is there other ways like including the text at compile time like .inc files?
.inc files are stil code so you would still need to use the quotes etc.

You could just add the SQL in the TSQLQuery itself (via the object inspector).
But that way you can't easily change things in code.
Is that a requirement?

You could also put it in a .txt file and link that in as resource and then create a function which reads the resource and extract the correct SQL.
But then again... you can't change it easily in code.

So what exactly is the requirement because I would put the code you showed directly in the TSQLQuery.
And for doing it in code I would merge some lines together.

Code: Pascal  [Select][+][-]
  1. SQLQuery1.SQL.Text := 'SELECT A, B, C,' + #13#10 +
  2.   'FROM Test' + #13#10 +
  3.   'WHERE A > C And C + B = 2';

Also note that you would probably work with parameters:
Code: Pascal  [Select][+][-]
  1. SQLQuery1.SQL.Text := 'SELECT A, B, C,' + #13#10 +
  2.   'FROM Test' + #13#10 +
  3.   'WHERE A > C And C + B = :wanted_addition';
  4. SQLQuery1.ParamByName('wanted_addition').AsInteger := 2;

paweld

  • Hero Member
  • *****
  • Posts: 851
Re: Best practice to store long SQL files with you project
« Reply #2 on: September 22, 2023, 04:39:13 pm »
Quote from: rvk
You could also put it in a .txt file and link that in as resource and then create a function which reads the resource and extract the correct SQL.
I am just using a similar solution.
I have a small unit where I create a list of strings from defined resources.
a single string looks like this:
Code: [Select]
---<? string-name 
sample text   
with   
few 
lines 
!!!   
111   
one one one   
--?>
   
   
This is very convenient if I use very long queries, texts, etc.   
   
Sample in attachment.
Best regards / Pozdrawiam
paweld

Okoba

  • Sr. Member
  • ****
  • Posts: 478
Re: Best practice to store long SQL files with you project
« Reply #3 on: September 22, 2023, 04:51:53 pm »
@rvk Your solution in case of using TSQLQuery is very good. Thank you.
@paweld, great way to organize. Thank you very much.

I wished Lazarus had a better resource support and not the current simple OS resource management, or FPC had a better multi line string handling, or better of all, a way to include content like
Code: Pascal  [Select][+][-]
  1. {$Import test.sql}

rvk

  • Hero Member
  • *****
  • Posts: 5651
Re: Best practice to store long SQL files with you project
« Reply #4 on: September 22, 2023, 05:10:43 pm »
The multiline option in the source is discussed at length here (in a 11 page topic somewhere) and on the mailinglist.
I don't think it got implemented (topic is from 2019).

For my commercial program I actually use a text file on my webserver for a Report Generator.

The file looks something like this:
Code: Text  [Select][+][-]
  1. ===================================================
  2. Menu title #1>Report title #1
  3. ----------
  4. select id,name,field1, field2, etc
  5. from table1
  6. where name=:name and docdate>=:docdate
  7.  
  8. ===================================================
  9. Menu title #1>Report title #2
  10. ----------
  11. select id,name,field1, field2, etc
  12. from table2
  13. where name=:name and docdate>=:docdate
  14.  
  15. ===================================================
  16. Menu title #2>Report title #3
  17. ----------
  18. select id,name,field1, field2, etc
  19. from table3
  20. where name=:name and docdate>=:docdate
  21.  
  22. etc. etc.
  23.  

The Report Generator retrieves this file and builds a pulldown-menu the user can choose from (besides entering their own SQL).
There are a lot of ready made SQL statements ranging from simple to very complex (with joins/unions etc).
The program build a dialog for the possible parameters when there are any.
The user only has to choose one and press execute (and fill out the form if there is any).

I have that file on my own webserver so I can adjust it easily and all users can access it directly (without the need to update the program).
You could also have this text file in a resource of course.

Okoba

  • Sr. Member
  • ****
  • Posts: 478
Re: Best practice to store long SQL files with you project
« Reply #5 on: September 22, 2023, 05:31:10 pm »
That is a very good sample. Thank you.
Yes I saw dissuasions about it and anyway it will be needed, not for SQL only.

Zvoni

  • Hero Member
  • *****
  • Posts: 2033
Re: Best practice to store long SQL files with you project
« Reply #6 on: September 25, 2023, 09:01:33 am »
Don't store SQL-Statements in plain Textfiles, especially not as multiline (like your example).
For "complex" and dynamic statements i use something like this:
Code: Pascal  [Select][+][-]
  1. program Project1;
  2. Uses SysUtils;
  3.  
  4. Var
  5.   f:Array[0..2] Of String;
  6.   w:Array[0..1] Of String;
  7.   s:String;
  8.  
  9. begin
  10.   f[0]:='A';
  11.   f[1]:='B';
  12.   f[2]:='C';
  13.   w[0]:='A > C';
  14.   w[1]:='C + B = 2';
  15.   s:='SELECT '+String.Join(', ',f)+ ' FROM Test WHERE '+String.Join(' AND ',w);
  16.   Writeln(s);
  17. end.
Returns:
SELECT A, B, C FROM Test WHERE A > C AND C + B = 2

If i have many SQL-Statements, i want to have access from outside, then i store them directly in the Database itself in its own Table
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

rvk

  • Hero Member
  • *****
  • Posts: 5651
Re: Best practice to store long SQL files with you project
« Reply #7 on: September 25, 2023, 09:14:47 am »
Don't store SQL-Statements in plain Textfiles, especially not as multiline (like your example).
<snip>
If i have many SQL-Statements, i want to have access from outside, then i store them directly in the Database itself in its own Table
What do you think is the problem with storing them in one textfiles on my own server (with backup on a webhost) and retrieving them on demand?

For me, storing them in a database on customer side is not convenient because I'm the one changing and adding those SQL commands when needed.
(BTW. The customer has also the option to add their own SQL, or adjust my retrieved SQL, which is stored in the database itself. This is for specific SQL which is only used by that customer.)

This has worked flawlessly for me for over 20 years (with over 100 customers and different installations).
Maintaining my own SQL statements (the shared ones by all customers) in the database of the customer itself would be a nightmare.
Now I can even change it while I have the customer on the telephone. Having them in a database remotely on customer side this would be much more problematic.

But I guess it depends on your use-case (which is why I have a combo of a textfile and database-stored).

Zvoni

  • Hero Member
  • *****
  • Posts: 2033
Re: Best practice to store long SQL files with you project
« Reply #8 on: September 25, 2023, 09:23:17 am »
Don't store SQL-Statements in plain Textfiles, especially not as multiline (like your example).
<snip>
If i have many SQL-Statements, i want to have access from outside, then i store them directly in the Database itself in its own Table
What do you think is the problem with storing them in one textfiles on my own server (with backup on a webhost) and retrieving them on demand?

For me, storing them in a database on customer side is not convenient because I'm the one changing and adding those SQL commands when needed.
(BTW. The customer has also the option to add their own SQL, or adjust my retrieved SQL, which is stored in the database itself. This is for specific SQL which is only used by that customer.)

This has worked flawlessly for me for over 20 years (with over 100 customers and different installations).
Maintaining my own SQL statements (the shared ones by all customers) in the database of the customer itself would be a nightmare.
Now I can even change it while I have the customer on the telephone. Having them in a database remotely on customer side this would be much more problematic.

But I guess it depends on your use-case (which is why I have a combo of a textfile and database-stored).
Each statement has its own unique numeric identifier in that table, so if i have to roll-out an update for statements themselves, it's a basic UPDATE SET WHERE.

I didn't say your approach is wrong, since it works for you.
It's more of a personal thing, that i just plainly dislike having such stuff locally in a plain textfile, where any noob can look inside and start playing with it.
Getting access to a Database-Server is a whole other thing, nevermind in such a case you can restrict read-access to the Statements-Table to an Application-User nobody except DBA's get the password for
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

rvk

  • Hero Member
  • *****
  • Posts: 5651
Re: Best practice to store long SQL files with you project
« Reply #9 on: September 25, 2023, 10:26:53 am »
It's more of a personal thing, that i just plainly dislike having such stuff locally in a plain textfile, where any noob can look inside and start playing with it.
Getting access to a Database-Server is a whole other thing, nevermind in such a case you can restrict read-access to the Statements-Table to an Application-User nobody except DBA's get the password for
Ha, Ok. For me the plain textfile is on my side, not on the customers side. It's something I can change easily and the program retrieves it over internet and uses it.
For the locally changed SQL the statements are of course stored in the database itself (otherwise you would have problems accessing it if the filesystem is restricted in a local network).
And the report generator is restricted per user and only SELECT is possible (with some additional checking for standard hacking practices :) ). There is even some table checking but normally you would want to restrict a report generator, the same way you would want to restrict direct database access via filesystem (unless you don't care about that and want an 'open' system).

But yes, it does shows that you need to think about more than just plumping some text in a file and executing it :)

Someone doing a DROP TABLE X could really mess up your system  ;)

Zvoni

  • Hero Member
  • *****
  • Posts: 2033
Re: Best practice to store long SQL files with you project
« Reply #10 on: September 25, 2023, 10:34:35 am »
Someone doing a DROP TABLE X could really mess up your system  ;)
Which is the worst case!
It's more like users fiddling with the statement, and simply forgeting a comma, having a typo or whatever
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Hansvb

  • Hero Member
  • *****
  • Posts: 521
Re: Best practice to store long SQL files with you project
« Reply #11 on: September 25, 2023, 10:44:58 am »
Hi,

I don't know why you want to include queries, but maybe this is an idea. I once wrote a tool that edits and executes queries. The queries are linked to a database connection, so when you make a connection, you only see the queries that belong to the opened connection. The queries are placed in a tree view and double-clicking on a node starts the query. You can check multiple queries (nodes) and all checked queries will be executed.
The management of the queries is also included in the tool. As you can see in the print screen, 1 query can actually contain multiple queries. The storage is a SQLite database file. The queries are stored encrypted so that no one can accidentally change them outside the tool.
I made this a few years ago with C#, but you can also make this with Pascal. I work a lot with Oracle spatial data. That won't work with the standard Lazarus components.
Maybe it's something you can use as an idea.

Чебурашка

  • Sr. Member
  • ****
  • Posts: 487
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Best practice to store long SQL files with you project
« Reply #12 on: September 25, 2023, 11:08:09 am »
IIRC

Code: Pascal  [Select][+][-]
  1. SQLQuery1.SQL
  2.  

is a TStrings, and you can just make

Code: Pascal  [Select][+][-]
  1. SQLQuery1.SQL.Add('select *');
  2. SQLQuery1.SQL.Add('from Table');
  3. SQLQuery1.SQL.Add('where');
  4. SQLQuery1.SQL.Add('field = :param_field');
  5. SQLQuery1.SQL.Add('and other_field = :param_other_field');
  6.  
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

Zvoni

  • Hero Member
  • *****
  • Posts: 2033
Re: Best practice to store long SQL files with you project
« Reply #13 on: September 25, 2023, 11:22:06 am »
IIRC

Code: Pascal  [Select][+][-]
  1. SQLQuery1.SQL
  2.  

is a TStrings,
in that case you could just use LoadFromFile.
OTOH, it would only work for a single statement per File.
My objections above to such not withstanding
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Чебурашка

  • Sr. Member
  • ****
  • Posts: 487
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Best practice to store long SQL files with you project
« Reply #14 on: September 25, 2023, 11:44:02 am »
I was just thinking about OP's original request to manage the multiline sql into source code.

I normally keep sql in code, even if I understand that somebody prefers to store in external files, because in turn, sql in code means loaded in memory while program runs and this is kinda senseless. I like using json for that or xml, having query name as key and sql text as content. That said I definitely do not split sql into separate lines, but this is just my personal way of seeing the world. All in one line (sometimes is very long!).
« Last Edit: September 25, 2023, 11:47:28 am by Чебурашка »
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

 

TinyPortal © 2005-2018