Queries can include other queries via the $include$ preprocessor directive. The format of the directive is
Includes have the following characteristics:
All preprocessor directives must appear before the first statement of the query.
The included query can be in a different data source. This allows creating libraries of functions.
Includes can be nested. The query engine detects circular includes automatically: If query A includes query B, and query B includes query A, running either query will fail.
"Running" a comment with the $include$ directive in a command window works.
For example, in the Project pane create a query called Lib, double-click the query to open it in the Command Window and enter the following text into the query:
FUNCTION Rad2Deg(@d FLOAT64) FLOAT64 AS
@d * 180 / Pi
FUNCTION Deg2Rad(@d FLOAT64) FLOAT64 AS
@d * Pi / 180
Close the Command Window to save the query. Alternatively, pressing the ! button to run the query also saves it. We can now use the functions defined in the Lib query in other queries after including Lib via the $include$ directive. For example, we could create a query in the Command Window such as:
-- $include$ [Lib]
SELECT value, Deg2Rad(value), Rad2Deg(Deg2Rad(value))
FROM CALL ValueSequence(0, 360, 10);
The above could be run directly from the Command Window or within a query component. Once we run the $include$ preprocessor directive in the Command Window to include a query such as Lib we can thereafter launch additional queries in the same Command Window session that reference functions in Lib without the need to each time re-run the $include$ directive, since each session in the Command Window preserves state.
If we would like to use included queries when working within a data source that has its own query engine, such as SQL Server, we must also add the $manifold$ directive to queries that use $include$ so that such queries will be processed by the Manifold engine.
There is nothing significant about the name Lib in the example above. We could name the included query whatever we want.
How references to scripts, components in statements, and components in functions are resolved depends on the data source of the included query.
In an included query:
For example, suppose we have a data source named DataSrc in our project. This might be a .map project called DataSrc.map we have created and linked into our project as a data source:
Inside the DataSrc data source we have a table called Log with an nvarchar feld called Text:
The Log table starts out empty.
public static int Calc(int number)
return number * 5;
Inside the DataSrc data source we also have a script called CsharpFuncs that is written in C#. The script defines a Calc function that multiplies the argument by 5 and returns the result.
Finally, inside the DataSrc data source we have a query named Inner that contains the following SQL:
FUNCTION calc(@a INT32) INT32 AS SCRIPT [CsharpFuncs] ENTRY 'Script.Calc';
FUNCTION getRootTable() TABLE AS [mfd_root] END;
VALUE @rootTable TABLE = [mfd_root];
VALUE @rootTable2 TABLE = CALL getRootTable();
INSERT INTO [Log] ([Text]) VALUES ('included');
The INSERT in the last line is not good practice: altering data from an included query is normally a bad idea, since writes as a side effect of an include are unexpected. We use it in this example as a convenient kludge.
In the main part of our project (that is, not inside the DataSrc data source) we have the following query, called Example:
-- $include$ [DataSrc]::[Inner]
-- At this point, [DataSrc]::[Inner] will insert a new record into
-- [DataSrc]::[Log], altering data from an included query is normally a
-- bad idea, since writes as a side effect of an include are unexpected
-- Uncomment one of the following lines to show a result.
-- VALUES (calc(2)); -- this calls Script.Calc in [DataSrc]::[CsharpFuncs]
-- TABLE CALL getRootTable(); -- this returns [mfd_root] in the main MAP file
-- TABLE @rootTable; -- this returns [DataSrc]::[mfd_root]
-- TABLE @rootTable2; -- this returns [DataSrc]::[mfd_root]
The Example query uses an $include$ to include the Inner query from within the DataSrc data source. When we run the Example query, the included text will run first, which will insert a text line into the Log table inside the DataSrc data source. It then will run whichever of the lines at the end of the query we uncomment.
For example, if we uncomment the VALUES line which calls the Calc function in the included script:
VALUES (calc(2)); -- this calls Script.Calc in [DataSrc]::[CsharpFuncs]
...the result reported by running the Example query will be 10, which is 2 * 5.
Uncommenting and then running one of the other lines will operate with results as given in the comments. For example, uncommenting this line:
TABLE CALL getRootTable(); -- this returns [mfd_root] in the main MAP file
...will run the getRootTable() function in the context of the project, not in the context of the data source. It will therefore return [mfd_root] in the main .map file and not in the data source.
In contrast, uncommenting and then running one of the last two lines will refer to a global value defined within the included Inner query, and thus the result returned will be in the context of the DataSrc data source, which is the data source of the included Inner query.
The same as EXECUTE <query> or SELECT * FROM <query> will run the referenced query, -- $include$ <query> in essence will also run the query as well. There are two important differences how the include runs the query compared to how EXECUTE or SELECT run the query:
First, EXECUTE and SELECT run the query as a single statement while $include$ runs the query statements individually. Because EXECUTE and SELECT run the query as a single statement the query cannot create components and then refer to them.
Example: Suppose we have a query named q and the text of the query is
CREATE TABLE t <...>; SELECT * FROM t;
If we now use query q in a SELECT statement such as
SELECT * FROM [q];
The SELECT must determine what fields will result from the query q. But the result of q is produced by the second statement within the query q, a SELECT statement, and that statement cannot know what the result is going to be before the first statement in q, a CREATE TABLE statement, is executed. Running the statement...
SELECT * FROM [q];
...requires knowing what results q produces before either of the two statements in q are executed. Since that is not possible statements of the form SELECT * FROM [q] will fail.
In contrast, $include$ does not have that limitation because $include$ runs the query statements individually. Included queries can create components and refer to them, for example, as q does in the example above. The first statement in q is run individually to create table t and then the second statement in q is run individually to select fields from the newly-created table t.
Second, EXECUTE and SELECT run the referenced query in a separate scope while $include$ does not. The separate scope used by EXECUTE and SELECT creates a barrier which protects the referencing query and the referenced query from each other. For example, if the referenced query defines a function that function will only be visible within the referenced query. It will not leak into the referencing query and thus cannot potentially overwrite a different function with the same name in the referencing query.
$include$ is different in that included statements do not run in a separate scope. The entire point of $include$ is to interpret the statements of the included query as if they were part of the including query. Therefore, if the included query defines a function that function will be visible to the including query.
File - Create - New Query
Command Window - Query Builder
Example: Create and Run a Query - See how the different parts of a command window operate when creating and running SQL queries. Includes use of the Log tab as well as the ?expression and !fullfetch commands.
Example: Refer to a Table within a Data Source - Within a query, how to refer to a table that is in a data source.
Example: Switching between Manifold and Native Query Engines - How to use the !manifold and !native commands to switch a query in the Command window from use the Manifold query engine to whatever query engine is provided by a data source.