Revision 1740
Added by Jing Tao over 20 years ago
| src/xmltables-sqlserver.sql | ||
|---|---|---|
| 26 | 26 |
drop table [dbo].[xml_replication]; |
| 27 | 27 |
drop table [dbo].[xml_catalog]; |
| 28 | 28 |
drop table [dbo].[accession_number]; |
| 29 |
drop table [dbo].[xml_accesssubtree]; |
|
| 29 | 30 |
|
| 30 | 31 |
/* |
| 31 | 32 |
* ACL -- table to store ACL for XML documents by principals |
| ... | ... | |
| 40 | 41 |
[begin_time] [datetime] NULL , |
| 41 | 42 |
[end_time] [datetime] NULL , |
| 42 | 43 |
[ticket_count] [int] NULL |
| 44 |
[subtreeid] [varchar] (250) NULL , |
|
| 45 |
[startnodeid] [bigint] NULL , |
|
| 46 |
[endnodeid] [bigint] NULL |
|
| 43 | 47 |
) ON [PRIMARY] |
| 44 | 48 |
GO |
| 45 | 49 |
|
| 46 | 50 |
/* |
| 51 |
* accesssubtree -- table to store access subtree info |
|
| 52 |
*/ |
|
| 53 |
CREATE TABLE [dbo].[xml_accesssubtree] ( |
|
| 54 |
[docid] [varchar] (250) NULL , |
|
| 55 |
[rev] [int] NULL , |
|
| 56 |
[controllevel] [varchar] (250) NULL , |
|
| 57 |
[subtreeid] [varchar] (250) NULL , |
|
| 58 |
[startnodeid] [bigint] NULL , |
|
| 59 |
[endnodeid] [bigint] NULL |
|
| 60 |
) |
|
| 61 |
GO |
|
| 62 |
|
|
| 63 |
/* |
|
| 47 | 64 |
* XML Catalog -- table to store all external sources for XML documents |
| 48 | 65 |
*/ |
| 49 | 66 |
CREATE TABLE [dbo].[xml_catalog] ( |
| ... | ... | |
| 127 | 144 |
[serverid] [bigint] IDENTITY (1, 1) NOT NULL , |
| 128 | 145 |
[server] [varchar] (512) NULL , |
| 129 | 146 |
[last_checked] [datetime] NULL , |
| 130 |
[replicate] [bit] NULL |
|
| 147 |
[replicate] [bit] NULL |
|
| 148 |
[datareplicate] [bit] NULL |
|
| 149 |
[hub] [bit] NULL |
|
| 131 | 150 |
) ON [PRIMARY] |
| 132 | 151 |
GO |
| 133 | 152 |
|
| 134 | 153 |
set identity_insert xml_replication on |
| 135 |
INSERT INTO xml_replication (serverid, server, replicate) VALUES ('1', 'localhost', '0');
|
|
| 154 |
INSERT INTO xml_replication (serverid, server, replicate, datareplicate, hub) VALUES ('1', 'localhost', '0', '0', '0');
|
|
| 136 | 155 |
set identity_insert xml_replication off |
| 137 | 156 |
|
| 138 | 157 |
/* |
| ... | ... | |
| 238 | 257 |
CONSTRAINT [DF_xml_documents_rev] DEFAULT (1) FOR [rev] |
| 239 | 258 |
GO |
| 240 | 259 |
|
| 260 |
ALTER TABLE [dbo].[xml_accesssubtree] WITH NOCHECK ADD |
|
| 261 |
CONSTRAINT [DF_xml_accesssubtree_rev] DEFAULT (1) FOR [rev] |
|
| 262 |
GO |
|
| 263 |
|
|
| 241 | 264 |
ALTER TABLE [dbo].[xml_relation] WITH NOCHECK ADD |
| 242 | 265 |
CONSTRAINT [IX_xml_relation] UNIQUE NONCLUSTERED |
| 243 | 266 |
( |
| 244 |
[subject], |
|
| 267 |
[docid], |
|
| 268 |
[subject], |
|
| 245 | 269 |
[relationship], |
| 246 | 270 |
[object] |
| 247 | 271 |
) ON [PRIMARY] |
| ... | ... | |
| 263 | 287 |
CREATE INDEX [IX3_xml_nodes] ON [dbo].[xml_nodes]([nodename]) ON [PRIMARY] |
| 264 | 288 |
GO |
| 265 | 289 |
|
| 290 |
ALTER TABLE [dbo].[xml_accesssubtree] ADD |
|
| 291 |
CONSTRAINT [FK_xml_accesssubtree_xml_documents] FOREIGN KEY |
|
| 292 |
( |
|
| 293 |
[docid] |
|
| 294 |
) REFERENCES [dbo].[xml_documents] ( |
|
| 295 |
[docid] |
|
| 296 |
) |
|
| 297 |
GO |
|
| 298 |
|
|
| 266 | 299 |
ALTER TABLE [dbo].[xml_access] ADD |
| 267 | 300 |
CONSTRAINT [FK_xml_access_xml_documents] FOREIGN KEY |
| 268 | 301 |
( |
| ... | ... | |
| 345 | 378 |
) REFERENCES [dbo].[xml_nodes] ( |
| 346 | 379 |
[nodeid] |
| 347 | 380 |
), |
| 348 |
CONSTRAINT [FK_xml_revisions_xml_r |
|
| 349 |
eplication] FOREIGN KEY |
|
| 381 |
CONSTRAINT [FK_xml_revisions_xml_replication] FOREIGN KEY |
|
| 350 | 382 |
( |
| 351 | 383 |
[server_location] |
| 352 | 384 |
) REFERENCES [dbo].[xml_replication] ( |
Also available in: Unified diff
Update script for sql: change a unique constraint in xml_relation table. Adding new fields in xml_replication and xml_access table. Adding new talbe xml_accesssubtree.