[soci-users] executing an dbschema file with soci?

[Witold E Wolski]

I have a text file with the db schema definition
dbschema.sql

Is there a way to let my application to set up the db using soci?
I am using sqlite.

my idea (which I could not get working) was to read in the file by
line and than to run a loop
i.e. to execute the sql.

std::string bla = "heresql.sqlite";

try{
soci::session sql_( soci::sqlite3 , bla );
std::vector<std::string> lines;
ralab::base::utils::readLines("../sql/dbschema.sql",lines);
for(size_t i = 0 ; i < lines.size() ; ++i)
{
sql_<< lines[i];
}
catch( ...

soci throws a error:

No sqlite statement created
...

Here is how the txt file with the schema looks.
<dbschame.sql>
-- basic sample information
PRAGMA foreign_keys = ON;

drop table if exists sample;
CREATE TABLE sample (
id integer primary key,
name text,
file text,
description text
);
</dbschame.sql>


Any suggestions?

[Mateusz Loskot]

Post by Witold E Wolski
I have a text file with the db schema definition
dbschema.sql
Is there a way to let my application to set up the db using soci?
I am using sqlite.
my idea (which I could not get working) was to read in the file by
line and than to run a loop
i.e. to execute the sql.

You need to provide such feature of file-based SQL on your own
as there is no such thing in SOCI.

Post by Witold E Wolski
std::string bla = "heresql.sqlite";
try{
soci::session sql_( soci::sqlite3 , bla );
std::vector<std::string> lines;
ralab::base::utils::readLines("../sql/dbschema.sql",lines);
for(size_t i = 0 ; i < lines.size() ; ++i)
{
sql_<< lines[i];
}
catch( ...
No sqlite statement created
...
Here is how the txt file with the schema looks.
<dbschame.sql>
-- basic sample information
PRAGMA foreign_keys = ON;
drop table if exists sample;
CREATE TABLE sample (
id integer primary key,
name text,
file text,
description text
);

I presume, the error is caused by the fact that
you read and execute with SOCI individual lines,
but your file contains multi-line statements.
IOW, your code seem to be equivalent of:

sql_ << "CREATE TABLE sample (";
sql_ << "id integer primary key,";
sql_ << "name text,";
sql_ << "file text,";
sql_ << "description text";

This must fail, obviously.

Best regards,

[Witold E Wolski]

Thx Mateusz.

How about an sql parser in soci?
Parsing an sql file is a quite common task I would think.
It's nothing big, i did't find a dedicated api.

regards

Post by Mateusz Loskot

Post by Witold E Wolski
I have a text file with the db schema definition
dbschema.sql
Is there a way to let my application to set up the db using soci?
I am using sqlite.
my idea (which I could not get working) was to read in the file by
line and than to run a loop
i.e. to execute the sql.

You need to provide such feature of file-based SQL on your own
as there is no such thing in SOCI.

Post by Witold E Wolski
std::string bla = "heresql.sqlite";
try{
soci::session sql_( soci::sqlite3 , bla );
std::vector<std::string> lines;
ralab::base::utils::readLines("../sql/dbschema.sql",lines);
for(size_t i = 0 ; i < lines.size() ; ++i)
{
sql_<< lines[i];
}
catch( ...
No sqlite statement created
...
Here is how the txt file with the schema looks.
<dbschame.sql>
-- basic sample information
PRAGMA foreign_keys = ON;
drop table if exists sample;
CREATE TABLE sample (
id integer primary key,
name text,
file text,
description text
);

I presume, the error is caused by the fact that
you read and execute with SOCI individual lines,
but your file contains multi-line statements.
sql_ << "CREATE TABLE sample (";
sql_ << "id integer primary key,";
sql_ << "name text,";
sql_ << "file text,";
sql_ << "description text";
This must fail, obviously.
Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net
------------------------------------------------------------------------------
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60134791&iu=/4140/ostg.clktrk
_______________________________________________
soci-users mailing list
https://lists.sourceforge.net/lists/listinfo/soci-users

[Mateusz Loskot]

Post by Witold E Wolski
Thx Mateusz.
How about an sql parser in soci?
Parsing an sql file is a quite common task I would think.

Given the SQL variety in among backends, it's a slippery area.

Just to give you some background:
By design, AFAIR, SOCI relies on SQL as an important part of the abstraction,
as a kind of a client of SQL, but interacting with SQL as little as
necessary, for binding only
Otherwise, SOCI would have to take over more heavyweight
responsibility of parsing
SQL dialects of all supported backends, a proper headache :)

However, I think it is a good idea to host extra tools in SOCI, extensions.
Based on my experience with Boost.Geometry [1] and Boost.GIL [1],
I find extensions an nice way to allow a library users to shape it as
they need it.

A SOCI-oriented self-contained SQL parser does not have to be tightly
coupled with SOCI core, it can be a non-intrusive addition.

If anyone feels like contributing SQL parser, I'd be supportive for
adding it to SOCI as an extension.
Ideally, if there was a generator as well (I think of Sporit's Karma)
and configurable modes of operation for all dialects we need :-)

BTW, the only standalone SQL parser of resonable codebase size,
though incomplete, is the Boost.Spirit sample [3].

Post by Witold E Wolski
It's nothing big, i did't find a dedicated api.

There is no such API, apart from access to query and
interface to plug a query transformations [4]

[1] http://svn.boost.org/svn/boost/trunk/boost/geometry/extensions/
[2] http://svn.boost.org/svn/boost/trunk/boost/gil/extension/
[3] http://boost-spirit.com/repository/applications/show_contents.php
[4] http://soci.sourceforge.net/doc/3.2/queries.html

Best regards,