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

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

[sqlite] under the christmas tree

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

[sqlite] whish list for 2016

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

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

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

Hi all, Just a link to this commit: http://bugs.python.org/msg247538 Regards,

Re: [sqlite] equivalent for JOIN LATERAL

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

Re: [sqlite] equivalent for JOIN LATERAL

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

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

[sqlite] Whish List for 2015

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

Re: [sqlite] Stored Procedures

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

Re: [sqlite] 50% faster than 3.7.17

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

Re: [sqlite] 50% faster than 3.7.17

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

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

... 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'

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.

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

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

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

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

Re: [sqlite] Window functions?

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

Re: [sqlite] Window functions?

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 (

Re: [sqlite] SQLite 3.8.6 beta

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

Re: [sqlite] SQLite 3.8.6 beta

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 *

Re: [sqlite] SQLite version 3.8.6 coming soon

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

Re: [sqlite] SQLite version 3.8.6 coming soon

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

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

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.

Re: [sqlite] SQLite version 3.8.6 coming soon

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,

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

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?

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

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

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

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

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

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

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

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

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

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

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

Re: [sqlite] sqlite4 completion status

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)

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

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

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 .

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

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

Re: [sqlite] Comments loss in sqlite_master table

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

Re: [sqlite] Comments loss in sqlite_master table

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

Re: [sqlite] Comments loss in sqlite_master table

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] Comments loss in sqlite_master table

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 */

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

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

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

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

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

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

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

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

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):

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

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 ?

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

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

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

Re: [sqlite] SQLite for single user data manipulation

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 :

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

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

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

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

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

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

[sqlite] about the apparently arriving soon "threads"

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

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

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 =

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

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'

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

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.

Re: [sqlite] basic "Window function"

"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"

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

Re: [sqlite] sqlite3_progress_handler callback behavior change

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?

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

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

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, ___

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

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

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

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 :

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

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

Re: [sqlite] About "speed"

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

[sqlite] about "windows" downloadable versions

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

Re: [sqlite] About "speed"

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

Re: [sqlite] About "speed"

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,

Re: [sqlite] SQLite version 3.8.4 release schedule

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

Re: [sqlite] About "speed"

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

Re: [sqlite] About "speed"

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

Re: [sqlite] About "speed"

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 :

Re: [sqlite] SQLite destroys civilization.

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

Re: [sqlite] About "speed"

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

Re: [sqlite] About "speed"

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

[sqlite] About "speed"

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 :

[sqlite] basic "Window function"

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

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

+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().

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

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

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

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.

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

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

Re: [sqlite] struggling with a query

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

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

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] random result

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 ==>

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

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

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

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

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

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] Documentation

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 ?

Re: [sqlite] pragmas in subselects?

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

Re: [sqlite] pragmas in subselects?

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

Re: [sqlite] Graphic SQLite Client Tool

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.

[sqlite] Graphic SQLite Client Tool

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

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

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

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

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

Re: [sqlite] "Common Table Expression"

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%

Re: [sqlite] "Common Table Expression"

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

Re: [sqlite] "Common Table Expression"

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

Re: [sqlite] "Common Table Expression"

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

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

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'

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

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

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

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

  1   2   >