[sqlite] problem or not problem around 'begin immediate'

[Big Stone]

there is some discussion about an issue-or-not-an-issue on bugs.python.org

https://bugs.python.org/issue28518

#

conn = sqlite3.connect(':memory:', isolation_level='IMMEDIATE')
conn.execute('begin immediate')

Throws:

sqlite3.OperationalError: cannot start a transaction within a transaction

This didn't happen in previous versions

#
Maybe a sqlite developer comment would help settle the case ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] under the christmas tree

[Big Stone]

Hello Dear Sqlite developers,

I'm amazed by the recent "json" and "raw values" features.

Is it technically possible that one day, sqlite will integrate a few of the
following analytical functions ?
OVER
PARTITION BY
NTILE
PERCENTILE_CONT
PERCENTILE_DISC

These are useful in my real life, and I don't guess if there is a technical
"impossibility" for them to ever appear in sqlite.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] whish list for 2016

[Big Stone]

Hi All,

To prepare for 2016 greetings moment, here is my personnal whish list
for sqlite:
- analytic functions (would fit the split/apply/combine data-science landscape),
- "generate_series" extension included in default sqlite.exe and
sqlite.dll for windows.

Best whishes for 2016!

[sqlite] CSV export CR+LF not working properly on Windows

[Big Stone]

hi all,

I've been hit by this bug:

http://sqlite.1065341.n5.nabble.com/new-line-now-r-n-regardless-the-OS-td77371.html

I confirm that some basic export writes now to windows 7 with LF only,
instead of CR+LF.

I'm not against this feature, but I need also the previous behaviour.
==> could it be made available back in a form of another ? (a new csvCRLF
option ?)

nota: it was still as expected in 3.8.7.2 binary version made available on
the main site.

[sqlite] sqlite 3.8.11.0 will be in next Windows release of Python3.4 / 3.5

[Big Stone]

Hi all,

Just a link to this commit: http://bugs.python.org/msg247538

Regards,

Re: [sqlite] equivalent for JOIN LATERAL

[Big Stone]

oups ! Thank you Roger, I had forgot to post the link.

I got it via a tweet of Wes McKinney, one of the DataScience leader in the
Python World.

https://twitter.com/wesmckinn/status/564526251591733248
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] equivalent for JOIN LATERAL

[Big Stone]

If not, would it be possible "without" too much effort ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] equivalent for JOIN LATERAL

[Big Stone]

Hello,

I fall over this presentation of LATERAL, from postgresql guys.
(look at pages 1 to 16)

Does it exist in SQLITE ?

If not, would it be possible too much effort ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Whish List for 2015

[big stone]

Hi all,

To prepare for  end of 2014 greetings moment, here is my whish list for
2015:
- a minimal subset of analytic functions [1], that I hope may help
end-user/students popularity [2]
- better information on what is coming ahead, for example:
   . I see the 'sessions' tree moving along main tree since a few months,
what is it about ?
   . sqlite4 is dead because sqlite3 did progress quicker than expected ?
   . 

[1] http://www.postgresql.org/docs/9.4/static/tutorial-window.html

[2] http://db-engines.com/en/ranking_trend

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

Re: [sqlite] Stored Procedures

[big stone]

Hi,

Here is an example of stored procedure made in Python for SQLite.

https://pypi.python.org/pypi/sqlite_bro/0.8.7.4

I Hope it will help you figure out quickly  if SQLite is ok enough for your
use-case.

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

Re: [sqlite] 50% faster than 3.7.17

[big stone]

ok,

Nearly all the time is spent in a big 'CTE' select.

So maybe this sort of ugly CTE query  is not threadable.

with
f0_k as
  (SELECT f.rowid as nof2, f.*, p.Dn, p.g1, p.g2, p.w, p.other FROM F0 AS
f, Pt AS p WHERE  f.Io =p.Io)
,F0_mcalc as
  (SELECT f.*, p.*, (case when Priority=999 then Cg else Source end) AS
Sourcef
   FROM F0_k AS f, Sor AS p WHERE  p.omg  in (f.Cg, '') And
   f.Slic Between Be And En
   And p.PtGroup In (f.Io,f.g1,f.g2,f.w,f.other,'PL','SO','ST','Not_found')
)
,F0_mcalcmin as
  (SELECT nof2, min(priority) AS minp FROM F0_mcalc GROUP BY nof2)
,F0_mcalc_final as
 (SELECT f.*, round(qty*coefficient,3) AS qtyf FROM  F0_mcalcmin
  AS fm inner join  F0_mcalc AS f on f.nof2=fm.nof2 and
f.priority=fm.minp)
 select *   from F0_mcalc_final ;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 50% faster than 3.7.17

[big stone]

Hi,

This 3.8.7alpha release seems to bring about 5%  win from 3.8.6 , on my
particular SQL test case.

Question : "PRAGMA threads=2" didn't bring any speed-up on my "2 true"
cores machine.

Did I miss a compilation option ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] papercut wish list : a PRAGMA encoding='UTF-8-SIG'

[big stone]

... unless I'm fooled by the sqlite.exe dos output and it's working already
as I hope.

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

[sqlite] papercut wish list : a PRAGMA encoding='UTF-8-SIG'

[big stone]

Hello,

As a windows user, I would like that sqlite.exe would support the encoding
'UTF-8-SIG' for files.

'UTF-8-SIG' = normal 'UTF-8' file, but starting by a Byte-Order-Mark.

(see http://en.wikipedia.org/wiki/Byte_order_mark#UTF-8)

For windows user, it would be a truly appreciated improvement.

Indeed :

- if I try to read an utf-8-sig file generated by excel or other thing, my
header is blowed up because of this lack of feature,

- if I want to avoid this, I have to use utf-16, and my intermediate file
baloon in size over the network.

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

Re: [sqlite] Curiosity question for an apparent change in Sqlite/timeline pattern

[big stone]

Thank you Richard for this informative answer,

I was quite impressed by the  recent "in ()" improvement, so it probably
created a reality distortion field on my perception.

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

[sqlite] Curiosity question for an apparent change in Sqlite/timeline pattern

[big stone]

Hello,

It seems to me that, all of a sudden, a LOT of  micro-wins and bigger wins
are raining on the Sqlite/timeline
(since about august 1rst, the "in ()" improvement)

Is it an effect of a new Compiling/Testing environnement ? of summer
vaccation ? of partner's help ?
(maybe I'm wrong and all is as speedy as usual)


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

Re: [sqlite] Window functions?

[big stone]

Hi Stephan,

"lite," is not a mathematic definition, and is increasing over time.
(at least 5% per year in Sqlite code size, by 30% in smartphone capabilities )

==> What was "heavy" in 2003 will become "lite" one day.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[big stone]

Hi,

For one of the few wishing it :
- I can understand when Richard writes it's very complex to implement "in
full", as I can imagine tricky requests with it,

- but would a basic subset, like the one described here in March  '14 (
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2014-March/051635.html
)  :
. be a sufficient step forward for people looking for it in SQLite,
. be also simple enough to be  pre-translated in  'current' supported
syntax , so hopefully avoiding complexity of implementation ?

Sqlite supports already only a subset of "ALTER TABLE", so it would not be
the first time only a subset is implemented.

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

Re: [sqlite] SQLite 3.8.6 beta

[big stone]

complementary information :
- compiling manually sqlite.dll , I have no issue,
- so the problem, if problem, is the official Sqlite.dll downloadble from
sqlite.org.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.8.6 beta

[big stone]

hello,

Maybe i did a wrong manipulation.
Using latest beta dll on windows / python3.3, I get this strange error :

import sqlite3 as sqlite
  File
"C:\Users\famille\Documents\winpython\WinPython-32bit-3.3.5.0\python-3.3.5\lib\sqlite3\__init__.py",
line 23, in 
from sqlite3.dbapi2 import *
  File
"C:\Users\famille\Documents\winpython\WinPython-32bit-3.3.5.0\python-3.3.5\lib\sqlite3\dbapi2.py",
line 26, in 
from _sqlite3 import *
ImportError: DLL load failed: La procédure spécifiée est introuvable.

I'm really not sure why this sudden issue, but I report as I never met that
before.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.8.6 coming soon

[big stone]

hi Jose,


The SQL request with a  "in()" that is improved by 5x in the latest beta
is of form  :

select * from a , b where a.field1 in (b.column1, b.column2, b.column3,
b.column4, 'fixed value')
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.8.6 coming soon

[big stone]

ok,

output comparison

Size is different :
- before28 488 559
- now with beta 28 711 111
==> delta = 222 552  ... which is my number of ouput lines

The change in Newline Output policy (from CR to CR+LF) did generate a
suspens.
WinMerge says output is identical otherwise.


So my 5x improvement seems not the result of a bug.

I'm eager to see the speed-up that will show-up in your more general tests,
section 26. of  http://www.sqlite.org/checklists/3080600/index


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

Re: [sqlite] In-memory DB slower than disk-based?

[big stone]

Hi,

I too noticed disappointing improvements by going ":memory:".
But, it seems also very dependent of the compilation options and the
version of SQLite you use.

sometimes, gain is roughly 0%
sometimes, gain can be 40%.

==> You may try with a more recent version of SQLite than 3.7.15.2.


https://raw.githubusercontent.com/stonebig/ztest_donotuse/master/benchmark_test01_measures.GIF
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.8.6 coming soon

[big stone]

Hello Sqlite team,

My specific need relies on a rather complex combination of "in", and with
the new beta I go from 50 seconds down to 10 seconds.
(a 5x improvement)

==> Is that sort of gain possible with the recent "in" omtimisation, or
shall look deeper if result is still right ?

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

Re: [sqlite] Autocommit in "with" query: bug or feature?

[big stone]

Curiousity question :

Why are you basing your SQLfast next book on Python 2.7.6 rather than
Python 3.3 (or 3.4) ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autocommit in "with" query: bug or feature?

[big stone]

Hi again,

To answer the question in the title :
- yes, the "autocommit" bug is buggy with "with",
  http://bugs.python.org/issue21718
- it is also buggy with comments at the beginning of the 'select'.

The "autocommit" feature was a false good idea, but as sqlite3 arrived like
that in the standard library, they can't "change" this default.

If I understood well, the "autocommit" idea was to not "hold" the database
file by default, and ease multi-user access to the file database.


I tested your example with Python 2.7.5 and SQLite3.8.4, works nicely.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autocommit in "with" query: bug or feature?

[big stone]

Hi Jean-Luc,


All your problem is that you must use conn.isolation_level = None
With conn.isolation_level = "" , the default of sqlite3, nothing related
with transaction will work.

Just try with :
* this program :
https://github.com/stonebig/sqlite_bro/blob/master/sqlite_bro.py
(or pip install sqlite_bro)

* your example slightly reworked :

-- use conn.isolation_level = None , when you want to manipulate
transactions
drop table if exists PERSON;
create table PERSON (PID char(4),Name char(10));
insert into PERSON values ('p1','Smith'),('p2','Dermiez');

BEGIN TRANSACTION;
-- We check the contents of table PERSON
select * from PERSON;

-- We insert Jones and we check the contents of PERSON
insert into PERSON values('p3','Jones');
select * from PERSON;

-- We execute a simple "with" query
with CTE(A) as (values (1),(2)) select A from CTE;

-- We cancel the last insertion (Jones should disappear)
-- and we check the contents of PERSON
ROLLBACK;
select * from PERSON;

-- No Surprise: Jones IS NO MORE in the DB
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] detect database/table/field use

[big stone]

Hi Roger,

I notice some of your examples are written in Python2-only syntax.

Would it be possible to make their syntax more Python2/3 compatible, at
least for the print function ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[big stone]

Hi Roger,

Why is APSW not findable on the Pypi infrastructure ? It should be the
place where a newcomer would look for it.

I just published my work as "sqlite_bro" (pip install sqlite_bro), and it
doesn't seem to be too complex.
(just one full day lost to figure it out, in my case)


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

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[big stone]

Hi Roger,

No doubt that ASPW is much much better than SQLite3 standard library module.

Unfortunately, many People won't install ASPW because :
- Python "out of the box" SQLite experience has been complex for them
(newcomers in Python),
- no small utility was there to keep their SQLite interest and motivate
them further,
- or more simply, they can't install anything on their school PC.

The small utility may be a small click-click-run-run  Database Browser in
one python file, with all basics functions.
==> So my questions since a few weeks, for which ASPW can't help me.

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

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[big stone]

Hi Domingo,

You were right !
There is indeed an iterdump function in sqlite3 module, that I didn't
notice.

Many thanks,

I just needed to :
- wrap it around "PRAGMA foreign_keys = OFF;" and "PRAGMA foreign_keys =
ON;"
- then understand the "transaction" strange default settings of SQlite3.

For people interested, about the "transaction" strange default settings of
SQlite3 :
- there is a written explanation here :

http://stackoverflow.com/questions/15856976/transactions-with-python-sqlite3
and a video about it, at :

- and a Django conf. video  :
   https://www.youtube.com/watch?v=09tM18_st4I#t=1751

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

Re: [sqlite] sqlite4 completion status

[big stone]

Hi,

I see a lot of commits on SQlite3 recently, but SQlite4 branch has nearly
nothing since 3 months.

Is there a problem  ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[big stone]

Hi Domingo,

I don't know what this API is and if it's usable from standard Python.

As I wanted also to backup my internal Python Procedures, I just finished
my first attempt.

I don't know if I did it in the right order.
==> Casual testers are welcome.

screenshot
https://github.com/stonebig/baresql/blo ...
anager.GIF

source (one file, design to work on Python3, but Python2 may survive ) :
https://github.com/stonebig/baresql/blo ...
manager.py

Procedure :
- launch the python3 program
- open one of your 'test' database (not the big one...)
- try last icon.

function to dump the database is  "savdb_script(self):" line142

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

Re: [sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[big stone]

Hi Simon,

For structure, all is in "select sql from master_sqlite", the big subtility
is to play them in a pertinent order.

For the data themselves,  it is already done in the source sqlite.exe for
the ".dump" function, so I have just to digg .
Apparently , it's in shell.c around line 1275 .

"if( c=='d' && strncmp(azArg[0], "dump", n)==0 && nArg<3 ){"

... maybe not so complex ...

 I suppose, for my simple wish case, I can omit the SAVEPOINT/RELEASE  and
  PRAGMA writable_schema=ON parts
(https://www.mail-archive.com/sqlite-users@sqlite.org/msg27037.html)

There is some obscure things in the code I don't know what it is, so I will
suppose they don't exist :

'sqlite_sequence'
WHERE tbl_name LIKE shellstatic()

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

[sqlite] Creating a 'SQL text' Backup of a SQlite database with the mere sqlite.dll tool (and a Python 3)

[big stone]

Hello,

I would like to save my sqlite ':memory:' database into a sql command text
file.

With sqlite.dll, what is the procedure/algorithm ?
Is it already explained somewhere on Internet ?


At first look, I may imagine that I need to :
- create tables in a certain order (keeping the comments for documentation
purpose)
- then insert datas,
- then create views and index, in another certain order
- then create stored procedures at the end, to avoid changing datas during
the  re-feeding of tables

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

Re: [sqlite] Comments loss in sqlite_master table

[big stone]

Hi Domingo,

Oracle is doing also this sort of things with there / * parallel */ comment
keyword.

For example  :
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm#i1006328
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comments loss in sqlite_master table

[big stone]

Hi Dan,

You're right : situation is much better than what I thought.
I ddid a more complete check, switching from sqlite.exe 3.8.3  to
sqlite.dll  3.8.4.3

create /*this */
-- really
table /* is */ toty(
-- for
/* truly */
   x --educative
) /*purpose */
-- and comment hunting
 ;

select sql from sqlite_master

... gives back (changing from sqlite.exe  to sqlite.dll and switching to
3.8.4.3) :
CREATE TABLE toty(
-- for
/* truly */
   x --educative
)

So  :
- the loss of header and tail comments doesn't sound logical,
- but it's much better than the total loss I thought.


A small change in favor of head and tail comments preservation would still
be nice, though.

Regards,





So we miss the early and end comments
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comments loss in sqlite_master table

[big stone]

Hi Luuk,

You're right : there is also a problem of consistency with documentation .

Neither the spaces around 'x' in  'toty(   x )' are in the
documentation,but SQLite kept those useless spaces anyway.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Comments loss in sqlite_master table

[big stone]

Hello,

I miss the functionnality of some other sql motors that keep the comments
inside an object definition, like a table.

Example : (with sqlite.exe 3.8.3)

create table /* This table has an educative purpose */ toto(x);
create table /* This table has an educative purpose */
   toty(x  );
create table
totz(x  );
select sql from sqlite_master;

... gives :
CREATE TABLE TOTO(X)
CREATE TABLE TOTY(X  )
CREATE TABLE
   TOTZ(X)

... instead of the original  full statements :
create table /* This table has an educative purpose */ toto(x)
create table /* This table has an educative purpose */
   toty(x  )
create table
totz(x  )


This sounds quite illogical, as :
-  Newlines and spaces are kept, (if not around a comment)
-  the SQLite internal parser would re-parse (and remove comments) from the
original statement without issue.


==> Would it be possible to keep comments, without loosing compatibility ?

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

Re: [sqlite] SQLite Database Browser officially moved to GitHub

[big stone]

Hi Justin,

What are the necessary steps to rebuild SQLite Database Browser from
source, on a windows 7 PC ?

(I have no Qt experience, so not sure of what to download )

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

Re: [sqlite] Error trying to compile sqlite-amalgamation-201405071546 in Windows

[big stone]

Thanks,

I failed to patch the file myself, so I will simply wait next amalgation
publication.

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

Re: [sqlite] Error trying to compile sqlite-amalgamation-201405071546 in Windows

[big stone]

oups !

Sorry, the error comes after (no CSV import is normal).
 I have this :

No such file or directory
Creating executable:   SQLite3d.exe
shell.c
Génération de code en cours
Fin de la génération du code
Creating executable:   SQLite3s.exe
shell3x.c
shell3x.c(31815) : error C2061: erreur de syntaxe : identificateur 'HANDLE'
shell3x.c(31819) : error C2061: erreur de syntaxe : identificateur
'lastErrno'
shell3x.c(31819) : error C2059: erreur de syntaxe : ';'
shell3x.c(31834) : error C2061: erreur de syntaxe : identificateur 'hMap'
shell3x.c(31834) : error C2059: erreur de syntaxe : ';'
shell3x.c(31840) : error C2059: erreur de syntaxe : '}'
shell3x.c(32017) : error C2065: 'AreFileApisANSI' : identificateur non
déclaré
shell3x.c(32017) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32042) : error C2065: 'CloseHandle' : identificateur non déclaré
shell3x.c(32042) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32047) : error C2065: 'CreateFileA' : identificateur non déclaré
shell3x.c(32047) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32056) : error C2065: 'CreateFileW' : identificateur non déclaré
shell3x.c(32056) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32066) : error C2065: 'CreateFileMappingA' : identificateur non
déclar
é
shell3x.c(32066) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32076) : error C2065: 'CreateFileMappingW' : identificateur non
déclar
é
shell3x.c(32076) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32085) : error C2065: 'CreateMutexW' : identificateur non déclaré
shell3x.c(32085) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32094) : error C2065: 'DeleteFileA' : identificateur non déclaré
shell3x.c(32094) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32102) : error C2065: 'DeleteFileW' : identificateur non déclaré
shell3x.c(32102) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32127) : error C2065: 'FlushFileBuffers' : identificateur non
déclaré
shell3x.c(32127) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32132) : error C2065: 'FormatMessageA' : identificateur non
déclaré
shell3x.c(32132) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32141) : error C2065: 'FormatMessageW' : identificateur non
déclaré
shell3x.c(32141) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32150) : error C2065: 'FreeLibrary' : identificateur non déclaré
shell3x.c(32150) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32157) : error C2065: 'GetCurrentProcessId' : identificateur non
décla
ré
shell3x.c(32157) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32162) : error C2065: 'GetDiskFreeSpaceA' : identificateur non
déclaré

shell3x.c(32162) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32171) : error C2065: 'GetDiskFreeSpaceW' : identificateur non
déclaré

shell3x.c(32171) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32180) : error C2065: 'GetFileAttributesA' : identificateur non
déclar
é
shell3x.c(32180) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32188) : error C2065: 'GetFileAttributesW' : identificateur non
déclar
é
shell3x.c(32188) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32196) : error C2065: 'GetFileAttributesExW' : identificateur non
décl
aré
shell3x.c(32196) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32205) : error C2065: 'GetFileSize' : identificateur non déclaré
shell3x.c(32205) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32213) : error C2065: 'GetFullPathNameA' : identificateur non
déclaré
shell3x.c(32213) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(3) : error C2065: 'GetFullPathNameW' : identificateur non
déclaré
shell3x.c(3) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32230) : error C2065: 'GetLastError' : identificateur non déclaré
shell3x.c(32230) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32241) : error C2065: 'GetProcAddress' : identificateur non
déclaré
shell3x.c(32241) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32251) : error C2065: 'GetSystemInfo' : identificateur non déclaré
shell3x.c(32251) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32258) : error C2065: 'GetSystemTime' : identificateur non déclaré
shell3x.c(32258) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32263) : error C2065: 'GetSystemTimeAsFileTime' : identificateur
non d
éclaré
shell3x.c(32263) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32272) : error C2065: 'GetTempPathA' : identificateur non déclaré
shell3x.c(32272) : error C2099: l'initialiseur n'est pas une constante
shell3x.c(32280) : error C2065: 'GetTempPathW' : identificateur non déclaré
shell3x.c(32280) : error C2099: l'initialiseur n'est pas une constante

[sqlite] Error trying to compile sqlite-amalgamation-201405071546 in Windows

[big stone]

Hello,

It's along time I didn't try to compile Sqlite amalgation.
Today, I get an error on Windows when trying then new beta
"sqlite-amalgamation-201405071546" :

error ~=  "no CSVImport.c

==> I didn't do anything different from usual, to the best of my knowledge.
==> Did anyone had an issue on this ?


C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC>call vcvarsall.bat
Setting environment for using Microsoft Visual Studio 2010 x86 tools.
C:\Users\famille\Documents\Visual Studio
2010\Projects\sqlite3_dll_385a20130519\
sqlite3\
type "buildMSVC.cmd"
C:\Users\famille\Documents\Visual Studio
2010\Projects\sqlite3_dll_385a20130519\
sqlite3>buildMSVC.cmd
CompileOptions=/nologo /O2 /GLFAy /fp:precise
Creating library file: SQLite3.dll
sqlite3.c
   Création de la bibliothèque SQLite3.lib et de l'objet SQLite3.exp
Génération de code en cours
Fin de la génération du code
Creating executable;   CSVImport.exe
CSVImport.c
c1 : fatal error C1083: Impossible d'ouvrir le fichier source :
'CSVImport.c' :
No such file or directory
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I know the python functions registered on SQLite ?

[big stone]

Hi,

I finally found that housekeeping was the best solution, as I needed anyway
to add a layer over the basic information kept by SQLite :
- the function ".__doc__" information,
- or even the full function creation script.

** discovery of the day : fibonacci works !**

pydef py_fib(n):
   "fibonacci : example with internal function call (external won't work) "
   fib = lambda n: n if n < 2 else fib(n-1) + fib(n-2)
   return("%s" % fib(n*1));

select py_fib(8), sqlite_version()
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I know the python functions registered on SQLite ?

[big stone]

Hi,

Thanks Rogers !
Danke Hick !

I'll do my manual housekeeping, so.

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

[sqlite] How do I know the python functions registered on SQLite ?

[big stone]

Hello,

Is there a way to get the list of all the 'external' functions created in
a  SQLite connexion ?

(maybe there is no solution except a manual housekeeping)

#* (example of external function creation in Python)
import sqlite3
conn = sqlite.connect(":memory:")
def mysqrt(s):
return ("%s" %s**.5);

conn.create_function('mysqrt', 1, mysqrt)
#*


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

[sqlite] Graphic User Interface to browse Sqlite, written in pure Python

[big stone]

Hello,

I failed to find a basic but correct Graphic User Interface program for
Sqlite in pure Python 3, with a liberal licence.
==> Did anyone know of something  I may have missed ?

As I don't have great goals, I started to build a small one.

I'm posting it there in hope :
- it can be of interest for some Python and Sqlite users,
- some of you can help to improve it more quickly :
. remaining tcl/tk glitches solving,
. better looking icons,
. missing basic functions.



Teaser :
https://github.com/stonebig/baresql/blob/master/examples/sqlite_py_manager.GIF?raw=true

Source : (not very clean source, )
https://github.com/stonebig/baresql/blob/master/examples/sqlite_py_manager.py


Status :
- works on any python 3.3 PC  : ONE .py file, only default modules.
- allows to create embedded functions in ... Python,
- GUI to import  CSV tables,
- GUI to attach other Sqlite databases,
- multi-Tab, multiple SQL requests and results per 'Tab'
- New query 'Tab' opening via :
   . double-clicking on a table '(Definition') field,
   . or double-clicking on a table normal field,

To Do :
- cleaning-up the code,
- CSV export,
- CSV import character-set handling (auto-detection of utf-8 utf-16),
- information bubble on the icon bar,
- (what basic function do you think is missing  ?)

Headaches  :
- the not perfect layout adjustements of 'Frames' inside a 'Tab' Window.
- the refresh of the left-database is closing the tree.



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

Re: [sqlite] SQLite for single user data manipulation

[big stone]

Hi Nick,

>"I just can't find any input for people
like me who just want a data manipulation tool for data that's too big to
read into RAM?"


Here is what I found, that may interest you :

>From Wes Mc Kinney  : (about medium datas handling)
- slides   :
http://www.slideshare.net/wesm/practical-medium-data-analytics-with-python(starting
slide 39).
- talk : http://vimeo.com/79562736 (starting at around 36')

>From Gael Varoquaux  : (about reducing the size of the problem)
- talk :
http://pyvideo.org/video/2121/processing-biggish-data-on-commodity-hardware-si-1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] about the apparently arriving soon "threads"

[big stone]

Hi James,

You're right :  my example is indeed a "4 independant process" rather than
"4 threads in the same process".

The job I need to do is unchanged : transform a big input table in a big
output table.

I hope that SQlite improvements will allow us to approach this "2x" (or
more) boost in the future, without the pain of managing parallelisation
outside of SQL.

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

Re: [sqlite] about the apparently arriving soon "threads"

[big stone]

Hi Simon,

About my test :
- principal input fact file is 220 000 line of 5 fields ( 7 389 Ko in Utf-8
on a windows pc)
- other files are 65 Ko
- initial and final data is on a 7200 rpm rotating disk,
- sqlite database(s), one per thread, is in ":memory:".

Threading Plumbery  is managed via DOS ".bat commands, as below :
- a "main.bat" dos command :
 . pre-clears the 4 "ok finished" files,
 . launch the 4 threads,
 . then check every 2 seconds that all "ok finished" files are generated.
- a "test_sqlite_this.bat" command launcher was necessary to pass
parameters for each sqlite session,



**main.bat** file
rem let's try parallel

cd %~dp0
set sqlite=%~dp0sqlite3.exe
echo %time%
@echo off

rem each thread is writing a "ok" file after it outputed its 1/4th of big
result

del /q "%~dp0zout_sqlite_v30_0ok.txt"
del /q "%~dp0zout_sqlite_v30_1ok.txt"
del /q "%~dp0zout_sqlite_v30_2ok.txt"
del /q "%~dp0zout_sqlite_v30_3ok.txt"
start cmd /C  "%~dp0test_sqlite_this.bat" %sqlite%
test_sqlite3_script_v30_0.txt  zout_sqlite_v30_0ok.txt debug
start cmd /C  "%~dp0test_sqlite_this.bat" %sqlite%
test_sqlite3_script_v30_1.txt  zout_sqlite_v30_1ok.txt nodebug
start cmd /C  "%~dp0test_sqlite_this.bat" %sqlite%
test_sqlite3_script_v30_2.txt  zout_sqlite_v30_2ok.txt nodebug
start cmd /C  "%~dp0test_sqlite_this.bat" %sqlite%
test_sqlite3_script_v30_3.txt  zout_sqlite_v30_3ok.txt nodebug

set zf="%~dp0zout_sqlite_v30_0ok.txt"

:step0
echo waiting %zf%
rem sleep 1
ping -n 2 127.0.0.1>nul

set zf="%~dp0zout_sqlite_v30_0ok.txt"
if not  exist %zf% goto step0

set zf="%~dp0zout_sqlite_v30_1ok.txt"
if not  exist %zf% goto step0

set zf="%~dp0zout_sqlite_v30_2ok.txt"
if not  exist %zf% goto step0

set zf="%~dp0zout_sqlite_v30_3ok.txt"
if not  exist %zf% goto step0

echo %time%
pause

** test_sqlite_this.bat** file
@echo off
rem %1=sqlite exec , %2=input file , %3=termination_file ,"%4"=="debug" (or
nothing)

cd %~dp0
echo %time%
echo "sqlite=%1"
echo "inputfile=%2"
echo "terminationfile=%3"
@echo on
%1 ":memory:"<%2

echo %time%>%3

if "%4"=="debug" pause
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] about the apparently arriving soon "threads"

[big stone]

Hi,

I did experiment splitting my workload in 4 threads on my cpu i3-350m to
see what are the scaling possibilities.

Timing :
1 cpu = 28 seconds
2 cpu = 16 seconds
3 cpu = 15 seconds
4 cpu = 14 seconds

Analysis :
- sqlite is such a small foot-print in memory, it is really scaling well
with the number of cores,

- hyper-threaded cores are useless for a database workload,
  (it was the first time I had the opportunity to really use 4 cores, so
the first time I notice)

- but the plumbery I personnaly need to manage threading out of sqlite
makes it not practical outside of a "test tube".
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] about the apparently arriving soon "threads"

[big stone]

Hello,

I see the "threads" branch of fossil has a lot of activity and seems close
to be finalized.

Will it be activated by default on the downloadable executable for windows ?
Will it apply to parallelisable CTE expression ?

Will it be possible from 1 sqlite.exe command line (or 1 python
sqlite.execute) to launch several SQL in parallel (and separated threads) ?

Typical workload case (that would be awesome if it could be 4 times
quicker) =

- a long treatment reading  a big table, which could be splitted into N
treatments reading 1/N th of the records of the big table.
- the N thread are adding the resulting records in 1 table.

This typical workload could also be defined as a big CTE :
   with
  resulset1 as (...),
  resulset2 as (...),
  resulset3 as (...),
  resulset4 as (...)
  select * from resultset1
  union all
  select * from resultset2
  union all
  select * from resultset3
  union all
  select * from resultset4

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

Re: [sqlite] Using a customized collate function to mimic integer array type.

[big stone]

Even if  SQLite was already implementing a bigger subset of the array type
of SQL2003 standard, your users would still have a few more words to learn :

'ROW', 'MEMBER OF', 'UNNEST'

"C > 100 (match 158)"  => " max(UNNEST(C)) > 100 "
 "C < 100 (match 42 and 76)"  => " min(UNNEST(C)) < 100 "
"C = 42 (match 42)" => "ROW(42) MEMBER OF C"

(reference : to http://farrago.sourceforge.net/design/CollectionTypes.html)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[big stone]

Hi again,

I confess I have difficulties to diggest your code.

>From your experience to reach this success, would you have some hints for
SQLite team that would allow the code to become more easy to read and stay
performant ?

Example :
- a non-performant implementation of 'this' required 'that' complex
workaround,
- a non-availabe syntax for 'this' required 'that' other  complex syntax to
workaround,
- ...

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

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[big stone]

Hi Edzard,

I just reproduced your test.

Indeed :
- you probably blew-up everything running SQL sudoku on this planet :
   . 'hardest1' in under 2 seconds on my machine,
   . 'eastermonster1' in 43ms.
- with Norvig's method and available SQLite syntax.

Each of these feats is jaw-dropping.


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

Re: [sqlite] basic "Window function"

[big stone]

"howzit" is my prefered greetings word.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] basic "Window function"

[big stone]

Hello again,

Windowing functions are :
- not easy to workaround with create_function(),
- a truly "sql core" motor functionality.

The only use case that I have, is the small subset I described earlier :
- inside one 'select' :
   .  several sum/min/avg/max (different fields) ,
   . all with exactly the same "(over partition by ... a series of fields)",
- no ranking, no order by inside this partition.

==> It allows to show a list of records at a certain level, with
statistical analysis done at a completely different (higher or lower) level.

Is it a feature SQLite team would like to do ?

Is there anyone else, (besides little bee), that would "like" this request
?

(http://upload.wikimedia.org/wikipedia/commons/1/13/Facebook_like_thumb.png)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_progress_handler callback behavior change

[big stone]

Hello Andreas,

Does it mean we may have SQLite3.8.4.1 in imminent Python 3.4 release ?

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

Re: [sqlite] Windows user app to display input form + reporting?

[big stone]

Hi Gilles,

Only Base and Access seem to fit your broad requirements.

With SQliteStudio.pl you can feed a sqlite database, but won't have form
and reports.

What don't you like in base solution that made you ask for other ideas ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[big stone]

Hello,

Thanks to Keith's help I succeeded to set up a comparison a "native" sqrt()
versus a "python" mysqrt() function .

The speed-up in a best case non-realistic scenario is only 40%.

create_function() looks very performant.

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

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[big stone]

Hello Keith,

Thanks for the ".timer on" tip.

I only succeed to get a "Error: not such function : sqrt" from default
Sqlite.exe
How did you get that "native" sqrt working ?


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

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[big stone]

Hello Max,

Your link is pretty interesting. It looks that :
- method1 should be easily implemented with SQLite "floating point"
representation,
- and with a very very small code size.

Here is the benchmarking of the two available methods :

https://raw.github.com/stonebig/ztest_donotuse/master/square_rooting_benchmark.GIF


So :
- your method is only 3 times slower than the python sqrt(),
- if SQLite team accepts to sacrifice a few bytes to implement sqrt(), we
may benefit a  389% speed-up (300/27*.652/1.86) at least.
(300/27*.652/1.86)

sqrt() is very interesting for statistics on-the-go over sql datas.

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

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[big stone]

Ooups !

Thanks to the awesome  posts about "RPAD/LPAD", I understood that I could
already create a "sqrt()" function for SQLite3  in interpreted python.

 (example) ***
import sqlite3
db_filename = ':memory:'

def mysqrt(s):
""" returns sqrt(s)"""
#must return a string, apparently
return ("%s" %sqrt(s))

with sqlite3.connect(db_filename) as conn:

conn.create_function('mysqrt', 1, mysqrt)
cursor = conn.cursor()
query = "select 'hello, sqrt' , mysqrt(3), 'of ', mysqrt(3)*mysqrt(3) "
cursor.execute(query)
for row in cursor.fetchall():
print (row)
cursor.close
cursor = None
conn.close
conn = None


 (The link that showed nicely how to play with that) ***
http://pymotw.com/2/sqlite3/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[big stone]

Hello,

I Re-checked today the  Mysql suspect performance, after a reboot, on :
"select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
by cand_nm, contbr_st;"

This particular operation is now 7.1 seconds.

I may have miss-used "MysqlWorkbench".

==> I updated the figure to the "reproducible" 7.1seconds.

https://raw.github.com/stonebig/ztest_donotuse/master/benchmark_test01_measures.GIF



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

[sqlite] about "windows" downloadable versions

[big stone]

Hello,

Wouldn't it be a good idea, for the "windows" download version, to propose
also (or only) the "-o2 compiled" .dll and .exe versions ?

(trading 300Ko of executable size for a 50% speed-up is a bargain, when you
have a pc)

If the embedded developpers re-compile anyway from source to get the exact
options they want, they would not be bothered.

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

Re: [sqlite] About "speed"

[big stone]

Hi

*Elefterios, Simon,*


*Wes McKinney gave us :- a fully detailed benchmark case (data +
reproducible test),*


*- where SQLite was :  . abnormally less good than Postgresql (so could be
better),*


*  . SQLdatabase in general were abnormally less good,   . a hint
"vertica"was given.*


*Maybe it is a 'golden' benchmark, as it hints a possible 10x margin of
improvement for SQLite. *
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[big stone]

ok,

Just updated with 3.8.4beta of  2014-03-05.

I also re-did some previous measures as :
- testing method improved a little,
- I measured more carefully that SQLite has also a sort of caching benefit,
when you run a query twice on windows7.


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

Re: [sqlite] SQLite version 3.8.4 release schedule

[big stone]

Hello,

Comparing 3.8.3 to 3.8.4beta fo today :
- both compiled in  -o2 mode and running in ":memory:" ,
- on a windows pc.

Changes :
* 8% quicker on Recursive CTE
   (sudoku test of  http://www.sqlite.org/lang_with.html)
* 12% quicker on Index Creation over a 5M record table
(
ftp://ftp.fec.gov/FEC/Presidential_Map/2012/P0001/P0001-ALL.zip)

==> Is it expected to have a big speed-up in index creation ? Or is it
noise ?


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

Re: [sqlite] About "speed"

[big stone]

Timing updates with Mysql 5.6.16

test =
https://raw.github.com/stonebig/ztest_donotuse/master/benchmark_test01.txt

results =
https://github.com/stonebig/ztest_donotuse/blob/master/benchmark_test01_measures.GIF?raw=true
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[big stone]

The result in a .csv format for Mikael,

Sorry I'm very bad in html, I hope someone can re-post it in a nice-looking
html table

Nota :
- Postgresql is not tuned at all, and its figures move a lot between two
measures,
- I couldn't do a Pandas measure because "not enough memory".


"sequence of operations \ time sec(at first
try)","postgresql9.2.3","sqlite3.8.3.0 7200rpm_disk.db","sqlite3.8.3.0
:memory:","sqlite3.8.3.0 7200rpm_disk.db compiled -o2","sqlite3.8.3.0
:memory: compiled -o2"
"loading 5 344 498 records * 18 columns from .csv",78,151,131,137,51
"select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm
",54,22,20,11,10
"select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm
-- a second time",5.1,22,20,11,10
"select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
by cand_nm, contbr_st",5.6,27,23,16,14
"CREATE INDEX xyzzy2 ON fec(cand_nm, contbr_st,
contb_receipt_amt)",176,43,38,30,25
"select cand_nm, sum(contb_receipt_amt) as total from fec group by
cand_nm,",4.9,3,3,2,2
"select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
by cand_nm, contbr_st,",5.6,4,4,3,3
"total time (not relevant)",329,272,239,210,115
"relative speed-up (not relevant)",-21%,0%,12%,23%,58%
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[big stone]

Hi Mikael,

I'm not expert in rtree virtual table handling, but you may try and post
the result here .
Adding the test of the -o2 compiled SQLite3.8.3.exe (size 801Ko vs 501Ko
for the standard Sqlite, 'size' optimized)

- feeding data :
   . in disk database : 151 seconds
   . in memory database :  131 seconds (25% = 1 cpu used out of 4)
   . in memory database -o2 compilation :  51 seconds (25% = 1 cpu
used out of 4)

 - creating index CREATE INDEX xyzzy2 ON fec(cand_nm, contbr_st,
contb_receipt_amt) :
   . in disk database : 43 seconds
   . in memory database :  38 seconds
   . in memory database -o2 compilation :  25 seconds

- select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm
;
  . in disk database : 22 seconds
  . in memory database :  19 seconds
  . in memory database -o2 compilation :  10 seconds
  . in disk database with index: 3 seconds
  . in memory database with index :  3 seconds
  . in memory database -o2 compilation with index :  2 seconds


- select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm ;
 . in disk database : 27 seconds
 . in memory database :  24 seconds
 . in memory database -o2 compilation  :  14 seconds
 . in disk database with index: 4 seconds
 . in memory database with index :  4 seconds
 . in memory database -o2 compilation with index :  3 seconds

The effect of -o2 is quite significant on these tests.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite destroys civilization.

[big stone]

Shouldn't you  add "nanobots" to the  "famous" user list , just below
flame, and over the "android" droids ?

Biggest Companies use SAP

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

Re: [sqlite] About "speed"

[big stone]

Hi again,

I tune a little the SQLite experiment :
- to get rid of the 19th columns message,
- to measure the previous tests with more precise figures,
- the effect of the suggested index :
   CREATE INDEX xyzzy2 ON fec(cand_nm, contbr_st, contb_receipt_amt);
- the effect of using a filesystem database.

results : (time +/-1 seconds, windows timing doesn't show below the second)
- feeding data :
   . in disk database : 151 seconds
   . in memory database :  131 seconds (25% = 1 cpu used out of 4)

 - creating index CREATE INDEX xyzzy2 ON fec(cand_nm, contbr_st,
contb_receipt_amt) :
   . in disk database : 43 seconds
   . in memory database :  38 seconds

- select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm
;
  . in disk database : 22 seconds
  . in memory database :  19 seconds
  . in disk database with index: 3 seconds
  . in memory database with index :  3 seconds


- select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm ;
 . in disk database : 27 seconds
 . in memory database :  24 seconds
 . in disk database with index: 4 seconds
 . in memory database with index :  4 seconds


Remarks :

- with an expert index, SQLite is 6 times quicker.
   ==> Why such a 'x6' speed-up, as we need to scan the whole table anyway ?

- the ":memory:" database is barely quicker than the disk database.
   ==> How can a rotating disk (7200rpm) database compete with a pure
in-memory database ?



*** ANNEXE 
script to launch with ":memory:" or with "turlututu.db"  :
(I measure the file LastWrite time, on windows via powershell to get seconds)


.header on
.mod csv
.separator ","

create table fec(
CMTE_ID ,CAND_ID ,CAND_NM ,CONTBR_NM ,
CONTBR_CITY ,CONTBR_ST , CONTBR_ZIP ,
CONTBR_EMPLOYER ,CONTBR_OCCUPATION ,CONTB_RECEIPT_AMT double
precision,
CONTB_RECEIPT_DT ,RECEIPT_DESC ,MEMO_CD ,
MEMO_TEXT  ,FORM_TP ,FILE_NUM ,
TRAN_ID ,ELECTION_TP ,USELESS_COLUMN
);
.import "P0001-ALL.csv" fec


--5 344 498 record read with warning as 19th empty column
.output fec_test0.csv
select *  from fec limit 1;

.output stdout
.output fec_test1.csv
select count(*) from fec;

.output stdout
.output fec_test2.csv

select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm;

.output stdout
.output fec_test3.csv

select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
by cand_nm, contbr_st;

.output stdout

-- in memory, with index   -
CREATE INDEX xyzzy2 ON fec(cand_nm, contbr_st, contb_receipt_amt);
.output fec_test0c.csv
select *  from fec limit 1;

.output stdout

.output fec_test1c.csv
select count(*) from fec;

.output stdout
.output fec_test2c.csv

select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm;

.output stdout
.output fec_test3c.csv

select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
by cand_nm, contbr_st;

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

Re: [sqlite] About "speed"

[big stone]

Hi again,

This is what I mean : we should have an updated "speed" page where we could
objectively measure.

In the mean time, I painfully partially reproduced two of the figures from
Wes.

Procedure :

download
ftp://ftp.fec.gov/FEC/Presidential_Map/2012/P0001/P0001-ALL.zip
unzip to P0001-ALL.csv

This data file is about 965 Mo, 18 columns *  5 344 498 records big.


** Test Preparation **
- Hardware : pc windows7, 4go ram, cpu intel i3-350m 2.27 Ghz
- Software :
  . sqlite-shell-win32-x86-3080300 (sqlite3.8.3)
  . postgresql 9.3.2.3 64bit

- preparation scripts of sqlite (As there is an added coma at the end of
each line, The default Sqlite  importation by reading headers will complain
a little)
.header on
.mod csv
.separator ","
.import "P0001-ALL.csv" fec

- preparation scripts of postgresql
create table fec(
CMTE_ID varchar,CAND_ID varchar,CAND_NM varchar,CONTBR_NM varchar,
CONTBR_CITY varchar,CONTBR_ST varchar, CONTBR_ZIP varchar,
CONTBR_EMPLOYER varchar,CONTBR_OCCUPATION varchar,CONTB_RECEIPT_AMT double
precision,
CONTB_RECEIPT_DT varchar,RECEIPT_DESC varchar,MEMO_CD varchar,
MEMO_TEXT  varchar,FORM_TP varchar,FILE_NUM double precision,
TRAN_ID varchar,ELECTION_TP varchar,USELESS_COLUMN varchar
);

copy fec from 'C:\\Users\Public\\Documents\\p1all.csv' CSV HEADER; -- load
in 82 seconds

** Speed Tests **
test1 = select cand_nm, sum(contb_receipt_amt) as total from fec group by
cand_nm;
==> SQlite 21 seconds (wes = 72s)
==> Postgresql  4.8 seconds stable  (44 seconds first time ?) (wes =4.7)

test2 = select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec
group by cand_nm, contbr_st;
select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
by cand_nm, contbr_st;
==> SQlite 27 seconds
==> Postgresql  5.7 seconds   (wes=5.96)

** Conclusion **
WesMcKinney "Sqlite/speed.htm" page about SQLite is 3.4 times more awfull
than what I measure.
Sqlite3.8.3 is about 4 times slower than Postgresql on this two 'raw' Data
analysis Tests.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] About "speed"

[big stone]

Hello,

This morning I saw  Pandas/Wes McKinney communicating figures :
 - starting at 37'37" of http://vimeo.com/79562736,
 - leaking a slide where SQLite "is" 15 times slower than Postgresql.

==> the dataset is public :
http://www.fec.gov/disclosurep/PDownload.do?candId=P0001=2012=All%20Candidates=pNational
==> the sql are basic.

Wouldn't it be nice to update the "speed.html" page to have an objective
vision ?

Rationals :
- better show progress (it's hidden in
http://www.sqlite.org/checklists/3080300/index),
- better show non-time metrics : memory, electricity ,i/o...
- better show options effect : ":memory:" , "compile -o2", ...
- better show SQLite position in the SQL landscape.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] basic "Window function"

[big stone]

Hello,

Sqlite 3.8.3 implemented recursive CTE.

Sqlite 3.8.4 is shaping to be another great tuning/optimisation release.

Would it be possible to get a small basic subset of the sql  windowing
function for Sqlite 3.8.5  ?

If we imagine that the basic windowing is a bit like a CTE 'rewording', it
may be very small in code size, wouldn't it ?

Example :

** a basic SQL window functions usage **

SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
FROM SalesOrderDetail

** 'internal' CTE rewording of a basic SQL window functions usage **


With window_this(SalesOrderID, su, av, co, mi, ma) as (

select SalesOrderID,
SUM(OrderQty),AVG(OrderQty),COUNT(OrderQty),MIN(OrderQty),MAX(OrderQty)
FROM SalesOrderDetail )

SELECT SalesOrderID, ProductID, OrderQty
,su AS 'Total'
,av AS 'Avg'
,co AS 'Count'
,mi AS 'Min'
,ma AS 'Max'
FROM SalesOrderDetail  inner join
  on window_this on SalesOrderDetail.SalesOrderID = window_this.SalesOrderID
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[big stone]

+1 .

A few more 'classic/simple' sql instructions would not be a bad thing :
sqrt(), locate(substring, string, start), ...
They are not in a sql official "normalisation", but :
-  '%' is not either,
- avg() looks a little bit incomplete without sqrt().
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[big stone]

Don't say postgresql is slower :
- I remember now I did then implement the "norvig postgresql",
- and it manages a 20% win over your solution with today postgresql 9.3.2.3.

Anyway, it's indeed a "less inefficient" run for now between SQL motors.

It remains to be seen if improving SQLite in sudoku solving will improve it
for other existing (or not yet imagined) workloads.

** the "norvig" PostgresSQL code **

-- implementing several "logical" algorithms in SQL to solve a sudoku
--
--   Norvig algorithm from Peter Norvig, http://norvig.com/sudoku.html
--
--   Brut Force algorithm from Anton Scheffer,
http://technology.amis.nl/2009/10/13/oracle-rdbms-11gr2-solving-a-sudoku-using-recursive-subquery-factoring/

--   translated per Marcin Mank,
http://archives.postgresql.org/pgsql-general/2009-11/msg00150.php
--
-- Norvig Algorithm =
--   . from given Sudoku problem, generate an array with each cell of the
sudoku being a string with all remaining possibilites
--   . define as "neighbors" of a cell, all cells which must not have the
same piece as this cells, (there are 20 neighbors to each cell)
--   . then analyse the cell (the string) have the less remaining
solutions, but more than 1,
--.. for a given sudoku position, "play" only on the first cell you
found with the less possibilities, and try each of them,
--   ... replace in this cell the string by the possible piece you
want to try,
--   ... remove the piece you played from the neighbors cells own
possibilities (as it's no more a possibility for them)
--... when removing a possibility to a neighbor, if it leave
him only on possibility, "play" it immediately,
--   ... if you remove all the solutions from a neighbor, than
you're not on the sudoku solution path, stop this track.
--
-- Brut force Algorithm =
--. try all possible piece in all empty position of the sudoku problem,
from top left to bottom right of the sudoku board cells,
--. stop a track each time you can't fill the next position
-- 
-- Speed result :
--. brute force loose on the easy and complex sudo, wins on the medium
--. "Brut" and  "Norvig" algorithms seem to stay mono-thread, giving-up
the competitive advantage of SQL
-- ==

-- Array usage in SQL (
http://www.postgresql.org/docs/9.2/static/functions-array.html)
-- array length (on a dimension) :array_length(anyarray, dimension_integer)
-- array to rowset : select unnest(ARRAY[1,2])
-- rowset to array : ARRAY(SELECT * FROM test)
-- array to string : select array_to_string(ARRAY[1, 2, 3, NULL, 5], ',',
'*')
-- string to array : select string_to_array('xx~^~yy~^~zz', '~^~', 'yy')



CREATE OR REPLACE function  format_string_sudoku(board  text [])
-- displays a sudoku "board" in 9x9 form with delimiters (so 13 line
records)
RETURNS setof text
LANGUAGE SQL
AS $$
with recursive  formatted_string(ss , ind )

as (select '' , 81 FROM generate_series(1,1) lp
union all
   select (case when length($1[ind])=1 then $1[ind] else '_' end) || (case
  when ind % 81=0 then '|='
  when ind % 27=0 then '||---||'
  when ind % 9=0 then '||'
  when ind % 3=0 then '|' else '' end)
  || ss , ind-1 from formatted_string where ind>0 )

select unnest(string_to_array( '=|' || ss, ''))
from formatted_string where ind=0
$$;

CREATE OR REPLACE function neighbors(cell integer)
-- gives back the rowset of neighbors of the given "cell" of any sudoku
board
RETURNS SETOF integer
LANGUAGE SQL
AS $$
with list_of(neighbors) as (select   unnest(ARRAY[
  mod( $1 - 1, 9 ) - 8 + lp * 9 ,
  ( ($1 - 1 ) / 9 ) * 9 + lp  ,
   mod( ( ( $1 - 1 ) / 3 ), 3 ) * 3 + ( ( $1 - 1 ) / 27 ) * 27 + lp + (
( lp - 1 ) / 3 ) * 6]) FROM generate_series(1,9) lp
  )
select  distinct * from list_of where
neighbors<>$1
$$;


CREATE OR REPLACE function truly_possibles(board text, cell integer)
-- gives the table of possible pieces to put in given "cell"  of the
current  sudoku "board"
-- including the one already in place, if it exists
RETURNS SETOF text
LANGUAGE SQL
AS $$

select z from   (SELECT gs::text AS z FROM generate_series(1,9) gs) z
  WHERE  substr( $1,  $2  ,1)=' '  and  NOT EXISTS ( SELECT NULL
   FROM neighbors($2) lp
   WHERE   z = substr( $1,   lp  , 1 )
 )
  union select  substr( $1,  $2 ,1 ) z where  substr( $1,  $2 ,1 )<>'
'
$$;

create or replace function create_sudoku_board(initial   text) returns
setof text[]
-- create a sudoku board from the given "s" (string form) sudoku problem
language sqlas
$$

with recursive

-- handle several string presentations of the sudoku board problem to solve
cleaned_starter(starter) as (select
regexp_replace(regexp_replace(initial,'\n|\=|\-|\|', '' ,'g' ),'\.','
','g'  )),

-- recursively generate the board from the bottom right cell to the top
left of the sudoku board
board_generator( t  , board , next_cell 

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[big stone]

Hi,

You're right,' recommands' is not in the code, but was not run anymore
either.
The "x" table cte definition should be removed, as the final request  use
only "q".

Anyway, after all this algorithmic surgery, Postgresql was slower with the
'optimization' algorithm than with the brute force.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[big stone]

Congratulations, *E.Pasma* !

You indeed won the "brute force sudoku speed" competition for sqlite 3.8.3
by a factor of  x2.5 on my PC !

D. Richard Hipp is still tuning the CTE implementation, so competition may
be less slow with 3.8.4.

To get clother to python speed (http://norvig.com/sudoku.html), we would
need to implement :
- an efficient Peter Norvig's method,
- or an efficient Dancing Links method.

==> See below link for "slower than brute force" dancing links
implementation in pgSQL.

http://www.postgresql.org/message-id/caj-6ajqpxxex8ftljksngksvbjrn0h3hydw9xpsvu837gsn...@mail.gmail.com

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

Re: [sqlite] struggling with a query

[big stone]

with sqlite 3.8.3 (for the with) :

with v(vid,name) as (values (1,'foo'),(1,'bar'),(2,'bar'),(2,'baz'))

select  name,
 -max(case when vid=1 then 1 else 0 end ) + max(case when vid=2
then 1 else 0 end)
 from v group by name
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[big stone]

Hello,

APSW looks indeed great for specialised installations.

If we wish to have SQLite + Python combination to become "reference choice"
in education, I would think that priority list should be :

- fixing true problems among those listed in "http://bugs.python.org/; with
search the keyword "SQLite" :
  (example : http://bugs.python.org/issue9303 : "Migrate sqlite3 module to
_v2 API to enhance performance")

- supporting a little more "SQL standard" features at each maintenance
release of SQLite,

- having an evolution in current Python.org Policy not to change of SQLite
version between 3.x.0beta1 and 3.x.9,
(it was no problem as long as SQLite was not evolving to support more
SQL standard)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] random result

[big stone]

A disturbing effect of the implementation of CTE :

with sample(rank,rand) as (values(1,random()),(2, random()))
 select * from sample a, sample b on a.rank=b.rank;

gives :


rank rand rank rand   0 1 -4662617171230690406 1 -8784008985057089983  1 2
-8243192423181459578 2 2566393184580211567
==> It would be nice if somehow we could suggest SQLite to pre-calculate a
CTE.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] recursive common table expression, an example

[big stone]

group_concat is indeed super nice ! I didn't notice that little jewel of
SQLite, thank you.

Is there a "standardized" SQL normalization for that ? (I see that oracle
has a LISTAGG instead)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] recursive common table expression, an example

[big stone]

Other usage  = A workaround to  "For XML PATH" (see
http://www.sqlusa.com/bestpractices/training/scripts/commadelimitedlist/ )

List of colors of a bag :
**

with colors(bag,color) as
(values('bag1','blue'),('bag1','red'),('bag2','green'),('bag2','yellow'),('bag1','yellow'))
   , bags (bag, colors, last) as (
   select  bag , min(color) as colors, min(color) as last from colors
group by bag
   union all
   select b.bag, colors ||' - ' ||  color  ,  color  from bags b ,
colors l
where b.bag=l.bag and l.color=(
select min(color) from colors as x where b.bag=x.bag and
b.last gives :
*
  bag colors  bag1 blue - red - yellow  bag2 green - yellow
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[big stone]

Maybe Chrismas will be in february this year  :

http://bugs.python.org/issue20465

Whatever will be the answer from the python team, THANKS a lot Mr Hipp, Mr
Kennedy, and Mr Mistachkin for making it possible !
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation

[big stone]

This page is more than awseome :
http://www.sqlite.org/draft/lang_select.html

By comparison, this other page is more than outdated, and drags down the
website global experience : http://www.sqlite.org/draft/speed.html

==> Wouldn't it be better to remove it, for the moment ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragmas in subselects?

[big stone]

Would the implementation of a subset of  "Information schema" also allow
the "Alter table" function to work in more (SQL) standard situations ?

(killing two birds with one stone)

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

Re: [sqlite] pragmas in subselects?

[big stone]

There is a non-logicality of having "pragma table_info(my_table)" :
- answering like a select,
- but being not usable as a "select", nor queriable by a "select".

Other databases seem more logical on this practical matter.

Multi-motor tools, like dbeaver, have currently much less good support of
"database diagram" when dealing with SQLite.
==> Is it the reason ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Graphic SQLite Client Tool

[big stone]

Hi Again,

Is there a longtime user of "http://sqlitestudio.pl; here that could write
about his user experience ?

==> It looks a much simpler tool than dbeaver (no java ! no painfull
installations on 40 old PCs !)
==> I would like to know if it is stable to rely on it for a classroom.

sqlitestudio-2.1.5.exe
md5 : 9d1600399a1d70ff72a4a1b5c9cbfb88
sha1 : f1377993868c9225a1b3c4c447dda84f09a7fc55
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Graphic SQLite Client Tool

[big stone]

Hello sqlite-users,

I'm looking for a Graphic SQLite  Client Tool  to equip a classroom of  old
windows PC.

So far, the best option I found is :
- dbeaver 2.3.6 (multi-motors : SQLite, Mysql, Postgresql)
- with the latest Xerial driver sqlite-jdbc4-3.8.2-SNAPSHOT.jar (with
SQLite3.8.2)


Has anyone knowledge and practice of a better option ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query executes in sqlite manager but not sqlite database in android

[big stone]

It seems Android use a pretty outdated SQlite motor : SQlite 3.7.1 = march
20th, 2012

http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android

SQLite 3.7.11:

19-4.4-KitKat
18-4.3-Jelly Bean
17-4.2-Jelly Bean
16-4.1-Jelly Bean

SQLite 3.7.4:

15-4.0.3-Ice Cream Sandwich
14-4.0-Ice Cream Sandwich
13-3.2-Honeycomb
12-3.1-Honeycomb
11-3.0-Honeycomb

SQLite 3.6.22:

10-2.3.3-Gingerbread
9-2.3.1-Gingerbread
8-2.2-Froyo

SQLite 3.5.9:

7-2.1-Eclair
4-1.6-Donut
3-1.5-Cupcake
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query executes in sqlite manager but not sqlite database in android

[big stone]

Hi Kamulegs,

Your SQLiteManager includes a version of SQLite >=3.7.16 , and your android
application does not.

Indeed the syntax "  (b JOIN c ON b.id = c.id)" is only accepted without
this errror after 3.7.16.
==> If you can rewrite your syntax without these parenthesis (like below),
all should be fine.

*SELECT M._id as Meter_id, M.MeterNumber, R1.ReadingDate as ReadingDate,
R1.Reading AS CurrentReading, R2.ReadingDate AS PrevReadingDate, R2.Reading
AS PrevMeterReading, R2.Rate as Rate, R2._id as _id,R1.TenantMeter_id
FROM Meters AS M INNER JOIN TenantMeters ON M._id = TenantMeters.Meter_id
INNER JOIN MeterReading AS R1 INNER JOIN MeterReading AS R2 ON
R1.TenantMeter_id = R2.TenantMeter_id ON TenantMeters._id =
R1.TenantMeter_id
WHERE (((R2.ReadingDate)=(SELECT Max(R3.ReadingDate)
  FROM [MeterReading] AS R3
  WHERE (R3.TenantMeter_id = R1.TenantMeter_id)
AND (R3.ReadingDate < R1.ReadingDate)
))) OR (((R2.TenantMeter_id) Is Null))
*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[big stone]

hi again,

With the version sqlite-amalgamation-201401242258   of this night.

 I found my error so timings are :

timing with medium sudoku example  :
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
2 nested with = 3.06 sec (3.32 sec previous beta of 21rst) 8% faster
1 nested with = 1.7 sec   (1.7 sec previous beta of 21rst) 0%
3 nested with = 2.65 sec (2.5 previous beta of 21rst) 5% faster
(1 nested with which could be 3 nested with) = 1.05 sec (1.09 sec
previous beta of 21rst) 3% faster

remark1 :
-
On my non-cte workload, it 24th  version seems quicker by  3% faster than 21rst)

==> It's curious, were there speed improvement commits since beta 2 ?


remark 2:
-
I'm using Keith buildMSVC.cmd file to compile (that I'm using without
knowing what it does exactly)

I have one more compile error than with 21th version.
"c1 : fatal error C1083: Impossible d'ouvrir le fichier source : 'CSVImport.c' "

==> Maybe it's normal.

*** full compile report ***
>buildMSVC.cmd
CompileOptions=/nologo /O2 /GLFAy /fp:precise
Creating library file: SQLite3.dll
sqlite3.c
   Création de la bibliothèque SQLite3.lib et de l'objet SQLite3.exp
Génération de code en cours
Fin de la génération du code
Creating executable;   CSVImport.exe
CSVImport.c
c1 : fatal error C1083: Impossible d'ouvrir le fichier source : 'CSVImport.c' :
No such file or directory
Creating executable:   SQLite3d.exe
shell.c
Génération de code en cours
Fin de la génération du code
Creating executable:   SQLite3s.exe
shell3x.c
shell3x.c(146240) : warning C4005: 'ArraySize' : redéfinition de macro
shell3x.c(8545) : voir la définition précédente de 'ArraySize'
Génération de code en cours
Fin de la génération du code
Copying Build to DIST
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[big stone]

Hi again,

On my 3 level of with example, my systems seems to "hang" if  :
- I put a "distinct" keyword in goods definition,
- or if I replace "union all" per a simple "union" .



** failure mode 1 (no union all in the neighbors) **
with digits(z, lp) AS (
   select '1' as z, 1 as lp
   UNION ALL SELECT
   CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
y(r) as (WITH RECURSIVE col(c) AS (
   select 81  as c
   union all
   select c-1 from col where c>1
   )  select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union  select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union  select r, (((r-1)/3) % 3) * 3
   + ((r-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6 from y, digits)
, goods (c,n) as (select  * from neighbors where r <>n)

,input(sud) AS (
   VALUES(

'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
)
),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM goods AS lp
 WHERE ind=lp.c and  z.z = substr(s, n, 1)
   )
)

SELECT * FROM x  WHERE ind=0;


** failure mode 2 (distinct in the goods definition) **
with digits(z, lp) AS (
   select '1' as z, 1 as lp
   UNION ALL SELECT
   CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
y(r) as (WITH RECURSIVE col(c) AS (
   select 81  as c
   union all
   select c-1 from col where c>1
   )  select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union all select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union all select r, (((r-1)/3) % 3) * 3
   + ((r-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6 from y, digits)
, goods (c,n) as (select distinct  * from neighbors where r <>n)

,input(sud) AS (
   VALUES(

'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
)
),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM goods AS lp
 WHERE ind=lp.c and  z.z = substr(s, n, 1)
   )
)

SELECT * FROM x  WHERE ind=0;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[big stone]

hi again,


I found my error so timings are :

timing with medium sudoku example  :
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
2 nested with = 3.32 sec
1 nested with = 1.7 sec
3 nested with = 2.65 sec
(1 nested with which could be 3 nested with) = 1.09 sec

** 3 nested with version **
with digits(z, lp) AS (
   select '1' as z, 1 as lp
   UNION ALL SELECT
   CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
y(r) as (WITH RECURSIVE col(c) AS (
   select 81  as c
   union all
   select c-1 from col where c>1
   )  select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union all select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union all select r, (((r-1)/3) % 3) * 3
   + ((r-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6 from y, digits)
, goods (c,n) as (select * from neighbors where r <>n)

,input(sud) AS (
   VALUES(
  
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
)
),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM goods AS lp
 WHERE ind=lp.c and  z.z = substr(s, n, 1)
   )
)

SELECT * FROM x  WHERE ind=0;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[big stone]

Hi,

The speedest version of the sudoku, staying in the limit of lisibility
would include 3 nested "with",

timing with medium sudoku example  :
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
2 nested with = 3.32 sec
1 nested with = 1.7sec

(1 nested with which could be 3 nested with) = 1.09 sec (see below 'annexe
1')

3 nested with   well I get a "MemoryError: " event on the simplest
example. (see below  'annexe 2')
==> nobody in real life would do that, would he ?

Anyway, when I was trying to implement "fake" CTE, I was :
- creating views when the CTE was like " x as (select ...)"
- creating table when the cte was like "x(r) as (select ...)"
- creating table with index when the cte was like (x(r INTEGER

*PRIMARY KEY) as (select ...)"*


*==> So, if the if sudoku solving at the maximum speed with only virtual
table was a DEAD or ALIVE situation, I may suggest "embrassing and
extending" the CTE standard with a similar trick.*


 example of "optimized" stupid-brut force sudoku 1 with **

drop table if exists ok;
create table ok(c,n);
CREATE INDEX ok_i
on ok (c, n);
with digits(z, lp) AS (
VALUES('1', 1),('2', 2) ,('3', 3),('4', 4),('5', 5),('6', 6),('7',
7),('8', 8),('9', 9)
)
,y(r) as (WITH RECURSIVE col(c) AS (
   select 81  as c
   union all
   select c-1 from col where c>1
   )  select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union all select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union all select r, (((r-1)/3) % 3) * 3
   + ((r-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6 from y, digits)
, goods (r,n) as (select * from neighbors where r <>n)
insert into ok select distinct * from goods;

-- easy   (0 sec)
'53..76..195986.8...6...34..8.3..17...2...6.628419..58..79'
-- medium (2 sec)
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
-- hard   (200 s)
'8..36..7..9.2...5...7...457.1...3...168..85...1..94..'

WITH RECURSIVE input(sud) AS (
   VALUES(
 
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
)
),

/* A table filled with digits 1..9, inclusive. */
digits(z, lp) AS (
VALUES('1', 1),('2', 2) ,('3', 3),('4', 4),('5', 5),('6', 6),('7',
7),('8', 8),('9', 9)
),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM ok AS lp
 WHERE ind=lp.c and  z.z = substr(s, n, 1)
   )
)

SELECT * FROM x  WHERE ind=0;

 same with 3 nested with ... blow up even on the simplest case (bug of
me ? ) ***
drop table if exists ok;
create table ok(c,n);
CREATE INDEX ok_i
on ok (c, n);
with digits(z, lp) AS (
VALUES('1', 1),('2', 2) ,('3', 3),('4', 4),('5', 5),('6', 6),('7',
7),('8', 8),('9', 9)
)
,y(r) as (WITH RECURSIVE col(c) AS (
   select 81  as c
   union all
   select c-1 from col where c>1
   )  select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union all select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union all select r, (((r-1)/3) % 3) * 3
   + ((r-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6 from y, digits)
, goods (r,n) as (select * from neighbors where r <>n)


,input(sud) AS (
   VALUES(
-- 
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
'53..76..195986.8...6...34..8.3..17...2...6.628419..58..79')
),

/* A table filled with digits 1..9, inclusive. */
--digits(z, lp) AS (
--VALUES('1', 1),('2', 2) ,('3', 3),('4', 4),('5', 5),('6', 6),('7',
7),('8', 8),('9', 9)
--),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM ok AS lp
 WHERE ind=lp.c and  z.z = substr(s, n, 1)
   )
)

SELECT * FROM x  WHERE ind=0;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clang 3.3 and Scan-Build results

[big stone]

Performance of 3.8.3 beta sqlite-amalgamation-201401221923 on windows7

For "CTE recursive" in memory 'batch' workload (sudoku):
- latest beta is quicker than previous 3.8.3 beta :
   . by 7% with one "recursive table",
   . by 15% with two nested "recursive table".

For "normal" in memory 'batch' workload (small database ,  loaded from a 20
Mo .csv) :
- no change between betas of 3.8.3
- 3.8.3 beta is quicker by 18% than 3.8.2  ... ??
  (and 3.8.2 was, for this workload, as slow as 3.7.17)

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

Re: [sqlite] Clang 3.3 and Scan-Build results

[big stone]

Hello,


To have more windows beta-testers, would it be possible ton include during
beta phase :
- a downloadable sqlite3.8.3beta.exe ?
- a downloadable sqlite3.8.3beta.dll ?

Other alternative nice to have feature on the download page would be :
- a md5 signature and  a sha-256 signature ,
- a complete procedure to compile the source with Microsoft Visual Studio
C++ 2010 Express
( I was totally lost until Keith "buildMSVC.cmd" file provided me the
missing stone)

A lot of potential beta-testing people in the Windows Python world may have
give up because the complexity to test the beta.

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

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

[big stone]

Hi Keith,

Indeed just removing the CTE creation of the DIGITS makes Dan's version up
to speed.

Would the "wholenumber" external SQLite module help :
- to make SQLite code cleaner ? (like "generate_series" of Postgresql, or
"dual" of Oracle)
- still provide the same speed-up ?


Portfolio of typical Sudokus
-- easy   (0 sec)
'53..76..195986.8...6...34..8.3..17...2...6.628419..58..79'
-- medium (2 sec)
'17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..'
-- hard   (200 s)
'8..36..7..9.2...5...7...457.1...3...168..85...1..94..'

WITH RECURSIVE input(sud) AS (
   VALUES(
'53..76..195986.8...6...34..8.3..17...2...6.628419..58..79'
   )
),

/* A table filled with digits 1..9, inclusive. */
digits(z, lp) AS (
VALUES('1', 1),('2', 2) ,('3', 3),('4', 4),('5', 5),('6', 6),('7',
7),('8', 8),('9', 9)
),

/* The tricky bit. */
x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL
   SELECT
   substr(s, 1, ind-1) || z || substr(s, ind+1),
   instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
   FROM x, digits AS z
   WHERE ind>0
   AND NOT EXISTS (
 SELECT 1 FROM digits AS lp
 WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
 OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
 OR z.z = substr(s, (((ind-1)/3) % 3) * 3
   + ((ind-1)/27) * 27 + lp
   + ((lp-1) / 3) * 6
   , 1)
   )
)

SELECT s FROM x WHERE ind=0;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users