Re: [sqlite] Question about SQLite features.
From: Kees Nuyt k.n...@zonnet.nl Thursday, November 11, 2010 10:34:51 AM Stored procedures don't enforce business rules by themselves. Constraints and triggers do. To enforce business rules stored as procedures in the database, one would need an access system which prevents direct modification of table data. As an embedded database SQLite definately is in an other niche of the DBMS market. Stored procedures can often be replaced by INSTEAD OF triggers on views, that's quite powerful. OK, they can't truly enforce business rules but nothing enforces good design on my C++ code either. What it does is allow the business rules to be packaged as a complete unit with the data and I claim that would be better design and more maintainable over the life of the project. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com 330819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE3 - sqlite3_step bug?
Richard,, I don't know if this helps but here is a stack track while the code appears to be stuck in a loop... winRead(sqlite3_file * id=0x04418048, void * pBuf=0x0487d10c, int amt=1024, __int64 offset=3043618816) Line 27073 + 0x1c bytes C sqlite3OsRead(sqlite3_file * id=0x04418048, void * pBuf=0x0487d10c, int amt=1024, __int64 offset=3043618816) Line 11852 + 0x20 bytes C readDbPage(PgHdr * pPg=0x0487d098) Line 34007 + 0x21 bytes C sqlite3PagerAcquire(Pager * pPager=0x04417f68, unsigned int pgno=2972285, PgHdr * * ppPage=0x0012e720, int noContent=0) Line 34403 + 0x9 bytesC sqlite3BtreeGetPage(BtShared * pBt=0x04417c78, unsigned int pgno=2972285, MemPage * * ppPage=0x0012e920, int noContent=0) Line 38145 + 0x17 bytesC getAndInitPage(BtShared * pBt=0x04417c78, unsigned int pgno=2972285, MemPage * * ppPage=0x0012e920) Line 38211 + 0x13 bytes C moveToChild(BtCursor * pCur=0x0482f1c8, unsigned int newPgno=2972285) Line 40616 + 0x11 bytes C moveToLeftmost(BtCursor * pCur=0x0482f1c8) Line 40747 + 0xd bytes C sqlite3BtreeNext(BtCursor * pCur=0x0482f1c8, int * pRes=0x0012ed98) Line 41151 + 0x9 bytes C sqlite3BtreeNext(BtCursor * pCur=0x0482f1c8, int * pRes=0x0012ed98) Line 41141 + 0xd bytes C sqlite3VdbeExec(Vdbe * p=0x0482f7c8) Line 54960 + 0x1e bytes C sqlite3Step(Vdbe * p=0x0482f7c8) Line 49388 + 0x9 bytes C sqlite3_step(sqlite3_stmt * pStmt=0x0482f7c8) Line 49449 + 0x9 bytes C From: drhsql...@gmail.com [mailto:drhsql...@gmail.com] On Behalf Of Richard Hipp Sent: Friday, November 12, 2010 1:17 PM To: David Levinson; sqlite-users@sqlite.org Subject: Re: SQLITE3 - sqlite3_step bug? On Fri, Nov 12, 2010 at 1:06 PM, David Levinson dlevin...@worldnow.com wrote: I have an 11GB database and when I attempt to query the database for the max(column) value the code within sqlite3_step() gets stuck in a loop for hours and hours and never seems to return. Do you know why this is the case and how it can be resolved? I stepped into the code and all I see page allocations getting created over and over and over. I just updated to the latest sqlite3 updates and the issue continues. My sense is that this is something related to 64 bit indexes. Could this be? If column is not indexed, then SQLite has to loop through all 11GB of your database file looking for the maximum value. That can take time (though measured in 10s of seconds, not hours). What does it say if you prepend EXPLAIN QUERY PLAN to the beginning of your query? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite doesn't recognize newly added table
I have somewhat of a strange issue with SQLite 3.7.3. I am cross-compiling the standard SQLite amalgamation 3.7.3 using GCC 3.4.4 on Linux for a MIPS big endian system. The cross-compiler uses uClibc as the standard library. Compilation proceeds with no errors. I copy the sqlite command line utility executable to the MIPS system and run it from the command line. ./sqlite3 /tmp/mnt/mydata.db sqlite create table tbl1 (one varchar(10)); sqlite insert into tbl1 values('hello world'); Error: no such table: tbl1 However, if I quit from the sqlite commandline and check my database, I do find that the tbl1 has been created!!. SQLite simply thinks that the table is not there however. If I restart my sqlite3 commandline and reattach to the same database and then do another insert, it still thinks that the table is not there and reports the same no such table error. I have tried compilation with a variety of compile flags without any change to this behavior. I have tried disabling threading support but that doesn't help. I see no errors and no other debug messages which give me a clue on what could be wrong. I am quite willing to debug this issue to figure out why sqlite is unable to load tables from the existing database if someone can point me in the general direction / functions in the source I should look at that could cause such behavior. Thanks in advance! Best Regards, Madhan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite doesn't recognize newly added table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/13/2010 04:47 PM, Madhan Kanagavel wrote: I am cross-compiling the standard SQLite amalgamation 3.7.3 using GCC 3.4.4 on Linux for a MIPS big endian system. The cross-compiler uses uClibc as the standard library. I'd suggest trying with uClibc on your host first just to eliminate platform issues. I see no errors and no other debug messages which give me a clue on what could be wrong. The best you can do is compile with -g and -DSQLITE_DEBUG. The latter will enable all the assertions in the SQLite code which will catch logic issues. If that doesn't catch anything then it is likely an I/O issue (eg doing a write and then a read of the same area doesn't give the same data back). You can enable I/O tracing by compiling with -DSQLITE_ENABLE_IOTRACE and doing whatever else is required for it to output the traces. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzfOCQACgkQmOOfHg372QRjFQCgguaYtk3khtbxSVR21OyrqybV f88AniGimkl1l2ZyNhJj+uMh9hjDotR2 =8BbS -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users