Re: [sqlite] Reduce database file size

[Roger Binns]

On 04/10/2010 03:06 PM, Nikolaus Rath wrote:
> However, I noticed that if I dump the entire database into a text file
> with the SQLite shell and then compress the text file, the result is
> significantly smaller than the "stripped" compressed database:

Have you tried different page sizes?

You could also do the transmission using rsync with compression which may 
turn out to transfer even less.

It may also be worthwhile looking to other compression mechanisms.  For 
example smaz works well on English text because the compression dictionary 
is prebuilt rather than generated for the specific data.  A prebuilt 
dictionary type mechanism may work well for you.  You may also find a PPM 
compressor even better.

> Alternatively, is there an easy way to dump and recover the DB using the
> standard API rather than the SQLite shell? (Obviously I could write a
> dump program myself, but I'd be nice if there is a solution that
> requires less work).

You are using Python and APSW IIRC.  APSW includes a Python based shell that 
has dumping and restore code and can be used programmatically or interactively.

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

Re: [sqlite] Reduce database file size

[Simon Slavin]

On 10 Apr 2010, at 11:06pm, Nikolaus Rath wrote:

> However, I noticed that if I dump the entire database into a text file
> with the SQLite shell and then compress the text file, the result is
> significantly smaller than the "stripped" compressed database:
> 
> Full database: 146 MB
> Without Custom Indices: 117 MB
> Compressed: 13 MB
> 
> Dumped DB: 181 MB
> Compressed: 6.8 MB

I assume you're using lzma -9 for both of these.  You might try other 
compressors besides lzma but you're already getting very good compression.

> Is there a way to strip even more redundancies from the DB for the
> transfer (e.g. the automatically created indices for primary keys)?

I think those are inherent in how SQLite works.  You can't strip them from its 
format.

> Alternatively, is there an easy way to dump and recover the DB using the
> standard API rather than the SQLite shell? (Obviously I could write a
> dump program myself, but I'd be nice if there is a solution that
> requires less work).

I think you've already worked out the best way to do it using sqlite and SQL 
formats.  And of course if you're writing your own dump program you're probably 
better off using TSV format than raw SQL commands.  Unless you have BLOBs in 
your file.

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

[sqlite] Reduce database file size

[Nikolaus Rath]

Hello,

I would like to make an SQLite database as small as possible to transfer
it over a slow link. Currently I am dropping all custom indices, run
VACUUM and compress the file with lzma -9.

However, I noticed that if I dump the entire database into a text file
with the SQLite shell and then compress the text file, the result is
significantly smaller than the "stripped" compressed database:

Full database: 146 MB
Without Custom Indices: 117 MB
Compressed: 13 MB

Dumped DB: 181 MB
Compressed: 6.8 MB


Is there a way to strip even more redundancies from the DB for the
transfer (e.g. the automatically created indices for primary keys)?

Alternatively, is there an easy way to dump and recover the DB using the
standard API rather than the SQLite shell? (Obviously I could write a
dump program myself, but I'd be nice if there is a solution that
requires less work).


Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

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

Re: [sqlite] 'DatabaseError: database disk image is malformed'

[Simon Slavin]

On 10 Apr 2010, at 3:32pm, D. Richard Hipp wrote:

> none of that should be in the first page of the database file.  Nor  
> does that text appear to be in a format that would appear anywhere in  
> a valid SQLite database.  So I'm guessing that some other process has  
> decided to open the SQLite database and overwrite it with log file  
> information.

What that text is, is a copy of an HTTP conversation between a web browser and 
a web server.  Looking at the conversation, it's a copy of what happens when a 
client talks to the trac server itself.  It's not some completely irrelevant 
thing, but probably something happening when someone tried to download those 
very files.

This suggests to me that some sort of corruption has occurred on the server 
itself, either low-level hard disk corruption or the trac server has bad code 
in and is overwriting the files its meant to be serving.  Either way, if I was 
the admin of that server, I would be switching to panic mode and doing a 
widespread investigation into how many of my files were corrupt.

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

Re: [sqlite] 'DatabaseError: database disk image is malformed'

[exarkun]

On 02:21 pm, rut...@cs.vu.nl wrote:
>[I first posted this on the Trac user list. There, I was referred to 
>the
>sqlite mailing lists.]
>
>I am running 3 trac instances on a FreeBSD server; trac 0.11b, sqlite3
>3.4.1, pysqlite-2.3.5. More or less simultaneously (at least within a
>few days) all three trac databases got corrupted. You can check for
>yourself at e.g. http://trac.rfidguardian.org:8000/trac-0-11b, it shows
>a python stack trace with at bottom a database disk image is malformed.
>
>When I have a look at one of the sqlite3 databases, the header block is
>obviously corrupted:
>
>000  53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 SQLite format 3.
>010  04 00 01 01 00 40 20 20 00 01 D5 6D 00 00 00 00 .@  ...m
>020  00 00 24 AA 00 00 04 9A 38 32 2E 39 35 2E 31 35 ..$.82.95.15
>030  37 2E 32 31 20 2D 20 2D 20 5B 30 36 2F 41 70 72 7.21 - - [06/Apr
>040  2F 32 30 31 30 20 31 34 3A 33 30 3A 31 30 5D 20 /2010 14:30:10]
>050  22 47 45 54 20 2F 74 72 61 63 2D 30 2D 31 31 62 "GET /trac-0-11b
>060  20 48 54 54 50 2F 31 2E 31 22 20 35 30 30 20 2D  HTTP/1.1" 500 -
>070  0A 74 72 75 6E 6B 2F 73 72 63 2F 75 69 2F 73 74 .trunk/src/ui/st
>080  64 69 6E 20 48 54 54 50 2F 31 2E 31 22 20 35 30 din HTTP/1.1" 50
>090  30 20 2D 0A 39 39 34 20 48 54 54 50 2F 31 2E 31 0 -.994 HTTP/1.1
>0A0  22 20 35 30 30 20 2D 0A 2E 30 22 20 35 30 30 20 " 500 -..0" 500
>0B0  2D 0A 20 35 30 30 20 2D 0A 54 50 2F 31 2E 30 22 -. 500 -.TP/1.0"
>0C0  20 35 30 30 20 2D 0A 22 20 35 30 30 20 2D 0A 50  500 -." 500 -.P
>0D0  2F 31 2E 31 22 20 35 30 30 20 2D 0A 31 2E 30 22 /1.1" 500 -.1.0"
>0E0  20 35 30 30 20 2D 0A 30 30 39 2D 30 37 2D 32 34  500 -.009-07-24
>0F0  54 32 30 25 33 41 35 30 25 33 41 35 35 5A 25 32 T20%3A50%3A55Z%2
>
>E.g. bytes 0x01c..0x01f should give the database size in 1K pages; that
>should be 1408 / 1024 = 13750 = 0x35b6. It is 0x00. And
>other fields are as obviously broken.
>
>What can be the cause of this simultaneous corruption? Is there a way 
>to
>recover the database?

I've seen trac write random garbage (request logs, debug logs, etc) to 
random files (mostly password files, never trac.db itself though).

Perhaps that's what happened here, trac got confused, though the 
database file was a log file (either by name or perhaps by file 
descriptor) and dumped some access log bytes into it.

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

Re: [sqlite] 'DatabaseError: database disk image is malformed'

[D. Richard Hipp]

On Apr 10, 2010, at 10:21 AM, Rutger Hofman wrote:

> [I first posted this on the Trac user list. There, I was referred to  
> the
> sqlite mailing lists.]
>
> I am running 3 trac instances on a FreeBSD server; trac 0.11b, sqlite3
> 3.4.1, pysqlite-2.3.5. More or less simultaneously (at least within a
> few days) all three trac databases got corrupted. You can check for
> yourself at e.g. http://trac.rfidguardian.org:8000/trac-0-11b, it  
> shows
> a python stack trace with at bottom a database disk image is  
> malformed.
>
> When I have a look at one of the sqlite3 databases, the header block  
> is
> obviously corrupted:
>
> 000  53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 SQLite format 3.
> 010  04 00 01 01 00 40 20 20 00 01 D5 6D 00 00 00 00 .@  ...m
> 020  00 00 24 AA 00 00 04 9A 38 32 2E 39 35 2E 31 35 ..$.82.95.15
> 030  37 2E 32 31 20 2D 20 2D 20 5B 30 36 2F 41 70 72 7.21 - - [06/Apr
> 040  2F 32 30 31 30 20 31 34 3A 33 30 3A 31 30 5D 20 /2010 14:30:10]
> 050  22 47 45 54 20 2F 74 72 61 63 2D 30 2D 31 31 62 "GET /trac-0-11b
> 060  20 48 54 54 50 2F 31 2E 31 22 20 35 30 30 20 2D  HTTP/1.1" 500 -
> 070  0A 74 72 75 6E 6B 2F 73 72 63 2F 75 69 2F 73 74 .trunk/src/ui/st
> 080  64 69 6E 20 48 54 54 50 2F 31 2E 31 22 20 35 30 din HTTP/1.1" 50
> 090  30 20 2D 0A 39 39 34 20 48 54 54 50 2F 31 2E 31 0 -.994 HTTP/1.1
> 0A0  22 20 35 30 30 20 2D 0A 2E 30 22 20 35 30 30 20 " 500 -..0" 500
> 0B0  2D 0A 20 35 30 30 20 2D 0A 54 50 2F 31 2E 30 22 -. 500 -.TP/1.0"
> 0C0  20 35 30 30 20 2D 0A 22 20 35 30 30 20 2D 0A 50  500 -." 500 -.P
> 0D0  2F 31 2E 31 22 20 35 30 30 20 2D 0A 31 2E 30 22 /1.1" 500 -.1.0"
> 0E0  20 35 30 30 20 2D 0A 30 30 39 2D 30 37 2D 32 34  500 -.009-07-24
> 0F0  54 32 30 25 33 41 35 30 25 33 41 35 35 5A 25 32 T20%3A50%3A55Z%2

All of that text in the header that looks like Apache logfile entires  
- none of that should be in the first page of the database file.  Nor  
does that text appear to be in a format that would appear anywhere in  
a valid SQLite database.  So I'm guessing that some other process has  
decided to open the SQLite database and overwrite it with log file  
information.

What does Trac call its database?  something.db?  Lots of programs use  
the ".db" suffix.  Perhaps one of these other programs mistook the  
SQLite database for a file in a different format and tried to  
overwrite it with new information that is in some other (non-SQLite)  
format.  Just a guess.

You are unlikely to be able to recover any useful content from a  
database that has been so thoroughly trashed.



>
> E.g. bytes 0x01c..0x01f should give the database size in 1K pages;  
> that
> should be 1408 / 1024 = 13750 = 0x35b6. It is 0x00. And
> other fields are as obviously broken.
>
> What can be the cause of this simultaneous corruption? Is there a  
> way to
> recover the database?
>
> Alas, we had a misunderstanding w/ our web host on backup policy; he
> turns out to keep multiple versions of the current files, but no  
> rollback...
>
> Thanks,
>
> Rutger Hofman
> VU Amsterdam
> http://www.rfidguardian.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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



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

[sqlite] 'DatabaseError: database disk image is malformed'

[Rutger Hofman]

[I first posted this on the Trac user list. There, I was referred to the 
sqlite mailing lists.]

I am running 3 trac instances on a FreeBSD server; trac 0.11b, sqlite3 
3.4.1, pysqlite-2.3.5. More or less simultaneously (at least within a 
few days) all three trac databases got corrupted. You can check for 
yourself at e.g. http://trac.rfidguardian.org:8000/trac-0-11b, it shows 
a python stack trace with at bottom a database disk image is malformed.

When I have a look at one of the sqlite3 databases, the header block is 
obviously corrupted:

000  53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 SQLite format 3.
010  04 00 01 01 00 40 20 20 00 01 D5 6D 00 00 00 00 .@  ...m
020  00 00 24 AA 00 00 04 9A 38 32 2E 39 35 2E 31 35 ..$.82.95.15
030  37 2E 32 31 20 2D 20 2D 20 5B 30 36 2F 41 70 72 7.21 - - [06/Apr
040  2F 32 30 31 30 20 31 34 3A 33 30 3A 31 30 5D 20 /2010 14:30:10]
050  22 47 45 54 20 2F 74 72 61 63 2D 30 2D 31 31 62 "GET /trac-0-11b
060  20 48 54 54 50 2F 31 2E 31 22 20 35 30 30 20 2D  HTTP/1.1" 500 -
070  0A 74 72 75 6E 6B 2F 73 72 63 2F 75 69 2F 73 74 .trunk/src/ui/st
080  64 69 6E 20 48 54 54 50 2F 31 2E 31 22 20 35 30 din HTTP/1.1" 50
090  30 20 2D 0A 39 39 34 20 48 54 54 50 2F 31 2E 31 0 -.994 HTTP/1.1
0A0  22 20 35 30 30 20 2D 0A 2E 30 22 20 35 30 30 20 " 500 -..0" 500
0B0  2D 0A 20 35 30 30 20 2D 0A 54 50 2F 31 2E 30 22 -. 500 -.TP/1.0"
0C0  20 35 30 30 20 2D 0A 22 20 35 30 30 20 2D 0A 50  500 -." 500 -.P
0D0  2F 31 2E 31 22 20 35 30 30 20 2D 0A 31 2E 30 22 /1.1" 500 -.1.0"
0E0  20 35 30 30 20 2D 0A 30 30 39 2D 30 37 2D 32 34  500 -.009-07-24
0F0  54 32 30 25 33 41 35 30 25 33 41 35 35 5A 25 32 T20%3A50%3A55Z%2

E.g. bytes 0x01c..0x01f should give the database size in 1K pages; that 
should be 1408 / 1024 = 13750 = 0x35b6. It is 0x00. And 
other fields are as obviously broken.

What can be the cause of this simultaneous corruption? Is there a way to 
recover the database?

Alas, we had a misunderstanding w/ our web host on backup policy; he 
turns out to keep multiple versions of the current files, but no rollback...

Thanks,

Rutger Hofman
VU Amsterdam
http://www.rfidguardian.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUBSTR overload and ALTER TABLE

[Jean-Christophe Deschamps]

>We just experienced the hard way that overloading certain built-in SQL
>function can interfere with core SQL commands if the overloaded function
>behaves differently from the built-in function.
>
>Not surprising, after looking at the sources:
>
>* ALTER TABLE - alter.c uses SUBSTR and LIKE.
>* VACUUM  - vacuum.c uses SUBSTR, LIKE, and QUOTE.
>* Possible others?
>
>Would it be possible that the SQLite core always uses the built-in
>functions instead of the overloaded ones? Or should overloading be
>disabled for "core critical" SQL functions?
>
>If not, maybe those "critical" functions could be mentioned in the
>documentation to warn developers that strange things might happen if
>they change their behavior?
>
>IMHO, LIKE is especially critical since it is a likely candidate for
>overriding to add UNICODE LIKE to applications.

Your finding is scary.  At first glance my Unicode-twisted LIKE won't 
mess up things, but my LIKE works with unaccented versions of strings, 
so all bets are off w.r.t. usage in database integrity critical 
operations...

IMVHO, the core definitely should call an internal _...@_like_@_ aliased 
to the original core version.  The same for your other discovers, of 
course.


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

Re: [sqlite] problem porting sqlite amalgamation 3.6.23.1 to vxworks 6.5 pentium3 DKM

[ZhiHua Huang]

Hi,

-- Forwarded message --
From: ZhiHua Huang 
Date: 2009/8/26
Subject: Re: [sqlite] port sqlite to VxWorks
To: General Discussion of SQLite Database 


Hi,

use these definitions.

-DOS_VXWORKS_660=660\
-DOS_VXWORKS_670=670\
-DOS_VXWORKS=OS_VXWORKS_670\
-DSQLITE_HOMEGROWN_RECURSIVE_MUTEX\
-DSQLITE_ENABLE_LOCKING_STYLE=1\
-DSQLITE_OMIT_LOAD_EXTENSION

and below is my patch with sqlite 3.6.17.

*** sqlite3.c.origTue Aug 11 03:54:50 2009
--- sqlite3.cWed Aug 12 13:43:51 2009
***
*** 19,24 
--- 19,28 
  **
  ** This amalgamation was generated on 2009-08-10 19:53:32 UTC.
  */
+ #if defined(OS_VXWORKS)
+ #include 
+ #endif /* OS_VXWORKS */
+
  #define SQLITE_CORE 1
  #define SQLITE_AMALGAMATION 1
  #ifndef SQLITE_PRIVATE
***
*** 20983,20989 
--- 20987,20999 
  #include 
  #include 
  #include 
+
+ #if defined(OS_VXWORKS) && defined(_WRS_KERNEL)
+ #include 
+ #else
  #include 
+ #endif /* OS_VXWORKS */
+
  #include 

  #if SQLITE_ENABLE_LOCKING_STYLE
***
*** 23171,23177 
--- 23181,23189 
/* Otherwise see if some other process holds it. */
if( !reserved ){
  sem_t *pSem = pFile->pOpen->pSem;
+ #if !defined(OS_VXWORKS)
  struct stat statBuf;
+ #endif

  if( sem_trywait(pSem)==-1 ){
int tErrno = errno;
***
*** 23224,23230 
--- 23236,23244 
  */
  static int semLock(sqlite3_file *id, int locktype) {
unixFile *pFile = (unixFile*)id;
+ #if !defined(OS_VXWORKS)
int fd;
+ #endif
sem_t *pSem = pFile->pOpen->pSem;
int rc = SQLITE_OK;

***
*** 23293,23299 
--- 23307,23317 
  /*
   ** Close a file.
   */
+ #if (OS_VXWORKS < 600)
  static int semClose(sqlite3_file *id) {
+ #else
+ static int semClose_native(sqlite3_file *id) {
+ #endif
if( id ){
  unixFile *pFile = (unixFile*)id;
  semUnlock(id, NO_LOCK);
***
*** 23831,23837 
--- 23849,23861 
  }
  return -1;
}
+
+ #if defined(OS_VXWORKS) && defined(_WRS_KERNEL)
+   got = write(id->h, (char *)pBuf, cnt);
+ #else
got = write(id->h, pBuf, cnt);
+ #endif /* OS_VXWORKS */
+
  #endif
TIMER_END;
if( got<0 ){
***
*** 24310,24316 
--- 24334,24344 
  IOMETHODS(
semIoFinder,  /* Finder function name */
semIoMethods, /* sqlite3_io_methods object name */
+ #if (OS_VXWORKS < 600)
semClose, /* xClose method */
+ #else
+   semClose_native,  /* xClose method */
+ #endif
semLock,  /* xLock method */
semUnlock,/* xUnlock method */
semCheckReservedLock  /* xCheckReservedLock method */
***
*** 25087,25093 
** tests repeatable.
*/
memset(zBuf, 0, nBuf);
! #if !defined(SQLITE_TEST)
{
  int pid, fd;
  fd = open("/dev/urandom", O_RDONLY);
--- 25115,25121 
** tests repeatable.
*/
memset(zBuf, 0, nBuf);
! #if !defined(SQLITE_TEST) && !defined(OS_VXWORKS)
{
  int pid, fd;
  fd = open("/dev/urandom", O_RDONLY);

I'v tested it on VxWorks 6.6/6.7 with both DKM and RTP mode.

2009/8/24 mly_hlmgood 

Hi,
>  I am puzzled by porting sqlite to VxWorks. can you help me.
> ___
> 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] SUBSTR overload and ALTER TABLE

[Ralf Junker]

We just experienced the hard way that overloading certain built-in SQL 
function can interfere with core SQL commands if the overloaded function 
behaves differently from the built-in function.

Not surprising, after looking at the sources:

* ALTER TABLE - alter.c uses SUBSTR and LIKE.
* VACUUM  - vacuum.c uses SUBSTR, LIKE, and QUOTE.
* Possible others?

Would it be possible that the SQLite core always uses the built-in 
functions instead of the overloaded ones? Or should overloading be 
disabled for "core critical" SQL functions?

If not, maybe those "critical" functions could be mentioned in the
documentation to warn developers that strange things might happen if
they change their behavior?

IMHO, LIKE is especially critical since it is a likely candidate for 
overriding to add UNICODE LIKE to applications.

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