Your content #1
Your content #2
Your content #3
Your content #4
Your content #5
Your content #6
Your content #7
Great stories have a personality. Consider telling a great story that provides personality. Writing a story with personality for potential clients will assist with making a relationship connection. This shows up in small quirks like word choices or phrases. Write from your point of view, not from someone else's experience.
-- ================================================
-- Database: ivms
-- Compatible with MySQL 5.7+ / 8.0+
-- ================================================
-- Create database (if not exists)
CREATE DATABASE IF NOT EXISTS ivms
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
USE ivms;
-- ================================================
-- Create Table: attlog
-- ================================================
DROP TABLE IF EXISTS attlog;
CREATE TABLE attlog (
id INT AUTO_INCREMENT PRIMARY KEY,
employeeID NVARCHAR(50),
authDateTime DATETIME,
authDate DATE,
authTime TIME(7),
direction NVARCHAR(50),
deviceName NVARCHAR(50),
deviceSerialNo NVARCHAR(50),
cardNo NVARCHAR(50),
personName NVARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ================================================
-- Optional: Insert sample data for testing
-- ================================================
INSERT INTO attlog (
employeeID, authDateTime, authDate, authTime, direction,
deviceName, deviceSerialNo, cardNo, personName
)
VALUES
('EMP001', NOW(), CURDATE(), CURTIME(), 'IN', 'Main Gate', 'SN-12345', 'CARD001', 'John Doe'),
('EMP002', NOW(), CURDATE(), CURTIME(), 'OUT', 'Back Door', 'SN-54321', 'CARD002', 'Mary Smith');
-- ================================================
-- Verify data
-- ================================================
SELECT * FROM attlog;
-- ================================================
-- Create and Configure Database: ivms
-- Compatible with SQL Server Express 2014
-- ================================================
-- Drop database if it already exists (optional for clean setup)
IF DB_ID('ivms') IS NOT NULL
BEGIN
PRINT 'Database [ivms] already exists. Dropping...';
ALTER DATABASE [ivms] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [ivms];
END
GO
-- Create database
CREATE DATABASE [ivms]
ON PRIMARY
(
NAME = N'ivms_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\ivms_Data.mdf',
SIZE = 10MB,
FILEGROWTH = 10%
)
LOG ON
(
NAME = N'ivms_Log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\ivms_Log.ldf',
SIZE = 5MB,
FILEGROWTH = 10%
);
GO
-- Use the new database
USE [ivms];
GO
-- ================================================
-- Create Table: dbo.attlog
-- ================================================
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
IF OBJECT_ID('dbo.attlog', 'U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[attlog];
END
GO
CREATE TABLE [dbo].[attlog] (
[id] INT IDENTITY(1,1) NOT NULL,
[employeeID] NVARCHAR(50) NULL,
[authDateTime] DATETIME NULL,
[authDate] DATE NULL,
[authTime] TIME(7) NULL,
[direction] NVARCHAR(50) NULL,
[deviceName] NVARCHAR(50) NULL,
[deviceSerialNo] NVARCHAR(50) NULL,
[cardNo] NVARCHAR(50) NULL,
[personName] NVARCHAR(50) NULL,
CONSTRAINT [PK_attlog_id] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY];
GO
-- ================================================
-- Optional: Insert Sample Data
-- ================================================
INSERT INTO [dbo].[attlog]
([employeeID], [authDateTime], [authDate], [authTime], [direction], [deviceName], [deviceSerialNo], [cardNo], [personName])
VALUES
('EMP001', GETDATE(), CAST(GETDATE() AS DATE), CAST(GETDATE() AS TIME), 'IN', 'Main Gate Device', 'SN-123456', 'CARD001', N'John Doe'),
('EMP002', DATEADD(MINUTE, -10, GETDATE()), CAST(GETDATE() AS DATE), CAST(GETDATE() AS TIME), 'OUT', 'Back Door Device', 'SN-654321', 'CARD002', N'Mary Smith');
GO
-- Verify
SELECT * FROM [dbo].[attlog];
GO
-- ================================================
-- Switch back to master
-- ================================================
USE [master];
GO
ALTER DATABASE [ivms] SET READ_WRITE;
GO
PRINT 'Database [ivms] and table [attlog] created successfully.';
GO
-- ================================================
-- Create and Configure Database: ivms
-- Compatible with SQL Server 2014
-- ================================================
-- Create database
CREATE DATABASE [ivms]
ON PRIMARY
(
NAME = N'ivms_Data',
FILENAME = N'C:\SQLData\ivms_Data.mdf',
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
)
LOG ON
(
NAME = N'ivms_Log',
FILENAME = N'C:\SQLData\ivms_Log.ldf',
SIZE = 5MB,
MAXSIZE = 1GB,
FILEGROWTH = 10%
);
GO
-- Configure FILESTREAM (if enabled on the instance)
ALTER DATABASE [ivms]
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL);
GO
-- Configure recovery and durability settings
ALTER DATABASE [ivms]
SET TARGET_RECOVERY_TIME = 60 SECONDS;
GO
ALTER DATABASE [ivms]
SET DELAYED_DURABILITY = DISABLED;
GO
-- Switch to the new database
USE [ivms];
GO
-- Ensure schema exists before table creation
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'att')
EXEC('CREATE SCHEMA [att]');
GO
-- ================================================
-- Create Table: att.logg
-- ================================================
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [att].[logg] (
[serialNo] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[employeeID] NVARCHAR(50) NULL, -- Employee ID as string (can be changed to INT if numeric)
[authDateTime] DATETIME NULL, -- Full date and time of authentication
[authDate] DATENULL, -- Time portion only (HH:MM:SS)
[authTime] TIME(8) NULL, -- Time portion only (HH:MM:SS)
[deviceName] NVARCHAR(50) NULL, -- Name of the device
[deviceSerialNo] NVARCHAR(50) NULL, -- Serial number of the device
[image] VARBINARY(MAX) FILESTREAM NULL -- For storing images or binary data
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO
-- ================================================
-- Switch back to master and set database to read-write
-- ================================================
USE [master];
GO
ALTER DATABASE [ivms] SET READ_WRITE;
GO
0
-- ================================================
-- Create and Configure Database: ivms
-- Compatible with SQL Server Express 2014
-- ================================================
-- Check if the database already exists
IF DB_ID('ivms') IS NOT NULL
BEGIN
PRINT 'Database [ivms] already exists. Dropping it...';
ALTER DATABASE [ivms] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [ivms];
END
GO
-- Create the database
CREATE DATABASE [ivms]
ON PRIMARY
(
NAME = N'ivms_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\ivms_Data.mdf', -- Adjust path if different
SIZE = 10MB,
FILEGROWTH = 10%
)
LOG ON
(
NAME = N'ivms_Log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\ivms_Log.ldf',
SIZE = 5MB,
FILEGROWTH = 10%
);
GO
-- Note: FILESTREAM configuration requires FILESTREAM enabled at the SQL Server instance level.
-- To check: EXEC sp_configure filestream_access_level;
-- Skip this section safely if not using FILESTREAM.
IF (SELECT value_in_use FROM sys.configurations WHERE name = 'filestream access level') > 0
BEGIN
PRINT 'FILESTREAM detected - enabling NON_TRANSACTED_ACCESS.';
EXEC('ALTER DATABASE [ivms] SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL)');
END
ELSE
BEGIN
PRINT 'FILESTREAM not enabled - skipping FILESTREAM configuration.';
END
GO
-- Configure recovery and durability settings (safe defaults for SQL Express)
ALTER DATABASE [ivms]
SET RECOVERY SIMPLE;
GO
ALTER DATABASE [ivms]
SET TARGET_RECOVERY_TIME = 60 SECONDS;
GO
ALTER DATABASE [ivms]
SET DELAYED_DURABILITY = DISABLED;
GO
-- Switch to the new database
USE [ivms];
GO
-- ================================================
-- Create Table: dbo.logg
-- ================================================
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
IF OBJECT_ID('dbo.logg', 'U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[logg];
END
GO
CREATE TABLE [dbo].[logg] (
[serialNo] INT IDENTITY(1,1) NOT NULL, -- Added IDENTITY for unique primary key
[employeeID] NVARCHAR(50) NULL,
[authDateTime] DATETIME NULL,
[authDate] DATE NULL,
[authTime] TIME(7) NULL,
[direction] NVARCHAR(50) NULL,
[deviceName] NVARCHAR(50) NULL,
[deviceSerialNo] NVARCHAR(255) NULL
) ON [PRIMARY];
GO
-- Add primary key constraint
ALTER TABLE [dbo].[logg]
ADD CONSTRAINT [PK_logg_serialNo] PRIMARY KEY CLUSTERED ([serialNo] ASC);
GO
-- ================================================
-- Switch back to master and set database to read-write
-- ================================================
USE [master];
GO
ALTER DATABASE [ivms] SET READ_WRITE;
GO
PRINT 'Database [ivms] created and configured successfully.';
GO
-- ================================================
-- Create and Configure Database: ivms
-- Compatible with SQL Server Express 2014
-- ================================================
-- Check if the database already exists
IF DB_ID('ivms') IS NOT NULL
BEGIN
PRINT 'Database [ivms] already exists. Dropping it...';
ALTER DATABASE [ivms] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [ivms];
END
GO
-- Create the database
CREATE DATABASE [ivms]
ON PRIMARY
(
NAME = N'ivms_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\ivms_Data.mdf', -- Adjust path if different
SIZE = 10MB,
FILEGROWTH = 10%
)
LOG ON
(
NAME = N'ivms_Log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\ivms_Log.ldf',
SIZE = 5MB,
FILEGROWTH = 10%
);
GO
-- Note: FILESTREAM configuration requires FILESTREAM enabled at the SQL Server instance level.
-- To check: EXEC sp_configure filestream_access_level;
-- Skip this section safely if not using FILESTREAM.
IF (SELECT value_in_use FROM sys.configurations WHERE name = 'filestream access level') > 0
BEGIN
PRINT 'FILESTREAM detected - enabling NON_TRANSACTED_ACCESS.';
EXEC('ALTER DATABASE [ivms] SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL)');
END
ELSE
BEGIN
PRINT 'FILESTREAM not enabled - skipping FILESTREAM configuration.';
END
GO
-- Configure recovery and durability settings (safe defaults for SQL Express)
ALTER DATABASE [ivms]
SET RECOVERY SIMPLE;
GO
ALTER DATABASE [ivms]
SET TARGET_RECOVERY_TIME = 60 SECONDS;
GO
ALTER DATABASE [ivms]
SET DELAYED_DURABILITY = DISABLED;
GO
-- Switch to the new database
USE [ivms];
GO
-- ================================================
-- Create Table: dbo.logg
-- ================================================
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
IF OBJECT_ID('dbo.logg', 'U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[logg];
END
GO
CREATE TABLE [dbo].[logg] (
[serialNo] INT IDENTITY(1,1) NOT NULL, -- Added IDENTITY for unique primary key
[employeeID] NVARCHAR(50) NULL,
[authDateTime] DATETIME NULL,
[authDate] DATE NULL,
[authTime] TIME(7) NULL,
[direction] NVARCHAR(50) NULL,
[deviceName] NVARCHAR(50) NULL,
[deviceSerialNo] NVARCHAR(255) NULL
) ON [PRIMARY];
GO
-- Add primary key constraint
ALTER TABLE [dbo].[logg]
ADD CONSTRAINT [PK_logg_serialNo] PRIMARY KEY CLUSTERED ([serialNo] ASC);
GO
-- ================================================
-- Switch back to master and set database to read-write
-- ================================================
USE [master];
GO
ALTER DATABASE [ivms] SET READ_WRITE;
GO
PRINT 'Database [ivms] created and configured successfully.';
GO
-- ================================================
-- Create and Configure Database: ivms
-- Compatible with SQL Server Express 2014
-- ================================================
-- Check if the database already exists
IF DB_ID('ivms') IS NOT NULL
BEGIN
PRINT 'Database [ivms] already exists. Dropping it...';
ALTER DATABASE [ivms] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [ivms];
END
GO
-- Create the database
CREATE DATABASE [ivms]
ON PRIMARY
(
NAME = N'ivms_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\ivms_Data.mdf', -- Adjust path if different
SIZE = 10MB,
FILEGROWTH = 10%
)
LOG ON
(
NAME = N'ivms_Log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\ivms_Log.ldf',
SIZE = 5MB,
FILEGROWTH = 10%
);
GO
-- Note: FILESTREAM configuration requires FILESTREAM enabled at the SQL Server instance level.
-- To check: EXEC sp_configure filestream_access_level;
-- Skip this section safely if not using FILESTREAM.
IF (SELECT value_in_use FROM sys.configurations WHERE name = 'filestream access level') > 0
BEGIN
PRINT 'FILESTREAM detected - enabling NON_TRANSACTED_ACCESS.';
EXEC('ALTER DATABASE [ivms] SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL)');
END
ELSE
BEGIN
PRINT 'FILESTREAM not enabled - skipping FILESTREAM configuration.';
END
GO
-- Configure recovery and durability settings (safe defaults for SQL Express)
ALTER DATABASE [ivms]
SET RECOVERY SIMPLE;
GO
ALTER DATABASE [ivms]
SET TARGET_RECOVERY_TIME = 60 SECONDS;
GO
ALTER DATABASE [ivms]
SET DELAYED_DURABILITY = DISABLED;
GO
-- Switch to the new database
USE [ivms];
GO
-- ================================================
-- Create Table: dbo.logg
-- ================================================
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
IF OBJECT_ID('dbo.logg', 'U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[logg];
END
GO
CREATE TABLE [dbo].[logg] (
[serialNo] INT IDENTITY(1,1) NOT NULL, -- Added IDENTITY for unique primary key
[employeeID] NVARCHAR(50) NULL,
[authDateTime] DATETIME NULL,
[authDate] DATE NULL,
[authTime] TIME(7) NULL,
[direction] NVARCHAR(50) NULL,
[deviceName] NVARCHAR(50) NULL,
[deviceSerialNo] NVARCHAR(255) NULL
) ON [PRIMARY];
GO
-- Add primary key constraint
ALTER TABLE [dbo].[logg]
ADD CONSTRAINT [PK_logg_serialNo] PRIMARY KEY CLUSTERED ([serialNo] ASC);
GO
-- ================================================
-- Switch back to master and set database to read-write
-- ================================================
USE [master];
GO
ALTER DATABASE [ivms] SET READ_WRITE;
GO
PRINT 'Database [ivms] created and configured successfully.';
GO