Project

General

Profile

Revision 772

Added by bojilova almost 20 years ago

cleared the COLLATION specifiers on column definitions:
it is new in SQL Server 2000 and not supported in its earlier versions

View differences:

xmltables-sqlserver.sql
31 31
 * ACL -- table to store ACL for XML documents by principals
32 32
 */
33 33
CREATE TABLE [dbo].[xml_access] (
34
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
35
  [accessfileid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
36
  [principal_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
37
  [permission] [int] NULL ,
38
  [perm_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
39
  [perm_order] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
40
  [begin_time] [datetime] NULL ,
41
  [end_time] [datetime] NULL ,
42
  [ticket_count] [int] NULL 
34
  [docid]		[varchar] (250) NULL ,
35
  [accessfileid]	[varchar] (250) NULL ,
36
  [principal_name]	[varchar] (100) NULL ,
37
  [permission]		[int] NULL ,
38
  [perm_type]		[varchar] (50) NULL ,
39
  [perm_order]		[varchar] (50) NULL ,
40
  [begin_time]		[datetime] NULL ,
41
  [end_time]		[datetime] NULL ,
42
  [ticket_count]	[int] NULL 
43 43
) ON [PRIMARY]
44 44
GO
45 45

  
......
47 47
 * XML Catalog -- table to store all external sources for XML documents
48 48
 */
49 49
CREATE TABLE [dbo].[xml_catalog] (
50
  [catalog_id] [bigint] IDENTITY (1, 1) NOT NULL ,
51
  [entry_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
52
  [source_doctype] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
53
  [target_doctype] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
54
  [public_id] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
55
  [system_id] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
50
  [catalog_id]		[bigint] IDENTITY (1, 1) NOT NULL ,
51
  [entry_type]		[varchar] (50) NULL ,
52
  [source_doctype]	[varchar] (250) NULL ,
53
  [target_doctype]	[varchar] (250) NULL ,
54
  [public_id]		[varchar] (250) NULL ,
55
  [system_id]		[varchar] (512) NULL 
56 56
) ON [PRIMARY]
57 57
GO
58 58

  
......
60 60
 * Documents -- table to store XML documents
61 61
 */
62 62
CREATE TABLE [dbo].[xml_documents] (
63
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
64
  [rootnodeid] [bigint] NULL ,
65
  [docname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
66
  [doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
67
  [user_owner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
68
  [user_updated] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
69
  [server_location] [bigint] NULL ,
70
  [rev] [int] NULL ,
71
  [date_created] [datetime] NULL ,
72
  [date_updated] [datetime] NULL ,
73
  [public_access] [bit] NULL ,
74
  [catalog_id] [bigint] NULL 
63
  [docid]		[varchar] (250) NOT NULL ,
64
  [rootnodeid]		[bigint] NULL ,
65
  [docname]		[varchar] (100) NULL ,
66
  [doctype]		[varchar] (100) NULL ,
67
  [user_owner]		[varchar] (100) NULL ,
68
  [user_updated]	[varchar] (100) NULL ,
69
  [server_location]	[bigint] NULL ,
70
  [rev]			[int] NULL ,
71
  [date_created]	[datetime] NULL ,
72
  [date_updated]	[datetime] NULL ,
73
  [public_access]	[bit] NULL ,
74
  [catalog_id]		[bigint] NULL 
75 75
) ON [PRIMARY]
76 76
GO
77 77

  
......
80 80
 * quick searching in structured searches
81 81
 */
82 82
CREATE TABLE [dbo].[xml_index] (
83
  [nodeid] [bigint] NOT NULL ,
84
  [path] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
85
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
86
  [doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
87
  [parentnodeid] [bigint] NULL 
83
  [nodeid]		[bigint] NOT NULL ,
84
  [path]		[varchar] (200) NOT NULL ,
85
  [docid]		[varchar] (250) NULL ,
86
  [doctype]		[varchar] (100) NULL ,
87
  [parentnodeid]	[bigint] NULL 
88 88
) ON [PRIMARY]
89 89
GO
90 90

  
......
92 92
 * Nodes -- table to store XML Nodes (both elements and attributes)
93 93
 */
94 94
CREATE TABLE [dbo].[xml_nodes] (
95
  [nodeid] [bigint] IDENTITY (1, 1) NOT NULL ,
96
  [nodeindex] [int] NULL ,
97
  [nodetype] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
98
  [nodename] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
99
  [nodedata] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
100
  [parentnodeid] [bigint] NULL ,
101
  [rootnodeid] [bigint] NULL ,
102
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
103
  [date_created] [datetime] NULL ,
104
  [date_updated] [datetime] NULL 
95
  [nodeid]		[bigint] IDENTITY (1, 1) NOT NULL ,
96
  [nodeindex]		[int] NULL ,
97
  [nodetype]		[varchar] (20) NULL ,
98
  [nodename]		[varchar] (250) NULL ,
99
  [nodedata]		[varchar] (4000) NULL ,
100
  [parentnodeid]	[bigint] NULL ,
101
  [rootnodeid]		[bigint] NULL ,
102
  [docid]		[varchar] (250) NULL ,
103
  [date_created]	[datetime] NULL ,
104
  [date_updated]	[datetime] NULL 
105 105
) ON [PRIMARY]
106 106
GO
107 107

  
......
109 109
 * Relations -- table to store relations of form <subject,relationship,object>
110 110
 */
111 111
CREATE TABLE [dbo].[xml_relation] (
112
  [relationid] [bigint] IDENTITY (1, 1) NOT NULL ,
113
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
114
  [packagetype] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
115
  [subject] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
116
  [subdoctype] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
117
  [relationship] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
118
  [object] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
119
  [objdoctype] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
112
  [relationid]		[bigint] IDENTITY (1, 1) NOT NULL ,
113
  [docid]		[varchar] (250) NULL ,
114
  [packagetype]		[varchar] (250) NULL ,
115
  [subject]		[varchar] (250) NOT NULL ,
116
  [subdoctype]		[varchar] (128) NULL ,
117
  [relationship]	[varchar] (128) NOT NULL ,
118
  [object]		[varchar] (250) NOT NULL ,
119
  [objdoctype]		[varchar] (128) NULL 
120 120
) ON [PRIMARY]
121 121
GO
122 122

  
......
124 124
 * Replication -- table to store servers that metacat is replicated to
125 125
 */
126 126
CREATE TABLE [dbo].[xml_replication] (
127
  [serverid] [bigint] IDENTITY (1, 1) NOT NULL ,
128
  [server] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
129
  [last_checked] [datetime] NULL ,
130
  [replicate] [bit] NULL 
127
  [serverid]		[bigint] IDENTITY (1, 1) NOT NULL ,
128
  [server]		[varchar] (512) NULL ,
129
  [last_checked]	[datetime] NULL ,
130
  [replicate]		[bit] NULL 
131 131
) ON [PRIMARY]
132 132
GO
133 133
     
......
140 140
 *                    or DELETE
141 141
 */
142 142
CREATE TABLE [dbo].[xml_revisions] (
143
  [revisionid] [bigint] IDENTITY (1, 1) NOT NULL ,
144
  [docid] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
145
  [rootnodeid] [bigint] NULL ,
146
  [docname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
147
  [doctype] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
148
  [user_owner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
149
  [user_updated] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
150
  [server_location] [bigint] NULL ,
151
  [rev] [int] NULL ,
152
  [date_created] [datetime] NULL ,
153
  [date_updated] [datetime] NULL ,
154
  [public_access] [bit] NULL ,
155
  [catalog_id] [bigint] NULL 
143
  [revisionid]		[bigint] IDENTITY (1, 1) NOT NULL ,
144
  [docid]		[varchar] (250) NULL ,
145
  [rootnodeid]		[bigint] NULL ,
146
  [docname]		[varchar] (100) NULL ,
147
  [doctype]		[varchar] (100) NULL ,
148
  [user_owner]		[varchar] (100) NULL ,
149
  [user_updated]	[varchar] (100) NULL ,
150
  [server_location]	[bigint] NULL ,
151
  [rev]			[int] NULL ,
152
  [date_created]	[datetime] NULL ,
153
  [date_updated]	[datetime] NULL ,
154
  [public_access]	[bit] NULL ,
155
  [catalog_id]		[bigint] NULL 
156 156
) ON [PRIMARY]
157 157
GO                                
158 158

  
......
160 160
 * Table used as Unique ID generator for the uniqueid part of Accession#
161 161
 */
162 162
CREATE TABLE [dbo].[accession_number] (
163
  [uniqueid] [int] IDENTITY (1, 1) NOT NULL,
164
  [site_code] [varchar] (100),
165
  [date_created] [datetime],
163
  [uniqueid]		[int] IDENTITY (1, 1) NOT NULL,
164
  [site_code]		[varchar] (100),
165
  [date_created]	[datetime],
166 166
  CONSTRAINT [PK_accession_number] PRIMARY KEY CLUSTERED ([uniqueid])
167 167
) ON [PRIMARY]
168 168
GO

Also available in: Unified diff