Re: [sqlite] Whish List for 2015
On Sun, Dec 21, 2014 at 4:47 AM, big stone stonebi...@gmail.com wrote: Hi all, To prepare for end of 2014 greetings moment, here is my whish list for 2015: - a minimal subset of analytic functions [1], that I hope may help end-user/students popularity [2] - better information on what is coming ahead, for example: . I see the 'sessions' tree moving along main tree since a few months, what is it about ? . sqlite4 is dead because sqlite3 did progress quicker than expected ? . [1] http://www.postgresql.org/docs/9.4/static/tutorial-window.html [2] http://db-engines.com/en/ranking_trend In R the sqldf R package allows one to perform SQL operations on R data.frames by transparently uploading them to an SQL database which it creates on the fly performing the specified SQL operation, reeturning the result and destorying the database it created. it uses SQLite by default but can also use H2, MySQL and PostgreSQL. Its my sense that users of sqldf use SQLite because its the default and is automatically installed when they install sqldf but if they move to one of the other databases the first choice is typically H2 because they want true date and time types which H2 supports and sqlite does not. Also, H2 is almost as easy to install as SQLite - the R drivers for both packages include the entire database and the only extra thing you need for H2 is java which many people have already anyways.Thus if SQLite had true date and time types I think this would address a significant portion of those sqldf users who find SQLite is not enough for their needs. If they don't go to H2 then they will likely go to PostgreSQL to get window/analytic functions and the main thing that would put them off here is that its not as easy to install PostgreSQL as SQLite or H2. Thus if SQLite were to support window/analytic functions it would address the main other group of those who switch from SQLite when using sqldf. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What if OpenDocument were built atop SQLite?
On Wed, Sep 10, 2014 at 8:36 AM, John McKown
john.archie.mck...@gmail.com wrote:
Well, I did a git clone to fetch the LibreOffice source. It appears to
be a mixture of Java, C, and C++. Just some stats:
$find . -name '*.c' | wc
108 1083908
~/source-oem/libreoffice-core$find . -name '*.cpp' | wc
26 261360
~/source-oem/libreoffice-core$find . -name '*.java' | wc
33293329 206921
~/source-oem/libreoffice-core$find . -name '*.cxx' | wc
95629562 405662
And, for fun, I ran: for i in c cpp cxx java;do echo $i;wc $(find
. -name *.${i});done | tee mckown
to get the number of lines of code in each of those.
c == 45,322 lines
cpp == 4,600 lines
cxx == 5,162,525
java == 616,578 lines
So C++ wins by about a 3:1 ratio of number of files and 10:1 ratio
in terms of lines of code over Java.
There is also a breakdown of libre office code here with graphs and a table:
https://www.openhub.net/p/libreoffice/analyses/latest/languages_summary
The corresponding page for sqlite is:
https://www.openhub.net/p/sqlite/analyses/latest/languages_summary
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window functions?
The wording in the cited link is that Queries of the form: SELECT max(x), y FROM table returns the value of y on the same row that contains the maximum x value. There is some question of whether min(x) is of the form max(x). On Thu, Aug 28, 2014 at 10:28 AM, Clemens Ladisch clem...@ladisch.de wrote: Adam Devita wrote: select id, category_id, name, min(price) as minprice from cat_pictures group by category_id; I'd be reluctant to write that query because it is non standard SQL and I can't easily (5 minutes of searching) point at a document that tells me the expected behavior. The SQL standard does not allow it. SQLite allows it for bug compatibility with MySQL. (The returned values are from some random row.) In SQLite 3.7.11 or later, the behaviour is defined: http://www.sqlite.org/releaselog/3_7_11.html but IIRC this was the wish of a paying customer, and is not documented anywhere else. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RPAD/LPAD
On Thu, Mar 6, 2014 at 6:29 PM, Walter Hurry walterhu...@gmail.com wrote:
Richard Hipp wrote:
On Thu, Mar 6, 2014 at 3:41 PM, Walter Hurry walterhu...@gmail.com wrote:
A small enhancement request:
It would be great if the RPAD and LPAD functions could be implemented in
sqlite.
The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
See http://www.sqlite.org/lang_corefunc.html#printf for details.
Thanks, but you snipped the relevant part of my post:
I know I can easily achieve the equivalent ... but if the functions were
available natively it would avoid the need to hack third party SQL scripts.
I have also found that it was tedious to retarget MySQL scripts to
SQLite because many of the function calls are different. Its not just
rpad and lpad but other functions too.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RPAD/LPAD
On Thu, Mar 6, 2014 at 8:41 PM, RSmith rsm...@rsweb.co.za wrote:
On 2014/03/07 01:59, Gabor Grothendieck wrote:
A small enhancement request:
It would be great if the RPAD and LPAD functions could be implemented
in
sqlite.
The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
See http://www.sqlite.org/lang_corefunc.html#printf for details.
Thanks, but you snipped the relevant part of my post:
I know I can easily achieve the equivalent ... but if the functions were
available natively it would avoid the need to hack third party SQL scripts.
I have also found that it was tedious to retarget MySQL scripts to
SQLite because many of the function calls are different. Its not just
rpad and lpad but other functions too.
Speaking as someone who retargets (nice word btw.) SQL scripts often, yes I
agree, it's a bit of a chore to retarget SQL scripts to SQLite sometimes,
but not really moreso than retargeting a script from MSSQL to PostGres or
I have also retargeted MySQL scripts to H2 and it was easier than to SQLite.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion to add locate as a broader version of instr
On Fri, Feb 14, 2014 at 2:33 PM, Max Vlasov max.vla...@gmail.com wrote: Hi, Some time ago when there was no instr functions, I looked at Mysql help pages and implemented a user function locate as the one that allows searching starting a particular position in the string. With two parameters form it was just identical to instr only the order of parameters was reversed. As I see, the latest sqlite has only instr. It's not a big deal, but I noticed that locate with three parameters becomes convenient for CTE recursive queries since it allows search sequentially in the string. For example, a little bulky at last, but I managed to do comma-list to dataset query I suppose implementing locate and doing instr as a call to locate would cost the developers probably no more than a hundred of bytes for the final binary Parsing fields is also done with substring_index in MySQL and having both locate and substring_index would be useful for MySQL compatibility. Parsing fields created using group_concat is one particular example. One related item is that in MySQL group_concat can specify the order of rows to be concatenated as well as a number of other aspects not currently available in SQLite. -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ 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 Mon, Feb 10, 2014 at 10:23 AM, Richard Hipp d...@sqlite.org wrote: The Problem: Many new users (especially university students taking a database 101 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. class) download the sqlite3.exe file from the SQLite website, double-click on the sqlite3 icon to get a command-line shell, then start typing SQL statements. But when they exit the shell, they are distressed to discover that their database has disappeared. Proposed Change To Address The Problem: When launching sqlite3.exe with a double-click, have it open a standard database in a standard place instead of an in-memory database as you would get when launching sqlite3.exe with no arguments. Possibly also give additional hints, such as references to the .open command, when launching by double-click. (1) Detect double-click launch by looking at argc and argv. On a double-click launch, argc==1 and argv[0] contains the full pathname of the executable. On a command-line launch, argv[0] contains whatever the user typed, which is usually not the full pathname I assume that means that if you do not keep sqlite3 on your path then you must use: /path/to/sqlite3 :memory: to call sqlite3 with an in-memory database. I am not so enthusiastic about this. How about as an alternative that it works as it does now but when you exit it asks you if you want to save the database. That seems more consistent with how other programs (editors, word processors, spreadsheets, etc.) work. (2) This change would be for Windows only. The code to implement it would be enclosed in #ifdef _WIN32 ... #endif (3) Announce the name of the standard database file in the banner. Questions: (4) What should the name of the standard database file be? (5) In what folder should the standard database file be created? %appdata%\sqilte ___ 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
That should have read right join. Its a nuisance when you are trying to show someone SQL and trying to keep things simple that you have to add the complexity of switching the arguments around. I am still on 3.7.17 which is the version that currently ships with the software I am using but its nice to know that I will have rfc4180 when its upgraded. On Mon, Feb 10, 2014 at 1:26 PM, Petite Abeille petite.abei...@gmail.com wrote: 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 -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ 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 Mon, Feb 10, 2014 at 1:56 PM, Petite Abeille petite.abei...@gmail.com wrote: 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. Right joins are important since they generalize subscripting. For example, if X and Y are data tables (created using the R data.table package) then X[Y] is a right join of X and Y. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hints for the query planner
PostgreSQL supports create index indexname on table ( expression ) Note that it allows an expression and not just a column name. See: http://www.postgresql.org/docs/9.1/static/sql-createindex.html Perhaps indexing the expression in question would be an alternative that would keep the performance info separate from the select. On Tue, Sep 10, 2013 at 3:26 PM, Richard Hipp d...@sqlite.org wrote: There is a survey question at the bottom of this message. But first some context... Over on the sqlite-dev mailing list, a debate has been going on about the best way to provide some useful hints to the query planner. The query under discussion looks like this: SELECT DISTINCT aname FROM album, composer, track WHERE cname LIKE '%bach%' AND composer.cid=track.cid AND album.aid=track.aid; Assuming that the schema has appropriate indices and ANALYZE has been run, SQLite does a good job of selecting an efficient query plan for the above. But the query planner lacks a key piece of information that could help it to do a better job. In particular, the query planner does not know how often the subexpression cname LIKE '%bach%' will be true. But, it turns out, the best query plan depends critically on this one fact. By default, the query planner (in SQLite 3.8.0) assumes that a subexpression that cannot use an index will always be true. Probably this will be tweaked in 3.8.1 so that such subexpressions will be assumed to usually, but not always, be true. Either way, it would be useful to be able to convey to the query planner the other extreme - that a subexpression is usually not true. (Pedantic detail: not true is not the same as false in SQL because NULL is neither true nor false.) There is currently code in a branch that provides a hinting mechanism using a magic unlikely() function. Subexpressions contained within unlikely() are assumed to usually not be true. Other than this hint to the query planner, the unlikely() function is a complete no-op and optimized out of the VDBE code so that it does not consume any CPU cycles. The only purpose of the unlikely() function is to let the query planner know that the subexpression contained in its argument is not commonly true. So, if an application developer knows that the string bach seldom occurs in composer names, then she might rewrite the query like this: SELECT DISTINCT aname FROM album, composer, track WHERE unlikely(cname LIKE '%bach%') AND composer.cid=track.cid AND album.aid=track.aid; The query planner might use this likelihood hint to choose a different query plan that works better when the subexpression is commonly false. Or it might decide that the original query plan was good enough and ignore the hint. The query planner gets to make that decision. The application developer is not telling the query planner what to do. The application developer has merely provided a small amount of meta-information about the likelihood of the subexpression being true, meta-information which the query planner may or may not use. Note that the subexpression does not have to be a LIKE operator. PostgreSQL, to name one example, estimates how often a LIKE operator will be true based on the pattern on its right-hand side, and adjust query plans accordingly, and some have argued for this sort of thing in SQLite. But I want a more general solution. Suppose the subexpression involves one or more calls to application-defined functions about which the query planner cannot possible know anything. A general mechanism for letting the query planner know that subexpressions are commonly not true is what is desired - not a technique for making LIKE operators more efficient. SURVEY QUESTION: The question for today is what to call this magic hint function: (1) unlikely(EXPR) (2) selective(EXPR) (3) seldom(EXPR) (4) seldom_true(EXPR) (5) usually_not_true(EXPR) Please feel free to suggest other names if you think of any. ADDITIONAL INFORMATION: The current implementation allows a second argument which must be a floating point constant between 0.0 and 1.0, inclusive. The second argument is an estimate of the probability that the expression in the first argument will be true. The default is 0.05. Names like unlikely or seldom work well when this probability is small, but if the second argument is close to 1.0, then those names seem backwards. I don't know if this matters. The optional second argument is not guaranteed to make it into an actually release. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list
Re: [sqlite] MONTH function
Which can also be written as:
select cast(strftime('%m','now') as integer)=6
On Sun, Jun 23, 2013 at 4:11 PM, Patrik Nilsson
nipatriknils...@gmail.com wrote:
You can write:
select cast(strftime('%m',datetime('now')) as integer)=6
1
On 06/23/2013 09:45 PM, Lucas wrote:
Hello,
I am testing SQLIte as we are considering to change our DB Server but I
found that a feature is missing, the function Month:
SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE MONTH(FECHA) = 1
This works perfect under MySQL or MSSQL.
Do you plan to introduce MONTH and YEAR functions?.
Also, the function strftime('%m', fecha) does not solve the issue:
SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1
Please, any idea of how to solve it.
Thank you. Best regards,
Lucas de Beltran
Caritas España
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
ASCII ribbon campaign ( )
against HTML e-mail X
www.asciiribbon.org / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Statistics Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] MONTH function
Or even:
select cast(strftime('%m') as integer)=6
On Sun, Jun 23, 2013 at 4:16 PM, Gabor Grothendieck
ggrothendi...@gmail.com wrote:
Which can also be written as:
select cast(strftime('%m','now') as integer)=6
On Sun, Jun 23, 2013 at 4:11 PM, Patrik Nilsson
nipatriknils...@gmail.com wrote:
You can write:
select cast(strftime('%m',datetime('now')) as integer)=6
1
On 06/23/2013 09:45 PM, Lucas wrote:
Hello,
I am testing SQLIte as we are considering to change our DB Server but I
found that a feature is missing, the function Month:
SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE MONTH(FECHA) = 1
This works perfect under MySQL or MSSQL.
Do you plan to introduce MONTH and YEAR functions?.
Also, the function strftime('%m', fecha) does not solve the issue:
SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1
Please, any idea of how to solve it.
Thank you. Best regards,
Lucas de Beltran
Caritas España
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
ASCII ribbon campaign ( )
against HTML e-mail X
www.asciiribbon.org / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Statistics Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
--
Statistics Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running on windows 98
On Thu, Jan 24, 2013 at 2:21 PM, Richard Hipp d...@sqlite.org wrote: On Thu, Jan 24, 2013 at 2:01 PM, Jose F. Gimenez jfgime...@wanadoo.eswrote: Richard, thanks for replying. We have no way of testing SQLite on Win9x and so we do not intend to support Win9x moving forward. Some older versions of SQLite are known to work on Win9x. If you are still supporting Win9x applications, I suggest you use those older versions of SQLite. How about just supporting a compile time option to turn on or off that optimization (on by default)? Then those compiling for Win9x could just turn it off yet it would not require explicit support and testing of Win9x since its the option being supported rather than the platform support. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] just a test
I am still having problems with Igor's gmail messages being marked as spam in gmail but after the upteenth time declaring them not to be spam google finally asked me if I wanted to report it to their gmail team so hopefully they will fix it soon. On Mon, Dec 3, 2012 at 11:59 PM, Clive Hayward haywa...@chayward.com wrote: Igor's messages sometimes get marked as spam by gmail. -- Clive Hayward On 2012-12-03, at 7:57 AM, e-mail mgbg25171 mgbg25...@blueyonder.co.uk wrote: I've posted a couple of mails lately...I'm not getting them via the list or any responses. Admin says Igor responded to one of them...Thanks Igor! This is just a test to see if the mail is coming to me (as a member of the list). Therefore please just ignore this. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL:2003 -- Window Functions
On Wed, Sep 19, 2012 at 12:51 PM, joe.fis...@tanguaylab.com joe.fis...@tanguaylab.com wrote: Too bad SQLite doesn't yet support SQL Window Functions. Are there any SQLite Extension Libraries that support SQL:2003 type Window Functions? I specifically need LEAD and LAG to calculate an event integer timestamp delta between consecutive rows. I've played with some self-join code but that's proving to be complicated. SQL Window Functions is the number one feature that I could use as well. In R, sqlite can be used for manipulating R data frames via the sqldf package and this sort of functionality would be very useful. (sqldf also handles PostgreSQL which does have windowing functions but PostgreSQL requires more set up than sqlite so its not as accessible to users.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Details on New Features
On Sun, May 6, 2012 at 8:00 PM, Donald Griggs dfgri...@gmail.com wrote: Regarding: What precisely are the improvements in handling of CSV inputs? Gabor, I don't know about precisely -- I'll let others on the list tell me where I'm off, but here's my take: A lot of strange things call themselves csv, but the change attempts to make the sqlite3 utility's CSV inputs perform a bit more closely to RFC4180. http://tools.ietf.org/html/rfc4180 http://en.wikipedia.org/wiki/Comma-separated_values#Toward_standardization In particular, during CSV mode import: -- Allow any field to be surrounded by double quote characters without those characters being considered part of the field data. -- Allow fields to contain embedded commas (or other separators) when the field is surrounded by double quote characters. -- Allow fields to span multiple lines if they are surrounded by double quote characters. -- Allow the double quote character to be escaped by having two adjacent double quote characters. (But note that a field consisting solely of two double quote characters still represents an empty string field.) -- On output in CSV mode, surround text fields with double quotes when needed. See check-in [93aa17d866] http://www.sqlite.org/src/info/93aa17d866 Thanks very much. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Details on New Features
In this link: http://sqlite.org/releaselog/3_7_11.html it refers to these new features: Queries of the form: SELECT max(x), y FROM table returns the value of y on the same row that contains the maximum x value. Improvements to the handling of CSV inputs in the command-line shell Is there documentation somewhere that defines exactly what these mean? Does the max(x) apply to min(x) too? does the max have to precede the y? Can there be multiple y's? What precisely are the improvements in handling of CSV inputs? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Details on New Features
On Fri, May 4, 2012 at 10:20 AM, Richard Hipp d...@sqlite.org wrote: On Fri, May 4, 2012 at 10:06 AM, Rob Richardson rdrichard...@rad-con.comwrote: Gabor Grothendieck mentioned a new feature of SQLite in 3.7.11: Queries of the form: SELECT max(x), y FROM table returns the value of y on the same row that contains the maximum x value. Is that standard SQL behavior? I'd have expected that to return one row for every row in the table. To get the behavior described above, I'd use SELECT x, y FROM table WHERE x = (SELECT max(x) FROM table). It is definitely NOT standard behavior. The standard behavior is undefined. Or (with many SQL engines) it will throw an error if you have a term in the result set that is not part of an aggregate function or an element of the GROUP BY clause. But lots of newbies expect SQL to work as described in the 3.7.11 release comments, and we used to get support questions because it did not. And so rather than continue to answer the questions over and over, I figured it would be easier to tweak SQLite to reliably do what newbies expect. I never anticipated that this change would be so controversial or confusing. All the existing, portable, documented ways to find the maximum element of one column while simultaneously finding the other elements in the same row, continue to work as they always have. You are not required to use this new approach. In fact, if you want your SQL to be portable, you should probably avoid it. By adding this feature, we had hoped to help application developers avoid a common SQL programming error. That's all. There is nothing profound going on here. Can't anyone answer the question directly? I would still like to know precisely what works and what does not. Its not possible to rely on general SQL documentation for this so I think its important to document it exactly. Otherwise, we are left to examine the source code or use trial and error (and these methods only tell you how it works but not how its intended to work and they could be different if there are bugs). If the documentation does exist please point me to it but I did not find it on my own. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Details on New Features
On Fri, May 4, 2012 at 10:46 AM, Gabor Grothendieck ggrothendi...@gmail.com wrote: On Fri, May 4, 2012 at 10:39 AM, Richard Hipp d...@sqlite.org wrote: On Fri, May 4, 2012 at 10:33 AM, Gabor Grothendieck ggrothendi...@gmail.com wrote: On Fri, May 4, 2012 at 10:20 AM, Richard Hipp d...@sqlite.org wrote: On Fri, May 4, 2012 at 10:06 AM, Rob Richardson rdrichard...@rad-con.comwrote: Gabor Grothendieck mentioned a new feature of SQLite in 3.7.11: Queries of the form: SELECT max(x), y FROM table returns the value of y on the same row that contains the maximum x value. Is that standard SQL behavior? I'd have expected that to return one row for every row in the table. To get the behavior described above, I'd use SELECT x, y FROM table WHERE x = (SELECT max(x) FROM table). It is definitely NOT standard behavior. The standard behavior is undefined. Or (with many SQL engines) it will throw an error if you have a term in the result set that is not part of an aggregate function or an element of the GROUP BY clause. But lots of newbies expect SQL to work as described in the 3.7.11 release comments, and we used to get support questions because it did not. And so rather than continue to answer the questions over and over, I figured it would be easier to tweak SQLite to reliably do what newbies expect. I never anticipated that this change would be so controversial or confusing. All the existing, portable, documented ways to find the maximum element of one column while simultaneously finding the other elements in the same row, continue to work as they always have. You are not required to use this new approach. In fact, if you want your SQL to be portable, you should probably avoid it. By adding this feature, we had hoped to help application developers avoid a common SQL programming error. That's all. There is nothing profound going on here. Can't anyone answer the question directly? I would still like to know precisely what works and what does not. Its not possible to rely on general SQL documentation for this so I think its important to document it exactly. Otherwise, we are left to examine the source code or use trial and error (and these methods only tell you how it works but not how its intended to work and they could be different if there are bugs). If the documentation does exist please point me to it but I did not find it on my own. If a single min() or max() aggregate function appears in a query, then any other columns that are not contained within aggregate functions and that are not elements of the GROUP BY will take values from one of the same rows that satisfied the one min() or max() aggregate function. Thanks! I expect that this will be useful for me for at least quick and dirty computations. I suggest that this statement be added to the docs if its not already there. Also the other part of my question. What were the improvements to the csv import? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Good books to lear SQL using SQLite?
On Mon, Apr 30, 2012 at 10:18 AM, Arbol One arbol...@programmer.net wrote: I am learning SQL using SQLite, can anyone tell me of a good book to learn SQL using SQLite? Its a web page and free software, not a book, but you might try this page to start off and then get a book after you have used it a bit: http://sqldf.googlecode.com It uses R but you don't really have to know R to use it. Note link which is for people who want to try it and don't know R: http://code.google.com/p/sqldf/#For_Those_New_to_R -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to use substrings of Windows DOS batch fiile parameters in sqlite3.exe -line db sql?
On Tue, Apr 10, 2012 at 7:14 PM, Frank Chang frank_chan...@hotmail.com wrote:
Good evening, We are trying to generate automated SQLITE SQL scripts based
on the names of SQLite tables derived by substring manipulation of Windows
DOS batch file and/or Windows environment variables. For example:
/* mary.bat */
FOR /f %%a IN ('dir /b *.zip') DO CALL sub %%a
/* sub.bat */
set str=%1
set camster=%str:~0.17%
The dot should be a comma.
--
Statistics Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to wrie table char in my html file?
On Sat, Mar 31, 2012 at 2:55 AM, YAN HONG YE yanhong...@mpsa.com wrote: sqlite3 -html C:\mydatabase\mydzh.db select 'table'mm.html this command result is not table in the mm.html file, it's this following text: TRTDlt;tablegt;/TD /TR not I wanted, how to wrie table char in my html file? This seems to be the code I sent you privately except its been changed from what I wrote so that it no longer works. The original code did not use -html . Also for those who are more used to UNIX since we are on Windows here, note that echo table and echo 'table' do NOT work (the first outputs double quotes and the second does not escape the angle brackets) but as an alternative this does work: echo ^table^ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] HOW TO EXPORT TABLE HEAD
On Mon, Mar 26, 2012 at 11:38 PM, YAN HONG YE yanhong...@mpsa.com wrote: WHEN I export sqlite database to a html file or txt file, I couldn't know how to include the database table head. who can tell me? Thank you! Use -header like this: sqlite3 -html -header my.db select * from mytable See output of: sqlite3 -help ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQL or C for data analysis?
On Tue, Mar 27, 2012 at 3:02 PM, Simon turne...@gmail.com wrote: select closing_price, moving_average( funky_oscillator( closing_price ) )... There is a moving average calculation in SQLite here but given the complexity you might prefer to do the analytical portion in your program: http://code.google.com/p/sqldf/#Example_16._Moving_Average It would be nice if sqlite had sql windowing functions to simplify these sorts of calculations. -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] more than 2000 columns
On Mon, Nov 14, 2011 at 5:32 AM, Simon Slavin slav...@bigfraud.org wrote: On 14 Nov 2011, at 7:38am, vinayh4 wrote: I need to create table with more than 2000 columns, How to reset SQLITE_MAX_COLUMN value which is 2000 . Plz help me on this issue. You almost never need to have more columns than you can fit in your head at one time. The way you handle 2000 columns is to make a database for them ! The requirement for a large number of columns is actually one thing that is often needed when using sqlite from R. Typically the use case is that a user wishes to read a portion of an external file into R and that file has thousands of columns. For example, each row might be an individual and each column is a gene. Or each row is a time point and each column is a security (stock, bond, etc.) The file may too large to reasonably handle in memory so rather than deal with it in chunks at a time its easier to just read it into sqlite in its entirety and then pick off the portion you want into R using sql. -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] more than 2000 columns
On Mon, Nov 14, 2011 at 12:21 PM, Igor Tandetnik itandet...@mvps.org wrote: On 11/14/2011 12:11 PM, Gabor Grothendieck wrote: The requirement for a large number of columns is actually one thing that is often needed when using sqlite from R. Typically the use case is that a user wishes to read a portion of an external file into R and that file has thousands of columns. For example, each row might be an individual and each column is a gene. Or each row is a time point and each column is a security (stock, bond, etc.) In relational databases, things like that are usually represented as GeneInfo(person, gene, infoAboutGene) or StockInfo(timestamp, stock, price) That is a good point; however, in the context of this use case we are dealing with external files and don't have control over their format. -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] more than 2000 columns
On Mon, Nov 14, 2011 at 12:50 PM, Simon Slavin slav...@bigfraud.org wrote: On 14 Nov 2011, at 5:11pm, Gabor Grothendieck wrote: The requirement for a large number of columns is actually one thing that is often needed when using sqlite from R. Typically the use case is that a user wishes to read a portion of an external file into R and that file has thousands of columns. For example, each row might be an individual and each column is a gene. That would be a text file, right ? So SQLite isn't involved in that. You have other routines to read text files. Yes. Its text. Also this is not application specific. Its a general facility that everyone uses so writing application specific routines is completely out of the question here. Or each row is a time point and each column is a security (stock, bond, etc.) The file may too large to reasonably handle in memory so rather than deal with it in chunks at a time its easier to just read it into sqlite in its entirety and then pick off the portion you want into R using sql. So I actually have R on my Mac and I went and had a look. You're talking about using RSQLite ? Yes, there are fast ways to move data between a SQLite table and an R matrix. I can see the appeal. But you can also execute arbitrary SQL commands. So you can write your own import/export routine which takes a very wide matrix from R but stores it in a less wide table in SQLite. What users want is to get access to their data with as little hassle as possible and currently its possible to do it all in one line of R code which sets up an sqlite database and table, reads the data into it and then applies a given sql statement to that and finally destroys the database. Its trivial to do. The only limitation is that the file can have no more than 999 columns as its currently set up. In most cases that works but some people have wider files and there are constantly requests to increase the limit. All you do is provide the filename, certain parameters such as the input field separator and optionally the sql statement (which defaults to select * from file). Its very easy from the user's point of view. Its just one line of code. What I think you're trying to do is use a SQLite table as a data frame. If I understand correctly, this means you can use the commands you'd normally use with an R matrix, but with data still stored in a SQLite table, without having to rewrite the code of your program. It's a really neat hack. Trouble is, SQLite isn't efficient with such wide tables. Your code is going to operate really slowly. I'm going to have to get backup from SQLite experts here, but I understand that unlike how matrices are stored in R, a table's columns are stored in a structure like a linked list. So if you try to access the 1,400th column it has to walk a list of 1,399 items to find it. Which isn't efficient. I am not aware of any performance tests on very wide files with sqlite followed by moving them into R but with the usual files of just a handful of columns it is sufficiently fast -- its so fast that at times it can be faster to read it into sqlite and then from there into R than reading the file straight into R (in those cases where both are possible). We will see what happens when it gets expanded beyond 999. A previous thread on this list suggested that there was no real downside to expanding the limit. I asked for clarification at the time but no one responded. So fine. Use SQLite to store tables as wide as you like. But write your own import/export commands to fetch appropriate parts into memory. There's no need to use a SQLite table 2000 columns wide just because your matrix is 2000 columns wide. Purely a recommendation for serious software intended for proper use. Do anything you like in quick hacks: CPU time and memory usage can be stupid big for those. I am not clear on what you are suggesting but the way it works is that the file gets read in its entirety into an sqlite database and then an sql statement specified by the user is applied to that and only the output of the sql statement ever gets sent to R so even if the input has thousands of columns, the data sent from sqlite to R might not. Hope that clarifies the situation. -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_MAX_VARIABLE_NUMBER
In R, the RSQLite driver for SQLite currently has SQLITE_MAX_VARIABLE_NUMBER set to 999. This is used by many people for many different projects and on different platforms and it seems that a number of these projects want a larger number. Users don't compile this themselves so they are stuck with whatever number is compiled in for them. What are the considerations for setting this number? Is there any general advice that can be given on how to set this number? -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_MAX_VARIABLE_NUMBER
On Tue, Nov 8, 2011 at 5:55 PM, Simon Slavin slav...@bigfraud.org wrote: On 8 Nov 2011, at 10:50pm, Gabor Grothendieck wrote: In R, the RSQLite driver for SQLite currently has SQLITE_MAX_VARIABLE_NUMBER set to 999. This is used by many people for many different projects and on different platforms and it seems that a number of these projects want a larger number. What sort of statements are R users doing which might require binding a thousand variables to one statement ? I can't think of any situation like this that doesn't indicate an insane schema which should be normalised. Anyone who uses R could be using this so we don't really know. We do know that there seems to be multiple requests for increasing the limit. R is used for analyzing data and when when one is doing that one does not always generate the data oneself but receives it from an external source. This may include files which may be too large to read into R or might fit in but are too slow to read into R. They might be read into a database and then a portion read into R from the database. One could imagine the rows might represent individuals and the columns might represent a large number of genes. Or perhaps each row is an individual and each column is a health marker. Or each row is a time point and each column is a security. Typically such users must use a different database but would have preferred to use SQLite hence the question of what are the considerations of coming up with a single SQLITE_MAX_VARIABLE_NUMBER that everyone is stuck with. -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_MAX_VARIABLE_NUMBER
On Tue, Nov 8, 2011 at 6:46 PM, Richard Hipp d...@sqlite.org wrote: On Tue, Nov 8, 2011 at 5:50 PM, Gabor Grothendieck ggrothendi...@gmail.comwrote: In R, the RSQLite driver for SQLite currently has SQLITE_MAX_VARIABLE_NUMBER set to 999. This is used by many people for many different projects and on different platforms and it seems that a number of these projects want a larger number. Users don't compile this themselves so they are stuck with whatever number is compiled in for them. What are the considerations for setting this number? Is there any general advice that can be given on how to set this number? I just checked and it appears that MacOS Lion compiles it a 50. Whenever you use a value like ?N, SQLite allocates an array of N objects, each of 72 bytes in size. So doing SELECT ?50 on Lion requires a 36MB memory allocation (with its accompanying memset()). That's really the only downside to choosing a really large SQLITE_MAX_VARIABLE_NUMBER. If SELECT ?50 allocates 50 * 72 bytes of memory then how does that relate to SQLITE_MAX_VARIABLE_NUMBER? SQLITE_MAX_VARIABLE_NUMBER did not seem to enter the calculation at all. -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_MAX_VARIABLE_NUMBER
On Tue, Nov 8, 2011 at 7:11 PM, Richard Hipp d...@sqlite.org wrote: On Tue, Nov 8, 2011 at 7:08 PM, Gabor Grothendieck ggrothendi...@gmail.comwrote: On Tue, Nov 8, 2011 at 6:46 PM, Richard Hipp d...@sqlite.org wrote: On Tue, Nov 8, 2011 at 5:50 PM, Gabor Grothendieck ggrothendi...@gmail.comwrote: In R, the RSQLite driver for SQLite currently has SQLITE_MAX_VARIABLE_NUMBER set to 999. This is used by many people for many different projects and on different platforms and it seems that a number of these projects want a larger number. Users don't compile this themselves so they are stuck with whatever number is compiled in for them. What are the considerations for setting this number? Is there any general advice that can be given on how to set this number? I just checked and it appears that MacOS Lion compiles it a 50. Whenever you use a value like ?N, SQLite allocates an array of N objects, each of 72 bytes in size. So doing SELECT ?50 on Lion requires a 36MB memory allocation (with its accompanying memset()). That's really the only downside to choosing a really large SQLITE_MAX_VARIABLE_NUMBER. If SELECT ?50 allocates 50 * 72 bytes of memory then how does that relate to SQLITE_MAX_VARIABLE_NUMBER? SQLITE_MAX_VARIABLE_NUMBER did not seem to enter the calculation at all. SQLITE_MAX_VARIABLE_NUMBER determines the largest N for which ?N will work. So by default, the maximum allocation is 999*72. You can increase this to whatever you are comfortable with. Thanks. So there is really no downside to making it 10,000, say? Those who want it that large will be able to have that many columns and those who don't need that many won't incur any penalties. Is that right? -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feed endless data into sqlite, thru a shell script
On Tue, Sep 27, 2011 at 2:14 PM, David Garfield garfi...@irving.iisd.sra.com wrote: Any entry in a pipe could be buffering. In a quick test here, awk is buffering. To find the buffering, try using the pieces up to a given stage with | cat added at the end. If this buffers, you've found the problem. Unbuffered output is usually slower, so it is normally done only to a terminal. I think the only easy way to externally disable the buffer is to wrap the program in a pseudo-tty. Alternatively, look for an option that lets you explicitly unbuffer. (for instance, in perl, do: $| = 1; ) gawk has fflush() ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?
On Mon, Jun 6, 2011 at 11:54 AM, Richard Hipp d...@sqlite.org wrote: On Mon, Jun 6, 2011 at 11:44 AM, Sidney Cadot sid...@jigsaw.nl wrote: Would it be useful to open a ticket on this issue, or will it never be changed e.g. for fear of breaking backward compatibility? There are approx 2 billion legacy apps in the wild that use SQLite. Not breaking things is very important to us, therefore. So there needs to be a compelling reason to make a change like this. I do not think you have made a sufficient case for the change, yet. I and others use SQLite from R and R itself gives Inf, -Inf and NaN for 1/0, -1/0 and 0/0 respectively so it would reduce the differences between the database and R if it worked in the same way. Perhaps an option could control this behavior so that backwards compatibility could be maintained. -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?
On Wed, Dec 8, 2010 at 2:44 PM, Richard Hipp d...@sqlite.org wrote: On Wed, Dec 8, 2010 at 2:35 PM, Petite Abeille petite.abei...@gmail.comwrote: Hello, The pragma foreign_key_list appears to be deprecated in 3.7.4: http://www.sqlite.org/pragma.html#pragma_foreign_key_list Any reason for such deprecation? Now that foreign key constraints are enforced natively, why would you want to have a list of them? Why should the foreign_key_list pragma continue to consume code space and developer maintenance time? Surely if the objective is to be small yet useful its important to give users the capability to easily implement what would otherwise have to be done in the database itself. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow SELECT Statements in Large Database file
On Fri, Oct 29, 2010 at 12:07 PM, Jonathan Haws jonathan.h...@sdl.usu.edu wrote: All, I am having some problems with a new database that I am trying to setup. This database is a large file (about 8.7 GB without indexing). The problem I am having is that SELECT statements are extremely slow. The goal is to get the database file up and running for an embedded application (we have tons of storage space so the size is not a problem). Here is the schema layout: CREATE table Dted (dtedkey INTEGER PRIMARY KEY, dted_lat INTEGER, dted_lon INTEGER, dted_alt FLOAT); We lookup dted_alt based on dted_lat and dted_lon. Here is our SELECT statement: SELECT dted_alt FROM Dted WHERE (dted_lat=%d AND dted_lon=%d); The numbers fed to dted_lat and dted_lon are typically on the order of 37 and -111. What can we do to speed up our SELECT statements? Minutes is unacceptable for our application. We were hoping we could run somewhere on the order of 500 queries per second and get valid results back. I am not an SQL expert, but I was reading about indexes that that it is best to have a specific index per SELECT. Since we only have one, this is the index I am creating now (it has been creating this index on my machine for the past 10 minutes now): CREATE INDEX idx_ll ON Dted(dted_lat,dted_lon); Is that a good index for my SELECT? Will it speed up the accesses? Any thoughts? Google for the spatialite extension. -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GUI for data entry
On Fri, Oct 15, 2010 at 12:54 PM, Graham Smith myotis...@gmail.com wrote: Tom, Thanks for this. My main reason for asking is because I am trying to encourage my students and indeed clients to think database rather than spreadsheet. Most of the time these aren't big or complex data sets (normally records in the hundreds, sometimes the thousands) but still big enough to create major problems for themselves and me, just because the spreadsheet gives them the freedom to really screw things up. While far from perfect, I could live with a single table in a database that could be queried from R . But it needs to be user friendly and run on Linux, Windows and Macs. If the purpose of this is teaching with R then the R package sqldf lets you query all R data frames in your session using sql as if they were one big giant database. If you stick with the few dozen data frames that ship with R or ones you create yourself using various R facilities then you don't have to enter anything in the first place. See the sqldf home page at: http://sqldf.googlecode.com -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Involving in sqlite development
On Thu, Oct 7, 2010 at 5:26 PM, sjtirtha sjtir...@gmail.com wrote: Hi, I'm interested involving in sqlite development. How can I start it? If I can add to this question is there a posted wishlist, todo list or roadmap? Something like this: http://www.h2database.com/html/roadmap.html -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
On Sat, Sep 4, 2010 at 7:31 AM, Mike Zang mikez...@yahoo.co.jp wrote: I try to convert data to SQLite3 for iPad, please give me some detail suggestion. I think that I can save date value as below to SQLite3, I want to know which is better, or anything else if you have good idea. 1. integer as seconds since 1970 2. integer as days since 1970 3. string as '2010-09-03' 4. string as '10-09-03' sqlite has julianday and date sql functions which convert back and forth between julianday (number of days since noon in Greenwich on November 24, 4714 B.C.) and -mm-dd representations and also handle other manipulations in those formats so you probably want to choose one of those. -mm-dd does have the advantage that its easier to look at the raw data in the database. Also, if you are only dealing with dates and do not need to consider time zones then its best to use a representation that uses neither times nor time zones since those can introduce errors which are artifacts of the representation. time zone errors (confusion between UTC and current time zone) can be particularly subtle. sqlite select date(now); 2010-09-04 sqlite select date(2000-01-01, +1 day); 2000-01-02 sqlite select julianday(date(now)) - julianday(2010-09-01); 3.0 sqlite select date(julianday(date(now))); 2010-09-04 See: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 5000 tables with 3000 records vs 1 table with 15000000 records
On Sun, Aug 22, 2010 at 4:51 AM, Mike Zang mikez...@yahoo.co.jp wrote: I have 5000 files and I want to converrt them to SQLite3 on iPad, now I have a performance question, I am not sure which way is better for select and insert data in SQLite3. I have two ideas for converting. 1. convert 1 file to 1 table, so that I will have about 5000 tables in SQLIte3 database, and any file will have about 3000 records. 2. convert all 5000 files to 1 table, there will be 1500 records. Please give a suggestion before I start my programming. Don't know how generalizable this is but I tried this with a single 10+ GB table made up of ~ 1000 similarly formatted tables of ~ 75k records each. This is running on a USB drive under Windows Vista and SQLite version 3.6.18. I tried adding two indexes. The first create index took a few minutes to an hour to create and I killed the second create index after it was still going 24 hours later. select count(*) takes 5 minutes to run but select count(*) from data where x = 'X' returns immediately where x is the first column in the index that succeeded. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Doing fine with SQLite
You don't need to dump the data to a csv file and then read it into R and there is no need to use the sqlite3 console at all as R's RSQLite package can directly read and write SQLite databases. Also see the sqldf package. On Tue, May 4, 2010 at 1:02 PM, Matt Young youngsan...@gmail.com wrote: I can work SQLite from by R stat package, but I am having hard time mixing special sqlite command intermixed with SQL statements when I send a text sequence to swqlite (even from the dos consol) sqlite3 test.db .mode csv select * from selected limit 4 Makes sqlite choke because I do not know what the inline terminator is for a text invocation argument, and can't find it in the docs. That is my last minor detail, and I have indices, joins, and unions going on the Bureau of Labor Statistic under R. Will make SQLite quite popular among the economists. Getting access to reams of data from economic we sites, directly into R dataframes via a set of common key words familiar to economists. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?
SQLite and R are already integrated through the RSQLite/DBI packages and even higher level facilities (which I have posted about on this thread) also exist. I think that such an integration would logically be done by R people rather than sqlite people. In the discussion on this thread integration may mean different things to different people so if you are referring to specific features beyond what exists (such as a SQL functions written in R or stored procedures written in R) then you can (1) speak with the RSQLite package maintainer to see if he is interested in adding them or (2) if you are interested in contributing then also discuss it with him, or (3) if you are referring to specific features that make more sense in certain other R packages (sqldf, RODBC, RJDBC, sqliteDF) then you can communicate to the maintainers of those packages. On Sun, May 2, 2010 at 8:38 PM, Matt Young youngsan...@gmail.com wrote: I want to see SQLite integrated into the R statistical package. R Project http://www.gardenersown.co.uk/Education/Lectures/R/regression.htm#multiple_regression for example. R statistical is very and becoming more popular, has great plotting, and wrestles data in frames that look awfully like sql tables. R has built in procedure function, can cast text around fairly powerfully and so on. It is begging for the sqlite engine. Like me, getting better access to government statistical table. On 5/2/10, Gilles Ganault gilles.gana...@free.fr wrote: Hello, It's probably quite an easy thing to do for someone well versed in C, but I haven't seen a project that would combine a web server and SQLite into a single EXE. Besides ease of deployment, this would offer a cross-platform solution that wouldn't require developing a specific client-side connector, since both hosts would speak HTTP. Would someone with enough know-how be interested in giving it a shot? Are there technical reasons why it wouldn't be a good idea? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] scripting language interpreter
On Sat, May 1, 2010 at 12:15 PM, Richard Hipp d...@sqlite.org wrote:
On Sat, May 1, 2010 at 9:25 AM, Tim Romano tim.romano...@gmail.com wrote:
I am aware that SQLite supports
loadable extensions, but would the SQLite architecture also permit the
integration of an interpreted scripting language? An integrated scripting
language makes an already powerful database engine orders of magnitude more
useful especially when one is solving ad hoc data problems requiring very
rapid turnaround.
See http://www.sqlite.org/tclsqlite.html for the original. SQLite began
life as a TCL extension. In fact, we often think of SQLite as a TCL
extension that escaped into the wild.
The integration between TCL and SQLite is very tight. If you know where to
look, you will see that many features of SQLite were designed specifically
to support integration with TCL.
An example of TCL using SQLite:
db eval {SELECT name FROM people WHERE personid=$personid} {
puts name=$name
}
If I understand correctly what is being illustrated here then the
sqldf package in R (http://sqldf.googlecode.com) has a similar
facility. For example. from the R command line:
# installs everything needed into R
install.packages(sqldf)
# loads everything needed into R workspace
library(sqldf)
# create R data frame (similar concept to an SQL table)
DF - data.frame(a = 1:3, b = 4:6)
# the next statement notices that DF is an R data frame,
# it automatically creates an sqlite data base in memory,
# sets up table definition for DF by issuing create table stmt,
# loads DF into the sqlite data base,
# performs the query returning a new data frame
# and deletes the sqlite data base
sqldf(select * from DF where a 3)
a b
1 1 4
2 2 5
The actual interfaces between R and sqlite is in the DBI and RSQLite R
packages and sqldf sits on top of those. The RSQLite package also
includes a copy of sqlite. Installing and loading sqldf automatically
installs and loads its dependencies.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to submit a file with sql to sqlite
On Sun, Apr 18, 2010 at 12:02 PM, Wensui Liu liuwen...@gmail.com wrote: dear listers, i am wondering if there is a way to submit a file with many sql statements, say several hundred lines, to sqlite. thanks for your insight. C:\tmp2type a.sql create table tab (a,b); insert into tab values(1, 2); insert into tab values(1, 2); select * from tab; C:\tmp2sqlite3 a.db a.sql 1|2 1|2 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] STandard Deviation
Not in sqlite itself but stddev_samp and stddev_pop are available in the spatialite loadable extension. Be careful since they interchanged sample and population in one version of the extension. On Sun, Apr 18, 2010 at 9:37 PM, Peter Haworth p...@mollysrevenge.com wrote: Does Sqlite have a STDEV function? Don;t see it listed under the core or aggregate functions. Thanks, Pete Haworth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Northwind example database
It would be nice if SQLite had a strptime-like function for things like this and not just strftime. On Mon, Mar 29, 2010 at 10:13 AM, Griggs, Donald donald.gri...@allscripts.com wrote: On 27 Mar 2010, at 10:46am, GeoffW wrote: Just for educational purposes I have been experimenting a little with the Northwind Sqlite database contained on the sqlite official site. Download link: http://download.vive.net/Northwind.zip. Am I misunderstanding here or are the dates in the wrong format for sqlite within this converted database ? Assuming it is wrong and not my understsanding, are there any easy ways to get the dates reversed and corrected to sqlite order and written back out to the database ? Perhaps this is better performed in the calling language, but the following sql should reformat these dates. Of course, you'd need to substitute and repeat for the other fields. -- Reformat date from, e.g., '1/5/2010 12:00:00 AM' to '2010-01-05' update employees set birthdate = replace (birthdate, ' 12:00:00 AM', ''); update employees set birthdate = '0' || birthdate where substr(birthdate, 2,1) == '/'; update employees set birthdate = substr(birthdate, 1, 3) || '0' || substr(birthdate, 4,99) where substr(birthdate, 5,1) == '/'; -- Date should now be formatted as dd/mm/ -- Now change to -mm-dd update employees set birthdate = substr(birthdate, 7,4) || '-' || substr(birthdate, 1,2) || '-' || substr(birthdate, 4,2); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get a specific sequence of rows...
On Fri, Mar 26, 2010 at 5:00 AM, Fredrik Karlsson dargo...@gmail.com wrote: Hi, I have a list of id:s stored in a field. I would now like to get some information from a table by these id:s, but exactly in this order. So, if I have a table 1 One 2 Two 3 Three and the sequence 3,1,2 stored somewhere, how do I get a neat list like Three One Two Try this: select *, 1 * (name = Three) + 2 * (name = One) + 3 * (name = Two) sorter from mytab where sorter 0 order by sorter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
