[sqlite] [ANN] ODB C++ ORM 2.4.0 Released, Adds Object Loading Views
I am pleased to announce the release of ODB 2.4.0. ODB is an open source object-relational mapping (ORM) system for C++. It allows you to persist C++ objects to a relational database without having to deal with tables, columns, or SQL and without manually writing any of the mapping code. Major new features in this release: * Support for bulk operations in Oracle and SQL Server. Bulk operations can be used to persist, update, or erase a range of objects using a single database statement execution which often translates to a significantly better performance. * Ability to join and load one or more complete objects instead of, or in addition to, a subset of their data members with a single SELECT statement execution (object loading views). * Support for specifying object and table join types in views (LEFT, RIGHT, FULL, INNER, or CROSS). * Support for calling MySQL and SQL Server stored procedures. * Support for defining persistent objects as instantiations of C++ class templates. A more detailed discussion of these features can be found in the following blog post: http://www.codesynthesis.com/~boris/blog/2015/02/11/odb-2-4-0-released/ For the complete list of new features in this version see the official release announcement: http://codesynthesis.com/pipermail/odb-announcements/2015/41.html ODB is written in portable C++ (both C++98/03 and C++11 are supported) and you should be able to use it with any modern C++ compiler. In particular, we have tested this release on GNU/Linux (x86/x86-64/ARM), Windows (x86/x86-64), Mac OS X (x86/x86_64), and Solaris (x86/x86-64/SPARC) with GNU g++ 4.2.x-5.x, MS Visual C++ 2005, 2008, 2010, 2012, and 2013, Sun Studio 12u2, and Clang 3.x. The currently supported database systems are MySQL, SQLite, PostgreSQL, Oracle, and SQL Server. ODB also provides optional profiles for Boost and Qt, which allow you to seamlessly use value types, containers, and smart pointers from these libraries in your persistent classes. More information, documentation, source code, and pre-compiled binaries are available from: http://www.codesynthesis.com/products/odb/ Enjoy, Boris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] ODB C++ ORM 2.3.0 released, adds schema evolution support
I am pleased to announce the release of ODB 2.3.0. ODB is an open source object-relational mapping (ORM) system for C++. It allows you to persist C++ objects to a relational database without having to deal with tables, columns, or SQL and without manually writing any of the mapping code. Major new features in this release: * Support for database schema evolution, including automatic schema migration, immediate and gradual data migration, as well as soft object model changes (ability to work with multiple schema versions using the same C++ classes). For a quick showcase of this functionality see the Changing Persistent Classes section in the Hello World Example chapter: http://www.codesynthesis.com/products/odb/doc/manual.xhtml#2.9 * Support for object sections which provide the ability to split data members of a persistent C++ class into independently loaded/updated groups. * Support for automatic mapping of C++11 enum classes. A more detailed discussion of these features can be found in the following blog post: http://www.codesynthesis.com/~boris/blog/2013/10/30/odb-2-3-0-released/ For the complete list of new features in this version see the official release announcement: http://www.codesynthesis.com/pipermail/odb-announcements/2013/37.html ODB is written in portable C++ (both C++98/03 and C++11 are supported) and you should be able to use it with any modern C++ compiler. In particular, we have tested this release on GNU/Linux (x86/x86-64/ARM), Windows (x86/x86-64), Mac OS X (x86), and Solaris (x86/x86-64/SPARC) with GNU g++ 4.2.x-4.8.x, MS Visual C++ 2005, 2008, 2010, and 2012, Sun Studio 12u2, and Clang 3.x. The currently supported database systems are MySQL, SQLite, PostgreSQL, Oracle, and SQL Server. ODB also provides optional profiles for Boost and Qt, which allow you to seamlessly use value types, containers, and smart pointers from these libraries in your persistent classes. More information, documentation, source code, and pre-compiled binaries are available from: http://www.codesynthesis.com/products/odb/ Enjoy, Boris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] ODB C++ ORM 2.2.0 released
Hi, I am pleased to announce the release of ODB 2.2.0. ODB is an open source object-relational mapping (ORM) system for C++. It allows you to persist C++ objects to a relational database without having to deal with tables, columns, or SQL and without manually writing any of the mapping code. Major new features in this release: * Ability to use multiple database systems (for example, MySQL, SQLite, etc.) from the same application. It comes in the 'static' and 'dynamic' flavors with the latter allowing the application to dynamically load the database support code for individual database systems if and when necessary. * Support for prepared queries which are a thin wrapper around the underlying database system's prepared statements functionality. Prepared queries provide a way to perform potentially expensive query preparation tasks only once and then execute the query multiple times. * Support for change-tracking containers which minimize the number of database operations necessary to synchronize the container state with the database. This release comes with change-tracking equivalents for std::vector and QList. * Support for custom sessions. This mechanism can be used to provide additional functionality, such as automatic change tracking, delayed database operations, auto change flushing, or object eviction. * Support for automatically-derived SQL name transformations. You can now add prefixes/suffixes to table, column, index, and sequence names, convert them to upper/lower case, or do custom regex transformations. * Automatic mapping of char[N] to database VARCHAR(N-1) (or similar). This release also adds support for Qt5 in addition to Qt4 and comes with a guide on using ODB with mobile and embedded systems (Raspberry Pi is used as a sample ARM target). A more detailed discussion of these features can be found in the following blog post: http://www.codesynthesis.com/~boris/blog/2013/02/13/odb-2-2-0-released/ For the complete list of new features in this version see the official release announcement: http://www.codesynthesis.com/pipermail/odb-announcements/2013/25.html ODB is written in portable C++ and you should be able to use it with any modern C++ compiler. In particular, we have tested this release on GNU/Linux (x86/x86-64/ARM), Windows (x86/x86-64), Mac OS X (x86), and Solaris (x86/x86-64/SPARC) with GNU g++ 4.2.x-4.8.x, MS Visual C++ 2008, 2010, and 2012, Sun Studio 12u2, and Clang 3.2. The currently supported database systems are MySQL, SQLite, PostgreSQL, Oracle, and SQL Server. ODB also provides profiles for Boost and Qt, which allow you to seamlessly use value types, containers, and smart pointers from these libraries in your persistent classes. More information, documentation, source code, and pre-compiled binaries are available from: http://www.codesynthesis.com/products/odb/ Enjoy, Boris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] ODB C++ ORM 2.1.0 released, improves SQLite support
Hi, I am pleased to announce the release of ODB 2.1.0. ODB is an open source object-relational mapping (ORM) system for C++. It allows you to persist C++ objects to a relational database without having to deal with tables, columns, or SQL and without manually writing any of the mapping code. Major new features in this release: * Ability to use accessor/modifier functions and expressions to access data members. In most cases ODB is capable of discovering suitable accessor/modifier functions automatically. * Support for virtual (imaginary) data members that can be used to handle the C++ pimpl idiom as well as aggregate or dis-aggregate real data members. * Ability to define database indexes on data members. Multi-member indexes as well as indexes with database-specific index types, methods, and options are supported. * Support for mapping extended database types, such as geospatial types, user-defined types, collections (arrays, table types, etc.), key-value stores, XML, JSON, etc., to suitable C++ types. * The Boost profile library now provides persistence support for the Uuid and Multi-Index container libraries. * The Qt profile library now provides persistence support for the QUuid type. * Support for generating single (combined) database schema file from multiple C++ header files. * SQLite ODB runtime now supports persistence of std::wstring. You can also pass the database name as std::wstring. The default SQLite mapping for float and double now allows the NULL value since SQLite treats NaN values as NULL. This release also adds support for Visual Studio 2012 and Clang 3.1. Specifically, all the runtime libraries, examples, and tests now come with project/solution files for Visual Studio 2012 in addition to 2010 and 2008. A more detailed discussion of these features can be found in the following blog post: http://www.codesynthesis.com/~boris/blog/2012/09/18/odb-2-1-0-released/ For the complete list of new features in this version see the official release announcement: http://www.codesynthesis.com/pipermail/odb-announcements/2012/18.html ODB is written in portable C++ and you should be able to use it with any modern C++ compiler. In particular, we have tested this release on GNU/Linux (x86/x86-64), Windows (x86/x86-64), Mac OS X, and Solaris (x86/x86-64/SPARC) with GNU g++ 4.2.x-4.7.x, MS Visual C++ 2008, 2010, and 2012, Sun Studio 12, and Clang 3.1. The currently supported database systems are MySQL, SQLite, PostgreSQL, Oracle, and SQL Server. ODB also provides profiles for Boost and Qt, which allow you to seamlessly use value types, containers, and smart pointers from these libraries in your persistent classes. More information, documentation, source code, and pre-compiled binaries are available from: http://www.codesynthesis.com/products/odb/ Enjoy, Boris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-Thread Reads to SQLite Database
Hi Richard, Richard Hipp d...@sqlite.org writes: In shared-cache mode, the page cache is shared across threads. That means that each thread must acquire a mutex on the page cache in order to read it. Which means that access to the page cache is serialized. I just ran our concurrency test in different configurations and I observer a similar behavior. That is, in the shared-cache mode, read-only transactions on the same table are executed pretty much sequentially. Also, your explanation doesn't feel quite satisfactory to me. In his original email, Eric mentioned that his table contains just 50 rows. Surely all this data would be loaded into the cache the first time it is requested and then accessed concurrently by all the threads. The only way I can see how the sequential performance could be explained here is if the cache mutex did not distinguish between readers and writers (which would seem to be a fairly natural thing to do). In our test, on the other hand, each thread queries its own set of rows from the table. So, based on your explanation, here each thread should end up with its own set of pages (more or less). However, even in this case, I still observe a near sequential performance. Any idea what else might be going on here? Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-Thread Reads to SQLite Database
Hi Richard, Richard Hipp d...@sqlite.org writes: In shared-cache mode, SQLite acquires a mutex on the shared cache, thus serializing access. Oh, that's quite a surprise. I would have never said that something like this is going on just by reading the documentation page on shared cache: http://www.sqlite.org/sharedcache.html Specifically, quoting Section 2.2, Table Level Locking: When two or more connections use a shared-cache, locks are used to serialize concurrent access attempts on a per-table basis. Tables support two types of locks, read-locks and write-locks. Locks are granted to connections - at any one time, each database connection has either a read-lock, write-lock or no lock on each database table. At any one time, a single table may have any number of active read-locks or a single active write lock. To read data [from] a table, a connection must first obtain a read-lock. To write to a table, a connection must obtain a write-lock on that table. From this I at the time concluded that there could be multiple connections reading from a table concurrently. But it appears I was wrong. Perhaps this page should be updated to reflect this. Specifically, the last paragraph in Section 2.0: There are three levels to the shared-cache locking model, transaction level locking, table level locking and schema level locking. They are described in the following three sub-sections. Should probably read something along these lines: There are four levels to the shared-cache locking model: transaction level locking, table level locking, cache level locking, and schema level locking. They are described in the following three sub-sections. Then a new section should be inserted between 2.2 and 2.3 that reads along these lines: 2.3 Cache Level Locking When a connection needs to access (read or write) data from a table, it must acquire an exclusive mutex on the shared cache. In particular, this means that in the shared cache mode actual access to the database pages, regardless of whether for reading or writing, is always serialized. If you find it suitable, feel free to use this to update the documentation. Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-Thread Reads to SQLite Database
Hi Pavel, Pavel Ivanov paiva...@gmail.com writes: There are four levels to the shared-cache locking model: transaction level locking, table level locking, cache level locking, and schema level locking. They are described in the following three sub-sections. This would be a bad way of documenting this because cache level locking works on a completely different level than transaction, table or schema level locking. The exclusive mutex is taken inside one call to sqlite3_step() and released upon exit. But all other locks have meta-level notion and work between calls to sqlite3_*() functions. I agree. But I also think that documenting it like this is better than not documenting it at all. This read serialization behavior of SQLite was a complete surprise to me. Maybe instead of documenting it, we should just fix it. I personally will rather write code than documentation ;-). Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-Thread Reads to SQLite Database
Hi Marc, Marc L. Allen mlal...@outsitenetworks.com writes: If I understand correctly, the purpose of the overall mutex is to prevent a page from being removed underneath a user. If the standard DB locking semantics are working properly, I think there is no possibility of a page's data from being modified underneath another user. Yes, that's my understanding also. This is all taken care of at higher (transaction and table) locking levels. If the above it true, cache protection semantics are strictly concerned with page management. That is, a page is requested that is not in the cache and needs to be inserted into it. If the cache is full, another page needs to be released. All that is required is protecting pages currently in use from being released. I think, instead of a mutex serializing access to the entire cache, all that is needed is a mutex serializing access to the cache meta-data and the use of reference counts to help the page replacement algorithm make a good choice in which page to remove. All sounds very sensible to me. Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] ODB C++ ORM 2.0.0 released, adds support for C++11, polymorphism
I am pleased to announce the release of ODB 2.0.0. ODB is an open source object-relational mapping (ORM) system for C++. It allows you to persist C++ objects to a relational database without having to deal with tables, columns, or SQL and without manually writing any of the mapping code. Major new features in this release: * Support for C++11 which adds integration with the new C++11 standard library components, including smart pointers and containers. Now you can use std::unique_ptr and std::shared_ptr as object pointers (their lazy versions are also provided). For containers, support was added for std::array, std::forward_list, and the unordered containers. * Support for polymorphism which allows you to persist, load, update, erase, and query objects of derived classes using their base class interfaces. Persistent class hierarchies are mapped to the relational database model using the table-per-difference mapping. * Support for composite object ids which are translated to composite primary keys in the relational database. * Support for the NULL semantics for composite values. This release has also been tested with GCC 4.7 and Clang 3.0 with the ODB compiler now supporting the GCC 4.7 series plugin interface. With this release we are also introducing a free proprietary license for small object models. A more detailed discussion of these features can be found in the following blog post: http://www.codesynthesis.com/~boris/blog/2012/05/02/odb-2-0-0-released/ For the complete list of new features in this version see the official release announcement: http://www.codesynthesis.com/pipermail/odb-announcements/2012/13.html ODB is written in portable C++ and you should be able to use it with any modern C++ compiler. In particular, we have tested this release on GNU/Linux (x86/x86-64), Windows (x86/x86-64), Mac OS X, and Solaris (x86/x86-64/SPARC) with GNU g++ 4.2.x-4.7.x, MS Visual C++ 2008 and 2010, Sun Studio 12, and Clang 3.0. The currently supported database systems are SQLite, MySQL, PostgreSQL, Oracle, and SQL Server. ODB also provides profiles for Boost and Qt, which allow you to seamlessly use value types, containers, and smart pointers from these libraries in your persistent classes. More information, documentation, source code, and pre-compiled binaries are available from: http://www.codesynthesis.com/products/odb/ Enjoy, Boris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] ODB C++ ORM 1.7.0 adds support for optimistic concurrency
Hi, I am pleased to announce the release of ODB 1.7.0. ODB is an open-source object-relational mapping (ORM) system for C++. It allows you to persist C++ objects to a relational database without having to deal with tables, columns, or SQL and without manually writing any of the mapping code. Major new features in this release: * Support for optimistic concurrency using object versioning. * Support for SQL statement execution tracing. * Support for read-only/const data members. * Support for persistent classes without object ids. * Support for the Oracle database, including updates to the Boost and Qt profiles. A more detailed discussion of these features can be found in the following blog post: http://www.codesynthesis.com/~boris/blog/2011/12/07/odb-1-7-0-released/ For the complete list of new features in this version see the official release announcement: http://www.codesynthesis.com/pipermail/odb-announcements/2011/11.html ODB is written in portable C++ and you should be able to use it with any modern C++ compiler. In particular, we have tested this release on GNU/Linux (x86/x86-64), Windows (x86/x86-64), Mac OS X, and Solaris (x86/x86-64/SPARC) with GNU g++ 4.2.x-4.6.x, MS Visual C++ 2008 and 2010, and Sun Studio 12. The currently supported database systems are MySQL, SQLite, PostgreSQL, and Oracle. ODB also provides profiles for Boost and Qt, which allow you to seamlessly use value types, containers, and smart pointers from these libraries in your persistent classes. More information, documentation, source code, and pre-compiled binaries are available from: http://www.codesynthesis.com/products/odb/ Enjoy, Boris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] ODB C++ ORM 1.6.0 released, adds object projections
Hi,
I am pleased to announce the release of ODB 1.6.0.
ODB is an open-source object-relational mapping (ORM) system for C++. It
allows you to persist C++ objects to a relational database without having
to deal with tables, columns, or SQL and without manually writing any of
the mapping code.
The major new feature in this release is the introduction of the view
concept. A view is a light-weight, read-only projection of one or more
persistent objects or database tables or the result of a native SQL query
execution.
Views can be used to load a subset of data members from objects or columns
from database tables, execute and handle results of arbitrary SQL queries,
including aggregate queries, as well as join multiple objects and/or
database tables using object relationships or custom join conditions.
For example, given this persistent class:
#pragma db object
class person
{
...
#pragma db id auto
unsigned long id_;
std::string first_, last_;
unsigned short age_;
};
We can define a view that returns the number of people stored in the
database:
#pragma db view object(person)
struct person_count
{
#pragma db column(count( + person::id_ + ))
std::size_t count;
};
And then use this view to find out how many people are younger than 30:
typedef odb::queryperson_count query;
typedef odb::resultperson_count result;
result r (db.queryperson_count (query::age 30));
cout r.begin ()-count endl;
Other important new features in this release are:
* Support for the NULL semantics and the odb::nullable container.
* Support for the boost::optional container (mapped to columns
with NULL values).
* Support for deleting persistent objects using query expressions.
* Support for storing BLOB data as std::vectorchar.
A more detailed discussion of these features can be found in the following
blog post:
http://www.codesynthesis.com/~boris/blog/2011/10/04/odb-1-6-0-released/
For the complete list of new features in this version see the official
release announcement:
http://www.codesynthesis.com/pipermail/odb-announcements/2011/07.html
ODB is written in portable C++ and you should be able to use it with any
modern C++ compiler. In particular, we have tested this release on GNU/Linux
(x86/x86-64), Windows (x86/x86-64), Mac OS X, and Solaris (x86/x86-64/SPARC)
with GNU g++ 4.2.x-4.6.x, MS Visual C++ 2008 and 2010, and Sun Studio 12.
The currently supported database systems are MySQL, SQLite, and PostgreSQL.
This release has also been tested with the recently released PostgreSQL 9.1.
More information, documentation, source code, and pre-compiled binaries are
available from:
http://www.codesynthesis.com/products/odb/
Enjoy,
Boris
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange foreign key constraint failed with DROP TABLE
Hi Richard, Richard Hipp d...@sqlite.org writes: Dan has convinced me that the SQLite code is correct as documented. The documentation states: Foreign key DML errors are may be reported if: (1) The parent table does not exist... And for the DELETE statement above, the parent table does not exist. So it is appropriate to rais an error. My problem with this interpretation is that the key in question is DEFERRED. The more complete version of the quote above reads like this: Foreign key DML errors are may be reported if: (1) The parent table does not exist, or (2) The parent key columns named in the foreign key constraint do not exist, or ... For DEFERRED keys, rule (2) is not enforced until COMMIT. It is not clear why rule (1) is not postponed until COMMIT as well. I agree, this is probably a very murky area and I am not sure it is worth changing this. However, I think the current SQLite behavior is not very consistent. Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange foreign key constraint failed with DROP TABLE
Hi Dan, Dan Kennedy danielk1...@gmail.com writes: It is. Now fixed in the trunk. Thanks for the fix. I patched 3.7.7.1 with it and indeed this now works: BEGIN TRANSACTION; DROP TABLE employer; DROP TABLE employee; COMMIT; However, this transaction: BEGIN TRANSACTION; DELETE FROM employer; DROP TABLE employer; DELETE FROM employee; DROP TABLE employee; COMMIT; Still issues Error: no such table: main.employer after the second DELETE. I don't think this should happen either. What do you think? Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Strange foreign key constraint failed with DROP TABLE
Hi,
I am observing a foreign key constraint failed error that looks
like an SQLite bug to me. I have tried the following using 3.7.7.1:
First I create two tables:
PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;
CREATE TABLE employer (name TEXT NOT NULL PRIMARY KEY);
INSERT INTO employer VALUES('Simple Tech Ltd');
CREATE TABLE employee (
id INTEGER NOT NULL PRIMARY KEY,
employer TEXT NOT NULL,
FOREIGN KEY (employer) REFERENCES employer (name) DEFERRABLE INITIALLY
DEFERRED);
INSERT INTO employee VALUES(1, 'Simple Tech Ltd');
COMMIT;
Now I want to drop them. If I do this:
BEGIN TRANSACTION;
DROP TABLE employer;
DROP TABLE employee;
COMMIT;
I get Error: foreign key constraint failed when executing COMMIT.
Here is a relevant quote from the SQLite Foreign Key Support[1] page:
If foreign key constraints are enabled when it is prepared, the DROP TABLE
command performs an implicit DELETE to remove all rows from the table before
dropping it. The implicit DELETE does not cause any SQL triggers to fire, but
may invoke foreign key actions or constraint violations. If an immediate
foreign key constraint is violated, the DROP TABLE statement fails and the
table is not dropped. If a deferred foreign key constraint is violated, then
an error is reported when the user attempts to commit the transaction if the
foreign key constraint violations still exist at that point. Any foreign key
mismatch errors encountered as part of an implicit DELETE are ignored.
So seeing that my foreign key is deferred and at the end of the transaction
all the violations have been resolved (there are no more rows in either
table and there are no other tables -- this is a fresh database), I don't
see why I am getting the error.
If we change the order of DROPs, then everything works:
BEGIN TRANSACTION;
DROP TABLE employee;
DROP TABLE employer;
COMMIT;
It also helps if we do explicit DELETEs before DROPs:
BEGIN TRANSACTION;
DELETE FROM employer;
DELETE FROM employee;
DROP TABLE employer;
DROP TABLE employee;
COMMIT;
This, however, does not work:
BEGIN TRANSACTION;
DELETE FROM employer;
DROP TABLE employer;
DELETE FROM employee;
DROP TABLE employee;
COMMIT;
In addition to the above error, this transaction also issues Error: no
such table: main.employer after the second DELETE.
Can someone confirm if this is a bug in SQLite? If so, I would also
appreciate any suggestions for work-arounds. I know I can disable
constraint checking, but in my case it is not easy since I am already
in transaction.
[1] http://www.sqlite.org/foreignkeys.html
Thanks,
Boris
--
Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog
Compiler-based ORM system for C++ http://codesynthesis.com/products/odb
Open-source XML data binding for C++ http://codesynthesis.com/products/xsd
XML data binding for embedded systems http://codesynthesis.com/products/xsde
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange foreign key constraint failed with DROP TABLE
Hi Stephan, Stephan Beal sgb...@googlemail.com writes: The workaround would be simply to switch the order of the DROP TABLEs, wouldn't it? In this simple case, yes. However, I need to support multiple levels of references which makes creating perfect DROP order difficult or even impossible (e.g., if there are cycles). Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange foreign key constraint failed with DROP TABLE
Hi William, Duquette, William H (318K) william.h.duque...@jpl.nasa.gov writes: What if you defined the foreign key with ON DELETE CASCADE? Dropping the employer table will delete the employees. That would be bad for the normal use. In other words, I don't want the CASCADE semantics for those references and I don't think there is a way to globally turn this on for all the foreign keys. Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange foreign key constraint failed with DROP TABLE
Hi William,
Duquette, William H (318K) william.h.duque...@jpl.nasa.gov writes:
On 8/19/11 10:18 AM, Boris Kolpackov bo...@codesynthesis.com wrote:
There's something odd here. You have the FK constraints deferred, and
your code looks like this:
BEGIN TRANSACTION;
DROP TABLE employer;
DROP TABLE employee;
COMMIT;
According to the sqlite docs, dropping a table when FK constraints are
enabled does an implicit DELETE FROM first. That DELETE FROM is causing
the constraint violations. But you have them deferred; they won't be
reported until the COMMIT, and only if the constraints are still violated
at that time. But by then, you've also dropped the employee table, so how
can there still be FK constraint violations?
That's exactly what I have said in my original post ;-).
Am I missing something?
If so, then that would be the two of us. Though I think this is a bug
in SQLite.
Are you sure you're dropping the tables in a transaction?
The SQL code I included in my original email can be copy-n-pasted into
the sqlite3 utility to verify this behavior. E.g., do:
$ cat | sqlite3 /tmp/fresh.db
Then copy-n-paste the following SQL (including the last blank line):
PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;
CREATE TABLE employer (name TEXT NOT NULL PRIMARY KEY);
INSERT INTO employer VALUES('Simple Tech Ltd');
CREATE TABLE employee (
id INTEGER NOT NULL PRIMARY KEY,
employer TEXT NOT NULL,
FOREIGN KEY (employer) REFERENCES employer (name) DEFERRABLE INITIALLY
DEFERRED);
INSERT INTO employee VALUES(1, 'Simple Tech Ltd');
COMMIT;
BEGIN TRANSACTION;
DROP TABLE employer;
DROP TABLE employee;
COMMIT;
And you will get:
Error: near line 21: foreign key constraint failed
Boris
--
Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog
Compiler-based ORM system for C++ http://codesynthesis.com/products/odb
Open-source XML data binding for C++ http://codesynthesis.com/products/xsd
XML data binding for embedded systems http://codesynthesis.com/products/xsde
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] ODB C++ ORM 1.4.0 released, adds support for Qt, inheritance
Hi,
I am pleased to announce the release of ODB 1.4.0.
ODB is an open-source object-relational mapping (ORM) system for C++. It
allows you to persist C++ objects to a relational database without having
to deal with tables, columns, or SQL and without manually writing any of
the mapping code.
Major new features in this release:
* Qt profile providing persistence support for Qt basic types, date-time
types, smart pointers, and containers. For example:
#pragma db object
class Employee
{
...
QString first_name_;
QString last_name_;
QDate born_;
QSetQString emails_;
QByteArray publicKey_;
QSharedPointerEmployer employer_;
};
* Support for non-polymorphic object inheritance including abstract base
classes.
* Automatic mapping of C++ enumerations to database ENUM or integer types.
For example, in MySQL enum color {red, green, blue}; would be mapped to
ENUM('red', 'green', 'blue').
A more detailed discussion of the new features can be found in the following
blog post:
http://codesynthesis.com/~boris/blog/2011/04/27/odb-1-4-0-released/
For the complete list of new features in this version see the official
release announcement:
http://www.codesynthesis.com/pipermail/odb-announcements/2011/04.html
ODB is written in portable C++ and you should be able to use it with any
modern C++ compiler. In particular, we have tested this release on GNU/Linux
(x86/x86-64), Windows (x86/x86-64), Mac OS X, and Solaris (x86/x86-64/SPARC)
with GNU g++ 4.2.x-4.5.x, MS Visual C++ 2008 and 2010, and Sun Studio 12.
More information, documentation, source code, and pre-compiled binaries are
available from:
http://www.codesynthesis.com/products/odb/
Enjoy,
Boris
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] ODB C++ ORM 1.3.0 released, adds support for SQLite
Hi, I am pleased to announce the release of ODB 1.3.0. ODB is an open-source object-relational mapping (ORM) system for C++. It allows you to persist C++ objects to a relational database without having to deal with tables, columns, or SQL and without manually writing any of the mapping code. This release adds support for the SQLite embedded database including the integration of the shared cache mode and unlock notification functionality for multi-threaded applications. A more detailed discussion of the new features can be found in the following blog post: http://www.codesynthesis.com/~boris/blog/2011/04/06/odb-1-3-0-released/ For the complete list of new features in this version see the official release announcement: http://www.codesynthesis.com/pipermail/odb-announcements/2011/03.html ODB is written in portable C++ and you should be able to use it with any modern C++ compiler. In particular, we have tested this release on GNU/Linux (x86/x86-64), Windows (x86/x86-64), Mac OS X, and Solaris (x86/x86-64/SPARC) with GNU g++ 4.2.x-4.5.x, MS Visual C++ 2008 and 2010, and Sun Studio 12. More information, documentation, source code, and pre-compiled binaries are available from: http://www.codesynthesis.com/products/odb/ Enjoy, Boris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unexpected deadlocks in shared cache mode with unlock_notify
Hi Simon, Simon Slavin writes: On 28 Mar 2011, at 8:45pm, Boris Kolpackov wrote: As expected, I periodically get deadlocks (SQLITE_LOCKED return code from unlock_notify()) for the second transaction due to the read to write lock upgrade. But I also get deadlocks reported for the first transaction and this is something that I didn't expect to happen. My reasoning is that this transaction tries to get the write lock right away so I don't see how it can be blocking some other transaction. Can someone explain this? You're not using BEGIN IMMEDIATE, so your lock isn't happening when you start the transaction, it's happening on the first command which makes a change. By that time another thread may have already done something. Although it will theoretically increase the amount of contention it might be worth using BEGIN IMMEDIATE instead of just BEGIN. Try this just for testing purposes: it's a trivial change to your code and simple to reverse. If your deadlocks go away then you can make it permanent. Since you apparently have the INSERT and UPDATE commands planned before you start your transaction won't lock up the database for long. I tried this. If I add IMMEDIATE (or EXCLUSIVE) to the first transaction (three INSERTs), nothing changes, I still get deadlocks reported for this transaction and the second. This lack of any difference is what I would expect since the first statement that this transaction executes (INSERT) is a write. So by adding IMMEDIATE/EXCLUSIVE all I did is changed the time when the write lock is acquired, namely from the first INSERT to BEGIN. If I add IMMEDIATE to the second transaction (SELECT then UPDATE), then the deadlocks go away for both transaction as one would expect. But that doesn't help me much. My problem is not that I get deadlocks -- I expect to get them for the second transaction (read to write upgrade). My problem is that I get deadlocks in the first transaction (goes straight to the write lock). This I don't understand. Thanks, Boris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unexpected deadlocks in shared cache mode with unlock_notify
Hi Simon, Simon Slavin writes: On 29 Mar 2011, at 10:48am, Boris Kolpackov wrote: If I add IMMEDIATE (or EXCLUSIVE) to the first transaction (three INSERTs), nothing changes, [snip] If I add IMMEDIATE to the second transaction (SELECT then UPDATE), then the deadlocks go away for both transaction as one would expect. In your testing, did you test adding IMMEDIATE to /both/ transactions ? No, since adding it just to the second transaction already got rid of all the deadlocks in both transactions. But I just ran the test with both transactions starting immediately. As I would expect, there are no deadlocks. Thanks, Boris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unexpected deadlocks in shared cache mode with unlock_notify
Hi, Boris Kolpackov bo...@codesynthesis.com writes: Half of the threads executes the following transaction: BEGIN INSERT INSERT INSERT COMMIT The other half of the threads runs the following transaction: BEGIN SELECT UPDATE COMMIT As expected, I periodically get deadlocks (SQLITE_LOCKED return code from unlock_notify()) for the second transaction due to the read to write lock upgrade. But I also get deadlocks reported for the first transaction and this is something that I didn't expect to happen. Ok, after some debugging I figured this one out. SQLite has a peculiar shared cache locking semantics in that besides table-level locks there is also a transaction-level lock, or, more specifically, the write transaction lock. There can only be one transaction operating on the shared cache that is writing. If another transaction tries to upgrade from reading to writing, then SQLITE_LOCKED is returned. As a result, the above two transactions have the following locking protocols (pseudo-code). First transaction: trans_lock table_lock (w) Second transaction: table_lock (r) trans_lock table_lock (w) Here trans_lock indicates acquisition of the write transaction lock and table_lock indicates acquisition of the table lock, either for reading or writing. From this it is quite obvious why the first transaction also deadlocks. Needless to say I find the fact that a writing transaction needs to acquire two separate locks very unintuitive from the user's perspective. It would have been fine if it were implementation details, but as we can see from the above, it can lead to the unexpected, user-visible behavior. One way to fix this would be to un-start the transaction (and release the write transaction lock) in the first transaction if acquiring the write table lock failed. If you think of it, it is kind of pointless to keep the write transaction lock if we failed to acquire the (first) write table lock and therefore won't be able to write anything to the database. Not sure how easy or difficult it will be to implement. Maybe it will be easier to downgrade the transaction to reading since there is already a mechanism for upgrading it to writing. Just some ideas... Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] unexpected deadlocks in shared cache mode with unlock_notify
Hi, I run several threads each having a shared cache connection to the same database containing a single table. No other threads or processes access this database. Half of the threads executes the following transaction: BEGIN INSERT INSERT INSERT COMMIT Where each INSERT inserts a unique row into the table. The other half of the threads runs the following transaction: BEGIN SELECT UPDATE COMMIT Where SELECT selects a row from the table and UPDATE updates this same row. The threads use the sqlite3_unlock_notify() function to aid concurrency in a pretty much the same way as described on the Using the sqlite3_unlock_notify() API[1] page. As expected, I periodically get deadlocks (SQLITE_LOCKED return code from unlock_notify()) for the second transaction due to the read to write lock upgrade. But I also get deadlocks reported for the first transaction and this is something that I didn't expect to happen. My reasoning is that this transaction tries to get the write lock right away so I don't see how it can be blocking some other transaction. Can someone explain this? The first transaction behaves as if, for some reason, it first obtained the read lock and then tried to upgrade it to the write lock. But this doesn't seem to be the case -- I tried to run just the first transaction in multiple threads and there are no deadlocks. Any ideas would be much appreciated. [1] http://www.sqlite.org/unlock_notify.html Boris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unexpected deadlocks in shared cache mode with unlock_notify
Hi Igor, Igor Tandetnik itandet...@mvps.org writes: On 3/28/2011 3:45 PM, Boris Kolpackov wrote: The first transaction behaves as if, for some reason, it first obtained the read lock and then tried to upgrade it to the write lock. No, it first obtained a RESERVED lock, and later tries to promote it to EXCLUSIVE lock. Does this (RESERVED-PENDING-EXCLUSIVE) applies to table locks in the shared cache as well? My understanding (based on the documentation and studying the code) is that this only applies to the database pages. And that the shared cache only has read and write locks. You can't have a deadlock with just one transaction being locked out. A deadlock, by definition, involves two transactions that mutually prevent each other from making progress. I am running identical transactions in multiple threads in parallel. Two transactions that execute the same SQL can deadlock. Thanks, Boris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Contributing VC 9 and 10 project/solution files
Hi, I've created project/solution files for VC 9 and 10 that build SQLite3 DLLs in multiple configurations (Debug/Release, Win32/x64). They also set proper preprocessor macros when building the sources (taken from mkdll.sh). I would like to contribute them if there is interest. I think it makes sense to provide them in the amalgamation .zip file, similar to how autotools-based build system is part of the tar ball. Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
