[sqlite] Auto Reply: Auto Reply: Re: sqlite-users Digest, Vol 38, Issue 4

This is an auto-replied message. I have left Oracle. My new e-mail address is n...@cryptonector.com. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Auto Reply: Re: sqlite-users Digest, Vol 38, Issue 4

This is an auto-replied message. I have left Oracle. My new e-mail address is n...@cryptonector.com. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

On Wed, Feb 02, 2011 at 04:45:16PM -0800, Jeff Rogers wrote: > Nicolas Williams wrote: > >On Wed, Feb 02, 2011 at 03:23:49PM -0800, Jeff Rogers wrote: > > > >>SQLite seems to do quite poorly performance-wise with fully-normalized > >>attribute tables like thi

Re: [sqlite] Question about database design

On Wed, Feb 02, 2011 at 03:38:07PM -0800, Jim Morris wrote: > I'd probably move the analyze out of the loop. > > Since your joining on props.id a better index pind might be > create index pind on props (id, pnam) Yes, you probably want two covering or partially-covering indexes: CREATE INDEX

Re: [sqlite] Question about database design

On Wed, Feb 02, 2011 at 03:23:49PM -0800, Jeff Rogers wrote: > Andreas Kupries wrote: > > > It seems to me that you are looking for > > http://en.wikipedia.org/wiki/Database_normalization > > > > SQLite seems to do quite poorly performance-wise with fully-normalized > attribute tables like

Re: [sqlite] WAL for single user database on NFS and Solaris

On Sun, Jan 30, 2011 at 11:06:34AM +0800, Ginn Chen wrote: > I think a single process accessing a single Sqlite database at a time over > NFS is supposed to be fine. > > But it is not working on Solaris. > On Solaris, man page of mmap() has > > EAGAINThe file to be mapped is already

Re: [sqlite] LAST() function not supported

On Sat, Jan 29, 2011 at 01:06:07PM -0800, Marian Cascaval wrote: > On Sat, January 29, 2011, Nicolas Williams wrote: > > If there's enough indices to satisfy all the ORDER BY expressions then > > how could a "last()" function do any better? For that matter, if there

Re: [sqlite] LAST() function not supported

On Sat, Jan 29, 2011 at 01:03:05PM -0800, Marian Cascaval wrote: > Here's the info source on LAST() function: > > http://www.w3schools.com/sql/sql_func_last.asp > > > > I needed to retrieve the last row from a table. If you need the "last row from a table" that's trivial to do efficiently in

Re: [sqlite] LAST() function not supported

On Sat, Jan 29, 2011 at 12:38:37PM -0800, Marian Cascaval wrote: > Hi! > > Is LAST() function going to be supported? > > Or will the "SELECT ... FROM ... ORDER BY ... DESC LIMIT 1" workaround always > be > enough? > > My concern is if there might be any speed improvement if LAST() function

Re: [sqlite] how to use group_concat uniquely

On Wed, Jan 19, 2011 at 09:19:54AM -0800, Noah Hart wrote: > Any ideas? You have two columns to sub-group by independently, as it were. You need correlated sub-queries to get that done: sqlite> SELECT f1.e, (SELECT group_concat(f2.t, ';') ...> FROM foo f2 WHERE f1.e = f2.e GROUP BY f2.p),

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

On Tue, Jan 18, 2011 at 10:13:10PM +0100, Florian Weimer wrote: > * Richard Hipp: > > > I don't think it makes sense in SQL (not just SQLite but SQL in > > general) for an aggregate query to return columns that are not in > > the GROUP BY clause. > > Isn't this just what PostgreSQL implements as

[sqlite] Procedural SQL techniques (Re: How to track a record if the aliased id is changed)

On Fri, Jan 14, 2011 at 10:53:56AM -0600, Nicolas Williams wrote: > But that function would have to track those changed rowids somewhere. > The trigger I posted does exactly that, using SQLite3's own primitives > (a temp table in this case): Speaking of which, the lack of procedural pr

Re: [sqlite] How to track a record if the aliased id is changed

On Fri, Jan 14, 2011 at 10:20:22AM +, Philip Graham Willoughby wrote: > On 13 Jan 2011, at 17:27, Nicolas Williams wrote: > > On Thu, Jan 13, 2011 at 10:59:29AM +, Simon Slavin wrote: > >> CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE ON TestTable >

Re: [sqlite] How to track a record if the aliased id is changed

On Thu, Jan 13, 2011 at 10:59:29AM +, Simon Slavin wrote: > His problem is that he doesn't know which rows are aliased to rowid, > so he can't provide a list of column names. So the following might be > closer > > CREATE TEMP TRIGGER [UpdatedRowIdTrigger] AFTER UPDATE ON TestTable > BEGIN >

Re: [sqlite] How to track a record if the aliased id is changed

On Wed, Jan 12, 2011 at 10:07:36PM +, Simon Slavin wrote: > On 12 Jan 2011, at 9:57pm, Max Vlasov wrote: > > Simon, your reply led me to the following sequence: > > - I know the rowid of the record I'm changing. I remember all integers (and > > all other data) I'm going to change in the Update

Re: [sqlite] Advice on breaking trigger recursion?

On Fri, Jan 07, 2011 at 09:54:07PM -0600, Nicolas Williams wrote: > On Sat, Jan 08, 2011 at 01:29:23AM +, Simon Slavin wrote: > > On 8 Jan 2011, at 1:12am, Nicolas Williams wrote: > > > I need to use recursive triggers. In some cases I want to "normalize" > &g

Re: [sqlite] Advice on breaking trigger recursion?

On Sat, Jan 08, 2011 at 12:07:08PM +0700, Dan Kennedy wrote: > On 01/08/2011 08:12 AM, Nicolas Williams wrote: > > I need to use recursive triggers. In some cases I want to "normalize" > > values of some columns of NEW being INSERTed or UPDATEd, but there's no > >

Re: [sqlite] tcl and NULL

IMO the best solution is to have a special procedure passed to the Tcl SQL function that must be called to set the return value of the SQL function. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Low-load methods of finding previous or next row

And if you use parametrized queries then you get this query plan: 0|0|TABLE toy VIA MULTI-INDEX UNION 0|0|TABLE toy WITH INDEX toy_abc 0|0|TABLE toy WITH INDEX toy_abc 0|0|TABLE toy WITH INDEX toy_abc The ORed terms are optimized as a UNION, with each sub-query using the index. That's three

Re: [sqlite] Low-load methods of finding previous or next row

On Wed, Dec 15, 2010 at 04:29:42AM -0600, Nicolas Williams wrote: > The ORed terms are optimized as a UNION, with each sub-query using the > index. That's three index operations per column that you order by. Not > bad. s/three/one/ ___ sql

Re: [sqlite] Low-load methods of finding previous or next row

On Wed, Dec 15, 2010 at 03:56:06AM -0600, Nicolas Williams wrote: > SELECT * FROM toy > WHERE > a >= (SELECT a FROM toy WHERE id = 6) OR > (a = (SELECT a FROM toy WHERE id = 6) AND > b <= (SELECT b FROM toy WHERE id = 6)) OR > (a = (SE

Re: [sqlite] Low-load methods of finding previous or next row

On Tue, Dec 14, 2010 at 05:09:04PM +, Simon Slavin wrote: > I recently found out that when you use LIMIT in SQLite the engine > still processes all applicable records even if it only has to return > the number you asked for. I suspect that this makes something I used > to do inefficient. So

Re: [sqlite] SQLITE 3.7.3 bug report (shell) - output in column?mode does not align UTF8-strings correctly

On Fri, Nov 26, 2010 at 06:52:56AM +, Niklas Bäckman wrote: > Igor Tandetnik writes: > > Note that counting codepoints, while it happens to help with your > > particular data, won't help in general. Consider combining > > diacritics: U+00E4 (small A with diaeresis) looks the

Re: [sqlite] Trouble with TRIGGERS

Do you have recursive triggers enabled? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

On Tue, Oct 19, 2010 at 09:39:44AM -0400, Richard Hipp wrote: > On Mon, Oct 18, 2010 at 8:19 AM, wrote: > > > I made an error in my SQL when I did not include one of my non-aggregate > > columns in my group. I was surprised that Sqlite did not catch this, and > > even

Re: [sqlite] WAL file growth concern

On Fri, Oct 22, 2010 at 09:56:22PM -0400, Richard Hipp wrote: > On many (most?) filesystems, it is faster to overwrite an existing area of a > file than it is to extend the file by writing past the end. That's why > SQLite doesn't truncate the WAL file on each checkpoint - so that subsequent >

Re: [sqlite] programmatic way of determining fragmentation?

On Sat, Oct 23, 2010 at 02:12:19AM +0400, Max Vlasov wrote: > As for your initial question, I think fragmentation evaluation is possible > with the help of VFS. I'd keep a total sum of of absolute difference between > consequent read offsets for xRead operation. In this case if some xRead >

Re: [sqlite] Speed up DELETE of a lot of records

On Fri, Oct 08, 2010 at 05:49:18PM +0100, Simon Slavin wrote: > On 8 Oct 2010, at 5:48pm, Stephan Wehner wrote: > > On Fri, Oct 8, 2010 at 7:14 AM, Michele Pradella > > wrote: > >> "science fiction?" was a rhetorically question. I'm only wondering > >> about what is

Re: [sqlite] SQLite database sync

On Thu, Oct 07, 2010 at 09:09:19PM +0200, Petite Abeille wrote: > On Oct 6, 2010, at 5:50 PM, David Haymond wrote: > > If I copy, I don't want to transfer EVERY record to the server each time I > > sync, because that would be a waste of bandwidth. What is the best way to > > copy only those

Re: [sqlite] Tricky grouping query

On Fri, Oct 01, 2010 at 08:37:07PM +0100, Andy Chambers wrote: > Given the following > > create table events ( > id, > date, > status > ); > insert into events values ('001','a','N'); > insert into events values ('001','b','N'); > insert into events values ('001','c','Y'); > insert into

Re: [sqlite] Need help with self-join (I think)

On Sat, Sep 25, 2010 at 01:01:36AM +0200, Kristoffer Danielsson wrote: > CREATE TABLE Test (TestID INTEGER PRIMARY KEY, Year INT NOT NULL, Name TEXT > NOT NULL); > INSERT INTO Test (Year, Name) VALUES (2007, 'A'); > INSERT INTO Test (Year, Name) VALUES (2007, 'B'); > INSERT INTO Test (Year, Name)

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

On Fri, Sep 17, 2010 at 02:02:44PM -0400, Igor Tandetnik wrote: > Keith Roberts wrote: > > I think what I really mean is I want a text column with only > > one unique value, that is also indexed. > > UNIQUE constraint will do just that. > > > Also ,I guess the INTEGER

Re: [sqlite] what could be the reason that natural join stops working ?

On Wed, Sep 15, 2010 at 08:05:26PM +0200, Stef Mientki wrote: > On 15-09-2010 11:36, Benoit Mortgat wrote: > > Are you sure that after altering your tables adding columns, natural > > join still only joins on vlid? > > > no, very stupid of me !! > I added a column to each of the tables, with the

Re: [sqlite] Detecting storage class from C

On Wed, Sep 08, 2010 at 07:01:19PM -0400, Igor Tandetnik wrote: > Schrum, Allan wrote: > > I guess I'm confused as to why sqlite3_column_type() would not work? It > > works for me on queries and I get back either > > SQLITE_INTEGER, SQLITE_FLOAT, or SQLITE3_TEXT. While

Re: [sqlite] Suggestion for project: Chat

On Tue, Aug 24, 2010 at 10:23:55PM +0530, Roger Binns wrote: > On 08/24/2010 09:51 PM, Artur Reilin wrote: > > I thinking about coding a chat with sqlite. > > The hard part of implementing chat is not storing the messages, but > rather dealing with all the clients at the same time especially

Re: [sqlite] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)

On Fri, Aug 06, 2010 at 02:11:33PM -0400, Richard Hipp wrote: > If "ch" is an unsigned char then how is the following unsafe: > > ch = (ch<0x80) ? tolower(ch) : ch > > And why does it need to be changed to > > ch = (ch>='A' && ch<='Z') ? ch - 'A' + 'a' : ch; > > There is only one such

Re: [sqlite] open db cx to fork(2)d children

On Mon, Jul 12, 2010 at 11:25:07PM -0700, Roger Binns wrote: > > No, just entry points into the library. What makes you think that I > > meant that every function in the library should check this? > > I also meant entry points but wasn't specific. There are a lot of them that > acquire/release

Re: [sqlite] open db cx to fork(2)d children

On Mon, Jul 12, 2010 at 08:43:32PM -0700, Roger Binns wrote: > > They're insane (the _first_, not last, fildes close(2) in a process > > drops all locks on the underlying file), but the child won't clobber the > > parent's locks. > > That is assuming all components (C libraries, threading,

Re: [sqlite] open db cx to fork(2)d children

On Mon, Jul 12, 2010 at 08:43:32PM -0700, Roger Binns wrote: > Earlier you were trying to optimise out calls to getpid() and now you want > every SQLite function source to be changed? No, just entry points into the library. What makes you think that I meant that every function in the library

Re: [sqlite] open db cx to fork(2)d children

On Mon, Jul 12, 2010 at 02:47:19PM -0700, Roger Binns wrote: > About the only correct thing to do in a process using SQLite through a fork > is to terminate the process. You can return an error to the caller too. Of course, if you're using the mutex functions to do this then it's too late, so

Re: [sqlite] open db cx to fork(2)d children

On Mon, Jul 12, 2010 at 01:59:30PM -0500, Nicolas Williams wrote: > The result is that you end up with a tiny penalty for fork > detection: two loads, a compare and a likely-not-taken branch. Actually three loads, two compares and two likely-not-taken branch, unless you kn

Re: [sqlite] open db cx to fork(2)d children

On Sat, Jul 10, 2010 at 11:27:41AM -0700, Roger Binns wrote: > On 07/10/2010 07:12 AM, Eric Smith wrote: > > Your wrapper is nice -- have you considered folding something like it > > into the core (disabled by default, enabled by a compile-time flag) and > > submitting it to drh for official

Re: [sqlite] open db cx to fork(2)d children

On Fri, Jul 09, 2010 at 02:38:08PM -0700, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/09/2010 02:31 PM, Nicolas Williams wrote: > > The trick to making that go fast is to use pthread_atfork() to get the > > new PID on the child side of

Re: [sqlite] open db cx to fork(2)d children

On Fri, Jul 09, 2010 at 02:22:37PM -0700, Roger Binns wrote: > On 07/09/2010 01:52 PM, Eric Smith wrote: > > What do you mean, "immediately"? As I said, my child comes to life, > > does some work without touching (its copy of) existing SQLite strucures, > > and then calls exit(2). > > I'll bet

Re: [sqlite] open db cx to fork(2)d children

On Fri, Jul 09, 2010 at 04:52:35PM -0400, Eric Smith wrote: > > > I strongly recommend that you always make the child side of fork(2) > > either exit(2) or exec(2) immediately. > > Sorry Nico, I never saw this response -- I appreciate it! > > What do you mean, "immediately"? Good question.

Re: [sqlite] open db cx to fork(2)d children

On Mon, Jun 28, 2010 at 11:30:49AM -0400, Eric Smith wrote: > From the docs: > > > Under Unix, you should not carry an open SQLite database across a > > fork() system call into the child process. Problems will result if you > > do. > > What if I fork a process that promises not to use the

Re: [sqlite] 64 bit libsqlite for AIX and Solaris

On Thu, Jun 24, 2010 at 01:37:50PM +0530, Sushil wrote: > I am looking for 64 bit libsqlite for AIX and Solaris. Is there a > place from where I can get them pre-built ? For OpenSolaris you can find SQLite3 packages, including a 64-bit build of the library, in the OpenSolaris IPS /release package

Re: [sqlite] marking transaction boundaries

On Tue, Jun 22, 2010 at 08:35:14AM -0700, b s wrote: > hi, > long ago, drh had proposed a trigger like mechanism that > can be invoked at the begin/end of a transaction. > http://osdir.com/ml/db.sqlite.general/2003-04/msg00137.html > > the general consensus was there is no use other than up'ng >

Re: [sqlite] Composite Foreign Key not enforced

On Fri, May 14, 2010 at 03:01:39PM -0400, George Somers wrote: [edited for brevity] > CREATE TABLE parent(parentID INTEGER PRIMARY KEY, ...); > > CREATE TABLE child(childID INTEGER PRIMARY KEY, fatherID INTEGER, ...); > > CREATE TABLE fatherChildActivity( >fcChildID INTEGER, >

Re: [sqlite] Changing file descriptor of database file

On Fri, May 07, 2010 at 05:24:58PM -0400, Mikhail Terekhov wrote: > IMHO it would be better to switch to poll/epoll from select instead of > fighting file descriptor numbers. Better: use libevent. (select()'s limit of 1024 fildes is... odd and derives mainly from the FD*() macros and their

Re: [sqlite] select %column% from table

On Fri, Apr 30, 2010 at 07:57:05AM -0700, David Lyon wrote: > if I had many many files like this: > http://research.stowers-institute.org/efg/ScientificSoftware/Utility/FCSExtract/CC4_067_BM.txt > > you see 2 columns keyword and value, the keywords would be the fields > (1st column in the html

Re: [sqlite] copy one row to another

sqlite> CREATE TABLE foo(id INTEGER PRIMARY KEY, a, b, c); sqlite> insert into foo values(1, 'a', 'b', 'c'); sqlite> select * from foo; 1|a|b|c sqlite> CREATE TEMP TABLE tempfoo AS SELECT * FROM foo WHERE id = 1; sqlite> UPDATE tempfoo SET a = 'z'; sqlite> INSERT OR REPLACE INTO foo SELECT * FROM

Re: [sqlite] Berkeley DB adds SQL using SQLite API !!

On Wed, Mar 31, 2010 at 06:16:13PM -0700, Jim "Jed" Dodgen wrote: > On Wed, Mar 31, 2010 at 8:50 AM, Wiktor Adamski > >> (3) Each table and index is in a > >> separate file so your "database" was a directory full of files instead > >> of a single file > > > > This one is not a problem. Actually I

Re: [sqlite] SQLite over NFS

On Mon, Mar 29, 2010 at 07:50:38AM -0700, Kumar, Abhinav wrote: > I am using SQLite version 3.5.9. My db sizes are 50-100 Gb. My DB is a > typical star schema. I am seeing an order of magnitude more time to do > a simple select query when doing over NFS (30-60 seconds) as compared > to local disk

Re: [sqlite] Available alternatives to syntax diagrams in documentation

On Tue, Mar 23, 2010 at 11:50:10PM -0400, Shane Harrelson wrote: > I have no idea what kinds of things a "screen reader" would need to make > this version useful, but if you let me know, I will try to add them. Screen readers are used by those who have impaired sight or not sight (i.e.,

Re: [sqlite] Computing day of week the using strftime() function - Feature or bug ?

On Tue, Mar 23, 2010 at 07:25:56PM +, Bernie Reiter wrote: > I am checking this for Sunday, March 21st 2010, Monday, 22nd March 2010 and > Tuesday, 23nd March 2010: > > Sunday, March 21st 2010:SELECT strftime('%w',2010-03-21); => 6 You need single quotes around the date value.

Re: [sqlite] Why we don't have strpos function?

On Wed, Mar 17, 2010 at 03:02:59PM -0500, Jay A. Kreibich wrote: > On Wed, Mar 17, 2010 at 04:42:59PM -0300, Israel Lins Albuquerque scratched > on the wall: > > are you right the call to strpos("??", "??") are returning 5 and > > not 3 > > > > I'm looking for this... > > You can't

Re: [sqlite] parallelizing an update

On Tue, Feb 02, 2010 at 09:23:36AM +0100, Sylvain Pointeau wrote: > On Mon, Feb 1, 2010 at 5:16 PM, Nicolas Williams > <nicolas.willi...@sun.com>wrote: > > Now to parallelize this: > > > > function par_updates { > I would be very interested to see some bench

Re: [sqlite] parallelizing an update

On Sat, Jan 30, 2010 at 10:36:56AM +0100, Sylvain Pointeau wrote: > echo "begin transaction" >> update.sql > > sqlite3 -separator $'\t' sample.db 'select rowid, item from foo;' | > while read rowid item ; do > status=$(long_running_process "${item}" ) > echo "update foo set status=${status}

Re: [sqlite] using SQLite to store test-data having C.J.K.T. characters, in it?

On Wed, Jan 06, 2010 at 08:21:05AM +0530, Harsha wrote: > I am developing a small testing application, which in vague way does > compare/diff etc. b/w the results obtained by executing a CLI command > under test. The output of this command may contain Asian character > such as Chinese(C),

Re: [sqlite] ICU collation

On Tue, Dec 22, 2009 at 07:49:24AM -0500, Tim Romano wrote: > On 12/22/2009 5:31 AM, Sylvain Pointeau wrote: > > It cannot be done in the application layer... > > > You are wrong about that. I have written a full-text search application > to go against ancient Germanic texts where, for

Re: [sqlite] ICU collation

On Tue, Dec 22, 2009 at 06:56:29AM +, Simon Slavin wrote: > On 22 Dec 2009, at 4:55am, Sylvain Pointeau wrote: > > How is [this?] supposed to work ICU in SQLite? > > I hope someone can answer your question. I don't know enough. I don't know about ICU but, really this is something that needs

Re: [sqlite] sqlite files and locking on Lustre filesystems.

On Sun, Dec 13, 2009 at 11:02:18AM -0800, George Hartzell wrote: > My computing environment at work is bringing a new cluster online, in > addition to some local storage there will be shared access to SAN > style storage using the Lustre filesystem. > > My reading about Lustre tells me that it

Re: [sqlite] BUG: The sqlite3 shell does not call setlocale

On Sat, Dec 12, 2009 at 12:39:23PM -0800, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Alexey Pechnikov wrote: > > On unix the shell must do this initialisation: > > > > setlocale(LC_ALL, ""); > > Why? Yes I know what the call does, but what desirable effect does it

Re: [sqlite] The byte order mark problem in fields with ICUcollation

On Mon, Dec 07, 2009 at 05:35:49PM -0500, Igor Tandetnik wrote: > Alexey Pechnikov > wrote: > > The normalization is now performed by any string operation. But more > > fast and useful to do it once at data store. > > So, which normalization form should the data store

Re: [sqlite] The next release of SQLite....

Use the glob operator. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WHERE CLAUSE in UNICODE

On Mon, Nov 30, 2009 at 08:56:48PM +, Simon Slavin wrote: > On 30 Nov 2009, at 6:49pm, Nicolas Williams wrote: > > See my previous message: it would make no sense to have a column with > > data-dependent collations. But perhaps I'm missing something. Can you > > d

Re: [sqlite] WHERE CLAUSE in UNICODE

On Mon, Nov 30, 2009 at 06:37:11PM +, Simon Slavin wrote: > > On 30 Nov 2009, at 5:51pm, Nicolas Williams wrote: > > > Consider a column that contains a person's last name. Q: do proper > > names have a language? A: No, since people can be from all over and > > e

Re: [sqlite] WHERE CLAUSE in UNICODE

On Mon, Nov 30, 2009 at 01:21:08PM -0500, Igor Tandetnik wrote: > Nicolas Williams <nicolas.willi...@sun.com> wrote: > > IMO you'll have two types of text to sort: a) generic text (e.g., > > proper names), b) localized text (e.g., message catalogs). For (a) > &g

Re: [sqlite] WHERE CLAUSE in UNICODE

On Mon, Nov 30, 2009 at 08:15:58AM +, Simon Slavin wrote: > On 30 Nov 2009, at 1:58am, Igor Tandetnik wrote: > > Note that Unicode collation is not as simple as you might think. Did > > you know that in Estonian, 'y' sorts between 'i' and 'j'? Or that in > > German phonebook sort, 'oe' sorts

Re: [sqlite] Unicode support

On Tue, Nov 17, 2009 at 09:31:46PM -0500, Tim Romano wrote: > but if ORDER BY is > relying on an index for ordering, then flip() can have negative > effects. > > > Substr() could have negative effects on ordering too. That is a red > herring. Flip() is merely a function that reverses the

Re: [sqlite] Unicode support

On Tue, Nov 17, 2009 at 05:15:16PM -0500, Igor Tandetnik wrote: > Nicolas Williams <nicolas.willi...@sun.com> wrote: > > This is no longer true, either of 'ch' nor 'll'. > > There is a number of contractions in Hungarian that are still very > much in use, but I can't recal

Re: [sqlite] Unicode support

On Tue, Nov 17, 2009 at 02:01:55PM -0500, Igor Tandetnik wrote: > This would mean that the result of the hypothetical flip() function > would be locale-dependent. E.g. in Spanish Traditional sort, a > combination 'ch' sorts as if it were a single letter between 'c' and > 'd', forming a single sort

Re: [sqlite] Output in currency format

On Fri, Nov 13, 2009 at 03:07:27AM +, Simon Slavin wrote: > > On 13 Nov 2009, at 12:34am, Nicolas Williams wrote: > > > On Thu, Nov 12, 2009 at 11:40:23PM +, Simon Slavin wrote: > >> There's still some possibility for confusion, however: how many places >

Re: [sqlite] Output in currency format

On Thu, Nov 12, 2009 at 11:40:23PM +, Simon Slavin wrote: > There's still some possibility for confusion, however: how many places > of decimals do you use for each currency ? As far as I know, no > currently traded currency uses more than two digits of precision.

Re: [sqlite] feature proposal - strong but dynamic typing

On Fri, Oct 30, 2009 at 03:59:11PM -0500, Jay A. Kreibich wrote: > On Fri, Oct 30, 2009 at 03:19:59PM -0500, Nicolas Williams scratched on the > wall: > > I should add that a pragma that cause CHECK constraints to be > > automatically created for enforcing strong typing in

Re: [sqlite] feature proposal - strong but dynamic typing

I should add that a pragma that cause CHECK constraints to be automatically created for enforcing strong typing in subsequent CREATE TABLE statements is rather like having FOREIGN KEY clauses automatically generate triggers. There's precedent, in other words, and it is a simple way to implement

Re: [sqlite] feature proposal - strong but dynamic typing

On Fri, Oct 30, 2009 at 01:30:31PM -0400, John Crenshaw wrote: +1 I don't think this proposal can or will be accepted. One reasonable idea, perhaps, would to have a pragma that causes subsequent CREATE TABLE statements to get automatically generated CHECK expressions that enforce typing. Any

Re: [sqlite] Late data typing. Am I missing something?

On Wed, Oct 28, 2009 at 07:11:29PM +, O'Neill, Owen wrote: > You can get close if you put some check constraints on the columns. This is key: you can get the benefits of static and dynamic data typing. > I must agree with other posters that the lack of an exposed timestamp > type does feel

Re: [sqlite] Idea for improving page cache

On Tue, Oct 27, 2009 at 04:28:11PM -0400, John Crenshaw wrote: > "advantage" kind of depends. ULL is more specialized. You gain some > benefit, but also lose some as well. For example, consider what is > involved in doing a sorted insert into an ULL. On the other hand, you > can get all of the

Re: [sqlite] Degree character not displayed correctly.

On Mon, Oct 26, 2009 at 10:01:43AM -0700, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Jean-Christophe Deschamps wrote: > > First decide or determine what is (or shall be) your database > > encoding. Even if SQLite has no problem storing ANSI (or EBCDIC or > >

Re: [sqlite] index for a group by

On Thu, Oct 22, 2009 at 12:34:26AM +0200, Sylvain Pointeau wrote: > if your "book" contains all lines (a,b,c,t,d)and you create an index on > (a,b,c,t) > > then your index is as fat as your book, isn't it? Depends on the size of d. Also, if you add a constraint declaring t, a, b, and c (you

Re: [sqlite] Bug in date() function ??

On Tue, Oct 13, 2009 at 03:30:44PM -0400, D. Richard Hipp wrote: > Begin with 2001-03-31 > Add 1 to 03, yielding 2001-04-31 > 04-31 means the 31st day from the beginning of april: 2001-05-01 > > Begin with 2001-03-31 > Subtract 1 from 03 yielding 2001-02-31. > 02-31 means the 31st day from the

Re: [sqlite] Compiler warnings with Sun Studio 12.1 on Solaris 10

On Tue, Sep 29, 2009 at 05:28:39PM -0700, Roger Binns wrote: > Nicolas Williams wrote: > > On Tue, Sep 29, 2009 at 11:21:30AM -0700, Roger Binns wrote: > >> Nicolas Williams wrote: > >>> If you move the cast to the left the warning should go away:

Re: [sqlite] Compiler warnings with Sun Studio 12.1 on Solaris 10

On Tue, Sep 29, 2009 at 12:05:21PM -0700, Jim Showalter wrote: > Warnings are never harmless--they clutter the build output and > introduce cognitive dissonance when trying to see if a build is clean > or not. > > I worked on a project where they hadn't enabled warnings during > development

Re: [sqlite] Compiler warnings with Sun Studio 12.1 on Solaris 10

On Tue, Sep 29, 2009 at 11:21:30AM -0700, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Nicolas Williams wrote: > > If you move the cast to the left the warning should go away: > > ((sqlite3_int64)(1L<<63)) > > And this is why m

Re: [sqlite] SQL language question

On Wed, Sep 23, 2009 at 06:12:13PM +0100, Simon Slavin wrote: > > On 23 Sep 2009, at 5:12pm, Nicolas Williams wrote: > > > On Tue, Sep 22, 2009 at 04:45:31PM -0400, D. Richard Hipp wrote: > >> UPDATE t1 SET x=x; -- key line: Is this considered an "updat

Re: [sqlite] SQL language question

On Tue, Sep 22, 2009 at 04:45:31PM -0400, D. Richard Hipp wrote: > UPDATE t1 SET x=x; -- key line: Is this considered an "update" > of t1.x? Igor pointed to the standards text, which I think is quite reasonable: an update is only an update if something changes. The same should probably

Re: [sqlite] Faster inserts in SQlite ...

On Mon, Sep 21, 2009 at 03:37:02PM -0400, Pavel Ivanov wrote: > > Have you any IO operations? As result you have dependence of page > > size. > > Though your performance most probably will not depend on these > operations because they will be executed at some random times by OS. > And they will

Re: [sqlite] SQLite 3.6.17

On Thu, Sep 03, 2009 at 03:27:38PM +0100, Simon Slavin wrote: > On 3 Sep 2009, at 1:38am, P Kishor wrote: > > well, I think the problem is with the sqlite3 command line tool. > I agree. I just checked it with OS X 10.6, which comes with SQLite > version 3.6.12, and got the same problem: you

Re: [sqlite] Booleans in SQLite

On Thu, Sep 03, 2009 at 04:21:28PM -0400, Wilson, Ronald wrote: > > Wrong pragma. Try: > > Thanks. I'm going to stop talking for a few days now. Enough gaffs for > one day. Heh. But, actually, why doesn't SQLite3 produce an error when unknown pragmas are used? Wouldn't that be the right

Re: [sqlite] Booleans in SQLite

On Thu, Sep 03, 2009 at 03:57:14PM -0400, Wilson, Ronald wrote: > Hmm. I can't get the pragma to return a value at all. > > SQLite version 3.6.10 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> pragma default_file_format; Wrong pragma. Try: sqlite>

Re: [sqlite] Defining user groups in SQLite table

On Thu, Sep 03, 2009 at 10:57:13AM -0700, karenebs wrote: > I have a database table that holds about 20,000 codes. Each code can be used > by several different user groups. I could add a column to the database > table for each user group to indicate which codes that particular group has > access

Re: [sqlite] Booleans in SQLite

On Wed, Sep 02, 2009 at 05:44:38PM -0400, Shaun Seckman (Firaxis) wrote: > I'm just curious how difficult it would be to add > support for booleans in SQLite. This would most likely involve adding a > new type affinity as well as adding "true" and "false" keywords to the > lexer.

Re: [sqlite] Looking for a w_char alternative for sqlite3_get_table

On Tue, Sep 01, 2009 at 10:41:27AM +0200, A.J.Millan wrote: > * Make sure there was no 16-bit version of the sqlite3_get_table at > function -perhaps it would be a good idea to include it in the standard API. > The reason is the same who advised include the current version. It might be easier

[sqlite] genfkey as loadable module (Re: Run genfkey on temp db from app)

On Mon, Jul 20, 2009 at 08:34:52AM -0500, Jay A. Kreibich wrote: > If someone is going to take the time to refactor the genfkey code > into a stand-alone piece of code, I would like to suggest it is made > into it a loadable module, so that it can be used pretty much anywhere. That sounds

Re: [sqlite] Do people think of SQLite as a file or as a database

On Wed, Jul 15, 2009 at 07:11:51PM -0700, CadMapper wrote: > > This is not a technical question about SQLite. I want to you how people in > general think about SQLite. Is that a file or a database? When you talk > about it, do you refer to it as file or database? It's a database. It happens

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

On Tue, Jul 07, 2009 at 12:17:36AM +0100, Simon Slavin wrote: > He didn't make up the rule. Nor did I. It's part of the standard for > mailing lists and usenet: > > This is way off-topic now, but, to be fair, RFC1855 is not a standard, it is an

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

On Mon, Jul 06, 2009 at 02:49:07PM -0700, James Gregurich wrote: > 1) Why on earth would you want to scroll all the way to the bottom of > a long email to get the response simply for the sake of "We read > English top to bottom." Any quoted context must be read before the reply or else is not

Re: [sqlite] referential integrity and INSERT OR REPLACE

On Sat, Jul 04, 2009 at 10:24:50AM +0200, Kees Nuyt wrote: > On Fri, 03 Jul 2009 14:38:43 -0700, James Gregurich > wrote: > > > > >nuts. that makes INSERT OR REPLACE worthless if you have tables > >dependent on one another. > > > > > >Is there any way to manually get a

  1   2   3   >