Xpg datalink

From Ljudmila

Jump to: navigation, search

A project to implement file management from within a database. Some of this is based on SQL/MED (Management of external data) specification.

The purpose of datalinks is to provide a mechanism to synchronize the integrity control, recovery, and access control of the files and the SQL-data associated with them.

  • integrity control - makes sure files referenced from database cannot be renamed or deleted while referenced
  • access control - makes files accessible through database (instead of filesystem) permissions
  • recovery - enables point-in-time recovery of files (backups)

xpg_datalink should enable you to:

  • efficiently store URLs (datalinks are stored as integers, not strings)
  • conveniently map between URLs and file system paths
  • safely read and write files from SQL (SQL/MED does not specify functions for this)
  • keep backups of changed files
  • manage files on different servers

Contents

Datalink type

Datalink type is the most interesting part of the SQL/MED datalink support.

Synopsys

create table my_files (
  ...,
  link datalink,
  ....
)

insert into my_files (link) select dlvalue('file:///etc/passwd');
 
select into url dlurlcomplete(link) from my_files;

update my_files set link=dlnewcopy(url,0);

update my_files set link=dlpreviouscopy(url,0);

Datalink support functions

Constructors for values of type datalink:

  • DLVALUE(url) -> datalink (INSERT only)
  • DLNEWCOPY(url,tokenp) -> datalink (UPDATE only)
  • DLPREVIOUSCOPY(url,tokenp) -> datalink (UPDATE only)

Functions for extracting information from datalink type:

  • DLURLCOMPLETE(datalink) -> url
  • DLURLCOMPLETEONLY(datalink) -> url
  • DLURLCOMPLETEWRITE(datalink) -> url
  • DLURLPATH(datalink) -> fpath
  • DLURLPATHONLY(datalink) -> fpath
  • DLURLPATHWRITE(datalink) -> fpath
  • DLURLSCHEME(datalink) -> URL scheme (HTTP or FILE)
  • DLURLSERVER(datalink) -> server address


Datalink attributes per SQL spec

These are refered to as link control options by SQL spec

LINK CONTROL

  • NO (0/n)
    • just store the datalink
    • file is not "linked", no further control
  • FILE (f)
    • file is "linked"
    • file has to exist
    • level of control can be specified with further options

INTEGRITY

  • ALL (a) - linked files cannot be deleted or renamed
  • SELECTIVE (c/s) - linked files can only be deleted or modified using file manager operations, if no datalinker is installed
  • NONE (0/n) - referenced files can be deleted or modified using file manager operations, not compatible with FILE LINK CONTROL

READ ACCESS

  • DB (d)
    • read access is controlled by SQL server, based on access privileges to the datalink value
    • involves read access tokens
      • encoded into the URL by the SQL server
      • verified by external file manager/data linker
  • FS (f)
    • read access is determined by file manager

WRITE ACCESS

  • ADMIN REQUIRING TOKEN FOR UPDATE (e/t)
    • write access governed by SQL server (and datalinker)
    • involves write access token for modifying file content
  • ADMIN NOT REQUIRING TOKEN FOR UPDATE (a)
    • write access governed by SQL server (and datalinker)
  • BLOCKED (b)
    • linked files cannot be modified
  • FS (f)
    • write access controlled by file manager

RECOVERY

  • YES (1/y)
  • NO (0/n)

UNLINK

  • DELETE (d)
    • file is deleted when unlinked
  • RESTORE (1/r)
    • original properties (ownership, permissions) restored as well
  • NONE (0/n)
    • ownership and permissions are not restored

Internal datalink attribute representation

Attributes can be conveniently specified as 6-character ASCII strings, in the manner of unix file permissions. This makes them quite convenient to handle in Perl. This is defined as a domain "dl_attr" with a value constraint for values, permissible by standard.

matching regexes for legal combinations according to standard:

  1. 'nnffnn' - no control
  2. 'fsffnn' - file must exist but can dissapear later (dl:exists)
  3. 'faffnn' - link file to SQL environment (dl:link)
  4. 'fafbnr' - protect file from renaming and deletion (dl:lock)
  5. 'fafbyr' - point-in-time recovery for file (dl:??)
  6. 'fadb[yn]r' - read access thru db only (via datalinker) (dl:read...)
  7. 'fadb[yn]d' - delete when no more references to it exist (dl:delete)
  8. 'fad[ta][yn][rd]' - write access through db, replace-in-place (dl:write...)

Datalinker

Datalinker is a process external to the database which interacts with files on the filesystem. Database communicates with this process. This process must possess right privileges to be able to perform some actions.

datalinker API

  • begin
  • commit
  • rollback
  • stat - get file info
  • lockw - protect against rename and delete
  • lockr - remove FS read permissions
  • unlink - delete file
  • read - read file
Retrieved from "http://wiki.ljudmila.org/index.php/Xpg_datalink"
Personal tools