Importing a local storage csv into sqlite database table?

[luckachi]

In my project I have created an sqlite database along with 3 tables...what I would like to do it take three csv files that are being downloaded, using the Qt Network Download example, and insert them into their appropriate tables so that I can work with the data (query, etc) in other qml pages within the app.

I have seen some examples about parsing the files and inserting values but at the moment I have not been able to successfully insert the data from the csv files to the tables.

If I include the code below to see if the file can be found, I do receive the "File is Open" response so that is a step in the right direction.

QFile file("filename.csv");
    if(!file.open(QIODevice::ReadOnly)){
        qDebug() << "File not open" << file.error();
    } else {
       qDebug() << "File is open";
    }

QFile file("filename.csv");
    if(!file.open(QIODevice::ReadOnly)){
        qDebug() << "File not open" << file.error();
    } else {
       qDebug() << "File is open";
    }

To copy to clipboard, switch view to plain text mode 

Should this process be included in the Network Download code, which I have in a separate .cpp file or can it go right into the main.cpp file? Also - can someone point me in the direction of some good examples of this or provide some insight on how I might accomplish this?

[d_stranz]

Should this process be included in the Network Download code, which I have in a separate .cpp file or can it go right into the main.cpp file?

This question doesn't make a whole lot of sense. If you are downloading a file from some web site based on the example code, then you are presumably creating a local file that the download is stored in. You know the file exists, because you created it. The code in the DownloadManager's saveToDisk() method returns true if the save was successful.

In any case, the action of downloading a file and then doing something with the contents of the file are two completely unrelated things. If you want to import the CSV file into your database as soon as it is saved, modify the DownloadManager class to add a signal that gets emitted in saveToDisk once the file has been written. Pass the name of the file as a parameter of the signal.

In the class that imports the CSV file, add a slot and connect it to this signal. When the slot gets called, you can open the CSV file and begin your conversion.

[luckachi]

I am still learning Qt / coding / etc so bare with me. I am trying to replicate an application in Qt that we already have available to users so that we can get out of our current development environment and our developer is no longer with us.

I have the Network Download example code in a separate .cpp file versus my main file to keep things clean. It downloads, locally, 3 csv files from our secure ftp - these csv files are updated periodically and the app needs to reflect those changes. What I want to do is take the information from those csv files and put the data an Sqlite database so that it can be queried, etc. This is how we have had our app set up for the last few years so maybe in Qt there is an easier way to handle this?

I currently have this, which creates the local database and the 3 tables:

QString dbName( "mydatabase.db" );
    QFile::remove( dbName ); // delete sqlite file if it exists from a previous run
    QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );
    db.setDatabaseName( dbName );
    db.open();
 
    //Create Tables
 
    QSqlQuery query;
    query.prepare("CREATE TABLE IF NOT EXISTS misin_species_list (cname VARCHAR, family VARCHAR, fdetail VARCHAR, genus VARCHAR, id VARCHAR, kingdom VARCHAR, species VARCHAR, habit VARCHAR)");
    if(!query.exec()){
        qDebug()<<"error:" << query.lastError().text();
    }
 
    QSqlQuery queryanimals;
    queryanimals.prepare("CREATE TABLE IF NOT EXISTS misin_factsheet_animals (cname VARCHAR, description VARCHAR, edrr VARCHAR, family VARCHAR, fdetail VARCHAR, genus VARCHAR, habitat VARCHAR, hdetail VARCHAR, id VARCHAR, damage VARCHAR, reproduction VARCHAR, similar VARCHAR, source VARCHAR, species VARCHAR)");
    if(!queryanimals.exec()){
        qDebug()<<"error:" << queryanimals.lastError().text();
    }
 
    QSqlQuery queryplants;
    queryplants.prepare("CREATE TABLE IF NOT EXISTS misin_factsheet_plants(cname VARCHAR, description VARCHAR, duration VARCHAR, edrr VARCHAR, family VARCHAR,fdetail VARCHAR, flowers VARCHAR, fruitseeds VARCHAR, genus VARCHAR, habit VARCHAR, habitat VARCHAR, hdetail VARCHAR, id VARCHAR, impact VARCHAR, kingdom VARCHAR, leaves VARCHAR, pcode VARCHAR, reproduction VARCHAR, similar VARCHAR, source VARCHAR, species VARCHAR, stems VARCHAR, tmodule VARCHAR)");
    if(!queryplants.exec()){
        qDebug()<<"error:" << queryplants.lastError().text();
    }

QString dbName( "mydatabase.db" );
    QFile::remove( dbName ); // delete sqlite file if it exists from a previous run
    QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );
    db.setDatabaseName( dbName );
    db.open();

    //Create Tables

    QSqlQuery query;
    query.prepare("CREATE TABLE IF NOT EXISTS misin_species_list (cname VARCHAR, family VARCHAR, fdetail VARCHAR, genus VARCHAR, id VARCHAR, kingdom VARCHAR, species VARCHAR, habit VARCHAR)");
    if(!query.exec()){
        qDebug()<<"error:" << query.lastError().text();
    }

    QSqlQuery queryanimals;
    queryanimals.prepare("CREATE TABLE IF NOT EXISTS misin_factsheet_animals (cname VARCHAR, description VARCHAR, edrr VARCHAR, family VARCHAR, fdetail VARCHAR, genus VARCHAR, habitat VARCHAR, hdetail VARCHAR, id VARCHAR, damage VARCHAR, reproduction VARCHAR, similar VARCHAR, source VARCHAR, species VARCHAR)");
    if(!queryanimals.exec()){
        qDebug()<<"error:" << queryanimals.lastError().text();
    }

    QSqlQuery queryplants;
    queryplants.prepare("CREATE TABLE IF NOT EXISTS misin_factsheet_plants(cname VARCHAR, description VARCHAR, duration VARCHAR, edrr VARCHAR, family VARCHAR,fdetail VARCHAR, flowers VARCHAR, fruitseeds VARCHAR, genus VARCHAR, habit VARCHAR, habitat VARCHAR, hdetail VARCHAR, id VARCHAR, impact VARCHAR, kingdom VARCHAR, leaves VARCHAR, pcode VARCHAR, reproduction VARCHAR, similar VARCHAR, source VARCHAR, species VARCHAR, stems VARCHAR, tmodule VARCHAR)");
    if(!queryplants.exec()){
        qDebug()<<"error:" << queryplants.lastError().text();
    }

To copy to clipboard, switch view to plain text mode 

I guess what I am wondering is if the steps above, along with the additional code required to insert the data into the database, should be incorporated into the network download .cpp versus in my main.cpp like it is now (separate from the network download code. I pretty much just need some clarification as to how to go about reading the csv files and importing them into the local sqlite tables.

[d_stranz]

I guess what I am wondering is if the steps above, along with the additional code required to insert the data into the database, should be incorporated into the network download .cpp versus in my main.cpp like it is now (separate from the network download code. I pretty much just need some clarification as to how to go about reading the csv files and importing them into the local sqlite tables.

You can put code anywhere you want as long as it works and does what you want it to.

However, if you want to create a robust and maintainable app that can be supported by the next developer to come along after you no longer have responsibility for the code, then I would suggest that you factor your design into components that have as little dependence on each other as possible.

From the description you have provided, you have at three or four independent things going on:

1 - You have an app that needs to be run periodically. This is what main.cpp is for, and nothing else. It creates the QApplication instance that runs the Qt part of your app, creates the top-level UI component, and starts it running. That's it - a typical 5 or 6 line main.cpp like you see in every Qt example app.

2 - You have a component that needs to monitor an ftp site for updates to a few files. When it sees a change, it downloads the files to a known location on disk.

3 - You have a component that is responsible for maintaining an up-to-date SQLite database. It does this by reading in CSV files and updating the database.

Components 2 and 3 are completely unrelated to each other. The only thing they have in common is that they each know about a certain location where they write or read CSV files. Component 2, the network component doesn't need to know anything about the content of the files, just how to download them and put them in the right place. Component 3, the database update component, doesn't need to know anything about where the files came from, just that when new ones show up, it must update the DB.

So at a minimum, you need three source code files: one containing main(), one pair of cpp and h files to implement the class for component 2, and one pair of cpp and h files to implement that class for component 3.

Component 2's only job is to watch the ftp site and download files when there are changes. The is basically the network example, customized. Maybe this class is called FTPMonitor, and it has methods like checkForChanges() and downloadFiles().

Component 3's only job is to wait for new files and update the database when they show up. This includes the code you posted above. Maybe that class is called DBUpdater, and it has methods like checkForNewFiles() and updateDatabase().

You don't say if your app runs continuously like a service, or if it is only run on demand or according to a schedule. If it does not run continuously, then all component 2 needs to do is to compare timestamps for the local files vs. those on the server and download the new ones.

For component 3, it doesn't matter if the program runs continuously or not. It doesn't do anything if there are no changes.

You could look at Qt's QFileSystemWatcher class, which does exactly what the name implies - it watches files and/or directories for changes, and emits signals when they do. When the program starts up, the DBUpdater component starts the file system watcher on the files or directory and then does nothing else. If the FTPMonitor class downloads something, the QFileSystemWatcher class will emit a signal, which the DBUpdater class uses to start the update.

Alternatively, the FTPMonitor class could have noUpdateRequired() and updateRequired() signals. The first one is emitted after checking the server and finding no changes. The second one is emitted when there are changes, but only after the files have been downloaded and are ready to go.

The DBUpdater class has slots that listen for both of these signals. If it receives the first one, it can call quit() on the app. If it receives the second one, then it updates the DB files and then quits.

A lot of words. The basic idea is that the most maintainable apps are those that clearly separate different functions into separate classes and source code files, don't mix together source code parts that have no relationship to each other, and which minimize the connection between different classes and functions.

[luckachi]

@ d_stranz - thank you for your time with breaking things down and explaining everything, I appreciate it. I will definitely read through your response in more detail but I wanted to respond quickly to one of your questions - this app will be running on mobile devices, phones / tablets for iOS and Android, or at least that is the goal. We currently use development software that costs quite a bit of money that we would like to move away from and develop using something a bit more cost effective and reliable.

[d_stranz]

Can't help you there. I have no experience writing for mobile devices, so I don't know what parts of Qt work and which don't. I am sure there are ports of SQLite for mobile OS (it is implemented in a single source code file) and I would be surprised if Qt's network app didn't work.