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

[Bas Driessen <bas driessen xobas com>]

On Tue, 2009-07-21 at 00:03 +1000, Bas Driessen wrote:
>     On Mon, 2009-07-20 at 13:49 +0200, Vivien Malerba wrote:
> >         
> >         
> >         2009/7/20 Bas Driessen <bas driessen xobas com>
> > >             
> > >             On Mon, 2009-07-20 at 10:52 +0200, Vivien Malerba wrote:
> > > >                 
> > > >                 
> > > >                 2009/7/20 Bas Driessen <bas driessen xobas com>
> > > > >                     On Sun, 2009-07-19 at 13:31 +0200, Vivien Malerba wrote:
> > > > > >                         
> > > > > >                         
> > > > > >                         2009/7/19 Bas Driessen <bas driessen xobas com>
> > > > > > >                             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?


Is it possible to get the "single data meta store update" patch/code in the LIBGDA_4.0 branch as well?

Thanks,
Bas.