Re: [sqlite] Database corruption on Linux ext3

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/13/2010 05:30 PM, Jim Wilcoxson wrote:
> I don't think this would work, because the problem described is that the
> writes aren't making it to disk.   If pages don't make it to disk, the old
> pages will be present, with the old, and valid checksums.

You are assuming the checksums are stored in the page they checksum.  That
would only detect corruption of that page.  You could have pages that store
the checksums of numerous other pages, so both the checksum page and the
data page would have to fail to make it to disk.  Yes, there are scenarios
where you could still get old apparently valid pages, but those are harder
to happen.

> SQLite would have to verify the checksum on every page when the
> database is opened and a hot journal exists, which could be quite a lot of
> overhead for a large database.

I would want checksums looked at when reading pages always.  For more
valuable data I would want a thorough open check.  I already use the pragmas
(integrity/quick check) on opens as appropriate.

> I think a checksum on every page, and maybe even an error-correction code,
> is a great idea as an optional feature.

It would have to be optional, much like WAL for backwards compatibility
issues, and because not everyone would want this.

> But it would only detect hardware problems and bit rot.

Plus normal errors, such as the undetectable one every 20TB of I/O rate I
hope I remembered correctly from the ZFS paper.  I don't know what the error
rate of Flash is, nor how that interacts with SATA signalling error rates,
but all these numbers are non-zero and we keep doing more and more I/O, have
larger datasets, and have things around for longer.  Plus we do like buying
cheap storage :-)

> This problem of not doing writes, or doing them in
> the wrong order, is a different animal IMO.

There will always be loopholes you could construct with that that are very
hard to detect.  Hopefully checksumming or some sort of similar data
integrity scheme would make it easier to detect some of the scenarios.  ie
things could be made better than they are now.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkw9TJYACgkQmOOfHg372QRnkACfdJp+FZHvxXeLNN0DMCHSNJ1+
3E8AnR1PXa4PTQ1ridHBaAC7WpSSwnyM
=5sbp
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL: no longer able to use read-only databases?

[Max Vlasov]

>
> > In testing the latest SQLite snapshot with WAL enabled, it seems that
> > there's no way to use a database in a read-only location.
> >
>
> Documentation on the developers' current view of this issue can be found
> here:
>
>
>
In my opinion it's ok, WAL already has special conditions on which it would
operate and the current documentation describes them all thoroughly.  I wish
only the "advantages" sections of WAL also grew a little, maybe giving more
details about speed improvement encouraging using this mode more frequently

Thanks

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database corruption on Linux ext3

[Scott Hess]

On Tue, Jul 13, 2010 at 10:03 PM, Scott Hess  wrote:
> On Tue, Jul 13, 2010 at 8:24 PM, Simon Slavin  wrote:
>> It might be useful to figure out whether we're aiming for
>> detection or correction.  By 'correction' I don't mean recovery
>> of all information, I mean restoring the database to some state
>> it was in just after a COMMIT took effect.  There's no point in
>> implementing a detection system if the users consider "This
>> database is corrupt" something worth complaining about.  On the
>> other hand, implementing a correction system may well slow down
>> every write operation and perhaps '_open' too.  It's not worth
>> doing that if slowing down SQLite will decrease usability.
>
> The best case is a system where corruption cannot happen.  Since
> that's clearly impossible ...
>
> Second-best would be an ability to rollback to a priori valid state.

[Sigh, did not mean some whizzy technical term, there.  Meant "a prior
valid state."]

-scott
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database corruption on Linux ext3

[Scott Hess]

On Tue, Jul 13, 2010 at 8:24 PM, Simon Slavin  wrote:
> It might be useful to figure out whether we're aiming for
> detection or correction.  By 'correction' I don't mean recovery
> of all information, I mean restoring the database to some state
> it was in just after a COMMIT took effect.  There's no point in
> implementing a detection system if the users consider "This
> database is corrupt" something worth complaining about.  On the
> other hand, implementing a correction system may well slow down
> every write operation and perhaps '_open' too.  It's not worth
> doing that if slowing down SQLite will decrease usability.

The best case is a system where corruption cannot happen.  Since
that's clearly impossible ...

Second-best would be an ability to rollback to a priori valid state.
WAL should provide some of this, because over time the system should
tend towards having everything stably written to the disk.  You just
have to think about how much you're willing to lose.

Currently, there is a gray area where you've seen something which is
incorrect, but you cannot reason about how extensive the damage is.
Checksums can provide you with some ability to detect corruption more
quickly, and perhaps a way to reason about it.  You could have a
function like iscorrupt(rowid) which could be used to salvage
uncorrupted rows, and then carefully inspect corrupted rows.  Or maybe
you could delete the corrupt rows and then the table would be clean.
Perhaps there could even be an ON CORRUPTION trigger involved.

[Obviously, here I'm not thinking about the case where a DBA sits down
and reconstructs things.  I'm thinking about what you do when a
database in the field has corruption.  For instance, Chrome has a
history database, and being able to easily delete corrupted rows and
reconstruct indices would be an improvement over nuking the database
from orbit.  Without explicit database support, though, I've always
been nervous about attempting such things in an ad-hoc fashion.]

-scott
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database corruption on Linux ext3

[Simon Slavin]

On 14 Jul 2010, at 1:30am, Jim Wilcoxson wrote:

> This problem of not doing writes, or doing them in
> the wrong order, is a different animal IMO.

If writes are not happening, or are happening in the wrong order, you're in 
trouble.  It's almost impossible to figure out how to even detect that hardware 
problem without a time-consuming scan of each unit that should be written 
which, in SQLite, means reading every page.  Since SQLite doesn't run a server 
process, it has no opportunity to use slack time to check integrity.

Under the conditions described on the web page, this problem can happen only 
because of a power failure or an OS (not an application) crash.  Under these 
conditions, the ext3 file system doesn't support ACID at all: any system that 
relies on ACID is not going to work.  And if the file system doesn't support 
ACID, the software can't.  I don't see any fast way of solving that kind of 
problem.

It might be useful to figure out whether we're aiming for detection or 
correction.  By 'correction' I don't mean recovery of all information, I mean 
restoring the database to some state it was in just after a COMMIT took effect. 
 There's no point in implementing a detection system if the users consider 
"This database is corrupt" something worth complaining about.  On the other 
hand, implementing a correction system may well slow down every write operation 
and perhaps '_open' too.  It's not worth doing that if slowing down SQLite will 
decrease usability.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling as part of MFC C++ project

[John Drescher]

>> What do I need to do to get sqlite3.c to compile in a MFC C++ project
>> (Visual C++)? If I just add it to the project, I end up getting a
>> compile error something like: "Unexpected end of file while searching
>> for pre-compiled header directive".
>>
>
> This is a basic Visual C++ question.
>
> http://www.delphifaq.com/faq/f923.shtml
>
Sorry for the noise. I guess I should not try reading mail while
playing a flash game in a second window..
John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling as part of MFC C++ project

[Jim Morris]

You need to use the project properties to set the file as a C file and 
to not use precompiled headers

On 7/13/2010 5:47 PM, GHCS Software wrote:
> What do I need to do to get sqlite3.c to compile in a MFC C++ project
> (Visual C++)? If I just add it to the project, I end up getting a
> compile error something like: "Unexpected end of file while searching
> for pre-compiled header directive".
>
> Do I need to rename it "sqlite3.cpp" and put the include of
> at the top? Or what??
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling as part of MFC C++ project

[John Drescher]

> What do I need to do to get sqlite3.c to compile in a MFC C++ project
> (Visual C++)? If I just add it to the project, I end up getting a
> compile error something like: "Unexpected end of file while searching
> for pre-compiled header directive".
>

This is a basic Visual C++ question.

http://www.delphifaq.com/faq/f923.shtml

John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compiling as part of MFC C++ project

[GHCS Software]

What do I need to do to get sqlite3.c to compile in a MFC C++ project 
(Visual C++)? If I just add it to the project, I end up getting a 
compile error something like: "Unexpected end of file while searching 
for pre-compiled header directive".

Do I need to rename it "sqlite3.cpp" and put the include of  
at the top? Or what??
-- 
Doug Gordon
*GHCS Software*
http://www.ghcssoftware.com
g...@ghcssoftware.com 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database corruption on Linux ext3

[Jim Wilcoxson]

On Tue, Jul 13, 2010 at 8:06 PM, Roger Binns  wrote:

>
> On 07/13/2010 04:57 PM, Simon Slavin wrote:
> > One on each page and one for the entire file that checksums the page
> checksums ?
>
> One for each page plus one of the header would make the most sense, but the
>

I don't think this would work, because the problem described is that the
writes aren't making it to disk.   If pages don't make it to disk, the old
pages will be present, with the old, and valid checksums.

The only way I can see checksums helping with this problem is if there is a
checksum over the entire file (or checksums of checksums of each page).
Then if you do any writes, but not all writes, the overall checksum will be
invalid.  SQLite would have to verify the checksum on every page when the
database is opened and a hot journal exists, which could be quite a lot of
overhead for a large database.

Plus, SQLite would have to keep a list of the checksums for every page, and
at commit time, recompute the overall hash/checksum.  This could be lots of
memory for a large database.  A 1GB database for example would require 1M
20-byte SHA1 hashes, so 20MB.  If a bit for every page in the database was a
scalability problem in earlier versions of SQLite, I'm guessing that 20
bytes for every page would be unworkable.

I think a checksum on every page, and maybe even an error-correction code,
is a great idea as an optional feature.  But it would only detect hardware
problems and bit rot.  This problem of not doing writes, or doing them in
the wrong order, is a different animal IMO.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://sites.google.com/site/hashbackup
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database corruption on Linux ext3

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/13/2010 04:57 PM, Simon Slavin wrote:
> One on each page and one for the entire file that checksums the page 
> checksums ?

One for each page plus one of the header would make the most sense, but the
overriding concern would be something that is as backwards and forwards
compatible as possible.  The journal would also need to be covered, as well
as some indication linking the database and the journal.

Currently if you wrote a stray zero somewhere in the file then the chances
of it being detected are virtually zero.  It may even be possible to have
entire sectors/512 bytes revert to all zeroes or 0xff without it being detected.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkw8/48ACgkQmOOfHg372QTZ8ACfVIvqoavqysTwII+c8N13gLp2
7XUAoIa/D3j8+DmuYtT/6I9TAjOBGgP7
=5lT2
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database corruption on Linux ext3

[Simon Slavin]

On 13 Jul 2010, at 9:26pm, Roger Binns wrote:

> On 07/13/2010 12:59 PM, D. Richard Hipp wrote:
>> You are encouraged to submit comments, insights, criticism, and  
>> analysis to this mailing list.  Thanks.
> 
> Have you considered adding internal checksums to SQLite files so that at the
> very least corruption can be detected?

One on each page and one for the entire file that checksums the page checksums 
?  I've seen that done elsewhere.  Perhaps instead one for each thing that 
needs its own pages (e.g. one for each table and index).  Or just one for each 
table which includes the index data in with it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[Eric Smith]

Griggs, Donald wrote: 

> I guess I was wondering if the fastest records-per-transaction value 
> would depend on the page cache and be more or less independent of the 
> total records to be imported.  

I think the page cache is one of a great many variables.

> So, the records-per-transaction for import to a 20 million row table 
> should be twenty times the size for a 1 million row table?  

I'm no sqlite or sql guru myself, so with a grain of salt: 

If you have no reason to commit in the middle of a batch, then don't 
do it.  I think inserting all the rows in a single go will give you the 
best insert performance in most use cases.  

The idea is that there is some fixed overhead (call it O) that SQLite 
has to go through every time it commits a transaction.  The overhead is 
'fixed' because it is independent of the number of rows you inserted.  
If you insert 1m rows and commit every 500, the total commit overhead is 
2000*O.  If you commit just once, the total commit overhead is just O.  

This argument is likely a small or big lie for a number of reasons, but 
is at least a push in the right direction.  

Eric 

-- 
Eric A. Smith

The problem with engineers is that they tend to cheat in order to get results.

The problem with mathematicians is that they tend to work on toy problems 
in order to get results.

The problem with program verifiers is that they tend to cheat at toy problems 
in order to get results.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[Griggs, Donald]

 


Griggs, Donald wrote: 

> Is the percentage of the final rowcount really a criterion?  

The answer to that, according to my brief exploration, is somewhere between 
"yes" and "very much yes", depending on various factors.

Thanks, Eric.  I guess I was wondering if the fastest records-per-transaction 
value would depend on the page cache and be more or less independent of the 
total records to be imported. (Indicies omitted.)

So, the records-per-transaction for import to a 20 million row table should be 
twenty times the size for a 1 million row table?

I confess I've got a lot to learn.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regression with sqlite-snapshot-201007091257

[Filip Navara]

Hi Shane!

I used two separate builds. One was built with the following command line:

cl /DSQLITE_ENABLE_FTS3=1 /DSQLITE_ENABLE_COLUMN_METADATA=1
/DTHREADSAFE=1 /O2 /LD sqlite3.c sqlite3.def

and the other was built from inside Visual Studio 2010 UI and reports
the following compile options:

sqlite> select sqlite_source_id();
2010-07-09 12:57:54 0c32c4bbdd74297767dcf4ec4295f9cc72875af0
sqlite> PRAGMA compile_options;
DEBUG
ENABLE_COLUMN_METADATA
ENABLE_FTS3
TEMP_STORE=1
THREADSAFE=1

Both suffer from the same problem and I tested it on various different
database files.

The reason why you probably can't reproduce it is because the problem
manifests only when other application blocks access to the journal
file. In my case the other application is TortoiseSVN, specifically
the TSVNCache process. In fact that was the original reason why we
started using journal_mode=truncate, which was supposed to avoid
deleting the journal file, but for some reason the new SQLite version
tries to do it anyway while the older one didn't.

Best regards,
Filip Navara

On Tue, Jul 13, 2010 at 11:21 PM, Shane Harrelson  wrote:
> I tried to reproduce the issue with the latest version, as well as with the
> referenced snapshot, and could not.
> I ran two instances of the sqlite CLI as you indicated without issue.
>
> C:\work\sqlite\win32\Debug>sqlite_snapshot test.db
> SQLite version 3.7.0
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> pragma encoding="UTF-8";
> sqlite> pragma auto_vacuum=incremental;
> sqlite> pragma journal_mode=truncate;
> truncate
> sqlite> select sqlite_version();
> 3.7.0
> sqlite> select sqlite_source_id();
> 2010-07-09 12:57:54 0c32c4bbdd74297767dcf4ec4295f9cc72875af0
> sqlite> PRAGMA compile_options;
> DEBUG
> OMIT_LOAD_EXTENSION
> TEMP_STORE=1
> TEST
> THREADSAFE=1
> sqlite>
>
> I included the output of version, source_id, and compile_options for
> reference.
>
> What options are you compiling with?  Is there perhaps a journal file
> somewhere that you're unaware of?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[Eric Smith]

Werner Smit wrote: 

> My question(s) 
> If I want to use the "insert or replace" to populate my database of 
> around 1 million records.  
> And I want to do it as fast as possible.  
> What are all the tricks I can use?  

Obey the first rule of optimization: don't do it unless you're 
sure you need to.

If you're sure you need to, here are a few hints from my own 
experience.  They all come with trade-offs.  

Buy faster hardware with more memory.  I am serious.  Sometimes it is 
much cheaper to wait for 6 months for CPU speeds and memory sizes to 
increase than to spend time optimizing your code.  

Don't define triggers, indices, unique constraints, check constraints, 
or primary keys.  Make sure foreign key checking is off (which it is by 
default).  

Give sqlite a large page cache.  If you are 32 bits then sqlite can't use 
more than 4Gb of memory, so keep that in mind.  If you give sqlite more 
memory than exists on your machine, you might go to swap hell, so don't 
do that.  

If you are CPU bound and if you can split your problem into orthogonal
chunks and if you have multiple CPUs, consider farming out the work to 
worker processes and incorporating their results into the main database.

Depending on your use-case you can consider telling your operating
system to favor the disk cache over processes' memory when you are
running low on RAM.  In linux this is accomplished by setting 'swappiness' 
high, not sure about other OSs.

> I had a count(*) to check how many inserts was actually done(4 progress 
> bar) - and this slowed my down very much.  

That's because count(*) doesn't run in constant time.  I'm not sure, but
it may be linear.  Which would imply that your algo as a whole is
quadratic instead of its original (likely constant) asymptotic behavior.

> Took it out, and want to use "select total_changes() " to keep track of 
> inserts.  Any problem with that?  

You sound like you are writing a multi-threaded program.  Are you sure 
that total_changes() is only counting changes due to your insertions?  

Consider keeping a loop execution counter and using that for your status 
bar.  

Good luck,
Eric 

-- 
Eric A. Smith

Electricity is actually made up of extremely tiny particles 
called electrons, that you cannot see with the naked eye unless 
you have been drinking.
-- Dave Barry
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[Eric Smith]

Griggs, Donald wrote: 

> Is the percentage of the final rowcount really a criterion?  

The answer to that, according to my brief exploration, is somewhere 
between "yes" and "very much yes", depending on various factors.

-- 
Eric A. Smith

The number of UNIX installations has grown to 10, with more expected.
-- The Unix Programmer's Manual, 2nd Edition, June 1972
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regression with sqlite-snapshot-201007091257

[Shane Harrelson]

I tried to reproduce the issue with the latest version, as well as with the
referenced snapshot, and could not.
I ran two instances of the sqlite CLI as you indicated without issue.

C:\work\sqlite\win32\Debug>sqlite_snapshot test.db
SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma encoding="UTF-8";
sqlite> pragma auto_vacuum=incremental;
sqlite> pragma journal_mode=truncate;
truncate
sqlite> select sqlite_version();
3.7.0
sqlite> select sqlite_source_id();
2010-07-09 12:57:54 0c32c4bbdd74297767dcf4ec4295f9cc72875af0
sqlite> PRAGMA compile_options;
DEBUG
OMIT_LOAD_EXTENSION
TEMP_STORE=1
TEST
THREADSAFE=1
sqlite>

I included the output of version, source_id, and compile_options for
reference.

What options are you compiling with?  Is there perhaps a journal file
somewhere that you're unaware of?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database corruption on Linux ext3

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/13/2010 12:59 PM, D. Richard Hipp wrote:
> You are encouraged to submit comments, insights, criticism, and  
> analysis to this mailing list.  Thanks.

Have you considered adding internal checksums to SQLite files so that at the
very least corruption can be detected?  I think this is one of the best
features of the current crop of version control systems.

IIRC one of the original papers describing ZFS claimed an undetected error
rate of once every 20TB of activity with hard drives.  That is not an
unreasonable amount of disk access in one day now.

Errors could of course instead be detected by using better filesystems,
drives, protocols etc, but we often don't have the luxury of dictating what
systems SQLite databases will be used on.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkw8y/IACgkQmOOfHg372QTWfgCgjcPCvOLQuPv1Xqc6XM3TkXLY
X80An2TFiktmQxOY0sMxO1VLD74YzYpX
=tJ9V
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] open db cx to fork(2)d children

[Nicolas Williams]

On Mon, Jul 12, 2010 at 11:25:07PM -0700, Roger Binns wrote:
> > No, just entry points into the library.  What makes you think that I
> > meant that every function in the library should check this?
> 
> I also meant entry points but wasn't specific.  There are a lot of them that
> acquire/release mutexes and every single one would need to be modified. That
> was my original approach until I hit on the idea of swizzling the mutexes
> instead.

There's about ~170 entry points, depending on which version of SQLite3.
There's about ~110 calls to sqlite3_mutex_enter(), and about as many to
drop mutexes.  Some mutex enter/leave calls might occur in loops
(haven't checked), but already ~170 < ~220 :)  Not that such a static
analysis says much.  Most apps probably use a small sub-set of entry
points, and estimating the number of mutex calls on average per-call to
one of those entry points is non-trivial.  But the point is that I would
generally expect #-of-entry-points < #-of-mutex-calls -- that needn't
always be true, but it's probably a good rule of thumb.

Your approach does allow you to implement fork-safety without modifying
SQLite3 source itself, which is pretty cool.  But that's not necessarily
how the SQLite3 maintainers might do it.

> > but when it comes to
> > POSIX file byte range locking (at least for Solaris anyways, which is
> > what I know best, implementation-wise) you can assume that the only
> > broken thing about them is their semantics, not their implementations.
> 
> Having spent a lot of my commercial programming life with many different
> Unix implementations, including Solaris, I do not trust them, especially for
> little used functionality.  And Solaris had many bugs in the past, but I
> haven't touched it in the last few years to form a recent opinion.  (Like
> this one time when I found Sun's NFS implementation was broken based on log
> messages on an HPUX machine and then doing packet capture, and Sun invented
> NFS ...)
> 
> In any event this roughly comes down to me saying to assume they are
> untrustworthy until it can be substantially be proven otherwise, and you
> seeming to believe that they are all correct seemingly based on
> extrapolating from a recent version of Solaris.  Or being a pessimist and
> optimist respectively :-)
> 
> This doesn't really matter.  [...]

I can't argue with detail-free anecdotes.  And I'm sure there have been
bugs.  I do have access to the Solaris bug database, so I could search
it, but by then I'd be spending an enormous amount of time just to argue
a rather minor point.  If you fill in some details I will do a search
for the bugs in question though (since details will help narrow down the
search).

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database corruption on Linux ext3

[D. Richard Hipp]

An appliance manufacturer has discovered a database corruption issue  
on Linux using ext3.   The issue is documented here:

 http://www.sqlite.org/draft/lockingv3.html#ext3-barrier-problem

You are encouraged to submit comments, insights, criticism, and  
analysis to this mailing list.  Thanks.

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL: no longer able to use read-only databases?

[Richard Hipp]

On Fri, Jul 9, 2010 at 3:21 PM, Matthew L. Creech wrote:

> In testing the latest SQLite snapshot with WAL enabled, it seems that
> there's no way to use a database in a read-only location.
>

Documentation on the developers' current view of this issue can be found
here:

http://www.sqlite.org/draft/wal.html#readonly

If you have any comments and complaints, please send them to this mailing
list.  Thanks.



-- 
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ticks to unixepoch date

[Griggs, Donald]

Hi Roberto,

Re:  Please find a test database in the attachment.

I don't think attachments are permitted on the mailing list messages.

If it's only ten rows, perhaps just using the command line utility to perform a

.dump

Command and pasting the text into a new message would do the trick. 

Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ticks to unixepoch date

[Roberto Dalmonte]

Thanks for your answer.
Please find a test database in the attachment.
As you can see I have 1 table with 10 records, and the columns StartDate 
and EndDate represent a DateTime field, where the values are saved as ticks.
There is a view that tries to convert the ticks into unixepoch values, 
but I receive a floating point error.

Can you please take a look at it?
Best Regards
Roberto Dalmonte
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[Simon Slavin]

On 13 Jul 2010, at 5:33pm, Werner Smit wrote:

> I currently use the following pragma's (for speed)
> temp_store = 2
> page_size=4096
> cache_size=8192
> synchronous=off
> 
> Any others I could try?

Don't get too involved in the PRAGMAs until you have a good reason to.  The 
default values are pretty good.  How much slower is it than you want it to be ? 
 If you need, say, 5% improvement we might suggest some things, but if you need 
50% improvement we might suggest more radical (and harder to program) 
solutions.  If you're optimizing just for the sake of it, find something better 
to do.

If you're doing a huge amount of database loading first it's faster to do it 
before you create any INDEXes, then to create your INDEXes once your TABLEs are 
populated.  Once your database is initialised do you expect to do more reads or 
more writes ?  Which one you do more of suggests how many INDEXes you should 
define.

> I also wrap my statements (about 500 inserts at a time) with a begin/end
> transaction.

As JD wrote, at 500 writes in a transaction you're wasting a lot of time in 
overheads.  Try 50,000.

> After these 500 i take a few seconds to read more data so sqlite should
> have time to do any housekeeping it might need.

Unlike, for example MySQL, SQLite does nothing in the background.  The only 
functions it runs are the ones you call directly: it has no server process and 
no daemon.  However, your hardware may be caching writes or something, so your 
hardware may be taking advantage of the pauses.

> I had a count(*) to check how many inserts was actually done(4 progress
> bar) - and this slowed my down very much.
> Took it out, and want to use "select total_changes() " to keep track of
> inserts.

Much better.  As an alternative (and I'm not saying it's better than what you 
already have) take a look at

http://www.sqlite.org/c3ref/last_insert_rowid.html

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[John Drescher]

On Tue, Jul 13, 2010 at 12:48 PM, John Drescher  wrote:
>> I was wondering if that's really so.  Wouldn't the marginal speed 
>> improvement be quite small?  Is the percentage of the final rowcount really 
>> a criterion?
>
> Each transaction costs at least 1 disk seek. Doing thousands of seeks
> the result would be very slow.
>

I guess synchronous=off eliminates this flushing / seeking though.

John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[John Drescher]

> I was wondering if that's really so.  Wouldn't the marginal speed improvement 
> be quite small?  Is the percentage of the final rowcount really a criterion?

Each transaction costs at least 1 disk seek. Doing thousands of seeks
the result would be very slow.

John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of John Drescher
Sent: Tuesday, July 13, 2010 12:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sqlite Insert Speed Optimization

> I also wrap my statements (about 500 inserts at a time) with a 
> begin/end transaction.
> After these 500 i take a few seconds to read more data so sqlite 
> should have time to do any housekeeping it might need.
>

Wrap more into a transaction. 500 is too small of a percentage of a million.

John


John,

I was wondering if that's really so.  Wouldn't the marginal speed improvement 
be quite small?  Is the percentage of the final rowcount really a criterion?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[Pavel Ivanov]

> After these 500 i take a few seconds to read more data so sqlite should
> have time to do any housekeeping it might need.

SQLite is not a Database Server. It has no background threads. So it
can't do any "housekeeping" until you call some sqlite3_* function.


Pavel

On Tue, Jul 13, 2010 at 12:33 PM, Werner Smit  wrote:
> Hi there.
>
> I've been playing around with sqlite.
> Very impressed so far.
> Using 3.5.6 in windows developing with Clarion.
>
> My question(s)
> If I want to use the "insert or replace" to populate my database of
> around 1 million records.
> And I want to do it as fast as possible.
> What are all the tricks I can use?
>
>
> I've read http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
> And looked at some of the various pragmas.
>
>
> But would PRAGMA locking_mode = EXCLUSIVE for instance give me an extra
> speed increase?
> Do we have a page in www.sqlite.org dedicated to speed optimization?
>
>
> I currently use the following pragma's (for speed)
> temp_store = 2
> page_size=4096
> cache_size=8192
> synchronous=off
>
> Any others I could try?
>
> I also wrap my statements (about 500 inserts at a time) with a begin/end
> transaction.
> After these 500 i take a few seconds to read more data so sqlite should
> have time to do any housekeeping it might need.
>
>
> I had a count(*) to check how many inserts was actually done(4 progress
> bar) - and this slowed my down very much.
> Took it out, and want to use "select total_changes() " to keep track of
> inserts.
> Any problem with that?
>
>
> This is my 1st post here and I have a feeling I'm asking to many
> questions at once? :-)
>
> Cheers,
> Werner
>
>
>
>
> DISCLAIMER:
> Everything in this email and its attachments relating to the official 
> business of Face Technologies is proprietary to Face Technologies. It is 
> confidential, legally privileged and protected by law. The person addressed 
> in the email is the sole authorised recipient. Any unauthorized dissemination 
> or copying of this e-mail (or any attachment to this e-mail) or the wrongful 
> disclosure of the information herein contained is prohibited.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Insert Speed Optimization

[John Drescher]

> I also wrap my statements (about 500 inserts at a time) with a begin/end
> transaction.
> After these 500 i take a few seconds to read more data so sqlite should
> have time to do any housekeeping it might need.
>

Wrap more into a transaction. 500 is too small of a percentage of a million.

John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite Insert Speed Optimization

[Werner Smit]

Hi there.

I've been playing around with sqlite.
Very impressed so far.
Using 3.5.6 in windows developing with Clarion.

My question(s)
If I want to use the "insert or replace" to populate my database of
around 1 million records.
And I want to do it as fast as possible.
What are all the tricks I can use?


I've read http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
And looked at some of the various pragmas.


But would PRAGMA locking_mode = EXCLUSIVE for instance give me an extra
speed increase?
Do we have a page in www.sqlite.org dedicated to speed optimization?


I currently use the following pragma's (for speed)
temp_store = 2
page_size=4096
cache_size=8192
synchronous=off

Any others I could try?

I also wrap my statements (about 500 inserts at a time) with a begin/end
transaction.
After these 500 i take a few seconds to read more data so sqlite should
have time to do any housekeeping it might need.


I had a count(*) to check how many inserts was actually done(4 progress
bar) - and this slowed my down very much.
Took it out, and want to use "select total_changes() " to keep track of
inserts.
Any problem with that?


This is my 1st post here and I have a feeling I'm asking to many
questions at once? :-)

Cheers,
Werner




DISCLAIMER:
Everything in this email and its attachments relating to the official business 
of Face Technologies is proprietary to Face Technologies. It is confidential, 
legally privileged and protected by law. The person addressed in the email is 
the sole authorised recipient. Any unauthorized dissemination or copying of 
this e-mail (or any attachment to this e-mail) or the wrongful disclosure of 
the information herein contained is prohibited.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regression with sqlite-snapshot-201007091257

[Filip Navara]

Steps to reproduce, run sqlite3 test.db twice. The first instance:

SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma encoding="UTF-8";
sqlite> pragma auto_vacuum=incremental;
sqlite> pragma journal_mode=truncate;
truncate

The second instance:

SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma encoding="UTF-8";
sqlite> pragma auto_vacuum=incremental;
Error: disk I/O error

Sorry for the spamming.

Best regards,
Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regression with sqlite-snapshot-201007091257

[Filip Navara]

The second problem is definitely related to the change in the
implementation of "pragma journal_mode" though I am not completely
sure how. The previous version of our code did

pragma encoding="UTF-8";
pragma auto_vacuum=incremental;
pragma journal_mode=truncate;

after opening the database file. When I change the code to

pragma main.journal_mode=truncate;
pragma encoding="UTF-8";
pragma auto_vacuum=incremental;

the problem doesn't happen anymore. Even if I use this code sequence
SQLite still tries to access the WAL file.

F.

On Tue, Jul 13, 2010 at 5:23 PM, Filip Navara  wrote:
> Btw, the failing application call is DeleteFileW.
> F.
>
> On Tue, Jul 13, 2010 at 5:02 PM, Filip Navara 
> wrote:
>>
>> Hi,
>> for a few days we have been running our application with SQLite built from
>> the 201007091257 snapshot. The application runs on Windows 7 systems that
>> are fully updated. We didn't enable the WAL mode and the following two
>> problems occurred:
>> - Every time a database file is opened SQLite tries to access the WAL file
>> even though WAL was never used on the database.
>> - Some commands fail intermittently when two connections are opened to the
>> same database. An SQLITE_IOERR error is returned.
>> Log from Process Monitor are available at
>> http://www.emclient.com/temp/logfile-3.6.21.1.txt
>> http://www.emclient.com/temp/logfile-3.6.21.1.pml
>> http://www.emclient.com/temp/logfile-3.7.txt
>> http://www.emclient.com/temp/logfile-3.7.pml
>> Apparently the second problem is caused by the following call that wasn't
>> present in the 3.6.21.1 logs:
>> 16:53:00,5982911 MailClient.exe 2712 CreateFile C:\Users\Filip
>> Navara\AppData\Roaming\eM Client\main.dat-journal SHARING VIOLATION Desired
>> Access: Read Attributes, Delete, Disposition: Open, Options: Non-Directory
>> File, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete,
>> AllocationSize: n/a
>> We'd welcome any advice on solving the issue and we will provide any
>> additional information requested.
>> Best regards,
>> Filip Navara
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ticks to unixepoch date

[Black, Michael (IS)]

Can you give us the values in your table?
 
This works for me on 3.6.23.1
 
create table t (i integer);
insert into t values(634355968);
select datetime((i/100)-62135596800,'unixepoch') from t as expr1;
2011-03-13 07:06:40
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Roberto Dalmonte
Sent: Tue 7/13/2010 10:20 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] Ticks to unixepoch date



Thanks for pointing that out Israel.
I had the right value (-62135596800) but I mistakenly pasted something else.
Nonetheless I get the following error:

invalid floating point operation
Any idea to make that statement work?
Best Regards
Roberto Dalmonte
Il 13/07/2010 16:46, Israel Lins Albuquerque ha scritto:
> Let me understand you...
>
> if tick is 0 the date id 0001/01/01 12:00:00?
> if yes the select do you want is
>
> SELECT datetime((TICK_VALUE / 100) - 62135553600, 'unixepoch') AS Expr1
>
>
> use that site for help!
> http://www.epochconverter.com/
>
>
>
> - "Roberto Dalmonte"  escreveu:
>   
>> In theory it should be possible to do it right now using the following
>> syntax ...
>>
>> SELECT datetime((columnAsTicks / 100) - 186796800,
>> 'unixepoch') AS Expr1
>> FROM Table
>>
>> ...unfortunately it doesn't work, at least the way I tried it.
>>
>> The operation is the following:
>> 1) transforms the ticks in seconds (divide ticks per 1 million);
>> 2) subtract the seconds passed from date 0001/01/01 (starting date in
>> System.DateTime) to 1970/1/1 (starting date in Unixepoch)
>> 3) Use the result with the built-in SQLite function datetime.
>>
>> This way you could build a view and be able to see a normal date instead
>> of ticks.
>>
>> Any idea?
>> Roberto
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] noobie Is there an additional web site for noo bie questions.??

[Monte Milanuk]

B H  writes:

> 
> also is there a web site or blog for questions and answers in additional to 
the email? 
> 

The mailing list is pretty good about answering most questions promptly and 
simply.

If you're looking for a more 'visual' intro to SQLite, you may want to check 
out some of the videos on YouTube by 'jaynonomous1'.  Go to YouTube and search 
for 'sqlite tutorial' and it should be one of the top listed results.  I know 
they helped me out a bunch.  I've still got lots of questions ;) but they 
definitely helped.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] noobie where is my data base? I am new

[Monte Milanuk]

B H  writes:

> 
> Downloaded sqlite to desktop.
> ran getting started.
> so where is  my example ex1.
> or How do and where can I save the ex data base and related tables.

As an example, create a directory off your Documents directory named 'sqlite', 
and copy the sqlite3 file into that directory.  From there, if you open up a 
command prompt window and maneuver to that directory and type 'sqlite3 ex1' and 
then create a simple table when you exit sqlite and type 'dir' (assuming M$ 
Windows here), you should see a new file 'ex1' right there.  All your tables 
and such for that database 'ex1' are in that file.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regression with sqlite-snapshot-201007091257

[Filip Navara]

Btw, the failing application call is DeleteFileW.

F.

On Tue, Jul 13, 2010 at 5:02 PM, Filip Navara wrote:

> Hi,
>
> for a few days we have been running our application with SQLite built from
> the 201007091257 snapshot. The application runs on Windows 7 systems that
> are fully updated. We didn't enable the WAL mode and the following two
> problems occurred:
>
> - Every time a database file is opened SQLite tries to access the WAL file
> even though WAL was never used on the database.
> - Some commands fail intermittently when two connections are opened to the
> same database. An SQLITE_IOERR error is returned.
>
> Log from Process Monitor are available at
> http://www.emclient.com/temp/logfile-3.6.21.1.txt
> http://www.emclient.com/temp/logfile-3.6.21.1.pml
> http://www.emclient.com/temp/logfile-3.7.txt
> http://www.emclient.com/temp/logfile-3.7.pml
>
> Apparently the second problem is caused by the following call that wasn't
> present in the 3.6.21.1 logs:
> 16:53:00,5982911 MailClient.exe 2712 CreateFile C:\Users\Filip
> Navara\AppData\Roaming\eM Client\main.dat-journal SHARING VIOLATION Desired
> Access: Read Attributes, Delete, Disposition: Open, Options: Non-Directory
> File, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete,
> AllocationSize: n/a
>
> We'd welcome any advice on solving the issue and we will provide any
> additional information requested.
>
> Best regards,
> Filip Navara
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ticks to unixepoch date

[Roberto Dalmonte]

Thanks for pointing that out Israel.
I had the right value (-62135596800) but I mistakenly pasted something else.
Nonetheless I get the following error:

invalid floating point operation
Any idea to make that statement work?
Best Regards
Roberto Dalmonte
Il 13/07/2010 16:46, Israel Lins Albuquerque ha scritto:
> Let me understand you...
>
> if tick is 0 the date id 0001/01/01 12:00:00?
> if yes the select do you want is
>
> SELECT datetime((TICK_VALUE / 100) - 62135553600, 'unixepoch') AS Expr1
>
>
> use that site for help!
> http://www.epochconverter.com/
>
>
>
> - "Roberto Dalmonte"  escreveu:
>
>> In theory it should be possible to do it right now using the following
>> syntax ...
>>
>> SELECT datetime((columnAsTicks / 100) - 186796800,
>> 'unixepoch') AS Expr1
>> FROM Table
>>
>> ...unfortunately it doesn't work, at least the way I tried it.
>>
>> The operation is the following:
>> 1) transforms the ticks in seconds (divide ticks per 1 million);
>> 2) subtract the seconds passed from date 0001/01/01 (starting date in
>> System.DateTime) to 1970/1/1 (starting date in Unixepoch)
>> 3) Use the result with the built-in SQLite function datetime.
>>
>> This way you could build a view and be able to see a normal date instead
>> of ticks.
>>
>> Any idea?
>> Roberto
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Regression with sqlite-snapshot-201007091257

[Filip Navara]

Hi,

for a few days we have been running our application with SQLite built from
the 201007091257 snapshot. The application runs on Windows 7 systems that
are fully updated. We didn't enable the WAL mode and the following two
problems occurred:

- Every time a database file is opened SQLite tries to access the WAL file
even though WAL was never used on the database.
- Some commands fail intermittently when two connections are opened to the
same database. An SQLITE_IOERR error is returned.

Log from Process Monitor are available at
http://www.emclient.com/temp/logfile-3.6.21.1.txt
http://www.emclient.com/temp/logfile-3.6.21.1.pml
http://www.emclient.com/temp/logfile-3.7.txt
http://www.emclient.com/temp/logfile-3.7.pml

Apparently the second problem is caused by the following call that wasn't
present in the 3.6.21.1 logs:
16:53:00,5982911 MailClient.exe 2712 CreateFile C:\Users\Filip
Navara\AppData\Roaming\eM Client\main.dat-journal SHARING VIOLATION Desired
Access: Read Attributes, Delete, Disposition: Open, Options: Non-Directory
File, Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete,
AllocationSize: n/a

We'd welcome any advice on solving the issue and we will provide any
additional information requested.

Best regards,
Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ticks to unixepoch date

[Israel Lins Albuquerque]

Let me understand you... 

if tick is 0 the date id 0001/01/01 12:00:00? 
if yes the select do you want is 

SELECT datetime((TICK_VALUE / 100) - 62135553600, 'unixepoch') AS Expr1 


use that site for help! 
http://www.epochconverter.com/ 



- "Roberto Dalmonte"  escreveu: 
> In theory it should be possible to do it right now using the following 
> syntax ... 
> 
> SELECT datetime((columnAsTicks / 100) - 186796800, 
> 'unixepoch') AS Expr1 
> FROM Table 
> 
> ...unfortunately it doesn't work, at least the way I tried it. 
> 
> The operation is the following: 
> 1) transforms the ticks in seconds (divide ticks per 1 million); 
> 2) subtract the seconds passed from date 0001/01/01 (starting date in 
> System.DateTime) to 1970/1/1 (starting date in Unixepoch) 
> 3) Use the result with the built-in SQLite function datetime. 
> 
> This way you could build a view and be able to see a normal date instead 
> of ticks. 
> 
> Any idea? 
> Roberto 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 

-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] work-arounds for (possibly buggy) nfs?

[Eric Smith]

Richard Hipp wrote:

> Try using:
> 
> sqlite3 db ./foo -vfs unix-dotfile

When my application dies while holding a lock under unix-dotfile, all
subsequent runs always think the database is locked.  I guess this is
because the lock file exists.

Is there an obvious/good way to know whether the lock is really being
held?

Eric

--
Eric A. Smith

Stocks have reached what looks like a permanently high plateau.
-- Irving Fisher, Professor of Economics, Yale University, 1929.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ticks to unixepoch date

[Roberto Dalmonte]

In theory it should be possible to do it right now using the following 
syntax ...

SELECTdatetime((columnAsTicks / 100) - 186796800, 
'unixepoch') AS Expr1
FROMTable

...unfortunately it doesn't work, at least the way I tried it.

The operation is the following:
1) transforms the ticks in seconds (divide ticks per 1 million);
2) subtract the seconds passed from date  0001/01/01 (starting date in 
System.DateTime) to 1970/1/1 (starting date in Unixepoch)
3) Use the result with the built-in SQLite function datetime.

This way you could build a view and be able to see a normal date instead 
of ticks.

Any idea?
Roberto
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] order of select result

[Simon Slavin]

On 13 Jul 2010, at 6:53am, Lloyd wrote:

> I dont have any supportive column in table to issue "order 
> by". Can it be achieved by issuing an "ordr by" query on "row_id" (used by 
> sqlite internaly)?

No consistently.  Because unless you're using a rowid column you declared 
yourself, even the values in the rowid column can change.  Either declare your 
own rowid column (you can add a new column to a TABLE after it was originally 
declared), or declare a column even better suited to ordering your rows.

Simon.

PS: New text /below/ text you're quoting, please.  English is read from top to 
bottom.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SELECT TicksToDateTime(AppointmentStart)

[Roberto Dalmonte]

Hi, I'm a C# developer and happy user of a SQLite database.
I use System.Data.SQLite dll and  I'm saving datetime data asSystem. 
DateTime.Ticks and I'm very happy with it.
The only thing I would like to have is something similar to the existing
SELECT datetime(1092941466, 'unixepoch');

I would need something like:
SELECT TicksToDateTime(AppointmentStart) where AppointmentStart is an 
ipothetical Table Column defined as integer in SQLITE (System.Int64 in 
.Net).
This way I could build a view to look my appointment table in a human 
readable format.
Is there such a function or is there a way to build it?
Best Regards Roberto Dalmonte
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Which SQLite API return SQLITE_BUSY?

[Lloyd]

Thanks Jay for your detailed reply.
I would be more happy if the SQLite documentaion include the error codes 
each function can return.

Thanks a lot,
  Lloyd


- Original Message - 
From: "Jay A. Kreibich" 
To: "General Discussion of SQLite Database" 
Sent: Thursday, July 08, 2010 8:27 PM
Subject: Re: [sqlite] Which SQLite API return SQLITE_BUSY?


> On Thu, Jul 08, 2010 at 12:42:36PM +0530, Lloyd scratched on the wall:
>> Hi,
>>
>> Which are all the type of SQLite APIs can return SQLITE_BUSY?
>>
>> sqlite3_open_v2
>> sqlite3_prepare_v2
>
>  All styles of these calls, not just the _v2 versions.
>
>> sqlite3_step
>
>  That's the big one.
>
>> sqlite3_finalize
>
>  sqlite3_reset() can as well, but in the case of both _finalize() and
>  _reset() the call itself (e.g. the finalize operation or reset
>  operation) will always work.  The error code is returned as the "left
>  over" from sqlite3_step().  See the history between _prepare() and
>  _prepare_v2().
>
>> sqlite3_bind_blob
>> sqlite3_column_int
>
>  The sqlite3_column_xxx() and sqlite3_bind_xxx() calls should not.
>
>
>
>  Additionally, sqlite3_blob_open() can, however, as can some of the
>  sqlite3_backup_xxx() calls (they use _prepare/_step internally).
>  sqlite3_exec() and sqlite3_get_table() as well.
>
>
>  sqlite3_close() can also return SQLITE_BUSY, but it isn't a locking
>  issue-- it just means you forgot to finalize all the statements.
>  IIRC, there are one or two other functions that do something similar,
>  but they're usually pretty obvious.
>
>  I think that's the bulk of it, however.
>
>   -j
>
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
> but showing it to the wrong people has the tendency to make them
> feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 



__
Scanned and protected by Email scanner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Noobie question about importing data into table

[Calimeron]

YES!!! THAT WORKS !!!

Thank you to all the repliers :)
-- 
View this message in context: 
http://old.nabble.com/Noobie-question-about-importing-data-into-table-tp29126116p29148819.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Noobie question about importing data into table

[cmartin]

On Tue, 13 Jul 2010, Calimeron wrote:

>
> Can somebody point me in the right direction?
>
> I have joined the two tables by doing: select * from ChineseTable,
> EnglishTable on IDX_c = IDX_cedict
>
> Now I would like to save this as a new table, so I can manipulate this table
> in sqlite and other programs.

You can create a table from a SELECT statement, i.e., CREATE TABLE mytable 
AS SELECT ...

See http://www.sqlite.org/lang_createtable.html

Chris
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Noobie question about importing data into table

[Calimeron]

Thanks :)

I tried >>>INSERT INTO newtable select * from cedict, english on IDX_c =
IDX_cedict<<<

("newtable" doesn't exist yet)

But that, predictably, didn't work. 

First I should create a table with the right characteristics? (10+4 columns,
columns with the right names and characteristics)

I tried that using the structure of the two separate tables, but not
successfully yet. I'm a noobie.


CREATE TABLE cedict (IDX_c INTEGER PRIMARY KEY AUTOINCREMENT,GB TEXT NOT
NULL,GBS TEXT NOT NULL default '',gbslength INTEGER unsigned NOT NULL
default '0',PinyinNr TEXT COLLATE NOCASE NOT NULL default '',PinyinAcc TEXT
COLLATE NOCASE NOT NULL default '',PinyinRed TEXT COLLATE NOCASE NOT NULL
default '',Frequency INTEGER default '0',Frequency2 INTEGER default
'0',Frequency3 INTEGER default '0')


CREATE TABLE english (IDX_e INTEGER PRIMARY KEY AUTOINCREMENT,englword TEXT
COLLATE NOCASE NOT NULL default '',inserted INTEGER default '0',IDX_cedict
INTEGER default '0')

-- 
View this message in context: 
http://old.nabble.com/Noobie-question-about-importing-data-into-table-tp29126116p29148702.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Noobie question about importing data into table

[Simon Davies]

On 13 July 2010 10:22, Calimeron  wrote:
>
> Can somebody point me in the right direction?
>
> I have joined the two tables by doing: select * from ChineseTable,
> EnglishTable on IDX_c = IDX_cedict
>
> Now I would like to save this as a new table, so I can manipulate this table
> in sqlite and other programs.
>
> Thank you for any replies :)
> --

Have a look at http://www.sqlite.org/lang_insert.html 2nd para

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Noobie question about importing data into table

[Calimeron]

Can somebody point me in the right direction?

I have joined the two tables by doing: select * from ChineseTable,
EnglishTable on IDX_c = IDX_cedict

Now I would like to save this as a new table, so I can manipulate this table
in sqlite and other programs.

Thank you for any replies :)
-- 
View this message in context: 
http://old.nabble.com/Noobie-question-about-importing-data-into-table-tp29126116p29148341.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] order of select result

[Swithun Crowe]

Hello

L> Thanks Swithun. I dont have any supportive column in table to issue 
L> "order by". Can it be achieved by issuing an "ordr by" query on 
L> "row_id" (used by sqlite internaly)?

You could, I think. But it might be better style to create an INTEGER 
PRIMARY KEY AUTOINCREMENT column to do this explicitly. One advantage of 
this is that if your table gets so large that all the 64 bit integers have 
been used up, then you won't be able to insert any more rows. Without the 
AUTOINCREMENT, the database will start to reuse numbers from rows that got 
deleted.

That at least, is what I understand from reading 
http://www.sqlite.org/autoinc.html and 
http://www.sqlite.org/lang_createtable.html

Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] open db cx to fork(2)d children

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/12/2010 08:53 PM, Nicolas Williams wrote:
> On Mon, Jul 12, 2010 at 08:43:32PM -0700, Roger Binns wrote:
>> Earlier you were trying to optimise out calls to getpid() and now you want
>> every SQLite function source to be changed?
> 
> No, just entry points into the library.  What makes you think that I
> meant that every function in the library should check this?

I also meant entry points but wasn't specific.  There are a lot of them that
acquire/release mutexes and every single one would need to be modified. That
was my original approach until I hit on the idea of swizzling the mutexes
instead.

> but when it comes to
> POSIX file byte range locking (at least for Solaris anyways, which is
> what I know best, implementation-wise) you can assume that the only
> broken thing about them is their semantics, not their implementations.

Having spent a lot of my commercial programming life with many different
Unix implementations, including Solaris, I do not trust them, especially for
little used functionality.  And Solaris had many bugs in the past, but I
haven't touched it in the last few years to form a recent opinion.  (Like
this one time when I found Sun's NFS implementation was broken based on log
messages on an HPUX machine and then doing packet capture, and Sun invented
NFS ...)

In any event this roughly comes down to me saying to assume they are
untrustworthy until it can be substantially be proven otherwise, and you
seeming to believe that they are all correct seemingly based on
extrapolating from a recent version of Solaris.  Or being a pessimist and
optimist respectively :-)

This doesn't really matter.  It is easy for anyone to add this functionality
to SQLite by swizzling mutex pointers, does not require any changes or
recompilation of SQLite, and can have whatever error behaviour desired, plus
whatever micro-optimisations will make them happy.  A straight forward
implementation with an application that spends all its time in SQLite and
with synchronous turned off (ie no disk waiting) will have a 1% performance
penalty.  Consider that a worst case.  If enough people find this useful
then there is a case for modifying SQLite itself.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkw8Br4ACgkQmOOfHg372QR9dQCeM0D2j/KxC85yvVagFpdvxVro
V3QAn3eu1HQTf0/ItPiDNe/AJmytVIy1
=Cbzq
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users