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

[Richard Hipp]

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, but figured it
> would be useful to report the issue in the hope that it could be fixed
> (or I could be shown a way to have this work correctly)

That is fixed in 3.19.0.  Suggest you update to the latest - 3.23.0.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 
> exactly.

[snip]

> there is no amount of translation that would reproduce the sentiment. I think 
> the tutorial from Simon's post will shed light, but Dean, if you still have 
> trouble, could you perhaps show us your schema and explain what you want to 
> know

That was my problem.  I don't use SQL Server and I don't know what would make 
someone who did reach for PARTITION BY, and I don't like cursor functions in 
SQL.  It seemed like they wanted GROUP BY but I wasn't sure.  Therefore I was 
loth to post an exact query.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R Smith]

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 redundant, it would work, but 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 exactly.


The reason I say that is that the OVER (PARTITION BY) functionality in 
MSSQL /can/ produce some amazing results that are very hard to do in 
SQLite (not impossible though, just difficult), but the shown query is 
not such a case, it's a straight-forward grouping, unless the query was 
simplified for posting, or the expected results were different than what 
we assume.


As to the solution, It's a bit like asking how you say "Cowabunga dude!" 
in Swahili - there is no amount of translation that would reproduce the 
sentiment. I think the tutorial from Simon's post will shed light, but 
Dean, if you still have trouble, could you perhaps show us your schema 
and explain what you want to know, because now we simply assume that 
shown query actually gives exactly what you wanted, which may or may not 
be the case. Tell us what the query output should reveal or look like, 
and someone here (or several someones) should come up with real good 
queries for the job.






On Fri, Apr 6, 2018 at 12:20 PM, Simon Slavin  wrote:


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 = TblContractDetails.SiteDigit) On
TblContractTasks.ContractNumber = TblContractDetails.ContractNumber
WHERE (Sites.Consultant='xx' ) ORDER BY Sites.Digit

Instead of PARTITION BY use GROUP BY .  See "with COUNT function" example
here:



I think everything else used will continue to work.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[jon baldry]

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 best option.


Now obviously, I could avoid the problem by avoiding hyphens, but my 
database was written originally in XML and I'll have to change a fair 
amount of code to undo my naming convention.


I have worked around this in PHP to modify the output, but figured it 
would be useful to report the issue in the hope that it could be fixed 
(or I could be shown a way to have this work correctly)


Regards

  Jon


.mode insert does not appear to quote identifiers when needed.

qlite3 --version

3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f

Example:

sqlite> create table "d-asher" ( "d-asher" TEXT );
Run Time: real 0.001 user 0.00 sys 0.00

sqlite> insert into "d-asher" ("d-asher") values ('a b c');
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> .mode insert "d-asher"
sqlite> select * from "d-asher";
INSERT INTO 'd-asher'(*d-asher*) VALUES('a b c');
Run Time: real 0.000 user 0.00 sys 0.00

Re-entering that line throws a syntax error.

sqlite> INSERT INTO 'd-asher'(d-asher) VALUES('a b c');
Run Time: real 0.000 user 0.00 sys 0.00
Error: near "-": syntax error

.dump gets it right.

sqlite> .dump "d-asher"
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE *"d-asher" *(*"d-asher"*TEXT );
INSERT INTO "d-asher" VALUES('a b c');
COMMIT;


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Don V Nielsen]

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 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 = TblContractDetails.SiteDigit) On
> > TblContractTasks.ContractNumber = TblContractDetails.ContractNumber
> > WHERE (Sites.Consultant='xx' ) ORDER BY Sites.Digit
>
> Instead of PARTITION BY use GROUP BY .  See "with COUNT function" example
> here:
>
> 
>
> I think everything else used will continue to work.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Warren Young]

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 
problems with other applications as well.

POSIX only requires that an OS support 256 character paths, which includes the 
trailing null.  See:

   http://pubs.opengroup.org/onlinepubs/9699919799/basedefs/limits.h.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] exit status of command shell

[Roman Fleysher]

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 statement.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
petern [peter.nichvolo...@gmail.com]
Sent: Thursday, April 05, 2018 3:25 AM
To: SQLite mailing list
Subject: 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 $?
Error: near line 1: no such table: sqlite_monster
1

Piped SQL lacking a trailing semicolon does indeed cause the shell to
report the wrong last command status.

Peter

On Wed, Apr 4, 2018 at 4:46 PM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLiters,
>
>
> I am using sqlite3 shell from bash scripts and I stumbled on what I think
> is incorrect exit code on error. In the first scenario, on error the exit
> code is 1 -- expected, in the second it is 0 -- unexpected. The error
> message is the same in both. Is that normal?
>
>
> echo -e "ww; \n.exit" | sqlite3
>
> Error: near line 1: near "ww": syntax error
>
> echo $?
>
> 1
>
>
> echo -e "ww" | sqlite3
>
> Error: near line 1: near "ww": syntax error
>
> echo $?
>
> 0
>
> Thank you,
>
> Roman
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 = TblContractDetails.SiteDigit) On
> TblContractTasks.ContractNumber = TblContractDetails.ContractNumber 
> WHERE (Sites.Consultant='xx' ) ORDER BY Sites.Digit 

Instead of PARTITION BY use GROUP BY .  See "with COUNT function" example here:



I think everything else used will continue to work.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In memory only WAL file

[Wout Mertens]

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.
>
> Risking corruption when the OS crashes is not really an option for me. I
> will probably go for what David Raymond suggested in one of other posts,
> or I may also solve my whole problem on the application level and have 2
> databases. One in memory only, which holds "all the changes since the
> last checkpoint", the second one on disk with all the data up to the
> last checkpoint. The character of the data I need to store allows this -
> I can first query the in-memory database for the most recent results, if
> I do not find them, I can query the on-disk database. My manual
> checkpoint will then be simply "writing everything from my in-memory
> database to my on-disk database in a single transaction". But my first
> choice will be the VFS David suggested.
>
> Thanks,
>
> Pavel
>
>
> On 04/05/2018 09:28 AM, Dan Kennedy wrote:
> > On 04/05/2018 02:08 PM, Pavel Cernohorsky wrote:
> >> Hello Dan, so you are saying that if WAL is somehow in memory only
> >> (not properly persisted) and app or OS crashes in the middle of the
> >> checkpoint operation, my main database file will get corrupted? And
> >> by corrupted you mean as in "you will loose changes which were in the
> >> WAL file", or "you will end up with unusable main database file, or
> >> file where rows which were affected by the checkpoint will have wrong
> >> contents (halfway through written, ...)". In other words, I may end
> >> up with the main database file in some other state than just "like
> >> before checkpointing" or "like after checkpointing"? I understood
> >> checkpointing as a kind of atomic operation which "merges data in the
> >> main database file and in the WAL". Is that understanding wrong?
> >
> > That's correct. If you crash mid-checkpoint and lose the wal file,
> > some future queries may return inconsistent results or SQLITE_CORRUPT
> > errors.
> >
> > The suggestion made in another post to put the wal file on a tmpfs or
> > similar file-system is a good one. Then you will only risk corruption
> > if the OS crashes. There is no easy way to do that at the moment
> > though, you will have to do some hacking to get it to work.
> >
> > Dan.
> >
> >
> >
> >
> >
> >>
> >> Thanks, Pavel
> >>
> >>
> >> On 04/04/2018 06:33 PM, Dan Kennedy wrote:
> >>> On 04/04/2018 09:01 PM, Pavel Cernohorsky wrote:
>  Hello, does anybody know if there is some possibility to not have
>  WAL file as a normal file on the disk, but only in memory? I
>  understand that all the modifications to the database would get
>  lost in case of the application / OS crash, but for my application,
>  I only need the level of durability based on checkpointing. I just
>  need to guarantee that all the data are properly written to the
>  main database and synchronized to disk when manual (or even
>  automatic) WAL checkpoint is called, but I do not care if I loose
>  data in between the checkpoints. Of course database should never
>  get corrupted.
> >>>
> >>> If your app or the OS crashes halfway through a checkpoint and this
> >>> means that the WAL file is lost, the database is likely to be
> >>> corrupted. Is that a problem?
> >>>
> >>> Dan.
> >>>
> >>>
> 
>  My goal is to limit the number of IOps being performed to the disk.
>  Currently I use "PRAGMA synchronous = 1" and there is only one
>  process manipulating the database (multiple reader threads, only
>  one writer thread at one moment in time). Or if it is not possible
>  to have WAL in memory only, is there something like “PRAGMA
>  wal_synchronous = none_and_delete_wal_if_corrupted”?
> 
>  Thanks for suggestions, kind regards,
>  Pavel
> 
> 
>  ___
>  sqlite-users mailing list
>  sqlite-users@mailinglists.sqlite.org
>  http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>>
> >>>
> >>> ___
> >>> sqlite-users mailing list
> >>> sqlite-users@mailinglists.sqlite.org
> >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> 

Re: [sqlite] Import zipped csv file

[Richard Hipp]

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 pass the data extracted by zipfile to csv extension ?
> Thanks.

I don't think that can be done right now.  It will require an
enhancement to the csv virtual table.  I have your request.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Import zipped csv file

[gwenn]

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.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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 :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 environment controlled for permissions.
>
> Tested on:
> Ubuntu Linux 17.10
>
> Not tested on Mac/Windows/other distros
>
> Sqlite3 --version
> 3.19.3 2017-06-08 14:26:16
> 0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] exit status of command shell

[petern]

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 $?
Error: near line 1: no such table: sqlite_monster
1

Piped SQL lacking a trailing semicolon does indeed cause the shell to
report the wrong last command status.

Peter



On Wed, Apr 4, 2018 at 4:46 PM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLiters,
>
>
> I am using sqlite3 shell from bash scripts and I stumbled on what I think
> is incorrect exit code on error. In the first scenario, on error the exit
> code is 1 -- expected, in the second it is 0 -- unexpected. The error
> message is the same in both. Is that normal?
>
>
> echo -e "ww; \n.exit" | sqlite3
>
> Error: near line 1: near "ww": syntax error
>
> echo $?
>
> 1
>
>
> echo -e "ww" | sqlite3
>
> Error: near line 1: near "ww": syntax error
>
> echo $?
>
> 0
>
> Thank you,
>
> Roman
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[petern]

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:

https://www.sqlite.org/loadext.html#programming_loadable_extensions

Here is another reply about reusing code between a SQLite server and client
that should give you some ideas:

http://sqlite.1065341.n5.nabble.com/Function-design-question-tc100925.html#a100933

Peter



On Thu, Apr 5, 2018 at 8:23 AM, Max Vlasov  wrote:

> Hi,
> I'm considering creating a virtual table or user function that might
> possible work either as a loadable extension or as a general, statically
> created one. In order to avoid repeating during developing, I thought that
> I might use sqlite3_api_routines structure as a universal access to sqlite
> code routines in both cases. But it seems it is impossible to access the
> structure outside of a loadable extension context. Or am I missing
> something?
>
> Probably some direct access to sqlite3Apis variable is possible, but I
> suspect such practice isn't  recommended.
>
> Thanks
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite equivalent of SQL Over(Partition)

[DThomas]

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 
FROM TblContractTasks INNER Join (Sites INNER Join TblContractDetails On
Sites.Digit = TblContractDetails.SiteDigit) On
TblContractTasks.ContractNumber = TblContractDetails.ContractNumber 
WHERE (Sites.Consultant='xx' ) ORDER BY Sites.Digit 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] exit status of command shell

[petern]

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 $?
Error: near line 1: no such table: sqlite_monster
1

Piped SQL lacking a trailing semicolon does indeed cause the shell to
report the wrong last command status.

Peter

On Wed, Apr 4, 2018 at 4:46 PM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLiters,
>
>
> I am using sqlite3 shell from bash scripts and I stumbled on what I think
> is incorrect exit code on error. In the first scenario, on error the exit
> code is 1 -- expected, in the second it is 0 -- unexpected. The error
> message is the same in both. Is that normal?
>
>
> echo -e "ww; \n.exit" | sqlite3
>
> Error: near line 1: near "ww": syntax error
>
> echo $?
>
> 1
>
>
> echo -e "ww" | sqlite3
>
> Error: near line 1: near "ww": syntax error
>
> echo $?
>
> 0
>
> Thank you,
>
> Roman
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Dan Billings]

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 environment controlled for permissions.

Tested on:
Ubuntu Linux 17.10

Not tested on Mac/Windows/other distros

Sqlite3 --version
3.19.3 2017-06-08 14:26:16
0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Documentation Query/Correction

[John McMahon]

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 to attention what I thought was an inconsistency in the 
documentation.


John


On 05/04/2018 06:25, David Raymond wrote:

Looks like when it goes and makes the table it doesn't give it an explicit "blob" type, 
as you would think from the phrase "When an expression is a simple reference to a column of a 
real table (not a VIEW or subquery) then the expression has the same affinity as the table 
column." It gives it no explicit type at all. However, according to...

http://www.sqlite.org/datatype3.html#determination_of_column_affinity
"3. If the declared type for a column contains the string "BLOB" or if no type is 
specified then the column has affinity BLOB."

...that lack of any explicit column type will results in an implicit blob 
affinity. So I guess it still winds up as blob in the end, but in a roundabout 
way. Though it doesn't show up in things like pragma table_info.



SQLite version 3.23.0 2018-04-02 11:04:16
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table foo (i int, nu numeric, r real, t text, b blob, n);

sqlite> create table bar as select i, nu, r, t, b, n from foo;

sqlite> select * from sqlite_master;
type|name|tbl_name|rootpage|sql
table|foo|foo|2|CREATE TABLE foo (i int, nu numeric, r real, t text, b blob, n)
table|bar|bar|3|CREATE TABLE bar(
   i INT,
   nu NUM,
   r REAL,
   t TEXT,
   b,
   n
)

sqlite> pragma table_info(foo);
cid|name|type|notnull|dflt_value|pk
0|i|int|0||0
1|nu|numeric|0||0
2|r|real|0||0
3|t|text|0||0
4|b|blob|0||0
5|n||0||0

sqlite> pragma table_info(bar);
cid|name|type|notnull|dflt_value|pk
0|i|INT|0||0
1|nu|NUM|0||0
2|r|REAL|0||0
3|t|TEXT|0||0
4|b||0||0
5|n||0||0


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of John McMahon
Sent: Wednesday, April 04, 2018 3:54 PM
To: SQLite Users
Subject: [sqlite] Documentation Query/Correction


In documentation for version 3.21.0:

in datatypes3.html
...
3. Type Affinity
...
Each column in an SQLite 3 database is assigned one of the following
type affinities:

  TEXT
  NUMERIC
  INTEGER
  REAL
  BLOB

(Historical note: The "BLOB" type affinity used to be called "NONE". But
that term was easy to confuse with "no affinity" and so it was renamed.)

and in lang_createtable.html
...
CREATE TABLE ... AS SELECT Statements
...
   The declared type of each column is determined by the expression
affinity of the corresponding expression in the result set of the SELECT
statement, as follows:

Expression Affinity Column Declared Type
TEXT"TEXT"
NUMERIC "NUM"
INTEGER "INT"
REAL"REAL"
NONE"" (empty string)

In the Expression Affinity table above, should the Expression Affinity
'NONE' be updated to 'BLOB' possibly with the explanatory 'Historical
note:' as per section '3. Type Affinity' in datatypes.html above.

NOTE: I have checked the current on line documents and they match the above.

For consideration.

Regards,
John




--
Regards
   John McMahon
j...@jspect.fastmail.com.au
04 2933 4203

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Max Vlasov]

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 my
structure

Thanks

On Fri, Apr 6, 2018 at 12:52 AM, Keith Medcalf  wrote:

>
> You write the code as if it were a loadable extension.
>
> If you compile "inline" (ie, appended to the amalgamation) the headers
> will detect the SQLITE_CORE symbol being defined and generate direct calls
> rather than indirect calls.  There is really no need to change the code
> from the code you would use from a loadable extension, unless you want to
> "hide" the loadable extension init() symbol when compiled as part of the
> amalgamation (or inline using the SQLITE_CORE define) and use the
> EXTRA_INIT hook to do initialization.
>
> ---
> 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 Max Vlasov
> >Sent: Thursday, 5 April, 2018 09:24
> >To: SQLite mailing list
> >Subject: [sqlite] Access to sqlite3_api_routines outside of a
> >loadable extension context
> >
> >Hi,
> >I'm considering creating a virtual table or user function that might
> >possible work either as a loadable extension or as a general,
> >statically
> >created one. In order to avoid repeating during developing, I thought
> >that
> >I might use sqlite3_api_routines structure as a universal access to
> >sqlite
> >code routines in both cases. But it seems it is impossible to access
> >the
> >structure outside of a loadable extension context. Or am I missing
> >something?
> >
> >Probably some direct access to sqlite3Apis variable is possible, but
> >I
> >suspect such practice isn't  recommended.
> >
> >Thanks
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users