[sqlite] Performance issue

On 8 Apr 2015, at 10:16am, Jeff Roux wrote: > SELECT SUM(nbBytesDecompOut+nbBytesCompIn) as vol, portLan \ > as item FROM flows WHERE timestamp>=1383770600 AND \ > timestamp<=1384770600 AND protocol IN (17, 6) GROUP BY portLan \ > ORDER BY vol DESC LIMIT 6 If you want us to

[sqlite] Prevent database file from being overwritten by other processes

On 8 Apr 2015, at 3:51pm, R.Smith wrote: > BEGIN EXCLUSIVE - Locks the database from other SQLite3 database connections > for the time being. > COMMIT; - Unlocks it again - so calling all this in one go is pointless. > > That said, database locking serves only to protect from other database

[sqlite] sqlite 3.8.2 foreign key issue

On 9 Apr 2015, at 6:04pm, Gustav Melno wrote: > Thanks for the help. Adding a trailing underscore helped also. I'm still > wondering why insertion worked at all because defining oid as column name > with the type VARCHAR should result in an error on execution. The three names for the integer

[sqlite] Request: Metadata about C API constants and functions

On 10 Apr 2015, at 1:42am, Richard Hipp wrote: > As it happens, the SQLite documentation build process already parses > out most of this and puts it into a database already. If you > check-out the documentation sources > (https://www.sqlite.org/docsrc/timeline) and successfully build the >

[sqlite] step and reset or reset and step?

On 10 Apr 2015, at 9:35am, Paolo Bolzoni wrote: > The subject already says it all, I was wondering what is the best > practice for a prepared statement that need to be used an unknown > number of times. > > It is better to reset and step (as many times as needed) or step > and reset after? Or

[sqlite] Request: Metadata about C API constants and functions

On 10 Apr 2015, at 12:53pm, R.Smith wrote: > It doesn't matter, C API calls or pragma, whatever works best as long as > there is /some/ way to get to that info. Usually though, most C API calls > valuable to end users too, ends up having a pragma that can produce similar > output. I think

[sqlite] Sqlite 2 and Windows 7

On 10 Apr 2015, at 12:33pm, Gabriel Tiste wrote: > We can do a select on columns but not select * on affected table except the > last column. Including that in the select cause the client to say that table > is missing or logic is not correct. > > Are there any known issues with sqlite2 Are

[sqlite] Sqlite 2 and Windows 7

On 10 Apr 2015, at 1:35pm, Gabriel Tiste wrote: > Your assumption regarding versions are not accurate unfortunatly. We are > actually using a sqlite2 database(old application...). Good grief. You need a SQLite2 expert. If you can find one. Sorry I can't help. Simon.

[sqlite] Index causing very slow queries

On 10 Apr 2015, at 2:24pm, Hamish Symington wrote: > LEFT JOIN Appointment ON Appointment_TxnUUID=Txn_UUID > WHERE ( date( Appointment_StartDate ) >= trim( ? ) AND date( > Appointment_StartDate ) <= trim( ? ) ) ) The application of a function to the column values means that it

[sqlite] Sqlite 2 and Windows 7

On 10 Apr 2015, at 3:09pm, Ketil Froyn wrote: > A hex editor? Unless a page boundary is crossed, the data for one row of a table is all held together in the database file. So if you can find the value of another column in the same row, and search for that using a hex editor, you should find

[sqlite] Request: Metadata about C API constants and functions

On 10 Apr 2015, at 5:00pm, R.Smith wrote: > On 2015-04-10 04:11 PM, Richard Hipp wrote: >> https://www.sqlite.org/toc.db > > Thank you Richard! > Will this link always have the latest DB? If I may suggest ... in that database, introduce a new column which holds the version in which that call

[sqlite] JSON expressions for records and synchoronisation

A web site of no official standing features two JSON formats which might interest readers of this list. The first is for describing a table of data: It does something close to what a CREATE TABLE statement does: define fields, their types, and

[sqlite] Select query becomes distinct on where matches unique with null value

On 11 Apr 2015, at 4:13pm, Keith Medcalf wrote: > Interestingly if you run analyze, it works properly ... Oh my. I don't like the idea that ANALYZE changes the result set. Simon.

[sqlite] sqlite3 (or sqlite4) performance on NFS

On 11 Apr 2015, at 2:30pm, Peng Yu wrote: > I am wondering for my limited usage scenario, is it possible to make > sqlite3 on NFS as fast as sqlite3 on local storage (such as disable > file locking). No. Data will flow to a local disk much faster than it can flow across your network. But

[sqlite] SQLite to SQL Server

On 13 Apr 2015, at 10:38pm, Drago, William @ CSG - NARDA-MITEQ wrote: > Is there a convenient way to transfer data from SQLite to SQL Server? I'm > presently writing code to do a row by row transfer of all my tables, but the > thought crossed my mind that maybe there's a better way. If

[sqlite] how to realize md5 function

On 15 Apr 2015, at 2:19am, YAN HONG YE wrote: > sqlite> select md5('mm1234'); > Error: no such function: md5 http://www.swhistlesoft.com/blog/2011/01/07/54-sqlite-hashing-functions-library http://stackoverflow.com/questions/3179021/sha1-hashing-in-sqlite-how/3179047#3179047 Simon.

[sqlite] Request: Metadata about C API constants and functions

On 16 Apr 2015, at 12:31am, Roger Binns wrote: > It is a very productive time when you > get to delete code :-) See the paragraph "How do you measure programmer productivity?" half way down the page, just before the

[sqlite] Request: Metadata about C API constants and functions

On 2015-04-10 04:11 PM, Richard Hipp wrote: > https://www.sqlite.org/toc.db I download this and do the following: 164:~ simon$ sqlite3 /Users/simon/Desktop/toc.db SQLite version 3.8.5 2014-08-15 22:37:57 Enter ".help" for usage hints. sqlite> .mode column sqlite> select * from toc limit 10;

[sqlite] Problems with pragma journal_mode

On 16 Apr 2015, at 10:10am, Janke, Julian wrote: > rc = sqlite3_open(dbPath, ); > rc = sqlite3_exec(db, "PRAGMA journal_mode=WAL;", testCallbackPrint, 0, > ); > [?] > sqlite3_close(db); Execute your PRAGMA as if it's a SELECT call (i.e. use sqlite3_prepare_v2(), sqlite3_step(), and

[sqlite] Problems with pragma journal_mode

On 16 Apr 2015, at 2:33pm, Janke, Julian wrote: > rc = sqlite3_step(stmt); > --> returns SQLITE_DONE After the above two lines, print the value returned by sqlite3_column_text(stmt, 0) I'm not good at C off the top of my head but I think it's something like - const unsigned

[sqlite] Problems with pragma journal_mode

On 17 Apr 2015, at 11:12am, Janke, Julian wrote: > I changed my code again: Move the DROP TABLE command on line 5 to after the _finalize() call. But I don't think it'll make any difference. You should not be getting SQLITE_DONE back from your call to _step(). The documentation says that

[sqlite] Problems with pragma journal_mode

On 17 Apr 2015, at 11:59am, Janke, Julian wrote: > I've changed the stmt to "SELECT 'Hello World !!';" > In this case, > > sqlite3_step() returns SQLITE_ROW > sqlite3_column_text() returns 'Hello World !!' > > That, looks right. I agree. And it shows that your C code is working perfectly.

[sqlite] Corruption and TEMPORARY tables

On 19 Apr 2015, at 9:45pm, Joe Pasquariello wrote: > Some users of my application have their servers in remote locations that lose > power. Richard has answered your questions, but you can help further by telling us these: Are the database files held on the same computer as the one doing

[sqlite] Destroy all evidence of a database

Dear folks, I have a setup where an app creates a single-user SQLite database by opening a connection, uses it for a number of complicated things, closes the connection, then deletes the database before quitting. The data which goes into this database is highly sensitive and it's very

[sqlite] Destroy all evidence of a database

On 22 Apr 2015, at 2:07pm, Paul Sanderson wrote: > You haven't said what operating system you are using Sorry, but I can't. However, the OS is strongly oriented towards security paranoia. As long as the proper OS calls are used to delete files and release memory, you can assume that they

[sqlite] Destroy all evidence of a database

On 22 Apr 2015, at 3:23pm, John McKown wrote: > If it is > a POSIX compliant, perhaps what you could do is create a "temporary" > (mktemp) file of "appropriate" size. I had never considered that idea. Thank you very much. Unfortunately it won't work in this situation because the people in

[sqlite] Destroy all evidence of a database

On 22 Apr 2015, at 3:46pm, Jim Callahan wrote: > Can you turn off logging and overwrite the database with unencrypted zeros > or nulls; > just before deleting it? The operating system overwrites disks and memory with random bits both just after it is released and just before it is allocated

[sqlite] Destroy all evidence of a database

On 22 Apr 2015, at 4:46pm, Michael Stephenson wrote: > Simon, if the data in the database is sensitive, could you encrypt the > database (ala something like https://www.zetetic.net/sqlcipher/)? Unfortunately, this doesn't help. I'm not concerned with the database file itself. I know

[sqlite] Destroy all evidence of a database

On 22 Apr 2015, at 7:18pm, Scott Hess wrote: > The only way SQLite can get to the disk is using the vfs, so if the > vfs encrypts things, all of the files (main db, temp db, journal, > everything) will be encrypted. Guys. Guys. Guys. My app doesn't have access to any level below standard

[sqlite] Destroy all evidence of a database

On 22 Apr 2015, at 9:40pm, John McKown wrote: > ?only his app has access to this directory, > so there are no worries about "somebody else" putting files in it. So I am > _guessing_ that there are files in that directory which need to persist > between executions of his application. Correct.

[sqlite] Error while creating view through command line

On 22 Apr 2015, at 4:49pm, Manoj Kumar Pasumarthi wrote: > attach 'SP_R3.s3db' as sprdb; > > BEGIN TRANSACTION; > > > CREATE view sprdb.[view1] as select model from sprdb.[windturbine]; > > COMMIT; > > .exit > > After executing this script, DB is getting corrupted. Please run

[sqlite] How do non-SQLite DBMS communicate?

On 22 Apr 2015, at 11:28pm, Drago, William @ CSG - NARDA-MITEQ wrote: > So, SQLite databases are accessed via .dll where as other local databases run > a server that is accessed via ??? Most database systems have client/server architecture. There is somewhere a server. The only program

[sqlite] Error while creating view through command line

On 23 Apr 2015, at 7:56am, Manoj Kumar Pasumarthi wrote: ` > qAdmin: Cannot perform this operation on a closed dataset. This is not a SQLite error message. It is generated by Delphi or by something that uses Delphi. If you try to open the database in the SQLite shell tool instead of that

[sqlite] Integrating sqlite with Core Data and iCloud

On 23 Apr 2015, at 9:29pm, Jeff M wrote: > Has there been any discussion about integrating sqlite with Apple's iCloud, > either by using Apple's Core Data as the Virtual Machine's database engine > (so the VM would operate on Core Data objects) or by otherwise modifying the > existing

[sqlite] Integrating sqlite with Core Data and iCloud

On 24 Apr 2015, at 6:59am, Jeff M wrote: > I don't need to map SQLite to iCloud -- I only need to map SQLite to Core > Data. Core Data then takes care of the iCloud issues. I imagine you'd do that by writing a VFS which used Core Data for storage. Core Data could store your data in any of

[sqlite] First-N query time scaling with table size

On 25 Apr 2015, at 1:12am, John Pitney wrote: > The results are the following, on a Windows 7 64-bit platform: Please do ANALYZE, then check your times again. Simon.

[sqlite] Changing WAL mode for a transaction

On 26 Apr 2015, at 1:01pm, Navaneeth K N wrote: > pragma page_size=4096 Just to remove this from your question, that PRAGMA has effect only before the first item is created in the database. Once the database file has something in it, it already has pages, so the page size is fixed.

[sqlite] Regarding testing

On 27 Apr 2015, at 12:54pm, Sairam Gaddam wrote: > How SQLite is tested and can I get those test cases? Simon.

[sqlite] Regarding testing

On 27 Apr 2015, at 1:36pm, Sairam Gaddam wrote: > Yeah I read that link previously but how do i get all those test cases? Following the links from that page, some of the tests are included in the full download of the SQLite source code, which can be found near the end of the Download page

[sqlite] SQLite and Scientific Computing: Arrays and Complex Numbers

On 27 Apr 2015, at 9:35pm, Jim Callahan wrote: > But, even so, I would expect that users of complex numbers would be an > extremely small subset of data base users (less than 5%? or even less than > 1%?). Right. There's no way to know this for sure, but I suspect that of the literally

[sqlite] Segfault during FTS index creation from huge data

On 28 Apr 2015, at 11:49pm, Scott Robison wrote: > I never saw a segfault in my case, though I never tried anything on any > posix style system. It was strictly Windows. Windows doesn't call it segfault. It will report an access violation or (very occasionally) a page fault. Simon.

[sqlite] Segfault during FTS index creation from huge data

On 29 Apr 2015, at 9:21am, Scott Robison wrote: > Personally I don't see it as a bug. A limitation, yes. A different > algorithm that requires less ram would remove / change the limit. Anything inside the SQLite code which causes a Segfault is a bug. Assuming that you're not hacking your OS

[sqlite] Segfault during FTS index creation from huge data

On 29 Apr 2015, at 9:39am, Scott Robison wrote: > On windows, malloc returns null if the allocation fails. Sqlite detects > this and returns an error. > > On linux, malloc may return a non null yet invalid pointer and only fail > when the memory is accessed because it wasn't really available.

[sqlite] how are the SQLITE_MAX_WORKER_THREADS and SQLITE_DEFAULT_WORKER_THREADS settings used to create multiple threads for a query ?

On 29 Apr 2015, at 3:22pm, Jason Vas Dias wrote: > My question is simply how to get sqlite3 to use multiple threads to create the > temporary table from a query If you are working on a database on disk (rather than one in memory), and most of your time is spend storing data in the

[sqlite] Does column order matter for performance?

On 30 Apr 2015, at 6:16pm, Drago, William @ CSG - NARDA-MITEQ wrote: > Read Mr. Hipp's reply to me when I asked a similar question: To rephrase (1) slightly, when reading a row from a table, SQLite reads up to the last column asked for and no further. So if you have columns you rarely need

[sqlite] CSV excel import

> On 1 Aug 2015, at 2:09am, Bernardo Sulzbach > wrote: > >> =CONCATENATE("INSERT INTO myTable VALUES (",a1,","a2");") > > This is so neat. Supposing you are not migrating from a spreadsheet > because it got too big (millions of rows will take a time for this to > finish) and that your data

[sqlite] CSV excel import

On 1 Aug 2015, at 3:25pm, Bernardo Sulzbach wrote: >> P.S. As Simon noted, it seems to be defined here: >> > >> I was probably referring to the first sentence of chapter 2: "While there >> are various specifications and implementations for the CSV

[sqlite] CSV excel import

On 1 Aug 2015, at 4:42pm, Jean-Christophe Deschamps wrote: > Indeed and I'm surprised noone mentionned this from the very start of this > thread: Nulls. There is no real provision in the RFC to represent Null, or > distinguish Null from an empty string. Just to deambiguate that, the first is

[sqlite] CSV excel import

On 1 Aug 2015, at 11:11pm, Jean-Christophe Deschamps wrote: > At 18:38 01/08/2015, you wrote: > >> Nobody mentions it because it is as irrelevant as bemoaning the fact that >> CSV cannot store lawn-chairs or Java objects. It wasn't intended to do so. > > Exactly. All I mean is that with

[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?

On 3 Aug 2015, at 1:58pm, Linquan Bai wrote: > I am trying to read large data from the database about 1 million records. > It takes around 1min for the first time read. But if I do the same process > thereafter, the time is significantly reduced to 3 seconds. How can I get a > fast speed for

[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?

On 4 Aug 2015, at 6:35pm, Scott Hess wrote: > BUT, keep in mind that you might find that you've just moved the 1min time > from query time to preload time, in which case you've just complexified > without adding anything. That's why I didn't give any advice. /Something/ has to load those

[sqlite] SQLite crash

On 7 Aug 2015, at 10:11pm, Robert Weiss wrote: > And here is the statement thatcaused the crash: > > create index d200_on_passport_fn ond200_on_passport(fn); > > > > The crash didn?t happen when Itried the CREATE INDEX statement on a test > database with the same ddlstatements but

[sqlite] Problem with SQLite in C++. DB is BUSY (Multithread)

On 9 Aug 2015, at 10:37pm, ??? ??? wrote: > I've got a problem. I'm using sqlite3 in my C++ project. In the log I've > got error's *DB is locked error code 5*. As I know error code 5 means, that > DB is busy. For testings, please use

[sqlite] Problem with SQLite in C++. DB is BUSY (Multithread)

On 10 Aug 2015, at 10:49pm, ??? ??? wrote: > When I set timeout to 12 ms (2 minutes). It starts work. But as I > understand from reading SQLite C interface, when DB in WAL journal mode it > should work without busytimeout. > > Can it work without busy timeout? It is correct

[sqlite] Problem with SQLite in C++. DB is BUSY (Multithread)

On 10 Aug 2015, at 11:12pm, ??? ??? wrote: > But what is minimum period? The minimum period doesn't matter. The number you set the timeout to is the /maximum/ period. SQLite will continue to try to access the file until that amount of time has passed. Then it will give up,

[sqlite] Fwd: Problem with SQLite in C++. DB is BUSY (Multithread)

On 11 Aug 2015, at 2:28am, ch wrote: > Is this because we don't create and handle savepoints correct? Have you set a timeout value using the routine I pointed to earlier ? If not, do so. Simon.

[sqlite] Lawyers, encryption, and RC4

On 11 Aug 2015, at 1:30pm, Eric Hill wrote: > We're getting some pushback from our lawyers suggesting that SQLite's use of > RC4 even just to generate random numbers is, in their minds, encryption for > export purposes. Now, this makes absolutely no sense to me, I can assure > you, and I am

[sqlite] Lawyers, encryption, and RC4

On 11 Aug 2015, at 3:11pm, Richard Hipp wrote: > The RC4 encryption algorithm consists of three subcomponents: > > (1) Key management logic > (2) The pseudo-random number generator (PRNG) > (3) The encoder/decoder > > SQLite only implements (2). It omits (1) and (3). And hence, the RC4 >

[sqlite] Site unavailable.

On 12 Aug 2015, at 12:18am, ??? ??? wrote: > In Russia when I'm trying to visit sqlite.org site, I'm getting conection > timeout. > > Also, sqlite.org doesn't pinged. The web site is accessible from western Europe. And I can reach Moscow State University so it doesn't seem to be

[sqlite] Lawyers, encryption, and RC4

On 11 Aug 2015, at 3:00pm, Rob Willett wrote: > I recall that it used to be that 40 bit RC4 was OK and I *think* that the bit > length is now longer (128bit?) as it has been shown that 40 bit RC4 is as > much use as a chocolate fireguard. While it is allowable to export software

[sqlite] Lawyers, encryption, and RC4

On 12 Aug 2015, at 2:45pm, Brian Willner wrote: > If you point out to your lawyers that SQLite is not doing anything that > Apple's OS X is doing, you may get some traction as well. Sorry to have to tell you that OS X /is/ registered for a US export licence, mostly for its incorporation of

[sqlite] Database sybchronisation

On 13 Aug 2015, at 3:52am, Chris Parsonson wrote: > I need to be able to synchronise some fairly simple table in two databases. Are both copies of a table being changed between synchronisations ? Are the changes just the adding of new rows, or do you sometimes delete or update rows ? Is the

[sqlite] Database sybchronisation

On 13 Aug 2015, at 5:55am, Chris Parsonson wrote: > The tables are very simple. They have a primary key, but no relationship > between tables in the sense that you mean. Synchronisation will be add new > rows, and update some rows, no deletions To access two different databases with one

[sqlite] Database sybchronisation

On 13 Aug 2015, at 10:16am, Chris Parsonson wrote: > the ATTACH gives no error in either my first > attempt or this second one. Then it's probably working. >SQLStmt = "ATTACH '" & sUpdateDBPath & "' AS UPD;" So after you've done that, write some code to insert a new row into a

[sqlite] Database sybchronisation

On 13 Aug 2015, at 11:34am, Chris Parsonson wrote: > Has anybody ever used this ATTTACH command? > Still doesn't work The code shown does not read the row back again once it is written. What makes you think your new row is not being stored ? What does it do when it doesn't work ?

[sqlite] Database sybchronisation

On 13 Aug 2015, at 4:03pm, sqlite-mail wrote: > With that would be trivial to log the statements that change the database to > replicate elsewhere. As Jean-Christophe wrote, it's not that simple. There are huge books written on the problems involved in synchronising two copies of a

[sqlite] Database sybchronisation

On 13 Aug 2015, at 5:28pm, Tim Streater wrote: > Presumably all the OP needs to do (in the future, at any rate) is: > > open first db > attach second db > start transaction > do updates to first db; > do identical updates to second db > commit transaction > close connection > > or some

[sqlite] SQLite database becomes corrupt on iOS

On 13 Aug 2015, at 6:32pm, skywind mailing lists wrote: > Before my app closes I close the database explicitly. Though I do not know if > this happens always when the iDevice shuts down due to battery issues. iDevices shut down quite a time before they'd actually run out of power. Before

[sqlite] SQLite database becomes corrupt on iOS

On 14 Aug 2015, at 4:10pm, skywind mailing lists wrote: > I do not get any error message from SQLite. And the database only gets > corrupted when the iDevice has to shut down due to battery issues. I have > never had a customer complaining about a corrupt database during normal > operation.

[sqlite] SQLite database becomes corrupt on iOS

On 14 Aug 2015, at 4:17pm, skywind mailing lists wrote: > this is the problem. I could never reproduce it by myself and even my > customers have normally no problems. But it happens once a year or so to one > of my customers. > Still this is very annoying as it results in data loss. I have

[sqlite] SQLite database becomes corrupt on iOS

On 14 Aug 2015, at 5:16pm, Random Coder wrote: > I've run into other issues > that lead me to believe the OS is caching file writes until the app > exits in some situations regardless of various sync calls, but I never > did have time to track down if I was just fooling myself, or if the OS >

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

> On 15 Aug 2015, at 12:53am, James K. Lowden > wrote: > > Simon Slavin wrote: > ... >> If it's going to be documented, it would also be nice to see it on >> >> <https://www.sqlite.org/tempfiles.html> >> >> which, according to my browser

[sqlite] ATTACH DATABASE statement speed

On 17 Aug 2015, at 7:52am, Paolo Bolzoni wrote: > I have a program that materialize the subset of a database in a second > database for future use. > > To do so I create the database object on the OUTPUT database, create > tables, create the indexes, and vacuum it just in the case I reused an

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

On 17 Aug 2015, at 8:11am, Paolo Bolzoni wrote: > If the problem is the multi-threading, it would be not enough to > simply document that the tmp directory should be set before spawning > any thread? e.g., in the main? It's partly a philosophical one. The setting for the temporary directory

[sqlite] ATTACH DATABASE statement speed

On 17 Aug 2015, at 8:17am, Simon Slavin wrote: > Also, out of interest, can you run "PRAGMA integrity_check" on all the > database files involved ? Also out of interest, instead of just ATTACHing the input database, 1) Close the output database 2) Reopen the output da

[sqlite] ATTACH DATABASE statement speed

On 17 Aug 2015, at 9:22am, Paolo Bolzoni wrote: > The pragma integrity_check is still running... Maybe my disk sucks for > some reason? I wonder whether the hard disk is faulty or the file is on a bad sector. If the other tests show nothing, can you duplicate the input database file ? Do

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

On 17 Aug 2015, at 4:19pm, Olivier Barthelemy wrote: > Create statement : > CREATE TABLE VariableDetails (dtl_id INTEGER PRIMARY KEY ASC AUTOINCREMENT, > storage_implicit BOOLEANCHECK (storage_implicit = 'true' OR > storage_implicit = 'false'), storage_type TEXT NOT NULL); [I assume there is

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

On 17 Aug 2015, at 4:50pm, Olivier Barthelemy wrote: >> SQLite has no such type > > I see nothing in Qt code that converts BOOLEAN in NUMERIC or anything else. > The statement is passed as is to sqlite. SQLite is interpreting BOOLEAN as NUMERIC. See the last line of the table in section

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

On 17 Aug 2015, at 5:08pm, Olivier Barthelemy wrote: > As stated in the second question of my first message, shouldn't there be > some check in sqlite that the type in the constraints are compatible with > the fields, with an error at table creation? SQLite allows what was done, on purpose.

[sqlite] order by not working in combination with random()

On 17 Aug 2015, at 9:46pm, Jeffrey Mattox wrote: > Could the random() be made part of an expression (that doesn't change the > result) to fool the optimizer into only doing the random() once, like this: > > SELECT ( random() * col_thats_always_one ) AS x FROM table ORDER BY x Use a

[sqlite] order by not working in combination with random()

On 18 Aug 2015, at 1:32am, Simon Davies wrote: > sqlite> SELECT r FROM (SELECT random() AS r FROM myTable) ORDER BY r DESC; > -6629212185178073901 > -5293473521544706766 > 2649466971390864878 > -6185422953036640443 > 1855956853707028764 Eek. Sorry, I should have tried it before posting.

[sqlite] ATTACH DATABASE statement speed

On 18 Aug 2015, at 4:14am, Paolo Bolzoni wrote: > In the input and output database I had a table of the same name and using: > > DROP TABLE IF EXISTS WaysNodes; > > sqlite3 was actually deleting the table of the input db, this was > unexpected as I thought that > without any prefix it

[sqlite] ATTACH DATABASE statement speed

On 18 Aug 2015, at 7:30am, Paolo Bolzoni wrote: > Any other idea of what can I try? Perhaps my filesystem is misconfigured? The long time you quote is not standard for SQLite and I don't think anyone can help you solve it by knowing picky details of SQLite. I'm even surprised that it

[sqlite] ATTACH DATABASE statement speed

On 18 Aug 2015, at 11:38am, Paolo Bolzoni wrote: > but after some seconds it drops terribly to less than 10MB/s This, along with some information from your previous posts, all goes to suggest you have a hardware problem of some kind. My guess is that your hard disk is becoming faulty,

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

On 19 Aug 2015, at 1:28am, Paolo Bolzoni wrote: > Wouldn't be easier to simply add a parameter to sqlite3_initialize()? > E.g., a char const pointer to the tmp directory? That, if null, > defaults to something reasonable as James mentioned? The correct place for temp files varies from

[sqlite] ATTACH DATABASE statement speed

On 19 Aug 2015, at 3:27am, Paolo Bolzoni wrote: > coping the sqlite db on an external disk connected via usb3 > and formatted with Ntfs actually does the pragma quick_check in little > more than 20 seconds and pragma integrity_check in 5 minutes. Those times are completely typical for SQLite.

[sqlite] ATTACH DATABASE statement speed

On 19 Aug 2015, at 10:46am, Paolo Bolzoni wrote: > As you might have guessed from the timezone I am not at home atm, so I > do not have spare external disks. > However, I do have an expendable 16BG usb stick so I tried on that. > > First I formatted it using zfs and I did the Pragma

[sqlite] do temporary on-disk databases get cleaned up on abnormal process termination?

On 18 Aug 2015, at 8:40pm, Sam Roberts wrote: > The docs say you have to close the DB handle to clean them up. I'm > concerned that if a process is SIGKILLed or just exits abruptly that > the temporary DBs will accumulate on disk. > > What mechanism is used to create the temporary files? If

[sqlite] do temporary on-disk databases get cleaned up on abnormal process termination?

On 19 Aug 2015, at 1:36pm, Keith Medcalf wrote: > Meaning that on a persistent temp storage the files will stay forever (or > until a manually deleted). Then again, on systems such as windows where temp > files are never deleted this is to be expected. Hmm. On every Unix box I've seen /tmp

[sqlite] Getting row number in a sorted list.

On 19 Aug 2015, at 1:16pm, Anthrathodiyil, Sabeel (S.) wrote: > Example, for below entries in NameTable > > Name > > 1. PTN > > 2. ABCD > > 3. CDE > > 4. PQRS > > 5. AXN > > > I want to get the row number of the first name that starts with 'P' in the >

[sqlite] do temporary on-disk databases get cleaned up on abnormal process termination?

On 19 Aug 2015, at 1:44pm, Richard Hipp wrote: > On Unix, unlink() after open is used. > > On Windows, the FILE_FLAG_DELETE_ON_CLOSE flags is used when the > temporary file is opened. I was wrong. Apologies. Simon.

[sqlite] Getting row number in a sorted list.

On 19 Aug 2015, at 2:00pm, R.Smith wrote: > Seriously though, if that column is not COLLATE NOCASE declared, 'PZZZ' will > fail. Either ensure your column has COLLATE NOCASE or perhaps simply choosing > the highest (non UTF-8) character such as: > > WHERE name BETWEEN 'P' AND 'P~' > >

[sqlite] SQLite database becomes corrupt on iOS

On 20 Aug 2015, at 6:22am, Scott Perry wrote: > That said, it's possible to corrupt a database by forcing the device to power > off (by holding the power and home buttons) while SQLite's writes are in > flight. This is equivalent to the conditions of a kernel panic. This is true only if your

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

On 21 Aug 2015, at 12:20pm, sqlite-mail wrote: > Does anybody knows how to rename a table and all it's dependencies in one go > ? Can't be done. Sorry. Simon.

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

On 21 Aug 2015, at 7:02pm, sqlite-mail wrote: > I'm pointing this here because postgresql do manage this case properly ! If you want postgres, you know where to find it. Please don't forget that SQLite has to run on your smartphone and your SatNav device and your TV. It has different design

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

On 21 Aug 2015, at 8:13pm, Scott Hess wrote: > Since renameTriggerFunc() > follows renameParentFunc(), my guess is that triggers are also handled. The documentation says that statements within TRIGGERs are not changed correctly: Perhaps

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

On 21 Aug 2015, at 9:07pm, Scott Hess wrote: > That said, writing code to do this manually has potential to be error > prone. It might make sense under an ENABLE flag. It feels like an obvious > thing to have, not something out-of-scope like embedding a new language or > support for a new

[sqlite] Stuck in busy handler

On 22 Aug 2015, at 2:33pm, Jon Kuhn wrote: > I am working on project that uses SQLite to save records in an in memory > database and periodically push them to an on-disk database. The project is > multi-threaded with several threads accessing the databases (memory and > on-disk) at the same

[sqlite] ESC_BAD_ACCESS when using a background thread

On 23 Aug 2015, at 11:31am, Jeff M wrote: > Any ideas on how to debug this? Are you checking the values returned by sqlite3_prepare, sqlite3_bind, and sqlite3_step, to make sure they return SQLITE_OK ? I'm not quite sure what you mean by 'done once' -- whether it's once for the whole

[sqlite] ATTACH DATABASE statement speed

On 24 Aug 2015, at 2:32am, Roger Binns wrote: > On 08/19/2015 05:56 PM, Paolo Bolzoni wrote: >> I left running the pragma quick check during the night and finished >> in 2 hours and 46 minutes, so it is about 8 times slower than in >> ext4. Zfs is an advanced filesystem plenty of features, but

<    1   2   3   4   5   6   7   8   9   10   >