File indexing completed on 2024-07-14 14:25:22

0001 // SPDX-License-Identifier: LGPL-2.1-or-later
0002 //
0003 // SPDX-FileCopyrightText: 2011 Dennis Nienhüser <nienhueser@kde.org>
0004 //
0005 
0006 #include "SqlWriter.h"
0007 
0008 #include <QDebug>
0009 #include <QSqlDatabase>
0010 #include <QSqlError>
0011 
0012 namespace Marble
0013 {
0014 
0015 SqlWriter::SqlWriter( const QString &filename, QObject* parent ) :
0016     Writer( parent ), m_placemarkId( 0 )
0017 {
0018     QSqlDatabase database = QSqlDatabase::addDatabase( "QSQLITE" );
0019     database.setDatabaseName( filename );
0020     if ( !database.open() ) {
0021         qCritical() << "Failed to connect to database";
0022         return;
0023     }
0024 
0025     execQuery( "DROP TABLE IF EXISTS placemarks;" );
0026     execQuery( "CREATE TABLE placemarks ("
0027                " regionId INTEGER,"
0028                " nameId INTEGER,"
0029                " number VARCHAR(8),"
0030                " category INTEGER,"
0031                " lon FLOAT(8),"
0032                " lat FLOAT(8) )" );
0033     execQuery( "DROP TABLE IF EXISTS names" );
0034     execQuery( "CREATE TABLE names ("
0035                " id INTEGER PRIMARY KEY,"
0036                " name VARCHAR(50) )" );
0037     execQuery( "DROP TABLE IF EXISTS regions" );
0038     execQuery( "CREATE TABLE regions ("
0039                " id INTEGER PRIMARY KEY,"
0040                " parent INTEGER NOT NULL,"
0041                " lft INTEGER NOT NULL,"
0042                " rgt INTEGER NOT NULL,"
0043                " name VARCHAR(50),"
0044                " lon FLOAT(8),"
0045                " lat FLOAT(8) )" );
0046     execQuery( "DROP VIEW IF EXISTS places" );
0047     execQuery( "CREATE VIEW places AS "
0048                " SELECT"
0049                "  placemarks.regionId AS region,"
0050                "  names.name AS name,"
0051                "  placemarks.number AS number,"
0052                "  placemarks.category AS category,"
0053                "  placemarks.lon AS lon,"
0054                "  placemarks.lat AS lat"
0055                " FROM names"
0056                " INNER JOIN placemarks"
0057                " ON names.id=placemarks.nameId" );
0058     execQuery( "BEGIN TRANSACTION" );
0059 }
0060 
0061 SqlWriter::~SqlWriter()
0062 {
0063     execQuery( "END TRANSACTION" );
0064     execQuery( "CREATE INDEX namesIndex ON names(name)" );
0065     execQuery( "CREATE INDEX placemarksIndex ON placemarks(regionId,nameId,category)" );
0066     execQuery( "CREATE INDEX regionsIndex ON regions(name,parent,lft,rgt)" );
0067 }
0068 
0069 void SqlWriter::addOsmRegion( const OsmRegion &region )
0070 {
0071     QSqlQuery query;
0072     query.prepare( "INSERT INTO regions"
0073                    " (id, parent, lft, rgt, name, lon, lat)"
0074                    " VALUES (?, ?, ?, ?, ?, ?, ?)" );
0075     query.addBindValue( ( qint32 ) region.identifier() );
0076     query.addBindValue( ( qint32 ) region.parentIdentifier() );
0077     query.addBindValue( ( qint32 ) region.left() );
0078     query.addBindValue( ( qint32 ) region.right() );
0079     query.addBindValue( region.name() );
0080     query.addBindValue( region.longitude() );
0081     query.addBindValue( region.latitude() );
0082     execQuery( query );
0083 }
0084 
0085 void SqlWriter::addOsmPlacemark( const OsmPlacemark &placemark )
0086 {
0087     if ( m_lastPlacemark.second != placemark.name() && !m_placemarks.contains( placemark.name() ) ) {
0088         m_lastPlacemark.first = ++m_placemarkId;
0089         m_lastPlacemark.second = placemark.name();
0090         m_placemarks[m_lastPlacemark.second] = m_lastPlacemark.first;
0091 
0092         QSqlQuery insertQuery;
0093         insertQuery.prepare( "INSERT INTO names"
0094                              " (id, name)"
0095                              " VALUES (?, ?)" );
0096         insertQuery.addBindValue( m_lastPlacemark.first );
0097         insertQuery.addBindValue( m_lastPlacemark.second );
0098         execQuery( insertQuery );
0099     }
0100 
0101     Q_ASSERT( m_placemarks.contains( placemark.name() ) );
0102 
0103     QSqlQuery query;
0104     query.prepare( "INSERT INTO placemarks"
0105                    " (regionId, nameId, number, category, lon, lat)"
0106                    " VALUES (?, ?, ?, ?, ?, ?)" );
0107     query.addBindValue( ( qint32 ) placemark.regionId() );
0108     query.addBindValue( m_placemarks[placemark.name()] );
0109     query.addBindValue( placemark.houseNumber() );
0110     query.addBindValue( ( qint32 ) placemark.category() );
0111     query.addBindValue( placemark.longitude() );
0112     query.addBindValue( placemark.latitude() );
0113     execQuery( query );
0114 }
0115 
0116 void SqlWriter::execQuery( const QString &query ) const
0117 {
0118     QSqlQuery sqlQuery( query );
0119     if ( sqlQuery.lastError().isValid() ) {
0120         qCritical() << "Problems occurred when executing the query" << query;
0121         qCritical() << "SQL error: " << sqlQuery.lastError();
0122     }
0123 }
0124 
0125 void SqlWriter::execQuery( QSqlQuery &query ) const
0126 {
0127     query.exec();
0128     if ( query.lastError().isValid() ) {
0129         qCritical() << "Problems occurred when executing the query" << query.executedQuery();
0130         qCritical() << "SQL error: " << query.lastError();
0131     }
0132 }
0133 
0134 }