SQL details

This is mainly a reply to anonymous from the last post's comments.

The database uses Android's built-in SQLite3. It's a local database - a tad
overkill at this point, but writing a dataprovider seems to be the easiest way to send potentially large arrays of data to the apps.

So what I'm doing, or going to be doing at any rate, is building up scene/narrative lists from XML and using them to drive the plot segments.

I think the SQL itself is all right. I have three tables:

                        mk_table(db, "create table narrative ("
                                + "     " + idstring + ", "
                                + "     ev_name varchar not null"
                                + ");"
                        );
                        count_narratives(db);

                        mk_table(db, "create table narrative_string ("
                                + "     " + idstring
                                + "     , ev_name varchar not null"
                                + "     , seq integer not null"
                                + "     , text_id integer not null"
                                + ");"
                        );

                        mk_table(db, "create table narrative_image ("
                                + "     " + idstring
                                + "     , ev_name varchar not null"
                                + "     , seq integer not null"
                                + "     , image_id integer not null"
                                + ");"
                        );

Where mk_table is defined thusly:

                private void mk_table(SQLiteDatabase db, String sql)
                {
                        Log.d(TAG, "creating table: " + sql);
                        db.execSQL(sql);
                }

The count_narratives function is just to sanity check things:

                private int count_narratives(SQLiteDatabase db)
                {
                        Cursor c;
                        String sql = "select count(*) n from " + TAB_NARRATIVE;

                        c = db.rawQuery(sql, null);
                        if(c == null) {
                                Log.d(TAG, "count_narratives: can't get cursor");
                                return -1;
                        }
                        Boolean bv = c.moveToFirst();
                        if(!bv) {
                                Log.d(TAG, "count_narratives: no rows for count(*)!");
                                return -1;
                        }
                        int n = c.getInt(0);
                        c.close();
                        Log.d(TAG, "count_narratives: "+n+" narratives added!");
                        return n;
                }

So if the table creates, that should always work. After I create the narrative table it tells me the table has zero rows (and therefore that it exists). So far so good.

I then populate the database:

                        Log.d(TAG, "starting intro narrative.");
                        int seq = 1;
                        db.beginTransaction();
                        insert_narrative(db, "intro", seq++, R.string.intro1, R.drawable.alley);
                        count_narratives(db);
                        insert_narrative(db, "intro", seq++, R.string.intro2);
                        count_narratives(db);
                        insert_narrative(db, "intro", seq++, R.string.intro3);
                        count_narratives(db);
                        insert_narrative(db, "intro", seq++, R.string.intro4);
                        count_narratives(db);
                        insert_narrative(db, "intro", seq++, R.string.intro5);
                        count_narratives(db);
                        insert_narrative(db, "intro", seq++, R.string.intro6);
                        insert_narrative(db, "intro", seq++, R.string.intro7);
                        insert_narrative(db, "intro", seq++, R.string.intro8);
                        insert_narrative(db, "intro", seq++, R.string.intro9, R.drawable.girl1);
                        insert_narrative(db, "intro", seq++, R.string.intro10);
                        insert_narrative(db, "intro", seq++, R.string.intro11);
                        insert_narrative(db, "intro", seq++, R.string.intro12);
                        db.endTransaction();
                        Log.d(TAG, "final narrative count.");
                        count_narratives(db);
                        Log.d(TAG, "final narrative count done.");
                        Log.d(TAG, "end of intro narrative.");

The count_narratives calls in here show the table continuing to exist, and with the expected number of rows. Again: so far, so good.

Then I try and retrieve the data:

                String sql = ""
                        + "select\n"
                        + "     e.ev_name,\n"
                        + "     s.seq,\n"
                        + "     s.text_id,\n"
                        + "     i.image_id\n"
                        + "from\n"
                        + "     "+TAB_NARRATIVE+" e\n"
                        + "     left outer join narrative_string s on\n"
                        + "             e.ev_name = s.ev_name\n"
                        + "     left outer join narrative_image i on\n"
                        + "             e.ev_name = i.ev_name\n"
                        + "where\n"
                        + "     e.ev_name = '" + narrative_name + "'\n"
                        + "order by\n"
                        + "     1, 2\n"
                ;

And for some reason the table no longer exists. So either A) I'm accidentally dropping the data after creation (possible, but I don't know where) or B) I'm accidentally opening a non-existent database for the query and SQLite is creating a new empty database, or else C) something very weird is happening.

I'm tending toward B) myself.