RPM Package Manager, CVS Repository
http://rpm5.org/cvs/
____________________________________________________________________________
Server: rpm5.org Name: Jeff Johnson
Root: /v/rpm/cvs Email: j...@rpm5.org
Module: rpm Date: 27-Apr-2012 03:39:08
Branch: rpm-5_4 Handle: 2012042701390800
Modified files: (Branch: rpm-5_4)
rpm/tests/ref rpmdb.sql sqldb-basic.out sqldb-basic.sql
Log:
- sqldb: enhance the schgmea, add inner joing and equi-join access
examples.
Summary:
Revision Changes Path
1.1.4.1 +48 -8 rpm/tests/ref/rpmdb.sql
1.5.4.1 +102 -25 rpm/tests/ref/sqldb-basic.out
1.4.4.1 +28 -4 rpm/tests/ref/sqldb-basic.sql
____________________________________________________________________________
patch -p0 <<'@@ .'
Index: rpm/tests/ref/rpmdb.sql
============================================================================
$ cvs diff -u -r1.1 -r1.1.4.1 rpmdb.sql
--- rpm/tests/ref/rpmdb.sql 8 Apr 2010 22:01:52 -0000 1.1
+++ rpm/tests/ref/rpmdb.sql 27 Apr 2012 01:39:08 -0000 1.1.4.1
@@ -1,17 +1,57 @@
+DROP TABLE IF EXISTS Packages;
CREATE TABLE Packages (
- i INTEGER UNIQUE PRIMARY KEY NOT NULL,
- h BLOB NOT NULL
+ v INTEGER UNIQUE PRIMARY KEY NOT NULL,
+ k BLOB NOT NULL
);
-CREATE TEMP TRIGGER insert_Packages AFTER INSERT ON Packages
+
+CREATE TRIGGER insert_Packages AFTER INSERT ON Packages
BEGIN
- INSERT INTO Nvra (k,v) VALUES ( new.h, new.rowid );
+ INSERT INTO Nvra (k,v) VALUES (
+ new.k, new.rowid );
+ INSERT INTO Name (k,v) VALUES (
+ SUBSTR(new.k, 1, 4), new.rowid );
+ INSERT INTO Version (k,v) VALUES (
+ SUBSTR(new.k, 6, 3), new.rowid );
+ INSERT INTO Release (k,v) VALUES (
+ SUBSTR(new.k, 10, 1), new.rowid );
+ INSERT INTO Arch (k,v) VALUES (
+ SUBSTR(new.k, 12), new.rowid );
END;
-CREATE TEMP TRIGGER delete_Packages BEFORE DELETE ON Packages
+CREATE TRIGGER delete_Packages BEFORE DELETE ON Packages
BEGIN
- DELETE FROM Nvra WHERE v = old.rowid;
+ DELETE FROM Nvra WHERE v = old.rowid;
+ DELETE FROM Name WHERE v = old.rowid;
+ DELETE FROM Version WHERE v = old.rowid;
+ DELETE FROM Release WHERE v = old.rowid;
+ DELETE FROM Arch WHERE v = old.rowid;
END;
+DROP TABLE IF EXISTS Nvra;
CREATE TABLE Nvra (
- k TEXT PRIMARY KEY NOT NULL,
- v INTEGER REFERENCES Packages(i) ON UPDATE RESTRICT ON DELETE RESTRICT
+ k TEXT NOT NULL,
+ v INTEGER REFERENCES Packages
+);
+
+DROP TABLE IF EXISTS Name;
+CREATE TABLE Name (
+ k TEXT NOT NULL,
+ v INTEGER REFERENCES Packages
+);
+
+DROP TABLE IF EXISTS Version;
+CREATE TABLE Version (
+ k TEXT NOT NULL,
+ v INTEGER REFERENCES Packages
+);
+
+DROP TABLE IF EXISTS Release;
+CREATE TABLE Release (
+ k TEXT NOT NULL,
+ v INTEGER REFERENCES Packages
+);
+
+DROP TABLE IF EXISTS Arch;
+CREATE TABLE Arch (
+ k TEXT NOT NULL,
+ v INTEGER REFERENCES Packages
);
@@ .
patch -p0 <<'@@ .'
Index: rpm/tests/ref/sqldb-basic.out
============================================================================
$ cvs diff -u -r1.5 -r1.5.4.1 sqldb-basic.out
--- rpm/tests/ref/sqldb-basic.out 12 Apr 2010 21:27:03 -0000 1.5
+++ rpm/tests/ref/sqldb-basic.out 27 Apr 2012 01:39:08 -0000 1.5.4.1
@@ -1,13 +1,3 @@
--- Loading resources from /home/jbj/.sqliterc
-.show
- echo: on
- explain: off
- headers: off
- mode: list
-nullvalue: ""
- output: stdout
-separator: "|"
- width:
.echo on
.explain off
.headers on
@@ -29,24 +19,83 @@
width:
.read ref/rpmdb.sql
SELECT * from Packages;
-i|h
+v|k
1|bing-1.2-3.noarch
2|bang-4.5-6.noarch
-3|boom-7.8.9.noarch
+3|boom-7.8-9.noarch
SELECT * from Nvra;
k|v
bing-1.2-3.noarch|1
bang-4.5-6.noarch|2
-boom-7.8.9.noarch|3
+boom-7.8-9.noarch|3
+SELECT * from Name;
+k|v
+bing|1
+bang|2
+boom|3
+SELECT * from Version;
+k|v
+1.2|1
+4.5|2
+7.8|3
+SELECT * from Release;
+k|v
+3|1
+6|2
+9|3
+SELECT * from Arch;
+k|v
+noarch|1
+noarch|2
+noarch|3
+SELECT Arch.k, Packages.k
+ FROM Arch
+ INNER JOIN Packages ON Arch.v = Packages.v;
+k|k
+noarch|bing-1.2-3.noarch
+noarch|bang-4.5-6.noarch
+noarch|boom-7.8-9.noarch
+SELECT Arch.k, Packages.k
+ FROM Arch, Packages
+ WHERE Arch.v = Packages.v;
+k|k
+noarch|bing-1.2-3.noarch
+noarch|bang-4.5-6.noarch
+noarch|boom-7.8-9.noarch
+SELECT *
+ FROM Arch
+ INNER JOIN Packages USING (v);
+k|v|k
+noarch|1|bing-1.2-3.noarch
+noarch|2|bang-4.5-6.noarch
+noarch|3|boom-7.8-9.noarch
SELECT * from Packages;
-i|h
+v|k
1|bing-1.2-3.noarch
-3|boom-7.8.9.noarch
+3|boom-7.8-9.noarch
SELECT * from Nvra;
k|v
bing-1.2-3.noarch|1
-boom-7.8.9.noarch|3
+boom-7.8-9.noarch|3
+SELECT * from Name;
+k|v
+bing|1
+boom|3
+SELECT * from Version;
+k|v
+1.2|1
+7.8|3
+SELECT * from Release;
+k|v
+3|1
+9|3
+SELECT * from Arch;
+k|v
+noarch|1
+noarch|3
+Error: disk I/O error
.backup main tmp/main.bak
+Error: disk I/O error
.restore main tmp/main.bak
.dump main
PRAGMA foreign_keys=OFF;
@@ -55,27 +104,55 @@
.databases
seq name file
--- ---------------
----------------------------------------------------------
-0 main /X/src/wdj/tests/tmp/sqldb
-1 temp
+0 main /X/src/wdj54/tests/tmp/sqldb
.indices Packages
sqlite_autoindex_Packages_1
.schema
+CREATE TABLE Arch (
+ k TEXT NOT NULL,
+ v INTEGER REFERENCES Packages
+);
+CREATE TABLE Name (
+ k TEXT NOT NULL,
+ v INTEGER REFERENCES Packages
+);
CREATE TABLE Nvra (
- k TEXT PRIMARY KEY NOT NULL,
- v INTEGER REFERENCES Packages(i) ON UPDATE RESTRICT ON DELETE RESTRICT
+ k TEXT NOT NULL,
+ v INTEGER REFERENCES Packages
);
CREATE TABLE Packages (
- i INTEGER UNIQUE PRIMARY KEY NOT NULL,
- h BLOB NOT NULL
+ v INTEGER UNIQUE PRIMARY KEY NOT NULL,
+ k BLOB NOT NULL
+);
+CREATE TABLE Release (
+ k TEXT NOT NULL,
+ v INTEGER REFERENCES Packages
+);
+CREATE TABLE Version (
+ k TEXT NOT NULL,
+ v INTEGER REFERENCES Packages
);
CREATE TRIGGER delete_Packages BEFORE DELETE ON Packages
BEGIN
- DELETE FROM Nvra WHERE v = old.rowid;
+ DELETE FROM Nvra WHERE v = old.rowid;
+ DELETE FROM Name WHERE v = old.rowid;
+ DELETE FROM Version WHERE v = old.rowid;
+ DELETE FROM Release WHERE v = old.rowid;
+ DELETE FROM Arch WHERE v = old.rowid;
END;
CREATE TRIGGER insert_Packages AFTER INSERT ON Packages
BEGIN
- INSERT INTO Nvra (k,v) VALUES ( new.h, new.rowid );
+ INSERT INTO Nvra (k,v) VALUES (
+ new.k, new.rowid );
+ INSERT INTO Name (k,v) VALUES (
+ SUBSTR(new.k, 1, 4), new.rowid );
+ INSERT INTO Version (k,v) VALUES (
+ SUBSTR(new.k, 6, 3), new.rowid );
+ INSERT INTO Release (k,v) VALUES (
+ SUBSTR(new.k, 10, 1), new.rowid );
+ INSERT INTO Arch (k,v) VALUES (
+ SUBSTR(new.k, 12), new.rowid );
END;
.tables
-Nvra Packages
+Arch Name Nvra Packages Release Version
.exit
@@ .
patch -p0 <<'@@ .'
Index: rpm/tests/ref/sqldb-basic.sql
============================================================================
$ cvs diff -u -r1.4 -r1.4.4.1 sqldb-basic.sql
--- rpm/tests/ref/sqldb-basic.sql 10 Apr 2010 18:16:45 -0000 1.4
+++ rpm/tests/ref/sqldb-basic.sql 27 Apr 2012 01:39:08 -0000 1.4.4.1
@@ -13,24 +13,48 @@
.show
-- Instantiate the rpmdb.sql schema.
+PRAGMA foreign_keys = ON;
.read ref/rpmdb.sql
-- Basic rpmdbAdd/rpmdbRemove operations using SQL triggers.
BEGIN TRANSACTION;
-INSERT into Packages (h) VALUES ('bing-1.2-3.noarch');
-INSERT into Packages (h) VALUES ('bang-4.5-6.noarch');
-INSERT into Packages (h) VALUES ('boom-7.8.9.noarch');
+INSERT into Packages (k) VALUES ('bing-1.2-3.noarch');
+INSERT into Packages (k) VALUES ('bang-4.5-6.noarch');
+INSERT into Packages (k) VALUES ('boom-7.8-9.noarch');
COMMIT TRANSACTION;
SELECT * from Packages;
SELECT * from Nvra;
+SELECT * from Name;
+SELECT * from Version;
+SELECT * from Release;
+SELECT * from Arch;
+
+-- explicit inner join
+SELECT Arch.k, Packages.k
+ FROM Arch
+ INNER JOIN Packages ON Arch.v = Packages.v;
+
+-- implicit inner join
+SELECT Arch.k, Packages.k
+ FROM Arch, Packages
+ WHERE Arch.v = Packages.v;
+
+-- equi-join
+SELECT *
+ FROM Arch
+ INNER JOIN Packages USING (v);
BEGIN TRANSACTION;
-DELETE FROM Packages WHERE i = 2;
+DELETE FROM Packages WHERE v = 2;
COMMIT TRANSACTION;
SELECT * from Packages;
SELECT * from Nvra;
+SELECT * from Name;
+SELECT * from Version;
+SELECT * from Release;
+SELECT * from Arch;
-- Basic .foo SQL metadata tests.
.backup main tmp/main.bak
@@ .
______________________________________________________________________
RPM Package Manager http://rpm5.org
CVS Sources Repository rpm-cvs@rpm5.org
