Re: [sqlite] BUG - Documentation

[Simon Slavin]

On 15 Jan 2010, at 12:29am, Dennis Cote wrote:

> "Disability the mutexes as compile-time is a recommended optimization 
> for applications were it makes sense."
> 
> I think it should be changed to:
> 
> "Disabling the mutexes at compile-time is a recommended optimization for 
> applications were it makes sense."

s/were/where

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

[sqlite] BUG - Documentation

[Dennis Cote]

On the website page at http://www.sqlite.org/custombuild.html

The following sentence appears:

"This object is somewhat misnamed since it is really an interface to 
whole underlying operating system, just the filesystem."

I think it should be changed to:

"This object is somewhat misnamed since it is really an interface to 
whole underlying operating system, *NOT* just the filesystem."

Or something similar. Maybe the NOT doesn't need that much emphasis, but 
it should be there.

HTH
Dennis Cote

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

[sqlite] BUG - Documentation

[Dennis Cote]

On the website page at http://www.sqlite.org/custombuild.html

The following sentence appears:

"Disability the mutexes as compile-time is a recommended optimization 
for applications were it makes sense."

I think it should be changed to:

"Disabling the mutexes at compile-time is a recommended optimization for 
applications were it makes sense."

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

Re: [sqlite] sqlite-users Digest, Vol 25, Issue 14

[Dave Toll]

Hi Jamie

Someone posted a problem recently related to SQLITE_CANTOPEN - I believe
the issue was that too many file descriptors were opened. Perhaps you
are using a file descriptor for every path you insert, and at some point
the OS returns an error when SQLite tries to get a file descriptor for
the journal file?

Cheers,
Dave.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of
sqlite-users-requ...@sqlite.org
Sent: Thursday, January 14, 2010 4:00 AM
To: sqlite-users@sqlite.org
Subject: sqlite-users Digest, Vol 25, Issue 14

Send sqlite-users mailing list submissions to
sqlite-users@sqlite.org

To subscribe or unsubscribe via the World Wide Web, visit
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
or, via email, send a message with subject or body 'help' to
sqlite-users-requ...@sqlite.org

You can reach the person managing the list at
sqlite-users-ow...@sqlite.org

When replying, please edit your Subject line so it is more specific than
"Re: Contents of sqlite-users digest..."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] way to get a list with column names

[Oliver Peters]

Robert Citek  writes:

> 
> Nothing with just SQL alone, although you can get close:
> 
> http://www.sqlite.org/faq.html#q7
> 
> You could use a command pipeline, but that only works if the table has
> at least one record:
> 
> $ sqlite3 -separator ", " -header sample.db 'select * from
> sqlite_master limit 1; ' |
> head -1
> type, name, tbl_name, rootpage, sql
> 
> Or you can do it from within a scripting language, e.g. ruby:
> 
> $ ruby -e '
> require "sqlite3" ;
> db=SQLite3::Database.new("sample.db") ;
> row=db.execute2("select * from sqlite_master limit 0 ") ;
> puts row.join(", ") ;
> '
> type, name, tbl_name, rootpage, sql
> 
> Good luck and let us know what works for you.
> 

Hello,

thank you for your hints. The shell solution helps me a lot - it is simple and
efficient. Thank god there is http://gnuwin32.sourceforge.net/ that lets me use
native gnu under WinXP.

greetings
Oliver

[...]



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

Re: [sqlite] size control of sqlite database

[Martin Engelschalk]

Hi Roger,

yes, thank you, i did not see this.

Martin

Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Martin.Engelschalk wrote:
>   
>> However, i could not find a way to determine when the empty pages are
>> used up and the file will start to grow again without checking the file
>> size after every insert.
>> 
>
> Doesn't PRAGMA freelist_count help with that?  There is also PRAGMA
> page_count and another to determine page size so you can do all these
> calculations without going outside of SQLite.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAktPZtwACgkQmOOfHg372QRHEgCeKwRMG+DpI1Kq/2jeo/Iw/DO0
> DtoAoIYIqt40E9TIWlebbYPta33S6o9r
> =pY6s
> -END PGP SIGNATURE-
> ___
> 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] size control of sqlite database

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Martin.Engelschalk wrote:
> However, i could not find a way to determine when the empty pages are
> used up and the file will start to grow again without checking the file
> size after every insert.

Doesn't PRAGMA freelist_count help with that?  There is also PRAGMA
page_count and another to determine page size so you can do all these
calculations without going outside of SQLite.

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

iEYEARECAAYFAktPZtwACgkQmOOfHg372QRHEgCeKwRMG+DpI1Kq/2jeo/Iw/DO0
DtoAoIYIqt40E9TIWlebbYPta33S6o9r
=pY6s
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_CANTOPEN Bohrbug [RESOLVED]

[Jamie Hardt]

On Jan 14, 2010, at 4:45 AM, D. Richard Hipp wrote:

> Another possibility:  You are using up all of your file descriptors.   
> Are you sure there is no file descriptor leak in your path enumeration  
> code?

That appears to be exactly what was going on; my code for opening files for 
reading and returning without closing them.  I fixed this and all is well now.

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

[sqlite] SQLITE3_CANTOPEN

[Dave Dyer]

There is a known problem, at least with NTFS, that deleting the
journal file fails unexpectedly, resulting in sqlite3_cantopen.
I believe I'm the one who originally reported this problem,
but the installed solution isn't quite what I recommended.

I'm not sure what OS/File System you are using, but something
analogous may be going on for you.

In "winDelete", the delete code in the released version is

 if( isNT() ){
do{
  DeleteFileW(zConverted);
}while(   (   ((rc = GetFileAttributesW(zConverted)) != 
INVALID_FILE_ATTRIBUTES)
   || ((error = GetLastError()) == ERROR_ACCESS_DENIED))
   && (++cnt < MX_DELETION_ATTEMPTS)
   && (Sleep(100), 1) );



The code I"m using is

 if( isNT() ){
do{
  rc = DeleteFileW(zConverted);
  if(rc==0) 
  { long attr = GetFileAttributesW(zConverted);
if(attr==0x) { rc=1; }  // ok as long as 
sombody deleted it
  }
}while( rc==0
&& (cnt++ < MX_DELETION_ATTEMPTS)
&& (Sleep(100), 1) );

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

Re: [sqlite] How to find Rank in SQLite3?

[Pavel Ivanov]

> Is there is any other possible way to find out rank?

As I said you can do it in your application, it will be a whole lot
faster than doing it with sql. Just select all your data with 'order
by deptno, sal desc'. Then during iteration over result set assign
rank 1 to the person when you first see his deptno, each next row is
next rank (think how you want to deal with repeated salary). And
that's it.


Pavel

On Thu, Jan 14, 2010 at 7:07 AM, Jigar Shah  wrote:
> First of all thanks Tim and Pavel for replying to my query.
>
>
>>
>> Assuming you're doing this by department, try to get just a list of the
>> distinct salary rankings into an inline view, using your count(*) +1
>> approach to set the salary rank,  where these three columns are unique
>> in combination:
>>
>> (dept, salary, rank ) as S
>>
>> select EE.dept, EE.name, EE.salary, S.rank
>> from employees as EE
>> JOIN
>> (inline view to create distinct salary bands by department goes here) as
> S
>> on EE.dept = S.dept
>> and EE.salary = S.salary
>> order by dept, rank
>
>
> My aim is to find rank of each employee within his dept. So I guess the
> inline view (adding empname and empno columns) is the final output that
> I require.
>
> So to join this output with original table would be an extra activity.
>
> Also, count(*) +1 approach for ranking is time consuming as it iterates
> over
> entire table for each row, so using the same in inline view may not help
> to
> increase speed.
>
>
>>    > I have used following query but it takes more than one hour even
> after
>>    > indexing,
>>
>> Shouldn't be - query is not so hard if a proper index used. What index
>> did you create?
>
> I have used following index.
>
> create index tempidx on EMPLOYEES(DEPTNO, SAL);
>
> I have tried query, using count(*) +1 approach, in mysql but it was slower
> (>two hours, still query was running) than Sqlite.
>
>
> Is there is any other possible way to find out rank?
>
>
>
> Thanks,
>
> Jigar Shah
>
>
>
> Disclaimer note on content of this message including enclosure(s)and
>  attachments(s): The contents of this e-mail are the privileged and
>  confidential material of National Stock Exchange of India Limited
>  (NSE). The information is solely intended for the individual/entity
>  it is addressed to. If you are not the intended recipient of this
>  message, please be aware that you are not authorized in any which
>  way whatsoever to read, forward, print, retain, copy or disseminate
>  this message or any part of it. If you have received this e-mail in
>  error, we would request you to please notify the sender immediately
>  by return e-mail and delete it from your computer. This e-mail
>  message including attachment(s), if any, is believed to be free of
>  any virus and NSE is not responsible for any loss or damage arising
>  in any way from its use.
> ___
> 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] Q. about core SQLite library

[Max Vlasov]

>
>  All you really need to do is splice your code between the VFS that
>  the SQLite engine sees and the native VFS layer that comes with the
>  distribution, adding a bit of extra code to xRead() and xWrite() to
>  munge the data.
>

I implemented this approach once, it worked, VFS also so flexible you can
even offset your data, for example write something unique seeding at the
start of the file and shift the actual sqlite data to some offset. Although
in this case xTruncate should be also adjusted.

The only probable disadvantage of using VFS is that sqlite temp file (etilq
... on windows) are bypassed by the VFS as long as I noticed, so if in any
case it is left in the system, the contents of temporary tables will be
exposed. Although I also noticed some tricky method they (temp files) are
created with, so even if I terminate the process unexpectedly they still
dissappear afterwards.

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

Re: [sqlite] size control of sqlite database

[Martin.Engelschalk]

Hi,

a sqlite database is a file, you can get its size using OS calls.
It is not possible to create a database with an initial size, because
the file grows dynamically when you insert data.

To avoid fragmentation, I also looked for a way to allocate empty space
inside the database file when creating it. Dr. Hipp proposed to create a
table with a single Blob-column, insert a very large, empty blob, and
then dropping the table. The file will then keep its size and afterwards
reuse the empty pages.
However, i could not find a way to determine when the empty pages are
used up and the file will start to grow again without checking the file
size after every insert.

Martin

gujx schrieb:
> Hi, I’d like to ask some question about the interface of the sqlite
> resource.
>
> Whether there is some interface to control the size of a database, for
> example, if I want to create a database with 5M initialized, how can I do
> that? And when I make change to a database, for example, insert a row to a
> table, then can I get the size of the database now?
>
>  
>
> Looking forward to your answer.
>
>  
>
>  
>
> Gu Jinxiang
>
>  
>
> 以上、よろしくお��いします。
>
>  
>
>   
> 
>
> ___
> 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_CANTOPEN Bohrbug

[D. Richard Hipp]

On Jan 14, 2010, at 12:06 AM, Jamie Hardt wrote:
>
> So, has anyone else run into SQLITE_CANTOPEN in a situation where it  
> wasn't a permissions issue?
>


My guess would be that the SQLite database is located in the same  
directory hierarchy that you are indexing and that somehow the system  
calls you are making to enumerate the paths are interfering with  
SQLite's ability to write to the database.  The fact that it is a  
CANTOPEN error suggests that it was trying to open a new rollback  
journal.  You might work around the problem by putting all of your  
inserts inside a single big transaction.

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] size control of sqlite database

[gujx]

Hi, I’d like to ask some question about the interface of the sqlite
resource.

Whether there is some interface to control the size of a database, for
example, if I want to create a database with 5M initialized, how can I do
that? And when I make change to a database, for example, insert a row to a
table, then can I get the size of the database now?

 

Looking forward to your answer.

 

 

Gu Jinxiang

 

以上、よろしくお��いします。

 

-- 

 

A new email address of FJWAN is launched from Apr.1 2007. 
The updated address is: g...@cn.fujitsu.com 

Development Dept.I
Nanjing Fujitsu Nanda Software Tech. Co., Ltd.(FNST) 8/F., Civil Defense
Building, No.189 Guangzhou Road, Nanjing, 210029, China
TEL：+86+25-86630566-619
COINS：79955-619 
FAX：+86+25-83317685
email：g...@cn.fujitsu.com 
-- 
This communication is for use by the intended recipient(s) only and may
contain information that is privileged, confidential and exempt from
disclosure under applicable law. If you are not an intended 
recipient of this communication, you are hereby notified that any
dissemination, distribution or copying hereof is strictly prohibited. If you
have received this communication in error, please notify me by 
reply e-mail, permanently delete this communication from your system, and
destroy any hard copies you may have printed. 

 

 

 

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

Re: [sqlite] How to find Rank in SQLite3?

[Jigar Shah]

First of all thanks Tim and Pavel for replying to my query.


> 
> Assuming you're doing this by department, try to get just a list of the 
> distinct salary rankings into an inline view, using your count(*) +1 
> approach to set the salary rank,  where these three columns are unique 
> in combination:
> 
> (dept, salary, rank ) as S
> 
> select EE.dept, EE.name, EE.salary, S.rank
> from employees as EE
> JOIN
> (inline view to create distinct salary bands by department goes here) as 
S
> on EE.dept = S.dept
> and EE.salary = S.salary
> order by dept, rank


My aim is to find rank of each employee within his dept. So I guess the 
inline view (adding empname and empno columns) is the final output that 
I require.

So to join this output with original table would be an extra activity.

Also, count(*) +1 approach for ranking is time consuming as it iterates 
over 
entire table for each row, so using the same in inline view may not help 
to 
increase speed.


>> I have used following query but it takes more than one hour even 
after
>> indexing,
> 
> Shouldn't be - query is not so hard if a proper index used. What index
> did you create?

I have used following index.

create index tempidx on EMPLOYEES(DEPTNO, SAL);

I have tried query, using count(*) +1 approach, in mysql but it was slower
(>two hours, still query was running) than Sqlite.


Is there is any other possible way to find out rank?



Thanks,

Jigar Shah



Disclaimer note on content of this message including enclosure(s)and   
 attachments(s): The contents of this e-mail are the privileged and 
 confidential material of National Stock Exchange of India Limited  
 (NSE). The information is solely intended for the individual/entity
 it is addressed to. If you are not the intended recipient of this  
 message, please be aware that you are not authorized in any which  
 way whatsoever to read, forward, print, retain, copy or disseminate
 this message or any part of it. If you have received this e-mail in
 error, we would request you to please notify the sender immediately
 by return e-mail and delete it from your computer. This e-mail 
 message including attachment(s), if any, is believed to be free of 
 any virus and NSE is not responsible for any loss or damage arising
 in any way from its use.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users