| RETRIEVE | |
| Paradigm: | Imperative, Declarative |
| Family: | dBASE |
| Designer: | Arden Scott |
| Developer: | Richard Moore |
| Programming Language: | FORTRAN |
| Platform: | SDS 940 |
| License: | Proprietary |
| File Formats: | --> |
| Influenced: | dBASE, JPLDIS, RECALL, many others |
RETRIEVE is a database management system (DBMS) offered on Tymshare's systems starting in August 1971. It was written in Tymshare's own SUPER FORTRAN on the SDS 940. It offered basic single-file, non-relational database functionality using an interactive programming language. It is one of the earliest examples of software as a service (SaaS).
RETRIEVE was highly influential and spawned a number of relatively direct clones. Wang Laboratories's RECALL on the Wang 2200 minicomputer was almost identical to RETRIEVE, to the point the differences were detailed in a single page. JPL made a version known as JPLDIS for the UNIVAC 1108 in 1973 that was also very similar.
Wayne Ratliff, a contractor at JPL for many years, was inspired by JPLDIS to port it to the IMSAI 8080 to manage his football pool, later releasing it commercially as Vulcan for CP/M in 1979. Ashton-Tate licensed Vulcan and re-released it as dBASE II in 1980, which sparked the microcomputer database market. Most of RETRIEVE's original syntax remains unchanged in dBASE and the many xBASE clones that survive into the 21st century.
In 1969, Jim Ryan of Tymshare designed an expanded version of IBM System/360 H-level (258 kB) FORTRAN, adding strings and other features that took advantage of their SDS 940 systems. Ryan hired Richard Moore and Franck Bracher to develop it as SUPER FORTRAN, and it went live on their systems in 1970. Soon after SUPER FORTRAN was released, Arden Scott asked Moore to use SUPER FORTRAN to develop his vision of a database management system (DBMS). The first version was ready in a few weeks, and immediately proved very popular with Tymshare customers.
During the 1970s, Tymshare began moving their systems from SDS to the PDP-10 platform, eventually running TOPS-10. This led to an effort to build an entirely new database engine for this platform, known as MAGNUM. MAGNUM was a complete relational database engine, and in many references it is claimed to be the first such system offered commercially when it went live in October 1975. Although most Tymshare customers, and internal users, switched to MAGNUM, by this time RETRIEVE had been ported to a number of platforms and these versions remained very popular outside the company.
In 1970, the Jet Propulsion Laboratory (JPL) installed three UNIVAC 1108 machines. Fred Thompson had been using RETRIEVE to manage a database of mechanical calculators at JPL, and decided to bring the system in-house when the 1108's arrived. In 1971, he collaborated with JPL programmer Jack Hatfield to produce JPLDIS. Hatfield left JPL in 1974, and the project was assigned to another JPL programmer, Jeb Long, who added a number of features.[1]
In 1973 the Wang 2200 was released, a desktop minicomputer with cassette tape storage.[2] RETRIEVE was ported to this platform under the name RECALL. A report for the US Army detailed the differences in a single page and concluded "Differences between the two implementations are very minor."
While working at JPL as a contractor, Wayne Ratliff entered the office football pool. He had no interest in the game, but felt he could win the pool by processing the post-game statistics found in newspapers. In order to do this, he started looking for a database system and, by chance, came across the documentation for JPLDIS. He used this as the basis for a port to PTDOS on his kit-built IMSAI 8080 microcomputer, and called the resulting system Vulcan (after Mr. Spock on Star Trek). It was later ported to CP/M when that system became almost universal in the S-100 bus market.
In 1980, George Tate saw an advertisement for Vulcan being sold for $49. He arranged a licensing deal with Ratliff, renamed it to dBASE II to make it sound like the second version, and put it on the market for $695. The system was a success, and in 1981, IBM commissioned a port to the still-unreleased PC DOS. This was a runaway success, one of the big three packages, along with Word Perfect and Lotus 1-2-3 that made up the equivalent of an office suite in the early DOS market.[3]
RETRIEVE was a non-relational database, that concept not being introduced until 1970. RETRIEVE databases contained a single table stored in a single file, which typically used a short-form of the database name as the filename. The table structure is defined when the database is created, allowing fields of character, integer or free-form numeric inputs. A database could have a maximum of 98 fields, with a maximum of 72 characters per field, and the total for any one row was 768 characters, or 256 words. Field names had to begin with a letter and could include additional letters, digits, a period and the @ character, with a maximum of 31 characters in total.
The system was interactive, using a command prompt within the system for user interaction. For instance, to start a new database the user would type at the command prompt, which would then respond by asking the user to type in the name for the database and then prompt for the field definitions. An empty line stops this process and sends it into data-entry mode, allowing rows to be entered. Any step of this operation could be removed by providing the data in the original command, for instance, if one typed instead of, the system no longer asked for the file name.
There were three database file formats, which could be specified during, the normal character-format, which saved numbers in their 24-bit-based internal formats, and which encrypted the file with a user-supplied password.
Existing databases were loaded using or (they were equivalent). Data was saved as it was changed, but there was also a command to write out data (or selections of it) to flat files. exited the system and returned to the underlying operating system.
Once the database was defined and populated, or an existing one was loaded, data could be displayed using . Without any parameters, it printed out all the records in the order they were entered, their "RECNO", which was printed at the front of the line. One could also provide a field list to select and reorder the fields in the printout, like . The statement worked almost identically, differing only in that it did not print out the RECNO at the start of the lines. was similar to, but suppressed the field headers as well.
Individual records could be selected using the "record number addressing system", which was prepended to or . This was a flexible format allowing both single RECNOs separated by commas or ranges with colons to be specified, for instance, would print out the records 1, everything from 7 to 20, and then 50. It also included the pseudo-record to represent the last record, so printed out all records from 100 on.
Records could also be selected by fields meeting certain criteria based on their contents, using the syntax. To list all the employees with a salary greater than $40,000, one would . The same syntax could be used to select single records based on their contents, not the RECNO, for instance . RETRIEVE supported all basic comparisons,,,,, and for not-equals, which was found in some contemporary BASICs. Unusually, RETRIEVE also included English expansions of traditional comparisons, so one could use either or . Such expressions could also include basic math, including,, for multiplication, for division, and for exponentiation. These could be further combined with boolean expressions using, and .
Additionally, the, and worked similar to or, including the same record selection concepts. As the name implies, these output a single value with their associated values. For instance, would likely return 1, while might return 42500.
In addition to the //, a further output statement is . This works in a similar fashion, but has a number of options to pretty-print the output. It can be invoked alone or with qualifiers as above, but when it is used it enters an interactive mode that asks a number of questions about where to send the output (with T being the terminal), whether it should be single or double spaced, include headers and totals, etc.
Records could be deleted using the statement, using the same record selectors or field expressions as above. New records were inserted using . entered an interactive mode allowing the user to type in additional records field-by-field rather than entering comma-separated values a row at a time. read data from a comma-delimited text file into the current database already in memory. was used to update existing records; it worked similar to, loading into the current database from another file, but in this case included an additional qualifier . For instance, would read data from the file ADDRESSES and look in that file for a column where the first entry was "NAME". It would then process the file row-by-row, looking for entries in the database with that NAME and then updating the rest of the fields with the data from that row in ADDRESS.
RETRIEVE supported two interactive methods to update existing records, and . worked similarly to the modern SQL equivalent,, taking a selector expression of some sort, one or more fields, and the new values. For instance, one might . While ultimately did the same thing, it did so using an interactive mode. One invoked without the new value, for instance, . The system printed out that row and then allowed the user to edit the record. If there is more than one unique value, for instance,, each value was printed on a separate line and changes were sent to all matching records. was essentially identical to but did not print out the existing values first.
Sorting was not performed at retrieval time, but was implemented by modifying the database and re-writing its contents in sorted order. This was accomplished with, followed by up to twenty field names. The original unsorted data is written to a backup file.
The modifier could be used with any of the data modification statements to redirect the results to a new database. For instance, would append the data in the file FEBSALES to the current database and then save the results to CURSALES without updating the already-opened database. Or one might .
Utility commands included which printed out the database schema, and which returned the number of records.
Although RETRIEVE was often used interactively, the system also included the ability to save lists of commands to files and then play them back. Command files could also include a number of other "helper" statements, including to output any string, to suppress the command prompt (a period), to turn the prompt back on, and and to stop the playback from appearing on the terminal.
These command files were run using the statement from the internal command line or from outside RETRIEVE, in EXECUTIVE. If the script intended to leave the user in RETRIEVE at the end, one could put a at the end, "running" the Terminal, which specified what should happen next. Scripts could be strung together with to form more complex workflows.
When run, the commands inside the files operated just as they would if the user typed them in. This means that if a statement is provided that would normally require additional user input, for instance, a with no parameters, the interactive mode would be invoked as normal. This allowed the command files to invoke user-based input and then perform additional instructions. One could, for instance, use to catch any recent changes in salary, call to have the system present each employee record and ask for their weekly hours, then to calculate the weekly paycheck for all users, and finally it send it all to a printer.
Although separated by almost a decade, and having moved across four platforms in the process, dBASE on DOS remained very similar to RETRIEVE. Ratliff stated there was a "sort of progression from Retrieve to JPLDIS to my program, which I called Vulcan." John Walker, better known for AutoCAD, also used JPLDIS and stated flatly that "DBase II, the Ashton-Tate database system, was a copy, a reimplementation of a package developed at the Jet Propulsion Laboratory called JPLDIS."[4]
/ became and periods in field names were replaced by colons, but most other commands and features remained unchanged other than to support differences in the underlying platforms, like numeric formats. For instance, the original dBASE User Manual uses this example:
use people list
Which is identical to the instructions in RETRIEVE:
LOAD people LIST
The overall operation of the statements is largely identical between the two systems. dBASE's primary differences are related to the programmability; dBASE added variables, could columns made of formulas like, and added a much wider variety of functions to manipulate data, including functions for returning the length of a string or the data type of a given field.