Re: [sqlite] WAL: no schema after close

2016-06-03 Thread Roger Binns
On 03/06/16 08:28, Максим Дементьев wrote: > Thank you, I'll try this "yet another python SQLite wrapper" in the near > future, it looks interesting. Disclosure: I am the author of APSW. I recommend looking at the page showing the differences between APSW and pysqlite:

Re: [sqlite] struct SrcList

2014-10-06 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/06/2014 04:41 AM, Prakash Premkumar wrote: > Can you please show me where in sqlite code the struct SrcList is > filled up ? You keep asking people to do lots of work on your behalf without showing you have made any effort yourself. You have

Re: [sqlite] Memory Usage

2014-10-06 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/02/2014 03:12 PM, David Muchene wrote: > I was wondering what options I can tune to make sqlite use more > memory. Have you tried using memory mapping (there is a pragma). https://sqlite.org/mmap.html Roger -BEGIN PGP SIGNATURE-

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

2014-10-10 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/10/2014 01:18 PM, jose isaias cabrera wrote: > I was able to figure out that comma's are more important than just > a 1000 number delemeter, so I received the right answer by taking > the commas out: To help avoid this in the future, be aware

Re: [sqlite] sqlite3.c in a library - api rename

2014-10-29 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/28/2014 08:18 PM, Ward Willats wrote: > I am using the amalgamation in a C++ library statically linked into > other people's applications. > > Is there a way to namespace and/or macro and/or let C++ do its > name-mangling thing to all the

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

2014-11-06 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/05/2014 02:05 PM, nicolas riesch wrote: > This means that EACH SUCCESSIVE function in the sequence above can > be processed on a DIFFERENT OS THREAD. That works just fine with SQLite, with one caveat. You should also make sure the wrapper

Re: [sqlite] Locking errors on network

2014-11-10 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/10/2014 09:41 AM, Mike McWhinney wrote: > Please let know if there are any other solutions to this database > locking problem as used on a network. Yes. Do not do it. See the FAQ: https://www.sqlite.org/faq.html#q5 Roger -BEGIN PGP

Re: [sqlite] Locking errors on network

2014-11-10 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/10/2014 10:22 AM, Mike McWhinney wrote: > So SQLite shouldn't be used at all on a network? Aren't there any > other provisions to handled the locking errors if/when they occur? Network filesystems do not implement locking and other operations

Re: [sqlite] Search for text in all tables

2014-12-04 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/03/2014 11:44 PM, Baruch Burstein wrote: > Is it possible to somehow search for/replace a string in all > columns of all tables? (Disclosure: I am the APSW author) The APSW shell includes a .find command that does the searching bit. You also

Re: [sqlite] Search for text in all tables

2014-12-04 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/04/2014 11:59 AM, Petite Abeille wrote: >> On Dec 4, 2014, at 8:44 AM, Baruch Burstein >> wrote: >> >> Is it possible to somehow search for/replace a string in all >> columns of all tables? > > .dump | sed ’s/old/new/g'

Re: [sqlite] Search for text in all tables

2014-12-05 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/05/2014 01:24 AM, Max Vlasov wrote: > I once implemented a virtual table "allvalues" that outputs all > database values with (hope self-explaining) fields > > TableName, TableRowId, FieldName, Value Could you expand on how you coped with the

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

2014-12-08 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/07/2014 04:43 PM, David Barrett wrote: > so I'm curious if you can think of a way using the API (or any > other way) to essentially "nice" the process by inserting a short > "sleep" into whatever loop runs inside the VACUUM command. Using OS

Re: [sqlite] Table names length and content

2014-12-08 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/2014 10:30 AM, jose isaias cabrera wrote: >>> Hmmm... what I am looking for it is not there. If the "string >>> length" defined there is what defines the length of the name of >>> a table, I am in business. :-) However, there is nothing

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

2014-12-08 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/2014 01:35 PM, Max Vlasov wrote: > I wonder whether I/O "sleeping" possible in the first place. In this particular case the OP wants to vacuum while the machine is doing other I/O activity unrelated to the vacuum. Having more sleeping

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

2014-12-09 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/2014 05:36 PM, David Barrett wrote: > *Re: "a simple way is to sleep in the progress callback"* -- Can > you tell me more about this? Are you referring to the callback > provided to sqlite3_exec(), or something else?

Re: [sqlite] VACUUM requires 6.7 times space ?

2015-01-05 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/05/2015 10:25 AM, Jim Wilcoxson wrote: > It's easy to do this in Python using iterdump(), Be aware that the pysqlite dump implementation is incomplete in many ways. Some issues: - - Doesn't dump in a transaction so will have irregularities if

Re: [sqlite] PHP: squelch warning and error messages

2015-01-05 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/05/2015 04:53 PM, Lev wrote: > I'm using the PHP bindings for SQLite3. How can I squelch error > and warning messages? I do error checking, but the failing call > emits the messages. > > Messages like: > > Warning: SQLite3::prepare(): Unable

Re: [sqlite] sqlite3_exec and returned error

2015-01-06 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/05/2015 11:43 PM, Valery Reznic wrote: > I was planing to use this function like following: > > --- > > int res; char *errmsg; I'd recommend you explicitly set that to NULL here. > > res = sqlite3_exec( db,

Re: [sqlite] damaged database recovery

2015-01-15 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/15/2015 12:52 PM, Dave Dyer wrote: > Of course that's possible, but .dump produced what superficially > appeared to be a perfectly consistent text file. Note that .dump writes the output and then on encountering problems attempts the table

Re: [sqlite] sqlite3 tool bug

2015-01-16 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/16/2015 01:05 PM, Simon Slavin wrote: > Why on earth would an operating system programmer bother to put any > translation into piping You have a system with a bunch of apps installed. You then upgrade to a new version of the operating system

Re: [sqlite] sqlite3 tool bug

2015-01-16 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/16/2015 02:35 PM, James K. Lowden wrote: > I'm skeptical of the notion that cmd.exe is diddling with your data > en route to the pipe. Almost certainly the reason is that stdout and stdin are in character mode. It requires extra code to put

Re: [sqlite] sqlite3 tool bug

2015-01-17 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/17/2015 12:20 PM, Graham Holden wrote: > I would echo this: it's good at mangling the command-line, but > I've not been aware of it ever mangling data sent to a file/stream > (other than the binary/text mode conversions). In text mode (the

Re: [sqlite] Fsync or fdatasync performs slowly

2015-01-19 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/18/2015 06:49 PM, Wei, Catherine wrote: > takes too much time when it executes fsync or fdatasync function. Note that although the documentation says only the file handle is synced, in practise many filesystems actually sync the whole

Re: [sqlite] Fsync or fdatasync performs slowly

2015-01-20 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/20/2015 01:12 AM, Wei, Catherine wrote: > What's strange is that when I remove the disk, the difference still > exists. It takes long time for fsync working with no disks, which > has confused me for several days. What exactly do you mean by

Re: [sqlite] Possible to get table size (in bytes)?

2015-02-04 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/04/2015 10:26 AM, Rael Bauer wrote: > Is it possible to get the information of how many bytes a table is > taking up in the database? What is the underlying problem you are trying to solve with this information? There may be alternate

Re: [sqlite] Interrupting custom collation processing

2015-02-07 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/07/2015 05:55 PM, Dan Ackroyd wrote: > Due to the nature of PHP, it would be expected that the processing > of the collation should stop immediately. However I can't see how > to indicate to SQLite that an error has occurred, and so the >

Re: [sqlite] equivalent for JOIN LATERAL

2015-02-08 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/08/2015 03:32 PM, Simon Slavin wrote: > For those like me who hadn't heard of it, here's a reference: Here is a presentation referenced "Modern SQL in PostgreSQL", with title "Still using Windows 3.1? So why stick to SQL-92?" Lots of nice

Re: [sqlite] SQLCipher - Full Database Encryption for SQLite

2015-02-09 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/09/2015 02:54 AM, Dominique Devienne wrote: > Adding PRAGMAs is not possible to a true SQLite "extension" I > thought, It is however possible to add functions. eg encryption_mode could be added and called like this: select

Re: [sqlite] sqlite3 mailing list broken

2015-02-12 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/12/2015 05:15 AM, Cal Leeming wrote: > I've been having a problem where iterdump() is exporting in a > format which is unsuitable for MySQL, iterdump is part of pysqlite, and has no code from the official SQLite project. iterdump itself is

Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)

2015-02-12 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/10/2015 07:03 PM, Jay Kreibich wrote: > ... VFS is unlikely to make the cut. ... similar things about the > xBestIndex() and xFilter() functions I haven't read the book, but one thing that may help is not using C for these. I think it is

[sqlite] insert in sqlite (returning value for inserted row)

2015-04-01 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/01/2015 10:10 AM, Kumar Suraj wrote: > Hi Richard.. this wont work for me due to following reason. Yes it will. This is how I do it: BEGIN TRANSACTION; INSERT INTO table .; SELECT last_insert_rowid(); COMMIT; That will always

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

2015-04-08 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/08/2015 07:18 AM, Fabian Pr?bstl wrote: > Is there an easy way of configuring / changing this so that > SHARED_WRITE is not acquired? Will SQLite even function? Is it just > easier to create a hidden copy and work on that? For something this

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

2015-04-09 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am the author of a Python wrapper for SQLite. As part of my documentation build process I link function names to the relevant page in the SQLite doc. I also have to wrap all constants - eg making sure that all SQLITE_LIMIT_ ones are wrapped and

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

2015-04-09 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/09/2015 05:42 PM, Richard Hipp wrote: > On 4/9/15, Roger Binns wrote: >> A JSON or XML file with that would make my life a lot easier. > > JSON/XML? Seriously? We eat our own dogfood here, thank you! > Whe

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

2015-04-09 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/09/2015 06:43 PM, Simon Slavin wrote: > Hmm. We could add an output mode to the shell tool which outputs > in JSON format. And by "We" I mean someone who can write good C, > which isn't me. Get yourself a time machine, go back 6 years, and

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

2015-04-10 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/10/2015 12:33 AM, Dominique Devienne wrote: > But that's build time. There is no way (i.e. pragma) to list > registered SQL functions at runtime. There is a ticket for this: https://www.sqlite.org/src/tktview/5896edbe463b6a1f0f14 It was

[sqlite] JSON expressions for records and synchoronisation

2015-04-10 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/10/2015 01:51 PM, Simon Slavin wrote: > With a cursory glance I do see important incompatibilities with > SQLite. I use JSON as the data format for $work stuff (startups) for years, and these JSON schemas etc miss why some of us use JSON. If

[sqlite] sqlite3 (or sqlite4) performance on NFS

2015-04-11 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/11/2015 06:30 AM, 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). The latency is what is getting you. SQLite uses

[sqlite] JSON expressions for records and synchoronisation

2015-04-13 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/13/2015 03:15 PM, James K. Lowden wrote: > I beg to differ, Roger. Note that I deliberately said "some" and not "all". Everyone has their reasons, and not everyone has the same reason. > The programming language of choice doesn't affect the

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

2015-04-15 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/10/2015 07:11 AM, Richard Hipp wrote: > On 4/9/15, Roger Binns wrote: >> >> So the URL for this SQLite database is? :-) >> > > https://www.sqlite.org/toc.db Thank you for this. I've now updated my doc build tool

[sqlite] MMAP performance with databases over 2GB

2015-08-06 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 In my testing I am finding that using SQLite's mmap support with larger databases (final db size is ~10GB) to create the database (WAL mode) is considerably (about three times) slower than no mmap. The default max mmap limit is 2GB (sadly). Has

[sqlite] MMAP performance with databases over 2GB

2015-08-06 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/06/2015 09:27 AM, Dan Kennedy wrote: >> Is it using more CPU cycles in mmap mode or just taking longer? >> If the former, does [time] attribute them to "user" or "system"? It is taking longer. I have 3 XML dumps which I turn into JSON

[sqlite] Determine query type

2015-08-06 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/06/2015 09:46 AM, Ben Newberg wrote: > Is there a good way to determine if a sql query is either > attempting to modify a database, or simply querying it? You most like want to use the authoriser. It tells you exactly what the query is doing,

[sqlite] MMAP performance with databases over 2GB

2015-08-07 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/07/2015 02:55 AM, Dan Kennedy wrote: >> http://www.sqlite.org/src/info/3a82c8e6cb7227fe > >> Does that improve performance any in your case? I tested 3a82c8e6 (fix above) against e596a6b6 (previous changeset), in both cases starting with an

[sqlite] how to detect when a table is modified ?

2015-08-07 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/07/2015 04:23 PM, Nicolas J?ger wrote: > I'm writing a program using sqlite3 and gtk to screen a database, > there is a way to get a signal from sqlite3 when something is > modified inside a database (values, add/delete row...), in the way > to

[sqlite] Site unavailable.

2015-08-11 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/11/2015 04:18 PM, ??? ??? wrote: > In Russia when I'm trying to visit sqlite.org site, I'm getting > conection timeout. Use down for everyone or just me to check sites: http://www.downforeveryoneorjustme.com/sqlite.org In this

[sqlite] ESC_BAD_ACCESS when using a background thread

2015-08-23 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/23/2015 03:31 AM, Jeff M wrote: > sqlite3_step(); // occasionally crashes here (showing > ESC_BAD_ACCESS on main thread) That has three very likely causes. The first is that your internal state gets messed up, and the statement has actually

[sqlite] ATTACH DATABASE statement speed

2015-08-23 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 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 this >

[sqlite] ATTACH DATABASE statement speed

2015-08-23 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/23/2015 06:48 PM, Simon Slavin wrote: > Paolo is using Linux which does not do read-ahead optimization like > some versions of Windows. Therefore if he really is using an SSD > then fragmentation is not an issue. You are confusing things. The

[sqlite] ESC_BAD_ACCESS when using a background thread

2015-08-24 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/24/2015 03:08 AM, Jeff M wrote: > I've checked all of your suggestions and nothing is amiss. You ran valgrind and it said everything is fine? That would be shocking. > I don't understand how the main thread can run before the > background

[sqlite] ESC_BAD_ACCESS when using a background thread

2015-08-25 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/25/2015 02:38 AM, Jeff M wrote: > I tried to install valgrind (on Mac OS Yosemite), but I can't get > it to compile. I don't have the fortitude to work out the install > issues. Generally you would be better off using something like homebrew:

[sqlite] Appropriate Uses For SQLite

2015-02-18 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/18/2015 11:43 AM, Richard Hipp wrote: > but I think the truth is we are probably stuck with SQL for a while > yet. In theory there could be an intermediate representation form (like compilers do) that is publicly available, with the (now

[sqlite] Appropriate Uses For SQLite

2015-02-19 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/18/2015 01:28 PM, Jay Kreibich wrote: > SQLite kind of already does this, if you consider VDBE instructions > to be an IR. There is a lot that would have to be done with it: - - make the IR stable across releases - - add more general

[sqlite] Sqlite3 tutorial and reference manuals?

2015-02-22 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/22/2015 10:10 AM, russ lyttle wrote: > The "natural" language for the Raspberry PI is Python. For Arduino > it is C++. I've confirmed that both PERL and Python drop too many > messages when using my naif DB with sqlite3, R-PI model A, and Digi >

[sqlite] SIGSEGV rolling back in-memory DB‏‎

2015-07-07 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/07/2015 09:47 AM, Richard Hipp wrote: > Can you send instructions on how to recreate the problem? Please > bare in mind that none of the core SQLite developers do much with > python. Alternatively, you might send emails to Ro

[sqlite] SIGSEGV rolling back in-memory DB‏‎

2015-07-07 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/07/2015 02:06 PM, Brian Soby wrote: > I don't believe it's a python specific issue since the core problem > occurs in a rollback command internal to SQLlite. Unless you configure it otherwise, pysqlite likes to parse your SQL and do extra

[sqlite] SIGSEGV rolling back in-memory DB‏‎

2015-07-08 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/08/2015 09:55 AM, Brian Soby wrote: > It's single threaded. Good - that eliminates a whole bunch of complications. > I have a log of all of the queries run through the database up > until the crash. It crashes on a rollback command. What

[sqlite] SIGSEGV rolling back in-memory DB‏‎

2015-07-09 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/08/2015 10:33 AM, Brian Soby wrote: > Nothing appears to happen but it's not running very faithfully. > Specifically, I get a bunch of problems on the rollbacks about the > savepoints not existing even though it's replaying the original >

[sqlite] Schema-less JSON SQLite DB?

2015-07-13 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/13/2015 05:43 PM, Hayden Livingston wrote: > Is there a concept of a schema-less JSON SQLite DB? What exactly is it you want? A schema-less database? A JSON database? Using SQLite to store JSON? (It is unclear if you mean something like

[sqlite] Schema-less JSON SQLite DB?

2015-07-13 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/13/2015 08:00 PM, Hayden Livingston wrote: > Does your code also map object hierarchies in json? Yes, but thankfully I don't have much of them. Essentially the top level of the object has a unique id (SQLite allocated), and then other tables

[sqlite] Fwd: Usenix paper: Korean researchers invent sqlite WALDIO mode to circumvent redundant journaling by EXT4 on eMMC

2015-07-14 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/14/2015 06:53 AM, Mikael wrote: > Does SQLite do the same, what about SQLite do the same, patch? The SQLite authors rejected checksumming SQLite database pages. The existing integrity check will only catch issues that happen in sufficiently

[sqlite] Schema-less JSON SQLite DB?

2015-07-15 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/15/2015 08:22 AM, Sergej Jure?ko wrote: > What do you guys think? Is it stupid, could it be improved? I recommend looking at Mongodb and Postgres first to see how they do queries. It would be better to be compatible with them where practical,

[sqlite] why does SQLite support VARCHAR(X) when the doc says it ignores it?

2015-07-16 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/16/2015 02:46 PM, Hayden Livingston wrote: > It seems that > > CREATE TABLE A( something varchar(255) ) > > it just > > CREATE TABLE A( something text ) > > Why have it at all? Because other code and other humans look at the database too.

[sqlite] CSV excel import

2015-07-30 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/30/2015 10:58 AM, Sylvain Pointeau wrote: > is it possible? in a lot of cases, I cannot use sqlite (executable) > because of the lack of a good CSV import. It would really great if > this could be addressed. Use the APSW shell:

[sqlite] Does length() use strlen() on blobs?

2015-03-01 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/01/2015 10:40 AM, David Barrett wrote: > Hi! If I define a column as BLOB type, will length() run strlen() > on the data, or will it consult some kind of internal value to > determine the length? sqlite> select cast(x'424446' as text); BDF

[sqlite] Read Blob Returning bytes till NULL Terminated String

2015-03-09 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/09/2015 10:31 AM, reddy ykk wrote: > I have a requirement of reading an image data (BLOB) from a > database. I could successfully read the length of the blob using > sqlite3_column_bytes() but when i try to read the entire blob > using

[sqlite] C++ ORM

2015-03-09 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/09/2015 02:38 PM, Scott Robison wrote: > Note: He's okay using SQLite more or less directly if he needs to, > as he recognizes the lack of reflection in C++ might lead to a less > manageable ORM. Still, never hurts to ask. Don't use an ORM

[sqlite] Problem: you can delete a virtual table with open statements and then SEGV

2015-03-23 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 This was originally reported as a problem in APSW (Python SQLite wrapper): https://github.com/rogerbinns/apsw/issues/184 The important bits are that a cursor (sqlite3_stmt) is created, and step called once, but *not* reset or finalised:

[sqlite] Problem: you can delete a virtual table with open statements and then SEGV

2015-03-24 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/23/2015 04:33 PM, Roger Binns wrote: > SQLite really should prevent it from happening by disallowing the > deletion of virtual tables with open statements. Thanks for fixing this. I can confirm that the latest SQLite dev code now

[sqlite] Bug: sqlite3_memory_used/highwater truncate to 32 bits

2015-05-09 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The sqlite3_memory_used and highwater interfaces are defined to return a 64 bit value. They carefully call the 32 bit limited sqlite3_status method and then cast to 64 bit. Instead they should call sqlite3_status64. Reported indirectly via

[sqlite] Issue: Inconsistency of sqlite3_dbstat_register declaration

2015-05-10 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 There is a forward declaration of sqlite3_dbstat_register (currently line 130871 in the 3.8.10.1 amalgamation): int sqlite3_dbstat_register(sqlite3*); Later is the actual implementation (currently line 155838 in the 3.8.10.1 amalgamation):

[sqlite] Issue: Inconsistency of sqlite3_dbstat_register declaration

2015-05-11 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/11/2015 01:34 AM, Jan Nijtmans wrote: > 2) If sqlite is not compiled with -DSQLITE_ENABLE_DBSTAT_VTAB=1, > or the function is renamed (as it should) it wouldn't even work. That was my scenario. I just -DSQLITE_ENABLE_DBSTAT_VTAB and the code

[sqlite] Suggestion for .import improvement

2015-05-11 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/11/2015 12:24 PM, tonyp at acm.org wrote: > It?s a real nuisance to have to edit out the headers every time, > especially when the files are very large and they do not load fast > (or at all) into a text editor. Plus, it?s semi-automatic. I

[sqlite] xBestIndex() implementation question

2015-05-13 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/13/2015 08:06 PM, Jilong Kuang wrote: > Sure, that is one option. But I'm just wondering if inside the > xBestIndex() we can get the value field, we can do a much better > job to give the cardinality info. Note that the value could be something

Re: [sqlite] Android - Occasional Error when closing a database

2013-10-29 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 29/10/13 06:23, Matthew Dumbleton wrote: > I have noticed that occasionally, after the data is entered and the > transaction ended, the close method call produces an error 'unable to > close due to unfinalised statements' and logCat shows the

Re: [sqlite] Need suggestion for database scheama

2013-11-07 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/11/13 19:47, James K. Lowden wrote: > You might guess from my email domain name that I take an interest in > posts like yours. And it's pretty good first cut, no pun intended. > ;-) It is also worthwhile looking at musicbrainz

Re: [sqlite] Concrete example of corruption

2013-12-01 Thread Roger Binns
On 01/12/13 06:10, L. Wood wrote: > D. Richard Hipp, are there any plans to make this more robust in the > future, so reading/writing a corrupt database (with no -journal file > available) will return an *error* instead of causing further damage? There has been a ticket languishing for many

Re: [sqlite] "Last Modified" file attribute is not updated

2013-12-09 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/12/13 10:30, Felipe Farinon wrote: > I'm sorry to repost, but I just want to confirm that there is no > interest in fixing this, so that I can handle this with a workaround in > my application. Note that the effect will be to make SQLite slower

Re: [sqlite] Does not detect invalid column name when subexpression optimized away

2013-12-19 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 19/12/13 15:36, RSmith wrote: > With this query you essentially ask the RDBMS to evaluate and supply > you with the result of (X and 0) - my guess is the optimiser pounces > directly on the fact that (X and 0) will always be 0 no matter what X > is

Re: [sqlite] Is that same between rebinding every parameter and

2013-12-30 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 30/12/13 06:18, Igor Tandetnik wrote: > sqlite3_clear_binding is very rarely needed, in my experience. In fact, > I have not yet encountered a reason to use it. I use it in my Python wrapper (APSW). The reason is because I have an automatic

Re: [sqlite] Saw something interesting in the debugger...

2014-01-09 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/01/14 08:53, Ward Willats wrote: > I found the UI thread and a worker thread, both in the DB, both in the > default busy handler, both taking a 1 second sleep. > > I expected to see a third thread in the DB doing some work while the > other two

Re: [sqlite] fsync on iOS

2014-01-16 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 16/01/14 11:43, Ward Willats wrote: > So it looks like fsync() is taking more than the 5 second timeout I've > set. This is not uncommon on mobile devices using flash based storage. There is a lot of volatility in read and write performance. I

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

2014-02-06 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/02/14 23:15, big stone wrote: > APSW looks indeed great for specialised installations. The intention behind APSW is a Python wrapper for SQLite3. It does everything the SQLite way where applicable. It advances with SQLite meaning new versions

Re: [sqlite] New

2014-02-28 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 28/02/14 16:54, Ashleigh wrote: > I'm trying to view files from my iphone backup I'm not sure which > program it is it says sqlite it is a black box like the windows command > If any one knows a better way to read and understand the files I would >

Re: [sqlite] Compiling SQLite on VxWorks 6.3 (DKM)

2014-03-01 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 28/02/14 06:37, deltuo wrote: > i compile sqlite 3.8.3 to vxworks 6.9, i first compile sqlite in dkm > and get xx.a lib file, and then test it in vip project, but meet disk > i/o error, can you help me ? thank you , my email is del...@126.com

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

2014-03-03 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/03/14 03:00, Simon Slavin wrote: > What the heck ? Is this a particular implementation of RAID ... The technical term is "write hole" and can occur at many RAID levels: http://www.raid-recovery-guide.com/raid5-write-hole.aspx You can

Re: [sqlite] SQLITE_MAX_PAGE_COUNT

2014-03-05 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/03/14 10:59, Raheel Gupta wrote: > If you point out to me the changes required I will do it and have it > reviewed. The changes required are to update the test suites (there are several) to hit/cross the current limit, to modify all relevant

Re: [sqlite] How good is pragma integrity_check

2014-03-21 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 20/03/14 18:06, Simon Slavin wrote: > All useful as far as SQLite itself goes, and better than nothing. > Unfortunately, failing hard disks do weird things in weird orders. And > the interaction between the physical hard disk and the on-board

Re: [sqlite] How good is pragma integrity_check

2014-03-23 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 21/03/14 15:24, Simon Slavin wrote: > Checksums stored with the page index lists, SQLite already has the ability to carve out data on each page for other uses. For example the encryption extension uses this. > Nevertheless, the basic SQLite

Re: [sqlite] INSERT several rows

2014-04-03 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/04/14 14:37, David King wrote: > ... because of the author's opinions of the standard Python ways to > require packages As said author, the problem is that pip etc authors chose to make it impossible to provide arguments to parts of the install

Re: [sqlite] sqlite dump makes wrong "CREATE VIEW" order

2014-04-17 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 17/04/14 00:43, Tyumentsev Alexander wrote: > Is it the user responsibility to follow all dependencies and recreate > "VIEW" tree ? The dumping is happening in the order that the views were created. This approach generally works, but fails in

Re: [sqlite] Most efficient storage for arrays

2014-04-22 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 22/04/14 05:24, Neville Dastur wrote: > I am looking for some advice on storing and searching data that comes > from an external JSON source and needs to be stored on device in a > Sqlite3 database. Your data is from MongoDB :) Note they do have

Re: [sqlite] Most efficient storage for arrays

2014-04-22 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 22/04/14 15:00, Neville Dastur wrote: > On 22 Apr 2014, at 21:58, Roger Binns <rog...@rogerbinns.com> wrote: >> Your data is from MongoDB :) Note they do have an extended JSON to >> deal with types like ObjectId, bi

Re: [sqlite] How do I know the python functions registered on SQLite ?

2014-05-08 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/05/14 09:34, big stone wrote: > Is there a way to get the list of all the 'external' functions created > in a SQLite connexion ? This is only known internally within SQLite and there is no way to get at the information from the public API.

Re: [sqlite] Pre-preparing querys

2014-05-19 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 17/05/14 15:27, Richard Hipp wrote: > The TCL interface for SQLite caches the N most recent prepared > statements (where N defaults to 10 but is configurable) and reuses > those prepared statements if the same queries are run again. That >

Re: [sqlite] Pre-preparing querys

2014-05-19 Thread Roger Binns
On 05/19/2014 02:26 PM, Simon Slavin wrote: Are there any advantages to this other than increased speed ? Nope. However I've yet to see anyone complaining that SQLite is too fast and shouldn't be faster :-) I believe that on average an app using SQLite will have a distribution where a few

Re: [sqlite] Pre-preparing querys

2014-05-20 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 19/05/14 21:09, James K. Lowden wrote: > I took "statement cache" to mean that execution plans would persist > either past sqlite3_exec() or that many plan would be kept, in case > later useful, when sqlite3_step() recompiles according to passed

Re: [sqlite] warning for partial indexes with "X AND Y"?

2014-05-23 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 23/05/14 05:26, Török Edwin wrote: > Would it be possible to show a message when someone creates a useless > index There was a ticket from two years requesting a "lint" mode to catch various issues that keep cropping up over the years:

Re: [sqlite] Reading compressed database files

2014-05-28 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 28/05/14 00:25, Hadashi, Rinat wrote: > My databases are very big (almost 100 GB). I am looking for a > compression solution. > > Did anyone have an experience with reading a compressed database? It would be helpful if you characterise your data

Re: [sqlite] Reading compressed database files

2014-05-28 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 28/05/14 02:26, Hadashi, Rinat wrote: > I have 13 tables, of which 2 are huge, 2 are medium and the rest are > very small. My huge tables have 3 columns: numeric, numeric and varchar > with millions of rows. I keep an index on the numeric columns.

  1   2   3   4   5   6   7   8   9   10   >