On Fri, 12 Apr 2019 11:40:13 -0400
Jim Dossey <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
