Database Support

1

Hi,
Is it possible to use sqlite or anything similar to sqlite db ?

Thanks

2

I think it would be possible to use sqlite. If all you are trying to do is store data and you don’t need relational database features, you might want to look at a Legato feature called configTree. http://legato.io/legato-docs/latest/c_config.html

3

I have built a VOIP [linphone] stack and sqlite3 was a requirement easily built and implemented as a dependency for linphone.

I build it and install it into the sysroot then install it into a build directory that i copy to the app tree and include in the adef files.

run ./configure with CC=$(CC) CXX=$(CXX) --host=arm-poky-linux-gnueabi
CC/CXX
${WP85_TOOLCHAIN_DIR}/arm-poky-linux-gnueabi-[gcc|g++]

4

See the make file bits i pasted for a better idea

5

Hi David,
In my application i have to store minimum 10,000 data point and each datapoint size is 512 bytes and want to store in binary format. is there any other way to write and read the data in flash memory efficiently rather than using configTree?
Thanks

6

I think you’re right that configTree won’t meet your needs. What is the data like? Is it a 512 byte binary blob with a key or is it a complex record with many fields that you may want to perform queries based on?

7

Hi David,
Thanks for your response.
Yes it is 512 byte binary data frame (in HEX) and each frame have unique ID in the header. Query based not very necessary.
Is there any api available to read / write into internal flash memory?

Thanks
Niladri

8

If you are writing a sandboxed app, then that app should be able to read and write files. Since the app is sandboxed, then only the app that wrote the files will really have access to them.

Another option would be to try to get a nosql style data store running on the mangOH. I know very little about those systems though, so I can’t really help you out.

Which option is best really depends on how you will be using the data.

9

@niladridm, how often do you want to write these binary data frames? Be careful of flash wear. You don’t want to burn out your flash device.

You can use the regular POSIX file system APIs (open/lseek/read/write/close or fopen/fseek/fread/fwrite/fclose) to store your data frames in a file in your flash file system. But, remember to set your process’s maximum file size limit (maxFileBytes in the “processes:” section of your .adef file) http://legato.io/legato-docs/latest/defFilesAdef.html#defFilesAdef_processMaxFileBytes

10

Hi Jen,
Thank you for your input. Yes currently I am using POSIX file system but the problem I am facing is, after power restart my data file is getting deleted. I am creating the file in tmp directory(may be this the reason). May be I should use sandboxed to create the file in app dir.
And data writing frequency is 1 sec and it should override after 10,000 record.

Thanks

12

Yes, /tmp is a RAM file system (tmpfs), so anything in there will be lost if the device is rebooted.

13

Hi All ,

This reply explains how to use third party library sqlite for database support
Here how you can do this.
We can use config tree to store small chunks of data .
As mentioned in above posts.
Whenever , large chunks of data are involved , it is better to create database
using sqlite3 library.

Steps to use sqlite3 API’s in legato applications
1.get the sqlite3 (third party library)
this will have
inc lib src

build your legato system update ‘make wp85’

2.make a folder in your legato root and copy sqlite3 in it
mkdir third-party
now , you have following path where sqlite3 is present
${LEGATO_ROOT}/third-party/sqlite3

  1. add this path in your application .adef file as
    bundles:
    {
    file:
    {
    [r] third-party/sqlite3/lib/sqlite3.so /lib/
    }

}

4.include this in your source
#include <sqlite3.h>

5.Either build as
mkapp -t wp750x testdata.adef -i “${LEGATO_ROOT}/third-party/sqlite3/lib/third-party/sqlite3/inc” -L “${LEGATO_ROOT}/third-party/sqlite3/lib/sqlite3.so”

or add in your Makefile

6.Legato Application using sqlite3 (3rd Party Library) Application can be installed on the target.

Regards
Ankita

14

Hi Ankita this is great! I want to follow the steps shared by you but have few doubts below

  1. where shall i get the sqlite3 (third party library) any link?
  2. How to build legato system update with sqlite3. (steps are not clear to me)
  3. any small example source code to write and read data from sqlite db

Thanks

15

Hi Nila ,
Please download the Source code present
https://sqlite.org/download.html
i.e sqlite-amalgamation-3190300.zip

  1. Make a folder structure for Sqlite3 as
    mkdir third-party
    cd third-party
    mkdir inc
    mkdir src
    mkdir lib

  2. Copy sqlite3.h in third-party/inc/
    sqlite3.c in third-party/src/

  3. Create a Makefile in path third-party/src/
    vi Makefile
    add following lines

    export TOOLCHAIN_DIR:=/opt/swi/y17-ext/sysroots/x86_64-pokysdk-linux/usr/bin/arm-poky-linux-gnueabi
    GCC = ${TOOLCHAIN_DIR}/arm-poky-linux-gnueabi-gcc
    all:
    ${GCC} -c -Wall -Werror -fpic -lpthread -ldl sqlite3.c -o …/lib/sqlite3.so

  4. Check for sqlite3.so file in the path third-party/lib/

Now you can use the library in your App Bundles.

CREATING LEGATO APPLICATION USING SQLITE3

In Path apps/sample
mkdir testsql

The directory structure should contain

  1. third-party folder containing sqlite3 lib inc and src.
    2.Application Defination file testsql.adef

    executables:
    {
    testsql = (testsqlComp)
    }
    bundles:
    {
    file:
    {
    [r] third-party/sqlite3/lib/sqlite3.so /lib/
    }
    }
    processes:
    {
    run:
    {
    (testsql)
    }
    }

  2. Component directory testsqlComp

4.cd testsqlComp
a) Create a Component.cdef file here as shown below
vi Component.cdef
sources:
{
testsql.c
}

b) vi testsql.c

Dont forget to include
legato.h and sqlite3.h
5.
/**************************************************************************
*Function:COMPONENT_INIT
****************************************************************************/
COMPONENT_INIT
{
LE_INFO(“Sample to use Sqlite3 in Legato”);
//Implement functionalities
}
Snippets to read/Write data using sqlite3
make sure you define macro with path where you want to create database
NEW_DATABASE “/data/sql.db”

void createDb()
{
		int rc ;
		rc = sqlite3_open(NEW_DATABASE, &db);
		if(!rc){
           		LE_INFO("Successfully Created ");
       		}
		else {
          		LE_INFO("Error in Creating/Opening Db file");
     		}
}

//This function is for creating Table
void initOperation()
{
char *sql;
char *zErrMsg = 0;
createDb();
if(db)
{
LE_INFO(“InitOperations Command For Table Creation”);
sql = “create table if not exists test_sqlite(ID int Primary\ key, valuename text , value int)”;
int rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
if(!rc){
LE_INFO(“Executed Command For Table Creation”);
}
}
}

//Inserting Data into sql.db

	void insertData(sqlite3 *db, int farg, const char *name, int val)
	{
			if (!db)
			return;
			LE_INFO("Insert Command");
			const char *zErrMsg ;
			sqlite3_stmt *stmt;
			char *SQL;
		// Insert data item into myTable
			SQL = "insert into test_sqlite(ID,valuename,value)\
           		values (?,?,?)";
			int rc = sqlite3_prepare(db, SQL, strlen(SQL), &stmt, &zErrMsg);
			if( rc == SQLITE_OK ) 
		{
				// bind the value
  				sqlite3_bind_int(stmt, 1, farg);
  				sqlite3_bind_text(stmt, 2, name, strlen(name), 0);
  				sqlite3_bind_int(stmt, 3, val);
  				// commit
  				sqlite3_step(stmt);
  				sqlite3_finalize(stmt);
 			}   
	}

//display data

	void selectAll()
	{
		char *sql;
		char *zErrMsg = 0;
  			int rc ;
  			createDb();
  			LE_INFO("Display All **************************");
  			sql = "select * from test_sqlite";
  			rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
  			if(rc != SQLITE_OK)
   			 {
      			LE_INFO("Error Executing Command--No Record to show");
    		}
    		else
    		{
            		LE_INFO("Select is success");
    		}
    		closeDB();
	}

//Callback required by sqlite3_exec in selectAll

static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
LE_INFO(“Insert Command”);
int i;
for(i=0; i<argc; i++)
{
fprintf(stdout,"%s = %s\n", azColName[i], argv[i] ? argv[i] : “NULL”);
}
return 0;
}

building application is explained in the previous reply.

Regards
Ankita

1 Like
16

Thats awesome Ankita.

Can you add this to the mangOH github for others to use?

1 Like
17

Hi Ashish ,

Yes , I can add that to mangoH github and will share the link.

Regards
Ankita

1 Like
18

hi David,
I am testing config tree api using a test application where i am writing a dummy data string of 500 bytes every 1 second but after 6000 records my WP8548 module get restarted and crashed, rebooting continuously. In my hardware i dont have serial so not able to restore the module in working condition using ssh as it is rebooting continuously. I want to know all the limitations of the config tree api to use this more efficiently. also please let me know is there any other way i can fix my wp module?

Thanks

19

Hi Ankita
i have an issue which i am not able to fix. I would be grateful if you could look into my attached code (just to create a db file) and help me on this.
I am getting below exception:
=ERR= | testsql[1282] | testsql: error while loading shared libraries: libsqlite3.so.0: cannot open shared object file: No such file or directory

Thanks

20

If it seems difficult one can use TinyDB as data base. it is pure python based DB.
it gives similar api to mysqldb after using some plugins.

21

Hi Nila ,

This is due to the linking error of sqlite3.so .
Please make following change and append the below in your Makefile
-i sqlite3/inc
-L “sqlite3/lib/sqlite3.so”
Also , if you are linking this way , ldflags in .cdef is not required.

The issue gets resolved by this.

Regards
Ankita