Re: [sqlite] Support for millisecond
On Jan 8, 2015, at 7:21 PM, Lance Shipman lship...@esri.com wrote: Can SQLite support millisecond precision in date time data? I looking at doc I think so, but it's not clear. There is no 'date time’ data type in SQLite. Feel free to store your time data as either text or number. To whatever precision suits you. There are a couple of built-in utility functions to convert things back and forth: http://www.sqlite.org/lang_datefunc.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] using a hex integer as input to DateTime
On Jan 7, 2015, at 11:35 PM, Paul Sanderson sandersonforens...@gmail.com wrote: Hmm - why can't I get that to work when the hex value is stored in a column, i.e. Most likely your data is stored as text, e.g.: with DataSet as ( select '0x49962d2' as value union all select 0x49962d2 as value ) select DataSet.value, DateTime( DataSet.value, 'unixepoch' ) as date, typeof( DataSet.value ) as type fromDataSet value|date|type 0x49962d2||text 77161170|1972-06-12 01:39:30|integer To quote the nice manual: hexadecimal integer notation is only understood by the SQL language parser, not by the type conversions routines”: https://www.sqlite.org/lang_expr.html#hexint ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] using a hex integer as input to DateTime
On Jan 7, 2015, at 11:08 PM, Paul Sanderson sandersonforens...@gmail.com wrote: Is this possible? With a contemporary version of SQLite, yes: sqlite select DateTime(77161170, 'unixepoch'); 1972-06-12 01:39:30 sqlite select DateTime(0x49962d2, 'unixepoch'); 1972-06-12 01:39:30 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Whish List for 2015
On Dec 21, 2014, at 10:47 AM, big stone stonebi...@gmail.com wrote: - a minimal subset of analytic functions + MERGE! Yeah! Happy Holidays! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Search for text in all tables
On Dec 4, 2014, at 10:26 PM, Roger Binns rog...@rogerbinns.com wrote: That will only work under the simplest of cases. Simplicity first and foremost. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS
On Oct 8, 2014, at 6:14 AM, Stephen Chrzanowski pontia...@gmail.com wrote: When adding a NULL value to a table that has the NOT NULL flag set on that field, instead of raising an exception, if the field definition were to have the word USE between ON CONFLICT and DEFAULT in its declaration, it'd use whatever the fields default value was set to. If USE is included, the DEFAULT value must be included, otherwise the table isn't created. Oh, wait… isn’t that what an other, unnamed database does? [1] E.g. DEFAULT ON NULL [1] http://www.oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1.php#nulls ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS
On Oct 8, 2014, at 8:51 PM, Stephen Chrzanowski pontia...@gmail.com wrote: If the field def'n were to be changed to [ col2 NUMBER DEFAULT ON NULL 0 ] and then when I insert/update something that becomes NULL and the result becomes 0 for that field, then yeah, bingo. Yep, that’s exactly what it says on the tin. But back to SQLite... your best bet is to implement such behavior with triggers. Same difference really. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] An order by problem, maybe a bug?
On Sep 20, 2014, at 9:21 PM, Simon Slavin slav...@bigfraud.org wrote: Anyone who worked for a big company these days and created such a database should get called in and told to do it again properly. Along these same lines: Your last name contains invalid characters http://blog.jgc.org/2010/06/your-last-name-contains-invalid.html Falsehoods Programmers Believe About Names http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/ How do you like the vCard specification? http://tools.ietf.org/html/rfc6350 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create join and add unique column
On Sep 16, 2014, at 8:15 PM, RSmith rsm...@rsweb.co.za wrote: could you show how to achieve this in SQL via the ranking method you linked Well, ranking is the same as numbering, no? So, for example: with NameSet as ( select 1 as id, 'paul' as name union all select 2 as id, 'helen' as name union all select 3 as id, 'melanie' as name ), CountrySet as ( select 1 as id, 'uk' as name union all select 20 as id, 'scotland' as name -- ), DataSet as ( select NameSet.id || '.' || CountrySet.id as key, NameSet.id as name_id, NameSet.name as name_name, CountrySet.id as country_id, CountrySet.name as country_name fromNameSet cross join CountrySet ) selectcount( * ) as id, DataSet.name_id as name_id, DataSet.name_name as name_name, DataSet.country_id as country_id, DataSet.country_name as country_name from DataSet join DataSet self onself.key = DataSet.key group by DataSet.name_id, DataSet.name_name, DataSet.country_id, DataSet.country_name order by 1; id|name_id|name_name|country_id|country_name 1|3|melanie|2|scotland 2|3|melanie|1|uk 3|2|helen|2|scotland 4|2|helen|1|uk 5|1|paul|2|scotland 6|1|paul|1|uk Or something :D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SET (x,y) = (x1,y1)?
On Sep 15, 2014, at 7:08 PM, Hick Gunter h...@scigames.at wrote: Maybe you can reformulate the query to fit INSERT OR UPDATE INTO t SELECT t.a,t.b,...,s.x,s.y FROM t, s … There is no such a thing as 'INSERT OR UPDATE’ in SQLite. There is a ‘REPLACE’, but it’s definitively not the same as an update. Anyway, what the OP would benefit from is a straightforward MERGE statement: http://en.wikipedia.org/wiki/Merge_(SQL) But there is no such functionality in SQLite either. Sigh... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG: Aggregate functions in subqueries
On Sep 15, 2014, at 4:48 PM, Richard Hipp d...@sqlite.org wrote: On Sun, Sep 14, 2014 at 12:18 AM, Lea Verou l...@verou.me wrote: Per the 3.7.11 changelog [1], queries of the form SELECT max(x), y FROM table return the value of y from the same row that contains the maximum x value. However, this: select y from (SELECT max(x), y FROM table); would not return the same y rows. This would work as expected: select m, y from (SELECT max(x) as m, y FROM table); I'm not sure if this qualifies as a bug or not, since the behavior is unspecified in the official documentation. Nevertheless, it is now fixed on trunk. Considering that the original query is non-sensical to start with, not quite sure what’s there to fix in the first place. Aside, of course, from raising an exception. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] XML into sqlite
On Sep 11, 2014, at 5:45 PM, Carlos A. Gorricho cgorri...@heptagongroup.co wrote: Next step is to venture into XML - sqlite integration...both ways. Considering you are on a *nix system, you may find Dan Egnor’s xml2 set of command line utilities of interest: http://www.ofb.net/~egnor/xml2/ Allows for rather straightforward transformation of XML into something more palpable, and back. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on expressions
On Sep 3, 2014, at 3:01 PM, Dominique Devienne ddevie...@gmail.com wrote: Asked differently, if adding this support, could this be done by adding virtual / computed columns to tables, and indexing those columns? Ohohohoho… virtual columns [1][2]…. yes… shinny! :) Now that would be rather cool. On the other hand, if one had to choose, I would rather see a MERGE statement, than some funky virtual columns. While virtual columns are handy at time, they are a bit exotic, all things being equal. On the other hand, MERGE is a must have. No amount of creative select + insert + update concoctions can begin to compensate for its absence in SQLite. A huge gap altogether. [1] http://en.wikipedia.org/wiki/Virtual_column [2] http://www.oracle-base.com/articles/11g/virtual-columns-11gr1.php ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting dates...
On Sep 2, 2014, at 9:48 PM, jose isaias cabrera jic...@cinops.xerox.com wrote: Thoughts? Thanks. SQLite doesn’t have date per se. You are free to store dates as either text or number, or anything you please. But it’s your responsibility to keep it straight. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting by month with two dates input
On Sep 1, 2014, at 6:00 PM, Igor Tandetnik i...@tandetnik.org wrote: (case when billdate != '' then billdate else bdate end) Or, more succinctly: coalesce( nullif( billdate, ‘’ ), bdate ) (To OP: empty strings are E V I L. Don’t use them. Ever.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting by month with two dates input
On Sep 1, 2014, at 9:19 PM, Keith Medcalf kmedc...@dessus.com wrote: On Sep 1, 2014, at 6:00 PM, Igor Tandetnik i...@tandetnik.org wrote: (case when billdate != '' then billdate else bdate end) Or, more succinctly: coalesce( nullif( billdate, '' ), bdate ) (To OP: empty strings are E V I L. Don't use them. Ever.) Unless of course it is one or more spaces and not an empty string ... coalesce(nullif(rtrim(billdate), ''), bdate) an empty string is indistinguishable from a string of one or more spaces unless one makes efforts to tell the difference. (To OP: if this is the case, you get what you deserve! :D ) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Edinburgh Buses - SOLVED!!!
On Aug 27, 2014, at 7:19 PM, Errol Emden eem...@hotmail.com wrote: … Couple of minor list minutia: - When starting a new topic, create a new message, instead of replying to your previous one and merely changing its subject line. This will make it easier to keep track of new messages for these of us who use threaded email clients. - For transparency’s sake, name your source. For example, the 'Edinburgh Buses’ trivia seems to originate from SQLZoo: http://sqlzoo.net/wiki/Self_join Bonus reading materials: http://mattgemmell.com/what-have-you-tried/ http://www.catb.org/esr/faqs/smart-questions.html Enjoy your learning. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window functions?
On Aug 26, 2014, at 2:09 AM, Keith Medcalf kmedc...@dessus.com wrote: select id, category_id, name, min(price) as minprice from cat_pictures group by category_id; Done. And no need for any windowing functions … This peculiar behavior is very unique to SQLite. Most reasonable SQL engines will throw an exception when confronted with the above. SQLite calls it a feature. I personally see it as a misfeature. ( Ditto with tagging an implicit limit 1 to scalar queries. Anyway. ) On the other hand, one could look at the current ‘group by’ behavior as exhibited by SQLite as a precursor to a proper, more formalize, handling of analytic functions…. :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window functions?
On Aug 27, 2014, at 10:57 PM, Eduardo Morras emorr...@yahoo.es wrote: Sorry, don't understand why others will throw an exception in the group by, perhaps I'm misunderstanding the group by, but that should work on others engines. Because not all expressions are accounted for, i.e.: not a GROUP BY expression Cause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause. Action: Include in the GROUP BY clause all SELECT expressions that are not group function arguments.” Try it. See what happen. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window functions?
On Aug 25, 2014, at 7:12 PM, Richard Hipp d...@sqlite.org wrote: You used the word immense which I like - it is an apt description of the knowledge and effort needed to add windowing functions to SQLite (and probably any other database engine for that matter). True. But what a quantum leap that would be. Like moving from the wheelbarrow to the jet engine. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window functions?
On Aug 25, 2014, at 7:04 PM, Simon Slavin slav...@bigfraud.org wrote: Would you care to explain what advantages Window functions would give us that VIEWs and sub-SELECTs don't give us ? I'm not being contrary, I'd like to know. Analytics are to sub-selects like cruise missile are to muskets: an entirely different ballgame. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window functions?
On Aug 25, 2014, at 9:25 PM, Stephan Beal sgb...@googlemail.com wrote: For the small percentage of users who need it (or would even know how to apply it). i've been following this list since 2006 or 2007 and i recall this topic having come up only a small handful of times, which implies that only a small minority of users feels the need for it. Meh, most developers cannot put a join together, much less comprehend what they never used. But ignorance is not an excuse :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window functions?
On Aug 25, 2014, at 7:18 PM, forkandwait webb.spra...@gmail.com wrote: Compare the two SQL examples between Approach 2 and Approach 3 in the linked page: http://hashrocket.com/blog/posts/sql-window-functions Couple more: There was SQL before window functions and SQL after window functions http://tapoueh.org/blog/2013/08/20-Window-Functions NoSQL? No, SQL! – How to Calculate Running Totals http://blog.jooq.org/2014/04/29/nosql-no-sql-how-to-calculate-running-totals/ Probably the Coolest SQL Feature: Window Functions http://blog.jooq.org/2013/11/03/probably-the-coolest-sql-feature-window-functions/ etc, etc, etc… the future is bright, the future is analytic :D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window functions?
On Aug 25, 2014, at 10:20 PM, forkandwait webb.spra...@gmail.com wrote: I would be interested to hear what parts of the full window function spec are not covered by the example, if someone can describe it easily. Well, the exact implementation varies from implementation to implementation, e.g. Oracle sports more than 32 of them [1][2]. But a very good start would be to turn the existing 6 aggregate functions [3] into analytics. [1] http://www.oracle-base.com/articles/misc/analytic-functions.php [2] http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm#SQLRF06174 [3] http://www.sqlite.org/lang_aggfunc.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing ~1000 CSV files faster
On Aug 19, 2014, at 11:11 PM, joe.fis...@tanguaylab.com joe.fis...@tanguaylab.com wrote: Is there something better I can do to improve this process? PRAGMA journal_mode = off; http://www.sqlite.org/pragma.html#pragma_journal_mode Perhaps one transaction? Perhaps turn something off? It took about 1.5 hours to run. I use the temp table because every CSV files has a header with the column names. I have to drop the table each time because of the header issue. Preprocess your files by dropping the first line beforehand, e.g.: sed -i ‘1d’ That will save half of your time already. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite relative dates and join query help
On Aug 13, 2014, at 3:43 AM, Keith Medcalf kmedc...@dessus.com wrote: I don't think you want max() around collections.book_in_date. You want the max(collection_date) but the book_in_date from that row. Since the collection_date is unique, the book_in_date can only come from one record. Possibly. Surely the OP knows her data. On the other hand, this being an aggregation, every keys must be accounted for. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite relative dates and join query help
On Aug 12, 2014, at 7:38 PM, Ben sqlite_l...@menial.co.uk wrote: The result I'm after is: id, prod_code, creation_date, last_book_in_date, last_collection_date Where the final two columns are from the collection which is the farthest in the future, but still within the 50-day period from creation. Perhaps something along these lines: selectitems.id, items.prod_code, items.creation_date, max( collections.book_in_date ) as last_book_in_date, max( collection_date ) as last_collection_date from items join collections oncollections.id = items.collection_id where collections.book_in_date between items.creation_date and items.creation_date + 50 and collections.collection_date between items.creation_date and items.creation_date + 50 group by items.id, items.prod_code, items.creation_date N.B. There is no ‘date’ type in SQLite. Will assume something else, say, a Julian number for ease of date manipulation. Should I instead be processing this in the application rather than database? No. A database is the perfect place to process data. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UEFA EURO 2012 Football Championship problem
On Aug 11, 2014, at 8:39 PM, Errol Emden eem...@hotmail.com wrote: 1. Matches in which neither team scored is not being displayed. Because you have an inner join to goal. If there no goal, then no entry will match. 2. Scores for the same matchid where both teams scored are appearing on separate lines instead of in a single line. Because you have a join to goal, which has a granularity of one entry per goal, per match. So, if multiple goal, multiple entries. You try to compensate by grouping per match and team, so you end up with two entries if both team have scored. What do I need to do to correct these issues? Get you granularity in order. selectgame.mdate, game.matchid, game.team1, ( select count( * ) from goal where goal.matchid = game.id and goal.teamid = game.team1 ) as score1, game.team2, ( select count( * ) from goal where goal.matchid = game.id and goal.teamid = game.team2 ) as score2 from game order by game.mdate, game.matchid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong?
On Aug 8, 2014, at 8:35 PM, Errol Emden eem...@hotmail.com wrote:
I am to list the film title and the leading actor for all of the films
'Julie Andrews' played in.
And another one, for diversity’s sake…
Assuming a slightly different data model:
with
AndrewsMovie
as
(
select movie_cast.movie_id
frommovie_cast
joinperson
on person.id = movie_cast.person_id
where person.name = 'Andrews, Julie (I)'
)
selectmovie.year,
movie.title,
person.name,
movie_cast.attribute
from movie_cast
join movie
onmovie.id = movie_cast.movie_id
join person
onperson.id = movie_cast.person_id
where movie_cast.attribute like '%1'
and exists
(
select 1
fromAndrewsMovie
where AndrewsMovie.movie_id = movie_cast.movie_id
)
order by movie.year,
movie.title;
Sample for 2010:
2010|20 to 1 (2005) {Our All Time Favourite Films (#9.11)}|Newton,
Bert|[Himself - Host] 1
2010|Breakfast (2000) {(2010-05-24)}|Stayt, Charlie|[Himself - Presenter] 1
2010|The Daily Show (1996) {Julie Andrews (#15.12)}|Stewart, Jon (I)|[Himself
- Host] 1
2010|The Late Late Show with Craig Ferguson (2005) {(#6.80)}|Ferguson, Craig
(I)|[Himself - Host] 1
2010|The Oprah Winfrey Show (1986) {(2010-10-28)}|Winfrey, Oprah|[Herself -
Host] 1
2010|Despicable Me (2010)|Carell, Steve|(voice) [Gru] 1
2010|Shrek Forever After (2010)|Myers, Mike (I)|(voice) [Shrek] 1
2010|Tooth Fairy (2010/I)|Johnson, Dwayne (I)|[Derek] 1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable values in Views
On Jul 28, 2014, at 3:53 PM, Jonathan Moules jonathanmou...@warwickshire.gov.uk wrote: Fair question, but I'm doing log analysis. Each set of tables will be for a given server that's being analysed. Alternatively, you could setup your tables as a set of distinct databases, one per server, and attach/detach the relevant one while keeping the same set of table/view names. Just a thought. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WITH syntax error
On Jul 12, 2014, at 7:29 PM, Staffan Tylen staffan.ty...@gmail.com wrote: The following statement is flagged as invalid, so what's the correct way of coding it? Flagged by whom? Invalid how? Either way, from SQLIte point of view, looks legit the way it is. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sequential numbers
On Jun 24, 2014, at 10:47 PM, Dave Wellman dwell...@ward-analytics.com wrote: I need the values to be sequential. Well… if your data set is as small as you mentioned (20 records or less)… you could roll your own numbering schema with the simple expedient of attaching a trigger to your tables to auto -number them with 'select count( * ) + 1 from table’ or something. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Understanding Sqlite
On Jun 4, 2014, at 8:35 AM, dd durga.d...@gmail.com wrote: What is/are the best practice(s) to become master in sqlite in short period of time for new developers (i mean, new to sqlite not for programming)? Master? In short time? Nope. If you don’t want to be a total phony, you will have to do it the hard, old fashion way: by learning and practicing. Continuously. Repetitively. There are no shortcuts. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE INSERT vs. REPLACE
On May 27, 2014, at 8:11 PM, Drago, William @ MWG - NARDAEAST william.dr...@l-3com.com wrote: Is there any difference between using REPLACE as opposed to deleting records and then inserting new ones to take their place? Same difference. For example: create table foo ( id integer primary key not null, key text not null, constraint foo_uk unique( key ) ); sqlite insert or replace into foo( key ) values( 'a' ); sqlite select * from foo; 1|a sqlite insert or replace into foo( key ) values( 'a' ); sqlite select * from foo; 2|a sqlite insert or replace into foo( key ) values( 'a' ); sqlite select * from foo; 3|a Note how the primary key, id, had changed over time, from 1 to 3. For all practical purposes, REPLACE is useless, if not dangerous even. What would really be useful would be a MERGE operation instead: http://en.wikipedia.org/wiki/Merge_%28SQL%29 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
On May 21, 2014, at 6:00 PM, Humblebee fantasia.d...@gmail.com wrote: only problem is that in this situation, the tables have already been defined and made by someone else so I cannot change it. I'm a bit stuck with the way it is. Nah… it’s software… you can always change it… in fact, better fix it now… as there is really no reasonable way forward with your current setup… ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
On May 21, 2014, at 6:00 PM, Humblebee fantasia.d...@gmail.com wrote: At least this is what I'm thinking from my very very limited understanding of SQL and with the way that I'm trying to do this. SMITH: Doctor, it hurts when I do _this_. DALE: Don’t _do_ that. with DataSet as ( select 'a,b,c,' as string ), CSV( string, value, position ) as ( select substr( string, instr( string, ',' ) + 1 ) as string, substr( string, 1, instr( string, ',' ) - 1 ) as value, 1 as position fromDataSet union all select substr( string, instr( string, ',' ) + 1 ) as string, substr( string, 1, instr( string, ',' ) - 1 ) as value, position + 1 as position fromCSV where length( string ) 0 ) select value, position fromCSV; value|position a|1 b|2 c|3 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to write this commands?
On May 16, 2014, at 6:25 PM, Igor Tandetnik i...@tandetnik.org wrote: So with SQLite, the query without max() would work, and produce expected results. With another database engine that enforces SQL rules more strictly, the query without max() would fail with a syntax error. I figured I'd do it by the book and wrap the field into an aggregate function. I could have used max(), or min(), or avg() - it doesn't matter which, they all work the same when there's only one row to aggregate. Good man. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign Key errors
On May 6, 2014, at 11:17 PM, Richard Hipp d...@sqlite.org wrote: It is theoretically possible to keep track of which constraints are failing so that the particular constraint can be identified in the error message. But that woudl require more memory and CPU cycles. That would be resources well spent. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 5, 2014, at 1:14 AM, James K. Lowden jklow...@schemamania.org wrote: To amplify the point, the issue isn't pure fussiness or obligation to adhere to standards. A permissive parser invites error. Exactly. It's not hard to imagine select 1 where 1 - 1; was intended as select 1 where 1 = 1; which, in the midst of a large query producing expected results, might easily be overlooked. You must have been peering over my shoulder! This is exactly how this came about: a bloody typo! :D I doubt Petite is confused by boolean evaluation, but rather is dismayed by its appearance in this context. Indeed. I would have expected a syntax error along the lines of 'invalid relational operator’ or such. And that’s that. SQL is not C. To the extent the SQL supplied by SQLite is nonstandard, it might as well be another language entirely. The better one knows SQL, the harder a nonconformant implementation is to use. Not long ago I was helping someone with a query in MS Access. Easy, just use a correlated subquery in an update statement. Hard, if it chokes the parser. Perhaps you know the joke with the punchline, Assume a can opener. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 5, 2014, at 7:15 PM, Stephan Beal sgb...@googlemail.com wrote: Why expect an error? It's abstractly the same as saying WHERE 'a' = 'b’, I mean ‘where 1’, or ‘where ‘1 - 1’, or ‘where null’, or ‘where 0 / 0’, or any of this nonsense. There is nothing to compare. It’s nonsensical. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 5, 2014, at 7:36 PM, Stephan Beal sgb...@googlemail.com wrote: Oh, but there is: 1-1 is an expression, the result of which is integer 0 It’s nonsensical as a where clause expression. (as opposed to string '0'), which, in all programming environments except, IIRC, Xenix, is boolean false. This is SQL, not Sparta. In fact, the sqlite shell makes a handy ad hoc calculator for exactly that purpose: Non sequitur. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 5, 2014, at 8:00 PM, Richard Hipp d...@sqlite.org wrote: Petite's complaint is that in most other SQL database engines, 0 is not false. If you try to use 0 where a boolean is needed, you get a syntax error. In strict SQL, boolean and integer are incompatible types that cannot be interchanged. While I do agree with the above, this not about types per se. And it’s not about the meaning of life, nor metaphysics. I simply wish the *SQL* parser was more strict about what it accepts. My point is that blindly accepting a bare expression as a comparison fails far short of both ‘simplicity’ and ‘sanity'. That’s all. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 5, 2014, at 8:21 PM, RSmith rsm...@rsweb.co.za wrote: the idea that introducing more complication will make erros/bugs less is just false. Straw man argument, unrelated to the topic at hand. This is solely about the SQL parser failing short of reporting syntax errors for nonsensical queries. select 1 where null; select 1 where is null; Error: near is: syntax error select 1 where not null; select 1 where is not null; Error: near is: syntax error select 1 where not( not null); elect 1 where not( is null ); Error: near is: syntax error select 1 where not( 1 ); select 1 where ‘When I use a word, Humpty Dumpty said, in rather a scornful tone, it means just what I choose it to mean — neither more nor less.’; select 1 where not 'The question is, said Alice, whether you can make words mean so many different things.’; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 5, 2014, at 9:15 PM, RSmith rsm...@rsweb.co.za wrote: Je suis desole mon ami… Moi aussi :P I have no quarrel with you, good Sir Knight, but I must cross this bridge: select 1 where 1 is 1; select 1 where 1 is not 1; select 1 where 1 is ( 1 = 1 ); select 1 in ( null ); — oh… select 1 in ( not null ); — really? Anyway… if, as Stephan Beal mentioned earlier on, one looks at SQLite’s SQL parser as some sort of glorified calculator, then, yes, it all makes perfect sense in some kind of wonderful way... :) http://www.sqlite.org/src/finfo?name=src/parse.y http://www.sqlite.org/src/artifact/22d6a074e5f5a7258947a1dc55a9bf946b765dd0 N.B. One more, just for fun: sqlite select 1 in ( null is null ); 1 sqlite select 1 in ( null is not null ); 0 m'kay... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 6, 2014, at 12:15 AM, Jay Kreibich j...@kreibi.ch wrote: Cross what bridge? http://www.youtube.com/watch?v=zKhEw7nD9C4 You seem to be trying to use common sense and semantic meaning to make an argument. To quote an old CS prof, “If you argue in English**, you’re wrong.” Math and formal specifications are really the only thing here, and these do exactly what one would expect. ( … must… refrain… from… argh…. ) … never mind… all good and peaceful in the beautiful kingdom... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite for write only workload, splitting databases into smaller files, but need to query it as a whole
On May 3, 2014, at 2:59 PM, Hayden Livingston halivings...@gmail.com wrote: Thoughts? Take a look at ‘ATTACH’, it might help: http://www.sqlite.org/lang_attach.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite for write only workload, splitting databases into smaller files, but need to query it as a whole
On May 3, 2014, at 3:40 PM, Hayden Livingston halivings...@gmail.com wrote: This looks promising. I sooo wish it didn't have a limit to number of databases. 10 by default if I recall properly. Can be perhaps be increased to 62 at most: http://www.sqlite.org/limits.html But I think I could reasonably do something like coalesce the databases into a new database once every 2 hours. I also need to generate some code to figure out how to address the tables which I guess means I'll have to do an N way JOIN? The table names stay the same. Each attached database can have a unique name. So, for example: attach … as attached01; attach … as attached02; ... with DataSet as ( select count( * ) as count from attached01.table union all select count( * ) as count from attached01.table union all ... ) select sum( count ) as sum from DataSet; Also: http://www.sqlite.org/pragma.html#pragma_database_list ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] decomposing a path into its components?
Given a path, say: /subversion/bindings/swig/java/org/tigris/subversion/client/ One would like to decompose it into all its components, say: /subversion/ /subversion/bindings/ /subversion/bindings/swig/ /subversion/bindings/swig/java/ /subversion/bindings/swig/java/org/ /subversion/bindings/swig/java/org/tigris/ /subversion/bindings/swig/java/org/tigris/subversion/ /subversion/bindings/swig/java/org/tigris/subversion/client/ Let further assume one would like to use only SQLite's build-in mechanism to achieve this. Any clever way to achieve this? FWIW, here is a rather weak attempt, using recursive CTE: with DataSet as ( select '/subversion/bindings/swig/java/org/tigris/subversion/client/' as path ), Component( path, leftover, component, position ) as ( select path, substr( path, instr( substr( path, 2 ), '/' ) + 1 ) as leftover, substr( path, 2, instr( substr( path, 2 ), '/' ) - 1 ) as component, 1 as position fromDataSet union all select Component.path as path, substr( Component.leftover, instr( substr( Component.leftover, 2 ), '/' ) + 1 ) as leftover, substr( Component.leftover, 2, instr( substr( Component.leftover, 2 ), '/' ) - 1 ) as component, Component.position + 1 as position fromComponent where Component.leftover != '/' ) selectpath, component, position, ( select'/' || group_concat( self.component, '/' ) || '/' from Component self where self.path = Component.path and self.position = Component.position group by self.path ) as component_path from Component order by path, position; While this work for one path, it doesn’t quite scale to multiple of them as the CTE gets re-executed over and over. One could cache the CTE in a temp table, and decompose the query into separated steps, but that would be rather inconvenient altogether. Thoughts? Suggestions? Alternatives? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] decomposing a path into its components?
On May 3, 2014, at 5:39 PM, Simon Slavin slav...@bigfraud.org wrote: On 3 May 2014, at 3:47pm, Petite Abeille petite.abei...@gmail.com wrote: Let further assume one would like to use only SQLite's build-in mechanism There are two kinds of programmers … Indeed: drunk and not yet drunk :D Turns out that this group_concat scalar was really not needed at all. D'oh. So, all-in-one now: with DataSet as ( select '/subversion/bindings/swig/java/org/tigris/subversion/client/' as path ), Component( path, leftover, component, component_path, position ) as ( select path, substr( path, instr( substr( path, 2 ), '/' ) + 1 ) as leftover, substr( path, 2, instr( substr( path, 2 ), '/' ) - 1 ) as component, '/' || substr( path, 2, instr( substr( path, 2 ), '/' ) - 1 ) as component_path, 1 as position fromDataSet union all select Component.path as path, substr( Component.leftover, instr( substr( Component.leftover, 2 ), '/' ) + 1 ) as leftover, substr( Component.leftover, 2, instr( substr( Component.leftover, 2 ), '/' ) - 1 ) as component, Component.component_path || '/' || substr( Component.leftover, 2, instr( substr( Component.leftover, 2 ), '/' ) - 1 ) as component_path, Component.position + 1 as position fromComponent where Component.leftover != '/' ) selectposition, component, component_path from Component order by path, position; position|component|component_path 1|subversion|/subversion 2|bindings|/subversion/bindings 3|swig|/subversion/bindings/swig 4|java|/subversion/bindings/swig/java 5|org|/subversion/bindings/swig/java/org 6|tigris|/subversion/bindings/swig/java/org/tigris 7|subversion|/subversion/bindings/swig/java/org/tigris/subversion 8|client|/subversion/bindings/swig/java/org/tigris/subversion/client ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] select 1 where 1 - 1;
Quick, without trying it out, what would you expect the following statement to return: select 1 where 1 - 1; (a) one row (b) no row (c) syntax error For extra entertainment, try some variations: select 1 where 1; select 1 where 0; etc... Bonus points for a rationalization of any of the behaviors you see or don’t see. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 2, 2014, at 8:54 PM, Richard Hipp d...@sqlite.org wrote: I'm guessing that Mr. Abeille is upset that SQLite … … doesn’t even bother with SQL syntax and will happily accept any old junk as a sorry excuse for a query. select 1 where null; select 1 where not null; When SQLite 4 sees the light of the day, I wish for a strict SQL parser and proper error messages. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 2, 2014, at 9:24 PM, Cory Nelson phro...@gmail.com wrote: quirks A peculiar behavioral habit. Idiosyncrasy, peculiarity, oddity, eccentricity, foible, whim, vagary, caprice. Indeed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in division?
On Apr 30, 2014, at 2:22 PM, John McKown john.archie.mck...@gmail.com wrote: PostgreSQL likewise returns 0 for 2/4 and .5 for 2/4.0 . This is likely a part of the SQL standard. Just to be contrarian, Oracle doesn’t and returns 0.5. Ah! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in division?
On Apr 30, 2014, at 8:50 PM, Jay Kreibich j...@kreibi.ch wrote: Given Oracle’s legacy, it might be that “2” defaults to a “numeric” type, rather than an integer. Indeed, there are no ‘integer’ type per se in Oracle. At least not at the SQL level. But more to the point, I don’t thing the various ANSI standards have anything normative to say about what the result of a division should be, merely that there is a division operator. So it’s rather a free for all. Sigh. FWIW, I personally find Oracle’s behavior more intuitive in that respect. But then again, nothing really wrong with how SQLite handles it. In other news: http://stilldrinking.org/programming-sucks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT alias name limitation
On Apr 28, 2014, at 9:27 PM, Staffan Tylen staffan.ty...@gmail.com wrote: (Thinking about it maybe WITH could be used, Yes, it’s a typical use case for WITH. but that doesn't answer the first question.) One cannot refer to an identifier in the same section it was declared in, and that’s that. Therefore ‘WITH’ and subqueries. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most efficient storage for arrays
On Apr 22, 2014, at 2:24 PM, Neville Dastur nevillebdas...@gmail.com wrote: So wondering is anyone that has done this sort of thing and worked out the best way? Yes. Normalize your data. And that’s that: http://www.schemamania.org/sql/#lists Quoting a few words: Questions are frequently asked about table designs that are hopelessly wrong. The solution to the question is not to write the query, but to re-write the table, after which the query will practically write itself. Perhaps the most egregious example is a column whose value is a list or, in SQL terms, a repeating group. The elements in the list are perhaps comma-separated, and some poor schlep has the task of selecting or joining on the the nth element in the list.” Don’t be *that* schlep. But, if you like pain and suffering, medieval style, I got a hack for you involving virtual tables, full text search, contentless tables, and shadows even! It goes a bit like this: sqlite .head on sqlite select * from json_text where array1 match ''; array1|array2|tags ,|,,|searchword1,searchword2,searchword3 So, what’s json_text? A virtual, contentless, FTS4 table: create virtual table json_text using fts4 ( content='json', array1 text, array2 text, tags text ); Where does it get its content? From a regular table: create table json ( _idtext, name text, array1 text, array2 text, tags text ); insert intojson ( _id, name, array1, array2, tags ) values ( 'xx', 'Description', ',', ',,', 'searchword1,searchword2,searchword3' ); insert intojson ( _id, name, array1, array2, tags ) values ( 'yyy', 'Description', ',', ',,', 'tag1,tag2,tag3' ); See the doc for details about the full setup: https://www.sqlite.org/fts3.html#section_6_2_1 What about the shadows you may ask? Here you go: create virtual table if not exists json_term using fts4aux( json_text ); sqlite select * from json_term order by 1 limit 10; term|col|documents|occurrences |*|1|1 |0|1|1 |*|1|1 |0|1|1 |*|1|1 |1|1|1 |*|1|1 |1|1|1 |*|1|1 |1|1|1 All the text fields, nicely tokenized, and for your to use and abuse. More info: https://www.sqlite.org/fts3.html#fts4aux ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CTEs and unions
On Apr 23, 2014, at 12:16 AM, Dominique Devienne ddevie...@gmail.com wrote: is there no way to reuse a CTE several times? Hrm… of course you can… that’s the entire point of *Common* Table Expression: with DataSet as ( select 1 as value ) select * fromDataSet union all select * fromDataSet; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
On Apr 7, 2014, at 3:28 PM, Dominique Devienne ddevie...@gmail.com wrote: For those interested, here's an article along the same lines that better demonstrate what I mean by the above: http://technology.amis.nl/2013/06/26/oracle-database-12c-joining-and-outer-joining-with-collections/ Aha! That’s what that mysterious CROSS/OUTER APPLY is all about. Thanks for the link :) The new Oracle 12c join syntax is basically just syntax sugar hiding the TABLE operator and its implicit COLUMN_VALUE column. Well, table( … ) can apply to records (e.g. pipelined function) with fully named attributes. So, really, we are saying this is rather high cholesterol for outer/full/cross join table( pipeline( parameter, ... ) )? Is it really worthwhile a full blown new keyword/concept? Doubtful. Anyway… back to SQLite :) As James K. Lowden kindly, and repetitively, pointed out: http://www.schemamania.org/sql/#lists Perhaps worthwhile quoting a few words: Questions are frequently asked about table designs that are hopelessly wrong. The solution to the question is not to write the query, but to re-write the table, after which the query will practically write itself. Perhaps the most egregious example is a column whose value is a list or, in SQL terms, a repeating group. The elements in the list are perhaps comma-separated, and some poor schlep has the task of selecting or joining on the the nth element in the list.” Don’t be *that* schlep. N.B. There is no glory in beautifully solving a hopelessly wrong problem. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up a bulk import
On Apr 7, 2014, at 8:33 PM, J Trahair j.trah...@foreversoftware.co.uk wrote: Any suggestions welcome. Thank you. One word: transaction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
On Apr 8, 2014, at 1:02 AM, David Simmons dsimmons...@earthlink.net wrote: Why are these people allowed to use this discussion board? Hmmm? What we've got here is failure to communicate perhaps. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to load a blob from the shell?
On Apr 8, 2014, at 1:46 AM, Andreas Kupries andre...@activestate.com wrote: Most generally, a website to show off any kind of contribution to sqlite, be it custom function, virtual table, virtual filesystem, schemata, other extensions, … ? A bit obsolete, but: http://www.sqlite.org/contrib Perhaps github could be of interest as well: https://github.com/search?q=sqliteref=cmdform For example: https://github.com/sqlcipher/sqlcipher ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to load a blob from the shell?
On Apr 6, 2014, at 10:01 PM, to...@acm.org wrote: I haven't figured out how to load a blob (e.g., image) from the shell. I would think there should be something like this but can't find anything: You have to roll your own… e.g. blob literal + hexdump: http://stackoverflow.com/questions/12865697/sqlite-insert-data-into-blob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to query key-value-pairs similar to PostgreSQL HSTORE type?
On Mar 18, 2014, at 2:46 AM, Stefan Keller sfkel...@gmail.com wrote: Any suggestions on how to query this most efficiently (like [select value from some_key])? As mentioned, turn this construct into a regular relational table structure. If, for some reasons, you cannot even accomplish first normal form [1], i.e. one and only one value per column, well, then, maybe, a relational database is not the right tool for the task at hand. [1] http://en.wikipedia.org/wiki/First_normal_form ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to query key-value-pairs similar to PostgreSQL HSTORE type?
On Mar 18, 2014, at 8:56 PM, Stefan Keller sfkel...@gmail.com wrote: And, actually, as you may have realized, PostgreSQL proved that even (post-)relational databases can handle KVP efficiently. Just because one can, doesn't mean one should. But, as always, to each their own. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] basic Window function
On Mar 13, 2014, at 4:17 PM, big stone stonebi...@gmail.com wrote: Is there anyone else, (besides little bee), that would like this request? Oh! Oh pick me! Pick me! Me! Me! M!” — Donkey, Shrek ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] About Syntax Diagrams
On Mar 11, 2014, at 3:51 AM, yulea...@163.com wrote: Now I have a non-technical issues. The syntax diagrams for SQLite on your SQLite website is so beautiful, and i want to draw one for myself but I do not know what software you use to draw it. Can you tell me? and, is it the software automatically converted from EBNF? For the record, those are called syntax diagrams (or railroad diagrams) : http://en.wikipedia.org/wiki/Syntax_diagram SQLite call them bubble diagram: http://www.sqlite.org/docsrc/finfo?name=art/syntax/bubble-generator.tcl Alternatively, ditaa [1] may be of interest: http://ditaa.sourceforge.net Here are some example generated with ditaa of the Oracle ‘alter user’ statement: http://picpaste.com/pics/alter_user_shadow.1394569031.png http://picpaste.com/pics/alter_user_noshadow.1394568104.png And here is one for ‘query block’: http://picpaste.com/pics/query_block.1394568877.png For reference, here are the original Oracle diagrams: http://docs.oracle.com/cd/E11882_01/server.112/e26088/img/alter_user.gif http://docs.oracle.com/cd/E11882_01/server.112/e26088/img/query_block.gif ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] curious idiom of the day...
with DataSet as ( select null as value union all select 'YES' as value union all select 'NO' as value union all select 'PERHAPS' as value ) select * fromDataSet where not exists ( select 1 where value = 'NO' ) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite_compileoption_get + cte
Just because we can: with Option( name, position ) as ( select sqlite_compileoption_get( 1 ) as name, 1 as position union all select sqlite_compileoption_get( position + 1 ) as name, position + 1 as position fromOption where sqlite_compileoption_get( position + 1 ) is not null ) selectname from Option order by name ENABLE_FTS3_PARENTHESIS ENABLE_RTREE ENABLE_STAT4 SYSTEM_MALLOC THREADSAFE=1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite_compileoption_get + cte
On Mar 1, 2014, at 7:46 PM, Bogdan Ureche bogdan...@gmail.com wrote: You are missing one value. To get all the values, start from 0: At least someone is paying attention! Thanks :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite_compileoption_get + cte
On Mar 1, 2014, at 6:30 PM, mm.w 0xcafef...@gmail.com wrote: ? PRAGMA compile_options; Yes, sure. But much snazzier to use a CTE, no? :D ( One very unfortunate aspect of pragmas is that one cannot query them with regular SQL… sigh…) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] basic Window function
On Mar 1, 2014, at 7:39 PM, big stone stonebi...@gmail.com wrote: Would it be possible to get a small basic subset of the sql windowing function for Sqlite 3.8.5 ? Yes! Pretty please :) Supporting windowing functions (aka analytics) would be a major breakthrough. http://www.orafaq.com/node/55 Here is a pretty nifty algorithm using analytics, the Tabibitosan method”: http://boneist-oracle.livejournal.com/7389.html http://forums.oracle.com/forums/thread.jspa?messageID=3989678 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite_compileoption_get + cte
On Mar 1, 2014, at 9:34 PM, Stephan Beal sgb...@googlemail.com wrote: note the duplicate first entry. Make sure to start everything at zero: select sqlite_compileoption_get( 0 ) as name, 0 as position ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] HTML Tokenizer
On Feb 13, 2014, at 8:48 PM, Wang, Baoping bw...@kelleydrye.com wrote: New to Sqlite, anybody knows is there a HTML tokenizer for full text search, No. Or do I need to implement my own? If you feel the urge. Otherwise, try lynx -dump. For example: curl -s http://www.sqlite.org | lynx -nolist -stdin -dump ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] HTML Tokenizer
On Feb 13, 2014, at 9:08 PM, Petite Abeille petite.abei...@gmail.com wrote: curl -s http://www.sqlite.org | lynx -nolist -stdin -dump While we are at it, www.sqlite.org exhibits many validation errors: http://validator.w3.org/check?uri=http%3A%2F%2Fwww.sqlite.org%2Fcharset=%28detect+automatically%29doctype=Inlinegroup=0user-agent=W3C_Validator%2F1.3+http%3A%2F%2Fvalidator.w3.org%2Fservices#result ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] HTML Tokenizer
On Feb 13, 2014, at 9:52 PM, Jan Nijtmans jan.nijtm...@gmail.com wrote: But if you put the validator in HTML5 mode, there are many less errors: Possibly. But it says 'HTML 4.01 Strict' on the tin: !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.01//EN http://www.w3.org/TR/html4/strict.dtd” Either way, a bunch of errors. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] on insert new row, increment date with a month
On Feb 11, 2014, at 7:07 PM, Gert Van Assche ger...@gmail.com wrote: Does anyone know if it is possible for a date field to be automatically incremented with a month when a new record is created? If you are looking for something wacky, triggers are where to look: http://www.sqlite.org/lang_createtrigger.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell
On Feb 10, 2014, at 5:19 PM, Gabor Grothendieck ggrothendi...@gmail.com wrote: The other features that would make teaching a bit easier would be to support left join explicitly and support the rfc4180 standard for csv files. Hmmm? Left join: http://www.sqlite.org/syntaxdiagrams.html#join-operator RFC-4180 compliant .import: http://sqlite.org/releaselog/3_8_0.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell
On Feb 10, 2014, at 4:23 PM, Richard Hipp d...@sqlite.org wrote: Proposed Change To Address The Problem: What’s the problem exactly? CS101 students distress? That’s way beyond SQLite reach. My 2¢: don’t create a default persistent database. This is not helpful to anyone. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell
On Feb 10, 2014, at 7:39 PM, Gabor Grothendieck ggrothendi...@gmail.com wrote: That should have read right join. My personal opinion? Anyone even considering using a right outer join should be cursed into repeating their first day at high school. For ever. Groundhog Day, The High School Years. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3
On Feb 10, 2014, at 8:05 PM, Bert Huijben rhuij...@apache.org wrote: As part of the Subversion 1.8.6 release we tried introducing some data in the 'sqlitstat_stat1' table using the recommended approach for Sqlite 3.8.0+ compatibility to tell sqlite about our 'bad indexes’: ( Not directly related to your question, but… why, oh why is svn log --use-merge-history so excruciatingly slow? ) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] recursive common table expression, an example
Now that 3.8.3 is officially out, we can all play with these nice little common table expressions! Yeah! So, while solving sudoku puzzles is all fine and dandy, the bread and butter of recursive queries is more along the lines of plain, old hierarchies. So, let create one: select 'A' as node, null as parent union all select 'B' as node, 'A' as parent union all select 'C' as node, 'B' as parent union all select 'D' as node, 'C' as parent A simple hierarchy, each node with one parent, the root node without one: A → B → C → D. Nice and easy. Let recurse! with DataSet as ( select 'A' as node, null as parent union all select 'B' as node, 'A' as parent union all select 'C' as node, 'B' as parent union all select 'D' as node, 'C' as parent ), Hierarchy( node, parent, level, path ) as ( select DataSet.node, DataSet.parent, 1 as level, ' → ' || DataSet.node as path from DataSet whereDataSet.parent is null union all select DataSet.node, DataSet.parent, Hierarchy.level + 1 as level, Hierarchy.path || ' → ' || DataSet.node as path fromHierarchy joinDataSet on DataSet.parent = Hierarchy.node ) select* from Hierarchy order by path; node|parent|level|path A||1| → A B|A|2| → A → B C|B|3| → A → B → C D|C|4| → A → B → C → D Beautiful. For each node, we get its level and full path, recursively. And that’s all there is to it. Very nice. Recursive or not, common table expressions are your friend. Use them ☺ N.B. One word of caution about circular recursion though… as it stands, SQLite doesn’t have any build-in mechanism to detect circularity… and will happily get into a funk and run forever if given the opportunity… so… watch out… Little demonstration: with DataSet as ( select 'A' as node, 'A' as parent ), Hierarchy( node, parent, level, path ) as ( select DataSet.node, DataSet.parent, 1 as level, ' → ' || DataSet.node as path from DataSet union select DataSet.node, DataSet.parent, Hierarchy.level + 1 as level, Hierarchy.path || ' → ' || DataSet.node as path fromHierarchy joinDataSet on DataSet.parent = Hierarchy.node ) select* from Hierarchy order by path; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On Jan 25, 2014, at 6:25 PM, Petite Abeille petite.abei...@gmail.com wrote: On Jan 25, 2014, at 6:05 AM, Keith Medcalf kmedc...@dessus.com wrote: Read the docs. It explains how recursive CTEs are computed and how UNION ALL vs UNION work in CTEs. Hmmm… perhaps… doing is believing… so will experiment once the next SQLite release is officially out. So… for the record… as it stands in 3.8.3, SQLite will happily recurse forever. ___ 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
On Feb 3, 2014, at 10:11 PM, big stone stonebi...@gmail.com wrote: bag colors bag1 blue - red - yellow bag2 green - yellow Does that really require a recursive query? Wouldn’t a simple group by + group_concat do as well? with DataSet as ( select 'bag1' as bag, 'blue' as color union all select 'bag1' as bag, 'red' as color union all select 'bag1' as bag, 'yellow' as color union all select 'bag2' as bag, 'green' as color union all select 'bag2' as bag, 'yellow' as color ) selectDataSet.bag as bag, group_concat( DataSet.color, ' - ' ) as colors from DataSet group by DataSet.bag order by DataSet.bag; P.S. I’m starting to develop a strong aversion to that ‘values( … )’ syntax… sigh... ___ 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
On Feb 3, 2014, at 11:05 PM, big stone stonebi...@gmail.com wrote: group_concat is indeed super nice ! I didn't notice that little jewel of SQLite, thank you. You are welcome. But *do* read the very fine prints associated with that aggregate function: http://www.sqlite.org/lang_aggfunc.html “… The order of the concatenated elements is arbitrary… “ … which, well, renders it pretty much useless for anything but display purpose… and even then… sigh... Is there a standardized SQL normalization for that ? No. Not that I know of. (I see that oracle has a LISTAGG instead) Yes, LISTAGG [1] is much more usable in that respect as it sports a WITHIN GROUP ( ORDER BY … ) clause which makes is more predictable. [1] http://docs.oracle.com/cd/E11882_01/server.112/e17118/functions089.htm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Troubleshooting nested query performance issue
On Feb 3, 2014, at 11:30 PM, Joseph L. Casale jcas...@activenetwerx.com wrote: I have a query Not directly related to your question, but… why oh why do people molest their queries by gratuitously and pointlessly aliasing perfectly good table name to meaningless random one letter codes?!? Masochism? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fine tuning table indexes
On Feb 2, 2014, at 5:55 PM, Keith Medcalf kmedc...@dessus.com wrote: Nevertheless, each traversal operation is only using one index at a time. One word: bitmap. As in bitmap index: http://www.oracle.com/technetwork/articles/sharma-indexes-093638.html http://en.wikipedia.org/wiki/Bitmap_index ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] too many SQL variables
On Jan 30, 2014, at 7:01 PM, E. Timothy Uy t...@loqu8.com wrote: Just for my edification, what is the limit on the number of SQL parameters? Today I hit too may SQL variables with about 1400… Just for our edification, which kind of statement was that? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragmas in subselects?
On Jan 29, 2014, at 9:58 PM, big stone stonebi...@gmail.com wrote: (killing two birds with one stone) No. One bird only. Enhancing ‘alter table’ is another kettle of fish altogether. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragmas in subselects?
On Jan 26, 2014, at 5:09 PM, Stephan Beal sgb...@googlemail.com wrote: Is this possible? Sadly, no. Much of a PITA. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragmas in subselects?
On Jan 26, 2014, at 11:19 PM, big stone stonebi...@gmail.com wrote: == Is it the reason ? Well, that pragmas are not directly queryable from SQL just add insult to injury. What SQLite would really benefit from is a proper, consistent, queryable data dictionary such as the the standard information schema: http://en.wikipedia.org/wiki/Information_schema ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On Jan 25, 2014, at 2:37 AM, James K. Lowden jklow...@schemamania.org wrote: Funny, we find ourselves on the opposite side of the compexity question this time. Ehehehe… yes… the irony is duly noted :) But, ok, then, let welcome our new VALUES overlord. May it have a long and prosperous carrier! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On Jan 25, 2014, at 6:05 AM, Keith Medcalf kmedc...@dessus.com wrote: Read the docs. It explains how recursive CTEs are computed and how UNION ALL vs UNION work in CTEs. Hmmm… perhaps… doing is believing… so will experiment once the next SQLite release is officially out. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On Jan 24, 2014, at 2:31 PM, Richard Hipp d...@sqlite.org wrote: Please see http://www.sqlite.org/draft/lang_with.html for draft documentation of the new Common Table Expression implementation for SQLite 3.8.3. Comments, criticism, and typo-corrections are appreciated. (1) What is this VALUES( … ) construct? What purpose does it serves? (2) What about cycles? How does one deal with them? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On Jan 24, 2014, at 11:32 PM, Clemens Ladisch clem...@ladisch.de wrote: It's exactly the same as SELECT …, but a little bit easier to write. (It behaves like with INSERT, but is now available in every place where a SELECT would be allowed.) Hmmm… seems rather pointless to me. select 1 as value /* from thin air */ union all select 2 as value /* from thin air */ … etc … Seems to be good enough. No point in hijacking a totally unrelated construct. I would drop such complication if I had a say. There is already a perfectly fine construct to conjure constants out of thin air: select. (2) What about cycles? How does one deal with them? With cycles, you probably want to use UNION instead of UNION ALL to stop at duplicate records. Hmmm… not quite sure how this would play out in practice… how would the recursion known when to stop? Say, given a circular hierarchy such as A- B - A…. when does the recursion stop? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dramatic performance difference between different PRAGMA synchronous settings for bulk inserts
On Jan 19, 2014, at 3:00 PM, Mario M. Westphal m...@mwlabs.de wrote: Also FTS4 is used, which also creates large tables. (Unrelated to your question, but, take a look at external content FTS4 table… they dramatically cut down the amount of duplicated data [1]) During an ingest phase, my application pumps in hundreds of thousands of records into multiple tables. For initial, bulk loading, I tend to use the following pragma combo: pragma journal_mode = off pragma locking_mode = exclusive pragma synchronous = off [1] http://www.sqlite.org/fts3.html#section_6_2_2 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On Jan 17, 2014, at 7:47 PM, big stone stonebi...@gmail.com wrote: - I just did my first recursive CTE under Ipython notebook. Finally! We can solve sudoku puzzles in SQL :P http://technology.amis.nl/2009/10/13/oracle-rdbms-11gr2-solving-a-sudoku-using-recursive-subquery-factoring/ Thanks a lot, dear SQLite team ! And enlighten sponsor. Thanks! ___ 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
On Jan 17, 2014, at 11:26 PM, big stone stonebi...@gmail.com wrote: Has anyone a clue ? No. But this is what Charlie the Unicorn has to say on the subject: Oh God you guys. This better be pretty important. Is the meadow on fire? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive query?
On Jan 12, 2014, at 11:02 AM, Darren Duncan dar...@darrenduncan.net wrote: I would expect so; you can't have WITH RECURSIVE without WITH. It’s taking shape: http://www.sqlite.org/src/timeline?r=common-table-exprnd Oh, so, exciting! :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive query?
On Jan 10, 2014, at 4:34 PM, Richard Hipp d...@sqlite.org wrote: FYI: The sponsor is now indicating that they want to go with WITH RECURSIVE. So the CONNECT BY branch has been closed and we are starting to work on a WITH RECURSIVE implementation. Much excellent. And much thanks to such rational sponsor :) So, considering that ‘with recursive’ is a superset of the plain vanilla ‘with’ clause, does it mean we can expect to see the regular ‘with’ clause in SQLite sometime in the near future as well? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Recursive query?
Hello, Couldn’t help but notice a brand new branch in SQLite’s repository, the one labeled Start a new experimental branch for support of Oracle-style CONNECT BY syntax.”. http://www.sqlite.org/src/info/4365ddd62d Two reactions: (1) Recursive queries! Yes! Hurray! :D (2) CONNECT BY syntax?!? What the…!!! To paraphrase Ridley Scott: “On the interweb, no one can hear you scream.” If you are considering a syntax for recursion... please, please, pretty please, use Common Table Expression. Even Oracle does use it now. Common table expressions are supported by DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), HyperSQL and H2 (experimental).”. http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL Let have SQLite added to that list :)) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
