/// <summary>
/// 生成SQL分页语句，记录总数为0表示生成统计语句
/// </summary>
/// <param name="dbmsType">数据库类型</param>
/// <param name="strSQLInfo">原始SQL语句</param>
/// <param name="strWhere">在分页前要替换的字符串，用于分页前的筛选</param>
/// <param name="PageSize">页大小</param>
/// <param name="PageNumber">页码</param>
/// <param name="AllCount">记录总数，如果是0则生成统计记录数量的查询</param>
/// <returns>生成SQL分页语句</returns>
public static string MakeSQLStringByPage(DBMSType dbmsType, string strSQLInfo, string strWhere, int PageSize, int PageNumber, int AllCount)
{
//根据不同的数据库引擎调用不同生成器
string SQL = string.Empty;
switch (dbmsType)
{
case DBMSType.Access:
case DBMSType.SqlServer:
SQL = MakePageSQLStringByMSSQL(strSQLInfo, strWhere, PageSize, PageNumber, AllCount);
break;
case DBMSType.Oracle:
SQL = MakePageSQLStringByOracle(strSQLInfo, strWhere, PageSize, PageNumber, AllCount);
break;
case DBMSType.MySql:
case DBMSType.SQLite:
SQL = MakePageSQLStringByMySQL(strSQLInfo, strWhere, PageSize, PageNumber, AllCount);
break;
case DBMSType.PostgreSQL:
SQL = MakePageSQLStringByPostgreSQL(strSQLInfo, strWhere, PageSize, PageNumber, AllCount);
break;
default:
//SQL = MakePageSQLStringByMSSQL(strSQLInfo, strWhere, PageSize, PageNumber, AllCount);
//SQL = strSQLInfo;
//break;
throw new Exception("分页错误：不支持此种类型的数据库分页。");
}
return(SQL);
}
public AddResultVisitor(string exprStr
, Identifier aliasName
, DBMSType dbmsType = DBMSType.Unknown
, bool forSqlAccessor = false)
: base(-1, exprStr, aliasName, dbmsType, forSqlAccessor)
{
}
public static string GetCNF(string inputText
, DBMSType dbmsType
, Dictionary <string, string> placeHolders = null)
{
var ast = MiniSqlParserAST.CreateStmts(inputText, dbmsType);
// テスト用テーブル列
var tableColumns = new BestCaseDictionary <IEnumerable <string> >();
var tableT = new string[] { "x", "y", "z" };
tableColumns.Add("T", tableT);
var tableU = new string[] { "x", "y", "z" };
tableColumns.Add("U", tableU);
var tableV = new string[] { "x1", "x2", "x3" };
tableColumns.Add("V", tableV);
var replacer = new ReplacePlaceHolders(placeHolders);
ast.Accept(replacer);
var visitor = new GetCNFVisitor(tableColumns, true);
ast.Accept(visitor);
return(visitor.Print(true));
}
public static string RenameColumnInOrderBy(string inputText
, DBMSType dbmsType
, Dictionary <string, string> placeHolders = null)
{
var ast = MiniSqlParserAST.CreateStmts(inputText, dbmsType);
// テスト用テーブル列
var tableColumns = new BestCaseDictionary <IEnumerable <string> >();
var tableT = new string[] { "x", "y", "z" };
tableColumns.Add("T", tableT);
var tableU = new string[] { "x", "y", "z" };
tableColumns.Add("U", tableU);
var tableV = new string[] { "x1", "x2", "x3" };
tableColumns.Add("V", tableV);
var replacer = new ReplacePlaceHolders(placeHolders);
ast.Accept(replacer);
var visitor = new NormalizeOrderByVisitor(tableColumns, true);
ast.Accept(visitor);
var stringifier = new BeautifulStringifier(144);
ast.Accept(stringifier);
return(stringifier.ToString());
}
private static MiniSqlParserParser CreateParser(string inputStr
, DBMSType dbmsType
, bool forSqlAccessor)
{
var input = new AntlrInputStream(inputStr);
var lexer = new MiniSqlParserLexer(input);
var tokens = new CommonTokenStream(lexer);
var parser = new MiniSqlParserParser(tokens);
var astListener = new MakeASTListener(tokens, dbmsType, forSqlAccessor);
var errorListener = new CumulativeErrorListener();
var lexerErrorListener = new CumulativeLexerErrorListener();
MiniSqlParserAST.SetDbmsType(lexer, parser, dbmsType);
// 文法で曖昧な箇所は動的にしか発見できないらしい
//parser.AddErrorListener(new DiagnosticErrorListener());
//parser.Interpreter.PredictionMode = PredictionMode.LlExactAmbigDetection;
lexer.RemoveErrorListeners();
lexer.AddErrorListener(lexerErrorListener);
parser.AddParseListener(astListener);
parser.RemoveErrorListeners();
parser.AddErrorListener(errorListener);
return(parser);
}
/// <summary>
/// 根据数据库管理系统枚举类型和连接字符串创建一个新的数据访问对象实例
/// </summary>
/// <param name="DbmsType">数据库类型媒介，有ACCESS/MYSQL/ORACLE/SQLSERVER/SYSBASE/UNKNOWN </param>
/// <param name="ConnectionString">连接字符串</param>
/// <returns>数据访问对象</returns>
public static AdoHelper GetDBHelper(DBMSType DbmsType, string ConnectionString)
{
string EngineType = "";
switch (DbmsType)
{
case DBMSType.Access:
EngineType = "OleDb"; break;
case DBMSType.MySql:
EngineType = "Odbc"; break;
case DBMSType.Oracle:
EngineType = "Oracle"; break;
case DBMSType.SqlServer:
EngineType = "SqlServer"; break;
case DBMSType.SqlServerCe:
EngineType = "SqlServerCe"; break;
case DBMSType.Sysbase:
EngineType = "OleDb"; break;
case DBMSType.SQLite:
EngineType = "SQLite"; break;
case DBMSType.UNKNOWN:
EngineType = "Odbc"; break;
}
AdoHelper helper = GetDBHelper(EngineType);
helper.ConnectionString = ConnectionString;
return(helper);
}
/// <summary>
/// Gets a standard connection string from a list of Username, Password, and Server
/// </summary>
/// <param name="DBMS">Type of database to format the ConnectionStr for</param>
/// <param name="UserName">Username in connection string</param>
/// <param name="Password">Password in connection string</param>
/// <param name="Server">Server in connection string</param>
public static string GetConnectionStr(
DBMSType DBMS,
string UserName,
string Password,
string Server,
string Database)
{
switch (DBMS)
{
case Sempra.Ops.Utils.DBMSType.Oracle:
return
("user id=" + UserName +
";password="******";data source=" + Server);
case Sempra.Ops.Utils.DBMSType.SQLServer:
return
("server=\"" + Server +
"\";uid=\"" + UserName +
"\";pwd=\"" + Password +
"\";database=\"" + Database + "\";");
default:
return("");
} //switch DBMS
}
private static string Unquote(string idStr
, DBMSType dbmsType
, bool mySqlAnsiQuotes)
{
var length = idStr.Length;
if (length < 2)
{
return(idStr);
}
var openQuote = idStr[0];
var closeQuote = Identifier.GetCloseQuote(openQuote, dbmsType, mySqlAnsiQuotes);
if (closeQuote == '\0')
{
return(idStr);
}
if (idStr[length - 1] != closeQuote)
{
string message = string.Format("Identifier \"{0}\" isn't quoted.", idStr);
throw new ApplicationException(message);
}
string innerStr = idStr.Substring(1, length - 2);
string pattern = string.Format("{0}{0}", closeQuote);
string replacement = string.Format("{0}", closeQuote);
return(innerStr.Replace(pattern, replacement));
}
public void ConsequtiveTransactionsNested_4(DBMSType dbmsType)
{
using (var dac = EnterCreateDatabaseScope(dbmsType, TestTables.BasicTable)) {
using (var scope0 = dac.BeginScope()) {
using (var scope1 = dac.BeginScope()) {
scope1.BeginTransaction();
using (var scope2 = dac.BeginScope(true)) {
scope2.BeginTransaction();
dac.Insert("BasicTable", new[] { new ColumnValue("ID", 1) });
scope2.Commit();
scope2.BeginTransaction();
dac.Insert("BasicTable", new[] { new ColumnValue("ID", 2) });
scope2.Rollback();
scope2.BeginTransaction();
dac.Insert("BasicTable", new[] { new ColumnValue("ID", 3) });
scope2.Commit();
}
scope1.Commit();
}
scope0.BeginTransaction();
dac.Insert("BasicTable", new[] { new ColumnValue("ID", 4) });
scope0.Commit();
}
Assert.AreEqual(1, dac.Count("BasicTable"));
}
}
/// <summary>
/// Gets a standard connection string from a list of Username, Password, and Server
/// </summary>
/// <param name="DBMS">Type of database to format the ConnectionStr for</param>
/// <param name="UserName">Username in connection string</param>
/// <param name="Password">Password in connection string</param>
/// <param name="Server">Server in connection string</param>
public static string GetConnectionStr(
DBMSType DBMS,
string UserName,
string Password,
string Server)
{
string strResult = "";
switch (DBMS)
{
case Sempra.Ops.DBMSType.Oracle:
//DATA SOURCE=SEMPRA.PROD;PERSIST SECURITY INFO=True;USER ID=OPS_TRACKING
strResult =
"user id=" + UserName +
";password="******";data source=" + Server;
break;
case Sempra.Ops.DBMSType.SQLServer:
strResult =
"uid=\"" + UserName +
"\";pwd=\"" + Password +
"\";server=\"" + Server + "\";";
break;
default:
strResult = "";
break;
} //switch DBMS
return(strResult);
}
/// <summary>
///ฐานข้อมูล Application Object
/// </summary>
/// <param name="inAlias">ระบุชื่อ Alias ที่จะใช้อ้างอิงใน DataSet</param>
/// <param name="inConnectionString">ระบุ Connection String ในการ Access Database</param>
/// <param name="inDataBaseReside">ระบุ Database Type</param>
public cEntityBase(string inAlias, string inConnectionString, DBMSType inDataBaseReside)
{
this.mstrAlias = inAlias;
this.mstrBrowViewAlias = this.mstrAlias;
this.mstrConnectionString = inConnectionString;
this.mDataBaseReside = inDataBaseReside;
this.pmInitComponent();
}
public SqlBuilder(string sqlStr
, DbmsType dbmsType = DbmsType.Unknown
, bool forSqlAccessor = true)
{
_sqlStr = sqlStr;
_dbmsType = SqlBuilder.ConvertDbmsType(dbmsType);
_forSqlAccessor = forSqlAccessor;
}
private string GetCNFOfPredicate(string predicate, DBMSType dbmsType = DBMSType.Unknown, bool ignoreCase = true)
{
var ast = MiniSqlParserAST.CreatePredicate(predicate, dbmsType);
var visitor = new GetCNFVisitor(_tableColumns, ignoreCase);
ast.Accept(visitor);
return(visitor.Print());
}
public async Task ReuseConnection_DifferentDAC_Async2(DBMSType dbmsType)
{
using (var dac = EnterCreateDatabaseScope(dbmsType, TestTables.BasicTable)) {
var dac2 = DuplicateDAC(dac);
using (var scope = dac.BeginScope(true)) {
await Task.Run(() => AssertConnectionPropagationAsync(scope.Connection, dac2));
}
}
}
public void Error_TransactionNotClosed(DBMSType dbmsType)
{
using (var dac = EnterCreateDatabaseScope(dbmsType, TestTables.BasicTable)) {
var scope = dac.BeginScope(true);
scope.BeginTransaction();
dac.Insert("BasicTable", new[] { new ColumnValue("ID", 1) });
Assert.Catch(() => scope.Dispose());
}
}
static private bool dbmsTypeRequireConnector(DBMSType type)
{
switch (type)
{
case DBMSType.MySQL: return(true);
default: return(false);
}
}
internal SqlBuilder(Stmt stmt
, DbmsType dbmsType = DbmsType.Unknown
, bool forSqlAccessor = true)
{
_stmt = stmt;
_dbmsType = SqlBuilder.ConvertDbmsType(dbmsType);
this.Dbms = dbmsType;
_forSqlAccessor = forSqlAccessor;
}
private void initilize(ConnectionParameters p)
{
this._connection = null;
this._connector = p.Connector;;
this._type = p.Type;
this._string_connection = DataBaseLogic.createStringConnection(p.Type, p.Type == DBMSType.SQLite? p.File : p.Host, p.Database, p.User, p.Password, p.Port);
this._parameters = p;
this._create_with_begin_transaction = p.BeginTransaction;
}
public void Error_BeginTransactionAfterAutoEnlisted(DBMSType dbmsType)
{
using (var dac = EnterCreateDatabaseScope(dbmsType, TestTables.BasicTable)) {
using (new TransactionScope(TransactionScopeOption.Required)) {
using (var scope = dac.BeginScope(true)) {
Assert.Catch(() => scope.BeginTransaction());
}
}
}
}
protected virtual UnitTestDAC EnterCreateDatabaseScope(DBMSType dbmsType, params TableSpecification[] tables)
{
var result = EnterCreateEmptyDatabaseScope(dbmsType);
foreach (var table in tables)
{
result.CreateTable(table);
}
return(result);
}
public void ReuseConnection_SameDAC_4(DBMSType dbmsType)
{
using (var dac = EnterCreateDatabaseScope(dbmsType, TestTables.BasicTable)) {
using (var scope = dac.BeginScope(true)) {
using (var scope2 = dac.BeginScope(true)) {
Assert.AreSame(scope.Connection, scope2.Connection);
}
}
}
}
public void Rollback_AutoEnlist(DBMSType dbmsType)
{
using (var dac = EnterCreateDatabaseScope(dbmsType, TestTables.BasicTable)) {
using (new TransactionScope(TransactionScopeOption.Required)) {
using (dac.BeginScope(true)) {
dac.Insert("BasicTable", new[] { new ColumnValue("ID", 1) });
}
}
Assert.AreEqual(0, dac.Count("BasicTable"));
}
}
public InsertResultVisitor(int index
, string exprStr
, Identifier aliasName
, DBMSType dbmsType = DBMSType.Unknown
, bool forSqlAccessor = false)
{
_index = index;
var expr = MiniSqlParserAST.CreateExpr(exprStr, dbmsType, forSqlAccessor);
_result = new ResultExpr(expr, true, aliasName);
}
private string NormalizeOrderBy(string sql, DBMSType dbmsType = DBMSType.Unknown, bool ignoreCase = true)
{
var ast = MiniSqlParserAST.CreateStmts(sql, dbmsType);
var visitor = new NormalizeOrderByVisitor(_tableColumns, ignoreCase);
ast.Accept(visitor);
var stringifier = new CompactStringifier(4098, true);
ast.Accept(stringifier);
return(stringifier.ToString());
}
public static string Compact(string inputText
, DBMSType dbmsType
, Dictionary <string, string> placeHolders = null)
{
var ast = MiniSqlParserAST.CreateStmts(inputText, dbmsType);
var placeHolderNodes = SetPlaceHoldersVisitor.ConvertPlaceHolders(placeHolders);
var stringifier = new CompactStringifier(64, true, placeHolderNodes);
ast.Accept(stringifier);
return(stringifier.ToString());
}
public void ReuseConnection_DifferentDAC_2(DBMSType dbmsType)
{
using (var dac = EnterCreateDatabaseScope(dbmsType, TestTables.BasicTable)) {
var dac2 = DuplicateDAC(dac);
using (var scope = dac.BeginScope(false)) {
using (var scope2 = dac2.BeginScope(true)) {
Assert.AreSame(scope.Connection, scope2.Connection);
}
}
}
}
public void RollbackTransaction(DBMSType dbmsType)
{
using (var dac = EnterCreateDatabaseScope(dbmsType, TestTables.BasicTable)) {
using (var scope = dac.BeginScope(true)) {
scope.BeginTransaction();
dac.Insert("BasicTable", new[] { new ColumnValue("ID", 1) });
scope.Rollback();
}
Assert.AreEqual(0, dac.Count("BasicTable"));
}
}
public void RepeatTransaction(DBMSType dbmsType)
{
using (var dac = EnterCreateDatabaseScope(dbmsType, TestTables.BasicTable)) {
Assert.Catch(() => {
using (var scope0 = dac.BeginScope()) {
scope0.BeginTransaction();
Assert.Catch(() => scope0.BeginTransaction());
}
});
}
}
public void Error_ManualEnlistAfterBeginTransaction(DBMSType dbmsType)
{
using (var dac = EnterCreateDatabaseScope(dbmsType, TestTables.BasicTable)) {
using (var scope = dac.BeginScope(true)) {
scope.BeginTransaction();
using (new TransactionScope(TransactionScopeOption.Required)) {
Assert.Catch(() => scope.EnlistInSystemTransaction());
}
scope.Commit();
}
}
}
private static void SetDbmsType(MiniSqlParserLexer lexer
, MiniSqlParserParser parser
, DBMSType dbmsType)
{
if (dbmsType == DBMSType.Unknown)
{
lexer.IsOracle = true;
parser.IsOracle = true;
lexer.IsMySql = true;
parser.IsMySql = true;
lexer.IsSQLite = true;
parser.IsSQLite = true;
lexer.IsMsSql = true;
parser.IsMsSql = true;
lexer.IsPostgreSql = true;
parser.IsPostgreSql = true;
lexer.IsPervasive = true;
parser.IsPervasive = true;
// Unkownの場合""で囲まれた文字列をIDENTIFIERとして
// 認識させるためMySqlAnsiQuotes=trueとする.
lexer.MySqlAnsiQuotes = true;
}
else if (dbmsType == DBMSType.Oracle)
{
lexer.IsOracle = true;
parser.IsOracle = true;
}
else if (dbmsType == DBMSType.MySql)
{
lexer.IsMySql = true;
parser.IsMySql = true;
}
else if (dbmsType == DBMSType.SQLite)
{
lexer.IsSQLite = true;
parser.IsSQLite = true;
}
else if (dbmsType == DBMSType.MsSql)
{
lexer.IsMsSql = true;
parser.IsMsSql = true;
}
else if (dbmsType == DBMSType.PostgreSql)
{
lexer.IsPostgreSql = true;
parser.IsPostgreSql = true;
}
else if (dbmsType == DBMSType.Pervasive)
{
lexer.IsPervasive = true;
parser.IsPervasive = true;
}
}
/// <summary>
/// 使用指定的数据库类型初始化本类
/// </summary>
/// <param name="dbType"></param>
public CommandInfo(DBMSType dbType)
{
this.DataBaseType = dbType;
}
/// <summary>
/// Gets a standard connection string from a list of Username, Password, and Server
/// </summary>
/// <param name="DBMS">Type of database to format the ConnectionStr for</param>
/// <param name="UserName">Username in connection string</param>
/// <param name="Password">Password in connection string</param>
/// <param name="Server">Server in connection string</param>
public static string GetConnectionStr(
DBMSType DBMS,
string UserName,
string Password,
string Server )
{
string strResult = "";
switch( DBMS )
{
case Sempra.Ops.DBMSType.Oracle :
//DATA SOURCE=SEMPRA.PROD;PERSIST SECURITY INFO=True;USER ID=OPS_TRACKING
strResult =
"user id=" + UserName +
";password="******";data source=" + Server;
break;
case Sempra.Ops.DBMSType.SQLServer:
strResult =
"uid=\"" + UserName +
"\";pwd=\"" + Password +
"\";server=\"" + Server + "\";";
break;
default :
strResult = "";
break;
}//switch DBMS
return strResult;
}
/// <summary>
/// 根据数据库管理系统枚举类型和连接字符串创建一个新的数据访问对象实例
/// </summary>
/// <param name="DbmsType">数据库类型媒介，有ACCESS/MYSQL/ORACLE/SQLSERVER/SYSBASE/UNKNOWN </param>
/// <param name="ConnectionString">连接字符串</param>
/// <returns>数据访问对象</returns>
public static AdoHelper GetDBHelper(DBMSType DbmsType, string ConnectionString)
{
string EngineType = "";
switch (DbmsType)
{
case DBMSType.Access:
EngineType = "OleDb"; break;
case DBMSType.MySql:
EngineType = "Odbc"; break;
case DBMSType.Oracle:
EngineType = "Oracle"; break;
case DBMSType.SqlServer:
EngineType = "SqlServer"; break;
case DBMSType.SqlServerCe:
EngineType = "SqlServerCe"; break;
case DBMSType.Sysbase:
EngineType = "OleDb"; break;
case DBMSType.SQLite:
EngineType = "SQLite"; break;
case DBMSType.UNKNOWN:
EngineType = "Odbc"; break;
}
AdoHelper helper = GetDBHelper(EngineType);
helper.ConnectionString = ConnectionString;
return helper;
}
//基本查找路径模板
/// <summary>
/// 构造函数
/// </summary>
/// <param name="SqlTextPath">配置文件路径</param>
/// <param name="ScriptType">数据类型</param>
/// <remarks></remarks>
public XmlCommand(string SqlTextPath, DBMSType ScriptType)
{
this.SqlTextPath = SqlTextPath;
this.ScriptType = ScriptType;
}
/// <summary>
/// 生成SQL分页语句，记录总数为0表示生成统计语句
/// </summary>
/// <param name="dbmsType">数据库类型</param>
/// <param name="strSQLInfo">原始SQL语句</param>
/// <param name="strWhere">在分页前要替换的字符串，用于分页前的筛选</param>
/// <param name="PageSize">页大小</param>
/// <param name="PageNumber">页码</param>
/// <param name="AllCount">记录总数，如果是0则生成统计记录数量的查询</param>
/// <returns>生成SQL分页语句</returns>
public static string MakeSQLStringByPage(DBMSType dbmsType, string strSQLInfo, string strWhere, int PageSize, int PageNumber, int AllCount)
{
//根据不同的数据库引擎调用不同生成器
string SQL = string.Empty;
switch (dbmsType)
{
case DBMSType.Access:
case DBMSType.SqlServer:
SQL = MakePageSQLStringByMSSQL(strSQLInfo, strWhere, PageSize, PageNumber, AllCount);
break;
case DBMSType.Oracle:
SQL = MakePageSQLStringByOracle(strSQLInfo, strWhere, PageSize, PageNumber, AllCount);
break;
case DBMSType.MySql:
case DBMSType.SQLite:
SQL = MakePageSQLStringByMySQL(strSQLInfo, strWhere, PageSize, PageNumber, AllCount);
break;
case DBMSType.PostgreSQL:
SQL = MakePageSQLStringByPostgreSQL(strSQLInfo, strWhere, PageSize, PageNumber, AllCount);
break;
default:
//SQL = MakePageSQLStringByMSSQL(strSQLInfo, strWhere, PageSize, PageNumber, AllCount);
//SQL = strSQLInfo;
//break;
throw new Exception("分页错误：不支持此种类型的数据库分页。");
}
return SQL;
}
