Re: [sqlite] Question about Practicality of Embedding SQLite on Cortex-M4 Processor
On Fri, 2 Mar 2018 19:46:17 + "Obrien, John J" 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about Practicality of Embedding SQLite on Cortex-M4 Processor
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: iv...@iveqy.com
website: http://www.iveqy.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about Practicality of Embedding SQLite on Cortex-M4 Processor
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( idINTEGER 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about Practicality of Embedding SQLite on Cortex-M4 Processor
On Fri, Mar 2, 2018 at 2:46 PM, Obrien, John J 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about Practicality of Embedding SQLite on Cortex-M4 Processor
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 bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about Practicality of Embedding SQLite on Cortex-M4 Processor
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
