Every once in a while we're treated with the rare opportunity to get a glimpse into the system that never should have been. The system whose architecture diagram alone would be a grossly inappropriate April Fool's Day joke. The system whose code quality is actually lower than what a hundred lemurs randomly pounding on keyboards would produce. The system whose stench even surpasses that produced by a hundred lemurs together in a room. And today, dear readers, we shall once again experience one of these systems thanks to Mike O.

Most systems require one, or at most, a handful of databases for its backend. But not this one. Like the rings on a tree, the number of databases this system requires to run grows by the day. Each day, a new database was dynamically generated to process and finalize the manufacturer's work schedule. A typical installation of this system is measured in the hundreds of databases ...

Of course, it's not as if the old databases aren't used. They certainly are. Since there are so many databases, the connection strings for each database is stored in ... you guessed it ... another database! In case your wondering the inner workings behind this wretched abuser of database technology, here is the (VB6) code powering the daily database creation method ...

Private Function CreateDB(ReleaseDate As Date) As Boolean
    On Error GoTo db_error
    Dim strConnectionString As String
    Dim strDatabaseName As String
    Dim strSQL As String
    Dim rsConnection As ADODB.Recordset
    m_progressForm.lblStatus.Caption = "Creating Database..."
    ReleaseDate = Format(ReleaseDate, "mm/dd/yy")
    strDatabaseName = Right$(Format$(ReleaseDate, "mm/dd/yy"), 2) _
& Left$(Format$(ReleaseDate, "mm/dd/yy"), 2) & _ Mid$(Format$(ReleaseDate, "mm/dd/yy"), 4, 2) 'check to see if database already exists strSQL = "SELECT dbConnectString FROM Databases WHERE dbDate = '" _
& ReleaseDate & "'" Set rsConnection = m_cnnProductMasterDB.Execute(strSQL) If rsConnection.BOF And rsConnection.EOF Then 'database not yet created strConnectionString = "" Else 'database already exists strConnectionString = rsConnection.Collect("dbConnectString") End If rsConnection.Close Set rsConnection = Nothing Dim objCommand As ADODB.Command Dim objRS As ADODB.Recordset CreateDB = True If GetProdDBConnection(strConnectionString) Then m_progressForm.lblStatus.Caption = "Synchronizing Database Information..." DoEvents If Not ReSyncDatabase(m_cnnProductMasterDB, m_cnnDailyDB, True) Then _
CreateDB = False Else 'create database Dim strProcedure As String Set objCommand = New ADODB.Command strSQL = "SELECT " & _ "(SELECT Value FROM db WHERE Parameter = 'PDBServer') AS DBServer, " & _ "(SELECT Value FROM db WHERE Parameter = 'PDBProvider') AS DBProvider, " & _ "(SELECT Value FROM db WHERE Parameter = 'PDBPassword') AS DBPassword, " & _ "(SELECT Value FROM db WHERE Parameter = 'PDBUserID') AS DBUserID" Set objRS = m_cnnProductMasterDB.Execute(strSQL) If Not IsNull(objRS(0)) Then 'setup provider Set m_cnnDailyDB = New ADODB.Connection If Not IsNull(objRS(1)) Then m_cnnDailyDB.Provider = objRS(1) Else m_cnnDailyDB.ConnectionString = m_cnnProductMasterDB.ConnectionString End If m_cnnDailyDB.Properties("Data Source") = objRS(0) m_cnnDailyDB.Properties("Initial Catalog") = "" If Not IsNull(objRS(3)) Then m_cnnDailyDB.Properties("User ID") = objRS(3) If Not IsNull(objRS(2)) Then m_cnnDailyDB.Properties("Password") = objRS(2) m_cnnDailyDB.Open objCommand.ActiveConnection = m_cnnDailyDB Else 'local server objCommand.ActiveConnection = m_cnnProductMasterDB End If strProcedure = "CREATE DATABASE [" & strDatabaseName & "]" objCommand.CommandText = strProcedure objCommand.CommandType = adCmdText objCommand.Execute 'create connection string for the new production database Dim InitialCatalogBegin As Long Dim InitialCatalogEnd As Long Dim UserIDBegin As Long Dim UserIDEnd As Long Dim strInitialConnectionString As String 'replace master catalog name with production db catalog name strInitialConnectionString = objCommand.ActiveConnection.ConnectionString InitialCatalogBegin = InStr(strInitialConnectionString, "Initial Catalog=") InitialCatalogEnd = InStr(InitialCatalogBegin, strInitialConnectionString, ";") strConnectionString = Left(strInitialConnectionString, InitialCatalogBegin - 1) & "Initial Catalog=" strConnectionString = strConnectionString & strDatabaseName & _
Mid(strInitialConnectionString, InitialCatalogEnd) If GetProdDBConnection(strConnectionString) Then If GetMasterDatabaseInfo() Then strSQL = "INSERT INTO [Databases] ([dbConnectString], [dbType], [dbDate], [dbName])" strSQL = strSQL & " VALUES ('" & strConnectionString & "', 0, '" strSQL = strSQL & ReleaseDate & "', '" & strDatabaseName & "')" m_cnnProductMasterDB.Execute strSQL, , adCmdText Else 'drop database m_cnnDailyDB.Close MsgBox "An Error occured while creating the database!!
The database will now be dropped from the server!!"
, vbExclamation, App.Title m_cnnDailyDB.Open strInitialConnectionString strProcedure = "DROP DATABASE [" & strDatabaseName & "]" objCommand.ActiveConnection = m_cnnDailyDB objCommand.CommandText = strProcedure objCommand.CommandType = adCmdText 'wait couple of seconds for connection to close Dim fTimer As Single fTimer = Timer While Timer - fTimer < 5: DoEvents: Wend objCommand.Execute Set objCommand = Nothing CreateDB = False End If Set objCommand = Nothing Else 'created but couldn't connect End If End If If Not objRS Is Nothing Then If objRS.State = adStateOpen Then objRS.Close Set objRS = Nothing End If Set objCommand = Nothing Err = 0 Exit Function db_error: 'SNIP CreateDB = False Exit Function Resume End Function

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!