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

[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

[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

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

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

[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] 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,

[sqlite] "Common Table Expression"

Hello, Does SQLite plan to implement soon a "Common Table Expression" subset ? CTE would allow to write much more readable SQLite 'SQL' code. It doesn't seem complex to do, as long as the 'RECURSIVE' part of CTE is ignored. Regards, ___ sqlite-users

Re: [sqlite] "Common Table Expression"

Hi again, "sub-select" method : - allows you to do only a part of what you can do in CTE, - becomes more and more difficult to read as you add tables and treatment in your sql. With CTE in SQLite, it would be possible to: - decompose your SQL treatment in clean intermediate steps, - make your

Re: [sqlite] "Common Table Expression"

Hi again, Indeed, '1' CTE can be replaced by the creation of 'N' temporary views (or tables), and their deletion after the CTE request. CTE is : - a syntaxic simplification : . the SQL creator don't have to care about those intermediate views, . these intermediate views don't grow and

Re: [sqlite] "Common Table Expression"

Hi again, I know the focus of SQLite people on "size", and "testing". Wouldn't a basic implementation of CTE in fact : - help the "size" of the global embedded system to be smaller ? - without impacting the "testing" ? Indeed, a "simple" implementation of CTE : * could rely only on SQLite

Re: [sqlite] "Common Table Expression"

As a proof of concept, I programmed a translation layer from "CTE" to "SQLite" : "with x as (y)... select z" into "drop view if exists x;create temp view x as y; ...; select z" "with x(d) as (y), ... select z" into "drop table if exists x;create temp table x(d) as y;insert into x y; ...

Re: [sqlite] "Common Table Expression"

Hello "CTE in SQLite" fans, To get CTE in SQLite, I guess we must answer by the example the fears expressed by Simon and Rsmith. I propose the following method : - unproven-demand : ==> publish on this mailing list external "CTE for SQLite" implementations, ==> if there is demand :

Re: [sqlite] "Common Table Expression"

Hi James K. Lowden, You're right : *"*CTEs ... add exactly zero to SQLite's capability." This is also right : "C Language ... add exactly zero to Intel X86 processor capability". In both case : - "adding zero capability" to the underlying tool is a physical constraint, - CTE (or C Language)

Re: [sqlite] "Common Table Expression"

Hello, When bigger brains created CTEs in SQL:99, I suppose they discussed a long moment the technical merit of CTEs. In my own experience : - the maintenance burden of my queries dropped significantly because of them, - I stopped harrassing (myself or a central database administrator) for views

Re: [sqlite] "Common Table Expression"

(sorry keyboard fall on the floor) Now : - I wouldn't let someone use SQL without allowing him to use CTE, - I need to use ubiquitous SQL motors, which are only TWO on windows (Access and SQLite) , and they still don't have CTE. ___ sqlite-users mailing

Re: [sqlite] SQLite 2013 retrospective

Whish list for 2014 = . a simplified implementation of "Common Table Expressions", . amazing SQlite4 performance when in ":memory:". ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite4 completion status

Hello, Is there a completion status for the sqlite4 project somewhere ? An estimate Year/Quarter for a first downloadable version ? I don't find that information in http://sqlite.org/src4/doc/trunk/www/index.wiki Regards, ___ sqlite-users mailing list

Re: [sqlite] "Common Table Expression"

The interest of this "forward lateral" move was its good standardization/effort ratio. For performance/effort ratio, I would have expect people to push SQLite4 and its 2x to 10x promise. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Generating sqlite4.c

Will you write a small result of your early experiment ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive query?

This is really great news ! Will it be developped also for SQLite 4 ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Insert multiple values as an economy of coding time and quicker upload process ?

Hello, I read the source code of sqlite and I find a lot of : CREATE TABLE t1(x INTEGER); INSERT INTO t1 VALUES(1);INSERT INTO t1 VALUES(2); Wouldn't it be both an economy of coding time and a more quick upload process to allow Multiple inserts SYNTAX ? like : INSERT INTO t1

Re: [sqlite] Insert multiple values as an economy of coding time and quicker upload process ?

Yes, I was talking of the very test script that were written last friday : [a26f399ba4] like : do_execsql_test 4.1 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(x); INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES(2); INSERT INTO t1 VALUES(3); INSERT

[sqlite] SqlAlchemy 0.9 release note complaint

Hello, Reading SQLAlchemy release note, I see a functionality complaint on SQLite. see :http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html In its last release Note, SQLAlchemy complains that SQLite doesn't allow this : SELECT a.*, b.*, c.* FROM a LEFT OUTER JOIN (b JOIN c ON

Re: [sqlite] Insert multiple values as an economy of coding time and quicker upload process ?

I'm not an expert in SQLite extension. Unless I missed something, Wholenumber seems apparently not embeded into SQLite for python : CREATE VIRTUAL TABLE nums USING wholenumber; SELECT value FROM nums WHERE value<1; SELECT value FROM nums WHERE value BETWEEN 100 AND 500 gives :

Re: [sqlite] SqlAlchemy 0.9 release note complaint

Ok, The problem is fixed starting SQLite 3.7.16, But Python 3.3 series (up to 3.3.3) seems to be shipped with the very old SQLite 3.7.12, by default. Is there a simple trick to "upgrade" SQLite3 version used by Python (under windows) ? Just changing the sqlite3.dll is safe ?

Re: [sqlite] SqlAlchemy 0.9 release note complaint

Python 3.4 last beta3 is planned for January 26, 2014 Current SQLite in Python3.4 beta2 is 3.8.1. Unless a miracle occurs these next few days, we will not see CTE in standard Python before 2 years more. ___ sqlite-users mailing list

Re: [sqlite] "Common Table Expression"

Yes ! It's in trunk. With Keith Medcalf's help, I suceeded to compile it. => Thanks a lot Keith ! I can confirm : - the sqlite3.dll works under python3 by swapping the default one, - I just did my first recursive CTE under Ipython notebook. ==> I would never have imagined that to happen so

[sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

Hello, I'm trying to do the SQLite version of the sudoku solver that exist here in POSTGRESQL (here : http://wiki.postgresql.org/wiki/Sudoku_puzzle) I'm at this stage below currently. could anyone help me ? I have a syntax error , and a bit confuse where it can be (not sure about the modulo

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

hello again, I get the message of error "OperationalError: circular reference: x" from the recursive CTE below. Is it a syntax error or a bug ? drop table if exists gen9; drop table if exists genx; create table gen9(z); insert into gen9 values

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

I think this should work, but with current almagation avalaible for download sqlite-amalgamation-201401171527, I get nothing. Has anyone a clue ? drop table if exists gen9; drop table if exists genx; drop table if exists initial; create table gen9(z); insert into gen9 values

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

sudoku ! drop table if exists gen9; create table gen9(z); insert into gen9 values ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); drop table if exists initial; create table initial ( s, ind) ; insert into initial select sud, instr( sud, ' ') from (SELECT '53 76 195986 8

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

Recursive CTE are really fun to : - describe algorithms in sql, - measure sql motor speed. For the hardest sudoku at hand that I have: '8..36..7..9.2...5...7...457.1...3...168..85...1..94..' - my badly written sql takes about 3'40", (on my windows pc) - your neater

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

I'm still making desesperate prayers to have this SQLite3.8.3 in Python3.4. Python3.4 beta2 is using in SQLite3.8.1 "SQL CTE motor inside !" could be a big incentive for Python2.X users to switch to Python 3.4. ___ sqlite-users mailing list

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

And : - python3.4 is still in beta also, not a released product. - sadly first RC candidate 1 is February 9, 2014, 2 days earlier. Sqlite was so close now, *so close* to achieve this goal ... very sad. ___ sqlite-users mailing list

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

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

[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] 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.

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

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

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

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

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

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

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

[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

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

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

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

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

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

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

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

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

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

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

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

[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

[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

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

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

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

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

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

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

  1   2   >