How to create nested tables in SQLite database?? (android)

How to create nested tables in SQLite database?? (android)

Lalage Yakiv Author: Lalage Yakiv Date: 2022-10-05
How to create nested tables in SQLite database?? (android)

All you need to know about How to create nested tables in SQLite database?? (android) , in addintion to How many tables and databases can I create in Android sqlite? , How do I join two SQLite tables in my Android application? , android - How to use MOBAC created OSMDroid SQLite tile source file offline? , java - How to create a list with data tables as items that can be sorted

  1. How to create nested tables in SQLite database?? (android)
  2. Question:

    i want to create a nested sqlite database in android. ie. I want a particular field in a table to have its whole new set of values in the form of a distinct table.


    Solution 1:

    What you're describing isn't possible; there is no way to include a table within a row in another table. Standard practice is to create "parent/child" tables by including the primary key of the parent table as a column in the child table; for instance:

    PARENT TABLE
    
    id | name
    ---------
    1  | Fred
    2  | Bob
    

    CHILD TABLE
    id | parent_id | name
    ---------------------
    1  | 1         | John
    2  | 1         | Jim
    3  | 2         | Joe
    4  | 2         | Jane
    

    This pair of tables would have "John" and "Jim" as the children of "Fred", and "Joe" and "Jane" as children of "Bob". You could get the set of all children of "Bob" (parent id=2) with the query:

    SELECT * FROM child_table WHERE parent_id = 2
    

    Solution 2:

    That is what i was going to do but You won't be able to create another sub-table of the current table.

    I will suggest you to create two different table from it, e.g.. if you have table employee and need to create another two sub-table marketing employee and engineering employee tables, just create the two table that i was describing. e.g.

    CREATE TABLE emp_engineer 
    

    and

    CREATE TABLE emp_marketing 
    

  3. How many tables and databases can I create in Android sqlite?
  4. Question:

    How many databases can I create in one instance of sqlite?

    In one Database how many tables can I create in sqlite?

    What is the maximum size of data that a Table or column can hold?


    Solution 1:

    How many databases can i create in one application?

    As many as you want, within the limits of available disk space.

    in one Database how many tables can i create?

    A billion or so, though you will run out of disk space first.

    and what was the maximum size of data that can holds a Table or a column?

    For Android, as much disk space as you have available.

    See: http://sqlite.org/limits.html

    Solution 2:

    you can create multiple database, tables in your application and the size of database is depends on your sd-card size because SQLite database stores in Environment.getDataDirectory() + /data/<Package Name>/databases.

    you can check the size of database by using this code

    SQLiteDatabase db;
    
    // ...
    
    long size = new File(db.getPath()).length();
    

  5. How do I join two SQLite tables in my Android application?
  6. Question:

    Background

    I have an Android project that has a database with two tables: tbl_question and tbl_alternative.

    To populate the views with questions and alternatives I am using cursors. There are no problems in getting the data I need until I try to join the two tables.

        Tbl_question  
        -------------
        _id  
        question  
        categoryid  
    
        Tbl_alternative
        ---------------
        _id 
        questionid 
        categoryid 
        alternative
    

    I want something like the following:

    SELECT tbl_question.question, tbl_alternative.alternative where 
    categoryid=tbl_alternative.categoryid AND tbl_question._id = 
    tbl_alternative.questionid.` 
    

    This is my attempt:

    public Cursor getAlternative(long categoryid) {
                String[] columns = new String[] { KEY_Q_ID, KEY_IMAGE, KEY_QUESTION, KEY_ALT, KEY_QID};
                 String whereClause = KEY_CATEGORYID + "=" + categoryid +" AND "+ KEY_Q_ID +"="+ KEY_QID;
                 Cursor cursor = mDb.query(true, DBTABLE_QUESTION + " INNER JOIN "+ DBTABLE_ALTERNATIVE, columns, whereClause, null, null, null, null, null);
                 if (cursor != null) {
                      cursor.moveToFirst();
                 }
                 return cursor;
    

    I find this way to form queries harder than regular SQL, but have gotten the advice to use this way since it is less error prone.

    Question

    How do I join two SQLite tables in my application?


    Solution 1:

    You need rawQuery method.

    Example:

    private final String MY_QUERY = "SELECT * FROM table_a a INNER JOIN table_b b ON a.id=b.other_id WHERE b.property_id=?";
    
    db.rawQuery(MY_QUERY, new String[]{String.valueOf(propertyId)});
    

    Use ? bindings instead of putting values into raw sql query.

    Solution 2:

    you can create multiple database, tables in your application and the size of database is depends on your sd-card size because SQLite database stores in Environment.getDataDirectory() + /data/<Package Name>/databases.

    you can check the size of database by using this code

    SQLiteDatabase db;
    
    // ...
    
    long size = new File(db.getPath()).length();
    

    Solution 3:

    In addition to @pawelzieba's answer, which definitely is correct, to join two tables, while you can use an INNER JOIN like this

    SELECT * FROM expense INNER JOIN refuel
    ON exp_id = expense_id
    WHERE refuel_id = 1
    

    via raw query like this -

    String rawQuery = "SELECT * FROM " + RefuelTable.TABLE_NAME + " INNER JOIN " + ExpenseTable.TABLE_NAME
            + " ON " + RefuelTable.EXP_ID + " = " + ExpenseTable.ID
            + " WHERE " + RefuelTable.ID + " = " +  id;
    Cursor c = db.rawQuery(
            rawQuery,
            null
    );
    

    because of SQLite's backward compatible support of the primitive way of querying, we turn that command into this -

    SELECT *
    FROM expense, refuel
    WHERE exp_id = expense_id AND refuel_id = 1
    

    and hence be able to take advanatage of the SQLiteDatabase.query() helper method

    Cursor c = db.query(
            RefuelTable.TABLE_NAME + " , " + ExpenseTable.TABLE_NAME,
            Utils.concat(RefuelTable.PROJECTION, ExpenseTable.PROJECTION),
            RefuelTable.EXP_ID + " = " + ExpenseTable.ID + " AND " + RefuelTable.ID + " = " +  id,
            null,
            null,
            null,
            null
    );
    

    For a detailed blog post check this http://blog.championswimmer.in/2015/12/doing-a-table-join-in-android-without-using-rawquery

  7. android - How to use MOBAC created OSMDroid SQLite tile source file offline?
  8. Question:

    I've been pulling my hair out trying to get my own offline Mobile Atlas Creator OSMDroid SQLite map working with OSMDroid 3.0.8 without luck. It's been a long 3 days. I'll try to explain with clips from my application. I've been extending ItemizedIconOverlay and OverlayItem so I hope it doesn't get too confusing.

    I created my own OSMDroid SQLite map file with 3 different zoom levels for a small are, like 10 square kms. I copied the resulting "base.sqlite" file into my project /res/raw/ directory. Note that the GeoPoints in my application should be well within the map's tile range. The "base.sqlite" file should get saved to the application specific data directory.

    Next I turfed the /osmdroid directory on my phone so I could get the previously cached maps off. I thought I had my own offline maps working until I turned on Airplane mode and noticed the cached maps were still available.

    Now all I get is blanks. I have no clue how to get this going. I've see a couple of examples but after a ton of experimentation I haven't been successful in getting any of them working.

    private Hashtable<String, NodeOverlayItem> nodes = new Hashtable<String, NodeOverlayItem>();
    
    private MapView mapView;
    private Context context;
    
    private LocationManager locManager;
    
    private MapController mapController;
    private MapTileProviderArray mapProvider;
    private String mapTileArchivePath = "base.sqlite";
    
    private ResourceProxy resourceProxy;
    
    @Override
    public void onCreate(final Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
    
        try {
            this.mapView = new MapView(this, 256);
            this.mapView.setBuiltInZoomControls(true);
            this.mapView.setMultiTouchControls(true);
    
            this.context = this.getApplicationContext();
            this.resourceProxy = new DefaultResourceProxyImpl(context);
    
            XYTileSource TILERENDERER = new XYTileSource("test", 
                ResourceProxy.string.offline_mode, 
                1, 20, 256, ".png", "http://127.0.0.1");
            SimpleRegisterReceiver simpleReceiver = new SimpleRegisterReceiver(this.context);
    
            IArchiveFile[] archives = { ArchiveFileFactory.getArchiveFile(this.getMapsFile()) };
            MapTileModuleProviderBase moduleProvider = new MapTileFileArchiveProvider(
                    simpleReceiver, 
                    TILERENDERER, 
                    archives);
            this.mapProvider = new MapTileProviderArray(TILERENDERER, null, new MapTileModuleProviderBase[] { moduleProvider });
            this.mapProvider.setUseDataConnection(false);
            this.mapView = new MapView(this, 256, this.resourceProxy, this.mapProvider);
            this.mapView.setUseDataConnection(false);
    
            mapController = mapView.getController();
            mapController.setZoom(18);
            mapController.setCenter(new GeoPoint((int)(45.349622 * 1E6), (int)(-75.880700 *1E6)));
    
            this.setContentView(mapView);
        } catch(Exception ex) {
            Log.e("test", ex.getMessage());
        }    
    }
    
    
    public File getMapsFile() throws IOException {
        Log.d("test", "Trying to load map tiles to: " + this.mapTileArchivePath);
        FileOutputStream fos = this.openFileOutput(this.mapTileArchivePath, Context.MODE_PRIVATE);
    
        InputStream in = getResources().openRawResource(R.raw.osmdroid);
        byte[] buff = new byte[1024];
        int read = 0;
        try {
        while ((read = in.read(buff)) > 0) {
          fos.write(buff, 0, read);
        }
        } finally {
          in.close();
          fos.close();
        }
        return new File(this.getFilesDir(), this.mapTileArchivePath);
        }
    


    Solution 1:

    OK! I know what I doing wrong and I have it all working now! (I'm excited :)

    Firstly, I had some trouble with writing my Raw resource map file to the application specific directory (e.g. openFileOutput()) I'm using a Galaxy Nexus which doesn't have an SD slot so I can't dump the map file to SD. Ensure the maps file you intend to use is byte compared with the original copy. Eclipse's DDMS perspective is useful to view a device's file structure.

    I also switched to the OSMdroid Zip format. I then made sure the XYTileSource() name matched the directory created in the Zip file by MOBAC, plus ensure the tile size and zoom levels match.

    XYTileSource TILERENDERER = new XYTileSource("OSM CloudMade 1", ResourceProxy.string.offline_mode, 16, 18, 256, ".png", "http://127.0.0.1");
    

    MOBAC by default will create 256 pixel tiles. I created an atlas file with 16, 17, and 18 zoom levels. PNG is the default MOBAC tile image format.

    Also, if your map file has any issues, ArchiveFileFactory.getArchiveFile() will catch them, even before MapTileFileArchiveProvider.

    Here's my usage. Just make every effort to get your IArchive setup correctly and you should be ok:

            XYTileSource TILERENDERER = new XYTileSource("OSM CloudMade 1", ResourceProxy.string.offline_mode, 16, 18, 256, ".png", "http://127.0.0.1");
            SimpleRegisterReceiver simpleReceiver = new SimpleRegisterReceiver(this.context);
            IArchiveFile[] archives = { ArchiveFileFactory.getArchiveFile(this.getMapsSdCard()) };
            MapTileModuleProviderBase moduleProvider = new MapTileFileArchiveProvider(
                    simpleReceiver, 
                    TILERENDERER, 
                    archives);
            this.mapProvider = new MapTileProviderArray(TILERENDERER, null, new MapTileModuleProviderBase[] { moduleProvider });
            this.mapProvider.setUseDataConnection(false);
            this.mapView = new MapView(this, 256, this.resourceProxy, this.mapProvider);
            this.mapView.setUseDataConnection(false);
    

    Maybe I'm the only one who had trouble with this, but osmdroid doesn't clearly document how to do this, and when I opened the issue I couldn't get them to comment on my usage. If they had said I was implementing MapTileFileArchiveProvider correctly or included a good offline mapping sample, I would have focused on everything else first.

    Solution 2:

    If you want to use sqlite db you only have to change

    ArchiveFileFactory.getArchiveFile(this.getMapsSdCard())

    to

    MBTilesFileArchive.getDatabaseFileArchive(f)

    where f is a File that points to your sqlite database.

  9. java - How to create a list with data tables as items that can be sorted
  10. Question:

    What I want to create
    I want to create a list where each item in that list is an data table like below: Example data table The user needs te be able to sort items in the data table and needs to be able to sort the data tables itself.

    Each table is going to represent a client and each item in that table is an order. The user will then collect the orders, if he collected an order the order wil dissapear but the user is also able to bring them back.

    What I tried
    I tried to put a Recyclerview inside a Recyclerview but this caused unitended side effects and bugs, also I read online that it is basically a bad practice. My initial intention was to use a recylcerview with a sortedlist.

    I did some searching online and a lot of people recommended using categories between items so that you only need one list. But because I have data tables (CardViews) that can be independent sorted this isn't an option.

    If anyone want to give me a nudge in the right direction, I would be really thankful.


    Solution 1:

    What you can do you can use recycler view and in custom row check box with 8 text views and when position is 0 inflate categories like fat, calcium etc and after that position populate data and if you want next button which shows next items in list use fragments or pagination that should do the trick and you can achieve this using single recycler view. You can also use header to show categories.