(original) SQLite packaging library SmartDB1.3 released

It's been a little bit empty lately. Smartdb has been upgraded, and SmartDB1.3 has been simplified for ease of use and flexibility compared to previous versions.
Smartdb to SQLite a layer of encapsulation, shielding a lot of details, making us easy to use. It also combines performance and flexibility with a focus on ease of use.

Ease of Use

Interface is very simple, the basic interface has the following several:

Void Open(const string& fileName);
Bool Close();

/ / Database operation interface
Template <typename... Args>
Bool Excecute(const string& sqlStr, Args && ... args);

/ / Return a value, such as performing a simple aggregation function
Template < typename R = sqlite_int64, typename... Args>
R ExecuteScalar(const string& sqlStr, Args&&... args);

/ / Query interface
Template <typename... Args>
Std::shared_ptr<rapidjson::Document> Query(const string& query, Args&&... args);

//transaction
Bool Begin();
Bool RollBack();
Bool Commit(); 

With these interfaces, you can do all the things. Let's look at a simple example:

 
 
void Test()
{
    SmartDB db;
    db.Open("test.db");

    const string sqlcreat = "CREATE TABLE if not exists PersonTable(ID INTEGER NOT NULL, Name Text, Address BLOB);";

    if (!db.Excecute(sqlcreat))
        return;

    const string sqlinsert = "INSERT INTO PersonTable(ID, Name, Address) VALUES(?, ?, ?);";
    int id = 2;
    string name = "Peter";
    string city = "zhuhai";
    blob bl = { city.c_str(), city.length() + 1 };

    if (!db.Excecute(sqlinsert, id, "Peter", nullptr))
        return;
}

The above example creates a database and a data table, and inserts a row of records, all done by Excecute, is not very simple.

Flexibility

The database interface has been enhanced so that we can receive tuple and JSON, because sometimes we need to save the value to save, when needed to save to the database, then we need a vector to save the data beforehand, here through the JSON or tuple is the most appropriate. Look at the tuple and JSON interfaces:

template<typename Tuple>
bool ExcecuteTuple(const string& sqlStr, Tuple&& t);

bool ExcecuteJson(const string& sqlStr, const char* json);

It's very similar to the excecute above. Look at their usage:

 
db.ExcecuteTuple(sqlinsert, std::forward_as_tuple(id, "Peter", bl));

The JSON interface is a little bit more complicated, and here we use the Rapidjson. Its usage:

 
void TestJson(SmartDB& db, const string& sqlinsert)
{
    rapidjson::StringBuffer buf;
    rapidjson::Writer<rapidjson::StringBuffer> writer(buf);
    writer.StartArray();
    for (size_t i = 0; i < 10; i++)
    {
        writer.StartObject();
        writer.String("ID");
        writer.Int(i + 1);

        writer.String("Name");
        writer.String("Peter");

        writer.String("Address");
        writer.String("Zhuhai");
        writer.EndObject();
    }
    writer.EndArray();

    auto r = db.ExcecuteJson(sqlinsert, buf.GetString());
}

It's a bit tedious to write a JSON object above, it doesn't matter, it can be simplified by my encapsulated jsoncpp:

Void TestJson(SmartDB& db, const string& sqlinsert)
{
     / / Here to simplify the creation of json objects by jsoncpp wrapper class
     JsonCpp jcp;
     jcp.StartArray();
     For (size_t i = 0; i < 10; i++)
     {
         jcp.StartObject();
         jcp.WriteJson("ID", i);
         jcp.WriteJson("Name", "Peter");
         jcp.WriteJson("Address", "zhuhai");
         jcp.EndObject();
     }
     jcp.EndArray();

     Auto r = db.ExcecuteJson(sqlinsert, buf.GetString());
} 

Flexibility

Put the query results directly into the JSON object, avoid the physical table needs and business entity one by one corresponding problem, the underlying physical table even if modified, the database layer does not need to modify, is stable, because the result of the query is a JSON object, it is itself a self-describing structure, not concerned about the underlying data table changes. Another benefit of returning JSON is that I can transmit the JSON string over the network, or directly to other languages, because JSON is a standard format, so it can be easily identified by different languages, it's not just the result of a query or a link to another language or system, it's very flexible. Take a look at its basic usage:

Auto p = db. Query ("select * from testinfotable");

The return is a JSON object, and it's easy to access it:

Rapidjson::Document& doc = *p;
     For (size_t i = 0, len = doc.Size(); i < len; i++)
     {
         For (size_t j = 0, size = doc[i].GetSize(); j < size; ++j)
         {
             //doc[i][j]; key-value pairs
         }
     }
Const char* json = doc.GetString(); //return json string

Performance

With transactions enabled, insert performance is high, and a seven-field table on a dual-core notebook can speed up to more than 200,000 rows per second. The query results are also faster and can reach speeds of 200,000 rows per second. Look at the code for the performance test:

Void TestPerformance()
{
     SmartDB db;
     db.Open("test.db");
     Const string sqlcreat = "CREATE TABLE if not exists TestInfoTable(ID INTEGER NOT NULL, KPIID INTEGER, CODE INTEGER, V1 INTEGER, V2 INTEGER, V3 REAL,

V4 TEXT);";
     If (!db.Excecute(sqlcreat))
         Return;

     Boost::timer t;
     Const string sqlinsert = "INSERT INTO TestInfoTable(ID, KPIID, CODE, V1, V2, V3, V4) VALUES(?, ?, ?, ?, ?, ?, ?);";
     Bool ret = db.Prepare(sqlinsert);
     db.Begin();
     For (size_t i = 0; i < 1000000; i++)
     {
         Ret = db.ExcecuteArgs(i, i, i, i, i, i + 1.25, "it is a test");
         If (!ret)
             Break;
     }

     If (ret)
         db.Commit(); //Submit transaction
     Else
         db.RollBack(); //rollback

     Cout << t.elapsed() << endl;
     T.restart();

     Auto p = db.Query("select * from TestInfoTable");

     Cout << t.elapsed() << endl;
     Cout << "size: " << p->Size() << endl;
} 

Dual-Core Notebooks display: 4.6s and 5s. This performance fully meets the usual development.

If you find that there are more concise, flexible and efficient SQLite packaging library Please let me know, if not please click on the recommendation, thank you. ^_^

SmartDBV1.03

Welcome to use, if you find a problem please give me feedback.

C++11 Boost Technical Exchange Group: 296561497, Welcome to Exchange technology.