USE MASTER;
GO
IF db_id (N'Civilizations') IS NOT NULL
DROP DATABASE Civilizations;
GO
CREATE DATABASE Civilizations
ON (NAME = Civ_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Civ.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = Civ_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Civ.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
USE Civilizations;
GO
------------------------------------------------------ Базовые таблицы-----------------------------
CREATE TABLE technology (
technologyid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
namet nvarchar(30) NOT NULL UNIQUE,
cost int NOT NULL DEFAULT 0,
generation int NOT NULL DEFAULT 0
);
GO
CREATE TABLE unit (
unitid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
nameu nvarchar(30) UNIQUE,
poweru int NOT NULL DEFAULT 0,
speed int NOT NULL DEFAULT 0,
prise int NOT NULL DEFAULT 0,
unit_technology int FOREIGN KEY REFERENCES technology(technologyid) ON DELETE SET NULL ON UPDATE NO ACTION
);
GO
CREATE TABLE building (
buildingid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
nameb nvarchar(30) UNIQUE,
discription text NOT NULL DEFAULT 'no',
prise int NOT NULL DEFAULT 0,
building_technology int FOREIGN KEY REFERENCES technology(technologyid) ON DELETE CASCADE ON UPDATE CASCADE
);
GO
CREATE TABLE country (
countryid int IDENTITY(1,1) Primary key NOT NULL,
namec nvarchar(30) NOT NULL UNIQUE,
discription text NOT NULL DEFAULT 'no',
technology_country int FOREIGN KEY REFERENCES technology(technologyid) ON DELETE NO ACTION ON UPDATE NO ACTION
);
GO
CREATE TABLE ruler (
rulerid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
namer nvarchar(30) NOT NULL UNIQUE,
Feature_I nvarchar(30) NOT NULL DEFAULT 'no1',
Feature_II nvarchar(30) NOT NULL DEFAULT 'no2',
ruler_country int FOREIGN KEY REFERENCES country(countryid) ON DELETE CASCADE ON UPDATE CASCADE
);
GO
CREATE TABLE uniq (
unitid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
nameu2 nvarchar(30) NOT NULL UNIQUE,
feature nvarchar(30) NOT NULL DEFAULT 'no1',
unit_country int FOREIGN KEY REFERENCES country(countryid) ON DELETE CASCADE ON UPDATE CASCADE,
uniq_unit int FOREIGN KEY REFERENCES unit(unitid) ON DELETE NO ACTION
);
GO
CREATE TABLE TT(
technologyFK int NOT NULL FOREIGN KEY REFERENCES technology(technologyid) ON UPDATE NO ACTION,
technologyFKnext int NOT NULL FOREIGN KEY REFERENCES technology(technologyid) ON UPDATE NO ACTION,
PRIMARY KEY (technologyFK, technologyFKnext),
);
GO
------------------------------------------------------ Представление на цивилизацию-----------------------------
IF OBJECT_ID ('Civilization', 'view') IS NOT NULL
DROP VIEW Civilization;
GO
CREATE VIEW Civilization WITH SCHEMABINDING AS
SELECT a.namec, a.discription, b.namer, b.Feature_I, b.Feature_II, c.nameu2, c.feature, d.nameu, e.namet FROM
dbo.country a INNER JOIN dbo.ruler b ON a.countryid = b.ruler_country
INNER JOIN dbo.uniq c ON c.unit_country = a.countryid
INNER JOIN dbo.unit d ON c.uniq_unit = d.unitid
INNER JOIN dbo.technology e ON e.technologyid = a.technology_country
GO
--Вставка цивилизации (Удаление цивилизации происходит каскадно после удаления страны)--
IF OBJECT_ID ('insert_Civilization','TR') IS NOT NULL
DROP TRIGGER insert_Civilization;
GO
CREATE TRIGGER insert_Civilization ON Civilization INSTEAD OF INSERT
AS
BEGIN
INSERT technology(namet) SELECT DISTINCT namet FROM inserted WHERE namet NOT IN (SELECT namet FROM technology)
INSERT unit(nameu) SELECT DISTINCT nameu FROM inserted WHERE nameu NOT IN (SELECT nameu FROM unit)
INSERT country(namec, technology_country) SELECT DISTINCT b.namec, a.technologyid FROM dbo.technology a INNER JOIN inserted b ON a.namet = b.namet WHERE b.namec NOT IN (SELECT namec FROM country)
UPDATE country SET discription =a.discription FROM inserted a WHERE (country.namec = a.namec AND a.discription IS NOT NULL)
INSERT ruler(ruler_country, namer) SELECT DISTINCT a.countryid, b.namer FROM dbo.country a INNER JOIN inserted b ON a.namec = b.namec WHERE b.namer NOT IN (SELECT namer FROM ruler) AND b.namec NOT IN (SELECT a.namec FROM dbo.country a INNER JOIN dbo.ruler d ON a.countryid = d.ruler_country ) ;
UPDATE ruler SET Feature_I = a.Feature_I FROM inserted a WHERE (ruler.namer = a.namer AND a.Feature_I IS NOT NULL);
UPDATE ruler SET Feature_II = a.Feature_II FROM inserted a WHERE (ruler.namer = a.namer AND a.Feature_II IS NOT NULL);
INSERT uniq(nameu2, unit_country, uniq_unit) SELECT DISTINCT b.nameu2, a.countryid, c.unitid FROM dbo.country a INNER JOIN inserted b ON a.namec = b.namec INNER JOIN dbo.unit c ON c.nameu = b.nameu WHERE b.nameu2 NOT IN (SELECT nameu2 FROM uniq) AND b.namec NOT IN (SELECT a.namec FROM dbo.country a INNER JOIN dbo.uniq d ON a.countryid = d.unit_country )
UPDATE uniq SET feature = a.feature FROM inserted a WHERE (uniq.nameu2 = a.nameu2 AND a.feature IS NOT NULL);
END;
GO
------------------------------------------------------ Представление на правителя-----------------------------
IF OBJECT_ID ('rulerview', 'view') IS NOT NULL
DROP VIEW rulerview;
GO
CREATE VIEW rulerview WITH SCHEMABINDING AS
SELECT a.namec, b.namer, b.Feature_I, b.Feature_II FROM
dbo.country a INNER JOIN dbo.ruler b ON a.countryid = b.ruler_country
GO
--Вставка правителя--
IF OBJECT_ID ('insert_ruler','TR') IS NOT NULL
DROP TRIGGER insert_ruler;
GO
CREATE TRIGGER insert_ruler ON rulerview INSTEAD OF INSERT
AS
BEGIN
INSERT ruler(ruler_country, namer)SELECT DISTINCT a.countryid, b.namer FROM dbo.country a INNER JOIN inserted b ON a.namec = b.namec WHERE b.namer NOT IN (SELECT namer FROM ruler);
UPDATE ruler SET Feature_I = a.Feature_I FROM inserted a WHERE (ruler.namer = a.namer AND a.Feature_I IS NOT NULL);
UPDATE ruler SET Feature_II = a.Feature_II FROM inserted a WHERE (ruler.namer = a.namer AND a.Feature_II IS NOT NULL);
END;
GO
--Удаление правителя--
IF OBJECT_ID ('delete_ruler','TR') IS NOT NULL
DROP TRIGGER delete_ruler;
GO
CREATE TRIGGER delete_ruler ON rulerview INSTEAD OF DELETE
AS
DELETE FROM ruler WHERE namer IN (SELECT namer FROM deleted);
DELETE FROM country WHERE countryid NOT IN (SELECT ruler_country FROM ruler);
GO
------------------------------------------------------ Представление на УУ-----------------------------
IF OBJECT_ID ('uniqview', 'view') IS NOT NULL
DROP VIEW uniqview;
GO
CREATE VIEW uniqview WITH SCHEMABINDING AS
SELECT a.namec, c.nameu2, c.feature, d.nameu FROM
dbo.country a INNER JOIN dbo.uniq c ON c.unit_country = a.countryid
INNER JOIN dbo.unit d ON c.uniq_unit = d.unitid
GO
--Вставка УУ--
IF OBJECT_ID ('insert_uniq','TR') IS NOT NULL
DROP TRIGGER insert_uniq;
GO
CREATE TRIGGER insert_uniq ON uniqview INSTEAD OF INSERT
AS
BEGIN
INSERT unit(nameu) SELECT DISTINCT nameu FROM inserted WHERE nameu NOT IN (SELECT nameu FROM unit)
INSERT uniq(nameu2, unit_country, uniq_unit) SELECT DISTINCT b.nameu2, a.countryid, c.unitid FROM dbo.country a INNER JOIN inserted b ON a.namec = b.namec INNER JOIN dbo.unit c ON c.nameu = b.nameu WHERE b.nameu2 NOT IN (SELECT nameu2 FROM uniq);
UPDATE uniq SET feature = a.feature FROM inserted a WHERE (uniq.nameu2 = a.nameu2 AND a.feature IS NOT NULL);
END;
GO
--Удаление УУ--
IF OBJECT_ID ('delete_uniq','TR') IS NOT NULL
DROP TRIGGER delete_uniq;
GO
CREATE TRIGGER delete_uniq ON uniqview INSTEAD OF DELETE
AS
DELETE FROM uniq WHERE nameu2 IN (SELECT nameu2 FROM deleted);
DELETE FROM country WHERE countryid NOT IN (SELECT unit_country FROM uniq);
GO
------------------------------------------------------ Представление для дерева технологий -----------------------------
CREATE VIEW treeview WITH SCHEMABINDING AS
SELECT a.namet AS tecprev, c.namet AS tecnext FROM
dbo.technology a LEFT JOIN dbo.TT b ON a.technologyid = b.technologyFK
LEFT JOIN dbo.technology c ON c.technologyid = b.technologyFKnext
GO
--Вставка связей в представление
IF OBJECT_ID ('insert_tree','TR') IS NOT NULL
DROP TRIGGER insert_tree;
GO
CREATE TRIGGER insert_tree ON treeview INSTEAD OF INSERT
AS
BEGIN
INSERT TT(technologyFK, technologyFKnext) SELECT DISTINCT a.technologyid, c.technologyid FROM dbo.technology a INNER JOIN inserted b ON a.namet = b.tecprev INNER JOIN dbo.technology c ON b.tecnext = c.namet;
END;
GO
--Вставка связей в таблицу--
IF OBJECT_ID ('insert_TT', 'TR') IS NOT NULL
DROP TRIGGER insert_TT;
GO
CREATE TRIGGER insert_TT ON TT INSTEAD OF INSERT
AS
BEGIN
INSERT TT (technologyFK, technologyFKnext) SELECT DISTINCT a.technologyFK, a.technologyFKnext FROM inserted a WHERE
( SELECT b.generation FROM inserted INNER JOIN technology b ON a.technologyFK = b.technologyid) < (SELECT c.generation FROM inserted INNER JOIN technology c ON a.technologyFKnext = c.technologyid)
END;
GO
--Удаление связей из таблицы--
IF OBJECT_ID ('delete_tree','TR') IS NOT NULL
DROP TRIGGER delete_tree;
GO
CREATE TRIGGER delete_tree ON treeview INSTEAD OF DELETE
AS
DELETE FROM TT WHERE technologyFK IN (SELECT DISTINCT a.technologyid FROM dbo.technology a INNER JOIN deleted b ON a.namet = b.tecprev) AND
technologyFKnext IN (SELECT DISTINCT a.technologyid FROM dbo.technology a INNER JOIN deleted b ON a.namet = b.tecnext)
GO
------------------------------------------------------ Представление на технологии-----------------------------
IF OBJECT_ID ('tecview', 'view') IS NOT NULL
DROP VIEW tecview;
GO
CREATE VIEW tecview WITH SCHEMABINDING AS
SELECT a.namet, a.cost, a.generation, b.nameu, c.nameb, d.tecprev, e.tecnext FROM
dbo.technology a LEFT JOIN dbo.unit b ON a.technologyid = b.unit_technology
LEFT JOIN dbo.building c ON a.technologyid = c.building_technology
Left Join dbo.treeview d ON a.namet = d.tecnext
Left Join dbo.treeview e ON a.namet = e.tecprev
GO
--Вставка технологии (Удаление технологии DELETE FROM technology WHERE namet = 'имя' юнит сбросится как не требующий техи, если к ней привязана страна удаления не будет--
IF OBJECT_ID ('insert_technology','TR') IS NOT NULL
DROP TRIGGER insert_technology;
GO
CREATE TRIGGER insert_technology ON tecview INSTEAD OF INSERT
AS
BEGIN
INSERT technology(namet, cost, generation) SELECT DISTINCT namet, cost, generation FROM inserted WHERE namet NOT IN (Select namet FROM technology);
INSERT unit(unit_technology, nameu) SELECT DISTINCT a.technologyid, b.nameu FROM dbo.technology a INNER JOIN inserted b ON a.namet = b.namet WHERE b.nameu NOT IN (SELECT nameu FROM unit) AND b.namet NOT IN (SELECT a.namet FROM dbo.technology a INNER JOIN dbo.unit d ON a.technologyid = d.unit_technology) AND b.nameu IS NOT NULL;
INSERT building(building_technology, nameb) SELECT DISTINCT a.technologyid, b.nameb FROM dbo.technology a INNER JOIN inserted b ON a.namet = b.namet WHERE b.nameb NOT IN (SELECT nameb FROM building) AND b.namet NOT IN (SELECT a.namet FROM dbo.technology a INNER JOIN dbo.building d ON a.technologyid = d.building_technology) AND b.nameb IS NOT NULL;
END;
GO
/*
Вставка юнита происходит вместе с технологией которая его добавляет или вместе в с УУ, который его добавляет
Параметры атаки, скорости и цены юнита задаются через апдейт таблицы юнитов по имени
Удаление юнита возможно если у нему не привязан УУ
*/
------------------------------------------------------ Тесты-----------------------------
--Тестирование цивилизаций--
INSERT Civilization(namec, namer, nameu, nameu2, namet) VALUES ('Egypt', 'Ramses', 'archer', 'arsher', 'astronomy');
INSERT Civilization(namec, namer, nameu, nameu2, namet) VALUES ('Egypt', 'Ramses II','archer', 'arsher', 'phisics');
INSERT ruler(namer, ruler_country) VALUES ('Ramses II', 1);
INSERT unit(nameu) VALUES ('archer2');
INSERT uniq(nameu2, unit_country, uniq_unit) VALUES ('archer3', 1, 2);
DELETE FROM uniqview WHERE nameu = 'archer'
DELETE FROM rulerview WHERE namer = 'Ramses'
DELETE FROM technology WHERE namet = 'phisics'
SELECT namec, namet, nameu2, namer, nameu FROM Civilization
SELECT namet FROM technology;
SELECT namec FROM country;
SELECT namer FROM ruler;
SELECT nameu2 FROM uniq;
SELECT nameu FROM unit;
GO
--Тестирование технологий--
INSERT tecview(namet, generation, cost, nameu, nameb) VALUES ('рыболовство', 0, 0, '1', 'plfy');
INSERT tecview(namet, generation, cost, nameu, nameb) VALUES ('мореплавание', 1, 0, '2', 'sdf');
INSERT tecview(namet, generation, cost, nameu) VALUES ('компас', 1, 0, '3');
INSERT tecview(namet, generation, cost, nameu) VALUES ('оптика', 2, 0, '4');
UPDATE building SET building_technology = technologyid FROM technology WHERE (namet = 'оптика' AND nameb = 'plfy');
INSERT TT(technologyFK,technologyFKnext) Values (1,1);
INSERT TT(technologyFK,technologyFKnext) Values (1,2);
INSERT TT(technologyFK,technologyFKnext) Values (2,1);
INSERT TT(technologyFK,technologyFKnext) Values (2,3);
INSERT TT(technologyFK,technologyFKnext) Values (3,4);
INSERT treeview(tecprev,tecnext) Values ('рыболовство','рыболовство');
INSERT treeview(tecprev,tecnext) Values ('рыболовство','мореплавание');
INSERT treeview(tecprev,tecnext) Values ('мореплавание','рыболовство');
INSERT treeview(tecprev,tecnext) Values ('мореплавание','компас');
INSERT treeview(tecprev,tecnext) Values ('компас','оптика');
SELECT namet, generation FROM technology;
SELECT *FROM tecview
SELECT *FROM unit
SELECT *FROM building
SELECT *FROM TT
SELECT*FROM treeview
SELECT*from tecview
GO