The Command Window provides a dialog for writing and executing queries in SQL and for interactively writing and executing scripts in programming languages using the Command window as a REPL (Read-Eval-Print Loop) console for the specified language. This topic discusses the Command Window in the context of SQL queries. For information on scripting, see the API Documentation website. Press F1 when a Command Window is open for a web-based quick reference guide to keyboard shortcuts.
Double-clicking an existing query or script opens it in a Command window. Any changes we make to an existing query or script that has been opened in a 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.
In contrast, if we first open a Command Window using View - New Command Window and then write text for a query or a script into that window, any such text will disappear when the Command Window is closed. To save the text into a permanent query or script, choose Edit - Save as Query or Edit - Save as Script.
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.
Launch a new Command window for SQL with a Ctrl-Tilde (the ~ character) 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).
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.
SQL |
Manifold's built-in parallel query database engine provides an always-on ability to write and to execute SQL queries.
Launch a Command Window for SQL using Ctrl-Tilde (the ~ character) or View - New Command Window - SQL. |
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 but
only SQL is guaranteed to always be available. We
may need to install IronPython and IronRuby from separately downloaded
installation packages for them to be available.
A query window is a Command Window that has been opened for SQL text. There are three ways to launch 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.
Launch a new Command window for SQL with a Ctrl-Tilde (the ~ character) keyboard shortcut.
To save the contents of a Command Window as a query component, use Edit - Save as Query.
Queries 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. We can switch back and forth between Manifold and the native engine by using !manifold and !native commands in the Command Window.
See the Query Builder topic for details on using built-in features to assist in writing queries. See also the Example: Switching between Manifold and Native Query Engines topic.
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 common 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 SQL queries as well as queries executed within an external query engine. Caution: Enabling !fullfetch can reduce performance. Do not use !fullfetch with large jobs. See the discussion in the Log Window topic.
!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
pane 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 other applications we are running.
As our skills improve we may 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.
Running a script with external libraries referenced via $reference: tries to locate these libraries within the Manifold installation folders bin, bin64 or extras, 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 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.
Queries imported from formats intended for non-Manifold databases, for example, from .mdb format for Access, may sometimes be marked as read-only. Manifold allows us to make a query read-only by adding a ReadOnly property to the component and setting a value of true for that property. We may discover this when importing sample books.mdb files published as sample databases for use with Chris Fehily's fine series of books teaching SQL.
In such cases, we can easily change the ReadOnly property for a read-only query from true to false to make the query read/write. Consider an example:
We have imported the books.mdb sample file from the downloads page for Chris Fehily's SQL (Third Edition) Visual Quickstart Guide. After importing the .mdb into the system we have opened the query called listing0414 in a Command Window, as seen above. The gray background for the query text indicates the query is read-only and cannot be edited.
In the Project pane we right-click onto the listing0414 component and choose Properties in the context menu. The properties dialog for listing0414 shows it has a ReadOnly property with a value of true.
We double-click into the value box and change the value to false, and then we press OK.
After we close the query and then open it again, we see that the query text now has a white background and is fully editable.
Suppose we have dozens, or hundreds of read-only components, and we would like to make all of them read/write? That is easy to do using Manifold's "everything is a table" design.
In the System Data folder we open the mfd_meta table, and then Ctrl-click the Property column header to sort that column. We ctrl-click the first row with a Property of ReadOnly and then we Shift-Ctrl-click the last row with a Property of ReadOnly. That selects all rows with a property of ReadOnly. Or, if we prefer, we can use the Select pane to select all rows with a Property of ReadOnly. We then double-click into a Value cells for any of the selected rows and we change the true value to false, as seen above.
Right-click onto the false value cell and in the context menu choose Copy to Selection. That copies the value false into the Value cell for all of the selected records.
We have just change the ReadOnly property for all components that had that property to false. All of them are now read/write.
The Info pane Components tab appears for Command Windows that have results tables.
The illustration at left above shows a typical display for a long-running SELECT query from a table in an external data source where the results table is still being populated. The final number of records in the results table is not yet known, and the Displayed and Fetched record numbers are still being dynamically updated as the query runs.
The illustration at right above shows results after the query has finished running, but the number of records is still indeterminate since the remote data source might have been updated while the query was running. Press the Refresh button to update the number of records fetched.
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.
Results Tables are Snapshots - Results tables show results for queries when the queries were run. If edits are made to the tables used in the queries, for example, deleting records by deleting objects in a map window that shows a drawing layer from a table used in the query, the query will not automatically be re-run and the results table updated. Re-run the query to incorporate any changes in the source data into the results table.
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 SQL 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 common 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.
MFD_ is reserved - All names starting with mfd_ (case not significant) are reserved for use by the system. Names for fields, indexes, constraints, tables, components other than tables, properties... everything. Do not name anything beginning with mfd_ or MFD_ or in any upper or lower case combination of those characters. mfd_id is a reserved name for a field in tables, to be used only for the identity field that Manifold maintains as an automatically unique value field.
Agnosticism - Manifold is completely agnostic about programming languages, as can be seen by the wide range of languages which are directly supported for scripting. JavaScript is built in for convenience and to leverage the ubiquity achieved by JavaScript as a result of Internet. A 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 any Manifold technology product. Manifold also support F#, PerlScript and PythonScript, which are easy to install if desired.
Character Literals and 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('SQL is Great!')
which gives a result of float64: 28, meaning 28 bytes are used for the string: two bytes for each of the 13 characters in the string "SQL is Great!" 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 ('SQL is Great!' AS VARCHAR))
for a result of float64: 14, meaning one byte for each of the 13 characters plus one byte for the closing zero.
To specify a given character in SQL use the Chr function or specify the Unicode value of the character using four hexadecimal digits, as in the pattern '\uXXXX' with X replaced by hexadecimal digits.
? 'Z' & Chr(246) & 'e'
? 'Z\u00f6e'
...both of the above return
nvarchar: Zöe
Constants - We can combine the use of Chr and Unicode values with available SQL constants listed in the Identifiers, Constants and Literals topic, such as CRLF for newline characters:
? 'Line 1' & Chr(13) & Chr(10) & 'Line 2'
? 'Line 1' & Cr & Lf & 'Line 2'
? 'Line 1' & CrLf & 'Line 2'
? 'Line 1\u000d\u000aLine 2'
... all of the above evaluate to
nvarchar: Line 1
Line 2
For information on scripting, see the Manifold API Documentation website.
Identifiers, Constants and Literals
Editing Queries, Scripts and Comments
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: Unique Names using Regular Expressions - We have a table with a text field that contains a list of names, separated by commas. Some of the names are repeated. We would like to transform the list of names into a similar list, but without any repetitions of names. This topic shows how using a regular expression. It shows the power and speed of a concise regular expression, and the flexibility with which regular expressions can be used in SQL queries as well as in the Transform pane.
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: Create and Run a JScript.NET Script - How to create and run simple JScript.NET scripts.
SQL Example: Learn SQL from Edit Query - Merging Areas - We learn how to write an SQL query that does a Merge : area (dissolve) operation by cutting and pasting from what the Edit Query button automatically generates.
SQL Example: Process Images with 3x3 Filters - Shows a step-by-step example of developing an SQL query that takes a query written by the Edit Query button and then modifies that query into a general purpose query that can apply any 3x3 filter. This makes it easy to use matrix filters we find on the web for custom image processing. We extend the query by using parameters and adding a function, and then show how it can be adapted to use a 5x5 filter.
SQL Example: List Transformation Grids - In this example we use a few snippets of easy SQL to list NTv2 and NADCON transformation grids that are available within the grids.dat compressed collection of transformation grid files. Grid transformation files can be used when creating custom base coordinate systems, for NADCON / HARN / HPGN and NTv2 high accuracy transformations that use grid files.