Re: [sqlite] SET (x,y) = (x1,y1)?
This construct does not work in postgresql 9.3.5 (unless I have a typo). However, I would love for it to work in both Postgresql and Sqlite. djenkins@ostara ~ $ psql -Upostgres -dcapybara_regtest psql (9.3.5) Type "help" for help. capybara_regtest=# create table test1 (col1 integer, col2 integer, col3 text); CREATE TABLE capybara_regtest=# insert into test1 values (1, 2, 'hello'); INSERT 0 1 capybara_regtest=# update test1 set (col1, col2, col3) = (select 4, 5, 'bye'); ERROR: syntax error at or near "select" LINE 1: update test1 set (col1, col2, col3) = (select 4, 5, 'bye'); ^ capybara_regtest=# \q On Mon, Sep 15, 2014 at 3:51 AM, Mark Lawrence wrote: > I occasionally have the need to update two columns based on complex sub > queries, which are often very similar > > UPDATE > t > SET > x = ( SELECT 1...), > y = ( SELECT 2...)-- nearly the same as SELECT 1 > ; > > Normally one could use a CTE to do the work once: > > WITH > cte > AS ( > SELECT 1 AS x, 2 AS y > ) > UPDATE > t > SET > x = cte.x, > y = cte.y > ; > > However CTEs don't work within triggers. > > I was wondering hard it would be to support the SET syntax as shown in > the subject line. I believe something like that works in PostgreSQL and > I could use it in SQLite for performance reasons. > > UPDATE > t > SET > (x,y) = (SELECT 1,2) > ; > > Alternatively, is there any effort underway to make CTEs work inside > triggers? > > -- > Mark Lawrence > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any interest for open source multi-user 'SQLite database server' application?
On Fri, Apr 18, 2014 at 6:32 AM, Christian Smith < csm...@thewrongchristian.org.uk> wrote: > On Tue, Apr 01, 2014 at 01:08:59PM +, Harmen de Jong - CoachR Group > B.V. wrote: > > We have built our own SQLite database server application and are > considering making this open source. Since there will be some time involved > into making it an open source project and maintaining it, we would first > like to make an inventory to find out if there is any interest in this > server application. > > > > ==> How it works: > > > > Clients can communicate with this server over TCP/IP sockets. Queries > are submitted and returned in XML format (BLOB results are returned in > binary format to prevent CPU intensive encoding and decoding). The server > application is written in native Visual C++ (without using MFC). If we > would make this project open source we would also include a client example. > > > I dare say that parsing and encoding XML would be more processor intensive > that BLOB encoding. > > IMO, you'd also increase your potential target audience if you could also > provide reasonably functional JDBC, ADO.NET, PHP and/or Python database > drivers. Providing any of these would allow existing users to plug your new > database into existing applications with the minimal of fuss. Personally, I > don't like the idea of XML as the protocol, largely because of the parsing > overhead, and have been looking at a similar server based on RPC, but for > debugging purposes it would be great. > > Regards, > Christian > ___ > Not all XML libraries are inefficient. TinyXml2 would probably work great for this use-case. (disclaimer: I use TinyXml2 in an unrelated project, but I am not the author). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM and large indices: best practice?
On Fri, Aug 30, 2013 at 3:52 PM, Simon Slavin wrote: > Great question. If all you are doing is writing (never any UPDATE or > DELETE FROM or DROP) then VACUUM won't save any space in your database > file. It would defragment your database and might thereby increase speed a > little, but this is mostly for old-fashioned systems that get most of their > speed from 'read-ahead buffering'. I would not expect much of a time > saving (less than 1%) from any modern setup (test it !). If it's > difficult, annoying or time-consuming to do the VACUUM I wouldn't bother. > This has not been my experience. I have a legacy system that exports data by writing it to a sqlite database. This file is only ever written to once (not updated or deleted while being written, either), vacuumed, zipped and sent to a receiving system. The file size varies greatly, based on the source data. The database schema has ~20 tables in it, some with many rows, some with very few. There are a few indices (created with the schema, before the data is populated). Vacuuming the database file shrinks it between 10% and 20%. This surprised me; I was expecting near zero savings. However, I did not research why, I just accepted it and moved on. I suggest to the OP to perform some experiments. That is what I did. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Request to register Application-ID
I just read the Sqlite mail list exchange between Eduardo Morras and Dr.
Hipp ("Header Application-ID list"). I was unaware that such a feature
existed. Now that I know, I feel compelled to chase a shiny object :)
I maintain a legacy proprietary payroll processing system (from the 1980s!
Yeah). Anyway, a few years ago I integrated sqlite into it for doing data
exports. If acceptable, I would like to register an application-id for the
data export file. I request 0x504d3858 ("PM8X", "PayMaster v8 export").
Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [off topic] SQL pie chart
http://code.openark.org/blog/mysql/sql-pie-chart An interesting project. The author seems to have way more fun with SQL than anyone should. But my first thought was... I wonder if this can be ported to sqlite? Dennis Jenkins Network Security Architect iStream Financial Services 262-432-1560 CONFIDENTIALITY NOTICE This electronic mail and the information contained herein are intended for the named recipient only. It may contain confidential, proprietary and/or privileged information. If you have received this electronic mail in error, please do not read any text other than the text of this notice and do not open any attachments. Also, please immediately notify the sender by replying to this electronic mail or by collect call to (262) 796-0925. After notifying the sender as described above, please delete this electronic mail message immediately and purge the item from the deleted items folder (or the equivalent) of your electronic mail system. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Porting into a microcontroller, minimum requirements
Jay A. Kreibich wrote: > On Wed, May 07, 2008 at 10:25:49PM -0400, Andrew Cunningham scratched on the > wall: > > >> "I have doubts that you will be able to get SQLite to work on anything >> less than a 32-bit processor. >> D. Richard Hipp" >> > > >> I was under the impression as long as the processor had enough room to >> hold the program (and RAM) it would work. >> > > Yeah, but you have to compile it first... > > >> The difference I would have >> expected would simply be speed of execution, but eventually getting >> there. With simple inserts/queries and the speed of sqlite, I thought >> it should be okay. >> > > I suspect the issue has to do with 64-bit integer support. SQLite does > a lot with native 64-bit integer values (e.g. "unsigned long long int"). > Most 32-bit processors have instructions that can deal with 64 bit > values as single, whole values, even if they require a lot of > slight-of-hand behind the scenes. Even if the processor doesn't have > explicit instructions, the compiler can often fake it by using the > overflow bits and a lot of byte shuffling. > > I suppose it would be possible to play the same games with a 16 bit > (or even 8) processor, but I'm guessing the compilers for most of > these smaller chips don't support long long ints. Even if they do, > it is going to be pretty slow and inflate the code side. > > -j > > I once thought about trying to compile it with "cc65" to run on an Apple IIe, for fun. I then calculated that the Apple II doesn't have enough RAM in a flat address space. I then decided that I'd rather go outside and enjoy the sunshine. :) However... I still wonder if it would be possible. Maybe if the compile could automatically split the code into sections that could work like "overlays" did back in the Borland C/Pascal days on MS-DOS. But I suspect that memory access would be a problem I could also compile Sqlite into MIPS R2000, and write a small emulator to run on the Apple (that transparently handled bank-switching and paging RAM). That might work. Slowly... -- CONFIDENTIALITY NOTICE This electronic mail and the information contained herein are intended for the named recipient only. It may contain confidential, proprietary and/or privileged information. If you have received this electronic mail in error, please do not read any text other than the text of this notice and do not open any attachments. Also, please immediately notify the sender by replying to this electronic mail or by collect call to (262) 796-0925. After notifying the sender as described above, please delete this electronic mail message immediately and purge the item from the deleted items folder (or the equivalent) of your electronic mail system. Thank you. -- The above email disclaimer is required by our legal department. However, if you would like a more humorous disclaimer, check here: http://www-users.cs.york.ac.uk/susan/joke/disclaim.htm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] May I ask why the source distribution mechanism was changed starting with 3.3.14?
Ulrich Telle wrote: drh wrote: I'm still having trouble trying to understand how managing 60 separate code files is perceived to be easier than managing just 2 files (sqlite3.c and sqlite3.h). It seems to me that the management problem gets much easier the fewer files there are to manage. In the case someone wants to use SQLite unchanged, it is certainly easier to manage only 2 files than 60+. But there are people - like myself - who would appreciate to have a source distribution containing the preprocessed source files as separate files as it was before. Not instead of the amalgamation distribution, but additionally. I don't know why others want or need the separate files, but I certainly can explain why I do: I have written an extension for SQLite using C++ which needs the preprocessed header files for compiling. Additionally I have to change the SQLite code at one place (essentially adding a single function call in the pager). The code change is not a big problem, but extracting the header files is quite inconvenient. Unfortunately I can't avoid to extract the header files since I can't merge my own code with SQLite code due to the fact, that SQLite is written in C and my extension in C++, and SQLite is not compilable in C++ mode. So, I would be grateful if the separate file distribution would return in addition to the amalgamation distribution. We have a custom function in SQLITE also (in the pager even). I would greatly prefer that the sqlite source be available as the separate files. Many years ago (1980 something) there was a DOS serial port communications library called "Async Pro" (or maybe it was "Async Plus"). It included ISRs for using serial ports on DOS, multi-port cards like Digi and Cyclades. It included Fax support (send/recv). The entire source distribution was 100+ .c and .h files. But when you compiled it, you just compiled a single master ".c" file that #included all of the other files. The compiler could do global optimizations, and the source was easy to edit. If you didn't want a feature, you just edited the master source file. Separate source files were nice, as we kept them in RCS and applied our own bug fixes to them. It would have been a pain to merge the vendor updates if everything were in one single file (especially if the vendor renamed lots of stuff or moved it around). - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] test fixture tcl errors on Windows
Dennis Cote wrote: I don't know enough TCL to know if there is a subtle bug here or not. I suspect that perhaps the file isn't really being closed until the script exits.Does this seem possible? Dennis Cote Get "filemon" from www.sysinternals.com. Set a filter to monitor all files in the directory containing the database file (or some other reasonable filter expression). Comment out all of the tests from the script that pass. Then fire it up :) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] compiling with VC++
RB Smissaert wrote: Did you make the alterations to make the dll VB compatible? Nope. C/C++ all the way. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] compiling with VC++
RB Smissaert wrote: Thanks, will have a look at that. Do you know from experience that it will compile OK with VC6++? I use VC 6.0 and it compiles just fine. I did not follow the instructions on the web site. I didn't even read them. I just put all of the C files into a single directory (except for the tcl one and shell.c - leave those out) and build a static library project using the source files. I did use the pre-processed C/H files from the windows zip file though (so I used sqlite.h, not sqlite.h.in). - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Effect of blobs on performance
[EMAIL PROTECTED] wrote:
Dennis Jenkins <[EMAIL PROTECTED]> wrote:
these are the settings that our app uses when it creates/opens the sqlite
database:
db.ExecuteImmediate("PRAGMA synchronous=OFF");
With synchronous=OFF, a power failure might result in database
corruption. Is this an issue for you?
--
D. Richard Hipp <[EMAIL PROTECTED]>
Not really. The data can be regenerated by the user without too much
difficulty. I don't have the numbers handy, but I seem to remember that
I did some performance experiments and determined that the performance
gains significantly outweighed the potential problems. To my knowledge,
for the past two years, only one user (out of many hundreds) has ever
gotten a corrupt database.
I suppose I'll put in a ticket into our issue tracking system to review
this decision. According to a comment in our source code, I based this
action on
http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html#pragma-synchronous
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Effect of blobs on performance
Thomas Fjellstrom wrote:
I still wonder about the utility of storing binary data in the db itself.
Maybe it makes it more easy to distribute that way, but how often does one
distribute an entire database in a "vendor specific" format?
I'm quite interested in hearing people's reasoning for going the blob route,
when you have a perfectly good "database" format for "blobs" already (various
filesystems).
1)
We use the sqlite encryption extension. We want our blobs encrypted as
well as our database, so putting the blobs into the database makes
sense. We have a special table for the blobs that has a primary key (3
columns) and the blob. The rest of the data is contained in other tables.
2)
We don't need to worry about atomically deleting disk blobs and database
rows. We take advantage of the ACID nature of sqlite. This way we
don't have to code for contingencies where the user has managed to
delete or corrupt a blob, or a blob that our app can't delete even when
it deletes the database row.
3)
Having everything in one package. Makes tech support much easier if the
user only has to transmit a single file instead of an entire directory.
4)
We modify the blobs at runtime. ACIDness of sqlite is very nice here.
I don't want to try to re-implement this directly on the filesystem
(even if it becomes a simple rename operation).
Our blobs vary in size from 12K to 3M. Sqlite is not a performance
bottleneck for us... the client's internet connection is.
I have not done extensive performance tests on these settings, but these
are the settings that our app uses when it creates/opens the sqlite
database:
db.ExecuteImmediate("PRAGMA page_size=4096");
db.ExecuteImmediate("PRAGMA legacy_file_format=ON");
db.ExecuteImmediate("PRAGMA cache_size=8000");
db.ExecuteImmediate("PRAGMA synchronous=OFF");
db.ExecuteImmediate("PRAGMA temp_store=2");
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] calculate age (off topic)
Jay Sprenkle wrote:
> LOL! You should look at a function to determine if a day is a holiday.
> Talk about ugly! In some places you literally need to know the weather
> and the phase of the moon!
>
> --
[EMAIL PROTECTED] ~/movie]$ pom
The Moon is Waning Gibbous (100% of Full)
[EMAIL PROTECTED] ~/movie]$ which pom
/usr/games/pom
is there a shell script program to let me know if the weather is clear
in mecca? ;)
wget -O -
"http://www.weather.com/outlook/travel/businesstraveler/local/SAXX0013?from=search_current";
| \
grep "B CLASS=obsTextA" | \
grep -v "\°" | \
awk -F "<" "{ print \$5 }" | \
awk -F ">" "{ print \$2 }"
Today it returns "Party Cloudy".
Solution is non optimal. There is probably a much better way to do it
in perl, but I'm too lazy to try right now. Subject to break whenever
weather.com's webmaster change their HTML layout. Tested on an
up-to-date Gentoo Linux (2006.1)
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] calculate age
Jay Sprenkle wrote: LOL! You should look at a function to determine if a day is a holiday. Talk about ugly! In some places you literally need to know the weather and the phase of the moon! -- [EMAIL PROTECTED] ~/movie]$ pom The Moon is Waning Gibbous (100% of Full) [EMAIL PROTECTED] ~/movie]$ which pom /usr/games/pom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Q about new SQLite API
[EMAIL PROTECTED] wrote: QUESTION 3: Suppose there is a schema change and the SQL statement is automatically reprepared. But the schema change is such that the SQL is no longer valid. (Perhaps one of the tables mentioned in a SELECT statement was dropped.) What error code should sqlite3_step() return in that case? I always liked it when errno would be "E_NOCLUE". Not a posix standard though... Seriously though... I think that it should return whichever error would be returned if the statement were "prepared" using the current API and the table did not exist (or other error occured). Even tough you don't currently support "alter table ... drop column" right now, you might add it some day. Whatever error code implementation you choose now should be forward compatible (with in reason) with API changes like "alter table". If the reprepare fails for some ambiguous reason, maybe fall back to returning "SQLITE_SCHEMA" ? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Memory Usage
If you are seeing different memory usage patterns for identical code based on if it is run from xinetd or on your command line, then I would check the process environment that xinetd creates. Maybe some component that sqlite uses is acting differently based on environment variables? I don't know with any certainty, but I'm fairly sure that sqlite doesn't use env vars for anything. It's just a thought... - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] reg:blob data reading
Dennis Jenkins wrote: Teg wrote: Hello Dennis, I'm, probably going to be offering optional encryption too. Why did you chose to use the SQLite encryption extensions versus just encrypting the blobs after you read them back in and before you write them out? 1) We wanted the entire database encrypted. There is sensitive non-blob data too. 2) Dr. Hipp's encryption extension is well tested and already integrated into sqlite. 3) The encryption is very transparent to the rest of our application. I don't have to manually call functions to look up keys and encrypt or decrypt blocks of data. 4) Updates to the blobs can now take advantage of the ACIDity of the sqlite engine. (Is that a valid use of the acronym 'ACID'? Gotta love the English language. We can conjugate anything anyway we want to.) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] reg:blob data reading
Teg wrote: Hello Dennis, I'm, probably going to be offering optional encryption too. Why did you chose to use the SQLite encryption extensions versus just encrypting the blobs after you read them back in and before you write them out? 1) We wanted the entire database encrypted. There is sensitive non-blob data too. 2) Dr. Hipp's encryption extension is well tested and already integrated into sqlite. 3) The encryption is very transparent to the rest of our application. I don't have to manually call functions to look up keys and encrypt or decrypt blocks of data. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] reg:blob data reading
Jay Sprenkle wrote: On 9/18/06, Teg <[EMAIL PROTECTED]> wrote: Hello Jay, The whole reason I store files in the DB in the first place is to have a single "package" to move around and backup when needed. My application is storing whole series of PNG and JPG files in the DB with meta data describing where the images came from. My technique won't help you then. I use it for things like scanning images of documents and using the database to keep track of the documents. I never have to search a picture using a select statement so it would be silly for me to put them into the database. I just back up the file system using off the shelf backup software and it works fine. Everyone has different needs. We like keeping all of the data (blobs included) in one data file. We also use the encryption extension, and it is mandatory that our blobs be encrypted. I don't need "read arbitrary byte ranges from a blob" for my work project, but I could use them in a personal project that involves sqlite (no encryption here; but it is important to keep all data in one data file). - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Error: file is encrypted or is not a database
Will Leshner wrote: Ah. Ok. It was just a shot in the dark. I've never seen SQLite itself change its header like that, so I suspect the culprit lies elsewhere. Most likely, the following is not the cause of your problem, but it's funny: There was once a virus that did a search and replace across the user's hard-drive (back in the DOS days of 20M drives) that changed all occurrences of "Microsoft" to "Macrosoft" or something like that. I wish I remembered the details and/or the virus name. I guess you had to be there. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] converting a sqlite table to dbf
Fred Williams wrote: Kind'a like chucking your Pentium 5 and going back to scratching on the cave wall with a rock, Eh? I'd export the tables to a CVS files using something like SQLiteAdmin.exe. Open the CVS files with Excel and save the resulting spreadsheets as .DBF files (My Excel has a choice of DBASE II through DBASE IV.) Or you could use that block buster database, "Access", to do the same. Fred yup. very good analogy. If you have an ODBC driver for sqlite installed, you can copy the database to a set of DBF files very easily using MS-FoxPro. Probably in three lines of code. I know that FoxPro can do this, but I don't know the language syntax. I was "exposed" to it a few years ago. The most vile thing in the world; on par with for ms-access. It gets worse when you have 100 foxpro apps using 30 some tables on a single Novell network with win95/98 clients. All on 10mbit hubs. Dropped IPX packets = corrupt DBF files. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Reading the same table from two threads
[EMAIL PROTECTED] wrote: It has been suggested that I add a mutex to every SQLite database connection. This would cause access to a database connection to automatically serialize even when two or more threads try to use that connection at once, thus preventing problems such as the above. The downside is the (minimal) additional overhead of acquiring and releasing a mutex on each API call. There will also be some changes to the OS-layer backend which will break private ports to unusual OSes. Thoughts? -- D. Richard Hipp <[EMAIL PROTECTED]> IMHO: Don't do it. Those of us that want to use threads should create our own threaded sqlite wrappers. I might make several sqlite calls that I don't want interwoven with other sqlite calls from other threads. I rely on my own mutexes. Granted, I can continue to use my own mutexes.. I just think that you should not add mutex code when: a) (guessing here) a sizable group of users don't use mutliple threads anyway. b) it would break the private OS ports as you mentioned. c) the library can alreayd be made thread safe via external logic. If you try to stop people from shooting themselves in the foot, they'll just pull a Dick Chenney on you and shoot someone else.
Re: [sqlite] real time gui updates
[EMAIL PROTECTED] wrote: > "Rob Menegon" <[EMAIL PROTECTED]> wrote: > >> Not sure whether I understand how this would occur. >> >> The application is not doing or responsible for the updates to the database. >> Its only function in life is to retrieve and display data. Updates, >> modifications occur via another application, so I was incorrect in my >> previous response to you - one user (app) doing updates and another >> displaying data - independent processes. >> >> > > Poll. Monitor the modification time on the database file and > when it changes, update your display. You can poll the modification > time of a file 10 times per second with no measurable CPU overhead. > > If you need to know specifically what changed, create triggers > that write a change log into a special table. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > Ick. Polling is evil. :) Granted, if your box can handle it, then fine. But consider that polling code will never get paged out, it will stay stuck in your processes' working set, and it will consume CPU cache entries for the code and data that it touches. You have to balance how often you expect a write event to occur with how often you are willing to have a read cycle that determines that nothing changed, ie, your "miss rate". If both the reader and writer are running on the same host, then use some form of IPC. Windows has lots of IPC mechanisms. Unix has some SYS-V IPC, but I haven't used it in a while. You could also use a file handle (trap SIGIO) or socket to do IPC. (disclaimer: I'm not saying that the following is the "one true way", it is just an example of something that I did to solve the same problem as the original poster): Years ago I wrote a system with one reader and many writers. The writers would insert some entries into a DB2 table on an IBM zSeries mainframe. I wanted the reader to process these entires as soon as they were written, but I didn't want to poll every few seconds. btw, the writers and readers were not on the same host. So, I wrote a stored procedure for DB2 that sent a UDP packet to a socket on my reader box. I made this stored proc part of the insert trigger for the table. The reader process created a socket and added that socket to its main "select()" call. Whenever the table was inserted into my process knew about it in < 50 ms. In case I missed a UDP packet, or my process was not running when the table was updated, I did still poll the table. But only once every 30 or 60 seconds. The really cool thing is that my UDP packet contained the primary key of the row that was inserted. So the reader could go directly to it. The reader had reasonable security (what I considered reasonable for the problem domain and the network) UDP packets were only accepted if they came from the server. The WAN had firewalls to prevent source spoofing. I wanted to add some sort of "cryptographic signing", but never got around to it. Now in your case, sqlite is not a database server like DB2. But your could still have a trigger in sqlite that fires off a UDP packet, or sets some other IPC mechanism into action.
Re: [sqlite] hard copy docs
[EMAIL PROTECTED] wrote: > Hi, > I am pretty much a hard copy guy too. But about a month ago I purchased a > dual output video card and a second monitor (LCD). Total price $270. Now I > have the docs on one screen while a work on the other. I like it much better > than I thought I would. > Bill > > > The developers at my company have dual-headed displays. Very nice. I highly recommend that others try it if they can. It makes debugging much easier when you can have your app open on one monitor and the debugger on the other. Or when you want to read API docs on one and code on the other. Or run real-time system / performance monitoring and the target app, both full screen at the same time. Very nice.
Re: [sqlite] Locking
RohitPatel wrote: > FoxPro supports row level locking. Ofcourse FoxPro creates one file for each > table. There must be some way to implement row level locking. Probable by > locking region in a file or somehow. > > Just thinking curiously...how MS could have implemented row level locking in > FoxPro. > FoxPro (DBase-III file structures) is a piece of shit that corrupts all the time. NEVER run FoxPro on a novell network with Win95 clients (ok, so this was last decade). FoxPro does region locking and has NO JOURNAL. "Rollbacks" are done by the client by replacing the changed rows. If the client crashes with a table (or table set) partially modified, then the table is left inconsistent. FoxPro is not ACID. At my previous employer I wrote some C code to repair broken foxpro tables. (I myself did not write foxpro code). Our main database at that time was 5 large tables, each about 256M in size. All sitting on a single Novell share. Every few days a win95 or win98 client (100 clients, all using IPX, novell v3 on HUBS (not switches)) would send a junk packet to the server. (or it sent a good packet that arrived mangled). From my analysis of the corrupted database file, it seems that the client intended to send a "seek (somewhere far down in the file)" and then "write". But what actually happened is that the Novell server seeked to file offset 0 (btw, the metadata for the table) and dutifully wrote out the data record. So instead of the DBF (foxpro table) file having the proper header that defines the record layout for the table, it contained junk. The FoxPro guys had no way to fix this except to try to restore from a backup and re-run tens of thousands of "transactions". So one day this happened and the CIO was freaking out. I pulled up a file format spec for the DBF file and fixed it using a hex editor. Later that day I wrote a tool to repair the damaged header. A fun hack, but a horrible situation and horrible technology. The moral of the story is that you should never trust a database system that does region lock of flat files sitting on a server IF the server will release the lock when the client dies AND the client does not leave behind some sort of useful journal. Maybe even this statement is too liberal. I'm sure that someone in this group can make a better argument for never using region locking. Moral #2 is that creative use of hex editors in front of the CIO can help your career. Unless he's a total spaz and doesn't understand what you are doing.
Re: [sqlite] Index usage tracking
[EMAIL PROTECTED] wrote: > Dennis Jenkins <[EMAIL PROTECTED]> wrote: > >> I would like to know where the best place in sqlite is to patch to >> have it record (syslog for unix, OutputDebugString() for windows, >> nothing fancy) each time it decides to use an index to satisfy a query. >> > > The index decisions are all made in where.c and there is already > code in that file for printing out the decisions for testing > purposes. I suggest you search for SQLITE_TEST inside where.c, > see that current testing code, and modify that to do whatever > it is you want to do. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > Thank you very much. I see the code that I need to tweak now. ;)
[sqlite] Index usage tracking
Hello all, I would like to know where the best place in sqlite is to patch to have it record (syslog for unix, OutputDebugString() for windows, nothing fancy) each time it decides to use an index to satisfy a query. For that matter, also each time is runs a select query and decided to not use an index. This is only for in-house debugging. Consider it "coverage testing" of all of our SQL to determine if I have the correct indicies. I'd like the logging to record/emit the original SQL and the names of the indicies (if any) used to execute that SQL. Our app has grown and morphed over the past two years. It has LOTS of sql in it now. Granted, I could isolate all of this sql (even the dynamically generated stuff) (select, delete, update statements) and run it through the analyzer. However, if the above mentioned "hack" is easy to do, then I would prefer the hack. I'm trying to determine is all of my indicies are actually being used, and to what frequency they are being used during a typical run of our software. I've been reading through "select.c" and "vdbe.c". I'm not sure if I should add the hack to the Virtual Machine opcode emitting code or the opcode consuming code. Maybe there is already a solution to my problem and I simply didn't see it.
Re: [sqlite] DLLs containing user-defined SQL functions
[EMAIL PROTECTED] wrote: > Dennis Jenkins <[EMAIL PROTECTED]> wrote: > >> The Windows way does not seem as powerful as the Unix way. I hate >> the M$ operating systems, but I code for them almost every day. So my >> next statement isn't so much a defense of Microsoft , but a rebuttal to >> your assertion that "the windows shared library loader is not >> sensible". The DLL mechanism made sense at the time it was created >> (8088, 640K ram, windows 1.0 running in real-mode in 320x200x4 graphics >> - not a lot of room for fancy features). You have to consider how and >> why the DLL mechanism evolved on windows, and why Microsoft went through >> so much effort to NOT break backwards compatibility. >> > > How does introducing a new shared library format that supports > automatic bidirectional linking (as in Unix) break backwards > compatibility? Nobody says they have to stop supporting DLLs. > Just provide something better in addition to DLLs... > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > The windows DLLs _DO_ support bi-directional linking. "A" can depend on "B" and "B" can depend on "A". The windows kernels actually have code to handle this. It is documented in the blog postings last summer that I mentioned earlier. It has done this since win95. Not sure about win 3.11. The problem is that under normal circumstances, you can't create the DLLs like this. You have to create a fake DLL "B", generate the real "A" using fake "B"s import library, then use the real A to generate a real B. But for us, "A" is a user's EXE and "B" is sqlite3.dll. Not very convenient. The user will be forced to compile their own SQLITE3.DLL file. As proof, consider the following exports from USER32.dll and GDI32.dll. They are circularly linked: tdump \WINDOWS\system32\gdi32.dll | grep "\.dll" Turbo Dump Version 4.2.16.1 Copyright (c) 1988, 1996 Borland International Imports from KERNEL32.dll Imports from ntdll.dll Imports from USER32.dll Exports from GDI32.dll tdump \WINDOWS\system32\user32.dll | grep "\.dll" Turbo Dump Version 4.2.16.1 Copyright (c) 1988, 1996 Borland International Imports from GDI32.dll Imports from KERNEL32.dll Imports from ntdll.dll Exports from USER32.dll Can you give a concrete example of what you are trying to do? This is my assumption: 1) You are STATICALLY linking sqlite3 into some program. There is no SQLITE3.DLL. 2) From the point of view of the OS, SQLITE does not exists. There is only the EXE and some system DLLs that you have no control over. 3) The EXE (from the OS point of view) wants to dynamically load a DLL that an sqlite programmer has created. This DLL will export certain symbols, like "foo" and "bar". So the sqlite3 engine will use "LoadLibrary" and "GetProcAddress" to obtain function pointers to "foo" and "bar". 4) "foo" and "bar" need to call normal (or hidden?) sqlite functions that reside in the EXE. For example, "sqlite3_changes" or "sqlite3_errcode" (actual names don't matter). 5) Step #4 fails because the EXE does not export those symbols. You can make the EXE export those symbols by creating a DEF file for the EXE. 6) You could also make this work if the user of SQLITE created a DLL instead of statically linking it in. In this case. both the "addon.dll" and "prog.exe" would have imports from "sqlite3.dll". This would work beautifully, so long as "prog.exe" and "addon.dll" match the "sqlite3.dll". Since we should all treat "sqlite3*" as an opaque structure, this should not be a big problem. 7) Idea from #6 is a no-go if the user is using the Sqlite3 crypto extension, as your license agreement requires that we use the crypto extension in such a way that a third party can't make use of it. IE, we can't put it into the sqlite3.dll file, as someone who did not pay for it could just take the DLL and have the functionality callable from their own app. Therefore, those of us that use the crypto extension in any "insecure" environment must statically link against sqlite.
Re: [sqlite] DLLs containing user-defined SQL functions
Robert Simpson wrote: >> -Original Message- >> From: Dennis Jenkins [mailto:[EMAIL PROTECTED] >> Sent: Wednesday, June 07, 2006 11:46 AM >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] DLLs containing user-defined SQL functions >> >> Robert Simpson wrote: >> >>>> -Original Message- >>>> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >>>> Sent: Wednesday, June 07, 2006 10:36 AM >>>> To: sqlite-users@sqlite.org >>>> Subject: Re: [sqlite] DLLs containing user-defined SQL functions >>>> >>>> >>>> >>> Pardon my ignorance about *nix, but what happens during >>> >> this whole global >> >>> symbol mapping thing if two libraries both export the same >>> >> function name? >> >>> >>> >> The PE (exe,dll,sys) file format on Windows defines an import table. >> Each entry in the import table has both a DLL name AND a >> symbol name (or >> ordinal import). It is perfectly valid for one PE file to import two >> objects from two different PEs that both have the same symbol name. >> Convincing your compiler/linker to produce such a PE import table is >> left as an exercise to the reader ;) >> > > I know how Windows works -- being a Windows programmer :) I was asking > about how *nix works. On the surface the *nix way resolving these global > symbols seemed like a keen way for some kind of injection attack or > something. > > You are 100% correct. I misread your email. Reading way too fast I'm not sure how Unix works (elf or a.out file formats). It is probably well documented. It is easy to do injection attacks on either platform. Just put a hacked copy of "libc.so" on the system (in /tmp even) and modify the user's share library path environment variable before invoking the application. You can do something very similar on windows. Just dump a hacked "kernel32.dll" into the same directory as the EXE. This might not work with SP2 of XP for system DLLs. However, if the EXE uses a non-system DLL (like libJpeg.dll), then just replace that one. Put some code into the DllMain function that installs whatever hook procedure you need, and viola! You have just compromised the EXE and can do anything on that system that you want that the user running the EXE has the rights to do. On windows you can also simply "inject" a foreign DLL into a running process. I've written some code to do it. My injected DLL enumerates all of the GDI objects in the ruuning app, allowing my to capture all of the HBITMAPs that back the HDCs. ;)
Re: [sqlite] DLLs containing user-defined SQL functions
Robert Simpson wrote: >> -Original Message- >> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >> Sent: Wednesday, June 07, 2006 10:36 AM >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] DLLs containing user-defined SQL functions >> >> >> It's official then: The lack of sensible shared library loader >> is yet another reason to avoid windows at all costs. In fact, >> I'm thinking this reason will go near the top of the list >> > > The Windows way does not seem as powerful as the Unix way. I hate the M$ operating systems, but I code for them almost every day. So my next statement isn't so much a defense of Microsoft , but a rebuttal to your assertion that "the windows shared library loader is not sensible". The DLL mechanism made sense at the time it was created (8088, 640K ram, windows 1.0 running in real-mode in 320x200x4 graphics - not a lot of room for fancy features). You have to consider how and why the DLL mechanism evolved on windows, and why Microsoft went through so much effort to NOT break backwards compatibility. Microsoft could have fixed lots of design flaws in windows, but in doing so they would have broken the ability for the OS to run older software. They are (or were before Vista anyway) super paranoid about backwards compatibility. Raymond Chen blogs about this often in his Microsoft blog. http://blogs.msdn.com/oldnewthing/archive/category/2282.aspx DLLs were meant to share code AND resource objects back in the win16 days. Once third parties started writing code that took advantage of the way those DLLs worked, Microsoft could not change the interface. > Pardon my ignorance about *nix, but what happens during this whole global > symbol mapping thing if two libraries both export the same function name? > > The PE (exe,dll,sys) file format on Windows defines an import table. Each entry in the import table has both a DLL name AND a symbol name (or ordinal import). It is perfectly valid for one PE file to import two objects from two different PEs that both have the same symbol name. Convincing your compiler/linker to produce such a PE import table is left as an exercise to the reader ;) > If SQLite only looked to the exe to provide this function, then what would > happen to folks writing their own libraries that abstracted the database > layer, but wanted to provide their own userdef functions from within their > library ... would SQLite find the function in their library instead of the > exe? What if the exe and/or two other dependent libraries all exported the > function too and had their own userdefs ... how would SQLite handle all > these libraries wanting to add their userdefs to SQLite? > This is a non-issue. The problem is that "sqlite.dll"'s import table MUST specify a PE source object for each symbol name. How would SQLITE.DLL know the name of your EXE at link time (when the OBJs are turned into the DLL). There are only two real ways for SQLITE to "reach back" into the EXE. 1) The EXE exports some symbols (for the magic functions that SQLITE wants to call). The EXE loads SQLITE (via import lib or LoadLibrary). The EXE calls a function in Sqlite passing it the "HINSTANCE" of the EXE. Sqlite uses the instance member (really a pointer to the load address of the PE header of the EXE) as teh first argument to "GetProcAddress". 2) The EXE simply marshals the pointers into a structure (or passes them one at a time) into SQLITE by calling a function in SQLITE. But isn't this what we already have? Why are we trying to change it?
Re: [sqlite] DLLs containing user-defined SQL functions
[EMAIL PROTECTED] wrote: > "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: > >>> This all works great on Unix. When I use dlopen() to >>> attach the shared library, the procA() reference in >>> the shared library is automatically resolved to the >>> address of procA() in the main program. >>> >> On Windows, the loader works in a very different way. Basically, >> export/import connections are established at link time, not at load >> time. The loader does not perform a symbol search over all the DLLs, the >> import tables in the executable image (emitted by the linker) tell it >> exactly where to look. >> >> > > That explanation does not seem entirely accurate (especially the second sentence). If that were true, it owuld not be possible to release an updated DLL with re-arranged entry points and expect the caller EXE or DLL to link to it properly; yet that works. Refer to the blog of the guy at Microsoft who wrote (or rewrote) the DLL loader: http://blogs.msdn.com/mgrier/rss.aspx > The disadvantages to the windows approach are obvious. > Before I add this characteristic to the ever-growing > list of reasons why I hate windows and especially hate > programming for windows, I should be fair and ask if > there are any advantages to the windows way of doing > things that I have overlooked. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > >
Re: [sqlite] Purging the mailing list roles. Was: Please RestoreYour Account Access
Dennis Cote wrote: > Dennis Jenkins wrote: >> Maybe someone could create a simple web site that lets us vote on the >> suggestions? >> > Unfortunately (or perhaps not) Richard runs SQLite and its related > community more like a benevolent dictatorship than a democracy. :-) > > I believe he listens to the responses (especially when he has > specifically asked for ideas), but he is not bound by them in any way. > Simply stating your opinion is the best way to get your ideas considered. > > As with all benevolent dictatorships, everything goes relatively > smoothly as long as the dictator remains benevolent. So far Richard > has been quite benevolent. > > Dennis Cote I should have said "opinion poll" instead of "vote". The results were not supposed to be "binding" on DRH. Nor was I trying to suggest that he should do this extra work. In most social groups, there is some small percentage of people that generate the most communication. In our group, this seems to be about 10 to 20 people. My idea was to poll a larger sample. The threaded nature of a discussion list does not seem to be a very practical method for conducting a quantitative poll. My overall idea was to determine the readership's opinion, not the "social butterfly" opinion. :) There are probably many pre-existing web sites where anyone can create an poll they want, so it might take less than 10 minutes to construct a poll and post a url. Then again, I could be delusional ;)
Re: [sqlite] Purging the mailing list roles. Was: Please RestoreYour Account Access
Dennis Cote wrote: > > I think an opt in mechanism with a CAPTCHA to allow an account to > continue to post to the list that is triggered every couple of months > wouldn't be a burden to those actively posting. First time or > occasional poster would receive an email when they try to post that > would require them to pass the CAPTCHA test before their message is > distributed. They would then be authorize to post for the next N > months. At the end of that time a similar email would be sent that > would require a similar response for them to maintain their posting > status. > > To winnow out the dead accounts, a second opt in response to a message > that is sent every year or two. This should not be a burden for those > who only want to continue to read the list postings. If they don't > reply to this message they will be deleted from the distribution list. > They can always sign up again, or use one of the list archives instead. > > Dennis Cote I like this idea the best so far. Maybe someone could create a simple web site that lets us vote on the suggestions? Make the back-end sqlite :)
Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access
Clark Christensen wrote: >> And yet somehow, the spammer still managed to get signed up >> using a "paypal.com" address. How did they do that? >> -- >> > > As others have pointed-out, there's probably a simple autoresponder on many > [EMAIL PROTECTED] mailboxes. It replied, and that was good enough :-) > > I think if the list confirm messages had a link to click on to validate the > subscription (that leads somewhere other than replying to the message), the > anonymous autoresponders wouldn't validate. Plus, it wouldn't lock-out > legitimate users at paypal.com (somebody suggested rejecting by domain). > > -Clark > > > > The email should contain TWO urls. One real and one a honey-pot. If the honey-pot URL gets tripped, then it should unsubscribe the box that polled all of the URLs in the email. If the email recipient clicks on the non-honey-pot URL, then they stay on the mailing list. Doesn't the sqlite web site already do something like this to defend against fubared search engines that ignore the ROBOTS.TXT file and scan all hyperlinks anyway? Man, that last sentence sucked. The English language should be drug out into the street and shot.
Re: [sqlite] Binary compatibility
[EMAIL PROTECTED] wrote: > Nikki Locke <[EMAIL PROTECTED]> wrote: > > > 3.2.2 will refuse to read a database file created by 3.3.5 > unless version 3.3.5 was compiled with -DSQLITE_DEFAULT_FILE_FORMAT=1 > or the "PRAGMA legacy_file_format=ON" pragma is used prior to creating > the database. But if the database is created in a way that 3.2.2 > can read it, there should be no incompatibilities between 3.3.5 > and 3.2.2 (or any other SQLite version, for that matter). Anything > created by version 3.2.2 should always be readable by 3.3.5, regardless. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > I have recently updated my development branch from v 3.2.1 to v 3.3.5 and noticed the compatibility issue as well. What are the key differences between the two file formats? Is there a compelling reason to move to the new format (vs using the pragma and keeping the old format)?
Re: [sqlite] Having troubles with Sqlite3 Crypto extension on v3.3.5
[EMAIL PROTECTED] wrote: > Dennis Jenkins <[EMAIL PROTECTED]> wrote: > >> Has the crypto extension been updated since last summer? >> >> > > Yes. The latest code has been sent to you by private email. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > Thank you. The code seems to have fixed the problems.
[sqlite] Having troubles with Sqlite3 Crypto extension on v3.3.5
Hello, I'm going to be light on the details here because I'm not sure how much I can publically discuss (with respect to the Sqlite3 crypto license). Out project has been using Sqlite3 v 3.2.1 for a long time, with Dr. Hipp's encryption extension (purchased 2005-7-11). I am about to begin a long stretch of development and wanted to update the sqlite code (which we statically compile into our win32 app). I read that v 3.3.4 fixes some multi-threaded bugs. I did not see an easy way to get 3.3.4 so I grabbed 3.3.5. I managed to get it all to compile. However, whenever I use the crypto extension, the next SQL that I execute returns code "1" (not a database) when I call sqlite3_step. If I disabled the calls to "sqlite3_key()" and start with an empty database file (ie, I nuke the file first), I get no errors, but no encryption. Is anyone else successfully using the crypto extension with sqlite 3.3.5? How can I get the sources for 3.3.4? I assume that I need to use CVS some how, but I'm not sure. Has the crypto extension been updated since last summer?
Re: [sqlite] create unique index quickly
Jay Sprenkle wrote: > > Me too! > > The two largest database setups I've worked with: > the total of all the call records for a major phone company, > and the cumulative records of most of the drugs bought in > the United States, don't add up to as much as this. > What are you sampling at 1/200th of a microsecond? Jay, do you work for the NSA or the DEA or both?
Re: [sqlite] create unique index quickly
Brannon King wrote: > The benefits I'm trying to get out of sqlite are the data queries. I > collect a large, sparse 2D array from hardware. The hardware device is > giving me a few GB of data data at 200MB/s. Future hardware versions > will be four times that fast and give me terabytes of data. After I > have the data, I then have to go through and make calculations on > sub-boxes of that data. (I'll post some more about that in a different > response.) I was trying to avoid coding my own > sparce-matrix-file-stream-mess that I would have to do if I didn't > have a nice DB engine. I think sqlite will work. I think it will be > fast enough. I'll have some nice RAID controllers on the production > machines with 48-256MB caches. Hello Brannon, I am simply curious. This sounds like an amazing engineering challenge. If it is not a secret, can you describe what this data represents and how it will be used? What is the ultimate source of this data? How many days/weeks/eons of it do you plan to accumulate? How much raw disk space is that? If backups and journaling are not important, then is it safe to assume that you can always regenerate that data on demand? Is each "set" of data identical, or only statistically similar to prior sets? Your project sounds like fun though, from what little I've read of this thread. Sure beats writing boring financial software ;)
Re: [sqlite] Getting free pages count
Alexey Belyaev wrote: > I mean freepages.c under "not official methods" :) > > I know. :) I'm hoping that someday it will be officially supported. If Dr. Hipp feels that it adds unnecessary bloat to the library, then maybe it can be "supported" but IFDEFd out by default, and those of us that want it can set the define and add it back in. I just feel that my code was a horrible hack. I had to copy many private structural definitions from other parts of the source code to make it work.
Re: [sqlite] Getting free pages count
Alexey Belyaev wrote: > Hi! > > In current versions sqlite (3.2.7 and later) not enought functions, > for receive information abut free pages count (or used pages count). > I have to use not official methods :( > May I expect that this functions will be introduced in next versions sqlite? > > I too would like an officially supported method for getting the free page count. I hacked up the following based on James P. Lyon's prior sqlite 2.xx work: http://unwg.no-ip.com/freepages.c
Re: [sqlite] Direct use of SQLite btree functions / performance
Jay Sprenkle wrote: > Just out of curiosity why is this data in the database? > I've seen very few applications where the blob is indexed or operated upon > by the database and it's always a pain to deal with it. We always just > left binary data in the file system and stored references to it in the > database. > For us, we use the encryption module, and our blobs must be encrypted. I did not want to mess with encrypting the blobs myself and the associated key management. I also don't want to have to deal with the blob and data pointing to it to be out of sync. The atomicity of storing the blob and its meta data is very nice.
Re: [sqlite] sqlite3 dll symbols
Essien Essien wrote:
> hiya,
>
> I have a code snippet that looks like:
>
> typedef int (*SQLITE3_CLOSE)(sqlite3*);
> typedef const char* (*SQLITE3_ERRMSG)(sqlite3*);
> typedef int (*SQLITE3_OPEN)(const char*, sqlite3**);
> typedef int (*SQLITE3_EXEC) (sqlite3*, const char*, sqlite3_callback, void*,
> char**);
>
> HINSTANCE sqlite3_dll;
>
> SQLITE3_CLOSE _sqlite3_close;
> SQLITE3_ERRMSG _sqlite3_errmsg;
> SQLITE3_OPEN _sqlite3_open;
> SQLITE3_EXEC _sqlite3_exec;
>
> int DB_Init()
> {
> sqlite3_dll = LoadLibrary("sqlite3.dll");
> if (sqlite3_dll == NULL) {
> printf("Cannot find sqlite3.dll. Make sure its in the same
> directory as the program\n");
> return 0;
> }
>
> _sqlite3_open = (SQLITE3_OPEN)GetProcAddress(sqlite3_dll,
> "sqlite3_open");
> if (_sqlite3_open == NULL) {
> printf("Cannot load function sqlite3_open");
> return 0;
> }
> }
>
> problem is, when ever i call DB_Init(), it always fails with 'Cannot load
> function sqlite3_open'. But it successfully passes the LoadLibrary portion.
> I'm not a win32 guru, so i'm willing to admit i've made a mistake somewhere.
>
> Any ideas on what i'm doing wrong?
>
> I'm using Turbo C++ 4.5 IDE and related tools. (yeah... i know turbo
> C++ 4.5is realy aged, but could this be the problem?)
>
> Essien
>
>
Since you have the borland compiler product, use the "TDUMP.EXE" tool to
view the PE header of the sqlite3.dll file. Sometimes the functions
will be exported with a leading underscore. If your compiler is
producing 32 bit binaries, and the DLL is also 32 bit, then you might
try adding a leading underscore to the symbol name when you call
'GetProcAddress'.
Re: [sqlite] Strange execution times
Ulrich Schöbel wrote: > Hi Richard, > > thanks for trying to reproduce my 'problem'. > > I'm using Linux 2.6.12 (Ubuntu Breezy Badger, a debian distro), > Tcl/Tk 8.4.12, sqlite 3.3.4, all pretty recent versions. > > I made the same tests today with the same results. > > Nevertheless, sqlite is by far faster than mySQL, so I'm going > to switch to it, anyway. But I'm still curious, where this effect > is coming from. > > Could you (or someone) write the test in 'C' and compile it with "-ggprof" flag on gcc and run it in the profiler? I propose that the test program should take one command line argument, the # of iterations. That way you can profile "1" vs "50" vs "10" independently of each other.
Re: [sqlite] Thread handling in Windows
[EMAIL PROTECTED] wrote: Every Windows DLL can provide a DllMain function. This function gets called whenever a thread gets created or destroyed. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dllproc/base/dllmain.asp Except that not all of us use Sqlite as a DLL. We statically link it directly into our multi-threaded application. Fortunately for us, we create a fixed number of threads at startup and after that, do not create nor destroy any threads that use sqlite until shutdown.
Re: [sqlite] multiple Db's and journal file time hit?
[EMAIL PROTECTED] wrote: The delay seems to coincide with the journal file creation-- it happens after our first (committed but not yet written to disk) write attempt to the database, the journal file does not at-the-time exist, and there are 500+ inserts pending with reads rapidly being added after that. Although I too have seen anti-virus programs hose things up, we don't have any running. It was a good thing to check for though. We can live with this minor start-up penalty because our users will oftentimes run this app 12-36 hours straight and could end up hitting the database 1,000,000 times in that period. It does not appear to be any kind of error as no exceptions are thrown. We can create a minor user-appreciated perception of busyness on-screen. :-) Tom From: [EMAIL PROTECTED] Sent: Monday, December 19, 2005 8:59 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] multiple Db's and journal file time hit? [EMAIL PROTECTED] wrote: We just noticed a 30-40 second hit at early on in our program running. > Others have reported things like this caused by anti-virus software running on the same machine and insisting on doing some kind of virus scan the first time the journal file is created. -- D. Richard Hipp A few suggestions: White box it: Compile SQLITE yourself with debugging symbols. Run your code in a profiler. Then run it again in a debugger examine the regions of code that the profiler flagged as "taking a long time". Black box it: Use "filemon" (win32/64, from www.sysinternals.com) or "strace" on Linux or the equivilent on any other platform. They will timestamp and record all disk access. Have your own program display the exact system timestamps before and after the lengthy operation. Examine the logs to see what is going on.
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
Just out of curiosity: Maybe someone can report the result of 'SELECT 5 / 2;' on MySql, SQLServer, and others? devl=# SELECT 5 / 2; ?column? -- 2 (1 row) devl=# select 5.0 / 2; ?column? 2.5000 (1 row) devl=# select 5 / 2.0; ?column? 2.5000 (1 row) devl=# select version(); version PostgreSQL 8.0.3 on i386-portbld-freebsd5.4, compiled by GCC cc (GCC) 3.4.2 [FreeBSD] 20040728 (1 row)
Re: [sqlite] compression
Jay Sprenkle wrote: On 9/28/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote: Your third statement is not true. On Linux (and FreeBSD, but FreeBSD does not have Reiser as far as I know) you can treat a regular file as if it were a filesystem and mount that fiel system via the "loop back" device. You can mount an ISO image file as an actual CD, for instance. Cool! Thanks for letting us know :) I should have mentioned the obvious though... the file must be an image of a valid file system. for example, the following will fail: dd if=/dev/zero of=file bs=4096 count=1024 losetup /dev/loop0 file mount /dev/loop0 /mnt/xxx However, the following should work: dd if=/dev/zero of=file bs=4096 count=1024 losetup /dev/loop0 file mke2fs /dev/loop0 mount /dev/loop0 /mnt/xxx You can even encrypt the entire filesystem over loop back: dd if=/dev/zero of=blob bs=1M count=1024 losetup -e AES256 /dev/loop0 blob mke3fs /dev/loop0 blob mount /dev/loop0 /mnt/crypto as usual, do a "man" on "losetup". This is from my Gentoo 2005.1 Linux system (home) with whatever packages it installed a few days ago. At work I use FreeBSD and I've not used loopback devices there, but the FreeBSD Handbook (online for free) shows how to do it.
Re: [sqlite] compression
Christian Smith wrote: On Wed, 28 Sep 2005, Sid Liu wrote: Is there a possibility that this Reiser 4 be used on a file, rather than a file system? Hopefully on Windows? Reiser FS is a filesystem. It manages files. So it cannot be used on a file. Your third statement is not true. On Linux (and FreeBSD, but FreeBSD does not have Reiser as far as I know) you can treat a regular file as if it were a filesystem and mount that fiel system via the "loop back" device. You can mount an ISO image file as an actual CD, for instance. Years ago I imaged all of my old DOS floppies. I access them via the loop back device now. In theory, you can do that with any file system that can use a block device (ntfs, iso9660, ext3, etc...) but not with nfs, smbfs, proc, etc... dd if=/dev/fd0 of=floppy_file.img # Eject floppy, don't need it anymore. losetup /dev/loop0 floppy_file.img mount -t vfat /dev/loop0 /mnt/floppy ls -l /mnt/floppy Windows NTFS already has compressed files. Right click a file or directory in exporer, select propeties, then advanced attributes. You can turn on compression there. Don't know how to do it from the command line, though.
Re: [sqlite] determining number of 'used' pages?
Mark Allan wrote: Hi, I am using SQLite on an embedded software product. The SQLite database file is saved and read from a NOR flash chip. We have found that the writing of data to this Flash chip can be quite slow. Therefore we need to minimise the writes that are made by SQLite. We have disabled the creation of the journal file when writing data to the database and this has halved the write time. If anyone can suggest any way we can further reduce the amount of file writes made or speed up the writing of data to the file system then these would be gratefully receieved. The main area where we now have a performance problem however is with deleting records. The problem is due to the need to 'vacuum' the database when we delete records. We do this as we need to know the size of the database file to show a capacity readout to the user. I have been looking into the SQlite code to try and find if there is a way in which I can read the number of 'used' pages. If I can determine the number of used pages in the database then I can use this to generate the capacity report and I can disable the vacuuming of the database, which will save us much time, about 3-4 seconds!. Please can someone advise me as to how I can determine the number of used pages? Looking at the vacuum code it seems it needs to create a temporary file and database to perform vacuum. Is there a way I can determine the number of 'used' pages without the overhead of having to create a temporary database on the filesystem? Thanks in advance for your help. Mark I ported some code from sqlite2 to sqlite3 that will do what you want. You can get it from "http://unwg.no-ip.com/freepages.c";. What you want is in the function at the very bottom of the file: int sqlite3_get_page_stats(sqlite3* db, long *lTotal, long *lFree, long *lSize); Happy hacking!
Re: [sqlite] tracing memroy leak
Mark Wyszomierski wrote:
app1:
SomeThread()
{
sqlite3 *db = opendatabase();
writesomestuff();
PostMessage(my_other_app, 0, 0);
closedatabase(db);
return 0;
}
app2:
MessageHandlerInMainThread()
{
sqlite3 *db = opendatabase();
ReadDatabaseStuff(db);
closedatabase();
return 0;
}
Thanks!
Mark
hmmm... that should work. Since in your message LPARAM and WPARAM are
0, I assume that they convey no pointers to memory buffers. Does the
posted message contain any data other than the message id?
(From memory) If you use "SendMessage" to send a message to a window
owned by a different process, then it will behave like PostMessage, but
block until the other process has dispatched your message. Let me check
the MSDN real quick...
Oops. It looks like what I posted earlier was in error. From the Feb
2003 platform SDK on "SendMessage":
Applications that need to communicate using HWND_BROADCAST should
use the RegisterWindowMessage function to obtain a unique message
for inter-application communication.
The system only does marshalling for system messages (those in the
range 0 to WM_USER). To send other messages (those above *WM_USER*)
to another process, you must do custom marshalling.
If the specified window was created by the calling thread, the
window procedure is called immediately as a subroutine. If the
specified window was created by a different thread, the system
switches to that thread and calls the appropriate window procedure.
Messages sent between threads are processed only when the receiving
thread executes message retrieval code. The sending thread is
blocked until the receiving thread processes the message. However,
the sending thread will process incoming nonqueued messages while
waiting for its message to be processed. To prevent this, use
SendMessageTimeout with SMTO_BLOCK set. For more information on
nonqueued messages, see Nonqueued Messages.
I'm at a loss right now. I supose that I'd need to learn more about
your code.
What mechanism are you using to track memory leaks? Many years ago I
used "memcheck". It worked great, but the company (Stratosware) seems
to be out of business now.
I call this function first thing in my WinMain() to turn on the
Microsoft run time library memory checking stuff:
voidSetDebugging(void)
{
// Turn on extream heap memory checking. This will slow down the
system considerably.
_CrtSetDbgFlag(_CRTDBG_LEAK_CHECK_DF|_CRTDBG_ALLOC_MEM_DF|_CRTDBG_CHECK_ALWAYS_DF);
_CrtSetReportMode( _CRT_ASSERT, _CRTDBG_MODE_FILE );
_CrtSetReportMode( _CRT_WARN, _CRTDBG_MODE_FILE );
_CrtSetReportMode( _CRT_ERROR, _CRTDBG_MODE_FILE );
_CrtSetReportFile( _CRT_ASSERT, _CRTDBG_FILE_STDERR );
_CrtSetReportFile( _CRT_WARN, _CRTDBG_FILE_STDERR );
_CrtSetReportFile( _CRT_ERROR, _CRTDBG_FILE_STDERR );
}
I also redirect stdout and stderr to a console created with
"AllocConsole()".
I assume that you do something similar?
Re: [sqlite] tracing memroy leak
Mark Wyszomierski wrote: I traced this error down a bit, it only appears when using windows' PostMessage() to communicate between applications. Both applications have their own database handles for sure. When one app gets some data, it simply uses PostMessage() to inform the other app that some data has been received. It is at this point that if I try accessing the database in this message handler, the memory leak occurrs. I even took the additional precaution of creating an entirely new database handle inside the message handler itself. If I replace PostMessage() with SendMessage(), no leak occurrs. However, replacing PostMessage() with SendMessage() would be a huge penalty. Any ideas why there is a problem here? Thanks, Mark Unless I'm wrong (he he... tha can happen): PostMessage simply inserts the "MSG" into the target thread's message queue. The target thread's message pump needs to consume that message and dispatch it. Remember, message queues are owned by threads, not windows. PostMessage is asynchronous. SendMessage actually dispatches the message to the target window handler, but inside the CALLERs thread context. SendMessage blocks until the call is completed. The target message handler must be reentrant for this to work.
Re: [sqlite] Re: Thread safety guarantees
Rolf Schaeuble wrote: Hello, if I understand the whole issue correctly, there is only one reason why it's not safe to use a sqlite handle in any thread of a process (as long as only one thread at a time uses it): File locks are tied to the thread that created them (except this special Redhat version). That's why on Windows everything's safe, since file locks are per-process there. If that's correct, couldn't this problem be solved by letting sqlite use a dedicated thread to do the locking? Each time a lock needed to be set/removed, a message would be sent to that thread which then would perform the locking. That should make it safe to use the sqlite* from any thread. For single threaded applications that would mean more thread switching. For multithreaded applications, however, life wouldn't only become easier, but performance may well increase. In my applications I have a dedicated database access thread. Each time a query needs to be executed, it's sent to this thread. In this case I have thread switching anyway. Only having it when a lock is set/removed will decrease the amount of switches. Best regards Rolf Schäuble I disagree with the entire approach. It seems hackish. Just have each thread create it's own sqlite handle. So long as one thread does not need to "pass off" an exisiting handle to a different thread you will be fine.
Re: [sqlite] Network-based DB performance for Mozilla
Jay Sprenkle wrote: I'm glad to see someone is working on this since it was a very noticable problem for me. I don't think it will matter what database you use if you're forced to have it hosted over a network. As far as I know they all rely on the underlying locking mechanism in the OS, which is inherently slow over a network. Can you avoid multiple locking latency hits by just getting a lock at startup and never relinquishing it? What about copying (importing) the network database to a ":memory:" database, and periodically copy (export) it back?
Re: [sqlite] Pre-allocating disk space to avoid db file fragments
Jay Sprenkle wrote: On 9/13/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote: Actually, you can defrag the database file yourself, if you have admin rights (b/c you need to open a handle to the physical device). I thought he needed an automated solution to include in his code released to users. Yeah. His code can defrag the file if it has the correct permissions and is running on Windows NT 4 or better. He can do this whenever he has the need to. Am I missing something? Maybe I missed the original goal and only focused on the "need to defrag" angle.
Re: [sqlite] Pre-allocating disk space to avoid db file fragments
Jay Sprenkle wrote: On 9/13/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote: It depends on lots of things: the OS, the filesystem, the % free space on the file system, other processes that are causing the OS to allocate disk blocks. I have noticed that Windows XP totally sucks at keeping files fragment free when copying them. Even if there is enough free space to hold the destination file contiguously, the OS won't do it. I have rarely bothered to check file fragmentation on Linux and FreeBSD systems, so I don't know how those handle it (but I would assume it to be much more intelligent than NTFS). ugh! Thanks for letting us know about that. There's no way I know of to control fragmentation. I've been assuming if you copy a complete file within a short time period to a new location it will likely be less fragmented that the original. It's not always true, but in my experience it's simple and generally tends to be true over the long run. If a user will not do defrag on their disk there's not a lot you can do to correct for it. Actually, you can defrag the database file yourself, if you have admin rights (b/c you need to open a handle to the physical device).
Re: [sqlite] Pre-allocating disk space to avoid db file fragments
Jay Sprenkle wrote: On 9/13/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote: Even vacuuming won't defrag the file. Disk space is allocated by the OS and the OS makes no guarantees. Won't Dr. Hipp's method of making a backup copy also defrag the file? i.e. execute begin exclusive to lock it. copy the file commit rename the files and use the backup copy as the new current database. Assuming your disk free space isn't heavily fragmented. If it is fragmented I believe this will tend to reduce the fragmentation with time, depending on what else is going on at the same time on the machine. It depends on lots of things: the OS, the filesystem, the % free space on the file system, other processes that are causing the OS to allocate disk blocks. I have noticed that Windows XP totally sucks at keeping files fragment free when copying them. Even if there is enough free space to hold the destination file contiguously, the OS won't do it. I have rarely bothered to check file fragmentation on Linux and FreeBSD systems, so I don't know how those handle it (but I would assume it to be much more intelligent than NTFS). To Ben's point, I neglected to consider table space fragmentation. He has a very good point. I read the source code to the VACUUM function. My understanding is that the resulting file won't have any table space fragmentation, but I could be wrong.
Re: [sqlite] Pre-allocating disk space to avoid db file fragments
Jay Sprenkle wrote: On 9/13/05, GreatNews <[EMAIL PROTECTED]> wrote: Hi D. Richard Hipp, I'm developing a desktop rss reader using your excellent sqlite engine. One issue my users found is that sqlite database can get heavily fragmented over time. I'm wondering if it's a viable suggestion that sqlite pre-allocates disk space when creating database, and grows the db file by bigger chunk(e.g. grow by 20% or so in size each time)? Why not do a vacuum every 10th time (or something similar) you exit the program? --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 Even vacuuming won't defrag the file. Disk space is allocated by the OS and the OS makes no guarantees. If the program is running on Windows, then you can defrag the file your self (if you have admin rights). You can read about the Win32 defrag APIs here http://www.sysinternals.com/Information/DiskDefragmenting.html. We don't defrag the database file, but I do vacuum it whenever the slack space exceeds 25% of the total file space used by the database file. We check for this when our application starts up. I ported a hack from sqlite2 to sqlite3 that calculates the amount of slack space. I submitted it (probably improperly) for inclusion into sqlite a few weeks ago. I can provide it to you if you wish, just email me. You could do the following: 1) Create a dummy table and fill it with a gazillion [1] rows of junk. 2) Defrag the database file. 3) Drop the dummy table. 4) You just created lots of slack space that will be reused by sqlite before sqlite extends the disk file (I think). [1] for suitable values of "a gazillion".
Re: [sqlite] SUM and NULL values
Jay Sprenkle wrote: So, in some cases (explicite addition), NULL + 0 = NULL. (we all know that NULL != NULL). However, the "sum" function skips NULLs. This seems inconsistant. NULL + 0 is not valid since you can't do a binary operation on only one number (zero is a number, NULL is not). So it returns an appropriate result when you ask it to do something it can't do. Exactly. NULL is the appropraite result for the exact reason that you gave. To me this seems similar to mixing up units, like saying that the store is 3.5 km + 10 minutes + 2 radians from my house. The units just don't mix. I understand the rational for "NULL + anything" returning NULL (makes sense mathematically). I understand the utility of having "sum" skip NULLs. However, we can now construct a math in the sql engine where a+b+c != a+b+c. Yucky. Only where one of the variables is different than the other side of the equation. The point being that if I sum up the rows using "sum()", I could get one answer. However, if I iterate the cursor in a stored procedure and manually sum them up using " accum += value" I would get a different answer. The math is inconsistant (in postgresql anyway). I actaully did not test sqlite :). I was just posting this to bring up the point about consistnecy. I'll let those with more knowledge than me hash it out. (no pun there).
Re: [sqlite] SUM and NULL values
From postgresql 8.0.1 on FreeBSD: syslog-ng=# select NULL is null; ?column? -- t (1 row) syslog-ng=# select (0+NULL) is null; ?column? -- t (1 row) syslog-ng=# select (0) is null; ?column? -- f (1 row) syslog-ng=# create table tmp1 ( a int4 ); CREATE TABLE syslog-ng=# insert into tmp1 values (2); INSERT 16949751 1 syslog-ng=# insert into tmp1 values (0); INSERT 16949752 1 syslog-ng=# insert into tmp1 values (NULL); INSERT 16949756 1 syslog-ng=# select * from tmp1; a --- 2 0 (3 rows) syslog-ng=# select a, a is null from tmp1; a | ?column? ---+-- 2 | f 0 | f | t (3 rows) syslog-ng=# select sum(a) from tmp1; sum - 2 (1 row) syslog-ng=# drop table tmp1; DROP TABLE syslog-ng=# \q So, in some cases (explicite addition), NULL + 0 = NULL. (we all know that NULL != NULL). However, the "sum" function skips NULLs. This seems inconsistant. I understand the rational for "NULL + anything" returning NULL (makes sense mathematically). I understand the utility of having "sum" skip NULLs. However, we can now construct a math in the sql engine where a+b+c != a+b+c. Yucky.
Re: [sqlite] checking the database status
Robert Simpson wrote: What I say below is in no means trying to be rude or show you (or anyone) up. I have no idea what your experiences are with Windows or Unix or Unix-Like operating systems. I'm probably stating things that almost all of us already know. :) So? If you open the file, that's 1 handle open. Someone unlinks it, but a handle is still open. sqlite3_open() then opens the file, that's 2 handles. You then close your handle and there's still 1 handle open until sqlite is done with it. I'm not a *nix programmer, so maybe I am missing something obvious. In Unix you can delete an open file. The file is simply removed from the directory. Properly, this is called "unlinking". The actual syscall is called "unlink". When a file has 0 hard-links, and no process has it open, it is removed from the file system and the space reclaimed. Normal files have only one "hard-link". The 'hard-link' count is stored in the file's inode in a traditional unix file system. (I know about UFS and EXT2/EXT3. I have no idea what JFS, XFS, ReiserFS, et al do.) In Windows you can not delete an open file, even if your process has the file open for writing. The text at the bottom was lifted directly from the February 2003 Win32 platform SDK on the "DeleteFile" API call [1] about 15 seconds ago. Note that the rules I'm discussing are for NT kernel based versions of Windows. #2.. Would that work if you opened the file exclusively? If you don't open it exclusively, theoretically someone else could open it too. On Windows, sqlite3_open() calls the CreateFile() API with the OPEN_ALWAYS flag, which means if the file doesn't exist, then create it -- in either case, always open the file. To atomically create a file and make sqlite3 open it (at least in Windows) you would call CreateFile() with the CREATE_NEW flag, which will atomically fail if the file already exists. If it creates a new file however, you can then pass the filename to sqlite3_open() and then subsequently close your handle. I know :) I'm simply saying that I wish that sqlite3_open() took some flags and "did the right thing" based on the underling OS. Those flags would be specific to sqlite and translated into the native OS flags deep inside to "os_***.c" wrappers. #define SQLITE3_OPEN_DEFAULT0 #define SQLITE3_OPEN_EXISTING1 #define SQLITE3_OPEN_TRUNCATE 2 ...etc... So my question is simply, "Why was sqlite3 designed to behave the way it does?" (That is, the caller has no control over the underlying "open" operation. Another option would be to create a version of "sqlite3_open" that takes an existing OS handle and uses the handle as-is (I suppose that you'd still have to pass in a filename so that the journal, vacuum, (and other?), files could be created). Now, I've not had a need for any of this. I did notice this a long time ago though. If I really needed this, I'd code it myself. I've already hacked away on the sqlite code base to make it do some things that I want it to do. [1] If an application attempts to delete a file that does not exist, the *DeleteFile* function fails. If the file is a read-only file, the function fails with ERROR_ACCESS_DENIED. To delete a read-only file, you must first remove the read-only attribute. To delete or rename a file, you must have either delete permission on the file or delete child permission in the parent directory. If you set up a directory with all access except delete and delete child and the ACLs of new files are inherited, then you should be able to create a file without being able to delete it. However, you can then create a file get all the access you request on the handle returned to you at the time you create the file. If you requested delete permission at the time you created the file, you could delete or rename the file with that handle but not with any other. For more information, see File Security and Access Rights . The *DeleteFile* function fails if an application attempts to delete a file that is open for normal I/O or as a memory-mapped file. *Windows Me/98/95: *The *DeleteFile* function deletes a file even if it is open for normal I/O or as a memory-mapped file. To prevent loss of data, close files before attempting to delete them. To recursively delete the files in a directory, use the *SHFileOperation* function. To close an open file, use the *CloseHandle* function. The *DeleteFile* function marks a file for deletion on close. Therefore, the file deletion does not occur until the last handle to the file is closed. Subsequent calls to *CreateFile* to open the file fail with ERROR_ACCESS_DENIED. *Windows Me/98/95: **DeleteFileW* is supported by the Microsoft Layer for Unicode. To use this, you must add certain files to your application, as outlined in Microsoft Layer for Unicode on Windows 95/98/Me Systems.
Re: [sqlite] checking the database status
Robert Simpson wrote: sqlite3_open() doesn't create/open a file for exclusive access off the bat. If you want to atomically open a file that already exists, open it yourself and while its open, call sqlite3_open(), then close your own handle to it. If you want to atomically create a file and only open it if you created it, create it yourself and while its open, call sqlite3_open() then close your handle. #1 should not work in all cases on Unix. Someone could unlink the file after you open it the first time and before you open it the second time. The first file will be removed when all open file handles to that file are closed. #2.. Would that work if you opened the file exclusively? If you don't open it exclusively, theoretically someone else could open it too. Unless I'm missing something, the only way to atomically create or open a file is to ask the OS to do that operation only once. Maybe Dr. Hipp can elaborate on why we can pass the file open/creation flags into the sqlite3_open() function. Maybe these flags do not exist on all platforms (in which case, why not just ignore the flags)? ?
Re: [sqlite] checking the database status
Robert Simpson wrote: um ... It's a file. You just check to see if the file exists. No sqlite calls involved. Robert That would work in this situation, but what about the inverse? You only want to open the file IF it already exists. There is a race condition: where you check for the file, don't find it, and then try to open the database, and in the mean time, a different process/thread has done the same? I do not need this yes, but I can imagine situations where an atomic "create new database ONLY" would be very nice.
Re: [sqlite] Why can i open a textfile?
Mike Shaver wrote: On 8/22/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote: I very much disagree. I want the entire file, header included, to be encrypted. Sometimes you don't want anyone to know what the file type is. Security through obscurity is not secure. However, you don't want to give the bad guys a road map either... Finding out that it's a sqlite file is not a hard problem for an attacker who has any interesting access to your machine, since your programs must find that file somehow. Once they find it, are you not concerned about lightening their cryptanalysis burden through known-plaintext attacks? Mike No, not really. The sqlite crypto engine consumes the first several hundred bytes of the rc4 random number generator output. It is my understanding that this would significantly complicate the plain-text attack. But I'm not a crytologist. I do find it facinating though. I do not understand how "finding the file" would give the attackers any clue to what kind of file it is (unless I make the filename something like "sqlite3.db3"). If the file were named "jimbob.dat", and the contents looked like gibberish, then what do they know? They must analyze the program that accesses the file. I once thought that I could remove all text strings from the sqlite code that would give the attacker any clues. I then realized that the strings are important to the proper functioning. The ones that need to be left behind are significant enough to be good clues that the program uses sqlite technology. So, I do agree with you, that it is not too difficult to determine if a data file _might_ be an sqlite database, even if it in encrypted. That being said, I still like having the header encrypted as it is. Maybe it just makes me feel warm and fuzzy on the inside :) In the end, I feel that our software is much more vulnerable to someone attacking it with a debugger than with crypto analytic attacks. At some point, you must call "sqlite3_key()" and pass it three things: the sqlite handle, a void* to the key initializer and an "int" (# of bytes in the key). All the attacker has to do is locate that code and determine what those last two arguments are. Personally, I find this to be an easier approach. But then, I've been coding in assembly since I was 8 and C for the last 10 years. I'm not much of a mathematician or code breaker. I have often wondered how difficult it would be to derive the rc4 initialization key given a known plain text and a known cipher text generated from the unknown key and known plain text. I imagine it as a breadth-first search of the key space. Lets say that it is computationally feasible to do just that. The sqlite header string is.. um, heck, I don't know, let's say 20 bytes. Then you can derive the exact values for at most 20 values of the key state vector (it might be less if a value gets muted more than once). What do you know about the remaining bytes of the first 256 bytes of the sqlite file? Some of those bytes have "sane" values or other constraints. I think that it would be too difficult to fully derive the key b/c you don't know much of the plain text. This is the extent of what I know about rc4. If someone else knows more, please enlighten me. :)
Re: [sqlite] Encryption and fixed header values (was Re: [sqlite] Why can i open a textfile?)
Mike Shaver wrote: On 8/22/05, Edwin Knoppert <[EMAIL PROTECTED]> wrote: Hmm, but every known file format has an header. Sqlite has a string, not really a header as it seems. Maybe for v4 to implement a real header (if not yet) A header doesn't need to be encrypted. (A bit for testing if it's encrypted might have it use as well) In fact, I was wondering about this very issue when I was working on my own encryption layer for Mozilla's storage use. Does the reliable format of the first page (known sqlite header string, various other fields that are very likely to be zero or in some way related to the size of the DB file) not make known-plaintext attacks on the encrypted database much easier? Given that the encryption is limited to a page at a time due to the pager implementation (and placement of the hooks in sqlite), it would seem that that would be fairly worrisome. I can mitigate it a bit by having the page number affect the key selection, but I'm still sort of nervous about it. Mike I hope that I don't spill too many beans here My company has licensed the RC4 encryption add-on for sqlite. RC4 is basically an XOR against a huge one-time pad. That pad is creating using a "key scheduling algorithm". The key state for the random number generator is 256 bytes of data and two 8-bit indicies. There are several attacks that can be used to derive the original key state, but they all require huge samples of data to analyze. RC4 is not the strongest encryption available, but for most of us, it is good enough. The best way to attack any system using sqlite w/ the crypto extension is to hook the call to "sqlite3_key()" and just steal the rc4 key directly. Much easier than crunhcing numbers on a super computer. http://en.wikipedia.org/wiki/Rc4
Re: [sqlite] Why can i open a textfile?
Edwin Knoppert wrote: Hmm, but every known file format has an header. Sqlite has a string, not really a header as it seems. Sqlite has a header. Part of it is plain text, part binary. You can learn about it by reading the source code. Maybe for v4 to implement a real header (if not yet) A header doesn't need to be encrypted. (A bit for testing if it's encrypted might have it use as well) I very much disagree. I want the entire file, header included, to be encrypted. Sometimes you don't want anyone to know what the file type is. Security through obscurity is not secure. However, you don't want to give the bad guys a road map either... Sqlite seems to be created espec. for c programmers. c programmers are truly a different breed :) And we love it. But it's not always handy to follow these directions or doings. Iow, i never looked at the sqlite source how it works. Sad, it is truely neat-o code. I guess at least 50 percent over here does not really care and would never explore how to solve these kind of things. 50 percent of whom? Programmers who use sqlite? Where is "over here"? I hope examples and such will eventually get more polished towards other languages as well. Examples for instance show *sqlite as hstmt and there is really no use to understand it's internally a pointer to a structure or so. hstmt as Long would do. Well long story but understanding functions (and even using it as cdecl) is not that trival due conversion and such.
Re: [sqlite] Multi-threading.
Mrs. Brisby wrote: meanwhile, two threads attempting to multiplex access to a single sqlite handle are a) bound to be confused, and b) need those resource locks anyway. (background: I've been using threads on win32 since 1998. Sometimes to spread load across multiple CPUs, but usually just because I find it simpler than state logic to do everything in one thread. I'm not entering into this debate on threads vs. non-threads.) I just want to know why anyone would code using multiple threads accessing the same SQLITE connection object ( or handle or whatever you want to call it). I allocate a separate connection object for each thread and have ZERO troubles with locking so long as I use transactions properly. Assuming a multi-threaded (with in the same process) environment, what benefits are there to use a single (global?) SQLITE object (protected by a mutex, semaphore, critical section, whatever)? It seems so much more complicated and brittle.
Re: [sqlite] ANN: sqlite3Explorer V 1.7
Dennis Jenkins wrote: Cariotoglou Mike wrote: *. new feature : support for encrypted databases (WITH the licenced version of sqlite) http://www.singular.gr/sqlite/ Awesome! Thanks! I was hoping that you would add encryption support. :) (We purchased our license a few weeks ago). However, I am unable to get it to work. I am 100% sure that I am entered the encryption key when prompted for it. :) Never mind. I solved my problem. I forgot to define 'SQLITE_HAS_CODEC=1' when compiling the DLL. It works great now. (previously I was getting error '26', "not a database, or encrypted").
Re: [sqlite] ANN: sqlite3Explorer V 1.7
Cariotoglou Mike wrote: *. new feature : support for encrypted databases (WITH the licenced version of sqlite) http://www.singular.gr/sqlite/ Awesome! Thanks! I was hoping that you would add encryption support. :) (We purchased our license a few weeks ago). However, I am unable to get it to work. I am 100% sure that I am entered the encryption key when prompted for it. I can access the database using the command line version of the encryption database shell program (made from shell.c). I rekeyed my database to use the key "hello". I still have no luck with sqlite explorer. Any suggestions? Here are my DLL exports: (from tdump, borland 5.0 compiler). Note that I compiled the DLL using MSDEV v6.0 (I just like the borland command line 'tdump' tool for dumping PE (exe, dll) files): Turbo Dump Version 4.2.16.1 Copyright (c) 1988, 1996 Borland International Display of File \BIN\SQLITE3.DLL Old Executable Header DOS File Size 3B000h (241664. ) Load Image Size 450h ( 1104. ) Relocation Table entry count h ( 0. ) Relocation Table address 0040h (64. ) Size of header record (in paragraphs)0004h ( 4. ) Minimum Memory Requirement (in paragraphs)h ( 0. ) Maximum Memory Requirement (in paragraphs)h ( 65535. ) File load checksumh ( 0. ) Overlay Numberh ( 0. ) Initial Stack Segment (SS:SP) :00B8 Program Entry Point(CS:IP) : Portable Executable (PE) File Header base: 00C8 CPU type 80386 Flags210E [ executable backwards 32bit library ] DLL flags [ ] Linker Version 6.0 Time stamp 42E7860B O/S Version 4.0 User Version 0.0 Subsystem Version4.0 Subsystem0002 [ Windows GUI ] Object count 0004 Symbols offset Symbols count Optional header size 00E0 Magic # 10B Code size0002F000 Init Data size B000 Uninit Data size Entry RVA0002F38D Image base 1000 Code base1000 Data base0003 Object/File align1000/1000 Reserved Image size 0003B000 Header size 1000 Checksum Stack reserve/commit 0010/1000 Heap reserve/commit 0010/1000 Number interesting RVAs 0010 Name RVA Size -- Exports 00033780 0B07 Imports 0003348C 003C Resources Exceptions Security Fixups 00039000 1224 Debug Description TLS Callbacks reserved reserved0003 009C reserved reserved reserved Object table: # Name VirtSizeRVA PhysSize Phys off Flags -- 01 .text 0002E450 1000 0002F000 1000 6020 [CER] 02 .rdata4287 0003 5000 0003 4040 [IR] 03 .data 325C 00035000 4000 00035000 C040 [IRW] 04 .reloc1318 00039000 2000 00039000 4240 [IDR] Key to section flags: C - contains code D - discardable E - executable I - contains initialized data R - readable W - writeable Imports from KERNEL32.dll DeleteFileA(hint = 007c) GetFileAttributesA(hint = 0156) CreateFileA(hint = 004d) GetTempPathA(hint = 01cb) CloseHandle(hint = 002e) ReadFile(hint = 02ab) WriteFile(hint = 0397) SetFilePointer(hint = 0310) FlushFileBuffers(hint = 00e5) SetEndOfFile(hint = 0305) GetFileSize(hint = 015b) GetVersionExA(hint = 01df) UnlockFile(hint = 0363) Sleep(hint = 0349) LockFile(hint = 0259) LockFileEx(hint = 025a) GetSystemTime(hint = 01be) GetFullPathNameA(hint = 0161) GetSystemTimeAsFileTime(hint = 01c0) DisableThreadLibraryCalls(hint = 0084) Imports from MSVCRT.dll _isctype(hint = 0115) __mb_cur_max(hint = 0061) sprintf(hint = 02b2) _iob(hint = 0113) _ftol(hint = 00f1) strncmp(hint = 02c0) tolower(hint = 02d3) localtime(hint = 028d) atoi(hint = 023d) toupper(hint = 02d4) malloc(hint = 0291) free(hint = 025e) strncpy(hint = 02c1) realloc(hint = 02
Re: [sqlite] Multi-threading.
Andrew Piskorski wrote: On Fri, Jul 15, 2005 at 04:21:05PM +0300, Cariotoglou Mike wrote: memory and cpu-wise. on Linux, this is nothing, it can handle it easily. otoh, 500 threads for windows is business as usual, but threading on Linux, is , I hear, iffy at best. Linux runs multi-threaded apps (e.g., AOLserver) quite well, and has for many years - since at least 2000 or so, probably earlier. My understanding is that the old LinuxThreads implementation had some pretty ugly bits, but it worked. NPTL is much better, and is standard with the Linux 2.6.x kernels. Some architectures permit, or even encourage, multi-threaded design. It can be done obviously. However, Dr. Hipp still has a point. One thread can trash another's address space. They share code, global data, the heap (generally) and system object handles (files, sockets, IPC devices ( and weird crap like "Desktop" and "Mutants" on windows). The only non-shared things are the stack, TLS (thread local storage) and per-thread CPU context. Even then all of those things can be trashed by other threads in the same process. Unless you can _prove_ that your code won't do this (and all code that you call, including system DLLs / SOs) then you are taking a risk. Personally, I prefer multi-threaded code. I like to write it and I like to debug it. I ship it to customers. Your millage may vary. And yes, Linux threads used to be very unstable. I've only used Linux threads once, and it was on a recent 2.6 kernel, so I never experienced the problem(s).
Re: [sqlite] Multi-threading.
Roushan Ali wrote: Hi, Thanks for your response. I don't have any idea how multiple connection objects work. Can you please tell us something about that. I wrappered the C interface to SQLite3 via a C++ Class called "CSqlite3". The constructor does NOT open the database, it just allocates the sqlite struct. I declared 4 global instances of this class. The constructors get called before my WinMain(). In my initialization code (called before any threads are created), I open the database 4 times. I do an integrity check (and some other logic) after the first open. Like this: g_DbMain.Open(szFilename); CheckDatabase(g_DbMain); // "pragma integrity_check, create missing tables / schema updates, vacuum" g_DbTimer.Open(szFilename); g_DbThread2.Open(szFilename); g_DbThread3.Open(szFilename); I then create the worker threads. One of my threads does NOT use any database, so we'll ignore it. Another thread (main / gui) already exists, so I am really only creating threads #2 and #3. The thread function uses the database object as needed. After the worker threads terminate, the main thread closes all four database objects. The object's destructor is called when the application exits. I do not create new connections to the database while the executing. Please note that my solution is NOT appropriate if I wanted to create arbitrary threads at arbitrary times. If I were doing that, then each thread would create it's own database object on it's own TLS (thread local storage) or stack. I created all of my database "Open()" code into the main thread just to keep it all together. Each of my threads does a very specific function that is totally unique to that thread: 1. The main thread uses it's database connection to respond to user initiated GUI events. 2. The main thread also uses the "timer" database connection to handle WM_TIMER messages to update a status display synchronously (kinda). Because this function can be invoked while the thread has a transaction on the main connection, I need to use a different connection. One thread, but it must be fully re-entrant. 3. Thread #2 is a producer. It gathers data and inserts it into the database. 4. Thread #3 is a consumer. It takes data from the database and does stuff with them. It updates those rows. The timer connection only executes "select" to update the GUI. The main connection is used to query the database, update the database and to delete from the database. The application is what it is. I make no public claims about it being the best designed thing ever, but it does work well under stress.
Re: [sqlite] Multi-threading.
Roushan Ali wrote: Thanks Richard for your reply. Actually, we have written a windows application which uses four threads. Each thread may have to add/delete thousands of entries in the database( for performance reason , we don't want to open/close the database for each insertion/deletion) .If we use different sqlite_open handle for each thread , then one thread has to do busy looping until other threads complete their operation, which is not desirable according to the application requirement. That's why we opened global database handle for the lifetime of the application and each thread used the handle serially and it worked. We have a multi-threaded windows application with four threads. Three threads need access to the database (all three are producers and consumers), but one thread is the GUI thread and wants to access the database while handling WM_TIMER messages (re-entrency issues). So we allocate 4 database connections during initialization. Each section of our code uses its own connection. We have a special "stress test" mode that we can enable. The program remains stable after hours of operation under the stress test. The program will slow down because of the database locking mechanism (especially during large transactions), but it has never crashed due to multiple threads accessing the database used _different_ connection objects. If you are going to be multi-threaded, then why not just use multiple connection objects (structs - ours are wrapped in a C++ class)?
[sqlite] freepages.c ported to sqlite v3. Request feedback.
Hello. Several months ago I ported the "free pages" code from sqlite v2 to v3. I found the original at [1]. My copy is at [2]. I unsuccessfully tried to contact the original author. This code simply computes the amount of "slack space" in the database. Our database will sometimes grow quite large and then most of the rows will be deleted. We want to reclaim the space, but due to the way our application works, it is not efficient to vacuum the database right after we delete the rows in question. So, we periodically call the "free space" routine and vacuum our database if the amount of slack space exceeds a certain threshold. Good, bad or ugly, that is what we do :) I would like to see this code ultimately merged into the official sqlite database distribution (so that we are not distributing a "hacked" version). However, I am not an sqlite genius. I would very much appreciate some peer-review. (For example, I removed all of the database integrity checks b/c the v2 code failed on a v3 system. I also had to copy many struct definitions from other files as these structures were not exposed via header files. These hacks seem ugly to me but I'm not sure what to do about it). I grant my code to the community. I can be licensed under the current sqlite license. Your comments are appreciated. Thank you. [1] http://web.utk.edu/~jplyon/sqlite/code/freepages.c [2] http://unwg.no-ip.com/freepages.c
[sqlite] (hack) Updated calculation code for counting the number of free pages in a database
Hello, This is my first post to this mailing list. While reading through the Sqlite Wiki I came across an interesting function at 'http://web.utk.edu/~jplyon/sqlite/code/freepages.c' for counting the number of free pages in a database by James P. Lyon. I wanted to use this code in sqlite3, but the database structures have changes. So I hacked on it for a few hours and I think that I've managed to convert it into a function that is useful to me. I did not readily find an email address for James, so I'll post a link to my code here. http://unwg.no-ip.com/freepages.c In my application program I check the number of total pages and the number of free pages when the program starts up. I call "vacuum" if the number of free pages is more than 25% of the database size. (Items get inserted slowly and deleted in large batches (20 to 80% of the database at once); I use sqlite as a data cache). Unfortunately, I had to copy the "struct" definitions from "btree.c" and "pager.c" into the file. I feel that this is not a viable long term solution. Also, don't freak when you see the line "*lFree = get4byte((char*)page1 + 36);". Compare my hack with the previous author's. :) I was totally clueless about fixing the section of code in "lockBtree" that verifies the database file is valid. My attempts to load "page1" did not work as expected. So I just commented it out. :) I ask two things: 1) Did I do it correctly? The function gives back meaningful results to me. That is, I can take a database and vacuum it. There are zero free pages. I can then delete some rows and check the free page count; it is now positive. If I vacuum the database again, the free page count goes back to zero. However, I don't know if I'm actually getting the correct data element. "page1->adata" is NULL and "page1->nFree" is always "257", so I made an educated guess based on the last author's code. 2) Can this code be formally placed into sqlite so that I (and others) don't have to hack it in if we want to use it in future updates? Like the original author, I disclaim all copyrights. -- Dennis Jenkins
