Is it possible to set SQLite DB on server and connect to it in an Android app

androidsqlite

I'm creating an app as a study project. One of requirements is not to hardcode. For this reason, I want to get data from DB with every start of the app and change data stored in DB outside of the app code. I wanted to use Redis and store data in JSON at first, but there were too many problems, because Redis doesn't work with Android by default.

So, the question is: is it possible to use SQLite set on server? I know that this DB is not supposed to work in client-server mode, but I have found out some SQLite hostings in the Internet. If it is possible, how to perform it? If not, which way should I store data? SQL format is pretty convenient for me, because it is literally a table.

Best Answer

No, it's not possible. SQLite is an embedded database engine, which means it runs as part of your program and uses a file (usually) in the application sandbox on Android (so this is not part of your source code).

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files.

It's probably possible to have an application run on a server which is able to function as a backend for your app and stores its data in a SQLite file, but I'd rather choose a database which is intended for this kind of purposes like PostgreSQL or MySQL. You can talk SQL to those databases too. Of course, in a production-grade application you'll need an application as a back end, not just a database, which is able to authenticate users, validate business logic etc.

Related Solutions

SQLite or CouchDB for Locational Data Mining Project

I think the answer mainly depends on how much time do you want to spend learning new databases vs how much time to you want to spend learning machine learning.

For example PostgreSQL has lots of GIS stuff built-in which I assume could be very useful for your queries. CouchDB has many useful features with their map/reduce stuff but I find it a bit limited. If you think you're going to add many columns later on based on new algorithms you might want to look into the column-oriented databases like Apache Cassandra.

However, I would recommend splitting the problem into two parts:

Data gathering
Data analysis

The first part you seem to have a good plan for already. I would write a super simple app that would just suck in the data from twitter and put it in a table as a BLOB. Or just a file on disk.

When you have that you can go from the raw data and insert it into any kind of backend. Now you can choose backend based on the current problem (algorithm) you're working on, instead of having one solution to fit all your problems. The key point here is to extract exactly the data you need. That way you shouldn't have to consider the fact that the data is nested inside a document from Twitter as you only pick out the parts you need. If you go that way I think an RDBMS would perform very nice as they can run all kinds of queries.

Make sense?

SQLite/Android – First Time Using an Index

WHERE c = ?

This lookup can be optimized with a simple index on the c column:
```
CREATE INDEX T_c on T(c);
```
WHERE a LIKE ? || '%'

There are several restrictions on indexes that can be used for LIKE searches. In this case, you need a case-insensitive index:
```
CREATE INDEX T_a_ci ON T(a COLLATE NOCASE);
```

WHERE b LIKE ? || '%'

same here:

CREATE INDEX T_b_ci ON T(b COLLATE NOCASE);

WHERE a LIKE ? || '%' AND b LIKE ? || '%'

The database can use at most one index per table in a query, so it will use one of the indexes above. If you have run ANALYZE, it might be able to choose the better one.

To check which indexes are actually being used, use EXPLAIN QUERY PLAN.

Whenever you change tables, any indexes must be updated too. When you're filling an empty table, it is usually a better idea to create indexes afterwards.

When your application does many updates to some table, it might be worthwhile omitting some index(es) that are useful only for seldomly-used queries.

Best Answer

Related Solutions

SQLite or CouchDB for Locational Data Mining Project

SQLite/Android – First Time Using an Index

Related Question