Lab3 BD

USE Magazin_de_Antichitati
GO

CREATE TABLE Versiune(
    CodV INT
)

INSERT Versiune
    VALUES (1)

CREATE TABLE MasiniTransport(
    CodM INT,
    CodF INT,
    NrUsi INT,
    Denumire VARCHAR(30),
    DataFabricarii DATE,
)

CREATE TABLE Furnizor(
    CodF INT PRIMARY KEY
)
GO

-- modifica tipul unei coloane                  = mod_col_1 | undo_col_1
-- adauga / elimina o coloana                   = add_col_2 | remove_col_2
-- adauga / elimina o constrangere DEFAULT      = add_default_3 | remove_default_3
-- creeaza / elimina o cheie primara            = add_pk_4 | remove_pk_4
-- creeaza / elimina o cheie secundara          = add_uk_5 | remove_uk_5
-- creeaza / elimina o cheie externa            = add_fk_6 | remove_fk_6
-- creeaza / elimina un tabel                   = add_table_7 | add_table_7

CREATE PROCEDURE mod_col_1 AS BEGIN
    DECLARE @t1 VARCHAR(30), @t2 VARCHAR(30)

    SELECT @t1 = DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'MasiniTransport' AND
              COLUMN_NAME = 'DataFabricarii'

    PRINT N'Inainte de exctuia procedurii, tipul de date al coloanei era: ' + @t1 + CHAR(13)+CHAR(10);

    ALTER TABLE MasiniTransport
        ALTER COLUMN DataFabricarii VARCHAR(30)

    SELECT @t2 = DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'MasiniTransport' AND
              COLUMN_NAME = 'DataFabricarii'

    PRINT N'Dupa exctuia procedurii, tipul de date al coloanei este: ' + @t2 + CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);
END
GO

CREATE PROCEDURE undo_col_1 AS BEGIN
    DECLARE @t1 VARCHAR(30), @t2 VARCHAR(30)

    SELECT @t1 = DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'MasiniTransport' AND
              COLUMN_NAME = 'DataFabricarii'

    PRINT N'Inainte de exctuia procedurii, tipul de date al coloanei era: ' + @t1;

    ALTER TABLE MasiniTransport
        ALTER COLUMN DataFabricarii DATE

    SELECT @t2 = DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'MasiniTransport' AND
              COLUMN_NAME = 'DataFabricarii'

    PRINT N'Dupa exctuia procedurii, tipul de date al coloanei este: ' + @t2 + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
END
GO

CREATE PROCEDURE add_col_2 AS BEGIN
    DECLARE @n1 INT, @n2 INT
    SELECT @n1 = count(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME='MasiniTransport'
    PRINT N'Inainte de modificare lista erau: ' + CAST(@n1 AS VARCHAR(30)) + N' coloane';

    ALTER TABLE MasiniTransport
        ADD CaiPutere INT

    SELECT @n2 = count(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME='MasiniTransport'
    PRINT N'Dupa modificare lista sunt: ' + CAST(@n2 AS VARCHAR(30)) + N' coloane';
    PRINT CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
END
GO

CREATE PROCEDURE remove_col_2 AS BEGIN
    DECLARE @n1 INT, @n2 INT
    SELECT @n1 = count(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME='MasiniTransport'
    PRINT N'Inainte de modificare lista erau: ' + CAST(@n1 AS VARCHAR(30)) + N' coloane';

    ALTER TABLE MasiniTransport
        DROP COLUMN CaiPutere

    SELECT @n2 = count(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME='MasiniTransport'
    PRINT N'Dupa modificare lista sunt: ' + CAST(@n2 AS VARCHAR(30)) + N' coloane';
    PRINT CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
END
GO

CREATE PROCEDURE add_default_3 AS BEGIN
    DECLARE @t1 VARCHAR(30), @t2 VARCHAR(30)
    select @t1 = COLUMN_DEFAULT
                from INFORMATION_SCHEMA.COLUMNS
                where TABLE_NAME='MasiniTransport' AND COLUMN_NAME ='NrUsi'
    IF @t1 IS NULL PRINT N'Inainde de executie coloana NrUsi nu are constrangeri de tip default';
    ELSE PRINT N'Inainte de executie coloana NrUsi are constrangeri de tip default: ' + @t1;

    ALTER TABLE MasiniTransport
        ADD CONSTRAINT df_4 DEFAULT 4 FOR NrUsi

    select @t2 = COLUMN_DEFAULT
        from INFORMATION_SCHEMA.COLUMNS
        where TABLE_NAME='MasiniTransport' AND COLUMN_NAME ='NrUsi'
    IF @t2 IS NULL PRINT N'Dupa executie coloana NrUsi nu are constrangeri de tip default' + CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);
    ELSE PRINT N'Dupa executie coloana NrUsi are constrangeri de tip default: ' + @t2 + CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);
END
GO

CREATE PROCEDURE remove_default_3 AS BEGIN
    DECLARE @t1 VARCHAR(30), @t2 VARCHAR(30)
    select @t1 = COLUMN_DEFAULT
                from INFORMATION_SCHEMA.COLUMNS
                where TABLE_NAME='MasiniTransport' AND COLUMN_NAME ='NrUsi'
    IF @t1 IS NULL PRINT N'Inainde de executie coloana NrUsi nu are constrangeri de tip default';
    ELSE PRINT N'Inainte de executie coloana NrUsi are constrangeri de tip default: ' + @t1;

    ALTER TABLE MasiniTransport
        DROP CONSTRAINT df_4

    select @t2 = COLUMN_DEFAULT
        from INFORMATION_SCHEMA.COLUMNS
        where TABLE_NAME='MasiniTransport' AND COLUMN_NAME ='NrUsi'
    IF @t2 IS NULL PRINT N'Dupa executie coloana NrUsi nu are constrangeri de tip default' + CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);
    ELSE PRINT N'Dupa executie coloana NrUsi are constrangeri de tip default: ' + @t2 + CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10);
END
GO

CREATE PROCEDURE add_pk_4 AS BEGIN
    DECLARE @pk1 VARCHAR(30), @pk2 VARCHAR(30)
    SELECT @pk1 = CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_NAME = 'MasiniTransport' AND COLUMN_NAME = 'CodM'
    IF @pk1 LIKE 'PK%' PRINT N'CodM era cheie primara inainte de executie';
    ELSE PRINT N'CodM nu era cheie primara inainte de executie';

    ALTER TABLE MasiniTransport
        ADD CONSTRAINT PK_CodM PRIMARY KEY(CodM)

    SELECT @pk2 = CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_NAME = 'MasiniTransport' AND COLUMN_NAME = 'CodM'
    IF @pk2 LIKE 'PK%' PRINT N'CodM este cheie primara dupa executie' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
    ELSE PRINT N'CodM nu este cheie primara dupa executie' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
END
GO

CREATE PROCEDURE remove_pk_4 AS BEGIN
    DECLARE @pk1 VARCHAR(30), @pk2 VARCHAR(30)
    SELECT @pk1 = CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_NAME = 'MasiniTransport' AND COLUMN_NAME = 'CodM'
    IF @pk1 LIKE 'PK%' PRINT N'CodM era cheie primara inainte de executie';
    ELSE PRINT N'CodM nu era cheie primara inainte de executie';

    ALTER TABLE MasiniTransport
        DROP CONSTRAINT PK_CodM

    SELECT @pk2 = CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_NAME = 'MasiniTransport' AND COLUMN_NAME = 'CodM'
    IF @pk2 LIKE 'PK%' PRINT N'CodM este cheie primara dupa executie' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
    ELSE PRINT N'CodM nu este cheie primara dupa executie' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
END
GO

CREATE PROCEDURE add_uk_5 AS BEGIN
    DECLARE @uk1 VARCHAR(30), @uk2 VARCHAR(30)
    SELECT @uk1 = CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_NAME = 'MasiniTransport' AND COLUMN_NAME = 'CodF'
    IF @uk1 LIKE 'UK%' PRINT N'CodF era cheie secundara inainte de executie';
    ELSE PRINT N'CodF nu era cheie secundara inainte de executie';

    ALTER TABLE MasiniTransport
        ADD CONSTRAINT UK_CodF UNIQUE(CodF)

    SELECT @uk2 = CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_NAME = 'MasiniTransport' AND COLUMN_NAME = 'CodF'
    IF @uk2 LIKE 'UK%' PRINT N'CodF este cheie secundara dupa executie' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
    ELSE PRINT N'CodF nu este cheie secundara dupa executie' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
END
GO

CREATE PROCEDURE remove_uk_5 AS BEGIN
    DECLARE @uk1 VARCHAR(30), @uk2 VARCHAR(30)
    SELECT @uk1 = CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_NAME = 'MasiniTransport' AND COLUMN_NAME = 'CodF'
    IF @uk1 LIKE 'UK%' PRINT N'CodF era cheie secundara inainte de executie';
    ELSE PRINT N'CodF nu era cheie secundara inainte de executie';

    ALTER TABLE MasiniTransport
        DROP CONSTRAINT UK_CodF

    SELECT @uk2 = CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_NAME = 'MasiniTransport' AND COLUMN_NAME = 'CodF'
    IF @uk2 LIKE 'UK%' PRINT N'CodF este cheie secundara dupa executie' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
    ELSE PRINT N'CodF nu este cheie secundara dupa executie' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
END
GO

CREATE PROCEDURE add_fk_6 AS BEGIN
    DECLARE @fk1 VARCHAR(30), @fk2 VARCHAR(30)
    SELECT TOP 1 @fk1 = CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_NAME = 'MasiniTransport' AND COLUMN_NAME = 'CodF'
    IF @fk1 LIKE 'FK%' PRINT N'CodF era cheie straina inainte de executie';
    ELSE PRINT N'CodF nu era cheie straina inainte de executie';

    ALTER TABLE MasiniTransport
        ADD CONSTRAINT FK_CodF FOREIGN KEY(CodF) REFERENCES Furnizor(CodF)

    SELECT TOP 1 @fk2 = CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_NAME = 'MasiniTransport' AND COLUMN_NAME = 'CodF'
    IF @fk2 LIKE 'FK%' PRINT N'CodF este cheie straina dupa executie' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
    ELSE PRINT N'CodF nu este cheie straina dupa executie' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
END
GO

CREATE PROCEDURE remove_fk_6 AS BEGIN
    DECLARE @fk1 VARCHAR(30), @fk2 VARCHAR(30)
    SELECT TOP 1 @fk1 = CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_NAME = 'MasiniTransport' AND COLUMN_NAME = 'CodF'
    IF @fk1 LIKE 'FK%' PRINT N'CodF era cheie straina inainte de executie';
    ELSE PRINT N'CodF nu era cheie straina inainte de executie';

    ALTER TABLE MasiniTransport
        DROP CONSTRAINT FK_CodF

    SELECT TOP 1 @fk2 = CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE TABLE_NAME = 'MasiniTransport' AND COLUMN_NAME = 'CodF'
    IF @fk2 LIKE 'FK%' PRINT N'CodF este cheie straina dupa executie' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
    ELSE PRINT N'CodF nu este cheie straina dupa executie' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
END
GO

CREATE PROCEDURE add_table_7 AS BEGIN
    CREATE TABLE FurnizoriDeFurnizori(
        CodFdF INT PRIMARY KEY,
        Nume VARCHAR(30)
    )
    PRINT N'A fost adaugat tabelul FurnizoriDeFurnizori' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
END
GO

CREATE PROCEDURE remove_table_7 AS BEGIN
    DROP TABLE FurnizoriDeFurnizori
    PRINT N'A fost sters tabelul FurnizoriDeFurnizori' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);
END
GO

CREATE PROCEDURE up @v INT, @vers VARCHAR(30) OUTPUT AS
        IF @v = 1 SET @vers = 'mod_col_1'
        ELSE IF @v = 2 SET @vers = 'add_col_2'
        ELSE IF @v = 3 SET @vers = 'add_default_3'
        ELSE IF @v = 4 SET @vers = 'add_pk_4'
        ELSE IF @v = 5 SET @vers = 'add_uk_5'
        ELSE IF @v = 6 SET @vers = 'add_fk_6'
        ELSE IF @v = 7 SET @vers = 'add_table_7'
RETURN
GO

CREATE PROCEDURE down @v INT, @vers VARCHAR(30) OUTPUT AS
        IF @v = 1 SET @vers = 'undo_col_1'
        ELSE IF @v = 2 SET @vers = 'remove_col_2'
        ELSE IF @v = 3 SET @vers = 'remove_default_3'
        ELSE IF @v = 4 SET @vers = 'remove_pk_4'
        ELSE IF @v = 5 SET @vers = 'remove_uk_5'
        ELSE IF @v = 6 SET @vers = 'remove_fk_6'
        ELSE IF @v = 7 SET @vers = 'remove_table_7'
RETURN
GO

CREATE PROCEDURE main @versNext INT AS BEGIN
    DECLARE @versPrev INT, @v1 INT, @v2 INT
    IF @versNext = @versPrev PRINT N'Baza se afla deja in versiunea specificata'
    ELSE IF @versNext < 0 OR @versNext > 7 PRINT N'Exista doar 7 versiuni ale bazei de date'
    ELSE BEGIN
        SELECT TOP 1 @versPrev = CodV
            FROM Versiune
        PRINT N'Versiunea actuala a bazei de date este: ' + CAST(@versPrev AS VARCHAR(30));

        IF @versNext > @versPrev BEGIN
            SET @v1 = @versPrev + 1
            WHILE @versNext >= @v1 BEGIN
                DECLARE @Tip1 VARCHAR(30)
                EXEC up @v1,@vers = @Tip1 OUTPUT
                EXEC @Tip1
                SET @v1 = @v1 + 1
            END
            UPDATE Versiune SET CodV = @versNext
        END

        IF @versNext < @versPrev BEGIN
            SET @v2 = @versPrev
            WHILE @versNext < @v2 BEGIN
                DECLARE @Tip2 VARCHAR(30)
                EXEC down @v2,@vers = @Tip2 OUTPUT
                EXEC @Tip2
                SET @v2 = @v2 - 1
            END
            UPDATE Versiune SET CodV = @versNext
        END

        DECLARE @versNew INT
        SELECT TOP 1 @versNew = CodV
            FROM Versiune
        PRINT N'Noua versiune a bazei de date este: ' + CAST(@versNew AS VARCHAR(30));
    END
END
GO

EXEC mod_col_1
EXEC undo_col_1

EXEC add_col_2
EXEC remove_col_2

EXEC add_default_3
EXEC remove_default_3

EXEC add_pk_4
EXEC remove_pk_4

EXEC add_uk_5
EXEC remove_uk_5

EXEC add_fk_6
EXEC remove_fk_6

EXEC add_table_7
EXEC remove_table_7


EXEC mod_col_1
EXEC add_col_2
EXEC add_default_3
EXEC add_pk_4
EXEC add_uk_5
EXEC add_fk_6
EXEC add_table_7
UPDATE Versiune SET CodV = 7

EXEC remove_table_7
EXEC remove_fk_6
EXEC remove_uk_5
EXEC remove_pk_4
EXEC remove_default_3
EXEC remove_col_2
EXEC undo_col_1
UPDATE Versiune SET CodV = 0
GO

EXEC main 0