Re: [sqlite] fixing time-warp

[E. Timothy Uy]

Is that a philosophical question? :)

On Mon, Nov 3, 2014 at 5:52 PM, Richard Hipp  wrote:

> On Mon, Nov 3, 2014 at 8:46 PM, E. Timothy Uy  wrote:
>
> > Ok, I now see that you intentionally left 2 time-warps in place. It would
> > be helpful to make that as a note for exporting to git.
> >
>
> Why would I want to export the SQLite history to Git?
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fixing time-warp

[Richard Hipp]

On Mon, Nov 3, 2014 at 8:46 PM, E. Timothy Uy  wrote:

> Ok, I now see that you intentionally left 2 time-warps in place. It would
> be helpful to make that as a note for exporting to git.
>

Why would I want to export the SQLite history to Git?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fixing time-warp

[E. Timothy Uy]

Guess my photo got blocked -
https://www.sqlite.org/src/timeline?c=2010-09-28+20:26:44 shows the
time-warp but also the tag messages fixing them (which I guess you later
unfixed in the db).

On Mon, Nov 3, 2014 at 5:46 PM, E. Timothy Uy  wrote:

> Ok, I now see that you intentionally left 2 time-warps in place. It would
> be helpful to make that as a note for exporting to git. It is also
> confusing that the tags are there showing that they were fixed.
>
> On Mon, Nov 3, 2014 at 5:44 PM, E. Timothy Uy  wrote:
>
>> Dear Richard,
>>
>> It is strange, but if you look at the timeline image I sent (second
>> email), your time-warp tag changes are clearly there but did not stick on
>> objid 35450 and 35460. I just manually did mine on this end (following your
>> same times) and it is now ok, though I feel a bit dirty changing commit
>> times!
>>
>> Leaving the time-warp in place will affect git export. Also, the other
>> barrier that blocks git export of the current sqlite version is that in the
>> fossil export.c, the pid needs to be filtered by type='ci' as one of the
>> parents is type='g' (tag). I have that in some notes in
>> http://www.fossil-scm.org/index.html/tktview?name=4013b0a81a
>>
>> db_prepare(,
>>   "SELECT pid FROM plink"
>>   " WHERE cid=%d AND isprim"
>>   "   AND pid IN (SELECT objid FROM event WHERE type='ci')",
>>   ckinId
>> );
>>
>> Respectfully,
>> Tim
>>
>> On Mon, Nov 3, 2014 at 5:37 PM, Richard Hipp  wrote:
>>
>>> On Mon, Nov 3, 2014 at 8:13 PM, E. Timothy Uy  wrote:
>>>
>>> > I found some posts in the past describing fixing time-warps using
>>> tags. How
>>> > does this process get initiated? I found two while trying to export to
>>> .git
>>> >
>>>
>>>
>>> The test_timewarps webpage will show them all to you.  Example:
>>>
>>>  http://www.sqlite.org/src/test_timewarps
>>>
>>> In the example above, most of the timewarps have been fixed.  To fix
>>> them,
>>> simple edit check-ins and change their time.
>>>
>>> I intentionally left one timewarp in SQLite unfixed.  See the
>>> https://www.sqlite.org/src/timeline?p=3f30f00a384d23=3f30f00a384d235
>>> timeline.  I left this one as a test case for ensuring that Fossil can
>>> display timewarps correctly.
>>>
>>>
>>>
>>>
>>> >
>>> > sqlite> SELECT l.*, ep.mtime, ec.mtime FROM plink l LEFT JOIN event ep
>>> ON
>>> > pid = ep.objid LEFT JOIN event ec ON cid = ec.objid WHERE ep.mtime >
>>> > ec.mtime LIMIT 10;
>>> >
>>> > 35460|35462|1|2455469.2683|2455469.46484954|2455469.2683
>>> >
>>> > 35450|35453|1|2455468.35189815|2455468.80332176|2455468.35189815
>>> >
>>> > Respectfully,
>>> >
>>> > Tim
>>> > ___
>>> > sqlite-users mailing list
>>> > sqlite-users@sqlite.org
>>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> >
>>>
>>>
>>>
>>> --
>>> D. Richard Hipp
>>> d...@sqlite.org
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fixing time-warp

[E. Timothy Uy]

Ok, I now see that you intentionally left 2 time-warps in place. It would
be helpful to make that as a note for exporting to git. It is also
confusing that the tags are there showing that they were fixed.

On Mon, Nov 3, 2014 at 5:44 PM, E. Timothy Uy  wrote:

> Dear Richard,
>
> It is strange, but if you look at the timeline image I sent (second
> email), your time-warp tag changes are clearly there but did not stick on
> objid 35450 and 35460. I just manually did mine on this end (following your
> same times) and it is now ok, though I feel a bit dirty changing commit
> times!
>
> Leaving the time-warp in place will affect git export. Also, the other
> barrier that blocks git export of the current sqlite version is that in the
> fossil export.c, the pid needs to be filtered by type='ci' as one of the
> parents is type='g' (tag). I have that in some notes in
> http://www.fossil-scm.org/index.html/tktview?name=4013b0a81a
>
> db_prepare(,
>   "SELECT pid FROM plink"
>   " WHERE cid=%d AND isprim"
>   "   AND pid IN (SELECT objid FROM event WHERE type='ci')",
>   ckinId
> );
>
> Respectfully,
> Tim
>
> On Mon, Nov 3, 2014 at 5:37 PM, Richard Hipp  wrote:
>
>> On Mon, Nov 3, 2014 at 8:13 PM, E. Timothy Uy  wrote:
>>
>> > I found some posts in the past describing fixing time-warps using tags.
>> How
>> > does this process get initiated? I found two while trying to export to
>> .git
>> >
>>
>>
>> The test_timewarps webpage will show them all to you.  Example:
>>
>>  http://www.sqlite.org/src/test_timewarps
>>
>> In the example above, most of the timewarps have been fixed.  To fix them,
>> simple edit check-ins and change their time.
>>
>> I intentionally left one timewarp in SQLite unfixed.  See the
>> https://www.sqlite.org/src/timeline?p=3f30f00a384d23=3f30f00a384d235
>> timeline.  I left this one as a test case for ensuring that Fossil can
>> display timewarps correctly.
>>
>>
>>
>>
>> >
>> > sqlite> SELECT l.*, ep.mtime, ec.mtime FROM plink l LEFT JOIN event ep
>> ON
>> > pid = ep.objid LEFT JOIN event ec ON cid = ec.objid WHERE ep.mtime >
>> > ec.mtime LIMIT 10;
>> >
>> > 35460|35462|1|2455469.2683|2455469.46484954|2455469.2683
>> >
>> > 35450|35453|1|2455468.35189815|2455468.80332176|2455468.35189815
>> >
>> > Respectfully,
>> >
>> > Tim
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>>
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fixing time-warp

[E. Timothy Uy]

Dear Richard,

It is strange, but if you look at the timeline image I sent (second email),
your time-warp tag changes are clearly there but did not stick on objid
35450 and 35460. I just manually did mine on this end (following your same
times) and it is now ok, though I feel a bit dirty changing commit times!

Leaving the time-warp in place will affect git export. Also, the other
barrier that blocks git export of the current sqlite version is that in the
fossil export.c, the pid needs to be filtered by type='ci' as one of the
parents is type='g' (tag). I have that in some notes in
http://www.fossil-scm.org/index.html/tktview?name=4013b0a81a

db_prepare(,
  "SELECT pid FROM plink"
  " WHERE cid=%d AND isprim"
  "   AND pid IN (SELECT objid FROM event WHERE type='ci')",
  ckinId
);

Respectfully,
Tim

On Mon, Nov 3, 2014 at 5:37 PM, Richard Hipp  wrote:

> On Mon, Nov 3, 2014 at 8:13 PM, E. Timothy Uy  wrote:
>
> > I found some posts in the past describing fixing time-warps using tags.
> How
> > does this process get initiated? I found two while trying to export to
> .git
> >
>
>
> The test_timewarps webpage will show them all to you.  Example:
>
>  http://www.sqlite.org/src/test_timewarps
>
> In the example above, most of the timewarps have been fixed.  To fix them,
> simple edit check-ins and change their time.
>
> I intentionally left one timewarp in SQLite unfixed.  See the
> https://www.sqlite.org/src/timeline?p=3f30f00a384d23=3f30f00a384d235
> timeline.  I left this one as a test case for ensuring that Fossil can
> display timewarps correctly.
>
>
>
>
> >
> > sqlite> SELECT l.*, ep.mtime, ec.mtime FROM plink l LEFT JOIN event ep ON
> > pid = ep.objid LEFT JOIN event ec ON cid = ec.objid WHERE ep.mtime >
> > ec.mtime LIMIT 10;
> >
> > 35460|35462|1|2455469.2683|2455469.46484954|2455469.2683
> >
> > 35450|35453|1|2455468.35189815|2455468.80332176|2455468.35189815
> >
> > Respectfully,
> >
> > Tim
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fixing time-warp

[Richard Hipp]

On Mon, Nov 3, 2014 at 8:13 PM, E. Timothy Uy  wrote:

> I found some posts in the past describing fixing time-warps using tags. How
> does this process get initiated? I found two while trying to export to .git
>


The test_timewarps webpage will show them all to you.  Example:

 http://www.sqlite.org/src/test_timewarps

In the example above, most of the timewarps have been fixed.  To fix them,
simple edit check-ins and change their time.

I intentionally left one timewarp in SQLite unfixed.  See the
https://www.sqlite.org/src/timeline?p=3f30f00a384d23=3f30f00a384d235
timeline.  I left this one as a test case for ensuring that Fossil can
display timewarps correctly.




>
> sqlite> SELECT l.*, ep.mtime, ec.mtime FROM plink l LEFT JOIN event ep ON
> pid = ep.objid LEFT JOIN event ec ON cid = ec.objid WHERE ep.mtime >
> ec.mtime LIMIT 10;
>
> 35460|35462|1|2455469.2683|2455469.46484954|2455469.2683
>
> 35450|35453|1|2455468.35189815|2455468.80332176|2455468.35189815
>
> Respectfully,
>
> Tim
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] fixing time-warp

[E. Timothy Uy]

I found some posts in the past describing fixing time-warps using tags. How
does this process get initiated? I found two while trying to export to .git

sqlite> SELECT l.*, ep.mtime, ec.mtime FROM plink l LEFT JOIN event ep ON
pid = ep.objid LEFT JOIN event ec ON cid = ec.objid WHERE ep.mtime >
ec.mtime LIMIT 10;

35460|35462|1|2455469.2683|2455469.46484954|2455469.2683

35450|35453|1|2455468.35189815|2455468.80332176|2455468.35189815

Respectfully,

Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index without backing table

[James K. Lowden]

On Mon, 03 Nov 2014 11:50:17 +0200
Paul  wrote:

> > > Would be nice to have ability to store both key and payload in the
> > > index. (Let's call it index-only table)
> > > This could be a feature that sets some limitations on a table,
> > > like being unable to have more than one index or inefficient
> > > table scans, but it will also give some advantage in special
> > > cases like mine.
> > 
> > What you're describing sounds very much to me like a SQLite table.
> > See http://www.sqlite.org/fileformat2.html, section 1.5, the
> > reference to "index b-tree".  
> 
> So, to be clear, WITHOUT ROWID table will have it's PRIMARY KEY 
> as a replacement for ROWID and table itself is an index?

Yes, approximately.  http://www.sqlite.org/withoutrowid.html says: 

> CREATE TABLE IF NOT EXISTS wordcount(
>   word TEXT PRIMARY KEY,
>   cnt INTEGER
> ) WITHOUT ROWID;
> 
> In this latter table, there is only a single B-Tree which uses the
> "word" column as its key and the "cnt" column as its data.  

That is, the table is stored as a B-tree with the declared primary key
as the B-tree key.  

I wouldn't say, "the table is an index".  I reserve the word "index" in
this sense to mean "something that speeds up searching in something
else", and in this case there's no "else".  

The table is stored on disk as a tree.  A tree can be used as an index,
and an index may be implemented as a tree.  But not every tree is an
index.  

HTH.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index without backing table

[Paul]

3 November 2014, 13:56:36, by "Richard Hipp" :
 
>   On Mon, Nov 3, 2014 at 6:48 AM, Clemens Ladisch  wrote:
> 
> > Paul wrote:
> > > Are additional indices, created for WITHOUT ROWID, potentially less
> > > efficient and more cumbersome?
> >
> > For tables with a rowid, the index stores the indexed columns and the
> > rowid.  For WITHOUT ROWID tables, the index stores the indexed columns
> > and the primary key.
> >
> 
> And, lookup by rowid is (usually) faster than lookup by arbitrary primary
> key.  So, yes, WITHOUT ROWIDs might be a little slower when using secondary
> indexes.
> 
> But in a complex system, many factors come into play.  It is best to give
> it a try.
> 
> Realize that any schema that works with WITHOUT ROWID on a table, will work
> just as well with the WITHOUT ROWID omitted.  So you can test your
> application both with and without the WITHOUT ROWID clause and see which
> gives the better performance, without making any changes to your internal
> queries.
> 

Thank you, for clarification, Dr. Hipp.

I'll definitely give it a try, especially after the fact that they (tables) 
work identically.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index without backing table

[Richard Hipp]

On Mon, Nov 3, 2014 at 6:48 AM, Clemens Ladisch  wrote:

> Paul wrote:
> > Are additional indices, created for WITHOUT ROWID, potentially less
> > efficient and more cumbersome?
>
> For tables with a rowid, the index stores the indexed columns and the
> rowid.  For WITHOUT ROWID tables, the index stores the indexed columns
> and the primary key.
>

And, lookup by rowid is (usually) faster than lookup by arbitrary primary
key.  So, yes, WITHOUT ROWIDs might be a little slower when using secondary
indexes.

But in a complex system, many factors come into play.  It is best to give
it a try.

Realize that any schema that works with WITHOUT ROWID on a table, will work
just as well with the WITHOUT ROWID omitted.  So you can test your
application both with and without the WITHOUT ROWID clause and see which
gives the better performance, without making any changes to your internal
queries.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index without backing table

[Paul]

3 November 2014, 13:48:30, by "Clemens Ladisch" :
 
>   Paul wrote:
> > Are additional indices, created for WITHOUT ROWID, potentially less
> > efficient and more cumbersome?
> 
> For tables with a rowid, the index stores the indexed columns and the
> rowid.  For WITHOUT ROWID tables, the index stores the indexed columns
> and the primary key.
> 

Just what I thought, thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index without backing table

[Clemens Ladisch]

Paul wrote:
> Are additional indices, created for WITHOUT ROWID, potentially less
> efficient and more cumbersome?

For tables with a rowid, the index stores the indexed columns and the
rowid.  For WITHOUT ROWID tables, the index stores the indexed columns
and the primary key.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index without backing table

[Paul]

 > 
> On 3 Nov 2014, at 9:50am, Paul  wrote:
> 
> > So, to be clear, WITHOUT ROWID table will have it's PRIMARY KEY 
> > as a replacement for ROWID and table itself is an index?
> 
> It would appear that the answer is "yes". I'm not going to go beyond the 
> official documentation at
> 
> 
> 
> but if you have a specific question, please post it.
> 
> Simon.

Dr. Hipp has pointed out to use WITHOUT ROWID table, but I was too ignorant to 
attentively read the whole page :( 


I have one more question though:

Are additional indices, created for WITHOUT ROWID, potentially less efficient 
and more cumbersome?

Thank You, 
Paul
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index without backing table

[Simon Slavin]

On 3 Nov 2014, at 9:50am, Paul  wrote:

> So, to be clear, WITHOUT ROWID table will have it's PRIMARY KEY 
> as a replacement for ROWID and table itself is an index?

It would appear that the answer is "yes".  I'm not going to go beyond the 
official documentation at



but if you have a specific question, please post it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index without backing table

[Paul]

> 
> > Would be nice to have ability to store both key and payload in the
> > index. (Let's call it index-only table)
> > This could be a feature that sets some limitations on a table, like
> > being unable to have more than one index or inefficient table scans,
> > but it will also give some advantage in special cases like mine.
> 
> What you're describing sounds very much to me like a SQLite table.  See
> http://www.sqlite.org/fileformat2.html, section 1.5, the reference to
> "index b-tree".  
> 
> You're in good company.  The technique of storing key and value together
> "in order" in some sense is as old as databases.  
> 
> --jkl

So, to be clear, WITHOUT ROWID table will have it's PRIMARY KEY 
as a replacement for ROWID and table itself is an index?

If so, it perfectly matches my needs.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users