Re: [sqlite] Problems with some SQL
The problem: Field UserGroup.UserGroupID is NOT declared as autoincrement field and statement: INSERT INTO UserGroup (UserGroupShortTitle ... don't fill this field. Check yourself with: SELECT * FROM UserGroup Check create autoincrement field : http://www.sqlite.org/faq.html#q1 CREATE TABLE UserGroup ( `UserGroupID` INTEGER PRIMARY KEY, Please see the INTEGER keywords in DDL. Good luck. - Original Message - From: To: Sent: Thursday, March 19, 2009 11:31 PM Subject: [sqlite] Problems with some SQL > Heya, > > I'm currently working on a simple setup for a news and user system. > However, I'm having some issues with the SQL I'm using. It's available @ > http://geekerzoid.pastebin.com/m532d173a > > I keep getting "SQL error: foreign-key violation: UserGroup.UserGroupID" > > Any help would be greatly appreciated. > > ~ Selyoid > > ___ > 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] problems with shared cache?
Well, those 3 x the number of tables in the Db. On Fri, Mar 20, 2009 at 3:17 PM, Damien Elmes wrote: > Yep - but only the three listed on this page: > > http://www.sqlite.org/cvstrac/wiki?p=UndoRedo > > So they shouldn't be modifying anything in the main DB, only the temporary DB. > > On Fri, Mar 20, 2009 at 3:13 PM, Dan wrote: >> >> On Mar 20, 2009, at 8:10 AM, Damien Elmes wrote: >> >>> Unfortunately, a user has just reported the same primary key error >>> message with shared cache disabled, although the freezing appears to >>> have been fixed. >>> >>> However, when I ask the user to send me their deck, I find that: >>> >>> sqlite> pragma integrity_check; >>> integrity_check >>> --- >>> ok >>> sqlite> select id, count(id) from cards group by id having count(id) >>> > 1; >>> sqlite> >>> >>> Any ideas? >> >> Triggers? >> >> >>> >>> >>> On Fri, Mar 20, 2009 at 10:00 AM, Damien Elmes >>> wrote: Hi all, Some of my users have been reporting strange database problems recently, which seem to have gone away when I removed a call to enable_shared_cache(). The problems were noticeable in at least 3.6.1 and 3.6.11, when using databases of 30MB+, and doing large updates using pysqlite. There were two distinct reported problems. One was that the program would just freeze, with no disk access and CPU usage, seemingly in the middle of a DB query, on Win32. I wasn't able to reproduce this on Linux. The other problem was reported by both win32 and mac users, and again I wasn't able to reproduce it. It resulted in errors like this: sqlalchemy.exceptions.IntegrityError: (IntegrityError) PRIMARY KEY must be unique 'update cards set isDue = 0 where type in (0,1,2) and priority = 0 and isDue = 1' {} .. which is strange, because the primary key on that table is called 'id' and is not affected by the update call. I also had some reports of DB corruption on OSX, but I'm not sure if that occurred since I upgrade to 3.6.11. One other hint is that while I'd been using shared cache mode for at least 6 months or more, these problems seem to have only surfaced recently. I'm not sure if that's due to a change in the queries I've been doing, or the fact that I changed the cache size to a bigger number, and changed the page size to 4096. Anyway, disabling the shared cache appears to have fixed the problem, and since my program is single threaded and has no need for the shared cache, it's not an issue for us anymore. But I thought it's worth reporting. Have there been any other instances of problems with the shared cache mode? Cheers, Damien >>> ___ >>> 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] problems with shared cache?
Yep - but only the three listed on this page: http://www.sqlite.org/cvstrac/wiki?p=UndoRedo So they shouldn't be modifying anything in the main DB, only the temporary DB. On Fri, Mar 20, 2009 at 3:13 PM, Dan wrote: > > On Mar 20, 2009, at 8:10 AM, Damien Elmes wrote: > >> Unfortunately, a user has just reported the same primary key error >> message with shared cache disabled, although the freezing appears to >> have been fixed. >> >> However, when I ask the user to send me their deck, I find that: >> >> sqlite> pragma integrity_check; >> integrity_check >> --- >> ok >> sqlite> select id, count(id) from cards group by id having count(id) >> > 1; >> sqlite> >> >> Any ideas? > > Triggers? > > >> >> >> On Fri, Mar 20, 2009 at 10:00 AM, Damien Elmes >> wrote: >>> Hi all, >>> >>> Some of my users have been reporting strange database problems >>> recently, which seem to have gone away when I removed a call to >>> enable_shared_cache(). The problems were noticeable in at least 3.6.1 >>> and 3.6.11, when using databases of 30MB+, and doing large updates >>> using pysqlite. >>> >>> There were two distinct reported problems. One was that the program >>> would just freeze, with no disk access and CPU usage, seemingly in >>> the >>> middle of a DB query, on Win32. I wasn't able to reproduce this on >>> Linux. >>> >>> The other problem was reported by both win32 and mac users, and again >>> I wasn't able to reproduce it. It resulted in errors like this: >>> >>> sqlalchemy.exceptions.IntegrityError: (IntegrityError) PRIMARY KEY >>> must be unique 'update cards set isDue = 0 where type in (0,1,2) and >>> priority = 0 and isDue = 1' {} >>> >>> .. which is strange, because the primary key on that table is called >>> 'id' and is not affected by the update call. >>> >>> I also had some reports of DB corruption on OSX, but I'm not sure if >>> that occurred since I upgrade to 3.6.11. >>> >>> One other hint is that while I'd been using shared cache mode for at >>> least 6 months or more, these problems seem to have only surfaced >>> recently. I'm not sure if that's due to a change in the queries I've >>> been doing, or the fact that I changed the cache size to a bigger >>> number, and changed the page size to 4096. >>> >>> Anyway, disabling the shared cache appears to have fixed the problem, >>> and since my program is single threaded and has no need for the >>> shared >>> cache, it's not an issue for us anymore. But I thought it's worth >>> reporting. Have there been any other instances of problems with the >>> shared cache mode? >>> >>> Cheers, >>> >>> Damien >>> >> ___ >> 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] problems with shared cache?
On Mar 20, 2009, at 8:10 AM, Damien Elmes wrote: > Unfortunately, a user has just reported the same primary key error > message with shared cache disabled, although the freezing appears to > have been fixed. > > However, when I ask the user to send me their deck, I find that: > > sqlite> pragma integrity_check; > integrity_check > --- > ok > sqlite> select id, count(id) from cards group by id having count(id) > > 1; > sqlite> > > Any ideas? Triggers? > > > On Fri, Mar 20, 2009 at 10:00 AM, Damien Elmes > wrote: >> Hi all, >> >> Some of my users have been reporting strange database problems >> recently, which seem to have gone away when I removed a call to >> enable_shared_cache(). The problems were noticeable in at least 3.6.1 >> and 3.6.11, when using databases of 30MB+, and doing large updates >> using pysqlite. >> >> There were two distinct reported problems. One was that the program >> would just freeze, with no disk access and CPU usage, seemingly in >> the >> middle of a DB query, on Win32. I wasn't able to reproduce this on >> Linux. >> >> The other problem was reported by both win32 and mac users, and again >> I wasn't able to reproduce it. It resulted in errors like this: >> >> sqlalchemy.exceptions.IntegrityError: (IntegrityError) PRIMARY KEY >> must be unique 'update cards set isDue = 0 where type in (0,1,2) and >> priority = 0 and isDue = 1' {} >> >> .. which is strange, because the primary key on that table is called >> 'id' and is not affected by the update call. >> >> I also had some reports of DB corruption on OSX, but I'm not sure if >> that occurred since I upgrade to 3.6.11. >> >> One other hint is that while I'd been using shared cache mode for at >> least 6 months or more, these problems seem to have only surfaced >> recently. I'm not sure if that's due to a change in the queries I've >> been doing, or the fact that I changed the cache size to a bigger >> number, and changed the page size to 4096. >> >> Anyway, disabling the shared cache appears to have fixed the problem, >> and since my program is single threaded and has no need for the >> shared >> cache, it's not an issue for us anymore. But I thought it's worth >> reporting. Have there been any other instances of problems with the >> shared cache mode? >> >> Cheers, >> >> Damien >> > ___ > 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] sqlite3 --version showing the wrong version
Ok, i think i figured it out, only took me about 8 hours! see the path i had: > $PATH=usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:/usr/bin:/bin:/usr/sb > > in:/sbin:/usr/local/bin:/usr/X11/bin it's missing the / as the very beginning of the path. i changed it to /usr/local... and now it all works fine. it was installing sqlite3 to my local bin but the version was being read from the usr/bin instead of the usr/local/bin i believe. this was my fault. i installed mysql some time ago and must have accidently left off the / when i was modifying the path :( thanks all for your help, I believe this solved the problem : D Lawrence Gold-3 wrote: > > On Mar 19, 2009, at 5:19 PM, erfon haubenstock wrote: > >> >> ok so macports installed sqlite3 just fine, but how do i check the >> version? >> i think i now have 3 versions of sqlite installed. >> >> 1 - when i type sqlite3 --version i get version 3.4.0 >> 2 - when i type usr/local/bin/sqlite3 --version i get version 3.5.4 >> 3 - I know that macports has installed 3.6.11, but don't know where >> it was >> installed. >> >> How on earth do i get all these versions to be the same? > > MacPorts installs to /opt/local/bin by default. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/sqlite3---version-showing-the-wrong-version-tp22596369p22613840.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problems with shared cache?
Unfortunately, a user has just reported the same primary key error message with shared cache disabled, although the freezing appears to have been fixed. However, when I ask the user to send me their deck, I find that: sqlite> pragma integrity_check; integrity_check --- ok sqlite> select id, count(id) from cards group by id having count(id) > 1; sqlite> Any ideas? On Fri, Mar 20, 2009 at 10:00 AM, Damien Elmes wrote: > Hi all, > > Some of my users have been reporting strange database problems > recently, which seem to have gone away when I removed a call to > enable_shared_cache(). The problems were noticeable in at least 3.6.1 > and 3.6.11, when using databases of 30MB+, and doing large updates > using pysqlite. > > There were two distinct reported problems. One was that the program > would just freeze, with no disk access and CPU usage, seemingly in the > middle of a DB query, on Win32. I wasn't able to reproduce this on > Linux. > > The other problem was reported by both win32 and mac users, and again > I wasn't able to reproduce it. It resulted in errors like this: > > sqlalchemy.exceptions.IntegrityError: (IntegrityError) PRIMARY KEY > must be unique 'update cards set isDue = 0 where type in (0,1,2) and > priority = 0 and isDue = 1' {} > > .. which is strange, because the primary key on that table is called > 'id' and is not affected by the update call. > > I also had some reports of DB corruption on OSX, but I'm not sure if > that occurred since I upgrade to 3.6.11. > > One other hint is that while I'd been using shared cache mode for at > least 6 months or more, these problems seem to have only surfaced > recently. I'm not sure if that's due to a change in the queries I've > been doing, or the fact that I changed the cache size to a bigger > number, and changed the page size to 4096. > > Anyway, disabling the shared cache appears to have fixed the problem, > and since my program is single threaded and has no need for the shared > cache, it's not an issue for us anymore. But I thought it's worth > reporting. Have there been any other instances of problems with the > shared cache mode? > > Cheers, > > Damien > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [newbie] Binary and text-file storage in SQL(ite).
2009/3/19 Nuno Magalhães : > Yeah, a religious question, i know, but what's recomended?... > > I have a small site that may have some images for some products. Said > products may also have descriptions. I have other small sites and one > where i'd like to manipulate large texts (say around 5 paragraphs... > ~5000 characters). > > What's recomended: to store strings that are the /path/to/textfile or > /to/image.jpg? Or to store the image file in the database? the text > file in the database? Which datatypes to use? SQLite has only two relevant dataypes for you... TEXT and BLOB. If you don't have a bazillion records of text and images that are megabytes+ in size, it is likely 6 of one or sqrt(36) of the other. Storing everything in a db will give you the convenience of managing only one file, of not having to cook up a file naming and storing scheme, and so on. Storing them on the filesystem will not tie you down to always using the db to access your files. Try one, then try the other. Use what you prefer. > > TIA, > Nuno Magalhães > LU#484677 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ Sent from: Madison WI United States. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] problems with shared cache?
Hi all, Some of my users have been reporting strange database problems recently, which seem to have gone away when I removed a call to enable_shared_cache(). The problems were noticeable in at least 3.6.1 and 3.6.11, when using databases of 30MB+, and doing large updates using pysqlite. There were two distinct reported problems. One was that the program would just freeze, with no disk access and CPU usage, seemingly in the middle of a DB query, on Win32. I wasn't able to reproduce this on Linux. The other problem was reported by both win32 and mac users, and again I wasn't able to reproduce it. It resulted in errors like this: sqlalchemy.exceptions.IntegrityError: (IntegrityError) PRIMARY KEY must be unique 'update cards set isDue = 0 where type in (0,1,2) and priority = 0 and isDue = 1' {} .. which is strange, because the primary key on that table is called 'id' and is not affected by the update call. I also had some reports of DB corruption on OSX, but I'm not sure if that occurred since I upgrade to 3.6.11. One other hint is that while I'd been using shared cache mode for at least 6 months or more, these problems seem to have only surfaced recently. I'm not sure if that's due to a change in the queries I've been doing, or the fact that I changed the cache size to a bigger number, and changed the page size to 4096. Anyway, disabling the shared cache appears to have fixed the problem, and since my program is single threaded and has no need for the shared cache, it's not an issue for us anymore. But I thought it's worth reporting. Have there been any other instances of problems with the shared cache mode? Cheers, Damien ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [newbie] Binary and text-file storage in SQL(ite).
Yeah, a religious question, i know, but what's recomended?... I have a small site that may have some images for some products. Said products may also have descriptions. I have other small sites and one where i'd like to manipulate large texts (say around 5 paragraphs... ~5000 characters). What's recomended: to store strings that are the /path/to/textfile or /to/image.jpg? Or to store the image file in the database? the text file in the database? Which datatypes to use? TIA, Nuno Magalhães LU#484677 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange SQLITE_CORRUPT error
On Mar 19, 2009, at 8:33 PM, Kim, Albert wrote: > > /* The maximum page number is 2^31. Return SQLITE_CORRUPT if a page > ** number greater than this, or zero, is requested. > */ > if( pgno>PAGER_MAX_PGNO || pgno==0 || pgno==PAGER_MJ_PGNO(pPager) ){ > return SQLITE_CORRUPT_BKPT; } > > > With some debug printfs I was able to see that pgno is -1 at this > point! pgno is an unsigned integer, so the value is really 4294967295. That number is greater than PAGER_MAX_PGNO, which is what is causing the SQLITE_CORRUPT to be returned. If you can figure out who is calling pagerAcquire() with the faulty page number, that would be helpful. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
On Thu, Mar 19, 2009 at 6:19 PM, erfon haubenstock wrote: > > ok so macports installed sqlite3 just fine, but how do i check the version? > i think i now have 3 versions of sqlite installed. > > 1 - when i type sqlite3 --version i get version 3.4.0 > 2 - when i type usr/local/bin/sqlite3 --version i get version 3.5.4 > 3 - I know that macports has installed 3.6.11, but don't know where it was > installed. > > How on earth do i get all these versions to be the same? now, why would you want that? If they were all the same, then why have three versions? Think about that. Would you want three identical copies of iTunes on your computer? No? well, then why have three identical copies of SQLite? As I said earlier, you have taken a very difficult route to accomplish a very simple thing. Your computer comes with a version that is in /usr/bin. My suggestion -- leave that alone and don't tinker with it. You install your own version in /usr/local (the binary in /usr/local/bin, the libs in /usr/local/lib and so on). It will take less time to do that than all the back and forth on email lists. Also, don't mess with macports and fink unless you really have to as they are usually more grief than worth it (but, you already have messed with them, so can't unwind that). You are using bash. Just type 'setting bash path' in Google and look up the answer. You should know that anyway -- it is useful knowledge. While at it, learn up on the role of the .bashrc and .bash_profile files as well. Very useful. The whole exercise will take about 15 to 20 mins including all the downloading, installing and learning about bash. Good luck. > > thanks > > erfon > > erfon haubenstock wrote: >> >> Thanks for pointing me to that piece of the guide. Copying the contents >> of my .profile into my .bash_login fixed the port command problem i was >> having : D >> >> This is my first experience with paths, .profile, or .bash_login and >> macports so this has all be quite a learning experience for me. I feel >> like neo from the matrix... >> >> Trying to install sqlite3 now, I'll let you all know how it goes. >> >> thanks >> >> erfon >> >> Lawrence Gold-3 wrote: >>> >>> On Mar 19, 2009, at 4:29 PM, erfon haubenstock wrote: >>> Hi again. Thanks for trying to help me by the way : D I tried relauching terminal and even restarting my computer, but to no avail. I'm not totally sure I understand when you say: One common pitfall here is that you must open a new shell for the port command to be recognised (or re-export the correct PATH). This seems likely as the default location for the port command is /opt/local/bin/port, which is not in your PATH. Do I need to change my PATH? >>> >>> The MacPorts installer is supposed to automatically set up the PATH >>> for you. I wonder if you might have a .bash_login or .bash_profile >>> file that's taking precedence: >>> >>> http://guide.macports.org/#installing.shell >>> >>> Regardless, typing >>> >>> /opt/local/bin/sqlite3 >>> >>> should bring up the version you expect. >>> -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ Sent from: Madison WI United States. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Strange SQLITE_CORRUPT error
Hi, I'm seeing a very strange, consistently reproducible case, in which SQLITE_CORRUPT is being returned in response to a simple insert statement. There is only one connection to the db so it is unlikely a concurrency issue. The insert is immediately after the table is created. The table creation statement goes through successfully and if I look at the db file afterward with the sqlite explorer, the db and table look fine, but without inserted values, of course. This happens every time, in exactly the same way, which seems to rule out random corruption of the db file.I did a little debugging into the sqlite code and this is where the "corruption" is caught first: static int pagerAcquire( Pager *pPager, /* The pager open on the database file */ Pgno pgno, /* Page number to fetch */ DbPage **ppPage,/* Write a pointer to the page here */ int noContent /* Do not bother reading content from disk if true */ ){ PgHdr *pPg; int rc; assert( pPager->state==PAGER_UNLOCK || pPager->nRef>0 || pgno==1 ); /* The maximum page number is 2^31. Return SQLITE_CORRUPT if a page ** number greater than this, or zero, is requested. */ if( pgno>PAGER_MAX_PGNO || pgno==0 || pgno==PAGER_MJ_PGNO(pPager) ){ return SQLITE_CORRUPT_BKPT; http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
On Mar 19, 2009, at 5:19 PM, erfon haubenstock wrote: > > ok so macports installed sqlite3 just fine, but how do i check the > version? > i think i now have 3 versions of sqlite installed. > > 1 - when i type sqlite3 --version i get version 3.4.0 > 2 - when i type usr/local/bin/sqlite3 --version i get version 3.5.4 > 3 - I know that macports has installed 3.6.11, but don't know where > it was > installed. > > How on earth do i get all these versions to be the same? MacPorts installs to /opt/local/bin by default. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
ok so macports installed sqlite3 just fine, but how do i check the version? i think i now have 3 versions of sqlite installed. 1 - when i type sqlite3 --version i get version 3.4.0 2 - when i type usr/local/bin/sqlite3 --version i get version 3.5.4 3 - I know that macports has installed 3.6.11, but don't know where it was installed. How on earth do i get all these versions to be the same? thanks erfon erfon haubenstock wrote: > > Thanks for pointing me to that piece of the guide. Copying the contents > of my .profile into my .bash_login fixed the port command problem i was > having : D > > This is my first experience with paths, .profile, or .bash_login and > macports so this has all be quite a learning experience for me. I feel > like neo from the matrix... > > Trying to install sqlite3 now, I'll let you all know how it goes. > > thanks > > erfon > > Lawrence Gold-3 wrote: >> >> On Mar 19, 2009, at 4:29 PM, erfon haubenstock wrote: >> >>> >>> Hi again. Thanks for trying to help me by the way : D >>> >>> I tried relauching terminal and even restarting my computer, but to no >>> avail. >>> >>> I'm not totally sure I understand when you say: >>> >>> One common pitfall here is that you must open a new shell for the port >>> command to be recognised (or re-export the correct PATH). This seems >>> likely >>> as the default location for the port command is /opt/local/bin/port, >>> which >>> is not in your PATH. >>> >>> Do I need to change my PATH? >> >> The MacPorts installer is supposed to automatically set up the PATH >> for you. I wonder if you might have a .bash_login or .bash_profile >> file that's taking precedence: >> >> http://guide.macports.org/#installing.shell >> >> Regardless, typing >> >> /opt/local/bin/sqlite3 >> >> should bring up the version you expect. >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > -- View this message in context: http://www.nabble.com/sqlite3---version-showing-the-wrong-version-tp22596369p22611451.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposal for SQLite and non pure ASCII letters
On 20/03/2009 1:55 AM, Mail.sqlite wrote: > Roger: > 4. Your comments sound to me that you are living in a 7bit ASCII country, do > you? >> Mail.sqlite wrote: >>> - Please, let us try to bring down the discussion to the intended solution - a simple way to define and use a "user defined" collating for 8 bit ASCII characters! FYI there are no such things as "7bit ASCII country" or "8 bit ASCII character". ASCII is a 7-bit character set, everywhere. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
Thanks for pointing me to that piece of the guide. Copying the contents of my .profile into my .bash_login fixed the port command problem i was having : D This is my first experience with paths, .profile, or .bash_login and macports so this has all be quite a learning experience for me. I feel like neo from the matrix... Trying to install sqlite3 now, I'll let you all know how it goes. thanks erfon Lawrence Gold-3 wrote: > > On Mar 19, 2009, at 4:29 PM, erfon haubenstock wrote: > >> >> Hi again. Thanks for trying to help me by the way : D >> >> I tried relauching terminal and even restarting my computer, but to no >> avail. >> >> I'm not totally sure I understand when you say: >> >> One common pitfall here is that you must open a new shell for the port >> command to be recognised (or re-export the correct PATH). This seems >> likely >> as the default location for the port command is /opt/local/bin/port, >> which >> is not in your PATH. >> >> Do I need to change my PATH? > > The MacPorts installer is supposed to automatically set up the PATH > for you. I wonder if you might have a .bash_login or .bash_profile > file that's taking precedence: > > http://guide.macports.org/#installing.shell > > Regardless, typing > > /opt/local/bin/sqlite3 > > should bring up the version you expect. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/sqlite3---version-showing-the-wrong-version-tp22596369p22611338.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
Ok, we're getting somewhere now. I typed /usr/local/bin/sqlite3 -version and got 3.6.11. But when i typed sqlite3 --version i get 3.4. So it must be a path issue. This is my first experience with PATH. I know I need to change my path, but I don't know what is missing from it that is giving me the issue. I'll google for help on this but any suggestions would be appreciated. thanks all again for your help! then type sqlite3 -version. If you still get 3.4.0, your path is wrong. P Kishor-3 wrote: > > On Thu, Mar 19, 2009 at 5:29 PM, erfon haubenstock wrote: >> >> Hi again. Thanks for trying to help me by the way : D >> >> I tried relauching terminal and even restarting my computer, but to no >> avail. >> >> I'm not totally sure I understand when you say: >> >> One common pitfall here is that you must open a new shell for the port >> command to be recognised (or re-export the correct PATH). This seems >> likely >> as the default location for the port command is /opt/local/bin/port, >> which >> is not in your PATH. >> >> Do I need to change my PATH? >> >> thanks! >> >> erfon >> >> Jean-Denis Muys-2 wrote: >>> >>> On 3/19/09 12:20 PM, "erfon haubenstock" wrote: >>> EEESH, i installed macports and when i try to port install i get an error that says the error "port: command not found" maybe this has something to do with it? my $PATH=usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:/usr/bin:/bin:/usr/sb in:/sbin:/usr/local/bin:/usr/X11/bin >>> >>> One common pitfall here is that you must open a new shell for the port >>> command to be recognised (or re-export the correct PATH). This seems >>> likely >>> as the default location for the port command is /opt/local/bin/port, >>> which >>> is not in your PATH. >>> >>> If it's there, it'll probably be as easy as quitting and relaunching >>> terminal. >>> >>> Jean-Denis >>> > > > why make things so difficult for yourself? > > download the amalgamation source code and untar/unzip it somwhere. > > type ./configure followed by make && sudo make install > > this will put your sqlite libraries under /usr/local/ > > type /usr/local/bin/sqlite3 -version and see if you get 3.6.11 > > then type sqlite3 -version. If you still get 3.4.0, your path is wrong. > > Google for how to set the correct path for your shell. > > The whole operation should take about 15 mins from start to finish > (and that includes the initial download times). > > don't screw around with macports and fink and other such complications > when they are not needed. > > -- > Puneet Kishor http://www.punkish.org/ > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ > Carbon Model http://carbonmodel.org/ > Open Source Geospatial Foundation http://www.osgeo.org/ > Sent from: Madison WI United States. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/sqlite3---version-showing-the-wrong-version-tp22596369p22611258.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
On Mar 19, 2009, at 4:29 PM, erfon haubenstock wrote: > > Hi again. Thanks for trying to help me by the way : D > > I tried relauching terminal and even restarting my computer, but to no > avail. > > I'm not totally sure I understand when you say: > > One common pitfall here is that you must open a new shell for the port > command to be recognised (or re-export the correct PATH). This seems > likely > as the default location for the port command is /opt/local/bin/port, > which > is not in your PATH. > > Do I need to change my PATH? The MacPorts installer is supposed to automatically set up the PATH for you. I wonder if you might have a .bash_login or .bash_profile file that's taking precedence: http://guide.macports.org/#installing.shell Regardless, typing /opt/local/bin/sqlite3 should bring up the version you expect. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
On Thu, Mar 19, 2009 at 5:29 PM, erfon haubenstock wrote: > > Hi again. Thanks for trying to help me by the way : D > > I tried relauching terminal and even restarting my computer, but to no > avail. > > I'm not totally sure I understand when you say: > > One common pitfall here is that you must open a new shell for the port > command to be recognised (or re-export the correct PATH). This seems likely > as the default location for the port command is /opt/local/bin/port, which > is not in your PATH. > > Do I need to change my PATH? > > thanks! > > erfon > > Jean-Denis Muys-2 wrote: >> >> On 3/19/09 12:20 PM, "erfon haubenstock" wrote: >> >>> >>> EEESH, i installed macports and when i try to port install i get an error >>> that says the error "port: command not found" >>> >>> maybe this has something to do with it? my >>> $PATH=usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:/usr/bin:/bin:/usr/sb >>> in:/sbin:/usr/local/bin:/usr/X11/bin >>> >> >> One common pitfall here is that you must open a new shell for the port >> command to be recognised (or re-export the correct PATH). This seems >> likely >> as the default location for the port command is /opt/local/bin/port, which >> is not in your PATH. >> >> If it's there, it'll probably be as easy as quitting and relaunching >> terminal. >> >> Jean-Denis >> why make things so difficult for yourself? download the amalgamation source code and untar/unzip it somwhere. type ./configure followed by make && sudo make install this will put your sqlite libraries under /usr/local/ type /usr/local/bin/sqlite3 -version and see if you get 3.6.11 then type sqlite3 -version. If you still get 3.4.0, your path is wrong. Google for how to set the correct path for your shell. The whole operation should take about 15 mins from start to finish (and that includes the initial download times). don't screw around with macports and fink and other such complications when they are not needed. -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ Sent from: Madison WI United States. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
Hi again. Thanks for trying to help me by the way : D I tried relauching terminal and even restarting my computer, but to no avail. I'm not totally sure I understand when you say: One common pitfall here is that you must open a new shell for the port command to be recognised (or re-export the correct PATH). This seems likely as the default location for the port command is /opt/local/bin/port, which is not in your PATH. Do I need to change my PATH? thanks! erfon Jean-Denis Muys-2 wrote: > > On 3/19/09 12:20 PM, "erfon haubenstock" wrote: > >> >> EEESH, i installed macports and when i try to port install i get an error >> that says the error "port: command not found" >> >> maybe this has something to do with it? my >> $PATH=usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:/usr/bin:/bin:/usr/sb >> in:/sbin:/usr/local/bin:/usr/X11/bin >> > > One common pitfall here is that you must open a new shell for the port > command to be recognised (or re-export the correct PATH). This seems > likely > as the default location for the port command is /opt/local/bin/port, which > is not in your PATH. > > If it's there, it'll probably be as easy as quitting and relaunching > terminal. > > Jean-Denis > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/sqlite3---version-showing-the-wrong-version-tp22596369p22609629.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems with some SQL
Heya, I'm currently working on a simple setup for a news and user system. However, I'm having some issues with the SQL I'm using. It's available @ http://geekerzoid.pastebin.com/m532d173a I keep getting "SQL error: foreign-key violation: UserGroup.UserGroupID" Any help would be greatly appreciated. ~ Selyoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] checking if a record exist or not
"P Kishor" wrote > On Thu, Mar 19, 2009 at 12:01 PM, jose isaias cabrera wrote: >> >> Greetings... >> >> I am updating an sqlite db from another sqlite db and it is working fine, >> unless the record does not exists. ie. >> >> "ATTACH db2 AS client; "; >> >> BEGIN; >> INSERT OR REPLACE INTO LSOpenJobs >> SELECT * FROM client.LSOpenJobs >> WHERE login='x' AND XtraB > '2000'; >> COMMIT; >> >> this works fine if the record already exists in the db getting updated. >> However, if the record was deleted, inadvertively, from the db getting >> updated, this will not work. >> >> Is there a way to insert the record back in the db getting updated if it >> is >> not there? You should know that this record did exists previously in this >> db, but it was deleted. > > > José, you didn't communicate the above very well, because the above > sentence makes no sense whatsoever. If you are still in a transaction > and delete something that you didn't mean to, you can always bring it > back by not committing the transaction. Yes, I am kinda new. Heck, I am a newbie and I may not have communicated the correct situation. Never mind, it does work. My fault. I got scared, because I built this system counting on INSERT OR REPLACE to work was the words describe. Thanks guys. False alarm. My apologies. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] checking if a record exist or not
"Igor Tandetnik" wrote... > jose isaias cabrera > wrote: >> I am updating an sqlite db from another sqlite db and it is working >> fine, unless the record does not exists. ie. >> >> "ATTACH db2 AS client; "; >> >> BEGIN; >> INSERT OR REPLACE INTO LSOpenJobs >> SELECT * FROM client.LSOpenJobs >> WHERE login='x' AND XtraB > '2000'; >> COMMIT; >> >> this works fine if the record already exists in the db getting >> updated. However, if the record was deleted, inadvertively, from the >> db getting updated, this will not work. > > What do you mean, will not work? In precisely what way does the observed > behavior differ from one you expect? I expected the INSERT to insert the record that no longer exists in the main DB. There are two DBs. One that guide the unique record number system and another one, that is a copy of it. I expected, that if a record is deleted from the main DB, but it still existed on another DB, when that user pushed the data to the main DB, the INSERT will find out that it was not there and it would insert it back into the main db. >> Is there a way to insert the record back in the db getting updated if >> it is not there? > > That's precisely what the INSERT part of "INSERT OR REPLACE" would do. I know, but it is not working. > Or are you saying that there's a record that isn't currently in the main > db (perhaps it existed some time ago, but was deleted) nor in the > attached db, and you expect SQLite to somehow magically conjure it up > out of thin air? I don't think real world works quite this way. No. The record exists in the attached DB, but not in the connected db, though, it did exist there. I allowed deletion for records because I expected the INSERT to insert it to the main db. > Igor Tandetnik thanks for the help. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposal for SQLite and non pure ASCII letters
On Thu, Mar 19, 2009 at 12:31:00PM -0700, Noah Hart scratched on the wall: > Rather my point is that it would be of benefit if SQLite would have > some built in mechanism for a rule-based collation. SQLite *does* have a mechanism for "rule-based" or any-other based collations: User defined collations. They work the exact same way built-in collations work. The API is simple and straight forward. If you want to show the SQLite community this is a Good Idea, there is one simple, easy, fast solution: stop writing posts and start writing code. I also don't buy the black and white argument that this needs to go into the core or it isn't worth doing. FTS wasn't in the core distribution for ages. Neither was the ICU stuff. They still aren't on by default... Yet plenty of people were able to do great work with it and -- most importantly -- prove their stability, worth, and limitations so that they were accepted by the community and integrated into the core distribution. The truth is dynamic modules work on just about every major platform and are trivial to write. Nearly anything that will allow you to execute a raw SQL command will let you attach a module. If you don't like modules, a custom version of SQLite or a SQLite DLL/so/dylib is trivial to compile. Even if your gee-wiz 3D GUI editor might not work out of the box, that's not the point. No matter how strongly anyone feels about this, there is a flat zero chance of it going into the core distribution until someone writes some code. Get the code working, be able to show that it works and has worth, then come talk. Even if you believe it must be in the core, you still need working code. So let's see some. We can sit here and argue and bicker until the cows come home, or someone can sit down, write some code and show/prove that the extension is (or isn't!) useful and productive. But until someone produces working code, all we have is a lot of hot air on **both** sides of the table. To quote the Internet Engineering Task Force: "We reject: Kings, Presidents, and voting. We believe in: rough consensus and running code." Show us the code. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposal for SQLite and non pure ASCII letters
Nico wrote: >> Number 1, the database is no longer portable. The only solution to >> this is to include the functionality in the core. >Yes but, there is no single Unicode collation. Collation is >language-specific, even when using Unicode. Thus you're asking that >SQLite3 have a plethora of built-in Unicode collations. I do understand the issues, but I think you misunderstand my intent. I'm not asking that SQLite3 have any built-in Unicode collations. Rather my point is that it would be of benefit if SQLite would have some built in mechanism for a rule-based collation. >> more general solution would be to design it around a sqlite_collation >> master table in the database. An application developer (not the SQLite >> team) would be responsible to define and populate their "user defined" >> collation. >It's more complex than you think. You need to keep Unicode >normalization forms in mind and you need to deal with decomposed >characters no matter what (since not all future additions to Unicode >will include pre-composed forms, and NFC is closed to new pre-composed >forms anyways), which means multi-codepoint sequences need to be >accounted for in the collation. You'd very quickly realize that it'd be >even simpler for you if SQLite3 just had built-in collations for all the >relevant languages. And once more SQLite3 would no longer be light. >Perhaps when built with ICU SQLite3 could make it trivial to load any of >those collations. I'm not suggesting that it is not a complex problem. SQLite does a very nice job of taking SQL statements and decomposing them into a form that a byte engine can process. I feel that the collations problem could also be addressed in this way with a rules based table along with some underlying data that a application developer (not the SQLite development team) could populate. If the "user defined" collation wouldn't work for a particular application, then it would not need to be used at all. If Richard feels that this is something that has no value, I'm more than willing to drop the whole idea. Regards, Noah CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] memory and db size
On Thu, 19 Mar 2009 08:14:52 -0700 (PDT), anjela patnaik wrote: > Hello all, > > I'm working on a GUI application using Tcl/Tk with > sqlite back end on windows. Now, multiple users will be > running this and all users must have a copy of the db > local to their PC or in a shared drive. At least that > is my understanding of how sqlite works. > > 1. What are my options if the db gets very large (say > 1 G) > and we don't want users to allocate that much disk > space and RAM? The same as for any other file or program, that is to say it is possible to make huge files or consume much memory with any other program. Think of bad awk scripts (with huge in-memory associative arrays) or Java or the tempfiles of a large sort. The usual system management strategies apply. > 2. How does sqlite allocate RAM when doing select > statements? what about when opening the db ? The allocation on open() is limited, most will be for the schema and perhaps page allocation maps. During SELECT / UPDATE / INSERT / DELETE the cache will grow. SQLite cache memory size = cache_size times page_size. There is quite some info on http://www.sqlite.org/ about architecture and optimization. > 3. Are there any ways to compress db files in disk (for > free) and then is sqlite able to still get to the data > easily? Donald already gave a good answer to that question. > Thank you! -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposal for SQLite and non pure ASCII letters
On Thu, Mar 19, 2009 at 09:52:55AM -0700, Noah Hart wrote: > I've been reading and thinking about this topic for a while, and would > like to add my thoughts. > > I realize that we don't "vote" on features, but I feel that this type > of idea has merit. > > It is true, that SQLite has user defined collations, and a extension > could be registered, but the problem with that is twofold: > > Number 1, the database is no longer portable. The only solution to > this is to include the functionality in the core. Yes but, there is no single Unicode collation. Collation is language-specific, even when using Unicode. Thus you're asking that SQLite3 have a plethora of built-in Unicode collations. And you'll probably want Unicode strings normalized for indexing and comparison. And... And SQLite3 would no longer be light. You can add Unicode collations using the user-defined collation function and whatever Unicode collation implementation you might have (e.g., ICU). > Number 2, your platform may not support the sqlite3_create_collation > interface. For example, Firefox now includes SQLite. Unfortunately, > while Firefox supports user defined functions, their implementation > does not support user defined collations. I'd call that a bug in Firefox. > Someone commented that the US lives in a 7-bit world. But that's not true. Even people who only read and write English can barely get by with just US-ASCII (if nothing else a lot of webpages would display as so many question marks if the browser didn't support anything other than US-ASCII). And there are plenty of multi-lingual people in the U.S. > This means that the other 6 billion people on the planet do not. There are lots of non-Unicode character and code sets. The rest of the world is not necessarily in a better position than the English-speaking world. Unicode is a solution, and the best one at that. > This creates a real problem for me. I am writing a foreign language > Firefox extension, and the issue of sorting is critical, since Firefox > uses Unicode sorting, which does not "sort" (based on my rules) > correctly. This means I have no way to correct the sorting, except > in the display routines. > > That being said, I would not limit this feature to 8bit locales. A 8-bit is so 1980s :) > more general solution would be to design it around a sqlite_collation > master table in the database. An application developer (not the SQLite > team) would be responsible to define and populate their "user defined" > collation. It's more complex than you think. You need to keep Unicode normalization forms in mind and you need to deal with decomposed characters no matter what (since not all future additions to Unicode will include pre-composed forms, and NFC is closed to new pre-composed forms anyways), which means multi-codepoint sequences need to be accounted for in the collation. You'd very quickly realize that it'd be even simpler for you if SQLite3 just had built-in collations for all the relevant languages. And once more SQLite3 would no longer be light. Perhaps when built with ICU SQLite3 could make it trivial to load any of those collations. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposal for SQLite and non pure ASCII letters
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mail.sqlite wrote: > 4. Your comments sound to me that you are living in a 7bit ASCII country, do > you? I am British but currently happen to live in the US, and have lived in every hemisphere whether you cut the earth horizontally or vertically. My high school had students of 74 different nationalities. In my career, every single piece of software I have worked on had to work at a *minimum* in the UK, USA (US English is different than UK English), Europe (at least France & Germany, often Italy) as well as in Japanese. Those various pieces of software have run on embedded environments, mini-computers, DOS, various flavours of Windows and a really wide selection of Unix systems. My repeated point throughout this is that I have no problem with taking shortcuts to get better performance/conserve memory but that you should be aware of how wrong the answers are. And if proposing it as core SQLite functionality then it really isn't fair unless the limitations are well documented since other programmers using it won't be able to tell how good the results are across all the different locales. As an analogy I could come up with something that speeds up SQLite floating point significantly but reduces accuracy of answers. But unless I documented how much the accuracy was reduced it would not be a good idea for other developers to use it. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAknCjWsACgkQmOOfHg372QS8zQCfVpXTHEWROfRsg4HiXzer4WK0 dz8AoKQhxwSpHMeV9xIR02XZaPlgQ/lr =u9Xi -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposal for SQLite and non pure ASCII letters
Igor Tandetnik writes: >This is not quite true. You say custom functions are supported: then you can >do ORDER BY sortkey(textField), >with a suitably defined sortkey() function >(see strxfrm, LCMapString). You can't however build indexes using >such a >function, something you can do with a collation. True, that is how I current accomplish it, but it is very inefficient, since indexes cannot be used, and a javascript function end up being called for the row by row comparison. >Sorting in many locales is not as simple as suitably ordering individual >Unicode characters. E.g. in German >phonebook order, letter ö (small o with >umlaut aka diaresis) sorts as if it were two letters oe, that is od < ö >< of. >In French, strings are compared ignoring diacritics first, then ties are >broken by considering diacritics >right-to-left. In Spanish traditional sort, >a pair ch sorts as if it were a single letter between c and d. Even >in >English, you would often want to sort co-op and coop, or cant and can't, in >such a way that they are kept >together. >Consider also things like combinig diacritics. Again I agree. In fact, with my application, I am not only dealing with UTF-16, but also there is the issue of combined letter forms. This is not a trivial topic, and I don't have a proposed solution. However, I feel this thread is worth pursuing. There are a lot of smart people on this list and who use SQLite. If the SQLite developers would be willing to open a discussion about embedding this functionality, maybe some brainstorming could come up with an acceptable solution. Regards, Noah CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] checking if a record exist or not
On Thu, Mar 19, 2009 at 12:01 PM, jose isaias cabrera wrote: > > Greetings... > > I am updating an sqlite db from another sqlite db and it is working fine, > unless the record does not exists. ie. > > "ATTACH db2 AS client; "; > > BEGIN; > INSERT OR REPLACE INTO LSOpenJobs > SELECT * FROM client.LSOpenJobs > WHERE login='x' AND XtraB > '2000'; > COMMIT; > > this works fine if the record already exists in the db getting updated. > However, if the record was deleted, inadvertively, from the db getting > updated, this will not work. > > Is there a way to insert the record back in the db getting updated if it is > not there? You should know that this record did exists previously in this > db, but it was deleted. José, you didn't communicate the above very well, because the above sentence makes no sense whatsoever. If you are still in a transaction and delete something that you didn't mean to, you can always bring it back by not committing the transaction. If you have already committed the transaction or were not in a transaction to begin with, then you could rely on a backup. Did you say you didn't have a backup? Well, you are out of luck then. How would the database know what to bring back? You could do one other thing -- not delete a record when a user "deletes" it... instead, mark it for deletion, and then never show it to the user. But, that is another story. > > This is the CREATE table schema: > CREATE TABLE LSOpenJobs > ( > id integer primary key, > ProjID integer, > login, > ..., > XtraB, > XtraC, > XtraD, > XtraE, > XtraF > ); > > Thanks for the help, > > josé > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ Sent from: Madison WI United States. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] memory and db size
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of anjela patnaik Sent: Thursday, March 19, 2009 11:15 AM To: sqlite-users@sqlite.org Subject: [sqlite] memory and db size Hello all, I'm working on a GUI application using Tcl/Tk with sqlite back end on windows. Now, multiple users will be running this and all users must have a copy of the db local to their PC or in a shared drive. At least that is my understanding of how sqlite works. 1. What are my options if the db gets very large (say > 1 G) and we don't want users to allocate that much disk space and RAM? 2. How does sqlite allocate RAM when doing select statements? what about when opening the db ? 3. Are there any ways to compress db files in disk (for free) and then is sqlite able to still get to the data easily? Thank you! === === Hi Anjela, I'm probably going to answer only part of your questions. Maybe others will reply or you will want to reformulate some questions. Regarding: "...all users must have a copy of the db local to their PC or in a shared drive." I guess it goes without saying that if each user keeps a local copy there will be no (automatic) communication of data among these separate databases. On the other hand, performance will be decreased, of course, if each user opens a common database file on a shared drive -- though this may well be acceptible in your application, and some operating systems have network file locking that has been called into question. Some folks have implemented sqlite server-like daemons or have written web-based applications so that only a local program needs to open the database. 1. Regarding: "What are my options if the db gets very large...? I'm not sure I understand. Are you asking just in general, or is there something relating to sqlite that you had in mind? The obvious answers that spring to mind may not be helpful, such as: -- buy more disk space -- it's rather cheap nowadays. -- Keep less data by periodically purging old data. -- Reevaluate your design to see if a portion of the data is better not being saved. -- (see #3 below) If you want sqlite to return an error and refuse to add too much data, perhaps you want to look at: PRAGMA max_page_count = N; 2. Sqlite and RAM.. Again, I may not understand the question. The answer on one level is, "malloc -- and the programmer can replace the ram allocation routines easily if needbe." Another type of answer would be to ask if you've reviewed: http://www.sqlite.org/pragma.html#modify PRAGMA cache_size; PRAGMA default_cache_size; and, while you're at it: PRAGMA page_size; Are you in an embedded environment (e.g. cellphone, mp3 player)? If so, there are some additional considerations. 3. Regarding: "Are there any ways to compress db files in disk (for free)..." I don't think you mentioned what operating system you're using, but: -- For Windows, you can simply set the COMPRESSED attribute on the database (or perhaps it's entire folder if you want to compress the journal files as well). This is "free" (once you've bought Window$) but will carry a little performance penalty MOST of the time, unless the reduction in disk movement with the compressed database outweighs the added CPU time. It's very easy to test, though. -- For *nix there may well be some similar "application transparent" disk compression arrangements -- I don't really know. -- There are various free program source code options for compressing a column before your write it to disk, then decompressing it when you retrieve it. You can do this in your application (what language??) or you can add a function right into sqlite. Indexed columns, of course, need to remain in the clear. Maybe this helps, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] checking if a record exist or not
jose isaias cabrera wrote: > I am updating an sqlite db from another sqlite db and it is working > fine, unless the record does not exists. ie. > > "ATTACH db2 AS client; "; > > BEGIN; > INSERT OR REPLACE INTO LSOpenJobs > SELECT * FROM client.LSOpenJobs > WHERE login='x' AND XtraB > '2000'; > COMMIT; > > this works fine if the record already exists in the db getting > updated. However, if the record was deleted, inadvertively, from the > db getting updated, this will not work. What do you mean, will not work? In precisely what way does the observed behavior differ from one you expect? > Is there a way to insert the record back in the db getting updated if > it is not there? That's precisely what the INSERT part of "INSERT OR REPLACE" would do. Or are you saying that there's a record that isn't currently in the main db (perhaps it existed some time ago, but was deleted) nor in the attached db, and you expect SQLite to somehow magically conjure it up out of thin air? I don't think real world works quite this way. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong ?
Olivier FAURAX wrote: > I tried with -1 and the length value of the string, but it didn't > help... Post a small complete compilable program that reproduces the problem. I don't see anything wrong in the code you've shown - hence, the problem is likely in the code you haven't. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposal for SQLite and non pure ASCII letters
Noah Hart wrote: > Number 2, your platform may not support the sqlite3_create_collation > interface. For example, Firefox now includes SQLite. Unfortunately, > while Firefox supports user defined functions, their implementation > does not support user defined collations. > > Someone commented that the US lives in a 7-bit world. > This means that the other 6 billion people on the planet do not. > > This creates a real problem for me. I am writing a foreign language > Firefox extension, and the issue of sorting is critical, since > Firefox uses Unicode sorting, which does not "sort" (based on my > rules) correctly. This means I have no way to correct the sorting, > except in the display routines. This is not quite true. You say custom functions are supported: then you can do ORDER BY sortkey(textField), with a suitably defined sortkey() function (see strxfrm, LCMapString). You can't however build indexes using such a function, something you can do with a collation. > That being said, I would not limit this feature to 8bit locales. A > more general solution would be to design it around a sqlite_collation > master table in the database. An application developer (not the > SQLite team) would be responsible to define and populate their "user > defined" collation. Sorting in many locales is not as simple as suitably ordering individual Unicode characters. E.g. in German phonebook order, letter ö (small o with umlaut aka diaresis) sorts as if it were two letters oe, that is od < ö < of. In French, strings are compared ignoring diacritics first, then ties are broken by considering diacritics right-to-left. In Spanish traditional sort, a pair ch sorts as if it were a single letter between c and d. Even in English, you would often want to sort co-op and coop, or cant and can't, in such a way that they are kept together. Consider also things like combinig diacritics. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong ?
I tried : rc = sqlite3_prepare_v2(metadata_db, "INSERT INTO metadata VALUES " "( ?1, ?2, ?3, ?4 ); COMMIT;", -1, &sthl, NULL); But this didn't helped. Anyway, thanks for your help. Olivier Hoover, Jeffrey a écrit : > is autocommit on? > try adding a manual commit. > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Olivier FAURAX > Sent: Thursday, March 19, 2009 10:56 AM > To: General Discussion of SQLite Database > Subject: [sqlite] What am I doing wrong ? > > Hello, > > I'm beginner and uses sqlite3. > I'm trying to record informations in a database, but this fails. > I have followed http://www.sqlite.org/c3ref/stmt.html > > rc = sqlite3_open(DB_FILENAME, &metadata_db); > rc = sqlite3_exec(metadata_db, > "CREATE TABLE IF NOT EXISTS metadata " > "( filename TEXT, key INT, data BLOB, length INT);", > NULL, NULL, > &error); > rc = sqlite3_prepare_v2(metadata_db, >"INSERT INTO metadata VALUES " >"( ?1, ?2, ?3, ?4 );", >0, &sthl, NULL); > rc = sqlite3_bind_text(sthl, 1, filename, 12, NULL); > rc = sqlite3_bind_int(sthl, 2, key); > rc = sqlite3_bind_blob(sthl, 3, data, data_length, NULL); > rc = sqlite3_bind_int(sthl, 4, data_length); > rc = sqlite3_step(sthl); > rc = sqlite3_finalize(sthl); > rc = sqlite3_close(metadata_db); > > I checked all "rc", they are all 0, except sqlite3_step returns 101 (aka > SQLITE_DONE) which is correct according to doc. > > The problem is that I obtain a correct database (with 4 fields), but > nothing in it. If I retry this code with an existing database, I still > have the table, but nothign in it. > > The only working case is when I add a dummy sqlite3_exec before the > close : this way, the statement is executed (I see data in the db), but > the dummy sqlite3_exec isn't executed (it's a simple INSERT). > This suggests that there is a kind of "flushing" that must be done, and > that it has been done to exec the new sqlite3_exec. > > Please let me know what I did wrong. > > Have a nice day -- Au bout de la chaîne de gestion des priorités Olivier FAURAX | +33 4 42 98 11 20 (243 en interne) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong ?
Igor Tandetnik a écrit : > Olivier FAURAX wrote: >> I'm beginner and uses sqlite3. >> I'm trying to record informations in a database, but this fails. >> I have followed http://www.sqlite.org/c3ref/stmt.html >> >> rc = sqlite3_open(DB_FILENAME, &metadata_db); >> rc = sqlite3_exec(metadata_db, >> "CREATE TABLE IF NOT EXISTS metadata " >> "( filename TEXT, key INT, data BLOB, length INT);", >> NULL, NULL, >> &error); >> rc = sqlite3_prepare_v2(metadata_db, >> "INSERT INTO metadata VALUES " >> "( ?1, ?2, ?3, ?4 );", >> 0, &sthl, NULL); > > The third parameter (where you pass 0) is the length of the string > pointed to by second parameter. So, you effectively pass an empty string > to sqlite3_prepare_v2. You must specify an actual string length, or -1 > to have the function calculate it automatically. Thanks for your answer. I tried with -1 and the length value of the string, but it didn't help... Have a nice day -- Olivier FAURAX ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] checking if a record exist or not
Greetings... I am updating an sqlite db from another sqlite db and it is working fine, unless the record does not exists. ie. "ATTACH db2 AS client; "; BEGIN; INSERT OR REPLACE INTO LSOpenJobs SELECT * FROM client.LSOpenJobs WHERE login='x' AND XtraB > '2000'; COMMIT; this works fine if the record already exists in the db getting updated. However, if the record was deleted, inadvertively, from the db getting updated, this will not work. Is there a way to insert the record back in the db getting updated if it is not there? You should know that this record did exists previously in this db, but it was deleted. This is the CREATE table schema: CREATE TABLE LSOpenJobs ( id integer primary key, ProjID integer, login, ..., XtraB, XtraC, XtraD, XtraE, XtraF ); Thanks for the help, josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposal for SQLite and non pure ASCII letters
I've been reading and thinking about this topic for a while, and would like to add my thoughts. I realize that we don't "vote" on features, but I feel that this type of idea has merit. It is true, that SQLite has user defined collations, and a extension could be registered, but the problem with that is twofold: Number 1, the database is no longer portable. The only solution to this is to include the functionality in the core. Number 2, your platform may not support the sqlite3_create_collation interface. For example, Firefox now includes SQLite. Unfortunately, while Firefox supports user defined functions, their implementation does not support user defined collations. Someone commented that the US lives in a 7-bit world. This means that the other 6 billion people on the planet do not. This creates a real problem for me. I am writing a foreign language Firefox extension, and the issue of sorting is critical, since Firefox uses Unicode sorting, which does not "sort" (based on my rules) correctly. This means I have no way to correct the sorting, except in the display routines. That being said, I would not limit this feature to 8bit locales. A more general solution would be to design it around a sqlite_collation master table in the database. An application developer (not the SQLite team) would be responsible to define and populate their "user defined" collation. True, if I call my "user defined" collation sequence "COLLATE_PN", someone else might use the same identifier. Their lookup table even be different than the "COLLATE_PN" I am using. However, I feel that is a deployment problem, not a development issue. It is unlikely that in a single SQLite database, we would have 2 user tables, with the same "user defined" collation identifier, but differing collation lookups. I would encourage the developers to at least consider such a feature, after all, they did recently add the built-in RTRIM collation. Regards, Noah Hart -Original Message- - Please, let us try to bring down the discussion to the intended solution - a simple way to define and use a "user defined" collating for 8 bit ASCII characters! As said before, the proposal doesn't rely on locales. If a user needs a german collating sequence with sort order for phone-book, dictionary or german upper case, it's up to the user to supply a simple 256 byte string with the wanted/needed sort order for that index. It could be beneficial to all users with the need for special sorting requirements and almost no impact to cpu cycles, even on small systems. There sould be many users with 8bit ASCII locales requirements that would love such an extension. CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong ?
Olivier FAURAX wrote: > I'm beginner and uses sqlite3. > I'm trying to record informations in a database, but this fails. > I have followed http://www.sqlite.org/c3ref/stmt.html > > rc = sqlite3_open(DB_FILENAME, &metadata_db); > rc = sqlite3_exec(metadata_db, > "CREATE TABLE IF NOT EXISTS metadata " > "( filename TEXT, key INT, data BLOB, length INT);", > NULL, NULL, > &error); > rc = sqlite3_prepare_v2(metadata_db, > "INSERT INTO metadata VALUES " > "( ?1, ?2, ?3, ?4 );", > 0, &sthl, NULL); The third parameter (where you pass 0) is the length of the string pointed to by second parameter. So, you effectively pass an empty string to sqlite3_prepare_v2. You must specify an actual string length, or -1 to have the function calculate it automatically. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] memory and db size
Hello all, I'm working on a GUI application using Tcl/Tk with sqlite back end on windows. Now, multiple users will be running this and all users must have a copy of the db local to their PC or in a shared drive. At least that is my understanding of how sqlite works. 1. What are my options if the db gets very large (say > 1 G) and we don't want users to allocate that much disk space and RAM? 2. How does sqlite allocate RAM when doing select statements? what about when opening the db ? 3. Are there any ways to compress db files in disk (for free) and then is sqlite able to still get to the data easily? Thank you! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong ?
is autocommit on? try adding a manual commit. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Olivier FAURAX Sent: Thursday, March 19, 2009 10:56 AM To: General Discussion of SQLite Database Subject: [sqlite] What am I doing wrong ? Hello, I'm beginner and uses sqlite3. I'm trying to record informations in a database, but this fails. I have followed http://www.sqlite.org/c3ref/stmt.html rc = sqlite3_open(DB_FILENAME, &metadata_db); rc = sqlite3_exec(metadata_db, "CREATE TABLE IF NOT EXISTS metadata " "( filename TEXT, key INT, data BLOB, length INT);", NULL, NULL, &error); rc = sqlite3_prepare_v2(metadata_db, "INSERT INTO metadata VALUES " "( ?1, ?2, ?3, ?4 );", 0, &sthl, NULL); rc = sqlite3_bind_text(sthl, 1, filename, 12, NULL); rc = sqlite3_bind_int(sthl, 2, key); rc = sqlite3_bind_blob(sthl, 3, data, data_length, NULL); rc = sqlite3_bind_int(sthl, 4, data_length); rc = sqlite3_step(sthl); rc = sqlite3_finalize(sthl); rc = sqlite3_close(metadata_db); I checked all "rc", they are all 0, except sqlite3_step returns 101 (aka SQLITE_DONE) which is correct according to doc. The problem is that I obtain a correct database (with 4 fields), but nothing in it. If I retry this code with an existing database, I still have the table, but nothign in it. The only working case is when I add a dummy sqlite3_exec before the close : this way, the statement is executed (I see data in the db), but the dummy sqlite3_exec isn't executed (it's a simple INSERT). This suggests that there is a kind of "flushing" that must be done, and that it has been done to exec the new sqlite3_exec. Please let me know what I did wrong. Have a nice day -- Olivier FAURAX ___ 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] What am I doing wrong ?
Hello, I'm beginner and uses sqlite3. I'm trying to record informations in a database, but this fails. I have followed http://www.sqlite.org/c3ref/stmt.html rc = sqlite3_open(DB_FILENAME, &metadata_db); rc = sqlite3_exec(metadata_db, "CREATE TABLE IF NOT EXISTS metadata " "( filename TEXT, key INT, data BLOB, length INT);", NULL, NULL, &error); rc = sqlite3_prepare_v2(metadata_db, "INSERT INTO metadata VALUES " "( ?1, ?2, ?3, ?4 );", 0, &sthl, NULL); rc = sqlite3_bind_text(sthl, 1, filename, 12, NULL); rc = sqlite3_bind_int(sthl, 2, key); rc = sqlite3_bind_blob(sthl, 3, data, data_length, NULL); rc = sqlite3_bind_int(sthl, 4, data_length); rc = sqlite3_step(sthl); rc = sqlite3_finalize(sthl); rc = sqlite3_close(metadata_db); I checked all "rc", they are all 0, except sqlite3_step returns 101 (aka SQLITE_DONE) which is correct according to doc. The problem is that I obtain a correct database (with 4 fields), but nothing in it. If I retry this code with an existing database, I still have the table, but nothign in it. The only working case is when I add a dummy sqlite3_exec before the close : this way, the statement is executed (I see data in the db), but the dummy sqlite3_exec isn't executed (it's a simple INSERT). This suggests that there is a kind of "flushing" that must be done, and that it has been done to exec the new sqlite3_exec. Please let me know what I did wrong. Have a nice day -- Olivier FAURAX ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposal for SQLite and non pure ASCII letters
Roger: 1. Sorry, I don’t want to screw up somebody with my post 2. It was not my proposal 3. Thank you for the information that I may program/implement whatever I want 4. Your comments sound to me that you are living in a 7bit ASCII country, do you? George > -Ursprüngliche Nachricht- > Von: Roger Binns > An: General Discussion of SQLite Database > Datum: 18-03-2009 19:40 > Betreff: Re: [sqlite] Proposal for SQLite and non pure ASCII letters > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Mail.sqlite wrote: > > - Please, let us try to bring down the discussion to the intended solution > > - a simple way to define and use a "user defined" collating for 8 bit ASCII > > characters! > > You don't need anyone's permission - go ahead and implement (or pay > someone to implement) whatever you want. Your proposal can be > implemented as a SQLite extension - there is no need to modify SQLite > itself. > > On the other hand if you want it to become a standard part of SQLite > then it isn't unreasonable to expect some documentation as to how > accurate and useful it actually is. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAknBP/oACgkQmOOfHg372QSwaACeNXBtbCJsm/RvKTBs70HdOXYI > R84AoMo4qgA6oDwfBzdobQLwW0af1z/p > =/CRf > -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] is primary key already indexed ?
On Thu, 19 Mar 2009 06:38:33 -0700 (PDT), baxy77bax wrote: >:-D > >one more question : > >if i create primary key on my table and then import the data (assuming i >used transactions) will my import be faster or slower vs the case where i >import the data first and then create key (foreign key ??) and index the >table. That's not a foreign key, it would just be a unique index. It's hard to guess what would be faster, The best way to find out would be to test both cases, you can do that yourself. Make sure you import in sort order of primary key and use large transactions (BEGIN / COMMIT). >the point is that i need my import to be faster. i have 3890660 rows to >import and row van be up to 50M large, so it's taking a pretty long time Optimize for your use case: PRAGMA page_size PRAGMA default_cache_size and cache_size PRAGMA synchronous=off (only during loading) By the way, INTEGER PRIMARY KEY is faster than a TEXT column (VARCHAR is also TEXT) as primary key. >thnx -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] httpd server ???
It is not an open source project but you are welcome to access to the source code.and notes. The intent of the project is to provide a highly efficient application server for AJAX support of browser-based IT networks. To that end the design criteria were: o Minimize process and thread creation and destruction. o Avoid inter process communication o Minimize buffer shadowing on network traffic o Provide a highly efficient generator of HTML pages from data using SQL. o Provide generation of charts from datab using SQL o Maximize traffic by using compression where it can help. o Implement stored procedure capability for the database o Provide a secure RPC mechanism for XMLHttpRequest traffic from browsers. A simple server page generation language which uses a compiled byte code format with resolved addresses efficiently creates HTML pages from templates and SQL. Embedded Sqlite provides efficiewnt database access from within the server process and the use of sendfiletTransmitFile network API minimizes buffer shadowing.. The end result is the ability of processing an AJAX-style transaction involving database access on a slow server and client machine in 20-30mS. That elapsed time includes network latency and the time spent processing Javascript in the client browswer. The HTTP primitives are all custom coded from first principles to speed parsing and general processing. As a simple HTTP server this program serves HTML pages and CGI processes with minimum overheads. Should you have an interest in examining or using all or part of this software contact me at jo...@viacognis.com and I can make a URLavailable. JS dcharno wrote: > John Stanton wrote: > >> I have an HTTP server wj\hich embeds Sqlite as well as a custom page >> generation language, and compiler and a remote procedure call interface >> for AJAX functionality and Javascript as an embedded scripting >> language. It runs on Unix/Linux and conditionally compiles for >> Windows. It uses a sinngle process with a pool of threads to handle >> multiple users. >> >> The RPC mechanism protects it from SQL injection attack. It also >> implements GI and SSL plus data compression.. >> >> Embedding the database code in the application server by using Sqlite >> gives an efficient application environment where there is not a >> proliferation of processes and process/thread generation and >> destruction. Performance is good.. >> >> Source is all in simple C. >> > > Sounds interesting. Is this an open source project hosted somewhere? > ___ > 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] is primary key already indexed ?
nop no i ment up to 50 MB an these are tough to import(i just wanted to mention that because on these records import is slownig down and it is not so fast any more example if i import one set of data that is small and the other that is large the overall time is T1= t1 +t2 but if i mix those data T2 > T1), db is 32 GB in total size D. Richard Hipp wrote: > > > On Mar 19, 2009, at 9:38 AM, baxy77bax wrote: > >> >> >> :-D >> >> one more question : >> >> if i create primary key on my table and then import the data >> (assuming i >> used transactions) will my import be faster or slower vs the case >> where i >> import the data first and then create key (foreign key ??) and index >> the >> table. >> >> the point is that i need my import to be faster. i have 3890660 rows >> to >> import and row van be up to 50M large, so it's taking a pretty long >> time > > > Do I understand you correctly? 3.8 million rows at 50MB per row comes > to almost 200 TB. > > The maximum size of an SQLite database with a 32KiB page size is 64 > TiB. The maximum size of a file on ext3 is 2 TiB and 16 TiB on ext4. > On NTFS, 2 TiB is the maximum size of an entire volume. > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/is-primary-key-already-indexed---tp22596778p22600912.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is primary key already indexed ?
On Mar 19, 2009, at 9:38 AM, baxy77bax wrote: > > > :-D > > one more question : > > if i create primary key on my table and then import the data > (assuming i > used transactions) will my import be faster or slower vs the case > where i > import the data first and then create key (foreign key ??) and index > the > table. > > the point is that i need my import to be faster. i have 3890660 rows > to > import and row van be up to 50M large, so it's taking a pretty long > time Do I understand you correctly? 3.8 million rows at 50MB per row comes to almost 200 TB. The maximum size of an SQLite database with a 32KiB page size is 64 TiB. The maximum size of a file on ext3 is 2 TiB and 16 TiB on ext4. On NTFS, 2 TiB is the maximum size of an entire volume. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is primary key already indexed ?
:-D one more question : if i create primary key on my table and then import the data (assuming i used transactions) will my import be faster or slower vs the case where i import the data first and then create key (foreign key ??) and index the table. the point is that i need my import to be faster. i have 3890660 rows to import and row van be up to 50M large, so it's taking a pretty long time thnx Kees Nuyt wrote: > > On Thu, 19 Mar 2009 02:54:34 -0700 (PDT), baxy77bax > wrote: > >> >>hi my question is : if i create table that contains primary key like; >> >>create table TEST (field1 varchar not null primary key); >> >>do i need to create index on it or not? > > No, the primary key implies an index will be created > automatically. > >>and is it better to create table with a primary key and then import data in >>it or create table without a key , import data and then just create index on >>the table. (which is faster- or should i ask which is the fastest way to >>import data in the table?) > > You can't add a primary key afterwards. > > The best way is to create the table with the primary key and > then insert records sorted by the primary key field. > For speed you also have to wrap the INSERT statements in a > transaction (BEGIN; INSERT; . INSERT; COMMIT;). > > If you want to use the .import command of the SQLite command > line tool, you would sort the import file beforehand on the > primary key column. > >>then if i create proper table , is it advisable to order data by >>column with primary key on it or data with index on it. > > In your case (with a primary key on a text column) that > won't make a difference, I think. > >>thank you > -- > ( Kees Nuyt > ) > c[_] > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > :-D:-D -- View this message in context: http://www.nabble.com/is-primary-key-already-indexed---tp22596778p22600402.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is primary key already indexed ?
:-D one more question : if i create primary key on my table and then import the data (assuming i used transactions) will my import be faster or slower vs the case where i import the data first and then create key (foreign key ??) and index the table. the point is that i need my import to be faster. i have 3890660 rows to import and row van be up to 50M large, so it's taking a pretty long time thnx Kees Nuyt wrote: > > On Thu, 19 Mar 2009 02:54:34 -0700 (PDT), baxy77bax > wrote: > >> >>hi my question is : if i create table that contains primary key like; >> >>create table TEST (field1 varchar not null primary key); >> >>do i need to create index on it or not? > > No, the primary key implies an index will be created > automatically. > >>and is it better to create table with a primary key and then import data in >>it or create table without a key , import data and then just create index on >>the table. (which is faster- or should i ask which is the fastest way to >>import data in the table?) > > You can't add a primary key afterwards. > > The best way is to create the table with the primary key and > then insert records sorted by the primary key field. > For speed you also have to wrap the INSERT statements in a > transaction (BEGIN; INSERT; . INSERT; COMMIT;). > > If you want to use the .import command of the SQLite command > line tool, you would sort the import file beforehand on the > primary key column. > >>then if i create proper table , is it advisable to order data by >>column with primary key on it or data with index on it. > > In your case (with a primary key on a text column) that > won't make a difference, I think. > >>thank you > -- > ( Kees Nuyt > ) > c[_] > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > :-D:-D -- View this message in context: http://www.nabble.com/is-primary-key-already-indexed---tp22596778p22600392.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any available free SQLite Database Comparer/Diff Tool?
On Thu, 19 Mar 2009 19:06:52 +0800, Ev wrote: >Any available free SQLite Database Comparer/Diff Tool? At least one tool is listed on http://www.sqlite.org/cvstrac/wiki?p=ManagementTools And of course you can diff a dump: # sqlite3 test_38.db3 .dump >t38 # sqlite3 test_41.db3 .dump >t41 # diff t38 t41 >Thanks, >Ev -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question
In the first query the (select id from tbl2 where name='Joe') is referred to as a SUBQUERY. The second query is uses a JOIN because you are joining together data from different rows. -Jeff -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dermot Sent: Wednesday, March 18, 2009 12:05 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Newbie question 2009/3/18 Hoover, Jeffrey : > > Assumming this is only one row in tbl2 where name='Joe'... > > this should work: > SELECT * FROM tbl1 > WHERE description='someval' > AND foreign_key_id=(select id from tbl2 where name='Joe'); > > this is better: > select tbl1.* from tbl1, tbl2 > where tbl1.description='someval' > AND tbl2.name='Joe' and tbl2.id=tbl1. foreign_key_id; > > if there may be many rows in tbl2 where name =- 'Joe' then > SELECT * FROM tbl1 > WHERE description='someval' > AND foreign_key_id in (select id from tbl2 where name='Joe'); Both of the top 2 worked. Thank you. I'll try and stick to the less ambiguous form and bear in mind all the comments about single quotes. A bit more information though. Is there a term for that type of SELECT statement? Thanx, Dp. ___ 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] FW: [Bug 482300] Firefox does not display back buttons or allow bookmarks
On Mar 18, 2009, at 4:18 PM, Lessinger, Robert A. wrote: > Hello, > > I’m hoping that someone will be able to assist me in figuring out > why Firefox consistently fails to operate properly for our Macintosh > users here at St. Cloud State University. I’ve created a bug report > with bugzilla, but am now being referred to your organization. The > bug report # is 482300 (as indicated in the subject line of this > message as well). > > Basically, we have network homes for our lab users. We employ SMB > homes in an Active Directory environment. To correctly authenticate > our clients systems and attach to users’ file space, we also employ > Thursby’s ADmit Mac on each lab machine. We’ve tried several > approaches to resolving this issue, but it comes down to a failure > of Firefox to close out the “places.sqlite” and the other “places.*” > files that are stored in the user’s Firefox profile folder. > > This problem has been plaguing us for some time now and I am now > concerned that I will be asked to make a recommendation regarding > continued support of this app on our campus. It continues to work > perfectly normally for Windows lab users as well as users who make > use of non-lab machines (including myself). > I have been following the bug report. You recognize, I hope, that this is really a bug in either your SMB server or in Thursby's ADmit Mac software - one of these or perhaps the combination appears to be failing to follow posix filesystem semantics. The best we can do is figure out a way to work around the problem. Since we do not have an SMB network nor Thursby's ADmit Mac software, we are unable to reproduce the problem in order to figure out what is really going on. We are completely dependent upon your analysis. I do not understand what you mean when you say: "a failure of Firefox to close out the 'places.sqlite' and other 'places.*' files". What does "close out a file" mean to you? Please tell us precisely what is happening to those files and what you were expecting to happen. Please also let us know the complete list of "places.*" files that you are talking about and where they are located. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
On 3/19/09 12:20 PM, "erfon haubenstock" wrote: > > EEESH, i installed macports and when i try to port install i get an error > that says the error "port: command not found" > > maybe this has something to do with it? my > $PATH=usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:/usr/bin:/bin:/usr/sb > in:/sbin:/usr/local/bin:/usr/X11/bin > One common pitfall here is that you must open a new shell for the port command to be recognised (or re-export the correct PATH). This seems likely as the default location for the port command is /opt/local/bin/port, which is not in your PATH. If it's there, it'll probably be as easy as quitting and relaunching terminal. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
EEESH, i installed macports and when i try to port install i get an error that says the error "port: command not found" maybe this has something to do with it? my $PATH=usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/X11/bin Jean-Denis Muys-2 wrote: > > > > > On 3/19/09 11:07 AM, "erfon haubenstock" wrote: > >> >> Hello Jean-Denis! I did it manually in terminal. I'm not familiar with >> macports or finks. >> >> Here's the exact code i used: >> >> $ curl http://www.sqlite.org/sqlite-3.6.11.tar.gz | tar zx >> $ cd sqlite-3.6.11 >> $ ./configure --prefix=/usr/local >> $ make >> $ sudo make install >> > > Then, without trying to replicate this, I suspect you have an issue with > your PATH. > > In my path, /usr/bin precedes /usr/local/bin. > > Echo $PATH and which sqlite3 are your friends. > > Handling this kind of issues transparently is one of the benefits of > package > distribution facilities such as MacPorts and Fink. I personally use > MacPorts. See http://www.macports.org/. It has SQLite 3.6.11 as an > available > port. > > I hope that helps. > > Jean-Denis > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/sqlite3---version-showing-the-wrong-version-tp22596369p22598170.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FW: [Bug 482300] Firefox does not display back buttons or allow bookmarks
Hello, I’m hoping that someone will be able to assist me in figuring out why Firefox consistently fails to operate properly for our Macintosh users here at St. Cloud State University. I’ve created a bug report with bugzilla, but am now being referred to your organization. The bug report # is 482300 (as indicated in the subject line of this message as well). Basically, we have network homes for our lab users. We employ SMB homes in an Active Directory environment. To correctly authenticate our clients systems and attach to users’ file space, we also employ Thursby’s ADmit Mac on each lab machine. We’ve tried several approaches to resolving this issue, but it comes down to a failure of Firefox to close out the “places.sqlite” and the other “places.*” files that are stored in the user’s Firefox profile folder. This problem has been plaguing us for some time now and I am now concerned that I will be asked to make a recommendation regarding continued support of this app on our campus. It continues to work perfectly normally for Windows lab users as well as users who make use of non-lab machines (including myself). --Bob. Robert Lessinger Open Computer Lab Support Specialist 720 4th Ave. South Miller Center 204 St. Cloud, MN 56301 (320) 308-6620 --Vivere est cogitare! -- Forwarded Message From: "bugzilla-dae...@mozilla.org" Date: Wed, 18 Mar 2009 14:55:50 -0500 To: Robert Lessinger Subject: [Bug 482300] Firefox does not display back buttons or allow bookmarks Do not reply to this email. You can add comments to this bug at https://bugzilla.mozilla.org/show_bug.cgi?id=482300 --- Comment #6 from Shawn Wilsher :sdwilsh 2009-03-18 12:55:43 PDT --- You best bet is probably to send a message to sqlite-users@sqlite.org explaining your problem. That's all I'll be able to do, and any questions they ask I'll just have to ask you, so we might as well cut out the middleman. -- Configure bugmail: https://bugzilla.mozilla.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You reported the bug. -- End of Forwarded Message ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Any available free SQLite Database Comparer/Diff Tool?
Any available free SQLite Database Comparer/Diff Tool? Thanks, Ev ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is primary key already indexed ?
On Thu, 19 Mar 2009 02:54:34 -0700 (PDT), baxy77bax wrote: > >hi my question is : if i create table that contains primary key like; > >create table TEST (field1 varchar not null primary key); > >do i need to create index on it or not? No, the primary key implies an index will be created automatically. >and is it better to create table with a primary key and then import data in >it or create table without a key , import data and then just create index on >the table. (which is faster- or should i ask which is the fastest way to >import data in the table?) You can't add a primary key afterwards. The best way is to create the table with the primary key and then insert records sorted by the primary key field. For speed you also have to wrap the INSERT statements in a transaction (BEGIN; INSERT; . INSERT; COMMIT;). If you want to use the .import command of the SQLite command line tool, you would sort the import file beforehand on the primary key column. >then if i create proper table , is it advisable to order data by >column with primary key on it or data with index on it. In your case (with a primary key on a text column) that won't make a difference, I think. >thank you -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema syntax error
On Thu, 19 Mar 2009 03:39:11 +0200, Tristan Seligmann wrote: >Divmod Axiom[1] is a Python ORM built on SQLite; one of the book >keeping tables it creates in the database has a column named >"indexed", which became a reserved word around SQLite 3.6.4 (?). The >"obvious" fix for this problem is to simply quote the column name >using "", but the problem is that it is now impossible to load older >databases which didn't have the column created with the name quoted: > >Error: malformed database schema (axiom_attributes) - near "indexed": >syntax error > >What sort of migration path exists for converting / fixing these old >databases? Ideally there would be some mechanism that does not require >reinstalling an older version of SQLite. Digging in the mailing list archives, I found this: == To: sqlite-users@sqlite.org Subject: Re: [sqlite] FTS and upgrades From: d...@hwaci.com Date: Tue, 10 Jul 2007 22:26:21 + I probably shouldn't tell you this, but There is a pragma: PRAGMA writable_schema=ON; Which when enabled allows you to UPDATE or DELETE against the sqlite_master table. == Using this PRAGMA, you can UPDATE the sql column in the sqlite_master table. Of course this is undocumented and unsupported, and you risk corrupting your databases. Backups and rigorous testing required. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is primary key already indexed ?
Hi,. baxy77bax schrieb: > hi my question is : if i create table that contains primary key like; > > create table TEST (field1 varchar not null primary key); > > do i need to create index on it or not? > Yes, the primary key is indexed > and is it better to create table with a primary key and then import data in > it or create table without a key , import data and then just create index on > the table. (which is faster- or should i ask which is the fastest way to > import data in the table?) > > then if i create proper table , is it advisable to order data by column with > primary key on it or data with index on it > My experience says that this does not matter much. However, you could try and see for yourself. If you have a Primary Key which is a single integer, you should declare the column as "integer primary key" (see http://www.sqlite.org/lang_createtable.html#rowid). > thank you > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
On 3/19/09 11:07 AM, "erfon haubenstock" wrote: > > Hello Jean-Denis! I did it manually in terminal. I'm not familiar with > macports or finks. > > Here's the exact code i used: > > $ curl http://www.sqlite.org/sqlite-3.6.11.tar.gz | tar zx > $ cd sqlite-3.6.11 > $ ./configure --prefix=/usr/local > $ make > $ sudo make install > Then, without trying to replicate this, I suspect you have an issue with your PATH. In my path, /usr/bin precedes /usr/local/bin. Echo $PATH and which sqlite3 are your friends. Handling this kind of issues transparently is one of the benefits of package distribution facilities such as MacPorts and Fink. I personally use MacPorts. See http://www.macports.org/. It has SQLite 3.6.11 as an available port. I hope that helps. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
Hello Jean-Denis! I did it manually in terminal. I'm not familiar with macports or finks. Here's the exact code i used: $ curl http://www.sqlite.org/sqlite-3.6.11.tar.gz | tar zx $ cd sqlite-3.6.11 $ ./configure --prefix=/usr/local $ make $ sudo make install thank you :-) erfon Jean-Denis Muys-2 wrote: > > On 3/19/09 10:28 AM, "erfon haubenstock" wrote: > >> >> This is really strange. I'm using a mac with leopard which comes with >> version 3.4.0 installed. I've installed 3.6.11, and it installs >> successfully as far as I can tell, but then when I check the version with >> sqlite3 --version in terminal, the version is shows that the installed >> version is STILL 3.4.0. >> >> Please help, this is driving me insane! > > Maybe you could tell us a bit more (see > http://catb.org/~esr/faqs/smart-questions.html): > > - how did you install v 3.6.11? Using MacPorts, or Finks? Or manually? > - what did you try to understand and fix the issue? > - did you STFW? > - what is the output of 'which sqlite3'? > - did you check your PATH? > > Eager to help... > > Jean-Denis > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/sqlite3---version-showing-the-wrong-version-tp22596369p22596952.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 --version showing the wrong version
On 3/19/09 10:28 AM, "erfon haubenstock" wrote: > > This is really strange. I'm using a mac with leopard which comes with > version 3.4.0 installed. I've installed 3.6.11, and it installs > successfully as far as I can tell, but then when I check the version with > sqlite3 --version in terminal, the version is shows that the installed > version is STILL 3.4.0. > > Please help, this is driving me insane! Maybe you could tell us a bit more (see http://catb.org/~esr/faqs/smart-questions.html): - how did you install v 3.6.11? Using MacPorts, or Finks? Or manually? - what did you try to understand and fix the issue? - did you STFW? - what is the output of 'which sqlite3'? - did you check your PATH? Eager to help... Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] is primary key already indexed ?
hi my question is : if i create table that contains primary key like; create table TEST (field1 varchar not null primary key); do i need to create index on it or not? and is it better to create table with a primary key and then import data in it or create table without a key , import data and then just create index on the table. (which is faster- or should i ask which is the fastest way to import data in the table?) then if i create proper table , is it advisable to order data by column with primary key on it or data with index on it thank you -- View this message in context: http://www.nabble.com/is-primary-key-already-indexed---tp22596778p22596778.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 --version showing the wrong version
This is really strange. I'm using a mac with leopard which comes with version 3.4.0 installed. I've installed 3.6.11, and it installs successfully as far as I can tell, but then when I check the version with sqlite3 --version in terminal, the version is shows that the installed version is STILL 3.4.0. Please help, this is driving me insane! -- View this message in context: http://www.nabble.com/sqlite3---version-showing-the-wrong-version-tp22596369p22596369.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users