The Manifold Command Window provides a dialog for writing and executing queries in SQL and for interactively writing and executing scripts in various programming languages using the Command window as a REPL (Read-Eval-Print Loop) console for the specified language.
SQL - The Manifold query database engine is built into Manifold and thus provides an always-on ability to write and to execute SQL queries.
JavaScript - For programming, JavaScript is built into Manifold using the Google V8 JavaScript engine and thus provides an always-on ability to write and to execute scripts written in JavaScript. Because JavaScript is always available it is the default scripting language used for examples.
IronPython and IronRuby - These languages will be available if we have installed Manifold using the portable installation that includes both IronPython and IronRuby. If we have installed Manifold using an .exe installation package we must install IronPython and IronRuby using additional installation packages as described on the Manifold web site.
Other Languages - If we create a script by right-clicking into the Project pane and choosing New Script, the dialog allows us to choose the script language from C#, F#, IronPython, IronRuby, JavaScript, JScript, JScript.NET PerlScript, PythonScript, VB.NET and VBScript. Because Manifold requires installation of Microsoft .NET all of the languages included in .NET or COM are also automatically available, namely C#, JScript, JScript.NET, VB.NET and VBScript.
Important:
The View - Command Window
command includes IronPython and IronRuby in the list along with SQL and
JavaScript but only SQL and JavaScript
are guaranteed to always
be available. We may need to install IronPython and IronRuby
from separately downloaded installation packages for them to be available.
Command windows will pop open automatically when we open a query or a script component in the project pane by double-clicking on it, to allow us to edit that query or script.
Any changes we make to the query or script in the Command window are automatically made to the query or script component - there is no need to "save" the changes we make. If we open a query or script in a Command window, make changes and then close the Command window, the next time we open that query or script component the changes we made will be in there.
We can also open a standalone Command window without creating a query or script component in the Project pane by using the View - New Command Window menu command.
We can also launch a new Command window for SQL with a Ctrl-Tilde keyboard shortcut (more accurately, a Ctrl-backquote for the name of the unshifted character which shares that key with the shifted tilde ~ character on US keyboards).
A query window is a Command Window that has been opened for SQL text. There are two fundamental ways of launching a query window:
Open a query component that already exists in the project pane.
Choose View - New Command Window - SQL to open a blank query window for writing new queries without creating a query component.
Queries in Manifold can use either the native Manifold query engine or whatever is the query engine provided by a data source if the query was created within a data source. A command window is automatically associated with whatever query engine is used for the data source in which the command window was opened.
For example, if we use View - New Command Window - SQL from the main menu that command window will automatically use the Manifold query engine. If in the Project pane have a data source created using Microsoft's SQL Server DBMS and we right-click on that data source and choose New Command Window that command window will by default use the SQL Server query engine to execute queries.
See the Query Builder topic for details on using built-in features to assist in writing queries.
Open an existing Query component within a command window by double-clicking on it in the project pane.
If we have no queries in the project pane we can create a new query by right-clicking into the Project pane and choosing New Query... We can then double-click that new query to open it.
By default, a new query component is created with a short example of SQL:
-- $manifold$
SELECT * FROM [mfd_root];
The short example ensures every query created can at least be run, as is.
The first line in the query is a directive within the comment block (comments begin with -- ) to use the Manifold query engine. Manifold can execute queries either using the local, Manifold engine (the default) or using the query engine of some data source such as Oracle DBMS. The directive is there by default to remind us it is the Manifold engine which will be executing the query.
The second line in the query is a small snippet of SQL that uses a table we know is available within every Manifold project, mfd_root. It ensures that even the simplest, default query will run. To run the example query, press the ! Run button in the command window's toolbar.
Most of our work with queries will be using a command window, taking advantage of the ability such windows provide for automatic entry of text, to reduce the need for manual keyboarding.
Command windows have three primary panes:
A command pane where we can enter the text of commands and queries to be executed. We can enter more than one query with each query ending in a semicolon ; and each will be executed in turn.
A templates pane that we can use as a source of automatic text to help build queries.
A tables pane into which we can drag and drop tables from the Project pane. The tables pane will show the structure of those table so we can use the names as a source of automatic text to help build queries.
The tables pane helps us see the structure of tables and also helps us avoid typographical errors by avoiding the need to manually keyboard the names of fields and other values we might use within queries. It also reminds us of the syntax for referring to tables from other data sources or from nested data sources.
The templates pane serves as a quick reminder of the syntax of various SQL statements and also helps us remember the names of operators and functions available within Manifold SQL. It lists operators, statements and functions in order of precedence.
We can switch the display of the command window between three modes by clicking on the tabs at the bottom of the window:
Query Builder - Shows the three pane display of command, templates and tables pane. See the Query Builder topic.
Results - Shows the results of executing any command or query.
Log - A running log of past activity. The log is useful as a source of text to copy and paste into the command pane should we wish to repeat or alter a previously executed command. See the Log Window topic.
To run a query we can press the ! button or from the menu we could choose View - Run. We can also highlight some portion of text in the command pane and press Alt-Enter to run just the highlighted portion of text. View - Run Selection will also run just the highlighted portion of text.
All commands run within the same Command window share the same execution state, that is a common environment within which they run. The shared execution state can be modified by commands like FUNCTION, which defines a function with the specified name and adds it to the execution state. Subsequent commands in that Command window can then refer to that function by name.
Besides hosting the text for SQL the command pane can accept directives and other extra commands related to running queries and controlling output to the Log.
? expression - Evaluate the expression following the ? question mark and print the result in the Log. For example, ? 1 + 2 when evaluated will result in the number 3 being printed to the log. This capability may be used to prototype portions of text that will be incorporated into queries, for example, trying out a function or other construction. Usually we enter the ? question mark followed by the expression we want to try out, we highlight the question mark and expression and then we press Alt-Enter to run just the highlighted text. For example, ? SystemCpuCount() reports the number of CPU cores available in the system, giving a value of 8 given hyper-threading on a four-core processor. When an expression involves a function that returns a table, use ? with CALL. For example, we write ? SystemCpuCount() since that returns a numeric value but we write ? CALL SystemGpgpus() since that returns a table. Important: the expression is not terminated with a semicolon ; character.
!fullfetch - Toggle more verbose information reporting on/off in the Log to include the time required, number of records involved and size of data involved. Again, we just highlight the !fullfetch text and press Alt-Enter to run just the highlighted text. !fullfetch works both with Manifold queries as well as queries executed within an external query engine. Caution: Enabling !fullfetch can dramatically reduce performance. Do not use !fullfetch with large jobs.
!manifold - For all subsequent statements use Manifold SQL and the Manifold query engine.
!native - For all subsequent statements use whatever is the native SQL and query engine used by the data source in which the command window was launched. Encountering a $manifold$ directive within a statement will override the !native command. !native is the default.
These command pane optional commands are only available when composing and running queries interactively within the command pane. They cannot be used within a query component (although the $manifold$ directive may be used within a query component). Note that switching between Manifold SQL and a native SQL when working in a Command window launched within a data source is only possible if that data source can run commands in both Manifold SQL and its own native SQL.
The ? command can also be used with expressions involving tables. For example, to display the mfd_root table in the Results tab we could run the following two commands one after the other in the command pane:
FUNCTION f() TABLE AS (SELECT * FROM mfd_root) END
...and then...
? CALL f()
We run the two commands separately because the ? command is interpreted purely in the context of the command window. In the above example it is used as a short-cut way of not having to keyboard in what could be a lengthy SELECT query that was built into the function. Note that when running functions that return a table we use CALL with the ? command, as in:
? CALL SystemGpgpus()
to see what GPGPU capable devices Manifold can use in our system.
The ? command is also a useful way to try out different functions to learn more about SQL or functions. For example, if we are not sure what COALESCE does we can try
? COALESCE(NULL, NULL, 1/0, NULL, 5, 8)
with a result of
> ? COALESCE(NULL, NULL, 1/0, NULL, 5, 8)
float64: 5
The COALESCE function walks through the arguments and returns the first non-NULL value. The first four values are NULLs and are skipped. The fifth argument produces the result, 5, while the sixth argument is ignored.
The following shortcuts work within the command pane:
Alt-Enter - Run the query. If any of the text in the command pane is highlighted (that is, selected) Alt-Enter will run only the elected text.
F5 - Run the query.
Shift-Alt-Enter - Evaluate the highlighted text as an expression. Equivalent to prepending ? to the selected text and evaluating it as an expression.
Command windows provide many automatic functions but one of the most useful when learning how to create components using SQL is the system's ability to automatically generate the SQL that would be used to create existing components. For example, if we want to learn how to create a particular image using SQL we could simply Copy that image in the Project pane and Paste it into the command pane. For an example, see the Example: Automatically Generating CREATE Queries topic.
When Manifold writes SQL, for example,
as generated by the Transform
template's Edit Query button,
by default the query automatically will include a THREADS
SystemCpuCount() command that parallelizes the query. The
SystemCpuCount() returns the number
of CPU cores in the system, as reported by Windows. That provides
a total number of cores whether they are all on a single CPU or whether
the number of cores reported is the sum of cores for more than one CPU
if we are using a multiple-CPU motherboard. The number of
hypercores will be counted by Windows as cores if hyperthreading is enabled
(the default in most systems).
Important: When manually writing queries in the Command Window we must add the THREADS SystemCpuCount() command to the queries we write to parallelize them. If we do not add a THREADS command specifying the number of threads we want, the query will run single-threaded, that is, non-parallel, resulting in significantly slower performance in many cases.
A typical situation is running an Intel Core i7 with eight hypercores. SystemCpuCount() will return a value of 8 so the command becomes THREADS 8 and the query will be automatically parallelized to run in eight threads. If we do not want to use all cores, we can specify the number of threads, and thus cores, to use. For example, we could write THREADS 4 to use only four threads.
The ability to manually choose to parallelize or not parallelize a query, as well as the ability to specify which parts of a query should be parallelized or how many cores should be used, gives us better control over the operation of queries. When Manifold launches many threads to consume the full power of our computer that is a great thing if we want the query to go as fast as possible, but if we want to continue doing other tasks while the query runs we might not want the entire power of our computer consumed by the query engine. We may want to leave a few cores left unused to provide a bit of power for reading email, for example.
As our skills improve we will also choose where we want to apply parallel power. Consider the following query, automatically written by the template used in the Example: Create a Drawing from a Geocoded Table topic:
UPDATE (
SELECT [mfd_id],
[Geom],
GeomMakePoint(VectorMakeX2([Longitude], [Latitude])) AS [n_Geom]
FROM [Cities]
THREADS SystemCpuCount()
) SET [Geom] = [n_Geom];
The query uses a THREADS statement within the SELECT statement, since that is where the action happens. That makes more sense than writing the query as:
UPDATE (
SELECT [mfd_id],
[Geom],
GeomMakePoint(VectorMakeX2([Longitude], [Latitude])) AS [n_Geom]
FROM [Cities]
) SET [Geom] = [n_Geom]
THREADS SystemCpuCount();
... where there is little point to parallelizing the SET command. There would be no harm done with the second form of the query since the Manifold engine is highly effective at optimizing parallelization. We could do worse than simply adding a THREADS SystemCpuCount() statement onto the end of every query we write.
The View - New Command Window provides options to create a new command window for scripting using languages that support interactive sessions. When used for scripting the command window does not display the Query Builder tab.
JavaScript (using the V8 engine that is built into Manifold) is always available but other scripting languages may require installation of enabling files or resources. For an example, see the Example: Run JavaScript in the Command Window topic.
Running a script with external libraries referenced via $reference: tries to locate these libraries within the Manifold installation folders bin, bin64 or shared, as well as the application data folder and user data folder for Manifold as specified by Windows. This is only done for references that contain a filename. Relative paths are not supported.
Running a script in F#, IronPython or IronRuby automatically searches for required CodeDOM or runtime DLLs even if they are not referenced via $reference:. The default script templates for these languages have been altered to exclude $reference: directives and instead to note the requirement to have the relevant packages installed.
The command window runs in the context of the root of the current project. We can change the context to run in the context of a data source by right clicking the data source and then choosing New Command Window from the context menu.
Consider a project that has two data sources, one called Mexico and the other called Europe, both created from Manifold .map file projects stored elsewhere on our system. Except for the two data sources the project is empty, with no tables at the root of the project.
We launch a Command Window using View - New Command Window - SQL.
Running the query:
SELECT [Population] FROM [Mexico Table];
... fails because the Command Window is running in the context of the root of the project and there is no [Population] field, and no [Mexico Table] table at the root.
We can fix that in two ways. The first way is to fully qualified names to refer to components within data sources, so a query executed in the context of the root of the project can use them:
The query:
SELECT [Population] FROM [Mexico]::[Mexico Table];
... runs as expected, since the query engine now knows what [Mexico Table] we have in mind.
Another approach is to launch the Command Window in the context of the data source.
In the Project pane we right-click onto the Mexico data source.
In the resulting context menu we choose New Command Window. That launches a Command Window in the context of the Mexico data source. To indicate the context the title bar of the Command Windows is captioned Command: Mexico.
We can now run the query that previously failed and this time it succeeds, since it is running in the context of the root of the Mexico data source.
We can launch Command Windows as deep as we like within data source hierarchies. For example, suppose we create a data source called World, which consists of a .map file that in turn has a data source called Europe, which is also a .map file. The Europe project in turn has data sources connected to .map files that provide data for England, France and other countries. Right-clicking on to the France data source two levels deep we can choose New Command Window to launch a command window in the context of that France data source.
The Command Window title bar will have the caption Command: France < Europe < World to show the hierarchy of data sources to the context within which the Command Window is operating.
Running a query such as:
SELECT * FROM [France Elevation Table]
ORDER BY [Highest Z-value (meter)] DESC;
Succeeds because the Command Window is operating at the root of the France data source.
We have one more way of running a query in the context of a data source. We can do that by using the EXECUTE statement to launch a query within the data source.
Suppose we have a query called MexQ that has been created within the Mexico data source.
The query is simple:
SELECT * FROM [Mexico Table];
We can run that query by using the EXECUTE statement.
Even though we have launched the Command Window in the context of the local root, the SQL statement:
EXECUTE [Mexico]::[MexQ];
Refers through the data source to the [MexQ] query component within that data source, to launch it for execution in the context of that data source.
The EXECUTE statement can also be used to launch inline queries together with the optional ON clause to specify that the inline query should be run in the context of the named data source. See the discussion in the Context for Inline Queries and ON Clause section of the EXECUTE topic.
Ctrl-Tilde keyboard shortcut - The tilde ~ character is on the same key as the single back quote ` character in US English keyboards, normally in the upper left corner of the keyboard where it is easy to click one-handed with our left thumb on the Ctrl key and the left index finger on the tilde key. It's so convenient that once we get used to the combination this keyboard shortcut will likely become our favorite way to launch the Command window for SQL.
The name of the short cut, though, is bogus since the tilde ~ character is the shifted use of the key. The unshifted use of the key is the back quote ` character. But to launch the Command window we do not use the Shift key, that is, doing a Ctrl-Shift-backquote for the shortcut. Instead, we simply do a Ctrl-backquote with the key. The keyboard shortcut therefore more accurately should be called Ctrl-` or Ctrl-backquote.
Given that most people have no idea what the back quote character is and how that differs from a single quote character it is no surprise that everyone refers to the shortcut as Ctrl-Tilde.
Project pane vs. Command Window - Running a query from the Project pane is equivalent to opening a Command window for the query, and then running the contents of the Command window using the View - Run menu command.
Running a query from the Project pane, is, however, slightly faster, because part of the state maintained by the Command window is not required for non-interactive runs.
Editable Results Tables - In most other database systems we normally think of results tables produced by an SQL query as read only, but in Manifold results tables are often editable. Manifold exposes indexes from source tables in results tables, so if the results table includes an indexed field, as is often the case with a SELECT or a JOIN statement, we can edit that results table and the edits made to field values automatically will be written back into the source tables. See the Editable Results Tables topic for details.
Edit - Schema works on Results Tables - If we want to see details on the results table created by a query, with the focus on the Command Window we can choose Edit - Schema to see the schema for the results table. Even though the results table is a virtual table it still has a schema that we can see.
Execution states - A command window operating on a remote database maintains a shared execution state for commands. For example, we can run a command that saves some data into a variable and then some time later run a command that uses the data in that variable.
Multiple command windows operating on the same database will use separate execution states so that they are isolated from each other and from the rest of the system.
Agnosticism - Manifold is completely agnostic about programming languages, as can be seen by the wide range of languages which are directly supported for scripting in Manifold. JavaScript is built in for convenience and to leverage the ubiquity achieved by JavaScript as a result of Internet. The V8 engine was selected as a high quality and fast JavaScript engine that is compatible with Manifold parallelism and which would fit within an .msi installation. The Manifold portable installation provides more space so both IronPython and IronRuby can also be included. C#, JScript, JScript.NET, VB.NET and VBScript are supported and are always available because of the Microsoft infrastructure required for a Manifold installation. Manifold also supports F#, PerlScript and PythonScript, which are easy to install if desired.
Unicode text - Text literals entered into the Command window are stored as Unicode internally using UTF16, that is, two bytes per character. We can see that when evaluating expressions such as
? DataLength('Manifold')
which gives a result of float64: 18, meaning 18 bytes are used for the string: two bytes for each of the 8 characters in the word "Manifold" plus two more bytes for the closing zero. If we wanted to compute the length of the string as ANSI text using one byte per character we could cast the default Unicode of string literals into the ANSI data type of VARCHAR by writing
? DataLength(CAST ('Manifold' AS VARCHAR))
for a result of float64: 9, meaning one byte for each of the 8 characters plus one byte for the closing zero.
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.
Example: Automatically Generating CREATE Queries - How to use the Command window to automatically generate SQL in the form of CREATE queries that create a desired component.
Example: Run JavaScript in the Command Window - How to run a simple V8 JavaScript script in the Command window.
SQL Example: Learning to Union Areas in SQL from Edit Query - We learn how to write an SQL query that does a custom Union Areas operation by cutting and pasting from what the Edit Query button automatically generates.