Re: [sqlite] How much a table takes (will this query always work)

[Jim Wilcoxson]

On Fri, Jul 16, 2010 at 9:13 AM, Jim Wilcoxson  wrote:

> On Fri, Jul 16, 2010 at 6:01 AM, Max Vlasov  wrote:
>
>> Hi,
>>
>> always wanted to have a possibility to calculate how much a table
>> occupies.
>> As long as I see from the archive, there's no out-of-the-box solution
>> (CMIIW)
>>
>>
> You mean, like this?
>
> pragma page_count
> pragma page_size
> size = page_count*page_size
>
>
Doh.  Nevermind.  I see you said table, not database. :)

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

Re: [sqlite] How much a table takes (will this query always work)

[Jim Wilcoxson]

On Fri, Jul 16, 2010 at 6:01 AM, Max Vlasov  wrote:

> Hi,
>
> always wanted to have a possibility to calculate how much a table occupies.
> As long as I see from the archive, there's no out-of-the-box solution
> (CMIIW)
>
>
You mean, like this?

pragma page_count
pragma page_size
size = page_count*page_size

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

[sqlite] Bug found in memdb.test script

[Andy Gibbs]

Hi,

There's a small bug in the memdb.test script, that produces the following 
error on v 3.6.23.1 when doing a make fulltest:

memdb-3.3... Ok
memdb-3.4... Ok
memdb-4.0... Ok
./testfixture: couldn't set loop variable: "t1"
while executing
"ifcapable memorydb {

# In the following sequence of tests, compute the MD5 sum of the content
# of a table, make lots of modifications to that table,..."
(file "../source/test/memdb.test" line 20)
invoked from within
"source $testfile"
("foreach" body line 6)
invoked from within
"foreach testfile [lsort -dictionary [glob $testdir/*.test]] {
set tail [file tail $testfile]
if {[lsearch -exact $EXCLUDE $tail]>=0} continue
..."
("for" body line 2)
invoked from within
"for {set Counter 0} {$Counter<$COUNT && $nErr==0} {incr Counter} {
  foreach testfile [lsort -dictionary [glob $testdir/*.test]] {
set tail [file ..."
(file "../source/test/all.test" line 77)
make: *** [fulltest] Error 1


The fix is simple enough:

--- test/memdb.test
+++ test/memdb.test
@@ -160,6 +160,7 @@
 #   t1 Content of "c" column of t1 assuming no error in $cmd
 #   t2 Content of "x" column of t2
 #
+unset -nocomplain t1
 foreach {i conf cmd t0 t1 t2} {
   1 {}   INSERT  1 {}  1
   2 {}   {INSERT OR IGNORE}  0 3   1


Regards
Andy 

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

Re: [sqlite] How much a table takes (will this query always work)

[Jay A. Kreibich]

On Fri, Jul 16, 2010 at 02:01:27PM +0400, Max Vlasov scratched on the wall:
> Hi,
> 
> always wanted to have a possibility to calculate how much a table occupies.
> As long as I see from the archive, there's no out-of-the-box solution
> (CMIIW)

  Check out sqlite3_analyzer.  This is available in binary format only
  on the SQLite website.   http://sqlite.org/download.html

  In addition to a large number of database file stats, it provides the
  following information for every table and index:

   name clob,-- Name of a table or index in the database file
   tblname clob, -- Name of associated table
   is_index boolean, -- TRUE if it is an index, false for a table
   nentry int,   -- Number of entries in the BTree
   leaf_entries int, -- Number of leaf entries
   payload int,  -- Total amount of data stored in this table or index
   ovfl_payload int, -- Total amount of data stored on overflow pages
   ovfl_cnt int, -- Number of entries that use overflow
   mx_payload int,   -- Maximum payload size
   int_pages int,-- Number of interior pages used
   leaf_pages int,   -- Number of leaf pages used
   ovfl_pages int,   -- Number of overflow pages used
   int_unused int,   -- Number of unused bytes on interior pages
   leaf_unused int,  -- Number of unused bytes on primary pages
   ovfl_unused int,  -- Number of unused bytes on overflow pages
   gap_cnt int   -- Number of gaps in the page layout



> Recently I made a query that probably solves this, but it has some
> limitations and there are still issues unsolved.

> - Are there cases when a wrong result possible with ideal conditions (no
> temp database before)?

  The reported size is likely smaller than the table, as it sits in the
  original database.  This technique defragements and re-packs the
  pages, not unlike the VACUUM command.  It should give you pretty good
  numbers for the VACUUMed size, but not for the current size.

   -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

Re: [sqlite] INSERT question

[Simon Slavin]

On 15 Jul 2010, at 2:34pm, Mark wrote:

> when using INSERT, if the record is already present, will this cause any 
> problems, does it overwrite or ignore?

You can chose your own preference for each command: you can make the command 
generate an error, or replace the existing record, or be silently ignored, or 
other options.  Take a look at

http://www.sqlite.org/lang_insert.html

INSERT OR REPLACE ...
INSERT OR FAIL ...

Note: the definition of 'record is already present' is that that it duplicates 
the UNIQUE keys of any existing record.  This includes, but is not only, the 
PRIMARY KEY.

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

Re: [sqlite] INSERT question

[Andreas Henningsson]

A table without unique contraints will get duplicates if you insert the same
row multiple times.

/Andreas

On Thu, Jul 15, 2010 at 3:34 PM, Mark  wrote:

> Hi there,
>
> when using INSERT, if the record is already present, will this cause any
> problems, does it overwrite or ignore?
>
> mtia
> Mark
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Andreas Henningsson

"Vanligt sunt förnuft är inte särkilt vanligt." -- Voltaire
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT question

[Mark]

Hi there,

when using INSERT, if the record is already present, will this cause any 
problems, does it overwrite or ignore?

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

[sqlite] How much a table takes (will this query always work)

[Max Vlasov]

Hi,

always wanted to have a possibility to calculate how much a table occupies.
As long as I see from the archive, there's no out-of-the-box solution
(CMIIW)

Recently I made a query that probably solves this, but it has some
limitations and there are still issues unsolved.

So, let's TableToCheck is table we want to know about.

-

CREATE TEMP TABLE [tblsizetemptable1] AS SELECT * FROM TableToCheck;
CREATE TEMP TABLE [tblsizetemptable2] ([Id] INTEGER PRIMARY KEY);
SELECT (SELECT rootpage FROM sqlite_temp_master  WHERE
tbl_name='tblsizetemptable2')-
(SELECT rootpage FROM sqlite_temp_master  WHERE
tbl_name='tblsizetemptable1');

The result can be used as a page count size or be multiplied with the result
of PRAGMA page_size;

After that we should drop the tables
DROP TABLE tblsizetemptable2;
DROP TABLE tblsizetemptable1;

I hope these queries are self-explanatory
-

So the questions and problems:
- Is it possible to create a similar query for indexes. I mean the one, in
what I only have to change the name of the index. As long as I understand
there's no syntax looking like CREATE INDEX ... AS
- If there are temp tables exist, the temp database file might be
fragmented, so this method can give bad results. Is there something like
VACUUM for the temp database or a similar actions leading to the compacted
temp database?
- Are there cases when a wrong result possible with ideal conditions (no
temp database before)?
- Such method is not good for large databases since all the data should be
re-saved just to get the value. If someone suggest an algorithm solving
this, this will be great.

Thanks,

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

Re: [sqlite] sqlite database handle caching and write permissions

[Dan Kennedy]

On Jul 16, 2010, at 2:05 PM, JT Olds wrote:

>> Unsafe. Using the authorizer callback instead to figure out if a
>> statement may write the database is a better way:
>>
>>   http://www.sqlite.org/c3ref/c_alter_table.html
>>   http://www.sqlite.org/c3ref/set_authorizer.html
>
> Beautiful Dan, thank you. That problem I think has been nailed.
>
> Any ideas on the shared cache issue? I went and reread the
> documentation, and it's still unclear to me as to whether or not the
> shared cache feature works across two database handles open to the
> same database in non-concurrent sequence.

Within a process using shared-cache mode, all internal
state related to a database is discarded when the number
of connections to that database drops to zero.


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

Re: [sqlite] sqlite database handle caching and write permissions

[JT Olds]

> Unsafe. Using the authorizer callback instead to figure out if a
> statement may write the database is a better way:
>
>   http://www.sqlite.org/c3ref/c_alter_table.html
>   http://www.sqlite.org/c3ref/set_authorizer.html

Beautiful Dan, thank you. That problem I think has been nailed.

Any ideas on the shared cache issue? I went and reread the
documentation, and it's still unclear to me as to whether or not the
shared cache feature works across two database handles open to the
same database in non-concurrent sequence.

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