Nuva 示例代码(每日一帖)之 模板示例(3)

<..========================================================
==
==
==
Macrobject Common Library ==
==
==
==
Copyright (c) 2004-2006 Macrobject Software ==
==
==
==
ALL RIGHTS RESERVED ==
==
==
==
http://www.macrobject.com ==
==
==
========================================================
..>
<.using("Function.sql.nuva").>

<.
foreach(T = System.Data.DefaultSchema.Tables | T.IsEnabled).>
CREATE TABLE [dbo].[<.=T.Name.>] (
<.var index = 0.>
<.foreach(F = T.Fields | F.IsEnabled).>
<.if(index > 0).>,
<./if.>
[<.=F.Name.>] [<.=GetDataType(Field = F).>]<.=GetDataSize(Field = F).> <.=GetNullable(Field=F).><.=GetIdentity(Field = F).>
<.index ++.>
<./foreach.]
) ON [PRIMARY]
GO

<./foreach.>

<.
foreach(T = System.Data.DefaultSchema.Tables | T.IsEnabled).>
ALTER TABLE [dbo].[<.=T.Name.>] ADD
CONSTRAINT [PK_<.=T.Name.>] PRIMARY KEY NONCLUSTERED
(
<.var index = 0.>
<.foreach(F = T.Fields | F.IsEnabled).>
<.if(F.IsPrimary).>
<.if(index > 0).>,
<./if.> [<.=F.Name.>]<.index ++.>
<./if.>
<./foreach.]
) ON [PRIMARY]
GO

<./foreach.>

<.
foreach(T = System.Data.DefaultSchema.Tables | T.IsEnabled).>
<.if(T.ParentRelations.Count > 0).>
<.foreach(P = T.ParentRelations | P.IsEnabled).>
CREATE INDEX [<.=P.Name.>] ON [dbo].[<.=T.Name.>](<.var index=0.>
<.foreach(RF = P.RelationFields).>
<.if(index > 0).>,
<./if.>[<.=RF.ChildField.Name.>]<.index ++.>
<./foreach.>
) ON [PRIMARY]
GO

ALTER
TABLE [dbo].[<.=T.Name.>] ADD
CONSTRAINT [<.=P.Name.>] FOREIGN KEY
(
<.index=0.>
<.foreach(RF = P.RelationFields).>
<.if(index > 0).>,
<./if.> [<.=RF.ChildField.Name.>]<.index ++.>
<./foreach.]
) REFERENCES [dbo].[<.=P.ParentTable.Name.>] (
<.index=0.>
<.foreach(RF = P.RelationFields).>
<.if(index > 0).>,
<./if.> [<.=RF.ParentField.Name.>]<.index ++.>
<./foreach.]
)
GO

<./foreach.>
<./if.>
<.
/foreach.>
 
    
    
==【Function.sql.nuva】内容如下== 
<..========================================================
==
==
==
Macrobject Common Library ==
==
==
==
Copyright (c) 2004-2006 Macrobject Software ==
==
==
==
ALL RIGHTS RESERVED ==
==
==
==
http://www.macrobject.com ==
==
==
========================================================
..>
<.
function GetDataType(Field)
var types = ['smallint', 'int', 'bigint', 'float', 'float', 'decimal',
'bit', 'datetime', 'uniqueidentifier', '', 'text', '', 'image'];

if (Field.DataType = 9)
if (Field.Width = 0)
result = 'text'
elseif (Field.Width > 8000)
result = 'text'
else
result = 'varchar'
end if
elseif (Field.DataType = 11)
if (Field.Width = 0)
result = 'image'
elseif (Field.Width > 8000)
result = 'image'
else
result = 'varbinary'
end if
else
Result = types[Field.DataType]
end if
end
function

function
GetDataSize(Field)
case(Field.DataType = 9,11)
if (Field.Width = 0)
result = ''
elseif (Field.Width > 8000)
result = ''
else
result = ' ('
result = result ~ Field.Width
result
= result ~ ')'
end if
else
result = ''
end case
end
function

function
GetNullable(Field)
if (Field.IsNullable)
result = 'NULL'
else
result = 'NOT NULL'
end if
end
function

function
GetIdentity(Field)
if (Field.IsAutoincrement)
result = ' IDENTITY(1,1)'
else
result = ''
end if
end
function
.>

<..
【简介】
    本例是一个真正的模板,来自于 Macrobject CodeAuto (
http://www.macrobject.com/cn/codeauto/index.htm) 附带的模板(位于 Templates/Microsoft SQL Server Scripts)。

    本例的模板将能够生成数据库的创建表的 SQL 脚本(本例应用于 MS SQL Server)。

    本例的模板与上例的模板一起构成一个完整的创建数据库表的 SQL 脚本(MS SQL Server)。

【看点】
    1、本例的第一个循环就是循环默认的数据库表:System.Data.DefaultSchema.Tables
       System.Data.DefaultSchema.Tables 等同于上例的 System.Tables,System.Tables 是其简便的写法,都是提供一个默认的数据库表集合,通过一个与示例程序文件名相同的 cfg 文件可以配置如何读取这样的表集合。

       本例中我们的 "模板示例.cfg" 内容如下:
       -xD:/Nuva-Samples/Northwind.xobject

       -x 参数指定一个 XObject 架构文件,通过该架构文件可以读取相应的数据架构信息。
       XObject 文件可以通过 Macrobject CodeAuto 从数据库提取(
http://www.macrobject.com/cn/codeauto/index.htm)。也可以通过 Nuva 程序直接从数据库读取,将在以后演示。

       cfg 文件中的参数可以在通过 Nuva 虚拟机执行时放入命令行作为其参数。

       System.Tables 返回一个 ObjectSet 对象,该对象是一个 DataObject 的集合,具体可以参考 <<Nuva API>> 的 System.Data 的信息。

       这里的集合内每一个对象都是 TableObject 对象(DataObject 子类),具体的属性包括在上面的示例代码中。(还有些以后再演示介绍)

       上面这些介绍内容与上例相同。

    2、foreach 的 | 后面的表达式是一个过滤表达式,只有符合这个表达式的枚举项才执行 foreach 内的代码。(本例中过滤 Enabled 不为 true 的项)

       里面的循环是 TableObject.ParentRelations,表示该表的所有父关系。

       这儿生成的 SQL 采用 ALTER 表的方式删除外键约束,其中 P.Name 是关系名称。

    3、本例中的模板非常讲究生成的 SQL 格式,因此生成的 SQL 脚本格式非常工整。用在讲究输出格式的模板上,Nuva 语言无疑是非常方便的。

    4、本例中用到了一些类型转换的代码,封装在 Function.sql.nuva 文件中,这里不再详细讨论。

    5、本例用到了一些与关系有关的数据库架构,介绍如下:
       TableObject.ParentRelations 表示该表的父关系,是一个 DataObject 集合(ObjectSet),其中有一个 Count 属性,表示集合的元素数目。
       ParentRelationObject.RelationFields 表示该关系包含的字段对(RelationFieldObject),每个 RelationFieldObject 包含两个属性,一个是 ChildField,表示子表字段对象,一个是 ParentField,表示父表字段对象。
       同样,每个 ParentRelationObject 也包括 ChildTable 和 ParentTable 两个表对象,分别表示子表对象和父表对象。


【扩展】
    本例可以进一步扩展以增强其实用性,比如可以改为支持 Oracle、DB2 等。
..>

本例运行结果如下:


CREATE TABLE [dbo].[Categories] (
 [CategoryID] [int] NOT NULL IDENTITY(1,1),
 [CategoryName] [varchar] (15) NOT NULL,
 [Description] [text] NULL,
 [Picture] [image] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CustomerCustomerDemo] (
 [CustomerID] [varchar] (5) NOT NULL,
 [CustomerTypeID] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CustomerDemographics] (
 [CustomerDesc] [text] NULL,
 [CustomerTypeID] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Customers] (
 [Address] [varchar] (60) NULL,
 [City] [varchar] (15) NULL,
 [CompanyName] [varchar] (40) NOT NULL,
 [ContactName] [varchar] (30) NULL,
 [ContactTitle] [varchar] (30) NULL,
 [Country] [varchar] (15) NULL,
 [CustomerID] [varchar] (5) NOT NULL,
 [Fax] [varchar] (24) NULL,
 [Phone] [varchar] (24) NULL,
 [PostalCode] [varchar] (10) NULL,
 [Region] [varchar] (15) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Employees] (
 [Address] [varchar] (60) NULL,
 [BirthDate] [datetime] NULL,
 [City] [varchar] (15) NULL,
 [Country] [varchar] (15) NULL,
 [EmployeeID] [int] NOT NULL IDENTITY(1,1),
 [Extension] [varchar] (4) NULL,
 [FirstName] [varchar] (10) NOT NULL,
 [HireDate] [datetime] NULL,
 [HomePhone] [varchar] (24) NULL,
 [LastName] [varchar] (20) NOT NULL,
 [Notes] [text] NULL,
 [Photo] [image] NULL,
 [PhotoPath] [varchar] (255) NULL,
 [PostalCode] [varchar] (10) NULL,
 [Region] [varchar] (15) NULL,
 [ReportsTo] [int] NULL,
 [Title] [varchar] (30) NULL,
 [TitleOfCourtesy] [varchar] (25) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EmployeeTerritories] (
 [EmployeeID] [int] NOT NULL,
 [TerritoryID] [varchar] (20) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Order Details] (
 [Discount] [float] NOT NULL,
 [OrderID] [int] NOT NULL,
 [ProductID] [int] NOT NULL,
 [Quantity] [smallint] NOT NULL,
 [UnitPrice] [decimal] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Orders] (
 [CustomerID] [varchar] (5) NULL,
 [EmployeeID] [int] NULL,
 [Freight] [decimal] NULL,
 [OrderDate] [datetime] NULL,
 [OrderID] [int] NOT NULL IDENTITY(1,1),
 [RequiredDate] [datetime] NULL,
 [ShipAddress] [varchar] (60) NULL,
 [ShipCity] [varchar] (15) NULL,
 [ShipCountry] [varchar] (15) NULL,
 [ShipName] [varchar] (40) NULL,
 [ShippedDate] [datetime] NULL,
 [ShipPostalCode] [varchar] (10) NULL,
 [ShipRegion] [varchar] (15) NULL,
 [ShipVia] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Products] (
 [CategoryID] [int] NULL,
 [Discontinued] [bit] NOT NULL,
 [ProductID] [int] NOT NULL IDENTITY(1,1),
 [ProductName] [varchar] (40) NOT NULL,
 [QuantityPerUnit] [varchar] (20) NULL,
 [ReorderLevel] [smallint] NULL,
 [SupplierID] [int] NULL,
 [UnitPrice] [decimal] NULL,
 [UnitsInStock] [smallint] NULL,
 [UnitsOnOrder] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Region] (
 [RegionDescription] [varchar] (50) NOT NULL,
 [RegionID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Shippers] (
 [CompanyName] [varchar] (40) NOT NULL,
 [Phone] [varchar] (24) NULL,
 [ShipperID] [int] NOT NULL IDENTITY(1,1)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Suppliers] (
 [Address] [varchar] (60) NULL,
 [City] [varchar] (15) NULL,
 [CompanyName] [varchar] (40) NOT NULL,
 [ContactName] [varchar] (30) NULL,
 [ContactTitle] [varchar] (30) NULL,
 [Country] [varchar] (15) NULL,
 [Fax] [varchar] (24) NULL,
 [HomePage] [text] NULL,
 [Phone] [varchar] (24) NULL,
 [PostalCode] [varchar] (10) NULL,
 [Region] [varchar] (15) NULL,
 [SupplierID] [int] NOT NULL IDENTITY(1,1)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Territories] (
 [RegionID] [int] NOT NULL,
 [TerritoryDescription] [varchar] (50) NOT NULL,
 [TerritoryID] [varchar] (20) NOT NULL
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[Categories] ADD
 CONSTRAINT [PK_Categories] PRIMARY KEY  NONCLUSTERED
 (
  [CategoryID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[CustomerCustomerDemo] ADD
 CONSTRAINT [PK_CustomerCustomerDemo] PRIMARY KEY  NONCLUSTERED
 (
  [CustomerID],
  [CustomerTypeID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[CustomerDemographics] ADD
 CONSTRAINT [PK_CustomerDemographics] PRIMARY KEY  NONCLUSTERED
 (
  [CustomerTypeID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customers] ADD
 CONSTRAINT [PK_Customers] PRIMARY KEY  NONCLUSTERED
 (
  [CustomerID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Employees] ADD
 CONSTRAINT [PK_Employees] PRIMARY KEY  NONCLUSTERED
 (
  [EmployeeID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeeTerritories] ADD
 CONSTRAINT [PK_EmployeeTerritories] PRIMARY KEY  NONCLUSTERED
 (
  [EmployeeID],
  [TerritoryID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Order Details] ADD
 CONSTRAINT [PK_Order Details] PRIMARY KEY  NONCLUSTERED
 (
  [OrderID],
  [ProductID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] ADD
 CONSTRAINT [PK_Orders] PRIMARY KEY  NONCLUSTERED
 (
  [OrderID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Products] ADD
 CONSTRAINT [PK_Products] PRIMARY KEY  NONCLUSTERED
 (
  [ProductID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Region] ADD
 CONSTRAINT [PK_Region] PRIMARY KEY  NONCLUSTERED
 (
  [RegionID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Shippers] ADD
 CONSTRAINT [PK_Shippers] PRIMARY KEY  NONCLUSTERED
 (
  [ShipperID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Suppliers] ADD
 CONSTRAINT [PK_Suppliers] PRIMARY KEY  NONCLUSTERED
 (
  [SupplierID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Territories] ADD
 CONSTRAINT [PK_Territories] PRIMARY KEY  NONCLUSTERED
 (
  [TerritoryID]
 )  ON [PRIMARY]
GO


CREATE  INDEX [FK_CustomerCustomerDemo_Customers] ON [dbo].[CustomerCustomerDemo]([CustomerID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CustomerCustomerDemo] ADD
 CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY
 (
  [CustomerID]
 ) REFERENCES [dbo].[Customers] (
  [CustomerID]
 )
GO

CREATE  INDEX [FK_CustomerCustomerDemo] ON [dbo].[CustomerCustomerDemo]([CustomerTypeID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CustomerCustomerDemo] ADD
 CONSTRAINT [FK_CustomerCustomerDemo] FOREIGN KEY
 (
  [CustomerTypeID]
 ) REFERENCES [dbo].[CustomerDemographics] (
  [CustomerTypeID]
 )
GO

CREATE  INDEX [FK_Employees_Employees] ON [dbo].[Employees]([ReportsTo]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Employees] ADD
 CONSTRAINT [FK_Employees_Employees] FOREIGN KEY
 (
  [ReportsTo]
 ) REFERENCES [dbo].[Employees] (
  [EmployeeID]
 )
GO

CREATE  INDEX [FK_EmployeeTerritories_Territories] ON [dbo].[EmployeeTerritories]([TerritoryID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeeTerritories] ADD
 CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY
 (
  [TerritoryID]
 ) REFERENCES [dbo].[Territories] (
  [TerritoryID]
 )
GO

CREATE  INDEX [FK_EmployeeTerritories_Employees] ON [dbo].[EmployeeTerritories]([EmployeeID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeeTerritories] ADD
 CONSTRAINT [FK_EmployeeTerritories_Employees] FOREIGN KEY
 (
  [EmployeeID]
 ) REFERENCES [dbo].[Employees] (
  [EmployeeID]
 )
GO

CREATE  INDEX [FK_Order_Details_Products] ON [dbo].[Order Details]([ProductID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Order Details] ADD
 CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY
 (
  [ProductID]
 ) REFERENCES [dbo].[Products] (
  [ProductID]
 )
GO

CREATE  INDEX [FK_Order_Details_Orders] ON [dbo].[Order Details]([OrderID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Order Details] ADD
 CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY
 (
  [OrderID]
 ) REFERENCES [dbo].[Orders] (
  [OrderID]
 )
GO

CREATE  INDEX [FK_Orders_Shippers] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] ADD
 CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY
 (
  [ShipVia]
 ) REFERENCES [dbo].[Shippers] (
  [ShipperID]
 )
GO

CREATE  INDEX [FK_Orders_Employees] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] ADD
 CONSTRAINT [FK_Orders_Employees] FOREIGN KEY
 (
  [EmployeeID]
 ) REFERENCES [dbo].[Employees] (
  [EmployeeID]
 )
GO

CREATE  INDEX [FK_Orders_Customers] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] ADD
 CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
 (
  [CustomerID]
 ) REFERENCES [dbo].[Customers] (
  [CustomerID]
 )
GO

CREATE  INDEX [FK_Products_Suppliers] ON [dbo].[Products]([SupplierID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Products] ADD
 CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY
 (
  [SupplierID]
 ) REFERENCES [dbo].[Suppliers] (
  [SupplierID]
 )
GO

CREATE  INDEX [FK_Products_Categories] ON [dbo].[Products]([CategoryID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Products] ADD
 CONSTRAINT [FK_Products_Categories] FOREIGN KEY
 (
  [CategoryID]
 ) REFERENCES [dbo].[Categories] (
  [CategoryID]
 )
GO

CREATE  INDEX [FK_Territories_Region] ON [dbo].[Territories]([RegionID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Territories] ADD
 CONSTRAINT [FK_Territories_Region] FOREIGN KEY
 (
  [RegionID]
 ) REFERENCES [dbo].[Region] (
  [RegionID]
 )
GO

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值