SQLite and Flex

Question

I am developing a GPS calculator that uses a lot of data to calculate different values, such as RMSE (root mean square error) and NSSDA (national standard for spatial data). The data is from a variety of different models of GPS units with a great degree in variation, i.e. internal/external antennae; auto, WAAS, DGPS, PPS signal; open, light, medium, heavy canopy; etc. Basically a whole lot of data needs to be stored to compute the huge variety of desired outcomes.

The data will always be read, and only in rare conditions will new data be added. If it does need to be added, it will most likely be by one of the developers, not users of the program. Because of this, we are very interested in using SQLite. We have Oracle on our server, but any creation of tables must be done by higher-ups, which can sometimes take weeks. For this reason SQLite seems like it might work for us. I have never used SQLite, so I am wondering if it is the right choice. However, most documentation about SQLite and Flex seems to be pertaining to AIR applications and SQLite as a local database. Would having an SQLite file on the server with a flex application performing the desired calculations even work? Or am I way off the mark on the use of SQLite files?

Answer 1

I don't know Flex. But I like SQLite very much.

Your application (reading data with very seldom updates -- mostly of just one instance) I would see as an ideal situation for SQLite!

SQLite is very performant and also scales up gracefully. The only application, I would not recommend it, is when you have a heavy transaction burden (many concurrent modifications of Db contents). Also when you have to have a really big data mass to handle (millions or billions of rows) I would more likely think of a grown up database with possibility to use multiple CPUs or instances ...

But all that seams to be not your case here.

Answer 2

A Flex application won't be able to use a SQLite database located on the user's computer - for that, you need to use AIR. But you seem to be saying that the SQLite database would remain on the server, and be queried by the Flex client. You only seem to want to use SQLite because setting up stuff in Oracle takes too long because of internal business processes.

So, you can certainly set up a SQLite database on your server, serve the data it contains to clients, using a whole host of server-side frameworks (my favourite: Django), and read and present the data using Flex in the client's browser. SQLite is a good choice for small-to-medium-sized applications.

Answer 3

Not the way you're probably thinking, no -- there's no SQLite client built into Flex proper. From a Web-based Flex app, the only way to access a relational database (even a SQLite file) is through a web-service API of some sort.

If you haven't already run across it, here's some useful StackOverflow discussion for you addressing the scalability of SQLite specifically:

http://stackoverflow.com/questions/54998/how-scalable-is-sqlite

Based on your description of the project, assuming you're not going to have very high volume (even if it's only selects), SQLite might be an acceptable choice.

But my personal suggestion would be that if you have any ability whatever to run even a simple a RDBMS instance somewhere (MySQL, for example), definitely go that route. You might simply be limited operationally -- every company's different in that sense. But if you can help it, don't choose a flat file as the database of your Web app. Even if you don't run into contention issues right off the bat, you'll almost surely run into them eventually. That's a headache best avoided if you can manage it.

Answer 4

I love sqlLITE but I have only used it with Perl.

You could use something like Amazon's SimpleDB which is a database that can be accessed as a webService. It's also free if you have less than 1GB of data and use it less than 25 hours a month.

Link to simpleDB http://aws.amazon.com/simpledb/

There is an AS3 library for simpleDB but I don't have the reputation to have more than one link :-(.. So google: "Amazon illustratedlife simpleDB"

Good Luck