Re: [sqlite] Uncached performance
Hello Donald, DG 1) Might there be further performance gains by placing the blobs in a DG separate table? DG E.g. DG CREATE TABLE myBlobs ( DG idINTEGER PRIMARY KEY REFERENCES global (id), DG value BLOB DG ); DG Then (if you haven't rebuilt a new db, and perhaps only once ever) run DG VACUUM and ANALYZE. This is how I do it. Seems to give me the best performance. I sometimes put them in their own DB file and then attach this file too. I have DB's with 30+ gigs of blobs in them. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Without ROWID and Autoincrement question
Hello General, I was going to say I thought the docs were ambiguous but, on my 3rd reading I see they aren't. Still, I think this statement could be re-written to reduce confusion (at least mine). https://www.sqlite.org/autoinc.html Because AUTOINCREMENT keyword changes the behavior of the ROWID selection algorithm, AUTOINCREMENT is not allowed on WITHOUT ROWID tables or on any table column other than INTEGER PRIMARY KEY. Any attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column other than the INTEGER PRIMARY KEY column results in an error.-- I think this comment would be clearer if the without rowid part was pulled out and made absolute. The AUTOINCREMENT keyword is not allowed on WITHOUT ROWID tables. Because the AUTOINCREMENT keyword changes the behavior of the ROWID selection algorithm, Any attempt to use AUTOINCREMENT on a column other than the INTEGER PRIMARY KEY column results in an error.-- Best regards, Conrad mailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performing a SELECT on an enormous database...
Hello Ross, Is H11 indexed? 15 minutes suggests to me that it's doing a linear scan and you need an index. I have databases that big and performance is pretty decent most of the time. I'm sure the boys here will ask you for a schema probably an explain on the query. C Friday, October 24, 2014, 7:09:05 PM, you wrote: RA Hi guys, RA I'm currently working with a pretty gigantic database (116 Gb at the RA moment, and growing). Performing a simple SELECT routine with one filter RA takes between 7 and 15 minutes, which is starting to become a problem. The RA command I'm using is the following: RA SELECT NVERTS FROM ToricCY WHERE H11=2; RA Pretty basic. I just want the NVERTS column of the table ToricCY where RA another column labeled H11 is 2, nothing fancy. Because of the huge size of RA the database, I would expect it to take a while, but I was wondering if you RA guys have any recommendations on how to speed it up (if possible). RA Also, because this problem will only get worse as the database grows, I'm RA looking for alternative approaches to storing this data. Does anyone have RA experience working with databases this big? RA Thanks! RA Best, RA Ross RA ___ RA sqlite-users mailing list RA sqlite-users@sqlite.org RA http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please fix the EBCDIC support
Hello John, I feed everything to Sqlite in UTF-8. If it's coming from Windows, that means I have to do a UTF-16 to UTF-8 conversion. I know Sqlite has UTF-16 support but, I want things to be consistent across all OS's I work with. The less I have to think about things like this, the better. C Thursday, October 16, 2014, 10:49:52 AM, you wrote: JM On Thu, Oct 16, 2014 at 9:09 AM, Richard Hipp d...@sqlite.org wrote: On Thu, Oct 16, 2014 at 9:53 AM, John McKown john.archie.mck...@gmail.com wrote: On Wed, Sep 24, 2014 at 9:46 PM, Richard Hipp d...@sqlite.org wrote: Please try the latest version of SQLite on trunk to see if that works better. Specifically, apply the patch at http://www.sqlite.org/src/vpatch?from=b2c89ef49cd1to=ef30e0352b3d -- D. Richard Hipp d...@sqlite.org Unfortunately, the ASCII vs. EBCDIC issued _does_ make it impossible to share a single SQLite data base file between z/OS and other ASCII-based SQLite systems. If you store content in EBCDIC, it is retrieved in EBCDIC and if you store content in ASCII it is retrieved in ASCII, regardless of which platform you do the storing and retrieving on. I wonder if this is the right approach. Perhaps the zOS patch should be amended to simply transform EBCDIC-ASCII on input and ASCII-EBCDIC on output. JM Hum, I'll need to look more closely at the code to see where this JM would need to be implemented. As I indicated previously, making it JM work on z/OS was so easy that I didn't need to really look closely at JM the code itself. I basically compiled and it worked. And this was my JM first attempt to port something to the z/OS environment, so I was not JM very knowledgeable about it. I am somewhat more knowledgeable today JM due to some other porting work that was somewhat more involved. I JM guess that instead of saying ASCII-EBCDIC, I need to say more JM exactly ISO8859-1-IBM-1047 since there are many ASCII' and EBCDIC JM code pages. Or does SQLite use UTF-8 internally? Hum, something else JM to look into. I am not really that familiar with SQLite's internals. JM Thanks for the thoughts. I appreciate your help. If I have more JM questions, I guess that I would go over to the sqlite-dev forum. I haven't looked closely enough at the code to see if the big-endian (z/OS) vs. little-endian (Intel) storage of integers would also be a problem. We do cross-platform database portability tests between x86, sparc, and PPC, to verify that big-endian vs little-endian is not a factor. JM Thanks for telling me that. I won't worry about it again. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UTF support
Hello J,
string_tsTest;
int nLengthNeeded = WideCharToMultiByte(CP_UTF8, 0, pszWide,nLength, 0,
0, 0, 0);
if( !nLengthNeeded )
{
ASSERT(0);
return(E_ABORT);
}
sTest.resize(nLengthNeeded + 16);
nLength = WideCharToMultiByte(CP_UTF8, 0, pszWide,nLength,
reinterpret_castchar*(sTest[0]),(uint32_t)sTest.size(),0, 0);
sTest[nLength] = 0;
ASSERT(!strcmp(sTest.c_str(),(char*)(*this)));
Is what I used to use to convert from UTF-16 to UTF-8 in Windows.
There are similar functions for converting in the opposite direction.
Internally my program is 100% UTF8. I do translations to UTF-16 right
at the point I display the strings in Windows.
This code is actually some test code I use today to compare the
conversions I do manually to what Windows generates. In debug mode, it
does two conversions and compares the two.
Tuesday, October 7, 2014, 8:59:07 AM, you wrote:
JD On Tue, Oct 7, 2014 at 5:39 AM, Richard Hipp d...@sqlite.org wrote:
On Tue, Oct 7, 2014 at 12:06 AM, J Decker d3c...@gmail.com wrote:
I saw a few things go by about unicode... and understand that it should
just work to store the data as characters...
I'm getting a unrecognized token... and think this page isn't right...
I was playing with greek translation of 'mary had a little lamb'
I ran the following script through the sqlite3 command-line shell and it
works fine:
CREATE TABLE option4_values(option_id, string, segment);
REPLACE INTO option4_values(`option_id`,`string`,`segment`)
VALUES('8b377a68-4358-11e4-ace4-3085a9903449','Μαίρη είχε ένα μικρό
αρνί',0);
SELECT * FROM option4_values;
Hmm... wonder what it's getting
I suggest that the problem is in your programming language, or in the
wrapper that links your programming language to SQLite, not in SQLite
itself. Can you tell us what programming language and what operating
system you are using?
C, visual studio 2012 build, windows.
JD built with UNICODE enabled... instead of multi-byte character set
JD it could be my conversion routine... I'm using wcstombs_s with _MSC_VER
JD set... before it was just faililng, because wcstombs_s doesn't convert
JD anything with a high bit set... so I added a handler to replace it with a
JD utf-8 16 bit character encode (expands to 3 bytes as described here
JD http://en.wikipedia.org/wiki/UTF-8#Description )
JD if( err == 42 )
JD {
JD (*ch++) = 0xE0 | ((unsigned char*)wch)[1] 4;
JD (*ch++) = 0x80 | ( ( ((unsigned char*)wch)[1] 0xF ) 2 ) | ( (
JD ((unsigned char*)wch)[0] ) 6 );
JD (*ch++) = 0x80 | ( ((unsigned char*)wch)[0] 0x3F );
JD }
JD which works... if I mouse-over on char * string it shows the right unicode
JD characters.
JD The logging that I included in the first message was converted from
JD wchar_t* to char* and then the sqlite3_strerror() is expanded from char *
JD to wchar_t * and still shows the right characters
JD I just cannot identify the unrecognized token... it's obviously not at
JD character 0... (that's gotten by comparing the pzTail result of
JD sqlite3_prepare_v2 )...
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
JD ___
JD sqlite-users mailing list
JD sqlite-users@sqlite.org
JD http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reinstalling My Build Environment
Hello Paul, What programming language are you using to devel with? Using C++, I deploy sqlite by building the lib and static linking it into my program. No DLL's or install is needed. Sounds like you're doing something significantly more complicated than that though. C Wednesday, September 10, 2014, 7:36:35 PM, you wrote: PB SQLite is a great database, but I've had nothing but problems deploying it PB to a clean machine. There is supposed to be nothing extra to do when moving PB the files from the bin directory to the target machine, but then I hear PB rumblings about an unknown missing C++ redistribution package that may need PB to be added. All my deployment problems must be something that I'm doing PB or not doing. Since I need to use Entity Framework, I started off by PB installing sqlite-netFx451-setup-bundle-x86-2013-1.0.92.0.exe. Since the PB deployment has been such a nightmare, I looked around and saw some PB instructions on using the NuGet package. I installed the Nuget package, and PB that pretty much corrupted my project and maybe my Visual Studio PB installation, so I've decided to start fresh. PB PB It would be tremendously helpful if you could point me in the correct PB direction for installing SQLite into my Visual Studio 2013 installation. PB There seems to be so many pieces and different instructions that I'm PB confused. Do I use the sqlite-net bundle (maybe I'm using the wrong one) to PB install, or do I use NuGet or perhaps both? It would seem that using the PB NuGet package would be best, but when I install the NuGet package into a PB clean environment (yes I've done this several times), it's never compatible PB with Entity Framework, or maybe I'm implementing it incorrectly. When using PB the NuGet package SQLite never comes up as a database option for the ADO.net PB Entity Data Model. PB PB Please help me. At this point, I'm desperate for an installation where I PB can copy the compiled files from the bin directory to a clean machine and PB have it run. PB PB I'm running: Windows 7 - 64 bit and Visual Studio 2013 Ultimate. My target PB machines could be either 32 bit or 64 bit Windows 7 machines. PB PB Your help is tremendously appreciated, PB PDB PB PB ___ PB sqlite-users mailing list PB sqlite-users@sqlite.org PB http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GUI INfo
Hello Maxine, It's totally unrelated to Sqlite though. Pick a programming language that works with Sqlite and make a GUI with this programming language. C Wednesday, September 10, 2014, 4:56:39 PM, you wrote: MN I am an experienced Access VBA programmer. I know about the SQLite commands MN to create and manipulate data in tables and queries. MN MN What I want to know is where do I find info on creating a graphical user MN interface such as menus, forms and reports. What additional programs are MN required to do this? MN MN Thanks in advance, MN Max MN MN ___ MN sqlite-users mailing list MN sqlite-users@sqlite.org MN http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What if OpenDocument were built atop SQLite?
Hello Richard, Monday, September 8, 2014, 8:35:30 AM, you wrote: RH See the essay at: RHhttp://www.sqlite.org/affcase1.html RH Comments, criticism, and feedback are welcomed. I use Sqlite as a container for images because it's superior to CBR/CBZ files (which are rar and zip files). I think this is a great idea. Finally, let us reiterate that this essay is a thought experiment. The OpenDocument format is well-established and already well-designed. Nobody really believes that OpenDocument should be changed to use SQLite as its container instead of ZIP. Nor is this article a criticism of OpenDocument for not choosing SQLite as its container since OpenDocument predates SQLite. Rather, the point of this article is to use OpenDocument as a concrete example of how SQLite can be used to build better application file formats for future projects. It might be too late in the game but, I would have preferred this to zips for sure. Teg mailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mixing journal modes from different threads
Hello George, I use different journal modes within the same application to different database files. My files don't all have the same protection level. Some files I turn it off completely for better speed. Others, I can't afford to lose the data. C Thursday, August 21, 2014, 3:17:05 PM, you wrote: GI Good evening Dr. Hipp, GI ok, got it. Thank you for the quick clarification. GI I only asked on the mailing list because I couldn't find this kind of GI information in the docs (or I didn't look thoroughly enough); if it's the GI first case, perhaps it should be mentioned somewhere... GI Thanks again, GI George. GI On Thu, Aug 21, 2014 at 10:07 PM, Richard Hipp d...@sqlite.org wrote: On Thu, Aug 21, 2014 at 3:05 PM, George Ionescu geoione...@gmail.com wrote: Hello dear sqlite users, is it ok to mix journal modes from different threads accessing the same database? SQLite does not allow you to mix WAL mode with other journal modes. Wal-mode is a property of the database file. If you change to WAL-mode, then *all* database connections must go to WAL-mode. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users GI ___ GI sqlite-users mailing list GI sqlite-users@sqlite.org GI http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Questions from a novice - basic browsing of records in a listview.
/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading compressed database files
Hello Rinat, I compress my data into blobs using Gzip before insert, and decompress when I read the blobs back out but, leave the other data in the DB un-compressed. In that way, I get compression but, normal operations remain fast. This works if the data to compress 4Kish. My blobs can be 200K to 20megs in size. If it's a bunch of small text columns, this technique won't be useful. C Wednesday, May 28, 2014, 5:26:29 AM, you wrote: HR Hi Roger HR Where can I learn how to characterize my database? HR I work read-only, single connection. HR I have 13 tables, of which 2 are huge, 2 are medium and the rest are very small. HR My huge tables have 3 columns: numeric, numeric and varchar with millions of rows. HR I keep an index on the numeric columns. HR Does that bring up any column ordering suggestion? HR Thanks HR Rinat HR -Original Message- HR From: sqlite-users-boun...@sqlite.org HR [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns HR Sent: Wednesday, May 28, 2014 12:06 PM HR To: General Discussion of SQLite Database HR Subject: Re: [sqlite] Reading compressed database files HR -BEGIN PGP SIGNED MESSAGE- HR Hash: SHA1 HR On 28/05/14 00:25, Hadashi, Rinat wrote: My databases are very big (almost 100 GB). I am looking for a compression solution. Did anyone have an experience with reading a compressed database? HR It would be helpful if you characterise your data and queries. HR For example if the size is due to blobs, then careful ordering of HR columns, or moving them to separate tables will likely be very useful. HR You can get compression external to SQLite by using a compressing HR filesystem like NTFS or btrfs (make sure to pick an appropriate HR page size), or by internal compression with cerod: HR http://www.hwaci.com/sw/sqlite/cerod.html HR Roger HR -BEGIN PGP SIGNATURE- HR Version: GnuPG v1 HR iEYEARECAAYFAlOFpvsACgkQmOOfHg372QRSRACfcDqTprcD//n9yYXcGPl9yQfo HR sTIAoLkIaQHR4JAwk1LbuRzCyQsx/5aN HR =tYeT HR -END PGP SIGNATURE- HR ___ HR sqlite-users mailing list HR sqlite-users@sqlite.org HR http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users HR - HR Intel Israel (74) Limited HR This e-mail and any attachments may contain confidential material for HR the sole use of the intended recipient(s). Any review or distribution HR by others is strictly prohibited. If you are not the intended HR recipient, please contact the sender and delete all copies. HR ___ HR sqlite-users mailing list HR sqlite-users@sqlite.org HR http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Load time performance
Hello Rinat, I found that if I opened the DB and read it using normal file IO, throwing away the data I read, then closing it and opening it with Sqlite could reduce this initial delay. It depends on the size of the DB though. It's mostly useful for small DB's. C Wednesday, May 28, 2014, 3:22:49 AM, you wrote: HR Hi, HR I work in READ ONLY mode. HR My application connects the DB only once, at the beginning. HR I can't really work with local files. (I log to any machine and get my files from the network.) HR Perhaps there are some intermediate files generated in the first HR load that I can prepare in advance? HR Rinat HR -Original Message- HR From: sqlite-users-boun...@sqlite.org HR [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stephen Chrzanowski HR Sent: Thursday, May 22, 2014 4:05 PM HR To: General Discussion of SQLite Database HR Subject: Re: [sqlite] Load time performance HR You shouldn't access a SQLite database remotely, except maybe for HR read only access, but that could come under fire. Any network HR file action can't guarantee that write locks happen. HR If you attempt to access a file in READ-ONLY mode, you'll be greeted with a HR 1-5 second delay (I don't remember what the delay is right now) HR per connection. If you're using one connection to the DB, then HR you'll experience the delay. If you've got one connection going HR for the life of your application, you'll get hit with the first HR delay, but all subsequent queries will work. HR On Thu, May 22, 2014 at 8:12 AM, Hadashi, Rinat rinat.hada...@intel.comwrote: Hi The first time I access a database takes significantly more time than subsequent accesses. I am looking for ideas to shorten the time required for the first access. I work on Linux, my db. file is somewhere in the file system, not locally on the machine from which I am running sqlite3 Thanks Rinat Hadashi HR - HR Intel Israel (74) Limited HR This e-mail and any attachments may contain confidential material for HR the sole use of the intended recipient(s). Any review or distribution HR by others is strictly prohibited. If you are not the intended HR recipient, please contact the sender and delete all copies. HR ___ HR sqlite-users mailing list HR sqlite-users@sqlite.org HR http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Retrieve a int or a sqlite_int64
Hello Charles, Tuesday, May 13, 2014, 3:12:09 PM, you wrote: CS Load it with sqlite3_value_int64 every time. If the number fits in a 32 bit CS integer, then you can store it in one. This is what I do. Everything is 64 bits to be future proof. CS Charles CS ___ CS sqlite-users mailing list CS sqlite-users@sqlite.org CS http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite for write only workload, splitting databases into smaller files, but need to query it as a whole
Hello Hayden, If I was doing this, I'd use a single database as the core then periodically from within the app, write out a new database with the last 5 minutes worth of data. Then let them run off the copy while you maintain a master copy of the DB. In that way this splitting and multiple DB thing can go away while you still give them access to the data without letting them touch the master. If they need to read the master, I'd probably write a little client/server application to let them look at but, not change the data (assuming that's a requirement). I'd avoid multiple databases with the core data like the plague. Obviously you can't always do it but, I see it as a positive design goal. C Saturday, May 3, 2014, 9:40:37 AM, you wrote: HL This looks promising. I sooo wish it didn't have a limit to number of HL databases. But I think I could reasonably do something like coalesce the HL databases into a new database once every 2 hours. I also need to generate HL some code to figure out how to address the tables which I guess means I'll HL have to do an N way JOIN? HL On Sat, May 3, 2014 at 6:16 AM, Petite Abeille petite.abei...@gmail.comwrote: On May 3, 2014, at 2:59 PM, Hayden Livingston halivings...@gmail.com wrote: Thoughts? Take a look at ‘ATTACH’, it might help: http://www.sqlite.org/lang_attach.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users HL ___ HL sqlite-users mailing list HL sqlite-users@sqlite.org HL http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Long table fails to create with exec sql on windows 8.1
Hello Mark, System just stops responding. It's not clear if you're talking about the program or your PC. If the PC is hanging, it's broken and this isn't a software problem (well not a Sqlite problem). Anytime a PC running windows hangs or crashes, you know it's a driver or hardware problem (or V scanner). I just thought your comment was a bit ambiguous. I have plenty of windows customers who have PC's that hang or crash so, I'm pretty aware of these issues. C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Noticed crash on windows
Quite unlikely is the prospect for somebody here to say Yes, we routinely see crashes on Windows in the sqlite3_mutex_enter code. You are going to have to debug this. This. I run the hell out of Sqlite under 32 and 64 bit windows with no issues. 20-40 GB databases are common with multi-thread access. Typically only one writer though but, multiple threaded readers. Try running your code in debug build mode. I've run into cases where the release would crash and the debug version wouldn't. Typically, it's caused by a variable I forgot to initialize, which is zero'd in debug mode and not in release. I've not had any luck with Full Program Optimization either. Might try not using that if you currently do. C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows service with SQLITE
Hello tejas, You're using windows so, you should be using Procmon to watch file IO. You need to add and look at logging. This is sort of trouble-shooting 101. If you can't get the debugger on it, you'll have to debug with logging. I'd generate a log file and log everything to do with opening and accessing the DB. C Sunday, February 16, 2014, 11:45:27 PM, you wrote: tp Hi Teg, tp I tried putting delay of 3 seconds and 30 seconds but nothing happens. tp Still not able to access database and store anything. tp When i create a file to store data it stores. Strange.. tp Need more views please. tp Thanks, tp Tejas tp On Sun, Feb 16, 2014 at 12:35 AM, Teg t...@djii.com wrote: Hello tejas, I'm thinking it sounds like the processing collides and you don't have retry's built into the process. Basically a race condition. The fact you can run one process and it works, and then later run a second process and it works suggests the problem is in the startup when you have two starting at the same time. As an experiment, I might put a long delay in the thread of the second process so, the first process gets good and started before the second one tries. C Saturday, February 15, 2014, 7:29:14 AM, you wrote: tp Hi All, tp We have a small issue with sqlite database. tp Program structure: tp one windows service calls two windows processes process-1 and process-2. tp both processes use common database db1.s3db file to store data. tp When windows services starts and calls two processes p-1 and p-2 none of tp the process is able to store data in the database. tp But if i call only one p-1 with service then everything works well tp meanwhile if i run p-2 manually with double click then also two processes tp are able to send data in to database. tp Please help i am clueless. tp Thanks, tp Tejas tp ___ tp sqlite-users mailing list tp sqlite-users@sqlite.org tp http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows service with SQLITE
Hello tejas, I'm thinking it sounds like the processing collides and you don't have retry's built into the process. Basically a race condition. The fact you can run one process and it works, and then later run a second process and it works suggests the problem is in the startup when you have two starting at the same time. As an experiment, I might put a long delay in the thread of the second process so, the first process gets good and started before the second one tries. C Saturday, February 15, 2014, 7:29:14 AM, you wrote: tp Hi All, tp We have a small issue with sqlite database. tp Program structure: tp one windows service calls two windows processes process-1 and process-2. tp both processes use common database db1.s3db file to store data. tp When windows services starts and calls two processes p-1 and p-2 none of tp the process is able to store data in the database. tp But if i call only one p-1 with service then everything works well tp meanwhile if i run p-2 manually with double click then also two processes tp are able to send data in to database. tp Please help i am clueless. tp Thanks, tp Tejas tp ___ tp sqlite-users mailing list tp sqlite-users@sqlite.org tp http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to Troubleshoot Disk I/O Error
Hello Akintoye, A common problem I ran into with the transition from XP to anything newer is that the user folders changed. So, what was a legal read/write folder in XP wasn't in Vista+. I had the folder embedded in the configuration file so, when they brought their old configurations forward, they were pointed at the wrong folder. Easy way to determine if this is the case it to right click the application and Run as Administrator. If it starts working then you know the problem is permissions. For my users, the choice is to move the file, change the permissions, re-install or just Run as Administrator (which I suggested they not do). Thursday, January 30, 2014, 10:52:18 AM, you wrote: AOB7L Thanks, will try this suggestions and post results. AOB7L - Akintoye AOB7L - Original Message - AOB7L From: sqlite-users@sqlite.org AOB7L To: sqlite-users@sqlite.org AOB7L At: Jan 30 2014 10:44:45 AOB7L On 01/30/2014 10:19 PM, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) wrote: Hi, We have one client that recently upgraded from WinXP SP3 to Win7 sp1 (Japanese Language). With new OS, attempts to access read from our sqlite database fails with disk I/O error. Can anyone help with suggestions on how to troubleshoot disk I/O error ? Adding call to sqlite3_config(SQLITE_CONFIG_LOG, ...) produces no additional information. We have not been able to reproduce the error in-house.The user has HP ENVY laptop with hybrid drive. User has tried : 1. Install latest file system drivers 2. reformat the hard-drive reinstall Win7 OS all to no avail. Client does not report problems with any other applications. AOB7L Immediately after the error occurs, what value is returned by AOB7L the sqlite3_extended_errcode() function? Often the extended error AOB7L code identifies the specific IO operation that failed, which can AOB7L shed light on the problem. AOB7L Another thing to do is to try compiling with the following defines: AOB7L-DSQLITE_DEBUG=1 -DSQLITE_DEBUG_OS_TRACE=1 AOB7L This causes the Windows VFS module to print various messages to AOB7L standard output that should help to figure out what is happening. AOB7L Dan. AOB7L ___ AOB7L sqlite-users mailing list AOB7L sqlite-users@sqlite.org AOB7L http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users AOB7L ___ AOB7L sqlite-users mailing list AOB7L sqlite-users@sqlite.org AOB7L http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite Preloading and ASP.Net debugging
I'm not a .net guy but, I do use visual studio. One thing I do is have several post-build steps that copy files around to where I need them before I debug. You can define post build steps in the project options. Thursday, January 30, 2014, 7:16:55 PM, you wrote: JM Eric Schultz wrote: When I debug in Visual Studio, IIS Express copies all of the Assemblies in my bin directory to a folder into the AppData\Temporary ASP.Net Files folder. Unfortunately, it does not copy the System.Data.SQLite.dll.config file or the x86 and amd64 subfolders of bin which contain my interop files. JM I've seen this behavior before as well. This appears to be a limitation of JM Visual Studio. Ideally, it *should* copy the configuration file and all the JM sub-folders as well. Is there any way so the preloading base directory is from where the System.Data.SQLite.dll file originally came from when I can't set the environment variables or have a config file in the same folder as the executing assembly (which I can't in this case). JM Why can't you set environment variables? The currently supported workaround JM for this limitation of Visual Studio is to use the JM PreLoadSQLite_BaseDirectory JM environment variable (or configuration file setting). JM Of course, I'm always open to alternative suggestions on how to work around JM issues like these in a portable and robust way. JM -- JM Joe Mistachkin JM ___ JM sqlite-users mailing list JM sqlite-users@sqlite.org JM http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Application statically linked with sqlite3.6 creates sqlite2.1 files
Hello Joachim, If you run it in the debugger, break it then look at the loaded modules. That'll tell you if the Sqlite dll got loaded somehow. I'd suspect some other part of the program might be loading the DLL manually and hooking the function calls. You could load DLL through ordinals (basically the function index in the DLL) and not have any Sqlite related strings in the EXE. I tend to load my own DLL's through ordinals. If you see nothing, I might stick an Sqlite dll in the program folder and then run a second time and check again. Maybe something is trying to be smart and load the DLL when available but failing that, falling back on using internal static linked. How large is the .lib you're loading? My release build, 32 bit Sqlite lib is 2.1 megs. Release build 64 bits is 2.7 Megs C Thursday, January 23, 2014, 10:38:10 AM, you wrote: JB Hello Simon, JB thank you for your fast response. On 23 Jan 2014, at 1:58pm, Joachim Bürmann jbuerm...@iftools.com wrote: On a certain customer system (Windows7 64bit) the application cannot read the example project files (created with sqlite3.6 library). And when the user stores his own settings in a new project, the project file is saved as a version 2.1 file (** This file contains an SQLite 2.1 database...) instead of a sqlite3 compatible file (SQLite format 3...) The calls to anything that can make a version 2.1 file have different names. It can't just be a case of calling the wrong 'sqlite_open()' function because the function is actually called 'sqlite3_open()' with the '3' right there in the name. But because the sqlite functionality is part of the application (static linked), the program should never able to access an external sqlite DLL. I don't think that that logic is correct. Can you run 'strings' (or whatever the Windows7 equivalent is) on the compiled file and see whether there are any mentions of 'sqlite2' ? Is there a debugging tool which will list the names of all routines which are called ? Ignore whether it is internal or external, just look at the routine names. JB ok, my wording could missunderstand. Of course, also a statically linked JB program can open a shared lib to access function from that. But our JB application doesn't do that - not for sqlite. I only thought, that JB accessing an external sqlite DLL could cause such a behaviour. But with JB different names - as you mentioned - it's hard to believe that a older JB DLL is the reason. JB I don't know 'strings' for windows. I tried it with dumpbin (outputs all JB dependencies and imports) and wintrc (similar to strace) and didn't find JB any sqlite access (neither a function nor a library/dll open call with JB sqlite in its name). That's why I'm so confused. I suspect that that particular customer computer has something weird loaded. JB yes, I suppose that there is something different in that computer. More JB than this: The customer told me, that he can reproduce the effect on JB another notebook. So I think it isn't a matter of the OS but of some JB other application he runs on both computers. JB Anyway, thanks a lot for your response JB Best regards JB Joachim Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users JB ___ JB sqlite-users mailing list JB sqlite-users@sqlite.org JB http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] store image file as blob
Hello d, I prefer them in the DB so, I can move them as a package. I have 30-40 GB DB's filled with image files. Performance is decent. I do keep the blobs in one table and meta-data in another. C Wednesday, October 30, 2013, 7:30:58 AM, you wrote: db Hi, db My sqlite database module has to store images (max. 100 KB) on client db machine. db 1. store images on file system and have reference in database db 2. store image as blob in database. db Which is the best way to store these images? db Any suggestions are welcome. db Best Regards, db va. db ___ db sqlite-users mailing list db sqlite-users@sqlite.org db http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and Virtual PC
Hello Stephen, My experience is that shared folders under Virtual PC are completely unreliable. SMB actually works better, other than the fact Sqlite doesn't seem to like SMB. C Sunday, September 8, 2013, 4:46:04 PM, you wrote: SC You're using it via a network which is a no-no for SQLite. ANY remote SC access of a file under any networking infrastructure isn't guaranteed with SC SQLite. SC On Sun, Sep 8, 2013 at 12:31 AM, Gintaras Didzgalvis SC supp...@quickmacros.com wrote: Hi, SQLite running on Microsoft Virtual PC 2007 cannot read and write databases that are on host PC. Details: sqlite3_open_v2(SQLITE_OPEN_**READWRITE) succeeds, but sqlite3_exec returns SQLITE_BUSY, even with SELECT. Using the official downloaded sqlite3.dll, version 3.8.0. OS on guest PC, where SQLite is used: Windows XP, SP3. OS on host PC: Windows 7, 32-bit. Accessing the database file using the shared folders feature of Virtual PC. Windows API functions can read and write the file. No problems on Vmware player (accessing the file through its virtual network). -- -- Gintaras http://www.quickmacros.com __ __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users SC ___ SC sqlite-users mailing list SC sqlite-users@sqlite.org SC http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_text truncates strings with .
Hello Nickye, I insert strings with . all the time. In fact I'd say most insertions I do have .s in them so, I don't think there's an inherent problem in Sqlite. I convert everything to UTF8 before I insert though the UTF8 of . is .. C Monday, July 15, 2013, 3:13:46 PM, you wrote: nmr Hello, nmr Prepared statement SQL: INSERT INTO tbl VALUES(?1); nmr Column type:TEXT nmr String to bind (ASCII, zero terminated): abcd.ef nmr nBytes parameter: -1 nmr destructor parameter: SQLITE_STATIC nmr Result after call sqlite3_step: abcd nmr With nBytes==(len(String)) there's no problem, result==abcd.ef nmr But then there's no way to bind parameter only once with the same text nmr buffer. nmr ni...@mail.ru nmr ___ nmr sqlite-users mailing list nmr sqlite-users@sqlite.org nmr http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to save live data into sqlite database using c language?
Hello Newbie89,
Sunday, April 28, 2013, 3:32:07 AM, you wrote:
N Thanks for the correction
N ok...I will check first.
N --
N View this message in context:
N
http://sqlite.1065341.n5.nabble.com/How-to-save-live-data-into-sqlite-database-using-c-language-tp68519p68521.html
N Sent from the SQLite mailing list archive at Nabble.com.
N ___
N sqlite-users mailing list
N sqlite-users@sqlite.org
N http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
I suspect you want one protocol field
enum _protocols
{
C_PROTOCOL_TCP,
C_PROTOCOL_UDP,
..
};
CREATE TABLE host
(
Src_MAC char(18),
Src_IP char(16),
Cap_Bytes integer,
Protocol integer,
);
With a single protocol tag per insert. Then you tag each packet insert
with the protocol field.
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing in a blob
Imagine what could happen if, for example, git where to start using this library. Instead of the .git directory containing lots and lots of separate files, your repository would be a single .git file which was really an SQLite database accessed through the sqlite3fs wrapper. I use Sqlite for a git like version control system for my own projects. I also use it for storing image files similar to CBR/CBZ files. Having random access to the files makes it a better solution than CBR/CBZ files because, they're basically RAR and ZIP files with no random access. In general, there are many benefits to keeping a bunch of smallish files in a DB you can move around and change atomically. I do tend to keep blobs in their own database files and the meta-data in another file. The blobs seem to impact the performance of the meta data tables when they're combined into a single DB. I like the idea of a general purpose File IO wrapper over Sqlite, have no need for it to be built in though. C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.17 preview - 2x faster?
Hello Richard, How much do you map at a time? I've virtually abandoned memory mapped files in Win32 because of address space limitations. There's a 2 GB address space limit in Win32 (most of the time) so, if the combination of allocated RAM and memory mapped file size bump into the limit, the memory map will fail. Win64 doesn't have this limit. It'll fail if it can't get a contiguous block of address space too. C Thursday, April 4, 2013, 8:02:34 AM, you wrote: RH By making use of memory-mapped I/O, the current trunk of SQLite (which will RH eventually become version 3.7.17 after much more refinement and testing) RH can be as much as twice as fast, on some platforms and under some RH workloads. We would like to encourage people to try out the new code and RH report both success and failure. Snapshots of the amalgamation can be RH found at RHhttp://www.sqlite.org/draft/download.html RH Links to the relevant documentation can bee seen at RHhttp://www.sqlite.org/draft/releaselog/3_7_17.html RH The memory-mapped I/O is only enabled for windows, linux, mac OS-X, and RH solaris. We have found that it does not work on OpenBSD, for reasons we RH have not yet been able to uncove; but as a precaution, memory mapped I/O is RH disabled by default on all of the *BSDs until we understand the problem. RH The biggest performance gains occur on windows, mac, and solaris. The new RH code is also faster on linux, but not by as big a factor. The speed RH improvement is also heavily dependent upon workload. Some operations can RH be almost twice as faster. For others, there is no measurable speed RH improvement. RH Your feedback on whether or not the new code is faster for you, and whether RH or not it even works for you, is very important to us. Thanks for giving RH the new code a try. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Saving ubyte[] zlib data in sqlite and calling it back
Hello jose, I compress/encrypt to a vector, then write the vector as a blob and reverse. Read the blob as a vector then reverse the process. Monday, February 11, 2013, 1:45:34 PM, you wrote: jic Greetings. jic I have this table, jic CREATE TABLE Test (login primary key, password); jic and I would like to save zlib data using ubyte[] and also call jic it back in. I have things like this: jic 1. To save in SQLite, jic cmd = jic UPDATE Test password = ~ jic cast(char[])cast(ubyte[])std.zlib.compress(cast(void[])password) jic WHERE login = 'test';; jic This works, or at least, it saves in SQlite, but when I call it back, it does not. jic 2. To call it in SQLite, jic char[] passWord = jic cast(char[])cast(ubyte[])std.zlib.uncompress(cast(void[])r[0][password]); jic where r[0][password] is what I just SELECTED from SQlite. I jic am getting an UTF8 error. This is a D program, so, the syntax jic may not be known to some or all of you. However, the more jic realistic SQLite question would be, how can I save ubyte data in jic SQLite and also call it back. Thoughts? Comments? Jokes? :-) jic thanks. jic josé jic ___ jic sqlite-users mailing list jic sqlite-users@sqlite.org jic http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug report: out of memory error for most operations on a large database
CL This looks as if all temporary data is stored in memory (of which you CL don't have too much in a 32-bit process.) Yeah, this. Typically you only have 2 gigs of address space to work with, in 32 bit windows. I've run out of RAM by having temp_store=memory before. I also have 30-60 GB DB files that I operate on that don't have any issues like this. CL This looks as if all temporary data is stored in memory (of which you CL don't have too much in a 32-bit process.) CL What is the value of PRAGMA temp_store; and the default setting of CL TEMP_STORE (PRAGMA compile_options;)? CL Regards, CL Clemens CL ___ CL sqlite-users mailing list CL sqlite-users@sqlite.org CL http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running on windows 98
I think you kick '98 to the curb and let the 5 people who use it maintain their own support for it. Bit-rot is a real problem with any code that goes mostly unexercised. I'm thinking maybe the '98 people should simply fork their own version of Sqlite. Then find their own '98 maintainer, instead of trying to force the job onto Dr Hipp. Thursday, January 24, 2013, 3:18:15 PM, you wrote: GG On Thu, Jan 24, 2013 at 2:21 PM, Richard Hipp d...@sqlite.org wrote: On Thu, Jan 24, 2013 at 2:01 PM, Jose F. Gimenez jfgime...@wanadoo.eswrote: Richard, thanks for replying. We have no way of testing SQLite on Win9x and so we do not intend to support Win9x moving forward. Some older versions of SQLite are known to work on Win9x. If you are still supporting Win9x applications, I suggest you use those older versions of SQLite. GG How about just supporting a compile time option to turn on or off that GG optimization (on by default)? Then those compiling for Win9x could GG just turn it off yet it would not require explicit support and testing GG of Win9x since its the option being supported rather than the platform GG support. GG ___ GG sqlite-users mailing list GG sqlite-users@sqlite.org GG http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] performance regression when using insert or replace
54698 if( pOp-p2 p-usesStmtJournal--- we go INTO
this if statement
54699 (db-autoCommit==0 || db-activeVdbeCnt1)
54700 ){
54701 assert( sqlite3BtreeIsInTrans(u.as.pBt) );
54702 if( p-iStatement==0 ){
54703 assert( db-nStatement=0 db-nSavepoint=0
);
54704 db-nStatement++; this sets to 1, causing
next line to set iStatement to 1
54705 p-iStatement = db-nSavepoint + db-nStatement;
54706 }
54707 rc = sqlite3BtreeBeginStmt(u.as.pBt,
p-iStatement);
- sqlite3BtreeBeginStmt calls sqlite3PagerOpenSavepoint using
iStatement as 2nd parameter,
therefore nSavepoint is set to 1
- eventually we call sqlite3BtreeInsert which calls insertCell which
calls sqlite3PagerWrite which
calls pager_write a second time for the SAME pPg-pgno (see below for
example page 6)
- because we've gone through once for the same page, we do not
enter the if clause
on line 35464,
35464 /* The transaction journal now exists and we have a
RESERVED or an
35465 ** EXCLUSIVE lock on the main database file. Write
the current page to
35466 ** the transaction journal if it is not there
already.
35467 */
35468 if( !pageInJournal(pPg) isOpen(pPager-jfd) ){
- therefore we DO NOT add this page to the savepoint bitVec on line
35517
35517 rc = sqlite3BitvecSet(pPager-pInJournal,
pPg-pgno);
- therefore we DO go into the if(subjRequiresPage(pPg)), because
nSavepoint is 1 but the bit is NOT set
35536 /* If the statement journal is open and the page is
not in it,
35537 ** then write the current page to the statement
journal. Note that
35538 ** the statement journal format differs from the
standard journal format
35539 ** in that it omits the checksums and the header.
35540 */
35541 if( subjRequiresPage(pPg) ){
35542 rc = subjournalPage(pPg);
35543 }
from gdb...
- (gdb) p *pPg
$17= {pPage = 0x2b84618, pData = 0x2b83618, pExtra =
0x2b84698, pDirty = 0x0, pPager = 0x2b666c8,
pgno = 6, flags = 6, nRef = 1, pCache = 0x2b667d0,
pDirtyNext = 0x2b83540, pDirtyPrev = 0x0}
(gdb) p pageInJournal(pPg)
$18 = 1
(gdb) p
sqlite3BitvecTest(pPager-aSavepoint[0]-pInSavepoint,6)
$19 = 0
- therefore we end up CALLING subjournalPage(pPg) from line 35542,
causing temp file to be created and lots of small writes to the
file
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
DK ___
DK sqlite-users mailing list
DK sqlite-users@sqlite.org
DK http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance
Hello David, In /control panel/system/device manager/disk drives/properties/Policies you can disable the windows write cache buffer flush to the drive. I wonder if this would make a difference? My machine is on a UPS so, I always disable it. I tend to disable it on laptops too. I know it's probably not a real solution but, I wonder if the lack of write buffering is why you see what you see. C Friday, November 30, 2012, 12:50:30 PM, you wrote: DdR Nope, I ran the tests both in Parallels and rebooting directly DdR into boot camp (basically native windows), and had essentially DdR identical performance (+/- 2%, within noise level differences). DdR It also echoes the performance difference I'd been seeing on the DdR database side just watching the real app run on iOS and on my DdR other non-Apple native windows box. Interesting little find, DdR nonetheless, thanks for that. :) DdR To Alex: Unfortunately, Windows is a core platform for us. We DdR can't really just tell them to buzz off, so it's either figure DdR out how to improve SQLite performance or switch DB engines, at least on that platform... DdR -David DdR DdR From: sqlite-users-boun...@sqlite.org DdR [sqlite-users-boun...@sqlite.org] on behalf of Black, Michael (IS) [michael.bla...@ngc.com] DdR Sent: Friday, November 30, 2012 9:46 AM DdR To: General Discussion of SQLite Database DdR Subject: Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance DdR Could this be your problem? DdR http://mattgadient.com/2011/02/18/mac-os-x-slow-for-10-15-minutes-after-boot-the-fix/ DdR Michael D. Black DdR Senior Scientist DdR Advanced Analytics Directorate DdR Advanced GEOINT Solutions Operating Unit DdR Northrop Grumman Information Systems DdR DdR From: sqlite-users-boun...@sqlite.org DdR [sqlite-users-boun...@sqlite.org] on behalf of David de Regt [dav...@mylollc.com] DdR Sent: Friday, November 30, 2012 11:41 AM DdR To: General Discussion of SQLite Database DdR Subject: EXT :[sqlite] Windows (slow) vs. iOS/OSX (fast) Performance DdR Hey all. I've been struggling with a basic perf issue running DdR the same code on Windows vs. iOS and OSX. DdR Basic query set: DdR CREATE TABLE test (col1 int, col2 text); DdR [loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4') DdR I'm coding this using the default C amalgamation release and DdR using prepare/etc. on all platforms in the exact same way (same DdR very simple DB-access class I made). I realize that using a DdR transaction around this would vastly improve perf, but given the DdR atomic nature of the app that this test is simulating, it won't DdR work to wrap it into transactions, so my goal is to improve the DdR atomic performance. These are all being run on the same Macbook DdR Pro, with an SSD, running Windows via boot camp, OSX natively, and iOS via the iOS simulator: DdR With defaults (pragma sync = on, default journal_mode): DdR Windows: 2500ms DdR iOS: 300ms DdR OSX: 280ms DdR With pragma sync = off, journal_mode = memory: DdR Windows: 62ms DdR iOS: 25ms DdR OSX: 25ms DdR Turning off sync doesn't make me feel warm and fuzzy about our DdR lost-power scenario, so with sync on, it seems like something DdR must be fishy for it to be ~8-9x slower than the other platforms. DdR Is there something ridiculous about the windows file system DdR performance that hoses sqlite's open/read/write/close transaction DdR cycle? Is there anything I can do, or just accept it and move DdR on? With how that scales up, we may need to move to something DdR like using embedded MySQL or LocalDB on Windows to get the same DdR performance as we see with SQLite on other platforms, which seems quite ridiculous. DdR Thanks! DdR -David DdR ___ DdR sqlite-users mailing list DdR sqlite-users@sqlite.org DdR http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users DdR ___ DdR sqlite-users mailing list DdR sqlite-users@sqlite.org DdR http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users DdR ___ DdR sqlite-users mailing list DdR sqlite-users@sqlite.org DdR http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VC++ and SQLite
Hello Arbol, There's nothing special about Stdafx.h or Stdafx.cpp. Even if you switch to a different compiler, they just compile with no PCH. You do have to create them if you're going to use them though. I typically copy them in from another project. I even use them in Linux projects because it's a nice place to centralize headers used by a project. Once you understand the in's and outs of PCH in VS it's simply not that hard. I don't use PCH with Sqlite.c. It's in it's own library project which my main project links to. In that way, each project can compile however works best for it. Everything I write uses PCH, everything I inherit, might or might not use PCH. AO What a nightmare Visual Studio is :( You know what they say. It's a poor craftsman who blames his tools. Your problems with VS seem to stem from general noobishness. Learning devel tools is no different than learning a foreign language. You need to research it and learn how it all works. I think things would be alot easier for you if you made a new static lib project just for Sqlite, compile it there with no PCH and link it into your program. If you add the project as a Reference VS will link it in automatically. Tuesday, November 13, 2012, 8:33:48 AM, you wrote: AO I would also keep this feature, however, in the case of SQLite3 AO amalgamation, I am really confused. You know how we have to AO #include the 'stdafx.h' in every declaration file (making it AO non-portable code), i.e. .c, .cpp, etc., well, I tried doing the AO same thing with sqlite.c, but VS10 complains about it. AO What a nightmare Visual Studio is :( AO Genius might have limitations, but stupidity is no handicap AO Eat Kosher AO -Original Message- AO From: sqlite-users-boun...@sqlite.org AO [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Drescher AO Sent: Monday, November 12, 2012 7:44 PM AO To: General Discussion of SQLite Database AO Subject: Re: [sqlite] VC++ and SQLite AO On Mon, Nov 12, 2012 at 1:52 PM, Doug Nebeker pa...@poweradmin.com wrote: You might be surprised at the speed increase you see in compile time if you've got large projects. The time isn't lost to CPU as much, but disk I/O time adds up when hitting many hundreds of small (header) files (even with an SSD). AO This is why I use PCH. Building some of my projects take a long AO time even on a 12 threaded processor with multiple SSDs. AO -- AO John M. Drescher AO ___ AO sqlite-users mailing list AO sqlite-users@sqlite.org AO http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users AO ___ AO sqlite-users mailing list AO sqlite-users@sqlite.org AO http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] First time poster: need advise
Hello Igor, Why don't you download the C and H file from the amalgamation and then compile it into your program? That's how I do it. No fuss no muss. No worry about DLL version problems too. C Friday, September 21, 2012, 4:40:35 PM, you wrote: IK Igor, IK On Fri, Sep 21, 2012 at 5:40 AM, Igor Tandetnik itandet...@mvps.org wrote: Igor Korot ikoro...@gmail.com wrote: When I tried to download the precompiled developmental package for Windows I saw the sqlite-dll-win32-x86-3071400.zip. However this file contains DLL file and no .h. Where can I get the headers? Do I need to download amalgamation archive? Yes. You would also need an import library - a LIB file. You make one as described at http://support.microsoft.com/kb/131313 . Simply run this command: lib /DEF:sqlite.def LIB tool is found in /bin subfolder of your Visual Studio installation. IK Stupid question: why not provide .h and .lib files as well in one IK archive along with .dll? IK The comment on those files says: This is all that is needed to do the IK development. IK Thank you. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users IK ___ IK sqlite-users mailing list IK sqlite-users@sqlite.org IK http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Store error messages in thread local memory
___ sqlite-users mailing list sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users SK ___ SK sqlite-users mailing list SK sqlite-users@sqlite.org SK http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Compile Bug for Windows 8
Hello Igor, Friday, September 14, 2012, 8:59:54 AM, you wrote: IT Chang Li changli...@hotmail.com wrote: At line 14573 require add (char *) for convert from (void *) z = (char *)sqlite3DbMallocRaw(db, (int)n); IT Are you, by any chance, trying to compile in C++ mode? SQLite is IT written in C, where such a cast is not required. Yeah, I'm building under Win8 with no issue. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE in a Win7 Service Application
Hello markus, Every Windows programmer needs to have Procmon installed on their system so, they can watch their program's disk IO. I'd suggest installing it, then using a filter to watch just your service. Then, you can simply watch what disk IO is failing. I use it on a daily basis. Almost as much as my debugger. It'll probably show you a permission problem. C ... Test.exe 6312 ReadFileE:\Test\Db.db3SUCCESS Offset: 0, Length: 100, Priority: Normal Test.exe 6312 ReadFileE:\Test\Db.db3SUCCESS Offset: 0, Length: 4,096, I/O Flags: Non-cached, Paging I/O, Priority: Normal Test.exe 6312 QueryOpen E:\Test\Db.db3SUCCESS CreationTime: 6/19/2012 9:06:58 PM, LastAccessTime: Test.exe 6312 LockFileE:\Test\Db.db3SUCCESS Exclusive: True, Offset: 1,073,741,824, Length: 1, Fail Immediately: True Test.exe 6312 LockFileE:\Test\Db.db3SUCCESS Exclusive: False, Offset: 1,073,741,826, Length: 510, Fail Immediately: True Test.exe 6312 UnlockFileSingle E:\Test\Db.db3SUCCESS Offset: 1,073,741,824, Length: 1 Test.exe 6312 QueryOpen E:\Test\Db.db3-journalSUCCESS CreationTime: 6/19/2012 9:06:58 PM, Test.exe 6312 QueryStandardInformationFile E:\Test\Db.db3SUCCESS AllocationSize: 106,496, EndOfFile: 106,496, NumberOfLinks: 1, Test.exe 6312 QueryOpen E:\Test\Db.db3-wal NAME NOT FOUND Test.exe 6312 QueryStandardInformationFileE:\Test\Db.db3SUCCESS AllocationSize: 106,496, EndOfFile: 106,496, Test.exe 6312 ReadFileE:\Test\Db.db3SUCCESS Offset: 0, Length: 8,192 ... and so on. Wednesday, August 15, 2012, 11:19:52 AM, you wrote: mr Hi, mr We are facing problems when the sqlite database () is used within an mr application that runs as a Win7 service under the local system account. mr The application is written in Java and we use the sqlite-jdbc-3.7.2 JDBC mr driver. mr How ever we can create a new database, reading and writing to it withount mr any problem. The only thing that is not working is the ATTACH DATABASE SQL mr command. We constantly receive a CAN_NOT_OPEN error code. mr When we start the same application under a local user everything works mr fine. I have to add the both databases that are to be attached are in the mr same folder and have been created by the Application itself. There are no mr access restrictions on the database folders or files. mr Does anyone know about this and / or has a solution? mr Kind regards mr Markus mr ___ mr sqlite-users mailing list mr sqlite-users@sqlite.org mr http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I consider this a bug. Anyone else?
In windows, I believe you want to use the function level linking option to get it to drop the most functions when they aren't used. Particularly for the amalgamation, I think this will give you the tightest link. By default, I believe the optimization only operates on compilation units, meaning the amalgamation would always include everything in the link stage. http://msdn.microsoft.com/en-us/library/xsa71f43(v=vs.80).aspx C Tuesday, June 26, 2012, 8:52:58 PM, you wrote: SS On 27 Jun 2012, at 1:48am, Stephen Chrzanowski pontia...@gmail.com wrote: When you compile the amalgamation with your source code, doesn't the compiler reject/not include/remove/doesn't consider the code that could have been generated from the actual final build? In other words, if you just have one function being used out of 10,000 functions, the final binary would only include the code for that single function and not the 10,000? SS Your compiler will probably be set to do optimization. It will SS not include any functions which are not called. SS Simon. SS ___ SS sqlite-users mailing list SS sqlite-users@sqlite.org SS http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with including sqlite3.c into c++ project
AD Do not use precompiled headers on sqlite3.c This. My Sqlite is in it's own library project so, it can have different precompiled header settings than my other projects. About 80% of my projects use procompiled headers, the other 20 are typically made of code like Sqlite which don't have built in support for PCH. You can probably make it work if you really want to by including Stdafx.h at the top of the amalgamation C file. You'll have to do this every time you upgrade though. C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] intial database creation
Hello Brett, All of my DB's are generated in code. When I version control the code, I'm also version controlling the schema. If my users end up with bad DB's (it's windows, it happens fairly frequently). They just delete them and they're then re-created as needed by the program. I have specific unit tests built as part of the class itself to test access to the DB and test all the queries and inserts. I just use the command line tool for trouble-shooting. C Thursday, May 10, 2012, 4:26:29 PM, you wrote: MBE How does one go about creating a database initially in SQLite? MBE Do you create a schema file for it to read or similar? MBE ___ MBE sqlite-users mailing list MBE sqlite-users@sqlite.org MBE 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] Visual Studio 2008 Express and sqlite3.c
Hello Marco,
You have to make sure it's building as C and not C++. This suggests
you might have your project set to build everything as C++. In my
case, I made a new subproject for Sqlite that builts it into a Lib.
Then I set the build dependencies so the lib is used automatically at
link time.
Friday, March 30, 2012, 8:32:55 AM, you wrote:
MB Please note that if I use the same compiler to compile sqlite3.c
MB as a single c file than everything is compiled without errors.
MB Errors occurs only when sqlite3.c is part of a c++ project.
MB I also manually set sqlite3.c to be compiled as C file instead of Default
without any luck.
MB Seems like something confuse the Visual C++ compiler.
MB Any idea?
MB --
MB Marco Bambini
MB http://www.sqlabs.com
MB On Mar 30, 2012, at 1:08 PM, Nick Shaw wrote:
-Original Message-
I am trying to compile the latest sqlite 3.7.11 amalgamation C file within
a Visual Studio 2008 Express C++ project.
sqlite3.c is correctly set as to be compiled as C code but I am unable to
find out a way to fix some compilation errors:
Compiling...
sqlite3.c
..\Sources\sqlite3.c(107831) : error C2143: syntax error : missing ':'
before '!'
..\Sources\sqlite3.c(107831) : error C2059: syntax error : ';'
..\Sources\sqlite3.c(107832) : error C2059: syntax error : '}'
..\Sources\sqlite3.c(107994) : error C2079: 'yy318' uses undefined struct
'LikeOp'
..\Sources\sqlite3.c(110530) : error C2224: left of '.eOperator' must have
struct/union type
..\Sources\sqlite3.c(110530) : error C2059: syntax error : '!'
..\Sources\sqlite3.c(110534) : error C2224: left of '.eOperator' must have
struct/union type
..\Sources\sqlite3.c(110534) : error C2059: syntax error : '!'
..\Sources\sqlite3.c(110538) : error C2275: 'ExprList' : illegal use of
this type as an expression
..\Sources\sqlite3.c(8133) : see declaration of 'ExprList'
..\Sources\sqlite3.c(110541) : error C2224: left of '.eOperator' must have
struct/union type
..\Sources\sqlite3.c(110541) : error C2198: 'sqlite3ExprFunction' : too few
arguments for call
..\Sources\sqlite3.c(110542) : error C2059: syntax error : '!'
..\Sources\sqlite3.c(110554) : error C2224: left of '.eOperator' must have
struct/union type
..\Sources\sqlite3.c(110554) : error C2198: 'sqlite3ExprFunction' : too few
arguments for call
..\Sources\sqlite3.c(110555) : error C2059: syntax error : '!'
Looks like something's not right with the definition of the LikeOp struct in
your copy of the file. I've got the exact same amalgamation in my VS2008
project and it builds fine. Can you check what you have for the LikeOp
structure definition? Mine looks like this, and starts on line 107829:
struct LikeOp {
Token eOperator; /* like or glob or regexp */
int not; /* True if the NOT keyword is present */
};
Thanks,
Nick.
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
MB ___
MB sqlite-users mailing list
MB sqlite-users@sqlite.org
MB http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Could/should Windows build of SQLite use #define WIN32_LEAN_AND_MEAN?
Hello Jeff, Isn't that just a build time thing? Include fewer includes during the compile? I just don't build sqlite often enough to seem to think this matters. Pretty sure this has no impact on the ultimate size of the code generated. Friday, March 23, 2012, 7:51:36 AM, you wrote: JR When building using the SQLite amalgamation, I noticed Windows.h JR being included without #define WIN32_LEAN_AND_MEAN. This includes JR a lot of extraneous cruft. Any reason not to trim down the windows build this way? JR ___ JR sqlite-users mailing list JR sqlite-users@sqlite.org JR http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VIsual Studio 2005. slow and high CPU in debug
Hello Juan, Debug mode is unusable for production code. Even it you add optimization, MS builds in a debug memory allocator/deallocator that's at least 10 times slower than the regular memory allocator. Running my application under debug, the memory allocations completely dominate processing performance. It's more than assertions, the debug memory allocator tracks every allocation and de-allocation. Your alternative is to build for release but, tell the linker and the compiler to add debug code to the exe. Even if you turn the optimizer off, you still end up with faster code. C Monday, March 19, 2012, 5:05:49 AM, you wrote: JP Hi all: JPI´m using SQLite amalgamation in various exe files with Visual Studio JP 2005. JPIn debug mode the exe files are very slow and have high CPU usage. JPIn release the problem dissapears. JPIs there some option in SQLite to avoid this problem? JP Thank you very much in advance. JP ___ JP sqlite-users mailing list JP sqlite-users@sqlite.org JP http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cyrillic support
Hello Mite, You just need to convert it to UTF-8 before insert, then convert it back when you pull the data. UTF-16-UTF8 - insert Select - UTF8-UTF16 Or whatever encoding you use. I insert and retrieve full asian languages using UTF-8 conversions. You should be using UTF-8 for you filenames when opening the DB too. C Saturday, March 10, 2012, 12:07:23 PM, you wrote: M I am using the newest version of SQLite. How do I enable support for M Cyrillic letters? Whenever I enter something in the DB with Cyrillic M letters it gets saved like this ?? M Thanks M ___ M sqlite-users mailing list M sqlite-users@sqlite.org M http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cyrillic support
Hello Jean-Christophe, Good point. My application is 100% UTF-8 so, I probably think in just UTF-8. C Saturday, March 10, 2012, 4:38:00 PM, you wrote: You just need to convert it to UTF-8 before insert, then convert it back when you pull the data. UTF-16-UTF8 - insert Select - UTF8-UTF16 JCD There are 16-bit SQLite APIs for that, no conversion needed JCD actually. But of course it all depends of the wrapper you use, if any. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug hunting in SQLite
Hello Patrik,
As a trouble-shooting step. I'd add a second table with a checksum of
the blob. Maybe an MD5 and then every time I accessed the row of the
DB for any reason, I'd re-check the MD5 of the blob. Then break/assert
when the program notices the blob is no longer correct.
I use gigs of blobs and have no issues with corruption. Assuming the
blob actually gets entered correctly, I imagine either the DB is
becoming corrupt or something in your program is writing to the blob
when you don't expect it.
C
Sunday, February 26, 2012, 3:47:18 PM, you wrote:
PN I only load data from a particular row when I need to display it. The
PN data that got corrupted isn't read for some time and even if I close
PN down the program and restarts the computer the same corruption is there
PN on the same row. It is saved into data, although I don't modify it in
PN any way.
PN Patrik
PN On 02/26/2012 09:12 PM, Pavel Ivanov wrote:
My guess is you read blob data incorrectly. You get pointer to the data
from SQLite, but actually read the data when memory has been already reused
for other purposes.
Pavel
On Sunday, February 26, 2012, Patrik Nilsson nipatriknils...@gmail.com
wrote:
Dear All,
I have a table called page (defined below) and have noticed that the
data-variable (blob) sometimes get corrupted. How do I set up a log to
debug what is happening. What I want is a log file saved with my file,
so I can backtrace the error.
This is also to be able to send in a good bug report to the developers
of SQLite.
I don't know the exact steps to reproduce the error, but the following
might give someone a clue:
1) Add rows (one by one) with data.
2) Update sortorder for the whole table with begin/commit
3) After these and maybe some other steps (i.e. updating a row's data
with new data), I discover that a random row of the table has a
corrupted data-blob.
I have made a verify function for the whole table of the data-blobs. The
data-blob can contain images varying in size from a few hundred bytes to
some (about 5) megabytes of images. (Currently only png-images.)
Best regards,
Patrik
create table if not exists page ( id integer primary key autoincrement,
type integer, sortorder integer, width integer, height integer, size
integer, deleted integer default 0, zoom integer default 0, modified
datetime default (datetime('now','localtime')), comment text, data blob )
___
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
PN ___
PN sqlite-users mailing list
PN sqlite-users@sqlite.org
PN http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert image into db - windows batch
Hello Petr, You could Mime encode them to text, insert as a string. Pull them back out as strings, un-mime them. Should be able to do that from a batch file. I keep a bunch of images in Sqlite DB files. It's reasonably fast and I like having them all in one place. Some of the DB files are 10 GB. C Wednesday, January 18, 2012, 10:14:17 AM, you wrote: PL Because of packing. The script should be distributable as one PL .cmd file, the database engine and database itself will be embeded PL inside od script. Dealing with embeded files in the shell script PL is not easy thus I want to minimize its number. PL L. On Jan 18, 2012, at 12:00 PM, Petr Lázňovský wrote: have windows batch working with sqlite, may I insert image into database and than read this images from? As pointed out, you might want to use the 'blob' type to store binary data. That said, why bother storing these images inside the database itself? Any benefit in doing so? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users PL ___ PL sqlite-users mailing list PL sqlite-users@sqlite.org PL http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.7.9 amalgamation file in VS2005
Yeah, I was wrong. Color syntax highlighting does work. It's single stepping into the code that doesn't. Sorry. C Wednesday, December 28, 2011, 3:04:39 AM, you wrote: AN Dear Michael, AN AN thanks for trying this out. Not at the moment but I will consider upgrading for the future. AN AN In fact I was surprised about this problem because amalgamation AN file version 3.7.7.1 still *has* correct highlighting in both AN VS2005 and 2008. The 3.7.9 amalgamation fle is, of course, larger, AN but the difference does not seem to be so big so that the AN highlighting should fail. So maybe there might be some syntax AN elements in 3.7.9 (large comments or whatever) causing this AN behaviour or there is really a maximum source file size that AN VS2005 and 2008 syntax highlighter can support, which was reached between 3.7.7.1 and 3.7.9. AN AN Thanks AN AN Alex AN __ Od: Black, Michael (IS) Komu: General Discussion of SQLite Database Datum: 27.12.2011 23:36 Předmět: Re: [sqlite] 3.7.9 amalgamation file in VS2005 AN I duplicated your problem on C++ 2005 Express and C++ 2008 Express. AN C++ 2010 Express does the syntax highlighting correctly (or at least a lot better). AN Can you upgrade? AN Michael D. Black AN Senior Scientist AN Advanced Analytics Directorate AN Advanced GEOINT Solutions Operating Unit AN Northrop Grumman Information Systems AN AN From: sqlite-users-boun...@sqlite.org AN [sqlite-users-boun...@sqlite.org] on behalf of Alexandr Němec [a.ne...@atlas.cz] AN Sent: Tuesday, December 27, 2011 8:55 AM AN To: sqlite-users@sqlite.org AN Subject: EXT :[sqlite] 3.7.9 amalgamation file in VS2005 AN Dear all, AN I have one question that is not strictly a SQLite question AN (sorry), but maybe someone encountered this problem and found the AN solution. I have upgraded from an older SQLite release to 3.7.9 AN and loaded the amalgamation file into a Visual Studio 2005 AN project. But the syntax code highlighter does not behave correctly AN with the 3.7.9 amalgamation file because it greys out not only the AN sections of code that are not compiled at all (because of AN if(n)def's) but also other sections that DO compile. Well, I think AN that this is a bug of the VS 2005 syntax highlighter for such a AN large source file, because the file compiles ok, but working with AN such a file in VS 2005 is frustrating. AN Did anyone see (did anyone find a solution for) this problem? I AN have not seen this for older versions of the amalgamation file. AN Best regards AN Alex AN ___ AN sqlite-users mailing list AN sqlite-users@sqlite.org AN http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users AN ___ AN sqlite-users mailing list AN sqlite-users@sqlite.org AN http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.7.9 amalgamation file in VS2005
VS2010 has the same problem. The file is simply too big for it. I'm not a fan of the amalgamation because of this. It's essentially impossible to trace into the SQlite code now. That said, it compiles and works fine. C Tuesday, December 27, 2011, 10:50:14 AM, you wrote: JJD In my experience I find that most syntax highlighters fail JJD on occasion, especially with large files. Trust the compiler not the JJD editor. JJD 2011/12/27 Alexandr Němec a.ne...@atlas.cz Dear all, I have one question that is not strictly a SQLite question (sorry), but maybe someone encountered this problem and found the solution. I have upgraded from an older SQLite release to 3.7.9 and loaded the amalgamation file into a Visual Studio 2005 project. But the syntax code highlighter does not behave correctly with the 3.7.9 amalgamation file because it greys out not only the sections of code that are not compiled at all (because of if(n)def's) but also other sections that DO compile. Well, I think that this is a bug of the VS 2005 syntax highlighter for such a large source file, because the file compiles ok, but working with such a file in VS 2005 is frustrating. Did anyone see (did anyone find a solution for) this problem? I have not seen this for older versions of the amalgamation file. Best regards Alex ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size
Hello Richard, When does the WAL get trimmed down? If my transaction completes, does the WAL file get deleted? I've seen the WAL grow to 4 times my actual DB size so, I don't really want 160 Gigs of WAL hanging out when the process that generated it is only run once a month. Wednesday, November 30, 2011, 10:02:34 AM, you wrote: RH On Wed, Nov 30, 2011 at 4:41 AM, Sreekumar TP sreekumar...@gmail.comwrote: RH Because we have experimentally determined that it is faster to overwrite an RH existing file than to append to a file. So we don't truncate the WAL file RH on a checkpoint, which makes subsequent writes to the WAL file go faster. RH SQLite will truncate the WAL file down to the size specified by the PRAGMA RH journal_size_limit setting, if you have set the journal_size_limit. -Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size
Hello Richard, Appreciate the reply but, it doesn't address my question (unless I'm missing something). I get that checkpointing doesn't reduce the size of the WAL. Assuming a single writer and no readers, If I perform a transaction that generates a WAL file. Does it eventually get deleted when I finish my transaction? How about when I close the DB? I haven't updated to the most current SQlite because I was unclear about how persistent the WAL files actually were. Some of my DB's are 30 GB+. I don't mind the WAL's growing as long as they go away when the process is finished. Wednesday, November 30, 2011, 1:10:50 PM, you wrote: RH On Wed, Nov 30, 2011 at 1:04 PM, Teg t...@djii.com wrote: Hello Richard, When does the WAL get trimmed down? If my transaction completes, does the WAL file get deleted? I've seen the WAL grow to 4 times my actual DB size so, I don't really want 160 Gigs of WAL hanging out when the process that generated it is only run once a month. RH SQLite starts writing the WAL file from the beginning again on the first RH write transaction that follows a checkpoint that ran to completion and RH where there are no readers using the WAL file. A checkpoint will normally RH run to completion if there are no readers still using prior transactions. RH If there are readers on prior transactions, then the checkpoint cannot run RH to completion since that would delete content out from under the readers. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size
Hello Richard, That's what I was hoping to hear. Thanks, Wednesday, November 30, 2011, 2:01:46 PM, you wrote: RH On Wed, Nov 30, 2011 at 1:39 PM, Teg t...@djii.com wrote: Hello Richard, Appreciate the reply but, it doesn't address my question (unless I'm missing something). I get that checkpointing doesn't reduce the size of the WAL. Assuming a single writer and no readers, If I perform a transaction that generates a WAL file. Does it eventually get deleted when I finish my transaction? How about when I close the DB? RH The wal file persists until the last connection to the database closes, RH then the wal file is deleted. Meanwhile, the wal file is recycled (meaning RH that SQLite starts reusing it again from the beginning) after each RH successful checkpoint. Checkpoints are automatically run by transactions RH that cause the size of the wal file to exceed 1000 pages (though this can RH be tuned or disabled if desired). RH For most applications, WAL mode just works and you don't have to think RH about how. I haven't updated to the most current SQlite because I was unclear about how persistent the WAL files actually were. Some of my DB's are 30 GB+. I don't mind the WAL's growing as long as they go away when the process is finished. Wednesday, November 30, 2011, 1:10:50 PM, you wrote: RH On Wed, Nov 30, 2011 at 1:04 PM, Teg t...@djii.com wrote: Hello Richard, When does the WAL get trimmed down? If my transaction completes, does the WAL file get deleted? I've seen the WAL grow to 4 times my actual DB size so, I don't really want 160 Gigs of WAL hanging out when the process that generated it is only run once a month. RH SQLite starts writing the WAL file from the beginning again on the first RH write transaction that follows a checkpoint that ran to completion and RH where there are no readers using the WAL file. A checkpoint will normally RH run to completion if there are no readers still using prior transactions. RH If there are readers on prior transactions, then the checkpoint cannot run RH to completion since that would delete content out from under the readers. -- Best regards, Tegmailto:t...@djii.com -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size
Since we're on this topic If I open a 30 GB DB file, then attach a new empty DB and then select/insert all the data from the full DB to the empty one, the WAL file grows to 4 times the size of the source file. Why is that? I don't believe I use any manual checkpointing. Just let WAL do it's thing. Wednesday, November 30, 2011, 4:36:00 PM, you wrote: SS On 30 Nov 2011, at 7:01pm, Richard Hipp wrote: The wal file persists until the last connection to the database closes, then the wal file is deleted. SS Actually this is the key to a lot of questions about this. If SS your WAL file is taking up too much space, quit and restart your SS app. Or have your app close and reopen the database connection. SS It's a little more complicated in multi-user or multi-process situations, of course. SS Simon. SS ___ SS sqlite-users mailing list SS sqlite-users@sqlite.org SS http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size
Hello Richard, Thanks again. Wednesday, November 30, 2011, 5:37:32 PM, you wrote: RH On Wed, Nov 30, 2011 at 5:31 PM, Teg t...@djii.com wrote: Since we're on this topic If I open a 30 GB DB file, then attach a new empty DB and then select/insert all the data from the full DB to the empty one, the WAL file grows to 4 times the size of the source file. Why is that? I don't believe I use any manual checkpointing. Just let WAL do it's thing. RH The WAL file won't get so big if you break the transfer up into multiple RH transactions. RH Or, you can temporarily disable WAL mode for certain unusual operations RH (like this one) for which is not well suited. Wednesday, November 30, 2011, 4:36:00 PM, you wrote: SS On 30 Nov 2011, at 7:01pm, Richard Hipp wrote: The wal file persists until the last connection to the database closes, then the wal file is deleted. SS Actually this is the key to a lot of questions about this. If SS your WAL file is taking up too much space, quit and restart your SS app. Or have your app close and reopen the database connection. SS It's a little more complicated in multi-user or multi-process situations, of course. SS Simon. SS ___ SS sqlite-users mailing list SS sqlite-users@sqlite.org SS http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with SQLite when used with MPI
Hello Gaurav, What does a debugger show you? C Tuesday, November 22, 2011, 2:07:38 AM, you wrote: GV Here is the situation. I am using a cluster on which you can use MPI to GV split the processing time on to different processors. I was using GV PostgreSQL but since the data was on different server than the one I am GV running the code, it was slowing down the simulation due to connection GV overhead on PostgreSQL server. Then I switched to on-disk management GV software and so I chose SQLite. I first ran it without MPI on server, and GV it ran (compile g++). Then I switched to MPI (mpicc compiler), it is not GV even running for one node (on which I am using one thread). It gives error GV Database or Disk is full. Further debugging revlealed that sqlite3_step() GV was returning 1 and that means SQL error or Database missing. It was GV able to get the number of columns with the same database and SQL querry. I GV dont understand why I am getting this error. GV Gaurav GV ___ GV sqlite-users mailing list GV sqlite-users@sqlite.org GV http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and local UNC filenames
What I've seen is that windows makes a local copy of the memory mapped file. Perhaps in swap. If you watch your application using ProcMon you can see all the disk IO. That's how I noticed the copy. For my usage, only one process opens the DB so, maybe it's a non-issue. C Tuesday, November 8, 2011, 10:05:17 PM, you wrote: PI On Mon, Nov 7, 2011 at 5:48 PM, Jean-Christophe Deschamps PI j...@antichoc.net wrote: Pavel, This is not a local file. Even if you use your hostname as netname file is still retrieved through network stack. And I guess SAMBA doesn't work well with memory mapped files (in addition to all problems with locking). True but why did I get no error? Since MMF don't cope with network it would be good to get a no-no somewhere. I really don't know which layer should bark however. PI I can't answer this question. Maybe it's something like all operations PI are successful but memory mapping is not actually shared between PI processes. So every process sees its own copy and assumes that no PI other process works with database, thus database can be corrupted in PI many possible ways. PI Pavel PI ___ PI sqlite-users mailing list PI sqlite-users@sqlite.org PI http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] does sqlite3_reset have a performance impact onqueries?
I'd like this clarified too. I specifically don't use transactions when I'm selecting. In fact, I'll select, then start a transaction later for inserting the results. Would I be better off wrapping the whole thing in a transaction? Wednesday, November 2, 2011, 9:13:20 AM, you wrote: BMI Maybe my memory is fading but this is the first time I've heard BMI anybody say the wrapping a BEBIN around a SELECT was needed. I'd BMI swear it was always said it wasn't ever needed. From the docs BMI http://www.sqlite.org/lang_transaction.html BMI basically, any SQL command other than BMI SELECThttp://www.sqlite.org/lang_select.html) will BMI automatically start a transaction if one is not already in effect BMI Now you're saying SELECT always starts a transaction? BMI Michael D. Black BMI Senior Scientist BMI Advanced Analytics Directorate BMI Advanced GEOINT Solutions Operating Unit BMI Northrop Grumman Information Systems BMI ___ BMI sqlite-users mailing list BMI sqlite-users@sqlite.org BMI http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond
Hello ChingChang,
How big is tempString? What kind of type is it? I wonder if you're
trashing the stack with the sprintf.
What happens if you replace all the sprinfs with some simple inserts
inserted directly into vector?
dbStatements.push_back(INSERT INTO...);
You need to simplify your test till you figure out what's wrong.
C
Tuesday, November 1, 2011, 7:09:09 PM, you wrote:
CH Do you know why it goes to core dump?
CH ChingChang
CH The source code is shown as below,
CH vectorstring dbStatements;
CH dbStatements.push_back( BEGIN TRANSACTION; );
CH for ( int x = 0; x 10; x++ ) {
CH sprintf( tempString,
CH update utilization_table set
CH utilization=%5.2f,sample=%d where slot='0' and device='cavium' and
CH resource='bus' and sample='%d';,
CH ntohd(msg-bus_util[x]),
CH x,
CH x );
CH dbStatements.push_back( tempString );
CH sprintf( tempString,
CH update utilization_table set
CH utilization=%5.2f,sample=%d where slot='0' and device='cavium' and
CH resource='icache' and sample='%d';,
CH 100.00-ntohd(msg-inst_hit_rate[x]), // Convert to misses
CH x,
CH x );
CH dbStatements.push_back( tempString );
CH sprintf( tempString,
CH update utilization_table set
CH utilization=%5.2f,sample=%d where slot='0' and device='cavium' and
CH resource='dcache' and sample='%d';,
CH 100.00-ntohd(msg-data_hit_rate[x]), // Convert to misses
CH x,
CH x );
CH dbStatements.push_back( tempString );
CH }
CH dbStatements.push_back( COMMIT; );
CH // populate the DB
CH vectorstring::iterator dbStatementsIter;
CH SqlQuery oper_db(operDatabase, __FILE__, __LINE__);
CH for ( dbStatementsIter = dbStatements.begin(); dbStatementsIter
CH != dbStatements.end(); dbStatementsIter++ ) {
CH oper_db.execw( *(dbStatementsIter) );
CH }
CH dbStatements.clear();
CH The core dump is shown as below.
CH #0 0x32e94b04 in raise () from /lib/libc.so.6
CH #1 0x32e962f4 in abort () from /lib/libc.so.6
CH #2 0x32e8c2a4 in __assert_fail () from /lib/libc.so.6
CH #3 0x32ae60cc in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
CH #4 0x32b4c324 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
CH #5 0x32ba12c0 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
CH #6 0x32b7926c in sqlite3_step () from /ovn/lib/libsqlite3.mgmt-crd.so
CH #7 0x32b7a2c4 in sqlite3_exec () from /ovn/lib/libsqlite3.mgmt-crd.so
CH #8 0x329a9630 in SqlQuery::execw () from /ovn/lib/libPlatform.so
CH #9 0x329a98e8 in SqlQuery::execw () from /ovn/lib/libPlatform.so
CH #10 0x10010290 in NpuMessageHandler::processUtilReport
CH (this=value optimized out, msg=value optimized out,
CH nbytes=value optimized out) at cavium_driver.cpp:1387
CH #11 0x10012808 in NpuMessageHandler::run (this=0x38be1008) at
cavium_driver.cpp:954
CH #12 0x328a65b0 in Thread::start_thread () from
/ovn/lib/libCommon.mgmt-crd.so
CH #13 0x3278b5cc in ?? () from /lib/libpthread.so.0
CH #14 0x32f39b88 in clone () from /lib/libc.so.6
CH ___
CH sqlite-users mailing list
CH sqlite-users@sqlite.org
CH http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite on Windows 64bit
As a programmer, I just build my own 64 bit version of Sqlite. You should try it, it's easy. Just a matter of selecting x64 when I compile. For my customers, 64 bit installs now outnumber 32 bit. Sqlite works just fine... C Saturday, October 29, 2011, 8:03:55 AM, you wrote: AO Yes, compiling to 32-bit is nice. It is like dancing at the tunes of Earth AO Wind and Fire, h, those were the days. Like the old good songs, the AO 32bit apps are a good memory, many people like it, but they are a thing of AO the past. We are developers and anyone of us who stays behind, is left AO behind. Not having the option of SQLite-64 is a major drawback and will AO leave us behind. So, come on folks, pull up your socks and let's get the AO SQLite apps into the 21st century, shall we? AO AO ___ AO sqlite-users mailing list AO sqlite-users@sqlite.org AO http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How about a proper forum rather than an e-mail list
Hello Frank, I love forums and consider them far superior to email if only because it's easier to follow a topic with less quoting needed. The downside is that someone has to manage the forum. I've managed a forum for the past 10 years and there's a daily spam cleanup process and constant attacks and required upgrades. You have to set the tone and be pretty ruthless about flaming too. I'd like to see a forum. I just wouldn't want to manage it. C Tuesday, October 18, 2011, 5:32:46 AM, you wrote: FM I think that the sqlite-users e-mail list has enough traffic to warrant a FM proper forum. FM Has this been considered? FM FM A proper forum also can contain several subject forums, e.g. FM USER FORUMS: FM Announcements FM General FM Help with SQL FM News FM DEVELOPERS CORNER: FM News FM General FM OS specific FM . FM FM There is a quite widespread, free Forum software that could be used: FM http://www.simplemachines.org/ FM http://en.wikipedia.org/wiki/Simple_Machines_Forum FM FM They support MySQL, SQLite and PostgreSQL as the underlying database. FM FM Well, just a thought. FM FM FM /Frank Missel FM ___ FM sqlite-users mailing list FM sqlite-users@sqlite.org FM http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Internal v. External BLOBs
My experience is that having larger blobs impacts performance too. It seems like SQlite struggles to seek over the blobs when traversing non-blob containing tables. I haven't characterized it beyond that. It might even be disk caching. I tend to keep my blobs in a different DB file than the tables that describe the blob contents though because of this. I have some 30 GB blob containing files. I use these DB's just for organization of the blobs. Performance wasn't that important. Wednesday, September 21, 2011, 5:29:16 AM, you wrote: AP There is a problem with access to file in directory with big amount of files. AP FS directory indicies are not really good. I did test 100 millions of 1k files AP in SQLite and results were better than reading from set of directories in FS. AP But for files about 1 Mb and more the SQLIte performance is not good. AP Is any reason why SQLite big blobs reading may be slowly? The AP performance of the BLOBS may limit performance of FTS and AP other custom storage/index realizations (Spatialite, etc). And it's more AP important I think. Especially when we need FTS index as fast hash index. AP 2011/9/21 Richard Hipp d...@sqlite.org: If you are storing large BLOBs in SQLite, can you read them faster if they are stored directly in the database file, or can you get to them quicker if you store just a filename in the database and read the BLOB content from a separate file? We did some experiments to try to answer this question, and the results seemed interesting enough to share with the community at large. Bottom line: On Linux workstations, it is faster to store BLOBs in the database if they are less than about 100KB in size, and faster to store them in a separate file if they are larger than about 100KB. This is on Ubuntu with EXT4 and a fast SATA disk - your mileage may vary with different operating systems, filesystems, and hardware. The complete report is here: http://www.sqlite.org/intern-v-extern-blob.html -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strategies for protecting sql queries
PI If someone was ambitious enough they could attach a debugger to your PI application, extract key used to decrypt the database and obtain all PI the sensitive information you have. So if you really want to protect PI your database against those who is able to attach a debugger you PI shouldn't use SQLite. PI Pavel This is the key. It depends on how secure you need it to be. If it really needs to be secure then having the encryption key in memory or in the application means it's not all that secure. This isn't a limitation of SQLite though. It's a limitation of running a program on someone else's hardware. You could encrypt the SQL and then decrypt right before usage then clear it from memory. Won't stop a debugger but, it will stop them from pulling the strings out of your application directly. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite + unicode
Hello NOCaut, I convert everything to UTF-8 for insert and then back to ascii or unicode when I pull the data from the DB. C Thursday, August 11, 2011, 4:20:36 AM, you wrote: N I now how work with sqlite guys N my problem: in const char * and i wont wchar_t*. becouse wchar_t* - N unicode type understand N int sqlite3_exec( N sqlite3*, /* An open database */ N const char *sql, /* SQL to be executed */ N sqlite3_callback, /* Callback function */ N void *, /* 1st argument to callback function */ N char **errmsg /* Error msg written here */ N ); Nsqlite3 *db; N char *zErrMsg = 0; N int rc; N rc = sqlite3_open(c:\\test.db, db); N N rc = sqlite3_exec(db, Select * from table , callback, 0, zErrMsg); N this code return char I want use wchar_t* for read unicode. N Thanks! -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read only scaling optimization
Hello Drew, Why multiple threads? What kind of performance do you get if you only use a single thread? Is it one thread per database perhaps? C Tuesday, August 9, 2011, 4:46:13 PM, you wrote: DK We have an application that does not scale well especially 8 and 16 DK threads we are getting about 10% at best to -50% at worst (2 and 4 are okay DK aprox. 40-50%). DK We have narrowed the problem down to the SQLite code. DK Looking for best practices from anyone that overcame scaling issues. DK First we generate 35 different SQLite databases, this is done in a separate DK program and performance is not an issue. DK The application is a C++ app. that accesses all the databases in a read DK only style. Size of database range from 1MB to 10GB. DK Each thread does the following in a loop DK 1. Grab a record from an external system (not SQLite) DK 2. Runs several SQLite queries to process the record DK We are currently using 3.6.14 (has this been improved in new versions?) DK Is there any compile time options or pragma's that are specifically DK engineered at optimizing for read only and/or scaling? DK Thank you in advance, DK Drew DK ___ DK sqlite-users mailing list DK sqlite-users@sqlite.org DK http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deep copy of 2 sqlite3*?
Hello Nikki, I'd suggest either not doing it (prevent the copy constructor from compiling by declaring it private) or doing it by passing ownership of the connection around as if it was a socket or file handle. That's how I do sockets. If my socket class gets copy constructed to a new class, that class becomes the owner of the socket and no further socket operations are permitted in the original class. The problem with opening a new connection in the new class constructor is that it's not really in the same state as the original connection. C Tuesday, August 2, 2011, 10:20:21 PM, you wrote: NT Hi sqlite-users! NT It's just that I'm writing a copy constructor of my own mini sqlite NT wrapper, and I ended up having to do a deep copy of a sqlite3* points to a NT SQLite connection. And I'm just wondering is it okay that I just do it with NT memcpy(), by digging into the code for the definition of struct sqlite3 and NT count the total bytes of this struct? NT Any ideas or suggestions are appreciated! NT ___ NT sqlite-users mailing list NT sqlite-users@sqlite.org NT http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Field drop work around
Hello Jack, I just migrate the tables forward. Generate a new one in my program and do a bulk insert from the old to the new, then drop the old one. At other times, I've just re-purposed the unused field. C Tuesday, August 2, 2011, 7:03:52 AM, you wrote: JH Hello all, JH Is there a workaround for the lack of support for dropping JH fields? I have an application and need to evolve the database JH schema as features are added and removed. Leaving fields sitting JH inside the database that are no longer used will lead to a schema JH that is difficult to understand. Especially so when the field is JH marked as NOT NULL. Years from now I will need to supply a value JH for a field when inserting a row that has long ago ceased to be used. JH Any ideas how I can remove unused fields from the database would be appreciated. JH Regards, JH Jack Hughes JH ___ JH sqlite-users mailing list JH sqlite-users@sqlite.org JH http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3.dll for Win 64
Hello Everton, EV Don't have any easy way to make this dll? There's a lot of 64bits systems EV out there that will need this dll. I just compile it into my program and don't bother with DLL's. I find that DLL's in general make my software less reliable so, I avoid them. Static linking all the way C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selected string differs to Inserted one
Hello Andrew, I convert all my strings the UTF-8 before insert or selecting. You probably need to look into something like that too. Filenames for the DB files have to be UTF-8 too or you'll have problems opening files sometimes. My test folder has an umlaut in the path so, this code gets exercised every time I run my program. C Sunday, July 24, 2011, 10:39:51 AM, you wrote: AL Dear All AL AL I am very new to SQLite and am trying to convert a Windows Forms AL C#.NET (VS2010 SP1) application from an Access database to an AL SQLite one. All seems to have gone extremely well but I have come AL across one problem that has held me up for several days now. AL AL I have a table with a TEXT field that does not return the same AL string that was inserted. The character that misbehaves is Greek AL letter Ø. In fact any character from the high end of the ANSII AL or ASCII table shows the problem. AL AL Since I have done nothing special in creating the database I AL believe it is encoded UTF-8 by default. I assume that my inserted AL string is similarly encoded UTF-8 since I have done nothing AL special in my C# code to change this. The data in the database AL seems to be correct since the SQLite Administrator program correctly displays the data. AL AL Why does the Selected data come back wrong, and how can I correct AL this. Clearly this is a bit of a non-problem otherwise every AL other .NET user would be screaming but I have searched for several AL days now without finding a solution. AL AL Can anyone help me please? AL Thanks in advance AL ~A AL Andrew Leeder AL AL . AL AL ___ AL sqlite-users mailing list AL sqlite-users@sqlite.org AL http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA bug in 3.7.7 (but fine in 3.7.6.3)
I had to back away from 3.7.7 too because my program hung during
startup. Wonder if it's related to this? I haven't looked any deeper
at it. It hung, I switched back to my older library and it worked.
C
Sunday, June 26, 2011, 3:59:33 PM, you wrote:
GS Hey all,
GS There is a bug in 3.7.7 that affects Apache Subversion. We've verified
GS that it is this latest release, as prior releases have worked just
GS fine for us.
GS Below is the reproduction script, thanks to Daniel Shahaf. With this
GS pragma not working, we cannot even start up :-(
GS Please help!
GS Thanks,
GS -g
GS ps. maybe this belongs on -dev, but I'm pending on its moderated
subscription...
GS [[[
GS #include assert.h
GS #include stdio.h
GS #include sqlite3.h
GS #define BUSY_TIMEOUT 1
GS int main(void)
GS {
GS sqlite3 *db3;
GS const char *path = foo.db;
GS int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
GS #ifdef SQLITE_OPEN_NOMUTEX
GS flags |= SQLITE_OPEN_NOMUTEX;
GS #endif
GS assert(SQLITE_OK == sqlite3_open_v2(path, db3, flags, NULL));
GS assert(SQLITE_OK == sqlite3_busy_timeout(db3, BUSY_TIMEOUT));
GS assert(SQLITE_OK == sqlite3_busy_timeout(db3, BUSY_TIMEOUT));
GS {
GSchar *errmsg;
GSint err = sqlite3_exec(db3, PRAGMA case_sensitive_like=1;, NULL,
GS NULL, errmsg);
GSif (err != SQLITE_OK)
GS printf(Error %d: %s\n, err, errmsg), sqlite3_free(errmsg);
GS }
GS return 0;
GS }
GS ]]]
GS ___
GS sqlite-users mailing list
GS sqlite-users@sqlite.org
GS http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Do I need to migrate to MySQL?
We use MySQL on a fairly high end PC server running Linux. The database is about 300-400 gigs. Front end of PHP and apache. It's been reliable for a couple years now and seems to have a bunch of headroom. I wrote a wrapper so my SQLite code (with minor modification) can feed MySQL instead of SQlite. In fact, the thing that feeds the MySQL DB reads Sqlite DB's and then feeds this data into MySQL every 15 minutes or so. I'd have no problem recommending MySQL for mission critical uses. Postgres might be better but, MySQL's been good enough. C Friday, June 3, 2011, 2:19:44 PM, you wrote: DD MySQL should be avoided like the plague. Use Postgres instead if you have to DD switch to a larger SQL DBMS. But hopefully the help you've gotten so far will DD extend your mileage with SQLite and you won't have to switch to anything yet. -- DD Darren Duncan DD Ian Hardingham wrote: Guys, the server for this game - http://www.frozensynapse.com uses SQLite. We've had an unexpectedly successful launch which has resulted in the server being swamped with players, and I'm trying to optimise everywhere I can. I've always been under the impression that SQLite is pefectly fast and it's the scripting language I wrote the server in which is too blame. (Yes, I know writing a back-end in a single-threaded scripting language is an absolutely terrible idea). However, everyone in the industry I talk to says that SQLite must be one of the problems. I may be looking at a complete re-write. I may also need to have a solution which scales beyond one machine. Can anyone give me advice on this matter specifically? (The video on that website at 2.04 gives a good idea of what kind of functions are being powered by the database). Thanks, Ian DD ___ DD sqlite-users mailing list DD sqlite-users@sqlite.org DD http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile error's on Visual Studio 2010
Hello Jan, Move it into it's own project, make it a static lib and turn down the warning level just for this project. That's what I do anyway. I'm not willing to touch the code but, that seems to be the only other solution. Sunday, May 8, 2011, 6:50:31 AM, you wrote: JB Hi, JB JB I am new to sqlite3 and just downloaded the code trying to test it from a JB C++ application using Visual Studio 2010. I just created a class and JB included the sqlite3.c and sqlite3.h from amalgamation-3070602 directly. JB JB The header compiles fine, but on the sqlite3.c I get typecast errors. This JB is because my compiler is set to a high warning level and sqlite3 code does JB things like converting from void* to struct pointers. JB JB Is there any way I can get around this without lowering the compiler's JB warning level? JB JB Jan JB ___ JB sqlite-users mailing list JB sqlite-users@sqlite.org JB http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile error's on Visual Studio 2010
Hello Jay, I haven't found this to be the case. I have numerous C only library's I compile and I don't have to change the defaults to compile them. There is an option to force C++ compiles but, I don't believe it's on by default. JAK In most default setups, Visual Studio insists on compiling .c files JAK with the C++ compiler, even though-- as this very example points JAK out-- the languages are different enough that this behavior seems JAK questionable. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to reuse a prepared statement
I got bit by this the other day. I typically just used reset but, if you have a case where you're looping through an insert and you don't always set all values (say a string is empty sometimes so, you forgo the bind), the previous value set will get inserted where you expected blanks to go. Clearly, it was my fault because I assumed reset cleared the old values. I've since changed my wrapper to clear with each reset. If I understand things correctly, you call sqlite3_reset() to reuse a prepaired statement, why do you call sqlite3_clear_bindings()? IT You don't have to. Personally, I've never once found a use for it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3 memory leaks in my c++ dll application
Hello Khanh,
Until you confirm the memory leak, I doubt anyone will respond. For
example, when my program exits, I abandon a bunch of allocated memory
to speed exit. VC says that's a memory leak but, it's not, because
it's not leaking memory while I'm using it. I purposely abandon
allocated memory on exit to speed exit processing (deallocating 2 gigs
of RAM when I'm exiting anyway is pointless).
The question is whether it's leaking RAM while you use it, not if it
leaks it when you exit (which is when VC checks).
C
Wednesday, April 20, 2011, 12:00:57 PM, you wrote:
KN Hi SQLite3 community,
KN Please forgive me this panic, incorrect bug report. The cause of memory
KN leaks was from another my software component. The SQLite3.dll is working
KN greatly with my solution. However, it was memory leaks detection from VS2008
KN when I built the SQLite3 with Sqlite3.c in my solution.
KN If you know about this, please give me some hints on how to set flags in my
KN database module(vcproj) to eliminate the memory leaks. I would greatly
KN appreciate your kindness.
KN The database vcproj DLL built with VS2008 C++ with this flag: Multi-threaded
KN Debug DLL (/MDd). Accessing the DLL via GetProcAddress() calls.
KN Sincerely and Thank you,
KN Khanh
KN On Mon, Apr 18, 2011 at 8:56 AM, Khanh Nguyen kn95...@gmail.com wrote:
Hi,
My name is Khanh Nguyen and currently using SQLite3.DLL 3.7.6 in my
application (DLL built with VS2008 C++ with this flag: Multi-threaded Debug
DLL (/MDd). This DLL is called by other DLL via GetProcAddress to access a
blob database. The access and blob database is in very good operation.
However, whenever I exited the application, the VS2008 debugger reported
this memory leak dectection:
The DLL has some memory leaks that I have captured here:
The thread 'Win32 Thread' (0x368) has exited with code 0 (0x0).
Detected memory leaks!
Dumping objects -
{8390} normal block at 0x01364C70, 64 bytes long.
Data: c:/DEV/Platform/ 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D 2F
{8382} normal block at 0x0138C9C0, 32 bytes long.
Data: 7Zl7ji/F9x+bOgbG 37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62 47
{8380} normal block at 0x0138B938, 32 bytes long.
Data: PolicyDataPack.i 50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E 69
{8377} normal block at 0x01391F48, 448 bytes long.
Data: 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD CD
{7174} normal block at 0x01391DC8, 64 bytes long.
Data: c:/DEV/Platform/ 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D 2F
{7166} normal block at 0x013621D8, 32 bytes long.
Data: 7Zl7ji/F9x+bOgbG 37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62 47
{7164} normal block at 0x01386690, 32 bytes long.
Data: PolicyDataPack.i 50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E 69
{7161} normal block at 0x01390430, 448 bytes long.
Data: 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD CD
{7108} normal block at 0x0138CDC8, 64 bytes long.
Data: c:/DEV/Platform/ 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D 2F
{7100} normal block at 0x01386320, 32 bytes long.
Data: ENt5KkYHRqeU/iLR 45 4E 74 35 4B 6B 59 48 52 71 65 55 2F 69 4C 52
{7098} normal block at 0x0138BCD8, 32 bytes long.
Data: PolicyDataPack.b 50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E 62
{7095} normal block at 0x0138D530, 448 bytes long.
Data: 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD CD
{7033} normal block at 0x013796D8, 64 bytes long.
Data: c:/DEV/Platform/ 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D 2F
{7025} normal block at 0x0136DED0, 32 bytes long.
Data: 5U/jyx2txHeUQUe/ 35 55 2F 6A 79 78 32 74 78 48 65 55 51 55 65 2F
{7023} normal block at 0x013658D0, 32 bytes long.
Data: NetworkSetting3. 4E 65 74 77 6F 72 6B 53 65 74 74 69 6E 67 33 2E
{7020} normal block at 0x01389BF0, 448 bytes long.
Data: 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD CD
Object dump complete.
The program '[5432] DebugConsole.exe: Native' has exited with code 0 (0x0).
Please help me resolve this memory leak issue.
Sincerely,
Khanh
KN ___
KN sqlite-users mailing list
KN sqlite-users@sqlite.org
KN http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: database collision detection
Hello Eugene, How about initally attempting the fetch and if that fails, do the insert? Since the results of a duplicate means you want the two additional blobs anyway. Select so and so on empty results Insert so and so. C Friday, March 11, 2011, 9:15:14 AM, you wrote: EN Hello EN I am just beginning my thorn-ridden way up the steep slope with SQLite and EN C++ to keep me company. And i am stuck at one point, so if you could help me EN to continue it would be awsome. EN I have a database contaning a table with a layout like this: EN NAME PART1 PART2 EN blobblob blob EN blobblob blob EN ......... EN I am thinking of creating it like: EN CREATE TABLE points (NAME blob unique, PART1 blob, PART2 blob); EN Then, in a loop, i will obtain new blob tuples and insert them in my table: EN INSERT INTO points VALUES(?,?,?); // using prepare statement and bind EN blob EN Then comes a tricky bit: EN 1) If there is a tuple with same NAME ( SQL error: column name is not EN unique) already, EN 2) I want to fetch its components PART1 and PART2 EN SELECT PART1, PART2 FROM points WHERE NAME = ?; EN // using prepare statement and bind blob EN The problem is, i dont know how to *DETECT* a collision gracefully. EN I first thought of looking at an error code, but i am not sure its a good EN idea. Then EN I heard somewhere about a thing called TRIGGERS, but still i am not sure, if EN thats EN the easiest way. EN So, basically, how do you detect duplicates and handle them how you consider EN appropriate? EN Thanks, EN Eugene EN ___ EN sqlite-users mailing list EN sqlite-users@sqlite.org EN http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error: disk I/O error on big databases vacuuming
Hello Alexey, Tuesday, March 8, 2011, 11:51:22 AM, you wrote: AP I try to vacuum database about 11Gb size on debian squeeze host with 1,5 Gb RAM: AP sqlite3 test.db 'vacuum;' AP Error: disk I/O error AP Note: any new files does not created on vacuuming process (may be AP created journal, does not it?). AP But this work correct: AP sqlite3 test.db '.dump'|sqlite3 test2.db On my windows 7 box using current Sqlite, vacuuming anything over about 7 gigs seems to be hit or miss. I haven't pursued it. I just dump the DB's to SQL and re-generate them, like you do. It's far faster than vacuum. Never have below 100 gigs free when I've tested this. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COUNT() extremely slow first time!
Hello Greg, I found this to be the case too. The difference between 1K and 8K is staggering. I default all my windows DB's to 8K now. Tuesday, February 22, 2011, 1:59:29 PM, you wrote: GB I'm currently dealing with a similar issue. I've found that the page_size GB PRAGMA setting can have a dramatic effect on how long it takes to warm up GB the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column) GB takes 40.2 seconds. page_size=4096 takes 15.5 seconds. page_size=8192 takes GB 8.5 seconds. This was done with a reboot between each test. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected
Hello Thomas, I download off the net at between 400Mbps to 1 Gbps and have to pack the data away into a Sqlite DB. I use an intermediate buffer on disk and a background thread to write the data to the DB. Obviously, I couldn't keep up if the data was continuous but, the lulls in between the peak data rate times give me time to pack it away. Even if I get an extended burst of data, it'll eventually make it in. If I was you, I'd start by having something service the interface and buffer the packet data and something else pulling the packet data from the buffer and feeding it to Sqlite. Then you won't be dropping packets. T Friday, February 11, 2011, 6:49:16 PM, you wrote: TF Hi, I have a small problem with a program I've developed. TF It basically captures packets from a network device on linux and stores TF transfer stats on each host seen. To store the stats long term I decided to TF use sqlite, and when the program saves the stats every few minutes, it takes TF about 4-5 seconds, and if I have the synchronous pragma turned off, it takes TF 1-2 seconds. TF These are the relevant sql commands: TF CREATE TABLE hosts (id INTEGER PRIMARY KEY,\ TF address INTEGER UNIQUE, \ TF first_seen INTEGER DEFAULT CURRENT_DATETIME, \ TF last_on INTEGER DEFAULT CURRENT_DATETIME, \ TF last_off INTEGER, \ TF rx_bytes INTEGER, \ TF tx_bytes INTEGER); TF UPDATE hosts SET last_on = ?, last_off = ?, rx_bytes = ?, tx_bytes = ? WHERE id TF = ? TF INSERT INTO hosts ( address, first_seen, last_on, rx_bytes, tx_bytes ) VALUES ( TF ?, ?, ?, ?, ? ) TF The stats saving code will interleave a bunch of UPDATEs and INSERTs, inside a TF single transaction, maybe that has something to do with it, I'm not sure. TF 1s to update them all isn't too bad, but it still can mean I'm potentially TF dropping packets, which I'd really rather not do. TF Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
Hello Nathan, I'd install Procmon set it to filter just on the problem file (and journal files) and let the software run. When you get an error, you should see the details listed in procmon. C Tuesday, February 8, 2011, 1:12:48 PM, you wrote: NB I haven't tried that, but if we stop the application then restart it, NB everything works again. That is until we get another Disk I/O error NB which happens the next day. NB I'm wondering if the virus scanner is blocking the database write. NB On 2/8/2011 1:03 PM, Simon Slavin wrote: On 8 Feb 2011, at 5:59pm, Nathan Biggs wrote: Once a day, not at the same time, we are getting a disk I/O error from our application using SQLite. If you try to duplicate the database file do you get any kind of error then ? If not, use the command-line tool to run an integrity check: http://www.sqlite.org/pragma.html#pragma_integrity_check Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users NB ___ NB sqlite-users mailing list NB sqlite-users@sqlite.org NB http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
Hello Nathan, It could be hardware but, my experience with my users is that anything weird like this is virus scanner of firewall related. You can suggest that they try telling the V scanner to ignore the folder your data resides in and see if it makes a difference. That's my normal suggestion to my users. Solves the problems most of the time. C Tuesday, February 8, 2011, 1:43:20 PM, you wrote: NB That was exactly my thought. But since it is at a customer's site, I NB have to prove that its their virus scanner that is causing the problems. NB On 2/8/2011 1:17 PM, Jim Morris wrote: Could a backup or virus scanning software be locking the database? On 2/8/2011 10:12 AM, Nathan Biggs wrote: I haven't tried that, but if we stop the application then restart it, everything works again. That is until we get another Disk I/O error which happens the next day. I'm wondering if the virus scanner is blocking the database write. On 2/8/2011 1:03 PM, Simon Slavin wrote: On 8 Feb 2011, at 5:59pm, Nathan Biggs wrote: Once a day, not at the same time, we are getting a disk I/O error from our application using SQLite. If you try to duplicate the database file do you get any kind of error then ? If not, use the command-line tool to run an integrity check: http://www.sqlite.org/pragma.html#pragma_integrity_check Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users NB ___ NB sqlite-users mailing list NB sqlite-users@sqlite.org NB http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3.h file for X64 Windows
I use the same header for both. It's so easy to build, I'm unclear why you went down this path as opposed to just building the lib from source. Monday, February 7, 2011, 7:57:53 AM, you wrote: IT Dileep dil...@codework-solutions.com wrote: Hi, I could not found Precompiled Binaries For Windows For X64 windows in http://www.sqlite.org/download.html I got 64bit version of SQLiteExpert From that I got Sqlite3.dll, I generated lib and Def files But I am not able to get sqlite3.h header file for X64 windows version of sqlite3. IT I don't see any reason why the same sqlite3.h wouldn't work for IT both Win32 and Win64. Have you tried it, and encountered a particular problem with it? -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LENGTH on a BLOB field stops at NUL byte
Hello Yves, You could alway mime/uu/yenc encode it into text before insert, and do the reverse when you retrieve it. Then the problem goes away. C Sunday, February 6, 2011, 10:53:05 AM, you wrote: YG On 06.02.2011 14:36 CE(S)T, Samuel Adam wrote: * You should be using bound parameters on INSERT. If you are not, change your code. This will eliminate a whole list of potential problems. YG I already do that. * Make sure the binding is done as BLOB and not TEXT. PDO probably has its own flags defined for this. This is the part that tells SQLite whether you are inserting TEXT or BLOB. YG There is a PDO method to execute a prepared statement with an array of YG values to be used as parameters. There is no way to specify additional YG information about how to interpret these values in this method. But YG there is another method to bind each value separately, and it has YG another argument to pass some data type. I'd need to change the way I YG execute my SQL statements to make use of it. YG I'd expect that SQLite known on its own what data type a column is and YG respect it. Seems like SQLite is sometimes more type-agnostic than PHP, YG where I take great care of data types in this special application. YG For now, I just won't save files to the database with SQLite but instead YG on disk. I won't get to rewriting the database class anytime soon but YG I'll look into it then. YG I'm wondering why I get all the data back but SQLite can't count its YG characters... And the image I get back from SQLite looks error-free so YG it probably didn't make a single mistake handling it as text data. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cause of disk I/O errors
Hello GHCS, Most common problem my users have is Virus scanners stepping on my writes. Might suggest they tell their Virus scanners to ignore your data folder. People do run out of disk space fairly frequently. I wouldn't discount it out of hand. C Sunday, January 30, 2011, 8:25:48 PM, you wrote: GS Are there any common reasons for encountering a disk I/O error (code GS 10) when creating records in an SQLite database on a Windows PC? My GS product uses V3.6.23.1 and I did not have extended error codes turned on GS in my releases up to now. GS The problem in reproducing this is that the users can be running XP, GS Vista, or Win7, and the amount and nature of the data being written is GS entirely dependent on data provided by the user and run through my GS processing program to create the database. When I've had someone upload GS their raw data to me so I can try it here, it works every time (naturally). GS So it seems it must be something about their particular machine GS environment, but what? I'm sure that nobody is running out of disk space GS these days. Any ideas on where to look or what to suggest to them that GS they can do? -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] REGEXP parameter order - embarassed newbie
Hello James630165, I guess it depends on how good a programmer you are. I just used the web site and some reference books on SQL. Wrote my own wrapper and improve it as time goes on and I need new features. C Sunday, January 23, 2011, 8:05:08 PM, you wrote: Jac JCC/DRH Jac Jac If a person speaks good English and has a logical mind, has previously Jac programmed in C ... why is there a need to buy The definitive guide to Jac programming SQLite? Jac Jac I understand that Everyone does. Jac Jac Jim. Jac ___ Jac sqlite-users mailing list Jac sqlite-users@sqlite.org Jac http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Embarassed Newbie
Hello Michael, Yeah, sqlite3.exe runs everywhere. This problem is more basic. Sqlite3.exe is in some other path and not in the command line search path. Where did you unzip the files? Either do the test from the folder you unzipped the files to or use full path C: C:\Unzipped\Sqlite3.exe test.db C Saturday, January 8, 2011, 9:12:10 AM, you wrote: BMI I just confirmed that the shell download is static. The 566,272 BMI size also indicates static compilation. BMI BMI There are no dependencies other than OS DLLs. BMI BMI c:\windows\system32\KERNEL32.DLL BMI c:\windows\system32\MSVCRT.DLL BMI c:\windows\system32\NTDLL.DLL BMI d:\SQLITE3.EXE BMI BMI Michael D. Black BMI Senior Scientist BMI Advanced Analytics Directorate BMI Northrop Grumman Information Systems BMI BMI BMI From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp BMI Sent: Sat 1/8/2011 7:10 AM BMI To: General Discussion of SQLite Database BMI Subject: EXTERNAL:Re: [sqlite] Embarassed Newbie BMI On Sat, Jan 8, 2011 at 8:06 AM, Peter BMI pe...@somborneshetlands.co.uk wrote: On 08/01/11 11:43, james630...@aol.com wrote: sqlite3 test.db I assume by 'binaries' you mean sqlite-shell-win32... and sqlite-dll-win32...? You need both to run the command you give above. BMI Is this true? I thought I was compiling sqlite3.exe as a standalone binary? BMI Does it really require that the DLL be downloaded too? If so, that is BMI something that needs to be fixed. Can you double-check this for me please. BMI -- BMI D. Richard Hipp BMI d...@sqlite.org BMI ___ BMI sqlite-users mailing list BMI sqlite-users@sqlite.org BMI http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] append sql file
Hello CDN, Why don't you embed it as a blob? Make a table just for your text and feed it into the DB? Seems like a better idea than appending to a DB file that might just extend itself right over your text with the next insert. C Monday, December 6, 2010, 12:53:19 PM, you wrote: CM Hi, CM was wondering if there was a way to have extra text on the end of an sql file, after the COMMIT; ? CM I'd like to combine two functions onto the one file, the text CM would be read by another programme, but how can I tell SQLite to ignore what's after the COMMIT CM mtia CM Mark CM ___ CM sqlite-users mailing list CM sqlite-users@sqlite.org CM http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Just compiled SQLite in Visual Studio
Hello Bob, In general, I guess that C++ is essentially the C language with a whole lot added. If you ignore the silly minutia then this is true. My application is a mix of C and C++ all compiled with VC10. C for legacy code, C++ for the stuff I write. Most of the time I have my C code partitioned into it's own library so, I can change the build environment specifically for that libary. C Wednesday, December 1, 2010, 11:26:39 AM, you wrote: BK Igor, BK BK You seemed to say that the only difference between C and C++ was BK the ending (.c versus whatever C++ uses), but that understanding BK of what you said was more a result of my ignorance of C and C++. BK Someone pointed out that, if I've gotten this right, in Visual BK Studio the ending controls how the written program is compiled. BK BK In general, I guess that C++ is essentially the C language with a BK whole lot added. I'm probably wrong on that interpretation of what BK was said, but at least I do now understand that C and C++ are not BK the same. Am I still misinterpreting this whole thread? BK BK Bob Keeland BK Forest Dynamics, Inc. BK --- On Tue, 11/30/10, Igor Tandetnik itandet...@mvps.org wrote: BK From: Igor Tandetnik itandet...@mvps.org BK Subject: Re: [sqlite] Just compiled SQLite in Visual Studio BK To: sqlite-users@sqlite.org BK Date: Tuesday, November 30, 2010, 8:06 PM BK Bob Keeland keela...@yahoo.com wrote: Oh, from the various replies I see that C++ is a more capable extension of C. Igor was only using a figure of speech, kind of. BK Everything I said in this thread so far, I meant quite literally. BK Which expression of mine do you take as a figure of speech? -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read database from multiple processes
Hello Prakash, What kind of drive is it? My experience is that SATA drives hate being banged on from multiple threads. They just can't handle multiple simultaneous work loads without slowing down drastically. They're fine with heavy duty single threaded IO. In my application, I serialize most heavy duty disk IO for this reason. If the drive is hanging off a USB interface, it's much worse. In your case, I might consider a server application that answers requests from the clients so, only one process is banging on the disk at a time. C Thursday, November 18, 2010, 4:21:04 PM, you wrote: PRB Hi, PRB I have a database placed on a shared drive. PRB Two processes (from different hosts) do a bunch of select commands. PRB I have a busy handler that sleeps of 1 second in each attempt PRB and bails out after 10 attempts. PRB The observation is that, if only one process is running (on any PRB host) the results are returned pretty fast. However, if both PRB processes are doing the selects concurrently the performance is dead slow. PRB Note that each process is doing somewhere around 5000 selects. PRB My understanding is that when only selects are happening there is no locking involved. PRB I am using 3.7.2. PRB Regards, PRB Prakash Bande PRB Altair Engg. Inc. PRB Troy MI PRB Ph: 248-614-2400 ext 489 PRB Cell: 248-404-0292 PRB ___ PRB sqlite-users mailing list PRB sqlite-users@sqlite.org PRB http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 5 GB DB with a 51 GB wal file.
I have a 5GB DB file, 5 simple tables. Several million entries. When I tried to delete perhaps 10% of the entries. The WAL file blew up to 51 GB, 10 times bigger than the DB file itself. I understand the problem I think, with long transactions and have since split the transactions up into smaller chunks. My question is, can I fix this DB? integrity_check says it's OK but, when I Pragma wal_checkpoint; from Sqlite.exe, nothing seems to happen. It completes and the WAL file is still 51 GB. I was expecting it to integrate the changes in the WAL file back into the DB file proper. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 5 GB DB with a 51 GB wal file.
Hello Teg, Thursday, November 11, 2010, 12:08:23 PM, you wrote: T I have a 5GB DB file, 5 simple tables. Several million entries. When I T tried to delete perhaps 10% of the entries. The WAL file blew up to T 51 GB, 10 times bigger than the DB file itself. I understand the T problem I think, with long transactions and have since split the T transactions up into smaller chunks. T My question is, can I fix this DB? integrity_check says it's OK but, T when I Pragma wal_checkpoint; from Sqlite.exe, nothing seems to T happen. It completes and the WAL file is still 51 GB. I was expecting T it to integrate the changes in the WAL file back into the DB file T proper. Sorry to reply to myself. False alarm. It did all the work but, I had to manually refresh the display to see it. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite with VS 2008
Linda, I'm using the individual files to compile a static lib. Just create a new project as a static lib. Unzip the source files into the project folder and then add all the h and c files to the project. You'll have to delete some but, you can figure that out when you compile. SQLITE_CORE; SQLITE_OMIT_AUTHORIZATION; SQLITE_OMIT_AUTOVACUUM; SQLITE_OMIT_BLOB_LITERAL; SQLITE_OMIT_DATETIME_FUNCS; SQLITE_OMIT_MEMORYDB; SQLITE_OMIT_TRACE; SQLITE_OMIT_CURSOR These are defines I use when I compile. I then include the lib project in my main project, set dependencies so, it automatically links in the lib and manually include the new project folder in my header search list. Add Existing Project in the Add right click of the solution explorer. The amalgamation works but, you won't be able to single step in and debug the lib if you use it. It's a limitation of the compiler. C Friday, October 29, 2010, 1:28:27 PM, you wrote: SAM I would suggest using the pre-compiled binary sqlite3.dll and just call SAM into the DLL. You can easily create an import library to link with in SAM your application by using the LIB /DEF command: SAM lib /def:sqlite3.def SAM That will create sqlite3.lib and sqlite3.exp. You can link against the SAM sqlite3.lib file and it will use the sqlite3.dll. SAM -Scott SAM sqlite-users-boun...@sqlite.org wrote on 10/29/2010 09:42:09 AM: I am trying to use sqlite in the debea database library. http://debea.net/trac/wiki/CompileSvn It simply says compile sqlite3. I am however a VS2008 user. I cannot find a way to compile it so I SAM can reference the include and library directories. Can you help? Linda Rawson Linda, The only way that I know of to compile SQLite in VS 2008 is to use the amalgamation download. You can find it here: http://www. sqlite.org/download.html. This will be only a single .c file. I'm not sure this will meet your needs if you need to reference the include folder. You may need to explore building the TEA tarball. I've never tried it in windows, but it look like there is guidance on the TEA website http://www.tcl.tk/doc/tea/. Has anyone tried this in Windows? RW Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com HARRIS CORPORATION | RF Communications Division assuredcommunications(tm) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users SAM ___ SAM sqlite-users mailing list SAM sqlite-users@sqlite.org SAM http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build
Hello Ben, Thursday, September 23, 2010, 9:17:39 AM, you wrote: BH I just discovered the MSVC compiler generates bad debug info for BH source files larger than 64k lines, which is the case with the Sqlite amalgamation. BH Does anyone know of a workaround? BH Thanks, BH Ben BH ___ BH sqlite-users mailing list BH sqlite-users@sqlite.org BH http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Don't use the amalgamation? That's one reason I don't use it. -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT is Slow when run in C++ code compared to SQLite Administrator
Hello ady,
I'd hazard a guess that SQLite Admin does something that caches the
file data in memory. If you do the select twice in a row. How fast is
it the second time on the C++ code?
I've found that opening the DB file using regular windows calls and
simply reading it to the end, closing it and THEN doing SQLite stuff
to it, gets rid of slow initial performance.
C
Monday, September 20, 2010, 8:49:07 AM, you wrote:
a Hi
a I have found that a select is slow when run through C++ code compared to
a SQLite Administrator
a I am executing SELECT * FROM docs WHERE (mail_index MATCH ('*FirstRun'))
a ORDER BY CREATED_TIME DESC
a int nRet = sqlite3_prepare(mpDB, szSQL, -1, pVM, szTail);
a nRet = sqlite3_step(pVM);
a SQLite Admin does it in 47ms and the CPP code takes 140 ms
a Wonder what could be wrong ?
a -Ady
a ___
a sqlite-users mailing list
a sqlite-users@sqlite.org
a http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] c compiler during install
Hello Glen, I'm not a Linux guy (though I've installed it and run a DB on it before) but, GS There was an error parsing the RHN proxy settings. GS RHN support will be disabled. Suggests your install can't get out on to the 'net to find the files you're looking for. If YUM can't get to the net, YUM can't download the upgrades. C Thursday, June 17, 2010, 10:13:01 AM, you wrote: GS Thanks. GS It doesn't appear that I have the necessary files to install gcc: GS [r...@selkirk 4.1.1]# gcc -v GS bash: gcc: command not found GS [r...@selkirk 4.1.1]# yum install gcc GS Loading installonlyn plugin GS Loading rhnplugin plugin GS There was an error parsing the RHN proxy settings. GS RHN support will be disabled. GS Setting up Install Process GS Setting up repositories GS No Repositories Available to Set Up GS Reading repository metadata in from local files GS Parsing package install arguments GS Setting up repositories GS No Repositories Available to Set Up GS Reading repository metadata in from local files GS No Match for argument: gcc GS Nothing to do GS [r...@selkirk 4.1.1]# GS Should I get this from RHS (I don't have install CDs)? GS Glen Scratchley GS DMS Solutions Architect GS Macro 4, Inc. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BLOB Support
Hello Sam, I store multiple gigs of image files, some as large as 2-3 megs in Sqlite DB's. For pretty much the same reason, the convenience of having them in one package. For my requirements, extracting the images from the DB, and displaying them isn't a bottleneck. It's fast enough. Search speed was improved by not storing the meta-data in the same table as the blob data. If I wanted to improve performance even more, I'd keep the meta-data in a different DB file. Essentially a DB for blobs and a DB for meta-data. It seems that seeking over the blobs to get to the meta-data in the combined DB is somewhat slow. C SC A lot also has to do with the requirements: My software is an event SC image viewing system, where each event is seen as a single document SC and all the data associated with the document is contained within SC the event folder. Currently only the metadata is stored in the SC database, all the images are stored in folders that are within the SC event folder. I am guessing, as is others, that storing the large SC images in the SQLite DB would be less efficient then how I am storing SC it now. One side effect, though is the requirement to backup the SC event folder. It takes a LOT longer to copy 5000 4k~8k files then SC it would be to copy one 20M ~ 40M database file. Because of this, in SC time I want to move all the thumbnails into one SQLite file, or maybe SC have one SQLite file per current folder holding images. SC Sam SC ___ SC sqlite-users mailing list SC sqlite-users@sqlite.org SC 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 the absence of msvcrt.dll a known issue with SQLite Windows 2000
Hello noel, Well, I have a large install base of users both foreign and US based and most of them still use XP. The benefit of Windows 2000 is that it's easily pirated (more easily than XP is what I mean). My software still works under Win2k, though the users have to install a a DLL from Microsoft. I'm not clear if my next version will run on '2k. As for the original problem, find the DLL and place it in the same folder as the application and it should work. C Saturday, June 5, 2010, 11:20:20 AM, you wrote: nf Roger Binns a écrit : -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/05/2010 04:21 AM, noel frankinet wrote: But in fact sqlite runs perfectly fine on windows2000 Exactly as my second paragraph says. But in the Windows 2000 time frame applications used to install their own copies of msvcrt.dll in the system directories which means one broken application is all it takes to screw that up. But when the operating system vendor no longer supports a platform it is increasingly pointless for a software person to continue support. At some point you have to call it quits. (For example see who still supports Windows 95, MacOS 7 or Redhat Linux 3.) nf I see windows 2000 as the finest os that redmont has produced. You'd nf better not develop on the latest ms os if you want to have some nf installed base. and is compilable with any free compiler (mingw for instance) You are mixing multiple things up. There are two different issues: source compatibility and binary compatibility. SQLite does compile with pretty much any C compiler out there (source compatibility). Binary compatibility is a bit more tricky. The compiler links SQLite against system libraries that provide underlying functionality such as file access, memory allocation etc. What happens is compiler and platform specific. In general the resulting SQLite works with that version of the operating system and any version going forward but not older ones. In the case of Microsoft, they have been distributing the C libraries with the compilers not the operating system and been numbering the C library as part of the file name. Additionally various data structures (eg stdio) have changed between library versions and having multiple versions of the C library used in the same process is a recipe for problems. Applications should be putting the C libraries in their own directories and not system locations. msvcrt.dll (with no numbers) is a special case and is in system directories and in theory is for the use of operating system applications only. Because of that pervasiveness MinGW targets it as the C library. In fact they claim to support no other version, although their compiled DLLs do work with other versions in my experience. nf I did not know that mingw was using msvcrt.dll. But anyway, its there, nf in system directory, so it should run fine. BTW I believe the distributed SQLite Windows DLL is actually produced by a gcc cross compiler on Linux, which you could label MinGW as well. nf Yes I think so too. nf Best regards nf Noël Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwKZMsACgkQmOOfHg372QRYyQCgsOpimyfGUtV83mEbqgEhS1+7 nAoAnR7r+vi6qb2WgiY1Z4zGfQbo2ZSK =DImh -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users nf ___ nf sqlite-users mailing list nf sqlite-users@sqlite.org nf http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on Windows 2003
Hello Joe, UTF8? What's the filename? If it has any accent characters, you need to convert the filename to UTF8 before you pass the filename to the SQLite. At least in C++ you do. C Tuesday, April 6, 2010, 4:03:24 PM, you wrote: BJ Hi Simon, BJ Yes, I was able to open the DB file using the command line tool. BJ I am unable to do so programmatically using C# .NET 2008. BJ Thanks, BJ Joe BJ Joe Burnett | Principal Software Engineer | FIDELITY INSTITUTIONAL BJ TECHNOLOGY BJ 2 Contra Way BJ Merrimack, NH 03054 BJ 603.791.5113 BJ cell: 603.289.0481 BJ -Original Message- BJ From: sqlite-users-boun...@sqlite.org BJ [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin BJ Sent: Tuesday, April 06, 2010 3:33 PM BJ To: General Discussion of SQLite Database BJ Subject: Re: [sqlite] SQLite on Windows 2003 BJ On 6 Apr 2010, at 8:30pm, Burnett, Joe wrote: I have a SQLite application running just fine on Windows XP SP3. I am attempting to move the application to Windows 2003 Enterprise Edition. However, the connection open method returns null. The connection string is good. The database file is there. I have the appropriate permissions on the file. The event viewer is no help. BJ Are you using the sqlite3_ library calls in a C program you've written, BJ or are you using some sort of 3rd Party framework or library ? BJ If you try to open the same database using the sqlite3 command-line BJ tool, does it work ? BJ Simon. BJ ___ BJ sqlite-users mailing list BJ sqlite-users@sqlite.org BJ http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users BJ ___ BJ sqlite-users mailing list BJ sqlite-users@sqlite.org BJ http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Non-Debug version of SQLite3.dll
Hello Roger, I find debug versions in Windows to be 2 or more times bigger than non-debug. I have a resource only DLL. Debug is 3.7 megs, release is 1.3. The debug executable is also 2-3 times larger. I'm not aware of any way to strip the debug information from the DLL. The linker can do it but, I believe it's too late for Bill's purposes. Bill, Why not just recompile the DLL yourself? I static link Sqlite directly into my programs so, I don't have to worry about DLL versioning. C Friday, April 2, 2010, 1:05:38 PM, you wrote: RB On 04/02/2010 09:25 AM, bi...@aol.com wrote: When I build my setup program I get a message that says File contains debug information. Is there a distributable SQLite3.dll that doesn't contain debug information and might be a little smaller? RB You should be able to run whatever the Windows equivalent of strip is to get RB rid of them. In any event it shouldn't save much space. I also notice there is no Windows version info which is typically in a resource file. Is there someone I can ping to suggest this be added? RB This request has been open for almost 6 years: RBhttp://www.sqlite.org/src/info/f711d5a937 RB Roger RB ___ RB sqlite-users mailing list RB sqlite-users@sqlite.org RB http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Non-Debug version of SQLite3.dll
Hello Roger, Looks like I was wrong. Supposedly Rebase can strip it out. C Friday, April 2, 2010, 1:05:38 PM, you wrote: RB On 04/02/2010 09:25 AM, bi...@aol.com wrote: When I build my setup program I get a message that says File contains debug information. Is there a distributable SQLite3.dll that doesn't contain debug information and might be a little smaller? RB You should be able to run whatever the Windows equivalent of strip is to get RB rid of them. In any event it shouldn't save much space. I also notice there is no Windows version info which is typically in a resource file. Is there someone I can ping to suggest this be added? RB This request has been open for almost 6 years: RBhttp://www.sqlite.org/src/info/f711d5a937 RB Roger RB ___ RB sqlite-users mailing list RB sqlite-users@sqlite.org RB http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite C API screwing port access?
Hello Martin, Do the ping both before and after you open the DB. ping_server(10.0.0.4); //my ping function, which pings a hardcoded IP, sqlite3_open(DB_NAME); sqlite3_close(DB_NAME); ping_server(10.0.0.4); //my ping function, which pings a hardcoded IP, See if the first one works. That'll validate your theory. My guess is the first one won't work either and you're barking up the wrong tree. C Tuesday, March 23, 2010, 12:55:36 PM, you wrote: MS I have a program which builds an ICMP package over IP and sends it. Before MS that, I get IP number and other information from a SQlite DB. I was having MS problems, so I began to comment different parts of the code, until I got to MS this code (pseudocode): MS sqlite3_open(DB_NAME); MS sqlite3_close(DB_NAME); MS ping_server(10.0.0.4); //my ping function, which pings a hardcoded IP, MS doesnt interact with DB MS If I run that code, the package nevers gets send (can't detect it with MS Wireshark). If a comment the sqlite part, it works perfectly. Somehow, the MS SQlite function calls are screwing with the port access, but I can't fin any MS reasonable explanation. MS Any ideas? MS I've been stuck for days with this problem. MS Regards, MS Martin Sigwald MS ___ MS sqlite-users mailing list MS sqlite-users@sqlite.org MS http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
