/db/sql/20111021094427_install_up.sql

 1
 2IF EXISTS(SELECT name FROM sysobjects WHERE name = N'settings' AND xtype='U') DROP TABLE [settings];
 3CREATE TABLE [settings] (
 4 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
 5 [settingname] VARCHAR(100),
 6 [settingvalue] VARCHAR(100),
 7 [createdat] DATETIME,
 8 [updatedat] DATETIME,
 9 [deletedat] DATETIME
10);
11INSERT INTO [settings] ( [SETTINGNAME],[SETTINGVALUE] ) VALUES ( 'installed',0 );
12IF EXISTS(SELECT name FROM sysobjects WHERE name = N'companies' AND xtype='U') DROP TABLE [companies];
13CREATE TABLE [companies] (
14 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
15 [companyname] VARCHAR(200) NOT NULL,
16 [address] VARCHAR(1000),
17 [phone] VARCHAR(50),
18 [fax] VARCHAR(50),
19 [createdat] DATETIME,
20 [updatedat] DATETIME,
21 [deletedat] DATETIME
22);
23IF EXISTS(SELECT name FROM sysobjects WHERE name = N'organizations' AND xtype='U') DROP TABLE [organizations];
24CREATE TABLE [organizations] (
25 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
26 [organizationname] VARCHAR(255) NOT NULL,
27 [companyid] INT DEFAULT NULL,
28 [createdat] DATETIME,
29 [updatedat] DATETIME,
30 [deletedat] DATETIME,
31 CONSTRAINT [FK_organizations_companies] FOREIGN KEY ([companyid]) REFERENCES companies([id])
32);
33IF EXISTS(SELECT name FROM sysobjects WHERE name = N'grades' AND xtype='U') DROP TABLE [grades];
34CREATE TABLE [grades] (
35 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
36 [gradename] VARCHAR(255) NOT NULL,
37 [companyid] INT DEFAULT NULL,
38 [createdat] DATETIME,
39 [updatedat] DATETIME,
40 [deletedat] DATETIME,
41 CONSTRAINT [FK_grades_companies] FOREIGN KEY ([companyid]) REFERENCES companies([id])
42);
43IF EXISTS(SELECT name FROM sysobjects WHERE name = N'users' AND xtype='U') DROP TABLE [users];
44CREATE TABLE [users] (
45 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
46 [username] VARCHAR(50) NOT NULL,
47 [userpassword] VARCHAR(128) NOT NULL,
48 [isadmin] VARCHAR(1) NOT NULL,
49 [userstatus] VARCHAR(1) NOT NULL,
50 [crosscompany] VARCHAR(1) NOT NULL,
51 [lastlogin] DATETIME,
52 [createdat] DATETIME,
53 [updatedat] DATETIME,
54 [deletedat] DATETIME
55);
56IF EXISTS(SELECT name FROM sysobjects WHERE name = N'userroles' AND xtype='U') DROP TABLE [userroles];
57CREATE TABLE [userroles] (
58 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
59 [userid] INT DEFAULT NULL,
60 [role] VARCHAR(200) NOT NULL,
61 [createdat] DATETIME,
62 [updatedat] DATETIME,
63 [deletedat] DATETIME,
64 CONSTRAINT [FK_userroles_users] FOREIGN KEY ([userid]) REFERENCES users([id])
65);
66IF EXISTS(SELECT name FROM sysobjects WHERE name = N'people' AND xtype='U') DROP TABLE [people];
67CREATE TABLE [people] (
68 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
69 [firstname] VARCHAR(50) NOT NULL,
70 [middlename] VARCHAR(50),
71 [lastname] VARCHAR(50),
72 [idcard] VARCHAR(50),
73 [birthplace] VARCHAR(100),
74 [birthdate] DATETIME,
75 [maritalstatus] VARCHAR(20),
76 [userid] INT DEFAULT NULL,
77 [createdat] DATETIME,
78 [updatedat] DATETIME,
79 [deletedat] DATETIME,
80 CONSTRAINT [FK_people_users] FOREIGN KEY ([userid]) REFERENCES users([id])
81);
82IF EXISTS(SELECT name FROM sysobjects WHERE name = N'employees' AND xtype='U') DROP TABLE [employees];
83CREATE TABLE [employees] (
84 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
85 [startdate] DATETIME NOT NULL,
86 [enddate] DATETIME,
87 [personid] INT DEFAULT NULL,
88 [companyid] INT DEFAULT NULL,
89 [organizationid] INT DEFAULT NULL,
90 [gradeid] INT DEFAULT NULL,
91 [createdat] DATETIME,
92 [updatedat] DATETIME,
93 [deletedat] DATETIME,
94 CONSTRAINT [FK_employees_people] FOREIGN KEY ([personid]) REFERENCES people([id]),
95 CONSTRAINT [FK_employees_companies] FOREIGN KEY ([companyid]) REFERENCES companies([id]),
96 CONSTRAINT [FK_employees_organizations] FOREIGN KEY ([organizationid]) REFERENCES organizations([id]),
97 CONSTRAINT [FK_employees_grades] FOREIGN KEY ([gradeid]) REFERENCES grades([id])
98);