Click here to Skip to main content
13,247,448 members (89,029 online)
Click here to Skip to main content
Articles » Database » Database » General » Downloads

Stats

68.1K views
2.5K downloads
75 bookmarked
Posted 29 May 2006

Database Visualization

, 31 May 2006
This article aims to create a simple tool for visualizing database tables and relations, a database map to refer to.
src
msado15.tlh
msado15.tli
QueryBuilder.dsp
QueryBuilder.dsw
QueryBuilder.exe
QueryBuilder.clw
res
QueryBuilder.ico
logo.bmp
msado15.tlh
msado15.tli
Manifest
default1.bin
ListIcons.bmp
WinGraphViz
WinGraphviz_v1.02.25s.cab
WinGraphviz_v1.02.24.msi
WinGraphviz_v1.02.24.cab
WinGraphviz_v1.02.20.msi
WinGraphviz_v1.02.20.cab
dotguide.pdf
// GenerateCodeDlg.cpp : implementation file
//

#include "stdafx.h"
#include "querybuilder.h"
#include "GenerateCodeDlg.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/////////////////////////////////////////////////////////////////////////////
// CGenerateCodeDlg property page

extern CGlobals GlobalVars;

IMPLEMENT_DYNCREATE(CGenerateCodeDlg, CPropertyPage)

CGenerateCodeDlg::CGenerateCodeDlg() : CPropertyPage(CGenerateCodeDlg::IDD)
{
	//{{AFX_DATA_INIT(CGenerateCodeDlg)
		// NOTE: the ClassWizard will add member initialization here
	//}}AFX_DATA_INIT

	m_strQueryType = "SELECT";
}

CGenerateCodeDlg::~CGenerateCodeDlg()
{
}

void CGenerateCodeDlg::DoDataExchange(CDataExchange* pDX)
{
	CPropertyPage::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CGenerateCodeDlg)
		// NOTE: the ClassWizard will add DDX and DDV calls here
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CGenerateCodeDlg, CPropertyPage)
	//{{AFX_MSG_MAP(CGenerateCodeDlg)
	ON_BN_CLICKED(IDC_BTN_CREATE_QUERY, OnBtnCreateQuery)
	ON_BN_CLICKED(IDC_BTN_CREATE_DIAGRAM, OnBtnCreateDiagram)
	ON_BN_CLICKED(IDC_BTN_GENERATE_CODE, OnBtnGenerateCode)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CGenerateCodeDlg message handlers

BOOL CGenerateCodeDlg::OnSetActive() 
{
	CPropertySheet* psheet = (CPropertySheet*) GetParent();   
	psheet->SetWizardButtons(PSWIZB_FINISH);

	CComboBox *pComboImageType = (CComboBox *)GetDlgItem(IDC_COMBO_IMAGE_FORMAT);
	pComboImageType->SetCurSel(0);

	return CPropertyPage::OnSetActive();
}

void CGenerateCodeDlg::OnBtnCreateQuery() 
{
	CString strSqlQuery(_T(""));

	GetSelectedTablesAndColumns(strSqlQuery);

	GetWhereClause(strSqlQuery);
	
	SaveSqlQuery(strSqlQuery);
}

void CGenerateCodeDlg::OnBtnCreateDiagram() 
{
	CVisualization Visual;

	CString strComments(_T(""));
	GetDlgItemText(IDC_EDIT_COMMENTS,strComments);
	Visual.AddComment(strComments);

	// Get the selected tables and columns and add tables and fields to the graph

		CString strTableAndColumn(_T(""));
		CString strTable(_T(""));
		CString strColumn(_T(""));
		CString strNextTableName(_T(""));

		CStringArray strArrayFields;

		int nColumnCount = GlobalVars.g_strSelectedColumnsList.GetSize();

		for(int nIndex = 0; nIndex < nColumnCount; nIndex++)
		{
			// Eg. Po_Product.Source_FK . Delimiter is . (Dot)
			strTableAndColumn = GlobalVars.g_strSelectedColumnsList.GetAt(nIndex);

			int nDelimiter = strTableAndColumn.ReverseFind('.');
			strTable = strTableAndColumn.Left(nDelimiter);

			strColumn = strTableAndColumn.Mid(nDelimiter+1);
			strArrayFields.Add(strColumn);

			int nNextIndex = nIndex + 1;
			
			if(nNextIndex<nColumnCount)
			{
				strTableAndColumn = GlobalVars.g_strSelectedColumnsList.GetAt(nNextIndex);
				nDelimiter = strTableAndColumn.ReverseFind('.');
				strNextTableName = strTableAndColumn.Left(nDelimiter);
			}
 			else
				strNextTableName.Empty();

			if(strTable != strNextTableName)
				Visual.AddTable(strTable,strArrayFields);
		}		

	// Analyze the where clause to determine the links and add the links

		int nRelationCount = GlobalVars.g_strSelectedRelationsList.GetSize();

		if(nRelationCount<=0) return;

		int nDelimiter = -1;

		BOOL bShowLabels = FALSE;

		CButton *pCheckShowLabels = (CButton *)GetDlgItem(IDC_CHECK_SHOW_LABELS);
		bShowLabels = (pCheckShowLabels->GetCheck() == BST_CHECKED) ? TRUE : FALSE;

		CString strRelation, strTableOneColOne, strTableTwoColTwo;
		CString strTableOne, strTableTwo, strColumnOne, strColumnTwo, strLink;

		for(int nRelationIndex = 0; nRelationIndex < nRelationCount; nRelationIndex++)
		{
			// Format of the string is Table1::Column1->Table2::Column2
			strRelation = GlobalVars.g_strSelectedRelationsList.GetAt(nRelationIndex);

			nDelimiter = strRelation.Find('>',0);

			strTableOneColOne = strRelation.Left(nDelimiter);
			strTableTwoColTwo = strRelation.Mid(nDelimiter + 1);

			strTableOneColOne.TrimRight();
			strTableTwoColTwo.TrimLeft();
			
			int nDelimiter	= strTableOneColOne.Find('.',0);

			strTableOne		= strTableOneColOne.Left(nDelimiter);
			strColumnOne	= strTableOneColOne.Mid(nDelimiter + 1);

			nDelimiter	= strTableTwoColTwo.Find('.',0);

			strTableTwo		= strTableTwoColTwo.Left(nDelimiter);
			strColumnTwo	= strTableTwoColTwo.Mid(nDelimiter + 1);

			strLink = strColumnOne + "=" + strColumnTwo;

			if(bShowLabels==TRUE)
				Visual.AddLink(strTableOne,strTableTwo,strLink);
			else
				Visual.AddLink(strTableOne,strTableTwo,"");
		}
		

	// Save the resultant output string to a file

		CString strImageType(_T(""));

		BOOL bOpenFile = FALSE;
		CButton *pCheckShowQuery = (CButton *)GetDlgItem(IDC_CHECK_SHOW_DIAGRAM);
		bOpenFile = (pCheckShowQuery->GetCheck() == BST_CHECKED) ? TRUE : FALSE;

		CComboBox *pComboImageType = (CComboBox *)GetDlgItem(IDC_COMBO_IMAGE_FORMAT);
		pComboImageType->GetLBText(pComboImageType->GetCurSel(),strImageType);

		if(strImageType=="JPG")
			Visual.SaveToFile(JPG,bOpenFile);
		else if(strImageType=="GIF")
			Visual.SaveToFile(GIF,bOpenFile);
		else if(strImageType=="SVG")
			Visual.SaveToFile(SVG,bOpenFile);
		else if(strImageType=="PNG")
			Visual.SaveToFile(PNG,bOpenFile);
		else if(strImageType=="EMF")
			Visual.SaveToFile(EMF,bOpenFile);
}

void CGenerateCodeDlg::OnBtnGenerateCode() 
{
	// Determine the programming language and the code to be generated ( client / server )
	// Generate the actual code
}

/***** Generic Functions *****/
CString CGenerateCodeDlg::GetUserComments()
{
	CString strComments(_T(""));
	CString strTemp(_T(""));

	GetDlgItemText(IDC_EDIT_COMMENTS,strTemp);
	strComments.Format("/* Comments : %s */ \n\n",strTemp);

	return strComments;
}

void CGenerateCodeDlg::GetQueryType()
{
	m_strQueryType = "SELECT";
	m_strQueryType.TrimRight();
	m_strQueryType += "\n\n\t";
}

void CGenerateCodeDlg::GetSelectedTablesAndColumns(CString &strSqlQuery)
{
	CString strTableAndColumn(_T(""));
	CString strTableName(_T(""));
	CString strTemp(_T(""));
	CString strPrevTableName(_T(""));

	CString strTableList(_T(""));
	CString strFrom(_T(""));

	int nColumnCount = GlobalVars.g_strSelectedColumnsList.GetSize();

	for(int nIndex = 0; nIndex < nColumnCount; nIndex++)
	{
		// Delimiter is . (Dot)
		strTableAndColumn = GlobalVars.g_strSelectedColumnsList.GetAt(nIndex);

		int nDelimiter = strTableAndColumn.ReverseFind('.');
		strTableName = strTableAndColumn.Left(nDelimiter);

		if( strPrevTableName.IsEmpty() )
		{
			strPrevTableName = strTableName;
			strFrom = strTableName;
			strTableList += strFrom;
		}

		if(strTableList.Find(strTableName,0)==-1)
		{
			strTemp.Format(", %s",strTableName);
			strFrom += strTemp;
			strTableList += strTemp;
		}

		// If the query statement is empty, add the query type
		if(strSqlQuery.IsEmpty())
			strSqlQuery.Format("%s %s",m_strQueryType,strTableAndColumn);
		else
		{
			strTemp.Format(", %s",strTableAndColumn);
			strSqlQuery += strTemp;
		}
	}

	strSqlQuery += "\n\n";
	strSqlQuery += "FROM";
	strSqlQuery += "\n\n\t";
	strSqlQuery += strFrom;
	strSqlQuery += "\n\n";
}

void CGenerateCodeDlg::GetWhereClause(CString &strSqlQuery)
{
	CString strRelation(_T(""));
	
	CString strTableOneColOne(_T(""));
	CString strTableTwoColTwo(_T(""));

	CString strTemp(_T(""));

	strSqlQuery += "WHERE \n\n\t";
	
	int nColumnCount = GlobalVars.g_strSelectedRelationsList.GetSize();

	if(nColumnCount<=0) return;

	int nDelimiter = -1;

	for(int nIndex = 0; nIndex < nColumnCount; nIndex++)
	{
		// Format of the string is Table1::Column1->Table2::Column2
		strRelation = GlobalVars.g_strSelectedRelationsList.GetAt(nIndex);

		nDelimiter = strRelation.Find('>',0);

		strTableOneColOne = strRelation.Left(nDelimiter);
		strTableTwoColTwo = strRelation.Mid(nDelimiter + 1);

		strTableOneColOne.TrimRight();
		strTableTwoColTwo.TrimLeft();

		// If it is the first time, the "AND" clause is not needed
		if(nIndex==0)
			strTemp.Format("%s = %s",strTableOneColOne,strTableTwoColTwo);
		else
			strTemp.Format("AND \n\n\t%s = %s",strTableOneColOne,strTableTwoColTwo);

		if(nIndex<nColumnCount-1)
		{
			 strTemp += "\n\n";
		}
		else // Last statement. So, lets end the Sql query
		{
			strTemp += ";";
		}

		strSqlQuery += strTemp;
	}
}

void CGenerateCodeDlg::SaveSqlQuery(CString strSqlQuery)
{
	CFileDialog SaveDialog(FALSE,NULL,NULL,OFN_OVERWRITEPROMPT,"Sql Files (*.sql)|*.sql||");

	int iRet = SaveDialog.DoModal();

    CString strFullPath(_T(""));
    strFullPath = SaveDialog.GetPathName();

	CString strFileName(_T(""));
	strFileName = SaveDialog.GetFileName();

	// If the extension has not been entered, append it
	if(strFileName.Find('.') <= 0)
		strFullPath += ".sql";

	if(iRet == IDOK)
	{
		CStdioFile QueryFile;
		BOOL bRetVal = QueryFile.Open(strFullPath,CFile::modeCreate | CFile::modeReadWrite);

		if(bRetVal==TRUE)
		{
			QueryFile.WriteString(GetUserComments());
			QueryFile.WriteString(strSqlQuery);
			QueryFile.Close();

			CButton *pCheckShowQuery = (CButton *)GetDlgItem(IDC_CHECK_SHOW_QUERY);

			if( pCheckShowQuery->GetCheck() == BST_CHECKED )
			{
				CString strCommand(_T(""));
				strCommand.Format("Notepad %s",strFullPath);

				WinExec(strCommand,SW_SHOW);
			}
		}
		else
		{
			MessageBox("Unable to save to file.","Warning",MB_OK | MB_ICONSTOP);
		}
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

VGirish
Founder
India India
No Biography provided

You may also be interested in...

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171114.1 | Last Updated 31 May 2006
Article Copyright 2006 by VGirish
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid