CODEWITHSUNDEEP
androiddatabasesqliteprimary-key
Kalina
Kalina

Reputation: 5594

What primary key to use in my SQLite database?

I have a .csv file that I turned into an SQLite database with the first column as a date and time (as a TEXT datatype because it's in the format 7/20/2011 12:00:00 PM), the second column as the subsecond (a REAL, like 0.142857), and the rest of the columns as data about that time. I will be using it as a read-only db in my Android app, in which the user selects a start time and an end time and then the data is displayed graphically for that time period.

This is my first time using a database and I'm not sure how to work with this. I believe I need a unique NUMERIC primary key for each row but simply labeling them by row number is a waste of space. The first column is not unique and is a TEXT datatype anyway, though if the subsecond was to be somehow incorporated then it would be unique. But I really can't re-write the database because it has 65534 rows... How do you suggest I access specific times in this database?

Upvotes: 0

Views: 3434

Answers (3)

piotrpo
piotrpo

Reputation: 12636

Every date can be converted to numeric timestamp quite easy:

Date date = new Date();
long timestamp = date.getTime();
Date otherDate = new Date(timestamp);

Numbers are MUCH easier and faster to process than text fields. If you are completely sure, that you have unique data within column you can use it as primary key.

Importing csv file into table should be also easy:

//just open file in some known way and read it line by line
// we have declared String[] data, String line is line read from your csv somewhere earlier 
// in code

data = line.split(",");
//here you have to process values, and execute insert statement

You have to create indexes on every column which will be used to search or order data. Please be also aware, that rows in table has no "default", "natural" or any other order. If you execute this same select statement twice you can get two totally different results in meaning of sorting. So simple select statement should look like that:

select 
   _id, other_colum_name, some_other_column 
from 
   table_name
where 
   column_name = 5
   and other_column_name = 3
order by 
   column_name;

Upvotes: 0

Nikola Despotoski
Nikola Despotoski

Reputation: 50578

If your database is local and external(not remote), than you must have _id and another another table android_metadata which will hold the locale.

If your database was remote. Yes, you can it is only matter of speed if you are write, since you don't. Using WHERE clause will do the work.

Upvotes: 0

Vincent Mimoun-Prat
Vincent Mimoun-Prat

Reputation: 28573

In Android you need a column named _ID in your database (else you'll face some issues later on). You will use that as the primary key.

Dates and times can be stored in SQLite in the form of a text column in the following format (See http://www.sqlite.org/datatype3.html):

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS")

If your database is static, simply use a tool such as SQLite Database Browser to convert it to a format convenient for Android.

Upvotes: 2

Related Questions