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..."
    DoEvents
    
    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!