SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
/**********************************************/
IF OBJECT_ID('dbo.getOidArc', 'FN') IS NOT NULL /*Backwards compatibility*/
DROP FUNCTION /*IF EXISTS*/ [dbo].[getOidArc];
GO
CREATE FUNCTION [dbo].[getOidArc] (@strList VARCHAR(512), @maxArcLen INT, @occurence INT)
RETURNS VARCHAR(512) AS
BEGIN
DECLARE @intPos INT
DECLARE @cnt INT
SET @cnt = 0
IF SUBSTRING(@strList, 1, 4) <> 'oid:'
BEGIN
RETURN ''
END
SET @strList = RIGHT(@strList, LEN(@strList)-4)
WHILE CHARINDEX('.',@strList) > 0
BEGIN
SET @intPos=CHARINDEX('.',@strList)
SET @cnt = @cnt + 1
IF @cnt = @occurence
BEGIN
SET @strList = LEFT(@strList,@intPos-1)
RETURN REPLICATE('0', @maxArcLen-len(@strList)) + @strList
END
SET @strList = RIGHT(@strList, LEN(@strList)-@intPos)
END
IF LEN(@strList) > 0
BEGIN
SET @cnt = @cnt + 1
IF @cnt = @occurence
BEGIN
RETURN REPLICATE('0', @maxArcLen-len(@strList)) + @strList
END
END
RETURN REPLICATE('0', @maxArcLen)
END
GO
/**********************************************/
IF OBJECT_ID('dbo.config', 'U') IS NOT NULL /*Backwards compatibility*/
DROP TABLE /*IF EXISTS*/ [dbo].[config];
CREATE TABLE [dbo].[config](
[name] [VARCHAR](50) NOT NULL,
[VALUE] [text] NOT NULL,
[description] [VARCHAR](255) NULL,
[protected] [bit] NOT NULL DEFAULT ('0'),
[visible] [bit] NOT NULL DEFAULT ('0'),
CONSTRAINT [PK_config] PRIMARY KEY CLUSTERED
(
[name] ASC
)
)
GO
/**********************************************/
IF OBJECT_ID('dbo.asn1id', 'U') IS NOT NULL /*Backwards compatibility*/
DROP TABLE /*IF EXISTS*/ [dbo].[asn1id];
CREATE TABLE [dbo].[asn1id](
[lfd] [INT] IDENTITY(1,1) NOT NULL,
[oid] [VARCHAR](255) NOT NULL,
[name] [VARCHAR](255) NOT NULL,
[standardized] [bit] NOT NULL CONSTRAINT [DF__asn1id__standardized] DEFAULT ('0'),
[well_known] [bit] NOT NULL CONSTRAINT [DF__asn1id__well_known] DEFAULT ('0'),
CONSTRAINT [PK_asn1id] PRIMARY KEY CLUSTERED
(
[lfd] ASC
),
INDEX [IX_asn1id_oid_name] NONCLUSTERED
(
[oid] ASC,
[name] ASC
)
)
GO
/**********************************************/
IF OBJECT_ID('dbo.iri', 'U') IS NOT NULL /*Backwards compatibility*/
DROP TABLE /*IF EXISTS*/ [dbo].[iri];
CREATE TABLE [dbo].[iri](
[lfd] [INT] IDENTITY(1,1) NOT NULL,
[oid] [VARCHAR](255) NOT NULL,
[name] [VARCHAR](255) NOT NULL,
[longarc] [bit] NOT NULL CONSTRAINT [DF__iri__longarc] DEFAULT ('0'),
[well_known] [bit] NOT NULL CONSTRAINT [DF__iri__well_known] DEFAULT ('0'),
CONSTRAINT [PK_iri] PRIMARY KEY CLUSTERED
(
[lfd] ASC
),
INDEX [IX_iri_oid_name] NONCLUSTERED
(
[oid] ASC,
[name] ASC
)
)
GO
/**********************************************/
IF OBJECT_ID('dbo.objects', 'U') IS NOT NULL /*Backwards compatibility*/
DROP TABLE /*IF EXISTS*/ [dbo].[objects];
CREATE TABLE [dbo].[objects](
[id] [VARCHAR](255) NOT NULL,
[parent] [VARCHAR](255) NULL,
[title] [VARCHAR](255) NULL,
[description] [text] NULL,
[ra_email] [VARCHAR](100) NULL,
[confidential] [bit] NOT NULL,
[created] [datetime] NULL,
[updated] [datetime] NULL,
[comment] [VARCHAR](255) NULL,
CONSTRAINT [PK_objects] PRIMARY KEY CLUSTERED
(
[id] ASC
),
INDEX [IX_objects_parent] NONCLUSTERED
(
[parent] ASC
),
INDEX [IX_objects_ra_email] NONCLUSTERED
(
[ra_email] ASC
)
)
GO
/**********************************************/
IF OBJECT_ID('dbo.ra', 'U') IS NOT NULL /*Backwards compatibility*/
DROP TABLE /*IF EXISTS*/ [dbo].[ra];
CREATE TABLE [dbo].[ra](
[ra_id] [INT] IDENTITY(1,1) NOT NULL,
[email] [VARCHAR](100) NOT NULL,
[ra_name] [VARCHAR](100) NULL,
[personal_name] [VARCHAR](100) NULL,
[organization] [VARCHAR](100) NULL,
[office] [VARCHAR](100) NULL,
[street] [VARCHAR](100) NULL,
[zip_town] [VARCHAR](100) NULL,
[country] [VARCHAR](100) NULL,
[phone] [VARCHAR](100) NULL,
[mobile] [VARCHAR](100) NULL,
[fax] [VARCHAR](100) NULL,
[privacy] [bit] NOT NULL CONSTRAINT [DF__ra__privacy] DEFAULT ('0'),
[salt] [VARCHAR](100) NULL,
[authkey] [VARCHAR](100) NULL,
[registered] [datetime] NULL,
[updated] [datetime] NULL,
[last_login] [datetime] NULL,
CONSTRAINT [PK_ra] PRIMARY KEY CLUSTERED
(
[ra_id] ASC
),
CONSTRAINT [IX_ra_email] UNIQUE (
[email] ASC
)
)
GO
/**********************************************/
IF OBJECT_ID('dbo.log', 'U') IS NOT NULL /*Backwards compatibility*/
DROP TABLE /*IF EXISTS*/ [dbo].[log];
CREATE TABLE [dbo].[log](
[id] [INT] IDENTITY(1,1) NOT NULL,
[unix_ts] [BIGINT] NOT NULL,
[addr] [VARCHAR](45) NOT NULL,
[event] [text] NOT NULL,
CONSTRAINT [PK_log] PRIMARY KEY CLUSTERED
(
[id] ASC
)
)
GO
/**********************************************/
IF OBJECT_ID('dbo.log_user', 'U') IS NOT NULL /*Backwards compatibility*/
DROP TABLE /*IF EXISTS*/ [dbo].[log_user];
CREATE TABLE [dbo].[log_user](
[id] [INT] IDENTITY(1,1) NOT NULL,
[log_id] [INT] NOT NULL,
[username] [VARCHAR](255) NOT NULL,
[severity] [INT] NOT NULL,
CONSTRAINT [PK_log_user] PRIMARY KEY CLUSTERED
(
[id] ASC
),
INDEX [IX_log_user_log_id] NONCLUSTERED
(
[log_id] ASC
),
INDEX [IX_log_user_username] NONCLUSTERED
(
[username] ASC
),
CONSTRAINT [IX_log_user_log_id_username] UNIQUE
(
[log_id],
[username]
)
)
GO
/**********************************************/
IF OBJECT_ID('dbo.log_object', 'U') IS NOT NULL /*Backwards compatibility*/
DROP TABLE /*IF EXISTS*/ [dbo].[log_object];
CREATE TABLE [dbo].[log_object](
[id] [INT] IDENTITY(1,1) NOT NULL,
[log_id] [INT] NOT NULL,
[object] [VARCHAR](255) NOT NULL,
[severity] [INT] NOT NULL,
CONSTRAINT [PK_log_object] PRIMARY KEY CLUSTERED
(
[id] ASC
),
INDEX [IX_log_object_log_id] NONCLUSTERED
(
[log_id] ASC
),
INDEX [IX_log_object_object] NONCLUSTERED
(
[object] ASC
),
CONSTRAINT [IX_log_object_log_id_object] UNIQUE
(
[log_id],
[object]
)
)
GO
/****** Set database version ******/
INSERT INTO [config] (name, description, VALUE, protected, visible) VALUES ('database_version', 'Version of the database tables', '205', '1', '0');
SET ANSI_PADDING OFF
GO