こんにちは！こんにちは！
肌寒くなってきましたね！
これを！
select
a.name as 商品名,
a.code as 商品コード,
b.price as 最新価格
from
cdb..商品 a
left join ddb..価格 b
on a.code = b.code
order by 商品コード
こうしたい！
Table Database,Scheme,Table,Alias ,cscheme,商品, ,dscheme,価格, Columns Table,Column,Alias,Statement a,name,商品名,a.name as 商品名 a,code,商品コード,a.code as 商品コード b,price,最新価格,b.price as 最新価格
Microsoft.SqlServer.TransactSql.ScriptDom を使う。
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using Microsoft.SqlServer.TransactSql.ScriptDom;
/**
* SELECT文を整理してCSV形式で出力する。
*
* Usage: dotnet run file_name
*
* 入力ファイルの文字コードはUTF-8
*
*/
namespace selectToCsv
{
class Program
{
static void Main(string[] args)
{
StreamReader sr = new StreamReader(args[0], Encoding.GetEncoding("UTF-8"));
string query = sr.ReadToEnd();
sr.Close();
Parse(query);
}
static void Parse(string query)
{
var parser = new TSql130Parser(false);
IList<ParseError> errors;
var fragment = parser.Parse(new StringReader(query), out errors);
if (errors.Count != 0)
{
throw new Exception("パース失敗");
}
TSqlScript sqlScript = fragment as TSqlScript;
iter(sqlScript.Batches);
}
static void iter(IList<TSqlBatch> sqlBatches)
{
foreach (TSqlBatch sqlBatch in sqlBatches)
{
iter(sqlBatch.Statements);
}
}
static void iter(IList<TSqlStatement> st)
{
foreach (TSqlStatement sts in st)
{
if (sts is SelectStatement)
{
ParseSelectStatement(sts as SelectStatement);
}
}
}
static void ParseSelectStatement(SelectStatement st)
{
ParseSelectToken(st.ScriptTokenStream);
}
static void ParseSelectToken(IList<TSqlParserToken> tokens)
{
Select statement = new Select(tokens);
Identifier identifier = new Identifier(statement);
print(identifier);
}
static void print(object o)
{
Console.WriteLine(o);
}
class Select
{
public TSqlParserToken State { get; set; }
public List<List<TSqlParserToken>> SelectList { get; set; }
public List<List<TSqlParserToken>> TableSource { get; set; }
public List<List<TSqlParserToken>> SearchCondition { get; set; }
public List<List<TSqlParserToken>> OrderExpression { get; set; }
public Select(IList<TSqlParserToken> tokens)
{
SelectList = new List<List<TSqlParserToken>>();
TableSource = new List<List<TSqlParserToken>>();
SearchCondition = new List<List<TSqlParserToken>>();
OrderExpression = new List<List<TSqlParserToken>>();
foreach (TSqlParserToken token in tokens)
{
Add(token);
}
}
public void Add(TSqlParserToken token)
{
switch (token.TokenType)
{
case TSqlTokenType.Select:
NewSelect();
State = token;
return;
case TSqlTokenType.From:
NewTable();
State = token;
return;
case TSqlTokenType.Where:
NewCondition();
State = token;
return;
case TSqlTokenType.Order:
NewOrder();
State = token;
return;
case TSqlTokenType.WhiteSpace:
case TSqlTokenType.SingleLineComment:
case TSqlTokenType.MultilineComment:
return;
}
if (State == null)
{
return;
}
if (State.TokenType == TSqlTokenType.Select)
{
if (token.TokenType == TSqlTokenType.Comma)
{
NewSelect();
return;
}
SelectList[SelectList.Count - 1].Add(token);
}
if (State.TokenType == TSqlTokenType.From)
{
if (token.TokenType == TSqlTokenType.Inner || token.TokenType == TSqlTokenType.Join)
{
NewTable();
return;
}
TableSource[TableSource.Count - 1].Add(token);
}
if (State.TokenType == TSqlTokenType.Where)
{
SearchCondition[SearchCondition.Count - 1].Add(token);
}
if (State.TokenType == TSqlTokenType.Order)
{
OrderExpression[OrderExpression.Count - 1].Add(token);
}
}
public List<TSqlParserToken> NewSelect()
{
List<TSqlParserToken> select = new List<TSqlParserToken>();
SelectList.Add(select);
return select;
}
public List<TSqlParserToken> NewTable()
{
List<TSqlParserToken> table = new List<TSqlParserToken>();
TableSource.Add(table);
return table;
}
public List<TSqlParserToken> NewCondition()
{
List<TSqlParserToken> condition = new List<TSqlParserToken>();
SearchCondition.Add(condition);
return condition;
}
public List<TSqlParserToken> NewOrder()
{
List<TSqlParserToken> order = new List<TSqlParserToken>();
OrderExpression.Add(order);
return order;
}
}
class SelectIdentifier
{
public string Table { get; set; }
public string Column { get; set; }
public string Alias { get; set; }
public string Statement { get; set; }
public SelectIdentifier(List<TSqlParserToken> tokens)
{
if (tokens.Count >= 2)
{
if (tokens[1].TokenType == TSqlTokenType.Dot)
{
Table = tokens[0].Text;
Column = tokens[2].Text;
}
}
if (Column == null)
{
Column = tokens[0].Text;
}
var indexOfAs = tokens.FindLastIndex((token) => token.TokenType == TSqlTokenType.As);
if (indexOfAs != -1)
{
Alias = tokens[indexOfAs + 1].Text;
}
Statement = String.Join("", tokens.ConvertAll((token) =>
{
if (token.TokenType == TSqlTokenType.Dot)
{
return token.Text;
}
if (token.TokenType == TSqlTokenType.Identifier)
{
return token.Text;
}
return " " + token.Text + " ";
}));
}
public override string ToString()
{
return Table + "," + Column + "," + Alias + "," + Statement;
}
public static string Header()
{
return "Table,Column,Alias,Statement";
}
}
class TableIdentifier
{
public string Database { get; set; }
public string Scheme { get; set; }
public string Table { get; set; }
public string Alias { get; set; }
public string Statement { get; set; }
public TableIdentifier(List<TSqlParserToken> tokens)
{
var indexOfOn = tokens.FindIndex((token) => token.TokenType == TSqlTokenType.On);
if (indexOfOn != -1)
{
tokens = tokens = tokens.GetRange(0, indexOfOn);
}
tokens = tokens.FindAll((token) =>
{
return token.TokenType == TSqlTokenType.Dot || token.TokenType == TSqlTokenType.Identifier;
});
var dotCount = tokens.FindAll((token) => token.TokenType == TSqlTokenType.Dot).Count;
switch (dotCount)
{
case 0:
Table = tokens[0].Text;
break;
case 1:
Scheme = tokens[0].Text;
Table = tokens[2].Text;
break;
case 2:
var i = tokens.FindIndex((token) => token.TokenType == TSqlTokenType.Dot);
Database = tokens[i - 1].Text;
var i2 = tokens.FindIndex(i + 1, (token) => token.TokenType == TSqlTokenType.Dot);
if (i != (i2 - 1))
{
Scheme = tokens[i2 - i].Text;
}
Table = tokens[i2 + 1].Text;
if (tokens.Count > i2 + 2)
{
Alias = tokens[i2 + 2].Text;
}
break;
}
}
public override string ToString()
{
return Database + "," + Scheme + "," + Table + "," + Alias;
}
public static string Header()
{
return "Database,Scheme,Table,Alias";
}
}
class Identifier
{
public List<SelectIdentifier> SelectIdentifiers { get; set; }
public List<TableIdentifier> TableIdentifiers { get; set; }
public Identifier(Select statement)
{
SelectIdentifiers = new List<SelectIdentifier>();
TableIdentifiers = new List<TableIdentifier>();
foreach (var list in statement.SelectList)
{
SelectIdentifiers.Add(new SelectIdentifier(list));
}
foreach (var list in statement.TableSource)
{
if (list.Count == 0)
{
continue;
}
TableIdentifiers.Add(new TableIdentifier(list));
}
foreach (var table in TableIdentifiers)
{
foreach (var select in SelectIdentifiers)
{
if (select.Table == table.Alias)
{
select.Table = table.Table;
}
}
}
}
public override string ToString()
{
return
"Table\r\n" +
TableIdentifier.Header() + "\r\n" +
String.Join("\r\n", TableIdentifiers.ConvertAll((table) => table.ToString())) + "\r\n\r\n" +
"Columns\r\n" +
SelectIdentifier.Header() + "\r\n" +
String.Join("\r\n", SelectIdentifiers.ConvertAll((select) => select.ToString()));
}
}
}
}
ライブラリを呼び出すだけの簡単なお仕事！ Enjoy!
