where is sqlite database ?

Use the ContentProvider directly, it's wrapping the SQL and should be the standard Android way to query the data from another app.

A few pointers:
 * Read the content providers tutorial: http://developer.android.com/guide/topics/providers/content-providers.html
 * The NoteProvider contains the Tomdroid specific implementation: http://bazaar.launchpad.net/~tomdroid-maintainers/tomdroid/main/annotate/head:/src/org/tomdroid/NoteProvider.java

You should be able to query for the notes with the following code:

String[] projection = { Note.ID, Note.TITLE, Note.FILE, Note.NOTE_CONTENT, Note.MODIFIED_DATE };
Uri notes = Tomdroid.CONTENT_URI;

Cursor managedCursor = managedQuery(notes,
                         projection,
                         null, // Which rows to return (all rows)
                         null, // Selection arguments (none)
                         Note.TITLE + " ASC");

I can't help you more than this without knowing what you intend to do. I'll be able to if you're willing to share the specifics.

Hope this helps.

I can not check now but with this i can not run a select distinct ... query

Benoît soyeux
Envoyé depuis mon HTC

----- Reply message -----
De : "Benoit Garret" <email address hidden>
Date : jeu., nov. 11, 2010 14:56
Objet : [Question #133600]: where is sqlite database ?
Pour : <email address hidden>

Your question #133600 on Tomdroid changed:
https://answers.launchpad.net/tomdroid/+question/133600

    Status: Open => Answered

Benoit Garret proposed the following answer:
Use the ContentProvider directly, it's wrapping the SQL and should be
the standard Android way to query the data from another app.

A few pointers:
 * Read the content providers tutorial: http://developer.android.com/guide/topics/providers/content-providers.html
 * The NoteProvider contains the Tomdroid specific implementation: http://bazaar.launchpad.net/~tomdroid-maintainers/tomdroid/main/annotate/head:/src/org/tomdroid/NoteProvider.java

You should be able to query for the notes with the following code:

String[] projection = { Note.ID, Note.TITLE, Note.FILE, Note.NOTE_CONTENT, Note.MODIFIED_DATE };
Uri notes = Tomdroid.CONTENT_URI;

Cursor managedCursor = managedQuery(notes,
                         projection,
                         null, // Which rows to return (all rows)
                         null, // Selection arguments (none)
                         Note.TITLE + " ASC");

I can't help you more than this without knowing what you intend to do.
I'll be able to if you're willing to share the specifics.

Hope this helps.

--
If this answers your question, please go to the following page to let us
know that it is solved:
https://answers.launchpad.net/tomdroid/+question/133600/+confirm?answer_id=0

If you still need help, you can reply to this email or go to the
following page to enter your feedback:
https://answers.launchpad.net/tomdroid/+question/133600

You received this question notification because you are a direct
subscriber of the question.

I have checked and for me with the function managedQuery, it not possible to get distinct entries.

My goal is to show the list of notebook. this could be get by the result from this request :
SELECT DISTINCT TAGS FROM notes WHERE TAGS NOT LIKE '%system:template%'

To do it, I need to connect to sqlite database with something like that :
db = SQLiteDatabase.openDatabase("/org.tomdroid.notes/notes",null,SQLiteDatabase.OPEN_READONLY);
After I create SQLiteQueryBuilder object and I use this function setDistinct liske this :

SQLiteQueryBuilder qb = new SQLiteQueryBuilder ();
qb.setDistinct(true);
qb.query(db, LIST_NOTEBOOK, where, null, null, null, orderBy);

Best Regards,
Benoit Soyeux

The database should be located at /data/data/com.google.android.providers.contacts/databases/tomdroid-notes.db

A few gotchas you need to be aware of about the note tags:
 * The sd card sync backend doesn't parse and store the tags (don't ask me why, I just discovered this...)
 * The tags are stored concatenated with commas (',').
 * I'm not entirely sure of it, but there may be more than one tag associated with a note, ie. you can end up with two entries like these ones: "system:notebook:Example notebook" and "system:notebook:Example notebook,random-tag", which is obviously not what you want. I'll advise asking Sandy Armstrong, he's the definitive source concerning the note schema.

It'd be VERY nice if you could extend the NoteManager to support notebook list querying, with one method to fetch the notebook list and another one to get all notes belonging to a notebook. We'll need these sooner or later and I'm willing to do the review and merging in case you submit a merge request.

I finaly find the path with the help of Benoit Garret
It is :
/data/data/org.tomdroid/databases/tomdroid-notes.db

hover, my idea does not work because I must select _Id column to populate my list.
The SQL query become :
SELECT note._id,note.TAGS FROM notes WHERE Note.TAGS NOT LIKE '%system:template%'
This query return all notes.
If I execute this query :
SELECT note.TAGS FROM notes WHERE Note.TAGS NOT LIKE '%system:template%'
I get only few entriees that match to my notebook but I did not how populate a list that is displayed.

This is my code to run select distinct on the database

public static Cursor getAllNotebooks(Activity activity, Boolean includeNotebookTemplates) {
  // get a cursor representing all notes from the NoteProvider
  Uri notes = Tomdroid.CONTENT_URI;
  String where = null;
  String orderBy;
  if (!includeNotebookTemplates) {
   where = Note.TAGS + " NOT LIKE '%" + "system:template" + "%'";
  }
  orderBy = Note.TAGS + " DESC";

  SQLiteDatabase db = null;
  Cursor notebooksCursor = null;
  Log.i(TAG,"Avant ouverture de la base");
  try{
   db = SQLiteDatabase.openDatabase("/data/data/org.tomdroid/databases/tomdroid-notes.db",null,SQLiteDatabase.OPEN_READONLY);
   if (!db.isOpen()){
    Log.e(TAG,"Impossible d'ouvir la base");
   }
   Log.i(TAG,"Apres ouverture de la base");
   //SQLiteOpenHelper mOpenHelper = new SQLiteOpenHelper(activity.getApplicationContext(), "notes", null, 1);

   SQLiteQueryBuilder qb = new SQLiteQueryBuilder ();
   qb.setDistinct(true);
   Log.i(TAG,"setDistinct OK");
   qb.setTables("notes");
   Log.i(TAG,"setTables OK");
   try{
    notebooksCursor = qb.query(db, LIST_NOTEBOOK, null, null, null, null, orderBy);
    Log.i(TAG,"query OK");
   } catch (Exception e) {
    e.printStackTrace();
    Log.e(TAG,"query KO");
   }
  } catch (Exception e) {
   e.printStackTrace();
  }

  return notebooksCursor;
  //return activity.managedQuery(notes, LIST_NOTEBOOK, where, null, orderBy);
 }

 public static ListAdapter getListAdapterNotebook(Activity activity) {
  Log.i(TAG,"fct getListAdapterNotebook()");
  Cursor notebooksCursor = getAllNotebooks(activity, false);
  Log.i(TAG,"notebooksCursor OK");

  // set up an adapter binding the TITLE field of the cursor to the list item
  String[] from = new String[] { Note.TAGS };
  Log.i(TAG,"from OK");
  int[] to = new int[] { R.id.notebook };
  Log.i(TAG,"to OK");
  return new SimpleCursorAdapter(activity, R.layout.notebooks_list_item, notebooksCursor, from, to);
 }

I still have no idea of what you're trying to do beyond getting a list of all notebooks. Could you publish your code in a branch, file a bug describing what to do and link them? It would be much easier to help you if I have a full buildable copy of the source you're running.

I'll try to come up with something with what you've already posted, but doing the above will greatly improve how soon I'll do it ;-)

Oh, and do not hesitate do ask for help if you're stuck somewhere in the process. Additional comments on this question will be fine.

I push all my code in this branch :
https://code.launchpad.net/~tomdroid-dev/+junk/SortingNote

With this, could you get my code ?

No problem, as soon as you push to it, the branch is currently empty ;-)

I try to use bazar explorer
When I want to push a update on launchpad from one of my two branch I had just click on commit.
I did not that is not enought.

It is my first open source project and I feel it very difficult to join this kind of project.

If i click on push, I get this error :
Run command: bzr push
Using saved push location: bzr+ssh://bazaar.launchpad.net/~tomdroid-dev/tomdroid/sortingNote/
bzr: ERROR: Server sent an unexpected error: ('error', "Cannot lock LockDir(lp-57136528:///~tomdroid-dev/tomdroid/sortingNote/.bzr/branchlock): File exists: u'/srv/bazaar.launchpad.net/mirrors/00/06/1c/9d/.bzr/branch/lock': [Errno 17] File exists: '/srv/bazaar.launchpad.net/mirrors/00/06/1c/9d/.bzr/branch/lock'")

Do you see more file on my branch ?

Could you try to push to lp:~benoit.soyeux/tomdroid/sort-notes instead?

I push to bzr+ssh://bazaar.launchpad.net/~benoit.soyeux/tomdroid/sort-notes
Now from my profile page, I can see my code
https://code.launchpad.net/~benoit.soyeux/tomdroid/sort-notes

thank you for tour help

We're getting somewhere, I can clone your branch now. Unfortunately, I can't compile your app. The sort icon is referenced but not found, as well as the Notebooks class and the notebooks and notebooks_list_item layouts. All these don't seem to exist in the branch that's on launchpad, did you forgot to commit them?

I did not add revision to new file.
After adding revision, commiting and pushing, the project seem to be good on line.

I Hope now it will be good !

You'll have to declare your Notebooks activity in AndroidManifest.xml, like this: <activity android:name=".ui.Notebooks"></activity>, the intent will not find the Notebooks class otherwise.

By the way, you could delete your sort-notes branch, you pushed your latest changes in the sortingNote one.

I will take a deeper look at this later, not today but hopefully by the end of the week.

And thanks a lot for sticking through the obstacles, it's really worth it at the end ;-)

You're missing the where argument in the query. You're building it correctly but not using it when you call query(). A correct version should be like this: qb.query(db, LIST_NOTEBOOK, where, null, null, null, orderBy); .

I don't think you'll achieve what you want by using a pure sql solution. Parsing will be needed at the application level to extract the notebook names. As an example, I have a note that has a tags column like this: list,system:notebook:ToDos,lastipod and I can't think of something that will not use a regexp to parse this.

You have two solutions:
 * fetch the tags minus the templates, extract the notebook names with a regular expression and use them to build an ArrayAdapter. This approach isn't very efficient as you have to run a whole bunch of regexps each time you want to get your list.
 * a cleaner way (but more involved in terms of work) would be to add a column to the database to store the notebook names and parse the note tags in NoteManager.putNote(). This way, you just have to run a select distinct query on the notebook column to get your shiny list.

You have an interesting road ahead ;-)

Sorry, I think I forget to complete AndroidManifest.xml

Hover, I do not understand what you say :
By the way, you could delete your sort-notes branch, you pushed your latest changes in the sortingNote one.

I think like you, notebook name can't be use as it. It must be filter by application.

I'm not sure but I think there is a mistake in your second solution. It is not column but a new table.
By creating a new table notebook with just the name of all notebook. Each time Tomdroid add a note, it check if the notebook of the new note is inside the table. if not, it add it.

I have a problem In your first solution that consisting to parse my SQL result to get a distinct java list) : how can I display an array in android ? I think it is possible but actually can't do it. Hover I think it is the best solution.

thank you for help

I try to add a second table with notebook.
I add a second URI as :
public static final Uri CONTENT_URI_NOTEBOOK= Uri.parse("content://" + AUTHORITY + "/notebooks");

I get error
unknown url content
when I create cursor on this new table :
  Uri notebooks = Tomdroid.CONTENT_URI_NOTEBOOK;
  String[] whereArgs = new String[1];
  whereArgs[0] = notebook;
  ContentResolver cr = activity.getContentResolver();
  Cursor managedCursor = cr.query(notebooks,EMPTY_PROJECTION,"notebook= ?",whereArgs, null);

How initialise this new table ?
I didn't see initialise procedure for notes table.

I find my problem, I miss to create a class notebookProvider and to declare it in android manifest file.

I finally success to filter note on notebook name.
I must add the possibility to unfilter before merging this branch to the main branch if you would like.