Re: [sqlite] sqlite bug? .mode insert does not quote identifiers

On 4/6/18, jon baldry wrote: > I have identifiers that unfortunately have a hyphen within them. > > I want to output the data from the tables with those identifiers using > .mode insert and .out foo.sql for re-loading. > > I have worked around this in PHP to modify the output,

Re: [sqlite] SQLite equivalent of SQL Over(Partition)

On 6 Apr 2018, at 8:58pm, R Smith wrote: > my guess is Mr. Thomas inherited it from someone else who did not fully know > what they wanted to achieve, then googled a solution and found a hit on an > old stackoverflow question that was sort-of like what they wanted, but not

Re: [sqlite] SQLite equivalent of SQL Over(Partition)

On 2018/04/06 9:15 PM, Don V Nielsen wrote: That seems like an odd application of OVER (Partition by). Is there some performance reason one would want to do DISTINCT OVER (PARTITION BY) instead of a simple GROUP BY Sites.Customer, Sites.Digit, Count()? Agreed, in fact half that query seems

[sqlite] sqlite bug? .mode insert does not quote identifiers

I have identifiers that unfortunately have a hyphen within them. I want to output the data from the tables with those identifiers using .mode insert and .out foo.sql for re-loading. I can't use .dump easily as i want to modify the tables before re-loading the data, so .mode insert seemed my

Re: [sqlite] SQLite equivalent of SQL Over(Partition)

That seems like an odd application of OVER (Partition by). Is there some performance reason one would want to do DISTINCT OVER (PARTITION BY) instead of a simple GROUP BY Sites.Customer, Sites.Digit, Count()? On Fri, Apr 6, 2018 at 12:20 PM, Simon Slavin wrote: > On 5 Apr

Re: [sqlite] Bug when opening a database file in a deep directory

On Apr 4, 2018, at 9:17 AM, Dan Billings wrote: > > 1) create a directory structure that produces a long path. If it’s longer than PATH_MAX on your system, then you’re exceeding your OS’s ability here, not a limit in SQLite. Using such paths is likely to cause

Re: [sqlite] exit status of command shell

Thank you, Peter, for confirming. I am using somewhat outdated version. I think this is a relatively minor issue. It surfaced for me because I mostly use bash shell to access database and rely on error codes to report status. As a work around I now always add ".exit" at the end of the SQL

Re: [sqlite] SQLite equivalent of SQL Over(Partition)

On 5 Apr 2018, at 11:41am, DThomas wrote: > Select DISTINCT Sites.Customer, Sites.Digit, > Count(TblContractTasks.TaskNumber) > OVER (PARTITION BY Sites.Digit) As TaskCount > FROM TblContractTasks INNER Join (Sites INNER Join TblContractDetails On > Sites.Digit =

Re: [sqlite] In memory only WAL file

Serious question: what prompts you to consider these things? Is sqlite being too slow for you? On Thu, Apr 5, 2018 at 10:00 AM Pavel Cernohorsky < pavel.cernohor...@appeartv.com> wrote: > Hello Dan, thank you very much for clearing this up, because that was my > important misunderstanding. > >

Re: [sqlite] Import zipped csv file

On 4/6/18, gwenn wrote: > Hello, > Is it possible to mix zipfile and csv extensions ? > sqlite> SELECT data FROM zipfile('csv_file.zip'); > works. > sqlite> .shell unzip csv_file.zip > sqlite> CREATE VIRTUAL TABLE test USING csv(filename='csv_file.csv'); > works. > How to

[sqlite] Import zipped csv file

Hello, Is it possible to mix zipfile and csv extensions ? sqlite> SELECT data FROM zipfile('csv_file.zip'); works. sqlite> .shell unzip csv_file.zip sqlite> CREATE VIRTUAL TABLE test USING csv(filename='csv_file.csv'); works. How to pass the data extracted by zipfile to csv extension ? Thanks.

Re: [sqlite] Bug when opening a database file in a deep directory

On 4/4/18, Dan Billings wrote: > To reproduce: > 1) create a directory structure that produces a long path. Why are you creating pathnames longer than 512 bytes? Seems like there will be usability issues there? > 2) attempt to open a DB file. > 3) observe error

Re: [sqlite] exit status of command shell

Roman. That's a good one. It affects the command status of well formed SQL as well: sqlite-src-323/bld$ echo 'SELECT * FROM sqlite_monster' | ./sqlite3;echo $? Error: near line 1: no such table: sqlite_monster 0 sqlite-src-323/bld$ echo 'SELECT * FROM sqlite_monster;' | ./sqlite3;echo

Re: [sqlite] Access to sqlite3_api_routines outside of a loadable extension context

Max. You are free to export your code differently by preprocessor directives. One binary of your code can be an extension and another can be an ordinary library. The loadable version binary simply needs to export a working sqlite3_extension_init() C entrypoint as described here:

[sqlite] SQLite equivalent of SQL Over(Partition)

Hello I have the following query in SQL Server 2008. The database has been moved to a mobile device using SQLite. Can anyone help with a equivalent statement for SQLite? Select DISTINCT Sites.Customer, Sites.Digit, Count(TblContractTasks.TaskNumber) OVER (PARTITION BY Sites.Digit) As TaskCount

Re: [sqlite] exit status of command shell

Roman. That's a good one. It affects the command status of well formed SQL as well: sqlite-src-323/bld$ echo 'SELECT * FROM sqlite_monster' | ./sqlite3;echo $? Error: near line 1: no such table: sqlite_monster 0 sqlite-src-323/bld$ echo 'SELECT * FROM sqlite_monster;' | ./sqlite3;echo

[sqlite] Bug when opening a database file in a deep directory

To reproduce: 1) create a directory structure that produces a long path. 2) attempt to open a DB file. 3) observe error :Error: unable to open database "X.db": unable to open database file 4) mv file to home dir 5) attempt to open 6) observe it opens as expected This has been tested in an

Re: [sqlite] Documentation Query/Correction

David My point point was that in one section of the documentation 'Type Affinity' was changed from 'NONE' to 'BLOB' with an explanatory note as to why and in another section it was unchanged. AFAIK type affinity of 'NONE' is the same as 'BLOB' as per the explanatory note. I was just bringing

Re: [sqlite] Access to sqlite3_api_routines outside of a loadable extension context

Oh, I see, just looked at the examples, all you described works thanks to the magic of c preprocessor. The problem in my case is that I'm on Delphi. Plus I already has something like indirect access to sqlite api functions, I will probably make a converter during the init between sqlite3Apis and