Re: [sqlite] After column add, what should be done to update the schema?

Bonjour, I use a simple ALTER TABLE ADD COLUMN statement. However, when I use the Sqlite Expert, the DDL is not reflected. Even though the column is created. I read somewhere that the DDL is really just a copy of the text when teh table was created. Is there a way to refresh this? I can't re

Re: [sqlite] How to determine the column type?

dump+100 (string), pass+1000 (string) the type information disappears and former integers becomes mere strings There must be something else going on here: Z:> sqlite3 SQLite version 3.30.1 2019-10-10 20:19:45 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".o

Re: [sqlite] Roadmap?

Hi Simon, Especially since it can't do something more fundamental than STDEV: return all surnames starting with the Unicode character 'Å'. Reconsider as this works fine. JcD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http

Re: [sqlite] using lower function with utf8

I was messing about with this and tried the following in sqlite expert professional select unicode(lower(char(256))); I was quite surprised when it responded with the correct result 257. Looking at the sqlite3.c code I can’t see anything that suggests sqlite would handle lower() for non-a

Re: [sqlite] [EXTERNAL] select for power-meter accumulated total readings

Oops, didn't notice the date of said row was out of sequence wrt rowids. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] select for power-meter accumulated total readings

Beware that row id 6655 isn't correct (total_kwh is suddenly decreasing). This inconsistancy maybe related to row 6654 missing: manipulated data? So you'd have to sanitize your data first. ID TIMESTAMP TOTAL_KWH 66532019-08-06 22:23:26.000 1494.00 66552019-07-30 22:32:26.000 15

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

Simon, Consider a bank which takes an audit every day at close-of-business. This might be declared to be 5pm. However, accounts are continued to be debited and credited all night, due to interest being added, ATM transactions, etc.. Nevertheless, the audit needs to see a snapshot as of 5pm

Re: [sqlite] misuse of aggregate function max()

That is what I use now, together with the select as Hick mentioned. An other version I played with is two indexes, one unique on (id, ts_from) to find the last version if eol is not null and one unique on (id, ts_eol) where eol = null to find the current active version of id. Beware that in

Re: [sqlite] Row locking sqlite3

You can use a simple 'L' flag on the rows you want locked and add a where to don't touch them. I'm afraid things are more complicated in many real-world cases. Locking a single row isn't enough. What if the UPDATE or DELETE forces deep changes in one or more indices? What if the UPDATE or D

Re: [sqlite] sqlite trig/geometry error

sqlite> select degrees( radians(175) + atan2( sin(radians(90)) * sin(0.2/6378.14)*cos(radians(-42)), cos(0.2/6378.14) - sin(radians(-42))*sin(radians(-42; 264.997582396241 postgres=# select degrees( radians(175) + atan2( sin(radians(90)) * sin(0.2/6378.14)*cos(radians(-42)), cos(0.2/6378.14

Re: [sqlite] Subject: Re: SQL Date Import

The problem not having a DATETIME field is, however, very simple: When reading a foreign database which stores date values as a number, I have to guess on how to get back the correct date. The datatype used is irrelevant w.r.t. this issue. Unless fully qualified with convention used and poss

Re: [sqlite] possible bug: select clause column alias shadowing

At 23:36 18/03/2018, you wrote: In other words, aliases in the SELECT clause are evaluated _after_ the FROM and WHERE clauses are done. The order of the SELECT/WHERE clauses in the SQL syntax is misleading; the actual behaviour would be better represented by something like this: ( FROM test

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

How many tables in your schema(s) use AUTOINCREMENT? Maybe a dozen uses in DB designs I made for my own use. Zero or very few in designs made for others. My use case of autoincrement is certainly somehow peculiar. For some of DBs I use daily for managing my own business (I'm self-employed

Re: [sqlite] Clarification on Storage

ed as IMAGE datatype, then the data grid won't display text or numeric values, only thumbnails if you've allowed them in options. The definitive answer will come from Expert support. -- Jean-Christophe Deschamps 2891 route de Pouillon 40180 Heugas France

Re: [sqlite] Missing data table

At 14:51 16/12/2017, you wrote: Just joined the list. Am a fairly long term user of SQLite Expert Personal (my version is 3.5.96.2516 - I upgraded to try version 4 a while back but the columns in the Data tab were fixed which is no good for me, I couldn't drag them wider so I went back to v3),

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

At 00:13 25/11/2017, you wrote: Looks like I do need to use AUTOINCREMENT after all, otherwise the framework I'm using doesn't appear to recognize that INTEGER PRIMARY KEY columns are allowed a NULL value on insert. I can't answer about just INTEGER PRIMARY KEY columns, but any table with an

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

At 23:49 24/11/2017, you wrote: On 11/24/17 5:26 PM, Jean-Christophe Deschamps wrote: At 22:38 24/11/2017, you wrote: One proof of the falsehood of your assertion is that we CAN fill a database with some data using UIDs, and we will almost certainly not get a collision, while you assertion

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

At 22:38 24/11/2017, you wrote: One proof of the falsehood of your assertion is that we CAN fill a database with some data using UIDs, and we will almost certainly not get a collision, while you assertion we will. This is an attempt at "proof by example". Keith is perfectly right --mathemati

[sqlite] Feature request

.53]) in some future release? -- Jean-Christophe Deschamps 2891 route de Pouillon 40180 Heugas France 06 15 10 19 29 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why this query plan?

At 15:13 12/01/2017, you wrote: Re: "I read this as a provocative joke." I didn't read it as just a joke. The analogy with random fonts, etc. breaks down, I think, because randomizing the ordering would be an attempt to *improve* sqlite's usability -- not some pedantic punishment. I read this

Re: [sqlite] Why this query plan?

Richard, At 02:00 12/01/2017, you wrote: The "PRAGMA reverse_unordered_selects=ON" statement has long been available to do this. But it is an optional feature that has to be turned on. And I don't think anybody ever turns it on. My proposal is to make it random. Maybe it would be sufficient

Re: [sqlite] Bug: Problem with ORDER BY UPPER(...) in conjunction with UNION

At 00:45 27/12/2016, you wrote: The work arounds is using a WITH clause or putting the upper function expression in the output of each select. Another way to rewrite is to wrap the compound select inside a simple outer select: select n from ( select 'Abc' n union selec

Re: [sqlite] Weird chars inserted

Igor, On 12/18/2016 7:17 AM, Jean-Christophe Deschamps wrote: Since your DB is UTF-16LE encoded, you shouldn't convert your strings to UTF8. int nRet= sqlite3_exec(m_hDB, szSQL, NULL, 0, &pErrMsg); alone should work fine. No it should not. The encoding of the database is i

Re: [sqlite] Weird chars inserted

Ariel, At 02:38 18/12/2016, you wrote: Hi people. I need some help I’m lost here. I’m writing an application using SQLite and whenever I insert Spanish characters I get this kind of strings: Mart�n (where it should read ‘Martín’) Ok, so if I open my database with SQLiteManager (th

Re: [sqlite] Problem with CASE in WHERE clause

Hi Simon, At 11:41 05/12/2016, you wrote: On 5 Dec 2016, at 7:48am, Jean-Christophe Deschamps wrote: > The choice of literals representing true and false is merely cosmetic. You got me interested in that. I had thought that "TRUE" and "FALSE" were reserved word

Re: [sqlite] Problem with CASE in WHERE clause

At 12:18 05/12/2016, you wrote: From: Jean-Christophe Deschamps Sent: Monday, December 05, 2016 9:48 AM To: SQLite mailing list Subject: Re: [sqlite] Problem with CASE in WHERE clause > > At 06:29 05/12/2016, you wrote: > >My app supports sqlite3, SQL Server, and PostgreSQL. >

Re: [sqlite] Problem with CASE in WHERE clause

At 06:29 05/12/2016, you wrote: My app supports sqlite3, SQL Server, and PostgreSQL. SQL Server has a ‘bit’ data type, which accepts 1/0 and ‘1’/’0’ as valid values. PostgreSQL has a ‘bool’ data type, which supports a variety of values ­ TRUE, ‘t’, ‘true’, ‘yy’,

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

At 22:41 15/11/2016, you wrote: So if you're truly worried about flush-to-disk what do you do ? Solution 1 is to buy hard disks rated for servers -- sometimes called "enterprise-class hard drives" -- and to set the DIP switches to tell them they're being used on a server. Those things are

Re: [sqlite] Typical suffixes of sqlite database files

At 01:17 21/10/2016, you wrote: What suffixes to sqlite database files do you use or see in the wild? I routinely/always use .sq3 but that's only a personal convention. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailin

Re: [sqlite] Protecting databases

Damien, At 09:18 08/10/2016, you wrote: Password protecting it is also good on many levels - if the database is to be used online then it is needless to say that authentication would be required for various people to view it. SQLite can't be put "online" per se. It will then be the duty of th

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

Rob, At 18:27 07/08/2016, you wrote: Too little sleep and far too much coffee. I was in the same situation, multiplying by 2 instead of dividing, as Ryan pointed out. Nice to see that WAL fits your use case. I for one found it rock solid and very useful. -- Jean-Christophe ___

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

Rob, At 08:55 07/08/2016, you wrote: We think that using WAL mode works for us, indeed inspection seems to indicate it does, but the size of the -wal file appears to be far larger than would be expected. Is there a problem here? It doesn't appear to be a problem but would welcome any comments.

Re: [sqlite] Trigger slowness even when it's not fired

At 10:26 04/07/2016, you wrote: Hello, I have a curious situation involving a trigger, which I’m at a loss to explain. I’m wondering if someone who knows more about the insides of SQLite can tell me more about why it’s happening. I’m running SQLite 3.8.7. The trigger code is at the

Re: [sqlite] How to use "cursors" in c#

As another further response and if you use automatic INTEGER PRIMARY KEY IDs, then the sqlite3_last_insert_rowid() call will tell you unambiguously, provided the call is made within an explicit transaction initiated before the insert. As a further response, regarding that Martina said "insert

Re: [sqlite] sqlite3_column_origin_name for INSERT and UPDATE

My first instinct is to add a Units column to any table with a measurement. The value in Units would be a foreign key to a Units table. Another table, Conversions, could hold conversion factors. In addition to what jkl said, I'd like to point out that if you may someday find yourself force

Re: [sqlite] Latin-1 characters cannot be supported for Unicode

At 09:22 08/06/2016, you wrote: A 3 Byte Sequence 0xFFFEC4 when converting 0xC4 to UTF-8 in UltraEdit This 3-byte sequence is neither UTF8 or UTF16 even if the BOM would make us believe it is UTF16-LE. UTF16 implies 16-bit encoding units, so an odd byte length is impossible. You probably mea

Re: [sqlite] UPDATE statement without FROM clause

At 23:34 04/06/2016, you wrote: On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps wrote: > Can't the same update be done more efficiently with a CTE? The command inside the WITH has to be a SELECT command. Definitely not as Ryan pointed out, and as the help file clearl

Re: [sqlite] UPDATE statement without FROM clause

At 18:18 04/06/2016, you wrote: Hi, why? At the moment I have to run something like: UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID); Using a FROM clause I just need one scan through B (at least in principle). Now, I nee

[sqlite] Messages posted on Nabble not getting to list

At 03:46 27/05/2016, you wrote: >If SQLite goes this route, I will probably (as with the others) stop >reading it too. Seconded.

Re: [sqlite] Messages posted on Nabble not getting to list

At 03:46 27/05/2016, you wrote: If SQLite goes this route, I will probably (as with the others) stop reading it too. Seconded. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo

[sqlite] application for initial charge

At 07:14 09/04/2016, you wrote: >I have implemented an application with Lazarus+FPCompiler, using SQLite >tables, and the information was obtained from Excel tables previously >done. > >I hope it would be very simple to prepare in the former Excel tables a >sheet >with the information required by

[sqlite] Correlated subquery refers to wrong rowid - bug?

At 16:14 09/03/2016, you wrote: >On 3/9/2016 9:58 AM, R Smith wrote: >>On 2016/03/09 4:35 PM, Igor Tandetnik wrote: >>>Yes, but why is that a problem? It is perfectly legal, and often >>>useful, for a subquery to refer to columns from enclosing query. >>>That's what makes it a *correlated* subquery

[sqlite] Correlated subquery refers to wrong rowid - bug?

At 15:16 09/03/2016, you wrote: > select id from a where id not in (select a.id from b); Shouldn't the engine bark on this, like it does on the modified version: select id from a where id not in (select zzz.id from b); "no such column zzz.id" As I understand it, there is no more an a.id colum

[sqlite] Bug: Successfully committed transaction rolled back after power failure

At 17:55 01/02/2016, you wrote: >The above about implementation of RAID is good. There were battery >backed up caching controllers 20 years ago. In the event of a power >loss, the cached writes could be completed later. I run such one RAID Areca controller with 8 server disks in RAID6. Even after

[sqlite] IS a SQLite db of small size as good as reliable cache?

At 22:57 30/01/2016, you wrote: >2 further if such a db is part of windows service / application running >continuously then may be pages will remain in the memory under normal >conditions. > >Q1) Operating System like Windows would cache the series of pages, is this >behavior not almost same as t

[sqlite] Best way to store only date

At 16:23 29/01/2016, you wrote: >Note that the fractional part in Julian day represents time-of-day >since noon, not since midnight. Ah yes, I often forget about this point. Sorry for incorrect lead.

[sqlite] Best way to store only date

At 08:28 29/01/2016, you wrote: >For storing a date I probably could use strftime('%J'). But I would >like to >store it as an INT. But I see no way to cast a float to an int. Is >that not >possible? select cast(julianday('now') as int) should do what you want.

[sqlite] whish list for 2016

At 08:28 13/01/2016, you wrote: >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 PRA

[sqlite] Simple Math Question

AFAICT system 360 machines had BCD in microcode, just like any other basic datatype. Z is only the renamed survivor of 360/xx and 370/xxx "boxes". At 14:33 23/10/2015, you wrote: >--- >On Fri, Oct 23, 2015 at 3:08 AM, wrote: > > > Computers don't store values in base-10 fractions when they us

[sqlite] sqlite3 file as database

At 01:07 15/09/2015, you wrote: >--- >SQUISH was a database format for storing messages in FidoNet systems. >--- Geez, I don't even recall my FidoNet node number aka address... Time must have flown by faster than I thought. -- jcd

[sqlite] sqlite3 file as database

I've decided to use .sq3 ; I'm mainly under Windows where a dedicated extension is pretty handy to launch a DB manager and sq3 doesn't seem to collide with much things around and leaves ample room for sq4, sq5, ... -- jcd

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

At 11:58 31/08/2015, you wrote: >--- >But the only sure way to prevent anyone else from installing its own >authorizer is to change the function name in the SQLite library. >--- That wouldn't really work under Windows: GetProcAddress can provide access to a DLL function by index (ordinal value)

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

At 09:14 28/08/2015, you wrote: >--- > > Looks like "How many zillion devices are going to misbehave if this > is fixed?" > >We will find out, because SQLite was changed two days ago: >http://www.sqlite.org/cgi/src/info/c2f3bbad77850468 >--- Fine, let's sit down and watch the world collapse. No

[sqlite] autoincrement field

At 23:25 27/08/2015, you wrote: >--- >I have a table structure like this: > >CREATE TABLE padstack ( > id INTEGER PRIMARY KEY AUTOINCREMENT, > pin_number INTEGER, > name TEXT >); > >Is there any way to get the 'id' of newly inserted row? My insert of >course >not contains the 'id' fie

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

At 21:11 27/08/2015, you wrote: > > There are 2 distinct and volontary function invokations, so I don't see > > how SQL engine would decide not to perform the second call. > >Agreed, though I'm pretty sure I've read messages in this thread at >advocate the same function should return the same valu

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

>I can see both sides of this debate, whether or not random() should be >evaluated twice in this context: > >select random() from blah order by random() There are 2 distinct and volontary function invokations, so I don't see how SQL engine would decide not to perform the second call. >So let me

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

At 16:00 27/08/2015, you wrote: > >An *ORDER BY* clause in SQL specifies >that a SQL SELECT statement >returns a result set with the >rows being sorted by the values of one or

[sqlite] Why sqlite show qualified column names when selecting from views ?

At 22:28 25/08/2015, you wrote: >In the next version of SQLite3 the names of all columns will be >'columnname' "Don't trust me" could be more apropriate.

[sqlite] Proper way to abort

> >I have some queries that may take 5-15 seconds to complete. Sometimes >the situation changes shortly after starting the query where my >program does not need those results anymore and the program wants to >abort and begin a different query instead. > >My question is: What is the proper way t

[sqlite] Database sybchronisation

simon, >This is normally resolved by logging the time each command was issued Not "when it was issued" but when exactly it was finally commited. When one or more update statements are part of a transcation, you now have to use sqlite3_profile to log when exactly the transaction ended (i.e. can

[sqlite] Database sybchronisation

Hi Simon, >Method 2 > > >Ignore Method 1. In each copy of your database keep a log of all >INSERT and UPDATE commands executed since the last 'synchronize': > >CREATE TABLE commandsSinceLastSynch (theCommand TEXT) > >To synchronise the two copies, play back the log for copy A to copy B

[sqlite] CSV excel import

At 18:38 01/08/2015, you wrote: >Nobody mentions it because it is as irrelevant as bemoaning the fact >that CSV cannot store lawn-chairs or Java objects. It wasn't intended >to do so. Exactly. All I mean is that with only very few additional strict rules it can be changed into a basic type com

[sqlite] CSV excel import

At 16:36 01/08/2015, Igor wrote: >There are many real problems with CSV - no need to make up imaginary ones. `--- Indeed and I'm surprised noone mentionned this from the very start of this thread: Nulls. There is no real provision in the RFC to represent Null, or distinguish Null from an e

[sqlite] Mozilla wiki 'avoid SQLite'

Marco, >There's never been that kind of tone, the article has been written to >point >new Mozilla codebase contributors at possible pitfalls we already hit in >the past, and actually help them making informed decisions. That's not how one reads it. Start with only the title: "Performance/Avoid

[sqlite] Mozilla wiki 'avoid SQLite'

I've refrained to comment about the OP linked page but I can't resist that long. I won't enter the C, C--, C++, C#, Java, Python, COBOL, Basic, assembler, Ruby, YouNameIt sub-debate. I found the rant on MozillaWiki way too wrong on too many points to keep quiet. While I can agree with some of

[sqlite] User-defined types -- in Andl

At 13:50 09/06/2015, you wrote: >BTW I don't remember the last time I saw SQL like this. Understanding it >might be the challenge `--- Most probably! I can imagine that you don't encounter such style in common business-like environments. Take your time, this SQL piece is clearly beyond norm

[sqlite] User-defined types -- in Andl

At 08:27 09/06/2015, you wrote: >Andl is at a slightly higher level than SQL for writing simple queries. >Where it shines is writing complex queries that involve user-defined >types, >custom transformations and custom aggregations. For complex relational >operations there is nothing I know th

[sqlite] ALTER TABLE ADD COLUMN

At 08:51 21/05/2015, you wrote: >I used sqlite3.exe by invoking it on the command line with the name of >a database. >Next I typed > > alter table database add column real_length numeric; > >Next I typed .fullschema >and I'm getting: > >sqlite> .fullschema >CREATE TABLE [database] ( >[database_n

[sqlite] AUTOINC vs. UUIDs

before* using one. Validating Global >uniqueness is, quite obviously, a very difficult problem. > > > -Original Message- > > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users- > > bounces at mailinglists.sqlite.org] On Behalf Of Jean-

[sqlite] AUTOINC vs. UUIDs

At 23:24 20/05/2015, you wrote: >On Wed, May 20, 2015 at 2:20 PM, Steven M. McNeese > wrote: > > Often times people will use GUIDs as primary keys when different > systems need to generate > > rows and then merge together. Like an application that works with > offline clients that push the > > d

[sqlite] AUTOINC vs. UUIDs

At 22:50 20/05/2015, you wrote: >Just to chime in here: I think using UUIDs for primary keys is an >absolutely >terrible idea, instead. First off, how are you going to generate them? How >will you avoid collisions? Why should it be necessary to using that much >storage space for a primary key and

[sqlite] Fwd: bulk upload from Excel

>-- Forwarded message -- >From: "maksood alam" <786maksood at gmail.com> >Date: May 11, 2015 11:21 AM >Subject: bulk upload from Excel >To: >Cc: > >Hi Team, > >Do we have any option like below for the bulk upload. > >INSERT INTO tbl_test1 SELECT * FROM [asd$] IN '" & >ThisWorkbook

[sqlite] VBA Sqllite blob data

Hi Simon, >On 11 May 2015, at 3:25pm, Jean-Christophe Deschamps > wrote: > > > Isn't that the actual size limit of Excel strings? > >Sort of. Although Excel cells can contain text longer than 255 >characters, VBA functions ignore text parameters after the 255th

[sqlite] VBA Sqllite blob data

Isn't that the actual size limit of Excel strings? At 15:27 11/05/2015, you wrote: >I have a db3 config file that contains several different records. I >need to select one specific record and store it in an excel worksheet >cell. I have been able to create a routine to extract the informati

[sqlite] sqlite3 and Unicode

At 09:22 05/05/2015, you wrote: >On 4-5-2015 20:54, Richard Hipp wrote: >>On 5/4/15, Peter Haworth wrote: >>>When using the .dump command with .output to a filename, what >>>encoding does >>>sqlite3 for the file? Same as the database encoding? Is it possible to >>>change whatever encoding i

[sqlite] With recursive question

At 04:05 01/03/2015, you wrote: >On 2/28/2015 7:22 PM, Jean-Christophe Deschamps wrote: >>Once again thank you very much Igor. I was making my life miserable >>trying to scan the "tree" from the other end. > >That, too, could be arranged. Something along these lines

[sqlite] full table scan ignores PK sort order?

At 11:27 03/03/2015, you wrote: >- the full table scan returns rows in rowID order, which is the order >in which the rows were added to the table `--- No and no. An SQL engine doesn't guarantee any row "order" unless you explicitely force an ORDER BY clause. Think of row order as random, w

[sqlite] With recursive question

Once again thank you very much Igor. I was making my life miserable trying to scan the "tree" from the other end. At 00:37 01/03/2015, you wrote: >It's a bit unfortunate that you made the root a parent of itself. >Forces the query to make an extra check to avoid infinite recursion. `--- Ye

[sqlite] With recursive question (addendum)

Forgot to mention that I sees the issue as fundamentally distinct from the Mandelbrot example in the SQLite docs: it uses a construct -outlined in procedural statements- like: For x in xRange For y in yRange compute something like f(x, y) -- here, y is independant of x Next y Next x

[sqlite] With recursive question

Dear list, After trying a number of ways I'm at loss solving the seemingly simple problem. For a simplified example say I have a list of individual filesystem directories with FK pointing to their parent: PRAGMA foreign_keys=ON; CREATE TABLE "Dirs" ( "DirID" INTEGER NOT NULL PRIMARY KEY,

Re: [sqlite] Feature Request - RowCount

Hi Simon, A) In that sqlite_sequence table you mentioned, as an additional column. Always up-to-date. But sqlite_sequence isn't always created. AFAIK it only exists when one or more table exists with an integer primary key autoincrement. B) In the tables prepared by SQLite ANALYZE. If you

Re: [sqlite] Feature Request - RowCount

At 03:14 14/12/2014, you wrote: ´¯¯¯ I take the point that the only possible improvements seem to need alteration to the file structure or added maintenance which may use up cycles for something that just isn't that important to DB use in general - and I have to agree, I too have zero want for

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

select 7,915 - 5,021 is: select 7, 915 - 5, 021 giving 7 91021 just like select 'a', 915 - 5, 'b' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crash when binding default column value

At 21:53 24/09/2014, you wrote: > If the default cannot be represented exactly, its rounding will be the > least of the difficulties. Not always: in scientific applications I've had column values default to plenty of irrational numbers: fractions of pi, sqrt(2.0) etc. My main gripe is when t

Re: [sqlite] Divide by 0 not giving error

> This would means that if ever an SQL statement encounters divide by > zero, the application will crash with no way handle the situation > gracefully, nor to locate the source of the problem. Seriously, what are you talking about? Why is there "no way to handle" the error, gracefully otherwis

Re: [sqlite] Divide by 0 not giving error

What the SQL standard calls an "exception" is not necessarily exactly the same as an exception in other programming languages. Granted. If SQLite were to change the division-by-zero handling, it could be reported exactly like most other errors, by returning SQLITE_ERROR from sqlite3_step().

Re: [sqlite] Divide by 0 not giving error

Dear forum, MySQL does it too unless ERROR_FOR_DIVISION_BY_ZERO mode is enabled: SQL Server does it too unless SET ARITHABORT is ON: PostgreSQL doesn't do i

Re: [sqlite] Insert optimization

How to improve the write speed then. I've found that in most cases using "chained" insertion speeds up things dramatically: insert into mytable (x, y, z) values (1, 2, 3), (4, 5, 6), (7, 8, 9), ... If you insert K rows at a time this way your total insert time can by cut by a significant

Re: [sqlite] Hexadecimal integer literals

Conversion of oversized hex into FP would break easily and reveal hardly reproductible across many platforms. Being a support for some languages fora I observe daily how FP inaccuracies is a real-world problem in simple-looking code. The only reasonable thing I can foresee is treat hex as sign

Re: [sqlite] Is sqlite case-sensitive?

At 22:36 18/05/2014, you wrote: The more I think of it, though, I think that the solution is as simple as converting all letters to lower(/upper) case and converting all whitespace to a single space each, except for within matching [ ], " ", ' ' or ` `. After that, I can do a memcmp(). You'r

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

At 21:35 03/03/2014, you wrote: ´¯¯¯ RAID3-4-5 was great when disks were expensive, in 80's an 90's. Now not. A minimal RAID5 needs 3 disks. A minimal RAID10 4. An enterprise disk SAS 15Krpm 146 GB 6G is $350, and a not enterprise grade cheaper and bigger. Now RAID1E and RAID10E give more flexi

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

It's how RAID5 works. Check this page docs http://baarf.com/ about it. This is utter BS. Serious RAID controllers perform parallel I/O on as many drives that are making up a given array. Of course I'm talking of SAS drives here with battery backed-up controller. Kid sister RAID5-6 impleme

Re: [sqlite] How should I use parenthesis?

Perhaps you want: select * from ( select * from A where x in (subselectA) left outer join ( select * from B where y in (subselectB) ) as a on -- something ) as A1 UNION ALL select * from ( select * from B where y in (subselectC) left ou

[sqlite] How should I use parenthesis?

Dear list, I'm trying to find the correct syntaxt for this, but I hit a syntax error each time: either SQLite shokes on outer parenthesis or on union all. ( select * from A where x in (subselectA) left outer join select * from B where y in (subselectB) ) union all ( -- <-- error select * fr

Re: [sqlite] Concrete example of corruption

At 08:45 11/12/2013, you wrote: My case wasn't about messing anything up, but simply moving a document. Just like moving the canvas while the artist is painting? Or is it moving the notebook while the schoolkid writes on it? This is prone to produce dribbles or worse, in my view. I fail to

Re: [sqlite] Intended use case for 'without rowid'?

How so? An AFTER INSERT trigger has the value in new.yourPKColumn. Can you show a scenario where this is insufficient, and a hypothetical last_insert_primary_key would save the day? You're correct, there is no situation where such a thing would make sense. _

Re: [sqlite] Intended use case for 'without rowid'?

At 15:45 15/11/2013, you wrote: last_insert_rowid() is needed for autoincremented keys. WITHOUT ROWID tables do not have an autoincrementing key. Your program has to generate or get the key in some other way, so it knows the value even before the record is inserted. last_insert_primary_key w

Re: [sqlite] collation for german "Umlaute"

Search the Internet for an SQLite extension called "unifuzz.c" and see if that does what you want in the way of character folding. I have a copy of the code on my other computer if you cannot find the original authors original code. Here is the download link: https://dl.dropboxusercontent.com

Re: [sqlite] racing with date('now') (was: Select with dates)

There are other ways to get a consistent 'now' across an entire SQL statement: Of course! This issue is sufficiently obscure that it is not worth adding (and testing and documenting and supporting forever) yet another pragma. I personally wouldn't call it so "obscure" as it pops up regula

Re: [sqlite] racing with date('now') (was: Select with dates)

There are perhaps 2 million applications in the wild that use SQLite, so it will be difficult to check them all. But one can easily imagine that one or more of those two million applications does something like this: SELECT current_timestamp, * FROM tab; And then runs sqlite3_step() eve

  1   2   3   4   5   >