Re: [sqlite] schema_version and Vacuum or Backup API

> On 8/09/2016, at 3:36 PM, David Empson wrote: > > >> On 8/09/2016, at 3:23 PM, Rowan Worth wrote: >> >> We recently made sqlite's logging more visible in our application and we're >> seeing a lot more "schema has changed" warnings than I'd expect,

Re: [sqlite] schema_version and Vacuum or Backup API

> On 8/09/2016, at 3:23 PM, Rowan Worth wrote: > > We recently made sqlite's logging more visible in our application and we're > seeing a lot more "schema has changed" warnings than I'd expect, since our > app pretty much doesn't touch the schema except during DB creation. I

Re: [sqlite] schema_version and Vacuum or Backup API

On 6 September 2016 at 20:04, Clemens Ladisch wrote: > Stephen Chrzanowski wrote: > > [...] I'm relying on the results from "pragma > > schema_version". I've noticed that this bumps up every time I run a > vacuum > > or use the backup API against the database. Is this

Re: [sqlite] Clarification on "No Isolation On Same Db Connection"

On 9/7/2016 6:11 PM, Stephan Mueller wrote: I understand that a way to ensure "SELECT is unperturbed" semantics is to use separate connections for SELECT and updates. If you go down that route, make sure you are using WAL journaling mode; it won't work otherwise. This is undesirable since

Re: [sqlite] Clarification on "No Isolation On Same Db Connection"

There is only cursor stability level isolation between queries on the same connection. The query is executed as a descending series of loops. There is a "current record" pointer based on the location of the "current record" in the query for each table. If you modify an entry, that

Re: [sqlite] Problem with rename table

Please reply if you sent this. Thanks. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, September 7, 2016 7:49 PM To: SQLite mailing list Subject: Re: [sqlite]

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Please reply if you sent this. Thanks. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Chris Locke Sent: Wednesday, September 7, 2016 12:12 PM To: SQLite mailing list Subject: Re: [sqlite]

Re: [sqlite] Clarification on "No Isolation On Same Db Connection"

Please reply if you sent this. Thanks. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Stephan Mueller Sent: Wednesday, September 7, 2016 6:12 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Clarification on "No

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

Please reply if you sent this. Thanks. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Chris Locke Sent: Tuesday, September 6, 2016 8:54 AM To: SQLite mailing list Subject: Re: [sqlite]

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Please reply if you sent this. Thanks. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Laura BERGOENS Sent: Wednesday, September 7, 2016 11:15 AM To: SQLite mailing list Subject: Re:

Re: [sqlite] Problem with rename table

Please reply if you sent this. Thanks. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Radovan Antloga Sent: Tuesday, September 6, 2016 9:37 AM To: SQLite mailing list Subject: [sqlite]

Re: [sqlite] Problem with rename table

On 9/6/16, Radovan Antloga wrote: > Hi Richard ! > > I can't find a solution how to fix my database after > I have renamed table DOKUMENTI to DOKUMENTI2. > Table DOKUMENTI had trigger dokumenti_trigger1 > and after renaming table I cant execute any sql. I forgot > to

[sqlite] Clarification on "No Isolation On Same Db Connection"

Hello all; I have a scenario that is well covered by the text at https://www.sqlite.org/isolation.html in the section "No Isolation Between Operations On The Same Database Connection" (relevant paragraphs at end for convenience) but am hoping for a bit more clarity. Here's an overview of my

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

>First of all, I'll check all the pragmas and stuff, plus the version of > SQliteBrowser (former DB Browser for SQlite indeed) I'm using, etc. Just to confirm (as it seems to be overlooked) that SQLite Browser (actually now called DB Browser for SQLite, rather than formerly...) is a 3rd party

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Ok I didn't know abount ANALYZE existence, sorry for that. I just run ANALYZE then the query again in sqlite3.exe and this time it took like half a second. Should I close and re open the DB, cause maybe this result was influenced with some cache system or something? Plus, I ran PRAGMA

[sqlite] Problem with rename table

Hi Richard ! I can't find a solution how to fix my database after I have renamed table DOKUMENTI to DOKUMENTI2. Table DOKUMENTI had trigger dokumenti_trigger1 and after renaming table I cant execute any sql. I forgot to drop trigger first. So now I always get error: malformed database schema

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

Makes perfect sense. Thanks. Chris On Tue, Sep 6, 2016 at 12:26 PM, Richard Hipp wrote: > On 9/6/16, Chris Locke wrote: > > When reducing the size of the browser (on Chrome desktop at least) the > '*** > > DRAFT ***' tag disappears. Rather than

Re: [sqlite] Best way to "split" a large DB into two DBs

On Wed, 7 Sep 2016 18:08:50 +0200 Dominique Devienne wrote: > Imagine there's a 25GB SQLite DB file in version v1 of an application. > > Because the way this DB is structured, with large to very large > blobs, this leads to problems for the app. That DB has 35 tables, but >

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

On Sep 7, 2016, at 11:02 AM, Richard Hipp wrote: > > On 9/7/16, dmp wrote: > >> The draft site looks and works fine, is fast for loads, doesn't >> seem to require scripting, GOOD! > > Sorry to disappoint, but some pages (ex: >

Re: [sqlite] Best way to "split" a large DB into two DBs

On 09/08/2016 12:57 AM, Clay Gerrard wrote: On Wed, Sep 7, 2016 at 10:08 AM, Dan Kennedy wrote: [...] then attach it and your main db to the same handle so that you can use an "INSERT INTO ... SELECT ..." statement Can you elaborate or provide any reference materials

Re: [sqlite] Best way to "split" a large DB into two DBs

On 7 Sep 2016, at 6:57pm, Clay Gerrard wrote: > Can you elaborate or provide any reference materials for this "attach it > and your main db to the same handle" procedure? Simon. ___

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Just a reminder that the automatic_index pragma does not create permanent indexes, it's for places where SQLite feels that "hey, if I take the time to create this temporary index then it'll save me more than that time during the rest of this one query." Those indexes get wiped at the end of the

Re: [sqlite] Best way to "split" a large DB into two DBs

On Wed, Sep 7, 2016 at 10:08 AM, Dan Kennedy wrote: > > [...] then attach it and your main db to the same handle so that you can > use an "INSERT INTO ... SELECT ..." statement Can you elaborate or provide any reference materials for this "attach it and your main db to

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

I haven't gone through the previous emails to see whether anyone has proposed this, but it seems possible to me that the GUI tool (oriented as it is to interaction) is not waiting for the query to finish before displaying the first results. On Wednesday, September 7, 2016 7:21 AM, Laura

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 7 Sep 2016, at 4:36pm, Laura BERGOENS wrote: > I've put ANALYZE in my code before running this SELECT query, I have no > change, and now on sqlite3.exe it runs kind of fast every time, even if i > close and re open the DB, without ANALYZE statement. Good. If

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

quote : "Is there a reason why you can't create a copy of the database and work with a copy?" I never said I couldn't, and if I misled you I'm sorry. Of course I can play with the DB, since I'm in a dev environment, and I'm open to any alteration on the tables. I conceed I'm not giving away a

Re: [sqlite] Best way to "split" a large DB into two DBs

On 09/07/2016 11:08 PM, Dominique Devienne wrote: Imagine there's a 25GB SQLite DB file in version v1 of an application. Because the way this DB is structured, with large to very large blobs, this leads to problems for the app. That DB has 35 tables, but 5 of those represent 95% of the DB size

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

On 9/7/16, dmp wrote: > The draft site looks and works fine, is fast for loads, doesn't > seem to require scripting, GOOD! > Sorry to disappoint, but some pages (ex: https://www.sqlite.org/draft/c3ref/funclist.html) use JS to split long lists into multiple

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi, Laura, On Wed, Sep 7, 2016 at 12:25 PM, Laura BERGOENS wrote: > Hi Dominique, > > I guess it's not, but I'm not quite sure on what I can share, I'm a newbie > in a professional environment. I would be more confortable if I ask my boss > first. In the meantime, all

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

I'm not interested in mobile browsing, just that the site is still acceptable after changes for desktop. I use unconventional browsers included one of my own design. The draft site looks and works fine, is fast for loads, doesn't seem to require scripting, GOOD! danap.

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi Dominique, I guess it's not, but I'm not quite sure on what I can share, I'm a newbie in a professional environment. I would be more confortable if I ask my boss first. In the meantime, all I can say is that there are no primary keys nor foreign keys in the table, and no indexes at all. I

Re: [sqlite] Best way to "split" a large DB into two DBs

What are the business logic reasons you want to split the database? Is your application going to be able to handle dealing with data that'd exist in multiple places? When I know I'm going to be putting large blobs within a database, those blob tables have two fields, one ID, and one for the

[sqlite] Best way to "split" a large DB into two DBs

Imagine there's a 25GB SQLite DB file in version v1 of an application. Because the way this DB is structured, with large to very large blobs, this leads to problems for the app. That DB has 35 tables, but 5 of those represent 95% of the DB size (because of those blobs). So for v2 of the app,

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On Wed, Sep 7, 2016 at 6:00 PM, Laura BERGOENS wrote: > Then I create real tables in the in-memory clone that contain the content > of the views : INSERT INTO tableA SELECT * from viewA > What matters is how you create the tables, and in particular what primary key

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi Igor, I'm working on providing those data, meanwhile I have to emphasize on how those tables are created, maybe there is something here : The idea is that i run the application using a given database. I want this database to be untouched as for its data, so i'm making a clone of it in memory.

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi, Laura, On Wed, Sep 7, 2016 at 10:48 AM, Laura BERGOENS wrote: > Note : I had some real queries that use to take 100 seconds to execute, and > I optimized them myself. > It looks like this : > > I have 4 tables: tableA, tableLink, tableC and tableD > tableA, Link

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

I've put ANALYZE in my code before running this SELECT query, I have no change, and now on sqlite3.exe it runs kind of fast every time, even if i close and re open the DB, without ANALYZE statement. 2016-09-07 17:15 GMT+02:00 Laura BERGOENS : > Ok I didn't know abount

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 7 Sep 2016, at 3:48pm, Laura BERGOENS wrote: > This query takes 100 seconds approx. Once your tables have some convincing data in (does not need to be final data, just something useful to see how the values are distributed), run "ANALYZE", just once. It might

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Note : I had some real queries that use to take 100 seconds to execute, and I optimized them myself. It looks like this : I have 4 tables: tableA, tableLink, tableC and tableD tableA, Link and C have no more than 10k rows in it, and tableD around 50k (which is not big at all right?) The query

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi Mr. Smith, Quote :"(though, SQLite successive iterations usually become faster, not slower, apart from compile-time options that may be different)." That's what concerns me as well, but since I'm a recent user of SQlite type database and sqlite3 I prefer blaming me for those differences.

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 2016/09/07 4:20 PM, Laura BERGOENS wrote: Hi Mr. Slavin, As for why the query takes so long, I do know the answer ! Long story short, my application does a lot of calculation and things, I don't want to get into the details here, but queries are built and auto-generated piece by piece.

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 2016/09/07 2:53 PM, Laura BERGOENS wrote: Hello everyone, I tried to get some answers using the mailing list archives, but you guys have been communicating so much on this, it's hard to run through everything that was written ;) Basically, I'm using sqlite3 in my C application. I believe

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi Mr. Slavin, As for why the query takes so long, I do know the answer ! Long story short, my application does a lot of calculation and things, I don't want to get into the details here, but queries are built and auto-generated piece by piece. Therefore, sometimes the queries aren't optimized at

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 9/7/16, Simon Slavin wrote: > > On 7 Sep 2016, at 3:10pm, Laura BERGOENS wrote: > >> I guess I don't have further question regarding my issue, since I have the >> same time execution as with the sqlite3.exe tool, so there are no problems >>

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 7 Sep 2016, at 3:10pm, Laura BERGOENS wrote: > I guess I don't have further question regarding my issue, since I have the > same time execution as with the sqlite3.exe tool, so there are no problems > with my pragmas or whatsoever. Nevertheless, 100 seconds is a

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi Mr. Hipp, First of all thanks for replying. As you recommend, I downloaded the sqlite3 executable (I'm on Debian). Just out of curiosity, I've ran both the "SELECT sqlite_source_id()" query and a long query i have on 3 systems, SQliteBrowser, sqlite3.exe and my app, here is what i get, it's...

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 9/7/16, Laura BERGOENS wrote: > Hello everyone, > > I tried to get some answers using the mailing list archives, but you guys > have been communicating so much on this, it's hard to run through > everything that was written ;) > > Basically, I'm using sqlite3 in my C

[sqlite] Query time execution difference between my application and SQLiteBrowser

Hello everyone, I tried to get some answers using the mailing list archives, but you guys have been communicating so much on this, it's hard to run through everything that was written ;) Basically, I'm using sqlite3 in my C application. I believe the tool SQLiteBrowser is using it as well. I

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

On Mon, Sep 5, 2016 at 10:55 PM, Richard Hipp wrote: > Most of the world views the internet on their phone now, I am told, > and websites are suppose to be "responsive", meaning that they > reformat themselves to be attractive and useful for the majority who > view them through