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