/*****************************************************************************/
/* SOURCE FILE */
/*****************************************************************************/
/*
$Archive: $
$Revision: $
$Date: $
$Author: $
Description: Implementation of sql lite database wrapper classes
TOOL And XML FORMS License
==========================
Except where otherwise noted, all of the documentation
and software included in the TOOL package is
copyrighted by Michael Swartzendruber.
Copyright (C) 2005 Michael John Swartzendruber.
All rights reserved.
Access to this code, whether intentional or accidental,
does NOT IMPLY any transfer of rights.
This software is provided "as-is," without any express
or implied warranty. In no event shall the author be held
liable for any damages arising from the use of this software.
Permission is granted to anyone to use this software for
any purpose, including commercial applications, and to
alter and redistribute it, provided that the following
conditions are met:
1. All redistributions of source code files must retain
all copyright notices that are currently in place,
and this list of conditions without modification.
2. The origin of this software must not be misrepresented;
you must not claim that you wrote the original software.
3. If you use this software in another product, an acknowledgment
in the product documentation would be appreciated but is
not required.
4. Modified versions in source or binary form must be plainly
marked as such, and must not be misrepresented as being
the original software.
*/
static char OBJECT_ID[] = "$Revision: $ : $Date: $";
/*****************************************************************************/
#include "../../../stdafx.h"
#include <stdlib.h>
#include <io.h>
#include <stdio.h>
#include <time.h>
#include "../../SqlLite/sqlite.h"
#include "VMSqlLite.h"
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteDatabase::VMSqlLiteDatabase
DESCRIPTION: opens the connection to the indicated database file
INPUT: pchFileName - pointer to the full path to the database file
OUTPUT: none
RETURNS: -
*/
VMSqlLiteDatabase::VMSqlLiteDatabase( const char* pchFileName ) : m_poDB( NULL )
{
InitializeCriticalSection( &m_xCriticalSection );
if ( NULL != pchFileName )
{
Open( VMString( pchFileName ) );
}
}
/* End of function "VMSqlLiteDatabase::VMSqlLiteDatabase"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteDatabase::VMSqlLiteDatabase
DESCRIPTION: default ctor for database connection object
INPUT: void
OUTPUT: none
RETURNS: none
*/
VMSqlLiteDatabase::VMSqlLiteDatabase( void ) : m_poDB( NULL )
{
InitializeCriticalSection( &m_xCriticalSection );
}
/* End of function "VMSqlLiteDatabase::VMSqlLiteDatabase"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteDatabase::~VMSqlLiteDatabase
DESCRIPTION: dtor
INPUT: void
OUTPUT: none
RETURNS: none
*/
VMSqlLiteDatabase::~VMSqlLiteDatabase( void )
{
Close();
DeleteCriticalSection( &m_xCriticalSection );
}
/* End of function "VMSqlLiteDatabase::~VMSqlLiteDatabase"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteDatabase::GetLock
DESCRIPTION: single threads the connection objct
INPUT: void
OUTPUT: none
RETURNS: void
*/
void VMSqlLiteDatabase::GetLock( void )
{
EnterCriticalSection( &m_xCriticalSection );
}
/* End of function "VMSqlLiteDatabase::GetLock"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteDatabase::FreeLock
DESCRIPTION: thread release of this
INPUT: void
OUTPUT: none
RETURNS: void
*/
void VMSqlLiteDatabase::FreeLock( void )
{
LeaveCriticalSection( &m_xCriticalSection );
}
/* End of function "VMSqlLiteDatabase::FreeLock"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteDatabase::Open
DESCRIPTION: opens and attaches to the given database file
INPUT: oFileName - the file name to open
OUTPUT: none
RETURNS: true if worked, false if not
*/
bool VMSqlLiteDatabase::Open( VMString oFileName )
{
char* pchErrorMessage = NULL;
GetLock();
m_poDB = sqlite_open( oFileName.Buffer(), 0, &pchErrorMessage );
if ( !m_poDB )
{
if ( pchErrorMessage )
{
m_oErrorMessage = VMString( pchErrorMessage );
sqlite_freemem( pchErrorMessage );
}
FreeLock();
return( false );
}
FreeLock();
return( true );
}
/* End of function "VMSqlLiteDatabase::Open"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteDatabase::Close
DESCRIPTION: detaches this from the database file
INPUT: void
OUTPUT: none
RETURNS: true if worked, false if not
*/
bool VMSqlLiteDatabase::Close( void )
{
GetLock();
if ( m_poDB )
{
sqlite_close( m_poDB );
m_poDB = NULL;
}
FreeLock();
return( true );
}
/* End of function "VMSqlLiteDatabase::Close"
/*****************************************************************************/
///////////////////////////////////////////////////////////////////////////////
//
//
//
///////////////////////////////////////////////////////////////////////////////
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryBase::VMSqlLiteQueryBase
DESCRIPTION: ctor
INPUT: poDB - pointer to database connection object
OUTPUT: none
RETURNS: none
*/
VMSqlLiteQueryBase::VMSqlLiteQueryBase( VMSqlLiteDatabase* poDB ) : m_poDB( poDB )
{
}
/* End of function "VMSqlLiteQueryBase::VMSqlLiteQueryBase"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryBase::SetColumnNameAtIndex
DESCRIPTION: for results result set, set the name of the column at the
the each column index
INPUT: ppchColumnNames - pointer to column names array
iColumnCount - the number of columns in the names array
OUTPUT:
RETURNS: void -
*/
void VMSqlLiteQueryBase::SetColumnNameAtIndex( const char** ppchColumnNames, int iColumnCount )
{
m_oColumnNames.clear();
for ( int iLoop = 0; iLoop < iColumnCount; iLoop++ )
{
VMString oColumnName( ppchColumnNames[ iLoop ] );
oColumnName.Lower();
m_oColumnNames[ std::string( oColumnName.Buffer() ) ] = iLoop;
}
}
/* End of function "VMSqlLiteQueryBase::SetColumnNameAtIndex"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryBase::GetColumnIndexForName
DESCRIPTION: returns the column index for the given column
INPUT: oFieldName - the name of the column to look up
OUTPUT: none
RETURNS: -1 if failed, or column index
*/
int VMSqlLiteQueryBase::GetColumnIndexForName( VMString oFieldName )
{
oFieldName.Lower();
MAP_FIELDS_BY_NAME_ITER oIter = m_oColumnNames.find( std::string( oFieldName.Buffer() ) );
if ( oIter == m_oColumnNames.end() )
{
return( -1 );
}
return( (*oIter).second );
}
/* End of function "VMSqlLiteQueryBase::GetColumnIndexForName"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryBase::CDBField::AsChar
DESCRIPTION: convert a result value to a character pointer
INPUT: void
OUTPUT: none
RETURNS: value as character pointer
*/
const char* VMSqlLiteQueryBase::CDBField::AsChar( void ) const
{
return( m_pchData );
}
/* End of function "VMSqlLiteQueryBase::CDBField::AsChar"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryBase::CDBField::AsString
DESCRIPTION: convert a result value to a string object
INPUT: void
OUTPUT: none
RETURNS: value as string
*/
VMString VMSqlLiteQueryBase::CDBField::AsString( void ) const
{
return( VMString( m_pchData ) );
}
/* End of function "VMSqlLiteQueryBase::CDBField::AsString"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryBase::CDBField::AsLong
DESCRIPTION: convert a result value to a long
INPUT: void
OUTPUT: none
RETURNS: value as a long
*/
long VMSqlLiteQueryBase::CDBField::AsLong( void ) const
{
return( atol( m_pchData ) );
}
/* End of function "VMSqlLiteQueryBase::CDBField::AsLong"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryBase::CDBField::AsDouble
DESCRIPTION: convert a result value to a double
INPUT: void
OUTPUT: none
RETURNS: value as double
*/
double VMSqlLiteQueryBase::CDBField::AsDouble( void ) const
{
return( atof( m_pchData ) );
}
/* End of function "VMSqlLiteQueryBase::CDBField::AsDouble"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryBase::CDBField::AsBool
DESCRIPTION: convert a result value to a boolean
INPUT: void
OUTPUT: none
RETURNS: value as boolean
*/
bool VMSqlLiteQueryBase::CDBField::AsBool( void ) const
{
return( AsLong() != 0 );
}
/* End of function "VMSqlLiteQueryBase::CDBField::AsBool"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryBase::CDBField::AsDate
DESCRIPTION: convert a result value to a date object
INPUT: void
OUTPUT: none
RETURNS: value as date
*/
VMDateTime VMSqlLiteQueryBase::CDBField::AsDate( void ) const
{
return( VMDateTime( m_pchData ) );
}
/* End of function "VMSqlLiteQueryBase::CDBField::AsDate"
/*****************************************************************************/
///////////////////////////////////////////////////////////////////////////////
//
//
//
///////////////////////////////////////////////////////////////////////////////
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQuery::VMSqlLiteQuery
DESCRIPTION: ctor
INPUT: poDB - pointer to database connection object
OUTPUT: none
RETURNS: none
*/
VMSqlLiteQuery::VMSqlLiteQuery( VMSqlLiteDatabase* poDB )
: VMSqlLiteQueryBase( poDB ),
m_poDBVM( NULL ),
m_iColumnIndex( -1 ),
m_ppchColumnValue( NULL ),
m_ppchColumnName(NULL)
{
}
/* End of function "VMSqlLiteQuery::VMSqlLiteQuery"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQuery::~VMSqlLiteQuery
DESCRIPTION: dtor
INPUT: void
OUTPUT: none
RETURNS: none
*/
VMSqlLiteQuery::~VMSqlLiteQuery( void )
{
Close();
}
/* End of function "VMSqlLiteQuery::~VMSqlLiteQuery"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQuery::Close
DESCRIPTION: close the query set
INPUT: void
OUTPUT: none
RETURNS: true if worked, false if not
*/
bool VMSqlLiteQuery::Close( void )
{
m_poDB->GetLock();
if ( !m_poDBVM )
{
m_poDB->FreeLock();
return( true );
}
m_oLastError = _T( "" );
char* pchErrorMessage = NULL;
int iResult = sqlite_finalize( m_poDBVM, &pchErrorMessage );
if ( iResult != SQLITE_OK )
{
if ( pchErrorMessage )
{
m_oLastError = VMString( pchErrorMessage );
sqlite_freemem( pchErrorMessage );
}
}
m_poDBVM = NULL;
m_poDB->FreeLock();
return( iResult == SQLITE_OK );
}
/* End of function "VMSqlLiteQuery::Close"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQuery::Execute
DESCRIPTION: execute a query against the database
INPUT: pchFormatString - format string
... - arguments
OUTPUT:
RETURNS: true if worked, false if not
*/
bool VMSqlLiteQuery::Execute( const char* pchFormatString, ... )
{
m_poDB->GetLock();
m_oLastError = _T( "" );
va_list args;
va_start( args, pchFormatString );
const char* pchSqlRemainder = NULL;
char* pchErrorMessage = NULL;
char* pchSQL = sqlite_vmprintf( pchFormatString, args );
int iResult = sqlite_compile( m_poDB->GetDB(), pchSQL, &pchSqlRemainder, &m_poDBVM, &pchErrorMessage );
sqlite_freemem( pchSQL );
va_end( args );
if ( iResult != SQLITE_OK )
{
if ( pchErrorMessage )
{
m_oLastError = VMString( pchErrorMessage );
sqlite_freemem( pchErrorMessage );
}
}
else
{
FetchData();
}
m_poDB->FreeLock();
return( m_oLastError.Length() == 0 );
}
/* End of function "VMSqlLiteQuery::Execute"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQuery::IsEOF
DESCRIPTION: is the result set exhausted?
INPUT: void
OUTPUT: none
RETURNS: true if it is, false if not
*/
bool VMSqlLiteQuery::IsEOF( void ) const
{
return( m_poDBVM == NULL );
}
/* End of function "VMSqlLiteQuery::IsEOF"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQuery::FetchData
DESCRIPTION: get one row row of data into local buffers
INPUT: void
OUTPUT: none
RETURNS: true if worked, false if not
*/
bool VMSqlLiteQuery::FetchData( void )
{
m_poDB->GetLock();
int iResult = 0;
int iColumn = sqlite_step( m_poDBVM, &iResult, &m_ppchColumnValue, &m_ppchColumnName );
if ( iColumn != m_iColumnIndex )
{
m_iColumnIndex = iColumn;
if ( NULL != m_ppchColumnName && NULL != *m_ppchColumnName )
{
SetColumnNameAtIndex( m_ppchColumnName, m_iColumnIndex );
}
}
if ( iResult == SQLITE_ROW )
{
m_poDB->FreeLock();
return( true );
}
else
{
Close();
m_poDB->FreeLock();
return( false );
}
}
/* End of function "VMSqlLiteQuery::FetchData"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQuery::GetColumnIndex
DESCRIPTION: returns the column index for this
INPUT: void
OUTPUT: none
RETURNS: internal column index value
*/
int VMSqlLiteQuery::GetColumnIndex( void ) const
{
return( m_iColumnIndex );
}
/* End of function "VMSqlLiteQuery::GetColumnIndex"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQuery::GetColumnNameAtIndex
DESCRIPTION: the column name for the given index
INPUT: iFieldIndex - the index to get the name for
OUTPUT: none
RETURNS: pointer to column name
*/
const char* VMSqlLiteQuery::GetColumnNameAtIndex( int iFieldIndex )
{
return( m_ppchColumnName[ iFieldIndex ] );
}
/* End of function "VMSqlLiteQuery::GetColumnNameAtIndex"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQuery::GetColumnValueByIndex
DESCRIPTION: gets the column value at the given column index (for the
current row)
INPUT: iFieldIndex - the column index
OUTPUT: none
RETURNS: VMSqlLiteQueryBase::CDBField value object
*/
VMSqlLiteQueryBase::CDBField VMSqlLiteQuery::GetColumnValueByIndex( int iFieldIndex )
{
return( CDBField( m_ppchColumnValue[ iFieldIndex ] ) );
}
/* End of function "VMSqlLiteQuery::GetColumnValueByIndex"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQuery::GetColumnValueByName
DESCRIPTION: get the value for the column by column name (for the current
row)
INPUT: oFieldName - the name of the field to get the value for
OUTPUT: none
RETURNS: VMSqlLiteQueryBase::CDBField value object
*/
VMSqlLiteQueryBase::CDBField VMSqlLiteQuery::GetColumnValueByName( VMString oFieldName )
{
int iIndex = GetColumnIndexForName( oFieldName );
if ( iIndex < 0 )
{
VMString oError;
oError.Format( "Unknown field name <%s>", oFieldName.Buffer() );
throw VMSqlLiteException( oError );
}
return GetColumnValueByIndex( iIndex );
}
/* End of function "VMSqlLiteQuery::GetColumnValueByName"
/*****************************************************************************/
///////////////////////////////////////////////////////////////////////////////
//
//
//
///////////////////////////////////////////////////////////////////////////////
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryResult::VMSqlLiteQueryResult
DESCRIPTION: ctor
INPUT: poDB - pointer to connection object
OUTPUT: none
RETURNS: none
*/
VMSqlLiteQueryResult::VMSqlLiteQueryResult( VMSqlLiteDatabase* poDB )
: VMSqlLiteQueryBase( poDB ),
m_ppchResult( NULL ),
m_iRowCount( 0 ),
m_iColumnCount( 0 ),
m_iCursor( 0 )
{
}
/* End of function "VMSqlLiteQueryResult::VMSqlLiteQueryResult"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryResult::~VMSqlLiteQueryResult
DESCRIPTION: dtor
INPUT: void
OUTPUT: none
RETURNS: none
*/
VMSqlLiteQueryResult::~VMSqlLiteQueryResult( void )
{
m_poDB->GetLock();
sqlite_free_table( m_ppchResult );
m_poDB->FreeLock();
}
/* End of function "VMSqlLiteQueryResult::~VMSqlLiteQueryResult"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryResult::Execute
DESCRIPTION: executes a query against the database and caches the result
INPUT: pchFormatText - format string for query
... - arguments
OUTPUT:
RETURNS: true if worked, false if not
*/
bool VMSqlLiteQueryResult::Execute( const char* pchFormatText, ... )
{
m_poDB->GetLock();
sqlite_free_table( m_ppchResult );
m_oLastError = _T( "" );
char* pchErrorMessage = NULL;
va_list args;
va_start( args, pchFormatText );
int iResult = SQLITE_ERROR;
iResult = sqlite_get_table_vprintf( m_poDB->GetDB(),
pchFormatText,
&m_ppchResult,
&m_iRowCount,
&m_iColumnCount,
&pchErrorMessage,
args );
va_end( args );
if ( iResult != SQLITE_OK )
{
if ( pchErrorMessage )
{
m_oLastError = VMString( pchErrorMessage );
sqlite_freemem( pchErrorMessage );
}
m_poDB->FreeLock();
return( false );
}
SetColumnNameAtIndex( (const char**)m_ppchResult, m_iColumnCount );
m_poDB->FreeLock();
return( true );
}
/* End of function "VMSqlLiteQueryResult::Execute"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryResult::NumChanges
DESCRIPTION: return the number of rows changed by a query
INPUT: void
OUTPUT: none
RETURNS: row count
*/
int VMSqlLiteQueryResult::NumChanges( void )
{
return( sqlite_changes( m_poDB->GetDB() ) );
}
/* End of function "VMSqlLiteQueryResult::NumChanges"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryResult::GetRowCount
DESCRIPTION: returns the number of rows in the result set
INPUT: void
OUTPUT: none
RETURNS: row count
*/
int VMSqlLiteQueryResult::GetRowCount( void )
{
return( m_iRowCount );
}
/* End of function "VMSqlLiteQueryResult::GetRowCount"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryResult::GetColumnCount
DESCRIPTION: returns the number of columns in the result set
INPUT: void
OUTPUT: none
RETURNS: column count
*/
int VMSqlLiteQueryResult::GetColumnCount( void )
{
return( m_iColumnCount );
}
/* End of function "VMSqlLiteQueryResult::GetColumnCount"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryResult::GetFieldNameByIndex
DESCRIPTION: returns the name of the column at the given colum index
INPUT: iFieldIndex - column index
OUTPUT: none
RETURNS: pointer to column name
*/
char* VMSqlLiteQueryResult::GetFieldNameByIndex( int iFieldIndex )
{
return( m_ppchResult[ iFieldIndex ] );
}
/* End of function "VMSqlLiteQueryResult::GetFieldNameByIndex"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryResult::GetValueAt
DESCRIPTION: return the value object at the given row/column location
INPUT: iRowIndex -
iFieldIndex -
OUTPUT:
RETURNS: VMSqlLiteQueryBase::CDBField value object
*/
VMSqlLiteQueryBase::CDBField VMSqlLiteQueryResult::GetValueAt( int iRowIndex, int iFieldIndex )
{
return( CDBField( m_ppchResult[ ( iRowIndex + 1 ) * m_iColumnCount + iFieldIndex ] ) );
}
/* End of function "VMSqlLiteQueryResult::GetValueAt"
/*****************************************************************************/
/*****************************************************************************/
/*
FUNCTION NAME: VMSqlLiteQueryResult::FieldByName
DESCRIPTION: return the value object at the given row index for the
given column name
INPUT: iRowIndex -
oColumnName -
OUTPUT:
RETURNS: VMSqlLiteQueryBase::CDBField value object
*/
VMSqlLiteQueryBase::CDBField VMSqlLiteQueryResult::FieldByName( int iRowIndex, VMString oColumnName )
{
int iColumnIndex = GetColumnIndexForName( oColumnName );
if ( iColumnIndex < 0 )
{
VMString oError;
oError.Format( "Unknown field name <%s>", oColumnName.Buffer() );
throw VMSqlLiteException( oError );
}
return( GetValueAt( iRowIndex, iColumnIndex ) );
}
/* End of function "VMSqlLiteQueryResult::FieldByName"
/*****************************************************************************/
VMSqlLiteQueryBase::CDBField VMSqlLiteQueryResult::GetValueAt( int fieldIdx )
{
return( GetValueAt( m_iCursor, fieldIdx ) );
}
VMSqlLiteQueryBase::CDBField VMSqlLiteQueryResult::FieldByName( VMString fieldName )
{
return( FieldByName( m_iCursor, fieldName ) );
}
/*****************************************************************************/
/* Check-in history */
/*
*$Log: $
*/
/*****************************************************************************/
Submit an article or tip