EugeneX MiniWeb

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Пример как создавать таблицы MSSQL ======

<code sql>
--Sql Server 2014 Express Edition
--Batches are separated by 'go'



SELECT @@version as 'sql server version';

IF OBJECT_ID('dbo.employee', 'U') IS NOT NULL
    DROP TABLE dbo.employee;

create table [employee] (
    [EmployeeID] bigint IDENTITY(1,1),
    [Name] nvarchar(1024) NOT NULL,
    [Pass] char(32) NOT NULL,
    CONSTRAINT PK_Employee_EmployeeID PRIMARY KEY ([EmployeeID]),
    CONSTRAINT UK_Employee_Name UNIQUE NONCLUSTERED ([Name])
);

CREATE table [access] (
    [AccessID] bigint IDENTITY(1,1),
    [EmployeeID] bigint NOT NULL,
    [Permission] nvarchar(2048) NOT NULL,
    [GrantPerm] bit NOT NULL DEFAULT 0,
    CONSTRAINT PK_Access_AccessID PRIMARY KEY (AccessID),
    CONSTRAINT FK_Access_EmployeeID FOREIGN KEY (EmployeeID)
    REFERENCES dbo.employee (EmployeeID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)

INSERT INTO [employee] ( [Name] , [Pass] ) VALUES ( 'TestUser' , CONVERT(VARCHAR(32),HashBytes('MD5',CONCAT('TestUser','secret')),2) );

SELECT * FROM [employee];

INSERT INTO [access] ( [EmployeeID], [Permission], [GrantPerm] ) VALUES ((SELECT TOP 1 EmployeeID FROM [employee] WHERE [Name] = 'TestUser') , '*', 0);
INSERT INTO [access] ( [EmployeeID], [Permission], [GrantPerm] ) VALUES ((SELECT TOP 1 EmployeeID FROM [employee] WHERE [Name] = 'TestUser') , 'member:profile:own', 1);
INSERT INTO [access] ( [EmployeeID], [Permission], [GrantPerm] ) VALUES ((SELECT TOP 1 EmployeeID FROM [employee] WHERE [Name] = 'TestUser') , 'member:store:comments:*', 1);
INSERT INTO [access] ( [EmployeeID], [Permission], [GrantPerm] ) VALUES ((SELECT TOP 1 EmployeeID FROM [employee] WHERE [Name] = 'TestUser') , 'member:update:comments:own', 1);

SELECT access.*, employee.[Name] FROM [access]
LEFT JOIN [employee] ON access.[EmployeeID] = employee.[EmployeeID];
</code>
EugeneX MiniWeb

User Tools

Site Tools

Page Tools
