Re: [sqlite] INSERT INTO ???
> > INSERT INTO SELECT * FROM ; > > However, this only works if already exists, which is actually > quite cumbersome.. > Is there a way to make the new table 'on the fly"? create table newtable as select * from oldtable; Bernhard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INSERT INTO ???
Hi folks, I would like to query a table and put the result in a new table, as in INSERT INTO SELECT * FROM ; However, this only works if already exists, which is actually quite cumbersome.. Is there a way to make the new table 'on the fly"? Regards, Bertwim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Regression: Query takes 10x longer when using version 3.8.x
On 21 Sep 2013, at 12:14am, Jared Albers wrote: > I was unsure if the rowid of a subquery even existed until I selected it. I > reviewed the output and determined that it was what I expected of a rowid > from a subquery (simply an auto-incremented int of the result set in > order). How/Why is the rowid of a subquery undefined when it always gives > me the result I expect? Because a earlier or later version of SQLite may make it act differently. Because it's undocumented, so the SQLite developers don't have to stick to a specific way of doing things. They might find a way to speed up SELECTs in a future version that might change the way subquery rows are numbered. Your extremely long SELECT with its sixteen COALESCEs and eight LEFT OUTER JOINs suggests that you have a schema which doesn't really suit the 'shape' of your data. Another hint pointing in the same direction is numbered database names. I think you might simplify your SELECT, reduce your data footprint, and speed up your query by rethinking your schema. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Regression: Query takes 10x longer when using version 3.8.x
>After laboriously translating your schema and query into something >readable, I get script shown below. Apologies. I assumed you planned on recreating the database and that readability wouldn't be an issue. >We (all SQLite developers have been consulted and agree) conclude that the >result of the query is undefined. You are sorting on the rowid of a >subquery. But the rowid of a subquery is undefined, so anything can happen >with there. I was unsure if the rowid of a subquery even existed until I selected it. I reviewed the output and determined that it was what I expected of a rowid from a subquery (simply an auto-incremented int of the result set in order). How/Why is the rowid of a subquery undefined when it always gives me the result I expect? >Was SQLite 3.7.17 really giving you the answer you expected? If so, we >think that was by luck. Yes version 3.7.17 is giving me the results I expect, but so does the latest version (3.8.0.2). The only difference is that the newest version does it much slower. >Can you recast the query in such a way that it does not sort on the rowid >of a subquery? The only reason why I am joining with a subquery and then sorting by its rowid is for performance. Originally I was simply sorting by "name" (or any other column or combination of columns) in the outer query but discovered it was exceedingly slow on large databases. I figured it was slow because I was ordering by a text column with an index that was not being used, which may or may not be true. This lead me to my subquery option that allows me to order by an int column (which I presume would be a "primary key" since it is a rowid). This gave me the performance I was looking for (the difference between ~1 second and ~15 seconds on large databases), but the cost of not being able to order by columns outside of my "Items" table. Note that my query does rely on an "ORDER BY" without a "LIMIT" in a subquery making it far from pointless is my use-case (valid or not). If I can get the performance I need while ordering in the outer query I would much rather do that. -Jared Date: Thu, 19 Sep 2013 15:50:17 -0400 > From: Richard Hipp > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Regression: Query takes 10x longer when using > version 3.8.x > Message-ID: > mx2pzx_jfjo-c_wtpgtu1kgaeqyd-sl5w7hed9mddd...@mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > > After laboriously translating your schema and query into something > readable, I get script shown below. > > We (all SQLite developers have been consulted and agree) conclude that the > result of the query is undefined. You are sorting on the rowid of a > subquery. But the rowid of a subquery is undefined, so anything can happen > with there. > > Was SQLite 3.7.17 really giving you the answer you expected? If so, we > think that was by luck. > > Can you recast the query in such a way that it does not sort on the rowid > of a subquery? > > Note that an ORDER BY clause without a LIMIT clause in a subquery is > pointless and might be removed by the query optimizer. > > > CREATE TABLE Items( > uid INTEGER PRIMARY KEY ASC, > id TEXT DEFAULT '', > type TEXT DEFAULT track, > kind TEXT DEFAULT '', > error INT DEFAULT 0, > name TEXT DEFAULT '', > album TEXT DEFAULT '', > artist TEXT DEFAULT '', > genre TEXT DEFAULT '', > composer TEXT DEFAULT '', > description TEXT DEFAULT '', > popularity REAL DEFAULT 0, > frequency REAL DEFAULT 0, > band TEXT DEFAULT '', > call_letters TEXT DEFAULT '', > city TEXT DEFAULT '', > state TEXT DEFAULT '', > country TEXT DEFAULT '', > provider TEXT DEFAULT '', > label TEXT DEFAULT '', > copyright TEXT DEFAULT '', > is_explicit TEXT DEFAULT false, > is_protected TEXT DEFAULT false, > is_purchased TEXT DEFAULT false, > bpm INT DEFAULT 0, > bit_rate INT DEFAULT 0, > sample_rate INT DEFAULT 0, > format TEXT DEFAULT '', > size INT DEFAULT 0, > time INT DEFAULT 0, > url TEXT DEFAULT '', > can_play TEXT DEFAULT false, > can_pause TEXT DEFAULT false, > can_seek TEXT DEFAULT false, > can_next TEXT DEFAULT false, > can_previous TEXT DEFAULT false, > can_queue TEXT DEFAULT false, > can_order TEXT DEFAULT false, > can_like TEXT DEFAULT false, > can_rate TEXT DEFAULT false, > can_star TEXT DEFAULT false, > modified_date INT DEFAULT 0, > released_date INT DEFAULT 0, > purchased_date INT DEFAULT 0, > skipped_date INT DEFAULT 0, > track_number INT DEFAULT 0, > disc_number INT DEFAULT 0, > track_count INT DEFAULT 0, > disc_count INT DEFAULT 0, > listener_count INT DEFAULT 0, > play_count INT DEFAULT 0, > skip_count INT DEFAULT 0 > ); > CREATE TABLE Relationships( > parent INTEGER, > child INTEGER, > instance INT DEFAULT 0, > owner DEFAULT '', > relationship DEFAULT '' > ); > CREATE TABLE Defaults( > type TEXT DEFAULT '', > may_play TEXT DEFAULT true, > may_pause TEXT DEFAULT true, >
Re: [sqlite] Any tool to create erd from sqlite database?
Hello, There is a minimalist one here: https://github.com/gwenn/sqliterd It depends on c/go compilers and the graphviz dot command... Regards. On Fri, Sep 20, 2013 at 2:26 PM, Jason H wrote: > Don't forget about ODBC tools... Just use ta SQLite ODBC driver... > > > > From: dd > To: General Discussion of SQLite Database > Sent: Friday, September 20, 2013 8:11 AM > Subject: [sqlite] Any tool to create erd from sqlite database? > > > I am looking for tool which generates er diagrams from existing database. > > Any suggetions? > > Thanks in advance. > ___ > 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] racing with date('now') (was: Select with dates)
On Fri, Sep 20, 2013 at 9:23 AM, Keith Medcalf wrote: > > Nice fix Richard, and it works as you have documented. > > With the added [cebd6fc551] to reset iCurrentTime when the statement is > reset, my testing indicates that if the p->iCurrentTime=0; added at line > 570 of vdbe.c by the original patch [daf6ba413c] (change fragment > [56e648f5ba9a9181]) is removed, then 'now' is stable for the statement > rather than just each step. > > That is, 'now' is stable from its first "access" in a statement until that > statement is reset. Unless you have found that it causes other issues (I > haven't found any yet) then removing the above mentioned clearing of > iCurrentTime during each step will result in SQLite's concept of 'now' > being consistent with that implemented in other SQL engines and in the > standard. > That's why I added the iCurrentTime clear to sqlite3_reset(), so that commenting out the iCurrentTime reset in sqlite3_step() would cause the behavior you describe. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] racing with date('now') (was: Select with dates)
Nice fix Richard, and it works as you have documented.
With the added [cebd6fc551] to reset iCurrentTime when the statement is reset,
my testing indicates that if the p->iCurrentTime=0; added at line 570 of vdbe.c
by the original patch [daf6ba413c] (change fragment [56e648f5ba9a9181]) is
removed, then 'now' is stable for the statement rather than just each step.
That is, 'now' is stable from its first "access" in a statement until that
statement is reset. Unless you have found that it causes other issues (I
haven't found any yet) then removing the above mentioned clearing of
iCurrentTime during each step will result in SQLite's concept of 'now' being
consistent with that implemented in other SQL engines and in the standard.
Personally, I think that 'now' should be constant for the duration of a
transaction across all statements executed within the same transaction --
however that is not what the standard says -- the standard only says that 'now'
be stable for the duration of the execution of a single statement which appears
to have been met if the aforesaid clearing of iCurrentTime in vdbe.c is removed.
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Monday, 16 September, 2013 08:38
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] racing with date('now') (was: Select with dates)
>
> On Mon, Sep 16, 2013 at 10:21 AM, Ryan Johnson
> wrote:
>
> > Rhetorical question: if sqlite3's behavior were tightened up would
> anybody
> > complain? Is there any possible use case where replacing the current
> > random-ish behavior with something consistent would change an
> application?
> > Seems like the requested behavior happens on accident often enough
> that no
> > current application could rely on its failure to appear.
> >
>
> There are perhaps 2 million applications in the wild that use SQLite, so
> it
> will be difficult to check them all. But one can easily imagine that one
> or
> more of those two million applications does something like this:
>
> SELECT current_timestamp, * FROM tab;
>
> And then runs sqlite3_step() every five or ten seconds in a background
> process to fetch a new row, and expects the timestamp on each row to
> reflect the actual time of retrieval from disk. Causing 'now' to mean
> exactly the same time for an entire SQL statement would break such
> applications.
>
> As a compromise, the current SQLite trunk causes 'now' to be exactly the
> same for all date and time functions within a single sqlite3_step()
> call.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Any tool to create erd from sqlite database?
Don't forget about ODBC tools... Just use ta SQLite ODBC driver... From: dd To: General Discussion of SQLite Database Sent: Friday, September 20, 2013 8:11 AM Subject: [sqlite] Any tool to create erd from sqlite database? I am looking for tool which generates er diagrams from existing database. Any suggetions? Thanks in advance. ___ 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] Any tool to create erd from sqlite database?
I am looking for tool which generates er diagrams from existing database. Any suggetions? Thanks in advance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
