Handle System Banner
Previous: Splitting a Handle Server       Next: Using the Handle Client Library           Table of Contents

12. Using a SQL Database for Storage

Using a SQL database as storage for as handle server allows greater control over data deposits as well as permitting complex data query.

Configuring the Handle Server

To configure a handle server with an SQL storage module, first run the SimpleSetup program for the handle server software. Once the setup process is completed, a directory will exist that contains the files necessary to run the new handle server.

In the directory for the new handle server is a file named `config.dct' that can be modified using a text editor. The `config.dct' file contains all of the settings for the handle server. The `config.dct' file has some server-wide settings as well as several subsections that affect different parts of the server.

For example, the `config.dct' file for most handle servers will have sections named hdl_tcp_config, hdl_udp_config and hdl_http_config. Each of these sections holds the settings for one type of "listener" for the handle server.

Normal handle servers (as opposed to simple handle caching servers or http gateways) will also have a section named server_config that maintains the settings for the core part of the server. To tell the server to use an SQL back-end for storing and retrieving the handles, add the following value to the server_config section:
 
  storage_type = "sql"

Since the specified storage type for this handle server is SQL, some extra settings need to be provided. The following subsection should also be added to the server_config section:
 
sql_settings = {
sql_url = "jdbc:sybase:Tds:localhost:2638"
sql_driver = "com.sybase.jdbc.SybDriver"
sql_login = "sqluser"
sql_passwd = "sqlpassword"
sql_read_only = "no"}

Of course you will need to change the values to suit your particular installation. Here is an informal description of what each item in this section is for:

Example SQL Tables

The default configuration assumes a specific database table setup. The following tables were used for RDBMS storage using MySQL.

 
create table nas (
   na varchar(255) not null,
   PRIMARY KEY(na)
) ;

create table handles (
    handle varchar(255) not null,
    idx int4 not null,
    type blob,
    data blob,
    ttl_type int2,
    ttl int4,
    timestamp int4,
    refs blob,
    admin_read bool,
    admin_write bool,
    pub_read bool,
    pub_write bool,
    PRIMARY KEY(handle, idx)
);

These tables were used for Oracle.

 

create table nas (
   na raw(512)
) ;


create table handles (
    handle      raw(512),
    idx         number(10),
    type        raw(128),
    data        raw(600),
    ttl_type    number(5),
    ttl         number(10),
    timestamp   number(10),
    refs        varchar2(512),
    admin_read  varchar2(5),
    admin_write varchar2(5),
    pub_read    varchar2(5),
    pub_write   varchar2(5)
) ;

Depositing Handles Outside the Handle Server

If you wish to create or modify handles in the SQL database using custom tools, rather than the Handle Server, you must use all capital letters for data in the "handle" field, since the Handle Server is case insensitive.

Using Custom SQL Statements

It is also possible to specify the SQL that is used by the handle server to query the database. Changing these SQL statements is required if you do not use the same setup as above.

The SQL handle storage object used by the handle server has default SQL statements that are used to query and update the database. To replace the default SQL statements with custom statements, simply add the corresponding configuration setting to the sql_settings section described above. The following is a list of the SQL statements, their configuration setting, default values, and a short description of what the statement is used for.

get_handle_stmt

Default:

 
select idx, type, data, ttl_type, ttl, timestamp, refs, admin_read, 
admin_write, pub_read, pub_write from handles where handle = ?

Description:

This statement is used to retrieve the set of handle values associated with a handle from the database.

Input: The name of the handle to be queried.

Output:
 
 idx - positive integer value; unique across all values for the handle
 type - alphanumeric value; indicates the type of the data in each value
 data - alphanumeric value; the data associated with the value
 ttl_type - byte/short; 0=relative, 1=absolute
 ttl - numeric; cache timeout for this value in seconds (if ttl_type is
    absolute, then this indicates the date/time of expiration in seconds
    since Jan 1 0:00:00 1970.
 timestamp - numeric; the date that this value was last modified, in 
    seconds since Jan 1 0:00:00 1970
 refs - alphanumeric; list of tab-delimited index:handle pairs.  In each
    pair, tabs that occur in the handle part are escaped as \t.
 admin_read - boolean; indicates whether clients with administrative
    privileges have access to retrieve the handle value
 admin_write - boolean; indicates whether clients with administrative
    privileges have permission to modify the handle value
 pub_read - boolean; indicates whether all clients have permission to
    retrieve the handle value
 pub_write - boolean; indicates whether all clients have permission to
    modify the handle value

have_na_stmt

Default:

select count(*) from nas where na = ?

Description:

This statement is used to query whether or not the specified naming authority is "homed" to this server.

Input: The naming authority handle (eg 0.na/10.1000)

Output: One row, with one field. The value of that field is >0 if this server is responsible for that naming authority, or <=0 if not.

del_na_stmt

Default:

delete from nas where na = ?

Description:

This statement is used to remove a naming authority from the list of naming authorities that this server is responsible for.

Input: The naming authority handle (eg 0.na/10.1000)

Output: None

add_na_stmt

Default:

insert into nas ( na ) values ( ? )

Description:

This statement is used to add a naming authority to the list for which this server is responsible.

Input: The naming authority handle to "home" (eg 0.na/10.1000)

Output: None

scan_handles_stmt

Default:

select distinct handle from handles

Description:

This statement is used to get a list of all of the handles in the database.

Input: None

Output: a row for each distinct handle in the database.

scan_by_prefix_stmt

Default:

select distinct handle from handles where handle like ?

Description:

This statement is used to get a list of all handles in the database that have a given prefix.

Input: The prefix, including the slash ('/') character

Output: A row for each distinct handle in the database that starts with the given prefix

scan_nas_stmt

Default:

select distinct na from nas

Description:

This statement is used to get a list of distinct naming authorities that call this server home.

Input: None

Output: A row for each distinct naming authority handle

delete_all_handles_stmt

Default:

delete from handles

Description:

This statement is used to delete all of the handles in the database (!) This is only used when the handle server is acting as a secondary/mirror to a primary service and has gotten so far out of sync that it tries to delete and re-copy the entire database from the primary.

Input: None

Output: None

delete_all_nas_stmt

Default:

delete from nas

Description:

This statement is used to delete all of the naming authorities in the database. This is only invoked under the same circumstances as delete_all_handles_stmt.

Input: None

Output: None

create_handle_stmt

Default:

 
insert into handles ( handle, idx, type, data, ttl_type, ttl, 
timestamp, refs, admin_read, admin_write, pub_read, pub_write) 
values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Description:

This statement is used to insert a single handle value into the database.

Input: The fields of the handle and handle value, with the same order and type specified in the default statement above. See get_handle_stmt for type information for each field.

Output: None

handle_exists_stmt

Default:

select count(*) from handles where handle = ?

Description:

This statement is used to query whether or not a given handle exists in the database.

Input: The handle being queried

Output: None

delete_handle_stmt

Default:

delete from handles where handle = ?

Description:

This statement is used to delete a given handle from the database

Input: The handle being deleted

Output: None

modify_value_stmt

Default:

 
update handles set type = ?, data = ?, ttl_type = ?, ttl = ?, 
timestamp = ?, refs = ?, admin_read = ?, admin_write = ?, 
pub_read = ?, pub_write = ? where handle = ? and idx = ?

Description:

This statement is used to update a single handle value with new values. The value to update is identified by the handle and index.

Input:
 
  type - alphanumeric; the new type for the handle value
  data - alphanumeric; the new data value
  ttl_type - byte/short int; indicates whether the cache timeout is
    specified in relative or absolute terms (1=absolute, 0=relative)
  ttl - numeric; indicates the cache timeout in seconds (if ttl_type is
    absolute then the ttl indicates the expiration date in seconds since
    Jan 1 0:00:00 1970
  timestamp - numeric; date of the last modification to this handle 
    value (should be the current date/time!)
  refs - alphanumeric; tab delimited list of references for this handle
    value.  See get_handle_stmt for encoding.
 admin_read - boolean; indicates whether clients with administrative
    privileges have access to retrieve the handle value
 admin_write - boolean; indicates whether clients with administrative
    privileges have permission to modify the handle value
 pub_read - boolean; indicates whether all clients have permission to
    retrieve the handle value
 pub_write - boolean; indicates whether all clients have permission to
    modify the handle value

Previous: Using a SQL Database for Storage       Next: Using the Handle Client Library           Table of Contents