MTB 643-00 Multics Technical Bulletin
To: MTB Distribution
From: Al Dupuis
Date: 12/20/83
Subject: The View Manager Facility: View Master Subsystem
ABSTRACT
The View Master subsystem is intended to be the replacement for
the LINUS subsystem. View Master is the end-user interface to
the View Manager relational system. This MTB provides an
overview of the View Master subsystem, along with reference
material describing the requests and SQL statements.
This MTB is only one of a group of related documents in the View
Manager series. These documents are
MTB-641 The View Manager Facility
MTB-642 The View Manager Facility: Subroutine Interfaces
MTB-643 The View Manager Facility: The View Master Subsystem
MTB-644 The View Manager Facility: SQL Parser
MTB-645 The View Manager Facility: Data Dictionary Interface
Comments may be made:
Via forum:
>udd>Multics>meetings>End_User_Data_Access
Via electronic mail:
Dupuis.Multics on System M
Via telephone:
(HVN) 357-6632 or (602) 862-6632
________________________________________
Multics Project internal working documentation. Not to be
reproduced outside the Multics Project.
MTB 643-00 Multics Technical Bulletin
Functional Capabilties
Introduction
This MTB describes the prototype version of the View Master
subsystem. View Master is intended to be a complete replacement
for the LINUS product. The interface described in this document
is the design for the prototype, and will change based on
findings during the prototyping. There are also miscellaneous
requests known to be needed for the final product which aren't
included for this prototype effort (i.e. a request to set and
list operational modes, a request to specify which data
dictionary to use).
The View Master subsystem will provide a menu/prompting interface
in addition to the traditional Multics command oriented
interface. This MTB describes only the traditional interface.
The menu/prompting interface will be described in a future MTB
after the prototype stage.
System Overview
The View Master subsystem is intended to be the end-user
interface to the View Manager relational system. All of the View
Manager functions, with the exception of the cursor oriented SQL
statements, will be available to the user. View Master and View
Manager will use the same language to accomplish relational
operations, and will both use the same code to implement these
functions. The IBM SQL language has been chosen as the user
interface language. The SQL statements common to the end-user
interface and the application programmers interface are
identical. The implementation of SQL will not be identical to
the IBM implementation due to differences in the Multics
philosophy, but will be extremely close.
System Structure
View Master (VM) provides SQL statements to perform relational
operations, and a number of requests to perform additional
non-relational functions. The requests are implemented as
standard ssu_ requests with code specific to VM to support the
functions. The SQL statements are implemented via code shared
between View Master and View Manager, and don't bear much
resemblence to other Multics commands or subsystem requests.
Instead they should be thought of more as a language specific to
the task they were designed for, much like a programming or text
editor language.
MTB 643-00 Multics Technical Bulletin
Functional Capabilties
System Components
View Master can be logically divided into six distinct portions.
The first portion consists of the standard ssu_ requests as
implemented in several Multics subsystems. The second (described
in attachment 1) consists of the requests to deal with the SQL
statements. The third (described in attachment 2) consists of
the requests to deal with the display of selected data. The
fourth (described in attachment 3) consists of the requests to
deal with display formats. The fifth (described in attachment 4)
consists of the SQL statements. The sixth portion will be made
up of seldom used, but necessary requests such as ones to set and
list modes, generate include files, etc. These requests will be
written up after the prototype stage, when their final form has
been agreed to.
All of these portions are well understood entities and have been
written up in other Multics documentation, with the exception of
the SQL statements and the requests to deal with the SQL
statements. The SQL statements are written up in reference form
in attachment 4, and are the subject of other MTBs in this
series. They will not be described further here. Instead the
rest of this section will cover the proposed method for
manipulating the SQL statements. This section is followed by the
various attachments that describe the View Master subsystem in
reference form.
SQL Statement Manipulation
A SQL statement is typed in directly from the view_master request
level, or, manipulated to a finer degree with the SQL utility
requests change_sql, check_sql, execute_sql, input_sql,
print_sql, and save_sql.
When a SQL statement is typed directly from request level, the
following actions are performed: (1) abbreviation processing is
done; (2) one level of quotes is removed; (3) request line
iteration is performed; and (4) active requests are evaluated.
All of these operations can be disabled or enabled at the
discretion of the user; the default for steps 1 and 3 is to have
them disabled. After these steps are performed the statement
becomes the current SQL statement and is checked for syntax. Any
errors encountered are reported to the user, and if errors are
found, processing stops. If no errors are found the statement is
executed. This will result in the requested action being
performed (i.e. the creation of a table). In the case of the
SQL select statement, this results in the return to view_master
request level where view_master requests such as
set_format_options and display can be used.
MTB 643-00 Multics Technical Bulletin
Functional Capabilties
The SQL utility requests allow a finer level of control when
manipulating SQL statements. The description of each follows.
The input_sql request allows a user to enter a SQL statement from
the terminal in a prompted input mode, or to enter a SQL
statement from an input file. The four steps of abbreviation
processing, quote stripping, request line iteration, and active
request evaluation are not done. The actions of replacing the
current SQL statement, checking it for syntax, and executing it
are under user control. The default action is to query the user
before replacing any current SQL statement. A negative response
to the query terminates the invocation of input_sql. A yes
answer results in the provided statement becoming the current
statement. The default is to then check the statement for syntax
and report any errors found to the user. If errors are found the
invocation of input_sql is terminated; a clean check results in
the immediate execution of the statement.
The check_sql request allows a user to have the current SQL
statement checked for syntax.
The execute_sql request allows a user to have the current SQL
statement executed. In the case of the SQL select statement, a
return to view_master request level is done where the user can
issue other view_master requests such as set_format_options and
display.
The print_sql request allows a user to print or return the
current SQL statement.
The save_sql request allows a user to save the current SQL
statement to a file.
MTB 643-00 Multics Technical Bulletin
Attachment 1
SQL Utility Requests
12/20/83 change_sql, cgsql
Syntax: change_sql {-control_args} command_line
Function: Places the current SQL statement into a temporary file, adds
the pathname of this file to the end of the supplied command line, and
executes the resulting Multics command line. If there is no current
statement or the control argument -new is used, the created file is
initially empty. The contents of the temporary file replaces the
current SQL statement after the Multics command line is executed.
Control Arguments:
-new
specifies that an empty file should initially be created.
-old
specifies that the existing statement should be made available
(DEFAULT).
Examples:
change_sql -new ted -pn
change_sql emacs
change_sql -old qedx -pn
MTB 643-00 Multics Technical Bulletin
Attachment 1
SQL Utility Requests
12/20/83 check_sql, cksql
Syntax: check_sql
Function: Checks the current SQL statement for correct syntax.
Examples:
check_sql
MTB 643-00 Multics Technical Bulletin
Attachment 1
SQL Utility Requests
12/20/83 execute_sql, exsql
Syntax: execute_sql
Function: Executes the current SQL statement, checking it first for
syntax if necessary.
Examples:
execute_sql
MTB 643-00 Multics Technical Bulletin
Attachment 1
SQL Utility Requests
12/20/83 input_sql, insql
Syntax: input_sql {-control_args}
Function: Takes the supplied SQL statement, makes it the current SQL
statement, checks it for syntax, and executes it.
Control Arguments:
-brief, -bf
specifies that the prompt "SQL Statement:" should be suppressed when
the statement is entered from the terminal.
-check, -ck
specifies that the SQL statement will be checked for syntax and any
errors found will be reported (DEFAULT).
-execute, -ex
specifies that the SQL statement will be executed if it's syntax is
correct (DEFAULT).
-force, -fc
specifies that the existing statement should be replaced. If a
statement exists and this control argument isn't used the user is
asked if the existing statement should be replaced. A negative
response terminates the invocation of input_sql.
-input_file path, -if path
specifies that the statement should be taken from the file named by
"path". If path does not have a suffix of ".sql", one is assumed.
-long, -lg
specifies that the prompt "SQL Statement:" should be written when
the statement is input from the terminal (DEFAULT).
-no_check, -nck
specifies that the SQL statement will become the current statement
but will not be checked for syntax or executed.
-no_execute, -nex
specifies that the SQL statement will become the current statement
and will be checked for syntax, but will not be executed.
-no_force, -nfc
specifies that if a SQL statement exists the user should be asked if
it should be replaced (DEFAULT). A negative response terminates the
invocation of input_sql.
-terminal_input, -ti
MTB 643-00 Multics Technical Bulletin
Attachment 1
SQL Utility Requests
specifies that the statements should be read from the terminal
(DEFAULT). A line consisting of only the single character "."
terminates the input. Typing "q" anywhere on a line also
terminates the input, but suppresses the syntax check and execution
of the statement regardless of the control arguments used.
Examples:
input_sql -input_file employee_status -force
input_sql
SQL Statement:
select * from sales
.
input_sql -force -input_file employee_status -no_check
input_sql -force -input_file employee_status -no_execute
MTB 643-00 Multics Technical Bulletin
Attachment 1
SQL Utility Requests
12/20/83 print_sql, prsql
Syntax: print_sql, or, [print_sql]
Function: Prints or returns the current SQL statement.
MTB 643-00 Multics Technical Bulletin
Attachment 1
SQL Utility Requests
12/20/83 save_sql, svsql
Syntax: save_sql path
Function: Takes the current SQL statement and saves it to the file
named by path. If path does not have a suffix of "sql" one is assumed.
MTB 643-00 Multics Technical Bulletin
Attachment 2
Display Requests
12/20/83 column_value, clv
Syntax: [column_value column_id -control_arguments]
Function: returns the value of the specified column for the current
row, the previous row, or the next row. This request can only be used
as an active request. It is used within a formatted report produced by
the display request to obtain a column's value. It is an error to use
this request anywhere except in a header/footer or editing string
within a report produced by the display request.
Arguments:
column_id
specifies which column the value should be returned for. column_id
can be given as the name of the column as defined in the accessed
table, or the number of the column in the SQL select statement.
Control arguments:
-current_row, -crw
specifies that the value of the named column for the current row
should be returned. This is the default.
-default STR
where STR is the character string that will be returned when there
is no previous row, or, when there is no next row. If this control
argument is not used the default value for STR is "".
-next_row, -nrw
specifies that the value of the named column for the next row should
be returned. If there is no next row, the string "" is returned
unless changed by the -default control argument.
-previous_row, -prw
specifies that the value of the named column for the previous row
should be returned. If there is no previous row, the string "" is
returned unless changed by the -default control argument.
Examples:
column_value foo
column_value 3
column_value foo -previous_row
column_value foo -next_row -default NO_NEXT_ROW
MTB 643-00 Multics Technical Bulletin
Attachment 2
Display Requests
12/20/83 display, di
Syntax: display {-control_args}
Function: retrieves selected data, creates a report, and displays it
on the terminal, to a file, or an io switch.
Control arguments:
-all, -a
specifies that every page of the report is to be displayed. This
argument is incompatible with -pages. -all is the default.
-brief, -bf, -long, -lg
specifies that display is to suppress warning messages (-brief), or
print warning messages (-long). Warning messages are printed when a
control argument such as -old_retrieval is used and the data from a
previous retrieval isn't available. -long is the default.
-character_positions STR1 {STR2}, -chpsn STR1 {STR2}
where STR1 and STR2 define the left and right character positions of
a vertical section of the report. STR1 must be given and defines
the left margin position to begin from. STR2 is optional, and if it
is not given the default is the rightmost character position of the
report. If this control argument is not given the entire page is
printed.
-discard_report, -dsrp, -keep_report, -krp
specifies that display is to delete or keep the report on its
termination. -keep_report is necessary to use -old_report on
subsequent invocations of display. -discard_report is the default.
-discard_retrieval, -dsr, -keep_retrieval, -kr
specifies that display is to delete or keep the retrieved data on
its termination. Keeping the retrieved data allows its re-use on
subsequent invocations of the display request. Previously retrieved
data that has been sorted retains its sort order.
-discard_retrieval is the default.
-enable_escape_keys, -eek
specifies that display is to use the escape keys sequences, rather
than the terminal's function keys and arrow keys, for the scrolling
functions. This is the default if the -scroll control argument is
given and the terminal doesn't have the necessary set of function
keys and arrow keys (see -enable_function_keys below.) (In the
MTB 643-00 Multics Technical Bulletin
Attachment 2
Display Requests
following description the mnemonic "esc-" means the terminal's
escape key.) The following escape key sequences are used if this
control argument is given, or, the terminal lacks the necessary set
of keys: forward -- esc-f; backward -- esc-b; left -- esc-l; right
-- esc-r; help -- esc-?; set_key -- esc-k; set_scroll_increment --
esc-i; quit -- esc-q; redisplay -- esc-d; start_of_report -- esc-s;
end_of_report -- esc-e; multics_mode -- esc-m; and goto -- esc-g.
-enable_function_keys, -efk
specifies that display is to try to use the terminal's function keys
and arrow keys for the scrolling functions. This is the default
when the -scroll control argument is given and the terminal has at
least nine function keys and the four arrow keys. (In the following
description the mnemonic: "fN" means function key N, where N is the
number of the function key; "down_arrow" means the down arrow key;
"up_arrow" means the up arrow key; "left_arrow" means the left arrow
key; and "right_arrow" means the right arrow key.) The following
key sequences are used if this control argument is given and the
terminal has the necessary set of keys: forward -- down_arrow;
backward -- up_arrow; left -- left_arrow; right -- right_arrow; help
-- f1; set_key -- f2; set_scroll_increment -- f3; quit -- f4;
redisplay -- f5; start_of_report -- f6; end_of_report -- f7;
multics_mode -- f8; and goto -- f9.
-extend
specifies that when the -output_file control argument is used and
the file exists, the report is appended to the end of the file
rather than replacing it. If this control argument isn't used the
default is to truncate an existing file.
-new_report, -nrp, -old_report, -orp
specifies that display is to create a new report, or, use the report
created in its previous invocation. -old_report requires that
"-keep_report" was used in the prior invocation of display.
"-new_report" is the default.
-new_retrieval, -nr, -old_retrieval, -or
specifies that display should begin a new retrieval from the data
base, or, use data retrieved during its previous invocation.
-new_retrieval is the default.
-pages STR, -pgs STR, -page STR, -pg STR
where STR is a blank separated list of pages (N N) or page ranges
(N,N). Page ranges can also be given as "N," or "N,$" which means
from page N to the end of the report, or $ which means the last
page. This argument is incompatible with "-all".
MTB 643-00 Multics Technical Bulletin
Attachment 2
Display Requests
-passes N, -pass N
where N is the number of times the report will be formatted. If
this control argument isn't given the default value for N is 1. No
output is produced until the last formatting pass of the report.
-output_file path, -of path
where path is the name of the file which will contain the formatted
report. If this argument and -output_switch are not given the
report is displayed on the terminal. This argument is incompatible
with -output_switch.
-output_switch switch_name, -osw switch_name
where switch_name is the name of a switch which will be used to
display the report. It is an error to use this control argument if
the named switch is not already open and attached when display is
invoked. This argument is incompatible with -output_file.
-scroll
specifies that display is to scroll the report according to key
sequences read from the terminal. Only terminals supported by the
Multics video system can use the scrolling feature. If the -window
control argument isn't used, display will create a uniquely named
window for the display of the report. The user_i/o window will be
reduced to four lines and the remaining lines will be used for the
uniquely named report display window. The minimum size for this
window is five lines, so the user_i/o window must be at least nine
lines before invoking display, unless the -window control argument
is used.
-set_key STR, -set_keys STR, -sk STR, -sks STR
specifies that the named scrolling functions are to be set to the
provided key sequences. STR is a blank separated list of one or
more scrolling function names and key sequences, given as
"function_name key_sequence ... {function_name key_sequence}". The
function names can be chosen from the set listed above under the
description of the "-enable_escape_keys" or "-enable_function_keys"
control arguments. The key sequences can be given as the actual
sequences, or, mnemonic key sequences. The provided mnemonic's can
be:
"fN" where N is the number of the desired function key; "esc-" or
"escape-" where this corresponds to the terminal's "escape"
character; "ctl-X" or "control-X" where this corresponds to the
character sequence generated by the terminal when the "control" key
is held while also pressing the character named by "X"; "down_arrow"
where this corresponds to the terminal's down arrow key; "up_arrow"
where this corresponds to the terminal's up arrow key; "left_arrow"
MTB 643-00 Multics Technical Bulletin
Attachment 2
Display Requests
where this corresponds to the terminal's left arrow key;
"right_arrow" where this corresponds to the terminal's right arrow
key; and "home" where this corresponds to the terminal's home key.
-sort STR {-ascending | -descending} {-case_sensitive |
-non_case_sensitive} ... {STR {-asc | -dsc} {-cs | -ncs}}
where STR is the name of a column as defined in the accessed table
or a number corresponding to the position of the column in the SQL
select statement. It can be followed by -ascending or -descending,
and -case_sensitive or -non_case_sensitive. If -ascending or
-descending is not specified, the default is -ascending. If
-case_sensitive or -non_case_sensitive is not specified, the default
is -case_sensitive.
-temp_dir dir_name, -td dir_name
specifies that the given directory should be used for storing the
retrieved data, the saved report if "-keep_report" is used, and
sorting workspace if "-sort" is used, instead of the process
directory. This temp dir will continue to be used until another new
temp dir is requested. A new temp dir can only be specified when a
new retrieval and new report are requested.
-truncate, -tc
specifies that when the -output_file control argument is used and
the file already exists, the report replaces the contents of the
file. If the -extend control argument isn't given the default is
-truncate.
-window STR, -win STR
specifies that the window named by STR should be used for the
display of the report. This argument is only meaningful when the
-scroll argument is also used. If this control argument is used the
window named by STR must be attached and open under the video
system, and it must be at least five lines high.
Examples:
display
display -output_file foo
display -keep_retrieval -sort bar -descending -non_case_sensitive
display -keep_retrieval -keep_report -of foo1
-character_positions 1 132
display -old_retrieval -old_report -of foo2
-character_positions 133 260
display -pages 1 3 12,19 58,$ -output_switch foo
MTB 643-00 Multics Technical Bulletin
Attachment 2
Display Requests
display -sort foo -decending bar -non_case_sensitive
MTB 643-00 Multics Technical Bulletin
Attachment 2
Display Requests
12/20/83 display_builtins, dib
Syntax: [display_builtins STR]
Function: returns the current value of the builtin named by STR. This
request can only be used as an active request. It is used within a
formatted report produced by the display request to obtain the current
value of the specified builtin. It is an error to use this request
anywhere except in a header/footer or editing string within a report
produced by the display request.
STR can be any one of the following builtins:
current_pass_number
the number of the current pass. This number begins at 1 and is
incremented by 1 for each additional formatting pass over the
report.
current_row_number
the number of the current row of the report.
first_row
"true" if the current row is the first row of the report. "false"
if it is not the first row of the report.
last_page_number
the number of the last page of the report, or "0" if it is the first
pass over the report. After each formatting pass over the report
this number is updated with the number of the last page.
last_pass
"true" if this is the last formatting pass of the report. "false"
if this is not the last pass of the report.
last_row
"true" if the current row is the last row of the report. "false" if
the current row is not the last row of the report.
last_row_number
the number of the last row of the table, or "0" if it is the first
pass over the report. After the first formatting pass over the
report this number is set to the number of the last row.
page_number
the number of the current page of the report.
previously_processed_row
MTB 643-00 Multics Technical Bulletin
Attachment 2
Display Requests
"true" if the current row had been processed on the preceding page,
but the row value would not fit and had to be deferred to the
current page. "false" if this is the first time the current row has
been processed.
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
12/20/83 list_format_options, lsfo
Syntax: lsfo -control_arg
or lsfo -format_option_args
or [lsfo -format_option_arg]
Function: lists the names and values of individual report formatting
options; all report formatting options; or the active report formatting
options. As an active request returns the value of the specified
format option.
Control arguments:
-active, -act
specifies that only the active formatting options are to be listed
(DEFAULT). Type help "formatting_options.gi" for more information
on "active" formatting options. This control arg is incompatible
with "-all" and the format option arguments.
-all, -a
specifies that all formatting options are to be listed. This
control arg is incompatible with "-active" and the format option
arguments.
Format Option Arguments (General Report Options):
-delimiter, -dm
the character used to delimit the different portions of a header or
footer.
-format_document_controls, -fdc
specifies the interpretation of imbedded format document controls
when filling is occuring, or the treatment of imbedded controls as
ordinary text.
-hyphenation, -hph
specifies whether or not hyphenation will be attempted for
overlength values.
-page_footer_value, -pfv
the page footer placed at the bottom of each page.
-page_header_value, -phv
the page header placed at the top of each page.
-page_length, -pl
the length of each formatted page given as the number of lines.
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
-page_width, -pw
the width of each formatted page given as the number of character
positions.
-title_line, -tl
specifies the printing or suppression of printing of the title line.
-truncation, -tc
the character or characters used to indicate truncation has occured.
Format Option Arguments (General Column Options):
-column_order, -co
the order of the display of columns in the detail line.
-count, -ct
the columns which will have counts taken on them.
-exclude, -ex
the columns that will be excluded from display in the detail line.
-group, -gr
the columns used to group a number of rows based on their values.
-group_footer_trigger, -gft
the columns which are candidates to cause the generation of the
group footer.
-group_footer_value, -gfv
the group footer placed after each group of rows.
-group_header_trigger, -ght
the columns which are candidates to cause the generation of the
group header.
-group_header_value, -ghv
the group header placed before each group of rows.
-outline, -out
the columns which are candidates for duplicate suppression.
-page_break, -pb
the columns which are candidates to cause a break to a new page.
-row_footer_value, -rfv
the row footer placed after each row value.
-row_header_value, -rhv
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
the row header placed before each row value.
-subcount, -sct
the columns which will have subcounts taken on them.
-subtotal, -stt
the columns which will have subtotals taken on them.
-total, -tt
the columns which will have totals taken on them.
Format Option Arguments (Specific Column Options):
"column_id" in the following descriptions means the column name as
defined in the accessed table, the number of the column in the SQL
statement, or a star name which is matched against the column names.
-alignment column_id, -al column_id
the alignment mode within the display width for the specified
column.
-editing column_id, -ed column_id
the editing string for the specified column.
-folding column_id, -fold column_id
the folding action taken when the column value exceeds the display
width for the specified column.
-separator column_id, -sep column_id
the character string that separates the specified column from the
column in the detail line which immediately follows it.
-title column_id, -ttl column_id
the character string that is placed at the top of the page above the
specified column.
-width column_id, -wid column_id
the display width in the detail line for the specified column.
Notes:
Refer to the description of the set_format_options request for a
complete list of the default values for the format options and a
discussion of their allowed values.
Examples:
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
list_format_options
list_format_options -all
list_format_options -width 1 -alignment salary
list_format_options -page_width -title ** -page_length
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
12/20/83 restore_format_options, rsfo
Syntax: rsfo path
Function: restores the saved report layout specified by path. Only
the formatting options found in the saved report layout have their
values changed.
Arguments:
path
the pathname of the saved report format to be restored. If path
does not have a suffix of ".fo.vmec", one is assumed.
Notes:
Refer to the save_format_options request for more detail on the content
of the saved report format.
Examples:
restore_format_options sample_display_format
restore_format_options another_display_format.fo.vmec
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
12/20/83 save_format_options, svfo
Syntax: svfo path {-format_option_args} {-control_args}
Function: saves the current values of format options as a subsystem
exec_com. The saved format can be restored later with the
restore_format_options request. The file is saved with a suffix of
".fo.vmec". Individual format options; active format options; or all
the format options can be saved. The current SQL statement can also be
saved.
Arguments:
path
the pathname of the segment which will contain the saved format.
The suffix ".fo.vmec" is appended if not given.
Format Option Arguments:
Refer to the description of the list_format_options request for a
complete list of the names of the format option arguments. Each format
option named will have its value saved in the exec_com specified by
path. These arguments are incompatible with the control arguments -all
and -active listed below.
Control arguments:
-active, -act
specifies that only the active formatting options will be saved.
(DEFAULT) Type "help formatting_options.gi" for more information on
"active" formatting options. This control argument is incompatible
with the format option arguments and "-all".
-all, -a
specifies that all formatting options should be saved. This control
argument is incompatible with the format option arguments and
"-active".
-sql_statement, -sqlst
specifies that the current SQL statement should also be saved. A
restore_format_options on the saved format will also restore and
process the saved SQL statement.
Notes:
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
-all and -active are incompatible with the format option arguments and
can not be used on the same request line. -all and -active are
incompatible with each other and if both are supplied on the request
line, the last one given will be used.
Examples:
save_format_options report_layout
save_format_options report_layout -all
save_format_options report_layout -sql_statement
save_format_options report_layout -page_header_value -page_footer_value
save_format_options report_layout -page_header_value -width salary
save_format_options report_layout -width ** -page_footer_value
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
12/20/83 set_format_options, sfo
Syntax: sfo {-format_option_args} {-control_args}
Function: sets individual report format options to user specified or
default values, and/or all of the formatting options to their default
values.
The option value given for any format option argument can be the
control arguments "-default" or "-prompt". If -default is given for
the value, view_master will set the value of the format option to the
system default. If -prompt is given for the value, view_master will
prompt for the value with the prompt string "Enter
FORMAT_OPTION_NAME.". A line consisting of the single character "."
will terminate the prompted input mode. To suppress the printing of
the prompt string use the -brief control argument.
Control arguments:
-brief, -bf, -long, -lg
print/don't print the prompt string for values when the "-prompt"
control argument is given. The default is -long, which means prompt
with the string "Enter FORMAT_OPTION_NAME". If -brief and -long are
both used on the request line, the last one supplied will be used.
-default
specifies that view_master should set the value of the format option
which immediately precedes this control argument to the system
supplied default.
-string STR, -str STR
allows STR to be entered as a format option value when STR begins
with a hyphen.
-prompt
specifies that view_master should prompt for the value of the format
option which immediately precedes this control argument. A prompt
string will be written before the prompting action unless the -brief
control argument is used. A line consisiting of the single
character "." will terminate the prompted input mode.
-reset, -rs, -no_reset, -nrs
reset/don't reset all formatting options to their system default
values. The default is -no_reset which means only the user
specified options will have their values changed. If -reset is
given, all format options will be reset to their system default
values before the values are changed for any other format options
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
specified in the request line. If -reset and -no_reset are both
used on the request line, the last one supplied will be used.
Format Option Arguments (General Report Options):
-delimiter CHAR, -dm CHAR
the character used to delimit the different portions of a header or
footer. The default character is "!". This can be set to any
printable character.
-format_document_controls STR, -fdc STR
the value of -format_document_controls is used to determine if the
format_document_ subroutine should interpret format document control
lines when filling overlength text. The default value for STR is
"off" which means format_document_ will not check for control lines
imbedded in the text. STR can also be set to "on" which means
format_document_ will interpret control lines in the text and
provide special filling actions based on the imbedded control lines.
-hyphenation STR, -hph STR
the value of -hyphenation is used to determine if hyphenation should
be attempted when filling overlength character strings. The default
value for STR is "off" which means no hyphenation is attempted by
default. STR can also be set to "on" which specifies that
hyphenation should be attempted.
-page_footer_value STR, -pfv STR
the page footer placed at the bottom of each page. The page footer
can consist of more than one line, and each line can have a left,
right, and center portion. The individual portions of each line are
delimited by the delimiter character. Active requests found in the
header are evaluated and their return value is placed into the
header before folding and alignment takes place. Portions of a
header or footer whose length are zero have their space on the page
redistributed to other portions whose lengths are not zero. For
example, if the page header contained only a center portion
(!!Sample Center Portion!!), the text would be centered on the page,
but would have the full page width available for the text.
Similarly, a left portion or right portion only would be aligned to
the left or right of the page, but would have the full page width
available for placement of its text. Two exceptions to this action
are when the header or footer has a left, right, and center portion,
and the left or right portion has a zero length (e.g.
!left part!center part!! or !!center part!right part!). In these
two cases the left or right part of the page is unavailable for
placement of text; that is, the space is not redistributed to the
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
other two portions. If the redistribution of the available page
width is not desired, the placement of a single blank into a portion
will prevent the redistribution from taking place because the
portion will have a length greater than zero (i.e.
! !Center Part! !). The default value for STR is "" which means
there is no page footer provided by default.
-page_header_value STR, -phv STR
the page header placed at the top of each page. Refer to the
description of -page_footer_value for the content of a
header/footer. The default value for STR is "" which means there is
no page header provided by default.
-page_length N, -pl N
the length of each formatted page given as the number of lines. N
can be given as "0" or any positive integer. "0" means that the
report is not to be paginated and it is created as one continous
stream. The default value for N is "0".
-page_width N, -pw N
the width of each formatted page given as the number of character
positions. N can be given as "0" or any positive integer. "0"
means that the page width will always be set by view_master to be
the exact width needed to contain all of the columns specified in
the SQL statement which are not excluded. If N is greater than zero
and the width for any column exceeds N, the column's width is
automatically set to N. The default value is "79" character
positions.
-title_line STR, -tl STR
the value of -title_line is used to determine whether a title line
should be printed. The default value for STR is "on" which means
that a title line is printed at the top of each page. STR can also
be set to "off" to inhibit the printing of the title line.
-truncation STR, -tc STR
the value of -truncation is used to determine the character(s) that
should be used to indicate that truncation of some value has
occured. The default value for STR is "*". STR can be set to any
sequence of printable characters.
Format Option Arguments (General Column Options):
-column_order COLUMN_LIST, -co COLUMN_LIST
the value of -column_order is used to determine the order that the
columns should appear in the detail line. The default value for
COLUMN_LIST is the list of columns from the SQL statement in the
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
order supplied. This means that the columns will appear in the
exact same order as they appear in the SQL statement. COLUMN_LIST
can be set to a list of column names or numbers. Columns missing
from this list will be placed after the columns which appear in the
list. That is, if five columns were selected and the -column_order
value had been given as "3 2", the complete order would be "3 2 1 4
5".
-count COLUMN_LIST, -ct COLUMN_LIST
the value of -count is used to determine the columns counts should
be generated for. The default value for COLUMN_LIST is "" which
means no columns should have counts generated. COLUMN_LIST can be
set to a list of column names or numbers. Counts are generated
after the last detail line. If a count is requested on a column
that is excluded, the count is also excluded from the page. An
exception to this rule is when all columns have been excluded.
Counts are provided in this case to allow reports consisting of some
combination of counts, subcounts, totals, and subtotals only.
-exclude COLUMN_LIST, -ex COLUMN_LIST
the value of -exclude is used to determine if any of the columns
selected should be excluded from the detail line. The default value
for COLUMN_LIST is "" which means no columns should be excluded.
COLUMN_LIST can be set to a list of column names or numbers.
-group COLUMN_LIST, -gr COLUMN_LIST
the value of -group is used to group a number of rows based on the
values of one or more columns. The default value for COLUMN_LIST is
"" which means no group of rows has been defined. COLUMN_LIST can
be set to a list of column names or numbers. The column or columns
named in the list become a hierarchy of columns. The first column
named is the most major column, and the last column named becomes
the most minor column. The hierarchy of columns can be used with
the -outline, -page_break, -subtotal and -subcount options as
described under each option's description.
-group_footer_trigger COLUMN_LIST, -gft COLUMN_LIST
the value of -group_footer_trigger is used to determine when to
generate the group footer. The default value for COLUMN_LIST is ""
which means no group footer triggers are defined by default.
COLUMN_LIST can be set to a list of column names or numbers. The
columns which appear in this list must also appear in the column
list associated with the -group option. If the -group option is set
to a new value, columns which are eliminated from the COLUMN_LIST
are also eliminated from the -group_footer_trigger COLUMN_LIST.
When any of the columns specified in the COLUMN_LIST are about to
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
change with the next row, the group footer is evaluated. The group
footer is always evaluated after the last row of the report.
-group_footer_value STR, -gfv STR
the group footer placed after each group of rows when any of the
columns associated with the -group_footer_trigger option changes.
Refer to the description of -page_footer_value for the content of a
header/footer. The default value for STR is "" which means there is
no group footer defined by default.
-group_header_trigger COLUMN_LIST, -ght COLUMN_LIST
the value of -group_header_trigger is used to determine when to
generate the group header. The default value for COLUMN_LIST is ""
which means no group header triggers are defined by default.
COLUMN_LIST can be set to a list of column names or numbers. The
columns which appear in this list must also appear in the column
list associated with the -group option. If the -group option is set
to a new value, columns which are eliminated from the COLUMN_LIST
are also eliminated from the -group_header_trigger COLUMN_LIST.
When any of the columns specified in the COLUMN_LIST have just
changed with the current row, the group header is evaluated. The
group header is always evaluated before the first row of the report.
-group_header_value STR, -ghv STR
the group header placed before each group of rows when any of the
columns associated with the -group_header_trigger option changes.
Refer to the description of -page_footer_value for the content of a
header/footer. The default value for STR is "" which means there is
no group header defined by default.
-outline COLUMN_LIST, -out COLUMN_LIST
the value of -outline is used to determine if duplicate values in a
column should be suppressed. The default value for COLUMN_LIST is
"" which means no columns should have duplicate values suppressed.
COLUMN_LIST can be set to a list of column names or numbers. If the
value of a named column is the same as its previous value, then the
value will be suppressed unless it is the first line of a new page.
If any of the named columns are a member of the "group" of rows
defined by the -group option, then it and all of the columns more
major in this group have outlining done on them. A change in value
of any one column causes all columns lower in the hierarchy to have
their values displayed, in addition to the column that changed. An
exception to this is if it is the first line on a new page, when
duplicate values are never suppressed.
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
-page_break COLUMN_LIST, -pb COLUMN_LIST
the value of -page_break is used to determine when page breaks
should be generated. The default value for COLUMN_LIST is "" which
means that no columns are watched for page breaks. COLUMN_LIST can
be set to a list of column names or numbers. The columns specified
in the list are watched and when their values change, a break to a
new page is generated. If any of the named columns are a member of
the "group" of rows defined via the -group option, it and all
columns more major in the group will be watched for page breaks.
-row_footer_value STR, -rfv STR
the row footer placed after each detail line. Refer to the
description of -page_footer_value for the content of a
header/footer. The default value for STR is "" which means there is
no row footer provided by default.
-row_header_value STR, -rhv STR
the row header placed before each detail line. Refer to the
description of -page_footer_value for the content of a
header/footer. The default value for STR is "" which means there is
no row header provided by default.
-subcount SUBCOUNT_SPEC, -stt SUBCOUNT_SPEC
the value of subcount is used to determine what columns subcounts
should be generated for, when they should be generated, and what
type of subcount should be generated. The default value for
SUBCOUNT_SPEC is "" which means no subcounts should be generated for
any columns. SUBCOUNT_SPEC can consists of one or more blank
separated "triplets". The syntax of a triplet is
"column_1,column_2{,reset | running}". column_1 is the name or
number of the column that a subcount will be generated for.
column_2 is the name or number of a column whose value should be
watched to determine when to generate the subcount. When the value
of this column being watched changes, the subcount is generated. If
this column is a member of the "group" of rows defined via the
-group option, it and all columns more major in the group will be
watched for subcount generation.
"reset" or "running" indicates the type of subcount desired. If
neither is given then "reset" is the default. "reset" means the
subcount counter will be reset to zero each time a subcount is
generated. "running" means the subcount will not be reset to zero.
If a subcount is requested on a column that is excluded, the
subcount is also excluded from the page. An exception to this rule
is when all columns have been excluded. Subcounts are provided in
this case to allow reports consisting of some combination of counts,
subcounts, totals, and subtotals only.
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
-subtotal SUBTOTAL_SPEC, -stt SUBTOTAL_SPEC
the value of subtotal is used to determine what columns subtotals
should be generated for, when they should be generated, and what
type of subtotal should be generated. The default value for
SUBTOTAL_SPEC is "" which means no subtotals should be generated for
any columns. SUBTOTAL_SPEC can consists of one or more blank
separated "triplets". The syntax of a triplet is
"column_1,column_2{,reset | running}". column_1 is the name or
number of the column that a subtotal will be generated for.
column_2 is the name or number of a column whose value should be
watched to determine when to generate the subtotal. When the value
of this column being watched changes, the subtotal is generated. If
this column is a member of the "group" of rows defined via the
-group option, it and all columns more major in the group will be
watched for subtotal generation.
"reset" or "running" indicates the type of subtotal desired. If
neither is given then "reset" is the default. "reset" means the
subtotal counter will be reset to zero each time a subtotal is
generated. "running" means the subtotal will not be reset to zero.
If a subtotal is requested on a column that is excluded, the
subtotal is also excluded from the page. An exception to this rule
is when all columns have been excluded. Subtotals are provided in
this case to allow reports consisting of some combination of counts,
subcounts, totals, and subtotals only.
-total COLUMN_LIST, -tt COLUMN_LIST
the value of -total is used to determine the columns totals should
be generated for. The default value for COLUMN_LIST is "" which
means no columns should have totals generated. COLUMN_LIST can be
set to a list of column names or numbers. Totals are generated
after the last detail line. If a total is requested on a column
that is excluded, the total is also exlcuded from the page. An
exception to this rule is when all columns have been excluded.
Totals are provided in this case to allow reports consisting of some
combination of counts, subcounts, totals, and subtotals only.
Format Option Arguments (Specific Column Options):
"column_id" in the following descriptions means the column name as
defined in the accessed table, the number of the column in the SQL
statement, or a star name which is used to match column names.
-alignment column_id STR, -al column_id STR
the value of -alignment is used to determine the alignment of a
column value within its display width. "column_id" specifies which
column the alignment applies to. "STR" is the alignment mode and
can be set to "center", "left", "right", "both", or "decimal N".
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
The default value for STR depends upon the type of column selected.
Character and bit strings default to left alignment, decimal data
with a non-zero scale defaults to decimal point alignment, and all
other data types default to right alignment. For decimal alignment,
the decimal alignment position within the display width is given a
default value. This alignment position can be changed by the user
by specifying the value as "decimal N", where N is the character
position within the display width where the decimal point should be
aligned. The alignment mode of "both" specifies that the column
value will be aligned to the left and rightmost character positions
within its display width. Text is padded by insertion of uniformly
distributed whitespace if necessary.
-editing column_id STR, -ed column_id STR
the value of -editing is used to specify additional editing that
should be done to the column value before it is placed on the page.
"column_id" specifies which column the editing applies to. The
default value for STR is "" which means additional editing should
not be done. Multics active functions and view_master active
requests are normally used to provide the additional editing. For
example, to place commas and dollar signs in a column called
"salary", the string "[pic $99,999v.99 [column_value salary]]" could
be specified as the editing value. Refer to the description of the
"column_value" request for its usage.
-folding column_id STR, -fold column_id STR
the value of -folding is used to determine what type of action
should occur when a column value exceeds its display width.
"column_id" specifies which column the folding applies to. The
default value for STR is "fill" which means portions of the value
which exceed the display width are moved down to the next line(s)
until a correct fit is obtained. STR can also be set to "truncate"
which means the column's value is truncated to fit in the display
width and the truncation character(s) is placed at the end of the
value to indicate truncation has occurred.
-separator column_id STR, -sep column_id STR
the value of -separator is used to separate a column from the next
one following it. The last column on a line does not have a
separator. "column_id" specifies which column the separator applies
to. The default value for STR is two blanks. STR can be changed to
any sequence of printable characters.
-title column_id STR, -ttl column_id STR
the value of -title is placed above the column at the start of each
page if the -title_line option is set to "on". "column_id"
specifies which column the title applies to. The default value of
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
STR is the name of the column taken from the accessed table. In the
case of expressions the default value for STR is "eN", where N
begins at 1 and is incremented by 1 for each additional expression
found in the select list. If the title isn't the same number of
characters as the column's display width, the title is centered
within the display width for its associated column. If the value of
title is wider than the columns display width it is filled or
truncated to obtain a correct fit, depending on it's parent column's
folding action.
-width column_id N, -wid column_id N
the value of width is used to determine the display width for a
column. "column_id" specifies which column the width applies to.
The default value for N is the width for the column derived from the
accessed table. The derived width will be the number of characters
needed to contain the value after conversion from the data type
found in the table to character format. N can be set to any
positive integer.
Notes:
At least one format option argument or "-reset" must be specified.
Format option arguments and control arguments can be mixed freely in
the request line, but a control argument cannot be placed in between a
format option name and a format option value. For example, "sfo
-page_width 80 -reset" is a valid request. "sfo -page_width -reset 80"
is not valid. If a value is to be set that begins with a hyphen, the
control argument "-string" must be given before the value to
distinguish it from control arguments and format option arguments.
Examples:
set_format_options -width 1 25
set_format_options -title emp_name Employee Name
set_format_options -reset -page_width 80 -page_length 60
set_format_options -page_footer_value -prompt
Enter -page_footer_value.
!!-[display_builtins page_number]-!!
.
set_format_options -page_header_value -prompt
Enter -page_header_value.
![e date]!View Master REPORT![e time]!
!!!!
!!--Page [display_builtins page_number]--!!
.
sfo -exclude exchange extension -width area_code 12
MTB 643-00 Multics Technical Bulletin
Attachment 3
Format Option Requests
sfo -editing area_code -prompt
Enter -editing area_code.
[fl ^a/^a-^a [clv area_code] [clv exchange] [clv extension]]
.
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 alter table
Syntax: alter table TABLE_NAME add COLUMN_NAME DATA_TYPE
Function: Changes the structure of the specified table.
Arguments:
TABLE_NAME
is the name of the table which will be altered.
COLUMN_NAME
is the name of the new column which will be added to the right-hand
side of the specified table. The new column added is assigned a
null value for each row present in the table. This name must not be
the same as any column which exits in the table.
DATA_TYPE
is the data type of the column to be added. The data types
supported are described in the create table documentation.
Notes:
You must have alter permission on the table to add a new column.
Examples:
alter table employee add salary fixed dec (7,2)
alter table employee add name char (32)
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 alter tablespace
Syntax: alter tablespace NAME OPERATION N records
Function: Changes the quota assignment for the specified tablespace.
Arguments:
NAME
is the name of the tablespace whose quota assignment will be
altered.
OPERATION
is the character string "add" or "remove" and specifies whether
quota should be added to the tablespace from the containing
directory, or removed from the tablespace and assigned to the
containing directory.
N records
is the number of records of quota to be added or removed.
Notes:
You must have alter_tablespace permission to use this statement.
Examples:
alter tablespace employee_tables add 50 records
alter tablespace department_tables remove 50 records
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 comment
Syntax: comment on OBJECT NAME is QUOTED_STRING
Function: Enters an explanatory comment into the comment column for
the specified table or column. Any existing comment is replaced.
Arguments:
OBJECT
is the character string "table" or "column", and denotes whether the
comment applies to a column or table.
NAME
is the name of the table or column for which the comment will be
entered. If a comment already exists for the table or column it is
replaced.
QUOTED_STRING
is the comment which will be entered. If STR contains whitespace it
must be enclosed in quotes.
Notes:
You must have alter permission on the table in order to use the comment
statement.
Examples:
comment on column employee.sal is """Employee's yearly salary."""
input_sql
SQL Statement:
comment on table employee is "Employee information."
.
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 commit work
Syntax: commit work
Function: This SQL statement ends a logical unit of work and commits
all changes made since the transaction began.
Examples:
commit_work
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 create assertion
Syntax: create assertion NAME {immediate} {on ASSERT_CONDITION}
is SEARCH_CONDITION
Function: Provides for data integrity rule enforcement during delete,
insert, and update operations.
Arguments:
NAME
is the name given to the assertion. This name will be displayed
along with the error message when a SQL statement is executed which
would violate the assertion.
immediate
specifies that the assertion is always enforced at the completion of
each SQL statement.
ASSERT_CONDITION
is a character string that specifies the type of assertion which
will be made. If the construct "on TABLE_NAME {variable name}" is
used it denotes that an assertion is to be made on a particular
table, and is enforced for each row of the table. If
ASSERT_CONDITION is ommitted it denotes an assertion that makes an
overall statement about one or more tables, rather than about
individual rows of a table. The on phrase isn't required because
the body of the assertion specifies which tables the assertion
applies to. If the contruct "on OPERATION of OBJECT" is used it
denotes an assertion that deals with transitions in a table.
"OPERATION" specifies when the assertion is to be enforced; on
insertion, deletion, or update. OBJECT is a table specified as
"table_name", or a column specified by as "table_name(column_name)".
The transition is described in terms of "old" and "new" values which
represent the row value before and after the transition. If a
single SQL statement updates many rows, the assertion is checked for
each row, and the entire statement is rejected if any row violates
the assertion.
SEARCH_CONDITION
is the body of the assertion that specifies the integrity
constraints. The examples below illustrate some of the forms
SEARCH_CONDITION can take; a complete specification will be provided
some time in the future.
Notes:
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
When the assert statement is used view_master checks the current value
of the assertion. If it is currently true the assertion is accepted
and all delete, insert, and update statements issued in the future will
fail if the assertion is violated by them; if it is currently false the
assert statement is rejected. If the argument "immediate" isn't used
and it isn't a transition assertion, the assertion will only be checked
at the end of a logical unit of work. This allows several SQL
statements to be issued within a logical unit of work, and have the
assertion checked when the necessary updates have been made to satisfy
the assertion. The entire logical unit of work is rolled back if the
assertion is false when the attempt is made to commit the work. You
must have alter permission on all referenced tables in order to use the
create assertion statement.
Examples:
create assertion maximum_salary on employees is salary < 50000
create assertion salary_rule on employees is if project = "Multics"
then salary between 10000 and 15000
create assertion head_count_rule on department e
is number_of_employees = (select count (*) from employees
where department_number = e.department_number)
create assertion employees_must_have_a_department is
(select department_number from employee)
is in
(select department_number from department)
create assertion salary_must_increase on update of employee (salary)
is new salary > old_salary
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 create index
Syntax: create {unique} index INDEX_NAME on TABLE_NAME
(COLUMN_NAME_1 {asc | dsc}
... {,COLUMN_NAME_N {asc | dsc}})
Function: Creates an index on a table composed of one or more columns.
Arguments:
unique
specifies that any future insert or update statement will not allow
modifications which would result in a duplicate index value. When
this argument is used and data already exits in the table which
results in a duplicate index value, an error message is printed and
the index isn't created.
INDEX_NAME
is the name of the index which will be created.
TABLE_NAME
is the name of the table on which the index will be created.
COLUMN_NAME
is the name of the column which will be used to create the index.
Several columns can be specified but the combined length of all
columns must not be more than some number of characters to be
determined in the future.
asc
specifies that the index should be created in ascending order.
(DEFAULT).
dsc
specifies that the index should be created in descending order.
Notes:
You must have index permission on the table for which the index is to
be created to use this statement.
Examples:
create unique index employee_number on employee (emp_no)
create index date on employee (year month day dsc)
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 create synonym
Syntax: create synonym NAME1 for NAME2
Function: Creates a synonym on a table or view. Later references to
the synonym are the same as if the table or view was referenced.
Arguments:
NAME1
is the name of the synonym which will be created. This name must
not be the same as any existing table, synonym or view name.
NAME2
is the name of the view or table for which the synonym will be
created.
Notes:
You must have create_synonym permission on the data dictionary in order
to use this statement.
Examples:
create synonym employee for emp
create synonym Multics_salaries for salaries_by_project
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 create table
Syntax: create table TABLE_NAME (COLUMN_NAME_1 TYPE_1 {not null}
... {,COLUMN_NAME_N TYPE_N {not null}})
{in tablespace TABLESPACE_NAME}
Function: Creates a table made up of one or more columns.
Arguments:
TABLE_NAME
is the name of the table which will be created. This name must not
be the same as any existing table, tablespace, synonym, or view
names.
COLUMN_NAME
is the name of the column which will be created in the table.
TYPE
is the data type of the column which will be created. The supported
data types are bit, bit varying, character, character varying, fixed
decimal, float decimal, fixed binary, and float binary. These data
types are described using standard PL/1 syntax, with standard PL1
defaults. The maximum length of the various data types is to be
determined.
not null
specifies that the column may not contain null values. If not
specified null values are allowed.
in tablespace TABLESPACE_NAME
specifies that the created table is to be placed in the tablespace
identified by TABLESPACE_NAME. If this argument isn't provided the
table will be placed in the default tablespace.
Notes:
You must have create_table permission on the tablespace in order to use
this statement. The select, insert, delete, update, index, link, and
alter permissions are automatically set for the creator of the table.
Examples:
create table employee (number fixed dec (5) not null,
name char (32) varying not null,
job char (2), hire_date fixed bin (72) unsigned aligned,
salary fixed dec (7,2))
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
create table file_names (name char (32)) in tablespace file_system
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 create tablespace
Syntax: create tablespace NAME in DIRECTORY_PATH {with N records}
Function: Creates a tablespace in a specified directory, optionally
moving quota from the directory to the created table space.
Arguments:
NAME
is the name that will be assigned to the created tablespace. This
name must be unique among all tablespaces described in the data
dictionary.
DIRECTORY_PATH
is the relative or absolute pathname of the directory under which
the tablespace will be created. If DIRECTORY_PATH contains less
than or greater than characters it must be enclosed in quotes.
with N records
specifies that N records of quota should be moved from the
containing directory to the newly created tablespace, where N is a
positive integer.
Notes:
You must have create_tablespace permission on the data dictionary in
order to use this statement.
Examples:
input_sql
SQL Statement:
create tablespace employee_tables in ">udd>Multics>Dupuis"
.
create tablespace department_tables in Dupuis with 50 records
create tablespace salary_tables in """>udd>Multics>Dupuis"""
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 create trigger
Syntax: create trigger NAME on CONDITION is (STATEMENT_LIST)
Function: Provides for the automatic execution of SQL statements upon
occurence of a specified action.
Arguments:
NAME
is the name given to the trigger. This name must not be the same as
any existing trigger names.
CONDITION
specifies the condition that will cause the execution of the
trigger. The action specified in the condition can be chosen from
"insertion", "deletion", "selection", and "update". A table name is
also specified, and can be followed by an optional variable name.
In the case of "update", it can also be followed by a parenthesis
enclosed list of columns.
STATEMENT_LIST
is a parenthesis enclosed list of SQL statements, with each SQL
statement separated by a semi-colon. This list of statements can be
preceded by an if statement, and followed by a then statement (see
examples below).
Notes:
You must have the necessary permissions on the tables referenced in
STATEMENT_LIST to perform the requested actions, and have alter
permission on the referenced tables in order to use this statement.
Examples:
create trigger change_dept_count on update of employee (dept_number)
(update department set
number_of_employees = number_of_employees + 1
where dept_number = new employee.dept_number;
update department set
number_of_employees = number_of_employees - 1
where dept_number = old employee.dept_number)
create trigger delete_dept_if_necessary on delete of employee x:
(if (select count (*) from employee
where dept_number = d.dept_number) = 0
then delete dept where dept_number = x.dept_number)
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 create view
Syntax: create view VIEW_NAME {(COLUMN_NAME_LIST)}
as SELECT_STATEMENT {with check option}
Function: Creates a view of a table.
Arguments:
VIEW_NAME
is the name of the created view. This view name must not be the
same as any existing table, view, or synonym names.
COLUMN_NAME_LIST
is a list of names separated by commas for each column in the view.
If this argument isn't used the column names in the view will be the
same as the column names of the underlying tables.
SELECT_STATEMENT
is the select statement that defines the view. The select statement
may reference other views. If "select *" is used and new columns
are added to the underlying table some time in the future, the new
columns are not seen by the view.
with check option
specifies that the associated where clause is checked when an insert
or update statement is issued against the view. If the new data
causes the where clause to fail the statement is rejected.
Notes:
You must have create_view permission on the data dictionary and at
least select permission on the underlying tables/views in order to use
this statement.
Examples:
create view Multics_salary_info as select * from salary_info
where project = """Multics"""
create view names_and_salaries as select name salary
from Multics_salary_info
create view average_salary (salary) as select avg (salary)
from employee information
create_view new_salary as select salary from emp
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
where salary < 50000 with check option
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 delete
Syntax: delete from TABLE_NAME {where SEARCH_CONDITION}
Function: Deletes one or more rows from a table.
Arguments:
TABLE_NAME
is the name of the table from which the rows will be deleted.
SEARCH_CONDITION
is an optional search condition that must be satisfied in order for
a row to be deleted. The syntax of a search condition is described
in the select documentation. If this argument isn't provided every
row in the named table is deleted.
Notes:
You must have delete permission on the table in order to use the delete
statement.
Examples:
delete from employee
delete from employee where project = """Multics"""
delete from employee where salary > 50000
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 deregister link
Syntax: deregister link NAME
Function: Removes the registration of a link from the data dictionary.
Arguments:
NAME
is the name of the link that is defined in the data dictionary.
Notes:
You must have deregister_link permission in order to use this
statement.
Examples:
deregister link employee
deregister link emp
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 deregister table
Syntax: deregister table NAME
Function: Removes the registration of a non-native table from the data
dictionary.
Arguments:
NAME
is the name of the non-native table that is defined in the data
dictionary.
Notes:
You must have deregister_table permission in order to use this
statement.
Examples:
deregister table salary_info
deregister table empployee_info
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 drop assertion
Syntax: drop assertion NAME
Function: Removes the named assertion.
Arguments:
NAME
is the name given to the assertion. This must identify an assertion
which currently exists.
Notes:
You must have alter permission on all referenced tables in order to use
the drop assertion statement.
Examples:
drop assertion maximum_salary
drop assertion salary_rule
drop assertion head_count_rule
drop assertion employees_must_have_a_department
drop assertion salary_must_increase
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 drop index
Syntax: drop index INDEX_NAME
Function: Removes the specified index.
Arguments:
INDEX_NAME
is the name of the index which will be dropped.
Notes:
You must have index permission on the table for which the index will be
dropped to use the drop index statement. If a drop index statement
attempts to drop an index that is in use by another user, the statement
is rejected after the wait time has been exceeded.
Examples:
drop index employee_number
drop index date
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 drop synonym
Syntax: drop synonym SYNONYM_NAME
Function: Drops the named synonym and makes it unavailable for use.
The table or view on which the synonym is defined is unaffected.
Arguments:
SYNONYM_NAME
is the name of the synonym which will be dropped.
Notes:
You must have drop_synonym permission in order to use this statement.
The affect on views which reference the synonym is to be determined at
some future date.
Examples:
drop synonym employee_number
drop synonym date
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 drop table
Syntax: drop table TABLE_NAME
Function: Drops the named table and makes it unavailable for use. All
indexes, views, privileges, and synonyms defined on the table are also
dropped.
Arguments:
TABLE_NAME
is the name of the table which will be dropped.
Notes:
If a drop table statement attempts to drop a table that is in use by
another user, the statement is rejected after the wait time has been
exceeded. You must have drop_table permission in order to use this
statement.
Examples:
drop table employee_data
drop table division
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 drop tablespace
Syntax: drop tablespace NAME
Function: Drops the named tablespace and makes it unavailable for use.
Arguments:
NAME
is the name of the tablespace which will be dropped.
Notes:
All tables and indexes defined in the tablespace are dropped and are
unavailable for future use. All views, synonyms, and privileges
defined on the dropped tables are also dropped. If a drop tablespace
statement attempts to drop a table, view, or synonym that is in use by
another user, the statement is rejected after the wait time has been
exceeded. You must have drop tablespace permission in order to use
this statement.
Examples:
drop tablespace employee_tables
drop tablespace department_tables
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 drop trigger
Syntax: drop trigger TRIGGER_NAME
Function: Drops the named trigger.
Arguments:
TRIGGER_NAME
is the name of the trigger which will be dropped.
Notes:
You must have alter permission on all tables referenced by the trigger
in order to use this statement.
Examples:
drop trigger salary_update
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 drop view
Syntax: drop view VIEW_NAME
Function: Drops the named view and makes it unavailable for use. All
views and synonyms which reference the removed view are also dropped.
Arguments:
VIEW_NAME
is the name of the view which will be dropped.
Notes:
If a drop view statement attempts to drop a view that is in use by
another user, the statement is rejected after the wait time has been
exceeded. You must have drop_view permission in order to use this
statement.
Examples:
drop view employee_data
drop view division
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 grant
Syntax: grant PRIVILEGE_LIST on OBJECT_NAME to USER_LIST
{with grant option}
or
grant PRIVILEGE_LIST to USER_LIST {with grant option}
Function: For the first syntax grants privileges on a table,
tablespace, or view. For the second syntax grants privileges on a data
dictionary.
Arguments:
PRIVILEGE_LIST
is one or more privileges separated by commas. For tables they can
be chosen from: select; insert; delete; update; index; alter; link;
and null. The update privilege can be optionally followed by a
comma separated column list, which is enclosed in parenthesis. The
column list denotes which columns the update privilege applies to.
For tablespaces they can be chosen from: alter_tablespace;
create_table; drop_table; and null. For views they can be chosen
from: select; insert; delete; update; link; and null. Update may
also be followed by a parenthesis enclosed, comma separated column
list. For data dictionary permissions they can be chosen from:
access; administrator; create_synonym; create_tablespace;
create_view; deregister_link; deregister_table; drop_synonym;
drop_tablespace; drop_view; null; register_link; and register_table.
The keyword "all" can also be used and specifies all applicable
permissions with the exception of null.
OBJECT_NAME
is the name of the table, tablespace, or view that the privileges
will be granted on.
USER_LIST
is a comma separated list of users that the privileges will be
granted to.
with grant option
specifies that the named users can also grant these permissions to
others.
Notes:
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
You must have the grant option on the data dictionary, table,
tablespace, or view and cannot grant more permissions than you yourself
have.
Examples:
grant select,insert on employees to Dupuis
grant select,insert on employees to Dupuis.Multics
grant all on employees to *.*.*
grant update(salary,age),index on employee to *.Multics
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 insert
Syntax: insert into RECEIVER {(COLUMN_NAMES)} values (DATA_VALUES)
or
Syntax: insert into RECEIVER {(COLUMN_NAMES)} SELECT_STATEMENT
Function: adds data to a table. The first syntax adds a single row to
a table, and the second syntax copies one or more rows into a table.
Arguments:
RECEIVER
is the name of a table or view into which the data will be inserted.
COLUMN_NAMES
are one or more column names separated by commas that identify the
columns which will receive the data. All columns of the table or
view that are not listed have a null value placed in them. If this
argument isn't used it is the same as if every column was named in
the order in which it is defined in the view or table.
DATA_VALUES
are one or more values separated by commas to be inserted as the new
row. If the data value is to be inserted into a character or bit
string column it must be enclosed in quotes. A null value is
inserted into a column by typing the word "null".
SELECT_STATEMENT
is a select statement that specifies the data to be inserted. Refer
to the documentation of the select statement for more information
about the syntax. If this argument is used the number of columns
selected must be the same as the number of columns to be inserted.
Notes:
You must have insert permission for the table or view to use the insert
statement. You must have select permission on all referenced views or
tables for the second type of insert where a select statement is
specified.
Examples:
insert into employee values ("""smith""", null, 25, 15234)
input_sql
SQL Statement:
insert into employee (name, age, salary) values ("smith", 25, 15243)
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
.
insert into department select number name department from employees
insert into senior_employees select * from employees where salary
> 40000 and years_of_service > 20
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 lock table
Syntax: lock table TABLE_NAME in STATE mode
Function: Locks the named table in the specified mode.
Arguments:
TABLE_NAME
is the name of the table which will be locked.
STATE
is the character string "exclusive" or "share". Locking the table
in exclusive mode prevents other users from reading or modifying any
data in the named table. Locking the table in shared mode allows
other users to read data in the named table, but prevents them from
modifying data.
Notes:
This statement is provided for cases when you know it will be necessary
to access the entire table and saves the expense of acquiring many
small locks. The lock remains locked until the logical unit of work
ends.
Examples:
lock table employee_data in exclusive mode
lock table division in share mode
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 register link
Syntax: register link NAME1 {as NAME2} from DD_PATH
Function: Registers an entity from another data dictionary in the
current data dictionary, as a link. After the registration process,
references to the link are the same as references to the target of the
link for many SQL statements.
Arguments:
NAME1
is the name of a table, view, link, or synonym as defined in the
data dictionary specified by DD_PATH.
NAME2
is an alternate name under which the link will be registered. This
argument must be used if NAME1 isn't unique among the tables,
synonyms, links, or views already defined.
DD_PATH
is the relative or absolute pathname of the data dictionary. If
this name contains greater than or less than characters it must be
enclosed in quotes.
Notes:
You must have register link permission in order to use this statement.
Examples:
register link employee from Kubicar
input_sql
SQL Statement:
register link employee as emp from ">udd>Multics>Dupuis>Dupuis"
.
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 register table
Syntax: register table "PATHNAME" {as TABLE_NAME}
{(COLUMN_NAME_1 TYPE_1 {not null}
... {,COLUMN_NAME_N TYPE_N {not null}})}
{using PROCEDURE_NAME}
{special "STR"}
Function: Registers a non view_master table. After the registration
process many of the view_master SQL statements can be used to
manipulate the table.
Arguments:
PATHNAME
is the relative or absolute pathname, including any suffix, of the
table to be registered. If pathname contains any less than or
greater than characters, it must be enclosed in quotes.
TABLE_NAME
is an alternate name under which the table will be registered. This
argument must be used if the entryname portion of PATHNAME isn't
unique among the tables, synonyms, links, or views already defined.
COLUMN_NAME
is the name of the column which will be registerd along with the
table information. This argument is optional if the procedure named
by PROCEDURE_NAME implements the capability of providing the column
names and data types in the specified table.
TYPE
is the data type of the column which will be registered. The
supported data types are bit, bit varying, character, character
varying, fixed decimal, float decimal, fixed binary, and float
binary. These data types are described using standard PL/1 syntax.
This argument is optional if the procedure named by PROCEDURE_NAME
implements the capability of providing the column names and data
types in the specified table.
not null
specifies that the column may not contain null values. If not
specified null values are allowed.
PROCEDURE_NAME
specifies the procedure that will be used to perform operations on
the registered table. This procedure will be found using the search
rules at the time an operation is performed on the table. This
argument is optional if the procedure name can be determined by the
suffix.
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
STR
is any special character string needed by the procedure to perform
table operations. This character string must be enclosed in quotes.
Notes:
You must have register table permission in order to use this statement.
Examples:
register table employee (number fixed dec (5) not null,
name char (32) varying not null, job char (2),
hire_date fixed bin (72) unsigned aligned,
salary fixed dec (7,2)) using private_table_manager
input_sql
SQL Statement:
register table employee as emp using load_table
special "-column_delimiter ~ -row_delimiter :"
.
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 restore
Syntax: restore {NAME}
Function: Backs out all changes made to the tables since the named
save point.
Arguments:
NAME
is the name that was given when the save statement was issued. If
this argument is ommited the last checkpoint is assumed, or the
beginning of the transaction if there hasn't been a checkpoint
within the transaction.
Notes:
After the execution of this statement all changes made to the tables
since the named save statement was issued are removed and the tables
are in the same state as when the save statement completed execution.
Examples:
restore checkpoint1
restore checkpoint2
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 revoke
Syntax: revoke PRIVILEGE_LIST on OBJECT_NAME from USER_LIST
or
revoke PRIVILEGE_LIST from USER_LIST
Function: For the first syntax revokes privileges granted on a table,
tablespace or view. For the second syntax revokes privileges granted
on a data dictionary.
Arguments:
PRIVILEGE_LIST
is one or more privileges separated by commas. See the description
of the grant statement for a complete list of privileges.
OBJECT_NAME
is the name of the table, tablespace or view from which the
privileges will be revoked.
USER_LIST
is a comma separated list of users that the privileges will be
revoked from.
Notes:
You can only revoke privileges that you have previously granted to
someone. Any privilege revoked from a user is also revoked from anyone
to whom that user may have granted it.
Examples:
revoke select,insert on employees from Dupuis
revoke select,insert on employees from Dupuis.Multics
revoke all on employees from *.*.*
revoke update,index on employee from *.Multics
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 rollback work
Syntax: rollback work
Function: End the current logical unit of work, restoring all tables
back to their state before the logical unit of work began.
Examples:
rollback work
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 save
Syntax: save NAME
Function: Saves all changes made to the tables since the last save
point or the beginning of the transaction.
Arguments:
NAME
is the name which will be assigned to the work done since the last
save point, or to the beginning of the transaction if no save has
previously been done within the transaction.
Notes:
After the execution of this statement, the restore statement can be
used to rollback all changes made to the tables from this save point up
until the restore statement is issued. The rollback work statement can
be used to rollback all changes made since the beginning of the
transaction.
Examples:
save checkpoint1
save checkpoint2
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 select
Syntax: select {all | distinct} SELECT_LIST from OBJECT_LIST
{where SEARCH_CONDITION}
{group by COLUMN_NAME {having GROUP_CONDITION}}
{order by COLUMN_SPEC {asc | dsc}
... {,COLUMN_SPEC {asc | dsc}}
Function: retrieves the selected data from tables.
Arguments:
all
specifies that duplicate values are not to be eliminated (DEFAULT).
This argument is incompatible with distinct.
distinct
specifies that duplicate values are to be eliminated. This argument
is incompatible with all.
SELECT_LIST
Is a list of one or more items separated by commas, or "*" which
means all columns. An item may be a column name, a constant, a
builtin function, or a combination of column names, constants, and
builtins connected by arithmetic operators.
from OBJECT_LIST
is a list of one or more table or view names.
where SEARCH_CONDITION
SEARCH_CONDITION is one or more conditions to apply in selecting
data. If no search conditions are specified all of the rows will be
selected.
group by COLUMN_NAME
COLUMN_NAME is the name of a column that will be used to group
multiple rows.
having GROUP_CONDITION
is one or more conditions to apply to the groups and only groups
that satisfy the condition are selected.
order by COLUMN_SPEC {asc | dsc} ... {,COLUMN_SPEC {asc | dsc}}
COLUMN_SPEC is the name or number of one of the selected columns and
is used to order the results. If a number is used it is given as
the number of the column from the select list. asc specifies
ascending order (DEFAULT), and dsc specifies descending order.
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
Notes:
You must have select permission on the referenced tables and views to
use the select statement.
Examples:
select * from employee
select name, salary, city, state from employee
select salary + commission from employee
select * from employee where salary > 50000
select employee.name, history.years_of_service from employee history
where employee.employee_number = history.employee_number
select state, city, salary from employee order by state dsc
select department, min (salary), max (salary), avg (salary)
from employee group by department order by department
select department, min (salary), max (salary), avg (salary)
from employee group by department having count (*) > 10
order by department
MTB 643-00 Multics Technical Bulletin
Attachment 4
SQL Statements
12/20/83 update
Syntax: update TABLE_NAME set COLUMN_NAME_1 = EXPRESSION_1
{... ,COLUMN_NAME_N = EXPRESSION_N}
{where SEARCH_CONDITION}
Function: updates the values of one or more columns in one or more
rows of a table.
Arguments:
TABLE_NAME
is the name of the table which will be updated.
COLUMN_NAME
is the name of a column which will be updated.
EXPRESSION
is the new value to be placed in the column. The expression may
contain constants, "null", column names, and the arithmetic
operators.
SEARCH_CONDITION
specifies the rows to be updated. All rows that meet the search
condition are updated. If this argument isn't used all rows of the
table are updated.
Notes:
You must have update permission on all specified columns in order to
use this statement.
Examples:
update employee set job = null, age = 25, salary = 15234
update employee set salary = salary + 100
where years_of_service > 20