"UPSERT " - Update or Insert in Sqlite

"INSERT OR REPLACE" is NOT equal to "UPDATE IF EXIST ,ELSE INSERT"
Since REPLACE is actually and "delete + create" action, not update.
Users need to insert values for ALL columns.

This thread on StackOverFlow provided a better solution to this issue.

Using UPDATE OR REPLACE together with SELECT and coalesce is an alternative to it.

Little Trick for Multiple Sql Commands by FMDB

Just consumed an hour for debugging a "fault" caused by putting a NSString contain multiple sql commands into a FMDB executeUpdate: function.

A question on StackOverFlow stated that FMDB doesn't support multiple commands in a single executeUpdate: call and I testified it by reading the code of FMDB.


Here is a little trick to do so:

    FMDatabase *db_ = [[FMDatabase alloc] initWithPath:yourPath];

    [db_ open];

    [db_ beginTransaction];
    
    NSArray *sqlCommands = [DATABASE_TEMPLATE componentsSeparatedByString:@";"];
    for (NSString *sql in sqlCommands)
    {
        [db_ executeUpdate:sql];
    }
    
    return [db_ commit];

It is particularly useful when creating many tables, and the string may come from some data files.

Installing SqlCipher, a Sqlite Encryption Tool, on Mac

Sqlite database file can be encrypted with the sqlite extension - SqlCipher. 
(Official encryption extension cost USD$2000)

SqlCipher is an open source project, could be cloned on git.
It is cross-platform and could be built on Unix/Linux/iOS/Android (and Windows?) since it is written by C.
Used some time to figure out what is going on, since I am not familiar with building terminal shell library. Writing it down should help me a lot in future.

The official iOS/Xcode tutorial is good enough for making it works, but it suggests to add sub-projects to the project which need its features that I think is not really necessary in my case, since this would need a much larger compile time when I clean-build my project.
After building the library files, just add them to my own project is fine.

However, I need command line support for convenience purpose.
It took me some time to install the command line sqlite3 with sqlcipher.

The description in the README of the git directory stated that:

This directory contains source code to 

    SQLite: An Embeddable SQL Database Engine

To compile the project, first create a directory in which to place
the build products.  It is recommended, but not required, that the
build directory be separate from the source directory.  Cd into the
build directory and then from the build directory run the configure
script found at the root of the source tree.  Then run "make".

For example:

    tar xzf sqlite.tar.gz    ;#  Unpack the source tree into "sqlite"
    mkdir bld                ;#  Build will occur in a sibling directory
    cd bld                   ;#  Change to the build directory
    ../sqlite/configure      ;#  Run the configure script
    make                     ;#  Run the makefile.
    make install             ;#  (Optional) Install the build products

My method is making a new folder named "bld ".
and type
"../sqlcipher-sqlcipher/configure --enable-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto""
"make"
"sudo make install"
 in Terminal.

And then go to /usr/local/bin, there would hv a newly built "sqlite3" file.
To use it directly in the Terminal, copy it to usr/bin (for safety, backup ur old version of sqlite3)


After that, you should see the version of sqlite version is updated and the sqlciper_export() function is supported.