Hello,
I have problems with a specific query when using the "sqlite3_step"
function:
SELECT DISTINCT interfaces_int_id,device_dev_id FROM devInterface
INNER JOIN interfaces ON
devInterface.interfaces_int_id=interfaces.intf_id
INNER JOIN nlink ON nlink.interfaces_intf_id=interfaces.intf_id
INNER JOIN neighbor ON neighbor.neighbor_id=nlink.neighbor_neighbor_id
I only posted the parts of the query which I identified to cause the
problem.
For querying the database, I use the following code:
http://www.dreamincode.net/forums/topic/122300-sqlite-in-c/
and when debugging it, it hangs at
while(true)
{
---> result = sqlite3_step(statement);
if(result == SQLITE_ROW)
{
std::vector<std::string> values;
...
}
...
}
Depending on the database size, it sometimes takes minutes to get a result.
But when using the Firefox plugin "SQLite Manager", it "only" takes 1-2
seconds.
In case the query takes several minutes, the "neighbor" and "nlink" tables
have more than 150k entries.
For buliding the database I used the Forward engineer feature of the MySQL
Workbench tool, and modified the syntax to work for sqlite3:
CREATE TABLE IF NOT EXISTS device(dev_id INTEGER PRIMARY KEY AUTOINCREMENT,
type INT, hwtype INT, dataSource INT, hostname TEXT, sw_version TEXT,
stpBridgeID TEXT, stpProtocol TEXT);
CREATE TABLE IF NOT EXISTS interfaces(intf_id INTEGER PRIMARY KEY
AUTOINCREMENT, intfName TEXT, intfType TEXT, phl INT, macAddress TEXT,
ipAddress TEXT, subnetMask TEXT, duplex TEXT, speed TEXT, status TEXT,
description TEXT, l2l3 TEXT, errLvl INT, loadLvl INT, channel_intf_id INT,
vpc_id INT, CONSTRAINT fk_interfaces_interfaces1 FOREIGN KEY
(channel_intf_id) REFERENCES interfaces (intf_id) ON DELETE CASCADE ON
UPDATE CASCADE);
CREATE TABLE IF NOT EXISTS devInterface (interfaces_int_id INT,
device_dev_id INT, cdp_cdp_id INT, PRIMARY KEY (interfaces_int_id,
device_dev_id, cdp_cdp_id), CONSTRAINT fk_dev_interface_interfaces1 FOREIGN
KEY (interfaces_int_id) REFERENCES interfaces (intf_id) ON DELETE CASCADE ON
UPDATE CASCADE, CONSTRAINT fk_dev_interface_device1 FOREIGN KEY
(device_dev_id) REFERENCES device (dev_id) ON DELETE CASCADE ON UPDATE
CASCADE, CONSTRAINT fk_devInterface_cdp1 FOREIGN KEY (cdp_cdp_id) REFERENCES
cdp (cdp_id) ON DELETE CASCADE ON UPDATE CASCADE);
CREATE TABLE IF NOT EXISTS neighbor (neighbor_id INTEGER PRIMARY KEY
AUTOINCREMENT, l2_addr TEXT NULL , l3_addr TEXT NULL);
CREATE TABLE IF NOT EXISTS nlink (neighbor_neighbor_id INT,
interfaces_intf_id INT, PRIMARY KEY (neighbor_neighbor_id,
interfaces_intf_id), CONSTRAINT fk_table1_neighbor1 FOREIGN KEY
(neighbor_neighbor_id ) REFERENCES neighbor (neighbor_id) ON DELETE CASCADE
ON UPDATE CASCADE, CONSTRAINT fk_table1_interfaces1 FOREIGN KEY
(interfaces_intf_id) REFERENCES interfaces (intf_id) ON DELETE CASCADE ON
UPDATE CASCADE);
I use SQLITE Version 3.6.22.
Thanks,
mspoerr
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
