ubuntuusers.de

Hi,

für ein bash-Skript benutze ich (bald) eine sqlite3-Datenbank, die u.a. folgende Tabelle enthält:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

CREATE TABLE tbl_urimark (
    tbl_authorities_key INTEGER NOT NULL,
    id INTEGER PRIMARY KEY NOT NULL,
    ctime INTEGER NOT NULL DEFAULT (strftime('%s','now')),
    mtime INTEGER NOT NULL DEFAULT (strftime('%s','now')),
    tbl_schemes_key INTEGER NOT NULL,
    part TEXT NOT NULL,
    name TEXT NOT NULL DEFAULT 'null',
    desc TEXT NOT NULL DEFAULT 'null',
    tbl_hierarchies_key INTEGER NOT NULL DEFAULT '0',
    unique (tbl_schemes_key, tbl_authorities_key, part),
    FOREIGN KEY(tbl_authorities_key) REFERENCES tbl_authorities(key) ON UPDATE CASCADE,
    FOREIGN KEY(tbl_schemes_key) REFERENCES tbl_schemes(key) ON UPDATE CASCADE,
    FOREIGN KEY(tbl_hierarchies_key) REFERENCES tbl_hierarchies(key) ON UPDATE CASCADE
);

Bei jedem INSERT, das innerhalb des Skripts ausgeführt ist, wird der ID explizit ein Wert > 0 zugeordnet. Dieser soll die niedrigste "Leerstelle" in der Menge aller derzeit benutzten IDs ausfüllen oder – falls keine Leerstelle(n) vorhanden sind – um eins größer sein als der höhste benutzte Wert. Habe ich "1 2 3 4", dann wird der Wert "5"; habe ich "1 3 4" oder "1 3 5", dann "2" usw. Dazu benutze ich derzeit diesen View, dessen Body ich aus dem Netz gefischt habe:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE VIEW view_calc_id_next AS
    SELECT id + 1 AS id_next
    FROM tbl_urimark UO
    WHERE NOT EXISTS
        (
            SELECT NULL
            FROM tbl_urimark UI
            WHERE UI.id == UO.id + 1
        )
    ORDER BY id
    LIMIT 1 ;

Mich stören daran zwei Dinge: (1) Wenn noch keine Records vorhanden sind, dann erhalte ich keine Ausgabe; (2) Der Code füllt nicht die Leerstelle "1" aus, sondern gibt stattdessen die höhste ROWID + 1 aus. Punkt (1) stört mich nicht sonderlich, weil ich darauf im Skript reagieren kann. Punkt (2) ist etwas nervig.

Weiß da jemand vllt weiter?

Gruß 🐸

D630 schrieb:

Bei jedem INSERT, das innerhalb des Skripts ausgeführt ist, wird der ID explizit ein Wert > 0 zugeordnet. Dieser soll die niedrigste "Leerstelle" in der Menge aller derzeit benutzten IDs ausfüllen oder – falls keine Leerstelle(n) vorhanden sind – um eins größer sein als der höhste benutzte Wert. Habe ich "1 2 3 4", dann wird der Wert "5"; habe ich "1 3 4" oder "1 3 5", dann "2" usw. Dazu benutze ich derzeit diesen View, dessen Body ich aus dem Netz gefischt habe:

1 2 3 4 5 6 7 8 9 10 11

CREATE VIEW view_calc_id_next AS SELECT id + 1 AS id_next FROM tbl_urimark UO WHERE NOT EXISTS ( SELECT NULL FROM tbl_urimark UI WHERE UI.id == UO.id + 1 ) ORDER BY id LIMIT 1 ;

Mich stören daran zwei Dinge: (1) Wenn noch keine Records vorhanden sind, dann erhalte ich keine Ausgabe; (2) Der Code füllt nicht die Leerstelle "1" aus, sondern gibt stattdessen die höhste ROWID + 1 aus. Punkt (1) stört mich nicht sonderlich, weil ich darauf im Skript reagieren kann. Punkt (2) ist etwas nervig.

Hm...

1
2
3

select min(x) as mx
from ( select id + 1 as x from id_test ) as mt
where not exists ( select 1 from id_test where id = x );

Mit vorhandenen Werten 1, 2, 3 und 5 liefert das 4. Der Ansatz ist aber der selbe wie mit dem View soweit ich das sehe.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

sqlite> select * from id_test;
1
2
3
5
sqlite> select min(x) as mx from ( select id + 1 as x from id_test ) as mt where not exists ( select 1 from id_test where id = x );
4
sqlite> insert into id_test values ( 4 );
sqlite> select min(x) as mx from ( select id + 1 as x from id_test ) as mt where not exists ( select 1 from id_test where id = x );
6

Das sieht doch OK aus. Was hast Du denn hier für ein Problem 2?

Ich habe auch noch eine Lösung für Problem 1: wir führen einfach eine UNION ein:

1
2
3
4
5

sqlite> select min(x) as mx from ( select id + 1 as x from id_test union select 1 ) as mt where not exists ( select 1 from id_test where id = x );
6
sqlite> delete from id_test;
sqlite> select min(x) as mx from ( select id + 1 as x from id_test union select 1 ) as mt where not exists ( select 1 from id_test where id = x );
1

☺

Edit: der Ansatz ist nicht ganz der selbe, da ich MIN nutze und der View ein LIMIT enthält.

rklm schrieb:

1 2 3 4 5 6 7 8 9 10

sqlite> select * from id_test; 1 2 3 5 sqlite> select min(x) as mx from ( select id + 1 as x from id_test ) as mt where not exists ( select 1 from id_test where id = x ); 4 sqlite> insert into id_test values ( 4 ); sqlite> select min(x) as mx from ( select id + 1 as x from id_test ) as mt where not exists ( select 1 from id_test where id = x ); 6

Das sieht doch OK aus. Was hast Du denn hier für ein Problem 2?

Also bei mir bekomme ich ohne UNION keine "1" ausgespuckt:

1
2
3
4
5
6
7

sqlite> SELECT id from tbl_urimark ORDER BY id;
2
3
4
5
sqlite> SELECT MIN(x) AS mx FROM (SELECT id + 1 AS x FROM tbl_urimark) AS mt WHERE NOT EXISTS (SELECT 1 FROM tbl_urimark WHERE id == x );
6

Aber mit ist es wunderbar! ☺

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

sqlite> SELECT MIN(x) AS mx FROM (SELECT id + 1 AS x FROM tbl_urimark UNION SELECT 1) AS mt WHERE NOT EXISTS (SELECT 1 FROM tbl_urimark WHERE id == x );
1
$ um add uri=http://forum.ubuntuusers.de/post/7276948
DO ADD
SET ID 1
SET OLD UUID 1 e4cf6c1f-3450-4a3d-b356-6a2c5a40d282
ADD ID 1 http://forum.ubuntuusers.de/post/7276948
DONE
sqlite> SELECT id from tbl_urimark ORDER BY id;
1
2
3
4
5
sqlite> SELECT MIN(x) AS mx FROM (SELECT id + 1 AS x FROM tbl_urimark UNION SELECT 1) AS mt WHERE NOT EXISTS (SELECT 1 FROM tbl_urimark WHERE id == x );
6
$ um add uri=http://forum.ubuntuusers.de/post/7276948${RANDOM}
DO ADD
SET ID 6
SET OLD UUID 6 e4cf6c1f-3450-4a3d-b356-6a2c5a40d282
ADD ID 6 http://forum.ubuntuusers.de/post/727694820855
DONE
$ um del -n id="1-3;5"
DO DEL
SET ID 1
SET ID 2
SET ID 3
SET ID 5
DEL ID 1
DEL ID 2
DEL ID 3
DEL ID 5
DONE
sqlite> SELECT id from tbl_urimark ORDER BY id;
4
6
sqlite> SELECT MIN(x) AS mx FROM (SELECT id + 1 AS x FROM tbl_urimark UNION SELECT 1) AS mt WHERE NOT EXISTS (SELECT 1 FROM tbl_urimark WHERE id == x );
1

Habe es nun so gemacht:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

CREATE VIEW view_calc_id_next AS
    SELECT MIN(x) AS id_next
    FROM
        (
            SELECT id + 1 AS x
            FROM tbl_urimark
            UNION
            SELECT 1
        )
    WHERE NOT EXISTS
        (
            SELECT NULL
            FROM tbl_urimark
            WHERE id == x
        );

Danke Dir!

D630 schrieb:

Habe es nun so gemacht:

1 2 3 4 5 6 7

... WHERE NOT EXISTS ( SELECT NULL FROM tbl_urimark WHERE id == x );

In SQL vergleicht man mit "=" - nicht mit "==". Auch wenn SQLite da tolerant ist, würde ich das standardkonform machen.

Warum selektierst Du hier eigentlich "NULL" und nicht z.B. "1", wie ich das gemacht habe?

Danke Dir!

Bitte sehr! Ich freue mich immer, wenn ich mal wieder etwas mit einer relationalen Datenbank machen kann. Dank Dir für die Gelegenheit! ☺

rklm schrieb:

In SQL vergleicht man mit "=" - nicht mit "==". Auch wenn SQLite da tolerant ist, würde ich das standardkonform machen.

Ok. Ja, laut Doku gehen "=" und "!=" bzw. "==" und "<>".

Warum selektierst Du hier eigentlich "NULL" und nicht z.B. "1", wie ich das gemacht habe?

Öh, da gibt es kein "warum"; war eher unabsichtlich.

Bitte sehr! Ich freue mich immer, wenn ich mal wieder etwas mit einer relationalen Datenbank machen kann. Dank Dir für die Gelegenheit! ☺

👍

@D630: Mich würde einmal interessieren, was der tiefere Sinn hinter Deinen Bemühungen ist! IdR. kann man sich solch manuelles Gefummel sparen und bei einem künstlichem numerischen PK der Datenbank die Arbeit überlassen, einen freien Schlüsselwert zu finden. Das ist sicherlich deutlich einfacher, weniger fehleranfällig und zudem noch *deutlich* effizienter.

Lysander schrieb:

@D630: Mich würde einmal interessieren, was der tiefere Sinn hinter Deinen Bemühungen ist! IdR. kann man sich solch manuelles Gefummel sparen und bei einem künstlichem numerischen PK der Datenbank die Arbeit überlassen, einen freien Schlüsselwert zu finden. Das ist sicherlich deutlich einfacher, weniger fehleranfällig und zudem noch *deutlich* effizienter.

Das ID-Feld wird u.a. als Filter verwendet, aber ich will nicht unnötig lange Zahlen eintippen müssen.

$ um help
urimark v0.4.0.0

um (-a|-c|-d|-e|-h|-i|-m|-r|-v) [<OPT>...]
um [<OPT>...] [<HOOK>]

Subcommands:
  -a, --add                 Add a record
  -c, --compact, --com      Compact database
  -d, --delete, --del       Delete a record
  -e, --edit                Edit manually one record with EDITOR
  -h, --help                Show this instruction
  -i, --initialize, --init  Initialize database
  -m, --modify, --modi      Modify many records without EDITOR
  -r, --rebuild, --reb      Rebuild and initialize database
  -v, --version             Print version

Options:
  Miscellaneous:
    -n, --non-interactive, --nv

  Operators:
    -!, --not
    -A, --and
    -M, --mod
    -O, --or

  Filters:
    -0, --uuid=UUID
    -1, --id=ID
    -2, --ctime=CT, --ct=CT
    -3, --mtime=MT, --mt=MT
    -4, --tag=TAG
    -7, --reference=REF, --ref=REF
    -D, --description=DESC, --desc=DESC
    -H, --hierarchy=HIER, --hier=HIER
    -N, --name=NAME
    -P, --part=PART
    -S, --scheme=SCHEME
    -U, --uri=URI
    -Y, --authority=AUTH, --auth=AUTH

  Special mod-Assignments:
    -5, --tag+=TAG
    -6, --tag-=TAG
    -8, --reference+=REF, --ref+=REF
    -9, --reference-=REF, --ref-=REF

You may use all long subcommands and options without double hypen.

Arguments:
  AUTH*                     'string'
  CT                        's' or range 's;s'. Two special
                            values: 'first' and 'last'
  DESC*                     'string'
  HIER*                     string like '/foo/bar/n'
  HOOK                      'string' without space character
  ID                        'int', 'int;int;n' or range 'int-int'
                            (or combi). Two special values: 'first'
                            and 'last'
  MT                        's' or range 's;s'. Two special
                            values: 'first' and 'last'
  NAME*                     'string'
  PART*                     'string'
  REF*                      'int' or 'int;int;n'
  SCHEME*                   'http','https','ftp','ftps','dav','davs',
                            'gopher','webdav','webdavs'
  TAG*                      'string' or 'string;string;n'
  URI*                      'string'
  UUID*                     'uuid'

* You may prefix these arguments with 'g~', 'l~' or 'r~' to use the
  core functions glob(), like() or regexp() (PCRE) of sqlite3.

:

D630 schrieb:

Das ID-Feld wird u.a. als Filter verwendet, aber ich will nicht unnötig lange Zahlen eintippen müssen.

Wie oft kommt denn das Löschen vor? Die Länge einer Zahl steigt ja immer langsamer an. Wenn das ganze also keine Queue abbildet, bei der permanent hinzugefügt und gelöscht wird, sollte die Länge lange Zeit nicht über 3 oder gar 4 kommen 😉

Wenn Du konstant kleine IDs möchtest, könntest Du Dir über einen Hash-Schlüssel Gedanken machen, wie er von URL-Shortenern verwendet wird...

Lysander schrieb:

D630 schrieb:

Das ID-Feld wird u.a. als Filter verwendet, aber ich will nicht unnötig lange Zahlen eintippen müssen.

Wie oft kommt denn das Löschen vor? Die Länge einer Zahl steigt ja immer langsamer an. Wenn das ganze also keine Queue abbildet, bei der permanent hinzugefügt und gelöscht wird, sollte die Länge lange Zeit nicht über 3 oder gar 4 kommen 😉

Wenn Du konstant kleine IDs möchtest, könntest Du Dir über einen Hash-Schlüssel Gedanken machen, wie er von URL-Shortenern verwendet wird...

Ich behalte das Mal im Hinterkopf. Ich schreibe das "add"-Kommando eh derzeit um, weil es bisher zweimal die Datenbank öffnen muss (via sqlite3) und folglich ein bissl lahm ist.