Re: [sqlite] more efficient JSON encoding: idle musing

> > > I experimented with a number of similar ideas for storing JSON when I > was first designing the JSON components for SQLite. I was never able > to find anything that was as fast or as compact as just storing the > original JSON text. > I've also done a lot of experiments and was surprised

[sqlite] Either a bug or I don't understand SQL update

t) from taxitems2 where taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 77); /* taxrate = "M" */ and update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 71); /*

Re: [sqlite] New word to replace "serverless"

"In-process" describes it best for me. On Wed, Jan 29, 2020 at 8:16 AM Darren Duncan wrote: > The concepts I like the best so far are "in-process" or "integrated" or > something library-themed. -- Darren Duncan > > On 2020-01-27 2:18 p.m., Richard Hipp wrote: > > For many years I have

Re: [sqlite] Query planner regression with FTS4: slower path is chosen

I has been a while without response, so I just bumping this message. 19 July 2019, 14:21:27, by "Paul" : > I have a test case when the regression can be observed in queries that > use JOINs with FTS4 tables, somewhere in between 3.22.0 and 3.23.0. > For some reason

[sqlite] Query planner regression with FTS4: slower path is chosen

I have a test case when the regression can be observed in queries that use JOINs with FTS4 tables, somewhere in between 3.22.0 and 3.23.0. For some reason the planner decides to search non-FTS table first then scan the whole FTS table. Version 3.22.0 is the last unaffected, while issue is still

[sqlite] Will ROWID be required to be unique in contentless Full text search

My question is whether or not I can rely on my use of non-unique rowids in FTS5 contentless tables to remain unenforced into the future of SQLite or am I exploiting an oversight that may be resolved in the future? Details ... I have a key-value pair SQLite table (called props) used for storing

Re: [sqlite] Database Encryption (System.Data.SQLite)

As I was using the unchanged nuget package I assumed it would be a “default” encryption as it isn’t something I compiled or changed? Tithras Sent from my iPhone > On 1 Apr 2019, at 15:33, Simon Slavin wrote: > >> On 1 Apr 2019, at 3:30pm, Mattock Paul wrote: >> >>

Re: [sqlite] Database Encryption (System.Data.SQLite)

Simon, Thanks, do we have any idea on what Algorithm is used? Tithras > On 01 April 2019 at 14:28 Simon Slavin wrote: > > > On 1 Apr 2019, at 1:15pm, Mattock Paul wrote: > > > Just to confirm I am using the nuget package (System.Data.SQLite v1.0.109.2) > >

Re: [sqlite] Database Encryption (System.Data.SQLite)

e base) > > On 2019/04/01 18:27, "sqlite-users on behalf of Mattock Paul" > pmatt...@ntlworld.com> wrote: > > All, > > Would anyone be able to confirm what cipher is used for encrypting an > SQLite database when password="" is used? >

[sqlite] Database Encryption (System.Data.SQLite)

All, Would anyone be able to confirm what cipher is used for encrypting an SQLite database when password="" is used? I have seen old posts online which state its 128bit but assume this is now wrong and am after completing a design document which requires I state the encryption level.

[sqlite] System.Data.Sqlite Encryption Cipher

Hi, Would anyone be able to confirm what encryption cipher is used when SetPassword="" is set on database creation? Regards, Paul ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailma

Re: [sqlite] Regarding CoC

If my opinion has any value, even though being atheist, I prefer this CoC 100 times over the CoC that is being currently pushed onto the many open-source communities, that was created by some purple-headed feminist with political motives. This one does not have any hidden intentions (at least,

[sqlite] Compiler warning - signed unsigned mismatch if enable sorter references defined

Compiling the amalgamation (some previous versions including the current Version 3.25.2 (2018-09-25)) generates the following warning with SQLITE_ENABLE_SORTER_REFERENCES defined.  The build works fine and always has so this is really just benign. The warning is: sqlite3.c(106816): warning

Re: [sqlite] Common index for multiple databases

ity across multiple "attached" databases, nor use cross "attachment" triggers (but you cannot do that now anyway). --- If you do, for some reason, choose this approach then by default SQLite limits the number of attached databases to 10, you can adjust this up to a max of 125

[sqlite] Full text serch - Matching all except chosen

Forgive me if this is the wrong way to do this but I'm a newbie. I am using version 3.24.0 with FTS5 Is there a way to perform a full text search that returns every row except records matching a specified query string? The following does not work because of a syntax error as the syntax requires a

Re: [sqlite] Reducing index size

If I understand correctly then changing from a base64 index to a blob containing the raw bytes would save 25% Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 30 July 2018 at 09:32, Eric Grange wrote: > Hi, > > Is there

Re: [sqlite] Minimum Delta Time

How about just using a trigger to check if endtime is < starttime+10 and updating if it fires Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 11 July 2018 at 17:09, David Raymond wrote: > For a minimum of 10 minutes it'd be so

Re: [sqlite] Fwd: ON CONFLICT parser bug?

> > > > A SELECT in an UPSERT should always contain a WHERE clause. Like this: > >INSERT INTO t1(a) SELECT a FROM t2 WHERE true ON CONFLICT(A) DO NOTHING; > > Without the WHERE clause, the ON keyword tricks the parser into > thinking it is processing an "ON" join constraint on the FROM

[sqlite] Fwd: ON CONFLICT parser bug?

ble name, e.g. WHERE or LIMIT will suffice) works: INSERT INTO T1 (A) SELECT A FROM T2 WHERE 1=1 ON CONFLICT(A) DO NOTHING I'm sure others have found this already? Regards, Paul. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mai

Re: [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 wrote: > >I want a query that returns all of the records with status = 1 and > >unique reco

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

Thanks Ryan As often is the case the the actual problem is more complex than my example - sometimes we over simplify to, well, simplify - but you have both given me some ideas and I'll go away and play. Paul Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/A

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

Thank You David - I was just starting to play with CTEs Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 29 June 2018 at 17:45, David Raymond wrote: > with status_one as ( > select * > from names > where status =

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

I have a table Create table names (id int, status int, name text) 1, 1, 'paul' 2, 1,'helen' 3, 0, 'steve' 4, 0, 'steve' 5, 0, 'pete' 6, 0, 'paul' 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

Re: [sqlite] Return a column's contents to the free list

each have a serial type 0, 8 & 9 respectively and there is no data stored for the column - the content of the column is inherent in the serial types array. Blobs and Strings with no content are serial types 12 & 13, and there is also zero bytes of data associated with each. Paul www.san

Re: [sqlite] ROWID....

As ROWID is often an alias for an integer primary key then it needs to be able to represent both negaitive and positive integers other wise you restrict the range of an integer PK. Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On

Re: [sqlite] [EXTERNAL] Re: Sqlite delete slow in 4GB

, then you have exactly the same problem with records that come after the string fields. Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 6 June 2018 at 10:15, Hick Gunter wrote: > Your schema has a major flaw that is addressed

Re: [sqlite] Sqlite delete too slow in 4 GB database

Have you made sure aut_ovacuum is disabled? pragma *auto_vacuum * = 0 have you got a nice large pagesize if your records are that big? Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 4 June 2018 at 13:01, Olivier Mascia wrote: &

Re: [sqlite] [EXTERNAL] Re: database locked on select

If you are doing each update in a separate transaction it will be much slower than wrapping them in a single transaction. See the faq here, it refers to inserts but updates will be the same. http://sqlite.org/faq.html#q19 Cheers Paul On Wed, 30 May 2018 at 09:34, Torsten Curdt wrote

Re: [sqlite] ALTER TABLE

ty_check; integrity_check ok I have not done any thorough testing as this sort of thing is outside my main area of interest, but it might give you some ideas. It goes without saying that messing with the sqlite_schema is dangerous territory. Paul www.sandersonforensics.com SQLite Forensics Bo

Re: [sqlite] Is this really the best way to do this?

or more in tune with the default value in the schema SELECT CASE count(*) WHEN 0 THEN 'Ok' ELSE action END FROM blocked WHERE email = 'rwillett.dr...@example.com'; Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 16 May 2018 at 09:2

Re: [sqlite] Is this really the best way to do this?

How about something like SELECT CASE count(*) WHEN 0 THEN 'Discard' ELSE 'Ok' END FROM blocked WHERE email = 'rwillett.dr...@example.com'; Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 16 May 2018 at 10:35, Abroży Niepr

Re: [sqlite] question about DB

Try it in a command line shell with the timer on .timer on Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 14 May 2018 at 17:27, Simon Slavin <slav...@bigfraud.org> wrote: > On 14 May 2018, at 3:03pm, sebastian bermudez

Re: [sqlite] This list is getting spammed again

ahh disregard - it was there Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence

Re: [sqlite] This list is getting spammed again

Prob not nabble as my message does not seem to have made it there yet, didn't check mailman - I suspect that they are subscribed as spam is sent within seconds. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum

Re: [sqlite] This list is getting spammed again

Would it be possible for an admin to run a script that sent an individual email (e.g. different number in subject) to each user on the list and see who is sending the spam based on the replies? Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http

Re: [sqlite] Only see unused when there are unused records

Or SELECT count(*) AS Total, CASE WHEN Sum(used = 'unused') > 0 THEN Sum(used = 'unused') END AS NotUsed FROM quotes There might be a more succinct way Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/fo

Re: [sqlite] How to Handle BigInt

transparently using the smallest on disk format possible for the value you have saved. For the record there is another possibility for the values 0 and 1 where SQLite uses no storage at all, other than the type byte in the serial types array. Paul www.sandersonforensics.com skype: r3scue193 twitter

Re: [sqlite] How to Handle BigInt

SQLite integers are all 64 bit - I don't about postgress, so unless postgress allows integers bigger than 64 bit, and you use them, you should be OK with your table definitions above. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http

Re: [sqlite] How to convert a datetime column to a date?

how about select date(dttm) dt,max(i) from foo group by date(dttm) order by 1; Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from

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

cess it with sqlite> select rowid, id, data from test; 1| |row 1 2| |row 2 but if you want to use the rowid as the PK then you should probably use an INTEGER pk so it becomes an alias for the rowid in the first place. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sa

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

open on a persistent database. sqlite> create table test (id integer primary key autoincrement); sqlite> create table test2 (id int primary key autoincrement); Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY sqlite> Paul www.sandersonforensics.com skype: r3scue193 twitter: @sa

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

ite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a

Re: [sqlite] UPSERT

I would suggest using the PostgreSQL way: https://www.postgresql.org/docs/9.5/static/sql-insert.html INSERT INTO ... ON CONFLICT [()] DO UPDATE SET foo = ... , bar = ... ; This approach is really cool, because we can specify which key is more important and discard other conflicts as

Re: [sqlite] UPSERT

19 March 2018, 09:26:15, by "Rowan Worth" <row...@dug.com>: > On 16 March 2018 at 21:44, Paul <> de...@ukr.net> wrote: > > > A few years back I've been asking the same question. To be honest, there's > > no more > > efficient alternative, th

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

0 In all of the few dozens of schemas. Rowid logic is almost perfect. In extremely rare cases, when primary key id is exposed outside of database, a custom table that keeps last allocated id is used. 16 March 2018, 17:37:31, by "Richard Hipp" : This is a survey, the

Re: [sqlite] UPSERT

for a convenience of thousands (or millions?) of library users rather than leaving it up for them to figure out. Best regards, Paul 16 March 2018, 12:25:06, by "Robert M. Münch" <robert.mue...@saphirion.com>: > Hi, is there an elegant way to simulate a dynamic UPSERT in SQLite sinc

Re: [sqlite] sqlite3 related crash (SIGSEGV) in GNOME tracker-store

Dan Kennedy wrote: > To: SQLite mailing list , I didn't get your mail because I am not subscribed and was not CCed. > In frame 0 of thread 1, what do the following gdb commands say? > >print *pCache >print *pPage I can't provide that easily

Re: [sqlite] Can an SQL script be built from within sqlite?

That terminal app is still sandboxed. AFAIAA you essentially get access to the applictaios data folder and you can add, create, delete, etc files within it. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum

Re: [sqlite] sqlite3 related crash (SIGSEGV) in GNOME tracker-store

On Fri, 2017-12-22 at 20:09 -0500, Richard Hipp wrote: > Nevertheless, we will investigate from the SQLite side, > just in case. If you need any information from the core dump, please let me know which gdb commands to run. I still have one available from the latest crash and have marked it

Re: [sqlite] sqlite3 related crash (SIGSEGV) in GNOME tracker-store

On Fri, 2017-12-22 at 20:09 -0500, Richard Hipp wrote: > There are no known issues like this with any recent version of SQLite. > Usually these kinds of things end up being heap corruption in the > application. Nevertheless, we will investigate from the SQLite side, > just in case. I see,

[sqlite] sqlite3 related crash (SIGSEGV) in GNOME tracker-store

Hi all, I got a couple of random crashes GNOME's tracker-store daemon that appear to be related to sqlite3. I use 3.21.0-1 from Debian buster. https://bugzilla.gnome.org/show_bug.cgi?id=791243 The short backtraces are available here:

[sqlite] Compiling / building SQLite to include regex

Greetings. I understand that SQLite doesn't come natively with regex support, but that it can be added. My question is how to do so when I install. I'm building from source from . Is there a simple recipe for "make REGEX work after installation"?

Re: [sqlite] Atomic DELETE index optimisation?

of anything. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 19 December 2017

Re: [sqlite] same code produces a different database file on different computers

SQLite stores the verison number of the librrary in the database header. Different SQlite libraries on different computers would cause this error. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195

Re: [sqlite] Atomic DELETE index optimisation?

Not sure how relevant it might be, but what page size is the DB set to and what is the average size of a record? Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic

Re: [sqlite] What happens if an in memory database runs out of memory

current test client 3.18.0 insert into blobs values(zeroblob(2147483647)); fails wih string or blob too big. Not had time to investigate :( Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite

Re: [sqlite] DateTime to bigint

62135596800 is the difference in seconds between 1/1/1970 and 1/1/0001 and 1000 converts it to nanoseconds HTH Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic

Re: [sqlite] Many ML emails going to GMail's SPAM

What about some sort of poll. Mail lists might work but the additonal functionality offered by a forum (I am a member of many) makes them my choice. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php

Re: [sqlite] Energy consumption of SQLite queries

and processor throttling to manage temperature/power consumption etc. and you have a mammoth task. Good luck :) Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit

Re: [sqlite] Many ML emails going to GMail's SPAM

Coincidence! I have just been in my gmail folder marking a load of SQLite email as 'not spam' Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite

Re: [sqlite] Grouping and grabbing one item

ahh bugger - google didn't show the new answers had popped up. Pleased I came up with a working solution though :) Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit

Re: [sqlite] Grouping and grabbing one item

How about select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt > 0; 2017-09-27|2017-10-01|es-ES|170.0 2017-09-27|2017-10-01|fr-FR|185.0 2017-09-27|2017-10-01|it-IT|200.0 Paul www.sandersonforensics.com sk

[sqlite] one to one relationships

2 tables keyed alike are in a one to one relationship. IE every record of T1 will yield one and only one record in T2 PAUL ALAGNA pjala...@gmail.com <mailto:pjala...@gmail.com> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.or

Re: [sqlite] PRAGMA optimize; == no busy handler?

on *every* query, just to be sure. -Paul > >Don't do that > Mostly sure, but there's some cases SQLite will skip the busyhandler and > immediately return, but they're not permanently-busy conditions. We have > multiple threads across multiple processes using the database > &g

Re: [sqlite] Why is Sqlite mediatype not registered at iana

Hope to hear from you, Regards, Paul. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data types for date and time functions

for calculations but would just require a conversion for display. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully

[sqlite] Why is Sqlite mediatype not registered at iana

above issue for use cases) Groet Paul van Genuchten ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Packing integer primary key with field bits

be given if we know the full table schema including typical sizes for data in any fields/ Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email

Re: [sqlite] Problem with the new pointer-passing interface

To me it seems like that string is very tightly coupled with the actual pointer being bound. I think it's a good idea, in case you cannot make it a literal or static, to keep it with an object whose pointer you bind. Hi, I came across a problem with the new pointer-passing interface,

Re: [sqlite] Summarising (missing) values

That simple - I was over thinking it cheers Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional

[sqlite] Summarising (missing) values

What I would like is a single query that summarises the values that are present in (or missing from) a table. e.g. 334, 344 1077, 1297 1701, 1701 2385, 2386 2390, 2390 2393, 3336 Different approaches to this would be of interest Paul www.sandersonforensics.com skype: r3scue193 twitter: @san

Re: [sqlite] Sqlite problem with opening database

Could your 32 bit app be picking up a 64 bit dll. Could you rename the dll's and hard code the location into your library? May not work for your release code but may help you narrow down the issue. Paul On Wed, 5 Jul 2017 at 18:19, Simon Slavin <slav...@bigfraud.org> wrote: > >

Re: [sqlite] syntax error near AS

The SQLite syntax diagrams are my first point of call when looking at an error in my code like this. https://sqlite.org/lang_update.html "AS" and an alias are clearly not part of the statement. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1

Re: [sqlite] FOREING KEY constraint

pragma foreign_key_list(table_name) may help Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional

[sqlite] Document typo?

\ " pgsize INTEGER, /* Size of the page */" \ " schema TEXT HIDDEN /* Database schema being analyzed */" \ ");" Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

Re: [sqlite] sqlite-users Digest, Vol 114, Issue 23

at sqlite-users-ow...@mailinglists.sqlite.org When replying, please edit your Subject line so it is more specific than "Re: Contents of sqlite-users digest..." Today's Topics: 1. 3 fixes for 3.19.3 (Danny Couture) 2. Could not load file or assembly error (Paul J. McMillan, Sr.)

Re: [sqlite] Providing incrementing column to query

Actually I just need a unique number - but sorted in code now. Thank You Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work

Re: [sqlite] Providing incrementing column to query

Hmm thanks Clemens Have written an extension to do it - some of my tables are very big and feel that the extension might be a better route. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite

[sqlite] Providing incrementing column to query

I Have a number of queries to which I want to supply an incrementing column, some of these queries involve without rowid tables. I have no control over the design of the tables. So for a table defined as: CREATE TABLE (name text, age integer) with values Steve, 34 Eric, 27

[sqlite] Could not load file or assembly error

this error? Thanks Paul McMillan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] imposter tables

Thanks Richard I understand how it works, quite simple really, and knowing they are used by the RBU extension explains why. I am working on a book and I just wanted to understand how/where it could be used where a view, created on the same columns in an index, couldn't be. Paul

Re: [sqlite] imposter tables

Can you create an imposter table on a view. A view has no associated b-tree so I would think not! Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit

[sqlite] imposter tables

you exactly what is in the index, where the view is my interpretation of the SQL needed to show what is in the index. Is this the main benefit? or am I missing something? Are there instances where a view created as I have done above cannot simulate an imposter table? Cheers Paul

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

Ahh - I always let SQLite decide what index to use as I assume that it knows best. I have never used "indexed by" to force the use of a specific index - I see the issue with backward compatibility now. Thanks Richard Paul www.sandersonforensics.com skype: r3scue193 twitter: @sanderson

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

the autoindex to start with. Just a thought Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional

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

index is built. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 19 May

[sqlite] auntondex with unique and integer primary key

(3); The table is created and populated as expected, but an sqlite_autoindex_test_1 is also created with content that mirrors exactly the rowid/id. Is the autoindex redundant and is this an opportunity for optimisation? Using 3.18.0 Paul www.sandersonforensics.com skype: r3scue193 twitter

Re: [sqlite] NOT NULL integer primary key

Ahh being dull and in a hurry thanks Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo

[sqlite] NOT NULL integer primary key

constraint be obeyed? Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence

Re: [sqlite] SQLite in memory

If by any chance you have access to Linux or alike, you can just mount a ramfs and move database file over there. It is a usual file system that lives in RAM. This will 100% guarantee you that no disk access will be made by SQLite. 18 May 2017, 08:18:47, by "Gabriele Lanaro"

Re: [sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

On Mon, May 8, 2017 at 8:20 PM, Scott Robison <sc...@casaderobison.com> wrote: > On Mon, May 8, 2017 at 11:40 AM, Paul van Helden <p...@planetgis.co.za> > wrote: > > Hi, > > > > I use a lot of indexes on fields that typically contain lots of NULLs, so > >

[sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

a partial index. Is this an optimization opportunity? I understand that other kinds of partial indexes might exclude possible Min or Max values, but a NOT NULL index would be fine for mins, maxes and most other things? Regards, Paul. ___ sqlite-users ma

[sqlite] Malformed databases and multithreading

Main question/comment: - On the "how to corrupt" page ( http://sqlite.org/howtocorrupt.html ) i do not see any mention of using SQLite in an incorrect way with respect to thread safety. Is there really no way that, for example, using the same connection on multiple threads at the same time

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

another oops I see Ryan pretty much posted the same as me 5 minutes earlier - I'll go back to bed :) Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

JulianDay(SubStr(Replace(dateplay.vi, '/', '-'), 1, 10)) < 365 THEN 'Under 1 year' END AS category FROM dateplay there may well be a neater way of doing it :) Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

Oops hit send too quickly the replace function replaces / with - in your date string to make the ISO 8601 and substr just makes sure we use the date portion only. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

dateplay Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 12 April 2017 at 13:13

Re: [sqlite] column alignment of views and tables;

oops you need to select the string length from the column width select substring('', 1, 16 - length(printf("%2.f", price))) || printf("%2.f", price) from prices Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44

Re: [sqlite] column alignment of views and tables;

Just shooting out so no time to test. But could you try something like select substring('', 1, length(printf("%2.f", price))) || printf("%2.f", price) from prices Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44

Re: [sqlite] Use of sqlite3_bind_int() vs sqlite3_bind_int64()

Thank you very much for replying so quickly! > On 3/28/17, Paul wrote: > > According to datatypes page https://sqlite.org/datatype3.html SQLite choses > > width of the integer automatically. Does it mean that if I let's say want to > > bind a number 5 in the query that

  1   2   3   4   5   6   7   8   >