Re: [sqlite] How/Where to check if a table exists and read it ?
Hello Richard ! Looking at the documentation, code and code comments I found that what I'm looking for somehow already exists in sqlite3, I mean I want the functionality of "temp" database as permanent let's call this database as "meta" anything created inside "meta" database would persist on disk and can be read again. Can this be done ? Cheers ! On 05/10/16 16:44, Richard Hipp wrote: On 10/5/16, Domingo Alvarez Duarte wrote: I just found that the changes I made to sqlite3 to allow reference objects on attached databases does not work properly, Where in the view execution path the table/view qualifiers could be discarded ? I'm not sure exactly what you are looking for, perhaps you are seeking the "sqlite3Fix()" routines found in attach.c. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How/Where to check if a table exists and read it ?
On 10/5/16, Domingo Alvarez Duarte wrote: > > I just found that the changes I made to sqlite3 to allow reference > objects on attached databases does not work properly, > > Where in the view execution path the table/view qualifiers could be > discarded ? > I'm not sure exactly what you are looking for, perhaps you are seeking the "sqlite3Fix()" routines found in attach.c. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How/Where to check if a table exists and read it ?
Hello Richard ! I just found that the changes I made to sqlite3 to allow reference objects on attached databases does not work properly, it does work when the tables/views from more than one database are not equal but if they are equal even with qualification it seems to always try to find first in the "main" database without qualification or always discard qualification. Where in the view execution path the table/view qualifiers could be discarded ? Cheers ! Example: .open db1.db create table if not exists t(id integer, count1 integer, count2 integer); insert or ignore into t values(1, 5, 5); create table if not exists t1(id integer, count1 integer, count2 integer); insert or ignore into t1 values(1, 1, 1); .open db2.db create table if not exists t(id integer, count1 integer, count2 integer); insert or ignore into t values(1, 10, 10); create table if not exists t2(id integer, count1 integer, count2 integer); insert or ignore into t2 values(1, 2, 2); .open db1.db pragma use_attached_dbs=ON; attach database 'db2.db' as db2; select a.count1+b.count1, a.count2+b.count2 from t1 a join t2 b on a.id=b.id; --3|3 create view if not exists v1 as select a.count1+b.count1, a.count2+b.count2 from t1 a join t2 b on a.id=b.id; select * from v1; --3|3 select a.count1+b.count1, a.count2+b.count2 from t a join db2.t b on a.id=b.id; --15|15 create view if not exists v2 as select a.count1+b.count1, a.count2+b.count2 from t a join db2.t b on a.id=b.id; select * from v2; --10|10 On 04/10/16 17:30, Richard Hipp wrote: On 10/4/16, Domingo Alvarez Duarte wrote: The problem is I didn't found yet the point where I should intercept the "openDatabase" where I plan to check if there is a table named for example "use_attached_dbs" and then attach the databases on that table and then reread the schema to make the views work. The place to do so probably is at the end of "openDatabase", can someone shed some light here ? The sqlite3Init() routine found at https://www.sqlite.org/src/artifact/b1140c3d0cf59bc8?ln=355 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How/Where to check if a table exists and read it ?
Hello Richard ! Thanks for reply ! I found the second point that was also controlling the restriction of referencing objects in other databases. Now it seems to work and I'll leave the initialization to the user level code for now, when the usage normalize I'll revisit it again to see if is worth move that code to my custom sqlite3. Cheers ! my changes /* ** PRAGMA use_attached_dbs ** PRAGMA use_attached_dbs = ON/OFF ** ** The first form reports the current setting for the ** use_attached_dbs flag. The second form changes the use_attached_dbs ** flag setting and reports thenew value. */ case PragTyp_USE_ATTACHED_DBS: { int b = -1; if( zRight ){ b = sqlite3GetBoolean(zRight, 0); sqlite3_limit(db, SQLITE_LIMIT_USE_ATTACHED_DBS, b); } b = sqlite3_limit(db, SQLITE_LIMIT_USE_ATTACHED_DBS, -1); returnSingleInt(v, "use_attached_dbs", b); break; } Table *sqlite3LocateTableItem( Parse *pParse, u32 flags, struct SrcList_item *p ){ const char *zDb; int use_attached_dbs = sqlite3_limit(pParse->db, SQLITE_LIMIT_USE_ATTACHED_DBS, -1); my changes assert( p->pSchema==0 || p->zDatabase==0 ); if( p->pSchema && !use_attached_dbs){ my changes int iDb = sqlite3SchemaToIndex(pParse->db, p->pSchema); zDb = pParse->db->aDb[iDb].zDbSName; }else{ zDb = p->zDatabase; } return sqlite3LocateTable(pParse, flags, p->zName, zDb); } int sqlite3FixSrcList( DbFixer *pFix, /* Context of the fixation */ SrcList *pList /* The Source list to check and modify */ ){ int i, use_attached_dbs; const char *zDb; struct SrcList_item *pItem; use_attached_dbs = sqlite3_limit(pFix->pParse->db, SQLITE_LIMIT_USE_ATTACHED_DBS, -1); my changes if( NEVER(pList==0) ) return 0; zDb = pFix->zDb; for(i=0, pItem=pList->a; inSrc; i++, pItem++){ if( pFix->bVarOnly==0 ){ if( pItem->zDatabase && !use_attached_dbs && sqlite3StrICmp(pItem->zDatabase, zDb) ){ my changes sqlite3ErrorMsg(pFix->pParse, "%s %T cannot reference objects in database %s", pFix->zType, pFix->pName, pItem->zDatabase); return 1; } sqlite3DbFree(pFix->pParse->db, pItem->zDatabase); pItem->zDatabase = 0; pItem->pSchema = pFix->pSchema; } #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER) if( sqlite3FixSelect(pFix, pItem->pSelect) ) return 1; if( sqlite3FixExpr(pFix, pItem->pOn) ) return 1; #endif } return 0; } On 04/10/16 17:30, Richard Hipp wrote: On 10/4/16, Domingo Alvarez Duarte wrote: The problem is I didn't found yet the point where I should intercept the "openDatabase" where I plan to check if there is a table named for example "use_attached_dbs" and then attach the databases on that table and then reread the schema to make the views work. The place to do so probably is at the end of "openDatabase", can someone shed some light here ? The sqlite3Init() routine found at https://www.sqlite.org/src/artifact/b1140c3d0cf59bc8?ln=355 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How/Where to check if a table exists and read it ?
On 10/4/16, Domingo Alvarez Duarte wrote: >> > The problem is I didn't found yet the point where I should intercept the > "openDatabase" where I plan to check if there is a table named for > example "use_attached_dbs" and then attach the databases on that table > and then reread the schema to make the views work. > > The place to do so probably is at the end of "openDatabase", can someone > shed some light here ? > The sqlite3Init() routine found at https://www.sqlite.org/src/artifact/b1140c3d0cf59bc8?ln=355 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How/Where to check if a table exists and read it ?
Hello ! I'm trying to make changes to sqlite to allow multi-databases databases (I mean have a database that is onl used to anchor attached databases and store mutli-database views/triggers). I already managed to add a new pragma "use_attached_databases=ON/OFF" to allow views/triggers to have references to attached databases and have it working (I mean I can create views/triggers that references other databases and it's accepted). The problem is when I open a database that contains those views/triggers it shows errors saying: Error: malformed database schema (v1) - view v1 cannot reference objects in database db1 The problem is I didn't found yet the point where I should intercept the "openDatabase" where I plan to check if there is a table named for example "use_attached_dbs" and then attach the databases on that table and then reread the schema to make the views work. The place to do so probably is at the end of "openDatabase", can someone shed some light here ? P.S.: This is for a custom use of sqlite3 so please don't bother to reply to say why I should not been doing this. Cheers ! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users