Question about Practicality of Embedding SQLite on Cortex-M4 Processor

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Question about Practicality of Embedding SQLite on Cortex-M4 Processor

Obrien, John J
Hello,


My team is working on a project that involves transmitting sensor data from a data logger module to a mobile application via Bluetooth. I am interested in finding a relatively fast, reliable way to store the data that was collected by the data logger. Since we aren't guaranteed to always have a reliable Bluetooth connection between the mobile app and the data logger, we will need a way to mark which records are synchronized with the mobile application and which still haven't been sent up, so that the data logger can continue to collect data even when the mobile application is out of range. We collect data continuously at 4Hz, so I anticipate that we will have far too much data to use a flat file and manually traverse each record prior whenever the mobile application requests data.


I am very new to embedded programming (almost no prior experience) but have prior mobile application development experience. My first thought is to store the data in a SQLite database table and include one column called "IsSynchronized" that can store a boolean value to indicate which datapoints have been synced. When the mobile application requests data, the records where "IsSynchronized == false" will be transmitted to the mobile app. Once successful, the mobile app will let the data logger know that the transmission succeeded and set the IsSynchronized column to true for each synchronized record. This is how I would do it if the data were traveling from a mobile app to a server, but I don't know if this is a good idea for an embedded database to a mobile application.


Our data logger uses an Atmel-SAM4S microprocessor. We have 8GB of flash memory, so storage isn't an issue. Our RAM is very limited; we only have 160KB. We are working with an external vendor to design the data logger. They have developed an custom, handrolled operating system, but since it contains no Virtual File System, we aren't sure if SQLite will be an option for us. I've heard of other folks using uClinux or other Unix based Operating Systems on the SAM4S to accommodate SQLite (http://sqlite.1065341.n5.nabble.com/VFS-for-an-MCU-internal-flash-td83079.html), but I don't know how practical this is.


My question is this:

  1.  First, am I on the wrong track by pursuing an embedded database to solve my data synchronization issue?
  2.  Are there other embedded database alternatives that might make more sense in my scenario. I've heard a lot about NoSQL DBs like BerkleyDB and UnQlite, but I don't know is they are practical for my case either.
  3.  If we do need to implement a VFS, does it make sense to use a unix-based embedded operating system? If so, are there any recommendations as to which one may be a good fit for my hardware
  4.  Last, are we wasting our time with the SAM4S processor to solve this problem? We would like to take advantage of existing code, the nice power-consumption characteristics of the SAM4S, and would rather not have to go through the time and expense of upgrading our processor, but, if our processor choice is a non-starter, it would be good to know sooner rather than later.

To summarize, my question is regarding what direction I should ask the hardware vendor to take. Does it make sense for them to spend time optimizing the SAM4S for SQLite or should we consider another approach?



Thank you,

JJ OBrien
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about Practicality of Embedding SQLite on Cortex-M4 Processor

Bob Friesenhahn
If you really only have 160KB of RAM (vs 160MB), then that would be
prohibitive.  Linux and SQLite are not going to be able to run with
160KB of RAM.

Otherwise, it sounds like a fine idea.

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about Practicality of Embedding SQLite on Cortex-M4 Processor

Doug Currie-2
In reply to this post by Obrien, John J
On Fri, Mar 2, 2018 at 2:46 PM, Obrien, John J <[hidden email]> wrote:

> [...]
>
> To summarize, my question is regarding what direction I should ask the
> hardware vendor to take. Does it make sense for them to spend time
> optimizing the SAM4S for SQLite or should we consider another approach?
>

John, try web searching for "flash key value store." Look for one that
supports SD Card or eMMC from ARM Cortex-M4 (or -A5).

e
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about Practicality of Embedding SQLite on Cortex-M4 Processor

nomad
In reply to this post by Obrien, John J
I can't speak to your other questions, but I have a comment on your
first thoughts:

> but have prior mobile application development experience. My first
> thought is to store the data in a SQLite database table and include
> one column called "IsSynchronized" that can store a boolean value to
> indicate which datapoints have been synced. When the mobile
> application requests data, the records where "IsSynchronized ==
> false" will be transmitted to the mobile app. Once successful, the
> mobile app will let the data logger know that the transmission
> succeeded and set the IsSynchronized column to true for each
> synchronized record. This is how I would do it if the data were
> traveling from a mobile app to a server, but I don't know if this is
> a good idea for an embedded database to a mobile application.

The above implies something like the following:

    CREATE TABLE events(
        id    INTEGER NOT NULL PRIMARY KEY,
        epoch INTEGER NOT NULL,         -- timestamp
        data  BLOB NOT NULL,            -- logged data
        IsSynchronized BOOLEAN NOT NULL DEFAULT 0
    );

Each time you get transmission confirmation you would be performing a:

    UPDATE
        events
    SET
        IsSynchronized = 1
    WHERE
        id = $ID

Which is a whole lot of updates (CPU/flash events) for your little
device. If you know that transmissions only happen chronologically then
I would recommend no IsSynchronized column, but instead to store the
latest successfully transmitted ID and update based on that:

    CREATE TABLE meta(
        latest_id INTEGER NOT NULL
            FOREIGN KEY REFERENCES events(ID)
    );

    -- Items to transmitt:
    SELECT
        e.epoch,
        e.data
    FROM
        meta m
    INNER JOIN
        events e
    ON
        e.id > m.latest_id
    ORDER BY
        e.epoch
    ;

    -- When the above is successfullly transmitted then record latest ID
    UPDATE
        meta
    SET
        latest_id = (
            SELECT
                MAX(e.id)
            FROM
                events e
        )
    ;

Both of the above queries would use an index and be quite fast.  If you
know that your "epoch" timestamps are unique you could make them the
primary key instead of the "id" column I have above, to save even more
space.

Mark
--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about Practicality of Embedding SQLite on Cortex-M4 Processor

Fredrik Gustafsson
Hi,
from what I've understood you're making this way too complicated.

A database would need an index to be able to find the item you want to
mark as is_logged. However you are always logging and saving the data
in a certain order, so i BTree is just a waste of space (and code).

My proposal would be:

1 struct representing a data point
struct datapoint {
        uint64_t timestamp;
        uint8_t sensor_id;
        uint8_t value;
}

And two pointer to keep track of your data on the flash:

uint8_t * next_insert_ptr
uint8_t * next_to_log_ptr

When you get a new datapoint, create it (pseudo code):
struct datapoint * dp = new_datapoint(time, sensor, value);
save_to_flash_at_address(dp, next_insert_ptr);
next_insert_ptr = increase_ptr(next_insert_ptr);

And when you're logging over bluetooth:
struct datapoint * dp = read_datapoint_from_flash(next_to_log_ptr);
log_to_phone_over_bluetooth(dp);
next_to_log_ptr = increase_ptr(next_to_log_ptr);

The details I haven't bothered with here is for example:
* don't log value that is ahead of next_insert_ptr
* cycle memory, when 8 gb is full, start again at 0 (or some address
  above your code)
--
Fredrik Gustafsson

phone: +46 733-608274
e-mail: [hidden email]
website: http://www.iveqy.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about Practicality of Embedding SQLite on Cortex-M4 Processor

Eduardo
In reply to this post by Obrien, John J
On Fri, 2 Mar 2018 19:46:17 +0000
"Obrien, John J" <[hidden email]> escribió:

> Hello,
>
>
> My team is working on a project that involves transmitting sensor data from a data logger module to a mobile application via Bluetooth. I am interested in finding a relatively fast, reliable way to store the data that was collected by the data logger. Since we aren't guaranteed to always have a reliable Bluetooth connection between the mobile app and the data logger, we will need a way to mark which records are synchronized with the mobile application and which still haven't been sent up, so that the data logger can continue to collect data even when the mobile application is out of range. We collect data continuously at 4Hz, so I anticipate that we will have far too much data to use a flat file and manually traverse each record prior whenever the mobile application requests data.
>
>
> I am very new to embedded programming (almost no prior experience) but have prior mobile application development experience. My first thought is to store the data in a SQLite database table and include one column called "IsSynchronized" that can store a boolean value to indicate which datapoints have been synced. When the mobile application requests data, the records where "IsSynchronized == false" will be transmitted to the mobile app. Once successful, the mobile app will let the data logger know that the transmission succeeded and set the IsSynchronized column to true for each synchronized record. This is how I would do it if the data were traveling from a mobile app to a server, but I don't know if this is a good idea for an embedded database to a mobile application.
>
>
> Our data logger uses an Atmel-SAM4S microprocessor. We have 8GB of flash memory, so storage isn't an issue. Our RAM is very limited; we only have 160KB. We are working with an external vendor to design the data logger. They have developed an custom, handrolled operating system, but since it contains no Virtual File System, we aren't sure if SQLite will be an option for us. I've heard of other folks using uClinux or other Unix based Operating Systems on the SAM4S to accommodate SQLite (http://sqlite.1065341.n5.nabble.com/VFS-for-an-MCU-internal-flash-td83079.html), but I don't know how practical this is.
>
>
> My question is this:
>
>   1.  First, am I on the wrong track by pursuing an embedded database to solve my data synchronization issue?
>   2.  Are there other embedded database alternatives that might make more sense in my scenario. I've heard a lot about NoSQL DBs like BerkleyDB and UnQlite, but I don't know is they are practical for my case either.
>   3.  If we do need to implement a VFS, does it make sense to use a unix-based embedded operating system? If so, are there any recommendations as to which one may be a good fit for my hardware
>   4.  Last, are we wasting our time with the SAM4S processor to solve this problem? We would like to take advantage of existing code, the nice power-consumption characteristics of the SAM4S, and would rather not have to go through the time and expense of upgrading our processor, but, if our processor choice is a non-starter, it would be good to know sooner rather than later.
>
> To summarize, my question is regarding what direction I should ask the hardware vendor to take. Does it make sense for them to spend time optimizing the SAM4S for SQLite or should we consider another approach?
>


For the first, yes, but.... should it be a 'real' db? If your queries to your
data structure (a table in sql) will be for '=' only and not for '>' '<' or
other more complex, a simple data structure (list, double queue,.. whatever)
will be faster and lighter.

For the second, I use sqlite, naked from every cloth I don't need and added via
virtualtables or functions the ones I want. UnQlite derives from Sqlite (there
were 2 projects with the same name), BerkleyDB is easy to use but
bigger/heavier than sqlite.

For the third, I used contiki-os for embedded (10 years ago or more) with
sqlite. Check it. If you need something more linux like, some friends use
zephyr, but don't know what processors support.

FOr the fourth, depends on what are you trying to solve. If you only want to
send data in IoT, I'll use contiki without anything, a double queue (or list),
one for not synch and other for synch data, moving from the first to the second
a struct will make the work. Don't think big in embedded world



--
Eduardo <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users