(C)Copyright 1996, 2008 Hume Integration SoftwareThis document may not be reproduced or redistributed without prior written permission of Hume Integration Software. Licensed users of HIS provided software have permission to reproduce or electronically distribute this document to support their usage of the HIS software.
All Rights Reserved
The Hume Datahub is an in-memory, low-latency database, a subscription server, a configurable Tcl/Tk interpreter, and a Distributed Message Hub (DMH) server. The synergistic combination of these capabilities is the cornerstone of a new architecture for distributed applications.
As a database server, the Hub provides the familiar programming model of relational database tables using a subset of Structured Query Language (SQL). A graphical user interface is available to display and manipulate table data either remotely as a client, or as part of the Hub process when it is not running in the background.
As a subscription server, the Hume Datahub provides asynchronous notification to Client processes whenever data that meets their selection criteria is inserted, updated, or deleted. These notifications can be standard SQL messages which are useful for data replication to other Hume Datahubs, or for data replication to commercially available persistent databases, such as Oracle or DB2. Other subscription options enable the developer to execute user defined Tcl procedures within the Hub process, or to obtain notification messages in a format that is designed for use by Tcl list manipulation commands.
When used as a DMH message server, a Hume Datahub becomes the hub of an efficient, event-driven, distributed application. The Tcl interpreter provides a high-level, dynamically customizable programming environment with comprehensive features. Client processes can easily exchange peer messages, can easily share common data in relational tables, and can subscribe and respond in realtime to changes in the application data. Shared application logic can be executed within the Hume Datahub process, sharing the same address space with application data tables and SQL command processing logic. The requirement for interprocess communication and the substantial overhead that it entails is reduced drastically compared to other approaches.
The capabilities of the Hume Datahub are built into a general purpose Tcl/Tk interpreter, where they are accessed along with the multitude of other Tcl/Tk commands. From within Tcl code, a developer uses the SQL Tcl command with an SQL statement as a single argument to access the data management functionality.
The Tcl programming language has features such as the namespace command to support combining new commands like the Hume provided SQL command with commands from other packages. When you use the Hume Datahub features, typically the command package require dmh and the command dmh_import have been executed during initialization to make the dmh package commands such as SQL available in the global namespace of the interpreter. If the dmh package commands have not been imported into the global namespace, then the command names must be invoked using the namespace prefix, for example, ::dmh::SQL would be used instead of SQL.
In normal use, a Datahub is also participating in a DMH messaging group. In this usage, the Datahub receives DMH messages sent to its command mailbox, and executes the messages as SQL statements. Usually these command messages are standard SQL statements such as the SQL insert statement or the SQL select statement. For convenience to its message system clients, the Datahub accepts and processes certain statements that are not standard SQL, which allow the clients to access the Tcl commands and features of the Datahub. For example, a client can use eval to execute Tcl code such as procedure evaluations.
The Graphical User Interface (GUI) of the Datahub provides features to display and manipulate table data. Also, the GUI provides windows for the input of Tcl commands or SQL commands. The GUI is written in Tcl code, and dynamically loaded into the Tcl/Tk interpreter using the same techniques that you use to create your own applications.
The Datahub uses a subset of ANSI standard SQL for database management. The basic SQL commands are supported for operations on one table per SQL statement. Table joining, grant and revoke, commit and rollback, creation of views, or data integrity constraints are not directly supported by the SQL command. However, because the SQL functionality is imbedded in a Tcl/Tk interpreter, it is possible to provide equivalent functionality to many of these advanced SQL features by writing Tcl procedures.
Table names and column names can have 1 to 31 alphanumeric or underscore characters. They should start with a leading alphabetic character or underscore. You are able to use either alphabetic case for table and column names or for SQL keywords in your SQL statements. The Datahub retains the original alphabetic case of table and column names from the table create statement, and uses this for query replies.
SQL character strings must be delimited with single quotes, even when they are a single character. To imbed single quotes in a character string, double them, as in the example: 'Here''s Johnny'.
The new Datahub is able to convert data types in SQL statements and expressions, between the basic integer, floating point, or character types. For example, the string constant '43' is acceptable as the value of an integer or as the floating point value, 43.0. Similarly, the numeric value, 3.14, if used in context where a character value is expected, will be treated as the character string '3.14'.
There is sufficent information in the reference section of this document to understand the syntax of SQL statements that are accepted by the Data Hub. If you are new to relational databases, you may wish to obtain additional information on table design and data normalization.
Database tables are created using the SQL create command, and destroyed using the SQL drop command. Each column of a table is a specific data type such as INTEGER. The supported data types are described with the create command.
When a table is created, one or more columns can be specified as primary key columns. When key columns are used, insertion of duplicate rows is not allowed. Said another way, you cannot insert a row into a table if a row already exists in the table with the same values of the key columns. In addition to preventing duplicate rows, keys are used to speed the lookup of rows, when processing the SQL insert, update, delete, or select commands. If all of the values of the key columns are specified, the specific row is located directly.
Using key columns also causes the select command to return row data in sorted order. In contrast to ANSI standard SQL, the sorted result is obtained by default, without using an ORDER BY clause in the SQL select command. If the keys are VARCHAR data types, the sorted order will be a proper ascending sequence of the primary key columns. If numeric data types are used as keys, the ordering is not as useful, and you may want to use the Tcl lsort command on the selected rows to obtain a properly sorted result.
Data is added to a table one row at a time using the SQL insert command. The SQL select command is used to query existing data. The SQL update command is used to modify stored data, and the SQL delete command is used to delete data rows.
When using the select, update, and delete commands you optionally provide a WHERE clause which specifies selection criteria for the affected rows. Also, a WHERE clause can be specified when using the SQL open command to establish a subscription only for rows meeting the WHERE clause selection criteria. A WHERE clause consists of one or more comparisons between a column value and a constant, or two column values. All of the usual SQL comparison operators are supported: equal (=), not equal (<>), less than (<), less than or equal (<=), greater than (>), and greater than or equal (>=). Compound conditions are expressed using the NOT, AND, and OR operators. Parentheses may be used to group terms. All of the comparison operators have equal precedence, and their precedence is higher than the NOT, AND, and OR operators. So, if you write
select * from equipment where machine='hx14' or machine='hx15'
it is equivalent to
select * from equipment where (machine='hx14') or (machine='hx15')
since the OR operator binds less tightly than the comparison operator =.
NOT has a higher precedence than AND which has a higher precedence than OR. If you write
select * from equipment where
class='hx' and not color='green' or machine='zq2'
it is equivalent to
select * from equipment where
((class='hx') and (not (color='green'))) or (machine='zq2')
Your WHERE clauses can also use the LIKE comparison operator to compare a VARCHAR field to a string constant pattern. Standard SQL uses the % character as a wild card that matches 0 or more characters, and the underscore, _, as a wild card that matches 1 character. So if you write
SQL "select * from equipment where machine like 'hxq%'"
you will select machine values such as 'hxq14'.
The LIKE operator has been implemented using the string match functionality of Tcl. If you look at the reference for the string match command, you will see that it has more features than the simple matching characters of the standard SQL LIKE; for example, you can use character classes such as [0-9] to represent any number. These powerful features have not been disabled, and you are able to use them in your LIKE operator patterns. However, these features are not standard SQL, and you should consider that these features are not portable to other SQL databases. The other implication of not disabling these features, is that if your LIKE pattern contains the special characters *, ?, or [ and ], you need to escape them using the backslash character to have them treated as regular characters. You may also precede the SQL wildcard characters % and _ with the backslash character in order to force them to be interpreted as ordinary characters.
Table data subscriptions are opened to have notification messages sent any time specified table data is changed, or, to have a Tcl procedure executed any time specified data is changed. There can be any number of subscriptions on each table. For example, you may wish to have each user interface open a subscription to a table that holds active alarm data so that a message is sent to every user interface when a new row representing a new alarm is inserted.
Another example use of a table data subscription is to perform realtime analysis on new measurement data using a Tcl procedure that executes in the Datahub. The analysis code is activated without the overhead of interprocess communication. The logic that produces the measurement data, whether it be an equipment interface, or user interface, is developed, tested, and deployed independent of the analysis logic.
The SQL open command is used to create a table data subscription. Each use of the open command creates a subscription to one or more columns of a specific table for all rows which match specified selection criteria. Options to the open command indicate whether the subscription notifications execute a Tcl procedure, or whether the notifications result in messages being sent. There is also a SYNC option that causes notifications to be activated for all of the existing data matching the selection criteria at the time the subscription is opened. The SYNC feature makes it easy for a client to synchronize with the current and future states of the application data. The ease of meeting this application requirement stands in marked contrast to other CIM products such as CELLWORKS dmbx, DEC Message Queue, or TIBCO Rendezvous, which offer broadcast or subscription based message propagation without any history.
The SQL close command is used to remove table data subscriptions.
The Datahub has a small set of data items that are assigned values using the SQL set command or queried using the SQL query command. These data parameters are used to configure specific aspects of the Datahub's behavior and are described under the set command.
The GROUP parameter specifies the DMH message group name that the Datahub will use when initializing DMH message communication. The value defaults to "mbx". The SERVER parameter specifies the mailbox name that the Datahub will use to receive SQL message commands from the DMH messaging system. The value of the SERVER parameter defaults to "DATAHUB". The SQL EOF command is used to initialize message system communication. Once message communication has been initialized, the values of the SERVER and GROUP parameters cannot be changed. It is typical to specify these parameters, and to initialize DMH message communication on the Datahub's command line. A typical startup might also use the SQL source command to read and process some Tcl initialization code, such as statements to build tables and load data. Thus, an invocation of a Datahub might look like:
datahub "set server=DBM" "set group=spc2" "source hub_init.tcl" eof &
The capabilities of the Datahub are built into a general purpose Tcl/Tk interpreter, dmh_wish which has its own document describing its command line options. These command line options are available to the Datahub.
Typically, the Datahub is invoked from a script file; namely datahub85, hub85, or datahub85.bat. On your system, you may find these scripts also installed under the version independent name datahub or datahub.bat. The script file configures the underlying interpreter so that the command line arguments which are not processed as interpreter options, are processed as SQL commands as described in the SQL Command Reference section of this document. One of the important dmh_wish command line options is -notk. This option starts the Datahub in background, without it communicating with the Windowing system. This is the way you want to run a production application, so that you can logout without shutting down the Datahub. Also, when running for production on Unix, you should start the Datahub in a shell that provides protection against the SIGHUP signal at logout such as the ksh, the csh, or the bash shells. If you use the older Bourne shell you may need to use the nohup command.
You typically do not use the -notk for development work. In this usage, the datahub script file creates the user interface to the Datahub which is invaluable to a developer.
Whether you run the Datahub in background or not, you can also run the hubclient application on any X-display or any Windows NT display in your TCP/IP network, in order to provide the same Datahub user interface. You are able to run as many instances of this application as you choose.
The Datahub user interface is a tool for developers and not for application users, because it makes it easy to drop tables, delete rows, terminate client applications, or make other potentially disruptive changes.The hubclient can be started with an argument that specifies the SERVER mailbox and the DMH GROUP of the target Datahub. For example, to provide a user interface to a Datahub running on host malibu, with the SERVER mailbox of DBM, and the DMH GROUP of sta1, enter
hubclient DBM@malibu:sta1 &at the command prompt. Windows NT users do not enter the background operator &. If you do not supply a command line argument, the hubclient application will prompt you for one.
The hub85 script configures the Datahub so that it does not become a DMH message system server. You use the hub85 script when you wish to run additional instances of Datahubs in a DMH message group, and another process in the group is already functioning as the DMH server. In other words, you should select a single Datahub to be the DMH server for a given group, and invoke it with the datahub script. Subsequent Datahubs that will join the same DMH group should be started using hub85. See the mbx command for more information on message groups.
The following Tcl commands have been implemented as part of the Hume dmh package in order to provide Hume Datahub functionality. Almost always, these commands have been imported into the global namespace of the Tcl interpreter from the ::dmh namespace. The importing occurs when the user runs the datahub85 or hub85 application scripts. Custom applications that do not use these scripts can have the same convenient global access to the commands by calling dmh_import or by directly using the namespace import command. The package commands can be accessed in their native namespace by prepending ::dmh:: to the command name. For example, the SQL command is always accessible as ::dmh::SQL.
The Tcl execute command is nearly synonymous with the Tcl SQL command. The command processes the sql_command argument as a Data Hub SQL command as described in the next section. The return value is a Tcl list where each element of the list is a reply message that would be obtained if the argument were sent as a message command to the Data Hub's command mailbox. In other words, since most of the Data Hub SQL commands return a single reply message, most of the return values will be formatted similar to "{//c 0 COMMAND: success}".
The execute Tcl command differs from the SQL Tcl command in the format of the reply for the select and the telect SQL statements. For both these statements, the SQL command reply uses the Tcl Reply Format so that the query data is easily manipulated using Tcl list commands. In contrast, the execute select statement reply is a list of "SELECT: ..." messages with a final "//c" legacy style message. See the select description for details.
The difference with the execute and SQL "telect" statement replies is subtle. The execute command returns the Tcl Select Reply Format but it is packaged as a one element Tcl list. Therefore, it is always true that the return value of the execute statement is a Tcl list where each element of the list is a reply message that would be obtained if the argument were sent as a message command to the Datahub's command mailbox.
This Tcl command is used internally by the Datahub software when it receives SQL command messages from its command mailbox. The command causes the mbxmsg argument to be processed as an SQL command, and causes reply messages to be sent to mailbox mbxreply when appropriate. When message communication is initialized with the SQL EOF command, Tcl code similar to:
mbx whenmsg $SQL(SERVER) { mbx whenmsg again ;
gs_execute $mbxdest $mbxmsg $mbxreply }
is executed to establish the basic processing of command messages.
Usually, the application developer need not be concerned about the gs_execute command. However, there are three circumstances that we can think of where you may want to use this command.
First, you can use this command in order to process SQL commands that are received in a different mailbox than the main server command mailbox. Just execute your own "mbx whenmsg" statement specifying the mailbox name that you wish.
A second use for the gs_execute command is to modify the basic DMH command processing loop of the Datahub. For example, in a debugging situation, you could print every statement being processed by executing:
mbx whenmsg $SQL(SERVER) { mbx whenmsg again ; puts $mbxmsg ;
gs_execute $mbxdest $mbxmsg $mbxreply }
Finally, in rare circumstances you may wish to re-arm the Datahub for re-entrant execution of SQL message commands. Ordinarily, you will not receive another DMH message in your command mailbox if gs_execute has not completed processing a prior message. This is a desirable characteristic of the DMH message system since it saves you from having to worry about re-entrant execution of your application code. However, there are circumstances where re-entrant execution may be desired. For example, a Datahub may receive a message that causes it to query another application for some data. In turn, the other application needs to query the Datahub for related data before sending a reply. To re-arm itself for re-entrant execution, the Datahub needs to execute the full statement
mbx whenmsg $SQL(SERVER) { mbx whenmsg again ; puts $mbxmsg ;
gs_execute $mbxdest $mbxmsg $mbxreply }
while it is "busy" processing the original message.
This contrived example does require re-entrant execution, but in truth its a more insightful example of poor design. The application should not require the Datahub to perform a synchronous query of another application in order to return a synchronous reply. The design should be reworked so that the processes pass along enough context information in order to process the same work asynchronously. The example problem can also be sidestepped by having the Datahub process message commands received in another mailbox, and having the other application use this alternate mailbox for its nested queries.
Here is an example of the format. The table below:
| name | description |
|---|---|
| 3sigma | Western Electric #1 - one point outside of +/- 3 sigma |
| 3sigmaH | One point higher than + 3 sigma |
| 3sigmaL | One point lower than - 3 sigma |
is rendered from the HTML data:
<table id="dc_runrules" border="1"><tr> <th>name</th><th>description</th></tr>
<tr><td>3sigma</td><td>Western Electric #1 - one point outside of +/- 3 sigma</td></tr>
<tr><td>3sigmaH</td><td>One point higher than + 3 sigma</td></tr>
<tr><td>3sigmaL</td><td>One point lower than - 3 sigma</td></tr>
</table>
Here is an example of the XML format. The table data that resulted in the HTML table shown in the previous procedure was also converted to the following XML data:
<?xml version="1.0" encoding="UTF-8"?>
<selection>
<notification>select</notification>
<tablename>dc_runrules</tablename>
<columns>
<column>name</column><column>description</column></columns>
<keys>
<key>name</key></keys>
<count>3</count>
<errorInfo></errorInfo>
<rows>
<row><name>3sigma</name><description>Western Electric #1 - one point outside of +/- 3 sigma</description></row>
<row><name>3sigmaH</name><description>One point higher than + 3 sigma</description></row>
<row><name>3sigmaL</name><description>One point lower than - 3 sigma</description></row>
</rows>
</selection>
In this section, the commands that are given as arguments to the Tcl SQL command, or to the Tcl execute command are presented. These are also the commands that are processed when sent as messages to the command mailbox of the Datahub.
The new Datahub retains reply format compatibility with the SCC Data Server program was developed by SEMATECH. In general, a reply is formatted as "//c rc COMMAND: text". In this format, a return code, rc, of 0 means that the command completed successfully. A non-zero code implies an error or warning condition.
A blank statement is treated as comment text. Comment commands are exceptional in that reply messages are not sent when the comment commands are received through the message system.
If you execute an SQL statement that is not supported, or execute some other unrecognized statement, the reply is:
//c 2 <UNKNOWN>: Unrecognized command, enter "help" for command list.
These commands also retain compatibility with the SEMATECH SCC Data Server program in other ways that are not explicitly documented for each command, because they are deprecated for future use. See the section on Compatibility.
| # | CLOSE | CREATE | DELETE | DROP | DUMP |
| ECHO | EOF | EVAL | EXIT | HELP | INCLUDE |
| INSERT | OPEN | PING | QUERY | READ | REM |
| SELECT | SET | SOURCE | TELECT | UPDATE | WRITE |
| # | comment |
| Syntax | # comment text |
| Examples |
# These statements build version 2 tables |
| Description | This command lets you lets you add comments to files of SQL commands that will be processed using the SQL include command. The # command and the rem command are equivalent. |
| Replies | none |
| CLOSE | Close a subscription |
| Syntax | CLOSE SUB subname TO table |
| Examples |
SQL "close sub my_sub to equipment" |
| Description | This command removes the subscription named subname from the table named table. Use dump, or the Datahub GUI to display existing subscriptions. |
| Replies |
//c 0 CLOSE: subname - Subscription closed to table table. |
| CREATE | Create a new database table |
| Syntax | CREATE TABLE table ( colname
datatype
[, colname datatype]* [, PRIMARY KEY(colname
[, colname]*)] )
datatype := { VARCHAR[(n)] | INT[TEGER] | FLOAT | DOUBLE [PRECISION] } n := non-zero unsigned integer |
| Examples |
SQL "create table spc_type ( |
| Description | The SQL create statement creates an empty database table.
Each column of the table is a specific datatype:
There is no set limit to the number of rows that are stored in a table. If you will be querying tables that have thousands of rows, you should design your application to use primary keys so that performance scales well. The Datahub itself imposes a limit that the total number of characters in an SQL command has to be less than 70,000 and the number of lexical tokens in an input line has to be less than 2500. This imposes a generous limit on the amount of data that can be stored in a single table row. These figures also imply that the maximum number of columns is limited to some value over 1200. This is no fixed limit on the size of a query result, other than a limit of 70,000 on any single row of table data. If your operating system and TCP/IP implementation can handle it, you can successfully manipulate multiple megabyte queries. Use dump, or the Datahub GUI to display existing tables. Use drop to delete tables. |
| Replies |
//c 0 CREATE: table - Table created. |
| DELETE | Delete rows from a table |
| Syntax | DELETE FROM table [WHERE where_criteria] |
| Examples |
SQL {delete from captains_log where stardate<'1996-07-05 00:00:00'} delete from my_table SQL "delete from my_table where my_col like 'tmp%'" |
| Description | This command deletes rows that are selected by the where_criteria from the table named table. If no WHERE clause is supplied, all of the rows are deleted. See The Where Clause for the grammar of the where_criteria argument. |
| Replies |
//c 0 DELETE: table - 1 row deleted. |
| DROP | Destroy an entire table |
| Syntax | DROP TABLE table |
| Examples |
drop table report_temp SQL "drop table report_temp" |
| Description | This command destroys the named table, freeing the memory
resources it was using. Subscriptions on the table are closed as it is
dropped.
It is more efficient to empty a table using delete than to drop and re-create it. |
| Replies |
//c 0 DROP: table - Table destroyed. |
| DUMP | Obtain status information |
| Syntax | DUMP { TABLE | SCHEMA table | SUB table } |
| Examples |
dump schema repair_log |
| Description | There are three variations of the DUMP command:
|
| Replies |
multiple replies of status information followed by |
| ECHO | send the argument as a reply |
| Syntax | ECHO echo_text |
| Examples |
echo hello world |
| Description | This command exists for compatibility with the older versions. |
| Replies |
echo_text |
| EOF | initialize message system communication |
| Syntax | EOF |
| Examples |
datahub "set group=spc2" eof & |
| Description | Executing this command causes the Datahub to initialize message system communication, and thereafter to process messages received in the server mailbox as SQL commands. |
| Replies |
//c 0 EOF: Message system initialized, command mailbox is \ |
| EVAL | evaluate Tcl code |
| Syntax | EVAL tclcode |
| Examples |
eval user_login jsmith rx7fan eval exec xterm -display atlas:0.0 & eval SQL "update checking set balance=12 where acct='smith'" ; SQL "update savings set balance=24 where acct='smith' eval proc hello {a} {puts $a} |
| Description | This command provides a means for message system clients to access the Tcl functionality of the Datahub. As the examples show, it can be used to execute Tcl procedures, or even to execute more than 1 SQL command in a single message. |
| Replies | If you are accessing the eval command with the DMH message
system
the reply message is exactly the result you would see if the same tclcode
were executed at a Tcl/Tk shell prompt.
When you access the eval command in Tcl code using the SQL command, the result is always returned as a single list element, which means that braces are added if the result contains internal white space. Here are two examples to demonstrate the difference, which can be subtle.
|
| EXIT | shutdown the Datahub |
| Syntax | EXIT |
| Examples |
exit |
| Description | This command causes the Datahub to exit. In contrast to older versions of the Datahub, delete and drop messages are not sent to subscribers during the processing of the exit. |
| Replies |
//c 0 EXIT: orderly shutdown initiated |
| HELP | display command usage |
| Syntax | HELP [command_name] |
| Examples |
help help create |
| Description | This command returns the syntax of an SQL command or of all the SQL commands. |
| Replies |
command synopsis |
| INCLUDE | process a file of SQL commands |
| Syntax | INCLUDE pathname |
| Examples |
include c:/users/tonka/xtc.sql include ../lib/report1.sql SQL "include ../lib/report1.sql" |
| Description | This reads and processes a file of SQL commands. The logic
reads each
text line in the file as
a new SQL command except if the ending newline occurs inside of an SQL
string constant or inside of an unfinished
sequence of Tcl code. In the latter cases, additional lines
are read until the SQL command is complete.
Relative pathnames are with respect to the current working directory of the Datahub process. The current working directory of the Datahub can be changed using the Tcl cd command, or by setting the CWD parameter. Even on Windows NT platforms, the pathname is specified using Unix style slashes. The INCLUDE command may be nested inside of included files to arbitrary depths. |
| Replies |
a reply for each included command |
| INSERT | add a new row to a table |
| Syntax | INSERT INTO table [(colname [, colname]* )] VALUES (value [, value]*) |
| Examples |
insert into spc_type (code,description) values ('X','XBar, S') insert into system_constants (name, value) values ('USE_BLIVETS', '1') SQL {insert into spc_type (code,description) values ('R','XBar, R')} |
| Description | This is a standard SQL syntax for adding a data row to a
table.
Columns that are not specifically named are given the default values
of 0 for numeric fields and the empty string, '', for character
data. Datahub versions after October 2010 allow for the list of column names to be omitted. If a list of column names is not supplied, then the list of columns from the table create statement is implied, and the number of supplied values must match the number of table columns. As a portability issue you should know that some persistent databases do not allow empty strings to be inserted for character fields that are primary key columns. |
| Replies |
//c 0 INSERT: table - Record inserted. |
| OPEN | create a new subscription on a table |
| Syntax |
For a message subscription: OPEN SUB subname TO table [SENDTO=mailbox] [REPLYTO=mailbox] [TELECT [ARGDATA=data]] [INSERT] [UPDATE] [DELETE] [DROP] [SYNC] [CREATE] { * | colname [, colname ]* } [WHERE where_criteria ] For a procedure subscription: OPEN SUB subname TO table { PROC=procedure | SQLPROC=procedure } [ARGDATA=data] [INSERT] [UPDATE] [DELETE] [DROP] [SYNC] [CREATE] { * | colname [, colname ]* } [WHERE where_criteria ] |
| Examples |
open sub testing to mytable sqlproc=puts * |
| Description |
The command opens a subscription to the table named table. You can only open subscriptions on tables that have one or more columns designated as primary key columns. There are two main variations depending on whether you want a message sent for each subscription notification, or you want to have a Tcl procedure evaluated for each subscription notification. When opening a message-based subscription, you specify the
mailbox to
receive the notifications using the SENDTO=mailbox clause.
The default value of this optional clause is the reply mailbox of the
open command message itself.
By default, the message notifications will be SQL statements. You
may
specify the TELECT keyword in order to receive messages that are
formatted
in the Tcl Reply Format style.
With the Tcl Reply Format, you are also able to specify optional data
using the clause ARGDATA=data.
If this argument is included after the TELECT argument, then an
additional list element is appended to the message data list and the
value of this element is the data
argument that you have specified. So you can use the ARGDATA
option to pass additional context information. With a message-based subscription, you
may optionally specify the REPLYTO=mailbox clause to indicate a
reply mailbox for the notification messages. The default value of the
REPLYTO mailbox is NULL, which is a special value that means no reply
mailbox is applicable. You can use the reply mailbox as a mechanism
to indicate
which Datahub or which subscription a subscription message came from as
long as your REPLYTO mailbox value is acceptable as a single token
mailbox name. When opening a procedure subscription, the SQLPROC=procedure
clause is used to indicate that the Tcl procedure
named procedure is to be called with a single argument, the SQL
notification statement. The alternative clause, PROC=procedure,
is used to specify a Tcl procedure that gets called with the 7
arguments
of the Tcl Reply Format. You are
able to specify a data value for an additional argument to your
subscription procedure using the ARGDATA=data option. If you specify
an ARGDATA clause, then the SQLPROC procedure is executed with 2
arguments, and the PROC procedure is executed with 8 arguments.
In both cases, the additional argument is the last one. When using the ARGDATA = data
option, the data value is
formatted as a single argument using the formatting rules of Tcl
arguments. You may use braces { } or double quotes to delimit an
argument containing spaces. Do not use SQL style single
quotes. For example, ARGDATA = {this is correct}, or ARGDATA =
"also correct", or ARGDATA = OneTokenCorrect. You are able to choose the type of data changes you wish to receive notifications for by optionally specifying one or more of the keywords INSERT, UPDATE, DELETE, or DROP. If you do not specify any of these data change types, then you receive notifications for all of them. The SYNC option causes your subscription to be notified immediately with insert notifications for each row in the table selected by your specified or implicit where_criteria. This option lets you synchronize with the existing data, and in the same action be registered for future changes. The default behavior is only to notify you of future changes. The CREATE keyword is used to receive an SQL create table
statement as
the
first SQL notification message or first SQLPROC procedure evaluation.
The SQL create
table statement is for the entire table, not just the columns that you
are subscribing to. Using both the SYNC and CREATE options is a
convenient way to get all of the SQL statements needed to replicate a
table.
For example, you can use the SYNC and CREATE options and specify as the
SENDTO destination the mailbox of another datahub, to create a copy of
a table
that stays in synchronization with changes to the original table.
The CREATE option is only used with SQL formatted message notifications
- it cannot be used with the TELECT or PROC choices. You can specify a list of specific column names for your subscription, or use * to indicate all columns. Whether you ask for them or not, all of the key columns for the table are included in your column list, and included in all of your notifications. This is done so that your notifications are unambiguous. You specify a column name to indicate you would like to be notified if a value in that column is updated, or you would like to know the new value of that column when a row is inserted. When a row is updated, update notifications do not include the value of every column name in the subscription's list. Instead, the update notifications include values for all of the key columns, and values for the non-key columns requested by the subscription only if they have been changed in the update. A value must actually be changed to appear in an update notification. For example, if an update statement is processed that specifies updating a field to its current value, no notifications are dispatched. When opening the subscription you optionally supply a WHERE clause to specify row selection criteria for your notifications. The default is that you are notified for changes to any row. See The Where Clause for the grammar of the where_criteria argument. Here is a fine point on update notifications. The where clause is evaluated on the updated row data, not on the original row data. If your where clause specifies comparisons on non-key column values, (1) you will see update notifications for rows that are updated to match the where criteria from a previous unmatching state, and (2) you will not see update notifications for rows that are updated such that they no longer match your where criteria. If the value of a primary key column (or columns) is (are) updated, the Datahub processes two notifications; a delete notification for the original value of all the key columns, followed by an insert notification for the row data with new key column values. This behavior guarantees that if a WHERE clause is expressed only on values of key columns, the subscription notifications synchronize perfectly with the selected subset of the table. If a single update notification were sent, (1) a selective subscription would not be notified when a row is updated to no longer match the WHERE criteria, and (2) a selective subscription would receive update notifications for rows that it did not previously know about, as they were updated to match the WHERE criteria. There can be any number of subscriptions on a table. The subscription name, subname, must be unique among all of the subscriptions to table table. Subscription names follow the same conventions as table and column names, except that they can be as long as 80 characters in length. You use the subscription name with the close command to close a subscription. All of the subscriptions on a table are processed by the Datahub before it replies to the original SQL command that caused the data change(s). There are certain operations that you should not do in a subscription procedure.
Perhaps surprisingly, it is safe in your subscription procedure to perform nested data manipulations, such as updating a row that has just been updated, or deleting a row that has been inserted or updated. If you design a complex mess like this, you need to be mindful that you are possibly breaking the design assumptions of the other subscriptions on the table. Also, the subscription notifications may not be in the proper sequence for all of the subscriptions since the nested notifications are completed before the Datahub finishes with the original notifications. A design alternative to nested data manipulations is to do the data manipulation in an after command so it is carried out after the procedure returns. |
| Replies |
//c 0 OPEN: subname - subscription opened. |
| PING | verify online responsiveness |
| Syntax | PING |
| Examples |
ping |
| Description | This command exists for compatibility with the older versions. |
| Replies |
//c 0 PING: SERVER date time (POSIX_seconds) Id |
| QUERY | display the value of configuration parameters |
| Syntax | QUERY [parameter] |
| Examples |
query group |
| Description | The Datahub has a handful of configurable data parameters. Each is described under the SET command. This command lets you obtain the current value(s). With no argument, all of the values are returned, otherwise the value of parameter is returned. For compatibility with SEMATECH style servers, the parameter = value reply is spaced differently depending on whether you requested a specific parameter or not. |
| Replies |
one or more: |
| READ | create a populated table from a saved file |
| Syntax | READ [OVER] table |
| Examples |
read over lotdata |
| Description | This command restores a table schema and its data from a
previously
saved image. The saved image is created using the write
command. The OVER option
is used in order to replace an existing table using the read
command.
The read command expects to find a file of the name table.tab in its current working directory in order to process table table. The current working directory can be changed using the CWD parameter or the Tcl cd command. Subscription information is not saved and restored using read and write. When you use the OVER option and replace a table, existing subscriptions are all closed. |
| Replies |
//c 0 READ: table - Table read from file "table.tab" |
| REM | comment |
| Syntax | REM comment text |
| Examples |
rem uncomment the following statements for option B |
| Description | This command lets you lets you add comments to files of SQL commands that will be processed using the SQL include command. The # command and the rem command are equivalent. |
| Replies | none |
| SELECT | query table data |
| Syntax | SELECT { * | column [, column]* } FROM table [ WHERE where_criteria ] |
| Examples |
select help_text from help_config where lot_id='any lot' and step='etch' select * from capt_log where sdate<='1996-01-01' SELECT: capt_log event='Klingons near chimney' sdate='1992-12-25' SQL "select * from spc_datatype" |
| Description | select is used routinely to lookup stored table data. You can use the symbol * to specify that you would like all of the column values in your query, or you can explicitly list the columns of interest. If you do not provide a WHERE clause, you obtain all rows of the table. See The Where Clause for the grammar of the where_criteria argument. |
| Replies |
If select is executed in Tcl code from the SQL command, you obtain the Tcl Reply Format which is described in its own section. The format described below is obtained when the select statement is sent to a Datahub as a message command, or executed in Tcl code from the execute command. One or more messages for each row, formatted as |
| SET | configure a Datahub data parameter |
| Syntax | SET parameter=value |
| Examples |
set server=DBM |
| Description | The Datahub has a small set of configurable data parameters
that
can be assigned values using this command.
|
| Replies |
//c 0 SET: Variable "parameter" set to "value". |
| SOURCE | source a file of Tcl code |
| Syntax | SOURCE pathname |
| Examples |
source c:/users/doe/zapp.tcl source ../library/util.tcl |
| Description | This command is used to explicitly load Tcl code such as
procedure
definitions into the Datahub. The functionality is equivalent to the
Tcl
command source.
You use this command repeatedly during development to reload a file of source code that you are editing. However, for production, many applications will take advantage of the interpreter's auto_path mechanism that loads unknown Tcl code only if it is actually executed. To use this mechanism, the developer uses the auto_mkindex command to build a tclIndex file, and adds the directories where the source code files are found to the auto_path list. |
| Replies | The reply is an empty string or a single error message. |
| TELECT | an alias for the select command |
| Syntax | see select |
| Examples |
telect * from menu_data |
| Description | This command exists to provide the Tcl Reply Format for message system clients. In the name of compatibility, the reply to select statements received through the command mailbox is the older, one message per row style. Tcl applications can use "telect" instead of "select" to obtain the new format which is designed for ease of parsing in Tcl code. |
| Replies |
The Tcl Reply Format |
| UPDATE | change existing rows in a table |
| Syntax | UPDATE table SET column=value [ , column=value ]* [ WHERE where_criteria ] |
| Examples |
update fast_food set pace='slow', rate=4.5 where vendor='TurtleJoe' SQL "update lot_data set is_removed=1 where lot_id='9803021A'" |
| Description | This is the ANSI standard SQL statement for modifying the data of a single table. If you do not provide a WHERE clause, you update all rows of the table. See The Where Clause for the grammar of the where_criteria argument. |
| Replies |
//c 0 UPDATE: table - N row[s] updated. |
| WRITE | save a table to a file |
| Syntax | WRITE [OVER] table [pathname] |
| Examples |
write over variable_data write over variable_data /tmp/myfile.sql |
| Description |
This command writes a create statement and insert statements
representing
a table to a file. The file can be read and the table restored using
the read command or the include
command. The add_semicolon option
controls whether semicolon statement terminators are written into the
file data. If pathname is specified, it is used as the path and file specification for the table information. Otherwise, the command creates a file by the name of table.tab in the current working directory. |
| Replies |
//c 0 WRITE: table - Table written to file "file" - N of N rows |
The format is a seven element Tcl list. Each element is now described.
| Element | Index | Description |
| notification | 0 | This element will be the literal string select for the return value of a select or telect query. For a subsciption notification, it will be the literal text insert, update, delete, or drop. |
| tablename | 1 | This item with be the name of the database table. The name will be formatted exactly as it was in the table's create statement. |
| columns | 2 | This element will be a list of the column names in the same sequence as the data items in the data_list element. For a select statement, the columns are in the order of your request, or in the table create statement order if you queried for *. For a subscription notification, the list will always include the primary keys of the table. |
| key_columns | 3 | This element will be a list of the column names of the primary key columns. |
| row_count | 4 | This element returns a count of the data rows in the data_list element. For the usual insert, update, or delete subscription notification, the value will be 1 because a separate notification is issued for each row. |
| error_info | 5 | If your query has an error such as referring to a column or table that does not exist, or a syntax error, this element is a string containing a non-zero numeric return code, followed by a diagnostic message. The return code is the number that you see in the old style "//c number" reply documentation. If you have a valid query that does not find any data rows, it is not an error, and the error_info element is an empty string. |
| data_list | 6 | This element is a list of data rows. Each data row has the elements named in the columns element, in the same order. Character strings that have imbedded white space are delimited in Tcl fashion using braces instead of single quotes. There are as many rows as specified by the row_count element. |
Here is example code displaying this format:
# create a table with some dataYou may find the vset Tcl command extremely useful to assign the data of a row to the column names, as in the example:
SQL "create table a (i int, str varchar(20), d double)"
SQL "insert into a (i,str,d) values (1,'hello world', 1.1)"
SQL "insert into a (i,str,d) values (2, 'two', 2.2)"
puts [SQL "select * from a"]
select a {i str d} {} 2 {} {{1 {hello world} 1.1} {2 two 2.2}}
set reply [SQL "select str from a where i=1"]
select a {str} {} 1 {} {{{hello world}}}
# Here is a methodical way to parse the reply for a single value
set datarows [lindex $reply 6]
set firstrow [lindex $datarows 0]
set firstvalue [lindex $firstrow 0]
puts "str=$firstvalue"
str=hello world
#
# As of Tcl 8.4, lindex can take several indicies at once
# so this is the preferred method to isolate a value.
#
set firstvalue [lindex $reply 6 0 0]
puts "str=$firstvalue"
str=hello world
# this example is compact and backwards compatible:
set str [lindex [lindex [lindex $reply 6] 0] 0]
# The rset command is even more compact
# but is not more efficient unless you are using several values
# such as the row count, the errtext, and the data value
rset $reply "tag table cols keys ct errtext {{{str}}}"
if { $ct == 1 } { puts "str=$str" }\
elseif { $errtext != "" } { puts "Error: errtext=$errtext" }
puts [SQL "select bad syntax from a"]
select {a} {} {} 0 {1 a - column "bad" not in table} {}
puts [SQL "select * from a where i=200"]
select a {i str d} {} 0 {} {}
set reply [SQL "select * from lotdata where lot='x'"]
set cols [lindex $reply 2]
foreach row [lindex $reply 6] {
vset $row $cols
puts lot=$lot
}
BOLD is used to indicate commands, keywords, or punctuation that needs to be entered as written. With the SQL commands, the keywords are written in uppercase for emphasis, but you can enter them in lowercase too. Tcl commands such as SQL and hub_tables are case sensitive and are entered exactly as written.
Italics are used to indicate names or other items where you supply your own instance value. For example, where we have written table you are expected to supply the name of a table.
We use square brackets to enclose items that are optionally specified. For example [WHERE where_criteria] indicates this clause may be omitted. The square brackets themselves are not entered.
When you see the * character following a phrase, it means the phrase is optionally repeated 0 or more times. For example, column [, column]* indicates that one column name must be supplied, followed by zero or more , column phrases.
Finally, the braces are used to show sets of alternate choices, with the choices separated by the | character. For example, { PROC=procname | SQLPROC=procname }. The braces and the | character are part of the notation and are not entered when the command is used.The new Datahub provided by Hume Integration Software, has been written from "scratch" as a set of C code extensions to the Tcl/Tk interpreter. While the software retains command syntax and reply format compatibility with the SEMATECH SCC Data Server and the extended version developed at Motorola, it does not use any of the source code from these applications, such as the SEMATECH Generic Server. Therefore, there are a substantial number of changes that you may need to be aware of in running your existing applications.
First of all, the new Datahub returns the same reply formats for the ordinary, successful usage of commands, but the error replies are for the most part different. The basic error replies that might be used to test for table existence, such as the "table not found" reply are preserved. However, in most cases, you will find that the error replies of the new Datahub are more detailed and specific, and will help you develop your applications faster. For example, if you try to insert a duplicate row in a keyed table, the old data server replied:
//c 3 INSERT: Unable to insert.The new Datahub replies:
//c 3 INSERT: Unable to insert. Unique key violation
With the new Datahub, a table that has primary key columns does not allow duplicate rows. This is a change - the old versions allowed duplicate rows unless the keyword NODUPROWS was supplied in the table create statement. For compatibility, the new Datahub accepts and ignores the keyword NODUPROWS. With the new Datahub, if you want a table that allows duplicate rows, you need to declare it without key columns.
The new Datahub allows you to update the values of any columns in an update statement. The old versions would not let you update the values of key columns; you had to delete the row and insert the updated row instead. As explained with the open command, the new Datahub processes subscription notifications of key value updates as both a delete and an insert notification, and therefore preserves interoperable compatibilty with the old versions and existing applications.
There are other changes with the subscription features. The default REPLYTO for a message subscription was changed from the data server's command mailbox to NULL. This makes it safe to feed a message subscription to another Datahub without having the subscription replies coming into the command mailbox if a developer forgets to set REPLYTO.
Also, there is a new message subscription option, TELECT. When this option is indicated for a message subscription, the format used is the Tcl Reply Format instead of SQL statements.Another change was made to the subscription functionality in order to make it more consistent and easier to explain. The old versions always include key column values on delete and update notifications even when they are not requested. However, insert notifications only provide the columns that are explicitly requested. The new Datahub always includes the key column values, on all of the data change notifications, whether they are explicitly requested or not.
Another improvement is that the new Datahub is able to properly handle newlines when they are imbedded in the data of SQL statements that are read from files using the read or include commands. The old versions use the Generic Server INCLUDE code which reads each line in a file as a separate command. With the older versions, you cannot properly read the data of a table, if a row has an imbedded newline in its data.
The older versions use the eof command to signify both the end of command line or console input, as well as a command to initialize message system communication. The new Datahub uses the eof command only as a command to initialize message system communication. The new Datahub uses the command line, file input, and or console input consistently with other Tcl/Tk interpreter applications.
The new Datahub does not support the old sleep command. You can accomplish the same functionality using the after Tcl command.
The new Datahub is compatible with the old versions in allowing trailing semicolons on SQL commands. This is not ANSI SQL and is deprecated for new applications.
The new Datahub overcomes a very nasty problem with the old versions. When the old versions are shutdown, subscribers are sent delete notifications as all of the application data is deleted. Many applications feed subscription notifications to a persistent database, and risk losing all of their data if the Data Server is shutdown without the subscriptions being closed, and the persistent database interface is not shutdown first. The new Datahub exits without sending these notifications.
The new Datahub also uses binary signatures to protect against data corruption. With the old versions, if you close a subscription in a procedure notification of the same subscription, or drop a table in a procedure notification on that table, the program will almost certainly exit with a segmentation violation. The new Datahub will take these changes in stride.
There have been other improvements that should not adversely affect existing applications. For example, the memory utilization of the new Datahub is substantially improved over the old versions:
Date of last revision: $Date: 2022/11/30 20:30:35 $
This document covers the Hume Integration Software developed Datahub that is available for the Tcl 8.5/Tk 8.5 environment on the Windows NT and UNIX/POSIX platforms.
Go to the Datahub Contents
Leave this document and go to the Tcl/Tk Resource Index