I am building a social media application which requires local storage of table data entities. This data must also be connected to a server to retrieve and update information to and from users. Our team has built an iOS client using core data, though we are looking for storage options in android. Is using SQLite the way to go? Any thoughts would be greatly appreciated.
lundi 29 juin 2015
How to query single record based on RecyclerView item click
My app has a RecyclerView/CardView of Tasks. Each card is populated by my adapter with getAllTasksForUser() in my TaskTableController class:
public List<Task> getAllTasksForUser() {
final String QUERY_ALL_RECORDS = "SELECT * FROM " + TaskEntry.TABLE_NAME;
List<Task> taskList = new ArrayList<>();
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(QUERY_ALL_RECORDS, null);
if (cursor.getCount() > 0) {
while (cursor.moveToNext()) {
Task task = new Task();
int TaskId = cursor.getColumnIndex(TaskEntry.COLUMN_TASK_ID);
task.setId(cursor.getString(TaskId));
task.setTitle(cursor.getString(cursor.getColumnIndex(TaskEntry.COLUMN_TASK_TITLE)));
task.setDue(new DateTime(cursor.getString(cursor.getColumnIndex(TaskEntry.COLUMN_TASK_DUE))));
task.setNotes(cursor.getString(cursor.getColumnIndex(TaskEntry.COLUMN_TASK_NOTES)));
task.setStatus(cursor.getString(cursor.getColumnIndex(TaskEntry.COLUMN_TASK_STATUS)));
taskList.add(task);
}
} else {
cursor.close();
db.close();
return taskList;
}
cursor.close();
db.close();
return taskList;
}
which is used to populate a ListArray of Task objects in my Adapter.
public class TaskAdapter extends RecyclerView.Adapter<TaskAdapter.TaskViewHolder> {
private List<Task> taskList;
OnItemClickListener mItemClickListener;
public TaskAdapter(List<Task> taskList) {
this.taskList = taskList;
}
@Override
public int getItemCount() {
return taskList.size();
}
@Override
public void onBindViewHolder(TaskViewHolder taskViewHolder, int i) {
Task task = taskList.get(i);
taskViewHolder.mTitle.setText(task.getTitle());
taskViewHolder.mDue.setText(task.getDue().toString());
if (task.getStatus().equals("completed")) {
taskViewHolder.mStatus.setChecked(true);
} else taskViewHolder.mStatus.setChecked(false);
}
@Override
public TaskViewHolder onCreateViewHolder(ViewGroup viewGroup, int i) {
View itemView = LayoutInflater.from(viewGroup.getContext()).
inflate(R.layout.tasklist_layout, viewGroup, false);
return new TaskViewHolder(itemView);
}
public void SetOnItemClickListener(final OnItemClickListener mItemClickListener) {
this.mItemClickListener = mItemClickListener;
}
public interface OnItemClickListener {
void onItemClick(View view , int position);
}
And subsequently in my MainActivity with
taskAdapter = new TaskAdapter(new TaskTableController(getActivity()).getAllTasksForUser());
return rootView;
My question is, once one of these items is clicked, how can I retrieve the sqlite _ID or another unique identifier to used as an input for a more detailed query to populate another view. I've tried by adding in the MainActivity
@Override
public void onViewCreated(View view, Bundle savedInstanceState) {
super.onViewCreated(view, savedInstanceState);
mRecyclerView.setAdapter(taskAdapter);
mRecyclerView.setHasFixedSize(true);
mRecyclerView.setLayoutManager(new LinearLayoutManager(getActivity()));
mRecyclerView.setItemAnimator(new DefaultItemAnimator());
taskAdapter.SetOnItemClickListener(new TaskAdapter.OnItemClickListener() {
@Override
public void onItemClick(View v, int position) {
m_ID = (TextView) v.findViewById(R.id._task_id);
Intent detailIntent = new Intent(v.getContext(), DetailActivity.class);
detailIntent.putExtra("TASK_ID", m_ID.getText().toString());
startActivity(detailIntent);
}
});
}
In my TaskTableController class i've added another method
public Task getTaskByID(String Id) {
final String SINGLE_TASK_QUERY = "SELECT * FROM " + TaskEntry.TABLE_NAME +
" WHERE " + TaskEntry._ID + " = " + Id;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(SINGLE_TASK_QUERY, null);
Task task = new Task();
while (cursor.moveToNext()){
task.setId(cursor.getString(cursor.getColumnIndex(TaskEntry.COLUMN_TASK_ID)));
task.setTitle(cursor.getString(cursor.getColumnIndex(TaskEntry.COLUMN_TASK_TITLE)));
task.setDue(new DateTime(cursor.getString(cursor.getColumnIndex(TaskEntry.COLUMN_TASK_DUE))));
task.setNotes(cursor.getString(cursor.getColumnIndex(TaskEntry.COLUMN_TASK_NOTES)));
}
return task;
}
should I call getTaskByID() in my DetailActivity or my DetailFragment which is responsible for inflating the detailed view? And how do I pass the ID from the intent extra to the query method?
Vacuum Sqlite database with EntityFramework 6.1
Recently as entertainment I decided to develop a small project to test the benefits of SQlite with EntityFramework provided by the library System.Data.SQLite.
The application has a data synchronization process which over time become obsolete so I decided to delete them from the database. As expected the size of the data base is not reduced by removing table rows, so I decided to run the command VACUUM therein.
After reading this excellent blog SQLite, VACUUM, and auto_vacuum, everything became much clearer to me, especially the fact that the command can not be executed within a transaction.
Like Code First is not yet available, I have to create the tables in the database with scripts, so in the same place I execute the command.
using (var context = new Context())
{
context.Database.CreateIfNotExists();
context.Database.ExecuteSqlCommand(
"CREATE TABLE IF NOT EXISTS \"main\".\"OutgoingMessages\" (\"Id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\"AccountId\" TEXT NOT NULL ON CONFLICT ROLLBACK,\"MessageId\" TEXT NOT NULL ON CONFLICT ROLLBACK,\"Date\" datetime NOT NULL ON CONFLICT ROLLBACK,\"Status\" INTEGER NOT NULL ON CONFLICT ROLLBACK,\"Content\" BLOB NOT NULL ON CONFLICT ROLLBACK,\"Size\" INTEGER NOT NULL ON CONFLICT ROLLBACK,\"Hash\" TEXT NOT NULL ON CONFLICT ROLLBACK,\"Comment\" TEXT);" +
"CREATE TABLE IF NOT EXISTS \"main\".\"IncomingMessages\" (\"Id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\"AccountId\" TEXT NOT NULL ON CONFLICT ROLLBACK,\"MessageId\" TEXT NOT NULL ON CONFLICT ROLLBACK,\"Date\" datetime NOT NULL,\"Status\" INTEGER NOT NULL,\"Comment\" TEXT);");
context.Database.ExecuteSqlCommand("VACUUM;");
}
I was surprised to receive the following exception:
Additional information: SQL logic error or missing database. Cannot VACUUM from within a transaction.
An exception of type 'System.Data.SQLite.SQLiteException' occurred in EntityFramework.dll but was not handled in user code
at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)
What it makes me think that all commands executed by the method ExecuteSqlCommand are processed within a transaction. I am using EntityFramework 6.1.3 and System.Data.Sqlite 1.0.97.0 with .NET Framework 4.5.
QUESTIONS
Am I wrong about that? If so, is there a way of executing the command?
SQL lite sum function
I have to sum the population for three columns. The countries Germany, France and Italy. Each has an associated population value, however, I cannot get the proper syntax for summing multiple columns.
100 K Data insert in android sqlite out of memory exception
I want to save 100K around data in sqlite on android mobile but after 15K it is showing below out of memory error I am using AsyncTask for communication from server :
Out of memory on a 6468956-byte allocation.
"AsyncTask #2" prio=5 tid=13 RUNNABLE
| group="main" sCount=0 dsCount=0 obj=0x436b3428 self=0x512c9010
| sysTid=9951 nice=10 sched=0/0 cgrp=apps/bg_non_interactive handle=1361025880
| state=R schedstat=( 0 0 0 ) utm=33 stm=17 core=1
at java.lang.String.<init>(String.java:~255)
at java.lang.String.<init>(String.java:171)
at java.lang.String.<init>(String.java:141)
at org.ksoap2.transport.HttpTransportSE.call(HttpTransportSE.java:192)
at org.ksoap2.transport.HttpTransportSE.call(HttpTransportSE.java:95)
at android.os.AsyncTask$2.call(AsyncTask.java:287)
at java.util.concurrent.FutureTask.run(FutureTask.java:234)
at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:230)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1080)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:573)
at java.lang.Thread.run(Thread.java:856)
threadid=13: thread exiting with uncaught exception (group=0x41631930)
FATAL EXCEPTION: AsyncTask #2
java.lang.RuntimeException: An error occured while executing doInBackground()
at android.os.AsyncTask$3.done(AsyncTask.java:299)
at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:352)
at java.util.concurrent.FutureTask.setException(FutureTask.java:219)
at java.util.concurrent.FutureTask.run(FutureTask.java:239)
at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:230)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1080)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:573)
at java.lang.Thread.run(Thread.java:856)
Caused by: java.lang.OutOfMemoryError
at java.lang.String.<init>(String.java:255)
at java.lang.String.<init>(String.java:171)
at java.lang.String.<init>(String.java:141)
at org.ksoap2.transport.HttpTransportSE.call(HttpTransportSE.java:192)
at org.ksoap2.transport.HttpTransportSE.call(HttpTransportSE.java:95)
at android.os.AsyncTask$2.call(AsyncTask.java:287)
at java.util.concurrent.FutureTask.run(FutureTask.java:234)
... 4 more
Please help... Thanks
SQLITE query, return records from multiple tables each joining their own metatable
I am developing a new database backend for my application for more flexibility.
Instead of one large table with many columns, I now have four tables;
- Table "roms" (contains a single column 'Name')
- Table "romsmeta" (contains columns 'Name','Title','Year')
- Table "software" (contains columns 'System' and 'Name')
- Table "softwaremeta" (contains columns 'System','Name','Title','Year')
So now I need to use a JOIN type in my queries, but my skills are a bit rusty. Basically, I would like to perform the following pseudo-query;
SELECT Title,Year from (roms INNER JOIN romsmeta,software INNER join softwaremeta) WHERE Title like '%enteredTitle%'
Obviously, this syntax is not valid.
What query would return the results I'm looking for?
Thanks in advance!
Storing Unicode strings to SQLite database using PeeWee
Working with peewee to create a simple sqlite database that stores items form a parsed MRSS feed, specifically video URLs and video titles.
I have a little dedupe script that I wrote that checks whether or not the URL and video title that's just been parsed is already in the database. I've noticed that when I pass URL strings to the database, there are no issues, dedupe works fine. When I browser through the database for the URLs I've stored there, using an app like sqlitebrowser, I see the URLs with no Unicode encoding, they looks like regular old strings, no u'.
However, when I pass unicode video titles — like this: (u'Animals doing the strangest things',) the dedupe script wasn't "seeing" the video titles in the database. The solution was to do a simple conversion with the video title object like this: videoName = str(videoName) — however, I have no clue why this works now. When I browse the videoName column in my database using sqlitebrowser, I see the video names, but they're still in Unicode and look the same as they did before: (u'Animals doing the strangest things',)
Any idea what the heck is going on here? Does this have something to do with a weird bug with how PeeWee is handling string serialization?
Inscription à :
Articles (Atom)