Re: [sqlite] LIMIT

[Richard Rousselot]

Just wanted to support the idea of adding windowing functions to SQLite as
it is the biggest hurdle for me to get others to use it.  If size is an
issue, I would love to have a build option to enable it.

My $0.02

On Tue, Jun 26, 2018 at 8:27 AM Gert Van Assche  wrote:

> Thanks Olivier, very good to know.
>
> gert
>
> Op di 26 jun. 2018 om 09:06 schreef Olivier Mascia :
>
> > > Le 26 juin 2018 à 07:56, Gert Van Assche  a écrit :
> > >
> > > If I would like to use these ranking techniques in SQLite, can I do
> this
> > > with an extension? Has nobody created a Windowing.dll yet?
> > > Just asking. My short term need has been solved, but now that I know
> > this,
> > > I'm sure I'd like to use this technique later. But I like to stick to
> > > SQLite.
> >
> > Don't want to spoil any news, it is viewable by the public anyway, but
> > there are clearly experimental, interesting (and significant) work
> ongoing
> > by the SQLite developers around SQL windowing functions.  A quick look to
> > the exp-window-functions branch clearly shows that.
> >
> > https://www.sqlite.org/src/timeline?n=100=exp-window-functions
> >
> > So it _may_ be possible that you see these features in a later version of
> > SQLite, or not.  We'll see.  I certainly wish the best for this
> experience
> > which looks good and nicely ongoing.
> >
> > From experience as a user of SQLite, not all development of features
> > through branches get merged to the trunk.  Sometimes they stay available
> as
> > a branch for you to choose to use, sometimes they end up in the main
> SQLite
> > product (trunk branch).  Sometimes they can stay significant time aside
> > before one day being merged.  I have no specific expectations, and you
> > shouldn't have too, I'm just monitoring areas of development that are
> > interesting to my eyes and programming needs.  Mainly the
> > 'server-process-edition' branch, the 'begin-concurrent-pnu' branch and
> this
> > 'exp-window-functions' branch.
> >
> > :)
> >
> > --
> > 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
> >
> ___
> 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] 3.24.0 is 20% larger than 3.23.1

[Richard Rousselot]

If FTS5 is enabled by default in the amalgamation, the documentation needs
to be updated.

https://sqlite.org/fts5.html Section 2.1


On Mon, Jun 11, 2018 at 9:57 AM Richard Hipp  wrote:

> On 6/11/18, Lonnie Abelbeck  wrote:
> >
> >> On Jun 11, 2018, at 7:23 AM, Richard Hipp  wrote:
> >>
> >> On 6/11/18, Lonnie Abelbeck  wrote:
> >>>
> >>> What changed for 3.23.1 -> 3.24.0 to cause such a large increase in
> >>> library
> >>> file size?
> >>>
> >>
> >> See Dan's follow-up.  Beginning with 3.24.0, the FTS5 and JSON1
> >> extensions are enabled by default.
> >>
> >> Manually edit the Makefile generated by ./configure to turn off those
> >> two features.  You ought to be able to do "./configure --disable-fts5
> >> --disable-json1" but those two --disable options appear to be broken
> >> in the ./configure script.
> >
> > Quick note, I added --disable-fts5 --disable-json1 to configure ...
> >
> > Then 3.24.0 when from 814112 bytes to 683648 bytes, also removed -lm
> > dependancy.
> >
> > Ref: For the amalgamation-tarball, enable FTS5 and JSON1 by default
> > https://www.sqlite.org/src/vinfo/03edecaf9dcfc927?diff=1
> >
> > Is there a reason we should not disable FTS5 and JSON1 going forward for
> > embedded use ?
>
> There are two configure scripts
>
> (1) The main configure script at the root of the source tree, used to
> build from canonical sources.
>
> (2) The configure script at autoconf/configure.ac that generates the
> configure script that is part of the prepackaged amalgamation
> deliverables.
>
> Dan was talking about (2) and he is correct that FTS5 and JSON1 went
> from default-off to default-on in version 3.24.0.  I was talking about
> (1) which did not change its default settings, but which does have the
> bug in which the --disable-fts5 and --disable-json1 flags seem to be
> ignored.
>
> There is no reason to include FTS5 and JSON1 in embedded builds if you
> do not need them.
>
> --
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Site error

[Richard Rousselot]

The error is back I think.  I am getting same message.

Also, since I have your attention, why not just make the search box always
visible vs. having to click it.  Seems like an unnessary step.

Richard

On Fri, Mar 16, 2018 at 1:03 PM Richard Hipp  wrote:

> On 3/16/18, David Raymond  wrote:
> > Getting same error message trying to search today.
>
> Fixed.
>
> Apparently one of the automatic site-update scripts is messing up the
> file permissions on the full-text search databases...
>
> --
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple read-only program very slow

[Richard Rousselot]

Had similar issue a few years ago; we were using the SQLite3.exe.  We
recompiled the exe as 64 bit which allowed it to use more than 4 GB of ram,
loaded the machine with as much memory as we could.  Voila, super fast
processing.

Can the Python libraries be made 64 bit some how?

On Thu, Nov 30, 2017 at 7:01 PM Keith Medcalf  wrote:

>
> Is there an index on pos where ppos is the left-most field (or the only
> field) in the index?
> What is the column affinity of ppos?  Of the fiold you are passing as a
> parameter?
> Is ppos unique?
>
> If you CREATE [UNIQUE] INDEX searchindex ON pos (ppos, pnum);
>
> then your query can be satisfied only using the searchindex covering index.
>
> If there is not an index on ppos, then you will be wasting time recreating
> the index for each query.
>
> You will probably need to increase the cache size beyond the paltry
> default in order for the entire btree structures to be cached in RAM -- you
> probably want to make it as big as you can.
>
> ---
> 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 Kevin O'Gorman
> >Sent: Saturday, 25 November, 2017 20:14
> >To: sqlite-users
> >Subject: [sqlite] Simple read-only program very slow
> >
> >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
> >