Re: [sqlite] Encrypted database

[Kevin Benson]

On Sat, Dec 13, 2014 at 9:08 PM, Igor Tandetnik  wrote:
>
> On 12/13/2014 2:29 PM, Mujtaba Ali Panjwani wrote:
>
>> I have created an encrypted database using visual studio plugin of
>> system.data.sqlite. Now whichever other database management software than
>> VS, I try to open that database is failing.
>>
>
> Isn't that the whole point? You've encrypted the database precisely so
> that it can't be opened by other tools, only by your application, haven't
> you? If not, what was the goal of the exercise?
>
> If you want the database to be accessible via any SQLite database
> management tool, don't encrypt it.
> --
> Igor Tandetnik
>

-> OR ...seek tools that are compatible with the encryption used:

On Tue, Nov 25, 2014 at 1:58 PM, Joe Mistachkin 
wrote:
>
>
> Krenn Christoph wrote:
> >
> > However I would like to know which encryption algorithm is used by
> > System.Data.SQLite, v1.0.94.0?
> >
>
> The legacy encryption support in System.Data.SQLite uses the CryptoAPI
> with the constants PROV_RSA_FULL and MS_ENHANCED_PROV, as described
> here:
>
>
>
> http://msdn.microsoft.com/en-us/library/windows/desktop/aa386986%28v=vs.85%2
> 9.aspx
>
> The exact algorithm and key strength selected probably depends on the
> version of Windows being used.
>
> --
> Joe Mistachkin
>

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request - RowCount

[RSmith]

On 2014/12/13 14:38, Richard Hipp wrote:
The "SELECT count(*) FROM table" query already has a special optimization in the b-tree layer to make it go faster.  You can see 
this by comparing the times of these queries:


 SELECT count(*) FROM table;
 SELECT count(*) FROM table WHERE 1;

The WHERE clause on the second query disables the optimization and so the second query should run slower.  The second query visits 
and partially decodes every row in the b-tree.  The first visits every leaf page of the b-tree, but it does nothing more than read 
the "number-of-entries" from the header of the page, add that value to the accumulating count, and then move on.


Thank you very much for the clear explanation and taking the time Richard. The proposed methods above are fast indeed, but not 
instant and I guess you will never satisfy everyone until everything works instantaneously. I take the point that the only possible 
improvements seem to need alteration to the file structure or added maintenance which may use up cycles for something that just 
isn't that important to DB use in general - and I have to agree, I too have zero want for seeing more cycles used. I was hoping 
there might be a way, but am satisfied there isn't and would like to hope out loud with Simon that this might be seen in SQLite4.


Thanks,
Ryan

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

Re: [sqlite] Encrypted database

[Igor Tandetnik]

On 12/13/2014 2:29 PM, Mujtaba Ali Panjwani wrote:

I have created an encrypted database using visual studio plugin of
system.data.sqlite. Now whichever other database management software than
VS, I try to open that database is failing.


Isn't that the whole point? You've encrypted the database precisely so 
that it can't be opened by other tools, only by your application, 
haven't you? If not, what was the goal of the exercise?


If you want the database to be accessible via any SQLite database 
management tool, don't encrypt it.

--
Igor Tandetnik

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

[sqlite] Encrypted database

[Mujtaba Ali Panjwani]

I have created an encrypted database using visual studio plugin of
system.data.sqlite. Now whichever other database management software than
VS, I try to open that database is failing. Can any of you please help me
with issue. I suspect this is some sort of encryption algorithm support
problem but I don't know how to resolve the issue.

 

Thanks in advance for helping me.

 

Regards,

 

Mujtaba Panjwani

 

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

Re: [sqlite] Feature Request - RowCount

[David Empson]

> On 14/12/2014, at 4:17 am, Simon Slavin  wrote:
> 
> 
> On 13 Dec 2014, at 12:38pm, Richard Hipp  wrote:
> 
>> Also, if there are indices available, SQLite attempts to count the smallest
>> index (it has to guess at which is the smallest by looking at the number
>> and declared datatypes of the columns) and counting the smallest index
>> instead, under the theory that a smaller index will involve less I/O.
> 
> Would it not be faster to just count the number of pages each index takes up 
> ?  Uh ... no.
> Wow.  You really don't like storing counts or sizes, do you ?

That wouldn't work, because leaf nodes in a B-tree contain a variable number of 
used entries. It is necessary to visit each leaf node to find out how many 
entries that page contains.

The reason for using the "smallest" index is that each leaf node will hopefully 
cover more records than the leaf nodes from the main table, therefore less I/O 
is required.

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

Re: [sqlite] Feature Request - RowCount

[Simon Slavin]

On 13 Dec 2014, at 7:46pm, James K. Lowden  wrote:

> Every DB Admin tool I've ever used proved to be more hinderance than
> help.  They seem to be written by the moderately competent to help the
> novice, and run out of gas or fall over when faced with anything
> complex.  [snip]
> 
> My first question, then, is whether or not the rowcount is so
> interesting that it must be known before a table can be operated on.
> I suggest the answer is No.  The relative & approximate sizes of the
> tables is known to the admin in most cases and, when it is not, the
> information is readily discovered on a case-by-case basis. [snip]

All true.  Yet when I wrote my own DB Admin tool (suitable only for my own use, 
of no interest to anyone else) I included the same feature in it.  When you 
click on a TABLE to select it the count(*) pops up along with information about 
the table's structure.  I had no real idea why I put that in, it just seemed a 
natural thing to do.

> That said, I'm puzzled why rowcount isn't maintained and exposed in
> SQLite as part of a table's metadata, particularly when indexes/keys are
> present.  The cost of maintaining a rowcount is small, in terms of
> computation and complexity.  ISTM it is valuable information to the
> system itself in evaluating query-plan costs.

It does seem that knowing count(*) would be a very good thing to know for 
evaluating query-plan costs.  I hope SQLite4 stores it.

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

Re: [sqlite] Feature Request - RowCount

[James K. Lowden]

On Sat, 13 Dec 2014 14:15:15 +0200
RSmith  wrote:

> Most DB Admin tools out there displays the number of rows in a table
> when you select it or open it, so too the one I am working on and
> after testing stuff on Simon's question about the row counting, I
> realised that selecting a large table always pauses somewhat (with
> suitable progress bar) to simply open and report the usual
> statistics. The culprit of course being row-counting.  

Every DB Admin tool I've ever used proved to be more hinderance than
help.  They seem to be written by the moderately competent to help the
novice, and run out of gas or fall over when faced with anything
complex.  So the number of tools with feature X is no measure of the
value of X.  (Notable example: the tool should keep every query and
result in a time-sequenced transcript log, so that prior results can be
re-examined and prior queries modified.  Most tools disassociate query
from output and invite the user to modify the query in-place,
destroying the prior.)  

My first question, then, is whether or not the rowcount is so
interesting that it must be known before a table can be operated on.
I suggest the answer is No.  The relative & approximate sizes of the
tables is known to the admin in most cases and, when it is not, the
information is readily discovered on a case-by-case basis.  

Would a proxy figure do?  Is it enough to know the number of pages or
bytes allocated to a table?  I don't know if such is available, but if
it is perhaps that would serve your purpose.  Otherwise your user
might be satisfied by lazy evaluation: run your count(*) query on a
fork and provide the answer through a pipe or similar, in such a way
that the user can cancel it if it's placing too high a load on the
system.  

That said, I'm puzzled why rowcount isn't maintained and exposed in
SQLite as part of a table's metadata, particularly when indexes/keys are
present.  The cost of maintaining a rowcount is small, in terms of
computation and complexity.  ISTM it is valuable information to the
system itself in evaluating query-plan costs.  The "because it's lite"
argument doesn't hold water insofar as applications are paying a high
cost (even if somewhat optimized) for a common query.  

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

Re: [sqlite] Feature Request - RowCount

[Richard Hipp]

No.  The fastest is to do "count(*)".

--
D. Richard Hipp
Sent from phone - Excuse brevity
On Dec 13, 2014 11:13 AM, "Jim Callahan" 
wrote:

> So, if I understand the discussion the fastest way to get a count from the
> command line interface (CLI) is to count the rows in the primary key,
> assuming you have a primary key and that it is not a composite key.
>
> SELECT COUNT(primarykey) FROM table1
>
> The "primarykey" in the above example is a stand in  for the actual name of
> the field designated as the primary key.
>
> I am also relying on the answers to FAQ #1 and #26.
>
> #26 The unique columns have non-null values (the answer says a lot more,
> but that is the essence of what I am relying on).
>
> #1 If you have an integer primary key (which by definition in SQLITE3
> autoincrements) one might be able to get an approximate row count faster
> using the:
>
> sqlite3_last_insert_rowid()
>
> function.
>
> Jim Callahan
> Orlando, FL
>
> On Dec 13, 2014 10:17 AM, "Simon Slavin"  wrote:
>
> >
> > On 13 Dec 2014, at 12:38pm, Richard Hipp  wrote:
> >
> > > Also, if there are indices available, SQLite attempts to count the
> > smallest
> > > index (it has to guess at which is the smallest by looking at the
> number
> > > and declared datatypes of the columns) and counting the smallest index
> > > instead, under the theory that a smaller index will involve less I/O.
> >
> > Would it not be faster to just count the number of pages each index takes
> > up ?  Uh ... no.
> > Wow.  You really don't like storing counts or sizes, do you ?
> >
> > > To do better than this requires, as far as I know, an incompatible file
> > > format change and/or a performance hit for applications that do not use
> > the
> > > feature.
> >
> > Can you tell us whether the problem exists in SQLite4 ?  I know it uses a
> > different format for indexes.  I tried checking the documentation but
> > didn't see an answer that didn't involve more work than I felt like
> doing.
> >
> > Simon.
> > ___
> > 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] Feature Request - RowCount

[Jim Callahan]

So, if I understand the discussion the fastest way to get a count from the
command line interface (CLI) is to count the rows in the primary key,
assuming you have a primary key and that it is not a composite key.

SELECT COUNT(primarykey) FROM table1

The "primarykey" in the above example is a stand in  for the actual name of
the field designated as the primary key.

I am also relying on the answers to FAQ #1 and #26.

#26 The unique columns have non-null values (the answer says a lot more,
but that is the essence of what I am relying on).

#1 If you have an integer primary key (which by definition in SQLITE3
autoincrements) one might be able to get an approximate row count faster
using the:

sqlite3_last_insert_rowid()

function.

Jim Callahan
Orlando, FL

On Dec 13, 2014 10:17 AM, "Simon Slavin"  wrote:

>
> On 13 Dec 2014, at 12:38pm, Richard Hipp  wrote:
>
> > Also, if there are indices available, SQLite attempts to count the
> smallest
> > index (it has to guess at which is the smallest by looking at the number
> > and declared datatypes of the columns) and counting the smallest index
> > instead, under the theory that a smaller index will involve less I/O.
>
> Would it not be faster to just count the number of pages each index takes
> up ?  Uh ... no.
> Wow.  You really don't like storing counts or sizes, do you ?
>
> > To do better than this requires, as far as I know, an incompatible file
> > format change and/or a performance hit for applications that do not use
> the
> > feature.
>
> Can you tell us whether the problem exists in SQLite4 ?  I know it uses a
> different format for indexes.  I tried checking the documentation but
> didn't see an answer that didn't involve more work than I felt like doing.
>
> Simon.
> ___
> 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] Feature Request - RowCount

[Simon Slavin]

On 13 Dec 2014, at 12:38pm, Richard Hipp  wrote:

> Also, if there are indices available, SQLite attempts to count the smallest
> index (it has to guess at which is the smallest by looking at the number
> and declared datatypes of the columns) and counting the smallest index
> instead, under the theory that a smaller index will involve less I/O.

Would it not be faster to just count the number of pages each index takes up ?  
Uh ... no.
Wow.  You really don't like storing counts or sizes, do you ?

> To do better than this requires, as far as I know, an incompatible file
> format change and/or a performance hit for applications that do not use the
> feature.

Can you tell us whether the problem exists in SQLite4 ?  I know it uses a 
different format for indexes.  I tried checking the documentation but didn't 
see an answer that didn't involve more work than I felt like doing.

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

Re: [sqlite] Feature Request - RowCount

[Richard Hipp]

The "SELECT count(*) FROM table" query already has a special optimization
in the b-tree layer to make it go faster.  You can see this by comparing
the times of these queries:

 SELECT count(*) FROM table;
 SELECT count(*) FROM table WHERE 1;

The WHERE clause on the second query disables the optimization and so the
second query should run slower.  The second query visits and partially
decodes every row in the b-tree.  The first visits every leaf page of the
b-tree, but it does nothing more than read the "number-of-entries" from the
header of the page, add that value to the accumulating count, and then move
on.

Also, if there are indices available, SQLite attempts to count the smallest
index (it has to guess at which is the smallest by looking at the number
and declared datatypes of the columns) and counting the smallest index
instead, under the theory that a smaller index will involve less I/O.

To do better than this requires, as far as I know, an incompatible file
format change and/or a performance hit for applications that do not use the
feature.  I considered including in the SQLite file format the ability to
discover the rank of an entry in the b-tree in logarithmic time.  In other
words, give any row X, to find the number of rows less than X in the
table.  That ability would all the "SELECT count(*) FROM table" query to
run in logarithmic time simply by seeking to the last entry of the table
and finding its rank.  But, I rejected that idea because (1) it takes up
extra space in the database file meaning that queries have to read a little
more data and so run slightly slower and (2) because there is a significant
amount of extra writing on an update because every parent node in the
b-tree must be updated whenever any of its children is updated.  You can
argue that I made a bad decision in omitting this capability, but others
might argument to the contrary, and in any event, that decision was made 10
years ago and cannot be changed without a file format break.

On Sat, Dec 13, 2014 at 7:15 AM, RSmith  wrote:
>
> To the SQLite devs:
>
> After recent discussion about the row-count issue w.r.t. Nulls in primary
> keys etc. I have been somewhat wrestling with how to improve this from a
> user perspective.
>
> To explain: Most DB Admin tools out there displays the number of rows in a
> table when you select it or open it, so too the one I am working on and
> after testing stuff on Simon's question about the row counting, I realised
> that selecting a large table always pauses somewhat (with suitable progress
> bar) to simply open and report the usual statistics. The culprit of course
> being row-counting.  The problem escalates with bigger tables and most
> users detest sluggishness and all of us try to make things less so.
>
> I thought of keeping the count cached, which works while the connection is
> open, but becomes useless if re-opened (another app may have changed that
> in the meantime - actually this may even have happened while the connection
> is open). I've also tried cheating by inspecting the file size and upon big
> enough files, defer row-counting with some form of [This is a large DB -
> Click here to check the row count, this may take some time.] user message
> where the row-count is supposed to appear - but as you must be aware I have
> run across DBs several GBs in size with only a few hundred-K rows in the
> large tables, and one DB I have weighs in at only 250MB but have about
> 11mil rows in the main table. Not to mention the fact that one table might
> have all the rows and the others may all be small.
>
> To address the table-walk for Indices containing NULLs: Most DB admins and
> system engineers are savvy to this problem, I am sure in over 90% of the
> cases they do not keep NULLs in primary keys even if SQLite allows this. (I
> think the figure is over 99% but I am weary of exaggeration) - but even if
> they do have NULLs, sometimes you just need to know the amount of rows, not
> the amount of non-NULL value rows. I realise this cannot fit in an
> algebraically verifyable SQL result such as count() because of those few
> cases, but a possible pragma can fix it for the rest of us.
>
> I realise this problem is rather specific to the DB admin programs as
> opposed to user systems, but a Pragma "rowcount(TableName);" would be
> spectacular where the count is simply a fast reference to the total rows
> regardless of content with documentation pointing out the difference.
>
> I am very willing to submit a documentation draft if this feature gets
> added (to save someone some work) but I am not versed well enough in the
> SQLite internals to attempt a patch. Also, the solution needn't fall upon
> my suggestion, any other suitable means of making row count
> fast-determinable would be welcome.
>
>
> Thank you kindly,
> Ryan
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> 

[sqlite] Feature Request - RowCount

[RSmith]

To the SQLite devs:

After recent discussion about the row-count issue w.r.t. Nulls in primary keys etc. I have been somewhat wrestling with how to 
improve this from a user perspective.


To explain: Most DB Admin tools out there displays the number of rows in a table when you select it or open it, so too the one I am 
working on and after testing stuff on Simon's question about the row counting, I realised that selecting a large table always pauses 
somewhat (with suitable progress bar) to simply open and report the usual statistics. The culprit of course being row-counting.  The 
problem escalates with bigger tables and most users detest sluggishness and all of us try to make things less so.


I thought of keeping the count cached, which works while the connection is open, but becomes useless if re-opened (another app may 
have changed that in the meantime - actually this may even have happened while the connection is open). I've also tried cheating by 
inspecting the file size and upon big enough files, defer row-counting with some form of [This is a large DB - Click here to check 
the row count, this may take some time.] user message where the row-count is supposed to appear - but as you must be aware I have 
run across DBs several GBs in size with only a few hundred-K rows in the large tables, and one DB I have weighs in at only 250MB but 
have about 11mil rows in the main table. Not to mention the fact that one table might have all the rows and the others may all be small.


To address the table-walk for Indices containing NULLs: Most DB admins and system engineers are savvy to this problem, I am sure in 
over 90% of the cases they do not keep NULLs in primary keys even if SQLite allows this. (I think the figure is over 99% but I am 
weary of exaggeration) - but even if they do have NULLs, sometimes you just need to know the amount of rows, not the amount of 
non-NULL value rows. I realise this cannot fit in an algebraically verifyable SQL result such as count() because of those few cases, 
but a possible pragma can fix it for the rest of us.


I realise this problem is rather specific to the DB admin programs as opposed to user systems, but a Pragma "rowcount(TableName);" 
would be spectacular where the count is simply a fast reference to the total rows regardless of content with documentation pointing 
out the difference.


I am very willing to submit a documentation draft if this feature gets added (to save someone some work) but I am not versed well 
enough in the SQLite internals to attempt a patch. Also, the solution needn't fall upon my suggestion, any other suitable means of 
making row count fast-determinable would be welcome.



Thank you kindly,
Ryan


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