public void CalcStringSize() { var data = new string[] { "123456789", "1234", "1234567" }; var derivedTable = SqQueryBuilder.Values(data.Select(SqQueryBuilder.Literal).ToList()).AsColumns("Str"); var tmpQuery = TempTableData.FromDerivedTableValuesInsert(derivedTable, new[] { derivedTable.Columns[0] }, out _, name: "tmpTable"); var sql = tmpQuery.ToMySql(); Assert.AreEqual("CREATE TEMPORARY TABLE `tmpTable`(`Str` varchar(9) character set utf8,CONSTRAINT PRIMARY KEY (`Str`));INSERT INTO `tmpTable`(`Str`) VALUES ('123456789'),('1234'),('1234567')", sql); }
public void BasicTest() { var data = new[] { new { Bool = true, Byte = byte.MaxValue, Int16 = short.MaxValue, Int32 = int.MaxValue, Int64 = long.MaxValue, Decimal = decimal.MaxValue, Double = 123.456, String = "ABCD", DateTime = new DateTime(2099, 1, 1), Guid = Guid.Parse("37197F28-AAED-480B-BA66-2C4D67A57E33") }, new { Bool = true, Byte = byte.MinValue, Int16 = short.MinValue, Int32 = int.MinValue, Int64 = long.MinValue, Decimal = decimal.MinValue, Double = -123.456, String = "ABCDABCD", DateTime = new DateTime(1900, 1, 1), Guid = Guid.Parse("E8C3620B-B8CD-4574-A074-ACE09AA3DA8A") }, }; List <List <ExprValue> > dataList = new List <List <ExprValue> >(data.Length); foreach (var row in data) { var l = new List <ExprValue>(); l.Add(SqQueryBuilder.Literal(row.Bool)); l.Add(SqQueryBuilder.Literal(row.Byte)); l.Add(SqQueryBuilder.Literal(row.Int16)); l.Add(SqQueryBuilder.Literal(row.Int32)); l.Add(SqQueryBuilder.Literal(row.Int64)); l.Add(SqQueryBuilder.Literal(row.Decimal)); l.Add(SqQueryBuilder.Literal(row.Double)); l.Add(SqQueryBuilder.Literal(row.String)); l.Add(SqQueryBuilder.Literal(row.DateTime)); l.Add(SqQueryBuilder.Literal(row.Guid)); dataList.Add(l); } var derivedTable = SqQueryBuilder.Values(dataList).AsColumns("Bool", "Byte", "Int16", "Int32", "Int64", "Decimal", "Double", "String", "DateTime", "Guid"); var q = TempTableData.FromDerivedTableValuesInsert(derivedTable, new [] { derivedTable.Columns[2], derivedTable.Columns[3] }, out var table, name: "TestTmpTable"); var sql = q.ToMySql(); Assert.AreEqual(sql, "CREATE TEMPORARY TABLE `TestTmpTable`(`Bool` bit,`Byte` tinyint unsigned,`Int16` smallint,`Int32` int,`Int64` bigint,`Decimal` decimal(29,0),`Double` double,`String` varchar(8) character set utf8,`DateTime` datetime,`Guid` binary(16),CONSTRAINT PRIMARY KEY (`Int16`,`Int32`));INSERT INTO `TestTmpTable`(`Bool`,`Byte`,`Int16`,`Int32`,`Int64`,`Decimal`,`Double`,`String`,`DateTime`,`Guid`) VALUES (true,255,32767,2147483647,9223372036854775807,79228162514264337593543950335,123.456,'ABCD','2099-01-01',0x287F1937EDAA0B48BA662C4D67A57E33),(true,0,-32768,-2147483648,-9223372036854775808,-79228162514264337593543950335,-123.456,'ABCDABCD','1900-01-01',0x0B62C3E8CDB87445A074ACE09AA3DA8A)"); sql = q.ToSql(); Assert.AreEqual(sql, "CREATE TABLE [#TestTmpTable]([Bool] bit,[Byte] tinyint,[Int16] smallint,[Int32] int,[Int64] bigint,[Decimal] decimal(29,0),[Double] float,[String] [nvarchar](8),[DateTime] datetime,[Guid] uniqueidentifier,CONSTRAINT [PK_TestTmpTable] PRIMARY KEY ([Int16],[Int32]));INSERT INTO [#TestTmpTable]([Bool],[Byte],[Int16],[Int32],[Int64],[Decimal],[Double],[String],[DateTime],[Guid]) VALUES (1,255,32767,2147483647,9223372036854775807,79228162514264337593543950335,123.456,'ABCD','2099-01-01','37197f28-aaed-480b-ba66-2c4d67a57e33'),(1,0,-32768,-2147483648,-9223372036854775808,-79228162514264337593543950335,-123.456,'ABCDABCD','1900-01-01','e8c3620b-b8cd-4574-a074-ace09aa3da8a')"); }