369 lines
12 KiB
Transact-SQL
369 lines
12 KiB
Transact-SQL
-- ----------------------------
|
|
-- Table structure for discos
|
|
-- ----------------------------
|
|
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[discos]') AND type IN ('U'))
|
|
DROP TABLE [dbo].[discos]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[discos] (
|
|
[id] int IDENTITY(1,1) NOT NULL,
|
|
[mediatype] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
|
|
[size] int NOT NULL,
|
|
[created_at] datetime2(6) DEFAULT getdate() NOT NULL,
|
|
[updated_at] datetime2(6) DEFAULT getdate() NOT NULL
|
|
)
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[discos] SET (LOCK_ESCALATION = TABLE)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Table structure for equipos
|
|
-- ----------------------------
|
|
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[equipos]') AND type IN ('U'))
|
|
DROP TABLE [dbo].[equipos]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[equipos] (
|
|
[id] int IDENTITY(1,1) NOT NULL,
|
|
[hostname] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
|
|
[ip] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
|
|
[mac] nvarchar(17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
|
|
[motherboard] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
|
|
[cpu] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
|
|
[ram_installed] int NOT NULL,
|
|
[ram_slots] int NULL,
|
|
[os] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
|
|
[architecture] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
|
|
[created_at] datetime2(6) DEFAULT getdate() NOT NULL,
|
|
[updated_at] datetime2(6) DEFAULT getdate() NOT NULL,
|
|
[sector_id] int NULL,
|
|
[origen] nvarchar(50) COLLATE Modern_Spanish_CI_AS DEFAULT 'manual' NOT NULL
|
|
)
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[equipos] SET (LOCK_ESCALATION = TABLE)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Table structure for equipos_discos
|
|
-- ----------------------------
|
|
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[equipos_discos]') AND type IN ('U'))
|
|
DROP TABLE [dbo].[equipos_discos]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[equipos_discos] (
|
|
[id] int IDENTITY(1,1) NOT NULL,
|
|
[equipo_id] int NULL,
|
|
[disco_id] int NULL,
|
|
[origen] nvarchar(50) COLLATE Modern_Spanish_CI_AS DEFAULT 'manual' NOT NULL
|
|
)
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[equipos_discos] SET (LOCK_ESCALATION = TABLE)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Table structure for equipos_memorias_ram
|
|
-- ----------------------------
|
|
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[equipos_memorias_ram]') AND type IN ('U'))
|
|
DROP TABLE [dbo].[equipos_memorias_ram]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[equipos_memorias_ram] (
|
|
[id] int IDENTITY(1,1) NOT NULL,
|
|
[slot] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
|
|
[equipo_id] int NULL,
|
|
[memoria_ram_id] int NULL,
|
|
[origen] nvarchar(50) COLLATE Modern_Spanish_CI_AS DEFAULT 'manual' NOT NULL
|
|
)
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[equipos_memorias_ram] SET (LOCK_ESCALATION = TABLE)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Table structure for historial_equipos
|
|
-- ----------------------------
|
|
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[historial_equipos]') AND type IN ('U'))
|
|
DROP TABLE [dbo].[historial_equipos]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[historial_equipos] (
|
|
[id] int IDENTITY(1,1) NOT NULL,
|
|
[equipo_id] int NULL,
|
|
[campo_modificado] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
|
|
[valor_anterior] nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
|
|
[valor_nuevo] nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
|
|
[fecha_cambio] datetime2(6) DEFAULT getdate() NOT NULL
|
|
)
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[historial_equipos] SET (LOCK_ESCALATION = TABLE)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Table structure for memorias_ram
|
|
-- ----------------------------
|
|
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[memorias_ram]') AND type IN ('U'))
|
|
DROP TABLE [dbo].[memorias_ram]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[memorias_ram] (
|
|
[id] int IDENTITY(1,1) NOT NULL,
|
|
[part_number] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
|
|
[fabricante] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
|
|
[tamano] int NOT NULL,
|
|
[velocidad] int NULL,
|
|
[created_at] datetime2(6) DEFAULT getdate() NOT NULL,
|
|
[updated_at] datetime2(6) DEFAULT getdate() NOT NULL
|
|
)
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[memorias_ram] SET (LOCK_ESCALATION = TABLE)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Table structure for sectores
|
|
-- ----------------------------
|
|
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sectores]') AND type IN ('U'))
|
|
DROP TABLE [dbo].[sectores]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[sectores] (
|
|
[id] int IDENTITY(1,1) NOT NULL,
|
|
[nombre] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
|
|
[created_at] datetime2(6) DEFAULT getdate() NOT NULL,
|
|
[updated_at] datetime2(6) DEFAULT getdate() NOT NULL
|
|
)
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[sectores] SET (LOCK_ESCALATION = TABLE)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Table structure for usuarios
|
|
-- ----------------------------
|
|
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[usuarios]') AND type IN ('U'))
|
|
DROP TABLE [dbo].[usuarios]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[usuarios] (
|
|
[id] int IDENTITY(1,1) NOT NULL,
|
|
[username] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
|
|
[password] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
|
|
[created_at] datetime2(6) DEFAULT getdate() NOT NULL,
|
|
[updated_at] datetime2(6) DEFAULT getdate() NOT NULL
|
|
)
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[usuarios] SET (LOCK_ESCALATION = TABLE)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Table structure for usuarios_equipos
|
|
-- ----------------------------
|
|
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[usuarios_equipos]') AND type IN ('U'))
|
|
DROP TABLE [dbo].[usuarios_equipos]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[usuarios_equipos] (
|
|
[usuario_id] int NOT NULL,
|
|
[equipo_id] int NOT NULL,
|
|
[origen] nvarchar(50) COLLATE Modern_Spanish_CI_AS DEFAULT 'manual' NOT NULL
|
|
)
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[usuarios_equipos] SET (LOCK_ESCALATION = TABLE)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Auto increment value for discos
|
|
-- ----------------------------
|
|
DBCC CHECKIDENT ('[dbo].[discos]', RESEED, 1)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Primary Key structure for table discos
|
|
-- ----------------------------
|
|
ALTER TABLE [dbo].[discos] ADD CONSTRAINT [PK_discos] PRIMARY KEY CLUSTERED ([id])
|
|
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
|
|
ON [PRIMARY]
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Auto increment value for equipos
|
|
-- ----------------------------
|
|
DBCC CHECKIDENT ('[dbo].[equipos]', RESEED, 1)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Primary Key structure for table equipos
|
|
-- ----------------------------
|
|
ALTER TABLE [dbo].[equipos] ADD CONSTRAINT [PK_equipos] PRIMARY KEY CLUSTERED ([id])
|
|
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
|
|
ON [PRIMARY]
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Auto increment value for equipos_discos
|
|
-- ----------------------------
|
|
DBCC CHECKIDENT ('[dbo].[equipos_discos]', RESEED, 1)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Primary Key structure for table equipos_discos
|
|
-- ----------------------------
|
|
ALTER TABLE [dbo].[equipos_discos] ADD CONSTRAINT [PK_equipos_discos] PRIMARY KEY CLUSTERED ([id])
|
|
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
|
|
ON [PRIMARY]
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Auto increment value for equipos_memorias_ram
|
|
-- ----------------------------
|
|
DBCC CHECKIDENT ('[dbo].[equipos_memorias_ram]', RESEED, 1)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Primary Key structure for table equipos_memorias_ram
|
|
-- ----------------------------
|
|
ALTER TABLE [dbo].[equipos_memorias_ram] ADD CONSTRAINT [PK_equipos_memorias_ram] PRIMARY KEY CLUSTERED ([id])
|
|
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
|
|
ON [PRIMARY]
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Auto increment value for historial_equipos
|
|
-- ----------------------------
|
|
DBCC CHECKIDENT ('[dbo].[historial_equipos]', RESEED, 1)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Primary Key structure for table historial_equipos
|
|
-- ----------------------------
|
|
ALTER TABLE [dbo].[historial_equipos] ADD CONSTRAINT [PK_historial_equipos] PRIMARY KEY CLUSTERED ([id])
|
|
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
|
|
ON [PRIMARY]
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Auto increment value for memorias_ram
|
|
-- ----------------------------
|
|
DBCC CHECKIDENT ('[dbo].[memorias_ram]', RESEED, 1)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Primary Key structure for table memorias_ram
|
|
-- ----------------------------
|
|
ALTER TABLE [dbo].[memorias_ram] ADD CONSTRAINT [PK_memorias_ram] PRIMARY KEY CLUSTERED ([id])
|
|
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
|
|
ON [PRIMARY]
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Auto increment value for sectores
|
|
-- ----------------------------
|
|
DBCC CHECKIDENT ('[dbo].[sectores]', RESEED, 1)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Primary Key structure for table sectores
|
|
-- ----------------------------
|
|
ALTER TABLE [dbo].[sectores] ADD CONSTRAINT [PK_sectores] PRIMARY KEY CLUSTERED ([id])
|
|
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
|
|
ON [PRIMARY]
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Auto increment value for usuarios
|
|
-- ----------------------------
|
|
DBCC CHECKIDENT ('[dbo].[usuarios]', RESEED, 1)
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Primary Key structure for table usuarios
|
|
-- ----------------------------
|
|
ALTER TABLE [dbo].[usuarios] ADD CONSTRAINT [PK_usuarios] PRIMARY KEY CLUSTERED ([id])
|
|
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
|
|
ON [PRIMARY]
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Primary Key structure for table usuarios_equipos
|
|
-- ----------------------------
|
|
ALTER TABLE [dbo].[usuarios_equipos] ADD CONSTRAINT [PK_usuarios_equipos] PRIMARY KEY CLUSTERED ([usuario_id], [equipo_id])
|
|
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
|
|
ON [PRIMARY]
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Foreign Keys structure for table equipos
|
|
-- ----------------------------
|
|
ALTER TABLE [dbo].[equipos] ADD CONSTRAINT [FK_equipos_sectores] FOREIGN KEY ([sector_id]) REFERENCES [dbo].[sectores] ([id]) ON DELETE SET NULL ON UPDATE NO ACTION
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Foreign Keys structure for table equipos_discos
|
|
-- ----------------------------
|
|
ALTER TABLE [dbo].[equipos_discos] ADD CONSTRAINT [FK_equipos_discos_equipos] FOREIGN KEY ([equipo_id]) REFERENCES [dbo].[equipos] ([id]) ON DELETE CASCADE ON UPDATE NO ACTION
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[equipos_discos] ADD CONSTRAINT [FK_equipos_discos_discos] FOREIGN KEY ([disco_id]) REFERENCES [dbo].[discos] ([id]) ON DELETE CASCADE ON UPDATE NO ACTION
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Foreign Keys structure for table equipos_memorias_ram
|
|
-- ----------------------------
|
|
ALTER TABLE [dbo].[equipos_memorias_ram] ADD CONSTRAINT [FK_equipos_memorias_ram_equipos] FOREIGN KEY ([equipo_id]) REFERENCES [dbo].[equipos] ([id]) ON DELETE CASCADE ON UPDATE NO ACTION
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[equipos_memorias_ram] ADD CONSTRAINT [FK_equipos_memorias_ram_memorias_ram] FOREIGN KEY ([memoria_ram_id]) REFERENCES [dbo].[memorias_ram] ([id]) ON DELETE CASCADE ON UPDATE NO ACTION
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Foreign Keys structure for table historial_equipos
|
|
-- ----------------------------
|
|
ALTER TABLE [dbo].[historial_equipos] ADD CONSTRAINT [FK_historial_equipos_equipos] FOREIGN KEY ([equipo_id]) REFERENCES [dbo].[equipos] ([id]) ON DELETE CASCADE ON UPDATE NO ACTION
|
|
GO
|
|
|
|
|
|
-- ----------------------------
|
|
-- Foreign Keys structure for table usuarios_equipos
|
|
-- ----------------------------
|
|
ALTER TABLE [dbo].[usuarios_equipos] ADD CONSTRAINT [FK_usuarios_equipos_usuarios] FOREIGN KEY ([usuario_id]) REFERENCES [dbo].[usuarios] ([id]) ON DELETE CASCADE ON UPDATE CASCADE
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[usuarios_equipos] ADD CONSTRAINT [FK_usuarios_equipos_equipos] FOREIGN KEY ([equipo_id]) REFERENCES [dbo].[equipos] ([id]) ON DELETE CASCADE ON UPDATE NO ACTION
|
|
GO
|
|
|