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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
