Deploy sqlite database with application on OS X

[klaus]

hi,

i want to deploy an existing sqlite database with my qt application on OS X. I read about inserting the database's path in OTHER_FILES in .pro file, but that does not work. Can anyone point me the right way? thanks.

[DenisKormalev]

Resource system should help you if I understood correctly your problem

[harryF]

There are several ways to do that. You can install additional files with qmake:

"qmake reference":http://doc.qt.nokia.com/4.6/qmake-environment-reference.html#installs

e.g. install it to /opt/myapp/1.0/mydatabase

Or, you copy the sqlite database into your application bundle. You can access the path with some native Mac OS X calls, see "Accessing the Bundle Path" here:

"Qt docs":http://doc.qt.nokia.com/4.6/mac-differences.html

[Felix]

you can also make a shell script, and call it in the qmake_post_link event. The script can then copy it anywhere you want

[klaus]

I just want to include a database in my application. and of course I want users to be able to write/read in/from this database. is there any EASY way to do that with Qt Creator?

if I include it in my resources, theres no database which I can see in my terminal....

[DenisKormalev]

If you need it only under Mac OSX then simpliest way is to put it into bundle, as harryF said. Not sure that it can be done via QtCreator, but it can be scripted easy one time and then used at each build.

[klaus]

hi,

thanks for your help so far. but I don't get it. I tried adding

@documentation.path = /Users/myUser/program/doc
documentation.files = /Users/myUser/test/*
INSTALLS += documentation@

in my .pro file. That should copy all from ..../test into ..../program/doc, shouldn't it? I compiled it, but it does not. Perhaps anyone could help me out with some code. In my source directory i have a file named "teams.db", this should be copied into myApp.app/data/

Of course I could move the file in after compiling with a terminal command, but it would be nice if there's another way in QtCreator (e.g. like in VisualStudio, where you can set files to be deployed beside the .exe). It would be great if that would work for windows, too.

[harryF]

@klaus - you have to run "make install" to copy the files. "make" will just build the binaries, but not deploy them.

[klaus]

hm ok, but it does not work. "make: *** No rule to make target `install'. Stop."

"make" doesn't work either "make: Nothing to be done for `myProgram.pro'."

and: I'd like to run it from QtCreator and not do "make install" every time i want to test...

[klaus]

hi,

i managed that. I also changed my .pro.user file and it now creates the application executable in the right directory, so that there's only one application directory, no matter if it's compiled with terminal or by Qt Creator. But if I execute my application from Qt Creator (by clicking on the green arrow), I get an error if I want to open a SQLite database.

QSqlError(-1, "Error opening database", "unable to open database file")

If I start the application manually (double click on the app in the directory), everything works well...

Database path is "data/teams.tbl" relative to my application path.
I open it with
@QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "teams");
db.setDatabaseName("data/teams.tbl");@

[hardcodes.de]

You may want to transport the database with your App bundle but some users may not have the permissions to write to that directory.
So on first startup you could copy it to an appropriate destination.
What is appropriate depends on the kind of application (sandboxed or not).

See http://developer.apple.com/library/mac/documentation/FileManagement/Conceptual/FileSystemProgrammingGuide/FileSystemOverview/FileSystemOverview.html#//apple_ref/doc/uid/TP40010672-CH2-SW7

[m.hosseini]

@klaus
Hi,

1. At the first step you have to add your Database file in your resources as shown as below.
   
2. then go to the data access layer and open data base like this.

try
    {
        //SQLITE Mode;
        QString dbName="CarInfoDB.db";
        QString dbPath=":/CarInfoDB.db";

        QFile file(dbPath);
        if(file.exists()){
            if (!QSqlDatabase::contains(dbPath)){
                _db = QSqlDatabase::addDatabase("QSQLITE",dbPath);
                _db.setDatabaseName(dbName);
            }
        }
        else{
            //emit losingConnection_Signal();
            return false;
        }

        if(!_db.isOpen()){
            _db.open();
            if(_db.isOpen()){
                //_db.exec("PRAGMA foreign_keys=ON");
                return true;
            }
            else{
                return false;
            }
        }
        else{
            return true;
        }
    }catch (...) {
        return false;
    }

3. after run "qmake" and rebuild project you have to create all of database tables by executing DLL(data definition language) queries in this step you have to run your project and create tables by executing queries like this.

 if(dbManager->OpenDb())
    {
        QString query=QString("CREATE TABLE tblCarInfo (Id integer not null)");
        
        QSqlQuery *q=new QSqlQuery(dbManager->getDbContext());
        q->setForwardOnly(true);
        q->prepare(query);
        
        q->exec();
        
        
        qInfo()<<q->lastError();
        q->finish();
        q->clear();
        // delete q;
        dbManager->CloseDb();
        return true;
    }

4. finally data base create beside of project.

[SGaist]

@m-hosseini hi,

Which benefits does your approach bring ?

From the looks of it, you will create an empty database anyway and the one from the resources is not used.

Also, a database in the resources would be read-only so you have to copy it to your drive before using it.

Finally, it's a bad idea to create the database by default in the same folder as the executable because it might be deployed in a read-only folder.