Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Tim Streater]

On 13 Mar 2020, at 09:35, Peter da Silva  wrote:

> What do you mean there have been "just a few threads" in the mailing list?
> I can barely keep up with it.

As a lurker I can say that this mailing list suits just fine. Another forum 
means another username/password I have to manage. On a list, the contributions 
come in date order which is easier to manage than, rather than as with a forum, 
having them grouped by thread. In fact, with this list, there's no management 
at all required. An advantage of writing one's own email client is extra added 
features which are just right for me: I can limit the size of a mailbox to a 
certain number of mails. For the sqlite list, it's 10k mails. More than that 
and the oldest get moved to the Trash automatically.

It's all I need.


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

Re: [sqlite] Please unsubscribe me

[Tim Streater]

On 30 Jan 2020, at 15:34, kuppappa  wrote:

> Regards,
> Kuppappa
> Mobile: +91 8050095558
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

You do that yourself using the above website.



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

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

[Tim Streater]

On 27 Jan 2020, at 22:18, Richard Hipp  wrote:

> For many years I have described SQLite as being "serverless", as a way
> to distinguish it from the more traditional client/server design of
> RDBMSes.  "Serverless" seemed like the natural term to use, as it
> seems to mean "without a server".
>
> But more recently, "serverless" has become a popular buzz-word that
> means "managed by my hosting provider rather than by me."  Many
> readers have internalized this new marketing-driven meaning for
> "serverless" and are hence confused when they see my claim that
> "SQLite is serverless".
>
> How can I fix this?  What alternative word can I use in place of
> "serverless" to mean "without a server"?

Fundamentally SQLite is a library that you link in, either at app build time or 
later at runtime. I'd prefer a term that suggests this.


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

Re: [sqlite] Unexplained table bloat

[Tim Streater]

On 12 Jan 2020, at 22:25, Tom Browder  wrote:

> On Sun, Jan 12, 2020 at 14:05 Keith Medcalf  wrote:
>
>> On Sunday, 12 January, 2020 09:03, Tom Browder 
>> wrote:
>> >Am I missing something? I thought every column has to have a type?
>>
>> Close, but no banana.  Every value has a type.  A column may contain
>> multiple values (as in one per row)

> I assume that is just for SQLite, or am I wrong again?

In general that's correct. Lengths of VARCHARs are ignored, too, as you 
probably saw.




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

Re: [sqlite] Unexplained table bloat

[Tim Streater]

On 10 Jan 2020, at 18:55, Keith Medcalf  wrote:

> On Friday, 10 January, 2020 11:44, Tim Streater  wrote:
>
>>On 10 Jan 2020, at 18:03, Richard Hipp  wrote:
>
>>> On 1/10/20, Dominique Devienne  wrote:
>>>> There's no way at all, to know the length of a text column with
>>>> embedded NULLs?
>
>>> You can find the true length of a string in bytes from C-code using
>>> the sqlite3_column_bytes() interface. But I cannot, off-hand, think
>>> of a way to do that from SQL.
>
>>But if I store UTF-8 in a TEXT column, surely I'm allowed to include
>>NULLs in that? They are after all valid UTF-8 characters.
>
> No, they are not. The "NUL character" in Modified UTF-8 is the two-byte
> sequence 0xC0 0x80. This is specifically so that 0x00 can be used as a string
> terminator. Validly encoded UTF-8 encoded text stored in a C String (0x00
> terminated sequence of bytes) must not contain an embedded 0x00 byte since
> that byte terminates the sequence.

Nice, but Wikipedia has that as a "Derivative" and "incompatible with the UTF-8 
specification and may be rejected by conforming UTF-8 applications." It appears 
(though I may have missed it) not to be mentioned on this handy site either:

https://www.utf8-chartable.de/unicode-utf8-table.pl

I shall have to check what my preferred language's wrapper does.


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

Re: [sqlite] Unexplained table bloat

[Tim Streater]

On 10 Jan 2020, at 18:03, Richard Hipp  wrote:

> On 1/10/20, Dominique Devienne  wrote:
>>
>> There's no way at all, to know the length of a text column with embedded
>> NULLs?
>>
>
> You can find the true length of a string in bytes from C-code using
> the sqlite3_column_bytes() interface.  But I cannot, off-hand, think
> of a way to do that from SQL.

But if I store UTF-8 in a TEXT column, surely I'm allowed to include NULLs in 
that? They are after all valid UTF-8 characters.



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

Re: [sqlite] TO

[Tim Streater]

On 08 Jan 2020, at 16:13, R Smith  wrote:

> Hopefully the last of the silly questions...
>
> The word "TO" is given as an SQLite Keyword, but I cannot find any 
> reference to it being used anywhere in the SQL used by SQLite.
>
> The search doesn't help (because the word TO is everywhere in text), so 
> manually looking through CREATE TABLE / TRIGGER / etc. came up with nothing.
>
> Anyone have an idea where the word TO is used in SQL in SQLite?

ALTER TABLE?

See: https://www.sqlite.org/lang_altertable.html


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

Re: [sqlite] Questions about your "Performance Matters" talk re SQLite

[Tim Streater]

On 03 Jan 2020, at 22:08, sky5walk  wrote:

> Querying the config state is helpful for a dll wrapped database, ...

What's one of them?


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

Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output

[Tim Streater]

On 21 Dec 2019, at 21:42, Michael Walker (barrucadu)  
wrote:

> I'm not sure the attachment to my first email got through ...

Correct. The list strips them.



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

Re: [sqlite] Result set column names

[Tim Streater]

On 10 Dec 2019, at 21:42, Simon Slavin  wrote:

> On 10 Dec 2019, at 9:35pm, Doug  wrote:
>
>> For column "xxx" what will the full column name be? "foo.xxx"?
>
> For column "xxx" in table "foo", yes.
>
> However, that's only if you've used "PRAGMA full_column_names".  And that
> PRAGMA is now deprecated so you shouldn't use it.

Why are these pragmas deprecated? And what does "deprecated" mean, in this 
context, anyway? There's lots of HTML that's "deprecated", but browsers will 
continue to support it until kingdom come.


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

Re: [sqlite] Crash Bug Report

[Tim Streater]

On 08 Dec 2019, at 21:55, Simon Slavin  wrote:

> On 8 Dec 2019, at 7:51pm, Yongheng Chen  wrote:
>
>> The bug exists in "SQLite version 3.31.0 2019-12-08 00:06:39” and before.
>
> SQLite version 3.28.0 2019-04-15 14:49:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE TABLE v0 ( v1 ) ;
> sqlite> CREATE TABLE v2 ( v3 ) ;
> sqlite> CREATE VIEW v4 AS WITH x AS ( SELECT x () OVER( ) FROM v4 ) SELECT v3
> AS x FROM v2 ;
> sqlite> DROP TRIGGER IF EXISTS x ;
> sqlite> ALTER TABLE v2 RENAME TO t3;
> sqlite>
>
> Not a problem for this version.

macOS 10.14.6:

Third-Mini% sqlite3
-- Loading resources from /Users/tim/.sqliterc
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE v0 ( v1 ) ;
sqlite> CREATE TABLE v2 ( v3 ) ;
sqlite> CREATE VIEW v4 AS WITH x AS ( SELECT x () OVER( ) FROM v4 ) SELECT v3 
AS x FROM v2 ;
sqlite> DROP TRIGGER IF EXISTS x ;
sqlite> ALTER TABLE v2 RENAME TO t3;
zsh: segmentation fault sqlite3
Third-Mini% 

My .sqliterc has:

Third-Mini% more .sqliterc 
.mode column
.width 0
.headers on



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

[sqlite] Result set column names

[Tim Streater]

At various times in various threads on this list it has been stated that the 
column name in a result set is not guaranteed unless one uses AS. IOW, one 
should say

  select abc as abc from mytable where i=23;

rather than just:

  select abc from mytable where i=23;

I'm trying to find where on the SQLite website that is documented, if it is, so 
I can point someone at it. The Xojo documentation doesn't mwention this so 
either that is a potential problem for all us Xojo users or they've worked 
around it somehow in their SQLite wrapper.

Thanks for a pointer.


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

Re: [sqlite] Can SQLite import Latin1 data?

[Tim Streater]

On 15 Nov 2019, at 20:13, Winfried  wrote:

> I have a big CSV file that's encoded in Latin1 (cp1252), while SQLite stores
> strings as Unicode.
>
> Neither Google nor ".help" helped to find if SQLite offers a switch to
> convert Latin1 to UTF-8 on the fly before running the ".import" command.
>
> Should I first convert the file into UTF-8 before importing it into SQLite?

Far as I know, SQLite stores your text as UTF-8 and you have to do the 
conversion yourself. I think there's a UTF-16 option too.

I'd start here: https://www.sqlite.org/faq.html




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

Re: [sqlite] rationale for private archives of the SQLite mailing list and suppressed reply-to

[Tim Streater]

On 11 Oct 2019, at 16:56, Brannon King  wrote:

> I agree that Mailman is archaic. I worry about the security on it. I don't
> enjoy using 3rd-party mirrors for searching it. I'd like to propose that we
> upgrade to something more modern and secure like Sympa or mlmmj, or even a
> more drastic system upgrade to something like Redmine -- a project
> tracker + forum.

A mailing list suits me just fine. It works well and gets next to no spam. In 
addition, it's not yet another damn website I have to log into to use and 
remember my username/password for. And I don't care if it's not "modern".


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

Re: [sqlite] Date time input

[Tim Streater]

On 07 Oct 2019, at 17:19, Keith Medcalf  wrote:

> On Monday, 7 October, 2019 07:17, Jose Isaias Cabrera 
> wrote:
>
>>I have to ask this question:  Why is it that the date function does not
>>take a '4/5/2019' and returns '2019-04-05'?  
>
> Because it does not.  Humans read things in big endian order.

No, we read them in order of importance. If someone asks what the date is, the 
most useful answer is the day number of the month (not, for example, the year). 
Equally, if asomeone asks what time of day it is, the most useful answer is the 
hour (not, for example, the second). If someone asked me the date and time, I'd 
hardly respond "It's 2019 and its 27 seconds past." Wouldn't be that useful, 
now would it. So the most important item if first, then the others in 
decreasing order of importance.


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

Re: [sqlite] Programming methodology (was DEF CON (wasL A license plate of NULL))

[Tim Streater]

On 15 Aug 2019, at 18:13, James K. Lowden  wrote:

> On Tue, 13 Aug 2019 16:47:43 -0400
> Richard Hipp  wrote:
>
>> On 8/13/19, Jose Isaias Cabrera  wrote:
>> >
>> > I see all of you smart programmers using this
>> > non-column matching behavior, and I ask myself why?
>> 
>> Because that's the way Dennis Richie did it.  :-)
>
> That's right.  Like many of a certain age, I learned C from K, and
> adopted Ritchie's style.  

In terms of learning C, me too. However I looked at the various styles and 
adopted Whitesmiths; the braces align and I'm not wasting time looking for the 
matching one.


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

Re: [sqlite] Best way to create multiple tables?

[Tim Streater]

On 12 Aug 2019, at 13:51, dboland9  wrote:

> I'm doing an app. that has multiple tables in sqlite.  When the app. is run
> for the first time, or the tables are lost/damaged, it will create the
> database and all tables.  My question is if there is a best way to create 5
> tables?
>
> I can put al the Python code into one function, pass the path to the database
> file to multiple functions, or pass the connection to multiple functions.  One
> function is the most contained, but very large!  Any suggestions from those
> that have been there done that (BTDT)?

I check for the existence of each database, and if not found then create it 
from scratch. I also have a globals table in each database, of which one of the 
columns is a version number. If a new software version expects a later version 
number, it can apply incremental schema changes. An older software version 
finding a too-new version number can quit with a message asking the user to 
upgrade.


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

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

[Tim Streater]

On 12 Aug 2019, at 14:30, J Decker  wrote:

> On Mon, Aug 12, 2019 at 5:42 AM Simon Slavin  wrote:
>
>> On 12 Aug 2019, at 1:27pm, Tim Streater  wrote:
>>
>> > I don't expect to do that with SQL. My "seconds since the epoch" is
>> based on converting any particular time to GMT and storing that. That
>> number is then converted to a date/time with TZ info for display.

> If the timezone is stored, then the time is all UTC and easily sortable.
>  A sub-order of timeone within a sepcific time sequence ends up happening
> *shrug*

Why are you storing the timezone? You display the TZ of the user who is, later, 
viewing the data. And that user could be anywhere.

> But then, I'm assuming the time would just be ISO8601; since SQLite
> datetime functions take that as an input already.

I'm a user; I don't want my times displayed as ISO8601. That's why we have 
date/time control panels so the user gets to choose how those are displayed.


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

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

[Tim Streater]

On 12 Aug 2019, at 12:41, Thomas Kurz  wrote:

> The problem is not only about storing an arbitrary integer or float number. A
> date is much more, it has timezone information with it, and I would like to
> see a DATE column handle this in a proper and well-defined way, just as a
> calendar (CalDAV) does handle it, so it would allow me to convert between e.g.
> EST and CEST or calculating time differences (e.g. working time in a company).
> I know many of this is somehow possible now as well, but as far as I know, not
> really compliant with other RDMSs.

I don't expect to do that with SQL. My "seconds since the epoch" is based on 
converting any particular time to GMT and storing that. That number is then 
converted to a date/time with TZ info for display.


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

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

[Tim Streater]

On 12 Aug 2019, at 00:43, Keith Medcalf  wrote:

> On Sunday, 11 August, 2019 16:02, Richard Damon 
> wrote:
>
>>On 8/11/19 4:21 PM, Thomas Kurz wrote:
>
 I do understand the value of having date/time types in SQLite, but
 it is not easy to do while retaining backward compatibility.  
>
> I do not see any value in having a date/time type is SQLite.

Neither do I. The correct way is to store, as an integer, seconds (or whatever 
time unit you want) since some epoch. The display of what that stored value 
means is a *presentation* issue and should be handled as such by whatever 
language is presenting to the user. That also allows the program to either 
allow the user to presentation format to suit themselves, or for the program to 
follow the way system prefs are set.


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

Re: [sqlite] round function inconsistent

[Tim Streater]

On 24 May 2019, at 13:35, Jose Isaias Cabrera  wrote:

> Alessandro Merolli, on Friday, May 24, 2019 07:39 AM, wrote...
>
>> Great! Now use SQLite API and add a new user defined function for
>> your used case.
>> I suppose that SQlite should always follow a well-defined pattern: in
>> this case as Mr. Hipp said, it is IEEE754.
>
> If IEEE754 can't figure out how to round 3.555 to 2 decimals, I don't know if
> I'd trust it. ;-) thanks.

IEEE754 knows nothing about 3.555. If you input that, it gets changed to a 
binary floating point number with a different actual value. Which is then 
rounded. No point in complaining about that.


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

Re: [sqlite] unsubscribe

[Tim Streater]

On 13 May 2019, at 09:19, Christof Arnosti  
wrote:

> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Do that yourself at the link above.



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

Re: [sqlite] Please remove my email address from the list

[Tim Streater]

On 05 May 2019, at 10:14, Michaël Doukan  wrote:

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

You have to do that yourself using the link in each email you get from the list.



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

Re: [sqlite] Problem with REAL PRIMARY KEY

[Tim Streater]

On 04 May 2019, at 18:49, Richard Hipp  wrote:

> On 5/4/19, Manuel Rigger  wrote:

>> Consider the following example:
>>
>> CREATE TABLE t1 (c0, c1 REAL, PRIMARY KEY (c1, c0));
>> INSERT INTO t1(c1) VALUES (0X7ff);;
>> SELECT ALL * FROM t1 WHERE c1 = 5.76460752303423e+17;
>>
>> I would expect the row to be fetched, which is not the case.
>
> But 0x7ff != 5.76460752303423e+17.  Try it:
>
>SELECT 0x7ff != 5.76460752303423e+17;
>
> You should get back 0.
>
> The rule of thumb is to never expect the == operator to give a
> meaningful answer for floating-point numbers. Only use <, <=, >, and >=.

My floating-point "don't do that" lesson occured in 1965 when trying to compute 
square roots by Newton's method. This was on an Elliott 803B.



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

Re: [sqlite] Getting the week of the month from strftime or date functions

[Tim Streater]

On 04 May 2019, at 09:35, Olivier Mascia  wrote:

>> Le 2 mai 2019 à 22:01, Thomas Kurz  a écrit :
>> 
>>  I think "week of the month" is not a standard value. As with week of the
>> year, is week #1 the week in which the month starts, the first complete week
>> within the month, or the first week with at least 4 days?
>
> These are very regional matters around our living globe.
> In most European locations where ISO-8601 applies for that matter, a week
> starts on Monday and ends on Sunday (not Sunday to Saturday) and the week #1
> of a year is the one week whose Thursday falls in that year (and the Monday
> can be in the previous year). This can lead to years sometimes with 53 weeks
> instead of 52.

It can also be a personal matter. For me, the week starts on Monday. For my 
wife, it starts on Sunday.



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

Re: [sqlite] Database corruption check.

[Tim Streater]

On 15 Apr 2019, at 11:02, Simon Slavin  wrote:

> On 15 Apr 2019, at 10:36am, Lullaby Dayal  wrote:
>
>> Thank you very much for your response. The link seems to be helpful. But I
>> fail to run the .selftest command from my sqlite3 prompt. I got the error:
>> unknown command or invalid arguments error.
>
> This command was added to the command-line tool recently.

3.19.3 has it - that's almost two years ago.


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

Re: [sqlite] Why no persistent user settings in a database file?

[Tim Streater]

On 03 Apr 2019, at 11:52, Tom Browder  wrote:

> After coming back to SQLite from a long absence, I was surprised that
> setting things like .mode and .headers in a database didn't stay that way
> after exiting the file.
>
> Then I remembered something about a resource file and found .sqliterc on an
> internet search and that allowed the persistent settings I wanted.
>
> However, would it not be more natural to keep those settings persistent
> inside the db file?

These settings are nothing to do with the database file. They're settings for 
the sqlite3 CLI program.



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

Re: [sqlite] What is the most commonly used file extension for sqlite3 database files?

[Tim Streater]

On 23 Mar 2019, at 16:06, Peng Yu  wrote:

> I see a variety of extensions for sqlite3 database files, such as .db,
> .db3, .sqlite3 and .sq3. Is there a most commonly used/accepted
> convention for the extension? Thanks.

I use none at all. No extension is actually needed.



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

Re: [sqlite] INSERTing from another table data

[Tim Streater]

On 20 Mar 2019, at 00:35, Simon Davies  wrote:

> On Tue, 19 Mar 2019 at 15:07, Tim Streater  wrote:
>>
>>  My use case is a mixture of these. My need is to copy a row from a table in
>> one db (db1) to a table with identical schema in another db (db2). The
>> complication is that there is an id column, so the row needs to get a new id
>> in db2.
>>
>> At the minute I open db1, and do these steps (absid is the id column):
>>
>> attach database ':memory:' as mem
>>  create table mem.messages as select * from main.messages where
>> absid=
>> update mem.messages set absid=null
>> attach database 'db2' as dst
>> insert into dst.messages select * from mem.messages
>>
>>  which works nicely but has too many steps. I've not found a way to reduce
>> the step count.
>
> absid is integer primary key, or a new id would not be generated in
> the above scenario; so the following should work:
>
> insert into dst.messages( notabsid_1, notabsid2,...) select
> notabsid_1, notabsid_2,... from main.messages;

Yes, I thought about that at the time, but preferred what I have done, as this 
allows me to have just the one place in the app where the table schema is 
defined. I do this trick to move or copy a row in several places in the app, 
with slight variants, and explicitly spelling out all the columns would mean 
I'd have to be sure I'd updated that in a number of places, were the schema to 
change from time to time.

Not ideal, but better from a maintenance PoV.


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

Re: [sqlite] INSERTing from another table data

[Tim Streater]

On 19 Mar 2019, at 13:46, R Smith  wrote:

> Three ways in SQL to create and fill a table with data from another:
>
> 1. CREATE ... AS
> Example:
> CREATE TABLE newTable AS SELECT a,b,c FROM oldTable;

> 2. CREATE TABLE + INSERT
> Example:
> CREATE TABLE newTable(a INT, b REAL, c TEXT);
> INSERT INTO newTable(a.b.c) SELECT a,b,c FROM oldTable;

My use case is a mixture of these. My need is to copy a row from a table in one 
db (db1) to a table with identical schema in another db (db2). The complication 
is that there is an id column, so the row needs to get a new id in db2.

At the minute I open db1, and do these steps (absid is the id column):

attach database ':memory:' as mem
create table mem.messages as select * from main.messages where 
absid=
update mem.messages set absid=null
attach database 'db2' as dst
insert into dst.messages select * from mem.messages

which works nicely but has too many steps. I've not found a way to reduce the 
step count.


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

Re: [sqlite] Maximum result set size

[Tim Streater]

On 11 Mar 2019, at 20:21, Simon Slavin  wrote:

> On 11 Mar 2019, at 7:30pm, Tim Streater  wrote:
>
>> What is the maximum size in bytes that a result set may be? And what happens
>> if that size were to be exceeded?
>
> [The following is simplified for clarity. I discuss only worst cases and
> ignore caching.]
>
> SQLite does not prepare an entire result set at once. Instead, it returns one
> row each time you call sqlite3_step(). It's up to your own program to process
> each row as it is returned. Since SQLite does not hold more than one row in
> memory at once, theoretically there's no limit on how many rows it can return
> for a single query.

[snip]

> <https://sqlite.org/limits.html>

Thanks for that, Simon. That would account for there being nothing on the 
limits.html page about a maximum result set size. My question came up because 
someone, using a language that has an API for a number of flavours of SQL 
(including SQLite), was experiencing a crash when his result set got to about a 
gig in size. So, as seems very often to be the case, it must be the interface 
API code to the SQLite library that was at fault.

@Wout: nothing wrong with your reply, it was the TL:DR; version :-)


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

[sqlite] Maximum result set size

[Tim Streater]

What is the maximum size in bytes that a result set may be? And what happens if 
that size were to be exceeded?


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

Re: [sqlite] Disk I/O errors

[Tim Streater]

On 23 Feb 2019, at 19:30, Simon Slavin  wrote:

> On 23 Feb 2019, at 6:41pm, Tim Streater  wrote:
>
>>  The PHP interface to SQLite doesn't appear to give me access to the extended
>> codes, unfortunately.
>
> I used to do SQLite from PHP myself.  Hold on ...
>
> Bah.  I agree with you.  There doesn't seem to be any way to do it.  You can't
> even extend the library yourself to add the facility, short of compiling your
> own SQLite3 library.
>
> Everything else in your post suggests you're doing it right.  I have nothing
> to suggest.  I hope someone else does.

Well it's all mysteriously OK now. I'm asking for SSH access to the site so I 
can run the sqlite3 shell there, perhaps compiled here in my Linux VM to show 
extended error codes (if the shell doesn't already do that, I haven't checked 
yet.).

The hosting support people did say I was running with an old version of PHP so 
I've selected to run 5.6 now, and will probably crank that up to 7.2 to get a 
much more recent version of the sqlite library. I think what happened is they 
just removed my ability to *select* to run that older version, while leaving 
PHP 5.3 actually in place (the hosting console allows selection of PHP 
version). The errors I was seeing persisted with PHP 5.6 but as I say, seem to 
gone now.

I may be back if I have problems exposing extended error codes in the shell. 
I'd fogotten those existed, so that's something learnt today at any rate :-)

Thanks.


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

Re: [sqlite] Disk I/O errors

[Tim Streater]

On 23 Feb 2019, at 18:10, Simon Slavin  wrote:

> On 23 Feb 2019, at 5:55pm, Tim Streater  wrote:
>
>>  I have a hosted web site using the SQLite functions from PHP. The page where
>> PHP is used was failing, and on investigation this is because an SQLite
>> function called from within PHP is now returning:
>> 
>> Code: 10 (SQLITE_IOERR)
>> Msg:  disk I/O error
>> 
>>  I will be working with my hosting provider but, is there a way to get more
>> specific information about this?
>
> Run this:
>
> <https://sqlite.org/pragma.html#pragma_integrity_check>

Both (tiny) db's return 'ok'. This doesn't surprise me too much as I can FTP 
them to my local machine and run, without error, the same select that the 
webpage runs on them.

> Is the disk with the database on inside the computer running PHP ?  If not,
> how are you accessing it ?

Yes, both db that the webpage accesses are on the host machine.

> You can find more detail than just "_IOERROR" using this:
>
> <https://www.sqlite.org/c3ref/c_abort_rollback.html>

The PHP interface to SQLite doesn't appear to give me access to the extended 
codes, unfortunately.



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

[sqlite] Disk I/O errors

[Tim Streater]

(sorry for the duplicate - vibrating finger).

I have a hosted web site using the SQLite functions from PHP. The page where 
PHP is used was failing, and on investigation this is because an SQLite 
function called from within PHP is now returning:

Code: 10 (SQLITE_IOERR)
Msg:  disk I/O error

I will be working with my hosting provider but, is there a way to get more 
specific information about this?

SQLite version:  3.7.7.1
PHP version: 5.3.28

Thanks.


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

[sqlite] Disk I/O errors

[Tim Streater]

I have a hosted web site using the SQLite functions from PHP. The page where 
PHP is used was failing, and on investigation this


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

Re: [sqlite] Am I understanding how to use ROLLBACK - SAVEPOINT correctly?

[Tim Streater]

On 13 Feb 2019, at 15:23, David Raymond  wrote:

> On a humor tangent, am I the only person who has ever named a savepoint
> "theHounds" for the sole purpose of being able to run the statement:
>
> release theHounds;

welease Bwian;



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

Re: [sqlite] My TimeStamp field keeps populating though I don't write anything to it

[Tim Streater]

On 05 Feb 2019, at 13:08, Scott  wrote:

> I have a Comment table with the fields: CommentID, Summary, Comment, Pages,
> TimeStamp, Hyperlink. The TimeStamp field is setup as a Time(8), time with 8
> characters only. Not every comment in this table requires a timestamp, so it
> should remain null. However, it has started adding "Thu Jan 01 00:00:00 EST
> 1970" and it will not let me clear it. This is being populated with the
> following Java code, in the past, it would just remain null. I included a
> Regular Expression to check the GUI field data when it is used. But, I can't
> even manually clear it now and I haven't changed the setup of the database,
> the table or TimeStamp field.
> Any thoughts or suggestions?

1) Please show your whole table definition for the comment table.

2) Time(8) has no meaning to SQLite, your timestamp field will probably default 
to TEXT.
   See: https://www.sqlite.org/datatype3.html



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

Re: [sqlite] Min/Max and skip-scan optimizations

[Tim Streater]

On 02 Feb 2019, at 22:19, Gerlando Falauto  wrote:

> What I'm now trying to do is filter on source1 and by range of timestamp.
> Results should be naturally ordered by source1, source2,ts.

Not unless you use an ORDER BY.


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

Re: [sqlite] SQLite error (5): database is locked

[Tim Streater]

On 14 Jan 2019, at 18:54, Simon Slavin  wrote:

> SQLite's own busy_timeout routine (the one you get if you don't supply your
> own) uses exponential backoff. It first sleeps for a very small amount of
> time, then checks the lock. If access is still prevented it sleeps longer,
> then checks again. Then longer still, then longer still, until the amount of
> time /since the routine started/ exceeds the timeout you set.

Hmm, from my attempt at interpreting the source code I gained the impression 
that the handler used exponential backoff for the first few attempts, but then 
used a constant period for the rest (up to the set limit). Is this not the case?


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

Re: [sqlite] Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"

[Tim Streater]

On 06 Jan 2019, at 13:11, Daniel Kraft  wrote:

> Since it seems that the mailing list swallowed my attached example code,
> I've put it on Github as well:

Attachments are explicitly disallowed on this list.


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

Re: [sqlite] Question about floating point

[Tim Streater]

On 17 Dec 2018, at 21:50, Thomas Kurz  wrote:

> Can someone explain to me why it has been defined this way? Having 1 bit sign,
> 11 bit exponent, and 52 bit mantissa, I would have stored the (in the meantime
> well known) number 211496.26 as 21149626E-2, i.e. I would have stored a 52 bit
> integer number and appropriate exponent. This way there should be no rounding
> errors and one would always have a guaranteed precision of ~15 significant
> digits.

Possibly:





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

Re: [sqlite] Question about floating point

[Tim Streater]

On 17 Dec 2018, at 04:32, D Burgess  wrote:

>> Banks still use, as they have for a very long time, Binary Coded
>> Decimal, or some equivalent that does not suffer from a loss of
>> accuracy, so all this foofaraw to do with floating point representation
>> of various amounts of currency does not apply to the real world.

> As do insurance companies and many in the manufacturing world (inventory).
> There is a lot to like about BCD.

And do any processors/computers have hardware for that these days?


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

Re: [sqlite] unsubscribe

[Tim Streater]

On 22 Oct 2018, at 20:08, thomgrayr...@printeasy.net wrote:

> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Go to that web site, which is shown in every mail you have received from this 
list.





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

Re: [sqlite] SQLITE_OPEN_READONLY in PHP

[Tim Streater]

On 20 Oct 2018, at 17:05, "p...@geniais.com"  wrote:

> I'm using a C compiled program to write exclusively to a database, and a php
> script for reading only (that communicates via socket).
> I know I can use sqlite3_open_v2() with SQLITE_OPEN_READONLY in C, but there
> is a way to open a database for readonly in php (since just one C program is
> used for writing)?

Try:

http://www.php.net/manual/en/sqlite3.open.php

and look at the flags available.


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

Re: [sqlite] A couple of questions about prepared statements

[Tim Streater]

On 19 Oct 2018, at 17:33, Simon Slavin  wrote:

> On 19 Oct 2018, at 3:43pm, Tim Streater  wrote:
>
>>  is it OK to generate the myid and goodtext parts using the usual string
>> methods from my host language, leaving only badtext as a bound variable, so
>> that my prepared statement looks like this:
>> 
>>  select somecol from mytable where myid=3 and badtext=? and
>> goodtext="somegoodtext"
>
> That will work fine.

Good.

> Just a note that your string quoting characters are wrong (this
> may have been done by your email client).  They should both be
> apostrophes:
>
> select somecol from mytable
> where myid=3 and badtext=? and goodtext='somegoodtext'

No, that was my fault. Not awake enough yet.

> Presumably you will 
> CREATE INDEX t_tgb ON mytable (myid,goodtext,badtext)
>
>>  My other question relates to when the database is actually touched. Is it
>> the case that statement preparation and variable binding do not affect the
>> database itself and it's only when the prepared statement is actually
>> executed that the database is touched and might generate an SQLITE_BUSY
>> response?
>
> The database needs to be read, but not written, during _prepare_v2().  It
> won't be written until _step().  Depending on your journal mode, it's possible
> that _prepare() will be blocked because another connection is making a change.
>
> However, proper use of _timeout() will mean you will not care about either of
> the above.

OK - I'll bear all that in mind - thanks.



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

[sqlite] A couple of questions about prepared statements

[Tim Streater]

Suppose I have an SQL statement like this:

select somecol from mytable where myid=3 and badtext="usertext" and 
goodtext="somegoodtext"

Suppose further that the values of myid and goodtext are generated by reliably 
me whereas badtext is supplied externally. If I want to use a prepared 
statement here, is it OK to generate the myid and goodtext parts using the 
usual string methods from my host language, leaving only badtext as a bound 
variable, so that my prepared statement looks like this:

select somecol from mytable where myid=3 and badtext=? and 
goodtext="somegoodtext"

That would simplify my life.

My other question relates to when the database is actually touched. Is it the 
case that statement preparation and variable binding do not affect the database 
itself and it's only when the prepared statement is actually executed that the 
database is touched and might generate an SQLITE_BUSY response?

Thanks.


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

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Tim Streater]

On 10 Oct 2018, at 18:10, Warren Young  wrote:

> On Oct 10, 2018, at 10:39 AM, Eric  wrote:
>> 
>> * mailing lists come to me, I don't have to go and get them
>
> So do Fossil email alerts.

So there's an unecessary email I've just received telling me to go to the 
forum. I hate emails like that.

>> * mailing lists all work the same
>
> No, they don’t.
>
> There are many different mailing list managers, each with different
> subscription methods, unsubscription methods, password requirements, commands,
> etc.

They work well enough the same.

> On top of that, the popular mailing list managers are highly configurable, so
> you can’t even say that all GNU Mailman mailing lists work the same.
>
>> no multiple forum URLs
>
> …but multiple mailing list manager URLs instead.

But I hardly ever visit these, once I'm subscribed.

> When was the last time you used a mail client without threading?  Mail
> messages are *rarely* entirely self-contained.

All the time, actually, and I hate clients which thread for me. So I didn't 
implement that in my email client, (although I generate the headers so others 
can do it). If I want to see all sqlite emails together, I filter them into a 
mailbox. If for some reason I wanted the mails chunked together by thread, I 
can sort the mailbox by subject line.

> It’s no faster to open a mail client than it is to open a folder full of forum
> bookmarks and scan their contents.
>
> Fossil forums are especially nice in this regard, since there is currently no
> subforum feature, so you don’t have to go digging through them to find out
> what’s new.  The forum’s front page lists new posts in newest-first order,

Ah, top-posted, eh?

Bottom line: leave this list as it is.



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

Re: [sqlite] SQLite Windows GUI alternative to Excel?

[Tim Streater]

On 07 Oct 2018, at 00:18, Warren Young  wrote:

> On Oct 6, 2018, at 2:21 PM, Simon Slavin  wrote:
>> 
>>  Excel ate the financial business world because companies use Excel to solve
>> a simple problem, then add a feature, then add another feature, and keep
>> going until they have some crawling creeping horror that needs to return to
>> R'lyeh.  There is never any point in this process when a manager looks at
>> what's being done with Excel and says "Okay we need to hire a programmer to
>> turn this into a proper App.”.
>
> Up to a certain point, there’s nothing wrong with that process.
>
> One of the tasks smart management should be doing is keeping an eye on these
> guerrilla software development projects and step in when it becomes clear
> they’ve got a valuable business tool that needs to be rewritten on a stronger
> foundation to allow its continued growth and increasing value to the business.

But they don't IME. Someone hacks something together as a proof of concept, and 
management thinks that it's job done. The hack, which requires some manpower to 
use, and which is not integrated with other systems, is then handed over to 
Operations.



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

Re: [sqlite] SQLite Windows GUI alternative to Excel?

[Tim Streater]

On 06 Oct 2018, at 17:23, Warren Young  wrote:

> I guess neither of you read the article, because the point of the original
> question isn’t to get recommendations for yet another spreadsheet program,
> it’s to get a program that lets us get away from spreadsheets-as-databases,
> because when such creations get complicated, they become very difficult to
> debug.

[snip]

> What we want is a SQLite-based program along the lines of Access or FileMaker,
> preferably with some kind of cloud capability.

If you toddle over to forum.xojo.com, there seems to be a thread ("Evaluating - 
how can there be no DataGrid") asking this question, with some possible answers.


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

Re: [sqlite] --disable-threadsafe broken in 3.25.x

[Tim Streater]

On 01 Oct 2018, at 21:02, Fabrice Fontaine  wrote:

> Please find attached a patch fixing this issue. As this is my first
> contibution to sqlite, please excuse me if I made any mistakes.

Don't send attachment (not allowed), include it inline.


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

Re: [sqlite] Some queries with ORDER BY and LIMIT lock DB

[Tim Streater]

On 07 Sep 2018, at 20:20, Richard Hipp  wrote:

> On 9/7/18, Firecore  wrote:
>>
>> It's easily reproduced with the sqlite3 client and db file available here
>> (https://www.dropbox.com/s/l1xofadq7vi5vjj/lock_issue.db?dl=0):
>
> I cannot reproduce it.  It always runs instantly for me.  I also ran
> it under valgrind with no issues reported.

Running macOS Mavericks:

With SQLite version 3.14.0 2016-08-08 13:40:27 (as supplied with the OS) it 
worked instantly.

With SQLite version 3.24.0 2018-06-04 19:24:41 (downloaded from sqlite.org 
today) the reported issue occurred.



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

Re: [sqlite] Blob and Text columns: what's the difference?

[Tim Streater]

On 28 Aug 2018, at 15:36, Keith Medcalf  wrote:

> On Tuesday, 28 August, 2018 07:50, Tim Streater  wrote:

>>How does it know not to do that if I want to send some binary data to a Text
>>column?
>
> Simply because you do not request that those things be done.
>
> So, the "things" that may occur depend on the APIs you use to read/write the
> data, and the "translations" that you might have asked SQLite3 to do.  For
> example, if your database is created with the 'encoding' set to one of the
> UTF-16 variants, and you use the sqlite3_bind_text() to put "text" in that
> column that is not valid UTF-8 that can be translated to UTF-16 then
> explosions will occur.  Similarly if the database encoding is UTF-8 (the
> default) and you use sqlite3_bind_text16() to try and store UTF16 data in the
> database, and that data is not valid UTF-16, then explosions will occur.  
>
> If however, you database encoding is UTF-8 (the default) and you use
> sqlite_bind_text() to put data in the database, then SQLite3 will not "tamper"
> with your data and you may put whatever you wish in there whether it is valid
> UTF-8 or not.  Similarly if the database encoding is UTF-16 and you use
> sqlite3_bind_text16() you can put whatever you wish in there whether it is
> valid UTF-16 or not.
>
> If you use the sqlite3_bind_blob() interface to put data in the database, it
> will be stored as a blob and the "affinity" (text) expressed in the create
> table statement will be ignored since the "affinity" specified in the table
> declaration will never attempt to translate a "blob" into something else.
>
> Assuming you managed to get data into the database, then how you ask to
> retrieve it may also cause explosions to occur.  For example, you might have
> stored your binary data (that is not valid UTF-8) using sqlite3_bind_text() in
> the database where the database encoding is UTF-8.  If you try and retrieve it
> using sqlite3_column_text16() then SQLite3 will, because *YOU* requested it,
> attempt to translate the data, and explosions will occur.  Mutatis mutandis if
> *you* request other translations that are invalid (ie, from a UTF-16 encoded
> database where the data is not valid UTF-16, and you try to retrieve it
> translated to UTF-8).
>
> However, if you attempt to retrieve the data using sqlite3_column_blob() you
> will always get back exactly what is in the database without translation.  If
> you store a blob and ask for some text, explosions may occur.
>
> So in short, just make sure that you store your "blob" of data using the
> appropriate storage type (sqlite3_bind_blob) when you store it, and always
> check what storage type it is before retrieving it and use the corresponding
> sqlite3_column_(type) to get the data out, and you will be fine.  SQLite3 will
> do *NOTHING* to your data that you have not explicitly asked it to do.

Thanks. I think the above is the clue I needed. I'm not familiar with the C 
interface; I'm using Xojo for this application. They have an SQLiteBlob class 
with its own read/write methods. It should therefore suffice for me to ask on 
their forum what they do.


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

[sqlite] Blob and Text columns: what's the difference?

[Tim Streater]

What is actually the difference between a column declared as TEXT and one 
declared as BLOB in an SQLite database? What does SQLite do to textual data 
that I ask it to put into a TEXT column? How does it know not to do that if I 
want to send some binary data to a Text column?

The reason I'm interested is that I have a table with a TEXT column. For 
backwards compatibility reasons, I'd like not to change the column's type or 
even add another column with BLOB type. Up to now I've assumed that the data to 
go in the column was all ASCII or perhaps UTF-8. Now it seems that it's legal 
for it to be a mixture of encodings. So I want to treat it as binary and be 
able to just use the existing TEXT column. Anything I need to look out for?


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

Re: [sqlite] Invalid Blob Length Error Message

[Tim Streater]

On 17 Aug 2018, at 01:24, Casey Rodarmor  wrote:

> I wrote an erroneous update statement:
>
> ```
> UPDATE foo SET bar = x'01234';
> ```
>
> The error message was:
>
> ```
> unrecognized token: "x'01234'
> ```
>
> I know now that the problem was that the blob's length was not a
> multiple of two. However, the error message didn't give me an
> indication of that. Would it be possible to make the error message
> more explicit, to help users debug this error?

I expect the error message is correct, and that what you meant to write was:

update foo set bar = 0x1234;


See:



and scroll down to the part about Literal Values (Constants).




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

Re: [sqlite] No unicode characters in sqlite3 console prg

[Tim Streater]

On 11 Aug 2018, at 17:51, Lars Frederiksen  wrote:

> Is this an error of my windows 10 cmd prompt or is it a general problem that
> the sqlite3 console is not able to show unicode in the cmd-window.??
>
> No problem writing to the database and show (greek) unicode characters in
> the cmd prompt as I key them, but when I want to show the data with the SQL
> SELECT * FROM Gloser then the greek word is shown with most of its letters
> as questionmarks.

Using macOS Mavericks (10.9.5):

SQLite version 3.14.0 2016-08-08 13:40:27
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x (q text);
sqlite> insert into x values ('∑ß∂π');
sqlite> select * from x;
q 
--
∑ß∂π


Seems to work.



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

Re: [sqlite] Add Column with "If Not Exists"

[Tim Streater]

On 01 Aug 2018, at 21:06, Warren Young  wrote:

> On Aug 1, 2018, at 1:52 PM, Tim Streater  wrote:
>> 
>> I don't use the pragma since, officially, they are unsupported.
>
> “Unsupported” how?  It’s documented and part of the SQLite file header, which
> is quite stable.
>
> If you mean this is not standard SQL and thus doesn’t work on non-SQLite DBs,
> then just do what we do, ...

No, I mean this, from https://www.sqlite.org/pragma.html:

Specific pragma statements may be removed and others added in future releases 
of SQLite. There is no guarantee of backwards compatibility.





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

Re: [sqlite] Add Column with "If Not Exists"

[Tim Streater]

On 01 Aug 2018, at 20:40, Warren Young  wrote:

> On Aug 1, 2018, at 7:34 AM, Simon White 
> wrote:
>> 
>>  I would like to suggest the addition of the "If not exists" to the Add
>> Column feature of SQLite.
>
> I maintain an application that’s been through dozens of schema changes over
> its nearly quarter century of life, so let me tell you what works for us: a DB
> schema serial number.
>
> Any time the DB schema changes, we bump the schema version number and modify a
> small program we include with the software that upgrades the schema.
>
> Each schema change is contained in a single function within this program, most
> of which are just a simple CREATE or ALTER TABLE statement.  A few are more
> complex, moving data around or transforming it.
>
> If you upgrade the software on a machine running DB schema 5 to with software
> that requires DB schema 8, there are 3 steps that, if performed in order,
> always result in you running DB schema 8.  This program simply calls those
> three functions in sequence based on the old schema number and the current
> schema number.

[snip]

> Now the neat bit: SQLite already supports doing this with its user_version
> feature:
>
> https://sqlite.org/pragma.html#pragma_user_version

Yeah, I already also do all of this, although I don't use the pragma since, 
officially, they are unsupported. But I'd rather have the IF NOT EXISTS.


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

Re: [sqlite] Add Column with "If Not Exists"

[Tim Streater]

On 01 Aug 2018, at 14:34, Simon White  wrote:

> I would like to suggest the addition of the "If not exists" to the Add 
> Column feature of SQLite.  There are quite common situations where 
> ensuring a column exists is important so that an update to remote 
> devices will not fail but it is not so important that deprecated fields 
> be removed.  This is often the case with backward compatibility.  New 
> columns will not affect old systems but allows all remote devices 
> running older software to be updated using the same process as new 
> devices.  Once the hardware reaches end of life it will be replaced and 
> the new hardware will use the new columns.  So having the ability to 
> Alter the table with a series of Add Column commands ensures that the 
> new records included in the update are added to the table.  These is 
> especially true for limited remote devices where full database 
> management is not feasible.  In this scenario all that is required is 
> that the required columns exist.  So to be able to alter the table with 
> a standard SQL command is the most efficient method on such devices.  
> Developing scripts to drop and re-create and re-load tables on hundreds 
> of remote devices greatly increases the risk of failures. Having the "if 
> not exists" would remove all of this potential complexity and allow a 
> quick and easy method to ensure the column exists in the table.

I'd like to second this and for just the same reasons. Something like:

   alter table add column if not exists my_new_col ...;

I would find very helpful.


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

Re: [sqlite] column types and constraints

[Tim Streater]

On 29 Jun 2018, at 17:46, Bob Friesenhahn  wrote:

> On Fri, 29 Jun 2018, danap wrote:
>>
>> Unless your trying to create a generic user interface.
>>
>> I have spent the last month trying to solve affinity with the columns.
>> The only way it seems to me to guarantee to solve the issue is to test
>> every retrieved column value and test its affinity.
>
> Affinity is only a hint and not an assurance of anything.
>
> I solve the problem by bloating the schema with checks like this:
>
>foo integer default 1234
>   check (typeof(foo) == 'integer'),
>
> This enforces that someone can't put "Hello world" where an integer 
> belongs.
>
> Without adding all the necessary safe-guards to ensure that only valid 
> data goes into the database, sqlite puts the using application at risk 
> (security and stability) with its wishy-washy ways.

No one is forcing you, or anyone else, to use SQLite.


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

Re: [sqlite] Shouldn't have to specify primary key explicitly

[Tim Streater]

On 28 Jun 2018, at 12:48, Scott Robertson  wrote:

> SQLite is supposed to autoincrement by default when a column is defined
> as "INTEGER PRIMARY KEY" according to everything I've read. But I've
> only gotten this to work if I let SQLite create its own PK column. If I
> have an explicit PK column, I am expected to specify an ID myself. What
> am I missing? I don't know why I'm getting this error. Thanks.

You've defined the table with three cols so you have to provide three values 
unless you name the cols you wish to fill. To get SQLite to auto increment, use 
NULL as the value fo your id column:

INSERT INTO test2 VALUES (NULL, 'Fletch', 245);



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

Re: [sqlite] Mailing list shutting down...

[Tim Streater]

On 13 Jun 2018, at 12:22, Richard Hipp  wrote:

> Unfortunately, I'm going to need to shut down this mailing list due to
> robot harassment.  I am working to come up with a fix or an
> alternative now.  Your suggestions are welcomed.

Perhaps another subscription mechanism is needed, if that is their attack 
vector. Personally I'd be loath to see this list moved to a web page, for 
instance.


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

Re: [sqlite] Insert with an '

[Tim Streater]

On 11 Jun 2018, at 09:07, Peter Nacken  wrote:

> I try to insert email addresses into a table and get an error with addresses
> they have a single quotation mark ( na'm...@domain.ltd ). 
>
> Sorry I'm facing this problem for weeks, I can't find a solution.
>
> Is there a known workaround for it ?

See:



and look at question 14 and its answer.



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

Re: [sqlite] How to download SQLite for Mac?

[Tim Streater]

On 06 Jun 2018, at 17:06, Sabrina Abdul Jalil  wrote:

> I am on MAC OS Sierra VER 10.12.06. How to download SQLite?

It's already installed on your computer.



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

Re: [sqlite] Function to use to convert a text float to a float for use in ORDER BY

[Tim Streater]

On 17 May 2018, at 23:13, Keith Medcalf  wrote:

> select col1, col2 from mytable order by cast(body_size as float);

Thank you. That's much better (although abs(body_size) worked too).



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

[sqlite] Function to use to convert a text float to a float for use in ORDER BY

[Tim Streater]

My db has a table with a column defined thus:

  body_size text default '0.0'

whose purpose is to hold the size of the item that the row represents. All rows 
contain a value but as a string to one decimal place, not a number. So the 
column contains strings such as '0.0', '3.7', '22.9', etc. All are positive.

Now I want to use the body_size in an ORDER BY so I'll need SQLite to convert 
the values to a float. What will be the best function to use? I'd thought of 
this:

  select col1, col2 from mytable order by round(body_size,1)

however the doc doesn't specify whether round accepts a non-numeric argument. 
Perhaps:

  select col1, col2 from mytable order by abs(body_size)

would be better, but I wonder which is fastest (although speed may not matter 
too much, the typical select shouldn't return more than few thousand rows).


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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Tim Streater]

On 13 May 2018, at 17:01, Dennis Clarke  wrote:

> On 05/13/2018 11:57 AM, Kevin O'Gorman wrote:

> Also is that a single socket machine with a single big memory bank or
> is it NUMA and multiple sockets or is it just a single motherboard unit?

And I'd be curious to know whether memory is interleaved or not.



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

Re: [sqlite] memory leak?

[Tim Streater]

On 12 Apr 2018, at 21:34, Simon Slavin  wrote:

> On 12 Apr 2018, at 8:00pm, Warren Young  wrote:
>
>> Also: http://valgrind.org/docs/manual/faq.html#faq.pronounce
>
> I didn't know that.  So they want it to be pronounced like 'lint'.
> Interesting.

That they want it pronounced that way is a matter of indifference to me.


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

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

[Tim Streater]

On 22 Mar 2018, at 14:05, Ron Watkins  wrote:

> The file that I use ".import" on contains records like this:

> '2018-03-22 07:01:01'|2533268

> I had assumed the value was being treated as a datetime, but it looks like it
> may be treated as a string?
>
> Im a bit confused because the column definition sais "datetime", not
> "varchar", so if it's being treated as a string what can I do to fix it to be
> treated as a actual datetime datatype?

SQLite has no DATETIME or BOOLEAN datatype, see:



Personally I store all date/times as seconds since the epoch; it simplifies 
life a lot.



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

Re: [sqlite] How to optimise a somewhat-recursive query?

[Tim Streater]

On 18 Mar 2018, at 21:48, Jonathan Moules  wrote:

> CREATE TABLE lookups (
>  url_id   INTEGER  REFERENCES urls (url_id),
>  retrieval_datetime   DATETIME,
>  error_code   INTEGER,
>  is_generic_flag  BOOLEAN  -- about one in 100 will have this 
> flagged
> );

SQLite has no DATETIME or BOOLEAN datatype, see:

   

Given this, I don't know what your attempts to compare date/times will do. 
Personally I store all date/times as seconds since the epoch; it simplifies 
life a lot.



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

Re: [sqlite] UTF8 and NUL

[Tim Streater]

On 26 Jan 2018, at 18:12, Keith Medcalf  wrote:

> Actually, EOF (0xFF) *is* part of a text file, and is the byte in an ASCII
> byte-stream that indicates end-of-file.

First I've heard of that. Which systems did that then? EOF is normally 
indicated by the file system, not by file data.


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

Re: [sqlite] SQLite version 3.22.0

[Tim Streater]

On 22 Jan 2018, at 21:21, Nnaemeka R Egudu  wrote:

> Please unsubscribe me from  this mailing list.
> Thanks.

Do it yourself using this URL:

> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Tim Streater]

On 22 Dec 2017, at 09:57, Hick Gunter  wrote:

> The problem lies in your reliance on unspecified behaviour. Unspecified
> behaviour is allowed to change.
>
> I am sure you have read (and ignored) the following guarantee taken from
> http://sqlite.org/c3ref/column_name.html :

My questions are these:

1) That the name without AS is documented as being unspecified, is that the 
case with all/most SQL systems or is it SQLite specific?

2) I don't see this issue mentioned when I read the PHP documentation about 
their SQLite interface, nor do I see it in the Xojo docs about *their* 
interface either. I assume their interfaces are not rewriting SELECT statements 
to include AS for every column selected, so should they be warning their users 
about it?


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

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

[Tim Streater]

On 22 Nov 2017, at 19:49, "Niall O'Reilly"  wrote:

> On 21 Nov 2017, at 16:27, Drago, William @ CSG - NARDA-MITEQ wrote:
>
>>  Please, not a forum. The email list is instant, dynamic, and convenient. I
>> don't think checking into a forum to stay current with the brisk activity
>> here is very practical or appealing.
>
> I agree with Bill on this.
>
> It seems to me that the idea of re-architecting such a useful communications
> channel as this mailing list on account of a cluster of false positives raised
> by a single provider's triage system would best be characterized as an example
> of "the tail wagging the dog".

Well quite. My advice to anyone whose ISP or gmail is doing unwanted spam 
filtering is to switch elsewhere.

And there's no need for 'likes' and related nonsense on a technical list.


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

Re: [sqlite] Best way to develop a GUI front-end

[Tim Streater]

On 13 Nov 2017, at 21:54, Balaji Ramanathan  wrote:

> Is there a third party free tool like MS Access that would allow me to
> connect to a SQLite db in the back-end and enable me to create a custom
> front-end to it with forms and reports?  All my searches for this kind of
> tool only lead me to tools like SqliteStudio, which is a GUI front end for
> SQLite, but not a programmable one like I want.  If anyone can point me
> towards a programmable GUI front-end development tool (preferrably
> open-source, but just free and well-maintained is sufficient), I would
> appreciate it greatly.

There is Xojo (www.xojo.com) which has SQLite built in and has reports (which I 
have never used). It's cross-platform and you create your own GUI front end 
with windows, buttons etc. It's free to use for development, but if you want to 
compile and build a stand-alone application (.exe or .app) then you buy a 
licence. There is a Lite licence to build for one platform which is not a high 
cost.



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

Re: [sqlite] SQLite DB - DateTime field values are not consistent

[Tim Streater]

On 02 Nov 2017, at 12:23, Rajat Pal  wrote:

> In SQLite database, All the values of datetime fields are saved as text
> without any format check. We can save any text value in the datetime column.
> This behavior is creating problem for us because for different regional
> settings,  datetime values are getting saved in different format.

As sqlite has no DATETIME field, what you actually get is a TEXT field in which 
you can store anything.

> For example, if the regional format is English(United States), the value is
> saved as "2017-09-21 00:00:00" but if the regional format is Finnish(Finland),
> the value is saved as "2017-10-27 00.00.00". So some dates have colon(:) as
> time separator and some dates have dot (.) as time separator.

In whatever language you use to call sqlite, you should write a general 
function that can take anything that looks like a date/time and convert it to 
seconds since the epoch (or any other fixed historical moment), then store that 
value as an integer using sqlite. If you are using PHP, the strtotime() 
function does a very good job for that purpose.

Then when you retreive it, convert it to a time/date in the form that the local 
user expects.


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

Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

[Tim Streater]

On 11 Oct 2017, at 18:53, R Smith  wrote:

> Yes. When you concatenate/add a string and integer together some SQL 
> engines will try to give a sensible result, so that '5' + 3 will yield 8 
> because 3 is INT and it reckons that '5' probably meant 5 since it is 
> added to another INT and the 5 doesn't have an explicit type. But, if 
> you force one of the terms to be string, such as CAST( 5 AS TEXT) or 
> strftime(%s,5) or use the value as a parameter to a function that 
> expects a specific type, then the engine might assume stuff, or try to 
> make sense of it, but in general when you start these shenanigans you 
> are on thin ice over "UNDEFINED" territory, which is what bit the OP 
> since the result in one version of SQlite differed from another version 
> (which the devs might adjust, but it's not a bug since mixing types is 
> not strictly supported).

All my times and dates are stored as seconds since the epoch (in a double for 
reasons that escape me at the minute). As a result I've never had a problem 
trying to compare them.

My philosophy is: internal format, seconds. External format (for display 
purposes), convert to the format the user wants.



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

Re: [sqlite] My 1st C++ SQLite3 program

[Tim Streater]

On 27 Aug 2017, at 15:35, Papa  wrote:

> First and foremost, I'd like to thank everybody for your replies.
> Although I have sound knowledge and understanding of C++ [ that can be 
> debatable ] and still remember a little how C works, I have no Idea how 
> to utilize the API of SQLitle3. Therefore, it is very difficult for me 
> to apply the advice given by you all, so, please, using the code I have 
> provided in the OP, how can I implement your advice?
>
> Again, thanks ya'll for all the help.
>
>
> On 2017-08-26 11:28 PM, Simon Slavin wrote:
>>
>> On 27 Aug 2017, at 2:34am, Papa  wrote:
>>
>>> Why do I get this error message?
>>  Try having the program using file calls to open a simple text file in the
>> same directory.  Does that succeed ?

Do what Simon suggests here. It does not require the SQLite3 API.


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

Re: [sqlite] What is the exact syntax for SELECT MAX in PHP?

[Tim Streater]

On 5 Aug 2017, at 9:35, Edmondo Borasio  wrote:

> $results = $db->query("SELECT MAX(ID) FROM Table")->fetchArray();
> $Highest_ID = $results['ID'];
> var_dump($Highest_ID);
>
> I get NULL

Try:

$results = $db->query("SELECT MAX(ID) as mx FROM Table")->fetchArray();
 $Highest_ID = $results['mx'];
 var_dump($Highest_ID);




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

Re: [sqlite] UPDATE database using parameters

[Tim Streater]

On 21 Jul 2017, at 11:14, Rowan Worth <row...@dug.com> wrote:

> On 21 July 2017 at 17:50, Tim Streater <t...@clothears.org.uk> wrote:
>
>>$sql = "UPDATE Movies SET name = '$newname' where id=$newid";
>>
>> Personally I don't like forcing PHP to scan strings so I tend to use
>> concatentation, rewriting the last of these as:
>>
>>$sql = 'UPDATE Movies SET name = '' . $newname . '' where id=' .
>> $newid;
>>
>> but that's just a personal style preference.
>>
>
> Either way, I hope for your sake no one releases a movie called:
> Avengers!'; Drop Table Movies; --

That’s going to be a problem anyway if your strings can legally contain 
single-quotes, never mind little Bobby Tables. Sanitisation is called for.

> As Simon says, better to use something like $sql = 'UPDATE Movies SET name
> = ? WHERE id = ?' and bind your parameters in.

Not disagreeing, but I was operating on the first-things-first principle.


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

Re: [sqlite] UPDATE database using parameters

[Tim Streater]

On 21 Jul 2017 at 10:04, Edmondo Borasio  wrote: 

> Hi Tim.
>
> It *almost* works..
>
>$DbItemNameTest = "new name";
>$hIdTest = "1";
>
>$db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest .
> '\' WHERE hospital_ID="1"'); //IT WORKS
>   $db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest . '\'
> WHERE hospital_ID=' . $hIdTest); //IT DOESN'T WORK
>
> The second one doesn't work even if I change hIdTest to integer.
>
>$hIdTest = 1;

Well, is hospital_ID stored in your database as a string or as an integer?

If it's an integer, then having $hIdTest as an integer should work. If it's a 
string you would leave $hIdTest as a string and change the sql to:

   ... WHERE hospital_ID=\'' . $hIdTest . '\'');

(I know SQLite can be cleverer than that in doing conversions but I've tried 
never to rely on that so am unfamiliar with it)

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

Re: [sqlite] UPDATE database using parameters

[Tim Streater]

On 21 Jul 2017 at 09:58, Simon Slavin  wrote: 

> On 21 Jul 2017, at 7:33am, Edmondo Borasio  wrote:
>
>> $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');
>
> Those are the wrong quotes.  SQLite requires single quotes around literal
> strings, and expects no quote around numbers.  And PHP doesn’t care which
> quotes you use as long as they match.

Actually PHP does care. It will not look inside single quoted strings for 
special escaped characters or variables for which to substitute values. It 
*will* do so for double-quoted strings.

So:

   echo 'Hello\n';

will not do the same thing as:

   echo "Hello\n";

The latter will put out a newline at the end of Hello whereas the former will 
put out two characters (\ and n).

Similarly, variable substitution will not happen here:

   $sql = 'UPDATE Movies SET name = $newname where id=$newid';

but will do here:

   $sql = "UPDATE Movies SET name = $newname where id=$newid";

So PHP is happy, but SQLite is not - you'd need:

   $sql = "UPDATE Movies SET name = '$newname' where id=$newid";

Personally I don't like forcing PHP to scan strings so I tend to use 
concatentation, rewriting the last of these as:

   $sql = 'UPDATE Movies SET name = \'' . $newname . '\' where id=' . $newid;

but that's just a personal style preference.

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

Re: [sqlite] UPDATE database using parameters

[Tim Streater]

On 21 Jul 2017 at 07:33, Edmondo Borasio  wrote: 

> I am updating a record of a SQLite database as follows:
>
> $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');
>
> but instead of using name and ID I want to use some variables, $NewItemName
> and $hId.
>
> Entering the variables as they are won't work. Neither using escape
> characters like \"$DbItemName\" and \"$hId\".

It would help if you said what language you are using.

In PHP, one could do this:

  $db->exec ('UPDATE Movies SET name = \'' . $DbItemName . '\' WHERE ID=' . 
$hId);

or perhaps:

  $db->exec ("UPDATE Movies SET name = '$DbItemName' WHERE ID=$hId");


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

Re: [sqlite] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

[Tim Streater]

On 29 Jun 2017 at 08:01, Eric Grange  wrote:

>> The sender, however, could be lying, and this needs to be considered
>
> This is an orthogonal problem: if the sender is sending you data that is
> not what it should be, then he could just as well be sending you
> well-encoded and well-formed but invalid data, or malware, or
> confidential/personal data you are not legally allowed to store, or, or,
> or... the list never ends.
>
> And generally speaking, if your code tries too hard to find a possible
> interpretation for invalid of malformed input, then you are far more likely
> to just end up with processed garbage, which will make it even harder to
> figure out down the road where the garbage in your database originated from
> (incorrect input? bug in the heuristics? etc.)

It will end up in the user's database. No heuristics are involved; I can do no 
more than believe what the sender tells me. The IDE I am using does at lest 
allow, in its base64-decode, that I request lossy conversion in the case of bad 
input.

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

Re: [sqlite] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

[Tim Streater]

On 28 Jun 2017 at 14:20, Rowan Worth  wrote:

> On 27 June 2017 at 18:42, Eric Grange  wrote:
>
>> So while in theory all the scenarios you describe are interesting, in
>> practice seeing an utf-8 BOM provides an extremely
>> high likeliness that a file will indeed be utf-8. Not always, but a memory
>> chip could also be hit by a cosmic ray.
>>
>> Conversely the absence of an utf-8 BOM means a high probability of
>> "something undetermined": ANSI or BOMless utf-8,
>> or something more oddball (in which I lump utf-16 btw)... and the need for
>> heuristics to kick in.
>>
>
> I think we are largely in agreement here (esp. wrt utf-16 being an oddball
> interchange format).
>
> It doesn't answer my question though, ie. what advantage the BOM tag
> provides compared to assuming utf-8 from the outset. Yes if you see a utf-8
> BOM you have immediate confidence that the data is utf-8 encoded, but what
> have you lost if you start with [fake] confidence and treat the data as
> utf-8 until proven otherwise?

1) Whether the data contained in a file is to be considered UTF-8 or not is an 
item of metadata about the file. As such, it has no business being part of the 
file itself. BOMs should therefore be deprecated.

2) I may receive data as part of an email, with a header such as:

   Content-type: text/plain; charset="utf-8"
   Content-Transfer-Encoding:  base64

then I interpret that to mean that the attendant data, after decoding from 
base64, is it to be expected to be utf-8. The sender, however, could be lying, 
and this needs to be considered. Just because a header, or file metadata, or 
indeed a BOM, says some data or other is legal utf-8, this does not mean that 
it actually is.


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

Re: [sqlite] Error message on insert

[Tim Streater]

On 19 Jun 2017, at 12:2, Keith Medcalf  wrote:

> insert into filters (absid, filter_name, enabled, filter_order) 
> values (null, 'Untitled filter', 0, coalesce((select max(filter_order) from
> filters)+1,1))
>
> If you want the filter_order by filter_name then you would need:
>
> insert into filters (absid, filter_name, enabled, filter_order) 
> values (null, 'Untitled filter', 0, coalesce((select max(filter_order) from
> filters where filter_name='Untitled filter')+1,1))
>
> (you need the coalesce to make sure the initial value is 1, unless you have
> defined the column in the table with a default of 1)
>
> -- 
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

Thanks, Keith and Chris. I had actually tried a "select max(…", but without, it 
would seem, enough brackets.


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

[sqlite] Error message on insert

[Tim Streater]

I want to insert a new row in my table, and while doing so setting a column to 
one more than the maximum value of that column, thus:

   insert into filters (absid, filter_name, enabled, filter_order) values 
(null, 'Untitled filter', 0, max(filter_order)+1)

However I get "Error: no such column: filter_order”. I had a look at the syntax 
diagram for insert which would seem to permit the above.

I can do this in code anyway so it’s not a show stopper, but what have I done 
incorrectly? My IDE appears to use sqlite 3.14.1.

Thanks.


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

Re: [sqlite] SQLite DB on external USB HD - is it safe?

[Tim Streater]

On 11 May 2017, at 23:35, Simon Slavin  wrote:

> It’s the word 'remote' which is not clearly explained.  In the SQLite
> documentation, the word 'remote' means you’re accessing it using a networking
> API not a file system API.  It’s the network storage APIs which tend to do
> locking badly.

Local: disk I/O is under the control of the computer you are working on.

Remote: disk I/O is under the control of another computer. Your computer makes 
requests to the other computer, that it perform I/O operations on behalf of 
your computer. But the other computer decides when and how to make them.



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

Re: [sqlite] SQLite log - automatic index on sqlite_sq_25FA456860(ID) (284)

[Tim Streater]

On 3 May 2017, at 18:16, Olivier Mascia  wrote:

> Would there be a cheap way for SQLite to log some more user-realm context
> about these?
> Maybe simply emitting a second call to the log function right after these
> messages code 284 where the third parameter (msg) would simply point at the
> prepared statement SQL text (just as sqlite3_sql(sqlite3_stmt*) returns?

Why don’t you write your own wrapper functions for the base SQLite calls. My 
set of these functions logs where each call occurs and what the SQL was.


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

Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?

[Tim Streater]

On 15 Apr 2017 at 22:17, Simon Slavin  wrote: 

> On 15 Apr 2017, at 9:14pm, petern  wrote:
>
>> Yes, please include it in the FAQ
>
> It’s not a FAQ.  Not on this list, at least.  I would argue against it.

Well he meant on the sqlite website. And if it's not a FAQ then it's 
nonetheless useful info, so perhaps what the sqlite website might have is an 
LFAQ page (Less Frequently Asked Questions).

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

Re: [sqlite] Testing sqlite db to see if exists & ready

[Tim Streater]

On 06 Apr 2017 at 19:54, Jens Alfke  wrote: 

> PS: Tim, for some reason your mail client (iLetter) is sending replies without
> an In-Reply-To header, which breaks up the threading (at least in my mail
> client) making it very hard to follow. There’s probably not a way for you to
> change that, but maybe you could send them a bug report?

I am actually the author of iLetter (the databases I was referring to upthread 
were users' mailboxes). I've never actually (ever) paid attention to the 
In-Reply-To: header but I can look at adding that once I find out what it is.

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

Re: [sqlite] Testing sqlite db to see if exists & ready

[Tim Streater]

On 06 Apr 2017 at 16:10, Simon Slavin <slav...@bigfraud.org> wrote: 

> On 6 Apr 2017, at 4:04pm, Tim Streater <t...@clothears.org.uk> wrote:
>
>> On 06 Apr 2017 at 15:33, Simon Slavin <slav...@bigfraud.org> wrote: 
>>
>>> After touching, try opening the file and issuing a CREATE TABLE command. 
>>> See whether it works or gives an error.
>>
>> The command works and the file goes from 0 to 8k bytes.
>
> Right.  So you can’t tell if a file is a SQLite database or not just by
> opening it using the SQLite API.  It might be a blank file that another
> program is intending to use for something else.
>
> If you’re trying to find out whether a file with a certain name exists and
> is a SQLite database, don’t open the file using the SQLite API.

I can tell if it's an sqlite database with the characteristics I am looking 
for, and the tests I do don't interfere with the file. That's all I care about.

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

Re: [sqlite] Testing sqlite db to see if exists & ready

[Tim Streater]

On 06 Apr 2017 at 15:33, Simon Slavin <slav...@bigfraud.org> wrote: 

> On 6 Apr 2017, at 2:44pm, Tim Streater <t...@clothears.org.uk> wrote:
>
>> That would appear not to be the case. Under OS X 10.9.5, I touched a
>> non-existent file and then using sqlite3.app did:
>>
>> .schema<--- gave nothing
>> select version from globals;   <--- gave "Error: no such table"
>>
>> My file stayed at zero bytes long.
>
> After touching, try opening the file and issuing a CREATE TABLE command.  See
> whether it works or gives an error.

The command works and the file goes from 0 to 8k bytes.


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

Re: [sqlite] Testing sqlite db to see if exists & ready

[Tim Streater]

On 06 Apr 2017 at 13:19, Simon Slavin <slav...@bigfraud.org> wrote: 

> On 6 Apr 2017, at 12:11pm, Tim Streater <t...@clothears.org.uk> wrote:
>
>> When my app starts, I check that the file in question actually *is* a
>> database by doing some simple steps like open, selects from important tables,
>> and a read/write to a globals table in the database that contains, for
>> instance, the version number.
>
> Don’t do this.  Because if the file isn’t there, or if the file is there
> but has zero length, SQLite will turn it into a SQLite file and then return
> results which don’t let you tell whether the file was already there or just
> created.  And you probably don't want this.

That would appear not to be the case. Under OS X 10.9.5, I touched a 
non-existent file and then using sqlite3.app did:

.schema<--- gave nothing
select version from globals;   <--- gave "Error: no such table"

My file stayed at zero bytes long.

I then added a bit of text to the file and repeating the exercise:

.schema<--- gave nothing
select version from globals;   <--- gave "Error: file is encrypted or is not a 
database"

The file was not modified by these actions.

> Instead use PHP functions to check that the file exists using PHP function
> "file_exists()" and then using fread() to read the first 16 bytes from it. 
> Those 16 bytes should be "SQLite format 3" followed by a 0x00 byte for a
> string terminator.

Actually I am scanning the directory and examining all the files there. The 
sequence of tests is: open, read from the two expected tables, write back to 
one of them. This confirms that the file is a database, it's one I'm looking 
for, it has the expected tables, and is writeable too. I keep a database of 
such files which is then re-populated with successful entries.

This sequence was subject to careful testing when I first wrote it. Each step 
is under try/catch and I only proceed to the next step if previous ones 
succeeded.

> I’m not sure what you mean by "unconfigured" so I’ll let other people
> write about that, or you can post to clarify.

I'm not sure either, better ask the OP.

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

Re: [sqlite] Testing sqlite db to see if exists & ready

[Tim Streater]

On 06 Apr 2017 at 11:28, Clemens Ladisch  wrote: 

> dave boland wrote:
>> Being a little paranoid, I like to insure that the db file exists
>
> SQLite automatically creates an empty DB if you try to open
> a nonexistent file, so you do not actually need to do anything.
>
>> and what state it is in (unconfigured, so needs to be made
>> ready; or ready to accept data (or be read)).  How do I do that?
>
> Store a DB version number somewhere.  (You can do it like Android and use
> PRAGMA user_version, or use an entry in some table.)  If the version
> number is not high enough, you have to create or update the database.
> Do everything in a transaction to prevent a partially-created/updated
> database:

When my app starts, I check that the file in question actually *is* a database 
by doing some simple steps like open, selects from important tables, and a 
read/write to a globals table in the database that contains, for instance, the 
version number. I'm using PHP for this and it is a good use for try/catch at 
each step. If the step succeeds, I pass to the next. Since the database (one of 
many, in fact) belongs to the user, I can't hide it away somewhere. It also 
allows the user to add possibly older versions of the database file there, or 
completely other files such as READMEs. Files such as the latter will fail at 
an early step (usually with: file is not an SQLITE database) but I don't care 
why; they either pass or fail, and if they fail, the app then ignores them.

Older versions of the db can be spotted by the version number in the globals 
table (as Clemens say above); they can then be silently upgraded to the current 
version. This will happen if I have, for instance, added a column or two to 
support some new feature.

I don't user PRAGMA user_version because I keep reading that the continued 
existence of any particular PRAGMA is completely un-guaranteed.


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

Re: [sqlite] Why isn't my time formatting working?

[Tim Streater]

On 08 Mar 2017 at 20:40, Paul Sanderson  wrote: 

> The vast majority of dates I see in SQLite databases are unix epoch integer
> times (seconds since 1/1/1980) with unix milli seconds a close second.
> Efficient to store, sort and do date arithmetic on but need to be converted
> to display.

This is also what I do; seconds since the epoch. I can't imagine string dates 
or times as strings. You're also going to need to give the user the option to 
select their date/time format, too, so converting to display should be done at 
display time, not before.

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

Re: [sqlite] Thread safety of serialized mode

[Tim Streater]

On 16 Feb 2017 at 18:30, James K. Lowden  wrote: 

> On Tue, 14 Feb 2017 17:05:30 -0800
> Darren Duncan  wrote:
>
>> There is nothing inherently wrong with threads in principle
>
> What's inherently wrong with threads in principle is that there is no
> logic that describes them, and consequently no compiler to control that
> logic.  

[snip remainder of long whinge about threads]

Sounds, then, like I'd better eliminate threads from my app. In which case when 
the user initiates some action that may take some minutes to complete, he can 
just lump it when the GUI becomes unresponsive. That OK with you? Can I point 
the user your way when he gives me grief about it? Or should I just say that 
no, he can't have a responsive GUI under those conditions because some guy on 
the Internet says so?

Well that ain't gonna happen. I'll just bring my 50 years experience of writing 
software to the table, including threaded apps for PDP-11s and VAXes. It's 
called debugging.

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

Re: [sqlite] Datatype for prices (1,500)

[Tim Streater]

On 30 Nov 2016 at 14:42, Werner Kleiner  wrote: 

> Hello,
> there is a small application which uses MYSQL db and can also switch to sqlite
> In MySQL a table "prices" has a column "basicprice" which is decimal(7,4)
> I can store a price in form 1.500 there.
> Same table and same columns in sqlite with datatype float(7,4) stores
> this value like
> 1.5
> It would be nice if there is a way to store in the same way as in
> MySQL with filling zeros.
>
> Is this not possible with sqlite?
> I also tried type decimal(7,4)
> Only with 1,500 the zeros are there, but I do not want to store a
> comma in the db

No such thing as decimal(7,4) in SQLite.

Have a look at:

http://www.sqlite.org/datatype3.html


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

Re: [sqlite] converting unix10 and unix13 dates in the same column

[Tim Streater]

On 29 Sep 2016 at 14:14, Paul Sanderson  wrote: 

> I have a table with dates in different formats, either 10 digit or 13
> digit unix dates
>
> 1234345087123
> 1234567890
> 1432101234
> 1456754323012

Are these strings or numbers? What is your SQL to get these into the table and 
what is the schema for dt?

(Me, I convert all dates into seconds-since-the-epoch, never had a problem).



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

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

[Tim Streater]

On 06 Sep 2016 at 11:02, Eric Grange  wrote: 

> Well, I guess I am in the minority using a tabbed browser then :)
>
> In tabbed browsers, all browser tabs share the same window width, and it is
> quite impractical to resize the browser every time you switch a tab.

Yes, I do the same. I have three Safari windows open, typically, each with 
several tabs. One for general browsing (BBC News, Twitter, etc), and each of 
the others is project specific with some number of tabs open. And I use a cheap 
clamshell mobile phone which is normally switched off, so the issue being 
debated doesn't really affect me except to the extent that I wouldn't want the 
SQLite site to look worse on a desktop.

Is there a reason why some of the links on the front page of the draft site 
have been styled to look like buttons? A button, IMO, should do something, not 
take you somewhere.

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