hi, sorry for my late response, i was very busy the last few days (my wife was in hospital and i had to care about the kids, pets and house and still had to work :-) i updated the sql.h.bz2 [http://greschenz.dyndns.org/downloads/sql.h.bz2] on my server and fixed that topic. you can now use multiple statements with one db. i also included a little sqlite-benchmark. i'm really very impressed: i'm able to make up to 800000 inserts/secon my notebook !!! well, the notebook is really a good one, but on big desktops/servers it should be easily possible to beat the 1 mio/sec !!! 1) if anyone tries the benchmark please let me know the results (including cpu-type / os) 2) has anyone some ideas to speed up this stuff ? i've no good reason right now, i'm just curious :-) i hope the code is at least a good starting point for all beginners here ! have fun, gg
Sam Carleton <[email protected]> hat am 22. Juni 2010 um 16:20 geschrieben: > gg, > > I looked over it real quick, it looks good. One comment I have is that both > the db and the stmt are in the same class, this prevents a user from having > multiple stmt's at one time. A few times in my code I prepare two different > types of inserts and then execute the correct one at runtime. So my thought > is to have SBS::prepare() return a new stmt type of class. What are there > some pit falls to such an approach? > > Sam > > On Tue, Jun 22, 2010 at 3:54 AM, [email protected] <[email protected]> wrote: > > > hi, > > > > > > > > i tried to make my code more easy to understand. > > > > hope it helps :-) > > > > it would be nice to get some feedback (positive or negative, both are > > welcome) > > > > > > > > http://greschenz.dyndns.org/downloads/sql.h.bz2 > > > > > > > > btw. i still have sqlite compression/crypting for download here: > > > > > > > > http://greschenz.dyndns.org/downloads/sqlite_compress.zip< > > http://greschenz.dyndns.org/downloads/sql.h.bz2 > > > > > > > http://greschenz.dyndns.org/downloads/sqlite_crypt.zip< > > http://greschenz.dyndns.org/downloads/sql.h.bz2 > > > > > > > > > > > if anyone needs it :) > > > > > > > > have fun, gg > > > > > > Sam Carleton <[email protected]> hat am 21. Juni 2010 um 17:23 > > geschrieben: > > > > > gg, > > > > > > I am needing to start tweaking my code to support two DBs, SQLite and > > > Firebird. After a quick, very quick, glance at your 100 lines, it > > > looks like it might be a good starting point for me. I was wondering > > > if you might be willing to share with me a copy that isn't so compact > > > and is better formatted. Maybe if you could zip/tar/etc the file? I > > > would really appreciate it! > > > > > > Sam > > > > > > On Mon, Jun 21, 2010 at 9:04 AM, [email protected] <[email protected]> > > > wrote: > > > > hi, > > > > > > > > i've written a small (<100 lines) c++ wrapper for sqlite. > > > > it supports: > > > > *) prepared statements > > > > *) NULL > > > > *) BLOBs > > > > > > > > here is a small usage example: > > > > > > > > > > > > > > ---------------------------------------------------------------------------- > > ---- > > > > ---------------------------------------SDB sdb("test.sdb"); > > > > sdb.exec("drop table test"); > > > > sdb.exec("create table test (x, y, z)"); > > > > sdb.exec("create index testXIdx on test (x)"); > > > > sdb.exec("create index testYIdx on test (y)"); > > > > sdb.exec("create index testZIdx on test (z)"); > > > > SDB::Statement insert(sdb, "insert into test values(?, ?, ?)"); > > > > insert.exec("hello", 42, "world"); > > > > insert.exec("hello", "world", 42); > > > > char *buf = (char *)malloc(1024*1024); > > > > memset(buf, 0, 1024*1024); > > > > insert.exec("hello", SDB::null(), SDB::Blob(buf, 1024*1024)); > > > > free(buf); > > > > SDB::Statement select(sdb, "select x, y, z from test where x = ? order > > by x, y, > > > > z"); > > > > if (select.exec("hello") == SQLITE_ROW) > > > > { > > > > do > > > > { > > > > const char *x = select.getText(0); > > > > int y = select.getInt(1); > > > > const char *y2 = select.getText(1); > > > > SDB::Blob z = select.getBlob(2); > > > > const char *z2 = select.getText(2); > > > > printf("x=%s, y=%d(%s), z=0x%08lx/%d(%s)\n", x, y, y2, z.m_data, > > z.m_len, z2); > > > > } > > > > while (select.step() == SQLITE_ROW); > > > > } > > > > > > ---------------------------------------------------------------------------- > > ---- > > > > --------------------------------------- > > > > > > > > > > > > > > > > and here is the code (no download, just inline, i hope its small enough > > :-) > > > > > > ---------------------------------------------------------------------------- > > ---- > > > > --------------------------------------- > > > > > > > > #include "sqlite3.h" > > > > > > > > > > > > #include <stdlib.h> > > > > #include <stdio.h> > > > > #include <string.h> > > > > > > > > > > > > #include <string> > > > > using namespace std; > > > > > > > > > > > > class SDB > > > > { > > > > public: > > > > struct Null {}; > > > > static Null null() { return Null(); } > > > > > > > > > > > > struct Blob > > > > { > > > > const void *m_data; > > > > int m_len; > > > > Blob(const void *data=NULL, int len=0): m_data(data), m_len(len) {} > > > > }; > > > > > > > > > > > > SDB(const char *fname) > > > > { > > > > check(sqlite3_open(fname, &sdb)); > > > > } > > > > ~SDB() { check(sqlite3_close(sdb)); } > > > > > > > > > > > > class Statement > > > > { > > > > public: > > > > Statement(SDB &db, const char *query=NULL) : sdb(db), sqlst(NULL) { > > > > prepare(query); } > > > > ~Statement() { if (sqlst) check(sqlite3_finalize(sqlst)); } > > > > int prepare(const char *query) { if (!query) return SQLITE_ERROR; int > > ok = > > > > sqlite3_prepare_v2(sdb.sdb, query, strlen(query), &sqlst, NULL); > > check(ok, > > > > query); return ok; } > > > > int step() { int ok = sqlite3_step(sqlst); check(ok); return ok; } > > > > int exec() { int ok = reset(); if (ok != SQLITE_OK) return ok; return > > step(); } > > > > template<typename T1> int exec(T1 v1) { int ok = reset(); if (ok != > > SQLITE_OK) > > > > return ok; bind(1, v1); return step(); } > > > > template<typename T1, typename T2> int exec(T1 v1, T2 v2) { int ok = > > reset(); if > > > > (ok != SQLITE_OK) return ok; bind(1, v1); bind(2, v2); return step(); } > > > > template<typename T1, typename T2, typename T3> int exec(T1 v1, T2 v2, > > T3 v3) { > > > > int ok = reset(); if (ok != SQLITE_OK) return ok; bind(1, v1); bind(2, > > v2); > > > > bind(3, v3); return step(); } > > > > template<typename T1, typename T2, typename T3, typename T4> int > > exec(T1 v1, T2 > > > > v2, T3 v3, T4 v4) { int ok = reset(); if (ok != SQLITE_OK) return ok; > > bind(1, > > > > v1); bind(2, v2); bind(3, v3); bind(4, v4); return step(); } > > > > template<typename T1, typename T2, typename T3, typename T4, typename > > T5> int > > > > exec(T1 v1, T2 v2, T3 v3, T4 v4, T5 v5) { int ok = reset(); if (ok != > > SQLITE_OK) > > > > return ok; bind(1, v1); bind(2, v2); bind(3, v3); bind(4, v4); bind(5, > > v5); > > > > return step(); } > > > > template<typename T1, typename T2, typename T3, typename T4, typename > > T5, > > > > typename T6> int exec(T1 v1, T2 v2, T3 v3, T4 v4, T5 v5, T6 v6) { int > > ok = > > > > reset(); if (ok != SQLITE_OK) return ok; bind(1, v1); bind(2, v2); > > bind(3, v3); > > > > bind(4, v4); bind(5, v5); bind(6, v6); return step(); } > > > > template<typename T1, typename T2, typename T3, typename T4, typename > > T5, > > > > typename T6, typename T7> int exec(T1 v1, T2 v2, T3 v3, T4 v4, T5 v5, > > T6 v6, T7 > > > > v7) { int ok = reset(); if (ok != SQLITE_OK) return ok; bind(1, v1); > > bind(2, > > > > v2); bind(3, v3); bind(4, v4); bind(5, v5); bind(6, v6); bind(7, v7); > > return > > > > step(); } > > > > template<typename T1, typename T2, typename T3, typename T4, typename > > T5, > > > > typename T6, typename T7, typename T8> int exec(T1 v1, T2 v2, T3 v3, T4 > > v4, T5 > > > > v5, T6 v6, T7 v7, T8 v8) { int ok = reset(); if (ok != SQLITE_OK) > > return ok; > > > > bind(1, v1); bind(2, v2); bind(3, v3); bind(4, v4); bind(5, v5); > > bind(6, v6); > > > > bind(7, v7); bind(8, v8); return step(); } > > > > template<typename T1, typename T2, typename T3, typename T4, typename > > T5, > > > > typename T6, typename T7, typename T8, typename T9> int exec(T1 v1, T2 > > v2, T3 > > > > v3, T4 v4, T5 v5, T6 v6, T7 v7, T8 v8, T9 v9) { int ok = reset(); if > > (ok != > > > > SQLITE_OK) return ok; bind(1, v1); bind(2, v2); bind(3, v3); bind(4, > > v4); > > > > bind(5, v5); bind(6, v6); bind(7, v7); bind(8, v8); bind(9, v9); return > > step(); > > > > } > > > > template<typename T1, typename T2, typename T3, typename T4, typename > > T5, > > > > typename T6, typename T7, typename T8, typename T9, typename T10> int > > exec(T1 > > > > v1, T2 v2, T3 v3, T4 v4, T5 v5, T6 v6, T7 v7, T8 v8, T9 v9, T10 v10) { > > int ok = > > > > reset(); if (ok != SQLITE_OK) return ok; bind(1, v1); bind(2, v2); > > bind(3, v3); > > > > bind(4, v4); bind(5, v5); bind(6, v6); bind(7, v7); bind(8, v8); > > bind(9, v9); > > > > bind(10, v10); return step(); } > > > > Null getNull(int i=0) { return null(); } > > > > Blob getBlob(int i=0) { return Blob(sqlite3_column_blob(sqlst, i), > > > > sqlite3_column_bytes(sqlst, i)); } > > > > int getInt(int i=0) { return sqlite3_column_int(sqlst, i); } > > > > sqlite3_int64 getInt64(int i=0) { return sqlite3_column_int64(sqlst, > > i); } > > > > double getDouble(int i=0) { return sqlite3_column_double(sqlst, i); } > > > > const char *getText(int i=0) { return (const char > > *)sqlite3_column_text(sqlst, > > > > i); } > > > > private: > > > > SDB& sdb; > > > > sqlite3_stmt *sqlst; > > > > void check(int ok, const char *stmt=NULL) { sdb.check(ok, stmt); } > > > > int reset() { int ok = sqlite3_reset(sqlst); check(ok); return ok; } > > > > void bind(int i, const Null& null) { check(sqlite3_bind_null(sqlst, > > i)); } > > > > void bind(int i, const Blob& blob) { if (blob.m_data) > > > > check(sqlite3_bind_blob(sqlst, i, blob.m_data, blob.m_len, > > SQLITE_TRANSIENT)); > > > > else check(sqlite3_bind_zeroblob(sqlst, i, blob.m_len)); } > > > > void bind(int i, int v) { check(sqlite3_bind_int(sqlst, i, v)); } > > > > void bind(int i, sqlite3_int64 v) { check(sqlite3_bind_int64(sqlst, i, > > v)); } > > > > void bind(int i, double v) { check(sqlite3_bind_double(sqlst, i, v)); } > > > > void bind(int i, const string& v) { check(sqlite3_bind_text(sqlst, i, > > v.c_str(), > > > > -1, SQLITE_TRANSIENT)); } > > > > void bind(int i, const char *v, int n=-1) { > > check(sqlite3_bind_text(sqlst, i, v, > > > > n, n==-1?SQLITE_TRANSIENT:SQLITE_STATIC)); } > > > > }; > > > > > > > > > > > > int exec(const char *query) { return Statement(*this, query).exec(); } > > > > string execStr(const char *query) { Statement stmt(*this, query); > > stmt.exec(); > > > > const char *rc = stmt.getText(0); return rc?rc:""; } > > > > > > > > > > > > private: > > > > sqlite3 *sdb; > > > > void check(int ok, const char *stmt=NULL) > > > > { > > > > if (ok != SQLITE_OK && ok != SQLITE_DONE && ok != SQLITE_ROW) > > > > { > > > > const char *msg = sqlite3_errmsg(sdb); > > > > if (strcmp("not an error", msg)) > > > > { > > > > if (stmt) fprintf(stderr, "sqlite-check(%d:%s) \"%s\"\n", ok, msg, > > stmt); > > > > else fprintf(stderr, "sqlite-check(%d:%s)\n", ok, msg); > > > > } > > > > } > > > > } > > > > }; > > > > > > ---------------------------------------------------------------------------- > > ---- > > > > --------------------------------------- > > > > > > > > > > > > have fun, bye, gg > > > > _______________________________________________ > > > > sqlite-users mailing list > > > > [email protected] > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
