imposter tables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

imposter tables

Paul Sanderson
I am just taking a look at imposter tables and while the implementation is
neat I am just wondering what their use is, or rather what they can achieve
that a view can't achieve (and without the risk of DB corruption).

For instance an imposter table created on an index such as the following
from Skype

CREATE INDEX chat_idx_chat_room_name_service_name ON chat(room_name,
service_name)

.imposter chat_idx_chat_room_name_service_name imptable

can be simulated with a view

CREATE TEMP VIEW impview AS select room_name, service_name, _rowid_ FROM
chat

Querying either impview or imptable should, as far as I can see, produce
the same results.

I can see that the imposter table will be faster as it links directly to
the b-tree, but with the risk of corrupting the index as described on
https://sqlite.org/imposter.html.

The only benefit I can see is that you know the imposter table is showing
you exactly what is in the index, where the view is my interpretation of
the SQL needed to show what is in the index. Is this the main benefit? or
am I missing something?

Are there instances where a view created as I have done above cannot
simulate an imposter table?



Cheers






Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786 <+44%201326%20572786>
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: imposter tables

Simon Slavin-3


On 14 Jun 2017, at 12:52pm, Paul Sanderson <[hidden email]> wrote:

> The only benefit I can see is that you know the imposter table is showing
> you exactly what is in the index, where the view is my interpretation of
> the SQL needed to show what is in the index. Is this the main benefit? or
> am I missing something?

Hmm.  If you create a view with calculated columns, and then create an impostor table on that view, do you get a virtual table with pre-calculated columns ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: imposter tables

Paul Sanderson
Can you create an imposter table on a view. A view has no associated b-tree
so I would think not!

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 14 June 2017 at 13:11, Simon Slavin <[hidden email]> wrote:

>
>
> On 14 Jun 2017, at 12:52pm, Paul Sanderson <[hidden email]>
> wrote:
>
> > The only benefit I can see is that you know the imposter table is showing
> > you exactly what is in the index, where the view is my interpretation of
> > the SQL needed to show what is in the index. Is this the main benefit? or
> > am I missing something?
>
> Hmm.  If you create a view with calculated columns, and then create an
> impostor table on that view, do you get a virtual table with pre-calculated
> columns ?
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: imposter tables

Stephen Chrzanowski
I didn't know about this functionality.  I know it is a 'sharp tool', but
what I've gathered out by reading the document is that this only affects
indexes, and if I understand the doc, only when the imposter table is
created or is used to modify the indexes.  What if an imposter table is
created at database inception, and is not changed or modified, and only
used for R/O queries?  Does it become a blunt instrument or is it still a
sharp utility knife?

Personally, I doubt I'd ever use this.  I've never required knowledge of
what the index is beyond faith in knowing that the engine knows what its
doing, or, needs to get updated periodically.  (I know of different methods
of indexing in different engines, but, never needed to know which is used
or implemented)


On Wed, Jun 14, 2017 at 8:22 AM, Paul Sanderson <
[hidden email]> wrote:

> Can you create an imposter table on a view. A view has no associated b-tree
> so I would think not!
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 14 June 2017 at 13:11, Simon Slavin <[hidden email]> wrote:
>
> >
> >
> > On 14 Jun 2017, at 12:52pm, Paul Sanderson <[hidden email]
> >
> > wrote:
> >
> > > The only benefit I can see is that you know the imposter table is
> showing
> > > you exactly what is in the index, where the view is my interpretation
> of
> > > the SQL needed to show what is in the index. Is this the main benefit?
> or
> > > am I missing something?
> >
> > Hmm.  If you create a view with calculated columns, and then create an
> > impostor table on that view, do you get a virtual table with
> pre-calculated
> > columns ?
> >
> > Simon.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: imposter tables

Jens Alfke-2
In reply to this post by Paul Sanderson

> On Jun 14, 2017, at 4:52 AM, Paul Sanderson <[hidden email]> wrote:
>
> I am just taking a look at imposter tables and while the implementation is
> neat I am just wondering what their use is

Well, right near the top of the page it says "Imposter tables are intended for analysis and debugging only.” So it doesn’t sound like they’re for use in a production system.

(On the other hand, my mind immediately jumps to wondering if they could be used to implement map/reduce views (a la CouchDB). The indexes-on-expressions feature gets partway toward enabling map/reduce, except for the fact it can only add a single row to the index per table row.)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: imposter tables

Richard Hipp-3
In reply to this post by Paul Sanderson
On 6/14/17, Paul Sanderson <[hidden email]> wrote:
> I am just taking a look at imposter tables and while the implementation is
> neat I am just wondering what their use is, or rather what they can achieve
> that a view can't achieve (and without the risk of DB corruption).

Imposter tables are used by the RBU extension
(https://www.sqlite.org/rbu.html) to allow indexes to be updated
independently from tables, and in key order, for reduced write
amplification and improved efficiency during bulk updates.

Minor coding errors in the use of imposter tables can corrupt the
database. If you feel you ust use them, do so with with great care.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: imposter tables

Paul Sanderson
Thanks Richard - the View approach is fine for my needs - just wanted to
know what the rationale was for the imposter tables given the ability to
simulate the imposter table with a view.

Paul

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 15 June 2017 at 00:09, Richard Hipp <[hidden email]> wrote:

> On 6/14/17, Paul Sanderson <[hidden email]> wrote:
> > I am just taking a look at imposter tables and while the implementation
> is
> > neat I am just wondering what their use is, or rather what they can
> achieve
> > that a view can't achieve (and without the risk of DB corruption).
>
> Imposter tables are used by the RBU extension
> (https://www.sqlite.org/rbu.html) to allow indexes to be updated
> independently from tables, and in key order, for reduced write
> amplification and improved efficiency during bulk updates.
>
> Minor coding errors in the use of imposter tables can corrupt the
> database. If you feel you ust use them, do so with with great care.
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: imposter tables

R Smith

On 2017/06/15 10:15 AM, Paul Sanderson wrote:
> Thanks Richard - the View approach is fine for my needs - just wanted to
> know what the rationale was for the imposter tables given the ability to
> simulate the imposter table with a view.

I'm thinking the easiest way to describe it is perhaps in programming
terms -
a view is like a function that computes and produces a result that may
or may not be a simple reflection of an internal variable,
and
an imposter table is rather more like a variable that lives at the same
memory address as another data structure so that querying it queries the
underlying structure direct (this part is also achievable by a view),
but changes to it also change in the underlying data structure (very
unlike a view).

This is great if you are looking for a way to update an index
independently from its data, but is also, as has been pointed out
numerous times, quite dangerous - but then, breaking it is mostly
fixable by a simple REINDEX.

Not sure if you can install such an imposter table on a corrupt database
file, moreso than a View anyway, but that actually might have some
utility towards retrieving or fixing (or at least gaining a better
understanding of) a corrupted data table / index, which I think Paul
might be interested in.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: imposter tables

Paul Sanderson
Thanks Richard

I understand how it works, quite simple really, and knowing they are used
by the RBU extension explains why.

I am working on a book and I just wanted to understand how/where it could
be used where a view, created on the same columns in an index, couldn't be.



Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 15 June 2017 at 12:58, R Smith <[hidden email]> wrote:

>
> On 2017/06/15 10:15 AM, Paul Sanderson wrote:
>
>> Thanks Richard - the View approach is fine for my needs - just wanted to
>> know what the rationale was for the imposter tables given the ability to
>> simulate the imposter table with a view.
>>
>
> I'm thinking the easiest way to describe it is perhaps in programming
> terms -
> a view is like a function that computes and produces a result that may or
> may not be a simple reflection of an internal variable,
> and
> an imposter table is rather more like a variable that lives at the same
> memory address as another data structure so that querying it queries the
> underlying structure direct (this part is also achievable by a view), but
> changes to it also change in the underlying data structure (very unlike a
> view).
>
> This is great if you are looking for a way to update an index
> independently from its data, but is also, as has been pointed out numerous
> times, quite dangerous - but then, breaking it is mostly fixable by a
> simple REINDEX.
>
> Not sure if you can install such an imposter table on a corrupt database
> file, moreso than a View anyway, but that actually might have some utility
> towards retrieving or fixing (or at least gaining a better understanding
> of) a corrupted data table / index, which I think Paul might be interested
> in.
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...