Re: [sqlite] compressed sqlite3 database file?
On Thu, 11 Apr 2019 18:45:01 -0600 Warren Young wrote: > Sure, but what *is* on the disk after a crash is always consistent > with ZFS, so any decent database engine can recover. It's been some years, but I saw a presentation about running Postgres on ZFS. Every "victory" was a way to compensate for a feature of ZFS. None of the problems surmounted are presented by normal filesystems. The simple fact is that ZFS does nothing for the DBMS. As you acknowledge, it does complicate matters. And the features ZFS supplies slow down the DBMS by stealing I/O cycles that the DBMS could otherwise use productively. > > It's really not the ideal substrate for a system that takes its > > fsyncs seriously. > > You know, I?ve just realized that it?s been a really long time since > I?ve heard anyone seriously talk about running databases on raw > storage. I don't know what you mean by "raw" storage. A storage substrate that really syncs when it's told to and did do when it says it did doesn't have to be a locally attached SATA drive. It could be a SAN. It just has to be a yankee at the end of a dirt road: simple and honest. ;-) > I?ve even heard of people successfully using ZFS snapshots to make > live, continuous DB replications from one site to another for fast > failover. No doubt. But absence of proof is not proof of absence: just because it seems to work doesn't mean it can be depended on. If the DBMS developer can't vouch for it, it's a sure bet it's depending on at least one fortuitous coincidence. > It calls into question how important, relatively speaking, > lack of mediation is in system storage design. We mustn't place much faith in common (a/k/a "best") practices, viz: > Of course raw storage isn?t the main alternative to ZFS. It?s LVM+md > +XFS and similar lash-ups, which are even worse in this regard. Exactly. The simple understanding that a DBMS has many of the attributes of an operating system, and therefore needs direct access to the hardware, is lost on many (I would say most) system administrators and not a few database administrators. Now if you'll excuse me I have some kids to chase off my lawn. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with SELECT by rowid after INSERT
On Fri, 12 Apr 2019 11:40:13 -0400
Jim Dossey wrote:
> CREATE TABLE "sessiond" (
> "journal" VARCHAR(4) DEFAULT '' NOT NULL,
> "session" VARCHAR(16) DEFAULT '' NOT NULL,
> "pid" INTEGER DEFAULT 0 NOT NULL,
> rowid INTEGER PRIMARY KEY
> );
Although it has nothing to do with the problem you posed, at some
point you might want to reconsider this design.
A row in this table is allowed to exist even if contains no
information. The journal and session can be empty strings, and the pid
0 (invalid). That, in fact, is the default.
I assume the rowid is meaningless, just a way to refer to the row from
another table.
The table has the hallmark of an application design that inserts "empty
records" and updates them. That's a whole other ball of wax, and I
don't want to critique an assumption. But it's easy to see how that
kind of design is inefficient and defeats features provided by the
DBMS.
The DEFAULT '' NOT NULL construct is particularly pernicious, because
NULL is useful in ways a zero-length string is not. NULL can always
be converted to something else with coalesce, and is usually obvious
when interrogating the database interactively. The same cannot be said
of zero-length strings.
Instead of allowing any number of meaningless duplicate rows, why not
let the DBMS do a little work for you? For example:
CREATE TABLE sessiond (
journal VARCHAR(4) NOT NULL,
session VARCHAR(16) NOT NULL,
pid INTEGER NOT NULL check (pid > 0),
primary key( pid, journal, session )
);
If the rowid actually helps, sure use it. If it's better as the
primary key, that's fine; it's an arbitrary choice, logically
speaking. But the {pid, journal, session} tuple should be unique
regardless. If not, what to 2 identical rows represent, and how are
they different? (I don't need to know, but you do.)
When you need to track two indistigushable events, the solution isn't
to allow two undifferentiated rows with an arbitrary discriminator.
The solution is to add a "quantity" column, and count them. It's easy
to delete the row when quantity reaches zero, or cull zero-quantity
rows on selection.
For extra credit, add CHECK constraints to enforce the lengths of
journal and session.
With a table like that, you can prevent applications from inserting
nonsense into the database. I've used that strategy to good effect.
Once the developers have agreed they don't want garbage in the
database, it's pretty rare, when an insert fails, for the programmer to
say, "yeah, I meant to do that". Nearly always, it's a bug.
--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with sqlite3_value_text
On Sat, Apr 13, 2019 at 12:04 PM x wrote:
> This seems to work OK as a sqlite function.
>
>
>
> // assume values[0] & [1] are supplied and not null
>
> // find Count of values[1] in values[0]
>
>
>
> char *c = (char *)sqlite3_value_text(values[0]);
>
> char *Sep = (char *)sqlite3_value_text(values[1]);
>
> int Byte1, Count=0, NrBytes, NrSepBytes = strlen(Sep);
>
you could use sqlite3_value_bytes(values[1]); instead.
>
>
>
> while (*c)
>
> {
>
>
>
>Byte1 = (*c) >> 4;
>
>if ((Byte1 & 8) == 0) NrBytes = 1;
>
>else if (Byte1 & 1) NrBytes = 4;
>
>else if (Byte1 & 2) NrBytes = 3;
>
>else NrBytes = 2; // (Byte1 & 4) == 4
>
>
>
>if (NrBytes == NrSepBytes && memcmp(c, Sep, NrBytes) == 0) Count++;
> // at first byte of Sep
>
>c += NrBytes;
>
> }
>
> sqlite3_result_int(ctx, Count);
>
>
>
>
> From: sqlite-users on
> behalf of Scott Robison
> Sent: Friday, April 12, 2019 8:40:19 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Help with sqlite3_value_text
>
> On Fri, Apr 12, 2019, 1:06 PM Keith Medcalf wrote:
>
> >
> > Actually you would have to convert the strings to UCS-4. UTF-16 is a
> > variable-length encoding. An actual "unicode character" is (at this
> > present moment in time, though perhaps not tomorrow) 4 bytes (64-bits).
> >
>
> That is some impressive compression! :)
>
> Regardless, even if you use UCS-4, you still have the issue of combining
> characters. Unicode is complex as had been observed.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with sqlite3_value_text
This seems to work OK as a sqlite function.
// assume values[0] & [1] are supplied and not null
// find Count of values[1] in values[0]
char *c = (char *)sqlite3_value_text(values[0]);
char *Sep = (char *)sqlite3_value_text(values[1]);
int Byte1, Count=0, NrBytes, NrSepBytes = strlen(Sep);
while (*c)
{
Byte1 = (*c) >> 4;
if ((Byte1 & 8) == 0) NrBytes = 1;
else if (Byte1 & 1) NrBytes = 4;
else if (Byte1 & 2) NrBytes = 3;
else NrBytes = 2; // (Byte1 & 4) == 4
if (NrBytes == NrSepBytes && memcmp(c, Sep, NrBytes) == 0) Count++; //
at first byte of Sep
c += NrBytes;
}
sqlite3_result_int(ctx, Count);
From: sqlite-users on behalf of
Scott Robison
Sent: Friday, April 12, 2019 8:40:19 PM
To: SQLite mailing list
Subject: Re: [sqlite] Help with sqlite3_value_text
On Fri, Apr 12, 2019, 1:06 PM Keith Medcalf wrote:
>
> Actually you would have to convert the strings to UCS-4. UTF-16 is a
> variable-length encoding. An actual "unicode character" is (at this
> present moment in time, though perhaps not tomorrow) 4 bytes (64-bits).
>
That is some impressive compression! :)
Regardless, even if you use UCS-4, you still have the issue of combining
characters. Unicode is complex as had been observed.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with sqlite3_value_text
Thanks for all the help. Things are much clearer now. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.28.0 beta 1
I would really support this other approach. It seems more extensible and eventually would address on of my pain points in SQLite, not knowing what type of statement is being executed. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
