Re: [sqlite] SET (x,y) = (x1,y1)?

[Dennis Jenkins]

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?

[Dennis Jenkins]

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?

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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?

[Dennis Jenkins]

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 Jenkins]

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++

[Dennis Jenkins]

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++

[Dennis Jenkins]

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

[Dennis Jenkins]

[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

[Dennis Jenkins]

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)

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

[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

[Dennis Jenkins]

   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]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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] Reading the same table from two threads

[Dennis Jenkins]

[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

[Dennis Jenkins]

[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] Locking

[Dennis Jenkins]

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

[Dennis Jenkins]

[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

[Dennis Jenkins]

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

[Dennis Jenkins]

[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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

[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 Jenkins]

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 Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

[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

[Dennis Jenkins]

[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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

[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?

[Dennis Jenkins]

[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 ?

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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?

[Dennis Jenkins]

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] Re: Thread safety guarantees

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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

[Dennis Jenkins]

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?

[Dennis Jenkins]

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?)

[Dennis Jenkins]

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?

[Dennis Jenkins]

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.

[Dennis Jenkins]

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]

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

[Dennis Jenkins]

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 = 

Re: [sqlite] Multi-threading.

[Dennis Jenkins]

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.

[Dennis Jenkins]

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.

[Dennis Jenkins]

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.

[Dennis Jenkins]

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

[Dennis Jenkins]

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