Implement SQLite across all platforms

He Xiaojie Dev (Senior Android architect) Copyright belongs to the author, please contact the author for authorization.

SQLite is a cross-platform database, so what’s the point?

No, I’ve been working on a project that requires SQLite databases, and I don’t even want to spend multiple sets of code on different platforms, since each platform includes different SDKS. For a simple example, to use SQLite on Android, you need to use the SQLiteDatabase class to operate in Java. On iOS, libsqlite3. TBD and sqlite3.h are required to operate using Objective-C. On PC, sqlite3.h is used as the main function, but there are still some inconsistency. For example, there are a wide variety of programming languages, most of which have different packages and inconsistent apis that can cause headaches.

Therefore, different code is bound to be used to operate SQLite on different platforms. Of course, with the exception of SQLite, it may be common practice to use different code on different platforms for the same functionality.

Please enter the title bcdef

Roll your eggs for normal! As a lazy person, when such a pot needs to be carried on one’s own, it is natural to look for simpler solutions. The goal is a set of code to go around the world!

Please enter the title abcdefg

There are a lot of techniques to choose from, such as C++. Sqlite3. H is quite useful. But I stuck with my favorite CodeTyphon because it had a more convenient encapsulation.

Luckily, CodeTyphon already comes with the SQlite3CONN unit, which can be referenced directly. For information on how to find referable libraries, look at the typhon-ide Pkgs and FPC Pkgs pages of CTC and you will find what you are looking for.

CTC

Let’s start with a simple database to test if the code works:

$ sqlite3 demo.db

> create table user(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(32) NOT NULL);

> insert into user(name) value ('ABC');

> insert into user(name) value ('XYZ');Copy the code

Then declare a structure based on the database structure, which will later be used for data passing:

type

  TDemoRec = record

    AId: Integer;

    AName: PChar;

  end;Copy the code

The C++ equivalent of this construct looks like this:

struct DemoRec {

    int AId;

    char* AName;

};Copy the code

Now that we can see how easy it is to use SQLite, I’ve defined a class that holds some data:

TSQLite = class

  private

    FDatabase: TSQLite3Connection;

    FQuery: TSQLQuery;

    FTransaction: TSQLTransaction;

  published

    property Database: TSQLite3Connection read FDatabase write FDatabase;

    property Transaction: TSQLTransaction read FTransaction write FTransaction;

    property Query: TSQLQuery read FQuery write FQuery;

  end;Copy the code

Once you have these things, you can easily play with them, such as executing a SQL statement:

function TSQLite.ExecuteSQL(ASQL: string): Boolean;

begin

  FQuery.Close;

  FQuery.SQL.Text:= ASQL;

  try

    FQuery.ExecSQL;

    Exit(True);

  except

    Exit(False);

  end;

end;Copy the code

This code seems too simple, and perhaps we’d rather have a reason why something went wrong, so we can change it:

function TSQLite.ExecuteSQL(ASQL: string; var AError: string): Boolean;

begin

  FQuery.Close;

  FQuery.SQL.Text:= ASQL;

  try

    FQuery.ExecSQL;

    Exit(True);

  except

    on E: Exception do begin

      AError:= e.Message;

      Exit(False);

    end;

  end;

end;Copy the code

Now, when you call this method, you just pass in an extra string argument to get the information when something went wrong.

In this system, it is also easy to query and encapsulate two additional methods:

// Query according to SQL statement

function TSQLite.Select(ASQL: string; var AError: string): Boolean;

begin

  FQuery.Close;

  FQuery.SQL.Text:= ASQL;

  try

    FQuery.Open;

    Exit(True);

  Except

    on E: Exception do begin

      AError:= e.Message;

      Exit(False);

    end;

  end;

end; 



// Get the number of rows in the query result

function dbGetSelectResultCount(APath: PChar): Integer;

var

  database: TSQLite;

begin

  Result := -1;

  if (DatabaseExists(string(APath))) then begin

    database := GetDatabase(string(APath));

    Result := database.Query.RecordCount;

  end;

end;



// Get a record of the specified line number

function dbGetSelectResult(APath: PChar; AIndex: Integer): TDemoRec;

var

  database: TSQLite;

  tmp: string;

begin

  Inc(AIndex);

  if (DatabaseExists(string(APath))) then begin

    database := GetDatabase(string(APath));

    if (database.Query.RecordCount >= AIndex) then begin

      database.Query.RecNo:= AIndex;

      Result.AId:= database.Query.FieldByName('id').AsInteger;

      tmp := database.Query.FieldByName('name').AsString;

      Result.AName:= StrAlloc(tmp.Length);

      strcopy(Result.AName, PChar(tmp));

    end;

  end;

end;Copy the code

As a cross-platform library, it needs to be called by other programs, so there must be an export function, and different platforms, the required function form is different, especially since Android uses JNI to call the dynamic library, the export function must comply with the JNI specification.

The following example is a good example of how to export functions:

// iOS, PC

function dbGetSelectResultCount(APath: PChar): Integer; cdecl;

function dbGetSelectResult(APath: PChar; AIndex: Integer): TDemoRec; cdecl;



// Android

function Java_com_sqlite_sample_NativeAPI_dbGetSelectResultCount(env: PJNIEnv; obj: jobject; APath: jstring): jint; stdcall;

function Java_com_sqlite_sample_NativeAPI_dbGetSelectResult(env: PJNIEnv; obj: jobject; APath: jstring; AIndex: jint): jobject; stdcall;Copy the code

The only thing to note is the call protocol, which must be set to STDCall for JNI and cDECL for everything else.

So the next step is to compile, directly use the FPC cross-platform compiler, the compilation method is very simple:

$ fpc64 -Fisqlite -Fusqlite sample.lprCopy the code

Libsample. dylib can be generated on Mac and libsample.so can be generated on Linux.

Cross-platform compilation is a bit trickier, but much simpler than you might think:

$export ANDROID_LIB = / usr/local/codetyphon/binLibraries/android - 5.0 - api21 - arm /

$ export FPC=/usr/local/codetyphon/fpc/fpc64/bin/x86_64-linux/fpc

$ ${FPC} -Tandroid -Parm -Fl${ANDROID_LIB} -Fiqslite -Fusqlite sample.lprCopy the code

At this point, you can generate libsample.so for the Android system based on arm architecture. By changing the parameter after -p, you can also compile so for x86, MIPS and other architectures.

Once you’re done, take a look at how the iOS library is compiled. Since iOS no longer allows dynamic loading of dylib, we had to compile the code into static libraries, a.a files, statically linked into iOS projects.

$export FPC_ROOT = / usr/local/lib/FPC / 3.1.1

$ export FPC=${FPC_ROOT}/ppcrossa64

$ ${FPC} -Tdarwin -dIPHONEALL -Cn -Fisqlite -Fusqlite sample.lpr

$ ar -q libsample.a `grep "\.o$" link.res`

$ ranlib libsample.aCopy the code

At this point you get a libsample.a file for the 64-bit real machine, and two more.a files must be compiled for compatibility on 32-bit iOS and emulators.

32-bit true: Replace the compiler with PPCrossarm

Emulator: Replace the compiler with PPCX64 and replace the -t argument with iphonesimCopy the code

When we have.a with three different schemas, sometimes we need to merge them, using the following command to merge them:

lipo -create libsample_A64.a libsample_ARM.a libsample_EMU.a -output libsample.aCopy the code

The result is a fused.A, which can be used in a variety of situations.

Now that we’re all set, let’s take a look at how to use our libraries, using dbGetSelectResultCount and dbGetSelectResult as examples for each platform.

Android:

package com.sqlite.sample;



public class NativeAPI {

    static {  System.loadLibrary("sample"); }

    public static native int dbGetSelectResultCount(String APath);

    public static native DemoRec dbGetSelectResult(String APath, int AIndex);

}Copy the code

IOS:

extern int dbGetSelectResultCount(const char* APath);

extern struct DemoRec dbGetSelectResult(const char* APath, int AIndex);

PC (C++ as an example) :

typedef int (*dbSelectResultCount)(const char* APath);

typedef struct DemoRec (*dbSelectResult)(const char* APath, int AIndex);



void* handle = dlopen("./libsample.so", RTLD_LAZY);

dbSelectResultCount mSelectResultCount = (dbSelectResultCount) dlsym(handle, "dbGetSelectResultCount");

dbSelectResult mSelectResult = (dbSelectResult) dlsym(handle, "dbGetSelectResult");Copy the code

As you can see, the resulting API is consistent regardless of platform, thus unifying the invocation. On this basis, to do secondary packaging is also very convenient. In addition, since there is almost no code coupling, it is easy to change the logic of SQLite’s underlying library without affecting the upper level calls as long as the API remains unchanged.

The following is a complete call code, taking the iOS terminal as an example, the other terminals are the same:

// Copy the database file

NSString * originPath = [[NSBundle mainBundle] pathForResource:@"demo" ofType:@"db"];

NSString * destPath = [ViewController getDocumentPath];

NSString * dbFile = [destPath stringByAppendingPathComponent:@"demo.db"];

[ViewController copyFile:originPath destFile:dbFile];



// Open the database

int b = dbOpen([dbFile UTF8String]);

printf("Open Database => %d\n", b);

// Execute the query

b = dbSelect([dbFile UTF8String], "select * from user");

printf("Select => %d\n", b);

// Get the number of rows in the query result

int count = dbGetSelectResultCount([dbFile UTF8String]);

printf("Select Rows => %d\n", count);

// retrieve each item of data

for (int i = 0; i < count; i++) {

    struct DemoRec r = dbGetSelectResult([dbFile UTF8String], i);

    printf("Data %d => {id => %d, name => %s}\n", i, r.AId, r.AName);

}

// Close the database

b = dbClose([dbFile UTF8String]);

printf("Close Database => %d\n", b);Copy the code

The output of this code is:

As you can see, the call succeeds and the data is passed correctly. The effect is exactly the same on other platforms.

The demo project is open source and can be accessed on Github:

https://github.com/rarnu/cross_sqlite