Re: [sqlite] Question about floating point

2018-12-15 Thread Keith Medcalf
On Friday, 14 December, 2018 23:49, Frank Millman wrote: > I know that floating point is not precise and not suitable for > financial uses. This is debatable. In the "old days" of what was called "fast floating point" it was certainly true since the epsilon of a "fast floating point" number

Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

2018-12-14 Thread Keith Medcalf
On Friday, 14 December, 2018 10:09, Luuk wrote: >On 14-12-2018 16:54, Keith Medcalf wrote: >> You get the message about missing collating sequences because the >> database is using collating sequences that you don't have (ie, are >> missing) in your version of SQLite3.

Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

2018-12-14 Thread Keith Medcalf
You get the message about missing collating sequences because the database is using collating sequences that you don't have (ie, are missing) in your version of SQLite3. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

Re: [sqlite] add constant to INTEGER PRIMARY KEY

2018-12-13 Thread Keith Medcalf
You cannot do that. The PRIMARY KEY is required to be unique at each "step" along the way, especially as this is the parent in a foreign key relationship. Letting alone why anyone would want to do such a thing (which is beyond my ken), you simply have to make sure that your values are

Re: [sqlite] sqlite_btreeinfo

2018-12-13 Thread Keith Medcalf
xtension wrong somehow. Also, I >saw >that it requires sqlite_db[vtab? not sure] and that table is also >missing > >Wout. > > >On Thu, Dec 13, 2018 at 12:43 AM Keith Medcalf >wrote: > >> >> I have the extension compiled in. When a database does not

Re: [sqlite] sqlite_btreeinfo

2018-12-12 Thread Keith Medcalf
I have the extension compiled in. When a database does not exist I get: SQLite version 3.27.0 2018-12-10 01:48:29 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> select * from sqlite_btreeinfo; Error:

Re: [sqlite] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

2018-12-12 Thread Keith Medcalf
I know nothing about "Time Machine", but does it copy the entire filesystem in (at least) "crash consistent" state? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users

Re: [sqlite] [sqlite-dev] using COUNT(*) in sql from a delphi program

2018-12-06 Thread Keith Medcalf
You will probably have a better response if you post your query to the correct list rather than the sqlite-dev list. It is unlikely that the SQLite3 developers use Delphi, however it is probable that at least one sqlite-user uses delphi ... Reporting to the correct list. --- The fact that

Re: [sqlite] Allow inclusion of generate_series function

2018-12-06 Thread Keith Medcalf
On Thursday, 6 December, 2018 08:23, Nathan Green wrote: >On Tue, Dec 4, 2018 at 11:25 AM Digital Dog wrote: >> On Tue, Dec 4, 2018 at 5:57 PM Richard Hipp wrote: >> > On 12/4/18, Digital Dog wrote: >> > > I vote for GENERATE_SERIES to be included in official sqlite3 >> > > binary and

Re: [sqlite] Support function_list in pre-built binaries from SQLite download page

2018-12-04 Thread Keith Medcalf
My introspection pragma's work, and always have. Then again, I compile with them turned on. Perhaps if they are available the option should appear in the compile_options output, at least? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

2018-12-02 Thread Keith Medcalf
On Sunday, 2 December, 2018 12:57, Simon Slavin wrote: >On 2 Dec 2018, at 7:29pm, E.Pasma wrote: >> drop table x; >> create table x(value INTEGER PRIMARY KEY) WITHOUT ROWID; >> >> insert into x select random() from generate_series where start=1 >and stop=1000; >> Run Time: real 88.759 user

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

2018-12-02 Thread Keith Medcalf
Well if it is unique and not null, then why not just make it the rowid? In either case, you would still have to permute the storage tree at insert time if the inserts were not in-order. So let us compare them shall we: sqlite> create table x(value INTEGER PRIMARY KEY); sqlite> insert into x

Re: [sqlite] is True (was: geopoly_contains_point(P, X, Y) doc is overly modest)

2018-12-01 Thread Keith Medcalf
>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Saturday, 1 December, 2018 12:45 >To: SQLite mailing list >Subject: [sqlite] is True (was: geopoly_contains_point(P, X, Y) doc >is overly modest) > > >On Saturday, 1 December, 2018 12:23, Richard Hipp >wro

[sqlite] is True (was: geopoly_contains_point(P, X, Y) doc is overly modest)

2018-12-01 Thread Keith Medcalf
On Saturday, 1 December, 2018 12:23, Richard Hipp wrote" >On 12/1/18, Keith Medcalf wrote: >>>Maybe it should say 'Non-Zero' or 'Greater than Zero' rather than >>>true, since true, as a symbol, as a special value. >> Yes and no, True and False is SQLite work

Re: [sqlite] geopoly_contains_point(P,X,Y) doc is overly modest

2018-12-01 Thread Keith Medcalf
>Maybe it should say 'Non-Zero' or 'Greater than Zero' rather than >true, since true, as a symbol, as a special value. Yes and no, True and False is SQLite work as one would expect (assuming that one is a programmer is a language that behaves as the underlying hardware (CPU) behaves). The

Re: [sqlite] [sqlite-dev] having problems with "select where not exists"

2018-11-30 Thread Keith Medcalf
Forwarded to the sqlite-users mailing list ... Probably the spelling error ... (mf2 does not exist cuz you typed my2 as the alias) >1) One problem is that there is no column name when I include the >"as" phrase in the select statment as in >select * from myfile as mf1... >where not exists >(

Re: [sqlite] Detecting erroneous multi-row subqueries

2018-11-29 Thread Keith Medcalf
On Thursday, 29 November, 2018 18:24, J. King wrote: >select (select 'foo' union select 'bar') || 'bar'; >SQLite 3.25.3 returns 'barbar' (regardless of the value of the >reverse_unordered_select pragma) while PostgreSQL 11 refuses to >process the query unless the subquery is reduced to a single

Re: [sqlite] Getting SQLITE_LOCKED

2018-11-29 Thread Keith Medcalf
Ya must be using shared cache as well? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Prajeesh Prakash

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

2018-11-28 Thread Keith Medcalf
Have you increased the paltry default cache size? (PRAGMA CACHE_SIZE) The bigger the better, especially since you are sorting and balancing large B-Tree's. The more this can be done in memory without having to spill to slow disk (or disk cache) the faster it will go ... (the best way to

Re: [sqlite] SQLITE_LOCKED and SQLITE_BUSY

2018-11-28 Thread Keith Medcalf
No. It is not correct. Have you read the documentation? https://sqlite.org/rescode.html#locked Multiple threads cannot perform operations at the same time on the same connection. This is verboten. Forbidden. Does not work. Will cause explosions and death of children. Do not do it.

Re: [sqlite] SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX

2018-11-28 Thread Keith Medcalf
The difference is that if both threads call the library on the same connection at the same time (that is, two calls are into the library are active at the same time) then all hell will break loose. You application will fail. Memory will be corrupted. You database will be corrupted. Hell

Re: [sqlite] Bug? Confused data entry with column name

2018-11-28 Thread Keith Medcalf
That is because some daft person is using the wrong quotes, doh! --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On

Re: [sqlite] [EXTERNAL] SQLITE_OPEN_FULLMUTEX

2018-11-28 Thread Keith Medcalf
SQLITE_OPEN_FULLMUTEX ensures that EACH CONNECTION is SERIALLY ENTRANT into the Sqlite3 library code (ie, that two threads cannot make a call into the library on different threads AT THE SAME TIME, or put another way that only ONE THREAD at a time on EACH CONNECTION may make a call into the

Re: [sqlite] [EXTERNAL] SQLITE_OPEN_FULLMUTEX

2018-11-28 Thread Keith Medcalf
No, you are incorrect. Isolation is only BETWEEN DIFFERENT CONNECTIONS, and has nought whatsoever to do with threads ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users

Re: [sqlite] sqlite3_exec()

2018-11-27 Thread Keith Medcalf
On Tuesday, 27 November, 2018 07:59, Simon Slavin wrote: >If you do not register an async writer, SQLite does not start its own >threads. Each SQLite library call executes in the thread which calls >it. Not entirely correct. The sorter can be configured to automatically use threads. ---

Re: [sqlite] Possible bug in Alter Table

2018-11-26 Thread Keith Medcalf
On Monday, 26 November, 2018 12:19, Petite Abeille wrote: ... > Talking of which, the CLI doesn’t seem to handle the following > statement very gracefully: > > sqlite> select DATE '1998-12-25’; > ...> > ...> > …> >Note how the CLI doesn’t recognize the semicolon marking the end-of-

Re: [sqlite] Creating and dropping tables with multiple connections

2018-11-20 Thread Keith Medcalf
Change the executed SQL to be executed using .executescript(...) and make sure that the executed SQL is a multi-statement batch consisting of BEGIN TRANSACTION; COMMIT; that is cur1.execute(createSQL) -> cur1.executescript('BEGIN TRANSACTION; ' + createSQL + ' COMMIT;') and see what

Re: [sqlite] Creating and dropping tables with multiple connections

2018-11-20 Thread Keith Medcalf
after adding in the obviously missing #include's for and the provided code does not compile. It is looking for an undefined something called EXPECT_EQ ... Kind of hard to reproduce if incomplete code is presented. As an aside, it works properly written in python using apsw. It does not

Re: [sqlite] Broken Foreign key

2018-11-13 Thread Keith Medcalf
On Tuesday, 13 November, 2018 05:47, Thomas Kurz asked: >May I ask why parent keys *must* have indexes? I don't see any >correlation between enforcing a client/parent-relationship and the >necessity for an index. I'm just asking to better understand. To me >it is clear that the parent column is

Re: [sqlite] Broken Foreign key

2018-11-13 Thread Keith Medcalf
e|CREATE TABLE `option4_name` (`name_id` char(36) >NOT NULL >PRIMARY KEY,`name` varchar(255) NOT NULL default '' UNIQUE) >index|sqlite_autoindex_option4_name_1|option4_name|3| >index|sqlite_autoindex_option4_name_2|option4_name|4| >index|name|option4_name|5|CREATE UNIQUE INDEX 'name' ON >'

Re: [sqlite] Broken Foreign key

2018-11-11 Thread Keith Medcalf
parent keys MUST have unique indexes. Add the missing indexes. child keys SHOULD be indexed: UNIQUE indexes for 1:1 relationships, non-unique indexes for 1:N relationships put unique indexes on your parents and all will work just fine. --- The fact that there's a Highway to Hell but only a

Re: [sqlite] [EXTERNAL] found a glitch in ALTER TABLE RENAME (3.25.x)

2018-11-09 Thread Keith Medcalf
On Friday, 9 November, 2018 16:20, J. King wrote: >To: SQLite mailing list >That could lead to loss of referential integrity when modifying a >table in a way not supported by ALTER TABLE, I believe. One usual >method is to turn foreign keys off, rename the old table, create a >new modified

Re: [sqlite] sharing in memory db through threads

2018-11-05 Thread Keith Medcalf
Did you compile sqlite3.c with SQLITE_USE_URI defined? Or use one of the methods that tells the library that you are using a URI filename? https://sqlite.org/uri.html --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

Re: [sqlite] json_each() in a table trigger for an 'attached' db causes an error

2018-11-02 Thread Keith Medcalf
Works for me using Lindsay's original scripts. 3.26.0 2018-11-02 17:38:39 1fa74930ab56171e2e840d4a5b259abafb0ad1e0320fc3030066570a6dd1alt2 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message-

Re: [sqlite] json_each() in a table trigger for an 'attached' db causes an error

2018-11-02 Thread Keith Medcalf
configure --enable-load-extension --enable-threadsafe --with-readline-lib=auto --with-pic --enable-json1 --enable-fts3 --enable-fts4 --enable-fts5 --enable-rtree --enable-session --enable-update-limit --enable-geopoly Adjust the configure parameters to include what you want included. ---

Re: [sqlite] SQLite for datalogging - best practices

2018-10-31 Thread Keith Medcalf
On Wednesday, 31 October, 2018 13:22, Gerlando Falauto wrote: >first of all let me tell you I cannot thank you enough for going >through all this trouble for me! No problem. I still really do not know if the answer is correct however it does let you get data about how much data is actually

Re: [sqlite] SQLite for datalogging - best practices

2018-10-30 Thread Keith Medcalf
@mailinglists.sqlite.org] On Behalf Of Gerlando Falauto >Sent: Tuesday, 30 October, 2018 01:46 >To: SQLite mailing list >Subject: Re: [sqlite] SQLite for datalogging - best practices > >On Tue, Oct 30, 2018 at 4:58 AM Keith Medcalf >wrote: > >> >> If you don't mind me ask

Re: [sqlite] curious discovery about geopoly module

2018-10-30 Thread Keith Medcalf
select sqlite_source_id(); both where it works and where it doesn't. Are they the same or different? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users-

Re: [sqlite] SQLite for datalogging - best practices

2018-10-29 Thread Keith Medcalf
If you don't mind me asking, what sort of data are you collecting? Are you the master (ie, scanning) or a slave (getting async data pushed to you). Are you "compressing" the returned data (storing only changes exceeding the deadband) or are you storing every value (or is the source instrument

Re: [sqlite] SQLite for datalogging - best practices

2018-10-29 Thread Keith Medcalf
>I see. However, giving up on indexes kind of defeats the whole >purpose of having a database. >I assume there is no way to leverage the fact that e.g. rows would be >inherently sorted by timestamp, without recurring to indexes. There kind of is. You can do this using a bit of funny business

Re: [sqlite] SQLite for datalogging - best practices

2018-10-29 Thread Keith Medcalf
See the ext/misc/unionvtab.c extension for "reading" a bunch of databases as if they were a single database. https://www.sqlite.org/src/artifact/0b3173f69b8899da --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

Re: [sqlite] SQLite for datalogging - best practices

2018-10-28 Thread Keith Medcalf
>>The next factor is the internal write multiplication factor. Lets >>say you have a device which is divided into 2 MB blocks. And you update 1 >>sector (512 bytes) somewhere in this block. The device must (a) read out >>the entire 2MB block (b) update the data within the block then (c) re-

Re: [sqlite] SQLite for datalogging - best practices

2018-10-28 Thread Keith Medcalf
On Sunday, 28 October, 2018 17:48, Gerlando Falauto wrote: >On Sun, Oct 28, 2018 at 6:18 PM Simon Slavin wrote: >> On 28 Oct 2018, at 2:06pm, Gerlando Falauto >> wrote: >>> - the SSD's wear due to continuous writes should be reduced to a minimum >> I assume your App generates items for the

Re: [sqlite] SQLite for datalogging - best practices

2018-10-28 Thread Keith Medcalf
On Sunday, 28 October, 2018 16:42, Petite Abeille wrote: >> On Oct 28, 2018, at 11:32 PM, Keith Medcalf wrote: >> will last 50 years (which is 10 times the warranty period) >Thank you. No problems. What I really mean of course is not that I "expect" the SS

Re: [sqlite] SQLite for datalogging - best practices

2018-10-28 Thread Keith Medcalf
This could be pretty complicated and depends a lot on the manufacturer of the SSD. The first thing to be said is that the most accurate portrayal of the life of the device is probably found by what the manufacturer is willing to warranty and in most cases the warranty will be very

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread Keith Medcalf
You also need to make sure the "no hit" does not degenerate into a table scan. RTree works well for this but is overall significantly slower than not using RTree since the purpose of RTree is to find the "small number of candidate records" that could possibly satisfy the query out of a

Re: [sqlite] Regarding CoC

2018-10-26 Thread Keith Medcalf
On Friday, 26 October, 2018 03:57, Petite Abeille wrote: >> On Oct 26, 2018, at 5:12 AM, Philip Warner >wrote: >> knowingly and deliberately ignoring large chunks, and broadly >> disagreeing with even more, and laughing at the rest. > Bah… Everything Is Amazing And Nobody Is Happy: >

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread Keith Medcalf
Limit 1 says to stop after returning 1 row. If the "first row" being searched is not the one containing "the answer" then the search will continue until the row that does not match the index constraint is hit, after which it is known that no answer is possible (without returning a row). ---

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread Keith Medcalf
Based on your assumptions being correct (a) start is unique (b) start end ranges do not overlap create table ranges ( start integer primary key, stop integer not null, value integer not null ); INSERT INTO ranges values (1, 10, 5); INSERT INTO ranges values (15, 29, 8); INSERT INTO

Re: [sqlite] Optmize queries on ranges

2018-10-25 Thread Keith Medcalf
--- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. On Thursday, 25 October, 2018 10:48, Dan Kennedy wrote: >On 10/25/2018 11:13 PM, siscia wrote: >> >> I am facing an interesting optimization problem. >> >> I have a table

Re: [sqlite] Optmize queries on ranges

2018-10-25 Thread Keith Medcalf
On Thursday, 25 October, 2018 10:48, Dan Kennedy wrote: >On 10/25/2018 11:13 PM, siscia wrote: >> Hi all, >> CREATE TABLE ranges ( >> start int, >> end int, >> value int, >> ); >> The query that I am interested in optimizing is "select value from >> ranges where (? between start

Re: [sqlite] nested foreign keys

2018-10-24 Thread Keith Medcalf
No, it means that you did not specify the whatisness of grandParent, parent, or child; and/or, you have not enabled foreign_keys. https://sqlite.org/lang_createtable.html https://sqlite.org/pragma.html#pragma_foreign_keys NB: I have compiled the CLI with foreign key enforcement ON be default.

Re: [sqlite] Index help...

2018-10-23 Thread Keith Medcalf
EXPLAIN QUERY PLAN shows the "High Level" outline of the plan for executing your query, primarily the constraints imposed on indexes, but not the WHERE conditions that are not used to constrain an index lookup. It does not show the "code" that is executed. Use EXPLAIN rather than EXPLAIN

Re: [sqlite] union + window functions = sqlite crash (version 3.25.2)

2018-10-23 Thread Keith Medcalf
On Monday, 22 October, 2018 14:13, Peter Ďurica wrote: >Table with sample data: >*create table t(a int, b int);* >*insert into t values(1,11);* >*insert into t values(2,12);* ... What is up with the asterisks, they make copying VERY VERY VERY VERY difficult. If you want to put "stars"

Re: [sqlite] segmentation fault in sqlite api call

2018-10-21 Thread Keith Medcalf
You have a vast number of undeclared variables that are pointing into super-crash-land: sqlite3 *mod_init() db is undeclared dbObj is undeclared lastError is undeclared query is undeclared zErrMsg is undeclared int mydef_set(cf_db_t *dbObj,char *key, char **value) type cf_db_t

Re: [sqlite] geopoly data input options

2018-10-19 Thread Keith Medcalf
From the source: /* ** Internal representation of a polygon. ** ** The polygon consists of a sequence of vertexes. There is a line ** segment between each pair of vertexes, and one final segment from ** the last vertex back to the first. (This differs from the GeoJSON ** standard in which the

Re: [sqlite] How to round to an Integer

2018-10-18 Thread Keith Medcalf
On Thursday, 18 October, 2018 14:13, Richard Hipp wrote: >On 10/18/18, John Harney wrote: >> Recently figured this out. Seems to work fine >> trim(trim(round(1.111,0),'0'),'.') = 1 >CAST(1.111 AS integer) That should be CAST(round(x,0) as integer) if you want the rounded result as an

Re: [sqlite] Error: dlsym(0x7fa073e02c60, sqlite3_sqliteicu_init): symbol not found

2018-10-18 Thread Keith Medcalf
e=icu >zh_CN); >Error: unknown tokenizer: icu > > >Why is that ? Is the whole point to build icu extension to get the >icu tokenizer ? > > >BTW I have build an icu sqlite version from amalgamation but this >time I need to icu extension only. > > >Thanks > > >Qiul

Re: [sqlite] Error: dlsym(0x7fa073e02c60, sqlite3_sqliteicu_init): symbol not found

2018-10-17 Thread Keith Medcalf
The symbol name is sqlite3_icu_init. When you load module lib.so the symbol sqlite3__init is called. You need to either (a) rename the shared library to the correct name (libicu.so) or pass the name of the init function (sqlite3_icu_init) to the loader when you load the module, or (c) change

Re: [sqlite] sqlite3_complete and comments

2018-10-14 Thread Keith Medcalf
On Sunday, 14 October, 2018 12:38, Roger Binns wrote: >I use sqlite3_complete in my shell in order to determine when a >complete statement has been input and can be run. (Otherwise a continuation >"sqlite> " prompt is shown.) >If the line entered is: > > -- hello >Then the sqlite shell

Re: [sqlite] [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...

2018-10-13 Thread Keith Medcalf
> Lastly, I'm assuming that 'IS' and 'IS NOT' is functionally > equivalent to the '=' and '<>' operators? > Or is there some subtle difference As long as neither the LHS or the RHS are null, then IS and IS NOT are the same as == and <> respectively. However, if you use the "comparison"

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

2018-10-12 Thread Keith Medcalf
This would seem to imply that BitDefender is not the cause of your woes. Do you have write permission on the database file? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users

Re: [sqlite] Replication

2018-10-12 Thread Keith Medcalf
>>> An SQL database is deemed "Relational" when it can communicate >>> mildly ... SQL stands for Structured Query Language. It has nothing whatsoever to do with the data store but rather is a specification of the Language used to retrieve/manipulate the datastore. This is the same as "C" or

Re: [sqlite] Filtering groups by non-grouped field.

2018-10-12 Thread Keith Medcalf
On Friday, 12 October, 2018 01:02, John Found wrote: >Hm, is sounds strange because when HAVING clause is processed, >the aggregate functions should not be processed yet (for a >performance reasons) i.e. the query still has access to all >values from the field >b >and theoretically should be

Re: [sqlite] Replication

2018-10-11 Thread Keith Medcalf
Balderdash. > The interlocking of artifacts by cryptographic hashes does seem very much > like the same idea as blockchain, which Wikipedia says was invented in > 2008. It is interesting that the first Fossil checkin was 21 July, 2007 > (and the first git checkin was 7 April, 2005). Hashed

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

2018-10-11 Thread Keith Medcalf
On Thursday, 11 October, 2018 00:22, Darren Duncan wrote: >On 2018-10-10 12:26 PM, Keith Medcalf wrote: >> And if you think that I am going to create YET ANOTHER LOGIN and >> YET ANOTHER PASSWORD just to use some crappy forum software, you have >> another think coming.

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

2018-10-10 Thread Keith Medcalf
On Wednesday, 10 October, 2018 14:08, Warren Young wrote: >On Oct 10, 2018, at 1:26 PM, Keith Medcalf >wrote: >> there is also absolutely no way to perform "positive identity >checks" on a web page post that cannot be equally trivially >falsified. >You’

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

2018-10-10 Thread Keith Medcalf
On Wednesday, 10 October, 2018 12:31, Warren Young wrote: >On Oct 10, 2018, at 11:51 AM, Chris Green wrote: >> Warren Young wrote: >>> Fossil forum email alerts include the full content of the message. >> And can you then simply 'reply' from your E-Mail client? If not >> then it doesn't

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

2018-10-09 Thread Keith Medcalf
>One of the problems we’ve been having on these lists is that every >time someone posts to it, they get NSFW spam, presumably because by >posting, you’ve just proven that your email address is valid. >Everyone on the ML sees the poster’s email address. My mailer >included yours in the quoting

Re: [sqlite] SQLite Windows GUI alternative to Excel?

2018-10-07 Thread Keith Medcalf
Many people do not "do" web forums. I am one of them. If there is not a mailing list then it does not exist. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

2018-10-05 Thread Keith Medcalf
The experimental ENABLE_SNAPSHOT interface can do this sort-of. The transaction still has to be in the WAL file (which means you may need to make the WAL file persistent across closes using the appropriate file control). However, you can only OPEN read-only snapshots in the past, you cannot

Re: [sqlite] alter table, other alter category, fails in presence of trigger on 3.25.2

2018-10-03 Thread Keith Medcalf
Unrelated, but are you sure that you want the albums "id int primary key" and did not happen to misspell "integer" so that the declaration should be "id integer primary key". In the former case, id is an integer that just happens to be unique (ie, "id int primary key" is the same as "id

Re: [sqlite] Help!

2018-10-02 Thread Keith Medcalf
Just pick "File -> Open" off the menu and then point and click that hooey-gooey at the database file ... (Assuming here -- most hooey-gooeys have a File Open menu clickety-pokey to open a file ...) Though since the problem is with "SQLite Studio" I would suggest either RTFM or contacting

Re: [sqlite] How to retrieve table names for the given string

2018-09-28 Thread Keith Medcalf
Narayanan >Sent: Friday, 28 September, 2018 00:25 >To: SQLite mailing list >Subject: Re: [sqlite] How to retrieve table names for the given >string > >Thanks Keith.. but I am getting an error while joining pragma table >info. > >On Fri, Sep 28, 2018, 6:30 AM Keith Medcalf

Re: [sqlite] How to retrieve table names for the given string

2018-09-27 Thread Keith Medcalf
Insert the following schema views: -- Catalog Views using sqlite_master for SysObjects (Object Names) -- and the various pragma_(ObjectName) tables to retrieve schema data -- all TEXT columns in views have "collate nocase" attachmented to the output -- columns to ensure that where conditions on

Re: [sqlite] storing unsigned 64 bit values

2018-09-27 Thread Keith Medcalf
ner >Sent: Thursday, 27 September, 2018 11:50 >To: SQLite mailing list >Subject: Re: [sqlite] storing unsigned 64 bit values > >On Thu, Sep 27, 2018 at 11:05:24AM -0600, Keith Medcalf wrote: > >> so the only way to store something [larger than a signed 64-bit >int]

Re: [sqlite] storing unsigned 64 bit values

2018-09-27 Thread Keith Medcalf
Well, you could call it a shiny shoe integer. the "shiny shoe" part is just ignored, just like your use of the word unsigned. And no, the value stored was a IEEE-754 double precision floating point so you got to keep the high 53 bits are the rest were discarded (this is because the value was

Re: [sqlite] Can write to database using API but get an error on writing with sqlite3.exe

2018-09-26 Thread Keith Medcalf
Have you checked to make sure the ID that you are using (interactively) is permitted read/write access to the directory containing the db files and to the files themselves? I mean *actually* checked that you have permission, since I doubt that the CGI process is running with the same ID you

Re: [sqlite] .timer

2018-09-25 Thread Keith Medcalf
No, I have not. The .timer discrepancy is due to the overhead of memory management which is done via DPC's and thus they accrue to the Supervisor and not the user process, the user process sees it as missing time. The underlying issue with the cache stride management has not been addressed

Re: [sqlite] weekday time modifier

2018-09-25 Thread Keith Medcalf
Actually no, the calculation is based on the Julian Day number in the struct DateTime. When you "load" a struct DateTime from a "string", the string is parsed and the constituent parts are used to create the JulianDay (iJD) field of the structure. This means that the iJD value is then

[sqlite] .lint fkey-indexes broken broken by SQLITE_EXPLAIN_ESTIMATED_ROWS

2018-09-16 Thread Keith Medcalf
Broken by SQLITE_EXPLAIN_ESTIMATED_ROWS ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Unique Constraint Failed

2018-09-14 Thread Keith Medcalf
Change is not likely. Putting a "UNIQUE" constraint is syntactic sugar for creating a unique index. That is CREATE TABLE dataStreamRecord ( fwParameterID INTEGER NOT NULL, dateTime INTEGER NOT NULL, data INTEGER NOT NULL, UNIQUE (fwParameterID, dateTime) ); is merely an

Re: [sqlite] [SQLITE]select from a table and use its data to select from another one

2018-09-14 Thread Keith Medcalf
If you ONLY want columns returned from table2 then: select table2.* from table2 join table1 on table2.rowid = table1.rowid where table1.name like '%smth%'; which is really the same thing as: select table2.* from table2, table1 where table2.rowid = table1.rowid and table1.name

Re: [sqlite] foreign_keys = 0N with Entity Framework 6.2.0

2018-09-13 Thread Keith Medcalf
PRAGMA x[=xx] is DML not DDL, though it might not return any rows, but it is a query. Have you tried running the pragma foreign_keys=1; as a DML (which may return results) rather than as DDL (which do not). Perhaps the entity framework is not executing the statement completely. --- The

Re: [sqlite] SQLITE touches unchanged expression indexes on update

2018-09-12 Thread Keith Medcalf
.15] >28IdxInsert 1 1 2 2 00 key=r[1] >29IdxInsert 3 4 5 2 00 key=r[4] >30MakeRecord 9 3 13 00 >r[13]=mkrec(r[9..11]) >31Insert 0 138 Foo05 intkey=

Re: [sqlite] SQLITE touches unchanged expression indexes on update

2018-09-12 Thread Keith Medcalf
00 >key=r[14..15] >26Column 0 0 14 00 r[14]=Foo.x >27IdxDelete 3 14200 >key=r[14..15] >28IdxInsert 1 1 2 2 00 key=r[1] >29IdxInsert 3 4

Re: [sqlite] SQLITE touches unchanged expression indexes on update

2018-09-12 Thread Keith Medcalf
my original, but different. So now I'm >wondering where the noop's, explain's, and comments are coming from >and what affects them. > > >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent:

Re: [sqlite] SQLITE touches unchanged expression indexes on update

2018-09-12 Thread Keith Medcalf
Interesting ... I get different output with explain comments enabled: addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 41000 Start at 41 1 Null

Re: [sqlite] sql UPDATE schema

2018-09-07 Thread Keith Medcalf
Your picture of the SQL statement is missing an operator -- your expression after WHERE is malformed. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users-

Re: [sqlite] Can you use ORDER BY clause in aggregate functions?

2018-09-07 Thread Keith Medcalf
>Can you use ORDER BY clause in aggregate functions? It seems that you >cannot; it is only available for window functions. >However, sometimes is useful using ORDER BY with aggregate functions >that aren't window functions, such as GROUP_CONCAT function. >Therefore is the suggestion to add it if

Re: [sqlite] Diferents values with equivalent queries. is a bug?

2018-09-06 Thread Keith Medcalf
Here is the two queries re-formatted to be more readable and comparable by humans ... and they appear as if they should both return the same result ... However, the declared affinity of the column codcnd is INTEGER in one case and TEXT in the other. When one is doing an equijoin, one of the

Re: [sqlite] Incorrect result when using WHERE with with correlated sub-select query

2018-09-05 Thread Keith Medcalf
Hehehe. I didn't even notice the floating point equality test Richard, so here we go: Try this one as 1e-13 is 1 ulp at a scale of 1000.0 ... which should be more than accurate enough in this case ... WITH GbC AS ( SELECT c.Country, c.CustomerId,

Re: [sqlite] Incorrect result when using WHERE with with correlated sub-select query

2018-09-05 Thread Keith Medcalf
Seems to return 26 rows for the current tip, what version are you experiencing the issue with? SQLite version 3.25.0 2018-09-03 17:11:11 Enter ".help" for usage hints. sqlite> .version SQLite 3.25.0 2018-09-03 17:11:11 f1138a38bd23f201a35621a71e82c5718abddb42ab82938e9516ab9d43e4alt2 zlib

Re: [sqlite] CAST AS STRING always returns 0 for STRING columns

2018-09-05 Thread Keith Medcalf
"STRING" is not a known affinity and equates to NUMERIC affinity/storage class. That means that your code is the equivalent of: create table a(col1 NUMERIC); insert into a values ('asdf'); select cast(col1 as NUMERIC) from a; Which will, of course, return the value 0. This is because: 1)

Re: [sqlite] sqlite3_column_* with error handling

2018-09-04 Thread Keith Medcalf
On Tuesday, 4 September, 2018 14:00, Clemens Ladisch wrote: >Keith Medcalf wrote: >> 6) If the column type is SQLITE_BLOB >> a) Retrieve the column value pointer using column_blob >> b) If the returned pointer is NULL, then an error has occurred &g

Re: [sqlite] sqlite3_column_* with error handling

2018-09-04 Thread Keith Medcalf
Well, that is not exactly true. If you attempt to retrieve the column values properly, either no errors can occur or if they do, they are obvious. 1) Query the column_type 2) If the column_type is SQLITE_NULL then return a NULL indicator and stop processing these steps. 3) If the

Re: [sqlite] Problems with compiling SQLite for MUSL.

2018-09-02 Thread Keith Medcalf
On Sunday, 2 September, 2018 20:32, John Found wrote: >On Sun, 02 Sep 2018 14:18:50 -0600 "Keith Medcalf" wrote: >> This is a compiler issue and has nothing to do with SQLite3. >>Anything you compiled using that compiler would exhibit the same >>problem

Re: [sqlite] Problems with compiling SQLite for MUSL.

2018-09-02 Thread Keith Medcalf
This is a compiler issue and has nothing to do with SQLite3. Anything you compiled using that compiler would exhibit the same problems ... The source for SQLite3 does not change the default compiler packing or alignment, so whatever alignment the compiler thinks is appropriate is what gets

[sqlite] Missing External Declarations

2018-09-01 Thread Keith Medcalf
1. sqlite3_create_window_function(...) is not available in the indirection list (sqlite3ext.h) and so you cannot have an "Windowing Function" in a loadable extension, they must all the part of the core (appended to the amalgamation file). 2. the fileio.c extension also only works (will only

Re: [sqlite] Best practices for forward conversion of database formats?

2018-08-30 Thread Keith Medcalf
Or you can store the version in a database table itself. Though I suppose the user_version pragma is kinda like a table: create table version(user_version); and then putting a single integer in there. All the "good" applications ship with an upgrader from *any* version ever to the current

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