[sqlite] Is it possible that dropping a big table takes very long

On Wed, Apr 20, 2016 at 3:31 PM, Cecil Westerhof wrote: > >>> ?Do the developers read this list, or should I post a bug report? > They do, and it's actually the preferred way to discuss issues/bugs/performances, etc... Dr. Hipp even wrote not long ago "bugs" entered would be aggressively

[sqlite] Field FOREIGN KEY when it is a pointer

On Wed, Apr 20, 2016 at 8:09 PM, Dan Kennedy wrote: > ?If I read that correctly then a field has to be NULL or point to a record >> in the other table. I want it to point to a record if it is an INT and >> whem >> it is TEXT it is just the text. (And it should only be allowed to be an >> INT >>

[sqlite] Multiple in-memory database table query

On Thu, Apr 21, 2016 at 8:20 PM, Dan Kennedy wrote: > Generally speaking, no matter how they are created, a given in-memory >> database has one and only one connection. You cannot, for example, use a >> URI ?filename? with mode=memory to open the same in-memory database more >> than once (I

[sqlite] No datasize field - why?

On Fri, Apr 22, 2016 at 3:09 PM, Igor Korot wrote: > [code] > SQLite version 3.9.2 2015-11-02 18:31:45 > Enter ".help" for usage hints. > sqlite> PRAGMA table_info(leagues); > 0|id|integer|0||1 > 1|name|varchar(100)|0||0 > 2|drafttype|integer(1)|0||0 > 3|scoringtype|integer(1)|0||0 >

[sqlite] huge difference between clustered/nonclustered index usage

On Fri, Apr 22, 2016 at 6:55 PM, Richard Hipp wrote: > On 4/22/16, Dimitris Bilidas wrote: > > SELECT count(qview1."wlbWellboreName") FROM "discovery" qview2 CROSS > > JOIN "wellbore_development_all" qview1 WHERE > > (qview1."wlbNpdidWellbore" = qview2."wlbNpdidWellbore"); > > You are aware

[sqlite] Pretty-printing. Was: huge difference between clustered/nonclustered index usage

On Fri, Apr 22, 2016 at 7:05 PM, Richard Hipp wrote: > In the next release, the ".schema" command will support a command-line > option "--indent" which does pretty-printing of the schema, turning > the above into the example shown below. The pretty-printer is simple > but it seems to work.

[sqlite] No datasize field - why?

On Mon, Apr 25, 2016 at 3:31 AM, Simon Slavin wrote: > > These are different concerns, and they don't really pose any > > difficulty. Given an encoding, a column of N characters can take up to > > x * N bytes. Back in the day, "x" was 1. Now it's something else. No > > big deal. > > No.

[sqlite] sqlite3 command line, read-only

On Mon, Apr 25, 2016 at 2:33 PM, Mark Foley wrote: > I'm new to the list, so apologies if this has been posted before. > > I am using sqlite3 3.7.17 on Linux Slackware64 14.1. I use the sqlite3 > command > every 10 minutes to query several Thunderbird calendar databases. > Occasionally, I > have

[sqlite] sqlite3 command line, read-only

On Mon, Apr 25, 2016 at 2:53 PM, Mark Foley wrote: > On Mon, 25 Apr 2016 14:41:30 Dominique Devienne > wrote: > > > Perhaps you're missing the leading file: ? I believe it is required. --DD > > No, unless I'm putting it in the wrong place. As I wrote I did: $ sqli

[sqlite] sqlite3 command line, read-only

On Mon, Apr 25, 2016 at 3:35 PM, Mark Foley wrote: > On Mon, 25 Apr 2016 15:14:56 Dominique Devienne > wrote: > > Please (re)read the example I included. --DD > > C:\Users\DDevienne>sqlite3 *file:*new.db?mode=ro > > Sorry, perhaps I'm a bit obtuse. I don't see ho

[sqlite] sqlite3 command line, read-only

On Mon, Apr 25, 2016 at 4:28 PM, Mark Foley wrote: > I'm running this on Linux. That Windows notation doesn't work. > > sqlite3 > "*file:*/mnt/tmp/Users/.../AppData/Roaming/Thunderbird/Profiles/8sbpxrwj.default/calendar-data/local.sqlite\?mode=ro" > "select distinct value from cal_properties

[sqlite] sqlite3 command line, read-only

On Mon, Apr 25, 2016 at 6:12 PM, Mark Foley wrote: > Nope: still error: > > sqlite3 \ > '*file:*/mnt/tmp/Users/hcarr.HPRS.000/AppData/Roaming/Thunderbird/Profiles/8sbpxrwj.default/calendar-data/local.sqlite?mode=ro' > \ > "select distinct value from cal_properties where key = 'CATEGORIES'" >

[sqlite] ANALYZE, sqlite_stat1, and query planning

On Thu, Apr 28, 2016 at 5:28 PM, Rowan Worth wrote: > In an attempt to understand a slow query I've had a quick look at the > contents of the sqlite_stat1 table. It looks like the stat column contains > a series of integers like: > > > > > ... > > Is this observation correct? And if so, does

[sqlite] ANALYZE, sqlite_stat1, and query planning

On Thu, Apr 28, 2016 at 5:53 PM, Richard Hipp wrote: > > It looks like the ENABLE_STAT2/STAT4 options generate histogram data > which > > would probably help for non-uniform data? > > Yes, STAT4 provides histogram data to help with query planning on > tables with non-uniform data distributions.

[sqlite] Table entries disappearing for unknown reason.

On Tue, Feb 2, 2016 at 10:32 AM, Clemens Ladisch wrote: > Vin?cius da Silva wrote: > > #pragma omp parallel for > > for( int i = 0; i < dispatchedThreads; ++i ) > > Does OMP guarantee that there is exactly one thread for each > loop iteration? > Yes,

[sqlite] json1 not escaping CRLF characters

On Thu, Feb 4, 2016 at 10:43 AM, Eric Grange wrote: > I have been experimenting with using the json1 extension to generate json > sql-side rather than code-side, but I am hitting an issue with the CR & LF > characters which are not escaped, which results in json that fails > JSON.parse or use as

[sqlite] json1 not escaping CRLF characters

On Thu, Feb 4, 2016 at 11:04 AM, Dominique Devienne wrote: > On Thu, Feb 4, 2016 at 10:43 AM, Eric Grange wrote: > >> I have been experimenting with using the json1 extension to generate json >> sql-side rather than code-side, but I am hitting an issue with the CR &

[sqlite] How to enter Unicode character?

On Mon, Feb 8, 2016 at 4:17 PM, Igor Korot wrote: > Now my question is: is it possible to enter a Unicode character > Yes. Just use the char() built-in function. --DD

[sqlite] How to enter Unicode character?

On Mon, Feb 8, 2016 at 4:28 PM, Dominique Devienne wrote: > On Mon, Feb 8, 2016 at 4:17 PM, Igor Korot wrote: > >> Now my question is: is it possible to enter a Unicode character >> > > Yes. Just use the char() built-in function. --DD > Oh, by "SQLite table con

[sqlite] How to enter Unicode character?

On Mon, Feb 8, 2016 at 4:46 PM, Igor Korot wrote: > On Mon, Feb 8, 2016 at 10:36 AM, Dominique Devienne > wrote: > What I mean is the following: > > sqlite> CREATE TABLE abc(); > > In that line '' should be the German character which look like the > Greek letter &qu

[sqlite] How to enter Unicode character?

On Mon, Feb 8, 2016 at 5:33 PM, Dominique Devienne wrote: > On Mon, Feb 8, 2016 at 4:46 PM, Igor Korot wrote: > >> On Mon, Feb 8, 2016 at 10:36 AM, Dominique Devienne >> wrote: >> What I mean is the following: >> >> sqlite> CREATE TABLE abc(); >

[sqlite] How to enter Unicode character?

On Mon, Feb 8, 2016 at 5:43 PM, Dominique Devienne wrote: > sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?) from > t?; > c?|length(c?)|length(cast(c? as blob))|unicode(c?) > ??|1|2|252 > ?|1|1|129 > > sqlite> .schema >> > CREATE T

[sqlite] How to enter Unicode character?

On Mon, Feb 8, 2016 at 6:26 PM, Dominique Devienne wrote: > OK, I retried with latest sqlite3.exe, and the results are different: > > C:\Users\DDevienne>sqlite3 > SQLite version 3.10.2 2016-01-20 15:27:19 > Enter ".help" for usage hints. > Connected to a tran

[sqlite] Version 3.11.0 beta

On Thu, Feb 11, 2016 at 1:10 PM, Olivier Mascia wrote: > > Le 11 f?vr. 2016 ? 10:48, Olivier Mascia a ?crit : > > It looks like the appropriate character set mapping behavior on Windows > is still not quite right in the command line utility. > > If I'm changing shell.c (utf8_printf) to use some

[sqlite] Unicode issue on windows consoles. Was: Version 3.11.0 beta

On Thu, Feb 11, 2016 at 5:07 PM, Stephan Beal wrote: > You might want to look at: > > https://www.fossil-scm.org/index.html/finfo?name=src/utf8.c Interesting. Thanks for sharing. fossil_utf8_to_console() does seem to be WriteConsoleW-based, when the http://www.siao2.com

[sqlite] Unicode issue on windows consoles. Was: Version 3.11.0 beta

On Fri, Feb 12, 2016 at 11:36 AM, Olivier Mascia wrote: > > So it would be a bad idea to change sqlite3's output depending on the > > current code page or font. > > I share your view on keeping things as unicode as possible, but the > frontier is thin before trying to impose our views onto

[sqlite] Small Doc Issue

in https://www.sqlite.org/c3ref/wal_hook.html and will those overwrite any prior sqlite3_wal_hook() settings should be and those will overwrite any prior sqlite3_wal_hook() settings no? --DD

[sqlite] How to remove duplicate records

On Wed, Feb 24, 2016 at 8:17 AM, wrote: > Now I want to eliminate duplicate record so for two records whose F1 and F2 > values are identical, even if their F3 values are different, then one > record > with the largest value of F3 will be kept and the other one will be > removed. > > Is that

[sqlite] How to remove duplicate records

On Wed, Feb 24, 2016 at 1:58 PM, Clemens Ladisch wrote: > Dominique Devienne wrote: > >Always ran into Error: only a single result allowed for a SELECT that > >is part of an expression. --DD > > In a join, you can directly use a subquery with multiple column

[sqlite] Using My Own Encryption Possible ?

On Mon, Feb 29, 2016 at 2:18 PM, Simon Slavin wrote: > Another way would be to write your own authorizer which, among other > things, did some encryption of the data involved. > It's the first time I read someone proposing using an authorizer

[sqlite] whish list for 2016

On Fri, Jan 8, 2016 at 12:14 AM, James K. Lowden wrote: > I would like to see a strict mode, too. +1 > I would also like to be able to make "strictness" a property of the > database, not the connection. > +1 similarly I'd like enforcing FKs to be per database, not per connection. > One way

[sqlite] whish list for 2016

On Wed, Jan 13, 2016 at 2:39 AM, Simon Slavin wrote: > On 12 Jan 2016, at 11:56pm, Scott Hess wrote: > > > If I am writing a client that can read SQLite databases, then I probably > > don't want your database to be injecting a bunch of arbitrary PRAGMA > calls > > into my client. > > It is,

[sqlite] Are there practical limits to a not-so simple schema?

On Wed, Jan 13, 2016 at 12:43 PM, Olivier Mascia wrote: > Is there any known structural performance issue working with a schema made > of [many] tables, [...] foreign keys constraints, [...] indexes [...] > primary keys and foreign keys. [...] tables would have [many] columns and > [...] rows >

[sqlite] Are there practical limits to a not-so simple schema?

On Wed, Jan 13, 2016 at 4:06 PM, Olivier Mascia wrote: > Some kind of MVCC is very interesting to us for the purpose of running > read transactions which would see a stable view of the data, not seeing at > all the writes committed after the start of the read transactions. If I'm > not

[sqlite] Using sqlite3.exe as a subprocess

On Fri, Jan 15, 2016 at 4:53 AM, Matthew Allen wrote: > It seems that sqlite3.exe (console) doesn't work as a subprocess with > pipes. > [...] I expect there is something funny going on with sqlite3.exe's > stdout/stdin. Sorry to highjack your thread Matthew, but I have what I consider a

[sqlite] Best way to terminate a dead-transaction: commit or rollback?

On Fri, Jan 15, 2016 at 7:25 PM, Keith Medcalf wrote: > While it is true that without further action each statement is carried out > in a separate transaction, it may very well be that the OP wants to have > what is called Repeatable-Read. The only way to obtain Repeatable-Read > between

[sqlite] Wrong Index Select with Large Table Joined to Small Table

On Fri, Jan 22, 2016 at 1:29 PM, Richard Hipp wrote: > The (partial) index is also useless for sorting: > > SELECT * FROM t1 ORDER BY x; > Is that a fundamental thing though? Or a pragmatic implementation decision? Nulls come before or after non-null, no? So couldn't the sort work like

[sqlite] Pragma automatic_index and ROWID: which one is really responsible for automatic indexes?

On Thu, Jan 28, 2016 at 7:17 PM, Yannick Duch?ne wrote: > I though automatic index was controlled by the pragma `automatic_index`, > which seems to do nothing for me, unless I'm doing it the wrong way, while > `WITHOUT ROWID` on table creations, seems to effectively prevent automatic > indexes,

[sqlite] Finding records containing non-ascii characters

On Fri, Jan 29, 2016 at 1:53 PM, Cecil Westerhof wrote: > 2016-01-29 10:12 GMT+01:00 Richard Hipp : > > On 1/29/16, Cecil Westerhof wrote: > > > In H2 I can find records containing non-ascii characters with: > > > SELECT * > > > FROM > > > WHERE STRINGENCODE() LIKE '%\\u%' > > > >

[sqlite] Is the first column of a composite primary key, special?

On Sun, Jan 31, 2016 at 2:54 PM, Yannick Duch?ne wrote: > Another mystery to me. Given this test table: > > CREATE TABLE t (a TEXT, b TEXT, c INTEGER, PRIMARY KEY (a, b, c)) > > ??this query: > > SELECT Sum(c) FROM t GROUP BY a > > ??executes faster than any of these two: > > SELECT

[sqlite] Are BLOB stored inline with the rest of the record fields?

On Fri, Mar 4, 2016 at 4:45 PM, Eric Grange wrote: > [...] why can't we both have our cake and eat it? :) > Exactly. That's why I've been asking/advocating for out-of-row (large) blobs for a while now. See for example http://permalink.gmane.org/gmane.comp.db.sqlite.general/97686. --DD

[sqlite] Changing the default page_size in 3.12.0

On Fri, Mar 4, 2016 at 4:48 PM, Richard Hipp wrote: > https://www.sqlite.org/draft/releaselog/3_12_0.html - from 2000 to -2000 + from 2000 to 500 [OT] The PRAGMA defer_foreign_keys=ON statement now also disables RESTRICT actions on foreign key. I've confused by that Richard. Does that

[sqlite] How to read data from WAL?

On Mon, Mar 7, 2016 at 6:52 AM, Sairam Gaddam wrote: > My main aim is to find the fresh changes which are to be made to database > through WAL module. > To achieve what goal? In any case, perhaps https://www.sqlite.org/rbu.html is of interest to you. --DD

[sqlite] Reserved column names

On Sat, Mar 19, 2016 at 11:56 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > My rule of thumb is to always quote (using square brackets) when the > query is generated by code, and only quote where reasonable when the > query is written by a human. > Seems like using

[sqlite] Reserved column names

On Mon, Mar 21, 2016 at 11:01 AM, Cezary H. Noweta wrote: > On 2016-03-21 08:57, Dominique Devienne wrote: > >> Seems like using square-brackets instead of double-quotes is non-standard: >> >> https://en.wikibooks.org/wiki/SQL_Dialects_Reference/Data_structure_definiti

[sqlite] Reserved column names

On Mon, Mar 21, 2016 at 12:18 PM, Stephen Chrzanowski wrote: > AFAIK, SQLite comes from the grass roots of PostgreSQL. Whatever > 'standards' it adheres to, SQLite goes by. As a fact, I don't know what > those standards are, and I don't care what they are. The documentation on > SQLite.org

[sqlite] Reserved column names

On Tue, Mar 22, 2016 at 12:36 AM, James K. Lowden wrote: > Roger's APSW is SQLIte specific. It's pretty easy to imagine, isn't > it, that > > char sql[] = "select [col] from [foo]"; > > is easier for him to use than > > char sql[] = "select \"col\" from \"foo\""; > > even if

[sqlite] Reserved column names

On Tue, Mar 22, 2016 at 8:45 AM, Dominique Devienne wrote: > On Tue, Mar 22, 2016 at 12:36 AM, James K. Lowden < > jklowden at schemamania.org> wrote: > >> Roger's APSW is SQLIte specific. It's pretty easy to imagine, isn't >> it, that >> >>

[sqlite] sqlite fixed data loading extension

On Fri, Mar 25, 2016 at 12:49 PM, Don V Nielsen wrote: > > (I guess this begets the question..."Is sqlite's csv import an extension?") No, it is not an extension, it's part of SQLite's shell directly: https://www.sqlite.org/cli.html#csv Which also means it's not part of the SQLite library

[sqlite] Doc Typo

In doc for new https://www.sqlite.org/c3ref/system_errno.html API s#the most reason I/O error#the most recent I/O error#g

[sqlite] Fastest way to backup/copy database?

On Wed, May 4, 2016 at 12:44 PM, Rob Willett wrote: > We?re trying to backup a 10GB live running database [...] > 2. If we use the command line sqlite .dump > it > works, but its very slow. That's going to SQL text. While .backup is page-based, and binary. But not incremental in the Shell I

[sqlite] Fastest way to backup/copy database?

On Wed, May 4, 2016 at 1:13 PM, Rob Willett wrote: > Thanks for the reply, > > Yes Example 2 in https://www.sqlite.org/backup.html is what we are talking > about. It was very slow to run for us. Then maybe https://www.sqlite.org/rbu.html is your last change. Although I don't see how it could be

[sqlite] Fastest way to backup/copy database?

On Wed, May 4, 2016 at 1:22 PM, Dominique Devienne wrote: > On Wed, May 4, 2016 at 1:13 PM, Rob Willett > wrote: >> Thanks for the reply, >> >> Yes Example 2 in https://www.sqlite.org/backup.html is what we are talking >> about. It was very slow to ru

[sqlite] Fastest way to backup/copy database?

On Wed, May 4, 2016 at 1:26 PM, Dominique Devienne wrote: > On Wed, May 4, 2016 at 1:22 PM, Dominique Devienne > wrote: >> On Wed, May 4, 2016 at 1:13 PM, Rob Willett >> wrote: >>> Thanks for the reply, >>> >>> Yes Example 2 in https://www.

[sqlite] Fastest way to backup/copy database?

On Wed, May 4, 2016 at 5:51 PM, Scott Robison wrote: > > > This is going to become a bigger problem for us as the database will > > > only get bigger so any advice welcomed. > > Perhaps, rather than backing up the live data, you create an append only > log of each and every query you send to the

[sqlite] Which CHECK constraint failed

On Mon, May 9, 2016 at 3:21 PM, Cecil Westerhof wrote: > I made a table with a few CHECK constraints. When an INSERT is not > possible, I would like to know which CHECK constraint fired. Is there a way > to get this information? Just names your CHECK constraint, and use a recent version of

[sqlite] Which CHECK constraint failed

On Mon, May 9, 2016 at 3:46 PM, Dominique Devienne wrote: > On Mon, May 9, 2016 at 3:21 PM, Cecil Westerhof > wrote: >> I made a table with a few CHECK constraints. When an INSERT is not >> possible, I would like to know which CHECK constraint fired. Is there a way >>

[sqlite] Which CHECK constraint failed

On Mon, May 9, 2016 at 3:48 PM, Dominique Devienne wrote: > On Mon, May 9, 2016 at 3:46 PM, Dominique Devienne > wrote: >> On Mon, May 9, 2016 at 3:21 PM, Cecil Westerhof >> wrote: >>> [...] Is there a way to get this information? >> >> Just name

[sqlite] 2 different SQLite versions inside the same process space

On Wed, May 11, 2016 at 4:16 AM, Roger Binns wrote: > On 10/05/16 10:42, Andrey Gorbachev wrote: >> I am a bit worried that the initialisation of 2 different versions of SQLite >> would interfere with one another. Any advice? > > There is a way to do it, [...] create a .c file that near the top

[sqlite] SQL Help: Finding case insensitive matches

Imagine I have a single table containing a single text column, of user names. I'm trying to find user names which differ only by case. > select count(*) from os_users 1969 > select u1.user, u2.user from os_users u1, os_users u2 where upper(u1.user) = > upper(u2.user) and u1.user <> u2.user

[sqlite] SQL Help: Finding case insensitive matches

On Fri, May 13, 2016 at 5:23 PM, Clemens Ladisch wrote: > Dominique Devienne wrote: >> select u1.user, u2.user from os_users u1, os_users u2 where upper(u1.user) >> = upper(u2.user) and u1.user <> u2.user; >> [...] but they are slow (almost 3s, with close to 4M step

[sqlite] SQL Help: Finding case insensitive matches

On Fri, May 13, 2016 at 5:27 PM, John McKown wrote: >> > select group_concat(user) from os_users group by upper(user) having >> count(*) > 1 >> foo,FOO >> > > how about: SELECT group_concat(user,"|") FROM os_users GROUP BY > upper(user) HAVING count(*) > 1 Thanks John. Sorry, I wasn't clear.

[sqlite] SQL Help: Finding case insensitive matches

On Fri, May 13, 2016 at 6:06 PM, Igor Tandetnik wrote: > On 5/13/2016 11:51 AM, Dominique Devienne wrote: >> But it still returns both rows. Any idea on that part? > > Change "u1.user <> u2.user" to "u1.user < u2.user" Seems obvious once someone clever shows you, yet isn't... Thanks Igor!

[sqlite] Sqlite incompatibility with Postgres

On Fri, May 13, 2016 at 7:33 PM, wrote: > Derby: > SELECT * FROM schemaTableName FETCH FIRST ROW ONLY > [...] > Oracle: > SELECT * FROM schemaTableName WHERE ROWNUM=1 Oracle 12c added FETCH FIRST ROW ONLY too. --DD

[sqlite] Any way to do inexpensive SQLite database/table versioning? (I.e. cheap snapshotting & jumping between)

On Sunday, May 15, 2016, Mikael wrote: > > Would there be any facility whereby after each transaction I do on a > database or table, I could somehow make a snapshot so that at any future > point in time, I could easily do a SELECT to a given version/snaphot? WAL basically does this already. But

[sqlite] constraint failed message and no clue what went wrong

On Mon, May 23, 2016 at 10:39 AM, Bernd Lehmkuhl wrote: > [...] What might cause a "constraint failed" message following this > command: [...] > Which version of SQLite? More recent ones tell you which constraint failed, when they are named, which yours are (a good thing IMHO). So using a

[sqlite] constraint failed message and no clue what went wrong

On Mon, May 23, 2016 at 12:22 PM, Bernd Lehmkuhl wrote: > > > Dominique Devienne hat am 23. Mai 2016 um 11:20 > geschrieben: > > On Mon, May 23, 2016 at 10:39 AM, Bernd Lehmkuhl < > bernd.lehmkuhl at mailbox.org > > > [...] What might cause a "constraint fa

[sqlite] View workarounds

On Mon, May 23, 2016 at 4:49 PM, Steve Schow wrote: > My suggestion is add the extra columns you need to the view, then when you > make a query against that view, only specify the more limited set of output > columns you want in the final output > SQLite almost supports what's needed, but only

Re: [sqlite] SELECT the "middle" of a large ordered table

On Fri, May 27, 2016 at 10:02 PM, Clay Gerrard wrote: > [...] These container databases are normally small (<1-4M rows) - and you > can have many of them (>10M) - but depending on the usage pattern - they can > also grow big (100+ GB) when there are many many object rows

Re: [sqlite] constraint failed message and no clue what went wrong

On Monday, May 23, 2016, Bernd Lehmkuhl <bernd.lehmk...@mailbox.org> wrote: > > > Dominique Devienne <ddevie...@gmail.com <javascript:;>> hat am 23. Mai > 2016 um 13:42 > Could it be your you "knoten" and "punkte" tables have values with the

Re: [sqlite] How to verify referential integrity of SQLite database

On Wed, Dec 12, 2012 at 10:01 PM, Jos Groot Lipman wrote: > When you enable foreign keys *after* you insert records, no guarantee is > given by SQLite that the foreign key constraints are fulfilled. This may not > be what most users would want or expect but it certainly not a

Re: [sqlite] Lock Database temporarily.

On Tue, Jan 8, 2013 at 2:17 PM, Simon Slavin wrote: > On 7 Jan 2013, at 4:46pm, sants87 wrote: > >> Hi I am using Sqlite3 for my application and I require to lock the database >> from any third party modification. Accessing database is fine but I

Re: [sqlite] Deletion slow?

On Tue, Feb 5, 2013 at 1:54 PM, Jason Gauthier wrote: > I am a fairly new user of sqlite, but not particularly new to SQL > principles. I am developing an application that will run on a low end system. Load the same data into another RDBMS you're familiar with, and see

Re: [sqlite] Maximum Number of Open Simultaneous connections in the latest release of SQLite

On Wed, Feb 20, 2013 at 2:07 PM, Simon Slavin wrote: > On 20 Feb 2013, at 12:57pm, Frank Chang wrote: > > Good morning, Could someone please tell us the maximum number of open > > simultaneous connections in the latest release of SQLite? Thank you.

Re: [sqlite] like query

On Tue, Feb 26, 2013 at 2:31 PM, Clemens Ladisch wrote: > ... 'somedata/' || CAST(x'F48FBFBF' AS TEXT) > Great trick! But it hardly qualifies as user friendly though, no? For our app, I added a chr() SQL function that take an arbitrary number of integers and UTF-8 encodes

Re: [sqlite] like query

On Wed, Feb 27, 2013 at 11:23 AM, Clemens Ladisch <clem...@ladisch.de>wrote: > Dominique Devienne wrote: > > My $0.02 is that such a chr() function could/should be built-in to > SQLite. > > Apparently, drh has a time machine: > http://www.sqlite.org/cgi/src/info/2

Re: [sqlite] like query

On Wed, Feb 27, 2013 at 3:16 PM, Igor Tandetnik <i...@tandetnik.org> wrote: > On 2/27/2013 4:35 AM, Dominique Devienne wrote: > >> PS: Something else that should also be part of SQLite built-in is the >> optimization that col LIKE 'prefix%' queries should implicitly try t

Re: [sqlite] Full covering index without table

On Tue, Mar 5, 2013 at 1:59 AM, Carlos Milon Silva wrote: > Also, You could not rebuild the index from the index, if necessary. I'm not sure what your point is Carlos. The table is the index, so there's no index to drop or rebuild. What Eleytherios is referring to, is known

Re: [sqlite] Break on cumulative sum

On Tue, Mar 5, 2013 at 9:24 AM, Nico Williams wrote: > On Tue, Mar 5, 2013 at 1:19 AM, James K. Lowden > > 2. Aggregation. The engine passes the rows to be aggregated to the > > function. After the last row, it calls the function's "xFinal" > > function, which returns a

Re: [sqlite] Break on cumulative sum

On Tue, Mar 5, 2013 at 9:47 AM, Nico Williams wrote: > Well, under the hood table functions could use temp, gensym'ed virtual > tables for all I care. The point is I want table functions :) SQLite already has table functions of sort: PRAGMAs... They have their own issues

[sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On Wed, Mar 6, 2013 at 3:29 PM, Ryan Johnson wrote: > Off topic, I'd love a way to request strong typing for a column (so that attempts to store 'abc' into an int column would fail). You can emulate it with a pair of before/update triggers (select raise(...) where

Re: [sqlite] SQLite3

On Wed, Mar 6, 2013 at 3:34 PM, wrote: > > it would be wise if you use a tool like the SQLite Expert; that will > > generate statements that you can use on command line as well. > > And a couple of other free tools are: > SQLite Administrator - http://sqliteadmin.orbmu2k.de/ >

Re: [sqlite] Full covering index without table

On Tue, Mar 5, 2013 at 4:05 PM, Richard Hipp wrote: > Both implementations allow for reading just the prefix of the content blob > in order to access earlier fields of a table, leaving the tail of the blob > unread on disk. This information sparked a bit of a debate among my

Re: [sqlite] Full covering index without table

On Thu, Mar 7, 2013 at 9:50 AM, Dominique Devienne <ddevie...@gmail.com> wrote: > On Tue, Mar 5, 2013 at 4:05 PM, Richard Hipp <d...@sqlite.org> wrote: > > Both implementations allow for reading just the prefix of the content blob > > in order to access earlier fields o

Re: [sqlite] Full covering index without table

On Thu, Mar 7, 2013 at 11:50 AM, Richard Hipp wrote: > When a row is larger than a single page, the content spills into multiple > pages where the pages are strung together as a linked list. You have to > read each page in order to follow the linked list. That makes sense.

[sqlite] Windows-Specific 2-c-files Amalgamation?

I understand the "deployment" ease and performance advantage of the amalgamation. I like it. Except I can't debug it... 'Cause I'm primarily on Windows, which has a well-known limitation in the way it stores Debug Information (uses 16-bit integer for the line number, so limited to ~64K lines,

Re: [sqlite] create view doesn't work

I'm no expert, but I think your problem comes from the excessive parentheses you are using. Below's a little experiment that reproduces your syntax, with its error, and provides an alternative simpler syntax that works. --DD C:\Users\DDevienne>sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05

Re: [sqlite] Non-English username in WinRT causes SQLiteException

On Wed, Mar 13, 2013 at 4:01 PM, Ercan Özdemir wrote: > However, does every developer have to write or change his code like this? > Indeed, it's whoever wrote that SQLite3.Open() .NET wrapper on top of the C-API that's responsible from doing proper conversion from that

[sqlite] Order of ON DELETE CASCADE specified in SQLite?

I stumbled upon http://stackoverflow.com/questions/60168/in-what-order-are-on-delete-cascade-constraints-processedand tried a simplified version in SQLite3, which appears to work fine (see below). Is this just happenstance in this case or will it work every-time there are similar "pseudo cycles"

Re: [sqlite] Non-English username in WinRT causes SQLiteException

On Wed, Mar 13, 2013 at 5:47 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 13 Mar 2013, at 3:39pm, Dominique Devienne <ddevie...@gmail.com> wrote: > > Indeed, it's whoever wrote that SQLite3.Open() .NET wrapper on top of the > > C-API that's responsible from d

Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

On Mon, Mar 11, 2013 at 5:21 PM, Bert Huijben <b...@qqmail.nl> wrote: > > -Original Message- > > From: ... On Behalf Of Dominique Devienne > > Except I can't debug it... 'Cause I'm primarily on Windows, which has a > > well-known limitation in the way it sto

Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

On Mon, Mar 11, 2013 at 5:41 PM, Richard Hipp <d...@sqlite.org> wrote: > On Mon, Mar 11, 2013 at 12:17 PM, Dominique Devienne <ddevie...@gmail.com >wrote: > > Perhaps someone knows a trick or two to work-around that MS debugging issue? > See the http://www.sqlite

Re: [sqlite] Order of ON DELETE CASCADE specified in SQLite?

On Wed, Mar 13, 2013 at 7:36 PM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 03/14/2013 12:09 AM, Dominique Devienne wrote: >> I stumbled upon >> http://stackoverflow.com/questions/60168/in-what-order-are-on-delete-cascade-constraints-processed >> >> Is th

Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

On Wed, Mar 13, 2013 at 10:59 PM, Random Coder <random.co...@gmail.com>wrote: > On Wed, Mar 13, 2013 at 1:14 PM, Dominique Devienne <ddevie...@gmail.com> > wrote: > > On Mon, Mar 11, 2013 at 5:21 PM, Bert Huijben <b...@qqmail.nl> wrote: > >> Since

Re: [sqlite] Inserting a CURRENT_TIMESTAMP value into a BIG INT column seems to work

On Tue, Mar 19, 2013 at 12:51 PM, Philipp Kursawe wrote: > INSERT INTO test VALUES(CURRENT_TIMESTAMP) > > This goes through without an error and the physical db file then really > contains the current timestamp as a string. CURRENT_TIMESTAMP is of text type. Simon's

Re: [sqlite] [sqlite-dev] SQLite version 3.7.16

On Tue, Mar 19, 2013 at 11:25 PM, Richard Hipp wrote: > > On Tue, Mar 19, 2013 at 6:18 PM, Petite Abeille wrote: > > On Mar 19, 2013, at 1:33 PM, D. Richard Hipp wrote: > > > http://www.sqlite.org/releaselog/3_7_16.html > > > > •

Re: [sqlite] Timeline for full ALTER TABLE support?

On Wed, Mar 27, 2013 at 2:41 PM, Clemens Ladisch wrote: > Tim Gustafson wrote: > > That page also says that things are listed there in the order they're > > likely to be implemented in SQLite. Is there a timeline or road map > > anywhere that could inform us as to when we

Re: [sqlite] How to achieve fastest possible write performance for a strange and limited case

On Fri, Mar 29, 2013 at 8:17 PM, Jeff Archer < jsarc...@nanotronicsimaging.com> wrote: > I could write directly to a file myself. But I would need to provide some > minimal indexing, some amount of housekeeping to manage variable > sized BLOBS and some minimal synchronization so that multiple

Re: [sqlite] Bug or some misunderstanding?

On Wed, Apr 3, 2013 at 1:54 AM, Support wrote: > sqlite3 -line ~/Desktop/maps.db 'select * from airports where LocationID > like "USE%";' > I get correct result. > > But when I call > sqlite3 -line ~/Desktop/maps.db 'select * from airports where > LocationID=="USE";' > SQL

Re: [sqlite] Bug or some misunderstanding?

On Wed, Apr 3, 2013 at 1:54 AM, Support wrote: > Hi > I have a database which has an entry "USE" in a table called airports with > column LocationID. > When I call > sqlite3 -line ~/Desktop/maps.db 'select * from airports where LocationID > like "USE%";' > I get correct

<    1   2   3   4   5   6   7   8   >