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");
}
