Project

General

Profile

Revision 759

Added by bojilova almost 20 years ago

several changes were needed related to the change of the unique id generation and use

View differences:

xmltables-sqlserver.sql
17 17
/*
18 18
 * Drop all of the objects in proper order
19 19
 */
20
if exists (select * from dbo.sysobjects 
21
 where id = object_id(N'[dbo].[FK_xml_documents_xml_catalog]') 
22
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
23
ALTER TABLE [dbo].[xml_documents] DROP CONSTRAINT FK_xml_documents_xml_catalog
24
GO
20
drop table [dbo].[xml_index];
21
drop table [dbo].[xml_access];
22
drop table [dbo].[xml_revisions];
23
drop table [dbo].[xml_relation];
24
drop table [dbo].[xml_documents];
25
drop table [dbo].[xml_nodes];
26
drop table [dbo].[xml_replication];
27
drop table [dbo].[xml_catalog];
28
drop table [dbo].[accession_number];
25 29

  
26
if exists (select * from dbo.sysobjects
27
 where id = object_id(N'[dbo].[FK_xml_revisions_xml_catalog]')
28
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
29
ALTER TABLE [dbo].[xml_revisions] DROP CONSTRAINT FK_xml_revisions_xml_catalog
30
GO
31

  
32
if exists (select * from dbo.sysobjects
33
 where id = object_id(N'[dbo].[FK_xml_access_xml_documents]')
34
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
35
ALTER TABLE [dbo].[xml_access] DROP CONSTRAINT FK_xml_access_xml_documents
36
GO
37

  
38
if exists (select * from dbo.sysobjects
39
 where id = object_id(N'[dbo].[FK_xml_index_xml_documents]')
40
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
41
ALTER TABLE [dbo].[xml_index] DROP CONSTRAINT FK_xml_index_xml_documents
42
GO
43

  
44
if exists (select * from dbo.sysobjects
45
 where id = object_id(N'[dbo].[FK_xml_relation_xml_documents]')
46
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
47
ALTER TABLE [dbo].[xml_relation] DROP CONSTRAINT FK_xml_relation_xml_documents
48
GO
49

  
50
if exists (select * from dbo.sysobjects
51
 where id = object_id(N'[dbo].[FK_xml_documents_xml_nodes]')
52
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
53
ALTER TABLE [dbo].[xml_documents] DROP CONSTRAINT FK_xml_documents_xml_nodes
54
GO
55

  
56
if exists (select * from dbo.sysobjects
57
 where id = object_id(N'[dbo].[FK_xml_index_xml_nodes]')
58
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
59
ALTER TABLE [dbo].[xml_index] DROP CONSTRAINT FK_xml_index_xml_nodes
60
GO
61

  
62
if exists (select * from dbo.sysobjects
63
 where id = object_id(N'[dbo].[FK_xml_nodes_parentnodeid]')
64
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
65
ALTER TABLE [dbo].[xml_nodes] DROP CONSTRAINT FK_xml_nodes_parentnodeid
66
GO
67

  
68
if exists (select * from dbo.sysobjects
69
 where id = object_id(N'[dbo].[FK_xml_nodes_rootnodeid]')
70
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
71
ALTER TABLE [dbo].[xml_nodes] DROP CONSTRAINT FK_xml_nodes_rootnodeid
72
GO
73

  
74
if exists (select * from dbo.sysobjects
75
 where id = object_id(N'[dbo].[FK_xml_revisions_xml_nodes]')
76
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
77
ALTER TABLE [dbo].[xml_revisions] DROP CONSTRAINT FK_xml_revisions_xml_nodes
78
GO
79

  
80
if exists (select * from dbo.sysobjects
81
 where id = object_id(N'[dbo].[FK_xml_documents_xml_replication]')
82
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
83
ALTER TABLE [dbo].[xml_documents] 
84
DROP CONSTRAINT FK_xml_documents_xml_replication
85
GO
86

  
87
if exists (select * from dbo.sysobjects
88
 where id = object_id(N'[dbo].[FK_xml_revisions_xml_replication]')
89
 and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
90
ALTER TABLE [dbo].[xml_revisions] 
91
DROP CONSTRAINT FK_xml_revisions_xml_replication
92
GO
93

  
94
if exists (select * from dbo.sysobjects
95
 where id = object_id(N'[dbo].[xml_access]')
96
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
97
drop table [dbo].[xml_access]
98
GO
99

  
100
if exists (select * from dbo.sysobjects
101
 where id = object_id(N'[dbo].[xml_catalog]')
102
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
103
drop table [dbo].[xml_catalog]
104
GO
105

  
106
if exists (select * from dbo.sysobjects
107
 where id = object_id(N'[dbo].[xml_documents]')
108
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
109
drop table [dbo].[xml_documents]
110
GO
111

  
112
if exists (select * from dbo.sysobjects
113
 where id = object_id(N'[dbo].[xml_index]')
114
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
115
drop table [dbo].[xml_index]
116
GO
117

  
118
if exists (select * from dbo.sysobjects
119
 where id = object_id(N'[dbo].[xml_nodes]')
120
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
121
drop table [dbo].[xml_nodes]
122
GO
123

  
124
if exists (select * from dbo.sysobjects
125
 where id = object_id(N'[dbo].[xml_relation]')
126
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
127
drop table [dbo].[xml_relation]
128
GO
129

  
130
if exists (select * from dbo.sysobjects
131
 where id = object_id(N'[dbo].[xml_replication]')
132
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
133
drop table [dbo].[xml_replication]
134
GO
135

  
136
if exists (select * from dbo.sysobjects
137
 where id = object_id(N'[dbo].[xml_revisions]')
138
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
139
drop table [dbo].[xml_revisions]
140
GO
141

  
142 30
/* 
143 31
 * ACL -- table to store ACL for XML documents by principals
144 32
 */
......
262 150
  [public_access] [bit] NULL ,
263 151
  [catalog_id] [bigint] NULL 
264 152
) ON [PRIMARY]
153
GO                                
154

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

  
267 166
/* 

Also available in: Unified diff