Include SQLite Code from One File Into Another?
Is there a way to include code from one .sql file into another?
For example, I sometimes have queries that I also use as sub-queries in a larger query. I can copy and paste the original query into the larger query. But that means that in effect I'm having to maintain two copies of the original query. If I make a change in the original, I also have to remember to make the same change in the larger query where I pasted it as as a sub-query.
I would instead like to have some sort of INCLUDE statement that's not an actual SQL statement but instead causes SQL statements to be included. The C and C++ world do this very nicely with the #include statement, but I can't think of a way to do it for SQL statements. I was hoping maybe NOTEPAD++ would have something like this and if so I could edit SQLite files with NOTEPAD++. But I can't find a feature like that in NOTEPAD++.
Interesting question. SQL statements can get to be sooo big.
If you simply want to run a set of .sql script files in sequence, then a simple windows cmd file (batch) will do the trick.
If you want to include different parts of a single statement from different files, then a simple #include won't work. The included text usually has to get inserted in specific places in the main statement, not just at the start of the file.
I guess stored procedures would be a way to help management, but SQLite doesn't support them.
I'm sure you're already aware of what I just said. Just giving a little background for other readers.
This would be very helpful to "call" vs cut/paste method.
I think I did something of the sort way back using Windows cmd or bat files to build a sql file from other sql files and other bridging statements and then execute the result on a database via the sqlite CLI. But it might be a false memory and conflated with what I did in Visual C# in creating RMtrix. And both may have been only as @richardotter has described, i.e., a series of unconnected operations.
One thing I have long intended to explore was to build a sqlite database of scripts which I could connect to any selected RM database - maybe a RMtrix 2 ;<). The database would hold each script and metadata about each one to aid finding and using them. An ability to reuse a script as a module in another would be attractive.
This may be off-base but if you are accustomed only to the command line SQLite and SQLiteSpy which require you to open a database in order to load and execute a script and then close the db, open another and reload the script to execute... a program like SQLite Expert or SQLite Studio might appeal as they allow multiple databases to be 'registered' and multiple scripts to persist as you select different databases to which you apply a script.
And, iirc, SQLite Studio includes support for LUA (and other?) scripting which might have capability to make one query a sub-query in another. Maybe worth exploring.
Then there are SQL Views which you've exploited in the past.
And, what about custom functions? I'd love to have one that decodes EventTable.Date (the date of an event), into plain language.
I recently started using SQlite Expert Personal -- before that I used DB browser (SQlite). As for the date I used various combination of substr () and reformat using the Date-Time Sqlite functions.
something like this:
SELECT
EventID, EventType, OwnerType, OwnerID
FamilyID, PlaceID, SiteID,
Date, IsPrimary, IsPrivate,
Proof, Status, Details,
UTCModDate, Note,
CAST(substr(Date,4,4) as int) as Year,
CAST(substr(Date,8,2) as int) as Month,
CAST(substr(Date,10,2) as int) as Day
FROM EventTable
Quote from kevync on 2023-07-12, 10:13 pm... As for the date I used various combination of substr () ...
...which could be wrapped in a custom function. Your example is very basic as it extracts only the first of possibly two dates embedded in the encoded event date field. It ignores date ranges and all the modifiers (e.g., after, before, between...), Quaker and double dates. That requires a much more complex decoding process, something like Date Decoder
The comments you all provided are much appreciated. They pretty well confirm my own thinking. There doesn't seem to be an easy way to do the kind of INCLUDE that I need.
However, I actually have come up with a solution of sorts. It doesn't involve any kind of INCLUDE facility. Instead, it involves the ability of a command line COPY command to combine several text files into one. For example, with the Windows command line you can do something like the following.
COPY A*.SQL B.SQL
This is with the plain old Windows command line COPY command, and there are more advanced versions of the COPY command such as ROBOCOPY. This example COPY command will take all SQL files whose first letter is A and put them together into a single file named B.SQL.
That doesn't solve the whole problem, but it solves about 95% of it. My particular application was that I needed to do something like the following.
SELECT COUNT(*)
FROM
(
-- any query of your choosing goes here.
)
This will give you a count of how many lines would be produced by the query if it were to be run by itself. The "queries of my choosing" are the ones for which I wanted to do an INCLUDE. So I rewrote the "queries of my choosing" to read as as follows.
--qqSELECT COUNT(*)
--qqFROM
--qq(
-- any query of your choosing goes here.
--qq)
Essentially, I put the enclosing COUNT(*) query into the original query and commented it out in such a way that I could uncomment it out globally by changing the string --qq to null. I like to include the sequence qq in such things because it very seldom appears naturally.
What I actually had was a bunch of queries where I need a summery giving the count for each query. This is my project to compare RM7 and RM9 databases table by table and row by row. When I run my summary query, the output looks something like the attached file. If I run the summary and see a non-zero, then I can run the detailed query just for that table. After running the COPY command, a quick edit with Notepad or NotePad++ to uncomment out the --qq lines produces a new summary query that includes all the most recent edits for each of the tables. I only have to do the COPY and the edit of the --qq lines if I know I have updated one or more of the individual queries. There are 13 individual queries in all.
Uploaded files:
I used Include for Php for my website -- it is very handy. I have not attempted anything like that in Sql/Sqlite.
The attached screenshot shows the results of a custom function rmdatedecode() that I created in SQLiteStudio from the SQL script on Date Decoder. It's a long convoluted script that I've incorporated in a number of other scripts making them even longer and more convoluted and harder to debug so I've long wished to make it into a function that could be called from any script.
I think I tried some years ago with SQLiteStudio but found it crashed too easily. It seems much more reliable now and has a lot of potentially good features.
@thejerrybryan, I think you might find SQLiteStudio to be an easy solution to your "Include" wish. Make each script a function or procedure so that they can be called as needed. That said, you might find other reasons that would make the app unsuitable.
Uploaded files: