On Wed, Feb 1, 2017 at 6:35 PM, Richard Hipp <d...@sqlite.org> wrote:
> On 2/1/17, Kevin O'Gorman <kevinogorm...@gmail.com> wrote:
> > I have a database of positions and moves in a strategic game, and I'm
> > searching for unsolved positions that have been connected to an immediate
> > ancestor. I'm using Python 3.5.2, and the code looks like
>
> Please provide us with the following additional information:
>
> (1) In python, run the query: "SELECT sqlite_version(),
> sqlite_source_id();"
>
> (2) In a recent sqlite3 command-line shell (the latest release, not
> whatever 5-year-old release happens to be installed on your system)
> bring up your database and run the command:
>
> .fullschema --indent
>
> And send in the output.
>
> (3) Download the bundle of command-line tools for your OS, then run
> the command "sqlite3_analyzer" on your database, and send in the
> output.
>
> Thanks.
>
>
>
I am unable to comply with items 2 and 3. I can download the linux x86
versions, which I expected would run on my x86-64 system, but they don't.
Instead, even when I point right at them, they report "No such file or
directory". I take this to mean that there is some file they do not find,
like a library, and they report the error code in their return status.
However, my "recent" software reports:
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> .fullschema --indent
Usage: .fullschema
sqlite> .fullschema
CREATE TABLE base64 (
b64char CHAR NOT NULL PRIMARY KEY,
b64val INTEGER);
CREATE TABLE pos (
pnum INTEGER PRIMARY KEY AUTOINCREMENT,
ppos CHAR(64) NOT NULL,
pcensus INTEGER NOT NULL,
pscore INTEGER,
pstate CHAR DEFAULT "N" NOT NULL,
pmin INTEGER DEFAULT -99 NOT NULL,
pmax INTEGER DEFAULT 99 NOT NULL,
pmain CHAR(64));
CREATE UNIQUE INDEX pipos ON pos (ppos);
CREATE TABLE move (
mfrom INTEGER NOT NULL,
mto INTEGER NOT NULL,
mtype CHAR NOT NULL,
mcell INTEGER NOT NULL,
mvalue INTEGER,
ma INTEGER DEFAULT -99,
mb INTEGER DEFAULT 99,
PRIMARY KEY (mfrom, mto, mcell));
CREATE UNIQUE INDEX mrev ON move (mto, mfrom, mcell);
CREATE TABLE expanded (
census INTEGER NOT NULL,
number INTEGER NOT NULL,
pos CHAR(64),
PRIMARY KEY (census, number));
ANALYZE sqlite_master;
ANALYZE sqlite_master;
INSERT INTO sqlite_stat1 VALUES('move','mrev','48329866 2 2 1');
INSERT INTO sqlite_stat1 VALUES('move','sqlite_autoindex_move_1','48329866
38 2 1');
INSERT INTO sqlite_stat1 VALUES('pos','pipos','74409802 1');
INSERT INTO sqlite_stat1 VALUES('base64','sqlite_autoindex_base64_1','64
1');
ANALYZE sqlite_master;
sqlite>
The analyzer is not included in my distribution or its repositiories, as
far as I can tell. This is Xubuntu, which is a flavor of Ubuntu, which is
derived from Debian.
I'm not sure I want to build your entire software suite. Perhaps you'd
care to download my database, which I freshly tar-ed and gzip-ed to
http://kosmanor.com/917/917.db.tgz
the databse is 21 GB; the tar is 3.1 GB
> >
> > #!/usr/bin/env python3
> > """Output positions that are reachable but unsolved at census 18 or
> greater
> > See page 76 of Qubic log
> >
> > Last Modified: Tue Jan 31 12:13:07 PST 2017
> > """
> >
> > import sqlite3 # https://docs.python.org/3.5/
> library/sqlite3.html
> >
> > with sqlite3.connect("917.db") as conn:
> > for row in conn.execute("""
> > SELECT DISTINCT ppos
> > FROM move JOIN pos ON mto = pnum
> > WHERE pcensus = 18 and pmin < pmax
> > """):
> > print(row[0])
> >
> > As written here, this query runs for 1193 minutes (just short of 20
> > hours). If I remove the "DISTINCT" and instead pipe the result into the
> > sort program that comes with Linux "sort --unique" the query and sort
> takes
> > only 31 minutes. The results are the same, and consist of 4.2 million
> rows.
> >
> > This seems extreme.
> >
> > --
> > word of the year: *kakistocracy*
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
--
word of the year: *kakistocracy*
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
