Using RCDB with an SQLite file

[markito3]

Presently in Hall D we do not have a way to use the SQLite form of the RCDB database from C++. In particular our "mcsmear" programs queries the RCDB to determine conditions for smearing Monte Carlo events and that works fine with MySQL. When the RCDB_CONNECTION environment variable is set to point to an SQLite file, we get the error:

src/JANA/JEventLoop.cc:685  EXCEPTION : RCDB built without SQLite3 support. Rebuild it using 'with-sqlite=true' flag

We are using RCDB version 0.02

Rebuilding RCDB with "with-sqlite=true" does not fix the problem; mcsmear must be compiled and linked with the -DRCDB_SQLITE compiler switch thrown when building mcsmear. In particular the file

$HALLD_HOME/src/programs/Simulation/mcsmear/mcsmear_config.cc

includes RCDB/Connection.h where the SQLite connection support is turned on. Trying to do this by hand (compiling mcsmear_config.cc with -DRCDB_SQLITE) we get

In file included from /u/scratch/marki/gluex_install_scratch/5/gluex_top/rcdb/rcdb_0.02/cpp/include/RCDB/Connection.h:16:0,
                 from programs/Simulation/mcsmear/mcsmear_config.h:13,
                 from programs/Simulation/mcsmear/mcsmear_config.cc:2:
/u/scratch/marki/gluex_install_scratch/5/gluex_top/rcdb/rcdb_0.02/cpp/include/RCDB/SqLiteProvider.h: In member function 'virtual std::unique_ptr<rcdb::Condition> rcdb::SqLiteProvider::GetCondition(uint64_t, const rcdb::ConditionType&)':
/u/scratch/marki/gluex_install_scratch/5/gluex_top/rcdb/rcdb_0.02/cpp/include/RCDB/SqLiteProvider.h:87:41: error: 'sqlite3_int64' was not declared in this scope
             _getConditionQuery.bind(1, (sqlite3_int64)run);

and although there is a header file in the RCDB tree that defines sqlite3_int64,

#include <SQLite/sqlite3.h>

gives

In file included from /u/scratch/marki/gluex_install_scratch/5/gluex_top/rcdb/rcdb_0.02/cpp/include/RCDB/SqLiteProvider.h:10:0,
                 from /u/scratch/marki/gluex_install_scratch/5/gluex_top/rcdb/rcdb_0.02/cpp/include/RCDB/Connection.h:16,
                 from programs/Simulation/mcsmear/mcsmear_config.h:13,
                 from programs/Simulation/mcsmear/mcsmear_config.cc:2:
/u/scratch/marki/gluex_install_scratch/5/gluex_top/rcdb/rcdb_0.02/cpp/include/SQLite/sqlite3.h:3455:20: error: conflicting declaration 'typedef struct Mem sqlite3_value'
 typedef struct Mem sqlite3_value;

Any help appreciated.

[DraTeots]

RCDB C++ API is a header only since possible 0.03 (I finished it just before I left). Which means there is no more librcdb and separate build step for RCDB. That also means that MySQL and SQLite libraries should be linked to the application which includes RCDB headers.

Here is the updated documentation

https://github.com/JeffersonLab/rcdb/wiki/Cpp#installation

[markito3]

Thanks for the updated documentation!

Three questions:

1. Can we have both mysql and sqlite support in the same binary?
2. I am assuming that this will require changes to sim-recon, no?
3. I do not see the 0.03 tag. Can I tag it from the master?

[DraTeots]

1. Can we have both mysql and sqlite support in the same binary?

Yes, sure, just add both RCDB_MYSQL and RCDB_SQLITE and all dependencies for MySQL and SQLite

2. I am assuming that this will require changes to sim-recon, no?

No code changes. But at least uncomment SQLITE in SBMS

Here is the code for AddRCDB. I would propose changing AddRCDB to something like:

##################################
# SQLite
##################################
def AddSQLite(env):
    if "SQLITE_LINKFLAGS" not in AddSQLite.__dict__:
        AddSQLite.SQLITE_LINKFLAGS= "-lsqlite3"
    AddLinkFlags(env, AddSQLite.SQLITE_LINKFLAGS)


##################################
# RCDB
##################################
def AddRCDB(env):
    rcdb_home = os.getenv('RCDB_HOME')
    if(rcdb_home != None) :
        env.AppendUnique(CXXFLAGS = ['-DHAVE_RCDB'])

        # add MySQL
        env.Append(CPPDEFINES='RCDB_MYSQL')
        AddMySQL(env)

        # add SQlite
        env.Append(CPPDEFINES='RCDB_SQLITE')
        AddSQLite(env)

(Would appreciate it to be tested because hasn't handled sim-recon installation on the latest Ubuntu)

3. I do not see the 0.03 tag. Can I tag it from the master?

I think, after p. 2 is tested.

[markito3]

Still need this line in sbms.py, no?

	RCDB_CPPPATH = ["%s/cpp/include" % (rcdb_home), "%s/cpp/include/SQLite" % (rcdb_home)]

Is there any library needed for SQLite?

[markito3]

Even after dropping the search of cpp/include/SQLite (thought that would help) I get errors like:

Linux_RHEL7-x86_64-gcc4.8.5/programs/Simulation/mcsmear/CCALSmearer.o: In function hddm_s::streamable::streamer(hddm_s::ostream&)': /local/scratch/three_test/rcdb_0.03/cpp/include/RCDB/SQLiteCpp.h:1911: multiple definition of SQLite::Backup::~Backup()'
.Linux_RHEL7-x86_64-gcc4.8.5/programs/Simulation/mcsmear/BCALSmearer.o:/local/scratch/three_test/rcdb_0.03/cpp/include/RCDB/SQLiteCpp.h:1911: first defined here

and

/usr/include/c++/4.8.2/bits/stl_list.h:1546: multiple definition of SQLite::FLOAT' .Linux_RHEL7-x86_64-gcc4.8.5/programs/Simulation/mcsmear/BCALSmearer.o:/usr/include/c++/4.8.2/bits/stl_list.h:114: first defined here /local/scratch/three_test/sim-recon/Linux_RHEL7-x86_64-gcc4.8.5/lib/libTRIGGER.a(DL1MCTrigger_factory.o): In function std::list<hddm_s::HitView*, std::allocator<hddm_s::HitView*> >::_M_transfer(std::_List_iterator<hddm_s::HitView*>, std::_List_iterator<hddm_s::HitView*>, std::_List_iterator<hddm_s::HitView*>)':
/usr/include/c++/4.8.2/bits/stl_list.h:1546: multiple definition of `SQLite::INTEGER'
.Linux_RHEL7-x86_64-gcc4.8.5/programs/Simulation/mcsmear/BCALSmearer.o:/usr/include/c++/4.8.2/bits/stl_list.h:1562: first defined here

when linking mcsmear. I do have sqlite installed via rpm (in case that is relevant):

sqlite-3.7.17-8.el7.x86_64
sqlite-devel-3.7.17-8.el7.x86_64

[DraTeots]

I think that may stay. If somebody sets RCDB_HOME manually (without calling source $RCDB_HOME/environment.something) this will help.

RCDB_CPPPATH = ["%s/cpp/include" % (rcdb_home)]

multiple definition of ...

Looks very much like something has not been fully rebuilt. I've double checked sim-recon code for sqlite occurrences.

[markito3]

I did a clean build and am still getting the same errors.

It looks like it does not like RCDB/SQLiteCpp.h which gets included via RCDB/SqLiteProvider.h. SQLiteCpp.h looks like it is a copy of a SQLite library header file. Could it be in conflict with something coming from the RHEL7 distribution?

[DraTeots]

Maybe the problem is that we now linking -lsqlite3 and CCDB has its own embedded sqlite. So they conflict on centos because it has older version of SQLite. So then lets remove -lsqlite3 and see if it works...

I'm installing centos on VM. Will try to reinstall and get finally sim-recon working with this problem reproduced.

[DraTeots]

But meanwhile could you just remove -lsqlite3 from the build and try?

[markito3]

Just dropping the -lsqlite3 gives lots of errors like:

.Linux_RHEL7-x86_64-gcc4.8.5/programs/Simulation/mcsmear/MyProcessor.o: In function SQLite::Database::loadExtension(char const*, char const*)': /local/scratch/three_test/rcdb/cpp/include/RCDB/SQLiteCpp.h:2279: undefined reference to sqlite3_enable_load_extension'
/local/scratch/three_test/rcdb/cpp/include/RCDB/SQLiteCpp.h:2283: undefined reference to sqlite3_load_extension' .Linux_RHEL7-x86_64-gcc4.8.5/programs/Simulation/mcsmear/smear.o: In function SQLite::Database::loadExtension(char const*, char const*)':
/local/scratch/three_test/rcdb/cpp/include/RCDB/SQLiteCpp.h:2279: undefined reference to sqlite3_enable_load_extension' /local/scratch/three_test/rcdb/cpp/include/RCDB/SQLiteCpp.h:2283: undefined reference to sqlite3_load_extension'

so I guess the sqlite3 library is really needed. Could it be that rcdb/cpp/include/RCDB/SQLiteCpp.h is inconsistent with the sqlite3 library from RedHat7?

[markito3]

Looks like we are using SQLiteCpp, no? Don't we have to build it as a library? If so, then how can RCDB be header only now?

If we have to build it as a separate library, that would not kill me.

[DraTeots]

No... It is my highly customized version. Look here:
SRombauts/SQLiteCpp#132

[markito3]

Making another attempt. Still getting the multiple definition of SQLite related objects reported back on December 12. Might there be a conflict with how CCDB pulls in SQLite? The multiple definitions could be because both CCDB and RCDB needed the same definitions, but doing them in different ways.

Speculation admittedly.

[markito3]

That is not it.

I made programs with both RCDB and CCDB calls, that worked with sqlite in both cases. No problem.

Now I am thinking that it may be the way RCDB/Connection.h is included. If it is in the global namespace of any of the objects, we are in trouble, no?