Replication over SMS

21 views
Skip to first unread message

Jim Redman

unread,
Feb 27, 2012, 12:30:01 PM2/27/12
to sqld...@googlegroups.com
Has anyone given any thought or action to the idea of replicating a
SQLite database over SMS.

My thought is something like taking each SQL statement as it is executed
by SQLDroid and sending it over SMS. Simple, except for things like
ordering, having an application that actually captures/processes the
SMS's, and error handling.

Jim

--
Jim Redman
(505) 662 5156 x85
http://www.ergotech.com

Uwe Kubosch

unread,
Feb 27, 2012, 12:32:08 PM2/27/12
to <sqldroid@googlegroups.com>
My company replicate several databases including SQLite using custom protocols for each application. None of these use SMS. For each application there are special needs, so we have not found any solution that fits all apps.

Please keep us informed if you go forward with this since it is verity interesting.

--
Uwe Kubosch
Systems Developer
Datek Wireless AS
u...@datek.no
http://datek.no/


Kristian Lein-Mathisen

unread,
Feb 27, 2012, 2:40:57 PM2/27/12
to sqld...@googlegroups.com
Hi Jim,

We've been working with SMS for some time and in my experience, it's not quite suitable for data-processing. SMSes may be permanently lost, or pop up after a day. Like you say, you'd need to order things yourself. 

There are also compatibility issues: You've got character-encoding, can you ensure your queries all contain only ANSI? If you want to send UTF8 (or UTF16, which is supported in GSM, but that will half you chars/SMS count), you can convert to binary SMSes. Unfortunately, some devices can't parse binary SMSes. And some operators don't let them pass, while other operators decide to convert each byte in the binary SMS into two hex-encoded characters.

I don't know what you're trying to build, but I'd say that if you can find any other ways of communicating at all, stay far away from SMS! It's quite expensive, too.

K.

Jim Redman

unread,
Feb 27, 2012, 3:23:34 PM2/27/12
to sqld...@googlegroups.com
Kristian,

On 02/27/2012 12:40 PM, Kristian Lein-Mathisen wrote:
> We've been working with SMS for some time and in my experience, it's not
> quite suitable for data-processing. SMSes may be permanently lost, or
> pop up after a day. Like you say, you'd need to order things yourself.

You're right, you need sequencing, also ack's and retries. Kinda like
implementing XModem (or and other serial transfer protocol) over SMS
(with tiny block sized and horribly slow baud rates).

The messages also clutter up the messaging app.

There are a few people who send data over SMS, don't know how, or how
reliable. The data I would want to send is well understood (SQL) but it
is rather wordy and inefficient.

> There are also compatibility issues: You've got character-encoding, can
> you ensure your queries all contain only ANSI? If you want to send UTF8
> (or UTF16, which is supported in GSM, but that will half you chars/SMS
> count), you can convert to binary SMSes. Unfortunately, some devices
> can't parse binary SMSes. And some operators don't let them pass, while
> other operators decide to convert each byte in the binary SMS into two
> hex-encoded characters.

Fair comment. The solution would probably be to limit data to UTF8, at
least until someone has a need for more and is willing to work on the
solution.

But now I think about it, there's a request for photos, so BASE64 (or
similar) encoding might resolve all the encoding issues. BUT that would
require more messages.

> I don't know what you're trying to build, but I'd say that if you can
> find any other ways of communicating at all, stay far away from SMS!
> It's quite expensive, too.

The initial idea was an app similar to a shared addressbook. Imagine a
group or club anyone whose information is added or updated is
distributed to the whole club.

It could have value in the types of work we do, sync'ing values read
remotely, or pick-lists for a warehouse, etc. (but it would have to be
very reliable to be trusted to that extent and I'm not sure it would
ever be the best solution).

I don't know where you are located, but here SMS's are almost free - I
pay $5/line for unlimited texts. Most people have unlimited texts.
They may have a mechanism to cut off people who use millions but if the
database is that big this is not the way to go. Data is expensive, not
universally available and most people don't subscribe.

I don't know when/if I'll ever get around to actually working on this.
I think that getting something that sorta-kinda works would be
relatively easy, but it would be hard to move it from an interesting toy
to a commercial strength solution.

Jim

> u...@datek.no <mailto:u...@datek.no>
> http://datek.no/

Kristian Lein-Mathisen

unread,
Feb 27, 2012, 3:54:09 PM2/27/12
to sqld...@googlegroups.com

Hey Jim,

It would be an interesting toy! If SMSes are practically free, I guess that changes the situation. I'm in Northern Europe where data over SMS would cost 150 times more than over 3G with the most expensive Internet-connection. 

Ah, of course, base64! We used base64 encoding at work previously, and it did solve most of the compatibility issues at the cost of 40% size increase.

You could probably squeeze that size further down, though. And since you know a lot about what you're compressing, it'd probably be a good idea to have common dictionaries for the clients (containing things like SELECT and WHERE). You could even convert keywords to uppercase before compressing.

K.

Jim Redman

unread,
Feb 27, 2012, 5:50:21 PM2/27/12
to sqld...@googlegroups.com
Kristian,

On 02/27/2012 01:54 PM, Kristian Lein-Mathisen wrote:
> It would be an interesting toy!

I just think that it would be hard to get the reliability needed for a
critical data structure. Who knows, maybe if I start some suitably
smart person will fix my code!

> If SMSes are practically free, I guess
> that changes the situation. I'm in Northern Europe where data over SMS
> would cost 150 times more than over 3G with the most expensive
> Internet-connection.

To get 3G here in Los Alamos, NM I'd have to build the towers! So far
we have 128k. (However, there's a rumor that T-Mobile will use the
proceeds of the failed AT&T merger - cash and spectrum - to increase LTE
rollout).

> Ah, of course, base64! We used base64 encoding at work previously, and
> it did solve most of the compatibility issues at the cost of 40% size
> increase.
>
> You could probably squeeze that size further down, though. And since you
> know a lot about what you're compressing, it'd probably be a good idea
> to have common dictionaries for the clients (containing things like
> SELECT and WHERE). You could even convert keywords to uppercase before
> compressing.

SQL is embarrassingly verbose.

It's also possible that you could have it only accept prepared
statements, so only the data would have to be sent for each change.
Then you'd have to sync two things, the data and the statements and
unless a prepared statement was really useful (repeated operations)
you'd end up sending the statement and that data each time.

Not to mention that prepared statements in SQLDroid are not as cooked as
you might hope.

Jim

>
> K.

> u...@datek.no <mailto:u...@datek.no> <mailto:u...@datek.no

--

Kristian Lein-Mathisen

unread,
Feb 29, 2012, 6:14:35 AM2/29/12
to sqld...@googlegroups.com
Hi Jim,

Good idea on prepared statements! Your "compression" probably won't get better than that.

I've still got the feeling that syncing sql-statements over SMS is a little scary. Like you say, it'll be tricky to get something reliable enough. How about http://en.wikipedia.org/wiki/SyncML over SMS for synchronising your phonebook?

K.
Reply all
Reply to author
Forward
0 new messages