[svn] commit: r662 - /experiments/graff-dbapi/sqlite3_ds.cc
BIND 10 source code commits
bind10-changes at lists.isc.org
Fri Jan 29 22:19:29 UTC 2010
Author: mgraff
Date: Fri Jan 29 22:19:28 2010
New Revision: 662
Log:
fetches records (and their signatures)
Modified:
experiments/graff-dbapi/sqlite3_ds.cc
Modified: experiments/graff-dbapi/sqlite3_ds.cc
==============================================================================
--- experiments/graff-dbapi/sqlite3_ds.cc (original)
+++ experiments/graff-dbapi/sqlite3_ds.cc Fri Jan 29 22:19:28 2010
@@ -5,43 +5,38 @@
#include <sqlite3.h>
-//
-// table: schema_version
-// version: integer (contains the schema version number)
-//
-// table: zones
-// id: integer (sequence)
-// name: string (zone name in normal DNS format)
-// dnssec: boolean (true if DNSSEC zone)
-// rdclass: string (usually IN)
-//
-// table: rrs
-// id: integer (sequnce)
-// zone_id: integer (zone this record belongs to)
-// name: string (stored in forward order)
-// rname: string (stored in reverse order)
-// rdtype: string (type, such as A, AAAA, RRSIG)
-// sigtype: string (only set if rdtype=RRSIG. Will be A, AAAA, etc.)
-// rdata: string (the data itself)
-//
+using namespace std;
class SQLITE3 {
public:
- void setDatabaseName(std::string name) {
+ //
+ // Create a new database, or open an existing one.
+ //
+ SQLITE3(const string name) {
database_name = name;
- }
-
- std::string getDatabaseName(void) {
- return database_name;
- }
-
- SQLITE3(std::string name = "zone.sql3") {
- setDatabaseName(name);
-
db = NULL;
database_version = -1;
}
+ //
+ // Close the database and free items up.
+ //
+ ~SQLITE3() {
+ db = NULL;
+ if (q_zone != NULL) {
+ release(q_zone);
+ q_zone = NULL;
+ }
+ if (q_record) {
+ release(q_record);
+ q_record = NULL;
+ }
+ sqlite3_close(db);
+ }
+
+ //
+ // Open the database.
+ //
void open(void) {
rc = sqlite3_open(database_name.c_str(), &db);
if (rc) {
@@ -54,40 +49,10 @@
checkAndSetupSchema();
}
- int fetch_row(sqlite3_stmt *row) {
- int columns = sqlite3_column_count(row);
- for (int column = 0 ; column < columns ; column++) {
- const unsigned char *str;
- int str_length;
- str = sqlite3_column_text(row, column);
- str_length = sqlite3_column_bytes(row, column);
- }
- return (columns);
- }
-
- int query(sqlite3_stmt *statement) {
- do {
- rc = sqlite3_step(statement);
- switch (rc) {
- case SQLITE_ROW:
- rc = fetch_row(statement);
- if (rc != SQLITE_OK)
- return rc;
- break;
- case SQLITE_DONE:
- return sqlite3_reset(statement);
- break;
- default:
- return rc;
- }
- } while (rc == SQLITE_ROW);
- return (rc);
- }
-
- ~SQLITE3() {
- sqlite3_close(db);
- }
-
+ //
+ // Prepare a statement. Can call release() or sqlite3_finalize()
+ // directly.
+ //
sqlite3_stmt* prepare(const char *statement) {
sqlite3_stmt *prepared = NULL;
rc = sqlite3_prepare_v2(db, statement, strlen(statement) + 1,
@@ -97,16 +62,113 @@
}
return prepared;
}
-
+
+ //
+ // Release memory associated with a prepared query.
+ //
void release(sqlite3_stmt* prepared) {
sqlite3_finalize(prepared);
}
+ //
+ // Get the database schema version.
+ //
int getVersion(void) {
if (database_version == -1) {
loadVersion();
}
return database_version;
+ }
+
+ //
+ // Find the exact zone match. Return -1 if not found, or the zone's
+ // ID if found. This will always be >= 0 if found.
+ //
+ int hasExactZone(const char *name) {
+ sqlite3_reset(q_zone);
+ rc = sqlite3_bind_text(q_zone, 1, name, -1, SQLITE_STATIC);
+ if (rc != SQLITE_OK) {
+ throw("Could not bind");
+ }
+ rc = sqlite3_step(q_zone);
+ if (rc == SQLITE_ROW) {
+ return sqlite3_column_int(q_zone, 0);
+ } else {
+ return -1;
+ }
+ }
+
+ int findRecords(const char *zonename, const char *name, const char *rdtype) {
+ int zone_id = hasExactZone(zonename);
+ if (zone_id < 0) {
+ throw("Don't have that zone");
+ }
+
+ sqlite3_reset(q_record);
+ rc = sqlite3_bind_int(q_record, 1, zone_id);
+ if (rc != SQLITE_OK) {
+ throw("Could not bind 1");
+ }
+ rc = sqlite3_bind_text(q_record, 2, name, -1, SQLITE_STATIC);
+ if (rc != SQLITE_OK) {
+ throw("Could not bind 2");
+ }
+ rc = sqlite3_bind_text(q_record, 3, rdtype, -1, SQLITE_STATIC);
+ if (rc != SQLITE_OK) {
+ throw("Could not bind 3");
+ }
+
+ // loop
+ int rows = 0;
+ do {
+ rc = sqlite3_step(q_record);
+ if (rc == SQLITE_ROW) {
+ rows++;
+
+ const unsigned char *type = sqlite3_column_text(q_record, 0);
+ int ttl = sqlite3_column_int(q_record, 1);
+ const unsigned char *sigtype = sqlite3_column_text(q_record, 2);
+ const unsigned char *rdata = sqlite3_column_text(q_record, 3);
+
+ cout << "Found:"
+ << name << " "
+ << ttl << " "
+ << type << " ";
+ if (sigtype != NULL) {
+ cout << sigtype << " ";
+ }
+ cout << rdata << endl;
+ }
+ } while (rc == SQLITE_ROW);
+
+ return (rows);
+ }
+
+ //
+ // Search for the closest enclosing zone. Will return -1 if not found,
+ // >= 0 if found. If position is not NULL, it will be filled in with the
+ // longest match found.
+ //
+ int findClosest(const char *name, const char **position) {
+ const char *current = name;
+
+ while (*current != 0) {
+ rc = hasExactZone(current);
+ if (rc >= 0) {
+ if (position != NULL) {
+ *position = current;
+ }
+ return rc;
+ }
+ while (*current != '.' && *current != 0) {
+ current++;
+ }
+ if (*current == '.') {
+ *current++;
+ }
+ }
+
+ return 0;
}
private:
@@ -120,27 +182,69 @@
database_version = sqlite3_column_int(prepared, 0);
release(prepared);
}
+
+ void setupPreparedStatements(void) {
+ const char *q_zone_str = "SELECT id FROM zones WHERE name=?1";
+ const char *q_record_str = "SELECT rdtype, ttl, sigtype, rdata FROM records WHERE zone_id=?1 AND name=?2 AND (rdtype=?3 OR sigtype=?3)";
+
+ try {
+ q_zone = prepare(q_zone_str);
+ } catch (const char *e) {
+ cout << e << endl << q_zone_str << endl;
+ cout << sqlite3_errmsg(db) << endl;
+ throw(e);
+ }
+ try {
+ q_record = prepare(q_record_str);
+ } catch (const char *e) {
+ cout << e << endl << q_record_str << endl;
+ cout << sqlite3_errmsg(db) << endl;
+ throw(e);
+ }
+ }
+
+ void execSetupQuery(const char *query) {
+ rc = sqlite3_exec(db, query, NULL, NULL, NULL);
+ if (rc != SQLITE_OK) {
+ throw(query);
+ }
+ }
void checkAndSetupSchema(void) {
- const char *q_zone_str = "SELECT id FROM zones WHERE name=?1";
- const char *q_record_str = "SELECT rdtype, sigtype, rdata FROM rrs WHERE zone_id=:1 AND name=:2 AND (rdtype=:3 OR sigtype=:3)";
try {
loadVersion();
- q_zone = prepare(q_zone_str);
- q_record = prepare(q_record_str);
+ setupPreparedStatements();
+ cout << "Loaded existing schema" << endl;
} catch(...) {
- sqlite3_exec(db, "CREATE TABLE schema_version (version INTEGER NOT NULL)", NULL, NULL, NULL);
- sqlite3_exec(db, "INSERT INTO schema_version VALUES (1)", NULL, NULL, NULL);
- sqlite3_exec(db, "CREATE TABLE zones (id INTEGER PRIMARY KEY, name STRING NOT NULL, rdclass STRING NOT NULL, dnssec BOOLEAN NOT NULL)",
- NULL, NULL, NULL);
- sqlite3_exec(db, "CREATE TABLE records (id INTEGER PRIMARY KEY, zone_id INTEGER NOT NULL, name STRING NOT NULL, rname STRING NOT NULL, rdtype STRING NOT NULL, sigtype STRING NOT NULL, rdata STRING NOT NULL)",
- NULL, NULL, NULL);
+ execSetupQuery("CREATE TABLE schema_version ("
+ "version INTEGER NOT NULL)");
+ execSetupQuery("INSERT INTO schema_version VALUES (1)");
+ execSetupQuery("CREATE TABLE zones ("
+ "id INTEGER PRIMARY KEY, "
+ "name STRING NOT NULL, "
+ "rdclass STRING NOT NULL DEFAULT 'IN', "
+ "dnssec BOOLEAN NOT NULL DEFAULT 0)");
+ execSetupQuery("CREATE TABLE records ("
+ "id INTEGER PRIMARY KEY, "
+ "zone_id INTEGER NOT NULL, "
+ "name STRING NOT NULL, "
+ "rname STRING NOT NULL, "
+ "ttl INTEGER NOT NULL, "
+ "rdtype STRING NOT NULL, "
+ "sigtype STRING, "
+ "rdata STRING NOT NULL)");
+ execSetupQuery("CREATE INDEX records_byname ON records (name)");
+ execSetupQuery("CREATE INDEX records_byrname ON records (rname)");
+ execSetupQuery("CREATE INDEX zones_byname ON zones (name)");
+
+ setupPreparedStatements();
+ cout << "Created new file and schema" << endl;
}
}
sqlite3 *db;
int rc;
- std::string database_name;
+ string database_name;
int database_version;
//
@@ -152,17 +256,52 @@
int main(int argc, char** argv) {
if (argc != 2) {
- std::cerr << "Usage: " << argv[0]
- << " DATABASE SQL-STATEMENT" << std::endl;
+ cerr << "Usage: " << argv[0]
+ << " DATABASE SQL-STATEMENT" << endl;
exit(1);
}
+ SQLITE3 sql(argv[1]);
try {
- SQLITE3 sql(argv[1]);
sql.open();
- std::cout << "Schema version: " << sql.getVersion() << std::endl;
+ cout << "Schema version: " << sql.getVersion() << endl;
} catch (const char *e) {
- std::cout << e << std::endl;
- }
+ cout << e << endl;
+ }
+
+ cout << "Has zone: example.com? " << sql.hasExactZone("example.com") << endl;
+
+ const char *best_match = NULL;
+ const char *target = "www.sql1.example.com";
+ int has_zone = sql.findClosest(target, &best_match);
+ if (has_zone >= 0) {
+ cout << "Has zone or parent for " << target << endl;
+ cout << " " << best_match << endl;
+ } else {
+ cout << "Does not have zone or parent for " << target << endl;
+ }
+
+ best_match = NULL;
+ target = "www.sql2.example.com";
+ has_zone = sql.findClosest(target, &best_match);
+ if (has_zone >= 0) {
+ cout << "Has zone or parent for " << target << endl;
+ cout << " " << best_match << endl;
+ } else {
+ cout << "Does not have zone or parent for " << target << endl;
+ }
+
+ best_match = NULL;
+ target = "www.example.com";
+ has_zone = sql.findClosest(target, &best_match);
+ if (has_zone >= 0) {
+ cout << "Has zone or parent for " << target << endl;
+ cout << " " << best_match << endl;
+ } else {
+ cout << "Does not have zone or parent for " << target << endl;
+ }
+
+ sql.findRecords("example.com", "example.com", "NS");
+ sql.findRecords("example.com", "www.example.com", "A");
}
More information about the bind10-changes
mailing list