selecttables.cpp

快速SQL交互工具

// SelectTables.cpp : implementation file
//

#include "stdafx.h"
#include "QryTool.h"
#include "SelectTables.h"
#include "MainFrm.h"

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

LPCTSTR g_szSection = _T("Tables");
LPCTSTR g_szViewsEntry = _T("Views");
LPCTSTR g_szSystemTablesEntry = _T("System_Tables");
LPCTSTR g_szTextOnlyEntry = _T("Text_Only");

extern LPCTSTR g_szOracle;
extern LPCTSTR g_szVisualFoxPro;
/////////////////////////////////////////////////////////////////////////////
// CSelectTables dialog

CSelectTables::CSelectTables(CWnd* pParent /*=NULL*/)
	: CDialog(CSelectTables::IDD, pParent)
{
	//{{AFX_DATA_INIT(CSelectTables)
	m_bTextOnly = FALSE;
	m_strSearch = _T("");
	//}}AFX_DATA_INIT

	m_bSort = true;
}

void CSelectTables::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CSelectTables)
	DDX_Control(pDX, IDC_TEXT_ONLY, m_buttonTextOnly);
	DDX_Control(pDX, IDC_SYSTEM_TABLES, m_buttonSystemTables);
	DDX_Control(pDX, IDC_VIEWS, m_buttonViews);
	DDX_Control(pDX, IDC_LIST1, m_ctrlList);
	DDX_Check(pDX, IDC_TEXT_ONLY, m_bTextOnly);
	DDX_Text(pDX, IDC_SEARCH, m_strSearch);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CSelectTables, CDialog)
	//{{AFX_MSG_MAP(CSelectTables)
	ON_NOTIFY(NM_DBLCLK, IDC_LIST1, OnDblclkList1)
	ON_NOTIFY(LVN_COLUMNCLICK, IDC_LIST1, OnColumnclickList1)
	ON_BN_CLICKED(IDC_SYSTEM_TABLES, OnSystemTables)
	ON_BN_CLICKED(IDC_VIEWS, OnViews)
	ON_EN_CHANGE(IDC_SEARCH, OnChangeSearch)
	ON_BN_CLICKED(IDC_TEXT_ONLY, OnTextOnly)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CSelectTables message handlers

BOOL CSelectTables::OnInitDialog() 
{
	CWaitCursor wait;

	CMainFrame* pFrame = (CMainFrame*)AfxGetMainWnd();
	ASSERT(pFrame);
	m_pChildFrame = (CChildFrame*)pFrame->MDIGetActive();
	m_pChildFrame->m_wndStatusBar.SetPaneText(0, _T("Please wait..."));

	CDialog::OnInitDialog();

	LV_COLUMN lvcColumn;
	lvcColumn.mask = LVCF_FMT | LVCF_SUBITEM | LVCF_TEXT | LVCF_WIDTH;
	lvcColumn.fmt = LVCFMT_LEFT;

	CRect rect;
	m_ctrlList.GetClientRect(&rect);
	CSize size = rect.Size();
	int nWidth = size.cx/3;
	lvcColumn.cx = nWidth;
	lvcColumn.pszText = _T("Name");
	lvcColumn.iSubItem = 0;
	m_ctrlList.InsertColumn(0, &lvcColumn);

	lvcColumn.cx = nWidth;
	lvcColumn.pszText = _T("Type");
	lvcColumn.iSubItem = 1;
	m_ctrlList.InsertColumn(1, &lvcColumn);

	lvcColumn.cx = nWidth;
	lvcColumn.pszText = _T("Owner");
	lvcColumn.iSubItem = 2;
	m_ctrlList.InsertColumn(2, &lvcColumn);

	CWinApp* pApp = AfxGetApp();
	BOOL bCheck = pApp->GetProfileInt(g_szSection, g_szSystemTablesEntry, 0);
	m_buttonSystemTables.SetCheck(bCheck);

	bCheck = pApp->GetProfileInt(g_szSection, g_szViewsEntry, 0);
	m_buttonViews.SetCheck(bCheck);
	
	bCheck = pApp->GetProfileInt(g_szSection, g_szTextOnlyEntry, 0);
	m_buttonTextOnly.SetCheck(bCheck);
	m_buttonTextOnly.EnableWindow(m_buttonViews.GetCheck());

	if(!PopulateList())
		SendMessage(WM_CLOSE);
	else
	{
		OnTextOnly();

		m_ctrlList.SetFullRowSel(TRUE);
		if(m_pChildFrame->m_bIsMultiSetSupported)
			m_ctrlList.ModifyStyle(LVS_SINGLESEL, 0);
	}
	
	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}

BOOL CSelectTables::PopulateList()
{
	BOOL bRet = FALSE;

	try
	{
		m_ctrlList.DeleteAllItems();

		char lpszType[64];
		strcpy(lpszType, "'TABLE'");
		if(m_buttonViews.GetCheck())
			strcat(lpszType, ",'VIEW'");
		if(m_buttonSystemTables.GetCheck())
			strcat(lpszType, ",'SYSTEM TABLE'");
		
		CTables set(&m_pChildFrame->m_database);
		bRet = set.Open(NULL, NULL, NULL, lpszType, CRecordset::forwardOnly);
		if(bRet)
		{
			if(!set.IsBOF())
			{
				CString sBuff;
				int nPos = -1;
				int nIndex = 0;
				while(!set.IsEOF())
				{
					if(m_pChildFrame->m_bIsJetDriver)
						sBuff += "[" + set.m_strTableName + "]|";
					else
					{
						nPos = set.m_strTableName.Find(' ');
						if(nPos != -1) // MS SQL Server scenario
							sBuff += "[" + set.m_strTableName + "]|";
						else // All other
							sBuff += set.m_strTableName + "|";
					}

					sBuff += set.m_strTableType + "|";
					sBuff += set.m_strTableOwner + "|";
					
					CHelpers::Insert(&m_ctrlList, sBuff, -1, nIndex++);

					set.MoveNext();			
				}
				
				m_ctrlList.SetItemState(0, LVIS_SELECTED | LVIS_FOCUSED,
							LVIS_SELECTED | LVIS_FOCUSED);
			}
		}
	}
	catch(CDBException* e)
	{
	   if(e)
	   {
			CString sMsg;
			if(!e->m_strError.IsEmpty())
				sMsg.Format(_T("%s%s"), (LPCTSTR)e->m_strError, (LPCTSTR)e->m_strStateNativeOrigin);
			else
				sMsg = e->m_strStateNativeOrigin;

			e->Delete();

			AfxMessageBox(sMsg);
	   }
	}
	catch(CMemoryException* e)
	{
		AfxMessageBox(_T("Out-of-memory."));
		if(e)
			e->Delete();
	}
	catch(LPCTSTR e)
	{
		AfxMessageBox(e);
	}
	catch(...)
	{
		AfxMessageBox(_T("Errors occurred."));
	}

	if(!m_ctrlList.GetItemCount())
	{
		GetDlgItem(IDOK)->EnableWindow(FALSE);
		GetDlgItem(IDCANCEL)->SetWindowText(_T("&Close"));
	}
	else
	{
		GetDlgItem(IDOK)->EnableWindow(TRUE);
		GetDlgItem(IDCANCEL)->SetWindowText(_T("Cancel"));
	}

	return bRet;
}

void CSelectTables::OnOK() 
{
	CWaitCursor wait;

	m_pChildFrame->m_wndStatusBar.SetPaneText(0, _T("Please wait..."));

	UpdateData();

	bool bRet = true;

	int nItem = m_ctrlList.GetNextItem(-1, LVNI_ALL | LVNI_SELECTED);
	if(nItem == -1)
		nItem = m_ctrlList.GetNextItem(-1, LVNI_ALL | LVNI_FOCUSED);
	m_strSQL = "\n\n";
	m_strType = m_ctrlList.GetItemText(nItem, 1);
	CString sTableName = m_ctrlList.GetItemText(nItem, 0);
	if(m_bTextOnly && !m_strType.CompareNoCase(_T("VIEW")))
	{
		if(!m_pChildFrame->m_strDBMS.CompareNoCase(g_szOracle))
		{
			m_strSQL += "select TEXT from ALL_SOURCE where type = 'VIEW' and NAME = '";
			m_strSQL += sTableName + "'\n\n" ;
		}
		else if(m_pChildFrame->m_bIsTSQLSupported)
		{
			sTableName = m_ctrlList.GetItemText(nItem, 0);
			if(m_pChildFrame->m_bIsTSQLSupported)
				m_strSQL += "sp_helptext " + sTableName + "\n\n";
		}
		else if(m_pChildFrame->m_bIsJetDriver)
		{
			if(sTableName.Find('[') != -1)
			{
				int nLength = sTableName.GetLength(); 
				sTableName = sTableName.Mid(1, nLength-2);
			}
			m_strSQL += sTableName + "\n\n";
		}
		else
			m_strSQL += sTableName + " // View text not supported\n\n";

		m_strObjName = sTableName;
	}
	else
	{
		CString sColumns, sSQL;
		while(nItem  != -1 )
		{
			sTableName = m_ctrlList.GetItemText(nItem, 0);
			bRet = GetColumnList(sTableName, sColumns);
			if(!bRet)
				break;
			else
			{
				sSQL = "SELECT " + sColumns + " FROM ";
				sSQL += sTableName;
				if(!m_pChildFrame->m_strDBMS.CompareNoCase(g_szVisualFoxPro))
					sSQL += ";";
				sSQL += "\n\n";
				m_strSQL += sSQL;
				nItem = m_ctrlList.GetNextItem(nItem, LVNI_ALL | LVNI_SELECTED);
			}
		}
	}

	if(bRet)
	{
		m_strSQL = m_strSQL.Left(m_strSQL.GetLength()-2);
			
		CWinApp* pApp = AfxGetApp();
		pApp->WriteProfileInt(g_szSection, g_szViewsEntry, m_buttonViews.GetCheck());
		pApp->WriteProfileInt(g_szSection, g_szSystemTablesEntry,
			m_buttonSystemTables.GetCheck());
		pApp->WriteProfileInt(g_szSection, g_szTextOnlyEntry, m_buttonTextOnly.GetCheck());
		
		CDialog::OnOK();
	}

	m_pChildFrame->m_wndStatusBar.SetPaneText(0, _T("")); 
}

void CSelectTables::OnDblclkList1(NMHDR* pNMHDR, LRESULT* pResult) 
{
	CWaitCursor wait;

	if(GetDlgItem(IDOK)->IsWindowEnabled())
		OnOK();

	*pResult = 0; // Framework stuff
}

void CSelectTables::OnColumnclickList1(NMHDR* pNMHDR, LRESULT* pResult) 
{
	CWaitCursor wait;

	NM_LISTVIEW* pNMListView = (NM_LISTVIEW*)pNMHDR;

	CSortColumn sort(&m_ctrlList, pNMListView->iSubItem, false);
	sort.Sort(m_bSort = !m_bSort);
	
	*pResult = 0; // Framework stuff
}

void CSelectTables::OnSystemTables() 
{
	CWaitCursor wait;

	UpdateData();

	m_ctrlList.SetRedraw(FALSE);
	PopulateList();
	m_ctrlList.SetRedraw();
}

void CSelectTables::OnViews() 
{
	OnSystemTables();
	m_buttonTextOnly.EnableWindow(m_buttonViews.GetCheck());
	if(m_buttonTextOnly.IsWindowEnabled())
		OnTextOnly();
	else
	{
		if(m_pChildFrame->m_bIsMultiSetSupported)
			m_ctrlList.ModifyStyle(LVS_SINGLESEL, 0);
	}
}

bool CSelectTables::GetColumnList(const CString& sTN, CString& sColumns)
{
	CString sTable = sTN;

	bool bRet = true;

	sColumns.Empty();

	try
	{
		if(sTable.Find('[') != -1)
		{
			int nLength = sTable.GetLength(); 
			sTable = sTable.Mid(1, nLength-2);
		}
		
		if(sTable.GetLength() > 8 && m_pChildFrame->IsTextDataSource())
			sColumns = GetColumnListForTextDataSrc(sTable);
		else
		{
			CColumns set(&m_pChildFrame->m_database);
#ifdef _UNICODE
			USES_CONVERSION;
			if(set.Open(NULL, NULL, W2CA((LPCTSTR)sTable), NULL))
#else
			if(set.Open(NULL, NULL, sTable, NULL))
#endif
			{
				if(!set.IsBOF())
				{
					CString sBuff;
					int nPos = -1;
					while(!set.IsEOF())
					{
						if(m_pChildFrame->m_bIsJetDriver)
						{
							nPos = set.m_strColumnName.Find('.');
							if(nPos == -1)
								sColumns +=	"[" + set.m_strColumnName + "], ";
							else
							{
								sColumns += "[" + set.m_strColumnName.Left(nPos) + "].";
								sColumns +=	"[" + set.m_strColumnName.Mid(nPos+1) + "], ";
							}
						}
						else
						{
							nPos = set.m_strColumnName.Find(' ');
							if(nPos != -1) // MS SQL Server scenario
								sColumns +=	"[" + set.m_strColumnName + "], ";
							else // All other
								sColumns +=	set.m_strColumnName + ", ";
						}

						set.MoveNext();	
					}

					sColumns = sColumns.Left(sColumns.GetLength()-2);					
				}
			}
		}
	}
	catch(CDBException* e)
	{
		bRet = false;
		
		if(e)
		{
			CString sMsg;
			if(!e->m_strError.IsEmpty())
				sMsg.Format(_T("%s%s"), (LPCTSTR)e->m_strError, (LPCTSTR)e->m_strStateNativeOrigin);
			else
				sMsg = e->m_strStateNativeOrigin;
			
			e->Delete();
			
			AfxMessageBox(sMsg);
		}
	}
	catch(CMemoryException* e)
	{
		AfxMessageBox(_T("Out-of-memory."));
		
		if(e)
			e->Delete();
	}
	catch(LPCTSTR e)
	{
		AfxMessageBox(e);
	}
	
	return bRet;
}

void CSelectTables::OnChangeSearch() 
{
	UpdateData();

	LVFINDINFO findInfo;
	findInfo.flags = LVFI_PARTIAL | LVFI_STRING;
	findInfo.psz = (LPCTSTR)m_strSearch;
	int nItem = m_ctrlList.FindItem(&findInfo);
	m_ctrlList.ModifyStyle(0, LVS_SINGLESEL);
	m_ctrlList.SetItemState(nItem, LVIS_SELECTED, LVIS_SELECTED);
	if(m_pChildFrame->m_bIsMultiSetSupported)
		m_ctrlList.ModifyStyle(LVS_SINGLESEL, 0);
	m_ctrlList.EnsureVisible(nItem, TRUE);
}

void CSelectTables::OnTextOnly() 
{
	if(m_buttonTextOnly.GetCheck())
		m_ctrlList.ModifyStyle(0, LVS_SINGLESEL);
	else
	{
		if(m_pChildFrame->m_bIsMultiSetSupported)
			m_ctrlList.ModifyStyle(LVS_SINGLESEL, 0);
	}
	
	m_ctrlList.SetFocus();
}

CString CSelectTables::GetColumnListForTextDataSrc(const CString& sTableName)
{
	CWaitCursor wait;

	CString sColumns;
	CRecordsetEx set(&m_pChildFrame->m_database);
	if(set.ExecDirect(CString("SELECT * FROM " + sTableName + " WHERE 1=2")))
	{
		CODBCFieldInfo fieldInfo;
		int nCols = set.GetODBCFieldCount();
		for(int n = 0; n < nCols; n++)
		{
			set.GetODBCFieldInfo(n, fieldInfo);
			sColumns += "[" + fieldInfo.m_strName + "], ";
		}

		sColumns = sColumns.Left(sColumns.GetLength()-2);
	}

	return sColumns;
}