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

posted on 2006-09-06 11:15 Wisdom-zh 阅读(756) 评论(0) 编辑收藏举报

刷新页面返回顶部

Nuva 示例代码（每日一帖）之模板示例（3）

导航

公告

Nuva 示例代码（每日一帖）之 模板示例（3）

导航

公告

Nuva 示例代码（每日一帖）之模板示例（3）