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; } }