void GetAllVisiter(int64 ParentID, vector<int64>& VisiterList,int64 NotIncludeChildID/*=0*/){
char TableName[30];
int64toa(ParentID,TableName);
if(!GetWorldDB().tableExists(TableName)){
return ;
}
CppSQLite3Buffer SQL;
SQL.format("select %s from \"%s\" where %s>%d",
ITEM_ID,
TableName,
ITEM_TYPE,
OBJECT_SPACE
);
CppSQLite3Query Result = GetWorldDB().execQuery(SQL);
if(Result.eof())return ;
while(!Result.eof()){
int64 ChildID = Result.getInt64Field(0);
if (ChildID != NotIncludeChildID)
{
VisiterList.push_back(ChildID);
}
Result.nextRow();
}
}
void CBrainMemory::GetMemo(int64 RoomID, deque<tstring>& MemoList){
int64 FatherID,ChildID,RoomType;
char buf[30];
CppSQLite3Buffer SQL;
if(LBrainHasTable(RoomID)){
//索引空间是否有表，有则表示其为其它空间的空间值
//根据索引表得到其作为空间值时的空间存储ID，存入种子表
ToLBrain(RoomID);
int64toa(RoomID,buf);
SQL.format("select %s,%s from \"%s\" ;",LB_FATHER_ID,LB_CHILD_ID,buf);
CppSQLite3Table t0 = BrainDB.getTable(SQL);
for (int row = 0; row < t0.numRows(); row++)
{
t0.setRow(row);
FatherID = t0.getInt64Field(0);
ChildID = t0.getInt64Field(1);
if(ChildID == -RoomID)continue;
RoomType = GetChildType(FatherID,ChildID);
if(RoomType == MEMORY_LAN){ //看是否文本空间修饰RoomID
tstring Text;
if(RetrieveText(ChildID,Text)){
MemoList.push_back(Text);
}
}
}
}
}
void CreateTable(int64 TableID)
{
char TableName[30];
int64toa(TableID,TableName);
CppSQLite3Buffer SQL; // 空间ID 空间名字 空间类型 空间指纹 空间属性 空间拥有者信息
SQL.format("CREATE TABLE \"%s\" ( a INTEGER NOT NULL ,b TEXT NOT NULL, c INTEGER NOT NULL, d TEXT, e TEXT, f TEXT );",TableName);
GetWorldDB().execDML(SQL);
}
int CDbMeter::GroupAddMember(const char* dbfilename, int nGroupKey, char* szMemberId)
{
try
{
CppSQLite3DB db;
db.open(dbfilename);
CppSQLite3Buffer bufSQL;
CppSQLite3Query q;
CppSQLite3Table t;
int nFindgroupkey = -1;
bufSQL.format("SELECT EUI64ID FROM member WHERE EUI64ID=%Q;", szMemberId);
t = db.getTable(bufSQL);
if( t.numRows() == 0 )
{
bufSQL.clear();
bufSQL.format("INSERT INTO member VALUES(%Q, 'Meter', 'none', '1.0.0', '1.0.0', 'Nomal', 'None');", szMemberId);
db.execDML(bufSQL);
}
bufSQL.clear();
bufSQL.format("SELECT * FROM groups WHERE GroupKey=%d;", nGroupKey);
t = db.getTable(bufSQL);
if( t.numRows() == 1)
nFindgroupkey = atoi(t.fieldValue(0));
else
nFindgroupkey = -1;
if( nFindgroupkey >= -1)
{
bufSQL.clear();
bufSQL.format("INSERT INTO groupmember(EUI64ID, GroupKey) VALUES(%Q, %d);", szMemberId, nGroupKey);
db.execDML(bufSQL);
}
db.close();
}
catch( CppSQLite3Exception& e )
{
XDEBUG("%s\r\n",e.errorMessage());
return -1;
}
return SQLITE_OK;
}
bool CSpace::Load(int64 ParentID,int64 ChildID){
m_ID = ParentID;
char ParentName[30], ChildName[30];
int64toa(ParentID,ParentName);
int64toa(ChildID,ChildName);
CppSQLite3Buffer SQL;
SQL.format("select * from \"%s\" where %s = \"%s\" ",
ParentName,
ITEM_ID,
ChildName
);
CppSQLite3Query Result = GetWorldDB().execQuery(SQL);
if(Result.eof()){
return false;
}
assert(Size()>5);
assert(GetDataType(0) == TYPE_INT);
int64& OldID = *(int64*)GetData(0);
OldID = ChildID;
AnsiString AnsiName = Result.getStringField(1,"");
tstring Name = UTF8toWS(AnsiName);
SetName(Name);
SPACETYPE Type = (SPACETYPE)Result.getIntField(2);
SetSpaceType(Type);
AnsiString Fingerprint = Result.getStringField(3);
tstring Fingerprint1 = UTF8toWS(Fingerprint);
SetFingerprint(Fingerprint1);
AnsiString AnsiProperty = Result.getStringField(4,"");
ePipeline Property;
uint32 pos =0;
bool ret = Property.FromString(AnsiProperty,pos);
if(!ret){
return false;
}
SetProerty(Property);
AnsiString AnsiOwner = Result.getStringField(5,"");
ePipeline OwnerInfo;
pos=0;
ret = OwnerInfo.FromString(AnsiOwner,pos);
if(!ret){
return false;
}
SetOwnerInfo(OwnerInfo);
return true;
}
bool DBSetting::SetValue(long lKey,const std::string &strValue)
{
//打开数据库
CppSQLite3DB dbTask;
dbTask.open(m_strDB.c_str());
CheckCreateSettingTable(dbTask);
CppSQLite3Buffer strSql;
try{
strSql.format("select count(*) from T_Setting where key=%d;",lKey);
CppSQLite3Query q = dbTask.execQuery(strSql);
if (0==q.getIntField(0))
{
strSql.format("insert into T_Setting(key,value) values(%d,'%q');",lKey,strValue.c_str());
if(1!=dbTask.execDML(strSql))
{
ATLASSERT(FALSE);
return false;
}
}
else
{
strSql.format("update T_Setting set value = '%q' where key=%d;",strValue.c_str(),lKey);
if(1!=dbTask.execDML(strSql))
{
ATLASSERT(FALSE);
return false;
}
}
}
catch(CppSQLite3Exception &exp)
{
exp;
ATLTRACE("error:%s\n",exp.errorMessage());
ATLASSERT(FALSE);
return false;
}
return true;
}
void CBrainMemory::InsertRow(int64 TableName,int64 one, int64 two, int64 three)
{
CppSQLite3Buffer SQL;
char a[30],b[30],c[30],d[30];
int64toa(TableName,a);
int64toa(one,b);
int64toa(two,c);
int64toa(three,d);
SQL.format("insert into \"%s\" values (\"%s\", \"%s\", \"%s\")",a,b,c,d);
BrainDB.execDML(SQL);
}
/*
-注意：由于是递归，外部调用着应该考虑用事物回滚
*/
void DeleteAllChild(int64 ParentID)
{
char ParentName[30];
int64toa(ParentID,ParentName);
if(!GetWorldDB().tableExists(ParentName))return; //指定空间没有子空间
CppSQLite3Buffer SQL;
//首先找到所有子空间的地址
vector<int64> AddressList;
SQL.format("select %s from \"%s\" ",
ITEM_ID,
ParentName
);
CppSQLite3Query Result = GetWorldDB().execQuery(SQL);
while(!Result.eof()){
int64 ChildID = Result.getInt64Field(0);
AddressList.push_back(ChildID);
Result.nextRow();
}
//根据地址，执行递归操作
for(uint32 i=0; i<AddressList.size();i++)
{
int64 ID = AddressList[i];
DeleteAllChild(ID);
}
//最后删除子空间的所在的容器（表）
if(AddressList.size() != 0){
SQL.format("drop table \"%s\" ",ParentName);
GetWorldDB().execDML(SQL);
}
}
bool HasChild(int64 ParentID)
{
if(!HasTable(ParentID))return false;
char TableName[30];
int64toa(ParentID,TableName);
CppSQLite3Buffer SQL;
SQL.format("select %s from \"%s\" ",ITEM_ID,TableName);
CppSQLite3Query Result;
Result = GetWorldDB().execQuery(SQL);
if(Result.eof())return false;
return true;
};
int CDbMeter::GroupDeleteMember(const char* dbfilename, int nGroupKey, char* szMemberId)
{
try
{
CppSQLite3DB db;
db.open(dbfilename);
CppSQLite3Buffer bufSQL;
//CppSQLite3Query q;
CppSQLite3Table t;
bufSQL.format("SELECT * FROM groupmember WHERE GroupKey=%d AND EUI64ID=%Q;", nGroupKey, szMemberId);
//q = db.execQuery(bufSQL);
t = db.getTable(bufSQL);
if( t.numRows() == 0 )
{
XDEBUG("ERROR: %s EUI64ID doesn't exist in %d Groupkey Table!!\r\n", szMemberId, nGroupKey);
return IF4ERR_GROUP_NAME_NOT_EXIST;
}
else
{
bufSQL.clear();
bufSQL.format("DELETE FROM groupmember WHERE GroupKey=%d AND EUI64ID=%Q;", nGroupKey, szMemberId);
db.execQuery(bufSQL);
XDEBUG("%s Groupmember of %d GroupKey deleted in Groupmember Table!!\r\n", szMemberId, nGroupKey);
}
db.close();
}
catch( CppSQLite3Exception& e )
{
XDEBUG("%s\r\n",e.errorMessage());
return e.errorCode();
}
return SQLITE_OK;
}
SpaceAddress FindSpace(ePipeline& Path){
SpaceAddress Addr;
if (Path.Size()==0)
{
Addr.ParentID = ROOT_SPACE;
Addr.ChildID = LOCAL_SPACEID;
return Addr;
}
CppSQLite3Buffer SQL;
int64 ParentID = 0;
int64 ChildID = ROOT_SPACE;
char ParentName[30];
while (Path.Size())
{
tstring Name = Path.PopString();
AnsiString AnsiName = WStoUTF8(Name);
ParentID = ChildID;
ChildID = 0;
int64toa(ParentID,ParentName);
SQL.format("select %s from \"%s\" where %s=\"%s\"",
ITEM_ID,
ParentName,
ITEM_NAME,
AnsiName.c_str()
);
CppSQLite3Query Result = GetWorldDB().execQuery(SQL);
if(!Result.eof()){
ChildID = Result.getInt64Field(0);
Result.nextRow();
}else{
return Addr;
}
}
assert(Path.Size()==0);
Addr.ParentID = ParentID;
Addr.ChildID = ChildID;
return Addr;
}
bool HasChild(int64 ParentID,int64 ChildID)
{
if(!HasTable(ParentID))return false;
char Parent[30],Child[30];
int64toa(ParentID,Parent);
int64toa(ChildID,Child);
CppSQLite3Buffer SQL;
SQL.format("select * from \"%s\" where %s = \"%s\" ",Parent,ITEM_ID,Child);
CppSQLite3Query Result;
Result = GetWorldDB().execQuery(SQL);
if(Result.eof())return false;
return true;
}
int32 GetAllChildList(int64 ParentID, ePipeline& ChildList,int64 NotIncludeChildID /*=0*/){
assert(ParentID >0);
ChildList.Clear();
char TableName[30];
int64toa(ParentID,TableName);
if(!GetWorldDB().tableExists(TableName)){
return 0;
}
//ChildList.SetID(ParentID);
CppSQLite3Buffer SQL;
SQL.format("select %s,%s,%s,%s from \"%s\"",
ITEM_ID,
ITEM_NAME,
ITEM_TYPE,
ITEM_FINGERPRINT,
TableName
);
CppSQLite3Query Result = GetWorldDB().execQuery(SQL);
while(!Result.eof()){
int64 ChildID = Result.getInt64Field(0);
if (ChildID !=NotIncludeChildID)
{
AnsiString s = Result.getStringField(1,"");
tstring Name = UTF8toWS(s);
Name = GetFileNoPathName(Name);
Name = GetFileName(Name);
int32 Type = Result.getIntField(2);
AnsiString FingerPrint = Result.getStringField(3,"");
//ChildList.PushInt64(ChildID);
ChildList.PushString(Name);
ChildList.PushInt(Type);
ChildList.PushString(FingerPrint);
}
Result.nextRow();
}
return ChildList.Size()/3;
}
void CVoxSQLite::GetProfile( const char* username, Profile& rProfile )
{
CppSQLite3Buffer buf;
buf.format( "SELECT * from Profile WHERE username = %Q;", username );
try
{
CppSQLite3Statement stmt = m_db.compileStatement( (const char*)buf );
int nProfileId = 0;
CppSQLite3Query q = stmt.execQuery();
//Process record set.
while (!q.eof())
{
nProfileId = q.getIntField(0);
rProfile.setFirstName ( q.getStringField(2) );
rProfile.setLastName ( q.getStringField(3) );
rProfile.setAlias ( q.getStringField(4) );
rProfile.setSmsSignature( q.getStringField(5) );
rProfile.setCompany ( q.getStringField(6) );
rProfile.setSex ( (EnumSex::Sex)q.getIntField(7) );
rProfile.setNotes ( q.getStringField(10) );
// rProfile.setBirthday( q.getStringField(8) );
// photo (filename)
//TODO rProfile.setPhoto( q.getBlobField(9) );
//Address, email, phone numbers, and URLs will be in separate tables to allow for easy
GetStreetAddresses( nProfileId, rProfile.getStreetAddresses() );
GetEmailAddresses ( nProfileId, rProfile.getEmailAddresses() );
GetTelephones ( nProfileId, rProfile.getTelephones() );
GetUrls ( nProfileId, rProfile.getUrls() );
q.nextRow();
}
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
bool CVoxSQLite::ContactExists( const char* username )
{
int nCount = 0;
try
{
CppSQLite3Buffer buf;
buf.format( "SELECT count(*) from Contact WHERE username = %Q", username );
nCount = m_db.execScalar( (const char*)buf );
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
return (nCount > 0);
}
void DeleteChild(int64 ParentID,int64 ChildID)
{
DeleteAllChild(ChildID);
char FatherName[30], ChildName[30];
int64toa(ParentID,FatherName);
int64toa(ChildID,ChildName);
CppSQLite3Buffer SQL;
//在父空间里删除本空间条目
SQL.format("delete from \"%s\" where %s = \"%s\" ",
FatherName,
ITEM_ID,
ChildName);
GetWorldDB().execDML(SQL);
}
CBrainMemory::CBrainMemory()
{
// TODO: add one-time construction code here
Open();
assert(BrainDB.IsOpen());
//首先生成ROOT_SPACE，似乎没有必要，当生成第一个空间时，会自动生成。
int64 RoomID = ROOT_SPACE;
if(!RBrainHasTable(RoomID))CreateRBrainTable(RoomID);
RoomID = ROOM_SYSTEM;
if(!HasTable(RoomID)){
CppSQLite3Buffer SQL;
char TableName[30];
int64toa(RoomID,TableName);
SQL.format("CREATE TABLE \"%s\" ( a INTEGER NOT NULL ,b TEXT NOT NULL);",TableName);
BrainDB.execDML(SQL);
}
}
void CBrainMemory::CreateTable(int64 ID)
{
CppSQLite3Buffer SQL;
char TableName[30];
int64toa(ID,TableName);
/*
关于：【记忆空间主键冲突问题】 16:54 2012/8/30
索引空间的第三栏原本设置为主键唯一，但是当两个不同语言的单词指向同一个意义空间时，并且大家都是NOUN_MEANING时，
此时意义空间值=意义空间ID，而意义空间值又是索引空间表名，这样，索引表的第三个数据就会相同，产生主键冲突。
目前取消主键唯一，还不清楚会产生何种影响，大概会影响信息回取结果的唯一性
if(ID<0)
SQL.format("CREATE TABLE \"%s\" ( a INTEGER NOT NULL ,b INTEGER NOT NULL,c INTEGER NOT NULL PRIMARY KEY ON CONFLICT FAIL);",TableName);
else
*/
SQL.format("CREATE TABLE \"%s\" ( a INTEGER NOT NULL ,b INTEGER NOT NULL,c INTEGER NOT NULL);",TableName);
BrainDB.execDML(SQL);
}
AnsiString CBrainMemory::GetSystemItem(int64 Item){
CppSQLite3Buffer SQL;
CppSQLite3Query Result;
char Root[30],Name[30];
assert(Item > 0 );
AnsiString s;
int64toa(ROOM_SYSTEM,Root);
int64toa(Item,Name);
SQL.format("select b from \"%s\" where a = \"%s\";",Root,Name);
Result = BrainDB.execQuery(SQL);
if(!Result.eof()) s = Result.getStringField(0);
return s;
};
bool CVoxSQLite::GroupExists( const char* strContactUsername, const char* strName )
{
int nCount = 0;
try
{
CppSQLite3Buffer buf;
buf.format( "SELECT count(*) from [Group] WHERE [username] = %Q AND [name] = %Q", strContactUsername, strName );
nCount = m_db.execScalar( (const char*)buf );
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
return (nCount > 0);
}
void CVoxSQLite::GetStreetAddresses( int nProfileId, StreetAddresses& rAddrs )
{
CppSQLite3Buffer buf;
buf.format( "SELECT * from StreetAddress WHERE profile_id = %d;", nProfileId );
try
{
CppSQLite3Statement stmt = m_db.compileStatement( (const char*)buf );
int nId = 0;
CppSQLite3Query q = stmt.execQuery();
StreetAddress addr;
//Process record set.
while (!q.eof())
{
// "( [profile_id], [type], [street], [locality], [region], [postcode], [country], [visibility] ) "
nId = q.getIntField(0); //Not part of Profile.
addr.setType ( q.getStringField(1) );
addr.setStreet1 ( q.getStringField(2) );
addr.setCity ( q.getStringField(3) );
addr.setStateProvince( q.getStringField(4) );
addr.setPostalCode ( q.getStringField(5) );
addr.setCountry ( q.getStringField(6) );
addr.setVisibility ( (EnumVisibility::Visibility)q.getIntField(7) );
rAddrs.Add( &addr );
q.nextRow();
}
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
int64 CBrainMemory::HasMeaningRoom(int64 ParentTable,int64 Meaning,int64 MeaningType){
CppSQLite3Buffer SQL;
char a[30],b[30];
if(!RBrainHasTable(ParentTable))return 0;
ToRBrain(ParentTable);
int64toa(ParentTable,a);
int64toa(MeaningType,b);
SQL.format("select %s, %s from \"%s\" where %s = \"%s\";",
RB_SPACE_ID,
RB_SPACE_VALUE,
a,
RB_SPACE_TYPE,
b
);
CppSQLite3Table t = BrainDB.getTable(SQL);
if(Meaning == 0){
for (int row = 0; row < t.numRows(); row++)
{
t.setRow(row);
int64 ID = t.getInt64Field(0);
int64 Value = t.getInt64Field(1);
if(ID == Value){
return ID; //已经记忆过了，直接返回
}
}
}else{
for (int row = 0; row < t.numRows(); row++)
{
t.setRow(row);
int64 ID = t.getInt64Field(0);
int64 Value = t.getInt64Field(1);
if(Value = Meaning){
return ID; //已经记忆过了，直接返回
}
}
}
return 0;
}
int CDbMeter::GroupTableDelete(const char* dbfilename, char* szTablekind)
{
try
{
CppSQLite3DB db;
db.open(dbfilename);
CppSQLite3Buffer bufSQL;
CppSQLite3Table t;
bufSQL.format("DELETE FROM %Q", szTablekind);
/*
switch(szTablekind)
{
case 1:
bufSQL.format("DELETE FROM groups;");
break;
case 2:
bufSQL.format("DELETE FROM member;");
break;
case 3:
bufSQL.format("DELETE FROM groupmember;");
break;
case 4:
bufSQL.format("DELETE FROM command;");
break;
}
*/
db.execDML(bufSQL);
db.close();
}
catch( CppSQLite3Exception& e )
{
XDEBUG("%s\r\n",e.errorMessage());
return e.errorCode();
}
return SQLITE_OK;
}
void CVoxSQLite::GetEmailAddresses( int nProfileId, EmailAddresses& rList )
{
CppSQLite3Buffer buf;
buf.format( "SELECT * from EmailAddress WHERE profile_id = %d;", nProfileId );
try
{
CppSQLite3Statement stmt = m_db.compileStatement( (const char*)buf );
int nProfileId = 0;
std::string type = "";
EmailAddress addr;
CppSQLite3Query q = stmt.execQuery();
//Process record set.
while (!q.eof())
{
// "( [profile_id], [type], [email], [visiblity]
nProfileId = q.getIntField(0); //Not part of Profile.
type = q.getStringField( 1 ); //Use to determine which address to update.
addr.setType ( q.getStringField(1) );
addr.setAddress ( q.getStringField(2) );
addr.setVisibility( (EnumVisibility::Visibility) q.getIntField(3) );
rList.Add( &addr );
q.nextRow();
}
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
void CSpace::UpdateProperty(){
if(!IsValid())return;
AnsiString Property;
GetProperty().ToString(Property);
int64 ParentID = GetParentID();
int64 ChildID = GetSpaceID();
CppSQLite3Buffer SQL;
char ParentName[30], ChildName[30];
int64toa(ParentID,ParentName);
int64toa(ChildID,ChildName);
SQL.format("update \"%s\" set %s=\"%s\" where %s = \"%s\" ;",
ParentName,
ITEM_OWNER,
Property.c_str(),
ITEM_ID,
ChildName
);
GetWorldDB().execDML(SQL);
};
void CVoxSQLite::GetUrls( int nProfileId, Urls& rList )
{
CppSQLite3Buffer buf;
buf.format( "SELECT * from Url WHERE profile_id = %d;", nProfileId );
try
{
CppSQLite3Statement stmt = m_db.compileStatement( (const char*)buf );
int nProfileId = 0;
std::string type = "";
CppSQLite3Query q = stmt.execQuery();
Url url;
//Process record set.
while (!q.eof())
{
// "( [profile_id], [type], [address]
nProfileId = q.getIntField(0); //Not part of Profile.
url.setType ( q.getStringField( 1 ) );
url.setUrl ( q.getStringField( 2 ) );
url.setVisibility( (EnumVisibility::Visibility)q.getIntField(3) );
rList.Add( &url );
q.nextRow();
}
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
void CVoxSQLite::GetContact( const char* username, Contact& c )
{
CppSQLite3Buffer buf;
buf.format( "SELECT * from Contact WHERE username = %Q;", username );
try
{
CppSQLite3Statement stmt = m_db.compileStatement( (const char*)buf );
int nContactId = 0;
CppSQLite3Query q = stmt.execQuery();
//Process record set.
while (!q.eof())
{
nContactId = q.getIntField(0);
// c.setName ( q.getStringField(1) );
// c.setNickname ( q.getStringField(2) );
// c.setBirthday ( q.getStringField(3) );
// c.setMergedContact( q.getStringField(3) );
GetMergedContacts( username, c.getMergedContacts() );
GetGroups ( username, c.getGroups() );
GetProfile ( username, c );
q.nextRow();
}
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
void CVoxSQLite::GetTelephones( int nProfileId, Telephones& rList )
{
CppSQLite3Buffer buf;
buf.format( "SELECT * from Telephone WHERE profile_id = %d;", nProfileId );
try
{
CppSQLite3Statement stmt = m_db.compileStatement( (const char*)buf );
int nProfileId = 0;
std::string type = "";
CppSQLite3Query q = stmt.execQuery();
Telephone tel;
//Process record set.
while (!q.eof())
{
// "( [profile_id], [type], [number]
nProfileId = q.getIntField(0); //Not part of Profile.
tel.setType ( q.getStringField( 1 ) );
tel.setNumber( q.getStringField( 2 ) );
rList.Add( &tel );
q.nextRow();
}
stmt.reset();
}
catch (CppSQLite3Exception& e)
{
e.errorCode();
}
}
tstring GetLocalSpaceName(){
if(!GetWorldDB().tableExists(LOCAL_SPACE_STR)){
return _T("");
}
CppSQLite3Buffer SQL;
SQL.format("select %s from \"%s\" where %s = \"%s\" ",
ITEM_NAME,
ROOT_SPACE_STR,
ITEM_ID,
LOCAL_SPACE_STR
);
CppSQLite3Query Result = GetWorldDB().execQuery(SQL);
if(Result.eof())return _T("");
AnsiString utf8Name = Result.getStringField(0,"");
tstring Name = UTF8toWS(utf8Name);
return Name;
}
void testCppSQLite()
{
try
{
int i, fld;
time_t tmStart, tmEnd;
remove(gszFile);
CppSQLite3DB* db = getSQLiteDB();
cout << "SQLite Version: " << db->SQLiteVersion() << endl;
cout << endl << "Creating emp table" << endl;
db->execDML("create table emp(empno int, empname char(20));");
///////////////////////////////////////////////////////////////
// Execute some DML, and print number of rows affected by each one
///////////////////////////////////////////////////////////////
cout << endl << "DML tests" << endl;
int nRows = db->execDML("insert into emp values (7, 'David Beckham');");
cout << nRows << " rows inserted" << endl;
nRows = db->execDML(
"update emp set empname = 'Christiano Ronaldo' where empno = 7;");
cout << nRows << " rows updated" << endl;
nRows = db->execDML("delete from emp where empno = 7;");
cout << nRows << " rows deleted" << endl;
/////////////////////////////////////////////////////////////////
// Transaction Demo
// The transaction could just as easily have been rolled back
/////////////////////////////////////////////////////////////////
int nRowsToCreate(50000);
cout << endl << "Transaction test, creating " << nRowsToCreate;
cout << " rows please wait..." << endl;
tmStart = time(0);
db->execDML("begin transaction;");
for (i = 0; i < nRowsToCreate; i++)
{
char buf[128];
sprintf(buf, "insert into emp values (%d, 'Empname%06d');", i, i);
db->execDML(buf);
}
db->execDML("commit transaction;");
tmEnd = time(0);
////////////////////////////////////////////////////////////////
// Demonstrate CppSQLiteDB::execScalar()
////////////////////////////////////////////////////////////////
cout << db->execScalar("select count(*) from emp;")
<< " rows in emp table in ";
cout << tmEnd-tmStart << " seconds (that was fast!)" << endl;
////////////////////////////////////////////////////////////////
// Re-create emp table with auto-increment field
////////////////////////////////////////////////////////////////
cout << endl << "Auto increment test" << endl;
db->execDML("drop table emp;");
db->execDML(
"create table emp(empno integer primary key, empname char(20));");
cout << nRows << " rows deleted" << endl;
for (i = 0; i < 5; i++)
{
char buf[128];
sprintf(buf,
"insert into emp (empname) values ('Empname%06d');", i+1);
db->execDML(buf);
cout << " primary key: " << db->lastRowId() << endl;
}
///////////////////////////////////////////////////////////////////
// Query data and also show results of inserts into auto-increment field
//////////////////////////////////////////////////////////////////
cout << endl << "Select statement test" << endl;
CppSQLite3Query q = db->execQuery("select * from emp order by 1;");
for (fld = 0; fld < q.numFields(); fld++)
{
cout << q.fieldName(fld) << "(" << q.fieldDeclType(fld) << ")|";
}
cout << endl;
while (!q.eof())
{
cout << q.fieldValue(0) << "|";
cout << q.fieldValue(1) << "|" << endl;
q.nextRow();
}
///////////////////////////////////////////////////////////////
// SQLite's printf() functionality. Handles embedded quotes and NULLs
////////////////////////////////////////////////////////////////
cout << endl << "SQLite sprintf test" << endl;
CppSQLite3Buffer bufSQL;
bufSQL.format("insert into emp (empname) values (%Q);", "He's bad");
cout << (const char*)bufSQL << endl;
db->execDML(bufSQL);
bufSQL.format("insert into emp (empname) values (%Q);", NULL);
cout << (const char*)bufSQL << endl;
db->execDML(bufSQL);
////////////////////////////////////////////////////////////////////
// Fetch table at once, and also show how to
// use CppSQLiteTable::setRow() method
//////////////////////////////////////////////////////////////////
cout << endl << "getTable() test" << endl;
CppSQLite3Table t = db->getTable("select * from emp order by 1;");
for (fld = 0; fld < t.numFields(); fld++)
{
cout << t.fieldName(fld) << "|";
}
cout << endl;
for (int row = 0; row < t.numRows(); row++)
{
t.setRow(row);
for (int fld = 0; fld < t.numFields(); fld++)
{
if (!t.fieldIsNull(fld))
cout << t.fieldValue(fld) << "|";
else
cout << "NULL" << "|";
}
cout << endl;
}
////////////////////////////////////////////////////////////////////
// Test CppSQLiteBinary by storing/retrieving some binary data, checking
// it afterwards to make sure it is the same
//////////////////////////////////////////////////////////////////
cout << endl << "Binary data test" << endl;
db->execDML("create table bindata(desc char(10), data blob);");
unsigned char bin[256];
CppSQLite3Binary blob;
for (i = 0; i < sizeof bin; i++)
{
bin[i] = i;
}
blob.setBinary(bin, sizeof bin);
bufSQL.format("insert into bindata values ('testing', %Q);",
blob.getEncoded());
db->execDML(bufSQL);
cout << "Stored binary Length: " << sizeof bin << endl;
q = db->execQuery("select data from bindata where desc = 'testing';");
if (!q.eof())
{
blob.setEncoded((unsigned char*)q.fieldValue("data"));
cout << "Retrieved binary Length: "
<< blob.getBinaryLength() << endl;
}
const unsigned char* pbin = blob.getBinary();
for (i = 0; i < sizeof bin; i++)
{
if (pbin[i] != i)
{
cout << "Problem: i: ," << i << " bin[i]: "
<< pbin[i] << endl;
}
}
/////////////////////////////////////////////////////////
// Pre-compiled Statements Demo
/////////////////////////////////////////////////////////////
cout << endl << "Transaction test, creating " << nRowsToCreate;
cout << " rows please wait..." << endl;
db->execDML("drop table emp;");
db->execDML("create table emp(empno int, empname char(20));");
tmStart = time(0);
db->execDML("begin transaction;");
CppSQLite3Statement stmt = db->compileStatement(
"insert into emp values (?, ?);");
for (i = 0; i < nRowsToCreate; i++)
{
char buf[16];
sprintf(buf, "EmpName%06d", i);
stmt.bind(1, i);
stmt.bind(2, buf);
stmt.execDML();
stmt.reset();
}
db->execDML("commit transaction;");
tmEnd = time(0);
cout << db->execScalar("select count(*) from emp;")
<< " rows in emp table in ";
cout << tmEnd-tmStart << " seconds (that was even faster!)" << endl;
cout << endl << "End of tests" << endl;
}
catch (CppSQLite3Exception& e)
{
cerr << e.errorCode() << ":" << e.errorMessage() << endl;
}
}
