Re: [sqlite] SQLite Application Server Concurrency

Thanks Ryan John and Lee. Quite a few options. I will look at a 'server service' in C# using WCF as I am familiar with it. Also, investigate the MySQL avenue. Much obliged for your help. James -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org

Re: [sqlite] [EXTERNAL] SQLite Application Server Concurrency

Thanks Hick, points noted. James -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: 11 January 2018 09:23 To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] [EXTERNAL]

Re: [sqlite] SQLite Application Server Concurrency

Hi Simon, Thanks for getting back so quickly. Web programming is not something I am familiar with. My application is desktop based mostly written in C#. However, your suggestion is something I can look deeper into. James -Original Message- From: sqlite-users [mailto:sqlite-users-boun

[sqlite] SQLite Application Server Concurrency

issues or database corruption. Any thoughts would be appreciated. Regards, James ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please remove multiple warnings from compiler about optimisation, variable conversion, signed overflow and many more potential errors.

On Fri, 29 Sep 2017 16:55:05 -0400 Igor Korot wrote: > But then why not give it some default value ("0" maybe") and default > it to "1" only if needed during configure? Because complexity. It takes effort --- unnecessary effort -- to set up that default. That effort could

[sqlite] sqlite3_analyzer on Windows 10 crash

the last years digest and didn’t find it. I did try running it from an “administrator” command prompt, same error. James ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite

Re: [sqlite] What's the level of B+-Tree ?

of cells is expected to get increased and the depth of the Btree will shrink. Is this correct ? Thanks & Best Regards, James From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Richard Hipp <d...@sqlite.org> Sent: Sat

Re: [sqlite] What's the level of B+-Tree ?

's the level of B+-Tree ? On 2017/08/11 11:08 AM, Clemens Ladisch wrote: > james ni wrote: >> As in the example that I provided, there are 4 cells in a single btree >> page. So there must be some mechanism to determine hoe many keys that >> one cell can own. > I want to know

Re: [sqlite] What's the level of B+-Tree ?

Let's don't focus on the "file format" documentation, just focus on the BTree algorithm. I want to know the depth of the BTree, and how to reduce the depth of it. From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of

Re: [sqlite] What's the level of B+-Tree ?

y columns are in that particular index record (which might not match the number of columns are in the table, as noted in the spec). Is that what you're after? Or are you trying to figure out the depth of a particular btree page? -Rowan On 11 August 2017 at 16:51, james ni <james...@live.cn> w

Re: [sqlite] What's the level of B+-Tree ?

che Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von ni james Gesendet: Freitag, 11. August 2017 04:57 An: sqlite-users@mailinglists.sqlite.org Betreff: [sqlite] What's the level of B+-Tree ? In the "SQLite File Format" document, the BTre

Re: [sqlite] What's the level of B+-Tree ?

: Friday, August 11, 2017 14:41 To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] What's the level of B+-Tree ? ni james wrote: > In the "SQLite File Format" document, the BTree layout is described, > but now I want to know how to get the BTree level (which is the 'K' >

Re: [sqlite] implicit transaction is commited without sqlite3_reset or sqlite3_finalize, differs with Documentation

and repost the same question  Regards, James From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Keith Medcalf <kmedc...@dessus.com> Sent: Friday, August 11, 2017 11:19 To: SQLite mailing list Subject: Re: [sqlite] implicit

[sqlite] What's the level of B+-Tree ?

In the "SQLite File Format" document, the BTree layout is described, but now I want to know how to get the BTree level (which is the 'K' value mentioned in the Documentation)? Generally, one B+Tree segment contains K keys and (K+1) pointers to child segments. From the source code, I found

[sqlite] implicit transaction is commited without sqlite3_reset or sqlite3_finalize, differs with Documentation

Question for the paragraph in http://www.sqlite.org/lang_transaction.html: "An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes. A statement finishes when its prepared

Re: [sqlite] Does prepare do arithmetic?

On Mon, 29 May 2017 16:18:17 +0200 R Smith wrote: > Actually what Richard is saying is that you SHOULD do the arithmetic > yourself when you can, because you can boldly make assumptions about > the code that you write, but he, or the SQLite engine to be precise, > cannot be

Re: [sqlite] NOT NULL integer primary key

On Fri, 26 May 2017 18:04:14 +0200 (CEST) Eric wrote: > Why should the INSERT return an error? It is quite OK to, when > inserting a row, not specify a value for a NOT NULL column - as long > as the DDL has specified some way of constructing a value. The SQL in question is >>>

Re: [sqlite] auntondex with unique and integer primary key

On Fri, 19 May 2017 13:06:23 -0600 "Keith Medcalf" wrote: > You asked for the extra index to be created in the table > specification. It is not the job of the database engine to correct > your errors (it is not even possible to know if it is an error). He didn't ask.

Re: [sqlite] Security vulnerabilities prevalent in web tutorials for PHP, javascript, etc..

On Sun, 23 Apr 2017 21:31:42 +0100 Simon Slavin wrote: > If you don?t understand what you?re doing, hire an experienced > programmer. Ah, but you don't know what you don't know. After all, 90% of programmers rate themselves "above average". When I first heard of "SQL

Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?

On Sun, 16 Apr 2017 12:01:01 +0200 Darko Volaric wrote: > There are good reasons to have stored procedures other than reducing > connection latency - developers like to encapsulate logic that is > associated entirely with the database in the database, use them to do > extended

Re: [sqlite] Testing sqlite db to see if exists & ready

On Thu, 6 Apr 2017 13:19:38 +0100 Simon Slavin wrote: > Instead use PHP functions to check that the file exists using PHP > function "file_exists()" and then using fread() to read the first 16 > bytes from it. Those 16 bytes should be "SQLite format 3" followed > by a 0x00

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

On Sun, 2 Apr 2017 09:40:36 +0200 Clemens Ladisch wrote: > > Why not use vis(3) instead? > > Because vis() is a nonstandard function that is not available > everywhere, "everywhere" is a high standard, but vis is freely available and included or packaged with almost

Re: [sqlite] 3.18 Problem

On Sun, 2 Apr 2017 23:50:01 +1000 David Burgess wrote: > The rules for the format of these numbers has changed over time and > the schema has changed with the rules of the time. PRAGMA > integrity_check now complains about some rows not passing the > constraint. No one

Re: [sqlite] Reporting Solutions that work with SQLite / VB 2015?

On Mon, 03 Apr 2017 06:26:35 -0700 Cousin Stanley wrote: > e.g. lines with . == My mistake, sorry. A little git hiccup. You found the correct fix. And the repository now holds a correct version. --jkl

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

On Fri, 31 Mar 2017 21:59:05 +0200 Clemens Ladisch wrote: > since commit 68f6dc7af1013f29, newlines in the .dump output are > escaped with char(). Why not use vis(3) instead? No one seemed to notice when I offered the suggestion. Newline encoding is a solved problem.

Re: [sqlite] Reporting Solutions that work with SQLite / VB 2015?

On Sat, 25 Mar 2017 22:32:49 -0700 Cousin Stanley <cousinstan...@gmail.com> wrote: > James K. Lowden wrote: > > https://github.com/jklowden/sqlrpt > > I installed your sqlrpt program under debian linux > and managed to successfully run it with an s

Re: [sqlite] Reporting Solutions that work with SQLite / VB 2015?

On Fri, 24 Mar 2017 19:29:07 + (UTC) Craig Bisgeier wrote: > I am wondering if anyone can recommend a free or low-cost reporting > solution that will work with SQLite and be compatible with Visual > Basic 2015 as an embedded or callable application?  This is a project >

Re: [sqlite] Drop view ?automatically? when its associated table is dropped?

On Fri, 24 Mar 2017 11:03:10 -0400 Richard Hipp <d...@sqlite.org> wrote: > On 3/24/17, James K. Lowden <jklow...@schemamania.org> wrote: > > > > We know SQLite parses SQL to produce byte-code for its virtual > > machine. And we know that's an abstract syntax

Re: [sqlite] Drop view ?automatically? when its associated table is dropped?

On Fri, 24 Mar 2017 01:15:50 + Simon Slavin wrote: > SQLite does not seem to parse views or triggers for dependencies. If > it did it wouldn?t allow this view or these triggers. I guess you mean SQLite doesn't parse a view when it processes CREATE VIEW or, if it does,

Re: [sqlite] Drop view ?automatically? when its associated table is dropped?

On Thu, 23 Mar 2017 11:56:00 + Simon Slavin wrote: > > This problem and other related with the lack of a "data dictionary" > > in sqlite, > > Not sure what you expect here. Can you point me at a "data > dictionary" for some other implementation of SQL ?

Re: [sqlite] RIGHT JOIN! still not supported?

On Wed, 22 Mar 2017 10:53:09 -0500 Jeffrey Mattox wrote: > Isn't it possible to get the same results of a RIGHT JOIN by using > two selects with a UNION or UNION ALL between them. Yes. By definition, an outer join is the union of the rows that meet the matching criteria

Re: [sqlite] Drop view ?automatically? when its associated table is dropped?

On Wed, 22 Mar 2017 15:07:41 + Simon Slavin wrote: > On 22 Mar 2017, at 11:52am, R Smith wrote: > > > > May I add further that views are tricky things. They can refer to > > multiple tables, or other views which in turn refer to multiple > > tables

Re: [sqlite] sqlite3 feature or regression

On Tue, 7 Mar 2017 19:04:47 +0100 Vermes Mátyás wrote: > > It is also unnecessarily complex and slow. > > The script demonstrates a regression (a bug). It is written in Ruby > so that everybody can run it, and see its _results_. It is absolutely > not interesting that it is

Re: [sqlite] More built-in functions for basic math

On Thu, 9 Mar 2017 10:45:36 +0100 Dominique Devienne wrote: > I find that I'm often missing basic mathematical functions in the > default shell. $ sqlite3 -header <<< 'select typeof(1/0) as "how many";' how many null Until SQLite deals with math as math, what is the point

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

On Tue, 7 Mar 2017 20:26:41 +0100 Clemens Ladisch <clem...@ladisch.de> wrote: > James K. Lowden wrote: > > Clemens Ladisch <clem...@ladisch.de> wrote: > >> Recursive CTEs make SQL Turing complete. > >> > >> But they cannot do everything. >

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

On Tue, 7 Mar 2017 13:30:00 +0100 Clemens Ladisch wrote: > Recursive CTEs make SQL Turing complete. > > But they cannot do everything. Isn't that a contradiction? --jkl ___ sqlite-users mailing list

Re: [sqlite] sqlite3 feature or regression

On Tue, 7 Mar 2017 09:36:34 +0100 Clemens Ladisch wrote: > I do not know what you expect to happen, or what actually happens, but > changing a table and reading it through a query at the same time has > an unspecified result. It is also unnecessarily complex and slow.

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

On Sun, 5 Mar 2017 08:28:44 -0800 Yuri wrote: > > I think you?re better off reading the existing BLOB value into > > memory using SELECT, editing it using standard memory-manipulation > > routines, then writing it back with an UPDATE when you?re ready. > > This is very slow.

Re: [sqlite] Massively multithreaded SQLite queries

On Fri, 3 Mar 2017 20:13:52 + Andrew Brown wrote: > once we start running them on a large server with a lot of > multithreading going on, I find that we spend a lot of time in > __raw_spin_lock - perhaps 75%+ of the time (In one case, 87% of 350 > seconds x

Re: [sqlite] foreign key cardinality

On Tue, 28 Feb 2017 18:01:35 -0800 J Decker wrote: > Then how would you properly find the children? Or find any > information about their parent, the child would have to in turn be > deleted. > > foreign keys are as good as pointers. That's an interesting way to put it.

Re: [sqlite] foreign key cardinality

On Tue, 28 Feb 2017 08:48:02 + Hick Gunter wrote: > "If they are not the primary key, then the parent key columns must > be collectively subject to a UNIQUE constraint or have a UNIQUE > index." Thank you, Hick. I have always thought af a foreign key as an existence

Re: [sqlite] foreign key cardinality

On Mon, 27 Feb 2017 16:07:48 -0500 Richard Hipp <d...@sqlite.org> wrote: > On 2/27/17, James K. Lowden <jklow...@schemamania.org> wrote: > > SQLite requires that foreign keys refer to primary > > keys. > > No it doesn't. Where did you get that impression? sql

[sqlite] foreign key cardinality

I would like to illustrate a problem with SQLite's foreign key enforcement policy. SQLite requires that foreign keys refer to primary keys. That rule has no relational foundation, and prevents the use of foreign keys that are perfectly valid. I have these tables (non-key columns omitted for

Re: [sqlite] Thread safety of serialized mode

On Thu, 16 Feb 2017 19:35:51 -0800 Jens Alfke <j...@mooseyard.com> wrote: > > On Feb 16, 2017, at 6:26 PM, James K. Lowden > > <jklow...@schemamania.org> wrote: > > > > It doesn't change the fact that the OS has subverted the > > guaran

Re: [sqlite] Thread safety of serialized mode

On Fri, 17 Feb 2017 04:10:09 + Deon Brewis wrote: > If you look at the original underlying NT I/O architecture that > Cutler implemented - it is a thing of beauty that's based in async > patterns, and not threads. ... > If instead NT initially only exposed the Nt API's and

Re: [sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

On Wed, 15 Feb 2017 12:06:58 +0100 Marek Wieckowski wrote: > The thing is that in principle there is nothing wrong with using > test.xxx fields in the subselect: there really should be no > difference whether you use them in "where" or "order by"... The fact > that sqlite does

Re: [sqlite] Thread safety of serialized mode

On Thu, 16 Feb 2017 21:49 + Tim Streater wrote: > > What's inherently wrong with threads in principle is that there is > > no logic that describes them, and consequently no compiler to > > control that logic. > > [snip remainder of long whinge about threads] > >

Re: [sqlite] Thread safety of serialized mode

On Thu, 16 Feb 2017 16:21:06 -0700 Warren Young wrote: > https://www2.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf > > Threads aren?t just distasteful from an implementation standpoint, > they?re *mathematically unsound*. Thank you for that. I think I encounted

Re: [sqlite] Deprecated OSX functioon

On 2/16/2017 7:40 AM, Ward WIllats wrote: This has been coming across my console when building Sqlite since upgrading to Mac OSX Sierra a few months ago. FWIW. Building sqlite3 shell... sqlite3.c:20839:17: warning: 'OSAtomicCompareAndSwapPtrBarrier' is deprecated: first deprecated in macOS

Re: [sqlite] Thread safety of serialized mode

On Tue, 14 Feb 2017 17:05:30 -0800 Darren Duncan wrote: > There is nothing inherently wrong with threads in principle What's inherently wrong with threads in principle is that there is no logic that describes them, and consequently no compiler to control that logic.

Re: [sqlite] Thread safety of serialized mode

On Wed, 15 Feb 2017 09:40:13 -0800 Jens Alfke wrote: > https://en.wikipedia.org/wiki/Communicating_sequential_processes > Also search YouTube for Rob Pike's presentations on CSP in Go. It will help clarify

Re: [sqlite] Thread safety of serialized mode

On Wed, 15 Feb 2017 12:34:51 + Simon Slavin wrote: > Two disadvantages are that threads are indistinguishable to anything > but the owner and don?t know how to keep out of each-other?s way. By > the time you?ve devised some sort of mutex/locking/blocking mechanism >

Re: [sqlite] Thread safety of serialized mode

On Wed, 15 Feb 2017 07:55:16 -0700 "Keith Medcalf" wrote: > Note that for several modern OSes, the OS is nothing more than a > discontiguous saved segment (DCSS) which is mapped into *every* > process space and that process isolation is more of a myth than a > reality. Are

Re: [sqlite] bug: fields from external (being updated) table cannot be used in "order by" clause of a subselect

On Tue, 14 Feb 2017 15:06:16 +0100 Marek Wieckowski wrote: > UPDATE test > SET value = value + 100 *( > SELECT i.value > FROM test i > WHEREi.whereField = test.whereField > ORDER BY

Re: [sqlite] Faster check: > or !=

On Mon, 13 Feb 2017 15:32:00 + jose isaias cabrera wrote: > I have a bunch of records to check, and I am wondering which is a > faster check. I am attaching a network DB as client, > > ATTACH 'h:\bkup\test.db' AS client; If you're concerned about speed or

Re: [sqlite] BUG: UPDATE with correlated sub-query

On Tue, 14 Feb 2017 00:49:29 +0700 Dan Kennedy wrote: > SQLite updates the first row (with a=1) and sets column "b" to 2. > But then, when it goes to update the next row, it runs the correlated > query a second time. And this time it returns 3. So you end up > setting "b"

Re: [sqlite] Could missing indexes ids cause slow queries?

On Mon, 13 Feb 2017 22:22:24 + jose isaias cabrera wrote: > sqlite> select id from LSOpenProjects where id > 13460 and id < > sqlite> 115520; > 13461 > 13462 > 13463 > 13464 > 13758 > 115516 > 115517 > 115518 > 115519 > sqlite> > > As you can see, the id indexing is

Re: [sqlite] pragma_table_info column name is interpreted as reserved work?

On Sun, 12 Feb 2017 12:40:27 -0700 "Keith Medcalf" wrote: > I am aware that it works if you quote the apparently reserved word > "notnull". That's the queer part. I can imagine "not" and "null" as keywords, but not "nonnull". --jkl

Re: [sqlite] thousand separator for printing large numbers

On Fri, 10 Feb 2017 10:46:24 +0100 Dominique Devienne wrote: > PS: In this context, I don't want to use a host-program provided UDF. > This is data meant to be viewed with any SQLite client, so kind of > "report". https://github.com/jklowden/sqlrpt While Clemens was

Re: [sqlite] thousand separator for printing large numbers

On Fri, 10 Feb 2017 20:57:34 +0100 Dominique Devienne wrote: > the view itself calls printf. I see now. Your request is very specific: to support a thousands separator in the SQL printf. You don't want to write a UDF, and you don't want to use any facilities above the API

Re: [sqlite] thousand separator for printing large numbers

On Fri, 10 Feb 2017 10:46:24 +0100 Dominique Devienne wrote: > Couldn't SQLite's built-in printf gain a thousand-separator formatting > argument, which doesn't need to be locale aware or could be even > explicit after the arg? ... > This is data meant to be viewed with any

Re: [sqlite] sqlite3_complete

On Thu, 9 Feb 2017 00:41:34 +0200 R Smith wrote: > In the end, accurately judging the effect and validity of a statement > without actually running it to completion, is just not useful. Agreed. Since you have to test the return code from preparing the statement anyway,

Re: [sqlite] Bulk Insert in Sqlite3

On Tue, 7 Feb 2017 14:42:13 +0800 Rowan Worth wrote: > Note that golang's sql transaction abstraction doesn't map perfectly > to sqlite. Golang does not allow any further operations on the Tx > following a call to Tx.Commit() or Tx.Rollback(). But in sqlite a > transaction

Re: [sqlite] Transactions

On Mon, 6 Feb 2017 09:38:20 -0800 Jens Alfke wrote: > In some cases there?d be multiple events in a second that triggered a > database write in a transaction; when this happened down in my humble > process, it could cause iTunes playback to stutter and video capture > to lose

Re: [sqlite] Why does a query run 50x slower across a network?

On Mon, 06 Feb 2017 13:12:22 +0100 a...@zator.com wrote: > In respect to the Windows environment I've appreciated that the use > of UNC convention over a network (LAN) behaves much slower that > "mapping" the logical unit as a drive letter D, E, .. Z in the local > host. That's bizarre. By

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

On Wed, 01 Feb 2017 17:48:48 -0500 "Keith Medcalf" wrote: > Microsoft products require multiple versions of multiple Microsoft > and third-party compilers to compile any of their products. They > will simply add whatever is needed to their internal compilers suite > and use

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

On Tue, 31 Jan 2017 15:50:08 -0800 Nathan Bossett wrote: > Since this is the sqlite users list and not the dev's list, can I ask > what your use case is that writing a thin wrapper around SQLITE > doesn't solve your problem? I've encountered no problem compiling SQLite

Re: [sqlite] Bitten by lack of isolation between SELECT and UPDATE on the same connection

On Mon, 30 Jan 2017 19:29:40 -0800 Jens Alfke wrote: > if I iterate over the the rows in a table using sqlite3_step, and > update each row after it?s returned, Bad Stuff happens. Specifically, > my query is just getting the first row over and over and over again, > and the

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

On Mon, 30 Jan 2017 13:32:46 -0700 Scott Robison wrote: > Basing source on "ANSI C" (as much as possible) just gives you the > biggest possible distribution / compatibility. Yes, but it also limits you to C as it stood 20 years ago. And counting. Is there no point at

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

On Sun, 29 Jan 2017 21:40:23 -0500 Richard Hipp <d...@sqlite.org> wrote: > On 1/29/17, James K. Lowden <jklow...@schemamania.org> wrote: > > > > I wonder what pricey embedded environment both supports dlopen(2) > > and does not support C99, in this day and ag

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

On Sat, 28 Jan 2017 11:49:19 +1100 "dandl" wrote: > >>>Sticking with C90 is perfectly rational if you're still running > >>>Windows 98 on a Pentium III at 500 Mhz with 256 MB RAM. Else, > >>>really, it's not too soon to adopt a 6-year old standard, C11. > > There are plenty

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

On Fri, 27 Jan 2017 10:08:17 +1300 David Empson wrote: > Should SQLite be aiming for the 1990 version of ANSI/ISO C as a > baseline, for widest compatibility, or is it OK to drop older > compilers and require C99 compliance? Older compilers? C99 was adopted 17 years ago,

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

On Thu, 26 Jan 2017 08:19:02 + Hick Gunter wrote: > On LP_64 architactures, the integer 0 is 32 bits while (void *)0 is > 64 bits, which makes more than a bit of a difference. A 64 bit > integer 0 would be denoted by 0L. in C, as you know, integer assignment is subject to

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

On Thu, 26 Jan 2017 11:45:41 -0600 Clyde Eisenbeis wrote: > Usually I look at examples, and extract the relevant info. In the Age of Google, that has become a popular technique. But it's not a good way to learn anything. I wonder, though: how do you know what the "relevant

Re: [sqlite] How to circumvent UNIQUE constraint

On Sat, 21 Jan 2017 18:14:06 + Simon Slavin wrote: > It is a failure of atomicity in SQLite semantics. > > This is one I do feel is a bug in SQLite. Thank you for your support. I feel it's important to understand it's a bug, not a feature. > I think I?ve seen

Re: [sqlite] How to circumvent UNIQUE constraint

On Sat, 21 Jan 2017 19:33:06 +0200 R Smith wrote: > UPDATE desktops SET indexNo = -indexNo - 1 WHERE indexNo >= X; > INSERT INTO desktops ... new row for indexNo X ... ; > UPDATE desktops SET indexNo = -indexNo WHERE indexNo < 0; unless FOREIGN KEY indexNo

Re: [sqlite] How to circumvent UNIQUE constraint

On Sat, 21 Jan 2017 11:54:57 +0100 Cecil Westerhof wrote: > I would think that this would work: > UPDATE desktops > SET indexNo = indexNo + 1 > > But it does not, it gives: > Error: UNIQUE constraint failed: desktops.indexNo It should work. It does work in other

Re: [sqlite] [OT] mySQL ORDER BY clause in Views

On Wed, 18 Jan 2017 23:36:14 + Peter Haworth wrote: > if I include a WHERE claus, the view's ORDER BY clause is ignored and > the rows are returned in seemingly random order. > > Searching around the web suggests that this behavior is accepted as > correct in mySQL although

Re: [sqlite] extension to run bash

On Thu, 12 Jan 2017 01:54:28 + Roman Fleysher wrote: > From bash script, make list of rows, run commands, load results to > sqlite. Or > > From sqlite, use extension to run commands for each row. > > Both have issues. You will find your shell script faster.

Re: [sqlite] Problem with compiled dll on Windows

On Wed, 11 Jan 2017 10:46:51 -0800 Random Coder wrote: > Normally, I'd say this is toolset knowledge, and really doesn't > belong in those sort of directions. But since you're not the first > person to hit this problem Having dealt with the problem of other-system

Re: [sqlite] Null returned from NOT NULL column thanks to undetected corruption

On Fri, 13 Jan 2017 11:17:11 +0800 Rowan Worth wrote: > I wonder if this is something sqlite could catch in normal operation > and return SQLITE_CORRUPT? Or are there reasons/history which would > render this conclusion inaccurate? Not without cost. In general, it's difficult

Re: [sqlite] Problem with compiled dll on Windows

On Wed, 11 Jan 2017 12:11:36 +0200 Rael Bauer wrote: > I am trying to compile the latest amalgamation (3160200) as a dll on > Windows 10 x64, using VS2015 x86 Native Tools Command Prompt. The dll > compiles fine, however trying to use this dll in various tools > (Delphi)

Re: [sqlite] Foreign key error...

On Sun, 08 Jan 2017 05:57:46 -0700 "Keith Medcalf" wrote: > artistid integer references artists Hmph. Learn something new every day. Where is that abbreviated form documented? I looked for "references" on the Create Table page, and didn't find anything about its

Re: [sqlite] Simplify multiple use of value in a trigger

On Sat, 7 Jan 2017 17:54:20 -0500 Gan Uesli Starling wrote: > As for the suggestion to 'normalize' my four tables to one ... yes, I > could do that ... but it would make for a LOT of redundant data > duplicated in plural columns, which seems to me in my doubtless > ignorant

Re: [sqlite] SQLite3 Tutorial error

On Fri, 06 Jan 2017 13:48:26 -0700 "Keith Medcalf" wrote: > Both "glob" and "like" call the same function, likeFunc with > different sets of user_data. likeFunc does a bunch of validation > then calls patternCompare which actually implements the like and glob >

Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs

On Sat, 7 Jan 2017 10:58:56 +0100 gwenn wrote: > After 3.16, > sqlite3_step returns SQLITE_DONE > and > sqlite3_column_count does not return 0 but 6 That is the correct answer. The function returns a set of rows. Every invocation returns the same number of columns. Some

Re: [sqlite] sqlite-users Digest, Vol 109, Issue 7

On Sat, 7 Jan 2017 16:40:04 +0100 Simone Mosciatti wrote: > Now it comes a write statement, something like: `INSERT INTO ...` > I push that statement in a buffer and I ack to the client that is > request is been processed. Now it comes another write statement, > `UPDATE

Re: [sqlite] Quest for "scratch table" implementation in SQLite.

On Thu, 5 Jan 2017 19:26:26 + "Smith, Randall" wrote: > * Can be created on demand while a database connection is open. > * Can include references to other material in the database to >ensure integrity. > * Will be automatically deleted when the database is

Re: [sqlite] Performance degradation of in-memory database with high row count and overall design discussion of my project

On Thu, 5 Jan 2017 18:56:13 +0100 Simone Mosciatti wrote: > I could store in some buffer some SQL statement and I could execute > all of them in one single transaction. That's a good idea, all things being equal. But are they equal? Your problem description is entirely

Re: [sqlite] SQLite3 Tutorial error

On Fri, 6 Jan 2017 10:23:06 +1100 "dandl" wrote: > Unix globbing for Linux is defined here: > http://man7.org/linux/man-pages/man7/glob.7.html. AFAICT Sqlite does > not implement this behaviour. A quick scan of SQLite sources shows only references to the glob function, no

Re: [sqlite] (dot) output call locks up when used on a named pipe

On Sat, 31 Dec 2016 15:16:19 -0500 Paul Lambert wrote: > I have used the (dot) .output call in conjunction with a both a file > and name pipe on Linux with Sqlite 3.13 installed. With a file this > functions successfully and completes. When using a named pipe this >

Re: [sqlite] Datatype for prices (1,500)

On Thu, 1 Dec 2016 16:50:21 -0800 Jens Alfke wrote: > I understand that the consensus among those who do is to _not_ store > monetary values as floating-point, due to the roundoff error. I wouldn't be so sure. I've worked in that space for 30 years designing databases and

Re: [sqlite] locating a minimum in SQLite 2

On Thu, 1 Dec 2016 11:12:48 -0800 James Walker <jam...@frameforge3d.com> wrote: > SELECT MIN(PRICE), IDENT FROM INFO; > > and get what I want. But in SQLite 2 (legacy code), this doesn't > work... I get the minimum value, but NULL in the IDENT column. Does it work with bo

Re: [sqlite] locating a minimum in SQLite 2

On 12/1/2016 11:55 AM, Baruch Burstein wrote: On Thu, Dec 1, 2016 at 9:12 PM, James Walker <jam...@frameforge3d.com> wrote: Let's say I have a table INFO with columns PRICE and IDENT, and I want to find the IDENT of the row with the minimum value of PRICE. In SQLite 3, I can say SELE

[sqlite] locating a minimum in SQLite 2

Let's say I have a table INFO with columns PRICE and IDENT, and I want to find the IDENT of the row with the minimum value of PRICE. In SQLite 3, I can say SELECT MIN(PRICE), IDENT FROM INFO; and get what I want. But in SQLite 2 (legacy code), this doesn't work... I get the minimum value,

Re: [sqlite] Read-only access which does not block writers

On Thu, 24 Nov 2016 22:59:32 +0100 Florian Weimer wrote: > Does LMDB perform lock-free optimistic reads and > retroactively verifies that the entire read operation was consistent? In LMDB there are readers and reader-writers. A reader never writes; a reader-writer may

Re: [sqlite] Pragma to flag unknown pragma?

On Tue, 22 Nov 2016 16:08:49 -0800 Scott Hess wrote: > One could perhaps fake pedantic pragma with SQLITE_FCNTL_PRAGMA in a > custom VFS, but that seems a little excessive for something like this. > Something like "PRAGMA pedantic_pragma = on" would be much slicker. You might

Re: [sqlite] Table name syntax

On Mon, 21 Nov 2016 20:46:45 + David Raymond wrote: > insert into main.foo > select db1.foo.* > from db1.foo left outer join db2.bar > on db1.foo.pk = db2.bar.pk > where db2.bar.pk is null; Just by the way, your query could be cast as insert into main.foo select

Re: [sqlite] Changing ID's to UUID

On Fri, 18 Nov 2016 10:22:15 -0800 Jens Alfke wrote: > You?d have to assign a central ?count server? to hand out consecutive > numbers No. You need only enumerate the sources, and accept that the key is the unique counter from the source plus the centrally assigned source

Re: [sqlite] I keep getting seg faults building my database using python sqlite3

On Fri, 18 Nov 2016 10:56:37 -0800 Roger Binns wrote: > Popen calls fork (it seems like you are doing Unix/Mac, not Windows). > fork() duplicates the process including all open file descriptors. > One or more of those descriptors belong to open SQLite databases and >

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