<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="21. Appendix" />
<meta name="dc.subject" content="21. Appendix" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="appendixa.html" title="Chapter Contents" />
<link rel="prev" href="support.html" title="Product Support" />
<link rel="next" href="apndxregexp.html" title="Basic Syntax of Regular Expressions" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>21. Appendix</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="systemtables" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>21. Appendix</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="appendixa.html">Chapter Contents</a> | <a class="link" href="support.html" title="Product Support">Prev</a> | <a class="link" href="apndxregexp.html" title="Basic Syntax of Regular Expressions">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="appendixa.html">Appendix</a>
</div>
<br />
<div>
<a href="yacc.html">YACC SQL Grammar Reference</a>
</div>
<div>
<a href="errors.html">Error Codes Reference</a>
</div>
<div>
<a href="signalsandexitcodes.html">Signals and Exit codes</a>
</div>
<div>
<a href="apparelnotes.html">Release Notes</a>
</div>
<div>
<a href="support.html">Product Support</a>
</div>
<div class="selected">
<a href="systemtables.html">Virtuoso System Tables</a>
<div>
<a href="#SysTables" title="Core System Tables">Core System Tables</a>
<a href="#UserSysTables" title="System Tables">System Tables</a>
<a href="#rowlevelsectab" title="Row Level Security Table">Row Level Security Table</a>
<a href="#charsetsystab" title="SYS_CHARSETS">SYS_CHARSETS</a>
<a href="#sys_collations" title="Collations System Table">Collations System Table</a>
<a href="#uddischema" title="UDDI Schema">UDDI Schema</a>
<a href="#robotsystables" title="Web Robot System Tables">Web Robot System Tables</a>
<a href="#davsystables" title="Web Server & DAV System Tables">Web Server & DAV System Tables</a>
<a href="#smtptabledesc" title="Mail Table Description">Mail Table Description</a>
<a href="#newssrvtables" title="NNTP Server Tables">NNTP Server Tables</a>
<a href="#systabswsrm" title="WS Reliable Messaging">WS Reliable Messaging</a>
<a href="#wstschema" title="WS Trust">WS Trust</a>
<a href="#syncmlschema" title="SyncML Schema Objects">SyncML Schema Objects</a>
<a href="#views_information_schema" title="INFORMATION_SCHEMA views">INFORMATION_SCHEMA views</a>
</div>
</div>
<div>
<a href="apndxregexp.html">Basic Syntax of Regular Expressions</a>
</div>
<div>
<a href="apndxclientcompilance.html">Server & client versions compatibility</a>
</div>
<br />
</div>
<div id="text">
<a name="systemtables" />
<h2>21.6. Virtuoso System Tables</h2>
<a name="SysTables" />
<h3>21.6.1. Core System Tables</h3>
<div>
<pre class="programlisting">
create table SYS_COLS (
TABLE varchar,
COLUMN varchar,
COL_ID integer,
COL_DTP integer,
primary key (COL_ID))
create index SYS_COLS_BY_NAME on SYS_COLS (TABLE, COLUMN)
create table SYS_KEYS (
KEY_TABLE varchar,
KEY_NAME varchar,
KEY_ID integer,
KEY_N_SIGNIFICANT integer,
KEY_CLUSTER_ON_ID integer,
KEY_IS_MAIN integer,
KEY_IS_OBJECT_ID integer,
KEY_IS_UNIQUE integer,
KEY_MIGRATE_TO integer,
primary key (KEY_TABLE, KEY_NAME))
create index SYS_KEYS_BY_ID on SYS_KEYS (KEY_ID)
create table SYS_KEY_PARTS (
KP_KEY_ID integer,
KP_NTH integer,
KP_COL integer
primary key (KP_KEY_ID, KP_NTH))
create table SYS_KEY_SUBKEY (
SUPER integer,
SUB integer,
primary key (SUPER, SUB))
</pre>
</div>
<p>
The SYS_COLS table defines all tables and columns. Each column
appears once in this table. No entries are made for inherited columns.
The SYS_KEYS table defines all indices. A row in this table corresponds
to each key in the database, either originally defined or inherited.
</p>
<p>
KEY_IS_MAIN is non-zero if this
is the primary key of its table. The KEY_MIGRATE_TO is the key ID of a
new version of this key if this key is obsolete (e.g. the primary key
of a table from before an ALTER TABLE). The KEY_N_SIGNIFICANT
indicates how many leading key parts are used in ordering keys.
</p>
<p>
The KEY_ID references the KP_KEY_ID in the SYS_KEY_PARTS table.
This table embodies the actual layout of keys and rows. The KP_NTH is
a number positioning the KP_COL to the appropriate place in the row.
The KP_COL references the COL_ID in SYS_COLS. The KP_NTH is not
necessarily a series of consecutive integers but the order matches the
order of columns on the row.
</p>
<p>
The SYS_KEY_SUBKEY table has a row for each pair of keys where one is
the immediate subkey of the other.
Note that when a table is altered, the obsolete layout is marked as a subtable of the new layout of the primary key. Thus a select on the new primary key will also get the obsolete rows.
</p>
<p>The following SQL procedure exemplifies how the SYS_COLS, SYS_KEYS
and SYS_KEY_PARTS tables combine. Given a table name it produces the
set of columns and the set of indices and their parts.
</p>
<div>
<pre class="programlisting">
create procedure dt (in tn varchar)
{
declare index_name, col, dtp varchar;
declare t, nth integer;
dtp := '-';
result_names (col, t); --- first result set
declare cr cursor for select COLUMN, COL_DTP
from SYS_COLS where TABLE = tn;
whenever not found goto done;
open cr;
while (1=1) {
fetch cr into col, t;
result (col, t);
}
done:
result_names (index_name, nth, col); -- 2nd result set
end_result ();
declare icr cursor for select KEY_NAME, KP_NTH, COLUMN
from SYS_KEYS, SYS_KEY_PARTS, SYS_COLS
where KEY_TABLE = tn and KP_KEY_ID = KEY_ID
and KP_NTH < KEY_N_SIGNIFICANT and COL_ID = KP_COL;
whenever not found goto k_done;
open icr;
while (1=1) {
fetch icr into index_name, nth, col;
result (index_name, nth, col);
}
k_done:
return 0;
}
</pre>
</div>
<br />
<a name="UserSysTables" />
<h3>21.6.2. System Tables</h3>
<p>DB objects related to the User and Role objects are created as follows. The terms group and role are used interchangeably.</p>
<div>
<pre class="programlisting">create table
SYS_USERS (
U_ID integer unique, -- unique id identifying the security object
U_NAME char (128), -- unique name identifying the security object
U_IS_ROLE integer default 0, -- if true it's a group
U_FULL_NAME char (128), -- for information only, name of that user or group
U_E_MAIL char (128) default '', -- e-mail for contact
U_PASSWORD char (128), -- encrypted password
U_GROUP integer, /* the primary group references SYS_USERS (U_ID), */
U_LOGIN_TIME datetime, -- last login time, can be set in login hooks,
-- otherwise is set when login in webDAV repository
U_ACCOUNT_DISABLED integer default 1, -- if true the account is not functional
U_DAV_ENABLE integer default 0, -- true if DAV login allowed
U_SQL_ENABLE integer default 1, -- true if SQL/(ODBC/JDBC/OLE/DB etc) login allowed.
U_DATA varchar, /* reserved */
U_METHODS integer, /* reserved */
U_DEF_PERMS char (10) default '110100000R', -- see PERMISSIONS option value
U_HOME varchar (128), -- see HOME option value
U_PASSWORD_HOOK varchar, -- see PASSWORD_MODE option value
U_PASSWORD_HOOK_DATA varchar, -- see PASSWORD_MODE_DATA option value
U_GET_PASSWORD varchar, -- see GET_PASSWORD option data
U_DEF_QUAL varchar, -- default qualifier for SQL/ODBC login
U_OPTS long varchar, -- extensibility options for user&group objects
primary key (U_NAME)
)</pre>
</div>
<div>
<pre class="programlisting">create table
SYS_ROLE_GRANTS (
GI_SUPER integer references SYS_USERS (U_ID), -- user object id
GI_SUB integer references SYS_USERS (U_ID), -- granted role object id
GI_DIRECT integer default 1, -- false if indirect, i.e.
-- true by inheritance, not direct grant.
GI_GRANT integer, -- who has granted this role
GI_ADMIN integer default 0, -- true if granted with admin option
primary key (GI_SUPER, GI_SUB, GI_DIRECT));</pre>
</div>
<p>
For Backwards compatibility the system tables SYS_DAV_USER, SYS_DAV_GROUP,
SYS_DAV_USER_GROUP and SYS_USER_GROUP are re-defined as views:</p>
<div>
<pre class="programlisting">-- WebDAV Users
create view WS.WS.SYS_DAV_USER (U_ID, U_NAME, U_FULL_NAME, U_E_MAIL, U_PWD,
U_GROUP, U_LOGIN_TIME, U_ACCOUNT_DISABLED, U_METHODS, U_DEF_PERMS, U_HOME)
as select U_ID, U_NAME, U_FULL_NAME, U_E_MAIL, U_PASSWORD as U_PWD,
U_GROUP, U_LOGIN_TIME, U_ACCOUNT_DISABLED, U_METHODS, U_DEF_PERMS, U_HOME
from DB.DBA.SYS_USERS where U_IS_ROLE = 0 and U_DAV_ENABLE = 1;
-- WebDAV Groups
create view WS.WS.SYS_DAV_GROUP (G_ID, G_NAME)
as select U_ID as G_ID, U_NAME as G_NAME
from DB.DBA.SYS_USERS where U_IS_ROLE = 1 and U_DAV_ENABLE = 1;
-- The granted groups to the WebDAV user
create view WS.WS.SYS_DAV_USER_GROUP (UG_UID, UG_GID) as select GI_SUPER, GI_SUB from DB.DBA.SYS_ROLE_GRANTS
where GI_DIRECT = 1;
create view SYS_USER_GROUP (UG_UID, UG_GID) as select GI_SUPER, GI_SUB
from SYS_ROLE_GRANTS where GI_DIRECT = 1;</pre>
</div>
<div>
<pre class="programlisting">
create table SYS_GRANTS (
G_USER varchar,
G_OP integer,
G_OBJECT varchar,
G_COL varchar,
primary key (G_USER, G_OP, G_OBJECT, G_COL));
</pre>
</div>
<p>
These tables are visible only to dba members. The procedure list_grants
shows a summary of granted privileges:
</p>
<div>
<pre class="screen">
SQL> list_grants (0);
</pre>
</div>
<p>
These tables should not be modified by applications. Only the SQL
statements GRANT, REVOKE, CREATE USER, DELETE USER, SET USER GROUP and
SET PASSWORD should be used to maintain user and security information.
Security information is cached in RAM during the execution of a Virtuoso
process and these statements ensure that the cache stays consistent with
the tables.
</p>
<br />
<a name="rowlevelsectab" />
<h3>21.6.3. Row Level Security Table</h3>
<div>
<pre class="programlisting">
create table SYS_RLS_POLICY (
RLSP_TABLE varchar, - the FQN of the table
RLSP_OP varchar, - the operation (one per row : I/U/D/S)
RLSP_FUNC varchar, - the FQN of the stored procedure to be called
PRIMARY KEY (RLSP_TABLE, RLSP_OP))</pre>
</div>
<p>That system table is used for persisting the definitions</p>
<br />
<a name="charsetsystab" />
<h3>21.6.4. SYS_CHARSETS</h3>
<div>
<pre class="programlisting">
CREATE TABLE SYS_CHARSETS (
CS_NAME varchar, -- The "preferred" charset name
CS_TABLE long nvarchar, -- the mapping table of length 255 Wide chars
CS_ALIASES long varchar -- serialized vector of aliases
);
</pre>
</div>
<br />
<a name="sys_collations" />
<h3>21.6.5. Collations System Table</h3>
<p>
The SYS_COLLATIONS system table holds the data for all the defined collations. It has the following structure:
</p>
<div>
<pre class="programlisting">
CREATE TABLE SYS_COLLATIONS (
COLL_NAME VARCHAR,
COLL_TABLE LONG VARBINARY,
COLL_IS_WIDE INTEGER);
</pre>
</div>
<p>
COLL_NAME is the fully qualified name of the collation (it's identifier)
</p>
<p>
COLL_TABLE holds the collation table (256 bytes or 65536 wide chars)
</p>
<p>
COLL_IS_WIDE show the collation's type (0 for CHAR and 1 for NCHAR). Note that a 8bit collation
cannot be used by a NCHAR data and vice versa.
</p>
<p>
Collation can be deleted by deleting its row from SYS_COLLATIONS.
</p>
<div class="note">
<div class="notetitle">Note</div>
<p>The collation will still be available until the server is restarted, as it's definition is cached into memory.
</p>
</div>
<br />
<a name="uddischema" />
<h3>21.6.6. UDDI Schema</h3>
<div>
<pre class="programlisting">
---=====================
--- Business Entity Table
---=====================
create table UDDI..BUSINESS_ENTITY (
BE_BUSINESS_KEY varchar,
BE_AUTHORIZED_NAME varchar,
BE_OPERATOR varchar,
BE_NAME varchar not null,
BE_CREATED datetime,
BE_CHANGED timestamp,
BE_OWNER integer,
PRIMARY KEY (BE_BUSINESS_KEY)
)
;
</pre>
</div>
<div>
<pre class="programlisting">
---=======================
--- Business service Table
---=======================
create table UDDI..BUSINESS_SERVICE (
BS_BUSINESS_KEY varchar, -- references business entity (optional)
BS_SERVICE_KEY varchar not null, -- Unique Key
BS_NAME varchar not null, -- Name
BS_CREATED datetime,
BS_CHANGED timestamp,
BS_OWNER integer,
PRIMARY KEY (BS_SERVICE_KEY)
)
;
</pre>
</div>
<div>
<pre class="programlisting">
---========================
--- Binding Templates Table
---========================
create table UDDI..BINDING_TEMPLATE (
BT_BINDING_KEY varchar,
BT_SERVICE_KEY varchar,
BT_ACCESS_POINT varchar,
BT_HOSTING_REDIRECTOR varchar,
BT_URL_TYPE varchar,
BT_CREATED datetime,
BT_CHANGED timestamp,
BT_OWNER integer,
PRIMARY KEY (BT_BINDING_KEY)
)
;
</pre>
</div>
<div>
<pre class="programlisting">
---======================
--- tModel Table
---======================
create table UDDI..TMODEL (
TM_TMODEL_KEY varchar,
TM_AUTHORIZED_NAME varchar,
TM_OPERATOR varchar,
TM_NAME varchar,
TM_CREATED datetime,
TM_CHANGED timestamp,
TM_OWNER integer,
primary key (TM_TMODEL_KEY)
)
;
</pre>
</div>
<div>
<pre class="programlisting">
---====================
--- Descriptions Table
---====================
create table UDDI..DESCRIPTION (
UD_DESC varchar, -- Description Text
UD_LANG varchar,
UD_PARENT_ID varchar, -- Parent ID (references tmodel,
-- businessService etc.)
UD_TYPE varchar -- ParentType (name of parent table)
)
create index DESC_PARENT on DESCRIPTION (UD_TYPE, UD_PARENT_ID)
;
</pre>
</div>
<div>
<pre class="programlisting">
--=====================================================================================
-- Discovery URL table: contains structure - holds a URL addressable discovery documents
--=====================================================================================
create table UDDI..DISCOVERY_URL (
DU_PARENT_ID varchar not null, -- parent ID
DU_PARENT_TYPE varchar not null, -- name of parent element
DU_URL varchar, -- URI
DU_USE_TYPE varchar -- UseType element
)
create index DISCOVERY_URLS_PARENT on DISCOVERY_URL (DU_PARENT_TYPE, DU_PARENT_ID)
;
</pre>
</div>
<div>
<pre class="programlisting">
--===================
-- Address line table
--===================
create table UDDI..ADDRESS_LINE (
AL_PARENT_ID varchar not null, -- Parent key
AL_PARENT_TYPE varchar not null, -- name of parent element
AL_USE_TYPE varchar, -- UseType element
AL_SORT_CODE varchar, -- SortCode element
AL_LINE varchar -- The Line content
)
create index ADDR_LINE_PARENT on ADDRESS_LINE (AL_PARENT_TYPE, AL_PARENT_ID)
;
</pre>
</div>
<div>
<pre class="programlisting">
---================
--- Contacts Table
---===============
create table UDDI..CONTACTS (
CO_CONTACT_KEY varchar,
CO_BUSINESS_ID varchar not null, -- references business entity table by business key
CO_USE_TYPE varchar, -- UseType element
CO_PERSONAL_NAME varchar not NULL, -- name
PRIMARY KEY (CO_CONTACT_KEY)
)
create index IN_BUSINESS on CONTACTS (CO_BUSINESS_ID)
;
</pre>
</div>
<div>
<pre class="programlisting">
--============
-- email table
--============
create table UDDI..EMAIL (
EM_CONTACT_KEY varchar not null,
EM_ADDR varchar,
EM_USE_TYPE varchar
)
create index IN_EMPARENT on EMAIL (EM_CONTACT_KEY)
;
</pre>
</div>
<div>
<pre class="programlisting">
--============
-- phone table
--============
create table UDDI..PHONE (
PH_CONTACT_KEY varchar not null,
PH_PHONE varchar,
PH_USE_TYPE varchar
)
create index IN_PHPARENT on PHONE (PH_CONTACT_KEY)
;
</pre>
</div>
<div>
<pre class="programlisting">
---=====================
--- Identifier Bag Table
---=====================
create table UDDI..IDENTIFIER_BAG (
IB_PARENT_ID varchar not null,
IB_PARENT_TYPE varchar not null,
IB_TMODEL_KEY_ID varchar,
IB_KEY_NAME varchar,
IB_KEY_VALUE varchar
)
create index IB_PARENT on IDENTIFIER_BAG (IB_PARENT_ID,IB_PARENT_TYPE)
;
</pre>
</div>
<div>
<pre class="programlisting">
---===================
--- Category Bag Table
---===================
create table UDDI..CATEGORY_BAG (
CB_PARENT_ID varchar not null,
CB_PARENT_TYPE varchar not null,
CB_TMODEL_KEY_ID varchar,
CB_KEY_NAME varchar,
CB_KEY_VALUE varchar
)
create index CB_PARENT on CATEGORY_BAG (CB_PARENT_ID, CB_PARENT_TYPE)
;
</pre>
</div>
<div>
<pre class="programlisting">
---===================
--- Overview Doc Table
---===================
create table UDDI..OVERVIEW_DOC (
OV_KEY varchar,
OV_PARENT_ID varchar not null,
OV_PARENT_TYPE varchar not null,
OV_URL varchar,
PRIMARY KEY (OV_KEY)
)
create unique index PARENT_OVERVIEW_DOC on OVERVIEW_DOC (OV_PARENT_ID, OV_PARENT_TYPE)
;
</pre>
</div>
<div>
<pre class="programlisting">
---===============================
--- TModel Instance Details Table
---===============================
create table UDDI..INSTANCE_DETAIL (
ID_KEY varchar not null,
ID_BINDING_KEY varchar, -- references btemplate(bindingkey)
ID_TMODEL_KEY varchar, -- references tmodel(tmodelkey)
ID_PARMS varchar,
primary key (ID_KEY)
)
create index IN_IDPARENT on INSTANCE_DETAIL (ID_BINDING_KEY, ID_TMODEL_KEY)
;
</pre>
</div>
<br />
<a name="robotsystables" />
<h3>21.6.7. Web Robot System Tables</h3>
<div class="formalpara">
<strong>Target sites table</strong>
<div>
<pre class="programlisting">
create table WS.WS.VFS_SITE (
VS_DESCR varchar, -- Human readable description
VS_HOST varchar, -- target hostname (eq. www.foo.com)
VS_URL varchar, -- target path (eq. /cgi-bin/ or /)
VS_INX char(5), -- not used
VS_OWN integer, -- ID of the local WebDAV owner
VS_ROOT varchar, -- target WebDAV collection
VS_NEWER datetime, -- Update the link if newer than
VS_DEL char(3), -- Delete local copy if delete on remote detected flag
VS_FOLLOW varchar, -- follow list (list of masks to allow following the links)
VS_NFOLLOW varchar, -- the do not follow list
VS_SRC char(3), -- do get of the images - flag
VS_OPTIONS varchar, -- username/password credentials for
-- authorization on target site
VS_METHOD varchar, -- use HTTP or HTTP/WebDAV to retrieve the target site
VS_OTHER char(10), -- go to other sites flag
primary key (VS_HOST, VS_ROOT)
)
;
</pre>
</div>
</div>
<div class="formalpara">
<strong>The queues table</strong>
<div>
<pre class="programlisting">
create table WS.WS.VFS_QUEUE (
VQ_HOST varchar, -- target host
VQ_TS datetime, -- when added to the queue
VQ_URL varchar, -- path on target
VQ_ROOT varchar, -- the WebDAV local collection
VQ_STAT char (15),-- status of the entry
VQ_OTHER varchar, -- flag for other site generated link
primary key (VQ_HOST, VQ_URL, VQ_ROOT)
)
;
</pre>
</div>
</div>
<div class="formalpara">
<strong>The retrieved URLs table</strong>
<div>
<pre class="programlisting">
create table WS.WS.VFS_URL (
VU_HOST varchar, -- target host
VU_URL varchar, -- path on target
VU_ROOT varchar, -- the local WebDAV collection containing
-- content of the retrieved link
VU_CHKSUM varchar, -- checksum
VU_ETAG varchar, -- Etag from target
VU_CPTIME datetime, -- when is copied
VU_OTHER varchar, -- is the link retrieved from foreign site
primary key (VU_HOST, VU_URL, VU_ROOT)
)
;
</pre>
</div>
</div>
<br />
<a name="davsystables" />
<h3>21.6.8. Web Server & DAV System Tables</h3>
<p>
All Web Server and DAV related tables and procedures are held in the
WS catalogue. This reference section illustrates their structure.
</p>
<a name="davcollections" />
<h4>21.6.8.1. Collections (folders)</h4>
<div>
<pre class="programlisting">
create table WS.WS.SYS_DAV_COL (
COL_ID integer, -- unique collection id
COL_NAME char(256),-- collection name
COL_OWNER integer, -- collection owner id (FK SYS_DAV_USER (U_ID))
COL_GROUP integer, -- collection owner group id (FK SYS_DAV_GROUP (G_ID))
COL_PARENT integer, -- parent collection id (FK SYS_DAV_COL (COL_ID))
COL_CR_TIME datetime, -- creation time
COL_MOD_TIME datetime, -- modification time
COL_PERMS char(9), -- collection access permissions (like UNIX ugo style)
primary key (COL_NAME, COL_PARENT)
)
;
</pre>
</div>
<br />
<a name="davgroups" />
<h4>21.6.8.2. Groups</h4>
<br />
<a name="davlocks" />
<h4>21.6.8.3. Locks</h4>
<div>
<pre class="programlisting">
create table WS.WS.SYS_DAV_LOCK (
LOCK_TYPE char (1), -- type of the lock (R (read) or W (write))
LOCK_SCOPE char (1), -- lock scope (X (exclusive) or S (shared))
LOCK_TOKEN char(256), -- unique lock token
LOCK_PARENT_TYPE char (1), -- lock parent type (R (resource), C (collection))
LOCK_PARENT_ID integer, -- lock parent (resource/collection)
-- id (FK SYS_DAV_COL (COL_ID) or SYS_DAV_RES (RES_ID))
LOCK_TIME datetime, -- lock creation time
LOCK_TIMEOUT integer, -- lock time-out
LOCK_OWNER integer, -- lock owner id (FK SYS_DAV_USER (U_ID))
LOCK_OWNER_INFO varchar, -- lock owner info (if WebDAV client sent
-- additional info eq. e-mail etc.)
primary key (LOCK_PARENT_TYPE, LOCK_PARENT_ID)
)
;
</pre>
</div>
<br />
<a name="davproperties" />
<h4>21.6.8.4. Properties</h4>
<div>
<pre class="programlisting">
create table WS.WS.SYS_DAV_PROP (
PROP_ID integer, -- unique property id
PROP_NAME char(256), -- property name
PROP_TYPE char (1), -- property parent type (R (resource), C (collection))
PROP_PARENT_ID integer, -- parent resource/collection id (FK SYS_DAV_COL (COL_ID)
-- or SYS_DAV_RES (RES_ID))
PROP_VALUE varchar, -- value of the property (plain text or serialized XML entity)
primary key (PROP_NAME, PROP_TYPE, PROP_PARENT_ID)
)
;
</pre>
</div>
<br />
<a name="davresources" />
<h4>21.6.8.5. Resources (documents)</h4>
<div>
<pre class="programlisting">
create table WS.WS.SYS_DAV_RES (
RES_ID integer, -- unique resource id
RES_NAME char(256),-- resource name
RES_OWNER integer, -- resource owner id (FK SYS_DAV_USER (U_ID))
RES_GROUP integer, -- resource owner group id (FK SYS_DAV_GROUP (G_ID))
RES_COL integer, -- parent collection id (FK SYS_DAV_COL (COL_ID))
RES_CONTENT long varchar IDENTIFIED BY RES_FULL_PATH,
-- resource content
RES_TYPE varchar, -- resource MIME type (eq. text/plain etc.)
RES_CR_TIME datetime, -- creation time
RES_MOD_TIME datetime, -- modification time
RES_PERMS char (9), -- resource access permissions (like UNIX ugo style)
RES_FULL_PATH varchar, -- resource full path (eq. /DAV/docs/name.txt)
primary key (RES_COL, RES_NAME)
)
;
</pre>
</div>
<br />
<a name="davrestypes" />
<h4>21.6.8.6. Resource Types (MIME types)</h4>
<div>
<pre class="programlisting">
create table WS.WS.SYS_DAV_RES_TYPES (
T_EXT varchar, -- extension
T_TYPE varchar, -- associated MIME type
T_DESCRIPTION varchar, -- optional description
primary key (T_EXT)
)
;
</pre>
</div>
<div class="note">
<div class="notetitle">Note:</div>
<p>
The Resource Types table is also used by the HTTP server for determining the
appropriate 'Content-Type' header for any deliverable, either from WebDAV or the file system.
</p>
</div>
<br />
<a name="davhttppath" />
<h4>21.6.8.7. HTTP Virtual Directory Mappings</h4>
<div>
<pre class="programlisting">
create table DB.DBA.HTTP_PATH (
HP_HOST varchar not null, -- mapping Host in HTTP header note: *ini*
HP_LISTEN_HOST varchar not null, -- IP address & port for
-- mapping listening session
HP_LPATH varchar not null, -- logical path
HP_PPATH varchar not null, -- physical path
HP_STORE_AS_DAV integer not null, -- flag for webDAV storage
HP_DIR_BROWSEABLE integer not null, -- directory listing allowed
HP_DEFAULT varchar, -- default page
HP_SECURITY varchar, -- which method allowed all/https/digest (NULL/SSL/DIGEST)
HP_REALM varchar, -- authentication realm
HP_AUTH_FUNC varchar, -- which function authenticate this directory
HP_POSTPROCESS_FUNC varchar, -- function call after request
HP_RUN_VSP_AS varchar, -- uid for VSPs REFERENCES SYS_USERS (U_NAME)
-- ON DELETE SET NULL
HP_RUN_SOAP_AS varchar, -- uid for SOAP REFERENCES SYS_USERS (U_NAME)
-- ON DELETE SET NULL
HP_PERSIST_SES_VARS integer, -- have a persistent session variables
HP_SOAP_OPTIONS varchar, -- SOAP options
HP_AUTH_OPTIONS varchar, -- options for authentication hook function
primary key (HP_LISTEN_HOST, HP_HOST, HP_LPATH)
)
;
</pre>
</div>
<br />
<a name="tables.db.dba.http_acl" />
<h4>21.6.8.8. HTTP Access Control List</h4>
<div>
<pre class="programlisting">
create table HTTP_ACL (
HA_LIST varchar not null, -- ACL name
HA_ORDER integer not null, -- Position in the list
HA_OBJECT integer not NULL default -1, -- Object ID (applicable to news groups also)
HA_CLIENT_IP varchar not NULL, -- *PATTERN*
HA_FLAG integer not NULL default 1, -- Allow/Deny flag, 0 - allow, 1 - deny
HA_RW integer default 0, -- Read/Write flag, 0 - read, 1 - post
HA_DEST_IP varchar default '*', -- Destination IP/Host (applicable to the proxy also)
PRIMARY KEY (HA_LIST, HA_ORDER, HA_CLIENT_IP, HA_FLAG)
);
</pre>
</div>
<br />
<br />
<a name="smtptabledesc" />
<h3>21.6.9. Mail Table Description</h3>
<div>
<pre class="programlisting">
- - Message queue
create table MAIL_MESSAGE_QUEUE (
MQ_ID varchar, - - FK MAIL_MESSAGE (MM_ID)
MQ_RECIEVER varchar, - - foreign mail exchanger
primary key (MQ_ID))
;
create index MAIL_MSG_Q_REC on MAIL_MESSAGE_QUEUE (MQ_RECIEVER)
;
-- Mail Table
create table DB.DBA.MAIL_MESSAGE (
MM_ID integer, -- Unique id of message (per user)
MM_OWN varchar (128), -- Local WebDAV account (recipient, mail box owner) name,
FK references WS.WS.SYS_DAV_USER (U_NAME)
MM_FLD varchar (128), -- Message Folder (initial 'Inbox')
MM_FROM varchar (512), -- From: RFC822 header field
MM_TO varchar (512), -- To: RFC822 header field
MM_CC varchar (512), -- Cc: RFC822 header field
MM_BCC varchar (512), -- Bcc: RFC822 header field
MM_SUBJ varchar (512), -- Subject of the message
MM_REC_DATE varchar (50), -- Date of arrival
MM_SND_TIME varchar (50), -- Date of posting
MM_IS_READED integer, -- Read flag (0/1 not read, 1 - read)
MM_BODY long varchar, -- Message content (including message header)
primary key (MM_OWN, MM_FLD, MM_ID)
)
;
</pre>
</div>
<div class="note">
<div class="notetitle">Note:</div>
<p>MM_FROM, MM_TO, MM_CC, MM_BCC, MM_SUBJ, MM_SND_TIME exists only if there exists corresponding RFC822 headers in mail message</p>
</div>
<div>
<pre class="programlisting">
-- Temporary message MIME parts table (for message composition)
create table DB.DBA.MAIL_PARTS (
MP_ID integer, -- Unique id per user (order of parts)
MP_PART long varbinary, -- Message MIME part body
MP_ATTRS long varbinary, -- Message MIME part attributes
MP_OWN varchar (128), -- WebDAV user name - FK REFERENCES WS.WS.SYS_DAV_USER (U_NAME)
primary key (MP_OWN, MP_ID)
)
;
- - Local mail users
(this table will be needed only in case of different DB, DAV and MAIL users)
create table MAIL_USERS (
MU_NAME varchar,
MU_PWD varchar,
primary key (MU_NAME))
;
- - Allowed relay domains
(only in case of allowed relaying)
create table MAIL_RELAY (
MR_ALLOWED varchar;
primary key (MR_ALLOWED))
;
</pre>
</div>
<br />
<a name="newssrvtables" />
<h3>21.6.10. NNTP Server Tables</h3>
<p>The server uses the following tables:</p>
<div>
<pre class="programlisting">
create table NEWS_MESSAGES (
NM_ID varchar (128) not null, -- Message-ID (unique)
NM_REF varchar (128), -- References
NM_GROUP varchar (128) not null, -- Newsgroups ID
NM_NUM_GROUP integer not null, -- ID unique for group
NM_READED integer, -- How many times this message is read
NM_OWN varchar (128), -- Local sender (if sender is non
local should be null)
NM_REC_DATE datetime, -- Receiving date
NM_STAT integer, -- Post from the local user
NM_HEAD long varchar, -- Message header
NM_BODY long varchar, -- Message content
primary key (NM_ID))
</pre>
</div>
<div>
<pre class="programlisting">
create table NEWS_GROUPS (
NG_GROUP integer identity, -- Newsgroups ID
NG_NAME varchar (128) NOT NULL, -- Local name
NG_DESC varchar (128), -- Comment
NG_SERVER varchar (128), -- Server
NG_SERV_PORT integer, -- Server port
NG_OUT_GROUP varchar (128), -- Out name
NG_POST integer, -- Flag 0/1 posting allowed
NG_UP_TIME datetime, -- Last Update
NG_CREAT datetime, -- When group is created (attached)
NG_UP_INT integer, -- Update interval (min)
NG_CLEAR_INT integer, -- Drop interval for messages
NG_LIFE_READ integer, -- Flag 0/1 read messages exist longer
NG_STAT integer, -- Flag 0/3 Result from last update.
0 - Unsuccessful
1 - Successful
3 - Successful but does not get all available messages.
NG_AUTO integer, -- Flag 0/1 Auto download.
NG_PASS integer, -- Download messages for one pass.
NG_UP_MESS integer, -- Messages from last update.
NG_NUM integer, -- Estimated number of articles in group
NG_FIRST integer, -- First article number in the group
NG_LAST integer, -- Last article number in the group
primary key (NG_GROUP))
</pre>
</div>
<div>
<pre class="programlisting">
create table NEWS_ACL (
NA_GROUP integer not NULL, -- News Group number, referencing
NEWS_GROUPS (NG_GROUP)
NA_IP varchar not NULL, -- Client IP (the mask is %)
NA_A_D integer not NULL, -- flag 0 - allow, 1 - deny (action)
NA_RW integer not NULL, -- flag 0 - read, 1 - post (reading
or posting action is allowed/disallowed)
PRIMARY KEY (NA_GROUP, NA_IP, NA_A_D, NA_RW));
</pre>
</div>
<br />
<a name="systabswsrm" />
<h3>21.6.11. WS Reliable Messaging</h3>
<a name="systabswsrmrecvr" />
<h4>21.6.11.1. Receiver-Side Schema Objects</h4>
<p>Incoming messages table used to keep
successfully received messages. An application on receiver side can
access it in order to process the messages.</p>
<div>
<pre class="programlisting">
CREATE TABLE SYS_WSRM_IN_MESSAGE_LOG (
IML_INDENTIFIER varchar, -- references SYS_WSRM_IN_SEQUENCES.WIS_IDENTIFIER
IML_MESSAGE_ID int NOT NULL, -- unique per sequence
IML_EXPIRE_DATE datetime, -- when expires
IML_RECEIVE_DATE timestamp NOT NULL ,
IML_MESSAGE long varchar NOT NULL, -- the message itself
IML_STATE int,
primary key (IML_INDENTIFIER, IML_MESSAGE_ID)
)
;
</pre>
</div>
<p>Incoming sequences table; used to keep the
successfully established message sequence
and policy to be applied to it.
</p>
<div>
<pre class="programlisting">
CREATE TABLE SYS_WSRM_IN_SEQUENCES
(
WIS_IDENTIFIER varchar, -- sequence identifier
WIS_VERSION varchar,
WIS_DELIVERY_ASSURANCE varchar,
WIS_SEQUENCE_EXPIRATION datetime,
WIS_INACTIVITY_TIMEOUT integer,
WIS_RETRANSMISSION_INTERVAL integer,
WIS_ACKNOWLEDGEMENT_INTERVAL integer,
primary key (WIS_IDENTIFIER)
)
;
</pre>
</div>
<br />
<a name="systabswsrmsndr" />
<h4>21.6.11.2. Sender's Schema Objects</h4>
<p>Outgoing messages log table is used to keep the outgoing messages,
to track their state. It also keeps parameter to the soap request in order
to re-send if are not already accepted.</p>
<div>
<pre class="programlisting">
CREATE TABLE SYS_WSRM_OUT_MESSAGE_LOG (
OML_INDENTIFIER varchar, -- references SYS_WSRM_OUT_SEQUENCES.WOS_IDENTIFIER
OML_MESSAGE_ID int NOT NULL, -- unique per sequence
OML_EXPIRE_DATE datetime, -- when expires
OML_SEND_DATE timestamp NOT NULL, -- when is sent
OML_MESSAGE long varchar NOT NULL,-- soap call parameters
OML_STATE int,
primary key (OML_INDENTIFIER, OML_MESSAGE_ID)
)
;
</pre>
</div>
<p>Outgoing sequences table is used to keep parameters for outgoing
message sequences. It's used to persist their state.</p>
<div>
<pre class="programlisting">
CREATE TABLE SYS_WSRM_OUT_SEQUENCES (
WOS_IDENTIFIER varchar, -- sequence identifier
WOS_VERSION varchar,
WOS_DELIVERY_ASSURANCE varchar,
WOS_SEQUENCE_EXPIRATION datetime,
WOS_INACTIVITY_TIMEOUT integer,
WOS_RETRANSMISSION_INTERVAL integer,
WOS_ACKNOWLEDGEMENT_INTERVAL integer,
primary key (WOS_IDENTIFIER)
)
;
</pre>
</div>
<br />
<br />
<a name="wstschema" />
<h3>21.6.12. WS Trust</h3>
<div>
<pre class="programlisting">
create table WST_SERVER_ISSUER_TOKENS
(
WSK_TOKEN_TYPE varchar,
WSK_REQUEST_TYPE varchar,
WSK_APPLIES_TO varchar,
WSK_FROM varchar,
WSK_SERVICE_NAME varchar,
WSK_PORT_TYPE varchar,
WSK_TOKEN varchar,
PRIMARY KEY (WSK_TOKEN_TYPE, WSK_FROM, WSK_TOKEN)
)
;
</pre>
</div>
<br />
<a name="syncmlschema" />
<h3>21.6.13. SyncML Schema Objects</h3>
<a name="smlsdevices" />
<h4>21.6.13.1. SyncML Devices</h4>
<div>
<pre class="programlisting">
create table SYNC_DEVICES (
DEV_ID integer identity,
DEV_USER_ID integer, -- references WS.WS.SYS_DAV_USER(U_ID)
DEV_URI varchar,
DEV_MAN varchar, -- manufacturer
DEV_MOD varchar, -- model
DEV_OEM varchar, -- OEM
DEV_FWV varchar, -- firmware version
DEV_SWV varchar, -- software version
DEV_HWV varchar, -- hardware version
DEV_DEVID varchar, -- device id ; must be unique
DEV_DEVTYP varchar, -- device type
DEV_UTC integer, -- requires datetime in UTC
DEV_SUPP_LOB integer, -- supports large objects
DEV_SUPP_NOC integer, -- supports "number of changes"
primary key (DEV_URI) -- constraint foobar unique(DEV_USER_ID, DEV_URI)
)
;
</pre>
</div>
<br />
<a name="smlsmaps" />
<h4>21.6.13.2. SyncML Maps</h4>
<div>
<pre class="programlisting">
create table SYNC_MAPS (
MAP_DEV_ID integer, -- references SYNC_DEVICES(DEV_ID)
MAP_COL_ID int, -- references WS.WS.SYS_DAV_COL (COL_ID)
MAP_LUID varchar, -- local id (from client's view)
MAP_GUID varchar, -- references WS.WS.SYS_DAV_RES (RES_ID)
primary key (MAP_DEV_ID, MAP_LUID, MAP_GUID)
)
;
</pre>
</div>
<br />
<a name="smlssyncanchors" />
<h4>21.6.13.3. SyncML Sync Anchors</h4>
<div>
<pre class="programlisting">
create table SYNC_ANCHORS (
A_COL_ID integer, -- references WS.WS.SYS_DAV_COL(COL_ID)
A_DEV_ID integer, -- references SYNC_DEVICES(DEV_ID)
A_LAST_LOCAL datetime, -- last local anchor
A_LAST_REMOTE varchar, -- last remote anchor
A_NEXT_LOCAL datetime, -- last local anchor
A_NEXT_REMOTE varchar, -- last remote anchor
primary key (A_COL_ID, A_DEV_ID)
)
;
</pre>
</div>
<br />
<a name="smlssynclog" />
<h4>21.6.13.4. SyncML Sync Log</h4>
<div>
<pre class="programlisting">
create table SYNC_RPLOG (
RLOG_RES_ID int, -- references WS.WS.SYS_DAV_RES (RES_ID)
RLOG_RES_COL int, -- references WS.WS.SYS_DAV_COL (COL_ID)
DMLTYPE varchar, -- IUD - insert/update/delete
SNAPTIME datetime, -- timestamp
primary key (RLOG_RES_ID)
)
;
</pre>
</div>
<br />
<a name="smlsdevicesessions" />
<h4>21.6.13.5. SyncML Device's Sessions</h4>
<div>
<pre class="programlisting">
create table SYNC_SESSION
(
S_ID varchar, -- session ID (unique per device)
S_DEV varchar, -- device URI
S_DEV_ID int, -- references SYNC_DEVICES(DEV_ID)
S_UID int, -- user ID SYS_USERS.U_ID
S_LAST_MSG int, -- last massage no
S_LAST_CMD int, -- last command no
S_DATA long varbinary, -- internal use
S_TS timestamp, -- last used
S_AUTH int default 0, -- authenticated for this session
S_NONCE varchar default '', -- nonce value
S_INIT int default 1, -- init state flag
primary key (S_ID, S_DEV)
)
;
</pre>
</div>
<br />
<br />
<a name="views_information_schema" />
<h3>21.6.14. INFORMATION_SCHEMA views</h3>
<p>The INFORMATION_SCHEMA views are described in the SQL200n standard.
These views are defined in every qualifier and describe the objects in
that qualifier.
</p>
<a name="views_information_schema_tables" />
<h4>21.6.14.1. TABLES</h4>
<div>
<pre class="programlisting">
INFORMATION_SCHEMA.TABLES
TABLE_CATALOG VARCHAR(128),
TABLE_SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128),
TABLE_TYPE VARCHAR(128),
V_KEY_TABLE VARCHAR,
V_KEY_NAME VARCHAR,
V_KEY_ID INTEGER,
V_KEY_N_SIGNIFICANT SMALLINT,
V_KEY_CLUSTER_ON_ID SMALLINT,
V_KEY_IS_MAIN SMALLINT,
V_KEY_IS_OBJECT_ID SMALLINT,
V_KEY_IS_UNIQUE SMALLINT,
V_KEY_MIGRATE_TO INTEGER,
V_KEY_SUPER_ID INTEGER,
V_KEY_DECL_PARTS SMALLINT,
V_KEY_STORAGE VARCHAR,
V_KEY_OPTIONS ANY
</pre>
</div>
<br />
<a name="views_information_schema_columns" />
<h4>21.6.14.2. COLUMNS</h4>
<div>
<pre class="programlisting">
INFORMATION_SCHEMA.COLUMNS
TABLE_CATALOG VARCHAR(128),
TABLE_SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128),
COLUMN_NAME VARCHAR(128),
ORDINAL_POSITION INTEGER,
COLUMN_DEF VARCHAR,
NULLABLE VARCHAR (3),
DATA_TYPE VARCHAR(128),
CHARACTER_MAXIMUM_LENGTH INTEGER,
CHARACTER_OCTET_LENGTH INTEGER,
NUMERIC_PRECISION SMALLINT,
NUMERIC_PRECISION_RADIX SMALLINT,
NUMERIC_SCALE SMALLINT,
DOMAIN_CATALOG VARCHAR(128),
DOMAIN_SCHEMA VARCHAR(128),
DOMAIN_NAME VARCHAR(128),
IS_IDENTITY VARCHAR(3),
IDENTITY_GENERATION VARCHAR(10),
IDENTITY_START VARCHAR,
IDENTITY_INCREMENT VARCHAR,
V_TABLE VARCHAR,
V_COLUMN VARCHAR,
V_COL_ID INTEGER,
V_COL_DTP SMALLINT,
V_COL_PREC INTEGER,
V_COL_SCALE SMALLINT,
V_COL_DEFAULT VARCHAR,
V_COL_CHECK VARCHAR,
V_COL_NULLABLE SMALLINT,
V_COL_NTH SMALLINT,
V_COL_OPTIONS ANY,
V_KP_NTH SMALLINT
</pre>
</div>
<br />
<a name="views_information_schema_schemata" />
<h4>21.6.14.3. SCHEMATA</h4>
<div>
<pre class="programlisting">
INFORMATION_SCHEMA.SCHEMATA
CATALOG_NAME VARCHAR(128),
SCHEMA_NAME VARCHAR(128),
SCHEMA_OWNER VARCHAR(128)
</pre>
</div>
<br />
<a name="views_information_schema_CHECK_CONSTRAINTS" />
<h4>21.6.14.4. CHECK_CONSTRAINTS</h4>
<div>
<pre class="programlisting">
INFORMATION_SCHEMA.CHECK_CONSTRAINTS
CONSTRAINT_CATALOG VARCHAR(128),
CONSTRAINT_SCHEMA VARCHAR(128),
CONSTRAINT_NAME VARCHAR(128),
CHECK_CLAUSE VARCHAR,
V_C_TABLE VARCHAR(128),
V_C_ID INTEGER,
V_C_TEXT VARCHAR(4000),
V_C_MODE LONG VARCHAR
</pre>
</div>
<br />
<a name="views_information_schema_CHECK_CONSTRAINTS" />
<h4>21.6.14.5. CHECK_CONSTRAINTS</h4>
<div>
<pre class="programlisting">
INFORMATION_SCHEMA.CHECK_CONSTRAINTS
CONSTRAINT_CATALOG VARCHAR(128),
CONSTRAINT_SCHEMA VARCHAR(128),
CONSTRAINT_NAME VARCHAR(128),
CHECK_CLAUSE VARCHAR,
V_C_TABLE VARCHAR(128),
V_C_ID INTEGER,
V_C_TEXT VARCHAR(4000),
V_C_MODE LONG VARCHAR
</pre>
</div>
<br />
<a name="views_information_schema_COLUMN_DOMAIN_USAGE" />
<h4>21.6.14.6. COLUMN_DOMAIN_USAGE</h4>
<div>
<pre class="programlisting">
INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
DOMAIN_CATALOG VARCHAR(128),
DOMAIN_SCHEMA VARCHAR(128),
DOMAIN_NAME VARCHAR(128),
TABLE_CATALOG VARCHAR(128),
TABLE_SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128),
COLUMN_NAME VARCHAR(128)
</pre>
</div>
<br />
<a name="views_information_schema_COLUMN_PRIVILEGES" />
<h4>21.6.14.7. COLUMN_PRIVILEGES</h4>
<div>
<pre class="programlisting">
INFORMATION_SCHEMA.COLUMN_PRIVILEGES
GRANTOR VARCHAR(128),
GRANTEE VARCHAR(128),
TABLE_CATALOG VARCHAR(128),
TABLE_SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128),
COLUMN_NAME VARCHAR(128),
PRIVILEGE_TYPE VARCHAR(10),
IS_GRANTABLE VARCHAR (3),
V_G_USER INTEGER,
V_G_OP INTEGER,
V_G_OBJECT VARCHAR (386),
V_G_COL VARCHAR (386),
V_G_GRANTOR VARCHAR (128),
V_G_ADMIN_OPT VARCHAR (128)
</pre>
</div>
<br />
<a name="views_information_schema_KEY_COLUMN_USAGE" />
<h4>21.6.14.8. KEY_COLUMN_USAGE</h4>
<div>
<pre class="programlisting">
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
CONSTRAINT_CATALOG VARCHAR(128),
CONSTRAINT_SCHEMA VARCHAR(128),
CONSTRAINT_NAME VARCHAR(128),
TABLE_CATALOG VARCHAR(128),
TABLE_SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128),
COLUMN_NAME VARCHAR(128),
ORDINAL_POSITION SMALLINT,
V_KEY_IS_MAIN SMALLINT,
V_KEY_IS_UNIQUE SMALLINT
</pre>
</div>
<br />
<a name="views_information_schema_ROUTINES" />
<h4>21.6.14.9. ROUTINES</h4>
<div>
<pre class="programlisting">
INFORMATION_SCHEMA.ROUTINES
SPECIFIC_CATALOG VARCHAR(128),
SPECIFIC_SCHEMA VARCHAR(128),
SPECIFIC_NAME VARCHAR(128),
ROUTINE_CATALOG VARCHAR(128),
ROUTINE_SCHEMA VARCHAR(128),
ROUTINE_NAME VARCHAR(128),
MODULE_CATALOG VARCHAR(128),
MODULE_SCHEMA VARCHAR(128),
MODULE_NAME VARCHAR(128),
UDT_CATALOG VARCHAR(128),
UDT_SCHEMA VARCHAR(128),
UDT_NAME VARCHAR(128),
DATA_TYPE VARCHAR(128),
CHARACTER_MAXIMUM_LENGTH INTEGER,
CHARACTER_OCTET_LENGTH INTEGER,
COLLATION_CATALOG VARCHAR(128),
COLLATION_SCHEMA VARCHAR(128),
COLLATION_NAME VARCHAR(128),
CHARACTER_SET_CATALOG VARCHAR(128),
CHARACTER_SET_SCHEMA VARCHAR(128),
CHARACTER_SET_NAME VARCHAR(128),
NUMERIC_PRECISION SMALLINT,
NUMERIC_PRECISION_RADIX SMALLINT,
NUMERIC_SCALE SMALLINT,
DATETIME_PRECISION SMALLINT,
INTERVAL_TYPE VARCHAR(128),
INTERVAL_PRECISION SMALLINT,
TYPE_UDT_CATALOG VARCHAR(128),
TYPE_UDT_SCHEMA VARCHAR(128),
TYPE_UDT_NAME VARCHAR(128),
SCOPE_CATALOG VARCHAR(128),
SCOPE_SCHEMA VARCHAR(128),
SCOPE_NAME VARCHAR(128),
MAXIMUM_CARDINALITY INTEGER,
DTD_IDENTIFIER VARCHAR(128),
ROUTINE_BODY VARCHAR(30),
ROUTINE_DEFINTION VARCHAR,
EXTERNAL_NAME VARCHAR(128),
EXTERNAL_LANGUAGE VARCHAR(30),
PARAMETER_STYLE VARCHAR(30),
IS_DETERMINISTIC VARCHAR(10),
SQL_DATA_ACCESS VARCHAR(30),
IS_NULL_CALL VARCHAR(10),
SQL_PATH VARCHAR(128),
SCHEMA_LEVEL_ROUTINE VARCHAR(10),
MAX_DYNAMIC_RESULT_SETS SMALLINT,
IS_USER_DEFINED_CAST VARCHAR(10),
IS_IMPLICITLY_INVOCABLE VARCHAR(10),
CREATED DATETIME,
LAST_ALTERED DATETIME
</pre>
</div>
<br />
<a name="views_information_schema_PARAMETERS" />
<h4>21.6.14.10. PARAMETERS</h4>
<div>
<pre class="programlisting">
INFORMATION_SCHEMA.PARAMETERS
SPECIFIC_CATALOG VARCHAR(128),
SPECIFIC_SCHEMA VARCHAR(128),
SPECIFIC_NAME VARCHAR(128),
ORDINAL_POSITION INTEGER,
PARAMETER_MODE VARCHAR(10),
IS_RESULT VARCHAR(10),
AS_LOCATOR VARCHAR(10),
PARAMETER_NAME VARCHAR(128),
DATA_TYPE VARCHAR(128),
CHARACTER_MAXIMUM_LENGTH INTEGER,
CHARACTER_OCTET_LENGTH INTEGER,
COLLATION_CATALOG VARCHAR(128),
COLLATION_SCHEMA VARCHAR(128),
COLLATION_NAME VARCHAR(128),
CHARACTER_SET_CATALOG VARCHAR(128),
CHARACTER_SET_SCHEMA VARCHAR(128),
CHARACTER_SET_NAME VARCHAR(128),
NUMERIC_PRECISION SMALLINT,
NUMERIC_PRECISION_RADIX SMALLINT,
NUMERIC_SCALE SMALLINT,
DATETIME_PRECISION SMALLINT,
INTERVAL_TYPE VARCHAR(128),
INTERVAL_PRECISION SMALLINT,
USER_DEFINED_TYPE_CATALOG VARCHAR(128),
USER_DEFINED_TYPE_SCHEMA VARCHAR(128),
USER_DEFINED_TYPE_NAME VARCHAR(128),
SCOPE_CATALOG VARCHAR(128),
SCOPE_SCHEMA VARCHAR(128),
SCOPE_NAME VARCHAR(128)
</pre>
</div>
<br />
<a name="views_information_schema_REFERENTIAL_CONSTRAINTS" />
<h4>21.6.14.11. REFERENTIAL_CONSTRAINTS</h4>
<div>
<pre class="programlisting">
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
CONSTRAINT_CATALOG VARCHAR(128),
CONSTRAINT_SCHEMA VARCHAR(128),
CONSTRAINT_NAME VARCHAR(128),
UNIQUE_CONSTRAINT_CATALOG VARCHAR(128),
UNIQUE_CONSTRAINT_SCHEMA VARCHAR(128),
UNIQUE_CONSTRAINT_NAME VARCHAR(128),
MATCH_OPTION VARCHAR(7),
UPDATE_RULE VARCHAR(9),
DELETE_RULE VARCHAR(9),
V_FK_TABLE VARCHAR(128)
</pre>
</div>
<br />
<a name="views_information_schema_TABLE_CONSTRAINTS" />
<h4>21.6.14.12. TABLE_CONSTRAINTS</h4>
<div>
<pre class="programlisting">
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
CONSTRAINT_CATALOG VARCHAR(128),
CONSTRAINT_SCHEMA VARCHAR(128),
CONSTRAINT_NAME VARCHAR(128),
TABLE_CATALOG VARCHAR(128),
TABLE_SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128),
CONSTRAINT_TYPE VARCHAR(11),
IS_DEFERRABLE VARCHAR(2),
INITIALLY_DEFERRED VARCHAR(2)
</pre>
</div>
<br />
<a name="views_information_schema_TABLE_PRIVILEGES" />
<h4>21.6.14.13. TABLE_PRIVILEGES</h4>
<div>
<pre class="programlisting">
INFORMATION_SCHEMA.TABLE_PRIVILEGES
GRANTOR VARCHAR(128),
GRANTEE VARCHAR(128),
TABLE_CATALOG VARCHAR(128),
TABLE_SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128),
PRIVILEGE_TYPE VARCHAR(10),
IS_GRANTABLE VARCHAR (3),
V_G_USER INTEGER,
V_G_OP INTEGER,
V_G_OBJECT VARCHAR (386),
V_G_GRANTOR VARCHAR (386)
</pre>
</div>
<br />
<a name="views_information_schema_VIEWS" />
<h4>21.6.14.14. VIEWS</h4>
<div>
<pre class="programlisting">
INFORMATION_SCHEMA.VIEWS
TABLE_CATALOG VARCHAR(128),
TABLE_SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128),
VIEW_DEFINITION VARCHAR,
CHECK_OPTION VARCHAR(7),
IS_UPDATABLE VARCHAR(3)
</pre>
</div>
<br />
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="support.html" title="Product Support">Previous</a>
<br />Product Support</td>
<td align="center" width="34%">
<a href="appendixa.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="apndxregexp.html" title="Basic Syntax of Regular Expressions">Next</a>
<br />Basic Syntax of Regular Expressions</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
891
