Re: [sqlite] Better way to get range of dates

2018-08-30 Thread Keith Medcalf
Slightly more efficient code is generated for the BETWEEN version (the LHS of the between is only calculated once). It is also somewhat easier to read. sqlite> select x from x where x between 1 and 10; QUERY PLAN `--SCAN TABLE x addr opcode p1p2p3p4 p5 comment

Re: [sqlite] sqlite on IBM z/OS Unix

2018-08-29 Thread Keith Medcalf
I am not familiar with the internals of z/OS ... YMMV. My initial take would be that it would depend on whether the LE remains active (initialized) and maintains its memory allocations/file opens, etc, between invocations from your native z/OS assembly code. That is to say is the sequence:

Re: [sqlite] Get data in one query

2018-08-29 Thread Keith Medcalf
... don't forget that Date('now') returns the UT1 date, not the local (as in Wall Clock/Calendar) date ... date('now', 'localtime') gives the local date in accordance with the timezone where your computer thinks it is located and should always be accurate for 'now' but maybe not a few years in

Re: [sqlite] Shared memory cache files on disk?

2018-08-28 Thread Keith Medcalf
SQLITE_USE_URI If this is not defined then URI's are not parsed. https://www.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. >-Original Message- >From: sqlite-users [mailto:sqlite-users-

Re: [sqlite] Blob and Text columns: what's the difference?

2018-08-28 Thread Keith Medcalf
On Tuesday, 28 August, 2018 07:50, Tim Streater wrote: >What is actually the difference between a column declared as TEXT and >one declared as BLOB in an SQLite database? Not a thing. You are free to store data of any type in any column in any row. The "TEXT" declaration only means that

Re: [sqlite] [EXTERNAL] Query on TEMP view.

2018-08-28 Thread Keith Medcalf
HAVING is only applicable to GROUP BY's. That is, the WHERE clauses constrain what goes into the sorter for the "group by" operation and the HAVING clauses constrain what comes out of the sorter from the "group by" operation and is returned as a query result. I think that the issue is that

Re: [sqlite] Query on TEMP view.

2018-08-28 Thread Keith Medcalf
There are a myriad of reasons for the behaviour you are seeing and they affect only performance and not correctness. In other words, you think that your UDF is more "expensive" to compute than the PPID == 2 test, and therefore the least expensive test should be performed first so that the

Re: [sqlite] Why trigger (UPDATE) does not work?

2018-08-26 Thread Keith Medcalf
Well, the documentation *says* that a with clause cannot be used in a trigger, but that is incorrect, at least for the current tip of trunk ... because the following script works ... ---//--- snip ---//--- pragma recursive_triggers = 1; create table if not exists services ( id

Re: [sqlite] Time Precision

2018-08-26 Thread Keith Medcalf
Are you running Windows or Unix? I am sending this to you as I was just looking into this again and although SQLite maintains time internally with a millisecond precision, the API used on Windows to read the time is limited by the Clock Resolution (usually about 16.5 ms). If you are using

Re: [sqlite] keys

2018-08-24 Thread Keith Medcalf
On Friday, 24 August, 2018 17:31, w...@us.net wrote: >"The parent key of a foreign key constraint is not allowed to use the >rowid. The parent key must used named columns only." >Why is this? You should think of this as: >"The parent key of a foreign key constraint is not allowed to use the

Re: [sqlite] bug: compare number with empty string get different resut

2018-08-19 Thread Keith Medcalf
The comparison is between a column with "numeric" (integer) affinity and a text value with no affinity. The text value is an empty string. Affinity conversion would attempt to convert the text value with no affinity into a numeric value (0) IF AND ONLY IF the conversion is lossless and

Re: [sqlite] The upcoming alter table rename column

2018-08-15 Thread Keith Medcalf
;) And I am stealing it back ... I like your changes that show the computed column affinity! --- 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] The upcoming alter table rename column

2018-08-15 Thread Keith Medcalf
single-quotes around the tablename -- it is a string not an identifier ... --- 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] Safe sqlite over remote filesystem?

2018-08-14 Thread Keith Medcalf
NFS is a Remote/Network File System. iSCSI is a local file system. iSCSI is just transporting the SCSI protocol over a "different" physical layer sort of like how you can transport SCSI over really really fat parallel SCSI cables, PATA cables, or SATA cables. (That is, pSCSI, sSCSI, and

Re: [sqlite] Can date('now') fire a trigger automatically after a day passed?

2018-08-10 Thread Keith Medcalf
Remember that date('now') is the UT1 date, not the local (wallclock) date. To get the date 'now' for the timezone in which your computer thinks it is located you need to add the 'localtime' qualifier, as in date('now', 'localtime') ... --- The fact that there's a Highway to Hell but only a

Re: [sqlite] Database locks

2018-08-07 Thread Keith Medcalf
By "constantly writing" I presume you mean "periodically writing". For example doing one independent INSERT every millisecond where there is no dependency from between inserts is "periodic writing". "Constantly writing", from a database perspective, means a single transaction that never ends

Re: [sqlite] Database locks

2018-08-07 Thread Keith Medcalf
Make sure to wrap your transactions in BEGIN / COMMIT. Use BEGIN IMMEDIATE for transactions that you know are going to write, and plain BEGIN for read-only transactions. You can omit the explicit BEGIN / COMMIT if each transaction consists of only a single statement since autocommit will do

Re: [sqlite] Using CTE with date comparison

2018-08-05 Thread Keith Medcalf
;From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of pali >Sent: Sunday, 5 August, 2018 07:35 >To: SQLite mailing list >Subject: Re: [sqlite] Using CTE with date comparison > >On Sun, Aug 05, 2018 at 05:25:02AM -0600, Keith Medcalf wrote: >&g

Re: [sqlite] Using CTE with date comparison

2018-08-05 Thread Keith Medcalf
...@mailinglists.sqlite.org] On Behalf Of Csányi Pál >Sent: Sunday, 5 August, 2018 02:08 >To: SQLite mailing list >Subject: Re: [sqlite] Using CTE with date comparison > >2018-08-05 0:18 GMT+02:00 Keith Medcalf : >> >> WITH RECURSIVE >> dates(dateD) AS (VALUES(:Sta

Re: [sqlite] "Cheating" at making common tables

2018-08-04 Thread Keith Medcalf
On Saturday, 4 August, 2018 20:01, Stephen Chrzanowski wrote: >I was right. I got the tables done before a response. But still >would like to know if there's a SQLite method of doing so. Of course there is. >My method was to use a templating application that I wrote at work. I >give it

Re: [sqlite] Using CTE with date comparison

2018-08-04 Thread Keith Medcalf
WITH RECURSIVE dates(dateD) AS (VALUES(:StartDate) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE date(dateD, '+1 year') <= :EndDate ) SELECT max(dateD), count(*) FROM dates; --- The fact that

Re: [sqlite] Common index for multiple databases

2018-08-03 Thread Keith Medcalf
On Friday, 3 August, 2018 13:50, Warren Young wrote: >I’d be careful trying to apply your knowledge directly to SQLite. >dBase comes out of the non-SQL world, so it’s going to have a >different outlook in many areas. >If the following is a fair description of how FoxPro for DOS indexes >work,

Re: [sqlite] Common index for multiple databases

2018-08-03 Thread Keith Medcalf
Not exactly. The index is stored in the SAME FILE that contains the table. For example in dBase I (or II or III) you might have the following files: Customer.DBF CustNo.NDX CustName.NDX where the two NDX files index fields from the Customer.DBF file. In FoxPro you can have "compound"

Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

2018-08-02 Thread Keith Medcalf
but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: Yuri [mailto:y...@rawbw.com] >Sent: Thursday, 2 August, 2018 17:06 >To: SQLite mailing list; Keith Medcalf >Subject: Re: [sqlite] Violated failed foreign key constraint d

Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

2018-08-02 Thread Keith Medcalf
e's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: Yuri [mailto:y...@rawbw.com] >Sent: Thursday, 2 August, 2018 16:37 >To: SQLite mailing list; Keith Medcalf >Subject: Re: [sqlite] Violated failed foreign k

Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

2018-08-02 Thread Keith Medcalf
Many versions ago a CLI command (that is, the sqlite3 Command Line Interface) was created so that folks would stop complaining about referential integrity enforcement being slow when they did not create the indexes that were necessary to enforce referential integrity (because failing to have

Re: [sqlite] Common index for multiple databases

2018-08-02 Thread Keith Medcalf
mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Jean-Luc Hainaut >Sent: Thursday, 2 August, 2018 15:04 >To: SQLite mailing list >Subject: Re: [sqlite] Common index for multiple databases > >On 02/08/2018 20:50, Keith Medcalf wrote: >> In no DBMS kno

Re: [sqlite] Common index for multiple databases

2018-08-02 Thread Keith Medcalf
tial integrity across multiple "attached" databases, nor use cross "attachment" triggers (but you cannot do that now anyway). --- 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] Common index for multiple databases

2018-08-02 Thread Keith Medcalf
You misunderstand how dBase databases work. An index is created on a table (.DBF file) and stored in an index file (.NDX). You can have multiple indexes associated with a single .DBF file (which means multiple .NDX files). FoxPro has a non-standard index format that permits the multiple

Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

2018-08-02 Thread Keith Medcalf
don't trigger >the error at all > >On Thu, Aug 2, 2018 at 9:35 AM Keith Medcalf >wrote: > >> You observe no violation when VIOLATION is 0 because there is no >> referential integrity violation to report ... >> > >Really Keith? Parent IDs are in range [

Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

2018-08-02 Thread Keith Medcalf
e.org] On Behalf Of Dominique Devienne >Sent: Thursday, 2 August, 2018 01:48 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Violated failed foreign key constraint delays >the rest of transaction ; Some foreign key violations don't trigger >the error at all > &

Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all

2018-08-02 Thread Keith Medcalf
You observe no violation when VIOLATION is 0 because there is no referential integrity violation to report ... However, you are correct that when inserting data the as shown in your code (where there is a referential integrity violation) the insertion is much slower after the violation

Re: [sqlite] Error: foreign key mismatch - "loan" referencing "user"

2018-08-01 Thread Keith Medcalf
Because the required unique index on copy(id_book, copy_number) exists (in the table definition). --- 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] (no subject)

2018-07-30 Thread Keith Medcalf
>"SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0", >"SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1", >"SQLITE_DEFAULT_FOREIGN_KEYS=1" >Is there something about the combination of options I've used? Do you get different results when using different options? (of course, if you turn

Re: [sqlite] Sqlite Sharding HOWTO

2018-07-30 Thread Keith Medcalf
>> A query doing a single insert of a few bytes with no Indexes, no >> triggers, no functions will be stupendously fast, whereas any >> increase in one or more of the above will slow things down. >> How much exactly is something you need to test, any guesswork >> will not be useful. What I can

Re: [sqlite] Sqlite Sharding HOWTO

2018-07-29 Thread Keith Medcalf
>In the current use case thre's a single process. The way I see it, in >the near future it would probably increase to 3-4 processes, >each doing 10-100 writes per second or so. Each write would be around >1KB-20KB (one single text field, I guess). >I wonder if writing data in batches would be

[sqlite] Parser Error? (was: Immediate mode transaction gets error 5, SQLITE_BUSY when attempting commit)

2018-07-24 Thread Keith Medcalf
>I have a database with one process (in one thread) writing to it, and >another process (also in a single thread) reading from it only. All >writes are done under BEGIN TRANSACTION IMMEDIATE. Sometimes, an END >TRANSACTION fails with error 5, SQLITE_BUSY. The documentation says >this should

Re: [sqlite] Immediate mode transaction gets error 5, SQLITE_BUSY when attempting commit

2018-07-24 Thread Keith Medcalf
>I have a database with one process (in one thread) writing to it, and >another process (also in a single thread) reading from it only. All >writes are done under BEGIN TRANSACTION IMMEDIATE. Sometimes, an END >TRANSACTION fails with error 5, SQLITE_BUSY. The documentation says >this should

Re: [sqlite] Issue using SEE

2018-07-20 Thread Keith Medcalf
g] On Behalf Of J Decker >Sent: Friday, 20 July, 2018 12:13 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Issue using SEE > >On Fri, Jul 20, 2018 at 11:05 AM Keith Medcalf >wrote: > >> >> Firstly, the SQLITE_DLL define does not exist (is this a

Re: [sqlite] Issue using SEE

2018-07-20 Thread Keith Medcalf
Firstly, the SQLITE_DLL define does not exist (is this a bug in the docs>? Secondly you did not define SQLITE_HAS_CODEC as required to integrate the SEE codec/ Thirdly a .dll file cannot be created with ar. ar is for creating libraries, not DLLs. (.a files) To statically link you should just

Re: [sqlite] Normalize extension

2018-07-15 Thread Keith Medcalf
On Saturday, 14 July, 2018 22:24, Gabriel Chiquini wrote: >Hi everyone, I tried to use the normalize function I found on the >ext/misc folder, but I couldn't load it, it returns the following >error: "normalize.so: undefined symbol: sqlite3_normalize_init". I am >using the latest version of

Re: [sqlite] Lowering totalUsed

2018-07-12 Thread Keith Medcalf
This query will work fine. You could also do something like: UPDATE tips SET totalUsed = totalUsed - (SELECT MIN(totalUsed) - 1 FROM tips); which would include the extra 1 (the new base) in the scalar subquery. The expression (SELECT MIN(totalUsed) FROM tips) is not correlated with the

Re: [sqlite] Kind of pivot table

2018-07-10 Thread Keith Medcalf
>boun...@mailinglists.sqlite.org] On Behalf Of Jens Alfke >Sent: Monday, 9 July, 2018 10:49 >To: SQLite mailing list >Subject: Re: [sqlite] Kind of pivot table > > >> On Jul 7, 2018, at 11:49 PM, Keith Medcalf >wrote: >> >> Why not use MOD (%) as in >> >

Re: [sqlite] Round-tripping SQLite back and forth between text representation.

2018-07-09 Thread Keith Medcalf
.dump in the command line shell? --- 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 Randall Smith >Sent:

Re: [sqlite] Kind of pivot table

2018-07-08 Thread Keith Medcalf
-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof >Sent: Sunday, 8 July, 2018 02:16 >To: SQLite mailing list >Subject: Re: [sqlite] Kind of pivot table > >2018-07-08 9:10 GMT+02:00 Keith Medcalf : > >> >> sqlite> &

Re: [sqlite] Kind of pivot table

2018-07-08 Thread Keith Medcalf
s [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof >Sent: Sunday, 8 July, 2018 00:59 >To: SQLite mailing list >Subject: Re: [sqlite] Kind of pivot table > >2018-07-08 8:49 GMT+02:00 Keith Medcalf : > >> >> Why not use MOD (%) as in

Re: [sqlite] Kind of pivot table

2018-07-08 Thread Keith Medcalf
Why not use MOD (%) as in ABS(RANDOM() % 6) --- 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 Cecil

Re: [sqlite] Subject: Re: Date Search

2018-07-06 Thread Keith Medcalf
This will however only work in all GMT/UT1/UTC. If the "input" (ie, the string) is "localtime" then the localtime modifier needs to be added to the date() function as in: date(date_type, 'unixepoch', 'localtime') like '2018-%' Note that you cannot create an index on the expression

Re: [sqlite] Time Precision

2018-07-05 Thread Keith Medcalf
>SELECT CAST((SELECT (julianday('now', 'localtime') - >julianday('1970-01-01'))*24*60*60*1000) AS INTEGER); Are you sure you want to be mixing up timezones? julianday('1970-01-01') returns the julianday timestamp for 1970-01-01 00:00:00 GMT julianday('now', 'localtime') returns the julianday

Re: [sqlite] Date Search

2018-07-05 Thread Keith Medcalf
Correct. You have stored integer Unix Epoch timestamps. You cannot do "string" searches on integers (at least not ones like what you have asked for, which involves conversion of an integer representing a Unix Epoch offset to an ISO-8601 string, not to an ordinary "string representation of

Re: [sqlite] CASE and NULL

2018-07-05 Thread Keith Medcalf
On Thursday, 5 July, 2018 00:57, Donald Shepherd : >On Thu, 5 Jul 2018 at 16:45, Simon Slavin >wrote: >> On 5 Jul 2018, at 7:30am, Clemens Ladisch >wrote: >>> The expression "x = x" will fail for NULL, but succeed for >>> everything else. So you can use that to implement a >>> "not-NULL

Re: [sqlite] Window Function Crash -- overriding builtin aggregate

2018-07-02 Thread Keith Medcalf
om: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy >Sent: Monday, 2 July, 2018 10:21 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Window Function Crash -- overriding builtin >aggregate > >On 07/02/2018 01:

Re: [sqlite] Window Function Crash -- overriding builtin aggregate

2018-07-02 Thread Keith Medcalf
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin >Sent: Monday, 2 July, 2018 04:27 >To: SQLite mailing list >Subject: Re: [sqlite] Window Function Crash -- overriding builtin >aggregate > >On 2 Jul 2018, at 7:40am, Keith Medcalf wrote: > >> Even if I register

[sqlite] Window Function Crash -- overriding builtin aggregate

2018-07-02 Thread Keith Medcalf
I have overridden the builtin AVG function with a function of my own that computes the average by using the "running average" method rather than the simple sum/count method. This function is registered as an old fashioned aggregate function. After the window extension is put in place, it

[sqlite] New WIndow Functions ... question

2018-07-01 Thread Keith Medcalf
They are part of the current draft release: http://www.sqlite.org/draft/releaselog/3_25_0.html Dan, I see that there is a new create function to create the window functions which have some slight changes to the methods being called and what they do. I presume that a function defined with the

Re: [sqlite] Time Precision

2018-07-01 Thread Keith Medcalf
You can make a user-defined function on Windows that returns the UnixTime to the limit of Accuracy of the underlying hardware/software (100 huns max) and to the limit of precision of the IEEE754 double precision floating point format with the following (so an accuracy of 100 nanoseconds with a

Re: [sqlite] Time Precision

2018-07-01 Thread Keith Medcalf
The "unixepoch" time used by SQLite is an "integer" in whole seconds of precision. ISO-8601 datetime strings are also "by default" generated in seconds of precision. If you use strftime rather than datetime then the ISO8601 strings can be read with "unlimited" precision and written with

Re: [sqlite] column types and constraints

2018-06-30 Thread Keith Medcalf
Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Warren Young >Sent: Saturday, 30 June, 2018 18:09 >To: SQLite mailing list >Subject: Re: [sqlite] column types and constraints > >On Jun 29, 2018, at 10:17 PM, Keith Medcalf &

Re: [sqlite] column types and constraints

2018-06-30 Thread Keith Medcalf
In your case, yes. If you do not wish SQLite3 to "convert" to the requested storage type on storage of a value, then do not specify a storage type (or specify a storage type of BLOB). Then whatever you request-to-store will be stored without conversion. SQLite version 3.25.0 2018-06-21

Re: [sqlite] column types and constraints

2018-06-30 Thread Keith Medcalf
When you declare a column with no affinity (that is with blob or none affinity), the data is stored precisely and exactly as presented with no conversions performed by SQLite3. You give it a character string, it stores a character string. You give it an integer, it stores an integer. You

Re: [sqlite] Determine collation associated with sort

2018-06-30 Thread Keith Medcalf
SQLite will not select the collation based on the index -- it is exactly the opposite -- the collation requested is used to find an appropriate index. So if you do an order by that needs BINARY collation, and the only index available is a NOCASE collation index, that index cannot be used

Re: [sqlite] unique values from a subset of data based on two fields

2018-06-30 Thread Keith Medcalf
: [sqlite] unique values from a subset of data based on >two fields > >Easier and pretty obvious :) Thanks Keith > > > >Paul >www.sandersonforensics.com >SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> > >On 29 June 2018 at 23:20, Keith Medcalf

Re: [sqlite] column types and constraints

2018-06-30 Thread Keith Medcalf
You "put" a ieee754 floating point double. If you retrieved an ieee754 floating point double, you would get back that which you put! The fact that internally SQLite3 stored it as a 3 (integer, token, string, whatever) is irrelevant. You "gets" what you "puts", as long as what you "putted"

Re: [sqlite] column types and constraints

2018-06-29 Thread Keith Medcalf
>-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Warren Young >Sent: Friday, 29 June, 2018 19:35 >To: SQLite mailing list >Subject: Re: [sqlite] column types and constraints > >On Jun 29, 2018, at 4:36 PM,

Re: [sqlite] column types and constraints

2018-06-29 Thread Keith Medcalf
pragma application_written_by=coder; -vs the default, which is- pragma application_written_by=programmer; ;-) All of the issues raised are "application" problems, not database problems. Clearly if you retrieved a value from the database and want to use it as an index you have to do bounds

Re: [sqlite] unique values from a subset of data based on two fields

2018-06-29 Thread Keith Medcalf
>I want a query that returns all of the records with status = 1 and >unique records, based on name, where the status =0 and the name is >not in the list status=1 Translation into SQL using English to SQL Translator, using the most direct translation on the "problem statement" above directly

Re: [sqlite] sqlite-users Digest, Vol 126, Issue 28

2018-06-28 Thread Keith Medcalf
t: Thursday, 28 June, 2018 10:00 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] sqlite-users Digest, Vol 126, Issue 28 > >> If you give the parent column a proper affinity (ie, integer) do >you get "happiness making" results? > >nope, made no difference &

Re: [sqlite] insert or replace performance with self "references" column

2018-06-27 Thread Keith Medcalf
If you give the parent column a proper affinity (ie, integer) do you get "happiness making" results? --- 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] column types and constraints

2018-06-27 Thread Keith Medcalf
In the current tip of trunk it pretends the unknown tokens are surrounded by double-quotes. Until you interpose a non type keyword ... at which point the parser stops "eating your junk as the type declaration" and resumes the grammar .. sqlite> create table x(x happy days); sqlite> pragma

Re: [sqlite] Understanding SELECT statement

2018-06-26 Thread Keith Medcalf
Actually, you would probably write: SELECT aDate FROM TeachingSaturdaysInSchoolYear WHERE aDate NOT IN (SELECT aDate FROM SchoolYearTeachingDays); Since the subquery is not correlated there is no *need* for aliases ... but if you want to type more characters you are

Re: [sqlite] Question about hidden columns

2018-06-22 Thread Keith Medcalf
You know that you can use the hidden columns by name in the WHERE cause correct, and do not have to use function parameters? So the ext/misc/series.c in the repository defines a virtual table generate_series which effectively returns each "value" generated by the statement: for (value=start;

Re: [sqlite] Strange Corruption Issue

2018-06-18 Thread Keith Medcalf
y a run >for >its money :P > >-Rowan > >On 19 June 2018 at 12:37, Keith Medcalf wrote: > >> >> The new "consumer" SSDs from Samsung carry a 1200 TBW/8 year >warranty on a >> 4 TB device. That is a lot of writing for a "consumer desktop"

Re: [sqlite] Strange Corruption Issue

2018-06-18 Thread Keith Medcalf
The new "consumer" SSDs from Samsung carry a 1200 TBW/8 year warranty on a 4 TB device. That is a lot of writing for a "consumer desktop" computer ... that is about 400 GB written per DAY every day for 8 years! --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a

Re: [sqlite] .timer

2018-06-18 Thread Keith Medcalf
The following pure python code does the same thing, memmapping the file when reading backwards ... works in Python 2 and 3, 32 and 64 bit. Emulates what sqlite3 is doing as closely as I can manage. As long as the mmap fits in memory it does not seem to affect performance. ---//--- from

Re: [sqlite] .timer

2018-06-18 Thread Keith Medcalf
These are with SQLITE3's memmap turned off (SQLITE_DEFAULT_MMAP_SIZE 0). I set the MAX_SIZE to 0 as well and it made no difference. Windows is memmapping the file by itself. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

Re: [sqlite] Criteria to define two fields as Primary Key or Unique

2018-06-17 Thread Keith Medcalf
Also note that you probably want your application to store the password as a salted-hash, and not as a plain-text password. Otherwise someone could look up the passwords with a text editor ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about

Re: [sqlite] .timer

2018-06-17 Thread Keith Medcalf
No, when you use OFFSET you are reading and discarding rows. There is no difference between: select * from t; and discarding all the results except the last row, and, select * from t limit ,1; for a table containing 1 rows. In both cases you have to read the entire table

[sqlite] TEMP Database in Memory (cannot free memory)

2018-06-16 Thread Keith Medcalf
When you have a temp database in memory it appears that you cannot release the memory for it. Even after you remove (drop) all the tables in the temp database, memory usage does not decrease. pragma shrink_memory does not free the memory and pragma temp.shrink_memory neither. The memory can

Re: [sqlite] .timer

2018-06-16 Thread Keith Medcalf
; > >________ >From: sqlite-users on >behalf of Keith Medcalf >Sent: Saturday, June 16, 2018 6:56:19 PM >To: SQLite mailing list >Subject: Re: [sqlite] .timer > > >I have confirmed that the distributed shell on sqlite.org displays >the s

Re: [sqlite] .timer

2018-06-16 Thread Keith Medcalf
Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Saturday, 16 June, 2018 11:56 >To: SQLite mailing list >Subject: Re: [sqlite] .timer > > >I have confirmed that the distributed shell on sqlite.org display

Re: [sqlite] .timer

2018-06-16 Thread Keith Medcalf
ven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Friday, 15 June, 2018 18:28 >To: SQLite mailing list >Subject: Re: [sqlite] .timer &g

Re: [sqlite] .timer

2018-06-16 Thread Keith Medcalf
Yeah, I had a lot of problems with the fileio.c extension after the fsdir virtual table was added. It needs a header file "test_windirent.h" to be available. I had to do some fiddling to get it to compile properly using MinGW (GCC) on Windows. I thought Richard had fixed it. It also makes

Re: [sqlite] .timer

2018-06-15 Thread Keith Medcalf
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 Keith Medcalf >Sent: Friday, 15 June, 201

Re: [sqlite] .timer

2018-06-15 Thread Keith Medcalf
says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin >Sent: Friday, 15 June, 2018 17:10 >To: SQLite mailing list >Subject: Re: [sqlite] .timer >

Re: [sqlite] .timer

2018-06-15 Thread Keith Medcalf
gement code ... --- 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 Keith Medcalf >Sent: Friday, 15

Re: [sqlite] .timer

2018-06-15 Thread Keith Medcalf
Ok, I have been able to reproduce this but have not figured out what is happening. And you are correct, it appears to be related to "reading backwards" somehow. It is unaffected by the WIN32 cache mode that is set (I tested all modes and they all behave the same -- except that I could not

Re: [sqlite] .timer

2018-06-15 Thread Keith Medcalf
Your other "right", the one on the left :) --- 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 Beha

Re: [sqlite] .timer

2018-06-15 Thread Keith Medcalf
You are using Windows 10? It is on the "Performance" tab, select the wee graph on the right for "Memory". In the detail, right underneath "Available" and beside "Committed" at the bottom where all the text is. Oh, you have to be in "more details" view, not in the "simple" default view ...

Re: [sqlite] .timer

2018-06-15 Thread Keith Medcalf
>You spoon fed me on a previous thread how to load extensions using a >core_init function placed at the end of the sqlite3.c code. I do have >the series.c in my core_init so it is available to me and works fine >in my cpp code. I don’t see how that relates to sqlite3.exe though. >How do you get

Re: [sqlite] .timer

2018-06-15 Thread Keith Medcalf
The increase/decrease in memory is almost certainly the cache (after running the command once and before flushing look and see what Task Manager says for "Cached", then look again after you do the flush and see if it releases it. This is memory that would otherwise be unused being used by

Re: [sqlite] .timer

2018-06-15 Thread Keith Medcalf
ER > > dwFlagsAndAttributes |= FILE_FLAG_NO_BUFFERING; > >#endif > > > >Is that correct? > > > >BTW ‘select * from generate_series(1,10)’ gives me an error ‘no such >table : generate_series’ in sqlite3.exe. I thought it was compiled >into the shell by default

[sqlite] Just noticed ... fileio.c cannot be compiled as an extension ...

2018-06-15 Thread Keith Medcalf
It looks for an external sqlite3_win32_utf8_to_unicode function. Although this is an exported API function the linker on Windows cannot resolve it at compile time. Even if it could, the loader trampoline could not link it back to the API in the original (loading) sqlite3 code (especially not

Re: [sqlite] .timer

2018-06-15 Thread Keith Medcalf
hereabouts? > >3 Will it still work if I compile in 64 bit mode? > > > > >From: sqlite-users on >behalf of Keith Medcalf >Sent: Thursday, June 14, 2018 10:09:50 PM >To: SQLite mailing list >Subject: Re: [sqlite] .timer > > &

Re: [sqlite] .timer

2018-06-15 Thread Keith Medcalf
it still work if I compile in 64 bit mode? > > > > >From: sqlite-users on >behalf of Keith Medcalf >Sent: Thursday, June 14, 2018 10:09:50 PM >To: SQLite mailing list >Subject: Re: [sqlite] .timer > > >See the following web page fo

Re: [sqlite] .timer

2018-06-14 Thread Keith Medcalf
way 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 Keith Medcalf >Sent: Thursday, 14 June, 2018 14:16 >To: SQLite mailing

Re: [sqlite] .timer

2018-06-14 Thread Keith Medcalf
Cannot reproduce. I am using the current trunk that I compile myself with MinGW 8.1.0 on Windows 10 1803 Pro for Workstations. The laptop has a 4 Ghz Quad Core Xeon and the disk is a Samsung NVMe drive. About the only relevant change is that I have forced the Windows caching mode from

Re: [sqlite] .timer

2018-06-14 Thread Keith Medcalf
Exactly. REAL is the elapsed time according to the wall clock USER is the actual time the CPU spent executing user code SYS is the actual time the CPU spent executing system code In "modern" Operating Systems USER usually reflects CPU usage by your process while the CPU is in USER mode

Re: [sqlite] How to convert SQL file into database when a column value is Inf?

2018-06-12 Thread Keith Medcalf
You can replace the "Inf" with 1e400 and -Inf with -1e400. These values will be parsed and stored as the appropriate plus/minus Infinity since they are larger than the maximum representable IEEE-754 Double Precision Binary Float. --- The fact that there's a Highway to Hell but only a Stairway

[sqlite] Possible Input Parser Issue Inf How to convert SQL file into database when a column value is Inf?

2018-06-12 Thread Keith Medcalf
The tip of trunk also does not parse "Inf" or "-Inf" floating point values (eg: in an insert statement), but will produce Inf and -Inf output. The bind and column values interfaces however do handle the IEEE inf/-inf correctly. Is this a bug/oversight in the parser? sqlite> create table x(x

Re: [sqlite] Trigger Performance

2018-06-11 Thread Keith Medcalf
overhead? Correct? > >On Tue, Jun 12, 2018 at 9:47 AM, David Burgess >wrote: >> Specifically, preparation of the "trigger part" of the statement is >> the overhead? Correct? >> >> >> On Mon, Jun 11, 2018 at 5:16 PM, Keith Medcalf > wrote:

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