sqldefine.cpp

ADO查看数据库工具的原码

// SqlDefine.cpp: implementation of the CSqlDefine class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "SqlDefine.h"

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

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CSqlDefine::CSqlDefine()
{
	m_SqlDefine = NULL;
	m_nCurOption = 0;
	m_nMaxOption = 0;
	m_SqlLike = 0;
	m_strSqlSel.Empty();
	m_strSqlDel.Empty();
	m_strSqlWhere.Empty();
	m_strSqlGroupby.Empty();
	m_strSqlOrderby.Empty();
	m_strSqlHaving.Empty();
	m_strTableName.Empty();
}

CSqlDefine::~CSqlDefine()
{
	if(m_SqlDefine != NULL)
	{
		delete []m_SqlDefine;
	}
}

int CSqlDefine::DefNewBuf(int nMaxLength)
{
	m_nCurOption = 0;
	m_strSqlSel.Empty();
	m_strSqlDel.Empty();
	m_strSqlWhere.Empty();
	m_strSqlGroupby.Empty();
	m_strSqlOrderby.Empty();
	m_strSqlHaving.Empty();
	m_strTableName.Empty();
	if(m_SqlDefine != NULL)
	{
		delete []m_SqlDefine;
	}
	try
	{
		m_SqlDefine = new TagSqlDef[nMaxLength];
	}
	catch(CMemoryException *pEx)
	{
		char pszmes[100];
		TRACE1("内存分配失败: %s\n",pEx->GetErrorMessage(pszmes,100));
		return -1;
	}
	m_nMaxOption = nMaxLength;
	return 0;
}

int CSqlDefine::AddSqlOption(char *pStr, int nType, bool bLock)
{
	if(m_SqlDefine == NULL)
		return -1;
	if(m_nCurOption > m_nMaxOption)
		return -2;
	if( strlen(pStr) > 64)
		return -3;
	strcpy(m_SqlDefine[m_nCurOption].cStr,pStr);
	m_SqlDefine[m_nCurOption].bLock = bLock;
	m_SqlDefine[m_nCurOption].nType = nType;
	m_SqlDefine[m_nCurOption].nGroup = 0;
	m_nCurOption++;
	return 0;
}

CString CSqlDefine::CreateGroupSql(int nGroup)
{
	CString strSql;
	CString strLogic = ") AND (";
	strSql = "(";
	bool bstart=true;
	for(int i=0;i<m_nCurOption;i++)
	{
		if(m_SqlDefine[i].nType == SQL_WHERE && m_SqlDefine[i].nGroup ==nGroup)
		{	
			if(bstart)
			{
				strSql += CreatSqlOption(i);
				bstart = false;
				if(m_SqlDefine[i].nSubType > OPER_TYPE_OR)
				{
					strLogic = ") OR (";
				}
			}
			else
			{
				strSql += strLogic;
				strSql += CreatSqlOption(i);
			}
		}			
	}
	strSql += ")";
	return strSql;
}

int CSqlDefine::AddWhereOption(char *pStrName, char* pStrValue, int nOper, int nGroupID,int bres,bool bLock)
{
	if(m_SqlDefine == NULL)
		return -1;
	if(m_nCurOption > m_nMaxOption)
		return -2;
	if( strlen(pStrName) > 64 ||strlen(pStrValue) > 64 )
		return -3;
	strcpy(m_SqlDefine[m_nCurOption].cStr,pStrName);
	strcpy(m_SqlDefine[m_nCurOption].cStrVal,pStrValue);
	m_SqlDefine[m_nCurOption].bLock = bLock;
	m_SqlDefine[m_nCurOption].nType = SQL_WHERE;
	m_SqlDefine[m_nCurOption].nSubType = nOper;
	m_SqlDefine[m_nCurOption].nGroup = nGroupID;
	m_SqlDefine[m_nCurOption].bres = bres;
	m_nCurOption++;
	return 0;
}

CString CSqlDefine::CreateSelSql()
{
	CString strSelSql;
	strSelSql = "SELECT ";
	bool bstart = true;
	for(int i=0;i<m_nCurOption;i++)
	{
		if(m_SqlDefine[i].nType == SQL_NORMAL_COLS && m_SqlDefine[i].nGroup ==0)
		{
			if(bstart)
			{
				strSelSql += m_SqlDefine[i].cStr;
				bstart = false;
			}
			else
			{
				strSelSql += ", ";
				strSelSql += m_SqlDefine[i].cStr;
			}
		}			
	}
	for(i=0;i<m_nCurOption;i++)
	{
		if(m_SqlDefine[i].nType == SQL_COUNT_COLS && m_SqlDefine[i].nGroup ==0)
		{
			if(bstart)
			{
				strSelSql += " COUNT(";
				strSelSql += m_SqlDefine[i].cStr;
				strSelSql += ") ";
				bstart = false;
			}
			else
			{
				strSelSql += ",COUNT( ";
				strSelSql += m_SqlDefine[i].cStr;
				strSelSql += ") ";
			}
		}		
	}
	if(bstart)
		strSelSql+= " * ";
	bstart = true;
	for(i=0;i<m_nCurOption;i++)
	{
		if(m_SqlDefine[i].nType == SQL_TABLES && m_SqlDefine[i].nGroup ==0 )
		{	
			strSelSql += " FROM ";
			strSelSql += m_SqlDefine[i].cStr;
			m_strTableName = m_SqlDefine[i].cStr;
			bstart = false;
			break;
		}			
	}
	if(bstart)
		return "";
	m_strSqlWhere   = CreateSqlWhere();
	m_strSqlGroupby = CreateSqlGroupby();
	m_strSqlHaving  = CreateSqlHaving();
	m_strSqlOrderby = CreateSqlOrderby();
	strSelSql += m_strSqlWhere;
	strSelSql += m_strSqlGroupby;
	strSelSql += m_strSqlHaving;
	strSelSql += m_strSqlOrderby;
	return strSelSql;
}

CString CSqlDefine::CreatSqlOption(int index)
{
	CString strSqlOption;
	if (index>m_nCurOption)
	return "";
		switch(m_SqlDefine[index].nSubType%1000)
		{
		case OPER_NONE:
			strSqlOption.Format(" %s ", m_SqlDefine[index].cStr);;
			break;
		case OPER_EQUAL:
			strSqlOption.Format("%s = %s ", m_SqlDefine[index].cStr, m_SqlDefine[index].cStrVal);
			break;
		case OPER_GREAT:
			strSqlOption.Format("%s > %s ", m_SqlDefine[index].cStr, m_SqlDefine[index].cStrVal);
			break;
		case OPER_LESS:
			strSqlOption.Format("%s < %s ", m_SqlDefine[index].cStr, m_SqlDefine[index].cStrVal);
			break;
		case OPER_GREAT_EQUAL:
			strSqlOption.Format("%s >= %s ", m_SqlDefine[index].cStr, m_SqlDefine[index].cStrVal);
			break;
		case OPER_LESS_EQUAL:
			strSqlOption.Format("%s <= %s ", m_SqlDefine[index].cStr, m_SqlDefine[index].cStrVal);
			break;
		case OPER_NOT_EQUAL:
			strSqlOption.Format("%s <> %s ", m_SqlDefine[index].cStr, m_SqlDefine[index].cStrVal);
			break;
		case OPER_LIKE:
			//Changed '*%s*' 2002-01-31 by zj
			if(isprint(m_SqlLike))		//if(pstrSqlOptionOpt->bStr)无用
				strSqlOption.Format("%s like '%c%s%c' ",m_SqlDefine[index].cStr,m_SqlLike,m_SqlDefine[index].cStrVal,m_SqlLike);
			else	return "";
			break;
		case OPER_BIT_AND_NEQUAL:
			strSqlOption.Format("%s & %s <> %d ", m_SqlDefine[index].cStr, m_SqlDefine[index].cStrVal, m_SqlDefine[index].bres);
			break;
		case OPER_BIT_AND_EQUAL:
			strSqlOption.Format("%s & %s = %d ", m_SqlDefine[index].cStr, m_SqlDefine[index].cStrVal, m_SqlDefine[index].bres);
			break;
		}
	return strSqlOption;
}


CString CSqlDefine::CreateSqlWhere()
{
	CString strSqlWhere,strLogic = " AND (";
	bool bstart = true;
	int* GroupFlag;
	GroupFlag = new int[m_nCurOption];
	for(int i=0; i<m_nCurOption;i++)
		GroupFlag[i]=0;
	for(i=0;i<m_nCurOption;i++)
	{
		if(m_SqlDefine[i].nType == SQL_WHERE)
		{	
			if(m_SqlDefine[i].nGroup ==0 && m_SqlDefine[i].nSubType==OPER_TYPE_OR)
			{
				strLogic = " OR (";
			}
	
			if(m_SqlDefine[i].nGroup !=0)
			{
				if(GroupFlag[m_SqlDefine[i].nGroup] == 1)
					continue;
				if(bstart) 	
				{
					strSqlWhere += " WHERE (";
					bstart = false;
				}
				else
				{
					strSqlWhere+= strLogic;
				}
				strSqlWhere += CreateGroupSql(m_SqlDefine[i].nGroup);
				strSqlWhere += ") ";
				GroupFlag[m_SqlDefine[i].nGroup] = 1;
			}
		}
	}
	delete []GroupFlag;
	return strSqlWhere;
}

CString CSqlDefine::CreateSqlGroupby()
{
	CString strSqlGroupby;
	for(int i=0;i<m_nCurOption;i++)
	{
		if(m_SqlDefine[i].nType == SQL_GROUPBY && m_SqlDefine[i].nGroup ==0)
		{
			
			strSqlGroupby += " GROUP BY ";
			strSqlGroupby += m_SqlDefine[i].cStr;
			return strSqlGroupby;		
		}			
	}
	return "";
}

int CSqlDefine::AddHavingOption(char *pStrName, char *pStrValue, int nOper, int bres, bool bLock)
{
	if(m_SqlDefine == NULL)
		return -1;
	if(m_nCurOption > m_nMaxOption)
		return -2;
	if( strlen(pStrName) > 64 ||strlen(pStrValue) > 64 )
		return -3;
	strcpy(m_SqlDefine[m_nCurOption].cStr,pStrName);
	strcpy(m_SqlDefine[m_nCurOption].cStrVal,pStrValue);
	m_SqlDefine[m_nCurOption].bLock = bLock;
	m_SqlDefine[m_nCurOption].nType = SQL_HAVING;
	m_SqlDefine[m_nCurOption].nSubType = nOper;
	m_SqlDefine[m_nCurOption].nGroup = 0;
	m_SqlDefine[m_nCurOption].bres = bres;
	m_nCurOption++;
	return 0;
}

CString CSqlDefine::CreateSqlHaving()
{
	CString strSqlHaving;
	bool bstart = true;
	for(int i=0;i<m_nCurOption;i++)
	{
		if(m_SqlDefine[i].nType == SQL_HAVING && m_SqlDefine[i].nGroup ==0)
		{
			if(bstart) 
			{
				strSqlHaving += " HAVING (";
				bstart = false;
			}
			else
			{
				if(m_SqlDefine[i].nSubType > OPER_TYPE_OR)
				{
					strSqlHaving += " OR (";
				}
				else
				{
					strSqlHaving += " AND (";
				}
			}
			strSqlHaving += CreatSqlOption(i);
			strSqlHaving += ") ";	
		}			
	}
	return strSqlHaving;
}

CString CSqlDefine::CreateSqlOrderby()
{
	CString strSqlOrderby;
	for(int i=0;i<m_nCurOption;i++)
	{
		if(m_SqlDefine[i].nType == SQL_ORDERBY && m_SqlDefine[i].nGroup ==0)
		{
			
			strSqlOrderby += " ORDER BY ";
			strSqlOrderby += m_SqlDefine[i].cStr;
			return strSqlOrderby;		
		}			
	}
	return "";
}

CString CSqlDefine::CreateDelSql()
{
	CString strDelSql;
	strDelSql = "DELETE ";
	bool bstart = true;
	for(int i=0;i<m_nCurOption;i++)
	{
		if(m_SqlDefine[i].nType == SQL_TABLES && m_SqlDefine[i].nGroup ==0 )
		{	
			strDelSql += " FROM ";
			strDelSql += m_SqlDefine[i].cStr;
			strDelSql += CreateSqlWhere();
			return strDelSql;
		}			
	}
	return "";
}



int CSqlDefine::CheckSql()
{
	m_strSqlSel.TrimLeft();
	m_strSqlSel.TrimRight();
	if(m_strSqlSel.IsEmpty())
		return -1;
	int ret=-1;
	for(int i=0;i<m_nCurOption;i++)
	{
		if(m_SqlDefine[i].nType == SQL_TABLES && m_SqlDefine[i].nGroup ==0 )
		{	
			CString strtmp;
			strtmp.Format("%s", m_SqlDefine[i].cStr);
			strtmp.TrimLeft();
			strtmp.TrimRight();
			if(!strtmp.IsEmpty())
				ret=0;
			break;
		}			
	}
	int start = -1;
	int count = 0;
	while((start = m_strSqlSel.Find("(",start+1)) > 0)
	{
		count++;
	}
	start =-1;
	while((start = m_strSqlSel.Find(")",start+1)) > 0)
	{
		count--;
	}
	if(count!=0)
		ret--;
	return ret;
}

int CSqlDefine::CreateSql()
{
	m_strSqlSel = CreateSelSql();
	m_strSqlDel = CreateDelSql();
	m_strSqlSel.TrimLeft();
	m_strSqlSel.TrimRight();
	if(m_strSqlSel.IsEmpty())
		return -1;
	return 0;
}
CString CSqlDefine::GetSqlSelStr()
{
	return m_strSqlSel;
}
CString CSqlDefine::GetSqlDelStr()
{
	return m_strSqlDel;
}

CString CSqlDefine::GetSqlOrderStr()
{
	return m_strSqlOrderby;
}

CString CSqlDefine::GetSqlHavingStr()
{
	return  m_strSqlHaving;
}

CString CSqlDefine::GetSqlWhereStr()
{
	return m_strSqlWhere;
}

CString CSqlDefine::GetSqlGroupStr()
{
	return m_strSqlGroupby;
}

CString CSqlDefine::GetTableName()
{
	return m_strTableName;
}

int CSqlDefine::CleanOptions(BOOL bCleanLock)
{

	for(int i=0;i<m_nCurOption;i++)
	{
		if(bCleanLock)
		{
			memset(&m_SqlDefine[i], -1, sizeof(TagSqlDef));
		}
		else
		{
			if(!m_SqlDefine[i].bLock)
			{
				memset(&m_SqlDefine[i], -1, sizeof(TagSqlDef));
			}
		}
	}
	CreateSql();
	return 1;
}