public void Server_Constructors(DatabaseType dbType) { var helper = ImplementationManager.GetImplementation(dbType).GetServerHelper(); var server = new DiscoveredServer(helper.GetConnectionStringBuilder("localhost", null, "franko", "wacky").ConnectionString, dbType); Assert.AreEqual("localhost", server.Name); }
/// <summary> /// Executes the given SQL against the database + sends GO delimited statements as separate batches /// </summary> /// <param name="sql">Collection of SQL queries which can be separated by the use of "GO" on a line (works for all DBMS)</param> /// <param name="conn"></param> /// <param name="transaction"></param> /// <param name="performanceFigures">Line number the batch started at and the time it took to complete it</param> /// <param name="timeout">Timeout in seconds to run each batch in the <paramref name="sql"/></param> public void ExecuteBatchNonQuery(string sql, DbConnection conn, DbTransaction transaction, out Dictionary <int, Stopwatch> performanceFigures, int timeout = 30) { performanceFigures = new Dictionary <int, Stopwatch>(); string sqlBatch = string.Empty; var helper = ImplementationManager.GetImplementation(conn).GetServerHelper(); DbCommand cmd = helper.GetCommand(string.Empty, conn, transaction); bool hadToOpen = false; if (conn.State != ConnectionState.Open) { conn.Open(); hadToOpen = true; } int lineNumber = 1; sql += "\nGO"; // make sure last batch is executed. try { foreach (string line in sql.Split(new[] { "\n", "\r" }, StringSplitOptions.RemoveEmptyEntries)) { lineNumber++; if (line.ToUpperInvariant().Trim() == "GO") { if (string.IsNullOrWhiteSpace(sqlBatch)) { continue; } if (!performanceFigures.ContainsKey(lineNumber)) { performanceFigures.Add(lineNumber, new Stopwatch()); } performanceFigures[lineNumber].Start(); cmd.CommandText = sqlBatch; cmd.CommandTimeout = timeout; cmd.ExecuteNonQuery(); performanceFigures[lineNumber].Stop(); sqlBatch = string.Empty; } else { sqlBatch += line + "\n"; } } } finally { if (hadToOpen) { conn.Close(); } } }
/// <summary> /// <para>Creates a new server pointed at the <paramref name="builder"/> server. </para> /// /// <para><see cref="ImplementationManager"/> must have a loaded implementation for the DBMS type</para> /// </summary> /// <param name="builder">Determines the connection string and <see cref="DatabaseType"/> e.g. MySqlConnectionStringBuilder = DatabaseType.MySql</param> /// <exception cref="ImplementationNotFoundException"></exception> public DiscoveredServer(DbConnectionStringBuilder builder) { Helper = ImplementationManager.GetImplementation(builder).GetServerHelper();; //give helper a chance to mutilate the builder if he wants (also gives us a new copy of the builder incase anyone external modifies the old reference) Builder = Helper.GetConnectionStringBuilder(builder.ConnectionString); }
public void SyntaxHelperTest_GetRuntimeName(DatabaseType t, bool expectUpper) { ImplementationManager.Load(new DirectoryInfo(TestContext.CurrentContext.TestDirectory)); var syntaxHelper = ImplementationManager.GetImplementation(t).GetQuerySyntaxHelper(); Assert.AreEqual(expectUpper?"CHI":"chi", syntaxHelper.GetRuntimeName("\"TEST_ScratchArea\".public.\"Biochemistry\".\"chi\"")); Assert.AreEqual(expectUpper?"FRANK":"Frank", syntaxHelper.GetRuntimeName("count(*) as Frank")); Assert.AreEqual(expectUpper?"FRANK":"Frank", syntaxHelper.GetRuntimeName("count(cast(1 as int)) as Frank")); Assert.AreEqual(expectUpper?"FRANK":"Frank", syntaxHelper.GetRuntimeName("[mydb].[mytbl].[mycol] as Frank")); Assert.AreEqual(expectUpper?"FRANK":"Frank", syntaxHelper.GetRuntimeName("[mydb].[mytbl].[mycol] as [Frank]")); Assert.AreEqual(expectUpper?"FRANK":"Frank", syntaxHelper.GetRuntimeName("\"Mydb\".\"mytbl\".\"mycol\" as \"Frank\"")); Assert.AreEqual(expectUpper?"FRANK":"Frank", syntaxHelper.GetRuntimeName("[mydb].[mytbl].[mycol] as `Frank`")); Assert.AreEqual(expectUpper?"MYCOL":"mycol", syntaxHelper.GetRuntimeName("[mydb].[mytbl].[mycol]")); Assert.AreEqual(expectUpper?"ZOMBIE":"zombie", syntaxHelper.GetRuntimeName("dbo.GetMyCoolThing(\"Magic Fun Times\") as zombie")); Assert.AreEqual(expectUpper?"FRANK":"Frank", syntaxHelper.GetRuntimeName("`mydb`.`mytbl`.`mycol` as `Frank`")); Assert.AreEqual(expectUpper?"FRANK":"Frank", syntaxHelper.GetRuntimeName("\"mydb\".\"mytbl\".\"mycol\" as \"Frank\"")); Assert.IsTrue(syntaxHelper.TryGetRuntimeName("\"mydb\".\"mytbl\".\"mycol\" as \"Frank\"", out string name)); Assert.AreEqual(expectUpper?"FRANK":"Frank", name); }
public void ServerHelper_ChangeDatabase_AdHoc(DatabaseType type, bool useApiFirst) { //create initial server reference var helper = ImplementationManager.GetImplementation(type).GetServerHelper(); var server = new DiscoveredServer(helper.GetConnectionStringBuilder("loco", "bob", "franko", "wacky")); Assert.AreEqual("loco", server.Name); Assert.AreEqual("bob", server.GetCurrentDatabase().GetRuntimeName()); //Use API to change databases if (useApiFirst) { server.ChangeDatabase("omgggg"); Assert.AreEqual("loco", server.Name); Assert.AreEqual("omgggg", server.GetCurrentDatabase().GetRuntimeName()); } //adhoc changes to builder server.Builder["Database"] = "Fisss"; Assert.AreEqual("loco", server.Name); Assert.AreEqual("Fisss", server.GetCurrentDatabase().GetRuntimeName()); server.Builder["Server"] = "Amagad"; Assert.AreEqual("Amagad", server.Name); Assert.AreEqual("Fisss", server.GetCurrentDatabase().GetRuntimeName()); }
public void SyntaxHelperTest_GetRuntimeName_MultipleCalls(DatabaseType dbType, string runtime, string wrapped) { // NOTE: Oracle does not support such shenanigans https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements008.htm // "neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0)." ImplementationManager.Load(new DirectoryInfo(TestContext.CurrentContext.TestDirectory)); var syntaxHelper = ImplementationManager.GetImplementation(dbType).GetQuerySyntaxHelper(); var currentName = runtime; for (int i = 0; i < 10; i++) { if (i % 2 == 0) { Assert.AreEqual(runtime, currentName); currentName = syntaxHelper.EnsureWrapped(currentName); currentName = syntaxHelper.EnsureWrapped(currentName); currentName = syntaxHelper.EnsureWrapped(currentName); } else { Assert.AreEqual(wrapped, currentName); currentName = syntaxHelper.GetRuntimeName(currentName); currentName = syntaxHelper.GetRuntimeName(currentName); currentName = syntaxHelper.GetRuntimeName(currentName); } } }
public void ServerHelper_ChangeDatabase_AdHoc(DatabaseType type, bool useApiFirst) { if (type == DatabaseType.Oracle) { Assert.Inconclusive("FAnsiSql understanding of Database cannot be encoded in DbConnectionStringBuilder sadly so we can end up with DiscoveredServer with no GetCurrentDatabase"); } //create initial server reference var helper = ImplementationManager.GetImplementation(type).GetServerHelper(); var server = new DiscoveredServer(helper.GetConnectionStringBuilder("loco", "bob", "franko", "wacky")); Assert.AreEqual("loco", server.Name); Assert.AreEqual("bob", server.GetCurrentDatabase().GetRuntimeName()); //Use API to change databases if (useApiFirst) { server.ChangeDatabase("omgggg"); Assert.AreEqual("loco", server.Name); Assert.AreEqual("omgggg", server.GetCurrentDatabase().GetRuntimeName()); } //adhoc changes to builder server.Builder["Database"] = "Fisss"; Assert.AreEqual("loco", server.Name); Assert.AreEqual("Fisss", server.GetCurrentDatabase().GetRuntimeName()); server.Builder["Server"] = "Amagad"; Assert.AreEqual("Amagad", server.Name); Assert.AreEqual("Fisss", server.GetCurrentDatabase().GetRuntimeName()); }
public void Test_GetFullyQualifiedName(DatabaseType dbType) { ImplementationManager.Load(new DirectoryInfo(TestContext.CurrentContext.TestDirectory)); var syntaxHelper = ImplementationManager.GetImplementation(dbType).GetQuerySyntaxHelper(); var name = syntaxHelper.EnsureFullyQualified("mydb", null, "Troll", ",,,"); Assert.AreEqual(",,,", syntaxHelper.GetRuntimeName(name)); switch (dbType) { case DatabaseType.MicrosoftSQLServer: Assert.AreEqual("[mydb]..[Troll].[,,,]", name); break; case DatabaseType.MySql: Assert.AreEqual("`mydb`.`Troll`.`,,,`", name); break; case DatabaseType.Oracle: Assert.AreEqual("\"MYDB\".\"TROLL\".\",,,\"", name); break; case DatabaseType.PostgreSql: Assert.AreEqual("\"mydb\".public.\"Troll\".\",,,\"", name); break; default: throw new ArgumentOutOfRangeException(nameof(dbType), dbType, null); } }
//[TestCase(DatabaseType.Oracle)] public void CreateParameter(DatabaseType type) { var syntax = ImplementationManager.GetImplementation(type).GetQuerySyntaxHelper(); var declaration = syntax.GetParameterDeclaration("@bob", new DatabaseTypeRequest(typeof(string), 10)); StringAssert.Contains("@bob", declaration); }
public void SyntaxHelperTest_GetRuntimeName(DatabaseType dbType, string expected, string forInput) { ImplementationManager.Load(new DirectoryInfo(TestContext.CurrentContext.TestDirectory)); var syntaxHelper = ImplementationManager.GetImplementation(dbType).GetQuerySyntaxHelper(); Assert.AreEqual(expected, syntaxHelper.GetRuntimeName(forInput)); }
public void Test_ExpectDatabase(DatabaseType type, bool upperCase) { var helper = ImplementationManager.GetImplementation(type).GetServerHelper(); var server = new DiscoveredServer(helper.GetConnectionStringBuilder("loco", "db", "frank", "kangaro")); var db = server.ExpectDatabase("omg"); Assert.AreEqual(upperCase?"OMG":"omg", db.GetRuntimeName()); }
public void ServerHelper_GetCurrentDatabase_WhenNoneSpecified(DatabaseType type) { var helper = ImplementationManager.GetImplementation(type).GetServerHelper(); var builder = helper.GetConnectionStringBuilder(""); var server = new DiscoveredServer(builder); Assert.AreEqual(null, server.Name); Assert.AreEqual(null, server.GetCurrentDatabase()); }
public void Test_IsSupportedType(DatabaseType dbType, string sqlDbType, bool expectedOutcome) { ImplementationManager.Load <OracleImplementation>(); ImplementationManager.Load <MicrosoftSQLImplementation>(); ImplementationManager.Load <MySqlImplementation>(); var tt = ImplementationManager.GetImplementation(dbType).GetQuerySyntaxHelper().TypeTranslater; Assert.AreEqual(expectedOutcome, tt.IsSupportedSQLDBType(sqlDbType), $"Unexpected result for IsSupportedSQLDBType with {dbType}. Input was '{sqlDbType}' expected {expectedOutcome}"); }
/// <summary> /// <para>Creates a new server pointed at the <paramref name="server"/> which should be a server of DBMS <paramref name="databaseType"/></para> /// /// <para><see cref="ImplementationManager"/> must have a loaded implementation for the DBMS type</para> /// </summary> /// <param name="server">The server to connect to e.g. "localhost\sqlexpress"</param> /// <param name="database">The default database to connect into/query (see <see cref="GetCurrentDatabase"/>)</param> /// <param name="databaseType">The DBMS provider type</param> /// <param name="usernameIfAny">Optional username to set in the connection string</param> /// <param name="passwordIfAny">Optional password to set in the connection string</param> /// <exception cref="ImplementationNotFoundException"></exception> public DiscoveredServer(string server, string database, DatabaseType databaseType, string usernameIfAny, string passwordIfAny) { Helper = ImplementationManager.GetImplementation(databaseType).GetServerHelper(); Builder = Helper.GetConnectionStringBuilder(server, database, usernameIfAny, passwordIfAny); if (!string.IsNullOrWhiteSpace(database)) { _currentDatabase = ExpectDatabase(database); } }
public void SyntaxHelperTest_GetRuntimeName_Oracle() { ImplementationManager.Load(new DirectoryInfo(TestContext.CurrentContext.TestDirectory)); var syntaxHelper = ImplementationManager.GetImplementation(DatabaseType.Oracle).GetQuerySyntaxHelper(); Assert.AreEqual("FRANK", syntaxHelper.GetRuntimeName("count(*) as Frank")); Assert.AreEqual("FRANK", syntaxHelper.GetRuntimeName("count(cast(1 as int)) as Frank")); Assert.AreEqual("FRANK", syntaxHelper.GetRuntimeName("count(cast(1 as int)) as \"Frank\"")); Assert.AreEqual("FRANK", syntaxHelper.GetRuntimeName("[mydb].[mytbl].[mycol] as Frank")); Assert.AreEqual("MYCOL", syntaxHelper.GetRuntimeName("[mydb].[mytbl].[mycol]")); }
public void Test_GetFullyQualifiedName_BacktickMySql() { ImplementationManager.Load(new DirectoryInfo(TestContext.CurrentContext.TestDirectory)); var syntaxHelper = ImplementationManager.GetImplementation(DatabaseType.MySql).GetQuerySyntaxHelper(); //when names have backticks the correct response is to double back tick them Assert.AreEqual("`ff``ff`", syntaxHelper.EnsureWrapped("ff`ff")); Assert.AreEqual("`d``b`.`ta``ble`", syntaxHelper.EnsureFullyQualified("d`b", null, "ta`ble")); //runtime name should still be the actual name of the column Assert.AreEqual("ff`ff", syntaxHelper.GetRuntimeName("ff`ff")); }
public void Test_SupportsEmbeddedParameters_DeclarationOrThrow(DatabaseType type) { var syntax = ImplementationManager.GetImplementation(type).GetQuerySyntaxHelper(); if (syntax.SupportsEmbeddedParameters()) { Assert.IsNotEmpty(syntax.GetParameterDeclaration("@bob", new DatabaseTypeRequest(typeof(string), 10))); } else { Assert.Throws <NotSupportedException>(() => syntax.GetParameterDeclaration("@bob", new DatabaseTypeRequest(typeof(string), 10))); } }
public void SyntaxHelperTest_GetRuntimeName_Impossible(DatabaseType t) { ImplementationManager.Load(new DirectoryInfo(TestContext.CurrentContext.TestDirectory)); var syntaxHelper = ImplementationManager.GetImplementation(t).GetQuerySyntaxHelper(); var ex = Assert.Throws <RuntimeNameException>(() => syntaxHelper.GetRuntimeName("count(*)")); StringAssert.Contains("Could not determine runtime name for Sql:'count(*)'. It had brackets and no alias.", ex.Message); Assert.Throws <RuntimeNameException>(() => syntaxHelper.GetRuntimeName("dbo.GetMyCoolThing(\"Magic Fun Times\")")); Assert.IsFalse(syntaxHelper.TryGetRuntimeName("count(*)", out _)); Assert.IsFalse(syntaxHelper.TryGetRuntimeName("dbo.GetMyCoolThing(\"Magic Fun Times\")", out _)); }
public void Test_NameValidation(DatabaseType dbType) { ImplementationManager.Load(new DirectoryInfo(TestContext.CurrentContext.TestDirectory)); var syntaxHelper = ImplementationManager.GetImplementation(dbType).GetQuerySyntaxHelper(); Assert.Throws <RuntimeNameException>(() => syntaxHelper.ValidateDatabaseName(null)); Assert.Throws <RuntimeNameException>(() => syntaxHelper.ValidateDatabaseName(" ")); Assert.Throws <RuntimeNameException>(() => syntaxHelper.ValidateDatabaseName("db.table")); Assert.Throws <RuntimeNameException>(() => syntaxHelper.ValidateDatabaseName("db(lol)")); Assert.Throws <RuntimeNameException>(() => syntaxHelper.ValidateDatabaseName(new string('A', syntaxHelper.MaximumDatabaseLength + 1))); Assert.DoesNotThrow(() => syntaxHelper.ValidateDatabaseName("A")); Assert.DoesNotThrow(() => syntaxHelper.ValidateDatabaseName(new string('A', syntaxHelper.MaximumDatabaseLength))); }
public void SetupDatabases() { foreach (DatabaseType type in Enum.GetValues(typeof(DatabaseType))) { try { var tt = ImplementationManager.GetImplementation(type).GetQuerySyntaxHelper().TypeTranslater; _translaters.Add(type, tt); } catch (ImplementationNotFoundException) { //no implementation for this Type } } }
public void SyntaxHelperTest_SplitLineIntoSelectSQLAndAlias(string line, string expectedSelectSql, string expectedAlias) { ImplementationManager.Load(new DirectoryInfo(TestContext.CurrentContext.TestDirectory)); foreach (DatabaseType t in new [] { DatabaseType.Oracle, DatabaseType.MySql, DatabaseType.MicrosoftSQLServer }) { var syntaxHelper = ImplementationManager.GetImplementation(t).GetQuerySyntaxHelper(); string selectSQL; string alias; Assert.AreEqual(expectedAlias != null, syntaxHelper.SplitLineIntoSelectSQLAndAlias(line, out selectSQL, out alias)); Assert.AreEqual(expectedSelectSql, selectSQL); Assert.AreEqual(expectedAlias, alias); } }
/// <summary> /// Prepares to import the named table as a <see cref="TableInfo"/> /// </summary> /// <param name="repository">Repository to create the <see cref="TableInfo"/>/<see cref="ColumnInfo"/> in</param> /// <param name="importFromServer"></param> /// <param name="importDatabaseName"></param> /// <param name="importTableName"></param> /// <param name="type"></param> /// <param name="username"></param> /// <param name="password"></param> /// <param name="usageContext"></param> /// <param name="importFromSchema"></param> /// <param name="importTableType"></param> public TableInfoImporter(ICatalogueRepository repository, string importFromServer, string importDatabaseName, string importTableName, DatabaseType type, string username = null, string password = null, DataAccessContext usageContext = DataAccessContext.Any, string importFromSchema = null, TableType importTableType = TableType.Table) { var syntax = ImplementationManager.GetImplementation(type).GetQuerySyntaxHelper(); _repository = repository; _importFromServer = importFromServer; _importDatabaseName = importDatabaseName; _importTableName = importTableName; _type = type; _username = string.IsNullOrWhiteSpace(username) ? null : username; _password = string.IsNullOrWhiteSpace(password) ? null : password; _usageContext = usageContext; _importFromSchema = importFromSchema ?? syntax.GetDefaultSchemaIfAny(); _importTableType = importTableType; InitializeBuilder(); }
public void ServerHelper_GetConnectionStringBuilder_NoDatabase(DatabaseType type, bool useWhitespace) { var helper = ImplementationManager.GetImplementation(type).GetServerHelper(); var builder = helper.GetConnectionStringBuilder("loco", useWhitespace? " ":null, "franko", "wacky"); var server = new DiscoveredServer(builder); Assert.AreEqual("loco", server.Name); Assert.IsNull(server.GetCurrentDatabase()); Assert.AreEqual("franko", server.ExplicitUsernameIfAny); Assert.AreEqual("wacky", server.ExplicitPasswordIfAny); server = new DiscoveredServer("loco", useWhitespace?" ":null, type, "frank", "kangaro"); Assert.AreEqual("loco", server.Name); Assert.IsNull(server.GetCurrentDatabase()); }
public void Test_GetAlias(DatabaseType t) { ImplementationManager.Load(new DirectoryInfo(TestContext.CurrentContext.TestDirectory)); var syntaxHelper = ImplementationManager.GetImplementation(t).GetQuerySyntaxHelper(); if (!(syntaxHelper.AliasPrefix.StartsWith(" ") && syntaxHelper.AliasPrefix.EndsWith(" "))) { Assert.Fail("GetAliasConst method on Type " + this.GetType().Name + " returned a value that was not bounded by whitespace ' '. GetAliasConst must start and end with a space e.g. ' AS '"); } var testString = "col " + syntaxHelper.AliasPrefix + " bob"; string selectSQL; string alias; syntaxHelper.SplitLineIntoSelectSQLAndAlias(testString, out selectSQL, out alias); Assert.AreEqual("col", selectSQL); Assert.AreEqual("bob", alias); }
public void ServerHelper_GetConnectionStringBuilder(DatabaseType type) { var helper = ImplementationManager.GetImplementation(type).GetServerHelper(); var builder = helper.GetConnectionStringBuilder("loco", "bob", "franko", "wacky"); var server = new DiscoveredServer(builder); Assert.AreEqual("loco", server.Name); //Oracle does not persist database in connection string if (type == DatabaseType.Oracle) { Assert.IsNull(server.GetCurrentDatabase()); } else { Assert.AreEqual("bob", server.GetCurrentDatabase().GetRuntimeName()); } Assert.AreEqual("franko", server.ExplicitUsernameIfAny); Assert.AreEqual("wacky", server.ExplicitPasswordIfAny); }
/// <summary> /// <para>Creates a new server pointed at the <paramref name="connectionString"/> which should be a server of DBMS <paramref name="databaseType"/></para> /// /// <para><see cref="ImplementationManager"/> must have a loaded implementation for the DBMS type</para> /// </summary> /// <param name="connectionString"></param> /// <param name="databaseType"></param> /// <exception cref="ImplementationNotFoundException"></exception> public DiscoveredServer(string connectionString, DatabaseType databaseType) { Helper = ImplementationManager.GetImplementation(databaseType).GetServerHelper(); Builder = Helper.GetConnectionStringBuilder(connectionString); }
public IQuerySyntaxHelper Create(DatabaseType type) { return(ImplementationManager.GetImplementation(type).GetQuerySyntaxHelper()); }
/// <summary> /// Executes the given SQL against the database + sends GO delimited statements as separate batches /// </summary> /// <param name="sql">Collection of SQL queries which can be separated by the use of "GO" on a line (works for all DBMS)</param> /// <param name="conn"></param> /// <param name="transaction"></param> /// <param name="performanceFigures">Line number the batch started at and the time it took to complete it</param> /// <param name="timeout">Timeout in seconds to run each batch in the <paramref name="sql"/></param> public void ExecuteBatchNonQuery(string sql, DbConnection conn, DbTransaction transaction, out Dictionary <int, Stopwatch> performanceFigures, int timeout = 30) { performanceFigures = new Dictionary <int, Stopwatch>(); StringBuilder sqlBatch = new StringBuilder(); var helper = ImplementationManager.GetImplementation(conn).GetServerHelper(); using (DbCommand cmd = helper.GetCommand(string.Empty, conn, transaction)) { bool hadToOpen = false; if (conn.State != ConnectionState.Open) { conn.Open(); hadToOpen = true; } int lineNumber = 1; sql += "\nGO"; // make sure last batch is executed. try { foreach (string line in sql.Split(new[] { "\n", "\r" }, StringSplitOptions.RemoveEmptyEntries)) { lineNumber++; if (line.Trim().Equals("GO", StringComparison.CurrentCultureIgnoreCase)) { var executeSql = sqlBatch.ToString(); if (string.IsNullOrWhiteSpace(executeSql)) { continue; } if (!performanceFigures.ContainsKey(lineNumber)) { performanceFigures.Add(lineNumber, new Stopwatch()); } performanceFigures[lineNumber].Start(); cmd.CommandText = executeSql; cmd.CommandTimeout = timeout; cmd.ExecuteNonQuery(); performanceFigures[lineNumber].Stop(); sqlBatch.Clear(); } else { sqlBatch.AppendLine(line); } } } finally { if (hadToOpen) { conn.Close(); } } } }
/// <summary> /// Initialises a new blank instance that does nothing. Call <see cref="AddOrUpdateKeyword"/> to adjust the template connection string options. /// </summary> /// <param name="databaseType"></param> public ConnectionStringKeywordAccumulator(DatabaseType databaseType) { DatabaseType = databaseType; _builder = ImplementationManager.GetImplementation(databaseType).GetBuilder(); }