[sqlite] C++ ORM
On Mon, Mar 9, 2015 at 3:25 PM, Simon Slavin wrote: > > On 9 Mar 2015, at 9:38pm, Scott Robison wrote: > >> A co-worker who is working on a project is interested in finding out if >> there is an effective ORM for C++ / SQLite. I've not used one so I'm >> turning to the list to see if anyone has a recommendation. > > For those playing along at home, ORM == Object Relational Mapping. In other > words you do Object-oriented programming in C++ and the objects are stored in > a SQLite database. > > I'm not aware that this problem has been solved well in any language or with > any database engine. It might seem like a terrific oppotunity to write a > demonstration library, but differences in how languages do OO and how > databases store data seem to make this a difficult problem to crack. I'd be > interested in any solutions that use SQLite with any popular OO language. You did qualify it with "solved well", so I can only comment that depending on your requirements various ORMs may work well enough for the job. I've done projects in Python with Django's ORM (MVC web apps), SQLAlchemy, and am finding peewee [1] to work well for a small project I'm working on right now. Most OO language ORMs work about like this: Model Definition: Model classes, fields and model instances all map to database concepts: Thing :: Corresponds to... Model class :: Database table Field instance :: Column on a table Model instance :: Row in a database table All that said, I'm not familiar with C++ options myself. [1] http://peewee.readthedocs.org/en/latest/index.html -- Darren Spruell phatbuckett at gmail.com
[sqlite] fts4 support in distributions of SQLite?
Greetings, I'm working on an app that may end up open sourced and released publicly for others to use. It uses SQLite and the records have a comments field for which I'd like to use a fts4 vtable to enable search. My few test systems show that support for fts4 is present, but I'm trying to learn if it's a reasonable assumption that the usual SQLite build/deployment "out there" includes support for the feature. Is it a reasonable expectation that package maintainers on Linux/BSD flavors, OS X port frameworks, etc. ship the package with fts4 enabled? Is it typically only missing if someone has built locally and intentionally disabled support for e.g. optimization reasons or something? Wondering how lazy I can get on working around, disclaiming, or otherwise handling a key feature that could be missing. Thanks, -- Darren Spruell phatbuckett at gmail.com
Re: [sqlite] Confusion on 'foreign key mismatch' errors
On Sun, Nov 11, 2012 at 11:16 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 12 Nov 2012, at 5:49am, Darren Spruell <phatbuck...@gmail.com> wrote:
>
>> sqlite> CREATE TABLE ip (
>> ...> id INTEGER PRIMARY KEY, date_added DEFAULT CURRENT_DATE,
>> ...> ip VARCHAR(15) NOT NULL UNIQUE, comment INTEGER NOT NULL,
>> ...> FOREIGN KEY(comment) REFERENCES comment);
>> sqlite> CREATE INDEX ipcommentindex ON ip(comment);
>> sqlite> INSERT INTO comment VALUES ('this is a comment.');
>> sqlite> SELECT rowid,content FROM comment;
>> 1|this is a comment.
>> sqlite> SELECT last_insert_rowid();
>> 1
>> sqlite> INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',last_insert_rowid());
>> Error: foreign key mismatch
>
> You slipped up in the FOREIGN KEY definition. You're relating it to the text
> field of the comment table. You should be relating it to the 'rowid' field,
> possibly something like.
>
> ...> FOREIGN KEY(comment) REFERENCES comment(rowid));
>
> However, you cannot relate to rowid, because it's not a properly defined
> field. So define an 'id' field for the comment table, then make sure you
> refer to it in your FOREIGN KEY definition.
If I'm not mistaken, that's a problem too, as type definitions are
ignored on FTS columns:
# http://www.sqlite.org/fts3.html
If column names are explicitly provided for the FTS table as part of
the CREATE VIRTUAL TABLE statement, then a datatype name may be
optionally specified for each column. This is pure syntactic sugar,
the supplied typenames are not used by FTS or the SQLite core for any
purpose. The same applies to any constraints specified along with an
FTS column name - they are parsed but not used or recorded by the
system in any way.
I would want my 'id' field to behave as an autoincrementing integer
field (i.e. INTEGER PRIMARY KEY) to work properly.
So I think I might be at an impasse with this plan. Maybe I'll drop
the foreign key and restructure around it.
Thx!
--
Darren Spruell
phatbuck...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Confusion on 'foreign key mismatch' errors
I'm stuck on some errors related to my use of foreign key constraints
in my application. The following illustrates:
$ sqlite3
SQLite version 3.7.9 --SOURCE-ID--
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE VIRTUAL TABLE comment USING fts4();
sqlite> CREATE TABLE ip (
...> id INTEGER PRIMARY KEY, date_added DEFAULT CURRENT_DATE,
...> ip VARCHAR(15) NOT NULL UNIQUE, comment INTEGER NOT NULL,
...> FOREIGN KEY(comment) REFERENCES comment);
sqlite> CREATE INDEX ipcommentindex ON ip(comment);
sqlite> INSERT INTO comment VALUES ('this is a comment.');
sqlite> SELECT rowid,content FROM comment;
1|this is a comment.
sqlite> SELECT last_insert_rowid();
1
sqlite> INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',last_insert_rowid());
Error: foreign key mismatch
sqlite> INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',1);
Error: foreign key mismatch
Thinking I understand the requirements for foreign key constraints, I
don't know why my INSERT into ip table returns 'foreign key mismatch'
error.
When I leave foreign key constraints off, things work as I would expect:
sqlite> CREATE VIRTUAL TABLE comment USING fts4();
sqlite> CREATE TABLE ip (
...> id INTEGER PRIMARY KEY, date_added DEFAULT CURRENT_DATE,
...> ip VARCHAR(15) NOT NULL UNIQUE, comment INTEGER NOT NULL,
...> FOREIGN KEY(comment) REFERENCES comment);
sqlite> CREATE INDEX ipcommentindex ON ip(comment);
sqlite> INSERT INTO comment VALUES ('this is a comment.');
sqlite> SELECT rowid,content FROM comment;
1|this is a comment.
sqlite> SELECT last_insert_rowid();
1
sqlite> INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',last_insert_rowid());
sqlite> SELECT * FROM ip;
1|2012-11-12|10.0.1.1|1
I suspect the issue might stem from trying to use the rowid in the
comment table as the parent key in the foreign key on the ip table,
reading:
# http://www.sqlite.org/foreignkeys.html
The parent key is the column or set of columns in the parent table
that the foreign key constraint refers to. This is normally, but not
always, the primary key of the parent table. The parent key must be a
named column or columns in the parent table, not the rowid.
...but then this kind of reads like it's supported:
# http://answers.oreilly.com/topic/1955-how-to-use-full-text-search-in-sqlite/
Unlike traditional tables, the ROWID of an FTS table is stable through
a vacuum (VACUUM in Appendix C), so it can be reliably referenced
through a foreign key.
I'm struggling to find a clear way to achieve a foreign key
constrained relation between these tables if a.) the FTS table can't
define an INTEGER PRIMARY KEY column to function as the parent key for
a child table, or b.) child tables can't reference the rowid on an FTS
parent table as the parent key.
Clue bat appreciated.
--
Darren Spruell
phatbuck...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
