Re: [sqlite] UPDATE statement without FROM clause

On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps wrote: > Can't the same update be done more efficiently with a CTE? The command inside the WITH has to be a SELECT command. I wonder if there's a good reason for that. If the command inside WITH could make changes to

Re: [sqlite] [BugReport]Data changes cannot actually flush to disk

On 7 Jun 2016, at 8:13am, 刘翔 wrote: > Problem: > When update database and power off immediately, How soon do you turn the power off ? Two seconds ? Ten seconds ? One minute ? What type of hard disk do you have ? Is it a rotating disk or a solid state drive ? Is

Re: [sqlite] Latin-1 characters cannot be supported for Unicode

On 7 Jun 2016, at 8:43am, Wang, Wei wrote: > Then I opened the database with SQLite Developer. SQLite Developer is not supported by the team which wrote SQLite. It's just a program which uses SQLite. If it allows you to pick character encoding then it is not correctly

[sqlite] libsqlfs - a file system in a SQLite database

"The libsqlfs library implements a POSIX style file system on top of an SQLite database. It allows applications to have access to a full read/write file system in a single file, complete with its own file hierarchy and name space. This is useful for

Re: [sqlite] Long lived prepared statements

On 4 Oct 2014, at 10:08pm, Jim Dodgen wrote: > What I do is to prepare a relatively large and complex query. Then I run > the same query forever never doing a finalize. My assumption is I will > have no memory leakage. You can do _step() and _reset() and _bind_() as many times

Re: [sqlite] Long lived prepared statements

On 4 Oct 2014, at 11:16pm, Jim Dodgen wrote: > It might be I need more of a Perl DBI question Whoops. Yes, my answer was geared to users of the C API and thin shims to it. I have no idea how Perl DBI works. Sorry about that, and I hope you can get a response from a Perl

Re: [sqlite] Detecting multiple CHECK failures

On 7 Oct 2014, at 10:00pm, Peter Haworth wrote: > I'm a great believer in using CHECK constraints to do as much validation as > possible within the database rather than code it in my application. > > However, I think I'm right in saying that as soon as a CHECK constraint >

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

On 8 Oct 2014, at 5:14am, Stephen Chrzanowski wrote: > The one downside I just realized is that ON CONFLICT can be used outside of > the table declarations as well, so perhaps a different word or signal might > be needed for it to make linguistic sense, or, this version of

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

On 8 Oct 2014, at 1:22pm, Stephen Chrzanowski wrote: > Can one not also put a constraint up on the field to say that the field can > only be of a certain value, kind of to emulate ENUM? Sure you can. You can do anything expressible in SQL which turns into a BOOLEAN

Re: [sqlite] In python, determine database status

On 8 Oct 2014, at 10:45pm, Mark Halegua wrote: > I think my problem is I've opened the database in different modules for > different views of the > data (there are six tables, one of which relates to two/three others, another > which relates to > one other) and

Re: [sqlite] Single large table vs. several smaller tables

On 9 Oct 2014, at 3:25pm, Drago, William @ MWG - NARDAEAST wrote: > The question I have is, should I lump everything together in one table just > like the .csv file or should I create several smaller tables that group > similar parameters? I'm not sure what would

Re: [sqlite] Will someone be able to explain this weird outcome...

On 10 Oct 2014, at 9:27pm, to...@acm.org wrote: > sqlite> select "7,915" - "5,021"; > 2 > > But, would someone explain the result of 2? Sorry for this child-like > question, but I can't find the how the result of 2 came to be displayed. 7 - 5 = 2 Simon.

Re: [sqlite] following a trail of references

On 12 Oct 2014, at 9:03pm, Paul Sanderson wrote: > Is this possible just using SQL select type statements? I don't know how to do it but I bet you can do it with a recursive WITH: Simon.

Re: [sqlite] (no subject)

On 13 Oct 2014, at 12:06pm, Rohit Kaushal wrote: > please unregister me Only you can stop forest fires. See the link at the bottom of every post to this list. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Performance issue when copying data from one sqlite database to another

On 14 Oct 2014, at 10:24pm, Pontus Bergsten wrote: > INSERT INTO Dest.TheTable (field1, ..., fieldN) FROM Main.TheTable WHERE time > BETWEEN t1 AND t2 > And here is the performance problem: When the application is executed on > Windows on a desktop computer, the

Re: [sqlite] UPDATE Help

On 15 Oct 2014, at 1:10am, Shantanu Namjoshi wrote: > ALTER TABLE dailydelete ADD COLUMN SMB varchar(11); If you find yourself doing things like this your schema is messed up. SMB is obviously data, not a column. Redesign your table so that HML, SMB,

Re: [sqlite] sqlite Query Optimizer

On 15 Oct 2014, at 12:54pm, Prakash Premkumar wrote: > I'm trying to understand the sqlite select query optimizer. It works by > assigning costs to each relation in FROM clause. That is only a little bit of how it works. Have you read these ?

Re: [sqlite] sqlite Query Optimizer

On 16 Oct 2014, at 7:50am, Prakash Premkumar wrote: > Does sqlite implement the pointers in the System R Algorithm ? SQLite does not implement the System R Algorithm, so no, it doesn't implement the pointers from System R. It implements the algorithms described in the

Re: [sqlite] sqlite Query Optimizer

On 16 Oct 2014, at 11:07am, Prakash Premkumar wrote: > Are there any projects where , sqlite optimizer has been extended to add > System R The optimizer currently built into SQLite does the same job as what you're thinking of as "System R". It is not possible to use

Re: [sqlite] Inmemory database in sqlite

On 17 Oct 2014, at 10:39am, Prakash Premkumar wrote: > So the approach where we open the db with ":memory:" keyword does not > provide durability . > Only by increasing the cache size can we make db act as an inmemory db with > durablity. That's the conclusion right ?

Re: [sqlite] 'INTEGER PRIMARY KEY' start value

On 19 Oct 2014, at 2:27pm, Baruch Burstein wrote: > Is the rowid/'INTEGER PRIMARY KEY' field that is not entered manually > guaranteed to start from 1? Or at least from a positive number? See the section 'Background' in for the

Re: [sqlite] Data error

On 18 Oct 2014, at 6:25am, aaquib Khan wrote: > i have encountered a strange issue lately, i am using sqllite in my windows > application. The logic in application is, at the start of the application > db file is created, and it is deleted when the application is closed. Now

Re: [sqlite] Question on locks

On 18 Oct 2014, at 1:32pm, Ali Jawad wrote: > I do have one sqlite DB, with multiple dbs, and multiple scripts writing to > those tables, at one time only one script writes to one table, I.e. there > is no simultaneous read/write access to one table at one time. However, I

Re: [sqlite] Question on locks

On 20 Oct 2014, at 1:15am, Ali Jawad wrote: > Thanks, I hope it works, I hate to use MySQL for this project. Pardon my > ignorance but the example at hand below is for C, any ideas on how to set > for Bash and PHP ? Or better yet globally for sqlite3 There's a PRAGMA which

Re: [sqlite] Regression with sqlite 3.8.7

On 23 Oct 2014, at 4:55pm, Luigi Iemma wrote: > When I run this query on 3.8.5 it takes 0.126 seconds, > when I run this query on 3.8.7 it takes 17.37 seconds Can you do an ANALYZE then try it again ? Simon. ___ sqlite-users

Re: [sqlite] quasi-bug related to locking, and attached databases....

On 24 Oct 2014, at 9:54pm, dave wrote: > Can locking be made more clever to know about aux being an alias > for main, and effectively translate the query shown to it's functional > equivalent of: >insert or replace into main.dest ( name, value ) values ('allow',(select

Re: [sqlite] quasi-bug related to locking, and attached databases....

On 24 Oct 2014, at 10:40pm, dave wrote: > Later I can try on linux, but I don't have it at my fingertips just now. I > don't mind trying with other versions of sqlite if you think it's helpful, > but I suspect it's been there forever. Not gonna ask for this since you've

Re: [sqlite] Question on locks

On 25 Oct 2014, at 3:31pm, Ali Jawad wrote: > bash script > > sqlite3 websites.db "PRAGMA busy_timeout=1500;CREATE TABLE [$SITE] (DATE > INT ,EU INT , US INT);" Creating and destroying tables always involves a long lock. > php script > > $ret = $db->query("PRAGMA

Re: [sqlite] Question on locks

On 25 Oct 2014, at 7:16pm, Ali Jawad wrote: > Thanks Simon, the create process is a one off. As for the table name I did > use this approach as to not accumulate too much data in one table and > instead split the data in multiple tables. From a design POV in sqlite is >

Re: [sqlite] Performing a SELECT on an enormous database...

On 25 Oct 2014, at 9:07pm, Ross Altman wrote: > Thanks for all the responses. The small integer column H11 comes before the > large string column NVERTS, so doesn't that mean SQLite is only loading the > minimum required while filtering? If that's the case then I don't

Re: [sqlite] unique with icu

On 26 Oct 2014, at 6:00am, dd wrote: > Application using sqlite database without icu extension. I am planning to > add icu extension. for schema, add new column and index with lower. > > Is it safe to add icu for existing db's? Yes. But once you've added it and used it

Re: [sqlite] Question on locks

On 26 Oct 2014, at 9:27am, Ali Jawad wrote: > right now this is all about the write process to > the database. 4 scripts run simultaneously, writing 500 entries each > through a while loop to 500 tables each every 10 minutes. > > The relevant part is here > > sqlite3

Re: [sqlite] [RFE bug] Improve error reporting for foreign keys

On 27 Oct 2014, at 8:17am, Clemens Ladisch wrote: > Tristan Van Berkom wrote: >> locateFKeyIndex() function issuing the not-so-informative >> message "foreign key mismatch" [...] >> >> o When foreign keys are enabled at CREATE TABLE time, it would >>be very helpful at

Re: [sqlite] quasi-bug related to locking, and attached databases....

On 27 Oct 2014, at 8:43am, Stephan Beal wrote: > - a couple months back Simon suggested ATTACHing the db to itself so that > we can effectively alias "main" to the well-known name we have specified > for that db instance. It worked like a charm until Dave discovered this

Re: [sqlite] Circular References [Was: Re: [RFE bug] Improve error reporting for foreign keys]

On 27 Oct 2014, at 9:11am, Tristan Van Berkom wrote: > This seems to me to be an elegant solution, but I'd be curious to > know if it would be considered badly designed for some reason, and > would be interested to know what kind of alternatives people would > propose.

Re: [sqlite] quasi-bug related to locking, and attached databases....

On 27 Oct 2014, at 9:49pm, Nico Williams wrote: > On Mon, Oct 27, 2014 at 3:17 PM, Stephan Beal wrote: >> That's conceptually the same problem we're trying to solve here: keep the >> public db names stable, regardless of where/how they're actually

Re: [sqlite] Ordering of group_concat values using subselect in 3.8.7

On 28 Oct 2014, at 7:33pm, James Earl wrote: > After upgrading from SQLite 3.8.6 to 3.8.7, I am no longer able to > order group_concat values by using a subselect. Sorry, but as the documentation says "The order of the

Re: [sqlite] problem trigger

On 28 Oct 2014, at 8:06pm, pablo Van wrote: > WHEN new.DiaHs_Inicio not between (old.DiaHs_Inicio and old.DiaHs_Fin) I do not think SQLite supports NOT BETWEEN. I would change it to something like WHEN (new.DiaHs_Inicio < old.DiaHs_Inicio) OR (new.DiaHs_Inicio >

Re: [sqlite] Database is locked exceptions

On 29 Oct 2014, at 4:13pm, Mike McWhinney wrote: > "URI=file:mydb.db; default timeout=10; Pooling=True; Max Pool Size=100;"; Can you please change your timeout to 1 (really, 1ms == 10 seconds) and see if this makes the problems go away ? It may not be necessary

Re: [sqlite] New kids on block the SQLite Master Suite.

On 29 Oct 2014, at 7:46pm, Billy Huynh wrote: > Please check it out by visiting http://www.aimtelligentsw.com? A few quick tips: * Say right up front which operating systems your application runs on. * What's Windows 2003 ? * On the download page, explain what

Re: [sqlite] Fw: Database is locked exceptions

On 30 Oct 2014, at 4:13pm, Mike McWhinney wrote: > I have continued to receive the locking erros. The latest connection string I > have as follows: > > public static string OMconnectionString = "URI=file:oslermedicine.db; busy > timeout=1; Pooling=True; Max Pool

Re: [sqlite] sqlite_column_table_name() and table alias name

> On 31 Oct 2014, at 12:30pm, Jose F. Gimenez wrote: > > SELECT a, b, table2.c, alias.c > FROM table1 > LEFT JOIN table2 ON ... > LEFT JOIN table2 AS alias ON ... > > the API sqlite_column_table_name() applied to both columns 3 and 4 returns > . I know that belongs to

Re: [sqlite] Stuck and drowning trying to work out some SQL.

On 1 Nov 2014, at 10:19am, Rob Willett wrote: > I struggle when I try to pull it all together so that I have one piece of SQL > that does all the work. You want to identify the first Active and the first Closed for each Disruption_id. Your biggest problem is

Re: [sqlite] sqlite_column_table_name() and table alias name

On 1 Nov 2014, at 12:29pm, Jose F. Gimenez wrote: > But if I need the column's fullname, I get: > > messages.subject, people.name, people.name > > And yes, I know that I can specify an alias for those columns which could be > ambiguous. That is "sender.name AS

Re: [sqlite] sqlite_column_table_name() and table alias name

On 2 Nov 2014, at 6:28pm, Jose F. Gimenez wrote: > If you develop bussiness software, where you know exactly the querys because > you are writing them, then there is no problem at all. But other kind of > software, like a database manager, need all available metadata.

Re: [sqlite] Index without backing table

On 3 Nov 2014, at 9:50am, Paul wrote: > So, to be clear, WITHOUT ROWID table will have it's PRIMARY KEY > as a replacement for ROWID and table itself is an index? It would appear that the answer is "yes". I'm not going to go beyond the official documentation at

Re: [sqlite] Random locking errors using Sqlite.NET

On 4 Nov 2014, at 5:45pm, Mike McWhinney wrote: > I am getting random locking errors during the execution of my program. The > program resides on a network and each client has a mapped share > with full read/write access to the folder as well as the .db file used by >

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

On 5 Nov 2014, at 12:13pm, vita...@yourcmc.ru wrote: > Which is of course very slow. Can you please run ANALYZE then try the plans again ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQLite as a meta database

On 5 Nov 2014, at 9:28pm, Edward Lau wrote: > Maybe some time in the future a version 4 be started that incorporates many > new advancement in the industry. Version 3 can still be continued for > backwards compatibility and version 4 will break some but set the stage for >

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

On 5 Nov 2014, at 10:05pm, nicolas riesch wrote: > Even if the user writes a Go program with only one logical thread, he has > no control about which OS thread will process a function call. > > This means that EACH SUCCESSIVE function in the sequence above can be

Re: [sqlite] Is it a bug ?

On 6 Nov 2014, at 3:13am, Andrei Yakimov wrote: > Problem is incorrect journal file, which is created on system reboot. > Reproduce this condition relativity simple: > > step 1: we open db > step 2: write/update something to db. > step 3: switch journal to memory > step 4:

Re: [sqlite] Bug report: typo in sqlite3.h

On 7 Nov 2014, at 9:18am, Philip Newton wrote: > The amalgamation-3080701 sqlite3.h file has a comment that reads, in part: > > ** These no-op macros are used in front of interfaces to mark those > ** interfaces as either deprecated or experimental. New applications >

Re: [sqlite] Is it safe to use backup at shell level when an application is running

On 7 Nov 2014, at 10:33am, Yves Crespin wrote: > We use sqlite3_open_v2() with > SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX flags and all the > transactions are exclusive. > > > So, if we add a SQLITE_BUSY handle, can we use the sqlite3 .backup > when the

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On 9 Nov 2014, at 1:49pm, Tristan Van Berkom wrote: > This year in particular I've been faced with my first queries of modest > complexity, see for example this (temporary) paste: > >http://www.fpaste.org/148918/41545194/ > > This is the beginnings of a query

Re: [sqlite] SQLite where clause tree

On 10 Nov 2014, at 9:40am, Prakash Premkumar wrote: > If I am constructing this tree for where clause by myself, should i take > the operator precedence in to account while constructing it or will sqlite > take care of precedence,given any tree (i.e constructing it with

Re: [sqlite] Virtual memory management of Sqlite core on Windows Mobile 6.5 with .NET CF 3.5

On 10 Nov 2014, at 3:45pm, Pavlo wrote: > Total Virtual memory allocations size made by Sqlite core seems to grow in > time in application process even though Sqlite connection get closed on a > regular basis. > For example if we do search touching several tables

Re: [sqlite] Virtual memory management of Sqlite core on Windows Mobile 6.5 with .NET CF 3.5

On 10 Nov 2014, at 4:45pm, Pavlo wrote: > That main problem is that we are working with managed code and .net wrapper > packaged in System.Data.Sqlite :) I apologise that I missed this. Fortunately we have several readers to the list who understand that

Re: [sqlite] Locking errors on network

On 10 Nov 2014, at 6:22pm, Mike McWhinney wrote: > So SQLite shouldn't be used at all on a network? SQLite is not designed for hosting a database on a server for access by lots of different computers at the same time. To do that efficiently you need a client/server

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

On 10 Nov 2014, at 10:55pm, RP McMurphy wrote: >> > > Okay, for my simplified example analyze does improve the times. But for our > application this does not help, and it also harmed a couple of other queries. If you can provide

Re: [sqlite] Triggers and CTE's

On 11 Nov 2014, at 11:15pm, Ben Newberg wrote: > WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks > limit 10) > INSERT INTO zWeeks (Week) select wk from Weeks; Just use 10 INSERT commands. I don't know what's causing your error message, but your

Re: [sqlite] Triggers and CTE's

On 11 Nov 2014, at 11:59pm, Ben Newberg wrote: > The 10 is just an arbitrary value I chose for this example. The user > actually determines the value at run-time, so this value could be any > integer. I have a way to settle that, if only I could figure out how I can > get

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

On 13 Nov 2014, at 12:23pm, Dinesh Navsupe wrote: > Does any of SQLite data Type support 23,10 precision format for Number? > > If yes, could you pleas help with right data type or approach to achieve > this. SQL stores REAL numbers in a REAL field which conforms to

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

On 13 Nov 2014, at 1:01pm, Dinesh Navsupe wrote: > My need is 23 decimal digits of precision. We work on complex payout > calculation engine where in formula outputs are quite large numbers and > clients do not want to round off. If you're working with floating-point

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

On 13 Nov 2014, at 3:44pm, Dominique Devienne <ddevie...@gmail.com> wrote: > On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin <slav...@bigfraud.org> wrote: > >> 100,000,000,000,000,000,000 > > Assuming he means Oracle's NUMBER(23, 10), and given [1], that's more >

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

On 14 Nov 2014, at 3:42am, James K. Lowden <jklow...@schemamania.org> wrote: > Simon Slavin <slav...@bigfraud.org> wrote: > >> I'm not aware of >> any usable libraries which actually support 23,10 outside the world >> of physics. > > http://www.mp

Re: [sqlite] sqlite Issue

On 14 Nov 2014, at 10:40am, ARVIND KUMAR wrote: > I am new for SQLite. I am trying to create database. But its not creating. > I have attached the screenshot. Please find and do needful. You cannot post screenshots to this mailing list. Most problems with creating a

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

On 14 Nov 2014, at 2:32pm, RP McMurphy wrote: > After we run analyze and then > let the process run for a while the DB > contents change - and it can change quite > considerably depending > upon what is > happening > > I suspect that the analyze data gets stale, but > I

Re: [sqlite] Student's t-test table

On 16 Nov 2014, at 7:39am, Giuseppe Costanzi wrote: > do you know if a database exists in sqlite with the values, degrees of > fredom vs probability (alfa) of the t Student? If you can find one in text or .csv format, we can tell you how to import it. Simon.

Re: [sqlite] Column name as a variable

On 17 Nov 2014, at 10:55am, Paul Sanderson wrote: > Is it possible to get a row count for each of the tables in a database > using a SQL query. > > i.e. > > is there a way I could use each row in sqlite_master and use > table_name to somehow do a select count(*)

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

On 17 Nov 2014, at 12:48pm, RP McMurphy wrote: > Upon further analysis it appears that the data "shape" is different in > different periods within the table. That is, some sections have the inverse > shape to other sections. So it looked like query times would change over

Re: [sqlite] Column name as a variable

On 18 Nov 2014, at 8:46am, Paul Sanderson wrote: > my requirement, which is using a > table name as a variable This is deliberately made very difficult in SQL. I think it's for security reasons. Simon. ___

Re: [sqlite] automatic index on sqlite_sq_#######

On 20 Nov 2014, at 7:45am, Yongil Jang wrote: > I've found that following log string when using sub-query. > > "automatic index on sqlite_sq_9F222470(STAT_DATA_ID)" This is not output by SQLite. It is output by a program which uses SQLite. It may indicate normal

Re: [sqlite] Why is a b-tree sort required for this query?

On 20 Nov 2014, at 9:48pm, Oliver Smith wrote: > The t2c table has an index on id, name; I expected it would use that index so > that the data would be naturally in order. As you've found, you cannot rely on this. If you need an answer to a query to be in a specific order,

[sqlite] Testing the 'I' in ACID

The 'I' in ACID stands for 'isolation'. In SQLite terms it means that a change made in one transaction doesn't affect another until the transaction is committed, at which point it does affect the database and transactions made afterwards. SQLite doesn't support massive concurrency because it

Re: [sqlite] Testing the 'I' in ACID

On 25 Nov 2014, at 10:39pm, Igor Tandetnik <i...@tandetnik.org> wrote: > On 11/25/2014 5:32 PM, Simon Slavin wrote: >> SQLite doesn't support massive concurrency because it locks the entire >> database during changes. > > Not entirely true. WAL mode allows one

Re: [sqlite] Network and concurrency

On 26 Nov 2014, at 4:20pm, Mike McWhinney wrote: > Are there any other PRAGMA or connection string > statements that can help with this concurrency issue? If you haven't set a busy timeout then SQlite won't retry when there is a network clash, it will immediately return

Re: [sqlite] "database is locked" for SQLITE_BUSY

On 3 Dec 2014, at 2:20pm, Stephen Chrzanowski wrote: > Although I think there is already an error result, one situation might be > when the DB is in a read only state. I just thought of the database /file/ being marked 'read-only'. But it turns out that there's a

Re: [sqlite] "database is locked" for SQLITE_BUSY

On 3 Dec 2014, at 3:10pm, Hick Gunter wrote: > SQLITE_BUSY means that some connection is BUSY with a write transaction and > has locked the database file; presumably, it will be possible to write to the > database when the current writer has finished, just not now or within

Re: [sqlite] "database is locked" for SQLITE_BUSY

On 3 Dec 2014, at 3:20pm, Richard Hipp wrote: > https://www.sqlite.org/rescode.html#busy Thanks, Richard. I have somehow never seen that. I had no idea that the difference between _BUSY and _LOCKED was purely about whether the conflicting access was from the same

Re: [sqlite] "database is locked" for SQLITE_BUSY

On 4 Dec 2014, at 5:36pm, Jonathan Moules wrote: > Depending on the application, an end user likely won't see the error code, > but instead just the error message SQlite is not a program. It's an API, intended for use by a programmer. Those error codes should

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

On 8 Dec 2014, at 12:43am, David Barrett wrote: > Other alternatives we're considering are to fork and nice the process, or > to call ioprio_set() directly, but I'm curious if there's a simpler way to > do it. Thanks! VACUUM does the same job (in a very different way)

Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?

On 8 Dec 2014, at 2:24am, Dwight Harvey wrote: > I know very little and Databases are complex and intimidating. > > I figured out how to run queries but I don't know if they are > correct/accurate, as in what I requested from the 'RUN' results? > > How

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 8 Dec 2014, at 1:31pm, Gwendal Roué wrote: > We share the same conclusion. I even tried to decorate the update query with > "ORDER" clauses, in a foolish attempt to reverse the ordering of row updates, > and circumvent the issue. A way to solve this is to use REAL for

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 8 Dec 2014, at 3:05pm, Gwendal Roué wrote: > Why not an opt-in way to ask for deferred constraint checking. The key here > is only to allow perfectly legit requests to run. With all the due respect to > sqlite implementors and the wonderful design of sqlite. SQL-99

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

On 9 Dec 2014, at 1:36am, David Barrett wrote: > *Re: Why VACUUM.* We vacuum weekly. This particular database is a > "rolling journal" -- we are constantly adding new rows to the end of the > table, and every week we truncate off the head of the journal to only keep >

Re: [sqlite] seeking advice

On 9 Dec 2014, at 8:41pm, Rene Zaumseil wrote: > Version 3: One table with time stamp, parameter id and parameter value > - Is it working when all values change? > - Is retrieving values for one parameter fast? That one. Versions 1 & 2 will both, technically, work,

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 9 Dec 2014, at 8:57pm, Nick wrote: > Environment is Linux with multiple (c. 4-6) processes accessing a single > sqlite database named "test.db". > > Backup: > - New process started using cronjob to initiate application checkpoint until > completion. > - rsync diff the

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 10 Dec 2014, at 12:30am, Nick wrote: > That's interesting Simon I didn't expect the database not to be trustworthy. The database will be trustworthy at any instant. Your copy of it will be corrupt because the file will be changing while you are copying it. > In WAL

[sqlite] replace many rows with one

Dear folks, A little SQL question for you. The database file concerned is purely for data manipulation at the moment. I can do anything I like to it, even at the schema level, without inconveniencing anyone. I have a TABLE with about 300 million (sic.) entries in it, as follows: CREATE

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 10 Dec 2014, at 10:40pm, Nick wrote: > All the processes would have automatic checkpointing disabled. Just the > backup process would perform the checkpoint. I don't know enough about the internals of SQLite to be sure, but various parts of me are concerned that this

Re: [sqlite] How to speed up database open

On 11 Dec 2014, at 11:51am, Paul wrote: > I understand, that having them is a must for a decent performance. > In my specific case I have millions of individual database files. > This is one, among other reasons that I can't keep them open all the time. > Just too many of

Re: [sqlite] replace many rows with one

On 10 Dec 2014, at 3:40pm, RSmith wrote: > INSERT INTO s2merged SELECT a, b, sum(theCount) FROM s2 GROUP BY a,b; Thanks to Martin, Hick and R for this solution. It was just what I was looking for. > Not sure if your theCount field already contains totals or if it just

[sqlite] Counting rows

In my table which had about 300 million (sic.) rows I did this SELECT count(*) FROM myTable; to count the number of rows. After half an hour it was still processing and I had to kill it. I know that the internal structure of a table means that this number isn't simple to produce. But is

Re: [sqlite] Counting rows

On 11 Dec 2014, at 3:58pm, Paul Sanderson wrote: > would count _rowid_ from mytable be quicker Hmm. Given that these tables have the normal use of rowid, and that rows in this table are only inserted, never deleted, I wonder whether SELECT max(rowid) FROM

Re: [sqlite] Counting rows

On 11 Dec 2014, at 4:39pm, Dominique Devienne wrote: > I have a little utility that connects to Oracle, and does a big UNION ALL > query to get the counts of all my tables (82 currently): Yeah, it's easy in Oracle. The problem is that SQLite3 uses a tree to store lists,

Re: [sqlite] Online/Hot backup of WAL journalling mode database

On 12 Dec 2014, at 10:27am, Clemens Ladisch wrote: > If you write your own backup tool that simply calls > "sqlite3_backup_step(b, -1)", the entire database is copied in > a single atomic transaction. OP's problem is that he runs several processes which are constantly

Re: [sqlite] Feature Request - RowCount

On 13 Dec 2014, at 12:38pm, Richard Hipp wrote: > Also, if there are indices available, SQLite attempts to count the smallest > index (it has to guess at which is the smallest by looking at the number > and declared datatypes of the columns) and counting the smallest index >

Re: [sqlite] Feature Request - RowCount

On 13 Dec 2014, at 7:46pm, James K. Lowden wrote: > Every DB Admin tool I've ever used proved to be more hinderance than > help. They seem to be written by the moderately competent to help the > novice, and run out of gas or fall over when faced with anything >

Re: [sqlite] Feature Request - RowCount

On 14 Dec 2014, at 11:08am, Jean-Christophe Deschamps wrote: > Without using slow triggers or changing the v3 file format there is still > another possibility which could be implemented relatively easily. All it > would need is a new pragma (or internal function) like

[sqlite] '.timer on' in the shell tool

Okay. I used '.timer on' in the shell tool. SQLite 3.7.13, if it matters. Here are two sample lines I got in response to different INSERT ... SELECT commands: CPU Time: user 880.710398 sys 353.260288 CPU Time: user 5073.001124 sys 11609.266484 The two commands were issued one after another

  1   2   3   4   5   6   7   8   9   10   >