[sqlite] .mode defaults

[Klaas V]

Ciao SQLiters,


The separators don't change back to the defaults automatically after change of 
mode from 'asci' to 'list'.
Perhaps a '.mode def' or something like that would help to do so?

Defaults

--[[z4us|binz--]]
sqlite3 o1.sqc '.sho'
? ? ? ? echo: off
? ? ? ???eqp: off
? explain: off
? ???headers: off
? ? ? ? mode: list
???nullvalue: ""
? ? ? output: stdout
colseparator: "|"
rowseparator: "\n"
? ? ???stats: off
? ? ???width: 

Test of current behaviour

--[[z4us|binz--]]
sqlite3 o1.sqc '.read o2def.sql'
echo: off
 eqp: off
  explain: off
 headers: on
mode: ascii
   nullvalue: ""
  output: stdout
colseparator: "\037"
rowseparator: "\036"
   stats: off
   width: 
titt1a2becho: off
 eqp: off
  explain: off
 headers: on
mode: list
   nullvalue: ""
  output: stdout
colseparator: "\037"
rowseparator: "\036"
   stats: off
   width: 
titt1a2b

--[[z4us|binz--]]
cat o2def.sql
.he on
create table t1 (ti integer primary key, tt text) without rowid;
insert into t1 values (1,'a');
insert into t1 values (2,'b');
.mo asci
.sho
select * from t1;
.mo list
.sho
select * from t1;




Kind regards |?Cordiali saluti | Vriendelijke groeten | Freundliche Gr?sse,
Klaas `Z4us` V ?- LinkedIn# 437429414

[sqlite] sqldiff

[Klaas V]

Dear fellow SQLite-fans,

I'm still using the old version which is located at 
https://www.sqlite.org/2015/sqldiff-*-3081000.zip

There seem to be some changes supporting the features 3.10 and higher brought 
us.

Where can we find the newest binaries for OS X et al?

Kind regards |?Cordiali saluti | Vriendelijke groeten | Freundliche Gr?sse,
Klaas `Z4us` V ?- LinkedIn# 437429414

[sqlite] Problem with accumulating decimal values

[Klaas V]

On Thu, 17 Dec 2015 13:13:24 Simon wrote:
>to round only when you need to be handling integers.
Round the result to maximum two digits after the decimal point (or comma) when 
it handles about money.

Kind regards |?Cordiali saluti | Vriendelijke groeten | Freundliche Gr?sse,
Klaas `Z4us` V ?- LinkedIn# 437429414

[sqlite] Proposed new version numbering scheme for SQLite - Feedback requested

[Klaas V]

On Thu, Oct 8, 2015 at 8:36 AM, Simon Slavin  wrote:

>
> On 8 Oct 2015, at 2:38pm, Richard Hipp  wrote:
>
> > If accepted, the new policy will cause the next release to be 3.9.0
> > instead of 3.8.12.  And the second number in the version will be
> > increased much more aggressively in future releases.
>
> I approve of this particular release changing the Y value (i.e. being
> 3.9.0) since it allows SQLite to create and change databases to a format
> which can't be opened with previous versions.
>

I don't care a rat's behind what number changes next in the naming convention 
3vvuupp 
where vv = 'version', uu = 'update' ; pp = patch 

As someone else noted what is the criterion to call a revision major/minor 
version and is one of them applicable in the transition 3081101 to
 309 respectively 3081200. 

If the incompatibilities are as great as feared I'd go to for a new vv, if as 
small as hoped, let's feel free and modest call it "just an" update.

My ?0.02

Kind regards |?Cordiali saluti | Vriendelijke groeten | Freundliche Gr?sse,
Klaas `Z4us` V ?- LinkedIn 437429414

[sqlite] SQLite slows by a factor of 1000 after foreign key

[Klaas V]

Andrew Cunningham wrote:  
>I have noticed that when FOREIGN KEY is in DEFERRED mode and a FOREIGN KEY
>violation occurs, SQLite will continue to run, but performance slows down
>by about 1000x. Breaking into the code shows that it seems to be
>continually re-reading the database. When I finally get to COMMIT , an

When any violation occurs it's wise to use '.bail on' (ref. 
https://www.sqlite.org/cli.html )
and eventually abort the application (creating a core dump if necessary to get 
an investigation done by a developer) as soon as it stops to run for this 
reason.

Kind regards |?Cordiali saluti | Vriendelijke groeten | Freundliche Gr?sse,
Klaas `Z4us` V ?- LinkedIn 437429414

[sqlite] sqlite3 file as database

[Klaas V]

Dear SQLite-users,

R.Smith wrote:
>".sqlite" is found somewhat, but if I had to pick one that seems most 
>common, it would just be the:  "*.db"

Firefox uses both database-extensions. '.sqlite' for sqlite3 , .db for another 
kind.
I use .sqb for sqlite3 and .sqc for ciphered databases. When sqlite4 is 
released I can go on
 with .sqd and .sqe. Like someone stated earlier: .sql is "reserved" for 
SQL-scripts, because 
 TextWrangler does some very useful syntax-checking for this filetype.

Kind regards |?Cordiali saluti | Vriendelijke groeten | Freundliche Gr?sse,
Klaas `Z4us` V ?- OrcID -0001-7190-2544

[sqlite] Disk-image malformed

[Klaas V]

Dear fellow users and developers of SQLite,

sqlite> create table dual(dummy blob);
sqlite> insert into dual values ('SQLite version 3.8.11.1 2015-07-29 20:00:57');
sqlite> create table z4usm1 as select (unicode(substr(type,2,1)) - 97) / 4 "zk",
   ...>name "zn", rootpage "zp", tbl_name "zt" from 
sqlite_master; 
Error: database disk image is malformed

-- relevant part of .dbinfo
number of tables:1
number of indexes:   0
number of triggers:  0
number of views: 0
schema size: 29

I found a work-around by creating a view (actually my first intention, because 
that is what it actually is), but 
  if you really want to create a table this way how to achieve that?

sqlite> pragma page_size=512;
sqlite> create table dual(dummy blob);
sqlite> insert into dual values ('SQLite version 3.8.11.1 2015-07-29 20:00:57');
sqlite> create view z4usm0 as select (unicode(substr(type,2,1)) - 97) / 4 "zk",
   ...>name "zn", rootpage "zp", tbl_name "zt" from 
sqlite_master; 
sqlite> .he on
sqlite> select * from z4usm0;
zk|zn|zp|zt
0|dual|2|dual
2|z4usm0|0|z4usm0

-- relevant part of .dbinfo
number of tables:1
number of indexes:   0
number of triggers:  0
number of views: 1
schema size: 182

sqlite> .sh ls -lg m?.sq?
-rw-r--r--  1 staff  1024  9 Set 11:20 m0.sqb
-rw-r--r--  1 staff  2048  9 Set 11:10 m1.sqb


Another question: When can I see type 'meta' (would result in zk=1) [ exists 
according to https://www.sqlite.org/cli.html#fileio ] ?


Kind regards |?Cordiali saluti | Vriendelijke groeten | Freundliche Gr?sse,
Klaas `Z4us` V ?- OrcID -0001-7190-2544

[sqlite] Dumb statement question...

[Klaas V]

Someone chose a title like 'Dumb%question%'

FYI: Except perhaps of those unasked there is no such thing as a dumb question
Especially if a question concerns the great field of ICT whether it's libraries 
like SQLite, programming compiled (or interpreted)/database/script languages 
{resp. e.g. C[++] (Perl)/SQL9_/JavaScript et al}.
Most developers of systems and/or applications are happy to answer those kind 
of questions and more, I guess (an understatement, I'm almost sure about it).

Kind regards |?Cordiali saluti | Vriendelijke groeten | Freundliche Gr?sse,
Klaas `Z4us` V ?- OrcID -0001-7190-2544

Re: [sqlite] Whish List for 2015 (David Barrett)

[Klaas V]

On my whish-list for this list is the possibility to include attachments like 
screenshots and databases.
For program more handy functions and documentation more examples. I'm pretty 
sure that less lazy users made functions that could be included as standard to 
be used for all of us.  
Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Where do we post to share code? CTE: Long to Wide format

[Klaas V]

Happy New Year, Joe and other SML-members,
You answered your own question in the title of your post perfectly.
If you want to post the results of .fullschema here as well you provide us with 
almost every detail of the way you coded your database.
Perhaps you mean other code in what language and under which operating system 
except SQL; please specify what kind of coding you'd like to share with us. 
Unfortunately the managers of this list don't allow us to attach files so only 
source code.  
Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Camel-db source

[Klaas V]

Apache Camel: Source
|   |
|   |   |   |   |   |
| Apache Camel: SourceWarning Icon Apache Camel code repository was moved to 
git, The old svn repo will not be updated. Web Browsing of git To browse via 
the web:  |
|  |
| View on camel.apache.org | Preview by Yahoo |
|  |
|   |

   
Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updating sqlite3 in anaconda

[Klaas V]

Mitch asked how to update SQLite in Anaconda,
1. Perhaps you better ask this in a Python-forum as well.2. Please tell them 
and us what operating system you use3. Happy New Year 
Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request - RowCount

[Klaas V]

Jim Callahan wrote:>#26 The unique columns have non-null values (the answer 
says a lot more,>but that is the essence of what I am relying on).
Right, but the question was how to count rows as quickly as possible regarding 
any or all columnse.g. count(ProspectName) from Clients; 
One can imagine from some prospects you don't know the (real) name yet, just 
her or his emailAddress
>#1 If you have an integer primary key (which by definition in 
>SQLITE3>autoincrements) one might be able to get an approximate row count 
>faster>using the:
>sqlite3_last_insert_rowid() function.
If you're happy with an approximate count, but as soon as rows are deleted the 
last inserted row# may and will be more off an accurate value. Far off at some 
point in time that it becomes unacceptable for most managers.

>Jim Callahan>Orlando, FL
 
Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsubscription

[Klaas V]

Venkatarangan MJ wrote:

>How do I unsubscribe from the group as I have stopped using SQLITE from quite 
>some time now?
>Thanks.
>Regards
>Rangan.

Send a message with subject or body 'help' to sqlite-users-requ...@sqlite.org

This is written in the header of each digest; I don't know about single 
messages.
We're sorry to see one of us go as a user of the greatest RDBMS on planet Earth 
:-|

As they say in Italy:in bocca al lupo 

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Klaas V]

Simon wrote about parts of names like e.g. the Dutch/Flemish 'van'

They should definitely not be capitalised

Is not always true. Especially in northern Belgium names are often spelled like 
Van (often even connected with the last name)  and I did personally the same to 
see which of the two e-mail clients I used at work so the NOCASE tag may cause 
inconsistencies as well.

Anyway to split a name in two, three or maybe even more parts is not a bad 
idea. The solution with a number of commas is a very good one.

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expressions

[Klaas V]

Dominique Devienne wrote:

(double-click on new version) SQLite version 3.8.5 2014-06-04 14:06:34

(terminal) SQLite version 3.7.13 2012-07-17 17:46:21

 
This means you downloaded the new version in a directory not in your $PATH

Add it to your path:

set PATH=: $PATH

If you do this in your startupscript you always get the latest

Another option (not recommended!) is to overwrite the systemversion with 

sudo cp /sqlite3 /usr/bin/sqlite3

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crash in sqlite3_mutex_try [Was: SQLite 3.8.6 beta]

[Klaas V]

 Jan wrote: " ** can manually set this value to 1 to emulate Win98 behavior.
*/"

Can anyone give me one good reason apart from nostalgia to support a MS system 
not supported by MS?


Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Spellfix] Avoiding exact duplicates

[Klaas V]

Philip wrote:

>Is it possible to tell the spellfix extension not to accept duplicate 
>words if the rank and langid are exactly the same? I am collecting words 
>from many different sources and after inserting them I very often get 
>the same word back several times.

Add a 'unique' constraint in your create table statement for the column word so 
you can't have double words unless the same word has more meanings.
In that case you need to add e.g. (verb), (noun), [1], [2] or whatever you find 
applicable storing words in a table. 

I don't know if the extension allows you to make changes. 

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Klaas V]

Like some others I vote for solution 6.
In general accepting hexadecimal notation for floating point values exceeding 
64 bits is too developer/scientist friendly. Who needs so much precision use 
Fortran or another specialized language rather than SQL of any flavor. My € 
2E-2 [±$0,026930006220831 ;-)]
 

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] think I need better error-handling guidance in the C API

[Klaas V]

James K. Lowden wrote:

>If the answer to every question, concern, and suggestion is that it
>already works for millions of programs, there is no point in discussion
>or further development.  Just use what's there because it's already
>perfect. 

Permission to differ, James!

Since there is no such thing as a perfect application, because no living 
perfect developer exists except probably (the) God(s).
SQLite however comes pretty
 close, that's why it has millions of users, fans if you like to call them like 
that. 

BTW (someone else wrote this): to call a program 'crappy' sounds a wee bit 
megalomanic...

No body is perfect, but some are more perfect than other.

 

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using sqlite3_file in shims.

[Klaas V]

Renji Panicker wrote to this fine list:

>
>- As per the sqlite3PagerOpen function, the size of the memory allocated to
>this pointer is ROUND8(pVfs->szOsFile).

What configuration you're using, Renji?
On some OS/browser/SupportSoftware etc. combinations this function may crash

https://crash-stats.mozilla.com/report/list?signature=sqlite3PagerOpen

There seem to be version differences as well.

 

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting error "file is encrypted or is not a database" when trying simple C example

[Klaas V]

FYI: SQLite2.8.17 for some Linux-versions (and Apple ppc) can be found here:
http://www.rpmfind.net/linux/rpm2html/search.php?query=sqlite2

Perhaps there are applications to convert to a more contemporary format like 
3.8.4.3

 

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V, freelance CIO & ICT-consultant, BCNarTOSit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decomposing a path into its components?

[Klaas V]

On 3 May 2014, at 3:47pm, Petite Abeille  wrote:

>> Let further assume one would like to use only SQLite's build-in mechanism

>Where Simon answered:  "There are two kinds of programmers ..."

I tend to disagree, in fact there are at least three kinds
1 Application programmers
2 System programmers
3 Those kind of programmers who do a bit of both kinds of programming

 

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V, freelance CIO & ICT-consultant, BCNarTOSit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Where did the release announcements go?

[Klaas V]

Patrick asked 
"Where did the release announcements go?

This is the right place to go. Here you find announcements of new versions, 
planned updates and bug-fixes even before they arrive at 

Recent SQLite News

 
   Recent SQLite News
Small. Fast. Reliable. Choose any three. About Sitemap Documentation Download 
License News Support Recent News 2014-04-03 - Release 3.8.4.3   
View on www.sqlite.org Preview by Yahoo  

 

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V, freelance CIO & ICT-consultant, BCNarTOSit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How synchronize two databases

[Klaas V]

Muhammad wrote:
| ... two identical sqlite databases from structure side. I want to make a 
synchronization for the data ...
| Is there any library can do that OR I've to do it by myself?

With an Apple you can do it within SQLite itself. 

http://www.sqlite.org/pragma.html#pragma_fullfsync

To do it yourself use ',mp insert' and change the resulting SQL-code as needed 
with a suitable editor like Notepad++

Cordiali saluti | Kind regards | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V, freelance CIO / ICT-guru / SystemDeveloper-Analyst 
kla...@innocentisart.eu http://innocentisart.eu/klaasv/indexw.html



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

Re: [sqlite] SQLite version 3.8.4 release schedule

[Klaas V]

| JN: "... the Cygwin VFS to behave the same as other UNIX'es"

Warning: Off topic: RTF* and Gnu's Not Unix 
Two answers from Cygwin's  FAQ

Q: What?
A The Cygwin tools are ports of the popular GNU development tools for Microsoft 
Windows. 
They run thanks to the Cygwin library which provides the POSIX system calls and 
environment 
these programs expect. 
Q ECCN?
A No. Cygwin source and binary are made publicly available and free of charge 
to download 
so Cygwin is provided under TSU/TSPA exemption. As a result, 
Cygwin does not require an ECCN number.

I interpret this as Cygwin *not* being "other UNIX". 
In an attempt to become on topic: SQLite is another library without ECCN.

Cordiali saluti | Kind regards | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V, freelance CIO / ICT-guru / SystemDeveloper-Analyst 


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

Re: [sqlite] New

[Klaas V]

 Kees wrote answering Ashleigh
 
| If you prefer a graphical user interface, I can recommend
|the sqlite manager plugin in the Firefox web browser.

|| If any one knows a better way to read and understand the files I would 
greatly appreciate it 
|| |I think the file ext. is a plist. 
|| Live, love & laugh. 

|
|-- 
|Groet, Cordialement, Pozdrawiam, Regards,|

a P(roperty)list is a normal twxt file similar to XML and HTML

example:
http://www.apple.com/DTDs/PropertyList-1.0.dtd;>


Cordiali saluti | Kind regards | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V, freelance CIO / ICT-guru / SystemDeveloper-Analyst 
kla...@innocentisart.eu http://innocentisart.eu/klaasv/indexw.html



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

Re: [sqlite] Windows service with SQLITE

[Klaas V]

Tejas Parekh wrote: "both processes use common database db1.s3db file to store 
data."


Unfortunately SQLite does not allow more than one process to write a single 
file the same time. The one writing process should unlock "his" database in 
order to make it possible to others to make changes to  db1.s3db

File locking and concurrency
 

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V, CIO & freelance software engineer, ArTosIt
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Send Mail from sqlite

[Klaas V]

From: "Keith Medcalf" 
Date: 7 Feb 2014 18:03:12 GMT+01:00
To: "General Discussion of SQLite Database" 
Reply-To: General Discussion of SQLite Database 



Have your application that is performing the update send an email when it does 
an update/insert/delete.

> I like to know if there is any possible to send a mail from sqlite.
> 
> I wanted to know if there is option to configure smtp in sqlite, which
> will help me to send a mail.
> 
> My requirement
> 
> I need to send a notification mail once the table is getting
> updated/inserted/deleted.

It's very simple: let your application enter 'mailto:@.' in 
the browser's URL-field

Kind regards|Cordiali saluti|Vriendelijke groeten|Freundliche Grüsse 
Klaas `Z4us` V < Ar(Tos)It>
 

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

Re: [sqlite] Boolean and DataReader

[Klaas V]

|Igor Tandetnik wrote:
   | On 1/30/2014 7:20 PM, James K. Lowden wrote:
   |or define a constraint
   |
   |colname boolean check (colname in ('Y', 'N'))
   |
   |Of course you can use 0 and 1, but there's no technical advantage

|There is a tiny advantage. Values 0 and 1 are special-cased in SQLite's file 
format, and occupy only one byte each. 'Y' and 'N' require two bytes to 
represent.

Since SQLite uses at least 8 bits there is enough space for three values: 
False, True, Undefined. One has to define standard values. 
For example -1 for false, 0 for undefined, all others for true. YMMV. Using a 
letter has the advantage of being more explicit.  

Kind regards,
Klaas `Z4us` V

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

Re: [sqlite] order of = in join

[Klaas V]

Anbrus wrote:

"what's the easiest way to generate a set of fixed number of rows like
VALUES does but in an order of your choice?"

Order them before with the command 'sort'

NAME
   sort - sort lines of text files

SYNOPSIS
   sort [OPTION]... [FILE]...

DESCRIPTION
   Write sorted concatenation of all FILE(s) to standard output.

   Mandatory arguments to long options are mandatory for short options too. 
 Ordering options:

   -b, --ignore-leading-blanks
  ignore leading blanks

   -d, --dictionary-order
  consider only blanks and alphanumeric characters

   -f, --ignore-case
  fold lower case to upper case characters

HTH,
Klaas `Z4us` V



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

Re: [sqlite] Simple data conversion in SQLite - please help

[Klaas V]

On Jan 22, 2014, at 8:21 AM, Richard Hipp  wrote:

> I seem to recall seeing some SMS databases off of an iPhone that used unix
> timestamps for the date/time.  That would be seconds since 1970.  You can
> use the 'unixepoch' modifier on the date functions within SQLite to do
> the conversion, if you want.

That's the normal epoch point zero. There are systems starting on January 1, 
1904
Perhaps other dates are used as well.

Klaas `Z4us` V
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] shell crashes when there is no column name

[Klaas V]

Clemens wrote:

>  $ sqlite3
>  SQLite version 3.8.3 2014-01-11 12:52:25
>  Enter ".help" for instructions
>  Enter SQL statements terminated with a ";"
>  sqlite> .header on
>  sqlite> values(1);
>  (null)
>  1
>  sqlite> .mode html
>  sqlite> values(1);
>  Segmentation fault


One runs the risk stumbling upon obscure bugs sing what seems to be a beta.
Aforementioned becomes almost certain when one uses new features.

With the official 'r.v.u b' out of the box (no special compiler options 
whatsoever):

sqlite3
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .he on
sqlite> values(1);
Error: near "values": syntax error
sqlite> .mode html
sqlite> values(1);
Error: near "values": syntax error

>  And I guess NULL should not be returned in the first place if there is no 
> error.

Since you did not provide a column name returning (null) is quite logical. What 
else? 

 

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundlliche Grüße,
Klaas `Z4us` V, CIO & freelance software engineer
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Programming in SQL vs. a procedural computer language

[Klaas V]

Tobias wrote "... simpler just to have afterwards some things to do in my 
program? "

What is 'simple' is an off topic discussion. More important is the reason of 
making changes by you and/or your colleagues. 
I've seen coding in a procedural language I could only figure out by reading 
the pseudo assembler code. 

To create a view rather than writing one or more sub-selects is a suggestion 
worth to be taken in consideration for more reasons than simplicity and 
readability of the code. Advantages are reuse of the project by others without 
having to write a more or less complex SQL statement. Just a simple 

SELECT bar from foo where bar ... ; 

will do where foo is the view created. Please give me one advantage of your 
sub-select over view 'foo' .

Especia;;y woth SQLite we have a library with a lot of 'stored procedures' 
(Oracle terminology) and on top of it the possibility to code your own. You can 
move some safety coding to a preprocessor written in your favorite procedural 
language, in my case it's the platform independent (sort of) Chipmunk Basic in 
cooperation with Bash shell scripting.

Happy holidays,
Klaas `Z4us` V

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

Re: [sqlite] SQLite version 3.8.2 running 2.5x faster for some queries.

[Klaas V]

Warren wrote 4 dec 2013:

| There are tiny corners of the programming world (FP) where this is not the 
case, but then you get  into 
|   questions of purity, and databases are about as far from side-effect-free 
as you can get.

That's a wee bit exaggerated, but who am I to start a discussion; it's a valid, 
even respectable opinion

and
| Anyway, all this arguing over how SQLite *should* behave seems misguided to 
me.  What 
|   matters is how SQL is specified.  SQLite should follow the spec in areas 
like this.

Not even this me seems. The developers of SQLite can choose not to "obey" 
rules, advices and guidelines of SQL standard at will if one of the three guys, 
one of us users feels it's appropriate and fits their philosophy better or for 
whatever reason even rewrite (some of) the standard and create a new or adapted 
version = skipping, changibg adding things =  let's call it SQL13 (14 etc) or a 
less prozaic name. What about iSQL or SQLNT?  ;-) 

Klaas `Z4us` V

The fun with standards is there are so many to choose from
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Klaas V]

When you'use the word 'perfornance' you might be interested not jonly in speed, 
but in strength of protection, privacy of the employees, your company as a 
whole and above all your clients.
One of the goals of encryption is to avoid e.g. the government(s and the spies 
they hired to peek into your own business and with this behavior those of - 
again - your clients, their clients, etcetera.

There is more in the world than speed and size.

On Wed, Dec 4, 2013 at 6:18 PM, Simon Slavin  wrote:

> 
> On 4 Dec 2013, at 10:45am, Raheel Gupta  wrote:
> 
>> I wanted to know which is the best in performance ?

> 

Season's greetings and cheers.
Klaas `Z4us` V


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

[sqlite] SQLite_version

[Klaas V]

On Mon, Nov 4, 2013 at 6:12 AM, SongbookDB wrote:

> Thanks Clemens
>
> Flash gives the error - no such function: 'sqlite_version'
>
> SQLite Administrator says 3.5.1
>
> I'll see what the Adobe community say.


Or just .v
-
[[zephyros|db]]

sqlite3
SQLite version 3.8.1 2013-10-17 12:57:35
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .v
SQLite 3.8.1 2013-10-17 12:57:35 c78be6d786c19073b3a6730dfe3fb1be54f5657a

- 

Cordiali saluti/Vriendelijke groeten/Kind regards,
Klaas "Z4us" V MetaDBA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hints for the query planner

[Klaas V]

Richard  wrote:

The question for today is what to call this magic hint function:

(1)  unlikely(EXPR)
(2)  selective(EXPR)
(3)  seldom(EXPR)
(4)  seldom_true(EXPR)
(5)  usually_not_true(EXPR)

Please feel free to suggest other names if you think of any.

 
I dislike #4 and#5, but what about one of the following three:
1. Probability

2. Improbability

3. Probe

Number 2 might be in honor of British writer Douglas Adams's masterpiece H2G2

Cordiali saluti/Vriendelijke groeten/Kind regards,
Klaas "Z4us" V MetaDBA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: add support for COMMENT statement

[Klaas V]

Dear fellow users and managers of SQLite,

Alexey Pechnikov wrote:

>Bad idea. The schema definition can't be modified!

In fact it's possible if you use the writable schema pragma
http://www.sqlite.org/pragma.html#pragma_writable_schema

An application could drop all applicable `ls -l` and `id -P`-results in a 
database, fire triggers execute one or more smart functions and "presto".
Can be done, me seems. This way not such a bad idea as Alexey thinks.

As said in the link  you have to know well what you're doing, but this is a 
good advice always.

Kind regards,
Klaas `Z4us` V

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

Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

[Klaas V]

Dear fellow SQLite afficionados,

  Thanumalayan Sankaranarayana Pillai  wrote:
  "I expect it wouldn't be a problem with WAL"
Thé SQLite (not wanting, but cobsidering him at leat  kind of) Force D. Richard 
H.  [who does not know Him don't read this message, you won;t understand enough 
I'm afraid]  answered to   
  There are no configuration options set wrong.  You shouldn't ever have to
set configuration options in order to get SQLite to work right.  I think
instead you are just running up against fundamental limits of modern
consumer-grade hardware.  I don't think there is anything anybody can do
about it" I'd say as a fellow consumer of a wee little bit different hardware 
as well as software
"asking has some kind of answering included sometimes"  I admit since it's not 
my native lingo in imperfect English, I'm brave and arrogant enough to 
encourage mr. Pillai to download and move to a directory within her or his PATH 
the brand new/recommended RVU(P) 3.17.0(±130520) ASAP (i.e. As Soon As Possible 
[just in the very unlikely case y'all did not know yet this geek speak]
To make a long story shorter: Use WAL to open not only yer eyes, but possibly 
you guys's (and gal's) mind as well. Hopefully it's not (yet) banned in your 
part of this wondrtful globe.
 
Just my not extremely humble opinion. Thanks a million for paying eventual 
attention, friends (and 'why not' foes),
 

Cordiali saluti/Vriendelijke groeten/Kind regards,
Klaas "Z4us" V MetaDBA
kla...@innocentisart.eu

P.S.In bocca al lupo, tutti!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segmentation fault in SQLite parser

[Klaas V]

Eelco wrote: 
Using version 3.7.16.2
select * from ((select * from x)) y;
Segmentation fault


On OSX we get 'Bus error'. Not a bug, because you were syntactical not exact 
enough.
See http://www.sqlite.org/lang_select.html 

You're supposed to use only one pair () not more. Before 3.7.15 or 16 the extra 
pair were user friendly ignored.

Following example from an ancient version where you see that existence of a 
table makes a difference:

SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table dual (dummy);
sqlite> insert into dual values (1);
sqlite> .schema
CREATE TABLE dual (dummy);
sqlite> select * from dual;
1
sqlite>  select * from ((select * from x)) y;
SQL error: no such table: x
sqlite> select * from ((select * from dual)) bar;
1
sqlite> select * from (select * from dual) bar;
1

An example where newer versions ask more attention to developers. 

Cordiali saluti/Vriendelijke groeten/Kind regards,
Klaas "Z4us" V MetaDBA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite abnormal IO writing

[Klaas V]

>On Sun, 21 Apr 2013 11:15:23 +0800 (CST), 刘运杰 
>wrote:
>Ok,I do not make my means clearly. I mean 60 seconds after my
>program started,not token 60 seconds to load database file.
...
>Now,I modify the implement of batch query, it take about one
>second and 70k IO writing.So there are abnormal something in
>batch query indeed,Sqlite or Qt SQL module.

>>From: Kees Nuyt 
>>Date: 21 Apr 2013 09:35:53 GMT+02:00
>>Is this still about bulk INSERT

It looks like Yunjie reorganized the database in a way that the specific table 
is contiguous.
This action can make a huge difference in I/O time.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Double quotes in query

[Klaas V]

>Mohit wrote:
>Can you use double quotes in the query?

In this case no problem, double quotes are ignored around a tbl_name

sqlite> create temp table "db..test"(c1 integer, c2);
sqlite> insert into "db..test" values (1, 'sqlite_temp_master');
sqlite> insert into "db..test" values (2, 'db..test');
sqlite> .width -8 20
sqlite> select * from "db..test";
      c1  c2                  
  
       1  sqlite_temp_master  
       2  db..test            
sqlite> select rootpage, type from "sqlite_temp_master" where tbl_name = 
'db..test';     
rootpage  type                
  
       2  table               





 

Cordiali saluti/Vriendelijke groeten/Kind regards,

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

Re: [sqlite] integrity_check "out of memory"

[Klaas V]

Max wrote he's using an encrypted VFS. Perhaps the problem disappears when he 
decodes the file?
I can imagine the software does not find encoded page numbers and a maximum 
number of errors before quitting a check is pretty normal. Eight hundred is yet 
high. If I would write an integrity check procedure I would say about 250 tops 
and stop.
 
Cordiali saluti/Vriendelijke groeten/Kind regards, Klaas V

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

Re: [sqlite] disk image malformed (Durga D)

[Klaas V]

If you made a backup in one of following ways yes:

1. SQLite's Internal command .backup 
2. cp (or copy) command of your operating system's command line.


> On Thu, Dec 6, 2012  Durga D <durga.d...@gmail.com> wrote:

Is there anyway to recover?


> On Thu, Dec 6, 2012 at 10:23 PM, Richard Hipp <d...@sqlite.org> wrote:

>
> > Hi,
> >
> >  Is it possible to corrupt a single table among 10 tables in a database?
> >
> >  Is it possible to corrupt some records among millions of records in a
> > table?
> >
>
> Yes.  Yes.
>
 

Cordiali saluti/Vriendelijke groeten/Kind regards,
Klaas V

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

[sqlite] SQLite does not accept "extended ASCII"

[Klaas V]

The Win CodePage for among other languages with some "exotic" diacritics is 
#1250

http://www.science.co.il/language/character-code.asp?s=1250

The input file needs to be converted with a tool. Perhaps this one?

http://sourceforge.net/projects/cp-converter/




Michal Walczak <michal.walc...@studentpartner.com> wrote:
>
 Because I am Polish, I am using the extended ASCII charset, also my 
username uses it. Apparently SQLite is not able to process
> the file name with the file path which includes this characters. 

Is
 too. sqlite3_open[_v2] accepts file names in UTF-8 encoding, while 
sqlite3_open16 accepts file names in UTF-16 encoding. You would need to 
convert your file names from this "extended ASCII" (by which I imagine 
you mean "system-default codepage", aka CP_ACP) to one of the two 
supported encodings.
 

Cordiali saluti/Vriendelijke groeten/Kind regards,Klaas V
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What Windows versions are supported by SQLite3

[Klaas V]

Mohit wrote:

>SQlite3 works fine on Windows CE (at least 5.0 and 6.0) and Windows 
Mobile 6.x >or so.  Windows Phone 7 doesn't support end-users compiling 
native C/ C++ code >and accessing it from their applications - but there 
is a C# port that does work (as >far as I know).

We used 3.6.x (don't remember exact update) successfully under Vista two years 
ago. Now we have OSX ( SQLite r.v.u 3.7.13)

 

Cordiali saluti/Vriendelijke groeten/Kind regards,
Klaas V

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

Re: [sqlite] how to update the Moving average value

[Klaas V]

Keith Metcalf wrote:


>id  Sales_vol      mov_avg
>1    1            
>2    2              
>3    3          =(1+2+3)/3
>4    5          =(2+3+5)/3
>5    4          =(3+5+4)/3
>6    2          =(5+4+2)/3
>7

>select id, sales_vol, (select avg(sales_vol) as mavg
>                        from tbl b
>                        where b.id between a.id - 3 and a.id)
>  from tbl a;

- 3 should be - 2. You're working with the last 4 days and might get an error 
if id=3
:P
 

Cordiali saluti/Vriendelijke groeten/Kind regards,
Klaas V

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

Re: [sqlite] How to build sqlite4? Which branches are expected to build?

[Klaas V]

Richard Hipp wrote:


>Copy Makefile.linux-gcc into Makefile.  Edit to suite your system.  Type
"make"

When a compiled version of sqlite4 will be released? We're on OSX Snow Leopard.

Cordiali saluti/Vriendelijke groeten/Kind regards,
Klaas V

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