Re: [gnome-db] Partial Meta Store update.

2009/7/20 Bas Driessen <[email protected]>

>  On Mon, 2009-07-20 at 13:49 +0200, Vivien Malerba wrote:
>
>
>
>  2009/7/20 Bas Driessen <[email protected]>
>
>
>   On Mon, 2009-07-20 at 10:52 +0200, Vivien Malerba wrote:
>
>
>
> 2009/7/20 Bas Driessen <[email protected]>
>
> On Sun, 2009-07-19 at 13:31 +0200, Vivien Malerba wrote:
>
>
>
> 2009/7/19 Bas Driessen <[email protected]>
>
> Hello,
>
> Question regarding partial meta store update. I found that the following to
> update a single table does not appear to work:
>
>        * g_print ("\nPartial metastore update for table '%s'...\n",
> TABLE_NAME);*
> *        GdaMetaContext mcontext = {"_tables", 1, NULL, NULL};*
> *        mcontext.column_names = g_new (gchar *, 1);*
> *        mcontext.column_names[0] = "table_name";*
> *        mcontext.column_values = g_new (GValue *, 1);*
> *        g_value_set_string ((mcontext.column_values[0] = gda_value_new
> (G_TYPE_STRING)), TABLE_NAME);*
> *        if (!gda_connection_update_meta_store (connection, &mcontext,
> &error))*
> *                return -1;*
>
> After this, my meta store is NOT updated.
>
>
> This may depend on the actual value of TABLE_NAME, specifically if it is a
> mix of upper and lower case, or if it's a reserved keyword. The proper way
> is:
> *tmp = gda_sql_identifier_quote (TABLE_NAME, cnc, NULL, TRUE, FALSE); //
> or with the last argument being TRUE, see the doc*
> *g_value_set_string ((mcontext.column_values[0] = gda_value_new
> (G_TYPE_STRING)), tmp);*
> *g_free (tmp);*
>
> Note that this is brand new (just been sorted out) and will require the
> master or LIBGDA_4.0 branches up to date.
>
>
>
>  Thanks Vivien, but this unfortunately is not still not working. Let me
> explain my testing scenario and if I understand this the correct way.
>
> -1 I have a database with a table called "groups"
> -2 I run the gda_connection_update_meta_store to update the meta store.
> -3 I look into the meta store with sqlite3 and execute "select * from
> _tables". I can see the "groups" table in that list.
>
>
> So far so good:)
>
>
>
> -4 I add a table called "parts"
> -5 I run the code above (with your modification) where the TABLE_NAME is
> set to "parts" (without quotes).
> -6 I execute "select * from _tables" and I expect to see the "parts" entry,
> but it is not there.
>
>
> It should be there...
>
>
>
> -7 I execute the gda_connection_update_meta_store to do the full update.
> -8 I execute "select * from _tables" and I expect to see the "parts" entry
> and now it is there.
>
> My questions:
>
> -1 Is my understanding of how this mechanism should work correct?
>
>
> Ye, it is.
>
>
>
> -2 Anything (obvious) that is not in the code above?
> -3 I use "gda_meta_store_new_with_file" to open my (sqlite3) meta store
> database. Is it possible that somehow it is mixing up the internal (memory)
> database with the external one?
>
>
> I don't think so, but I'd prefer to have some actual code to look at before
> I can say...
>
> If you send me a standlone program which shows the bug, I'll make the
> necessary corrections (either to Libgda or to your code).
>
>
>
>   Attached a small application that demonstrates the single table problem. Set up as follows:
>
>
> (postgresql) database with 1 more tables
> data source called "stocksql" (or change the connection name in the
> source).
>
> The program performs the following steps:
>
> -1 Open the connection
> -2 Open the meta store
> -3 Drop table "xparts" (if exists)
> -4 Build complete meta store
> -5 Create table "xparts"
> -6 Update SINGLE table meta store.
>
>
> After this open: sqlite3 /tmp/single.db
>
> select * from _tables
>
> There should be an entry for "xparts", but it is not there.
>
> Is this enough input?
>
> As a comment, I quickly put this together, so the error handling and
> program style is not the nicest :) Also I did not use DDL calls for drop
> table etc as that is not the focus anyway.
>
>
>
> It works for me (I've had to modify  "gchar tableName;" to "gchar
> *tableName;" but it's the only modification I've done):
>
> c0> select * from _tables where table_name = 'xparts';
> table_catalog | table_schema | table_name | table_type | is_insertable_into
> | table_comments | table_short_name | table_full_name | table_owner
>
> --------------+--------------+------------+------------+--------------------+----------------+------------------+-----------------+------------
> stocksql      | public       | xparts     | BASE TABLE | TRUE
> |                | xparts           | public.xparts   | vivien
> (1 row)
>
>
>
> You are right about the *tableName of course. Not sure why that * got
> dropped.
>
>  Which version of PostgreSQL and libgda are you using?
>
>
> I am on the latest LIBGDA_4.0 git branch. (so I am NOT on master, perhaps
> that may be the issue?)
>
> I am using postgresql 8.3.7  (default with Fedora 11)
>
> $ rpm -qa | grep postgresql
> postgresql-devel-8.3.7-1.fc11.x86_64
> postgresql-python-8.3.7-1.fc11.x86_64
> postgresql-libs-8.3.7-1.fc11.x86_64
> postgresql-server-8.3.7-1.fc11.x86_64
> postgresql-8.3.7-1.fc11.x86_64
>
> What additional permissions (grants) do I need in Postgresql to be able to
> retrieve the data that is used the build the meta store? For testing now I
> have assigned an administrator role, but that is probably not a  good idea
> in production?
>

No specific permission is required, except that the pg_catalog and
information_schema schemas need to be readable (but I think it's the case
all the time). Moreover, the meta data retreival works when updating all
(calling gda_connection_update_meta_store() with a NULL context).

I don't see why it does not work...

What you can do is uncomment the line 2248 of gda-meta-store.c to define
DEBUG_STORE_MODIFY, recompile and run your code again. You'll then have a
lot of information about the update process of the GdaMetaStore, and maybe
you'll see what's wrong. Be aware that when running
gda_connection_update_meta_store(), the whole process takes place in a
transaction so if something fails, then the meta store is reverted to what
it was before the call.

If you want you can send me the output it produces so I can have a look.

Vivien

_______________________________________________
gnome-db-list mailing list
[email protected]
http://mail.gnome.org/mailman/listinfo/gnome-db-list