--
-- 0 - Make directories used by example
--

-- Production directory
EXEC xp_cmdshell 'mkdir c:\MSSQL';
EXEC xp_cmdshell 'mkdir c:\MSSQL\DATA';
EXEC xp_cmdshell 'mkdir c:\MSSQL\LOG';
GO

-- Quality Assurance directory
EXEC xp_cmdshell 'mkdir c:\QA';
EXEC xp_cmdshell 'mkdir c:\QA\DATA';
EXEC xp_cmdshell 'mkdir c:\QA\LOG';
GO

-- System Integration Testing directory
EXEC xp_cmdshell 'mkdir c:\SIT';
EXEC xp_cmdshell 'mkdir c:\SIT\DATA';
EXEC xp_cmdshell 'mkdir c:\SIT\LOG';
GO


--
-- 1 - Drop existing database
--

-- Use master to create new DPS db
USE [master]
GO

-- Delete existing databases.
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'WILDLIFE')
    DROP DATABASE [WILDLIFE]
GO


--
-- 2 - Create simple database
--

-- Define the db with default groups
CREATE DATABASE [WILDLIFE] 
ON PRIMARY 
( NAME = 'WildLifeData',
    FILENAME = 'C:\MSSQL\DATA\Wild_Life_Data.mdf',
    SIZE = 50MB,
    MAXSIZE = 200MB,
    FILEGROWTH = 5MB)
LOG ON 
 ( NAME = 'WildLifeLog',
    FILENAME = 'C:\MSSQL\LOG\Wild_Life_Log.ldf',
    SIZE = 12MB,
    MAXSIZE = 48MB,
    FILEGROWTH = 1MB)
GO


--
-- 3 - Create table & index using PRIMARY
--

-- Use the database
USE [WILDLIFE]
GO

-- Create the animals table
CREATE TABLE ANIMALS
(
ID INT NOT NULL IDENTITY (1, 1),
NAME VARCHAR(200) NOT NULL,
CATEGORY INT DEFAULT (0)
) ON [PRIMARY]
GO

-- Add index on name (non-clustered)
CREATE CLUSTERED INDEX IDX_ANIMAL_CATEGORY ON DBO.ANIMALS(CATEGORY) 
ON [PRIMARY];
GO

--
-- Create a function, map name -> code
--

CREATE FUNCTION UFN_MY_CATEGORIES(@NAME VARCHAR(200))
RETURNS INT AS
BEGIN

   -- Default value
   DECLARE @RETVAL INT = 0;
   
   -- 2 POWER 0
   IF (LOWER(@NAME) LIKE '%fly%')
       SET @RETVAL = @RETVAL + 1;

   -- 2 POWER 1
   IF (LOWER(@NAME) LIKE '%pig%')
       SET @RETVAL = @RETVAL + 2;

   -- 2 POWER 2
   IF (LOWER(@NAME) LIKE '%bat%')
       SET @RETVAL = @RETVAL + 4;

   -- 2 POWER 3
   IF (LOWER(@NAME) LIKE '%dog%')
       SET @RETVAL = @RETVAL + 8;
   
   -- RETURN THE CODE
   RETURN(@RETVAL);
   
END;


--
-- Create a trigger to categorize name
--

CREATE TRIGGER TRG_CATEGORIZE_ANIMALS ON ANIMALS
AFTER INSERT, UPDATE AS
BEGIN

    -- Detect inserts (1 .. n)
    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) 
    BEGIN     
        -- Update records
        UPDATE ANIMALS
        SET CATEGORY = dbo.UFN_MY_CATEGORIES(I.NAME)
        FROM ANIMALS A JOIN INSERTED I ON A.ID = I.ID;
        
        -- Debugging    
        PRINT 'INSERT DETECTED, CREATED CATEGORY';
    END; 
    
    -- Detect deletes (1 .. n)
    IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted) 
    BEGIN
        -- Debugging    
        PRINT 'DELETE DETECTED, NOTHING TO DO';
    END;
        
    -- Detected updates (1 .. n)
    IF EXISTS (SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
    BEGIN     
        -- Update records
        UPDATE ANIMALS
        SET CATEGORY = dbo.UFN_MY_CATEGORIES(I.NAME)
        FROM ANIMALS A JOIN INSERTED I ON A.ID = I.ID;
        
        -- Debugging    
        PRINT 'UPDATE DETECTED, ADJUST CATEGORY';
    END; 
    
END
GO

-- Use sample data from WIKIPEDIA
INSERT INTO ANIMALS (NAME) VALUES
('Aardvark'),
('Aardwolf'),
('Afghan Hound'),
('Albatross'),
('Alligator'),
('Alpaca'),
('American Robin'),
('Anaconda'),
('Angelfish'),
('Anglerfish'),
('Ant'),
('Anteater'),
('Antelope'),
('Antlion'),
('Ape'),
('Aphid'),
('Armadillo'),
('Arrow crab'),
('Asp'),
('Ass'),
('Baboon'),
('Badger'),
('Bald Eagle'),
('Bandicoot'),
('Barnacle'),
('Basilisk'),
('Barracuda'),
('Bass'),
('Basset Hound'),
('Bat'),
('Beaked whale'),
('Bear'),
('Beaver'),
('Bedbug'),
('Bee'),
('Beetle'),
('Bird'),
('Bison'),
('Blackbird'),
('Black panther'),
('Black Widow Spider'),
('Blue Jay'),
('Blue Whale'),
('Boa'),
('Bobcat'),
('Bobolink'),
('Bonobo'),
('Booby'),
('Box jellyfish'),
('Boston Terrier'),
('Bovid'),
('Buffalo'),
('Bug'),
('Bulldog'),
('Bull Terrier'),
('Butterfly'),
('Buzzard'),
('Camel'),
('Canid'),
('Cape Buffalo'),
('Capybara'),
('Cardinal'),
('Caribou'),
('Carp'),
('Cat'),
('Caterpillar'),
('Catfish'),
('Cattle'),
('Centipede'),
('Cephalopod'),
('Chameleon'),
('Cheetah'),
('Chickadee'),
('Chicken'),
('Chihuahua'),
('Chimpanzee'),
('Chinchilla'),
('Chipmunk'),
('Clam'),
('Clownfish'),
('Cobra'),
('Cockroach'),
('Cod'),
('Collie'),
('Condor'),
('Constrictor'),
('Coral'),
('Cougar'),
('Cow'),
('Coyote'),
('Crab'),
('Crane'),
('Crane Fly'),
('Crawdad'),
('Crayfish'),
('Cricket'),
('Crocodile'),
('Crow'),
('Cuckoo'),
('Daddy longlegs'),
('Damselfly'),
('Deer'),
('Dingo'),
('Dinosaur'),
('Dog'),
('Dolphin'),
('Donkey'),
('Dormouse'),
('Dove'),
('Dragonfly'),
('Duck'),
('Dung beetle'),
('Eagle'),
('Earthworm'),
('Earwig'),
('Echidna'),
('Eel'),
('Egret'),
('Elephant'),
('Elephant seal'),
('Elk'),
('Emu'),
('English pointer'),
('English Setter'),
('Ermine'),
('Falcon'),
('Ferret'),
('Finch'),
('Firefly'),
('Fish'),
('Flamingo'),
('Flea'),
('Fly'),
('Flyingfish'),
('Fowl'),
('Fox'),
('Frog'),
('Fruit Bat'),
('Galliwasp'),
('Gazelle'),
('Gecko'),
('Gerbil'),
('German Shepherd'),
('Giant Panda'),
('Giant squid'),
('Gibbon'),
('Gila monster'),
('Guanaco'),
('Guineafowl'),
('Giraffe'),
('Goat'),
('Golden Retriever'),
('Goldfinch'),
('Goldfish'),
('Goose'),
('Gopher'),
('Gorilla'),
('Grasshopper'),
('Great Blue Heron'),
('Great Dane'),
('Great white shark'),
('Greyhound'),
('Grizzly Bear'),
('Ground Sloth'),
('Grouse'),
('Guinea pig'),
('Gull'),
('Guppy'),
('Haddock'),
('Halibut'),
('Hammerhead shark'),
('Hamster'),
('Hare'),
('Harrier'),
('Hawk'),
('Hedgehog'),
('Hermit crab'),
('Heron'),
('Herring'),
('Hippopotamus'),
('Hookworm'),
('Hornet'),
('Horse'),
('Hound'),
('Hoverfly'),
('Human'),
('Hummingbird'),
('Humpback Whale'),
('Husky'),
('Hyena'),
('Iguana'),
('Impala'),
('Insect'),
('Irish Setter'),
('Irish Wolfhound'),
('Irukandji jellyfish'),
('Jackal'),
('Jaguar'),
('Jay'),
('Jellyfish'),
('Kangaroo'),
('Kangaroo mouse'),
('Kangaroo rat'),
('Kingfisher'),
('Kite'),
('Kiwi'),
('Koala'),
('Koi'),
('Komodo dragon'),
('Krill'),
('Labrador Retriever'),
('Ladybug'),
('Lamprey'),
('Lark'),
('Leech'),
('Lemming'),
('Lemur'),
('Leopard'),
('Leopon'),
('Liger'),
('Lion'),
('Lizard'),
('Llama'),
('Lobster'),
('Locust'),
('Loon'),
('Louse'),
('Lungfish'),
('Lynx'),
('Macaw'),
('Mackerel'),
('Magpie'),
('Mammal'),
('Mammoth'),
('Mandrill'),
('Manta Ray'),
('Marlin'),
('Marmoset'),
('Marmot'),
('Marsupial'),
('Marten'),
('Mastiff'),
('Mastodon'),
('Meadowlark'),
('Meerkat'),
('Mink'),
('Minnow'),
('Mite'),
('Mockingbird'),
('Mole'),
('Mollusk'),
('Mongoose'),
('Monitor lizard'),
('Monkey'),
('Moose'),
('Mosquito'),
('Moth'),
('Mountain goat'),
('Mouse'),
('Mule'),
('Muskox'),
('Mussel'),
('Narwhal'),
('Newt'),
('Nightingale'),
('Ocelot'),
('Octopus'),
('Old English Sheepdog'),
('Opossum'),
('Orangutan'),
('Orca'),
('Ostrich'),
('Otter'),
('Owl'),
('Ox'),
('Oyster'),
('Panda'),
('Panther'),
('Panthera hybrid'),
('Parakeet'),
('Parrot'),
('Parrotfish'),
('Partridge'),
('Peacock'),
('Peafowl'),
('Pekingese'),
('Pelican'),
('Penguin'),
('Perch'),
('Peregrine Falcon'),
('Persian Cat'),
('Pheasant'),
('Pig'),
('Pigeon'),
('Pike'),
('Pilot whale'),
('Pinniped'),
('Piranha'),
('Planarian'),
('Platypus'),
('Polar bear'),
('Pony'),
('Poodle'),
('Porcupine'),
('Porpoise'),
('Portuguese Man o War'),
('Possum'),
('Prairie dog'),
('Prawn'),
('Praying Mantis'),
('Primate'),
('Puffin'),
('Puma'),
('Python'),
('Quail'),
('Quelea'),
('Quokka'),
('Rabbit'),
('Raccoon'),
('Rainbow trout'),
('Rat'),
('Rattlesnake'),
('Raven'),
('Ray (Batoidea)'),
('Ray (Rajiformes)'),
('Red Panda'),
('Reindeer'),
('Rhinoceros'),
('Right whale'),
('Roadrunner'),
('Rodent'),
('Rook'),
('Rooster'),
('Roundworm'),
('Saber-toothed cat'),
('Sailfish'),
('Saint Bernard'),
('Salamander'),
('Salmon'),
('Sawfish'),
('Scale insect'),
('Scallop'),
('Scorpion'),
('Sea anemone'),
('Sea cow'),
('Seahorse'),
('Sea lion'),
('Sea slug'),
('Sea urchin'),
('Setter'),
('Shark'),
('Sheep'),
('Shrew'),
('Shrimp'),
('Siamese Cat'),
('Silkworm'),
('Silverfish'),
('Skink'),
('Skunk'),
('Sloth'),
('Slug'),
('Smelt'),
('Snail'),
('Snake'),
('Snipe'),
('Snow Leopard'),
('Sockeye salmon'),
('Sole'),
('Spaniel'),
('Sparrow'),
('Sperm whale'),
('Spider'),
('Spider monkey'),
('Spoonbill'),
('Squid'),
('Squirrel'),
('Starfish'),
('Star-nosed Mole'),
('Steelhead trout'),
('Stingray'),
('Stoat'),
('Stork'),
('Sturgeon'),
('Sugar Glider'),
('Swallow'),
('Swan'),
('Swift'),
('Swordfish'),
('Swordtail'),
('Tabby cat'),
('Tahr'),
('Takin'),
('Tapeworm'),
('Tapir'),
('Tarantula'),
('Tarsier'),
('Tasmanian Devil'),
('Termite'),
('Tern'),
('Terrier'),
('Thrush'),
('Tick'),
('Tiger'),
('Tiger shark'),
('Tigon'),
('Toad'),
('Tortoise'),
('Toucan'),
('Toy Poodle'),
('Trapdoor spider'),
('Tree frog'),
('Trout'),
('Tuna'),
('Turkey'),
('Turtle'),
('Tyrannosaurus'),
('Urial'),
('Vampire bat'),
('Vampire Squid'),
('Vicuna'),
('Viper'),
('Vole'),
('Vulture'),
('Wallaby'),
('Walrus'),
('Wasp'),
('Warbler'),
('Water Buffalo'),
('Weasel'),
('Whale'),
('Whippet'),
('Whitefish'),
('Whooping Crane'),
('Wildcat'),
('Wildebeest'),
('Wildfowl'),
('Wolf'),
('Wolverine'),
('Wombat'),
('Woodpecker'),
('Worm'),
('Wren'),
('X-ray fish'),
('Yak'),
('Zebra')
GO

-- Select everything with word 'fly'
SELECT * FROM WILDLIFE.dbo.ANIMALS WHERE CATEGORY = 1
GO

--
-- Copy PROD to QA
--

-- Switch to master
USE master
GO

-- Kick off users
ALTER DATABASE [WILDLIFE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Set the database to off-line
ALTER DATABASE [WILDLIFE] SET OFFLINE
GO

-- Copy data file to QA region
EXEC xp_cmdshell 'COPY C:\MSSQL\DATA\Wild_Life_Data.mdf C:\QA\DATA\Wild_Life_Data.mdf';
GO

-- Copy log file to QA region
EXEC xp_cmdshell 'COPY C:\MSSQL\LOG\Wild_Life_Log.ldf C:\QA\LOG\Wild_Life_Log.ldf';
GO


-- Bring database on-line
ALTER DATABASE [WILDLIFE] SET ONLINE
GO

-- Add users
ALTER DATABASE [WILDLIFE] SET MULTI_USER


-- Make sure permissions allow service account access!

-- Attach & create a new database
CREATE DATABASE [QA_WILDLIFE] ON 
(FILENAME = 'C:\QA\Data\Wild_Life_Data.mdf'), 
(FILENAME = 'C:\QA\Log\Wild_Life_Log.ldf')
FOR ATTACH ;


-- Redo example, 
DROP DATABASE [QA_WILDLIFE];

-- Attach & create a new database
CREATE DATABASE [QA_WILDLIFE] ON 
(FILENAME = 'C:\QA\Data\Wild_Life_Data.mdf')
FOR ATTACH_REBUILD_LOG;



-- Select everything with word 'fly'
SELECT * FROM QA_WILDLIFE.dbo.ANIMALS WHERE CATEGORY = 1
GO


--
-- Rename QA to SIT2
--

-- Allow people to finish work
ALTER DATABASE [QA_WILDLIFE] SET SINGLE_USER
GO

-- Option 1 - rename the database
sp_renamedb 'QA_WILDLIFE', 'SIT2_WILDLIFE'

-- Option 2 - rename the database
ALTER DATABASE QA_WILDLIFE
MODIFY NAME = SIT2_WILDLIFE ;


-- Allow people to access the database
ALTER DATABASE [SIT2_WILDLIFE] SET MULTI_USER
GO



-- Select everything with word 'fly'
SELECT * FROM SIT2_WILDLIFE.dbo.ANIMALS WHERE CATEGORY = 1
GO    


--
-- Detach SIT2 & reattach SIT
--

-- Kick off users, roll back current work
ALTER DATABASE [SIT2_WILDLIFE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Set the database to off-line
ALTER DATABASE [SIT2_WILDLIFE] SET OFFLINE
GO

-- Full text indexes can be drop if not careful.
-- Detach database
sp_detach_db 'SIT2_WILDLIFE'


-- Attach database
EXEC sp_attach_db @dbname = N'SIT_WILDLIFE', 
    @filename1 = N'C:\QA\Data\Wild_Life_Data.mdf', 
    @filename2 = N'C:\QA\Log\Wild_Life_Log.ldf';

-- Full text indexes can be drop if not careful.
-- Detach database
sp_detach_db 'SIT2_WILDLIFE'


-- Attach database
EXEC sp_attach_db @dbname = N'SIT_WILDLIFE', 
    @filename1 = N'C:\QA\Data\Wild_Life_Data.mdf', 
    @filename2 = N'C:\QA\Log\Wild_Life_Log.ldf';


-- Bring database on-line
ALTER DATABASE [SIT_WILDLIFE] SET ONLINE
GO

-- Allow people to access the database
ALTER DATABASE [SIT_WILDLIFE] SET MULTI_USER
GO
    
        
-- Select everything with word 'fly'
SELECT * FROM SIT_WILDLIFE.dbo.ANIMALS WHERE CATEGORY = 1
GO    


--
-- Move data files for SIT2
--

-- Allow people to finish work
ALTER DATABASE [SIT_WILDLIFE] SET SINGLE_USER
GO

-- A way to move datafiles of the database
ALTER DATABASE [SIT_WILDLIFE] SET OFFLINE
GO
-- Move data file to SIT region
EXEC xp_cmdshell 'MOVE C:\QA\DATA\Wild_Life_Data.mdf C:\SIT\DATA\Wild_Life_Data.mdf';
GO

-- Move log file to SIT region
EXEC xp_cmdshell 'MOVE C:\QA\LOG\Wild_Life_Log.ldf C:\SIT\LOG\Wild_Life_Log.ldf';
GO

-- Update system catalog for data file
ALTER DATABASE [SIT_WILDLIFE] MODIFY FILE
   (NAME=WildLifeData,FILENAME='C:\SIT\DATA\Wild_Life_Data.mdf')
GO

-- Update system catalog for log file
ALTER DATABASE [SIT_WILDLIFE] MODIFY FILE
   (NAME=WildLifeLog,FILENAME='C:\SIT\LOG\Wild_Life_Log.ldf')
GO

-- Bring database on-line
ALTER DATABASE [SIT_WILDLIFE] SET ONLINE
GO

-- Allow people to access the database
ALTER DATABASE [SIT_WILDLIFE] SET MULTI_USER
GO


-- Select everything with word 'fly'
SELECT * FROM SIT_WILDLIFE.dbo.ANIMALS WHERE CATEGORY = 1
GO