Re: [sqlite] ordinary CTE containing sum()

On 2/8/2015 10:23 PM, James K. Lowden wrote: I have a couple of efficiency questions for those who know: 1. Is the left-join on a CTE apt to be more effecient than the version that uses a correlated subquery in the SELECT clause? I'm guessing it matters in some DBs but from testing it

[sqlite] SQL Help

I used to make Pivot-table-like reports in (what I thought was) the normal way: SELECT ProductID, sum(QtyMade) AS TotQty, (sum(CASE Mth WHEN 1 THEN QtyMade ELSE 0 END)) AS 'Jan Qty' ,(sum(CASE Mth WHEN 2 THEN QtyMade ELSE 0 END)) AS 'Feb Qty' ,(sum(CASE Mth WHEN 3 THEN

Re: [sqlite] unreached code in sqlite3.c?

On 2/12/2015 4:58 PM, Doug Currie wrote: Well, if TERM_VNULL is 0, then the code is truly unreachable, so I wouldn't call it a compiler bug. e The point is that TERM_VNULL is controlled by a compiler pre-processor switch and is only unreachable for specific values of that switch - hence if

Re: [sqlite] unreached code in sqlite3.c?

On 2/12/2015 8:50 PM, Doug Currie wrote: On Thu, Feb 12, 2015 at 1:35 PM, R.Smith <rsm...@rsweb.co.za> wrote: Now one could argue the warning should not be issued for it, or some warnings are fine as information. Personally I prefer zero unneeded warnings/clutter but that's just my ped

Re: [sqlite] sqlite journal file question

I don't think it can be done, and if it could be done, it would not be wise. The journal is owned and specific to a connection. a hot Journal for connection A on DB 1 cannot ever be used to roll back or affect in any way connection B on DB 2. However, when you say the "standby is syncing", I

Re: [sqlite] sqlite journal file question

This was my initial reading too Paul, but reading the OP post again it could be either - who knows what is meant by "syncing". I also think this is the main point for the OP - If by syncing he really means "copying the file" then Richard's advice, else if he means "adding via normal DB

Re: [sqlite] unreached code in sqlite3.c?

On 2/13/2015 9:57 AM, Dominique Devienne wrote: Warnings are always a tradeoff between pointing out what could be mistakes/oversights versus senseless noise. Most times I get the unreachable warning in my code is when I'm actively coding, experimenting, moving things around, then when I'm

[sqlite] Two different Order By in one statement

On 2015-04-01 10:29 AM, Bart Smissaert wrote: > OK, let me give the simplest example possible. > > Table with 3 integer fields, A, B and C > > AB C > > 1 1 2 > 2 1 2 > 1 2 1 > 2 2 1 > > This needs to be sorted on column A asc >

[sqlite] Performance issue

On 2015-04-01 10:20 AM, Jeff Roux wrote: > Here is the complete schema: > > sqlite> .schema > CREATE TABLE application (id INTEGER CONSTRAINT applpk PRIMARY KEY, > shortname VARCHAR(64), name VARCHAR(256)); > > CREATE TABLE flows (idx INTEGER PRIMARY KEY, ipLan INTEGER, ipWan INTEGER, > flags

[sqlite] insert in sqlite (returning value for inserted row)

On 2015-04-01 07:10 PM, Kumar Suraj wrote: > Hi Richard.. this wont work for me due to following reason. > > If a separate thread performs a new INSERT > on the same database connection > while the sqlite3_last_insert_rowid() >

[sqlite] Interrupt SQL query in SHELL

On 2015-04-01 08:29 PM, Gert Van Assche wrote: > Hi all, > > When running SQLite3 from command line, is there a way to interrupt the CMD > file when a condition is true? > > I'm importing a lot of plain text files that should all count the same > number of lines, so I want to compare the record

[sqlite] Did A Recent Windows & Update Affect SQLite???

On 2015-04-01 09:17 PM, Mark Romero wrote: > Hello everyone and thanks for your replies (I am new to using a mailing > list so I hope that everyone gets this response). Everyone did. As to the question - as Simon mentioned, we are not aware - but I am not a Lightroom or Win7 user, so I phoned

[sqlite] Interrupt SQL query in SHELL

On 2015-04-01 09:09 PM, Gert Van Assche wrote: > Dr. Hipp, thanks for the tip. I put > .bail on > in the script. > > > Ryan, I think I don't know how to trigger the bail out from within a SELECT > statement. > I tried > SELECT CASE (select count(*) from T1) WHEN (select count(*) from

[sqlite] Version 3.8.9 is in testing

Some small legibility / continuity change suggestions to the new index_xinfo pragma doc: Original: *PRAGMA */database./*index_xinfo(*/index-name/*);* This pragma returns information about every column in an index. Unlike this index_info pragma

[sqlite] Version 3.8.9 is in testing

Ugh, the text-only formatter messed that up, let's try again: > Suggested (changes in *...*): > > PRAGMA database.index_xinfo(index-name); > > This pragma returns information about every column in an index. Unlike > *the* index_info pragma, this pragma returns information about *all > columns

[sqlite] Performance issue

On 2015-04-08 11:35 AM, Dominique Devienne wrote: > On Wed, Apr 8, 2015 at 11:16 AM, Jeff Roux wrote: > >> # For mysql, I use: >> /etc/init.d/mysql stop; /etc/init.d/mysql start; \ >> time echo 'SELECT SUM(nbBytesDecompOut+nbBytesCompIn) as vol, portLan \ >> as item FROM flows WHERE

[sqlite] Performance issue

On 2015-04-08 01:57 PM, Dominique Devienne wrote: >> No Dominique, it's not that - >> > Perhaps. But that still doesn't get to my point. With a LIMIT clause, in > such a GROUP BY ORDER BY returning a large result set, would SQLite: > 1) sort the whole result-set and then keep only the first

[sqlite] Prevent database file from being overwritten by other processes

On 2015-04-08 04:18 PM, Fabian Pr?bstl wrote: > Hi there! > > Currently, we are using SQLite as our application file format for a Windows > 7/C#/System.Data.SQLite based desktop application. We only allow one instance > to open the file by running "set locking_mode=EXCLUSIVE;BEGIN >

[sqlite] Prevent database file from being overwritten by other processes

On 2015-04-08 05:38 PM, Dan Kennedy wrote: > On 04/08/2015 09:51 PM, R.Smith wrote: >> >> >> On 2015-04-08 04:18 PM, Fabian Pr?bstl wrote: >>> Hi there! >>> >>> Currently, we are using SQLite as our application file format for a >>> Wind

[sqlite] Prevent database file from being overwritten by other processes

On 2015-04-08 06:00 PM, Dan Kennedy wrote: > On 04/08/2015 10:52 PM, R.Smith wrote: >> >> >> On 2015-04-08 05:38 PM, Dan Kennedy wrote: >>> On 04/08/2015 09:51 PM, R.Smith wrote: >>>> >>>> >>>> On 2015-04-08 04:18 PM, Fabian

[sqlite] sqlite 3.8.2 foreign key issue

On 2015-04-09 12:00 AM, Gustav Melno wrote: > The example below is s subset of my ical storage database which has > problems with foreign keys. Although I tried a lot I couldn't figure > out why the foreign key doesn't work. I took the example from the > documentation and compared to my two

[sqlite] sqlite 3.8.2 foreign key issue

You are always welcome. SQLite is not strongly typed - you are very welcome to store an integer in a text field (in fact it results in only using the data associated with the smallest integer internal type that can hold the value, so a clear advantage in the embedded world). You are also

[sqlite] possible Bug

On 2015-04-09 07:57 PM, Simon Slavin wrote: > On 9 Apr 2015, at 6:04pm, Gustav Melno wrote: > >> Thanks for the help. Adding a trailing underscore helped also. I'm still >> wondering why insertion worked at all because defining oid as column name >> with the type VARCHAR should result in an

[sqlite] possible Bug

This is the minimal SQL for a test case needed to reproduce the oid - foreign key bug: (By changing only 1 character renaming oid to xid in the first line, the SQL works as intended). CREATE TABLE tParent(id INTEGER PRIMARY KEY, c1 TEXT, oid INTEGER); CREATE TABLE tChild( id INTEGER PRIMARY

[sqlite] Request: Metadata about C API constants and functions

On 2015-04-10 09:48 AM, Zsb?n Ambrus wrote: > On 4/10/15, Dominique Devienne wrote: >> But that's build time. There is no way (i.e. pragma) to list registered SQL >> functions at runtime. >> >> This is something that I've asked for before, and I'm having trouble >> understanding why nobody

[sqlite] Request: Metadata about C API constants and functions

On 2015-04-10 02:09 PM, Simon Slavin wrote: > On 10 Apr 2015, at 12:53pm, R.Smith wrote: > >> It doesn't matter, C API calls or pragma, whatever works best as long as >> there is /some/ way to get to that info. Usually though, most C API calls >> valuable to end u

[sqlite] Request: Metadata about C API constants and functions

On 2015-04-10 04:11 PM, Richard Hipp wrote: > https://www.sqlite.org/toc.db Thank you Richard! Will this link always have the latest DB? This db format is preferred, but in case anyone is still interested in the webby formats, here is the DB in XML: http://sqlc.rifin.co.za/toc.xml and JSON:

[sqlite] Select query becomes distinct on where matches unique with null value

Yep, it's a Bug since at least 3.8.8. Minimal Test-case to reproduce: create table t( c1 integer primary key, c2 integer, c3 integer, UNIQUE (c2, c3) ); insert into t values( 1,null,'a'); insert into t values( 2,null,'a'); insert into t values( 3,'xxx','a'); select * from t; --

[sqlite] Select query becomes distinct on where matches unique with null value

On 2015-04-11 06:12 PM, Simon Slavin wrote: > On 11 Apr 2015, at 4:13pm, Keith Medcalf wrote: > >> Interestingly if you run analyze, it works properly ... > Oh my. I don't like the idea that ANALYZE changes the result set. > > Simon. Yes, that would be worrisome... but not to worry, the bug

[sqlite] sqlite3 (or sqlite4) performance on NFS

On 2015-04-11 03:30 PM, Peng Yu wrote: > Hi, > > I know that sqlite3 is not recommended to be used on NFS. But I only > use sqlite3 for read only (the only time that I write is to load the > initial data to the data base). With this restriction, NFS should be > fine as the storage since no file

[sqlite] SELECT * with ORDER BY returning duplicate rows.

To add: The second query Seg-faults in the sqlite 3.8.8 dll near the end. The other two runs to end. It seems to be a NULL reference, the error ref: -- Last Script Error: Exception Executing Script: Access violation at address 61C11C1E in module 'sqlite3.dll'. Read of address

[sqlite] Performance issue

On 2015-04-13 09:49 AM, Jeff Roux wrote: > Hi everyone, > > I have copied the original database on my personnal website in a tbz > archive here : > > http://nice-waterpolo.com/misc/db/ > > There is only one index on timestamp,protocol. Hi Jeff, I am not sure what is wrong your side, but the

[sqlite] SQLite to SQL Server

On 2015-04-13 11:38 PM, Drago, William @ CSG - NARDA-MITEQ wrote: > All, > > Is there a convenient way to transfer data from SQLite to SQL Server? I'm > presently writing code to do a row by row transfer of all my tables, but the > thought crossed my mind that maybe there's a better way.

[sqlite] Destroy all evidence of a database

On 2015-04-22 05:56 PM, Simon Slavin wrote: > On 22 Apr 2015, at 4:46pm, Michael Stephenson > wrote: > >> Simon, if the data in the database is sensitive, could you encrypt the >> database (ala something like https://www.zetetic.net/sqlcipher/)? > Unfortunately, this doesn't help. I'm not

[sqlite] Destroy all evidence of a database

Actually, I assumed SQLite made the duplicates / alternates, it may well have been the anti-virus doing it. I doubt anything else had a motive though. On 2015-04-22 06:20 PM, R.Smith wrote: > > > On 2015-04-22 05:56 PM, Simon Slavin wrote: >> On 22 Apr 2015, at 4:46pm, Mic

[sqlite] CSV excel import

On 2015-08-01 05:42 PM, Jean-Christophe Deschamps wrote: > > At 16:36 01/08/2015, Igor wrote: > >> There are many real problems with CSV - no need to make up imaginary >> ones. > `--- > Indeed and I'm surprised noone mentionned this from the very start of > this thread: Nulls. There is no

[sqlite] datetime result help

On 2015-07-31 03:37 PM, jose isaias cabrera wrote: > > How much I owe you? :-) The holidays was the next question. ;P > Thanks. This will help me understand CTE much deeper/better. Thanks. > The help is a pleasure, but those CTEs were hacked together to solve the date-time conundrum you've

[sqlite] CSV excel import

On 2015-08-01 09:28 PM, Igor Tandetnik wrote: > On 8/1/2015 12:38 PM, R.Smith wrote: >> if I have this csv line, what values must the parser end up with?: >> >> 1, "2", "3" 4, 5 "6", 7 > > This is not a valid line of CSV, at least not as

[sqlite] how to detect when a table is modified ?

Best way to do so is using the Authorizer - read more here: https://sqlite.org/c3ref/set_authorizer.html On 2015-08-08 01:23 AM, Nicolas J?ger wrote: > Hi, > I'm writing a program using sqlite3 and gtk to screen a database, there > is a way to get a signal from sqlite3 when something is

[sqlite] Problem with SQLite in C++. DB is BUSY (Multithread)

On 2015-08-10 11:49 PM, ??? ??? wrote: > When I set timeout to 12 ms (2 minutes). It starts work. But as I > understand from reading SQLite C interface, when DB in WAL journal mode it > should work without busytimeout. > > Can it work without busy timeout? It can work without

[sqlite] Database sybchronisation

To Attach a second Database is fairly straightforward and shouldn't fail unless there is a physical problem with either of the files. The Attach command example is like this (on a WinX machine): ATTACH DATABASE 'C:\Documents\OtherDatabase.db' AS "DB2"; Execute that as a standard SQL statement.

[sqlite] Database sybchronisation

lem, simply put that UpdateWolfpro DB in the Documents folder or AppData folder and adjust the code to suit. The correct place to put data files in Windows will be in the "c:\Users\(username)\AppData\Roaming\(YourAppName)\" (more or less) but that is another discussion. Let us know if th

[sqlite] journal files not always removed

Checked this on WIndows 7 & 8, works as expected. This means either Windows 10 has some glitch or the access level to the folder is not correct, files may not be deleted by that process or some antivirus is checking the file while SQLite is trying to delete it, or you are not closing the DB

[sqlite] journal files not always removed

Also, make sure that folder is not a shared resource or inside a shared resource that is visible from any other network node or machine. This piece, describing the same symptom for Internet Explorer temporary files, may help: https://support.microsoft.com/en-us/kb/814782 What happens when you

[sqlite] Enhance the SELECT statement?

On 2015-08-17 05:44 PM, John McKown wrote: > I use both SQLite3 and PostgreSQL. One thing that would be really useful > for me in SQLite3 which is in PostgreSQL is the INTO phrase. The PostgreSQL > documentation this is here: > http://www.postgresql.org/docs/9.4/interactive/sql-selectinto.html

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

On 2015-08-17 06:08 PM, Olivier Barthelemy wrote: > OK, > I already had removed my constraint as it is, since it is obviously invalid > > So for me : > Am i right to leave a constraint, say CHECK (varname=0 OR varname=1), to > avoid other integer values, or is the constraint useless? (i guess

[sqlite] Enhance the SELECT statement?

CORRECTION: It seems one of the two options I've mentioned earlier, namely the CREATE TABLE AS SELECT... does not actually work on the back of a WITH clause. The other option still do, but this request has more appeal now. On 2015-08-17 07:24 PM, R.Smith wrote: > > > On 2015-08-17

[sqlite] Enhance the SELECT statement?

On 2015-08-17 08:15 PM, Petite Abeille wrote: >> On Aug 17, 2015, at 8:08 PM, R.Smith wrote: >> >> CORRECTION: It seems one of the two options I've mentioned earlier, namely >> the CREATE TABLE AS SELECT... does not actually work on the back of a WITH >> cla

[sqlite] Getting row number in a sorted list.

On 2015-08-19 02:40 PM, Simon Slavin wrote: > > SELECT rowid FROM NameTable > WHERE name BETWEEN 'P' AND 'P' > ORDER BY name > LIMIT 1 > > This will execute faster if you have an index on 'name' in NameTable. > > [Yes I know 'P' is lazy. Until you find someone with

[sqlite] Getting row number in a sorted list.

On 2015-08-19 03:02 PM, John McKown wrote: > On Wed, Aug 19, 2015 at 7:40 AM, Simon Slavin wrote: > >> 1|ABCD >> 2|CDE >> 4|AXN >> 5|AXN2 >> 6|PQRS2 >> sqlite> select rowid from NameTable where Name between 'P' and 'PZZZ' >> limit 1; >> 6 >> >> >> Hum, that probably isn't what the OP

[sqlite] Getting row number in a sorted list.

> > SELECT count(sub.Name) + 1 AS Rank, a.Name > FROM NameTable AS a LEFT OUTER JOIN NameTable AS sub ON sub.Name < > a.Name > WHERE a.Name LIKE 'P%' > GROUP BY a.Name > ORDER BY a.Name > LIMIT 1 > > > -- Rank | Name > -- | -- > -- 4 | PQRS I

[sqlite] Getting row number in a sorted list.

On 2015-08-20 07:44 AM, Anthrathodiyil, Sabeel (S.) wrote: > Hi, > I think there is ambiguity in the example I provided, I wouldn't need a > ranking in my case as I need the offset (think row number is misleading) of > the first record in the sorted list. > > Here is my use case. > Find the

[sqlite] When sqlite3_close() returns SQL_BUSY

Hi Jeff, On 2015-08-21 07:30 AM, Jeff M wrote: > Sometimes my iOS app creates an unreasonable number of prepared statements > (perhaps 1,000, an app bug that I'm fixing). These prepared statements are > later finalized just prior to doing sqlite3_close(), which sometimes returns > SQL_BUSY.

[sqlite] design problem involving trigger

On 2015-08-21 04:47 AM, Will Parsons wrote: > I'm working on a program that involves a catalogue of books. Part of > the database schema looks like this: > > create table Books(id integer primary key, > title text collate nocase not null, > author

[sqlite] There is any reason to sqlite not expand "*" in function calls ?

On 2015-08-22 11:42 AM, sqlite-mail wrote: > Hello ! > > I'm testing the new json functions and when I tried this: > > select json_array(*) as json from one_table; > > I've got : > > [] > > [] > > .. > > Then I tried with some custom functions accepting variable number of > parameters and

[sqlite] design problem involving trigger

On 2015-08-21 11:23 PM, Will Parsons wrote: > On 21 Aug 2015, R.Smith wrote: >> >> On 2015-08-21 04:47 AM, Will Parsons wrote: >>> I'm working on a program that involves a catalogue of books. Part of >>> the database schema looks like this: >>> >

[sqlite] DbFunctions.TruncateTime

On 2015-08-22 10:57 PM, Steffen Mangold wrote: >> how can I trunc time in EntityFramework? >> >> I tried it this way: >> >> model.Datas >> .GroupBy(d => >> DbFunctions.TruncateTime(d.TimeStamp)) >> .Select(d => d.Key.Value) >>

[sqlite] : it seems to be only orber by and group

This will work great - just a correction, there should be a comma after "columnB" in the order by clause, else it might not parse, so the revised is: SELECT * FROM table ORDER BY columnA, columnB, CASE WHEN columnC = 1 THEN 0 ELSE 1 END; On 2015-08-22 10:47 PM,

[sqlite] DbFunctions.TruncateTime

On 2015-08-23 12:16 AM, Steffen Mangold wrote: > Hi Ryan, > > I get your point. :) > It seems the I was misunderstanding this help mailing list. I thought it's > also support for 'System.Data.SQLite'. > > In the way 'System.Data.SQLite' is an ADO.NET provider for SQLite and also > give support

[sqlite] design problem involving trigger

On 2015-08-23 03:32 AM, Barry Smith wrote: > Could this not be achieved by two indexes: one partial and one complete? > > CREATE UNIQUE INDEX idx_books1 ON Books(title, author); > > CREATE UNIQUE INDEX idx_books2 ON Books(title) WHERE author ISNULL; > > To save space and (maybe) time, you could

[sqlite] SQlite database access over wireless network

On 2015-08-24 06:43 PM, Mike McWhinney wrote: > Hello, > I am trying to troubleshoot a problem that I'm having with a program > usingSQLite over a wireless network. I know that wireless networks can > havetheir own sets of issues that cause database access problems. > What is happening is

[sqlite] PRAGMA cache_size and ATTACHED DBs

The cache size pragma dictates to (and affects) the connection, not the DB. So yes. On 2015-08-24 07:30 PM, jose i cabrera wrote: > > Greetings! > > When connecting to a DB, and setting a PRAGMA cache_size, will the > attached DB also respond/behave the same way/size set by the original >

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

On 2015-08-25 08:37 PM, Richard Hipp wrote: > On 8/25/15, Simon Slavin wrote: >> "If there is no AS clause then the name of the column is unspecified and may >> change from one release of SQLite to the next." >> > Heed this warning!!! > > And yet, there are countless tens of thousands of

[sqlite] WITH () AS (SELECT ) help

On 2015-08-26 05:12 AM, jose isaias cabrera wrote: > Greetings, perhaps this is not even possible... > > But I am trying to do something like this, > > WITH EmailData (name,contact,dstamp) AS > ( >SELECT > 'last, first', > 'first.last at xerox.com', > '2015-08-25 11:11:11' > ) > UPDATE

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

On 2015-08-27 03:03 AM, James K. Lowden wrote: > On Wed, 26 Aug 2015 13:39:09 +0100 > Simon Slavin wrote: > >> On 26 Aug 2015, at 11:27am, tonyp at acm.org wrote: >> >>> Plus, it apparently recognizes if the random() expression in the >>> ORDER BY is the same as the SELECT one and again sort

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

On 2015-08-27 03:29 PM, Simon Slavin wrote: > > Sure. I chose to use an alias just to emphasise how wrong the result looked. > However, I have seen code written by teams where the person writing the > query has no real idea whether they're querying a TABLE, a VIEW, or a virtual > table.

[sqlite] why I don't get an error ?

On 2015-08-27 04:06 PM, Nicolas J?ger wrote: > Hi Darko, Igor and others. > >so the only reason to define datatype in sqlite is for the size on >the disk ? > >so why not just only using `BLOB` (excepted for `INTEGER PRIMARY >KEY`) ? > >being less persmissive wouldn't make

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

On 2015-08-27 04:50 PM, Scott Hess wrote: > I keep thinking I remember a thread from years ago where a lot of this was > hashed out, but I cannot find it. > //// > There is already some precedent for this, because ORDER BY RANDOM() must > internally be holding the random values used fixed

[sqlite] Insertion into the large DB drastically slows down at some point

On 2015-08-28 04:15 AM, Yuri wrote: > I build a fairly large DB, with major tables having 800k..5M rows, > with several relationships between tables. > > At ~30% into the insertion process it slows down rapidly. Records > #171k..172k are still fast, and records #172k...173k are already ~10 >

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

On 2015-08-28 01:17 PM, Yahoo! Mail wrote: > Obviously you did not get my issue; something is wrong and your timer > suggestion indicates this. During the execution of each command, I > would monitor it with *watch "du test.db*"*. The journal size would go > mad even surpassing the database's

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

On 2015-08-28 03:09 PM, Yahoo! Mail wrote: > Where did you see the vacuum happening inside the transaction? It's > just right before begin...anyway. It seems I'm unable to make clear > the actual "issue" of mine, but anyhow it's not a bit deal. I just > wanted to report what I have noticed,

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

On 2015-08-28 04:45 PM, Rousselot, Richard A wrote: > I have noticed that SQLite Query Browser is running slower than other IDEs, > including SQLitespeed, for some reason. Even when each IDE is set to using > similar versions of the SQLite3.dll. We had a recursive query in SQB take 6 > min,

[sqlite] combine SELECTs

On 2015-08-30 09:02 PM, Lev wrote: > Okay, it is more like an SQL question... > > So I have say three different SELECT on one table. I'd like to combine the > three to have data in the result, only if one particular field is the same for > each query. Might I suggest 2 approaches: (I'm just

[sqlite] Fwd: Re: sqlite journal file question

On 2/14/2015 12:19 AM, Mayank Kumar (mayankum) wrote: > Thanks all for the responses. Just want to clarify the scenario one more > time:- > > -by syncing, I mean taking the modifications on the active machine and > sending over wire to another machine(in some proprietary format) , where >

[sqlite] test for bounce detection

I can confirm the bounces happen for the ".mailinglists" email address which is now automatically added to the "Reply-To" address. I hit the reply-to-all button and then remove the .mailinglists address to fix it, but it is rather cumbersome, if the powers that control such things could kindly

[sqlite] test for bounce detection

On 2/14/2015 10:18 AM, Ma?l Nison wrote: > As a side, it's the first time that I received a mail from this mailing > list without it being automatically flagged as spam. Well, technically this is spam... but it's the good kind :) Thank you kindly Mike for the prompt repair and keeping things

[sqlite] GROUP BY with self join

On 2/14/2015 6:32 PM, Bart Smissaert wrote: > Having problems with the following SQL: > > 2 tables, trying to count occurrence of field 2 and field 3 of table 1 > in field 1 (only field) of table 2. > > Table 1 called ITEM with fields: NAME, DEFINITION1, DEFINITION2 > all text fields. Values in

[sqlite] GROUP BY with self join

On 2/14/2015 6:55 PM, R.Smith wrote: > > On 2/14/2015 6:32 PM, Bart Smissaert wrote: >> Having problems with the following SQL: >> >> 2 tables, trying to count occurrence of field 2 and field 3 of table 1 >> in field 1 (only field) of table 2. ... > I haven't te

[sqlite] Backup API and WAL

On 2015-02-23 04:54 AM, Donald Shepherd wrote: > On Mon Feb 23 2015 at 1:41:31 PM Simon Slavin wrote: > >> On 22 Feb 2015, at 11:15pm, Donald Shepherd >> wrote: >> >>> If I use the backup API to create a copy of an SQLite database that uses >>> Write-Ahead Logging, will the resulting copy

[sqlite] Sqlite subqueries

There's been many discussions on this topic, you can search for it, but I will try to recap in short: SQL does not work like this, not in SQLite or any other SQL engine may an entity construct be referenced by an uncontrolled data value. Of course it is easy to get around this in code whereby

[sqlite] Err

There is nothing unusual about the output - it looks exactly as expected (without knowing what is inside the tables referenced). As Igor already asked: Is there anything specific you find unusual? Is it the stats block that confuses you, but which you specifically requested with the ".stats

[sqlite] Characters corrupt after importing a CSV file

NULLNULL uk???1NULLNULLNULL ja???1NULLNULLNULL In the command-line facility I got all weird characters, the usual kind of thing when you try to represent Unicode / UTF8 in ANSI or ASCII text. C:\Users\R.Smith\Desktop>sqli

[sqlite] Characters corrupt after importing a CSV file

Thanks Adam, Could you kindly post an example file somewhere (the kind which you import) for us to test with? (The list does not allow attachments). Also, could you try a later version of SQLite so that we could see if this problem still exists on your platform. Updated versions obtainable

[sqlite] Characters corrupt after importing a CSV file

While it is a presentation issue in the end, it still is an issue, so thank you for happening upon it and bringing it to our attention, and no need to apologize for lack of investigation. I think it is possibly the command line utility falling into the character-length trap which Hick

[sqlite] List duplication

Hi all, Just a quick question, I've been getting duplicated mails via the forum. It seems very random, roughly 1 in 4 mails I receive in duplicate. Has anyone else been getting duplicate mails? It's not a biggie, I just delete the duplicates. Also, it might be my email settings or setup being

[sqlite] List duplication

On 2015-02-27 10:09 PM, Peter Aronson wrote: > I've seen it too. All of the duplicate messages appear to have been sent to > both sqlite-dev at mailinglists.sqlite.org and sqlite-dev at sqlite.org or to > both sqlite-usersmailinglists.sqlite.org and sqlite-users at sqlite.org. Ah, I think

[sqlite] List duplication

On 2015-02-28 12:47 AM, Simon Slavin wrote: > On 27 Feb 2015, at 10:04pm, R.Smith wrote: > >> Ah, I think this is the answer probably. Maybe people hitting the "reply to >> all" or such and it copies across lists. Simon is possibly not subscribed >> to both,

[sqlite] List duplication

On 2015-02-28 05:02 AM, Darren Duncan wrote: > I'm seeing a lot of message duplication too, but the ones I see are > due to someone putting the list address twice as a recipient. > > For example a bunch of the "PhD student" messages say "To" > sqlite-users at mailinglists.sqlite.org plus "Cc"

[sqlite] Reader.GetBytes() - when is a byte not a byte?

That exception is only thrown if the source column is not a BLOB (as far as I can tell). You specify the column Type (or type affinity) as BLOB but then you store a non-BLOB TEXT value in it ('"blue" "red" "orange" "yellow"' <-- Not a BLOB) SQLite will store this as TEXT, not BLOB. A BLOB

[sqlite] Reader.GetBytes() - when is a byte not a byte?

On 2015-07-02 03:24 PM, R.Smith wrote: > That exception is only thrown if the source column is not a BLOB (as > far as I can tell). > > You specify the column Type (or type affinity) as BLOB but then you > store a non-BLOB TEXT value in it ('"blue" "red" &

[sqlite] Slow real world performance - Any suggestions please (warning long)

On 2015-07-03 12:16 AM, Rob Willett wrote: > To try and isolate the problem away from the Perl program, we then generated > the 5,000 SQL calls that would be made by the Perl program into a file. e.g > > select Id,Calculation,Peak,Red,Amber,Green from RAG where text = 'traffic is > moving well

[sqlite] Adding PRAGMA=UTF-8 makes INSERT slower?

Hayden, I've been following your questions (to try and help) but I have to comment - the questions are all exceedingly vague, it's really hard to help with one hand tied behind our collective backs. This is not a complaint - I'm very sure you try to keep it short out of kindness and

[sqlite] Doc page revision request

I have to agree with the web filter being at fault. I am ambivalent to whether or not the word is innocuous or whether it can be misconstrued or even if, to some people, it is truly offensive. What bothers me more is the idea that the rest of the World all needs to update their documentation

[sqlite] Doc page revision request

On 2015-07-22 12:09 AM, Keith Medcalf wrote: > It is not the word that is offensive (that is illogical and plainly > impossible). It is the thing that is offended that is the problem -- the > gutter mind and carnal tendencies of the receiver -- I am sure there are > psychiatric descriptions

[sqlite] Possible substr() optimization?

On 2015-07-23 04:56 PM, Igor Tandetnik wrote: > On 7/23/2015 10:47 AM, Bernardo Sulzbach wrote: >>> select * from tbl1 where substr(col1,1,1) in ('A','a') and col1 like >>> 'a_c%'; >> >> Just adding to Igor's answer: >> col1 between 'a' and 'b' or col1 between 'A' and 'B' > > That's not quite

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

On 2015-07-27 04:29 PM, rotaiv wrote: >> ?WOW! That is an amazing difference. Makes me glad that I'm OCD about >> staying relatively current. And please ignore my previous post. I somehow >> managed to not register that you would doing a LEFT join, an equi-join.? >> > I am also OCD about

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

On 2015-07-27 05:48 PM, Marc L. Allen wrote: > When would that specific LEFT JOIN ever do anything except return NULLs for > the right table? It only accepts rows from work where fpath is null, and > only joins those rows where fpath = home.fpath. Since fpath must be null, > home.fpath

[sqlite] index for OR clause

On 2015-07-27 08:09 PM, Simon Slavin wrote: > On 27 Jul 2015, at 6:58pm, Sylvain Pointeau > wrote: > >> create table TEST ( >> a TEXT NOT NULL, >> a2 TEXT NOT NULL, >> b TEXT NOT NULL, >> c TEXT NOT NULL >> ); >> >> create index IDX_TEST_1 on TEST(a,a2,b,c); >> >> insert into TEST(a,a2,b,c)

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

On 2015-07-28 09:25 PM, rotaiv wrote: > Not quite. The older version brought it down to 8 seconds (as compared to > 5 seconds) but still a whole lot better than 40+ minutes. > > It is very interesting to see an index can make such a remarkable > difference in that particular scenario. This is

[sqlite] datetime result help

On 2015-07-29 06:34 PM, jose isaias cabrera wrote: > Greetings! > > I am trying to calculate a date using sqlite date and time functions. > Perhaps, one of you GURUs can help this poor soul. I have been trying to > figure it out, but I know I am lack the understanding. I read the >

  1   2   3   >