On Sun, 12 Jan 2020 16:24:53 -0700
"Keith Medcalf" wrote:
> while one may be tempted to claim that "consistency is the hobgoblin
> of little minds"
You might have forgotten that the much overpraised Ralph Waldo specified
"a foolish consistency". He only meant: don't try to hang your hat on
On Sun, 12 Jan 2020 15:48:05 -0700
"Keith Medcalf" wrote:
> SQL is simply a Structured Query Language that can be overlaid on a
> variety of datastore models, one of which is the Relational model.
I think that's misleading, Keith, for a couple of reasons.
SQL was invented by IBM expressly
On Monday, 13 January, 2020 06:36, Dominique Devienne
wrote:
> Please remind me, is the encoding a "client-side" setting, or also a
> "server-side" (i.e. stored) setting?
"pragma encoding " sets the internal storage format for text encoding in a
database (on the "inside SQLite" side of the
fined. "
-Original Message-
From: sqlite-users On Behalf Of
Barry Smith
Sent: Monday, January 13, 2020 1:54 PM
To: SQLite mailing list
Subject: Re: [sqlite] Unexplained table bloat
On the original topic...
How does one end up with a database in this state? I.e with a binary value that
con
On the original topic...
How does one end up with a database in this state? I.e with a binary value that
contains 0x00 bytes followed by other bytes but a type of TEXT?
If the definition of a text string in SQLite is that it ends at the first 0x00
byte, then it seems that anything stored as a
On 13 Jan 2020, at 9:26am, Dominique Devienne wrote:
> Which implies length(text_val) is O(N), while
> length(blob_val) is O(1),
> something I never quite realized.
For this reason, and others discussed downthread, some languages which store
Unicode strings store the number of graphemes as
On Mon, Jan 13, 2020 at 1:10 PM Keith Medcalf wrote:
> If the register object contains "text" and you cast it to a blob (remove the
> text affinity) you are left with just the bag-o-bytes, and length() will
> return the size of the bag encoded in the register. If the data in the
> register is
On 1/13/20 5:24 AM, Dominique Devienne wrote:
On Mon, Jan 13, 2020 at 11:07 AM Keith Medcalf wrote:
On Monday, 13 January, 2020 02:27, Dominique Devienne
wrote:
I'd vote for a lengthof(col) that's always O(1) for both text and blob
So what should lengthof(something) return the number of
If the register object contains "text" and you cast it to a blob (remove the
text affinity) you are left with just the bag-o-bytes, and length() will return
the size of the bag encoded in the register. If the data in the register is
other than type "text" then it must be converted to text
On 2020/01/13 12:24 PM, Dominique Devienne wrote:
Bytes of course. Of the data stored, i.e. excluding the header byte
I checked, I was apparently correct about the casting. This following
extract from a DB I got from a forum member with Greek-to-Danish
translations where I added length
On 2020/01/13 12:24 PM, Dominique Devienne wrote:
On Mon, Jan 13, 2020 at 11:07 AM Keith Medcalf wrote:
On Monday, 13 January, 2020 02:27, Dominique Devienne
wrote:
I'd vote for a lengthof(col) that's always O(1) for both text and blob
So what should lengthof(something) return the number
On Mon, Jan 13, 2020 at 11:07 AM Keith Medcalf wrote:
> On Monday, 13 January, 2020 02:27, Dominique Devienne
> wrote:
> >> I'd vote for a lengthof(col) that's always O(1) for both text and blob
>
> So what should lengthof(something) return the number of bytes in the
> 'database encoding' or
On Monday, 13 January, 2020 02:27, Dominique Devienne
wrote:
>On Fri, Jan 10, 2020 at 7:03 PM 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
On Fri, Jan 10, 2020 at 7:03 PM 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.
On 2020/01/13 12:25 AM, Tom Browder wrote:
On Sun, Jan 12, 2020 at 14:05 Keith Medcalf wrote:
Close, but no banana. Every value has a type. A column may contain
multiple values (as in one per row)
Thanks, Keith.
I assume that is just for SQLite, or am I wrong again?
You are not
On Sunday, 12 January, 2020 15:31, Simon Slavin wrote:
>You're generally right. SQLite always uses affinities (more or less
>'weak typing') rather than strong typing. I don't know of any other SQL
>implementations which allow this without explicit declaration, and most
>don't allow it at all.
On Sunday, 12 January, 2020 15:29, Richard Damon
wrote:
>On 1/12/20 5:25 PM, 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?
>>>
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
On 12 Jan 2020, at 10:25pm, Tom Browder wrote:
> I assume that is just for SQLite, or am I wrong again?
You're generally right. SQLite always uses affinities (more or less 'weak
typing') rather than strong typing. I don't know of any other SQL
implementations which allow this without
On 1/12/20 5:25 PM, 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
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)
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). Therefore each of those values has a type, which
may be
Am I missing something? I thought every column has to have a type?
-Tom
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
:39:43 UTC 2020
kevin@KCYDell:/mnt/KCY/KCYDocs$
Message: 6
Date: Fri, 10 Jan 2020 08:48:21 -0500
From: Ryan Mack
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Unexplained table bloat
Message-ID:
<
CABhGdGRbR1kT+3_BU6ob9L7tpSPZ09HJn=ofPyK6OXvgQK=_...@mail.gmail.com>
On 1/10/20 2:24 PM, Tim Streater wrote:
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
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?
>
>>>
On 1/10/20 1:43 PM, 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
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
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
On Friday, 10 January, 2020 10:50, Dominique Devienne :
>On Fri, Jan 10, 2020 at 4:30 PM Richard Hipp wrote:
>> length() on a BLOB should show the number of bytes in the BLOB.
>> length() on a string should show the number of *characters* (not
>> bytes) in the string up through but not
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.
--
On Fri, Jan 10, 2020 at 4:30 PM Richard Hipp wrote:
> length() on a BLOB should show the number of bytes in the BLOB.
>
> length() on a string should show the number of *characters* (not
> bytes) in the string up through but not including the first
> zero-character. It is possible to have
On 10 Jan 2020, at 3:11pm, Ryan Mack wrote:
> OK, I think I've got a better sense now. Hex encoding the column shows that
> there's actually a huge amount of data stored in there. For some reason
> length() isn't revealing it even if the column type is blob. Dumping and
> restoring the table
On 1/10/20, Ryan Mack wrote:
> OK, I think I've got a better sense now. Hex encoding the column shows
> that there's actually a huge amount of data stored in there. For some
> reason length() isn't revealing it even if the column type is blob.
> Dumping and restoring the table is truncating the
OK, I think I've got a better sense now. Hex encoding the column shows
that there's actually a huge amount of data stored in there. For some
reason length() isn't revealing it even if the column type is blob.
Dumping and restoring the table is truncating the data.
On Fri, Jan 10, 2020 at 9:58 AM
Accidentally sent my first reply direct, responding to the list. I'm
now wondering if there's a lot of binary data hidden in each row.
Trying to figure out how to determine that if length() doesn't show
anything.
Prior response:
An excellent idea, thank you :-) . The output is included below.
On 1/10/20, Ryan Mack wrote:
>
> I'm trying to understand unexplained table bloat
The sqlite3_analyzer command-line utility program (available in the
"Precompiled binaries" bundles on the https://sqlite.org/download.html
page) is designed to help understand these kinds of problems. Please
run
Thank you, I was unaware of the integrity_check pragma. It returns OK
for the database in question.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
On 10 Jan 2020, at 2:06pm, David Raymond wrote:
> Well something's weird anyway. When I open it with the command line tool it
> queries it just fine
Did you run an integrity_check on the database ? It looks from your posts as
if it's corrupt.
___
AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Unexplained table bloat
Hi list,
I'm trying to understand unexplained table bloat I found in what should be
a very small table in an old database file. If you dump/restore the
database, the problem goes away. If you duplicate the table, the proble
Hi list,
I'm trying to understand unexplained table bloat I found in what should be
a very small table in an old database file. If you dump/restore the
database, the problem goes away. If you duplicate the table, the problem
propagates. Schema:
CREATE TABLE copied(
id_local INT,
41 matches
Mail list logo
