static void TestTpcds_LoadData()
{
var files = Directory.GetFiles(@"../../../tpcds", "*.sql");
string[] norun = { "q1", "q10" };
Tpcds.CreateTables();
Tpcds.LoadTables("tiny");
Tpcds.AnalyzeTables();
// make sure all queries can generate phase one opt plan
QueryOption option = new QueryOption();
option.optimize_.enable_subquery_unnest_ = true;
option.optimize_.remove_from_ = false;
option.optimize_.use_memo_ = false;
foreach (var v in files)
{
char[] splits = { '.', '/', '\\' };
var tokens = v.Split(splits, StringSplitOptions.RemoveEmptyEntries);
if (norun.Contains(tokens[1]))
{
continue;
}
var sql = File.ReadAllText(v);
var result = SQLStatement.ExecSQL(sql, out string phyplan, out _, option);
}
}
static void RunSQLFromFile(string filename)
{
// Not working as expected, need to investigate.
var option = new QueryOption();
option.explain_.mode_ = ExplainMode.full;
option.optimize_.use_memo_ = true;
option.explain_.show_estCost_ = false;
string allquery = File.ReadAllText(filename);
string[] listquery = allquery.Split(';');
List <string> listoutput = new List <string>();
int linenum = 0;
for (int i = 0; i < listquery.Length; ++i)
{
linenum = i + 1;
string sql = listquery[i].Trim();
if (sql.Length <= 0)
{
continue;
}
else if (sql.StartsWith("--"))
{
continue;
}
try
{
Console.WriteLine(sql);
string outline = linenum.ToString();
outline += ": " + sql + "\n";
listoutput.Add(outline);
var result = SQLStatement.ExecSQL(sql, out string physicplan, out string error_, option);
if (physicplan != null)
{
Console.WriteLine(physicplan);
listoutput.Add(physicplan);
}
if (result != null)
{
Console.WriteLine(result);
listoutput.Add(result.ToString());
}
}
catch (Exception e)
{
Console.WriteLine("SQL: " + sql + "\nEXCEPTION: " + e + "\n");
continue;
}
}
string alloutput = string.Join('\n', listoutput);
string outfile = filename + ".out";
File.WriteAllText(outfile, alloutput);
}
static public void AnalyzeTables()
{
foreach (var v in tabnames_)
{
var sql = $"analyze {v};";
SQLStatement.ExecSQL(sql, out _, out _);
}
}
static void createBuildInTestTables()
{
// create tables
string[] createtables =
{
@"create table test (t1 int, t2 int, t3 int, t4 int);"
,
@"create table a (a1 int, a2 int, a3 int, a4 int);",
@"create table b (b1 int, b2 int, b3 int, b4 int);",
@"create table c (c1 int, c2 int, c3 int, c4 int);",
@"create table d (d1 int, d2 int, d3 int, d4 int);",
// nullable tables
@"create table r (r1 int, r2 int, r3 int, r4 int);",
// distributed tables
@"create table ad (a1 int, a2 int, a3 int, a4 int) distributed by a1;",
@"create table bd (b1 int, b2 int, b3 int, b4 int) distributed by b1;",
@"create table cd (c1 int, c2 int, c3 int, c4 int) distributed by c1;",
@"create table dd (d1 int, d2 int, d3 int, d4 int) distributed by d1;",
@"create table ar (a1 int, a2 int, a3 int, a4 int) replicated;",
@"create table br (b1 int, b2 int, b3 int, b4 int) replicated;",
@"create table arb (a1 int, a2 int, a3 int, a4 int) roundrobin;",
@"create table brb (b1 int, b2 int, b3 int, b4 int) roundrobin;",
// steaming tables
@"create table ast (a0 datetime, a1 int, a2 int, a3 int, a4 int);", // bounded table with ts
@"create stream ainf (a0 datetime, a1 int, a2 int, a3 int, a4 int);", // unbounded table
};
SQLStatement.ExecSQLList(string.Join("", createtables));
// load tables
var appbin_dir = AppContext.BaseDirectory.Substring(0, AppContext.BaseDirectory.LastIndexOf("bin"));
var folder = $@"{appbin_dir}/../data";
var tables = new List <string>()
{
"test", "a", "b", "c", "d", "r", "ad", "bd", "cd", "dd", "ar", "br", "arb", "brb", "ast"
};
foreach (var v in tables)
{
string filename = $@"'{folder}/{v}.tbl'";
var sql = $"copy {v} from {filename};";
var result = SQLStatement.ExecSQL(sql, out _, out _);
}
// create index
string[] createindexes =
{
@"create unique index dd1 on d(d1);",
@"create index dd2 on d(d2);",
};
SQLStatement.ExecSQLList(string.Join("", createindexes));
// analyze tables
foreach (var v in tables)
{
var sql = $"analyze {v};";
var result = SQLStatement.ExecSQL(sql, out _, out _);
}
}
static public void LoadTables(string subfolder)
{
string curdir = Directory.GetCurrentDirectory();
string folder = $@"{curdir}\..\..\..\tpch\data\{subfolder}";
foreach (var v in tabnames_)
{
string filename = $@"'{folder}\{v}.tbl'";
var sql = $"copy {v} from {filename};";
SQLStatement.ExecSQL(sql, out _, out _);
}
}
static public void CreateTables()
{
// hack: drop tpch table customer
SQLStatement.ExecSQL("drop table customer;", out _, out _);
string curdir = Directory.GetCurrentDirectory();
string folder = $@"{curdir}\..\..\..\tpch\create";
string filename = $@"{folder}\tpch.sql";
var sql = File.ReadAllText(filename);
SQLStatement.ExecSQLList(sql);
}
static void createBuildInTestTables()
{
// create tables
string[] createtables =
{
@"create table test (a1 int, a2 int, a3 int, a4 int);"
,
@"create table a (a1 int, a2 int, a3 int, a4 int);",
@"create table b (b1 int, b2 int, b3 int, b4 int);",
@"create table c (c1 int, c2 int, c3 int, c4 int);",
@"create table d (d1 int, d2 int, d3 int, d4 int);",
// nullable tables
@"create table r (r1 int, r2 int, r3 int, r4 int);",
// distributed tables
@"create table ad (a1 int, a2 int, a3 int, a4 int) distributed by a1;",
@"create table bd (b1 int, b2 int, b3 int, b4 int) distributed by b1;",
@"create table cd (c1 int, c2 int, c3 int, c4 int) distributed by c1;",
@"create table dd (d1 int, d2 int, d3 int, d4 int) distributed by d1;",
// steaming tables
@"create table ast (a0 datetime, a1 int, a2 int, a3 int, a4 int);",
};
SQLStatement.ExecSQLList(string.Join("", createtables));
// load tables
var curdir = Directory.GetCurrentDirectory();
var folder = $@"{curdir}\..\..\..\data";
var tables = new List <string>()
{
"a", "b", "c", "d", "r", "ad", "bd", "cd", "dd", "ast"
};
foreach (var v in tables)
{
string filename = $@"'{folder}\{v}.tbl'";
var sql = $"copy {v} from {filename};";
var result = SQLStatement.ExecSQL(sql, out _, out _);
}
// create index
string[] createindexes =
{
@"create unique index dd1 on d(d1);",
@"create index dd2 on d(d2);",
};
SQLStatement.ExecSQLList(string.Join("", createindexes));
// analyze tables
foreach (var v in tables)
{
var sql = $"analyze {v};";
var result = SQLStatement.ExecSQL(sql, out _, out _);
}
}
static void TestDataSet2()
{
Random rand = new Random();
int inside(int d)
{
var x = rand.NextDouble();
var y = rand.NextDouble();
var ret = x * x + y * y <= 1 ? 1 : 0;
return(ret);
}
SQLContext sqlContext = new SQLContext();
SQLContext.Register <int, int>("inside", inside);
var sql = "SELECT 4.0*sum(inside(a1.a1))/count(*) from a a1, a a2, a a3, a a4, a a5, a a6, a a7, a a8, a a9, a a10";
var rows = SQLStatement.ExecSQL(sql, out string plan, out _);
}
static void TestDataSet()
{
// register c#'s sqrt as an external function
string sqroot(double d) => Math.Sqrt(d).ToString("#.###");
SQLContext sqlContext = new SQLContext();
SQLContext.Register <double, string>("sqroot", sqroot);
var a = sqlContext.Read("a");
var b = sqlContext.Read("b");
a.filter("a1>1").join(b, "b2=a2").select("a1", "sqroot(b1*a1+2)").show();
string s = a.physicPlan_.Explain();
Console.WriteLine(s);
var sql = "SELECT a1, sqroot(b1*a1+2) from a join b on b2=a2 where a1>1";
var rows = SQLStatement.ExecSQL(sql, out string plan, out _);
}
static void TestJobench()
{
var files = Directory.GetFiles(@"../../../jobench");
JOBench.CreateTables();
// make sure all queries can generate phase one opt plan
QueryOption option = new QueryOption();
option.optimize_.TurnOnAllOptimizations();
option.optimize_.memo_use_joinorder_solver_ = true;
foreach (var v in files)
{
var sql = File.ReadAllText(v);
var result = SQLStatement.ExecSQL(sql, out string phyplan, out _, option);
Debug.Assert(result != null);
Debug.Assert(phyplan != null);
Console.WriteLine(v);
Console.WriteLine(phyplan);
}
}