Revision 2109
Added by Duane Costa about 20 years ago
| xmltables-sqlserver.sql | ||
|---|---|---|
| 27 | 27 |
drop table [dbo].[xml_replication]; |
| 28 | 28 |
drop table [dbo].[xml_catalog]; |
| 29 | 29 |
drop table [dbo].[accession_number]; |
| 30 |
drop table [dbo].[harvest_site_schedule]; |
|
| 31 |
drop table [dbo].[harvest_log]; |
|
| 32 |
drop table [dbo].[harvest_detail_log]; |
|
| 30 | 33 |
|
| 31 | 34 |
/* |
| 32 | 35 |
* ACL -- table to store ACL for XML documents by principals |
| ... | ... | |
| 188 | 191 |
GO |
| 189 | 192 |
|
| 190 | 193 |
/* |
| 194 |
* harvest_site_schedule -- table to store harvest sites and schedule info |
|
| 195 |
*/ |
|
| 196 |
CREATE TABLE [dbo].[harvest_site_schedule] ( |
|
| 197 |
[site_schedule_id] [int] IDENTITY (1, 1) NOT NULL , |
|
| 198 |
[documentlisturl] [varchar] (250) NOT NULL , |
|
| 199 |
[ldapdn] [varchar] (250) NOT NULL , |
|
| 200 |
[datenextharvest] [datetime] NULL , |
|
| 201 |
[datelastharvest] [datetime] NULL , |
|
| 202 |
[updatefrequency] [int] NULL , |
|
| 203 |
[unit] [varchar] (50) NULL , |
|
| 204 |
[contact_email] [varchar] (50) NULL , |
|
| 205 |
[ldappwd] [varchar] (20) NOT NULL |
|
| 206 |
) ON [PRIMARY] |
|
| 207 |
GO |
|
| 208 |
|
|
| 209 |
/* |
|
| 210 |
* harvest_log -- table to log entries for harvest operations |
|
| 211 |
*/ |
|
| 212 |
CREATE TABLE [dbo].[harvest_log] ( |
|
| 213 |
[harvest_log_id] [int] IDENTITY (1, 1) NOT NULL , |
|
| 214 |
[harvest_date] [datetime] NOT NULL , |
|
| 215 |
[status] [int] NOT NULL , |
|
| 216 |
[message] [varchar] (1000) NULL , |
|
| 217 |
[harvest_operation_code] [varchar] (30) NOT NULL , |
|
| 218 |
[site_schedule_id] [int] NOT NULL |
|
| 219 |
) ON [PRIMARY] |
|
| 220 |
GO |
|
| 221 |
|
|
| 222 |
/* |
|
| 223 |
* harvest_detail_log -- table to log detailed info about documents that |
|
| 224 |
* generated errors during the harvest |
|
| 225 |
*/ |
|
| 226 |
CREATE TABLE [dbo].[harvest_detail_log] ( |
|
| 227 |
[detail_log_id] [int] IDENTITY (1, 1) NOT NULL , |
|
| 228 |
[harvest_log_id] [int] NOT NULL , |
|
| 229 |
[scope] [varchar] (50) NOT NULL , |
|
| 230 |
[identifier] [bigint] NOT NULL , |
|
| 231 |
[revision] [bigint] NOT NULL , |
|
| 232 |
[document_url] [varchar] (255) NOT NULL , |
|
| 233 |
[error_message] [varchar] (1000) NOT NULL , |
|
| 234 |
[document_type] [varchar] (100) NOT NULL |
|
| 235 |
) ON [PRIMARY] |
|
| 236 |
GO |
|
| 237 |
|
|
| 238 |
/* |
|
| 191 | 239 |
* Constraints and indexes |
| 192 | 240 |
*/ |
| 193 | 241 |
ALTER TABLE [dbo].[xml_catalog] WITH NOCHECK ADD |
| ... | ... | |
| 240 | 288 |
) ON [PRIMARY] |
| 241 | 289 |
GO |
| 242 | 290 |
|
| 291 |
ALTER TABLE [dbo].[harvest_site_schedule] WITH NOCHECK ADD |
|
| 292 |
CONSTRAINT [PK_harvest_site_schedule] PRIMARY KEY CLUSTERED |
|
| 293 |
( |
|
| 294 |
[site_schedule_id] |
|
| 295 |
) ON [PRIMARY] |
|
| 296 |
GO |
|
| 297 |
|
|
| 298 |
ALTER TABLE [dbo].[harvest_log] WITH NOCHECK ADD |
|
| 299 |
CONSTRAINT [PK_harvest_log] PRIMARY KEY CLUSTERED |
|
| 300 |
( |
|
| 301 |
[harvest_log_id] |
|
| 302 |
) ON [PRIMARY] |
|
| 303 |
GO |
|
| 304 |
|
|
| 305 |
ALTER TABLE [dbo].[harvest_detail_log] WITH NOCHECK ADD |
|
| 306 |
CONSTRAINT [PK_harvest_detail_log] PRIMARY KEY CLUSTERED |
|
| 307 |
( |
|
| 308 |
[detail_log_id] |
|
| 309 |
) ON [PRIMARY] |
|
| 310 |
GO |
|
| 311 |
|
|
| 243 | 312 |
ALTER TABLE [dbo].[xml_access] WITH NOCHECK ADD |
| 244 | 313 |
CONSTRAINT [CK_xml_access] CHECK ([begin_time] < [end_time]) |
| 245 | 314 |
GO |
| ... | ... | |
| 387 | 456 |
) |
| 388 | 457 |
GO |
| 389 | 458 |
|
| 459 |
ALTER TABLE [dbo].[harvest_log] ADD |
|
| 460 |
CONSTRAINT [FK_harvest_log_harvest_site_schedule] FOREIGN KEY |
|
| 461 |
( |
|
| 462 |
[site_schedule_id] |
|
| 463 |
) REFERENCES [dbo].[harvest_site_schedule] ( |
|
| 464 |
[site_schedule_id] |
|
| 465 |
) |
|
| 466 |
GO |
|
| 467 |
|
|
| 468 |
ALTER TABLE [dbo].[harvest_detail_log] ADD |
|
| 469 |
CONSTRAINT [FK_harvest_detail_log_harvest_log] FOREIGN KEY |
|
| 470 |
( |
|
| 471 |
[harvest_log_id] |
|
| 472 |
) REFERENCES [dbo].[harvest_log] ( |
|
| 473 |
[harvest_log_id] |
|
| 474 |
) |
|
| 475 |
GO |
|
| 476 |
|
|
Also available in: Unified diff
Add three new Harvester tables