[svn] commit: r2856 - in /branches/trac324: ./ src/lib/datasrc/ src/lib/datasrc/tests/ src/lib/datasrc/tests/testdata/ src/lib/python/isc/datasrc/ src/lib/python/isc/datasrc/tests/ src/lib/python/isc/datasrc/tests/testdata/
BIND 10 source code commits
bind10-changes at lists.isc.org
Tue Sep 7 21:06:04 UTC 2010
Author: each
Date: Tue Sep 7 21:06:04 2010
New Revision: 2856
Log:
- Changed database schema in SQLite3 data source to use more efficient indexes
- Added code to update schema when opening old database files
- Added getSchemaVersion() to class Sqlite3DataSrc to report version
- Add conversion tests in C++ and python datasrc libraries
Added:
branches/trac324/src/lib/datasrc/tests/testdata/test.sqlite3.in (contents, props changed)
- copied, changed from r2851, branches/trac324/src/lib/datasrc/tests/testdata/test.sqlite3
branches/trac324/src/lib/python/isc/datasrc/tests/
branches/trac324/src/lib/python/isc/datasrc/tests/Makefile.am
branches/trac324/src/lib/python/isc/datasrc/tests/datasrc_test.in (with props)
branches/trac324/src/lib/python/isc/datasrc/tests/datasrc_test.py
branches/trac324/src/lib/python/isc/datasrc/tests/testdata/
branches/trac324/src/lib/python/isc/datasrc/tests/testdata/test.sqlite3.in (with props)
Removed:
branches/trac324/src/lib/datasrc/tests/testdata/test.sqlite3
Modified:
branches/trac324/configure.ac
branches/trac324/src/lib/datasrc/sqlite3_datasrc.cc
branches/trac324/src/lib/datasrc/sqlite3_datasrc.h
branches/trac324/src/lib/datasrc/tests/sqlite3_unittest.cc
branches/trac324/src/lib/datasrc/tests/testdata/example.org.sqlite3 (contents, props changed)
branches/trac324/src/lib/datasrc/tests/testdata/example2.com.sqlite3 (contents, props changed)
branches/trac324/src/lib/datasrc/tests/testdata/test-root.sqlite3 (contents, props changed)
branches/trac324/src/lib/python/isc/datasrc/Makefile.am
branches/trac324/src/lib/python/isc/datasrc/sqlite3_ds.py
Modified: branches/trac324/configure.ac
==============================================================================
--- branches/trac324/configure.ac (original)
+++ branches/trac324/configure.ac Tue Sep 7 21:06:04 2010
@@ -439,6 +439,7 @@
src/lib/python/Makefile
src/lib/python/isc/Makefile
src/lib/python/isc/datasrc/Makefile
+ src/lib/python/isc/datasrc/tests/Makefile
src/lib/python/isc/cc/Makefile
src/lib/python/isc/cc/tests/Makefile
src/lib/python/isc/config/Makefile
@@ -498,6 +499,7 @@
src/lib/config/tests/data_def_unittests_config.h
src/lib/python/isc/config/tests/config_test
src/lib/python/isc/cc/tests/cc_test
+ src/lib/python/isc/datasrc/tests/datasrc_test
src/lib/python/isc/log/tests/log_test
src/lib/python/isc/notify/tests/notify_out_test
src/lib/dns/gen-rdatacode.py
Modified: branches/trac324/src/lib/datasrc/sqlite3_datasrc.cc
==============================================================================
--- branches/trac324/src/lib/datasrc/sqlite3_datasrc.cc (original)
+++ branches/trac324/src/lib/datasrc/sqlite3_datasrc.cc Tue Sep 7 21:06:04 2010
@@ -54,9 +54,10 @@
};
namespace {
+// SQL statements to create a database file. schema version 2.
const char* const SCHEMA_LIST[] = {
"CREATE TABLE schema_version (version INTEGER NOT NULL)",
- "INSERT INTO schema_version VALUES (1)",
+ "INSERT INTO schema_version VALUES (2)",
"CREATE TABLE zones (id INTEGER PRIMARY KEY, "
"name STRING NOT NULL COLLATE NOCASE, "
"rdclass STRING NOT NULL COLLATE NOCASE DEFAULT 'IN', "
@@ -67,14 +68,30 @@
"rname STRING NOT NULL COLLATE NOCASE, ttl INTEGER NOT NULL, "
"rdtype STRING NOT NULL COLLATE NOCASE, sigtype STRING COLLATE NOCASE, "
"rdata STRING NOT NULL)",
- "CREATE INDEX records_byname ON records (name)",
- "CREATE INDEX records_byrname ON records (rname)",
+ "CREATE INDEX records_byname ON records (zone_id, name, rdtype, "
+ "sigtype, id, ttl, rdata)",
+ "CREATE INDEX records_byrname ON records (zone_id, rname, rdtype, name)",
"CREATE TABLE nsec3 (id INTEGER PRIMARY KEY, zone_id INTEGER NOT NULL, "
"hash STRING NOT NULL COLLATE NOCASE, "
"owner STRING NOT NULL COLLATE NOCASE, "
"ttl INTEGER NOT NULL, rdtype STRING NOT NULL COLLATE NOCASE, "
"rdata STRING NOT NULL)",
- "CREATE INDEX nsec3_byhash ON nsec3 (hash)",
+ "CREATE INDEX nsec3_byhash ON nsec3 (zone_id, hash, rdtype, "
+ "id, ttl, rdata)",
+ NULL
+};
+
+// SQL statements to convert schema version 1 to schema version 2
+const char* const V1_TO_V2[] = {
+ "DROP INDEX records_byname",
+ "CREATE INDEX records_byname ON records (zone_id, name, rdtype, "
+ "sigtype, id, ttl, rdata)",
+ "DROP INDEX records_byrname",
+ "CREATE INDEX records_byrname ON records (zone_id, rname, rdtype, name)",
+ "DROP INDEX nsec3_byhash",
+ "CREATE INDEX nsec3_byhash ON nsec3 (zone_id, hash, rdtype, "
+ "id, ttl, rdata)",
+ "UPDATE schema_version SET version = 2",
NULL
};
@@ -98,7 +115,7 @@
const char* const q_any_str = "SELECT rdtype, ttl, sigtype, rdata "
"FROM records WHERE zone_id=?1 AND name=?2";
-const char* const q_count_str = "SELECT COUNT(*) FROM records "
+const char* const q_count_str = "SELECT COUNT(rname) FROM records "
"WHERE zone_id=?1 AND rname LIKE (?2 || '%');";
const char* const q_previous_str = "SELECT name FROM records "
@@ -547,7 +564,7 @@
}
Sqlite3DataSrc::Sqlite3DataSrc() :
- dbparameters(new Sqlite3Parameters)
+ dbparameters(new Sqlite3Parameters), schema_version_(-1)
{}
Sqlite3DataSrc::~Sqlite3DataSrc() {
@@ -627,6 +644,33 @@
}
void
+checkVersion(Sqlite3Initializer* initializer) {
+ sqlite3* const db = initializer->params_.db_;
+ int version = initializer->params_.version_;
+ switch (version) {
+ case 1:
+ // XXX: replace this with a logging call
+ cerr << "[sqlite3] Old database version detected, "
+ << "updating to schema version 2" << endl;
+ for (int i = 0; V1_TO_V2[i] != NULL; ++i) {
+ if (sqlite3_exec(db, V1_TO_V2[i],
+ NULL, NULL, NULL) != SQLITE_OK)
+ {
+ isc_throw(Sqlite3Error, "Unable to update schema: '"
+ << V1_TO_V2[i] << "' failed");
+ }
+ }
+ initializer->params_.version_ = 2;
+ break;
+ case 2:
+ // Current version, no action necessary
+ break;
+ default:
+ isc_throw(Sqlite3Error, "Unknown schema version: " << version);
+ }
+}
+
+void
checkAndSetupSchema(Sqlite3Initializer* initializer) {
sqlite3* const db = initializer->params_.db_;
@@ -636,15 +680,17 @@
sqlite3_step(prepared) == SQLITE_ROW) {
initializer->params_.version_ = sqlite3_column_int(prepared, 0);
sqlite3_finalize(prepared);
+ checkVersion(initializer);
} else {
if (prepared != NULL) {
sqlite3_finalize(prepared);
}
for (int i = 0; SCHEMA_LIST[i] != NULL; ++i) {
- if (sqlite3_exec(db, SCHEMA_LIST[i], NULL, NULL, NULL) !=
- SQLITE_OK) {
- isc_throw(Sqlite3Error,
- "Failed to set up schema " << SCHEMA_LIST[i]);
+ if (sqlite3_exec(db, SCHEMA_LIST[i],
+ NULL, NULL, NULL) != SQLITE_OK)
+ {
+ isc_throw(Sqlite3Error, "Failed to set up schema: '"
+ << SCHEMA_LIST[i] << "' failed");
}
}
}
@@ -677,6 +723,7 @@
}
checkAndSetupSchema(&initializer);
+ schema_version_ = initializer.params_.version_;
initializer.move(dbparameters);
}
@@ -724,5 +771,10 @@
return (SUCCESS);
}
-}
-}
+int
+Sqlite3DataSrc::getSchemaVersion() {
+ return (schema_version_);
+}
+
+}
+}
Modified: branches/trac324/src/lib/datasrc/sqlite3_datasrc.h
==============================================================================
--- branches/trac324/src/lib/datasrc/sqlite3_datasrc.h (original)
+++ branches/trac324/src/lib/datasrc/sqlite3_datasrc.h Tue Sep 7 21:06:04 2010
@@ -97,6 +97,7 @@
Result init() { return (init(isc::data::ElementPtr())); }
Result init(const isc::data::ConstElementPtr config);
Result close();
+ int getSchemaVersion();
private:
enum Mode {
@@ -114,6 +115,7 @@
private:
Sqlite3Parameters* dbparameters;
+ int schema_version_;
};
}
Modified: branches/trac324/src/lib/datasrc/tests/sqlite3_unittest.cc
==============================================================================
--- branches/trac324/src/lib/datasrc/tests/sqlite3_unittest.cc (original)
+++ branches/trac324/src/lib/datasrc/tests/sqlite3_unittest.cc Tue Sep 7 21:06:04 2010
@@ -14,6 +14,7 @@
// $Id$
+#include <fstream>
#include <stdint.h>
#include <algorithm>
@@ -96,6 +97,21 @@
ADDRESS,
REFERRAL
} FindMode;
+
+namespace {
+void
+restoreFile(isc::data::ConstElementPtr config) {
+ if (!config || !config->contains("database_file")) {
+ isc_throw(DataSourceError, "No SQLite database file specified");
+ }
+
+ string name = config->get("database_file")->stringValue();
+ string orig = name + ".in";
+ ifstream f1(orig.c_str(), fstream::binary);
+ ofstream f2(name.c_str(), fstream::trunc|fstream::binary);
+ f2 << f1.rdbuf();
+}
+}
class Sqlite3DataSourceTest : public ::testing::Test {
protected:
@@ -364,6 +380,20 @@
answers, signatures);
}
+// This is a mock "test" whose sole purpose is to set up the database
+// file before any other test is run. It must be listed before any other
+// test, and must use a different test case name from the ones below it.
+TEST(Sqlite3Setup, restoreDB) {
+ restoreFile(SQLITE_DBFILE_EXAMPLE);
+}
+
+// This checks that the schema for the test database file has been
+// updated to version 2. This should always be the first test in
+// Sqlite3DatasrcTest.
+TEST_F(Sqlite3DataSourceTest, schemaVersion) {
+ EXPECT_EQ(2, data_source.getSchemaVersion());
+}
+
TEST_F(Sqlite3DataSourceTest, close) {
EXPECT_EQ(DataSrc::SUCCESS, data_source.close());
}
Modified: branches/trac324/src/lib/datasrc/tests/testdata/example.org.sqlite3
==============================================================================
Binary files - no diff available.
Modified: branches/trac324/src/lib/datasrc/tests/testdata/example2.com.sqlite3
==============================================================================
Binary files - no diff available.
Modified: branches/trac324/src/lib/datasrc/tests/testdata/test-root.sqlite3
==============================================================================
Binary files - no diff available.
Modified: branches/trac324/src/lib/python/isc/datasrc/Makefile.am
==============================================================================
--- branches/trac324/src/lib/python/isc/datasrc/Makefile.am (original)
+++ branches/trac324/src/lib/python/isc/datasrc/Makefile.am Tue Sep 7 21:06:04 2010
@@ -1,3 +1,5 @@
+SUBDIRS = tests
+
python_PYTHON = __init__.py master.py sqlite3_ds.py
pythondir = $(pyexecdir)/isc/datasrc
Modified: branches/trac324/src/lib/python/isc/datasrc/sqlite3_ds.py
==============================================================================
--- branches/trac324/src/lib/python/isc/datasrc/sqlite3_ds.py (original)
+++ branches/trac324/src/lib/python/isc/datasrc/sqlite3_ds.py Tue Sep 7 21:06:04 2010
@@ -37,7 +37,7 @@
def create(cur):
"""Create new zone database"""
cur.execute("CREATE TABLE schema_version (version INTEGER NOT NULL)")
- cur.execute("INSERT INTO schema_version VALUES (1)")
+ cur.execute("INSERT INTO schema_version VALUES (2)")
cur.execute("""CREATE TABLE zones (id INTEGER PRIMARY KEY,
name STRING NOT NULL COLLATE NOCASE,
rdclass STRING NOT NULL COLLATE NOCASE DEFAULT 'IN',
@@ -51,8 +51,10 @@
rdtype STRING NOT NULL COLLATE NOCASE,
sigtype STRING COLLATE NOCASE,
rdata STRING NOT NULL)""")
- cur.execute("CREATE INDEX records_byname ON records (name)")
- cur.execute("CREATE INDEX records_byrname ON records (rname)")
+ cur.execute("""CREATE INDEX records_byname ON records (zone_id, name,
+ rdtype, sigtype, id, ttl, rdata)""")
+ cur.execute("""CREATE INDEX records_byrname ON records (zone_id, rname,
+ rdtype, name)""")
cur.execute("""CREATE TABLE nsec3 (id INTEGER PRIMARY KEY,
zone_id INTEGER NOT NULL,
hash STRING NOT NULL COLLATE NOCASE,
@@ -60,7 +62,34 @@
ttl INTEGER NOT NULL,
rdtype STRING NOT NULL COLLATE NOCASE,
rdata STRING NOT NULL)""")
- cur.execute("CREATE INDEX nsec3_byhash ON nsec3 (hash)")
+ cur.execute("""CREATE INDEX nsec3_byhash ON nsec3 (zone_id, hash,
+ rdtype, id, ttl, rdata)""")
+
+#########################################################################
+# check_version: check database schema; if it is an old version, update
+# to the current one
+# input: cur - database cursor
+# version - the current version number
+# returns: the new version
+#########################################################################
+def check_version(cur, version):
+ if version == 1:
+ # XXX: replace this with a logging call
+ print ("[sqlite3] Old database version detected, " +
+ "updating to schema version 2", file=sys.stderr)
+ cur.execute("DROP INDEX records_byname")
+ cur.execute("""CREATE INDEX records_byname ON records (zone_id, name,
+ rdtype, sigtype, id, ttl, rdata)""")
+ cur.execute("DROP INDEX records_byrname")
+ cur.execute("""CREATE INDEX records_byrname ON records (zone_id, rname,
+ rdtype, name)""")
+ cur.execute("DROP INDEX nsec3_byhash")
+ cur.execute("""CREATE INDEX nsec3_byhash ON nsec3 (zone_id, hash,
+ rdtype, id, ttl, rdata)""")
+ cur.execute("UPDATE schema_version SET version = 2")
+ elif version != 2:
+ raise Sqlite3DSError("Unknown database schema version " + version)
+
#########################################################################
# open: open a database. if the database is not yet set up,
@@ -88,8 +117,10 @@
conn.commit()
row = [1]
- if row == None or row[0] != 1:
- raise Sqlite3DSError("Bad database schema version")
+ if row == None:
+ raise Sqlite3DSError("Database schema version not found")
+ else:
+ check_version(cur, row[0]);
return conn, cur
More information about the bind10-changes
mailing list