Re: [sqlite] sqlite-users Digest, Vol 46, Issue 29

[Pete]

Thanks.  I guess I'd like to confirm just where column aliases can
be referenced.  I think they cannot be referenced within the list of column
names in which they are defined, and they can be referenced in any other
clauses of the SELECT statement, eg WHERE, ORDER BY, GROUP BY, HAVING. Is
that correct?
Pete






> --
>
> Message: 11
> Date: Fri, 28 Oct 2011 16:34:15 -0400
> From: Igor Tandetnik 
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Referring to column alias
> Message-ID: 
> Content-Type: text/plain; charset=UTF-8; format=flowed
>
> On 10/28/2011 4:28 PM, Pete wrote:
> > I have another variation of this issue:
> >
> > SELECT col1 - col2 as Total, Total * price FROM tst
> >
> > ... gives an error " no such column: Total".  I can just repeat "col1 -
> col2"
> > of course, but wondering if there is a way to refer to Total within the
> > SELECT.
>
> This is by design, blessed by SQL-92 standard. The closest you can get
> is something like
>
> SELECT Total, Total * price FROM
> (select col1 - col2 as Total, price from tst);
>
> This will likely be noticeably slower though.
> --
> Igor Tandetnik
>
>
>
> --
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Disk I/O Error

[Richard Hipp]

On Mon, Oct 31, 2011 at 5:40 PM, Pavel Ivanov  wrote:

> > Error code 522 is SQLITE_IOERR_SHORT_READ.  It is generated here:
> >
> > http://www.sqlite.org/src/artifact/07acbb3e074e?ln=3012
> >
> > SQLite was trying to read N bytes and got back M byes where M>0 and M
> Could it be that N bytes cannot be read atomically and operation was
> interrupted in the middle by some signal? This scenario can explain
> short read even if database is not truncated or corrupted.
>

If an interrupt occurs that stops the read before it can complete, then the
read should be tried again.
http://www.sqlite.org/src/artifact/07acbb3e074e?ln=2968


>
>
> Pavel
>
>
> On Mon, Oct 31, 2011 at 5:31 PM, Richard Hipp  wrote:
> > On Mon, Oct 31, 2011 at 5:01 PM, Korey Calmettes <
> kcalmet...@icontime.com>wrote:
> >
> >> Hello,
> >>
> >> We are having a random problem with our system that has be puzzled at
> >> this point.
> >>
> >> First a little background.  We have an embedded system running an ARM
> >> processor and JFFS file system.  We are running 3.7.7.1.  I e-mailed
> >> about a month ago about enabling WAL on our system.  Found out that I
> >> needed to set SQLITE_SHM_DIRECTORY to our tmpfs file system when
> >> compiling as JFFS doesn't support shared mmap files.
> >>
> >> This has been working fine, however throughout our testing, we have been
> >> hitting a problem.  It seems that some tables are no longer accessible
> >> giving a Disk I/O Error.  I logged into the command line interface and
> >> replicated the problem.  I ran ".log stdout" and here are the results.
> >>
> >> sqlite> select password from auth;
> >> (522) statement aborts at 7: [select password from auth;]
> >> Error: disk I/O error
> >>
> >> However access to other tables will work without any problems.
> >>
> >
> > Error code 522 is SQLITE_IOERR_SHORT_READ.  It is generated here:
> >
> > http://www.sqlite.org/src/artifact/07acbb3e074e?ln=3012
> >
> > SQLite was trying to read N bytes and got back M byes where M>0 and M >
> > Perchance is your database file truncated?  What happens when you run
> > "PRAGMA integrity_check" on the database?  What is the page size of your
> > database?
> >
> >
> >
> >>
> >> I searched for how to read the log without success.  I am hopeful that
> >> someone would be able to tell me what's going on.
> >>
> >> Korey
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > 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
>



-- 
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] Disk I/O Error

[Pavel Ivanov]

> Error code 522 is SQLITE_IOERR_SHORT_READ.  It is generated here:
>
>     http://www.sqlite.org/src/artifact/07acbb3e074e?ln=3012
>
> SQLite was trying to read N bytes and got back M byes where M>0 and M wrote:
> On Mon, Oct 31, 2011 at 5:01 PM, Korey Calmettes 
> wrote:
>
>> Hello,
>>
>> We are having a random problem with our system that has be puzzled at
>> this point.
>>
>> First a little background.  We have an embedded system running an ARM
>> processor and JFFS file system.  We are running 3.7.7.1.  I e-mailed
>> about a month ago about enabling WAL on our system.  Found out that I
>> needed to set SQLITE_SHM_DIRECTORY to our tmpfs file system when
>> compiling as JFFS doesn't support shared mmap files.
>>
>> This has been working fine, however throughout our testing, we have been
>> hitting a problem.  It seems that some tables are no longer accessible
>> giving a Disk I/O Error.  I logged into the command line interface and
>> replicated the problem.  I ran ".log stdout" and here are the results.
>>
>> sqlite> select password from auth;
>> (522) statement aborts at 7: [select password from auth;]
>> Error: disk I/O error
>>
>> However access to other tables will work without any problems.
>>
>
> Error code 522 is SQLITE_IOERR_SHORT_READ.  It is generated here:
>
>     http://www.sqlite.org/src/artifact/07acbb3e074e?ln=3012
>
> SQLite was trying to read N bytes and got back M byes where M>0 and M
> Perchance is your database file truncated?  What happens when you run
> "PRAGMA integrity_check" on the database?  What is the page size of your
> database?
>
>
>
>>
>> I searched for how to read the log without success.  I am hopeful that
>> someone would be able to tell me what's going on.
>>
>> Korey
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Disk I/O Error

[Richard Hipp]

On Mon, Oct 31, 2011 at 5:01 PM, Korey Calmettes wrote:

> Hello,
>
> We are having a random problem with our system that has be puzzled at
> this point.
>
> First a little background.  We have an embedded system running an ARM
> processor and JFFS file system.  We are running 3.7.7.1.  I e-mailed
> about a month ago about enabling WAL on our system.  Found out that I
> needed to set SQLITE_SHM_DIRECTORY to our tmpfs file system when
> compiling as JFFS doesn't support shared mmap files.
>
> This has been working fine, however throughout our testing, we have been
> hitting a problem.  It seems that some tables are no longer accessible
> giving a Disk I/O Error.  I logged into the command line interface and
> replicated the problem.  I ran ".log stdout" and here are the results.
>
> sqlite> select password from auth;
> (522) statement aborts at 7: [select password from auth;]
> Error: disk I/O error
>
> However access to other tables will work without any problems.
>

Error code 522 is SQLITE_IOERR_SHORT_READ.  It is generated here:

 http://www.sqlite.org/src/artifact/07acbb3e074e?ln=3012

SQLite was trying to read N bytes and got back M byes where M>0 and M
> I searched for how to read the log without success.  I am hopeful that
> someone would be able to tell me what's going on.
>
> Korey
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] UPDATE

[Don V Nielsen]

I need help with a complex UPDATE.  I want to update each row in a table,
calculating an average, and then apply that value back into a column of the
same row.  Is this possible with Sqlite?  Below is code that should work
with SqlServer; its UPDATE supports a FROM statement.

UPDATE m SET rtwgt = avgrowid
FROM seg_02_matches as m
JOIN (
  SELECT pr3.zip, pr3.crrt, avg(ap.[rowid]) AS avgrowid
  FROM (
   SELECT pr1.zip, pr1.crrt, pr1.prty, 'WI' AS 'id' FROM pool_WI AS pr1
   UNION
   SELECT pr2.zip, pr2.crrt, pr2.prty, 'NY' AS 'id' FROM pool_NY AS pr2
  ) AS pr3
  INNER JOIN add_priorities AS ap ON ap.prty = pr3.prty AND ap.poolid =
pr3.id
  GROUP BY pr3.zip, pr3.crrt
) as sub ON m.zip = sub.zip AND m.route = sub.crrt

I don't think I can use WHERE IN because I need multiple elements returned
by the sub-select.

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

Re: [sqlite] SQLite Expert

[Don V Nielsen]

I use the free version of sqlite expert.  I use sqlite for my IO handling.
 Having the power of sql available to test values, generate counts, etc...
is indispensable.

On Sun, Oct 30, 2011 at 2:50 PM, Abair Heart  wrote:

> Hi all,
>
> Searching the archives implies, that "sqlite expert" hasn't been asked
> about.
>
> Has anyone tried it yet?  Is the program safe? time-saving?
>
>
> Thanks for any input.
>
> Abair
> ___
> 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] Disk I/O Error

[Korey Calmettes]

Hello,
 
We are having a random problem with our system that has be puzzled at
this point.
 
First a little background.  We have an embedded system running an ARM
processor and JFFS file system.  We are running 3.7.7.1.  I e-mailed
about a month ago about enabling WAL on our system.  Found out that I
needed to set SQLITE_SHM_DIRECTORY to our tmpfs file system when
compiling as JFFS doesn't support shared mmap files.
 
This has been working fine, however throughout our testing, we have been
hitting a problem.  It seems that some tables are no longer accessible
giving a Disk I/O Error.  I logged into the command line interface and
replicated the problem.  I ran ".log stdout" and here are the results.
 
sqlite> select password from auth;
(522) statement aborts at 7: [select password from auth;]
Error: disk I/O error

However access to other tables will work without any problems.
 
I searched for how to read the log without success.  I am hopeful that
someone would be able to tell me what's going on.
 
Korey
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Expert

[Abair Heart]

Thanks for your answer, Jean-Christophe,

I'll give it a try.


Abair



> On Sun, 30 Oct 2011 22:41:58 +0100 Jean-Christophe Deschamps said:
>
> >On 30 Oct 2011, at 7:50pm, Abair Heart wrote:
> >
> > > Searching the archives implies, that "sqlite expert" hasn't been asked
> > > about.
> > >
> > > Has anyone tried it yet?  Is the program safe? time-saving?
> > ...
>
> As a long-term fan user of SQLite Expert, I can vouch it's one of the
> very best if not the best Windows-based SQLite manager.  I just can't
> live without and use it hourly.
>
> The program is not only "safe" (don't know what you meant here) but
> guaranteed free of any kind of nasties


that's what I meant.


> even in its freeware
> version.  It also uninstalls cleanly.
> ...
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow inserts with UNIQUE

[Fabian]

2011/10/30 Black, Michael (IS) 

>
> #1 What version?
>

3.7.8, using System.Data.Sqlite wrapper


> #2 How long to insert the 1M?
>

10 seconds


>
> #3 What's the average string size?
>

55 characters


>
> #5 How long to create the index?
>

10 seconds


> #6 How long to insert the next 10,000?
>
>
34 seconds. But... only 1 second if I do it immediately after filling the
database, so it seems to be related wether the file is in the filesystem
cache or not. Server-apps will always have the file cached, but this is a
desktop-app, where the db will not be in the filesystem cache most of the
times.

One more thing...show us the EXPLAIN of your insert.  Is sounds like your
> insert is not using the index for the insert for some reason (buq in
> sqlite?).


0 Trace 0 0 0  00
1 Goto 0 18 0  00
2 OpenWrite 0 2 0 1 00
3 OpenWrite 1 14345 0 keyinfo(1,BINARY) 00
4 NewRowid 0 2 0  00
5 String8 0 3 0 test 00
6 SCopy 3 4 0  00
7 SCopy 2 5 0  00
8 MakeRecord 4 2 1 ab 00
9 SCopy 2 6 0  00
10 IsUnique 1 12 6 4 00
11 Goto 0 15 0  00
12 IdxInsert 1 1 0  10
13 MakeRecord 3 1 6 a 00
14 Insert 0 6 2 table 1b
15 Close 0 0 0  00
16 Close 1 0 0  00
17 Halt 0 0 0  00
18 Transaction 0 1 0  00
19 VerifyCookie 0 2 0  00
20 TableLock 0 2 1 table 00
21 Goto 0 2 0  00

The resulting database is about 125MB large. So 34 seconds seems way too
long, even if the whole db-file has to be read from disk, and stored into
memory, it shouldn't take that long.

Pragma's used:

PRAGMA page_size = 4096;
PRAGMA synchronous = OFF;
PRAGMA journal_mode = OFF;
PRAGMA temp_store = MEMORY;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA cache_size = 72500;

If you need any more info, let me know!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ChangePassword method problem

[Joe Mistachkin]

Reading your code quickly, it seems the problem may be related to the use
of single-quotes (') around the passwords in the connection string.  Please
remove all the single-quotes and try again.

--
Joe Mistachkin

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

Re: [sqlite] Best practices for dealing with password protected database

[Simon Slavin]

On 31 Oct 2011, at 8:07am, Eugene N wrote:

> For your .NET application to use the, DB it must first decrypt it; That
> means, storing the plain version in ram; A memory dump will pronto show the
> contents of this sqlite database;

Physical possession of the hardware concerned is always an end to encryption 
methods.  Once they've got the computer it's just a question of how much time 
and effort they want to figuring out where you put the password.  That's one 
reason so many systems have just thin clients on your user's hardware and do 
all the secure stuff on a server back in headquarters.

Igor's approach is as good as any: use a password that doesn't look like a text 
string.  You can also introduce an additional step of scrambling the password 
you store in some way, so a cracker doesn't spot a mystery string in a 
configuration file and say "Well, I guess that's the password in plaintext.".

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

Re: [sqlite] Corrupted Database

[Igor Tandetnik]

Özgür KELEŞ  wrote:
> We use sqlite in our industrial devices. But sometimes the database
> corrupted. We could not find the problem , how it can be possible to
> corrupt the database.

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

Section 6.0 "How To Corrupt Your Database Files"
-- 
Igor Tandetnik

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

Re: [sqlite] Default column values conflict with not null option

[Igor Tandetnik]

Евгений Земляков  wrote:
> Default column values conflict with not null option.

Conflict in what sense? What statement are you executing, and how does the 
outcome of running that statement differ from your expectations?

This works for me:

create table t(x integer not null default(42));

-- 
Igor Tandetnik

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

[sqlite] Default column values conflict with not null option

[Евгений Земляков]

Default column values conflict with not null option.

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

[sqlite] Corrupted Database

[Özgür KELEŞ]

Hi,
We use sqlite in our industrial devices. But sometimes the database 
corrupted. We could not find the problem , how it can be possible to 
corrupt the database.  It is possible to see powerless on devices and OS 
crashes cause of electromagnetic noises, rarely. I attached some of 
corrupted databases. Can you help me on this subject?


Our Tools:
*OS: Embedded Linux 2.6.30.4
*QT Framework 4.7.2
*File System: yaffs2


Best Rigards
--
*Özgür KELEŞ*

Kordonboyu Mh. Barbaros Cd. Usta 1 Apt. No:11/16 Kartal/Istanbul/Turkey

T:+90216 353 41 56 (pbx) F:+90216 374 19 15

*www.enmos.com *



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

Re: [sqlite] Best practices for dealing with password protected database

[Igor Tandetnik]

Bernd  wrote:
> This may not be really SQLite specific, but as it's at least SQLite
> related I thought I asked here.
> Our program ships with an encrypted SQLite database that has to be
> opened by the application to process some other data. As it's a .NET
> application, it's very easy to peek inside the source code (even though
> I'm planning to use an obfuscater tool). Are there any best practices on
> how to store the password to open the database?

Generate a random password when first creating the database. Encrypt it using 
ProtectedData.Protect and store the encrypted version somewhere (e.g in the 
registry, or in a file alongside the database file). Decrypt with 
ProtectedData.Unprotect before using.

This effectively encrypts the password with the user's Windows login 
credentials.
-- 
Igor Tandetnik

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

Re: [sqlite] Best practices for dealing with password protected database

[Eugene N]

Hello Bernd!

I have a very limited knowledge about such matters, but it seems to me
there is a caveat in the whole area of using encrypted data on a end-user
pc;

For your .NET application to use the, DB it must first decrypt it; That
means, storing the plain version in ram; A memory dump will pronto show the
contents of this sqlite database;

There is also handy way of using a debugger to find the function that
decrypts the db (by monitoring all standard .NET disk I/O, obviously the
function will have to read the file first);

So, given that you are aware of those issues, there is a way that has been
known to work in the past (for C programs)  - implement certain
anti-debugger techniques (program detected the presence of a debugger and
behaved differently or erratically);
Storing a password in an obscure manner (not a string! cause they can be
sniffed by 'strings' utility with ease) also is a good option; Using hash
to calculate password at run-time can aslo complicate the job of a will-be
hacker;

Hope that is helpful

Eugene


2011/10/31 Bernd 

> This may not be really SQLite specific, but as it's at least SQLite
> related I thought I asked here.
> Our program ships with an encrypted SQLite database that has to be opened
> by the application to process some other data. As it's a .NET application,
> it's very easy to peek inside the source code (even though I'm planning to
> use an obfuscater tool). Are there any best practices on how to store the
> password to open the database?
> To put things clear - we don't want to make the program absolutely
> hacker-proof, but simply avoid making it too easy for everybody to see how
> the database is structured and what it contains.
> __**_
> 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] Best practices for dealing with password protected database

[Bernd]

This may not be really SQLite specific, but as it's at least SQLite 
related I thought I asked here.
Our program ships with an encrypted SQLite database that has to be 
opened by the application to process some other data. As it's a .NET 
application, it's very easy to peek inside the source code (even though 
I'm planning to use an obfuscater tool). Are there any best practices on 
how to store the password to open the database?
To put things clear - we don't want to make the program absolutely 
hacker-proof, but simply avoid making it too easy for everybody to see 
how the database is structured and what it contains.

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