TRETBASE

Author: Paul Tretter
Version: 1.0
Date: 1 July 2008

Contents:

1. What is TRETBASE?

TRETBASE is a disk-based DATABASE MANAGEMENT SYSTEM (RDBMS). It is written in REBOL (www.REBOL.com) and caters to the REBOL developer.

TRETBASE is comprised of the database object called 'db for all database manipulation and queries. TRETBASE contains a META Table called MASTER. This Master database (table) contains:

• The Database names
• Column fields and their respective datatype
• Number of records in each database
• Creation Date and Modification dates of each database
• Termination used for row data (records) of each database file.

Each individual database created in the system is comprised of three files with the following file extensions:

• .db - Primary records
• -deletions.db - Deleted records
• -changes.db - Changed records

So each database will have these three files located in the directory location specified by the db/db-path variable. These records will be created regardless of whether or not there has been any records added, changed, or deleted whenever a database is created in the system.

Note - In TRETBASE the term "database" implies the same meaning as the term "table" as used in traditional Database Management Systems. Therefore, all tables in TRETBASE are databases.

2. How to Setup TRETBASE?

You can call TRETBASE from your scripts by simply calling the TRETBASE script in the common REBOL method using the DO function:

DO %tretbase1.r

That's it. Your now ready to add databases and records.

3. Operation

3.1 Adding and initializing Databases

Once you have TRETBASE setup, now it's time to create your first database. Create a database via the db/create function. We are going to use the name "my-base" for our database. To create our database we issue the command:

db/create my-base ["First Name" string! "Last Name" string! "Age" integer!]

We have passed the dbase argument called my-base and the data argument to the db/create function. But at this point we have created a database container in the MASTER database and created the fields (column assignments) for our database called 'my-base and assigned their respective datatypes.

Note: The Field names must be specified as either a string! or word! value. This means instead of putting in the word string "Age" we could have simply put Age.

Notice the format of the block. It must follow that format where the field name is followed by its respective datatype. Now let's verify that TRETBASE sees our new database. We do this with the db/list function:

db/list

You should now see the "my-base" database presented in the return block. Let's check how many records are found in my-base:

db/records? my-base

We haven't got any records yet because we haven't added any.

3.2 Adding Records to Databases

At this point we have created our database. We are now ready to add records to our database. Let's add a record to our database (all names chosen for these exercises represent fictional characters):

db/add my-base ["Harry" "Smith" 47]

Now, let's look at the record we just added to the database called "my-base". The order of the items in the block correspond to our database columns. So the first item "Harry" corresponds to the field name "First Name" that we created with the db/create command. Additionally, the second item "Smith" and the integer 47 correspond respectively in the same order as the field assignments. Notice that the word "Harry" is specified as a string! just as we assigned it as well as "Smith" and then the AGE field is assigned an integer! datatype just as specified in the db/create function. If we need to recall the field assignments we just issue the command:

db/header? my-base

Now let's confirm it actually contains records. We do that with the following function:

db/records? my-base

We should only see one record returned by the function. Now let's add more records to our database. We can continue to add records one by one as we did previously with the db/add command. Also, we could put the db/add function in a loop block and add a record several times as follows:

loop 99 [db/add my-base ["Harry" "Smith" 47]]

We would now have just added 99 identical records to my-base and have a total of 100 records. Of course our database should reflect distinct data so let's add records in a batch mode. To add several records we just take each record (block) and insert them into a container block. This will allow us to add several records in a single function call as follows:

db/add my-base [["Jason" "Humphries" 22] ["Jill" "Ramsey" 34] ["Bill" "Tate" 39] ["Mary" "Jones" 23]]

Notice that each block within the large block is an individual record conforming to the db/create data argument that we provided earlier. So in that batch function we added four more records to the my-base database. We can again confirm this with the db/records? function. Another option for adding records is to import them from a file by just specifing a file as the data argument. This import method requires that you have a file where each line is a record. To use the function in this manner perform the following:

db/add my-base %some-records.txt

Again, to use that function in the above example would require that you had a record on each line of the %some-records.txt file.

Images can also be added to the database using the image! datatype. The Header of the database is the area that stores the column field assignments and their respective datatypes. TRETBASE will scan this for the presence of the image! datatype and automatically convert image files on the fly. Images files are stored in a compressed encoded base 64 format. When retrieved the record is decompressed and decoded. Let's assume the header of the my-base database is simply defined as this ["Image name" string! "image" image!]. Now to add an image file we simply reduce the block we are passing with the load-image command contained therein:

db/add my-base reduce ["myimage.jpg" load-image %myimage.jpg]

Notice the 'reduce' function is used. This causes the load-image function to import the image data into the block. If we use the db/retrieve function to recall that record it will contain the image data in decompressed and decoded format.

3.3 Searching and Retrieving Database Records

Now that we have records in our database, we want to be able to search the database and retrieve those records. The db/search function is a powerful function that allows us to search the database and perform other operations on the data. Let's perform a simple search of the my-base database that we created:

db/search my-base "Ji"

What will be returned is the record number containing the match for the search query. Now we can retrieve that record with the db/retrieve function:

db/retrieve my-base 102

Now we should be able to locate the matched query inside of the retrieved record. The search query can be of any datatype supported in REBOL but it must match those setup with the db/create function for the respective database. Also, the results returned will only match those if the data found matches the same datatype as our search query. So in our example, 'ji would have not returned any results since no such datatype exists in the header of the my-base database.

The db/search function of TRETBASE is a powerful feature. It can do far more than these simple searches. When a block is passed to the db/search function then this tells TRETBASE to perform a more complex search based on the data provided in the blocks. So a more advanced search method is as follows:

db/search my-base [[equal? r/2 "tate"][n]]

Notice we have a block as the argument containing two more blocks of the format [block! block!]. The first block contains the function we want to evaluate and the second block contains what we want to return. The second block is only returned if the LAST value of the first block evaluates to TRUE. Notice the 'r and 'n items in this query. Those words are variables populated by TRETBASE with specific data. In this case 'r is set to the value of the record of type block! and the 'n is set to the value of the record number of type integer!. So the first block is evaluating whether the second index position of the record (r/2) is equal to the string "tate". If so then the second block is evaluated with the record number (n) and returned. Here is the reserved words for function passing:

	i	Used for capturing the index (coloumn) position which will be of any-type! depending on the fields assignment.
	r	Used for capturing the record which will be of type block!.
	n	used for capturing the record number which will be of type integer!.

Note: Those variables are global and should not be defined in your script.

We can also do searches using the field name values that were assigned to the respective database. For example:

db/search my-base [["Age" > 20][n]]

That function would return all record numbers where the "Age" column contains a value greater than 20.

db/search my-base [[found? find "First Name" "Harry"][n]]

That function would return all record numbers where the "First Name" field equals "Harry". Note the use of the 'found? function in that query. This is necessary because TRETBASE only evaluate the second block if the first block returns a true value. Also, the true value must be the result of the reduced block as the LAST value of the block in order to evaluate the second block. This gives us the capability to perform other operations in the first block during evaluation.

TRETBASE also support case sensitive searches and even wildcard searching with the additional refinements of /match and /wild respectively.

3.4 Result Sets

When performing a search in TRETBASE it is best to capture your results in a block such as:

blk: db/search my-base "smith"

The 'blk will now contain the results of the search as series! data. In advanced operations of the db/search block function it is possible to populate an external block but doing so may bypass change and deletions checking of the respective records and provide inaccurate results.

3.5 Changing Database Records

To change a database record we must know its record number. Once the record number is located then we perform a change of the record. Let's change record number 104:

db/change my-base 104 ["Sally" "Forbes" 21]

Notice that our record argument is a block! datatype. It must adhere to the Field specification that was implemented using the db/create command previously.

3.6 Deleting Database Records

TRETBASE maintains a separate database file for deleted records. This gives us the advantage of rapid deletions that are offloaded for later processing by the reconcile function of TRETBASE. To delete a record we utilize the db/delete function as follows:

db/delete my-base 104

Notice that we passed the record number to the db/delete function. This appends the record number to the tail of the deletions file. To verify whether a record is deleted we use the db/deleted? function:

db/deleted? my-base 104

This will return TRUE or FALSE.

3.7 Adding and Dropping columns from a Database

To add and remove columns from a database we use the db/column function. This function takes three arguments depending on which respective task (add or drop) your performing. The format is:

db/column my-base drop "First Name"

db/column my-base add ["First Name" string!]

The first example is for dropping columns. Instead of using the field name itself you can alternately use the index position of the column. In the second example, we are adding a column to the database. When adding a column, an argument of a block! datatype must be used. The first parameter in the block will contain the field name of the column your adding. This field name can either be of word! or string! value. The next value in our block is the datatype designation of the new column.

When the functions are invoked they will recreate new database files. The old files will be renamed with a .clm extension in case this data must be later restored. Additionally, a header file with the extension of .hdr will be created for the respective database which will contain the previous field assignments of the respective column positions. You will need to manually delete the .clm and .hdr files when adding or dropping additional columns.

3.8 Reconciling the Database

Because of the manner in which TRETBASE is designed it is important to periodically invoke a reconcile of a database. A Reconcile operation will merge all changes and deletions into the main database file. To perform a reconcile you use the following function:

db/reconcile my-base

The function will create a temporary new database file while performing the operation. When it completes it will have renamed the old database files with a .bak extension in case they need to be restored. If it detects that a database has existing .bak files present it will not allow a reconcile in order to ensure needed data is not overwritten. Therefore, subsequent reconcile operations will require you to manually delete the .bak files for the respective database.

3.9 Releasing a Database

To delete a database we use the db/release function. The db/release function only removes the database information from the Master database. All the record data will remain on the storage media in case an accidental deletion took place. You use the function as follows, only passing the database to be released as its argument.

db/release my-base

4. Helper Functions

4.1 Flatten function

TRETBASE contains a helper function called db/flatten that may be useful depending on how your result sets are formatted after search routines. The db/flatten function is used to reduce the number of brackets that might accumulate around your returned values during processing. It is also useful for specific situations where you want to build a block of returned values to be later used in a manner where easy selection using REBOL's 'select function. Here is an example:

blk: db/flatten/semi db/search my-base [[equal? i "smith"][n r]]

Notice in the example that we are calling the db/flatten function to format the search result-set. The /semi refinement tells the db/flatten function to strip it in a manner that the block 'blk is formatted for easy select retrieval. The [n r] block of the query is returning a block that will contain the record number and then the record of those items matching the query. Other options of the db/flatten function include the /full refinement for removing all internal brackets from a block and the /fine refinement which is useful for trimming excess brackets from the exterior blocks.

4.2 Created? function

When you create a new database in TRETBASE, its creation time and date will be entered into the MASTER data for the respective database. This value can be queried with:

db/created? my-base

4.3 Modified? function

Anytime a change, add, or delete, of a record takes place on the database files, TRETBASE updates the modified data of the database's MASTER data. You can query this information with the following function:

db/modified? my-base

5. Misc Information and Configuration Options

You can set a 'db-path variable to the location where you want database files to be located in your own script as TRETBASE will check to see if 'db-path is already assigned before setting its default location to 'db-path.

You can change the default line terminator used for each respective database as follows:

db/eor some-base "*EOR*"

The default value is "^/" for each newly created database unless you use this command to change the default value that was assigned for the respective database.

That command should only be used during setup of new databases. Otherwise, you could corrupt your database if it is used after the database contains records.

You can check to ensure your database files exists using the db/exists? function. For example, if the database name is my-base we would use the following command:

db/exists? my-base

To check to see if all my-base database files are present we use the /full refinement:

db/exists?/full my-base

This command is a logic function so it will only return true or false.

You can also use the db/present? function to check to see if a database is recognized in MASTER Database. To perform this command we use:

db/present? my-base

This command is a logic function so it will only return true or false.

TRETBASE allows you to operate multiple databases at the same time. In this sense a database is what is traditionally known as a table but the data doesn't have to relate in any manner with other databases (tables) in the system. You can obtain a count of databases installed at any time using the db/count? function which will return the integer! value of databases installed on the system.

With the capabilities of TRETBASE's search function and its versatility, one can create their own dialect that meets their requirements and build wrapper functions for commonly used queries.