private void IdentifyOrCreateDBAndTableByCode(string code)
{
var sqlLocation = ConfigurationManager.AppSettings["SqlServerLocation"];
code = code.ToUpper();
string code1 = code.Split('.')[0];
string code2 = code.Split('.')[1];
var sqlScript = string.Format(@"USE [master]
if db_id('StockMinuteTransaction') is null
begin
CREATE DATABASE [StockMinuteTransaction]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'StockMinuteTransaction', FILENAME = N'{2}\StockMinuteTransaction.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'StockMinuteTransaction_log', FILENAME = N'{2}\StockMinuteTransaction_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
ALTER DATABASE [StockMinuteTransaction] SET COMPATIBILITY_LEVEL = 120
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [StockMinuteTransaction].[dbo].[sp_fulltext_database] @action = 'enable'
end
end
go
if object_id('[StockMinuteTransaction].[dbo].[{0}_{1}]') is null
begin
CREATE TABLE [StockMinuteTransaction].[dbo].[{0}_{1}](
[Code] [varchar](20) NOT NULL,
[DateTime] [datetime] NOT NULL,
[open] [decimal](12, 4) NULL,
[high] [decimal](12, 4) NULL,
[low] [decimal](12, 4) NULL,
[close] [decimal](12, 4) NULL,
[volume] [decimal](20, 4) NULL,
[amount] [decimal](20, 4) NULL,
[UpdatedDateTime] [datetime] NULL,
CONSTRAINT [PK_{0}_{1}] PRIMARY KEY CLUSTERED
(
[Code] ASC,
[DateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [StockMinuteTransaction].[dbo].[{0}_{1}] ADD CONSTRAINT [DF_{0}_{1}_UpdatedDateTime] DEFAULT (getdate()) FOR [UpdatedDateTime]
end ", code1, code2, sqlLocation);
sqlWriter.ExecuteSqlScript(sqlScript);
}
private void CreateDBOrTableIfNecessary(string databaseName, string tableName)
{
var fileLocation = ConfigurationManager.AppSettings["SqlServerLocation"];
var sqlScript = string.Format(@"use master
if db_id('{0}') is null
begin
CREATE DATABASE [{0}]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'{0}', FILENAME = N'{2}\{0}.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'{0}_log', FILENAME = N'{2}\{0}_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
ALTER DATABASE [{0}] SET COMPATIBILITY_LEVEL = 120
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [{0}].[dbo].[sp_fulltext_database] @action = 'enable'
end
end
go
if object_id('[{0}].dbo.[{1}]') is null
begin
CREATE TABLE [{0}].[dbo].[{1}](
[tdatetime] [datetime] NOT NULL,
[code] varchar(20) NOT NULL,
[open] [decimal](10, 4) NULL,
[high] [decimal](10, 4) NULL,
[low] [decimal](10, 4) NULL,
[close] [decimal](10, 4) NULL,
[delaynum] [decimal](4, 0) NULL,
[startnum] [decimal](4, 0) NULL,
[calculatornum] [decimal](4, 0) NULL,
[longsignal] [decimal](4, 0) NULL,
[shortsignal] [decimal](4, 0) NULL,
[LastUpdatedTime] [datetime] NULL
) ON [PRIMARY]
ALTER TABLE [{0}].[dbo].[{1}] ADD CONSTRAINT [DF_{1}_LastUpdatedTime] DEFAULT (getdate()) FOR [LastUpdatedTime]
CREATE NONCLUSTERED INDEX [IX_{1}_1] ON [{0}].[dbo].[{1}]
(
[tdatetime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
end", databaseName, tableName, fileLocation);
sqlWriter.ExecuteSqlScript(sqlScript);
}
private void CreateDBOrTableIfNecessary(DateTime date)
{
var fileLocation = ConfigurationManager.AppSettings["SqlServerLocation"];
var sqlScript = string.Format(@"use master
if db_id('ivix{0}') is null
begin
CREATE DATABASE [ivix{0}]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'ivix{0}', FILENAME = N'{2}\ivix{0}.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ivix{0}_log', FILENAME = N'{2}\ivix{0}_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
ALTER DATABASE [ivix{0}] SET COMPATIBILITY_LEVEL = 120
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ivix{0}].[dbo].[sp_fulltext_database] @action = 'enable'
end
end
go
if object_id('[ivix{0}].dbo.[{1}]') is null
begin
CREATE TABLE [ivix{0}].[dbo].[{1}](
[tdatetime] [datetime] NOT NULL,
[expiredate1] [datetime] NULL,
[expiredate2] [datetime] NULL,
[duration1] [decimal](10, 6) NULL,
[duration2] [decimal](10, 6) NULL,
[sigma1Ask] [decimal](10, 4) NULL,
[sigma1Bid] [decimal](10, 4) NULL,
[sigma2Ask] [decimal](10, 4) NULL,
[sigma2Bid] [decimal](10, 4) NULL,
[sigmaAsk] [decimal](10, 4) NULL,
[sigmaBid] [decimal](10, 4) NULL,
[vegaTotal] [decimal](10, 4) NULL,
[number] [decimal](10, 4) NULL,
[percentAskMax] [decimal](10, 4) NULL,
[percentAskMin] [decimal](10, 4) NULL,
[percentBidMax] [decimal](10, 4) NULL,
[percentBidMin] [decimal](10, 4) NULL,
[percentVolumeMax] [decimal](10, 4) NULL,
[percentVolumeMin] [decimal](10, 4) NULL,
[LastUpdatedTime] [datetime] NULL
) ON [PRIMARY]
ALTER TABLE [ivix{0}].[dbo].[{1}] ADD CONSTRAINT [DF_{1}_LastUpdatedTime] DEFAULT (getdate()) FOR [LastUpdatedTime]
CREATE NONCLUSTERED INDEX [IX_{1}_1] ON [ivix{0}].[dbo].[{1}]
(
[tdatetime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
end", date.Year, date.ToString("yyyy"), fileLocation);
sqlWriter.ExecuteSqlScript(sqlScript);
}
private void CreateDBOrTableIfNecessary(DateTime date)
{
var fileLocation = ConfigurationManager.AppSettings["SqlServerLocation"];
var sqlScript = string.Format(@"use master
if db_id('Impv{0}') is null
begin
CREATE DATABASE [Impv{0}]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Impv{0}', FILENAME = N'{2}\Impv{0}.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Impv{0}_log', FILENAME = N'{2}\Impv{0}_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
ALTER DATABASE [Impv{0}] SET COMPATIBILITY_LEVEL = 120
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Impv{0}].[dbo].[sp_fulltext_database] @action = 'enable'
end
end
go
if object_id('[Impv{0}].dbo.[{1}]') is null
begin
CREATE TABLE [Impv{0}].[dbo].[{1}](
[code] varchar(20) NOT NULL,
[tdatetime] [datetime] NOT NULL,
[strike] [decimal](8, 4) NULL,
[call_or_put] [nvarchar](10) NULL,
[expiredate] [decimal](10, 0) NULL,
[duration] [decimal] (10,6) NULL,
[maturitydate] [datetime] NOT NULL,
[etfPrice] [decimal](12, 4) NULL,
[futurePrice] [decimal](12, 4) NULL,
[futurePrice0] [decimal](12, 4) NULL,
[ask] [decimal](12, 4) NULL,
[bid] [decimal](12, 4) NULL,
[ask_impv] [decimal](12, 4) NULL,
[bid_impv] [decimal](12, 4) NULL,
[LastUpdatedTime] [datetime] NULL
) ON [PRIMARY]
ALTER TABLE [Impv{0}].[dbo].[{1}] ADD CONSTRAINT [DF_{1}_LastUpdatedTime] DEFAULT (getdate()) FOR [LastUpdatedTime]
CREATE NONCLUSTERED INDEX [IX_{1}] ON [Impv{0}].[dbo].[{1}]
(
[strike] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
CREATE NONCLUSTERED INDEX [IX_{1}_1] ON [Impv{0}].[dbo].[{1}]
(
[tdatetime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
end", date.Year, date.ToString("yyyy-MM-dd"), fileLocation);
sqlWriter.ExecuteSqlScript(sqlScript);
}
private void IdentifyOrCreateDBandDataTable()
{
var sqlLocation = ConfigurationManager.AppSettings["SqlServerLocation"];
if (!Directory.Exists(sqlLocation))
{
Directory.CreateDirectory(sqlLocation);
}
var sqlScript = string.Format(@"USE [master]
if db_id('DailyTransaction') is null
begin
CREATE DATABASE [DailyTransaction]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'DailyTransaction', FILENAME = N'{0}\DailyTransaction.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'DailyTransaction_log', FILENAME = N'{0}\DailyTransaction_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
ALTER DATABASE [DailyTransaction] SET COMPATIBILITY_LEVEL = 120
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DailyTransaction].[dbo].[sp_fulltext_database] @action = 'enable'
end
end
go
if object_id('DailyTransaction.dbo.StockOption') is null
begin
CREATE TABLE [DailyTransaction].[dbo].[StockOption](
[Code] [varchar](20) NOT NULL,
[DateTime] [date] NOT NULL,
[OPEN] [decimal](12, 4) NULL,
[HIGH] [decimal](12, 4) NULL,
[LOW] [decimal](12, 4) NULL,
[CLOSE] [decimal](12, 4) NULL,
[VOLUME] [decimal](20, 0) NULL,
[AMT] [decimal](20, 3) NULL,
[SETTLE] [decimal](20, 4) NULL,
[OI] [decimal](20, 0) NULL,
[UpdatedDateTime] [datetime] NULL CONSTRAINT [DF_StockOption_UpdatedDateTime] DEFAULT (getdate()),
CONSTRAINT [PK_StockOption_1] PRIMARY KEY CLUSTERED
(
[Code] ASC,
[DateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
end", sqlLocation);
sqlWriter.ExecuteSqlScript(sqlScript);
}
private void CreateDBOrTableIfNecessary(string indexCode, string parameters)
{
var fileLocation = ConfigurationManager.AppSettings["SqlServerLocation"];
var sqlScript = string.Format(@"use master
if db_id('BasisData{0}') is null
begin
CREATE DATABASE [BasisData{0}]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'BasisData{0}', FILENAME = N'{2}\BasisData{0}.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'BasisData{0}_log', FILENAME = N'{2}\BasisData{0}_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
ALTER DATABASE [BasisData{0}] SET COMPATIBILITY_LEVEL = 120
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [BasisData{0}].[dbo].[sp_fulltext_database] @action = 'enable'
end
end
go
if object_id('[BasisData{0}].dbo.[{1}]') is null
begin
CREATE TABLE [BasisData{0}].[dbo].[{1}](
[tdatetime] [datetime] NOT NULL,
[future1] varchar(20) NOT NULL,
[future2] varchar(20) NOT NULL,
[expiredate1] [datetime] NOT NULL,
[expiredate2] [datetime] NOT NULL,
[duration1] [decimal](12, 4) NULL,
[duration2] [decimal](12, 4) NULL,
[indexPrice] [decimal](12, 4) NULL,
[price1] [decimal](12, 4) NULL,
[price2] [decimal](12, 4) NULL,
[basis1] [decimal](12, 4) NULL,
[basis2] [decimal](12, 4) NULL,
[basis12] [decimal](12, 4) NULL,
[LastUpdatedTime] [datetime] NULL
) ON [PRIMARY]
ALTER TABLE [BasisData{0}].[dbo].[{1}] ADD CONSTRAINT [DF_{1}_LastUpdatedTime] DEFAULT (getdate()) FOR [LastUpdatedTime]
CREATE NONCLUSTERED INDEX [IX_{1}_1] ON [BasisData{0}].[dbo].[{1}]
(
[tdatetime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
end", indexCode, parameters, fileLocation);
sqlWriter.ExecuteSqlScript(sqlScript);
}
private void CreateStockBonusInfoInDB()
{
var sqlScript = string.Format(@"create table common.dbo.StockBonusInfo (
[ExDividendDate] [datetime] NOT NULL,
[Code] [varchar](20) NOT NULL,
[SecName] [varchar](20) NOT NULL,
[CashPayoutRatio] [decimal](12, 4) NULL,
[StockSplitRatio] [decimal](12, 4) NULL,
[StockDividendRatio] [decimal](12, 4) NULL,
[SeoRatio] [decimal](12, 4) NULL,
[SeoPrice] [decimal](12, 4) NULL,
[RightsIssuePrice] [decimal](12, 4) NULL,
[RightsIssueRatio] [decimal](12, 4) NULL,
[ExDividendNote] [varchar](200) NOT NULL,
[UpdateTime] [datetime] NOT NULL)");
sqlWriter.ExecuteSqlScript(sqlScript);
}
//构造SQL表结构
private void IdentifyOrCreateDBAndTable(DateTime date)
{
var sqlLocation = ConfigurationManager.AppSettings["SqlServerLocation"];
var sqlScript = string.Format(@"USE [master]
if db_id('StockInfo') is null
begin
CREATE DATABASE [StockInfo]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'StockInfo', FILENAME = N'{0}\StockInfo.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'StockInfo_log', FILENAME = N'{0}\StockInfo.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
ALTER DATABASE [StockInfo] SET COMPATIBILITY_LEVEL = 120
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [StockInfo].[dbo].[sp_fulltext_database] @action = 'enable'
end
end
go
if object_id('[StockInfo].[dbo].[BasicInfoDaily{1}]') is null
begin
CREATE TABLE [StockInfo].[dbo].[BasicInfoDaily{1}](
[Code] [varchar](20) NOT NULL,
[DateTime] [datetime] NOT NULL,
[UpdatedDateTime] [datetime] NULL,
CONSTRAINT [PK_BasicInfoDaily{1}] PRIMARY KEY CLUSTERED
(
[Code] ASC,
[DateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [StockInfo].[dbo].[BasicInfoDaily{1}] ADD CONSTRAINT [DF_BasicInfoDaily{1}_UpdatedDateTime] DEFAULT (getdate()) FOR [UpdatedDateTime]
end ", sqlLocation, date.Year);
sqlWriter.ExecuteSqlScript(sqlScript);
}
private void CreateDBOrTableIfNecessary(DateTime date)
{
var fileLocation = ConfigurationManager.AppSettings["SqlServerLocation"];
var sqlScript = string.Format(@"use master
if db_id('StockTickTransaction{0}') is null
begin
CREATE DATABASE [StockTickTransaction{0}]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'StockTickTransaction{0}', FILENAME = N'{2}\StockTickTransaction{0}.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'StockTickTransaction{0}_log', FILENAME = N'{2}\StockTickTransaction{0}_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
ALTER DATABASE [StockTickTransaction{0}] SET COMPATIBILITY_LEVEL = 120
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [StockTickTransaction{0}].[dbo].[sp_fulltext_database] @action = 'enable'
end
end
go
if object_id('[StockTickTransaction{0}].dbo.[{1}]') is null
begin
CREATE TABLE [StockTickTransaction{0}].[dbo].[{1}](
[stkcd] [char](10) NOT NULL,
[tdatetime] [datetime] NOT NULL,
[cp] [decimal](12, 4) NULL,
[S1] [decimal](12, 4) NULL,
[S2] [decimal](12, 4) NULL,
[S3] [decimal](12, 4) NULL,
[S4] [decimal](12, 4) NULL,
[S5] [decimal](12, 4) NULL,
[B1] [decimal](12, 4) NULL,
[B2] [decimal](12, 4) NULL,
[B3] [decimal](12, 4) NULL,
[B4] [decimal](12, 4) NULL,
[B5] [decimal](12, 4) NULL,
[SV1] [decimal](10, 0) NULL,
[SV2] [decimal](10, 0) NULL,
[SV3] [decimal](10, 0) NULL,
[SV4] [decimal](10, 0) NULL,
[SV5] [decimal](10, 0) NULL,
[BV1] [decimal](10, 0) NULL,
[BV2] [decimal](10, 0) NULL,
[BV3] [decimal](10, 0) NULL,
[BV4] [decimal](10, 0) NULL,
[BV5] [decimal](10, 0) NULL,
[ts] [decimal](20, 0) NULL,
[tt] [decimal](20, 3) NULL,
[HighLimit] [decimal](12, 4) NULL,
[LowLimit] [decimal](12, 4) NULL,
[LastUpdatedTime] [datetime] NULL
) ON [PRIMARY]
ALTER TABLE [StockTickTransaction{0}].[dbo].[{1}] ADD CONSTRAINT [DF_{1}_LastUpdatedTime] DEFAULT (getdate()) FOR [LastUpdatedTime]
CREATE NONCLUSTERED INDEX [IX_{1}] ON [StockTickTransaction{0}].[dbo].[{1}]
(
[stkcd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
CREATE NONCLUSTERED INDEX [IX_{1}_1] ON [StockTickTransaction{0}].[dbo].[{1}]
(
[tdatetime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
end", date.Year, date.ToString("yyyy-MM-dd"), fileLocation);
sqlWriter.ExecuteSqlScript(sqlScript);
}
private static void CreateCommonDBAndTables(string filePath, SqlServerWriter sqlWriter)
{
string sqlStr = string.Format(@"use [master];
if db_id('common') is null
begin
CREATE DATABASE [Common]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Common', FILENAME = N'{0}\Common.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Common_log', FILENAME = N'{0}\Common_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
ALTER DATABASE [Common] SET COMPATIBILITY_LEVEL = 120
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Common].[dbo].[sp_fulltext_database] @action = 'enable'
end
end
go
if object_id('Common.dbo.[TransactionDate]') is null
begin
CREATE TABLE [Common].[dbo].[TransactionDate](
[DateTime] [date] NOT NULL,
CONSTRAINT [PK_TransactionDate] PRIMARY KEY CLUSTERED
([DateTime] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
end
go
if object_id('Common.dbo.OptionInfo') is null
begin
CREATE TABLE [Common].[dbo].[OptionInfo](
[wind_code] [varchar](20) NOT NULL,
[sec_name] [nvarchar](30) NULL,
[option_mark_code] [varchar](15) NOT NULL,
[exercise_mode] [nvarchar](10) NULL,
[exercise_price] [nvarchar](10) NULL,
[call_or_put] [nvarchar](10) NULL,
[contract_unit] [int] NULL,
[listed_date] [date] NULL,
[expire_date] [date] NULL,
[update_date_time] [datetime] NULL,
CONSTRAINT [PK_OptionInfo] PRIMARY KEY CLUSTERED
(
[wind_code] ASC,
[option_mark_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [Common].[dbo].[OptionInfo] ADD CONSTRAINT [DF_OptionInfo_update_date_time] DEFAULT (getdate()) FOR [update_date_time]
end
go
if object_id('Common.dbo.StockInfo') is null
begin
CREATE TABLE [Common].[dbo].[StockInfo](
[Code] [varchar](12) NOT NULL,
[SecName] [nvarchar](12) NULL,
[IPODate] [date] NULL,
[DelistDate] [date] NULL,
[UpdateDateTime] [datetime] NULL,
CONSTRAINT [PK_StockInfo] PRIMARY KEY CLUSTERED
(
[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [Common].[dbo].[StockInfo] ADD CONSTRAINT [DF_StockInfo_UpdateDateTime] DEFAULT (getdate()) FOR [UpdateDateTime]
end
GO", filePath);
sqlWriter.ExecuteSqlScript(sqlStr);
}