public void Where_Simple()
{
SubSonic.SqlQuery qry = Select.AllColumnsFrom<Product>().Where("productID").IsGreaterThan(5);
ANSISqlGenerator gen = new ANSISqlGenerator(qry);
string w = gen.GenerateWhere();
Assert.AreEqual(" WHERE [dbo].[Products].[ProductID] > @ProductID0\r\n", w);
}
public void Drop_Table()
{
ANSISqlGenerator gen = new ANSISqlGenerator(null);
string sql = gen.BuildDropTableStatement(Product.Schema);
Assert.AreEqual("DROP TABLE [dbo].[Products]", sql);
}
public void Where_Simple()
{
SubSonic.SqlQuery qry = Select.AllColumnsFrom <Product>().Where("productID").IsGreaterThan(5);
ANSISqlGenerator gen = new ANSISqlGenerator(qry);
string w = gen.GenerateWhere();
Assert.AreEqual(" WHERE [dbo].[Products].[ProductID] > @ProductID0\r\n", w);
}
public static void IntersectsWithCast_CountTest() {
var expected = @"WHERE (geometry::Parse('POINT (' + CONVERT(varchar, [CreatedBy]) + ' ' + CONVERT(varchar, [CreatedOn]) + ')')).STIntersects(geometry::Parse(geometry::Parse(@Parameter00).MakeValid().STUnion(geometry::Parse(@Parameter00).MakeValid().STStartPoint()).ToString()).ToString()) = 1";
var select = Select.AllColumnsFrom<Product>()
.Where(Geospatial.SqlHelper.CastAsPoint(GeospatialType.Geometry, Product.Columns.CreatedBy, Product.Columns.CreatedOn)).IntersectsWith(TestPolygon.ToString(), GeospatialType.Geometry);
var gen = new ANSISqlGenerator(select);
var sql = gen.GetCountSelect().Trim();
Console.WriteLine(sql);
Assert.IsTrue(sql.Contains(expected));
}
public static void Intersects_Test() {
var expected = @"WHERE ([dbo].[Products].[CreatedBy]).STIntersects(geography::Parse(geometry::Parse(@CreatedBy0).MakeValid().STUnion(geometry::Parse(@CreatedBy0).MakeValid().STStartPoint()).ToString()).ToString()) = 1";
var select = Select.AllColumnsFrom<Product>()
.Where(Product.Columns.CreatedBy).IntersectsWith(TestPolygon.ToString());
var gen = new ANSISqlGenerator(select);
var sql = gen.GenerateWhere().Trim();
Console.WriteLine(sql);
Assert.AreEqual(expected, sql);
}
public void Select_Generate_FromList()
{
Select qry = Select.AllColumnsFrom<Product>();
ANSISqlGenerator gen = new ANSISqlGenerator(qry);
string from = gen.GenerateFromList();
Assert.AreEqual(" FROM [dbo].[Products]\r\n", from);
}
public void Select_ColumnList_Specified()
{
SubSonic.SqlQuery qry = new Select("productid", "productname").From(Product.Schema);
ANSISqlGenerator gen = new ANSISqlGenerator(qry);
string selectList = gen.GenerateCommandLine();
Assert.AreEqual("SELECT [dbo].[Products].[ProductID], [dbo].[Products].[ProductName]\r\n", selectList);
}
public void Select_Generate_FromList()
{
Select qry = Select.AllColumnsFrom <Product>();
ANSISqlGenerator gen = new ANSISqlGenerator(qry);
string from = gen.GenerateFromList();
Assert.AreEqual(" FROM [dbo].[Products]\r\n", from);
}
public void Create_Table()
{
ANSISqlGenerator gen = new ANSISqlGenerator(null);
string sql = gen.BuildCreateTableStatement(Product.Schema);
Assert.AreEqual(
"CREATE TABLE [dbo].[Products] (\r\n [ProductID] int NOT NULL PRIMARY KEY IDENTITY(1,1),\r\n [ProductName] nvarchar(40) NOT NULL,\r\n [SupplierID] int NULL,\r\n [CategoryID] int NULL,\r\n [QuantityPerUnit] nvarchar(20) NULL,\r\n [UnitPrice] money NULL CONSTRAINT DF_Products_UnitPrice DEFAULT (((0))),\r\n [UnitsInStock] int NULL CONSTRAINT DF_Products_UnitsInStock DEFAULT (((0))),\r\n [UnitsOnOrder] int NULL CONSTRAINT DF_Products_UnitsOnOrder DEFAULT (((0))),\r\n [ReorderLevel] int NULL CONSTRAINT DF_Products_ReorderLevel DEFAULT (((0))),\r\n [Discontinued] bit NOT NULL CONSTRAINT DF_Products_Discontinued DEFAULT (((0))),\r\n [AttributeXML] varchar NULL,\r\n [DateCreated] datetime NULL CONSTRAINT DF_Products_DateCreated DEFAULT ((getdate())),\r\n [ProductGUID] uniqueidentifier NULL CONSTRAINT DF_Products_ProductGUID DEFAULT ((newid())),\r\n [CreatedOn] datetime NOT NULL CONSTRAINT DF_Products_CreatedOn DEFAULT ((getdate())),\r\n [CreatedBy] nvarchar(50) NULL,\r\n [ModifiedOn] datetime NOT NULL CONSTRAINT DF_Products_ModifiedOn DEFAULT ((getdate())),\r\n [ModifiedBy] nvarchar(50) NULL,\r\n [Deleted] bit NOT NULL CONSTRAINT DF_Products_Deleted DEFAULT (((0))) \r\n)",
sql);
}
public void Select_ColumnList_Specified()
{
SubSonic.SqlQuery qry = new Select("productid", "productname").From(Product.Schema);
ANSISqlGenerator gen = new ANSISqlGenerator(qry);
string selectList = gen.GenerateCommandLine();
Assert.AreEqual("SELECT [dbo].[Products].[ProductID], [dbo].[Products].[ProductName]\r\n", selectList);
}
public void Remove_Column()
{
TableSchema.Table productSchema = Product.Schema;
TableSchema.TableColumn column = productSchema.GetColumn("ProductName");
ANSISqlGenerator gen = new ANSISqlGenerator(null);
string sql = gen.BuildDropColumnStatement(productSchema, column);
Assert.AreEqual("ALTER TABLE [dbo].[Products] DROP COLUMN [ProductName]", sql);
}
public void Alter_Column()
{
TableSchema.Table productSchema = Product.Schema;
TableSchema.TableColumn column = productSchema.GetColumn("ProductName");
column.MaxLength = 150;
ANSISqlGenerator gen = new ANSISqlGenerator(null);
string sql = gen.BuildAlterColumnStatement(column);
Assert.AreEqual("ALTER TABLE [dbo].[Products] ALTER COLUMN [ProductName] nvarchar(150) NOT NULL", sql);
// Set it back to 40 or Create_Table fails.
column.MaxLength = 40;
}
public void Add_Column()
{
TableSchema.Table productSchema = Product.Schema;
TableSchema.TableColumn column = new TableSchema.TableColumn(productSchema);
column.ColumnName = "Address4";
column.DataType = DbType.String;
column.MaxLength = 50;
column.IsNullable = true;
ANSISqlGenerator gen = new ANSISqlGenerator(null);
string sql = gen.BuildAddColumnStatement(Product.Schema, column);
Assert.AreEqual("ALTER TABLE [dbo].[Products] ADD [Address4] nvarchar(50) NULL", sql);
}
public void Select_Generate_JoinList()
{
SubSonic.SqlQuery qry = Select.AllColumnsFrom<Product>()
.InnerJoin(Category.Schema)
.InnerJoin(Supplier.Schema);
ANSISqlGenerator gen = new ANSISqlGenerator(qry);
string joins = gen.GenerateJoins();
Assert.AreEqual(
" INNER JOIN [dbo].[Categories] ON [dbo].[Products].[CategoryID] = [dbo].[Categories].[CategoryID]\r\n INNER JOIN [dbo].[Suppliers] ON [dbo].[Products].[SupplierID] = [dbo].[Suppliers].[SupplierID]\r\n",
joins);
}
public void Select_Generate_JoinList()
{
SubSonic.SqlQuery qry = Select.AllColumnsFrom<Product>()
.InnerJoin(Category.Schema)
.InnerJoin(Supplier.Schema);
ANSISqlGenerator gen = new ANSISqlGenerator(qry);
string joins = gen.GenerateJoins();
Assert.AreEqual(
" INNER JOIN `main`.`Categories` ON `main`.`Products`.`CategoryID` = `main`.`Categories`.`CategoryID`\r\n INNER JOIN `main`.`Suppliers` ON `main`.`Products`.`SupplierID` = `main`.`Suppliers`.`SupplierID`\r\n",
joins);
}
public void Select_Generate_JoinList()
{
SubSonic.SqlQuery qry = Select.AllColumnsFrom <Product>()
.InnerJoin(Category.Schema)
.InnerJoin(Supplier.Schema);
ANSISqlGenerator gen = new ANSISqlGenerator(qry);
string joins = gen.GenerateJoins();
Assert.AreEqual(
" INNER JOIN [dbo].[Categories] ON [dbo].[Products].[CategoryID] = [dbo].[Categories].[CategoryID]\r\n INNER JOIN [dbo].[Suppliers] ON [dbo].[Products].[SupplierID] = [dbo].[Suppliers].[SupplierID]\r\n",
joins);
}
public void Where_And()
{
SubSonic.SqlQuery qry = Select.AllColumnsFrom<Product>().Where("productID").IsGreaterThan(5).And("categoryID").IsEqualTo(5);
ANSISqlGenerator gen = new ANSISqlGenerator(qry);
string w = gen.GenerateWhere();
Assert.AreEqual(
" WHERE `main`.`Products`.`ProductID` > @ProductID0\r\n AND `main`.`Products`.`CategoryID` = @CategoryID1\r\n",
w);
}
public void Add_Column()
{
TableSchema.Table productSchema = Product.Schema;
TableSchema.TableColumn column = new TableSchema.TableColumn(productSchema);
column.ColumnName = "Address4";
column.DataType = DbType.String;
column.MaxLength = 50;
column.IsNullable = true;
ANSISqlGenerator gen = new ANSISqlGenerator(null);
string sql = gen.BuildAddColumnStatement(Product.Schema, column);
Assert.AreEqual("ALTER TABLE [dbo].[Products] ADD [Address4] nvarchar(50) NULL", sql);
}
public void Drop_Table()
{
ANSISqlGenerator gen = new ANSISqlGenerator(null);
string sql = gen.BuildDropTableStatement(Product.Schema);
Assert.AreEqual("DROP TABLE [dbo].[Products]", sql);
}
public void Remove_Column()
{
TableSchema.Table productSchema = Product.Schema;
TableSchema.TableColumn column = productSchema.GetColumn("ProductName");
ANSISqlGenerator gen = new ANSISqlGenerator(null);
string sql = gen.BuildDropColumnStatement(productSchema, column);
Assert.AreEqual("ALTER TABLE [dbo].[Products] DROP COLUMN [ProductName]", sql);
}
public void Alter_Column()
{
TableSchema.Table productSchema = Product.Schema;
TableSchema.TableColumn column = productSchema.GetColumn("ProductName");
column.MaxLength = 150;
ANSISqlGenerator gen = new ANSISqlGenerator(null);
string sql = gen.BuildAlterColumnStatement(column);
Assert.AreEqual("ALTER TABLE [dbo].[Products] ALTER COLUMN [ProductName] nvarchar(150) NOT NULL", sql);
}
public void Create_Table()
{
ANSISqlGenerator gen = new ANSISqlGenerator(null);
string sql = gen.BuildCreateTableStatement(Product.Schema);
//Assert.Fail("sql = " + sql);
string expected =
@"CREATE TABLE `main`.`Products` (
`ProductID` int NOT NULL PRIMARY KEY IDENTITY(1,1),
`ProductName` nvarchar(150) NOT NULL,
`SupplierID` int NULL,
`CategoryID` int NULL,
`QuantityPerUnit` nvarchar(MAX) NULL,
`UnitPrice` real NULL,
`UnitsInStock` int NULL,
`UnitsOnOrder` int NULL,
`ReorderLevel` int NULL,
`Discontinued` bit NOT NULL,
`AttributeXML` nvarchar(MAX) NULL,
`DateCreated` datetime NULL,
`ProductGUID` uniqueidentifier NULL,
`CreatedOn` datetime NOT NULL,
`CreatedBy` nvarchar(MAX) NULL,
`ModifiedOn` datetime NOT NULL,
`ModifiedBy` nvarchar(MAX) NULL,
`Deleted` bit NOT NULL
)";
Assert.AreEqual(expected, sql);
}
public void Create_Table()
{
ANSISqlGenerator gen = new ANSISqlGenerator(null);
string sql = gen.BuildCreateTableStatement(Product.Schema);
Assert.AreEqual(
"CREATE TABLE [dbo].[Products] (\r\n [ProductID] int NOT NULL PRIMARY KEY IDENTITY(1,1),\r\n [ProductName] nvarchar(40) NOT NULL,\r\n [SupplierID] int NULL,\r\n [CategoryID] int NULL,\r\n [QuantityPerUnit] nvarchar(20) NULL,\r\n [UnitPrice] money NULL CONSTRAINT DF_Products_UnitPrice DEFAULT (((0))),\r\n [UnitsInStock] int NULL CONSTRAINT DF_Products_UnitsInStock DEFAULT (((0))),\r\n [UnitsOnOrder] int NULL CONSTRAINT DF_Products_UnitsOnOrder DEFAULT (((0))),\r\n [ReorderLevel] int NULL CONSTRAINT DF_Products_ReorderLevel DEFAULT (((0))),\r\n [Discontinued] bit NOT NULL CONSTRAINT DF_Products_Discontinued DEFAULT (((0))),\r\n [AttributeXML] nvarchar NULL,\r\n [DateCreated] datetime NULL CONSTRAINT DF_Products_DateCreated DEFAULT ((getdate())),\r\n [ProductGUID] uniqueidentifier NULL CONSTRAINT DF_Products_ProductGUID DEFAULT ((newid())),\r\n [CreatedOn] datetime NOT NULL CONSTRAINT DF_Products_CreatedOn DEFAULT ((getdate())),\r\n [CreatedBy] nvarchar(50) NULL,\r\n [ModifiedOn] datetime NOT NULL CONSTRAINT DF_Products_ModifiedOn DEFAULT ((getdate())),\r\n [ModifiedBy] nvarchar(50) NULL,\r\n [Deleted] bit NOT NULL CONSTRAINT DF_Products_Deleted DEFAULT (((0))) \r\n)",
sql);
}
