Re: [sqlite] Using SQLite with R*Tree and FTS3 support in iOS
4.3 MB seems really too big... you are probably building a debug version of the library. -- Marco Bambini http://www.sqlabs.com On May 16, 2011, at 5:01 PM, Tito Ciuro wrote: > Hello, > > I have a question about SQLite running on iOS. If I'm not mistaken, SQLite on > iOS is not compiled with R*Tree and FTS3. Compiling a static library of > SQLite's amalgamated version weighs at about 4.3 MB, which represents almost > 25% of the 20 MB-per-app allowed on the App Store. For many, this is a major > setback because many apps can easily reach this limit. > > My question is: since a "light" version of SQLite is already included in iOS, > would it be too complicated to build a static library with only R*Tree and > FTS3 support? The idea being of course that the app would link against iOS' > SQLite and the app's R*Tree/FTS3 library, thus reducing the app's footprint > considerably. > > Are there dependencies that would make this attempt a nightmare? Has anyone > gone through this? > > Thanks in advance, > > -- Tito > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] vacuum and rowids
Dave please take a look at this blog post: http://www.sqlabs.com/blog/?p=51 -- Marco Bambini http://www.sqlabs.com On Apr 28, 2011, at 9:36 PM, Dave Hayden wrote: > When the VACUUM feature was added I took a look at using it to keep database > file sizes down, but discovered that it changed rowids and messed up my > references between tables (or what I gather the database people call "foreign > keys"). I'm playing around with this again and it looks like rowids aren't > affected if I have an INTEGER PRIMARY KEY column, but I don't want to rebuild > the existing tables if I don't have to. > > After more poking, it appears that rowids might not be changed by a vacuum if > I have an index on the table. Is this true? If so, is it something I can rely > on going forward? > > Thanks! > -Dave > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Disable lock controls on Windows
Hi all, As I remember it is possible to disable the Sqlite locking method in the Unix compiled lib but is it possible also to disable the locking in the Windows compiled lib of Sqlite ? There are locking problems in some "exotic" hardware configuration for which I should manage the locking method from myself using a semaphone file. Any suggest ? Thanks in advance. Regards, Marco Turco IT Business Devl Manager Software XP LLP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with a query
Thanks a lot Simon and Robert. -- Marco Bambini http://www.sqlabs.com On Mar 4, 2011, at 11:50 AM, Robert Hairgrove wrote: > On Fri, 2011-03-04 at 11:10 +0100, Marco Bambini wrote: >> Hello, >> I have a table defined as: >> CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id >> INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key)) >> >> In that table there some rows like: >> obj_id prop_keyprop_value >> 1PARENTID0 >> 1RESOURCE_ORDER 0 >> 2PARENTID0 >> 2RESOURCE_ORDER 1 >> 3PARENTID0 >> 3RESOURCE_ORDER 3 >> >> I need a query that returns all the obj_id with prop_key='PARENTID' AND >> prop_value='0' but ordered by prop_value WHERE prop_key='RESOURCE_ORDER'. >> Any help? > > Sounds like a job for a self-join. Try this: > > SELECT T1.obj_id, T2.prop_value > FROM MKProperties T1 > INNER JOIN MKProperties T2 > ON (T1.obj_id = T2.obj_id) > WHERE T2.prop_key = 'RESOURCE_ORDER' > AND T1.prop_key = 'PARENT_ID' > AND T1.prop_value = 0 > ORDER BY T2.prop_value; > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help with a query
Hello, I have a table defined as: CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key)) In that table there some rows like: obj_id prop_keyprop_value 1 PARENTID0 1 RESOURCE_ORDER 0 2 PARENTID0 2 RESOURCE_ORDER 1 3 PARENTID0 3 RESOURCE_ORDER 3 I need a query that returns all the obj_id with prop_key='PARENTID' AND prop_value='0' but ordered by prop_value WHERE prop_key='RESOURCE_ORDER'. Any help? -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] memory used by sqlite library
Michele take a look at the sqlite3_status function: http://www.sqlite.org/c3ref/status.html and http://www.sqlite.org/c3ref/c_status_malloc_count.html -- Marco Bambini http://www.sqlabs.com On Feb 25, 2011, at 2:17 PM, Michele Pradella wrote: > Do you know if is there a way to ask to the sqlite library the amount of > memory that is using? > It could be useful when I have to take a look to the memory used in my > application. So I can distinguish between memory allocated by my > application itself and allocated by sqlite library. > It's possible to set up a maximum amount of memory that the library can use? > > -- > Selea s.r.l. > > >Michele Pradella R > > >SELEA s.r.l. > > Via Aldo Moro 69 > Italy - 46019 Cicognara (MN) > Tel +39 0375 889091 > Fax +39 0375 889080 > *michele.prade...@selea.com* <mailto:michele.prade...@selea.com> > *http://www.selea.com* > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query help
Thanks Igor and thanks Martin, I need to add both the id and the other properties to an hash table (a Cocoa NSDictionary) so I needed a way to have a key, value representation that includes also the id. I solved the problem with 2 queries and some Cocoa code. I don't like complex queries and 2 simple queries is a better approach for the maintainability of the project. Thanks a lot for your advices. -- Marco Bambini http://www.sqlabs.com On Feb 2, 2011, at 8:11 PM, Igor Tandetnik wrote: > On 2/2/2011 11:16 AM, Marco Bambini wrote: >> your query returns 3 columns, but I need just two columns (key, value for >> example). > > Why? You have all the information you need, just in a slightly different > (and, arguably, easier to use) form. > >> The first row should be the label 'ID' and the id of the MKObjects followed >> by a SELECT prop_key, prop_value WHERE obj_id= MKObjects.id. > > Why should it? Why exactly do you insist on this format? > > What should happen, in your proposed representation, when there is more > than row in MKObjects, each with its own set of properties? > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query help
Hello Igor, your query returns 3 columns, but I need just two columns (key, value for example). The first row should be the label 'ID' and the id of the MKObjects followed by a SELECT prop_key, prop_value WHERE obj_id= MKObjects.id. For example MKObjects contains (1,IPHONE,PANEL,0) and MKProperties contains (1,1,NAME,About Box) and (2,1,WIDTH,200) the result of the query should should be: col1col2 'ID'1 'NAME' 'About Box' 'WIDTH', '200' -- Marco Bambini http://www.sqlabs.com On Feb 2, 2011, at 1:43 PM, Igor Tandetnik wrote: > Marco Bambini <ma...@sqlabs.net> wrote: >> Hello, I have two tables defined as: >> >> CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, >> type TEXT, parent_id INTEGER DEFAULT 0); >> CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id >> INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, >> prop_key)); >> >> I need to create a query that returns 2 columns key, value (column names are >> not important) where the first row is the label 'ID' >> with value id from MKObjects and the other rows are the columns prop_key, >> prop_value from MKProperties where obj_id= MKObjects.id >> satisfying a WHERE condition. > > You don't really need, or want, to create a query like that. It goes against > the grain of SQL. You want this: > > select o.id, prop_key, prop_value > from MKObjects o join MKProperties p on (o.id = p.obj_id) > where type='PANEL' AND platform='IPHONE' > order by o.id; > > When formatting your report, output a section heading whenever id column > changes from previous row. > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query help
Hello, I have two tables defined as: CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, type TEXT, parent_id INTEGER DEFAULT 0); CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key)); I need to create a query that returns 2 columns key, value (column names are not important) where the first row is the label 'ID' with value id from MKObjects and the other rows are the columns prop_key, prop_value from MKProperties where obj_id= MKObjects.id satisfying a WHERE condition. So far I am using a query like: SELECT 'ID', id FROM MKObjects WHERE type='PANEL' AND platform='IPHONE' UNION SELECT prop_key, prop_value FROM MKProperties WHERE obj_id=(SELECT id FROM MKObjects WHERE type='PANEL' AND platform='IPHONE' ORDER BY id); but I am wondering if there is a better way (without using 3 select statements). Thanks a lot. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite and windows server 2008 DFS
Hi, any experience using an sqlite db on Windows server 2008 with DFS enabled ? A customer with this configuration reported me that there is a lost of data. Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TEMP TRIGGER and SQLITE_OMIT_TEMPDB
I am trying to use TEMP TRIGGER inside my code but every time I use it I
receive a "SQL logic error or missing database" error.
I compiled sqlite with the option SQLITE_OMIT_TEMPDB.
but analyzing the source code I can see for example:
#ifndef SQLITE_OMIT_TRIGGER
/* If there are TEMP triggers on this table, modify the sqlite_temp_master
** table. Don't do this if the table being ALTERed is itself located in
** the temp database.
*/
if( (zWhere=whereTempTriggers(pParse, pTab))!=0 ){
sqlite3NestedParse(pParse,
"UPDATE sqlite_temp_master SET "
"sql = sqlite_rename_trigger(sql, %Q), "
"tbl_name = %Q "
"WHERE %s;", zName, zName, zWhere);
sqlite3DbFree(db, zWhere);
}
#endif
so sqlite_temp_master is necessary in order to use TEMP TRIGGER and so I can
use TEMP TRIGGER only if I recompile sqlite without the SQLITE_OMIT_TEMPDB
macro.
Anyone can confirm my assumption?
Thanks a lot.
--
Marco Bambini
http://www.sqlabs.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CREATE TEMP TRIGGER
Where I can find more information about TEMP TRIGGERs? In the official page: http://www.sqlite.org/lang_createtrigger.html there is no mention about the TEMP clause. Thanks. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] SQLiteConverter
SQLabs today is pleased to announce SQLiteConverter, the fastest and easiest way to convert your mySQL, PostgreSQL, Oracle (natively) and a wide range of ODBC compliant databases (like Microsoft SQL Server, Access, FoxPro and many others) to sqlite. It combines a very intuitive interface with powerful features so you can convert an existing remotely hosted database to sqlite in few steps. Thanks to a very intuitive wizard you'll be guide step by step to 5 easy operations in order to completely convert your remote databases. The entire process depends on how much data you need to convert but most of the time you'll be able to complete it in few seconds. SQLiteConverter is the ideal tool not only to complete a conversion between different databases to sqlite but it is also the ideal solution to backup your remote data to another disk based relational sql database. You always have full control over the conversion process, you can decide to convert the entire database or just select the individual tables you want to backup. More information available from: http://www.sqlabs.com/sqliteconverter.php -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] R: R: Crypto lib for Sqlite - suggest required
Hi, thank you for your suggests. My app is an accounting system. This accounting system must support the exchange of db between users that work with the this application so I think the only way is to use the same key for all users. This generate a lack of security of course but anyway my executable is crypted itself using an anti-debug cipher. I also need my app could read the same db crypted and also in the decrypted format because for some special situations I need to provide my app running with the Db decrypted. My doubt about the xor cryptation is that with this solution I can't manage a text search into the db using a select but I need to read all data , decrypt it and then make the text search, for this reason a low level cryptation would be better in my case. Marco -Messaggio originale- Da: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] Per conto di Roger Binns Inviato: domenica 9 gennaio 2011 05:16 A: sqlite-users@sqlite.org Oggetto: Re: [sqlite] R: Crypto lib for Sqlite - suggest required -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/08/2011 04:36 AM, Marco Turco wrote: > Essentially I would avoid that my competitors can look inside the Db > structure and import data. On any machine where the database is used the key will have to exist in plain form no matter how convoluted the encryption going on. Your competitors will always be able to run it under a debugger. You don't say if the database content is the same for everyone or if it is different for each user. If it is the same for everyone then all it takes is one bad user and the DB contents will be publishable for all. If it is different per user then I don't see the problem. The only way to be "secure" is to provide the data one value at a time via a web service where you can audit each and every request and not provide everything at once. (And anyone receiving that data can still republish it.) In these situations it is usually enough just to obfuscate the database so it isn't immediately apparent that SQLite is in use. A simple way of doing that is to write your own VFS that calls the normal VFS but xors all data that is being read and written. This is exactly that scheme using Python and demonstrates how much code it is: http://apidoc.apsw.googlecode.com/hg/example.html#example-vfs If you still want to go down the encryption route then be aware that getting encryption right is very hard. More accurately it is trivial to use encryption, and even easier to use it wrongly. People usually get it wrong. Some random subjects: IV, salt, key strengthening The SQLite paid for encryption module is a bargain. It is a one time fee and you get it forever. It will always work with SQLite as the versions change. It does security right and is always thoroughly tested alongside SQLite development. Work out the value of that and the value of your time. Since you won't really be able to prevent copying the data, what you should look for is ways of proving that someone has done so. Obfuscating the database is a good first start since no one could "accidentally" look at the contents - they had to put in deliberate effort. Then throw in some mountweazels: http://en.wikipedia.org/wiki/Fictitious_entry#Motivations_for_creation http://en.wikipedia.org/wiki/Trap_street This would give you enough evidence to sue a competitor. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0pNnIACgkQmOOfHg372QT8jwCg0DRP/QhGxrOWo2fWDsYNPZj4 tgUAoM0ReVOOJ9exG8rb9iz4cFqZJOWq =w+gv -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] assert crash in wal
Try to add:
-DSQLITE_THREADSAFE =1
to your compilation options.
--
Marco Bambini
http://www.sqlabs.com
On Dec 15, 2010, at 2:34 PM, Yoni Londner wrote:
> Hi,
>
> I wrote a little program that insert in a loop rows in to the DB, and in
> another thread run wal_checkpoint.
> After few minutes (6-7) I get (consistently) the following assert error:
>
> sqlite_test: ..//src/wal.c:1364: walMerge: Assertion `iLeft>=nLeft ||
> aContent[aLeft[iLeft]]>dbpage' failed.
>
> I compiled sqlite from fossil, with -DSQLITE_DEBUG and -DSQLITE_TEST
> I pasted below the program and the stacks.
> Am I doing something wrong?
>
> Yoni.
>
> (gdb) info threads
> 2 Thread 26132 0x0804ed3b in pthreadMutexEnter (p=0x8103068)
> at ..//src/mutex_unix.c:238
> * 1 Thread 26133 0xb7734424 in __kernel_vsyscall ()
> (gdb) bt
> #0 0xb7734424 in __kernel_vsyscall ()
> #1 0xb75e2640 in raise () from /lib/i686/cmov/libc.so.6
> #2 0xb75e4018 in abort () from /lib/i686/cmov/libc.so.6
> #3 0xb75db5be in __assert_fail () from /lib/i686/cmov/libc.so.6
> #4 0x080776a8 in walMerge (aContent=0xb7729088, aLeft=0x96157b4, nLeft=2,
> paRight=0xb759a898, pnRight=0xb759a89c, aTmp=0x9619cb8) at
> ..//src/wal.c:1364
> #5 0x080778a0 in walMergesort (aContent=0xb7729088, aBuffer=0x9619cb8,
> aList=0x9614654, pnList=0xb759a8f0) at ..//src/wal.c:1405
> #6 0x08077c63 in walIteratorInit (pWal=0x93f5c60, pp=0xb759a984)
> at ..//src/wal.c:1510
> #7 0x08077d8b in walCheckpoint (pWal=0x93f5c60, sync_flags=2, nBuf=1024,
> zBuf=0x93c3968 "\r") at ..//src/wal.c:1579
> #8 0x08079c0b in sqlite3WalCheckpoint (pWal=0x93f5c60, sync_flags=2,
> nBuf=1024,
> zBuf=0x93c3968 "\r") at ..//src/wal.c:2647
> #9 0x0805de51 in sqlite3PagerCheckpoint (pPager=0x93c2f08) at
> ..//src/pager.c:6558
> #10 0x0809e68d in sqlite3BtreeCheckpoint (p=0x93c17a8) at
> ..//src/btree.c:7953
> #11 0x0804b8fb in sqlite3Checkpoint (db=0x93c0af0, iDb=10) at
> ..//src/main.c:1402
> #12 0x080dd80d in sqlite3VdbeExec (p=0x93c36d8) at ..//src/vdbe.c:5225
> #13 0x0806b932 in sqlite3Step (p=0x93c36d8) at ..//src/vdbeapi.c:394
> #14 0x0806bba9 in sqlite3_step (pStmt=0x93c36d8) at ..//src/vdbeapi.c:458
> #15 0x080497af in sqlite3_exec (db=0x93c0af0, zSql=0x80e3e75 "PRAGMA
> wal_checkpoint",
> xCallback=0, pArg=0x0, pzErrMsg=0xb759b38c) at ..//src/legacy.c:70
> #16 0x0804937b in _sql_exec (conn=0x93c0af0, query=0x80e3e75 "PRAGMA
> wal_checkpoint",
> fail_if_locked=0) at sqlite_large_wal.c:52
> #17 0x080494d8 in thread_do () at sqlite_large_wal.c:104
> #18 0xb75a34c0 in start_thread () from /lib/i686/cmov/libpthread.so.0
> #19 0xb769784e in clone () from /lib/i686/cmov/libc.so.6
> (gdb) thread 2
> [Switching to thread 2 (Thread 26132)]#0 0x0804ed3b in pthreadMutexEnter (
> p=0x8103068) at ..//src/mutex_unix.c:238
> 238 assert( p->nRef>0 || p->owner==0 );
> (gdb) bt
> #0 0x0804ed3b in pthreadMutexEnter (p=0x8103068) at
> ..//src/mutex_unix.c:238
> #1 0x0804e6db in sqlite3_mutex_enter (p=0x8103068) at ..//src/mutex.c:112
> #2 0x0804da91 in sqlite3_free (p=0x958de28) at ..//src/malloc.c:470
> #3 0x0804dbcf in sqlite3DbFree (db=0x93b4068, p=0x958de28) at
> ..//src/malloc.c:503
> #4 0x0806f289 in releaseMemArray (p=0x96130d8, N=6) at
> ..//src/vdbeaux.c:1018
> #5 0x0807021f in closeAllCursors (p=0x94df2b8) at ..//src/vdbeaux.c:1538
> #6 0x08070f21 in sqlite3VdbeHalt (p=0x94df2b8) at ..//src/vdbeaux.c:2042
> #7 0x080d1991 in sqlite3VdbeExec (p=0x94df2b8) at ..//src/vdbe.c:861
> #8 0x0806b932 in sqlite3Step (p=0x94df2b8) at ..//src/vdbeapi.c:394
> #9 0x0806bba9 in sqlite3_step (pStmt=0x94df2b8) at ..//src/vdbeapi.c:458
> #10 0x080497af in sqlite3_exec (db=0x93b4068,
> zSql=0x80e3e24 "INSERT INTO tbl1 values('", 'a' ,
> "', '", 'b' , "')", xCallback=0, pArg=0x0,
> pzErrMsg=0xbf9dd57c)
> at ..//src/legacy.c:70
> #11 0x0804937b in _sql_exec (conn=0x93b4068,
> query=0x80e3e24 "INSERT INTO tbl1 values('", 'a' times>, "', '", 'b' , "')", fail_if_locked=1) at
> sqlite_large_wal.c:52
> #12 0x080493ed in sql_exec (conn=0x93b4068,
> query=0x80e3e24 "INSERT INTO tbl1 values('", 'a' times>, "', '", 'b' , "')") at sqlite_large_wal.c:64
> #13 0x0804945b in do_insert (conn=0x93b4068) at sqlite_large_wal.c:85
> #14 0x08049663 in main (argc=, argv= optimized out>)
> at sqlite_large_wal.c:139
> (gdb)
>
>
> #include "sqlite3.h"
> #include "stdio.h"
> #include "stdlib.h"
> #include "fcntl.h"
> #include "errno.h"
>
> #define NSEC_PER_MS 10
[sqlite] How to disable locking method on Sqlite (window)
Hi all, I always have problem with locking using MacOSX and Wine on a shared network drive. I checked that Sqlite permit to disable the locking setting SQLITE_ENABLE_LOCKING_STATE=2 but this is only supported on Unix system as I can see on the sqlite3.c source and this is not usable in a MacOSX + Wine due to the fact that the application runs in a Windows like environment. Any ideas to disable the internal Sqlite locking method on a Window app ? Thanks in advance Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] R: R: R: R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk
Hi, tried but unfortunately it doesn't runs. The window application running under Wine hasn't direct access to the absolute path. Marco -Messaggio originale- Da: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] Per conto di Sylvain Pointeau Inviato: domenica 12 dicembre 2010 22:44 A: General Discussion of SQLite Database Oggetto: Re: [sqlite] R: R: R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk ... and if you use the "unix-dotfile" as the VFS name in your open call. Does it work? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] R: R: R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk
>Argh. Any chance of trying it with something other than Vista ? I don't know it's definitely the problem, I'm just allergic to Vista. ;-)) I agree with you. I connected now the Mac to a Window XP 2002 sp3. The same lock problem remains. >One stage of that process is defeating the locking process. Please try mapping the shared drive directly from the Wine stage. I think it isn't possible. It seems Wine only permit to assign a drive letter to an already mounted remote drive. >That bug was fixed years ago. And I think that if it was a problem your Macintosh application would have the same problem. However, if you want to see how to mount that drive from the command-line on a Mac, type 'man mount_smbfs'. Tried. Unfortunately seems that the "nobrl" option is not available. Gr... Marco -Messaggio originale- Da: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] Per conto di Simon Slavin Inviato: domenica 12 dicembre 2010 22:09 A: General Discussion of SQLite Database Oggetto: Re: [sqlite] R: R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk On 12 Dec 2010, at 8:31pm, Marco Turco wrote: >> What OS (including version) is the host computer running ? > Windows Vista Business sp2 Argh. Any chance of trying it with something other than Vista ? I don't know it's definitely the problem, I'm just allergic to Vista. >> How is Wine accessing the server ? Did you mount the server in the > Macintosh layer, using an 'SMB://' URL, or did you use the Windows > facilities to mount it inside Wine ? > I mounted the server using the Mac layer (finder->Connect to server) > then I mapped a drive (Z:\) into the Wine configuration. One stage of that process is defeating the locking process. Please try mapping the shared drive directly from the Wine stage. > It seems the problem is due to a bug on the debian distribution of > Samba and the only way to solve it is at this moment to mount the net > disk using the nobrl parameter see > http://www.mail-archive.com/sqlite-users@sqlite.org/msg20409.html > anyway I am not sure this parameter is supported on Mac Os X mount > command but I am not an expert in Mac & Unix Os. That bug was fixed years ago. And I think that if it was a problem your Macintosh application would have the same problem. However, if you want to see how to mount that drive from the command-line on a Mac, type 'man mount_smbfs'. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] R: R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk
>The situation you're having a problem with ... am I right in saying that the application with problems is a Windows application running inside the Wine layer ? Yes. >What OS (including version) is the host computer running ? Windows Vista Business sp2 >What OS (including version) is the client computer running ? Include the version of Wine if Wine is involved. Mac OS X 10.5.8. Wine 1.1.44 >How is Wine accessing the server ? Did you mount the server in the Macintosh layer, using an 'SMB://' URL, or did you use the Windows facilities to mount it inside Wine ? I mounted the server using the Mac layer (finder->Connect to server) then I mapped a drive (Z:\) into the Wine configuration. It seems the problem is due to a bug on the debian distribution of Samba and the only way to solve it is at this moment to mount the net disk using the nobrl parameter see http://www.mail-archive.com/sqlite-users@sqlite.org/msg20409.html anyway I am not sure this parameter is supported on Mac Os X mount command but I am not an expert in Mac & Unix Os. Any ideas ? Marco -Messaggio originale- Da: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] Per conto di Simon Slavin Inviato: domenica 12 dicembre 2010 21:13 A: General Discussion of SQLite Database Oggetto: Re: [sqlite] R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk On 12 Dec 2010, at 7:58pm, Marco Turco wrote: > this problem appear only in accessing the sqlite database from Mac OS > X on a Windows shared disk. The situation you're having a problem with ... am I right in saying that the application with problems is a Windows application running inside the Wine layer ? > On full Windows networks and also on Mac OS X in local all runs fine. What OS (including version) is the host computer running ? What OS (including version) is the client computer running ? Include the version of Wine if Wine is involved. How is Wine accessing the server ? Did you mount the server in the Macintosh layer, using an 'SMB://' URL, or did you use the Windows facilities to mount it inside Wine ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk
Hi, this problem appear only in accessing the sqlite database from Mac OS X on a Windows shared disk. On full Windows networks and also on Mac OS X in local all runs fine. I tried with the oplock enable and disable on the Window server but the Sqlite db always remains locked from the Mac. I have more than 97% of my customers that work stand-alone or in a network environment with less than 4 computer and I haven't any control on the hardware they use so I think the Sqlite solution is preferable instead of a client-server db. I have also some customers with 10-12 computer but due the kind of product there isn't an high concurrency in writing and tracking the locks it appears that only 4-5 locks/day for just 0.3 seconds each one executed on these bigger network. Marco -Messaggio originale- Da: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] Per conto di Simon Slavin Inviato: domenica 12 dicembre 2010 20:38 A: General Discussion of SQLite Database Oggetto: Re: [sqlite] Lock problem opening a Sqlite db on a Samba/CIFS shared disk On 12 Dec 2010, at 7:09pm, Marco Turco wrote: > I am having a problem running my Window Sqlite app on Mac OS X with > Wine emulator. > > It runs well in local but when I try to access to a network disk > hosted on Windows XP The Wine emulator is an excellent emulator but it doesn't correctly emulate all the obscure elements of Windows. Please try it on a proper Windows computer. > I checked on internet about this and as I know Sqlite at this moment > doesn't support the Samba/CIFS disk with reference to the locking system. Sorry, I don't know. However if you have both Mac and Windows clients trying to access the database simultaneously, you should be very careful with your settings for oplocks and such things. Perhaps someone with experience of that kind of setup can help. > I am really in trouble because I have more than 50 customers with > mixed network (Windows/Mac OS X) having this problem in the next future. I know this is not what you asked but I have advice. If you have users with more than 5 or ten computers trying to access the database simultaneously, you may want to use a proper multi-user SQL engine instead of SQLite. If you use a SQL engine with client/server architecture no disk locking is done: the only computer actually accessing the database files is the server. I'm not saying that SQLite will fail, I'm saying that MySQL (and several similar systems) are specially designed for simultaneous multi-user access, and they do the job without having to worry about file locking and access from different types of computer. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Lock problem opening a Sqlite db on a Samba/CIFS shared disk
Hi all, I am having a problem running my Window Sqlite app on Mac OS X with Wine emulator. It runs well in local but when I try to access to a network disk hosted on Windows XP then a lock error appears. I checked on internet about this and as I know Sqlite at this moment doesn't support the Samba/CIFS disk with reference to the locking system. I am really in trouble because I have more than 50 customers with mixed network (Windows/Mac OS X) having this problem in the next future. Any ideas or turn-around to solve this problem ? Thanks in advance Marco Turco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_analyzer issue on MacOS X 10.6.5
I just tried it on my Mac and this new build works pretty well. Thanks a lot for your assistance. -- Marco Bambini http://www.sqlabs.com On Dec 8, 2010, at 1:54 AM, Richard Hipp wrote: > In Tue, Dec 7, 2010 at 7:15 PM, Richard Hipp <d...@sqlite.org> wrote: >> On Tue, Dec 7, 2010 at 6:22 PM, Marco Bambini <ma...@sqlabs.net> wrote: >>> Running sqlite3_analyzer on a MacOS X 10.6.5 results in the following issue: >>> >>> dyld: Library not loaded: /usr/local/lib/libtcl8.6.dylib >>> Referenced from: /Users/marco/Desktop/sqlite3_analyzer >>> Reason: image not found >>> Trace/BPT trap >>> >>> Seems like a broken binary to me. >>> Any idea? >> >> I don't know how to statically link the TCL libraries on a Mac. I >> tried every combination of options I could think of and none of them >> seem to work. >> >> I think you just have to install TCL on your Mac in order to use >> sqlite3_analyzer there. Bummer. > > I finally figured out how to statically link TCL on a Mac (you have to > add "-framework CoreFoundation" to the compiler command-line) I > rebuilt using this recipe and put up a new image. Please download the > latest and try again. > > >> >> >>> -- >>> Marco Bambini >>> http://www.sqlabs.com >>> >>> >>> >>> >>> >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> >> >> -- >> D. Richard Hipp >> d...@sqlite.org >> > > > > -- > 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_analyzer issue on MacOS X 10.6.5
Running sqlite3_analyzer on a MacOS X 10.6.5 results in the following issue: dyld: Library not loaded: /usr/local/lib/libtcl8.6.dylib Referenced from: /Users/marco/Desktop/sqlite3_analyzer Reason: image not found Trace/BPT trap Seems like a broken binary to me. Any idea? -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Date/Time query help
I have a table like:
CREATE TABLE foo (id integer PRIMARY KEY AUTOINCREMENT,connection_date text);
and I insert data into this table using the syntax:
INSERT INTO foo (connection_date) VALUES (datetime('now','localtime'));
INSERT INTO foo (connection_date) VALUES (datetime('now','localtime'));
I really need to use localtime and after the two simple INSERT above my table
contains rows like:
id connection_date
1 2010-12-07 14:39:43
2 2010-12-07 14:39:59
I need to create a query that is able to retrieve all the id(s) from foo where
connection_date is older than 5 minutes starting from now (in localtime).
Thanks a lot.
--
Marco Bambini
http://www.sqlabs.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database corrupted
Hello, I'm doing some tests to get the best out of the threading models for sqlite. All tests are done on a multicore processor, windows XP; sqlite is working in WAL mode. I've got a database image corrupted when using SQLITE_THREADSAFE=2 and two threads, each opening a private connection to the same database. sqlite3* pointers are not shared, each thread has his own. Compiling with SQLITE_THREADSAFE=1 seems to work well, but I will test it more. Am is missing something? Do I have to synchronize the threads? Thanks in advance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL mode and backup API
Hello, if a running sqlite database is in WAL mode and a backup is performed on that db using the sqlite3_backup API, does that process is considered like a reader and can proceed concurrently with other readers and with the other writer? Thanks a lot for your answer. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] SQLiteConverter
Viadana, Italy - SQLabs announced SQLiteConverter, the fastest and easier way to convert your mySQL, PostgreSQL and Oracle database to sqlite. It combines a very intuitive interface with very powerful features so you can convert an existing remotely hosted database to sqlite in few time. Thanks to a very intuitive wizard you'll be guide step by step to 5 easy operations in order to completely convert your remote databases. The entire process obviously depends on how much data you need to convert but most of the time you'll be able to complete it in few seconds. SQLiteConverter is the ideal tool not only to complete a conversion between different DBMS to sqlite but it is also the ideal solution to backup your remote data to another disk based relational sql database. You always have full control over the conversion process, you can decide to convert the entire database or just select the individual tables you want to backup. New databases data sources will be added in the future as a free upgrade! More information available at: http://www.sqlabs.com/sqliteconverter.php Minimum requirements: * MacOS X 10.4 or higher * Windows 2000/NT/XP/Vista/7 A SQLiteConverter single license is $49 USD. Company and multiplatform licenses are also available. For more information, please visit the SQLabs website. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DELETE with 64bit unsigned integer key
Hello there sqlite-users, I have a quick question regarding selection and deletion of large unsigned integers. I create my table as following: [1] CREATE TABLE t1 (instnum BIG UNSIGNED INTEGER PRIMARY KEY, ...) I then insert some row: [2] INSERT INTO t1 (instnum, ...) VALUES (CAST (?1 AS BIG UNSIGNED INTEGER), ...) This is just a test for 64bit unsigned, so I have the following rows: 1 2 18446744073709551615 (0x) 9223372036854775808 (0x8000) 0 3 1234 23456 654321 When I try to DELETE using: [3] DELETE FROM t1 WHERE instnum <= CAST (?1 AS BIG UNSIGNED INTEGER); I don't always get what I would expect, to me it looks like large numbers (64bit with the MSB set to 1) are treated as negative. For example if ?1 in query [3] is set to 654321, all rows are being deleted (while 18446744073709551615 and 9223372036854775808 should not be deleted) If ?1 is set to 18446744073709551615, the rows 18446744073709551615 and 9223372036854775808 are deleted, but the other ones are not. (while all of them should be gone) (I was previously using [2] and [3] without explicit cast, but the result is the same). How can I solve this issue? Thank you, Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Strange error
I have a strange error with sqlite 3.6.23.1, does anyone have an explanation?
CREATE TABLE 'tblMoneyFlow'
(
'id' INTEGER DEFAULT '0' NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
'RekeningNrEigenaar' INTEGER DEFAULT '0' NOT NULL REFERENCES 'tblBankAccount'
('idAccountNr'),
'Valuta' VARCHAR(4),
'DatumTransactie' DATE NOT NULL DEFAULT '01-01-1970',
'direction' DEFAULT 'D',
'bedrag' FLOAT NOT NULL DEFAULT '0',
'RekeningNrBestemming' INTEGER DEFAULT '0' NOT NULL REFERENCES 'tblBankAccount'
('idAccountNr'),
'Begunstigde' VARCHAR(32),
'DatumBoeking' DATE NOT NULL DEFAULT '01-01-1970',
'TransactieCode' INTEGER NOT NULL DEFAULT '0' REFERENCES 'tblMoneyFlowAction'
('id'),
'TransactieOmschrijving' VARCHAR(25)
);
INSERT INTO 'tblMoneyFlow' DEFAULT VALUES;
error is "no such table: main.tblMoneyFlow"
Thanks a lot.
P.S. I suspect it should have something to do with the references constraints
--
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] SQLiteManager 3.5
Viadana, Italy - SQLabs has announced SQLiteManager 3.5, the most powerful sqlite database manager tool for MacOS X and Windows. SQLiteManager is a powerful GUI database manager for sqlite databases. It combines an incredible easy to use interface with blazing speed and advanced features. SQLiteManager allows you to open and work with sqlite 2, sqlite 3, in memory databases, AES 128 encrypted databases and with REAL Server databases. It allows you to create and browse tables, views, triggers and indexes. It enables you to insert, delete and updates records in a very intuitive way, it supports you arbitrary SQL commands and much more. Version 3.5 features a new powerful table editor with foreign key support and an advanced low level sqlite 3 database analyzer plus a lot of other improvements. What's new in this version: * Added a new detailed analyzer feature * Added Foreign Key support * Added a Copy RecordSet new menu item * Added Views to the Export dialog * Brand new CREATE/ALTER table dialog * Improved the importer engine * Improved the exporter engine * Improved display of BLOB images * Improved NULL values handling * Improved handling of table names that contains special characters * Improved both the Manage and SQL panel * Fixed a Win32 Edit Table menu issue * Fixed a couple of minor Win32 related issues * Fixed a case insensitive bug in inline editing * Fixed a bug that occurs while updating a row that contains BLOB columns * Fixed some issues related to BOOLEAN values * Fixed an issue that occurs while editing values with an sqlite 2 database * Fixed the incorrect Database Seems Encrypted message * Fixed an issue related to the RSReport engine * Fixed an issue related to saved SQL commands * Updated sqlite to the latest 3.6.23.1 version * Updated DoD ListBox to version 2.3 * Updated User's Manual and RBScript Manual * Updated Language Reference * Updated SQLite3ProfessionalPlugin to version 3.9 * Updated RSReport engine to version 2010.2.1 * A lot of other small fixes and optimizations Some features include: * SQLite2 and SQLite3 support * REAL Server support * In-Memory database support * AES 128 encrypted SQLite 3 databases support * Browse tables, views, and indexes * Create new tables, views, indexes and triggers * Create notes and script * Drop tables, views, indexes, triggers * Full alter tables support * Manage tables by inserting, editing, and deleting records * Built-in inline editing * Built-in virtual machine analyzer * Built-in query optimizer * Full-text search support * Show PDF, JPEG, BMP, TIFF, QuickTime from BLOB records * Save frequently used SQL commands for later use * Convert SQLite 2 databases to SQLite 3 * Powerful reports generation with flexible report templates * Advanced import and export capabilities * Built-in language reference * Embed notes and stickies in your databases * Load native sqlite 3 extensions * Script language support for automate repetitive tasks * New record editor to easily modify/add rows * Real-time BLOB preview * SQL history * Log window * New CSV import engine that supports multi gigabity files * Improved export engine * New powerful print engine with real-time preview and PDF export * Attach external database files * Dump database files on disk * Ability to open hidden files or bundles under OSX * New table editor with full support for altering exiting tables * New improved GUI * New Chart panel to easily visualize your data (Line chart, Bar chart, Pie chart, Venn chart, Scatter, Radar, Map and QR Code) Minimum requirements: * MacOS X 10.4 or higher * Windows 2000/NT/XP/Vista/7 A SQLiteManager single license is $49 USD (a $20 discount coupon code is available for SQLiteManager 2.x registered users). Company and multiplatform licenses are also available. For more information, please visit the SQLabs website at: http://www.sqlabs.com/sqlitemanager.php -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_column_name
Unfortunately I cannot modify the query... it is supplied by an user. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Nov 13, 2009, at 3:10 PM, Virgilio Fornazin wrote: > SELECT > field as NAME > > does not work? > > On Fri, Nov 13, 2009 at 12:07, Marco Bambini <ma...@sqlabs.net> wrote: > >> sqlite 3.6.19 >> >> CREATE TABLE foo (col1 INTEGER PRIMARY KEY, col2 TEXT); >> a >> SELECT rowid, col1, col2 >> >> returns the following column names with sqlite3_column_name: >> col1, col1, col2 >> >> Is there a way to force the first column name to be returned as rowid and >> not as its col1 alias? >> >> Thanks. >> -- >> Marco Bambini >> http://www.sqlabs.com >> http://www.creolabs.com/payshield/ >> >> >> >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_column_name
sqlite 3.6.19 CREATE TABLE foo (col1 INTEGER PRIMARY KEY, col2 TEXT); a SELECT rowid, col1, col2 returns the following column names with sqlite3_column_name: col1, col1, col2 Is there a way to force the first column name to be returned as rowid and not as its col1 alias? Thanks. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Viewer for blobs in hex?
Hello Yan, you could use my SQLiteManager application: http://www.sqlabs.com/sqlitemanager.php Regards, -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Sep 21, 2009, at 9:43 AM, Yan Bertrand wrote: >Hi all, > > > > I would like to display the contents of blobs in my table as > hexadecimal. I have not found any easy way of doing this. I tried : > > - wxSQLitePlus, but it does not display blobs contents (or I > could not make it do so) > > - SQLiteManager plugin (for Mozilla Firefox), but it does not > do this by default. The website says it can but the explaination for > it > is still < to be done >. > > - SQLiteStudio does not display anything (it says < NULL > in > italic, but it is not a NULL content.) > > - SQLite2009 Pro but it does not dosplay blobs either > > - I could dump the table but it really gets tedious. > > > > Note: I tried exporting to other formats but the blobs are replaced > by a > string (either < NULL > or < NONE >). I know my blobs are not empty > because C-code can read them. > > > > Any idea? > > > > Thank you for your support and merry continued use of SQLite! > > > > Yan > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Explanation
Yes, you are right (as always). Time with SQLITE_THREADSAFE=0 is about 4.33 seconds now. Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Aug 28, 2009, at 5:23 PM, D. Richard Hipp wrote: > > On Aug 28, 2009, at 11:17 AM, Mike Eggleston wrote: > >> On Fri, 28 Aug 2009, Marco Bambini might have said: >>> >>> Version 3.4.2 takes about 5.06 seconds (average value) while version >>> 3.6.17 takes about 7.28 seconds (average value). >>> Could be a slowdown in the library for the complexity added over the >>> years or does someone have another possible explanation? > > Our measurements show a substantial performance improvement over > 3.4.2. > > Perhaps you are running in the default configuration, which has been > augmented with many new mutexes since version 3.4.2 in order to make > SQLite proof against over-zealous users of threads. If you recompile > with -DSQLITE_THREADSAFE=0, perhaps you will get your old performance > back. > > >>> >>> Thanks. >> >> Did you just relink your app or did you also migrate the data to a >> new >> sqlite3 database? I think the migration command is: >> >> echo '.dump' | sqlite3 $db | sqlite3 $dbnew >> >> Maybe the internal database structure has changed? > > The file-format is unchanged. > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Explanation
Library is statically linked into the final app and the db is newly created... -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Aug 28, 2009, at 5:17 PM, Mike Eggleston wrote: > On Fri, 28 Aug 2009, Marco Bambini might have said: > >> Hello, >> >> today I made some test on a project I wrote some years ago. >> I upgraded sqlite library from version 3.4.2 to version 3.6.17. >> What I am really unable to understand is the time difference required >> to perform the same query using the exact same algorithm by the two >> libraries. >> >> SELECT * FROM table1 >> where table1 has 1 million rows and 10 columns (its an 80MB db). >> >> Version 3.4.2 takes about 5.06 seconds (average value) while version >> 3.6.17 takes about 7.28 seconds (average value). >> Could be a slowdown in the library for the complexity added over the >> years or does someone have another possible explanation? >> >> Thanks. > > Did you just relink your app or did you also migrate the data to a new > sqlite3 database? I think the migration command is: > > echo '.dump' | sqlite3 $db | sqlite3 $dbnew > > Maybe the internal database structure has changed? > > Mike > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Explanation
Hello, today I made some test on a project I wrote some years ago. I upgraded sqlite library from version 3.4.2 to version 3.6.17. What I am really unable to understand is the time difference required to perform the same query using the exact same algorithm by the two libraries. SELECT * FROM table1 where table1 has 1 million rows and 10 columns (its an 80MB db). Version 3.4.2 takes about 5.06 seconds (average value) while version 3.6.17 takes about 7.28 seconds (average value). Could be a slowdown in the library for the complexity added over the years or does someone have another possible explanation? Thanks. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] translating CSV file into sqlite3 database for iPhone?
Have you tried my SQLiteManager app? http://www.sqlabs.com/sqlitemanager.php It can easily convert/import your CSV file into an sqlite3 database. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Aug 7, 2009, at 4:57 PM, Adam DeVita wrote: > This is why I generally advocate TAB delimited files over CSV > > Restaurant , Menu Item, Price > Tom, Dick "The MAN", and Harry's Bar & Grill , Specials /new stuff! > Mikey's > Burger "Delishiousness ' , $5 > > If you only have to upload your data once, you should be able to use a > spreadsheet program to convert to TAB delimited rather than going > through > the work of writing your own parser. > > > > On Fri, Aug 7, 2009 at 10:43 AM, Wilson, Ron P < > ronald.wil...@tycoelectronics.com> wrote: > >>> I'm trying to take a CSV file and create a sqlite3 database for the >>> iPhone. >>> The CSV file has 33K entries and is 2 MB. The problem I am having >>> is >> that >>> only about 1/10 of the database file gets written into the sqlite3 >>> database. >> >> The .import csv method is imperfect; if you have quoted strings in >> your csv >> that have commas or newlines in them, the import will do surprising >> things. >> I had to write my own code to do imports with quoted strings. >> >> RW >> >> Ron Wilson, Engineering Project Lead >> (o) 434.455.6453, (m) 434.851.1612, www.harris.com >> >> HARRIS CORPORATION | RF Communications Division >> assuredcommunications(tm) >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > VerifEye Technologies Inc. > 905-948-0015x245 > 7100 Warden Ave, Unit 3 > Markham ON, L3R 8B5 > Canada > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] SQLiteManager 3.0
VIADANA, Italy (June 24, 2009) - SQLabs is proud to announce today the worldwide availability of SQLiteManager 3.0, the most powerful sqlite database manager tool for MacOS X and Windows. SQLiteManager is a "next generation" GUI database manager for sqlite databases. It combines an incredible easy to use interface with blazing speed and advanced features. SQLiteManager allows you to open and work with sqlite 2, sqlite 3, in memory databases, AES 128 encrypted databases and with REAL Server databases. It allows you to create and browse tables, views, triggers and indexes. It enables you to insert, delete and updates records in a very intuitive way, it supports you arbitrary SQL commands and much more. Some features include: *SQLite2 and SQLite3 support. *REAL Server support. *In-Memory database support. *AES 128 encrypted SQLite 3 databases support. *Browse tables, views, and indexes. *Create new tables, views, indexes and triggers. *Create notes and script. *Drop tables, views, indexes, triggers. *Full alter tables support. *Manage tables by inserting, editing, and deleting records. *Built-in inline editing. *Built-in virtual machine analyzer. *Built-in query optimizer. *Full-text search support. *Show PDF, JPEG, BMP, TIFF, QuickTime from BLOB records. *Save frequently used SQL commands for later use. *Convert SQLite 2 databases to SQLite 3. *Powerful reports generation with flexible report templates. *Advanced import and export capabilities. *Built-in language reference. *Embed notes and stickies in your databases. *Load native sqlite 3 extensions. *Script language support for automate repetitive tasks. *New record editor to easily modify/add rows. *Real-time BLOB preview. *SQL history. *Log window. *New CSV import engine that supports multi gigabity files. *Improved export engine. *New powerful print engine with real-time preview and PDF export! *Attach external database files. *Dump database files on disk. *Ability to open hidden files or bundles under OSX. *New table editor with full support for altering exiting tables. *New improved GUI. *New Chart panel to easily visualize your data (Line chart, Bar chart, Pie chart, Venn chart, Scatter, Radar, Map and QR Code) Minimum requirements: - MacOS X 10.4 or higher - Windows 2000/NT/XP/Vista/7 A SQLiteManager single license is $49. Company and multiplatform licenses are also available. For more information, please visit the SQLabs website: http://www.sqlabs.com/sqlitemanager.php Contact Information Web: http://www.sqlabs.com Email: i...@sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why row is not found?
Hello Martin, it's a db than an user sent me... so I really don't know... -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Jun 5, 2009, at 11:31 AM, Martin Engelschalk wrote: > Hi Marco, > > How do you insert this data into your database? > I opened your database with an old version of SQLiteSpy, which uses an > even older version of sqlite. It showed the value as a blob. > > Martin > Marco Bambini wrote: >> I understand that the issue could be caused by the wrong datatype... >> but what is strange is that the same db and the same query worked >> fine >> with sqlite 3.4.2 and the behavior changed with sqlite 3.6.x >> >> -- >> Marco Bambini >> http://www.sqlabs.com >> http://www.creolabs.com/payshield/ >> >> >> >> >> >> >> On Jun 5, 2009, at 11:17 AM, Martin Engelschalk wrote: >> >> >>> Your field value is a blob, so you have to use a cast like you did >>> to >>> find the row, or use a blob literal: >>> SELECT * FROM lo_user WHERE lo_name=X'61646d696e'; >>> >>> >>> >>> Marco Bambini wrote: >>> >>>> I just posted the db on my website... there is one row and there >>>> aren't invisible characters. >>>> >>>> Please note that the following query returns the exact row: >>>> SELECT * FROM lo_user WHERE CAST(lo_name AS TEXT)='admin'; >>>> but I really don't have an explanation... >>>> >>>> -- >>>> Marco Bambini >>>> http://www.sqlabs.com >>>> http://www.creolabs.com/payshield/ >>>> >>>> >>>> >>>> >>>> >>>> >>>> On Jun 5, 2009, at 11:10 AM, Martin Engelschalk wrote: >>>> >>>> >>>> >>>>> Hi, >>>>> >>>>> attachments do not make it through the list. >>>>> There is no row with the value 'admin' in the field 'lo_name' in >>>>> your >>>>> table. Did you check that there are no blank spaces or other >>>>> invisible >>>>> characters? >>>>> >>>>> Martin >>>>> >>>>> Marco Bambini wrote: >>>>> >>>>> >>>>>> Anyone can please explain me why this query: >>>>>> SELECT * FROM lo_user WHERE lo_name='admin'; >>>>>> returns 0 rows in this db? >>>>>> >>>>>> >>>>>> >>>>>> Thanks. >>>>>> -- >>>>>> Marco Bambini >>>>>> http://www.sqlabs.com >>>>>> http://www.creolabs.com/payshield/ >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> ___ >>>>>> sqlite-users mailing list >>>>>> sqlite-users@sqlite.org >>>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>>>> >>>>>> >>>>>> >>>>> ___ >>>>> sqlite-users mailing list >>>>> sqlite-users@sqlite.org >>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>>> >>>>> >>>> ___ >>>> sqlite-users mailing list >>>> sqlite-users@sqlite.org >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>> >>>> >>>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why row is not found?
I understand that the issue could be caused by the wrong datatype... but what is strange is that the same db and the same query worked fine with sqlite 3.4.2 and the behavior changed with sqlite 3.6.x -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Jun 5, 2009, at 11:17 AM, Martin Engelschalk wrote: > Your field value is a blob, so you have to use a cast like you did to > find the row, or use a blob literal: > SELECT * FROM lo_user WHERE lo_name=X'61646d696e'; > > > > Marco Bambini wrote: >> I just posted the db on my website... there is one row and there >> aren't invisible characters. >> >> Please note that the following query returns the exact row: >> SELECT * FROM lo_user WHERE CAST(lo_name AS TEXT)='admin'; >> but I really don't have an explanation... >> >> -- >> Marco Bambini >> http://www.sqlabs.com >> http://www.creolabs.com/payshield/ >> >> >> >> >> >> >> On Jun 5, 2009, at 11:10 AM, Martin Engelschalk wrote: >> >> >>> Hi, >>> >>> attachments do not make it through the list. >>> There is no row with the value 'admin' in the field 'lo_name' in >>> your >>> table. Did you check that there are no blank spaces or other >>> invisible >>> characters? >>> >>> Martin >>> >>> Marco Bambini wrote: >>> >>>> Anyone can please explain me why this query: >>>> SELECT * FROM lo_user WHERE lo_name='admin'; >>>> returns 0 rows in this db? >>>> >>>> >>>> >>>> Thanks. >>>> -- >>>> Marco Bambini >>>> http://www.sqlabs.com >>>> http://www.creolabs.com/payshield/ >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> ___ >>>> sqlite-users mailing list >>>> sqlite-users@sqlite.org >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>> >>>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why row is not found?
I just posted the db on my website... there is one row and there aren't invisible characters. Please note that the following query returns the exact row: SELECT * FROM lo_user WHERE CAST(lo_name AS TEXT)='admin'; but I really don't have an explanation... -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Jun 5, 2009, at 11:10 AM, Martin Engelschalk wrote: > Hi, > > attachments do not make it through the list. > There is no row with the value 'admin' in the field 'lo_name' in your > table. Did you check that there are no blank spaces or other invisible > characters? > > Martin > > Marco Bambini wrote: >> Anyone can please explain me why this query: >> SELECT * FROM lo_user WHERE lo_name='admin'; >> returns 0 rows in this db? >> >> >> >> Thanks. >> -- >> Marco Bambini >> http://www.sqlabs.com >> http://www.creolabs.com/payshield/ >> >> >> >> >> >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why row is not found?
You can download the db from: http://www.sqlabs.com/download/test.sqlite It's only 4KB. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Jun 5, 2009, at 11:04 AM, Marco Bambini wrote: > Anyone can please explain me why this query: > SELECT * FROM lo_user WHERE lo_name='admin'; > returns 0 rows in this db? > > > > Thanks. > -- > Marco Bambini > http://www.sqlabs.com > http://www.creolabs.com/payshield/ > > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why row is not found?
Anyone can please explain me why this query: SELECT * FROM lo_user WHERE lo_name='admin'; returns 0 rows in this db? Thanks. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] most efficient way to get 1st row
SELECT ... LIMIT 1; -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On May 19, 2009, at 5:03 PM, Sam Carleton wrote: > I am far from a SQL expert, but I am 99.9% sure there is SQL syntax > to limit > the number of results, I have not looked it up but I will in a little > while... > > I only need one result. Since I am working with the C/C++ API, I > plan to > simply call sqlite3_step() only once. > > Is there any point in using the SQL syntax to limit it to one? > Would the > syntax slow the process down because it simply isn't needed or will > it speed > it up because of all the wonderful internal things going on? > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic error or missing database
Thanks a lot for the explanation Igor.
--
Marco Bambini
On May 14, 2009, at 1:55 PM, Igor Tandetnik wrote:
> "Marco Bambini" <ma...@sqlabs.net> wrote
> in message news:aa7dd05f-4679-43dd-9dd3-2ba6b98af...@sqlabs.net
>> I have two threads that are writing 2000 rows each to the same
>> database at the same time.
>> I am using sqlite 3.6.13 compiled with SQLITE_THREADSAFE=1.
>>
>> Each client executes this code (pseudo C code):
>> void write (sqlite3 *db) {
>> int i;
>>
>> for (i=1; i<=2000; i++) {
>> if (sqlite3_get_autocommit(db) != 0) sqlite3_exec(db, "BEGIN
>> IMMEDIATE;", ...);
>> sqlite3_exec(db, "INSERT INTO", ...);
>> if (sqlite3_get_autocommit(db) == 0) sqlite3_exec(db,
>> "COMMIT;", ...);
>> }
>> }
>> and db is shared between the two clients.
>
> You have a race condition here: between the calls to
> sqlite3_get_autocommit and sqlite3_exec, the other thread could very
> well have issued a BEGIN or a COMMIT of its own. Between the time you
> check the condition and the time you act on it, the condition could
> have
> changed.
>
> Besides, the documentation on sqlite3_get_autocommit has this
> sentence:
> If another thread changes the autocommit status of the database
> connection while this routine is running, then the return value is
> undefined. In other words, sqlite3_get_autocommit is explicitly not
> thread-safe.
>
> Since you only run one INSERT per transaction anyway, why do you feel
> you need explicit BEGIN and COMMIT?
>
>> At the end of the loop, instead of having 4000 rows I have 3976 rows
>> (it's random, sometimes I have 3972 or 3974).
>> sqlite3_exec doesn't returns any error during the INSERT statement,
>> but I have some errors during the BEGIN IMMEDIATE, errors are all:
>> SQL logic error or missing database (printed with sqlite3_errmsg).
>
> Your use of sqlite3_errmsg is itself very likely a race. Between the
> time you detect an error and the time you retrieve error message, the
> other thread could have run some statements that modify the error
> message. Moreover, between the time you call sqlite3_errmsg and the
> time
> you actually print the string pointed to by the char* pointer the
> function returns, the string may be modified or even deallocated.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL logic error or missing database
I have two threads that are writing 2000 rows each to the same
database at the same time.
I am using sqlite 3.6.13 compiled with SQLITE_THREADSAFE=1.
Each client executes this code (pseudo C code):
void write (sqlite3 *db) {
int i;
for (i=1; i<=2000; i++) {
if (sqlite3_get_autocommit(db) != 0) sqlite3_exec(db, "BEGIN
IMMEDIATE;", ...);
sqlite3_exec(db, "INSERT INTO", ...);
if (sqlite3_get_autocommit(db) == 0) sqlite3_exec(db,
"COMMIT;", ...);
}
}
and db is shared between the two clients.
At the end of the loop, instead of having 4000 rows I have 3976 rows
(it's random, sometimes I have 3972 or 3974).
sqlite3_exec doesn't returns any error during the INSERT statement,
but I have some errors during the BEGIN IMMEDIATE, errors are all:
SQL logic error or missing database (printed with sqlite3_errmsg).
Any explanation of the possible causes for the missed rows?
Thanks.
--
Marco Bambini
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query takes ages
eger , couvAffi Double , repAffi Double , gprAffi SmallInt , odvAffi Integer , gamme_id Integer , vente_id Integer , type_id Integer , popTouche Integer , affiniteAffimetrie Float , updateEDI VarChar NOT NULL , groupe_id Integer ); CREATE TABLE reseaux_insee (id_reseau_insee Integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, reseau_id Integer , insee_id Integer , population Integer , panneau Integer ); CREATE TABLE typeformats (id_typeFormat Integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, format VarChar NOT NULL ); Can someone suggest me the best index/indexes to use for such a query? or the best way to rewrite it in a way more manageable by sqlite? I really appreciate your help. Thanks. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory Usage
cache_size is set to default 2000, page size is 1K... here you go the output of sqlite3_status: 2009-04-21 15:24:25 SQLITE_STATUS_MEMORY_USED current: 106704136 high: 109873952 2009-04-21 15:24:25 SQLITE_STATUS_PAGECACHE_USED current: 0 high: 0 2009-04-21 15:24:25 SQLITE_STATUS_PAGECACHE_OVERFLOW current: 4816736 high: 4819808 2009-04-21 15:24:25 SQLITE_STATUS_SCRATCH_USED current: 0 high: 0 2009-04-21 15:24:25 SQLITE_STATUS_SCRATCH_OVERFLOW current: 0 high: 6664 2009-04-21 15:24:25 SQLITE_STATUS_MALLOC_SIZE current: 1014 high: 52000 2009-04-21 15:24:25 SQLITE_STATUS_PARSER_STACK current: 0 high: 0 2009-04-21 15:24:25 SQLITE_STATUS_PAGECACHE_SIZE current: 0 high: 0 2009-04-21 15:24:25 SQLITE_STATUS_SCRATCH_SIZE current: 4480 high: 6664 -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Apr 21, 2009, at 3:12 PM, D. Richard Hipp wrote: > > On Apr 21, 2009, at 9:07 AM, Marco Bambini wrote: > >> The database is on-disk ... does huge not committed transactions uses >> memory? >> > > It should do writes to disk periodically to free up memory, once you > hit your cache_size limit. > > What is cache_size set to. What are the output from sqlite3_status() > telling you about memory usage? > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory Usage
The database is on-disk ... does huge not committed transactions uses memory? -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Apr 21, 2009, at 2:27 PM, D. Richard Hipp wrote: > > On Apr 21, 2009, at 8:22 AM, Marco Bambini wrote: > >> Yes, executing sqlite3_memory_used () after 183,000 INSERT statement >> returns: 106,766,848. >> Database is never closed during application lifetime. >> Each statement is prepared, stepped and properly finalized. >> > > We do that kind of test all the time but we never get huge memory > usage like this. Are you sure you have opened on on-disk database and > not an in-memory database? > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory Usage
Yes, executing sqlite3_memory_used () after 183,000 INSERT statement returns: 106,766,848. Database is never closed during application lifetime. Each statement is prepared, stepped and properly finalized. P.S. the database is encrypted using your modules. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Apr 21, 2009, at 2:11 PM, D. Richard Hipp wrote: > > On Apr 21, 2009, at 8:08 AM, Marco Bambini wrote: > >> Hello guys, >> >> I am trying to write you again about a simple question... how can I >> limit sqlite memory usage during insert commands? It seems that the >> amount of memory usage increases when the number of objects inserted >> into the database is increased and memory is never freed. >> >> I tried to set PRAGMA cache_size with no luck ... I tried also to >> recompile with SQLITE_ENABLE_MEMORY_MANAGMENT and use >> sqlite3_soft_heap_limit but nothing seems to change. > > > How do you know that memory usage is increasing? Are you using > sqlite3_status() and/or sqlite3_memory_used() to measure memory usage? > > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Memory Usage
Hello guys, I am trying to write you again about a simple question... how can I limit sqlite memory usage during insert commands? It seems that the amount of memory usage increases when the number of objects inserted into the database is increased and memory is never freed. I tried to set PRAGMA cache_size with no luck ... I tried also to recompile with SQLITE_ENABLE_MEMORY_MANAGMENT and use sqlite3_soft_heap_limit but nothing seems to change. Please help. Thanks. -- Marco Bambini ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Memory usage
Hello guys, I need your help in order to solve a very annoying issue with sqlite 3.6.11. I have two opened db inside my application and when I insert 180,000 rows inside a transaction I can see heap memory usage that exceeds 100MB (data is written twice so I have 2 transactions inside two different db each one that write 180,000 rows). I tried to use the sqlite3_soft_heap_limit without much luck(with SQLITE_ENABLE_MEMORY_MANAGEMENT defined) ... please note that transactions are started with BEGIN EXCLUSIVE. I think that the new sqlite versions simply try to cache as much data as possible (memory usage was much lower with version 3.2.1 for example) ... so, how can keep memory usage low or to a limit similar to the old versions? Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query help
Hello all, I have a table foo (id INTEGER, tid INTEGER, optype INTEGER), and I have some data into foo: id id2 optype - 1 2 10 2 2 10 3 2 10 4 2 10 5 2 10 6 2 20 7 2 10 8 2 20 9 2 20 10 2 10 I need a query that returns results like: 1,2,3,4,5 6 7 8,9 10 (divided by optype and sorted by id) If I use a simple: SELECT group_concat(id) FROM rsql_mvcc WHERE transactionID=2 GROUP BY OPTYPE; I obtain: 1,2,3,4,5,7,10 6,8,9 I would really appreciate any help. Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_THREADSAFE=1 and in-memory databases
Hello,
I have compiled compiled sqlite with SQLITE_THREADSAFE=1 and inside my
application I have 2 concurrent threads that need to open some in-
memory databases that must be completely independents like unique file
on disk. I open the in-memory dbs with: sqlite3_open(":memory:",
) ... but from the errors I received during the app usage I wonder
if the combination of compile option and the function used to open the
db give me some sort of shared in-memory db. Should use
sqlite3_open_v2 with the SQLITE_OPEN_FULLMUTEX flag set?
In other words ... I need to open in-memory db in a multithreaded
environment and they must be unique.
Any help would be really appreciate.
Thanks.
--
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.11
What about backup of encrypted databases? -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Feb 18, 2009, at 1:28 AM, D. Richard Hipp wrote: > SQLite version 3.6.11 is now available from the SQLite website: > > http://www.sqlite.org/ > > Version 3.6.11 adds support for a new live-backup API which enables > applications to make backup copies of SQLite databases while the > database is in use. There are also improvements to the documentation > and various obscure bug fixes. > > As always, please let us know if you encounter any difficulties. > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction within script
Is there any documentation available about savepoints? -- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Jan 2, 2009, at 2:19 AM, D. Richard Hipp wrote: > > On Jan 1, 2009, at 7:25 PM, Igor Tandetnik wrote: > >> "Webb Sprague" <webb.spra...@gmail.com> >> wrote in message >> news:b11ea23c0901011622i23f7b583wd05f07104dd06...@mail.gmail.com >>> I have a script containing the following, which works fine except >>> that >>> at the end of the script I get "SQL error near line 5: cannot >>> commit - >>> no transaction is active". The table specified in the file on >>> line 2 >>> gets created just fine, and populated just fine on line 4. I am >>> using >>> sqlite 3.6.7, custom built with new column limits and variable >>> limits. >> >> I suspect .import directive invokes BEGIN and COMMIT internally. >> SQLite >> transactions don't nest. > > > FWIW, nested transactions (in the form of SAVEPOINTs) will appear in > the next SQLite release, which we hope to get out by mid-January. The > SAVEPOINT code is already in CVS and is working well for all of our > tests so far, in case anyone would like to take it out for a test > drive. > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transactions on attached databases
It's a very useful API, thanks a lot. -- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Dec 12, 2008, at 5:26 PM, D. Richard Hipp wrote: > > On Dec 12, 2008, at 11:16 AM, Marco Bambini wrote: > >> I have two databases, db1 and db2. >> At some point I attach db2 to db1 as 'destdb' then I do: >> >> sqlite3_exec(db1, "BEGIN", ...); >> sqlite3_exec(db1, "INSERT INTO destdb.table1 SELECT * FROM >> main.table1", ...); >> sqlite3_exec(db1, "INSERT INTO destdb.table2 SELECT * FROM >> main.table2", ...); >> sqlite3_exec(db1, "COMMIT", ...); >> >> so, I am actually reading from db1 and writing to db2, but since the >> two db are attached and the transaction is started in db1, I wonder >> if >> locking on db1 is marked as RESERVED or EXCLUSIVE at some point. (I >> just would like to know if db1 seems a db with write operations from >> sqlite's point of view)... and should the transaction be started on >> db2 or it doesn't matter when the two dbs are attached? >> > > Recompile with -DSQLITE_DEBUG=1. Then call "PRAGMA lock_status" prior > to the COMMIT (or any other place when you want to know what the > status of the locks is) and it will tell you. > > Or, from C, call sqlite3_file_control() with the > SQLITE_FCNTL_LOCKSTATE option for each attached database and you will > get back the lock status for that database. See the implementation of > the "lock_status" pragma for an example. > > D. Richard Hipp > d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Transactions on attached databases
I have two databases, db1 and db2. At some point I attach db2 to db1 as 'destdb' then I do: sqlite3_exec(db1, "BEGIN", ...); sqlite3_exec(db1, "INSERT INTO destdb.table1 SELECT * FROM main.table1", ...); sqlite3_exec(db1, "INSERT INTO destdb.table2 SELECT * FROM main.table2", ...); sqlite3_exec(db1, "COMMIT", ...); so, I am actually reading from db1 and writing to db2, but since the two db are attached and the transaction is started in db1, I wonder if locking on db1 is marked as RESERVED or EXCLUSIVE at some point. (I just would like to know if db1 seems a db with write operations from sqlite's point of view)... and should the transaction be started on db2 or it doesn't matter when the two dbs are attached? Thanks a lot for the clarifications. -- Marco Bambini ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why SQLITE_LOCKED here?
I have several threads inside an application and each thread opens a connection to the same database. The application has been compiled with SQLITE_THREADSAFE = 1. One of the threads (just one) open another database connection to the same database used by ALL threads for all the writing operations. So I have N threads and N+1 db connections ... and the one db connection is SHARED between all the threads just for WRITE operations (the others N are used just for READ operations on the db). I thought that this approach could prevent my app from receiving a SQLITE_LOCKED error... but sometimes it still occurs. Any idea of the reason of the error? Any idea about how to solve the issue without using the sqlite3_busy_handler or sqlite3_busy_timeout functions? Thanks a lot for the clarifications. -- Marco Bambini ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unable to compile fts2 as loadable extension
Yes I know and fts3 is enabled by default but I need to be able to load fts2 as an external extension for legacy support. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 8, 2008, at 6:15 AM, Alexandre Courbot wrote: > Any reason why you don't want to use fts3 instead? > > Using fts2 means potential big consistency issues if you run vacuum on > your database. Moreover, fts3 should just compile and run smoothly on > latest versions. > > Alex. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unable to compile fts2 as loadable extension
I was able to compile fts1 as a loadable extension but I am having a lot of issue with fts2. The first issue is a duplicate sqlite_api symbol found in fts2.c and fts2_tokenizer.c (due to the SQLITE_EXTENSION_INIT1 macro used in both files). I solved the issue using a: extern sqlite3_api_routines *sqlite3_api; in fts2_tokenizer.c. But now the linked reports missing sqlite3_malloc, sqlite3_free, sqlite3_realloc symbols. Do I have to link with sqlite3.c or I should just write some wrapper functions? Can fts2 be compiled as an external loadable extension? --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual tables
Exactly Stephen! I was trying to dump a database and I was wondering how to deal with virtual tables. I think that a good way to dump a database skipping internally generated real tables could be to: - first create all tables that contains the CREATE VIRTUAL TABLE statement - then get the name of all the tables (not virtual) created inside the db (save their names somewhere) - and at the end copy all the tables whose name was not previously saved I wondering if is there a simpler/better solution... --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Sep 13, 2008, at 7:25 PM, Stephen Woodbridge wrote: > Kishor, > > I think Marco may want to be able to know how to determine which > tables > in a DB are real tables and which ones below to virtual tables. If you > want to do something like dump tables from the database, you do not > want > to be dumping all the internally generated real tables. It might be > nice > if there were some way to identify if a given table was: > > 1) a normal table > 2) a virtual table > 3) a child of a virtual table > > But I'm only guess that this might be what Marco wants? Marco? > > -Steve > > P Kishor wrote: >> On 9/13/08, Marco Bambini <[EMAIL PROTECTED]> wrote: >>> Yes but creating a virtual tables involves the creations of other >>> related tables ... >> >> Well, the FTSn mechanism does all the extra table voodoo for you, so >> you don't have to be bothered about it. From what it seems like, the >> other magic tables are not virtual tables. In any case, we are not >> advised to mess with them unless we have security clearance. >> >>> does all the virtual table implementations (fts1, >>> fts2, fts3) follow the same schema or it is implementation >>> dependent? >> >> Probably there is some difference from FTS1..3, but I have no >> recollection of 2, and I never implemented 1. >> >> In any case, the table that is VIRTUAL is the one that you create >> yourself. And, per your original question of how to identify it, >> well, >> it says so in the schema. There might be a PRAGMA command for it as >> well, but nothing could be clearer than the word VIRTUAL right there >> in the schema. >> >> >>> >>> --- >>> Marco Bambini >>> http://www.sqlabs.net >>> http://www.sqlabs.net/blog/ >>> http://www.sqlabs.net/realsqlserver/ >>> >>> >>> >>> >>> On Sep 13, 2008, at 4:02 PM, P Kishor wrote: >>> >>>> On 9/13/08, Marco Bambini <[EMAIL PROTECTED]> wrote: >>>>> What is the best way to identify virtual tables inside a sqlite >>>>> database? >>>> >>>> >>>> isn't the schema enough? In my world it says >>>> >>>> CREATE VIRTUAL TABLE ... >>>> >>>>> >>>>> Thanks a lot. >>>>> --- >>>>> Marco Bambini >>>>> http://www.sqlabs.net >>>>> http://www.sqlabs.net/blog/ >>>>> http://www.sqlabs.net/realsqlserver/ >>>>> >>> >>>> ___ >>>> sqlite-users mailing list >>>> sqlite-users@sqlite.org >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual tables
Yes but creating a virtual tables involves the creations of other related tables ... does all the virtual table implementations (fts1, fts2, fts3) follow the same schema or it is implementation dependent? --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Sep 13, 2008, at 4:02 PM, P Kishor wrote: > On 9/13/08, Marco Bambini <[EMAIL PROTECTED]> wrote: >> What is the best way to identify virtual tables inside a sqlite >> database? > > > isn't the schema enough? In my world it says > > CREATE VIRTUAL TABLE ... > >> >> Thanks a lot. >> --- >> Marco Bambini >> http://www.sqlabs.net >> http://www.sqlabs.net/blog/ >> http://www.sqlabs.net/realsqlserver/ >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Virtual tables
What is the best way to identify virtual tables inside a sqlite database? Thanks a lot. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_CORE use for ??
Thanks a lot for the clarification. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Jul 30, 2008, at 4:28 PM, D. Richard Hipp wrote: > > On Jul 30, 2008, at 10:22 AM, Marco Bambini wrote: > >> Can someone clarify this point please? >> I mean, if I want to compile sqlite in a way that it should be able >> to >> load extensions, SQLITE_CORE could be defined or not? >> Or if it doesn't matter, what is its role? >> > > A developer using SQLite in their product should never have to mess > with SQLITE_CORE. The SQLITE_CORE macro is for internal use only. If > you find a case where you think you have to set SQLITE_CORE manually > in order to compile SQLite, that is bug - either in SQLite itself or > in your use of SQLite. > > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_CORE use for ??
Can someone clarify this point please? I mean, if I want to compile sqlite in a way that it should be able to load extensions, SQLITE_CORE could be defined or not? Or if it doesn't matter, what is its role? Thanks. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Jul 30, 2008, at 3:45 PM, Mihai Limbasan wrote: > Kevin Tang wrote: >> Dear all, >> >> After I upgrade to SQLite 3.6.0, I found that I must add >> "SQLITE_CORE" in >> PreProcessor to build my program. >> >> What is the "SQLITE_CORE" use for?? >> >> Thanks, >> Kevin Tang. >> >> > When defined, SQLITE_CORE prevents the redefinition of some API > functions in sqlite3ext.h. From that file: > > /* > ** The following macros redefine the API routines so that they are > ** redirected throught the global sqlite3_api structure. > ** > ** This header file is also used by the loadext.c source file > ** (part of the main SQLite library - not an extension) so that > ** it can get access to the sqlite3_api_routines structure > ** definition. But the main library does not want to redefine > ** the API. So the redefinition macros are only valid if the > ** SQLITE_CORE macros is undefined. > */ > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Max limits on the following
http://www.sqlite.org/limits.html --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Jun 23, 2008, at 8:55 AM, Shailesh Birari wrote: > Hello all, > I wanted to know if there are any upper limits on the following: I > have > seen sqlite_ext.h but did not any so just wanted to confirm the same. > > 1) Max number of tables in the sqlite database? > 2) Max number of fields/columns in the table : 2000?? > 3) Max column name size? > 4) Max table name size? > 5) Max database name size? > 6) Max constraint name size? > 7) Max table constraints in the table:? > > Kindly let me know, > Regards > Shailesh > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparison of SQLite applications for Mac
Please take a look also at my SQLiteManager app: http://www.sqlabs.net/sqlitemanager.php --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On May 8, 2008, at 8:56 AM, Neville Franks wrote: > Hi Hartwig, > The last release for this was Apr 2005 so it looks like it has died. > > I'm also working on a mini-review of SQLite GUI DB Managers for > Windows. I'll post to the list when it is ready. > > > Thursday, May 8, 2008, 4:45:09 PM, you wrote: > > HW> Hi Tom, > > HW> SQLite Database Browser (sqlitebrowser.sourceforge.net) seems to > be > HW> missing. > > HW> Hartwig > > HW> Am 07.05.2008 um 06:20 schrieb BareFeet: > >>> Dennis Cote wrote: >>> >>>>> 2. Know of another application that should be included. >>>>> >>> >>>> You may want to include the free SQLite Manager add on for Firefox. >>>> See >>>> https://addons.mozilla.org/en-US/firefox/addon/5817 for additional >>>> information. >>>> >>>> It provides a general database browser and editor that works on Mac >>>> OS as well. >>> >>> Thanks for the pointer, Dennis. I've added the SQLite Manager for >>> Firefox to my review matrix of SQLite GUI software at: >>> http://www.tandb.com.au/sqlite/compare/?mlp >>> >>> If anyone else knows of another program worth adding to the mix, >>> please let me know. >>> >>> Please let me know of any corrections to what's there or any stand >>> out >>> features in your favorite program that you think are worth >>> comparing. >>> >>> Thanks, >>> Tom >>> BareFeet >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> > > > HW> ___ > HW> sqlite-users mailing list > HW> sqlite-users@sqlite.org > HW> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > Best regards, > Neville Franks, http://www.surfulater.com http://blog.surfulater.com > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] String is changing after inserting into database
Is your database UTF-16 encoded? More information at: http://www.sqlite.org/pragma.html PRAGMA encoding section. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Apr 18, 2008, at 8:37 AM, Harish Dixit wrote: > Hello, > > I am inserting some unicode string into the SQLite database. After > inserting, at the time of retrieving value has been modified. > > For example: > > I am inserting "즒铭ꓽ菷\큭셙냼誜\꾁霤꿩뱪낌.wma" > when i am retrieving it the value is : "馒铭ꓽ菷\큭셙냼誜 > \꾁霤꿩뱪낌.wma" > > > It seems that the problem is related to the some symbols having > ASCII value > between these ranges: > > 1.56320 - 57343 > 2.55296 - 56319 > > > I debugged and found that, upto the point where we call sqlite3_step > () > method value goes correctly, but it changed after inserting into the > database. > > > Please help me regarding this issue. > > Thannks in advance. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to select uncomitted rows?
Another approach could be to create an in-memory database (and in in-
memory table, like CREATE TABLE last_transaction(id INTEGER);)
and after each write operation save the rowid of the row using
sqlite3_last_insert_rowid (in C) or using SELECT last_insert_rowid();
(SQL) into that table.
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/
On Apr 17, 2008, at 9:43 PM, Alex Katebi wrote:
> The reason I did not keep track in a seperate table was because I
> wanted to
> do it using triggers. But triggers don't trigger until commit.
>
> On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
>
>> Until the data is committed, it's not really in the database. If you
>> crash, it will be rolled back. So if it's really important to know
>> what data has been written to the database but not committed, why
>> don't you just track what you're writing to the database in an
>> in-memory data structure of some sort? Or, to save space, just track
>> the rowid of the rows you modify.
>>
>> -scott
>>
>>
>> On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED]>
>> wrote:
>>> Hi Richard,
>>>
>>> create table t1 (name);
>>> insert into t1 values ('Alex');
>>> begin;
>>> insert into t1 values ('Richard');
>>> select * from t1;
>>>
>>> How can I select only the second row in the above example?
>>> If there is not an easy way to do this I would probably have to use
>> another
>>> connection then diff the two selects right?
>>>
>>> Thanks,
>>> -Alex
>>>
>>>
>>>
>>>
>>>
>>> On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]>
>>> wrote:
>>>
>>>>
>>>> On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
>>>>> Is there a way to select rows that have not been committed yet?
>>>>>
>>>>
>>>> No. SQLite doesn't really commit rows. It commits pages. A
>>>> single page might hold multiple rows, only some of which might
>>>> have changed. Or a single row might span multiple pages.
>>>>
>>>>
>>>> D. Richard Hipp
>>>> [EMAIL PROTECTED]
>>>>
>>>>
>>>>
>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users@sqlite.org
>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed database schema with SQLite version > 3.5.x
Yes, of course I can send you the DB: the file is under 1MB, about 100K if compressed via .zip Can I send it to you via e-mail? Thanks very much for the support ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Delphi7] TEXT vs. (VAR)CHAR?
A brief history: There used to be a size limitation in dBase and perhaps Paradox. Traditionally this was supported within the Borland Database Engine. In the Delphi components this resolved in text fields and memo fields. The Borland Database Engine (BDE) and the components within Delphi. From this size limit (probably 255 characters) the field types are set into the component layer. I've written a dbExpress driver for Delphi 6, and working a version for DbExpress4 (Delphi 2007) which has a preference which can be set. Using this preference you are allows to implement longer texts as memo's or keep them as strings. Either way the application needs to know when to switch display behaviour. Some programming needs may still be required. It may be that SQLite's BLOB support may be a better way to solve this in the end. I haven't really looked at that yet. In any case it is the Connector or Driver on top of SQLite which determines the actual field type into the component layer of Delphi. This is what the DbExpress driver is supposed to do, translate the database specifics into Delphi specifics (and back). Regards, Marco. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction log writing performance
On Feb 22, 2008, at 4:57 PM, [EMAIL PROTECTED] wrote: > That depends on your filesystem. On many modern file > systems you can safely omit 3 and 4. And if the > xDeviceCharacteristics() method of the VFS implementation > for a particular filesystem reports SQLITE_IOCAP_SAFE_APPEND, > then SQLite skips steps 3 and 4. Steps 3 and 4 are > also skipped if you set > >PRAGMA synchronous=NORMAL; > > instead of the default > >PRAGMA synchronous=FULL; > > It has been reported to us that by omitting steps 3 and > 4 you get about a 30% speed improvement on MacOS X. So, is safe so to set PRAGMA synchronous=NORMAL; under MacOS X? It would be really nice to know under which modern file system it is safe to skip 3 and 4. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite 3.5.5 and SQLITE_ENABLE_LOCKING_STYLE=1
It is safe to define SQLITE_ENABLE_LOCKING_STYLE=1 with sqlite 3.5.5 if I am interested in opening database files on a shared volumes on Mac? As far as I know it was the only workaround ... or something is changed in recent versions? Thanks. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.5.5 Released
What about speed? Can we expect the same performance of version 3.5.4? Thanks a lot for your continue improvements. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Jan 31, 2008, at 6:33 PM, [EMAIL PROTECTED] wrote: SQLite version 3.5.5 is now available for download from the SQLite website: http://www.sqlite.org/ The big change from version 3.5.4 is that the internal virtual machine was reworked to use operands in registers rather than pulling operands from a stack. The virtual machine stack has now been removed. The removal of the VM stack will help prevent future stack overflow bugs and will also facilitate new optimizations in future releases. There should be no user-visible changes to the operation of SQLite in this release, except that the output of EXPLAIN looks different. In order to make this change, about 8.5% of the core SQLite code had to be reworked. We thought this might introduce instability. But we have done two weeks of intensive testing, during which time we have increased the statement test coverage to 99% and during which we have found and fixed lots of minor bugs (mostly things like leaking memory following a malloc failure). But for all of that testing, we have not detected a single bug in the new register-based VM. And for that reason, we believe the new VM, and hence version 3.5.5, is stable and ready for production use. As usual, please report any problems to this mailing list, or directly to me. -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Next Version of SQLite
What will be the main benefits of the new virtual machine? I mean, it will be just faster or there will be other improvements in the library? --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Jan 13, 2008, at 3:07 AM, D. Richard Hipp wrote: On Jan 12, 2008, at 7:55 PM, Shawn Wilsher wrote: Hey all, I was wondering when you plan on releasing the next version of SQLite. Mozilla is currently using 3.5.4, but that does not include some OS/2 fixes that were checked in after the release of 3.5.4. Instead of patching our local copy of sqlite, I'd like to use a release version, but at the same time do not want to delay this fix to our OS/2 users very long. The specific checkins we are looking at are 4646, 4647, and 4648. In case you haven't been watching the timeline (http://www.sqlite.org/cvstrac/timeline) we are in the middle of some major changes. The virtual machine inside of SQLite is being transformed from a stack-based machine into a register-based machine. The whole virtual machine and the code generator is being rewritten. Slowly. Piece by piece. I haven't done an overall line change count yet, but we are looking at some pretty serious code churn. 3.5.4 to 3.5.5 is likely to be the biggest single change in the history of SQLite. So you might not want to release product with 3.5.5 embedded. All the regression tests pass, but still If you like, we can set up a special Mozilla branch off of 3.5.4 that includes the OS/2 fixes. On the other hand, if this is not for a release, but rather for general development work, then please build and test with the latest code from CVS. (This applies to *everybody* not just Mozilla.) Please report any problems. The test suite for SQLite is very thorough, but I have found that users can be very creative in stressing SQLite in ways that I would have never imagined, and have not developed tests for. D. Richard Hipp [EMAIL PROTECTED] -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to get record count
SELECT count(*) FROM myTable; --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Dec 12, 2007, at 2:55 PM, Tom Parke wrote: How can I get a count of the number of records in a table? Sqlite3_get_table() might work, but I only need the count, not the record set. Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2
Starting from version 3.4.2 I receive errors with queries like: SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY a.field or even SELECT a.field FROM a UNION ALL SELECT a.field FROM a ORDER BY a.field error is: ORDER BY term number 1 does not match any result column Tables are created by: CREATE TABLE a (field); CREATE TABLE b (field); Please note that the above queries worked fine with sqlite 3.2.x or 3.3.x. Any idea? --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
I vote for (4). --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Nov 9, 2007, at 7:45 PM, [EMAIL PROTECTED] wrote: "Mark Wyszomierski" <[EMAIL PROTECTED]> wrote: Not a terribly useful comment but was just glancing through the new look and noticed a typo: http://sqlite.hwaci.com/about.html "We believe that General Electric uses SQLite in some product or another because they twice wrote the to SQLite developers ".. "wrote the to " Thanks, Mark. I am going to go through and clean all that up. I'm focused on the layout right now, though. I put up 4 variations. Please, everyone, offer your opinions: (1) http://sqlite.hwaci.com/v1/ No CSS of any kind. (2) http://sqlite.hwaci.com/v2/ CSS menus with rounded corners (3) http://sqlite.hwaci.com/v3/ CSS menus with square corners (4) http://sqlite.hwaci.com/v4/ CSS font specification only (2) and (3) do not work on IE6. (1) has ugly fonts, I am told. That leaves me with (4). I suppose we could go with (4) now and change it later -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
Another solution is to design your css for standard browser and then just create a iefixes.css file to load only in IE that contains the various fixes for that browser. The trick is to add that lines in the head section: --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Nov 9, 2007, at 7:29 PM, [EMAIL PROTECTED] wrote: Joe Wilson <[EMAIL PROTECTED]> wrote: It takes time to get all popular browsers working, but it leaves a good first impression with potential users of your software. It seems like a better solution would be to do the website without any CSS and then spend the days or weeks of frustration saved working on SQLite instead. -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
Yes sure Joe, I just needed some more time. Here it is the output of explain SELECT a FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101; 0|Goto|0|20||1| Integer|0|0||2| OpenRead|1|4|keyinfo(3,BINARY,BINARY)|3| SetNumColumns|1|4||4| Integer|99|0||5| IsNull|-1|18||6| Integer|100|0||7| IsNull|-2|18||8| Integer|101|0||9| IsNull|-3|18||10| MakeRecord|3|0|ddd|11| MemStore|0|0||12| MoveGe|1|18||13| MemLoad|0|0||14| IdxGE|1|18|+|15| Column|1|0||16| Callback|1|0||17| Next|1|13||18| Close|1|0||19| Halt|0|0||20| Transaction|0|0||21| VerifyCookie|0|2||22| Goto|0|1||23| Noop|0|0|| result for CW is still 99... --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 31, 2007, at 10:38 PM, Joe Wilson wrote: You're not the least bit interested in finding out what the issue in CodeWarrior was? It might be a symptom of another problem. --- Marco Bambini <[EMAIL PROTECTED]> wrote: The problem was somewhere inside CodeWarrior because the same exact code worked fine with Visual C. I used CodeWarrior for Win for all my win32 sqlite compilation but it seems time to update my Win Dev environment... __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
The problem was somewhere inside CodeWarrior because the same exact
code worked fine with Visual C.
I used CodeWarrior for Win for all my win32 sqlite compilation but it
seems time to update my Win Dev environment...
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/
On Oct 31, 2007, at 7:59 PM, Joe Wilson wrote:
Can you post the output of this command when you compile
sqlite 3.4.2 with code warrior for your test.sqlite database?
explain SELECT a FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101;
With the sqlite 3.5.1 shell compiled with gcc 4.1.1 I see:
0|Goto|0|25|
1|Integer|0|0|# One
2|OpenRead|0|2|
3|SetNumColumns|0|4|
4|Integer|0|0|# idx_One
5|OpenRead|1|4|keyinfo(3,BINARY,BINARY)
6|Integer|99|0|
7|IsNull|-1|22|
8|Integer|100|0|
9|IsNull|-2|22|
10|Integer|101|0|
11|IsNull|-3|22|
12|MakeRecord|3|0|ddd
13|MemStore|0|0|
14|MoveGe|1|22|
15|MemLoad|0|0|
16|IdxGE|1|22|+
17|IdxRowid|1|0|
18|MoveGe|0|0|
19|Column|0|0|# One.a
20|Callback|1|0|
21|Next|1|15|
22|Close|0|0|
23|Close|1|0|
24|Halt|0|0|
25|Transaction|0|0|
26|VerifyCookie|0|2|
27|TableLock|0|2|One
28|Goto|0|1|
29|Noop|0|0|
Just for the heck of it, can you also provide the code warrior/3.4.2
output for these commands as well?
-- select case 2
-- Getting all columns works
explain SELECT * FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101;
-- select case 3
-- Not using whole index works
explain SELECT a FROM One WHERE b2 = 100 and b3 = 101;
-- select case 4
-- Getting one column, in the index, works
explain SELECT b1 FROM One WHERE b1 = 99 AND b2 = 100 and b3 = 101;
--- Marco Bambini <[EMAIL PROTECTED]> wrote:
To be really sure I rewrote the example in C linked to the official
sqlite 3.4.2.
Here it is my source code:
#include
#include
#include
#include "sqlite3.h"
int main(void)
{
sqlite3 *db = NULL;
int rc = SQLITE_OK;
charsql[256];
char**result;
inti, nrow, ncol;
// open db
rc = sqlite3_open("test.sqlite", );
if (rc != SQLITE_OK) goto abort;
// create table
rc = sqlite3_exec(db, "CREATE TABLE One (a varchar primary key, b1
integer, b2 integer, b3 integer, z varchar);", NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
// create index
rc = sqlite3_exec(db, "CREATE UNIQUE INDEX idx_One ON One(b1, b2,
b3);", NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
// insert loop
for (i=1; i<=100; i++)
{
snprintf(sql, 256, "INSERT INTO One VALUES ('%d', %d, %d, %d,
'A');", i, i+1, i+2, i+3);
rc = sqlite3_exec(db, sql, NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
}
// query test 1
rc = sqlite3_get_table(db, "SELECT a FROM One WHERE b1 = 99 AND b2 =
100 and b3 = 101;", , , , NULL);
if (rc != SQLITE_OK) goto abort;
for(i=0; i<ncol; ++i)
{
printf(result[i]);
printf("\t\t");
}
printf("\n");
for(i=0; i<ncol*nrow; ++i)
{
printf(result[ncol+i]);
printf("\t\t");
if (i % ncol == 0) printf("\n");
}
// free table
sqlite3_free_table(result);
// close db
sqlite3_close(db);
printf("simple test finished!\n");
return 0;
abort:
printf("%s\n", sqlite3_errmsg(db));
if (db != NULL) sqlite3_close(db);
return -1;
}
On Windows (not on Mac!) it returns 99 instead of the correct 98
value.
Anyone can confirm that on Windows?
Thanks a lot.
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/
On Oct 30, 2007, at 5:15 PM, [EMAIL PROTECTED] wrote:
Marco Bambini <[EMAIL PROTECTED]> wrote:
I am experiencing a very strange issue in sqlite 3.4.2 (only
with the
Win32 version, OSX and linux works fine).
I wonder if there was a bug in the 3.4.2 version that I should
fix...
Please note that I cannot upgrade to the latest 3.5.x versions...
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
Hmm ... I was using CodeWarrior for Windows ... maybe its time to upgrade --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 31, 2007, at 12:14 PM, Dan Petitt wrote: I compiled up your code and ran it on Windows using VC6 and got: a 98 Hope this helps Dan -Original Message- From: Marco Bambini [mailto:[EMAIL PROTECTED] Sent: 31 October 2007 09:33 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version No, the database is created by the code it doesn't already exists. I was able to reproduce the issue only on Windows, Mac and Linux worked fine. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 31, 2007, at 10:25 AM, Stephan Beal wrote: On 10/31/07, Marco Bambini <[EMAIL PROTECTED]> wrote: ... On Windows (not on Mac!) it returns 99 instead of the correct 98 value. Anyone can confirm that on Windows? Hi, Marco! While i can't confirm how it behaves under Windows, i can confirm that it returns 98 on Linux: [EMAIL PROTECTED]:~/tmp$ gcc -o win -I$HOME/include win.c -L$HOME/lib - lsqlite3 [EMAIL PROTECTED]:~/tmp$ ./win a 98 simple test finished! i quickly scanned through your code and found no reason that 99 should come up. One thing to check: does your test.sqlite DB already exist o your windows box, with a record already in it? That would explain the discrepancy (but if that were the case, the CREATE TABLE call should fail, so that's probably not the problem). -- - stephan beal http://wanderinghorse.net/home/stephan/ -- -- - To unsubscribe, send email to [EMAIL PROTECTED] -- -- - -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
No, the database is created by the code it doesn't already exists. I was able to reproduce the issue only on Windows, Mac and Linux worked fine. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 31, 2007, at 10:25 AM, Stephan Beal wrote: On 10/31/07, Marco Bambini <[EMAIL PROTECTED]> wrote: ... On Windows (not on Mac!) it returns 99 instead of the correct 98 value. Anyone can confirm that on Windows? Hi, Marco! While i can't confirm how it behaves under Windows, i can confirm that it returns 98 on Linux: [EMAIL PROTECTED]:~/tmp$ gcc -o win -I$HOME/include win.c -L$HOME/lib - lsqlite3 [EMAIL PROTECTED]:~/tmp$ ./win a 98 simple test finished! i quickly scanned through your code and found no reason that 99 should come up. One thing to check: does your test.sqlite DB already exist o your windows box, with a record already in it? That would explain the discrepancy (but if that were the case, the CREATE TABLE call should fail, so that's probably not the problem). -- - stephan beal http://wanderinghorse.net/home/stephan/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Strange error in sqlite 3.4.2 Win32 version
To be really sure I rewrote the example in C linked to the official
sqlite 3.4.2.
Here it is my source code:
#include
#include
#include
#include "sqlite3.h"
int main(void)
{
sqlite3 *db = NULL;
int rc = SQLITE_OK;
charsql[256];
char**result;
int i, nrow, ncol;
// open db
rc = sqlite3_open("test.sqlite", );
if (rc != SQLITE_OK) goto abort;
// create table
rc = sqlite3_exec(db, "CREATE TABLE One (a varchar primary key, b1
integer, b2 integer, b3 integer, z varchar);", NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
// create index
rc = sqlite3_exec(db, "CREATE UNIQUE INDEX idx_One ON One(b1, b2,
b3);", NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
// insert loop
for (i=1; i<=100; i++)
{
snprintf(sql, 256, "INSERT INTO One VALUES ('%d', %d, %d, %d,
'A');", i, i+1, i+2, i+3);
rc = sqlite3_exec(db, sql, NULL, 0, NULL);
if (rc != SQLITE_OK) goto abort;
}
// query test 1
rc = sqlite3_get_table(db, "SELECT a FROM One WHERE b1 = 99 AND b2 =
100 and b3 = 101;", , , , NULL);
if (rc != SQLITE_OK) goto abort;
for(i=0; i<ncol; ++i)
{
printf(result[i]);
printf("\t\t");
}
printf("\n");
for(i=0; i<ncol*nrow; ++i)
{
printf(result[ncol+i]);
printf("\t\t");
if (i % ncol == 0) printf("\n");
}
// free table
sqlite3_free_table(result);
// close db
sqlite3_close(db);
printf("simple test finished!\n");
return 0;
abort:
printf("%s\n", sqlite3_errmsg(db));
if (db != NULL) sqlite3_close(db);
return -1;
}
On Windows (not on Mac!) it returns 99 instead of the correct 98 value.
Anyone can confirm that on Windows?
Thanks a lot.
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/
On Oct 30, 2007, at 5:15 PM, [EMAIL PROTECTED] wrote:
Marco Bambini <[EMAIL PROTECTED]> wrote:
I am experiencing a very strange issue in sqlite 3.4.2 (only with the
Win32 version, OSX and linux works fine).
I wonder if there was a bug in the 3.4.2 version that I should fix...
Please note that I cannot upgrade to the latest 3.5.x versions...
What makes you think the bug is in SQLite and not in your
language interface wrapper? Do you still get the wrong
answer if you run the same queries from the CLI?
--
D. Richard Hipp <[EMAIL PROTECTED]>
--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Proposed sqlite3_initialize() interface
I think that sqlite3_initialize should be allowed to be called more than once. With the help of a static flag, only the first time it is executed the proper initialize functions will be invoked, successive calls to the sqlite3_initialize should just be a NOP operation... --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 30, 2007, at 5:14 PM, Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: It is also an error to invoke sqlite3_initialize() more than once. That is a pretty nasty restriction to have. If you link multiple other libraries into your program, each of which also uses SQLite then you'd somehow have to arrange that only one of them calls sqlite3_initialize which is a serious pain. (The wxPython gui library used to have a similar issue when initializing things like cursors and colours and caused endless grief before it was fixed to allow multiple calls). In any event this is a very serious API change and really does qualify for calling it SQLite 4. Alternatively, you don't actually need the interface for 99.99% of users out there (Windows, Linux, Mac) so you could make it unnecessary for them, but do require it for the various esoteric embedded systems. That would justify still calling it SQLite version 3. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHJ1hXmOOfHg372QQRAjCHAKCdH4R/OQNY3ALUli9nRCmbFeyDfACeIHcY 7irdFT/ofCgoNK0jERTjze8= =yB1W -END PGP SIGNATURE- -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Strange error in sqlite 3.4.2 Win32 version
Hi guys,
I am experiencing a very strange issue in sqlite 3.4.2 (only with the
Win32 version, OSX and linux works fine).
Here it is what's happen:
// create table
CREATE TABLE One( a varchar primary key, b1 integer, b2 integer, b3
integer, z varchar )
CREATE UNIQUE INDEX idx_One ON One( b1, b2, b3 )
// insert 100 rows
// pseudo code
for i as integer = 1 to 100
db.SQLExecute( "INSERT INTO One VALUES( '" + Str(i) + "', " + Str
(i+1) + ", " + Str(i+2) + ", " + Str(i+3) + ", '" + Chr(i +Asc("A"))
+ "' )" )
next
// select case 1
// Getting one column, not in index, FAILS!
rs = db.SQLSelect( "SELECT a FROM One WHERE b1 = 99 AND b2 = 100 and
b3 = 101" )
the return value should be 98, but it is 99!
// select case 2
// Getting all columns works
rs = db.SQLSelect( "SELECT * FROM One WHERE b1 = 99 AND b2 = 100 and
b3 = 101" )
// select case 3
// Not using whole index works
rs = db.SQLSelect( "SELECT a FROM One WHERE b2 = 100 and b3 = 101" )
// select case 4
// Getting one column, in the index, works
rs = db.SQLSelect( "SELECT b1 FROM One WHERE b1 = 99 AND b2 = 100 and
b3 = 101" )
I wonder if there was a bug in the 3.4.2 version that I should fix...
Please note that I cannot upgrade to the latest 3.5.x versions...
Thanks a lot.
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: Custom functions and *
Thanks a lot Joe, I'll take a look at that. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 18, 2007, at 7:05 PM, Joe Wilson wrote: --- Marco Bambini <[EMAIL PROTECTED]> wrote: I need to create a custom function that returns all the value from that row. If the * syntax was supported then I don't need to save or retrieve all the column's name for that table. Here's a simple workaround similar to the 'eval' function in scripting: Look in vacuum.c and you'll see 2 functions: execSql and execExecSql. Create sqlite function wrappers for them. Using these wrapped user functions and querying the sqlite_master table will allow you to generate the SQL query you want with an expanded arg list. This generated SQL will in turn be executed by these functions. There are many example in vacuum.c. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Custom functions and *
I need to create a custom function that returns all the value from that row. If the * syntax was supported then I don't need to save or retrieve all the column's name for that table. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 18, 2007, at 4:44 PM, Igor Tandetnik wrote: Marco Bambini <[EMAIL PROTECTED]> wrote: I created a custom function in sqlite, and when I try to execute it with a statement like: SELECT myfunction(col1, col2, col3) FROM table1 WHERE ... then everything works fine. The problem is that I don't know in advance the names of the columns so I tried to use it with a statement like: SELECT myfunction(*) FROM table1 WHERE ... but when myfunction is executed the argc parameter is set to 0. Is the * syntax supported in custom sqlite3 functions? Well, it _is_ supported - you didn't get a syntax error in your statement. It just doesn't do what you hoped it would. I'm not sure why you expected it to pass a list of all the fields. The only existing case of similar syntax I can think of is COUNT (*), and clearly COUNT doesn't accept a list of fields, and wouldn't know what to do with it. Igor Tandetnik -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3_update_hook
Thanks Dennis for your reply. I would like to avoid triggers for performance reasons. I haven't found an official solution so I am implementing my own sqlite3_update_notify API that is executed before the operation takes place. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 18, 2007, at 4:41 PM, Dennis Cote wrote: Marco Bambini wrote: with sqlite3_update_hook I can get the rowid of the row AFTER it has been INSERTed, DELETEd or UPDATEd. Is there a way to get the rowid of a row BEFORE it is DELETEd or UPDATEd ? If not, can someone suggest a good approach to this problem? Marco, You can use a "before update on table" or "before delete on table" trigger to get the rowid of the row before it is deleted. You can access the value old.rowid from within the trigger and save it into another table for example. See http://www.sqlite.org/ lang_createtrigger.html for more details. HTH Dennis Cote -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_update_hook
Hi, with sqlite3_update_hook I can get the rowid of the row AFTER it has been INSERTed, DELETEd or UPDATEd. Is there a way to get the rowid of a row BEFORE it is DELETEd or UPDATEd ? If not, can someone suggest a good approach to this problem? Thanks a lot, --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Advice about a trigger
I know John, obviously "sql" should be replaced with a way to get the original sql statement that created that row. My question was is there is some smart way to retrieve it... --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Oct 4, 2007, at 3:09 PM, John Stanton wrote: Try using the correct delimiter for SQL literals, ', not ". Marco Bambini wrote: I need to create a trigger that BEFORE a row is deleted from a table, the sql used to create that row (or a way to recreate it) should be saved to another backup table. For example: CREATE TRIGGER trigger_delete Before DELETE ON table1 BEGIN INSERT INTO backup_table(oldid, sql, tablename, operation) VALUES (old.rowid, "sql", "table1", 1); END The missing field is "sql" ... do you have a smart idea to solve my problem? Thanks. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ - To unsubscribe, send email to sqlite-users- [EMAIL PROTECTED] - -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Advice about a trigger
I need to create a trigger that BEFORE a row is deleted from a table, the sql used to create that row (or a way to recreate it) should be saved to another backup table. For example: CREATE TRIGGER trigger_delete Before DELETE ON table1 BEGIN INSERT INTO backup_table(oldid, sql, tablename, operation) VALUES (old.rowid, "sql", "table1", 1); END The missing field is "sql" ... do you have a smart idea to solve my problem? Thanks. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] why a VIEW doesn't contain a ROWID
Hi Kees,
He is telling about the Rowid the unique number that represents each row
in the table, not about a table column named "ID" or anything else, or
the primary key of the table.
[]'s,
Marco Antonio Abreu
IT Quality Systems
[EMAIL PROTECTED]
http://www.itquality.com.br
Kees Nuyt wrote:
> Hi Lokesh,
>
> On Mon, 3 Sep 2007 15:30:10 +0530, you wrote:
>
>
>> This I know, but the thing is, I want the ROWID
>> in VIEW to be sequential even after a SELECT with
>> some condition has been executed, ie., from 1 to n.
>> Just like in normal table.
>> In your case it is not like that.
>>
>
> If you delete rows from a table the tables' rowid isn't
> consecutive anymore:
>
> CREATE TABLE testTbl(
> t_id INTEGER PRIMARY KEY,
> t_name TEXT
> );
> INSERT INTO testTbl VALUES( 1, 'd1' );
> INSERT INTO testTbl VALUES( 2, 'd2' );
> INSERT INTO testTbl VALUES( 3, 'd3' );
> INSERT INTO testTbl VALUES( 4, 'd4' );
> SELECT * FROM testTbl;
> 1|d1
> 2|d2
> 3|d3
> 4|d4
> DELETE FROM testTbl WHERE t_id=2;
> SELECT * FROM testTbl;
> 1|d1
> 3|d3
> 4|d4
>
>
>> By the way, what I mean to say is, why
>> don't we have default ROWID in >VIEW
>> like as in normal TABLE.
>>
>
> Because a view isn't a table.
>
> By the way, the concept of rowid is not in the SQL standard.
> It is a physical property (the B-Tree key) which rows happen to
> have when they are stored the SQLite way. It has no other
> meaning. A member of a set doesn't have an ordinal number in
> relational theory.
>
> Richard Hipp made rowid visible because some tight embedded
> applications can be speeded up nicely by using it.
> Any code which uses the rowid is not portable, though.
>
> The number of a row in a view is its order of its occurence.
> The first row has number 1
> The second row has number 2
> etc.
>
> It is easy to materialize that number in any language you will
> use around your SQL, even in a shell:
>
> sqlite3 your.db "select * from testTbl;" | \
> awk -v OFS='|' '{print NR,$0}'
>
> 1|1|d1
> 2|3|d3
> 3|4|d4
>
> note: \ is linewrap
>
> Regards,
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite versions, binary compatibility
Hi Ray, Look at version 3.4.0 (2007 june 18), third item. - Added explicit upper bounds on the sizes and quantities of things SQLite can process. *This change might cause compatibility problems* for applications that use SQLite in the extreme, which is why the current release is 3.4.0 instead of 3.3.18. []'s, Marco Antonio Abreu IT Quality Systems [EMAIL PROTECTED] http://www.itquality.com.br Ray Kiddy wrote: > > Hello - > > I tried to use an older version of the sqlite3 executable on Mac OS X > with a data file from a newer version. No joy resulted. > > Looking at http://www.sqlite.org/changes.html, I do not see any notes > about whether any version breaks binary compatibility with any older > version. > > Should it just be assumed that any version change breaks all binary > compatibility with earlier versions? Is this done deliberately? Or is > there somewhere else that issues relating to binary compatibility of > data files between versions is documented? > > thanx - ray > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Towards SQLite version 3.5.0
On Aug 28, 2007, at 4:51 PM, Dennis Cote wrote: I wonder if it might not be better to change this API to accept an empty string, in addition to a NULL pointer, to find the default VFS. It seems to me this might make life easier for those writing wrappers in languages that don't have a concept of a NULL pointer. Dennis Cote Just pass 0 in that case. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] incredible slow performance of a trigger
Hi Zlatko, In your commands, I did't find table "stocks" used in view "qry_stocks_sum". In any case, try to use SQLite default types, like REAL and TEXT in place of FLOAT and VARCHAR. The type "VARCHAR(0)" is not indicated for field type and length, please use some thing like "TEXT(40)". Finally, indexes help queries performances. []'s, Marco Antonio Abreu IT Quality Systems [EMAIL PROTECTED] http://www.itquality.com.br Zlatko Matic wrote: > Hello. > I have terrible performance when executing the following query, which > inserts rows from table "products" to table "bom_products": > INSERT INTO bom_products ( > plant, > product, > product_description, > product_base_qty_units, > product_base_qty) > SELECT DISTINCT >products.plant, >products.product, >products.product_description, >products.product_base_qty_units, >products.product_base_qty > FROM >products > ORDER BY >products.plant, >products.product; > The query fires trigger "bom_products_tr_after_row_insert" (see below) > that should populate table "bom_components" with corresponding rows > for every row in table "bom_products" (bom_products and bom_components > are one-to-many). > > CREATE TABLE [products] ( > [products_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > [plant] VARCHAR(0) NULL, > [product] VARCHAR(0) NULL, > [product_description] VARCHAR(0) NULL, > [product_base_qty_units] VARCHAR(0) NULL, > [product_base_qty] FLOAT NULL > ) > > CREATE TABLE [bills_of_materials] ( > [bills_of_materials_id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, > [plant] VARCHAR(0) NULL, > [product] VARCHAR(0) NULL, > [component] VARCHAR(0) NULL, > [component_description] VARCHAR(0) NULL, > [component_brutto_qty] FLOAT NULL, > [component_brutto_qty_units] VARCHAR(0) NULL, > [product_base_qty] FLOAT NULL > ) > > CREATE TABLE [bom_products] ( > [bom_products_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > [plant] VARCHAR(0) NULL, > [product] VARCHAR(0) NULL, > [product_description] VARCHAR(0) NULL, > [product_base_qty_units] VARCHAR(0) NULL, > [product_base_qty] FLOAT NULL, > [product_target_qty] FLOAT NULL > ) > > CREATE TRIGGER [bom_products_tr_after_row_insert] > AFTER INSERT ON [bom_products] > FOR EACH ROW > BEGIN > > INSERT INTO bom_components( > plant, > product, > component, > component_description, > component_brutto_qty, > component_brutto_qty_units, > product_base_qty, > product_target_qty, > component_stock_unrestricted, > component_stock_restricted, > component_stock_qlty_insp, > component_stock_blocked, > component_stock_in_transfer, > component_stock_returns, > component_stock_total) > SELECT DISTINCT >qry_bom_components_input.plant, >qry_bom_components_input.product, >qry_bom_components_input.component, >qry_bom_components_input.component_description, >qry_bom_components_input.component_brutto_qty, >qry_bom_components_input.component_brutto_qty_units, >qry_bom_components_input.product_base_qty, >NEW.product_target_qty, >qry_bom_components_input.component_stock_unrestricted, >qry_bom_components_input.component_stock_restricted, >qry_bom_components_input.component_stock_qlty_insp, >qry_bom_components_input.component_stock_blocked, >qry_bom_components_input.component_stock_in_transfer, >qry_bom_components_input.component_stock_returns, >qry_bom_components_input.component_stock_total > FROM > qry_bom_components_input > WHERE qry_bom_components_input.product=NEW.product > ORDER BY >qry_bom_components_input.plant, > qry_bom_components_input.product, > qry_bom_components_input.component; > END > > CREATE VIEW qry_bom_components_input > AS > SELECT DISTINCT bills_of_materials.plant AS plant, >bills_of_materials.product AS product, >bills_of_materials.component AS component, >bills_of_materials.component_description AS component_description, >bills_of_materials.component_brutto_qty AS component_brutto_qty, >bills_of_materials.component_brutto_qty_units AS > component_brutto_qty_units, >bills_of_materials.product_base_qty AS product_base_qty, >bills_of_materials.product_base_qty AS product_target_qty, >qry_stocks_sum.material_stock_unrestricted AS >component_stock_unrestricted, >qry_stocks_sum.material_stock_restricted AS >component_stock_restricted, qry_stocks_sum.material_stock_qlty_insp AS >component_stock_qlty_insp
Re: [sqlite] Proposed incompatible change to SQLite
We'll be more than happy with a change like that. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Aug 9, 2007, at 5:37 PM, [EMAIL PROTECTED] wrote: We are considering a change to the SQLite API which is technically not backwards compatible. On the other hand, it is difficult to imagine a situation where the change would cause anyone any serious hardship. We suspect that most people will consider this change a vast improvement. The proposed changes is to these APIs: sqlite3_soft_heap_limit(); sqlite3_enable_shared_cache(); sqlite3_release_memory(); As currently implemented (and documented), these routines only work across database connections in the same thread. We propose to modify this so that these routines work across all database connections in the same process. If you think such a change will cause problems for you, please let me know. Tnx. -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] fts2 in the amalgamation source?
I have modified the Makefile, so I have added: SRC += \ $(TOP)/ext/fts2/fts2.c \ $(TOP)/ext/fts2/fts2.h \ $(TOP)/ext/fts2/fts2_hash.c \ $(TOP)/ext/fts2/fts2_hash.h \ $(TOP)/ext/fts2/fts2_porter.c \ $(TOP)/ext/fts2/fts2_tokenizer.h \ $(TOP)/ext/fts2/fts2_tokenizer1.c make sqlite3.c works fine and I was able to compile it. Hope this help. --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Jul 26, 2007, at 4:41 PM, [EMAIL PROTECTED] wrote: "David Crawshaw" <[EMAIL PROTECTED]> wrote: Hello all, I was wondering if it would be possible to include fts2 in the amalgamated version of the source code. It looks like all that needs to be done is add tclsh $(TOP)/ext/fts2/mkfts2amal.tcl to the end of the target_source target in Makefile.in and then add fts2amal.c to the end of the "foreach file" loop in tool/mksqlite3c.tcl. I hesitate because with the scripts effectively written for this, there is probably a reason why fts2 has been omitted. The reason fts2 is omitted is that there are name collisions between internal symbols of fts2 and the SQLite core. So the two entities cannot exist in the same translation unit. I've been meaning to go in and resolve the conflicts, but have not gotten around to that yet. -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Get the data from previous months: please, help me to optimize the query...
Dear both (Christian and Joe), (I'm the original author of the first mail, I'm just using my "usual" mail, now... :D ). Thanks for the replies: both works fine: I have no problem in adding new data to the DB, but the performance IS an issue. I tested your solutions, and I got the data in 200ms, that is really good (compared to the one before). Thanks again Marco 2007/7/13, Joe Wilson <[EMAIL PROTECTED]>: --- Christian Smith <[EMAIL PROTECTED]> wrote: > > Much faster - add 3 new fields in CustomerData which you can populate > > via SQLite's trigger mechanism, or an explicit UPDATE prior to your > > SELECT: > > > > MonthRef-- populate from Months table > > MonthRef2 -- date(Months.MonthRef, '-1 year') > > MonthRef3 -- date(Months.MonthRef, 'start of year', '-1 month') > > > > This way you can avoid several joins with the Months table > > and avoid the use of the slow view. > > > This is leaving you open to data errors. Fair enough - just use a temp table to close that loophole. This is pretty much optimal without changing the original poster's schema or any application logic concerning IDMonth and MonthRef: CREATE TABLE Months ( IDMonth INTEGER PRIMARY KEY NOT NULL, MonthRef INTEGER ); CREATE TABLE CustomerData ( IDCustomerData INTEGER PRIMARY KEY NOT NULL, IDMonth INTEGER, NdgSingolo TEXT NOT NULL DEFAULT '0', NdgCliente TEXT NOT NULL DEFAULT '0', FatturatoNdg REAL DEFAULT 0 , FatturatoGruppo REAL DEFAULT 0 , MargineIntermediazioneLordo REAL DEFAULT 0 , MargineInteresse REAL DEFAULT 0 , MargineServizi REAL DEFAULT 0 , RaccoltaDirettaSM REAL DEFAULT 0, RaccoltaIndirettaSM REAL DEFAULT 0 , ImpieghiSM REAL DEFAULT 0 , RaccoltaDirettaSP REAL DEFAULT 0 ); drop table CustomerData2 if exists; create temp table CustomerData2 as SELECT MonthRef, date(MonthRef, '-1 year') as MonthRef2, date(MonthRef, 'start of year', '-1 month') as MonthRef3, IDCustomerData, Months.IDMonth IDMonth, NdgSingolo, NdgCliente, FatturatoNdg, FatturatoGruppo, MargineIntermediazioneLordo, MargineInteresse, MargineServizi, RaccoltaDirettaSM, RaccoltaIndirettaSM, ImpieghiSM, RaccoltaDirettaSP FROM CustomerData, Months WHERE CustomerData.IDMonth = Months.IDMonth; create index CustomerData2i on CustomerData2(NdgSingolo, NdgCliente, MonthRef); explain query plan SELECT AC.*, M1.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_m1, AP.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_ap, M1.MargineInteresseAS MargineInteresse_m1, AP.MargineInteresseAS MargineInteresse_ap FROM CustomerData2 AC LEFT OUTER JOIN CustomerData2 M1 ON AC.NdgSingolo = M1.NdgSingolo AND AC.NdgCliente = M1.NdgCliente AND M1.MonthRef = AC.MonthRef2 LEFT OUTER JOIN CustomerData2 AP ON AC.NdgSingolo = AP.NdgSingolo AND AC.NdgCliente = AP.NdgCliente AND AP.MonthRef = AC.MonthRef3; -- 0|0|TABLE CustomerData2 AS AC -- 1|1|TABLE CustomerData2 AS M1 WITH INDEX CustomerData2i -- 2|2|TABLE CustomerData2 AS AP WITH INDEX CustomerData2i -- optional - temp table will be destroyed by connection anyway drop table CustomerData2; Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow - To unsubscribe, send email to [EMAIL PROTECTED] -
