Re: [sqlite] How/Where to check if a table exists and read it ?

[Domingo Alvarez Duarte]

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 ?

[Richard Hipp]

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 ?

[Domingo Alvarez Duarte]

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 ?

[Domingo Alvarez Duarte]

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 ?

[Richard Hipp]

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 ?

[Domingo Alvarez Duarte]

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