260 lines
16 KiB
Transact-SQL
260 lines
16 KiB
Transact-SQL
USE [master]
|
||
GO
|
||
/****** Object: Database [ais] Script Date: 04/23/2019 10:24:29 ******/
|
||
CREATE DATABASE [ais] ON PRIMARY
|
||
( NAME = N'ais', FILENAME = N'Z:\MSSQL\Data\ais.mdf' , SIZE = 500352KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
|
||
LOG ON
|
||
( NAME = N'ais_log', FILENAME = N'Z:\MSSQL\Data\ais_log.ldf' , SIZE = 3392KB , MAXSIZE = 2048000KB , FILEGROWTH = 10%)
|
||
GO
|
||
ALTER DATABASE [ais] SET COMPATIBILITY_LEVEL = 100
|
||
GO
|
||
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
|
||
begin
|
||
EXEC [ais].[dbo].[sp_fulltext_database] @action = 'enable'
|
||
end
|
||
GO
|
||
ALTER DATABASE [ais] SET ANSI_NULL_DEFAULT OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET ANSI_NULLS OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET ANSI_PADDING OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET ANSI_WARNINGS OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET ARITHABORT OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET AUTO_CLOSE OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET AUTO_CREATE_STATISTICS ON
|
||
GO
|
||
ALTER DATABASE [ais] SET AUTO_SHRINK ON
|
||
GO
|
||
ALTER DATABASE [ais] SET AUTO_UPDATE_STATISTICS ON
|
||
GO
|
||
ALTER DATABASE [ais] SET CURSOR_CLOSE_ON_COMMIT OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET CURSOR_DEFAULT GLOBAL
|
||
GO
|
||
ALTER DATABASE [ais] SET CONCAT_NULL_YIELDS_NULL OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET NUMERIC_ROUNDABORT OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET QUOTED_IDENTIFIER OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET RECURSIVE_TRIGGERS OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET DISABLE_BROKER
|
||
GO
|
||
ALTER DATABASE [ais] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET DATE_CORRELATION_OPTIMIZATION OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET TRUSTWORTHY OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET ALLOW_SNAPSHOT_ISOLATION OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET PARAMETERIZATION SIMPLE
|
||
GO
|
||
ALTER DATABASE [ais] SET READ_COMMITTED_SNAPSHOT OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET HONOR_BROKER_PRIORITY OFF
|
||
GO
|
||
ALTER DATABASE [ais] SET READ_WRITE
|
||
GO
|
||
ALTER DATABASE [ais] SET RECOVERY SIMPLE
|
||
GO
|
||
ALTER DATABASE [ais] SET MULTI_USER
|
||
GO
|
||
ALTER DATABASE [ais] SET PAGE_VERIFY CHECKSUM
|
||
GO
|
||
ALTER DATABASE [ais] SET DB_CHAINING OFF
|
||
GO
|
||
USE [ais]
|
||
GO
|
||
/****** Object: User [aisuser] Script Date: 04/23/2019 10:24:29 ******/
|
||
CREATE USER [aisuser] FOR LOGIN [aisuser] WITH DEFAULT_SCHEMA=[dbo]
|
||
GO
|
||
/****** Object: Table [dbo].[shipinfo2] Script Date: 04/23/2019 10:24:31 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
SET ANSI_PADDING ON
|
||
GO
|
||
CREATE TABLE [dbo].[shipinfo2](
|
||
[mmsi] [varchar](100) NOT NULL,
|
||
[mtime] [varchar](100) NULL,
|
||
[status] [varchar](100) NULL,
|
||
[type] [varchar](100) NULL,
|
||
[lat] [varchar](100) NULL,
|
||
[lon] [varchar](100) NULL,
|
||
[speed] [float] NULL,
|
||
[course] [varchar](100) NULL,
|
||
[heading] [varchar](100) NULL,
|
||
[draft] [varchar](100) NULL,
|
||
[width] [varchar](100) NULL,
|
||
[length] [varchar](100) NULL,
|
||
[name] [varchar](100) NULL,
|
||
[call] [varchar](100) NULL,
|
||
[dest] [varchar](100) NULL,
|
||
[eta] [varchar](100) NULL,
|
||
[reg] [varchar](100) NULL,
|
||
[imo] [varchar](100) NULL,
|
||
[l1] [varchar](100) NULL,
|
||
[w1] [varchar](100) NULL,
|
||
[extracode] [varchar](1) NULL,
|
||
[timestamp] [datetime] NULL,
|
||
[statusVT] [varchar](1) NULL,
|
||
[typeVT] [varchar](1) NULL,
|
||
[erfdat] [datetime] NULL,
|
||
CONSTRAINT [PK_shipinfo2] PRIMARY KEY CLUSTERED
|
||
(
|
||
[mmsi] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
SET ANSI_PADDING OFF
|
||
GO
|
||
/****** Object: Table [dbo].[hotposition] Script Date: 04/23/2019 10:24:31 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[hotposition](
|
||
[Id] [uniqueidentifier] NOT NULL,
|
||
[mmsi] [int] NULL,
|
||
[pid] [uniqueidentifier] NULL,
|
||
PRIMARY KEY CLUSTERED
|
||
(
|
||
[Id] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[aisstation] Script Date: 04/23/2019 10:24:31 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[aisstation](
|
||
[Id] [uniqueidentifier] NOT NULL,
|
||
[lat] [int] NULL,
|
||
[lon] [int] NULL,
|
||
[telnetHost] [nvarchar](255) NULL,
|
||
[telnetPort] [int] NULL,
|
||
[comPort] [nvarchar](10) NULL,
|
||
[name] [nvarchar](255) NULL,
|
||
[baudrate] [int] NULL,
|
||
[address] [nvarchar](512) NULL,
|
||
[active] [bit] NULL,
|
||
PRIMARY KEY CLUSTERED
|
||
(
|
||
[Id] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[aisstaticdata] Script Date: 04/23/2019 10:24:31 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[aisstaticdata](
|
||
[Id] [uniqueidentifier] NOT NULL,
|
||
[aisversion] [int] NULL,
|
||
[imoNumber] [int] NULL,
|
||
[callsign] [nvarchar](10) NULL,
|
||
[name] [nvarchar](100) NULL,
|
||
[shiptype] [int] NULL,
|
||
[dimension] [int] NULL,
|
||
[typeofdevice] [int] NULL,
|
||
[maxpresetstaticdraught] [int] NULL,
|
||
[destination] [nvarchar](255) NULL,
|
||
[dte] [int] NULL,
|
||
[spare] [int] NULL,
|
||
[mmsi] [int] NOT NULL,
|
||
[classb] [bit] NULL,
|
||
[breadth] [int] NULL,
|
||
[length] [int] NULL,
|
||
[aisstation_id] [uniqueidentifier] NULL,
|
||
[eta] [datetime] NULL,
|
||
[Changed] [smalldatetime] NULL,
|
||
[UpdateCount] [int] NOT NULL,
|
||
PRIMARY KEY CLUSTERED
|
||
(
|
||
[Id] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
CREATE UNIQUE NONCLUSTERED INDEX [idx_mmsi] ON [dbo].[aisstaticdata]
|
||
(
|
||
[mmsi] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Table [dbo].[aisposreport] Script Date: 04/23/2019 10:24:31 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE TABLE [dbo].[aisposreport](
|
||
[Id] [uniqueidentifier] NOT NULL,
|
||
[MMSI] [int] NOT NULL,
|
||
[NavStatus] [int] NULL,
|
||
[ROT] [int] NULL,
|
||
[COG] [int] NULL,
|
||
[SOG] [int] NULL,
|
||
[Accuracy] [int] NULL,
|
||
[Longitude] [int] NULL,
|
||
[Latitude] [int] NULL,
|
||
[Heading] [int] NULL,
|
||
[Timestamp] [datetime] NULL,
|
||
[Reserved] [int] NULL,
|
||
[Spare] [int] NULL,
|
||
[Raim] [int] NULL,
|
||
[CommState] [int] NULL,
|
||
[AISStationId] [uniqueidentifier] NOT NULL,
|
||
PRIMARY KEY CLUSTERED
|
||
(
|
||
[Id] ASC
|
||
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
) ON [PRIMARY]
|
||
GO
|
||
CREATE NONCLUSTERED INDEX [_dta_index_aisposreport_7_2105058535__K2_1_3_5_6_8_9_10_11] ON [dbo].[aisposreport]
|
||
(
|
||
[MMSI] ASC
|
||
)
|
||
INCLUDE ( [Id],
|
||
[NavStatus],
|
||
[COG],
|
||
[SOG],
|
||
[Longitude],
|
||
[Latitude],
|
||
[Heading],
|
||
[Timestamp]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
GO
|
||
CREATE NONCLUSTERED INDEX [special_cursor_index_13_08_2018] ON [dbo].[aisposreport]
|
||
(
|
||
[Timestamp] ASC
|
||
)
|
||
INCLUDE ( [MMSI],
|
||
[NavStatus],
|
||
[COG],
|
||
[SOG],
|
||
[Longitude],
|
||
[Latitude],
|
||
[Heading]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
||
GO
|
||
/****** Object: Default [DF__hotposition__Id__0F975522] Script Date: 04/23/2019 10:24:31 ******/
|
||
ALTER TABLE [dbo].[hotposition] ADD DEFAULT (newid()) FOR [Id]
|
||
GO
|
||
/****** Object: Default [DF__aisstation__Id__060DEAE8] Script Date: 04/23/2019 10:24:31 ******/
|
||
ALTER TABLE [dbo].[aisstation] ADD DEFAULT (newid()) FOR [Id]
|
||
GO
|
||
/****** Object: Default [DF__aisstaticdat__Id__0AD2A005] Script Date: 04/23/2019 10:24:31 ******/
|
||
ALTER TABLE [dbo].[aisstaticdata] ADD DEFAULT (newid()) FOR [Id]
|
||
GO
|
||
/****** Object: Default [DF__aisstatic__Updat__108B795B] Script Date: 04/23/2019 10:24:31 ******/
|
||
ALTER TABLE [dbo].[aisstaticdata] ADD DEFAULT ((0)) FOR [UpdateCount]
|
||
GO
|
||
/****** Object: Default [DF__aisposreport__Id__014935CB] Script Date: 04/23/2019 10:24:31 ******/
|
||
ALTER TABLE [dbo].[aisposreport] ADD DEFAULT (newid()) FOR [Id]
|
||
GO
|