void ServicesDb::createPendingMapIndexes() { for (int i = 0; i < _pendingMapIndexes.size(); i++) { long mapId = _pendingMapIndexes[i]; _execNoPrepare(QString("ALTER TABLE %1 " "ADD CONSTRAINT current_nodes_changeset_id_fkey_%2 FOREIGN KEY (changeset_id) " "REFERENCES %3 (id) MATCH SIMPLE " "ON UPDATE NO ACTION ON DELETE NO ACTION ") .arg(_getNodesTableName(mapId)) .arg(_getMapIdString(mapId)) .arg(_getChangesetsTableName(mapId))); _execNoPrepare(QString("CREATE INDEX %1_tile_idx ON %2 USING btree (tile)") .arg(_getNodesTableName(mapId)) .arg(_getNodesTableName(mapId))); _execNoPrepare(QString("ALTER TABLE %1 " "ADD CONSTRAINT current_relations_changeset_id_fkey_%2 FOREIGN KEY (changeset_id) " "REFERENCES %3 (id) MATCH SIMPLE " "ON UPDATE NO ACTION ON DELETE NO ACTION ") .arg(_getRelationsTableName(mapId)) .arg(_getMapIdString(mapId)) .arg(_getChangesetsTableName(mapId))); _execNoPrepare(QString("ALTER TABLE %1 " "ADD CONSTRAINT current_way_nodes_node_id_fkey_%2 FOREIGN KEY (node_id) " "REFERENCES %3 (id) MATCH SIMPLE " "ON UPDATE NO ACTION ON DELETE NO ACTION, " "ADD CONSTRAINT current_way_nodes_way_id_fkey_%2 FOREIGN KEY (way_id) " "REFERENCES %4 (id) MATCH SIMPLE " "ON UPDATE NO ACTION ON DELETE NO ACTION") .arg(_getWayNodesTableName(mapId)) .arg(_getMapIdString(mapId)) .arg(_getNodesTableName(mapId)) .arg(_getWaysTableName(mapId))); _execNoPrepare(QString("ALTER TABLE %1 " "ADD CONSTRAINT current_ways_changeset_id_fkey_%2 FOREIGN KEY (changeset_id) " "REFERENCES %3 (id) MATCH SIMPLE " "ON UPDATE NO ACTION ON DELETE NO ACTION ") .arg(_getWaysTableName(mapId)) .arg(_getMapIdString(mapId)) .arg(_getChangesetsTableName(mapId))); } _pendingMapIndexes.clear(); }
void ServicesDb::deleteMap(long mapId) { _dropTable(_getRelationMembersTableName(mapId)); _dropTable(_getRelationsTableName(mapId)); _dropTable(_getWayNodesTableName(mapId)); _dropTable(_getWaysTableName(mapId)); _dropTable(_getNodesTableName(mapId)); _dropTable(_getChangesetsTableName(mapId)); _execNoPrepare("DROP SEQUENCE IF EXISTS " + _getNodeSequenceName(mapId) + " CASCADE"); _execNoPrepare("DROP SEQUENCE IF EXISTS " + _getWaySequenceName(mapId) + " CASCADE"); _execNoPrepare("DROP SEQUENCE IF EXISTS " + _getRelationSequenceName(mapId) + " CASCADE"); _exec("DELETE FROM maps WHERE id=:id", (qlonglong)mapId); }
void OsmApiDbChangesetWriter::write(const QString sql) { LOG_INFO("Executing changeset SQL queries against OSM API database..."); QString changesetInsertStatement; QString elementSqlStatements = ""; const QStringList sqlParts = sql.split(";"); for (int i = 0; i < sqlParts.size(); i++) { const QString sqlStatement = sqlParts[i]; if (i == 0) { if (!sqlStatement.toUpper().startsWith("INSERT INTO CHANGESETS")) { throw HootException( "The first SQL statement in a changeset SQL file must create a changeset."); } else { changesetInsertStatement = sqlStatement + ";"; } } else { elementSqlStatements += sqlStatement + ";"; } } if (elementSqlStatements.trimmed().isEmpty()) { throw HootException("No element SQL statements in sql file"); } _db.transaction(); //had problems here when trying to prepare these queries (should they be?); the changeset writing //needs to be done before the element writing, hence the separate queries _execNoPrepare(changesetInsertStatement); _execNoPrepare(elementSqlStatements); _db.commit(); LOG_INFO("Changeset SQL queries execute finished against OSM API database."); }
long ServicesDb::insertMap(QString displayName, int userId, bool publicVisibility) { if (_insertMap == 0) { _insertMap.reset(new QSqlQuery(_db)); _insertMap->prepare("INSERT INTO maps (display_name, user_id, public, created_at) " "VALUES (:display_name, :user_id, :public, NOW()) " "RETURNING id"); } _insertMap->bindValue(":display_name", displayName); _insertMap->bindValue(":user_id", userId); _insertMap->bindValue(":public", publicVisibility); long mapId = _insertRecord(*_insertMap); QString mapIdStr = _getMapIdString(mapId); _copyTableStructure("changesets", _getChangesetsTableName(mapId)); _copyTableStructure("current_nodes", "current_nodes" + mapIdStr); _copyTableStructure("current_relation_members", "current_relation_members" + mapIdStr); _copyTableStructure("current_relations", "current_relations" + mapIdStr); _copyTableStructure("current_way_nodes", "current_way_nodes" + mapIdStr); _copyTableStructure("current_ways", "current_ways" + mapIdStr); _execNoPrepare("CREATE SEQUENCE " + _getNodeSequenceName(mapId)); _execNoPrepare("CREATE SEQUENCE " + _getRelationSequenceName(mapId)); _execNoPrepare("CREATE SEQUENCE " + _getWaySequenceName(mapId)); _execNoPrepare(QString("ALTER TABLE %1 " "ALTER COLUMN id SET DEFAULT NEXTVAL('%4'::regclass)") .arg(_getNodesTableName(mapId)) .arg(_getNodeSequenceName(mapId))); _execNoPrepare(QString("ALTER TABLE %1 " "ALTER COLUMN id SET DEFAULT NEXTVAL('%4'::regclass)") .arg(_getRelationsTableName(mapId)) .arg(_getRelationSequenceName(mapId))); _execNoPrepare(QString("ALTER TABLE %1 " "ALTER COLUMN id SET DEFAULT NEXTVAL('%4'::regclass)") .arg(_getWaysTableName(mapId)) .arg(_getWaySequenceName(mapId))); // remove the index to speed up inserts. It'll be added back by createPendingMapIndexes _execNoPrepare(QString("DROP INDEX %1_tile_idx") .arg(_getNodesTableName(mapId))); _pendingMapIndexes.append(mapId); return mapId; }
void OsmApiDb::deleteData() { // delete ways data first _execNoPrepare("DELETE FROM current_relation_members CASCADE"); _execNoPrepare("DELETE FROM current_relation_tags CASCADE"); _execNoPrepare("DELETE FROM current_relations CASCADE"); _execNoPrepare("ALTER SEQUENCE current_relations_id_seq RESTART WITH 1"); _execNoPrepare("DELETE FROM relation_members CASCADE"); _execNoPrepare("DELETE FROM relation_tags CASCADE"); _execNoPrepare("DELETE FROM relations CASCADE"); // delete relations data 2nd _execNoPrepare("DELETE FROM current_way_nodes CASCADE"); _execNoPrepare("DELETE FROM current_way_tags CASCADE"); _execNoPrepare("DELETE FROM current_ways CASCADE"); _execNoPrepare("ALTER SEQUENCE current_ways_id_seq RESTART WITH 1"); _execNoPrepare("DELETE FROM way_nodes CASCADE"); _execNoPrepare("DELETE FROM way_tags CASCADE"); _execNoPrepare("DELETE FROM ways CASCADE"); // delete nodes data 3rd _execNoPrepare("DELETE FROM current_node_tags CASCADE"); _execNoPrepare("DELETE FROM current_nodes CASCADE"); _execNoPrepare("ALTER SEQUENCE current_nodes_id_seq RESTART WITH 1"); _execNoPrepare("DELETE FROM node_tags CASCADE"); _execNoPrepare("DELETE FROM nodes CASCADE"); // delete changesets _execNoPrepare("DELETE FROM changesets_subscribers CASCADE"); _execNoPrepare("DELETE FROM changeset_tags CASCADE"); _execNoPrepare("DELETE FROM changesets CASCADE"); _execNoPrepare("ALTER SEQUENCE changesets_id_seq RESTART WITH 1"); // delete users _execNoPrepare("DELETE FROM users CASCADE"); _execNoPrepare("ALTER SEQUENCE users_id_seq RESTART WITH 1"); }