Re: [sqlite] Help . . . I want to Export my Firefox Bookmarks to .CSV in one click, using SQLite3 in a .BAT file

[Clemens Ladisch]

You have to do two things to run SQL statements from a batch file:
1. Use quotes so that all parameters to sqlite3.exe are a single parameter; and
2. Use quotes so that SQL strings are delimited correctly.

e-mail wrote:
> sqlite3.exe -csv "C:\...\places.sqlite" "SELECT ... 
> datetime(...,"unixepoch","localtime") AS Date FROM ..."

Double quotes are interpreted by cmd.exe to delimit parameters to sqlite3.exe,
so they will not show up in the actual SQL statement as seen by sqlite3.

> sqlite3.exe -csv C:\...\places.sqlite SELECT ... 
> datetime(...,'unixepoch','localtime') AS Date FROM ...

Without quotes, there are too many parameters.


In SQL, strings should use single quotes (double quotes are just for bug
compatibility with MySQL); and cmd.exe expects double quotes for parameters.
So this is easy:

  sqlite3.exe -csv "C:\...\places.sqlite" "SELECT ... 
datetime(...,'unixepoch','localtime') AS Date FROM ..."


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

[sqlite] Simple read-only program very slow

[Kevin O'Gorman]

I'm pretty new at SQLite, so this may seem obvious to you.  Be kind.
I'm using Python on Ubuntu Linux 16.04 LTS, and the sqlite that is built
into Python.  The database
is using WAL.

I've got a database of some 100 million records, and a file of just over
300 thousand that I want represented in it.  I wanted to check how much
difference it was going to make, so I wrote a super
simple program to the read the file and count how many records are already
there.  I got impatient waiting for it so I killed the process and added an
output of one dot (".") per 1000 records.  It went very fast for what I
estimate was around 200 dots and hit a wall.  It made progress, but very
very slowly.

So I killed it again and added a commit() call every time it output a dot.
It didn't hit a wall, just some rough road (that is, it slowed down at
about the same spot but not nearly so drastically).

The code makes to changes to the database at all.  Why does commit() make a
difference?  What else should I learn from this?

The field being used for the lookup has an index.

++ kevin

Code follows:
#!/usr/bin/env python3
"""Count the number of records that represent rows in the database 'pos'
table.
The database is not modified.

 Last Modified: Sat Nov 25 18:56:49 PST 2017
"""

import os.path  # https://docs.python.org/3.5/library/os.path.html
import sys  # https://docs.python.org/3.5/library/sys.html
import argparse # https://docs.python.org/3.5/library/argparse.html
import sqlite3  # https://docs.python.org/3.5/library/sqlite3.html
import re   # https://docs.python.org/3.5/library/re.html

# from /usr/local/lib/python3.5/dist-packages
import qcreate
from qerror import *
import myparser

if __name__ == '__main__':
parser = argparse.ArgumentParser(description="""A program to read
positions and count how many are
in the database""",)
parser.add_argument("--dbname", default=None,
help="name of the database to work on (overrides qubic.ini
file)")
parser.add_argument("file", nargs='?', type=argparse.FileType('r'),
default=sys.stdin,
help="file containing the qsearch results (default stdin)")
args=parser.parse_args()
infile = args.file

if args.dbname is None:
here=os.path.split(os.path.realpath('.'))[1]
for confdir in
".",os.path.join(os.environ["HOME"],".config"),"/etc/xdg/":
f = os.path.join(confdir, "qubic.ini")
if os.path.exists(f):
args.dbname = myparser.parse(f, here, "dbname")
if args.dbname is not None:
break
if args.dbname is None:
print(" *** ERROR: no database name provided and none found in
qubic.ini files")
sys.exit(1)

present = missing = lines = 0
with sqlite3.connect(args.dbname) as conn:
for line in infile:
fields = line.split()
pos = fields[0]
if len(pos) != 64: # Important test to catch grep without
--no-filename
raise InputError(" ERROR: input line has wrong-sized
position: " + line)

pnum = None
for row in conn.execute("""
SELECT pnum
FROM pos
WHERE ppos=?
""",(pos,)):
pnum = row[0]
break
if pnum is None:
missing += 1
else:
present += 1
lines += 1
if lines % 1000 == 0:
print(".",flush=True,end="")
conn.commit()
print("there were",present,"records on file and",missing," were
missing")
print("out of a total of", lines, "records.")



-- 
word of the year: *kakistocracy*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help . . . I want to Export my Firefox Bookmarks to .CSV in one click, using SQLite3 in a .BAT file

[e-mail]

This "view" works in "DB Browser for SQLite" to get my Firefox Bookmarks . . . 
I then export them to Desktop as a .CSV, and run an Excel macro on them . . .

SELECT a.id AS ID, a.title AS Title, b.url AS URL, 
datetime(a.dateAdded/100,"unixepoch","localtime") AS Date
FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - -

But then I thought, why not use SQLite3 in a .BAT file, and make it "one-click" 
?

The following code is what I have put into a .BAT file - I don't see any 
errors, and both just create a blank .CSV on my Desktop.1.) Broken out, for 
examination
2.) Quotes and apostrophes - not sure what's necessary - it seems like quotes 
are probably necessary, if there's a space in the path
3.) Just has apostrophes - not sure what's necessary
Any idea why this isn't working?

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - -
sqlite3.exe -csv

"C:\Users\SLUDGE\AppData\Roaming\Mozilla\Firefox\Profiles\u689l7dh.default\places.sqlite"

"SELECT a.id AS ID, a.title AS Title, b.url AS URL, 
datetime(a.dateAdded/100,"unixepoch","localtime") AS Date FROM 
moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id"

> "C:\Users\SLUDGE\Desktop\output.csv"
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - -
sqlite3.exe -csv 
"C:\Users\SLUDGE\AppData\Roaming\Mozilla\Firefox\Profiles\u689l7dh.default\places.sqlite"
 "SELECT a.id AS ID, a.title AS Title, b.url AS URL, 
datetime(a.dateAdded/100,"unixepoch","localtime") AS Date FROM 
moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id" > 
"C:\Users\SLUDGE\Desktop\output.csv"
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - -
sqlite3.exe -csv 
C:\Users\SLUDGE\AppData\Roaming\Mozilla\Firefox\Profiles\u689l7dh.default\places.sqlite
 SELECT a.id AS ID, a.title AS Title, b.url AS URL, 
datetime(a.dateAdded/100,'unixepoch','localtime') AS Date FROM 
moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id > 
C:\Users\SLUDGE\Desktop\output.csv
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - -
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug: When it's needs quotation on csv mode in WIN32, The columns is not converted by winUnicodeToMbcs.

[白附 聖]

Hello, Everyone.

I'm Akira.

I found a bug.

I use sqlite3 on windows.
I figure out that odd character on CSV mode when I use Mbcs.

I understood that the columns is not converted by winUnicodeToMbcs  when it's 
needs quotation on csv mode in WIN32

Please see lines from 2553 to 2558 below source.
There is no utf8_printf, And It uses putc function directly.

I guess that  utf8_printf should be used there.
So, When I use csv mode,  Mbcs charcters show odd display.

Thank you.

shell.c:2537:static void output_csv(ShellState *p, const char *z, int bSep){
shell.c:2538:  FILE *out = p->out;
shell.c:2539:  if( z==0 ){
shell.c:2540:utf8_printf(out,"%s",p->nullValue);
shell.c:2541:  }else{
shell.c:2542:int i;
shell.c:2543:int nSep = strlen30(p->colSeparator);
shell.c:2544:for(i=0; z[i]; i++){
shell.c:2545:  if( needCsvQuote[((unsigned char*)z)[i]]
shell.c:2546: || (z[i]==p->colSeparator[0] &&
shell.c:2547: (nSep==1 || memcmp(z, p->colSeparator, nSep)==0)) ){
shell.c:2548:i = 0;
shell.c:2549:break;
shell.c:2550:  }
shell.c:2551:}
shell.c:2552:if( i==0 ){
shell.c:2553:  putc('"', out);
shell.c:2554:  for(i=0; z[i]; i++){
shell.c:2555:if( z[i]=='"' ) putc('"', out);
shell.c:2556:putc(z[i], out);
shell.c:2557:  }
shell.c:2558:  putc('"', out);
shell.c:2559:}else{
shell.c:2560:  utf8_printf(out, "%s", z);
shell.c:2561:}
shell.c:2562:  }
shell.c:2563:  if( bSep ){
shell.c:2564:utf8_printf(p->out, "%s", p->colSeparator);
shell.c:2565:  }
shell.c:2566:}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting an advance list of RowIDs for a query result set

[Keith Medcalf]

This is fixed in the current head of trunk.  Although the implementation may 
change, it will appear in the next release.

https://www.sqlite.org/src/timeline?n=50

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Saturday, 25 November, 2017 10:08
>To: SQLite mailing list
>Subject: Re: [sqlite] Getting an advance list of RowIDs for a query
>result set
>
>Simon, I’ve no users. I’ve been teaching myself c++ (and SQLite)
>during a lengthy illness so this is just experimentation for me.
>
>
>
>This subject touches on a previous question of mine you were involved
>in regarding redundant tables. The following may jog your memory
>
>
>
>create table TblA(A integer primary key, B int, C int);
>
>create table TblB(B integer primary key, BX int);
>
>create table TblC(C integer primary key, CX int);
>
>
>
>explain query plan select A from TblA left join TblB using (B) left
>join TblC using (C) where BX=?;
>
>0|0|0|SCAN TABLE TblA
>
>0|1|1|SEARCH TABLE TblB USING INTEGER PRIMARY KEY (rowid=?)
>
>
>
>explain query plan select A from TblA left join TblB using (B) left
>join TblC using (C) where CX=?;
>
>0|0|0|SCAN TABLE TblA
>
>0|1|1|SEARCH TABLE TblB USING INTEGER PRIMARY KEY (rowid=?)
>
>0|2|2|SEARCH TABLE TblC USING INTEGER PRIMARY KEY (rowid=?)
>
>
>
>In the first explain SQLite drops the trailing redundant table but in
>the second explain it doesn’t drop the middle redundant table.
>
>
>
>As TblB is included in the second query it must surely run slower
>than if it were omitted.
>
>
>
>I’m not complaining about the SQLite optimiser failing to spot the
>redundancy as it’s got to deal with a variety of queries far removed
>from my narrow experiment. Checking for such redundancies would
>likely slow down prepares and, when it comes down to it, anyone
>including TblB in the second query is only getting what they asked
>for.
>
>
>
>I do think though that it’s possible to write code to remove these
>redundancies so as to get the vector of RowIDs as fast as possible.
>So far I’ve been splitting SQL into ‘RowSQL’ (returns RowIDs involved
>in correct order) and ‘ColSQL’ (returns columns requested in original
>SQL for the requested range as shown in my second post) but I’ve only
>been doing it visually via knowledge of the tables. What I’m trying
>to do is write a function to automatically ‘split’ the sql into
>RowSQL and ColSQL. I’ll make another post later showing where I’m at
>with that.
>
>
>From: sqlite-users  on
>behalf of Simon Slavin 
>Sent: Saturday, November 25, 2017 1:26:00 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] Getting an advance list of RowIDs for a query
>result set
>
>
>
>On 25 Nov 2017, at 1:15pm, curmudgeon  wrote:
>
>> Given a select where a 'base table' is attached to lookup tables
>> how can I determine which of the lookup tables can be removed from
>the table
>> such that
>>
>> select BaseTbl.RowID from ... where ... order by ...
>>
>> will find the set of records that represents the original query.
>
>That optimization could be done at the level of the SQL engine.  You
>wouldn’t want to do it inside your own code since that would make
>your code extremely complicated.  So just execute the query without
>trying to optimize it and see what happens.  Does it run fast enough
>for your users ?
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Getting an advance list of RowIDs for a query result set

[x]

Simon, I’ve no users. I’ve been teaching myself c++ (and SQLite) during a 
lengthy illness so this is just experimentation for me.



This subject touches on a previous question of mine you were involved in 
regarding redundant tables. The following may jog your memory



create table TblA(A integer primary key, B int, C int);

create table TblB(B integer primary key, BX int);

create table TblC(C integer primary key, CX int);



explain query plan select A from TblA left join TblB using (B) left join TblC 
using (C) where BX=?;

0|0|0|SCAN TABLE TblA

0|1|1|SEARCH TABLE TblB USING INTEGER PRIMARY KEY (rowid=?)



explain query plan select A from TblA left join TblB using (B) left join TblC 
using (C) where CX=?;

0|0|0|SCAN TABLE TblA

0|1|1|SEARCH TABLE TblB USING INTEGER PRIMARY KEY (rowid=?)

0|2|2|SEARCH TABLE TblC USING INTEGER PRIMARY KEY (rowid=?)



In the first explain SQLite drops the trailing redundant table but in the 
second explain it doesn’t drop the middle redundant table.



As TblB is included in the second query it must surely run slower than if it 
were omitted.



I’m not complaining about the SQLite optimiser failing to spot the redundancy 
as it’s got to deal with a variety of queries far removed from my narrow 
experiment. Checking for such redundancies would likely slow down prepares and, 
when it comes down to it, anyone including TblB in the second query is only 
getting what they asked for.



I do think though that it’s possible to write code to remove these redundancies 
so as to get the vector of RowIDs as fast as possible. So far I’ve been 
splitting SQL into ‘RowSQL’ (returns RowIDs involved in correct order) and 
‘ColSQL’ (returns columns requested in original SQL for the requested range as 
shown in my second post) but I’ve only been doing it visually via knowledge of 
the tables. What I’m trying to do is write a function to automatically ‘split’ 
the sql into RowSQL and ColSQL. I’ll make another post later showing where I’m 
at with that.


From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Saturday, November 25, 2017 1:26:00 PM
To: SQLite mailing list
Subject: Re: [sqlite] Getting an advance list of RowIDs for a query result set



On 25 Nov 2017, at 1:15pm, curmudgeon  wrote:

> Given a select where a 'base table' is attached to lookup tables
> how can I determine which of the lookup tables can be removed from the table
> such that
>
> select BaseTbl.RowID from ... where ... order by ...
>
> will find the set of records that represents the original query.

That optimization could be done at the level of the SQL engine.  You wouldn’t 
want to do it inside your own code since that would make your code extremely 
complicated.  So just execute the query without trying to optimize it and see 
what happens.  Does it run fast enough for your users ?

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

Re: [sqlite] fts5CreateTokenizer pUserData destruction in case of failure

[Dan Kennedy]

On 11/25/2017 02:57 AM, Stephen F. Booth wrote:

I have been working on a custom FTS5 tokenizer using SQLite 3.21.0.  I
noticed that in fts5CreateTokenizer() if the call to sqlite3_malloc() fails
SQLITE_NOMEM is returned, however xDestroy (if present) is not called.  A
similar situation is handled differently in sqlite3_bind_pointer(); in that
function, xDestructor is called on failure.  In case of a failure in
fts5CreateTokenizer() is it the caller's responsibility to invoke
xDestroy() or should this be handled by SQLite?


Hi,

Ideally, it would have been handled by SQLite. But the API has been 
released now, so I think we'll just document that xCreateFunction() and 
xCreateTokenizer() do *not* invoke the xDestroy() if they fail. The 
application has to clean up its own objects in this case. This happened 
with sqlite3_create_collation_v2() as well.


Dan.


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

Re: [sqlite] More timeline changes.

[Simon Slavin]

On 25 Nov 2017, at 1:41pm, Richard Hipp  wrote:

> Web developers - help me with this:  For item (3) above, how can I
> make the ellipsis or icon to "show more detail" configurable using
> CSS?

Declare two different CSS classes: one is the "hidden" one which does not show 
the content, and the other is the "shown" one which shows the content with 
whatever style you want.  Use an "onclick" event on the ellipsis which swaps 
the class for the appropriate details from one to the other.



You may have too much text to make this practical, but had you considered 
tooltips ?  Hover the cursor over an entry you’re interested in and it’ll show 
you more details.



A disadvantage is that because you cannot click on the contents of a tooltip 
there’s no way to use them for text which includes links.

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

[sqlite] fts5CreateTokenizer pUserData destruction in case of failure

[Stephen F. Booth]

I have been working on a custom FTS5 tokenizer using SQLite 3.21.0.  I
noticed that in fts5CreateTokenizer() if the call to sqlite3_malloc() fails
SQLITE_NOMEM is returned, however xDestroy (if present) is not called.  A
similar situation is handled differently in sqlite3_bind_pointer(); in that
function, xDestructor is called on failure.  In case of a failure in
fts5CreateTokenizer() is it the caller's responsibility to invoke
xDestroy() or should this be handled by SQLite?

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

Re: [sqlite] Compiling on Xcode

[Lifepillar]

>Hi, ALL,
>Right now my C Language option on the Xcode 5.1.1 is set to "GNU99".
>When using this option I am getting a lot of different warnings like:
>
>Value Conversion Issue
>Implicit conversion loses integer precision.
>
>I know its just a warning, but I prefer the code to be compiled clean.
>I would guess that I need a change the option to get a clean compile.
>
>Which one I should use?

Hi,
I am resurrecting this old thread, because I recently had a similar
issue and didn't find an answer in this list.

When building sqlite-amalgamation-321 using Xcode 9.1 in a newly
created project, I get a few dozen warnings, mostly about value
conversion issues as mentioned above, a few about ambiguous MIN/MAX
macro definitions, unreachable code, possible misuse of comma
operator, and one about a (not C99-compliant) over-length string.

Most warnings are easily seen to be harmless (e.g., unreachable code
depends on compile-time flags, and ambiguous macros likely depend on
merging multiple files into one—but the various macro definitions
are equivalent). Besides, I get the same warnings if I add suitable
-W flags to sqlite-autoconf-321's Makefile, which otherwise
builds cleanly. This gives me confidence that the warnings may
indeed be ignored—although I am a bit scared of ignoring “implicit
conversion loses integer precision” warnings: I'd like someone
familiar with the code to confirm that that is no cause of concern.

So, you may silence all the warnings by setting the following items
in the target's build settings:

Apple LLVM 9.0 - Warnings - All Languages
  - Implicit Conversion to 32 Bit Type: No
  - Suspicious Commas: No
  - Unreachable Code: No

Apple LLVM 9.0 - Custom Compiler Flags
  - Other Warning Flags: -Wno-ambiguous-macro -Wno-overlength-strings

To avoid missing potential problems in the rest of your code, I'd
recommend suppressing the warnings only for the Release
configuration and only for the specific target.

Hope this may be helpful to others as well,
Life.


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

[sqlite] Wrong mailing list..... Was: More timeline changes.

[Richard Hipp]

Please ignore.  This post was intended for
fossil-us...@lists.fossli-scm.org, not for the SQLite users mailing
list.  My appologies..

On 11/25/17, Richard Hipp  wrote:
> In the latest code on https://www.fossil-scm.org/fossil/timeline and
> at https://sqlite.org/srcx/timeline has a "Declutter" button on the
> sub-menu bar to simplify the screen.  In the simplified timeline,
> there is a "Details" button to get all the details back again.
>
> I'm not done with this interface improvement push.  Here are my
> short-term plans:
>
> (1) Right now, pressing "Declutter" or "Details" is a server
> round-trip.  I think it would better to handle this using javascript.
>
> (2) Decluttered should be the default.  Currently Details is the
> default.  I spent a lot of time experimenting last night, and what I
> found myself doing every time I encountered a timeline was immediately
> pressing the "Declutter" button to get a high-level overview of the
> graph, then clicking on "Details" if I wanted to see more.  From that
> experience, I think coming up in Decluttered mode would be a much
> better approach.
>
> (3) Details/Declutter for the entire graph is good and should be kept.
> But it would be even better to be able to see the details of
> individual timeline entries.  I'm thinking that timelines come up in
> decluttered mode (showing only the check-in comment for each entry)
> but with ellipses or some other small icon at the end of each comment
> that you can click on to expand the details.
>
> You can help!  Send me your ideas of what you think timelines should
> look like.  Even better:  send me mock-ups.  Static HTML pages that
> you have hand-edited will be fine - just be sure to include the CSS,
> or better, put all the CSS in-line on your hand-edited mockup.
>
> You can also keep experimenting with the code I have on-line and send
> me your complaints and suggestions for improvement.
>
> Web developers - help me with this:  For item (3) above, how can I
> make the ellipsis or icon to "show more detail" configurable using
> CSS?  What's the best way to handle that so that people can customize
> the look for various skins?
>
> --
> D. Richard Hipp
> d...@sqlite.org
>


-- 
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] More timeline changes.

[Richard Hipp]

In the latest code on https://www.fossil-scm.org/fossil/timeline and
at https://sqlite.org/srcx/timeline has a "Declutter" button on the
sub-menu bar to simplify the screen.  In the simplified timeline,
there is a "Details" button to get all the details back again.

I'm not done with this interface improvement push.  Here are my
short-term plans:

(1) Right now, pressing "Declutter" or "Details" is a server
round-trip.  I think it would better to handle this using javascript.

(2) Decluttered should be the default.  Currently Details is the
default.  I spent a lot of time experimenting last night, and what I
found myself doing every time I encountered a timeline was immediately
pressing the "Declutter" button to get a high-level overview of the
graph, then clicking on "Details" if I wanted to see more.  From that
experience, I think coming up in Decluttered mode would be a much
better approach.

(3) Details/Declutter for the entire graph is good and should be kept.
But it would be even better to be able to see the details of
individual timeline entries.  I'm thinking that timelines come up in
decluttered mode (showing only the check-in comment for each entry)
but with ellipses or some other small icon at the end of each comment
that you can click on to expand the details.

You can help!  Send me your ideas of what you think timelines should
look like.  Even better:  send me mock-ups.  Static HTML pages that
you have hand-edited will be fine - just be sure to include the CSS,
or better, put all the CSS in-line on your hand-edited mockup.

You can also keep experimenting with the code I have on-line and send
me your complaints and suggestions for improvement.

Web developers - help me with this:  For item (3) above, how can I
make the ellipsis or icon to "show more detail" configurable using
CSS?  What's the best way to handle that so that people can customize
the look for various skins?

-- 
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] Getting an advance list of RowIDs for a query result set

[Simon Slavin]

On 25 Nov 2017, at 1:15pm, curmudgeon  wrote:

> Given a select where a 'base table' is attached to lookup tables
> how can I determine which of the lookup tables can be removed from the table
> such that
> 
> select BaseTbl.RowID from ... where ... order by ...
> 
> will find the set of records that represents the original query.

That optimization could be done at the level of the SQL engine.  You wouldn’t 
want to do it inside your own code since that would make your code extremely 
complicated.  So just execute the query without trying to optimize it and see 
what happens.  Does it run fast enough for your users ?

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

Re: [sqlite] Getting an advance list of RowIDs for a query result set

[curmudgeon]

Sorry, in last post

select * from (select Value from carray(ID+?1, ?2, 'int64'))
inner join AwfyBigTbl on AwfyBigTbl.RowID = _Value;

by setting ?1 = TopRecNo and ?2 = n.

should read

select * from (select Value from carray(*?1*, ?2, 'int64'))
inner join AwfyBigTbl on AwfyBigTbl.RowID = _Value;

by setting ?1 = *ID + TopRecNo* and ?2 = n.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting an advance list of RowIDs for a query result set

[curmudgeon]

A trivial example of what I'm trying to do. Given

select * from AwfyBigTbl where ACol=?;

I'd run the query

select RowID from AwfyBigTbl where ACol=?;

step through the records and store the values in a std::vector
called ID.
I could then retrieve n records starting at TopRecNo (0 based) with the
query

select * from (select Value from carray(ID+?1, ?2, 'int64'))
inner join AwfyBigTbl on AwfyBigTbl.RowID = _Value;

by setting ?1 = TopRecNo and ?2 = n.

I realise I could get the same result with

select * from AwfyBigTbl where ACol=? limit n offset TopRecNo;

but the first way will be faster and, as the record count - ID.size() - is
known , it would allow a
record counter to be shown, vertical scrollbar to be sized, last known
record to be relocated etc.

Anyway, what I'm trying to do is find a way of doing the same thing for more
complicated
queries. Given a select where a 'base table' is attached to lookup tables
how can I determine which of the lookup tables can be removed from the table
such that

select BaseTbl.RowID from ... where ... order by ...

will find the set of records that represents the original query.

Hope that's clearer.





--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Peter Da Silva]

>> What about time resets to the epoch which are not restored, user time 
>> changes,
> 
> I know some systems at least increment the node each time a time change is 
> detected. It will take 2^47 time changes to roll over. Since the node part is 
> not relevant to SQLite, this is perfectly safe.

Also, the UUID clock doesn't need to be the system clock, so you can simply 
ignore backwards changes in the system clock (or maintain a common offset that 
gets updated whenever a backwards change is detected in the system clock). Over 
time this may trim a few decades off the 3000+ year life of the format.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Peter Da Silva]

Ah, I see someone else has already brought up version 1 UUIDs.

> What about invalid and reused MAC addresses and devices with no MAC address 
> at all?

Not an issue with SQLite since that part of the UUID is a constant within a 
given database. It would be reasonable to simply pick "0" then set the low bit 
of the first octet to 1 in accordance with the standard for non-MAC node IDs. 
The standard suggests using a hash or a cryptographically secure random number 
generator, setting the 

> What about time resets to the epoch which are not restored, user time changes,

I know some systems at least increment the node each time a time change is 
detected. It will take 2^47 time changes to roll over. Since the node part is 
not relevant to SQLite, this is perfectly safe.

> daylight saving or leap seconds?

Not relevant to the timestamp format, since it's an epoch time.

> It sounds even more probabilistic than the probabilistic methods. Does anyone 
> actually use it?

It's Microsoft's standard GUID/UUID format and is also used by a lot of other 
systems for compatibility.

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

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Peter Da Silva]

Are people here talking about UUIDs or things that just look like UUIDs? It 
sounds like the latter. UUIDs are actually structured objects, with embedded 
type bits. There are multiple UUID generation schemes, one of which is based on 
random numbers, others are based on hashes, and there is the common ugly 
workaround of generating a 128 bit hash and calling it a UUID.

If you use version 1 UUIDs you're mathematically guaranteed to avoid 
collisions. At least for the next 3000 years:

Version 1 UUIDs are based on a node address (MAC), a 60 bit clock, and a 
node-specific sequence number. You can generate 163 billion version 1 UUIDs per 
second and they won't roll over until 5236 AD.

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

[sqlite] Get wal page count out of context of wal_hook?

[Olivier Mascia]

Dear all,

I'm looking for a mean to query the current WAL page count (at other times than 
at commit through the wal_hook() callback).

Did I overlooked something in the C API which would allow me to query this?

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia

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