The SQL Scripting Editor

The Orixa framework includes a powerful tool to assist programmers in writing the SQL scripts that define your App. The Syntax Editor makes it easier to write SQL scripts correctly, and includes numerous features to make script-writing easier, such as keyboard short-cuts, macro recording and playback, and code-completion of SQL based on context-sensitive keywords.

If you need help on how to write SQL, the languages keywords and structure please refer to the SQL writing sections of the help. To use the Syntax Editor you must understand how to write SQL.
 
The Syntax Editor's code-completion feature is particularly valuable, as it allows Developers to write code that works without spelling errors, lists keywords such as field-names, table-names and other useful keywords from the App's database  so the Developer does not have to remember them all.

 

• Keywords such as "SELECT" or "FROM" in any script are shown in bold
• Names of all data-tables in your database such as "People" or "Organisations" in any script are shown in blue
• Elements representing constants such as numbers or strings of text are shown in red
• Functions and procedures are shown in a light blue.
• Comments are shown in green.

Syntax Editor Context Menu

The syntax editor features a rich context menu. Right-click on the syntax editor to show this menu, which includes features for testing scripts, running scripts, showing data from scripts in a grid or data-viewer, adding segments of SQL from templates, and even includes a SQL modelling tool.

 

SyntaxEditor with Context Menu  

• Search / Find options: Open windows to review and update the SQL, specialized dialog windows have been created to make swapping and changing scripts easier.
• Add From Template (detailed below)
• Test SQL: Click here to have the system attempt to "prepare" your SQL script. If the script has a bug, an error message will be displayed, including the line and position of the error.
• Show SQL Plan / Optimization. This will open a window showing the "cost" of opening running your script in terms of the amount of data that will need to be processed. SQL can often be optimized to reduce this, allowing the query to run faster.
• Add script using SQL Modeller (detailed below)
• Show data in Grid. Click here to run the SQL and open the resulting data in a grid. Note that if the script contains an errorr this will be displayed, and the grid will open empty.
• Show data in Data-viewer. Click here to preview how the data would display in a data-viewer.

Viewing Data returned by SQL SELECT statements in the Syntax Editor

Show Data  

Once you have completed the SQL, you can check the data that it returns by right-clicking on the Syntax Editor and clicking on "Show data in Grid" (1., above) to show a standard Orixa data-grid, you can also click "Show data in Data-viewer" to see a data-viewer version of the result.

Note that there is also a "Test SQL" option (2. in the image above) which is useful to check that SQL is well formed without actually running the full statement, which may be slow to execute.

Using the Context Menu

Add From Tetmplate

Add From Template The system includes a list of commonly used basic SQL statements. If you are writing a new SQL script, or adding to an existing one, you can pick common statements from this list and they will be added at the position of the cursor in the Syntax Editor.

Orixa SQL Modeller

Add script using SQL Modeller The system includes a fully featured visual SQL modelling tool. Using this is detailed below. Programmers with experience of visual SQL creation tools will find the interface relatively self-explanitory. The SQL Modeller cannot write all possible SQL SELECT Statements, but it can be extremely useful for creating outline statements with complex elements, particularly if the developer is not familiar with the names of columns and data-tables in the database. More details about the SQL Modeller are provided at the end of this help

Keyboard Shortcuts in the SQL Syntax Editor

The Syntax Editor uses all the short-cuts that are commonly used in many editor tools and word-processing windows, such as [ctrl] + Z for Undo, [ctrl] + X / C / V for Cut / Copy / Paste. Users can select text with [shift] + any "move" key-stroke, such as home, end, Page-Down etc. If you want to save a SQL script [ctrl] + S Saves the current SQL to a file on disk.

Specialized "coding" keyboard short-cuts

• [shift] + [ctrl] + R starts and stops Macro recording. While macro recording is taking place all your key-strokes are recorded, and can be played-back later using [shift] + [ctrl] + P.
• [shift] + [ctrl] + [Spacebar] triggers code-completion (explained below).
• [ctrl] + [number from 0 to 9] Sets a bookmark.
• [shift] + [ctrl] + [number from 0 to 9] Jump to previously set bookmark.
• [shift] + [ctrl] + R triggers the Marco Recorder to start to record key-strokes (explained below)
• [shift] + [ctrl] + P plays back keystrokes recorded by the Marco Recorder.

Using Code-Completion in the SQL Syntax Editor

Code-completion is common with script-writing where the Developer is using a computer language. Orixa has added code-completion to make the Developer's life easier.

Note that the key-word list used by Orixa's code-completion will vary in different parts of the App. In Editor-windows where the user is writing "SELECT" SQL a more limited set of key-words will be shown. In the DB Utility's SQL Editor a full set of SQL with "DDL" keywords such as "ALTER TABLE" etc., is included.

How to trigger code completion:

1. Initiate the Code Completion Window by pressing "[shift] + [ctrl] + [spacebar]" after you have typed 1 or 2 letters of a SQL key-word. 
2. Use UP and DOWN arrows to select items on the auto-completion list. 
3. Press ENTER to paste the selected text into your SQL Script.

Code Completion Type "S"	Code Completion reviews the text in the current word the developer has started writing. It looks at its list of keywords, and shows all keywords starting with this text. In the example shown in the image, the Develop has typed "S", so the code completion window is showing. Note: You can resize the auto-completion window if it is useful to make it taller / shorter.
Code Completion "Fu"	Code Completion contains the following words: All SQL Keywords such as "SELECT" etc. All the Field-names, Table-Names and View-Names in your database. Names of all the Functions and Procedures in your database. If the Developer is writing a multipart script in the DB Utility, additional "DDL" keywords are added to the Syntax Editor, such as CREATE, DROP, DELETE. Orixa SQL Extensions (explained below)
Code Completion "[" Square bracket shows Orixa-Specific Syntax	Orixa includes a very few extensions to the SQL Syntax, which are added to allow an App to call for User Input when a SQL script is run. All these extensions start with a square bracket. If a Developer is unsure of how to add an Orixa-specific SQL extension, typing the square bracket and showing code-completion gives them immediate hints for how to complete the SQL. If the code-completion example includes angle-brackets, the Developer is expected to replace this text with their own key-word.
Completed example SQL	The SQL shown on the left, and below was almost entired written using code-completion. The Developer only needed to remember the approximate field and table names, and tidy up the code-completion suggestions a bit. SELECT   ID,   FullName, FROM Organisations WHERE MainContactID = [BO 'People']

The SQL Marco Recorder

It is often necessary to undertake repeated text entry when writing any kind of source-code. For example finding a set part of a series of lines and deleting some text then replacing it with something else.

If the typing and cursor movements are sufficiently repetitive, you can record your actions, and play them back in different locations in the SQL Statement to repeat the process without having to re-type. 

1. Find the first location where you want to make the change.
2. Press [shift] + [ctrl] + R. This starts macro-recording.
3. Use the keyboard to delete, add and move text. You can use key-strokes like copy, paste, home, end etc., to move around the the SQL Editor window. Mouse movements are not recorded.
4. Press [shift] + [ctrl] + R again. This stops the recording process. All the keys pressed between the two points are now saved in the SQL Editor's macro-recording memory.
5. Find a new location in code where you want to repeat the actions you have recorded, and press: [shift] + [ctrl] + P. The macro should play back.

The SQL Modeller

Orixa includes a built in SQL Modeller to assist with some of the "leg work" of writing SQL Scripts. It is similar in layout and function to many SQL building tools in other programming environments.

SyntaxEditor SQL Builder 

Elements of the SQL Modeller

1. The main design-area. Click on a field in one table and drag it to a field in a second table in order to "link" the two tables in a SELECT statement.
2. List of data-tables and database-views. Drag these into the design-area to use them in a SQL SELECT statement.
3. Control visibility of each column, and other aspects it's beviour with settings in the bottom of the window.
4. Add a WHERE clause.
5. Set the sorting order for a colunn
6. Add a function such as SUM or COUNT.
7. Add GROUP BY elements to the query.
8. Click on the "tick" to accept the SQL and return to the main Syntax editor window, click on the red "x" to abandon your work.

 

The SQL modeller does not cover all possible aspects of SQL script creation, for example it does not allow the creation of UNION, HAVING or SUB-SELECT SQL However it is extremely useful in the creation of SQL SELECT statements, and is particularly useful for developers who are still learning how to craft SQL statements, or are not completely familiar with all the field-names and data-table names in their system.

Extra Features of the Syntax Editor in the Database Utility

Most users will spend most of their time using the SQL Syntax editors linked to Orixa meta-data-tables such as Resources and BusinessObjects. The SQL in these data-tables is always SELECT statements which return data. These statements may be long and complex, but they do not contain "DDL" SQL which changes the data-definitions of the database.

In the Database Utility, an extended SQL Syntax Editor is added, which adds the ability to "Execute SQL Scripts", these scripts can include actions such as CREATE, UPDATE, ALTER, DELETE and DROP actions.

Extra actions in the menu of the Database Utility Syntax Editor  

The image above shows the extra actions available in the SQL Editor of the Database Utility. Note you can load and save SQL statements, as well as running them in sequence.

Select part of a SQL Script to only run the highlighted statements  

Another useful feature of the Database Utility SQL Editor is that if the user highlights part of the script, when they choose to Execute, only those statements will run.

1. Highlighted statements
2. These statements (and any after them, or before the highlighted section) will not run.
3. User is given the choice to run the statements, and told how many will be run.