date:20120302

Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)

2012-03-02 Thread Dan Kennedy


On 03/03/2012 10:30 AM, Sreekumar TP wrote:

Could someone throw some light on this issue too?


I can't see from the stack trace why this is crashing.

Does it crash if you run the query from the sqlite shell?

Maybe try building the shell without optimizations, and
then running it under valgrind.

Dan.




Sreekumar
On Mar 2, 2012 10:05 AM, "Sreekumar TP"  wrote:


The backtrace
===


Program received signal SIGSEGV, Segmentation fault.
0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, yyminor=...,
pParse=0x2d4035c8) at sqlite3.c:106740
106740  }while( yymajor!=YYNOCODE&&  yypParser->yyidx>=0 );
#0  0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, yyminor=...,
pParse=0x2d4035c8) at sqlite3.c:106740
 yyminorunion = {
   yyinit = 735636932,
   yy0 = {
 z = 0x2bd8edc4 "FROM dir_table",
 n = 4
   },
   yy4 = 735636932,
   yy90 = {
 a = 735636932,
 b = 0x4
   },
   yy118 = {
 pExpr = 0x2bd8edc4,
 zStart = 0x4,
 zEnd = 0x2b697000 ""
   },
   yy203 = 0x2bd8edc4,
   yy210 = 196 '\304',
   yy215 = {
 value = 735636932,
 mask = 4
   },
   yy259 = 0x2bd8edc4,
   yy292 = {
 pLimit = 0x2bd8edc4,
 pOffset = 0x4
   },
   yy314 = 0x2bd8edc4,
   yy322 = 0x2bd8edc4,
   yy342 = {
 eOperator = {
   z = 0x2bd8edc4 "FROM dir_table",
   n = 4
 },
 not = 728330240
   },
   yy384 = 0x2bd8edc4,
   yy387 = 0x2bd8edc4
 }
 yyact = 21104640
 yyendofinput = 0
 yypParser = 0x2d401e40
#1  0x2b65a210 in sqlite3RunParser (pParse=0x2d4035c8, zSql=0x2bd8eda4
"SELECT COUNT(distinct Alb_Name) FROM dir_table", pzErrMsg=0x2bd8cbf8) at
sqlite3.c:107465
 nErr = 0
 i = 36
 pEngine = 0x2d401e40
 tokenType = 119
 lastTokenParsed =
 enableLookaside = 1 '\001'
 db = 0x6a14b0
 mxSqlLen = 10
#2  0x2b65ac68 in sqlite3Prepare (db=0x6a14b0, zSql=0x2bd8eda4 "SELECT
COUNT(distinct Alb_Name) FROM dir_table", nBytes=-1, saveSqlFlag=0,
pReprepare=0x0, ppStmt=0x2bd8cca8, pzTail=0x0) at sqlite3.c:90212
 pParse = 0x2d4035c8
 zErrMsg = 0x0
 rc =
 i =
#3  0x2b65b468 in sqlite3LockAndPrepare (db=,
zSql=, nBytes=-1, ppStmt=,
pzTail=0x0) at sqlite3.c:90304
 rc =
#4  sqlite3_prepare (db=, zSql=,
nBytes=-1, ppStmt=, pzTail=0x0) at sqlite3.c:24831
 rc = 21104640
#5  0x00466730 in sql_stmt (db=9, stmt=0xffc0) at /localhome/user/a/b/c/d/e/f/g/h/k.c:496
 errmsg = 0x0
 retval =
 __PRETTY_FUNCTION__ = "\000\000\000\000\000\000\000\000"








On Thu, Mar 1, 2012 at 11:42 PM, Dan Kennedywrote:


On 03/01/2012 10:54 PM, Sreekumar TP wrote:


version 3.7.7.1
The query works on x86, but fails on MIPS processor!



Are you able to post a stack trace? Ideally generated by
the gdb "where full" command. Thanks.

Dan.





Sreekumar



On Thu, Mar 1, 2012 at 7:00 PM, Dan Kennedy
  wrote:

  On 03/01/2012 05:48 PM, Sreekumar TP wrote:


  In my system, the statement causes sqlite3parser function to crash.

My compiler is  mips , little endian, gcc version is 4.5.2



SQLite version? 3.7.10 seems Ok here.

Dan.

___
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





___
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] SELECT COUNT (DISTINCT column_name FROM table)

2012-03-02 Thread Sreekumar TP

Could someone throw some light on this issue too?

Sreekumar
On Mar 2, 2012 10:05 AM, "Sreekumar TP"  wrote:

> The backtrace
> ===
>
>
> Program received signal SIGSEGV, Segmentation fault.
> 0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, yyminor=...,
> pParse=0x2d4035c8) at sqlite3.c:106740
> 106740  }while( yymajor!=YYNOCODE && yypParser->yyidx>=0 );
> #0  0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, yyminor=...,
> pParse=0x2d4035c8) at sqlite3.c:106740
> yyminorunion = {
>   yyinit = 735636932,
>   yy0 = {
> z = 0x2bd8edc4 "FROM dir_table",
> n = 4
>   },
>   yy4 = 735636932,
>   yy90 = {
> a = 735636932,
> b = 0x4
>   },
>   yy118 = {
> pExpr = 0x2bd8edc4,
> zStart = 0x4 ,
> zEnd = 0x2b697000 ""
>   },
>   yy203 = 0x2bd8edc4,
>   yy210 = 196 '\304',
>   yy215 = {
> value = 735636932,
> mask = 4
>   },
>   yy259 = 0x2bd8edc4,
>   yy292 = {
> pLimit = 0x2bd8edc4,
> pOffset = 0x4
>   },
>   yy314 = 0x2bd8edc4,
>   yy322 = 0x2bd8edc4,
>   yy342 = {
> eOperator = {
>   z = 0x2bd8edc4 "FROM dir_table",
>   n = 4
> },
> not = 728330240
>   },
>   yy384 = 0x2bd8edc4,
>   yy387 = 0x2bd8edc4
> }
> yyact = 21104640
> yyendofinput = 0
> yypParser = 0x2d401e40
> #1  0x2b65a210 in sqlite3RunParser (pParse=0x2d4035c8, zSql=0x2bd8eda4
> "SELECT COUNT(distinct Alb_Name) FROM dir_table", pzErrMsg=0x2bd8cbf8) at
> sqlite3.c:107465
> nErr = 0
> i = 36
> pEngine = 0x2d401e40
> tokenType = 119
> lastTokenParsed = 
> enableLookaside = 1 '\001'
> db = 0x6a14b0
> mxSqlLen = 10
> #2  0x2b65ac68 in sqlite3Prepare (db=0x6a14b0, zSql=0x2bd8eda4 "SELECT
> COUNT(distinct Alb_Name) FROM dir_table", nBytes=-1, saveSqlFlag=0,
> pReprepare=0x0, ppStmt=0x2bd8cca8, pzTail=0x0) at sqlite3.c:90212
> pParse = 0x2d4035c8
> zErrMsg = 0x0
> rc = 
> i = 
> #3  0x2b65b468 in sqlite3LockAndPrepare (db=,
> zSql=, nBytes=-1, ppStmt=,
> pzTail=0x0) at sqlite3.c:90304
> rc = 
> #4  sqlite3_prepare (db=, zSql=,
> nBytes=-1, ppStmt=, pzTail=0x0) at sqlite3.c:24831
> rc = 21104640
> #5  0x00466730 in sql_stmt (db=9, stmt=0xffc0  of bounds>) at /localhome/user/a/b/c/d/e/f/g/h/k.c:496
> errmsg = 0x0
> retval = 
> __PRETTY_FUNCTION__ = "\000\000\000\000\000\000\000\000"
>
>
>
> 
>
>
>
>
> On Thu, Mar 1, 2012 at 11:42 PM, Dan Kennedy wrote:
>
>> On 03/01/2012 10:54 PM, Sreekumar TP wrote:
>>
>>> version 3.7.7.1
>>> The query works on x86, but fails on MIPS processor!
>>>
>>
>> Are you able to post a stack trace? Ideally generated by
>> the gdb "where full" command. Thanks.
>>
>> Dan.
>>
>>
>>
>>>
>>> Sreekumar
>>>
>>>
>>>
>>> On Thu, Mar 1, 2012 at 7:00 PM, Dan Kennedy
>>>  wrote:
>>>
>>>  On 03/01/2012 05:48 PM, Sreekumar TP wrote:

  In my system, the statement causes sqlite3parser function to crash.
> My compiler is  mips , little endian, gcc version is 4.5.2
>
>
 SQLite version? 3.7.10 seems Ok here.

 Dan.

 ___
 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
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DISTINCT bug with 3.7.10

2012-03-02 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/03/12 15:20, Steven Russell wrote:
> I obviously don't expect the duplicate results here.  If you create the
> table without the UNIQUE clause, then the results are as expected (1
> and 2 both only show up once).

BTW the team agreed it was a bug, created a ticket, fixed it and added it
to the test suite.  For some reason they don't tend to mention this on the
mailing list.  If you follow the timeline you can tell.

  http://www.sqlite.org/src/info/3557ad65a0

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9RiU4ACgkQmOOfHg372QQ2UACeMM1hQi7pIctdSHwTVJx+h4R/
FtcAoJvexerIIuyuT2eX428cmIFH+kT4
=s4jH
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to replace connection string at runtime

2012-03-02 Thread Agrawal, Manish

Thanks very much. After trying many of the suggestions among the search 
results, the solution that worked was:

http://social.msdn.microsoft.com/Forums/fi-FI/wpf/thread/b7d8a3dd-031e-481f-94b7-919373c61f4b

The only problem is that the settings.designer.cs file seems to be 
auto-generated, meaning that the changes could get lost if the IDE re-generates 
the file.

Thanks
Manish

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Kevin Benson
Sent: Friday, March 02, 2012 5:16 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] how to replace connection string at runtime

--
   --
  --
 --ô¿ô--
K e V i N


On Fri, Mar 2, 2012 at 2:47 PM, Agrawal, Manish  wrote:

>  My question is: what is the simplest way to replace the connection string
> in a C# application with the file location at runtime?  I do want the
> convenience of the table objects that have been auto-generated.

https://www.google.com/search?num=100=en==c%23+sqlite+app.config+%22how+to+change%22+connection+string
 --
   --
  --
 --ô¿ô--
K e V i N
___
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] DISTINCT bug with 3.7.10

2012-03-02 Thread Larry Brasfield


Simon Slavin wrote and quoted:

>> Would the output of 'PRAGMA compile_options;' be sufficient ?
>
> That's a cute and useful feature I had forgotten.  It would be sufficient to 
show the preprocessor variable values that were in effect as the amalgamation was 
compiled.  However, for something that looks like either a SQLite code error or a 
gcc code generation error, the optimization settings are also likely to be needed, 
and those are not susceptible to that PRAGMA's behavior.

Don't forget this isn't is own weird project which may be using the API in an 
unusual way.  It's the shell tool.  Possibly even a the version downloaded from 
the SQLite web site.  Given how clear the bug is, with the addition of the 
bug-reporter's 'PRAGMA compile_options;', I think that's enough information to 
let the team try to reproduce the behaviour and see what happens.

By the way, this shell tool included with a recent version of OS X does not 
have the problem.


I don't want to get into a big debate over this, (in part because it's 
an indeterminate sort of issue), but I am responding so that the O.P. is 
not induced to ignore what I still think was good advice.


Of course "the team" can try to reproduce, something that can try with 
whatever repro info they have.  And, as I also indicated, the 
preprocessor settings may be enough.  If they are not, if the problem in 
fact arises with some strange combination of the seemingly countless 
optimization and other code generation options gcc has, the O.P. will 
ultimately have to discover that himself or provide those options to 
"the team".  It would be more efficient for whoever does this little 
investigation to have full repro data.  If they can only guess at the 
compilation flags, they do not have reproducibility due to the 
practically unbounded combinations, and that investigation will have to 
be broken into more sessions while the data I suggest be provided is 
obtained.  Getting that data is not some big effort.  If the O.P. is an 
experienced developer, he will know just how to get it.  If not, he can 
either state he is using defaults or reflect upon whether playing with 
intriguing gcc flags is the best course.


To the O.P.: As someone who has seen a few code generation problems, I 
can state without doubt that optimization or other code generation 
settings may very well be relevant here, and that providing them up 
front will help resolve this if that is the case.


To Simon: I do not claim here that these settings *are* the problem, 
just that if they are, they will need to be made evident to enable 
progress to be made toward a solution.

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

Re: [sqlite] DISTINCT bug with 3.7.10

2012-03-02 Thread Simon Slavin

On 3 Mar 2012, at 12:46am, Larry Brasfield  wrote:

>> On 3 Mar 2012, at 12:29am, Larry Brasfield  
>> wrote:
>> 
>> > That's approaching a pretty good "bug" report.  However, I would suggest a 
>> > little more to promote a resolution of this problem.  You do not state the 
>> > compilation options. Optimization settings and preprocessor variable 
>> > settings are critical for narrowing what is happening.  Also, the 
>> > preprocessor settings may be the whole problem, one which can be quickly 
>> > diagnosed for you if they are revealed.
>> 
>> Would the output of 'PRAGMA compile_options;' be sufficient ?
> 
> That's a cute and useful feature I had forgotten.  It would be sufficient to 
> show the preprocessor variable values that were in effect as the amalgamation 
> was compiled.  However, for something that looks like either a SQLite code 
> error or a gcc code generation error, the optimization settings are also 
> likely to be needed, and those are not susceptible to that PRAGMA's behavior.

Don't forget this isn't is own weird project which may be using the API in an 
unusual way.  It's the shell tool.  Possibly even a the version downloaded from 
the SQLite web site.  Given how clear the bug is, with the addition of the 
bug-reporter's 'PRAGMA compile_options;', I think that's enough information to 
let the team try to reproduce the behaviour and see what happens.

By the way, this shell tool included with a recent version of OS X does not 
have the problem.

162:~ simon$ which sqlite3
/usr/bin/sqlite3
SQLite version 3.7.7 2011-10-10 22:11:44
sqlite> PRAGMA compile_options;
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_LOCKING_STYLE=1
ENABLE_RTREE
OMIT_AUTORESET
OMIT_BUILTIN_TEST
OMIT_LOAD_EXTENSION
TEMP_STORE=1
THREADSAFE=2

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

Re: [sqlite] DISTINCT bug with 3.7.10

2012-03-02 Thread Larry Brasfield


On 3 Mar 2012, at 12:29am, Larry Brasfield  wrote:

> That's approaching a pretty good "bug" report.  However, I would suggest a 
little more to promote a resolution of this problem.  You do not state the compilation 
options.  Optimization settings and preprocessor variable settings are critical for 
narrowing what is happening.  Also, the preprocessor settings may be the whole problem, one 
which can be quickly diagnosed for you if they are revealed.

Would the output of 'PRAGMA compile_options;' be sufficient ?


That's a cute and useful feature I had forgotten.  It would be 
sufficient to show the preprocessor variable values that were in effect 
as the amalgamation was compiled.  However, for something that looks 
like either a SQLite code error or a gcc code generation error, the 
optimization settings are also likely to be needed, and those are not 
susceptible to that PRAGMA's behavior.  Of course, if some inconsistent 
set of preprocessor settings was used, that will become evident without 
the optimizations being known.  (I highly doubt a SQLite code error in 
this case.)


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

Re: [sqlite] DISTINCT bug with 3.7.10

2012-03-02 Thread Simon Slavin


On 3 Mar 2012, at 12:29am, Larry Brasfield  wrote:

> That's approaching a pretty good "bug" report.  However, I would suggest a 
> little more to promote a resolution of this problem.  You do not state the 
> compilation options.  Optimization settings and preprocessor variable 
> settings are critical for narrowing what is happening.  Also, the 
> preprocessor settings may be the whole problem, one which can be quickly 
> diagnosed for you if they are revealed.

Would the output of 'PRAGMA compile_options;' be sufficient ?

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

Re: [sqlite] DISTINCT bug with 3.7.10

2012-03-02 Thread Larry Brasfield


Steven Russell wrote:

I just grabbed the 3.7.10 amalgamation source and built it, but have run into 
an issue where DISTINCT doesn't appear to actually return distinct values on a 
table that includes a UNIQUE clause.  It unexpectedly returns duplicates 
instead.

My build environment is:

- Mac OS X 10.6.8
- Xcode 3.2.5
- The source is built from within an Xcode project using LLVM GCC 4.2
- As mentioned above, I'm using the amalgamation source

I found a fairly simple repro case using the shell tool (again, built by Xcode 
as above).

% ./sqlite3 :memory:
SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t ( a INTEGER NOT NULL, b INTEGER NOT NULL, UNIQUE (a,b) );
sqlite> INSERT INTO t VALUES(1,1);
sqlite> INSERT INTO t VALUES(2,1);
sqlite> INSERT INTO t VALUES(3,1);
sqlite> INSERT INTO t VALUES(2,2);
sqlite> INSERT INTO t VALUES(3,2);
sqlite> INSERT INTO t VALUES(4,2);
sqlite> SELECT DISTINCT b from t WHERE a IN (1,2,3);
1
2
1
2


I obviously don't expect the duplicate results here.  If you create the table 
without the UNIQUE clause, then the results are as expected (1 and 2 both only 
show up once).

As a point of reference, the sqlite3 shell tool that ships with the OS (3.6.12) 
does produce the correct results.


That's approaching a pretty good "bug" report.  However, I would suggest 
a little more to promote a resolution of this problem.  You do not state 
the compilation options.  Optimization settings and preprocessor 
variable settings are critical for narrowing what is happening.  Also, 
the preprocessor settings may be the whole problem, one which can be 
quickly diagnosed for you if they are revealed.


For your own resolution, you might try eliminating optimizations.  I 
would want to review docs for the preprocessor settings if this was my 
problem.


Good luck,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Simon Slavin

On 2 Mar 2012, at 10:45pm, Steffen Mangold  wrote:

> I now delete the malform message and the rollback command from the *.sql file 
> and run ".read".

Okay ...

> Sqlite shell runs complete and the shell ask me for new command "> " (DB file 
> seems to have the right size.
> I'm happy now and enter command ".exit" and bam Db file has 0kb?!?!?
> What have i done wrong? Any Commit command or something?

By all means enter 'COMMIT;' as a command after your '.read' has finished.  
Worst it can do is issue an error message.  Hmm.  Yes, if the file you read 
contains a BEGIN and then a ROLLBACK then the shell tool may not have the logic 
to realise that the end of the file means there should probably be a COMMIT 
somewhere.

Then, before your '.exit' command, try a '.tables' command and see if the 
database now has tables in.

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/03/12 15:32, Steffen Mangold wrote:
> how to "replace" this?

Change the abort to commit as others pointed out.  The dump code does the
following:

print BEGIN
foreach table in the database:
   foreach row in the table:
 print the row
print COMMIT

However if there is an error returned (SQLITE_CORRUPT) while iterating
over each row then it may start from the end and iterate backwards.
Instead of commit, it prints ABORT on failing to completely iterate over
the table.

> But sometime my DB corrupted and .dump repairs it for me without data
> loose. (was only some index errors).

How do you know there is no data loss?  You could only know that if you
had a good clean copy of the entire database, in which case you can just
use that.

The "index errors" are not the only errors.  They are only the ones
reported.  There can be silent errors, undetected corruption, stale pages
and who knows what else.

While doing a dump SQLite does not look at the contents of indices.  If
corruption was only inside an index then it would not be detected during
.dump and you could repair by dropping and recreating the index.

> But this time a part of the database goes really corrupt, so i now this
> data is lost.

The errors reported are not all the errors that exist, only a subset of them,

> The damage has come with a server blackout because USV failure. I now
> from docu that this can heavily damage a SQLite DB.

A UPS failure will not corrupt a SQLite database, nor will other forms of
power failure.

You can get corruption if the drive or controller lie about content being
synced during a power failure, but you should fix the drive/controller.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9RXQYACgkQmOOfHg372QQOFQCgighDzftxJVxe0RGNANZMzoln
Jw4AoLz+Q237aPK6aoQ8/nwKRjnxbnyt
=WohS
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold

>
> You should be able to, yes. Just type in "END;" (without quotes, but with 
> semicolon).
>

Ok thank you i will try :) (in a few hours because DB is so big. :) 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/03/12 14:45, Steffen Mangold wrote:
> I'm happy now and enter command ".exit" and bam Db file has 0kb?!?!? 
> What have i done wrong? Any Commit command or something?

What you don't seem to understand is that your original database is
corrupt.  The data that is in it is not exactly what SQLite (and hence
your program) put in it.  While doing the dump, the corruption was
discovered and the dump aborted.  You just replaced the abort command with
"save what we have so far".

There is no way for SQLite to recover what is changed/missing/corrupted.
You'll need to work out what the damage has been.  The integrity check
only examines the top level structure - it does not detect data changes
(mostly).  For example if every 'a' had been changed to a 'b' it would not
detect that.

You should also work out how the corruption happened since you really
don't want it to happen again.  Here is how to corrupt a SQLite database:

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

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9RVc8ACgkQmOOfHg372QQQ3gCfZBxh6oxaZ2OXhYDB9xsK7+BT
38IAnj7aiKugfj1w6/L1GbWfvkAsAz9/
=wWdx
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DISTINCT bug with 3.7.10

2012-03-02 Thread Steven Russell


I just grabbed the 3.7.10 amalgamation source and built it, but have run into 
an issue where DISTINCT doesn't appear to actually return distinct values on a 
table that includes a UNIQUE clause.  It unexpectedly returns duplicates 
instead.

My build environment is:

- Mac OS X 10.6.8
- Xcode 3.2.5
- The source is built from within an Xcode project using LLVM GCC 4.2
- As mentioned above, I'm using the amalgamation source

I found a fairly simple repro case using the shell tool (again, built by Xcode 
as above).

% ./sqlite3 :memory:
SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t ( a INTEGER NOT NULL, b INTEGER NOT NULL, UNIQUE (a,b) );
sqlite> INSERT INTO t VALUES(1,1);
sqlite> INSERT INTO t VALUES(2,1);
sqlite> INSERT INTO t VALUES(3,1);
sqlite> INSERT INTO t VALUES(2,2);
sqlite> INSERT INTO t VALUES(3,2);
sqlite> INSERT INTO t VALUES(4,2);
sqlite> SELECT DISTINCT b from t WHERE a IN (1,2,3);
1
2
1
2


I obviously don't expect the duplicate results here.  If you create the table 
without the UNIQUE clause, then the results are as expected (1 and 2 both only 
show up once).

As a point of reference, the sqlite3 shell tool that ships with the OS (3.6.12) 
does produce the correct results.



-- Steven Russell
sruss...@extensis.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Igor Tandetnik


On 3/2/2012 5:57 PM, Steffen Mangold wrote:


If you have a BEGIN command in your script, then you should also have END or 
COMMIT at the end (the two are synonyms).



can i do this by shell command after ".read" if my SQL script has miss that?


You should be able to, yes. Just type in "END;" (without quotes, but 
with semicolon).

--
Igor Tandetnik

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

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

2012-03-02 Thread Pavel Ivanov

> I have created my own function  which uses a global variable for the default
> locale. (see attached)

I guess I received your attachment because you sent this email to me
directly but generally this list doesn't allow attachments - you
should include your code into email.

And about your code: I can't check it all I've never written user
functions for SQLite. But your setLocale function is incorrect because
your copy the characters from zLocale and don't copy zero-termination
of the string.

>>   At application startup you fill this variable with value
>>   from table db_locale or with some default value if there's nothing in
>>   db_locale. Then each time you change your application's variable you
>>   save new value in table db_locale. That's it.
>
> I am struggling with this one, could someone help me out with sample code.
> I figure that the code would go to  openDatabase(..), but that is about all
> I know at the moment.

No, you won't use openDatabase and won't use any internal functions of
SQLite. You will use sqlite3_open, sqlite3_prepare_v2, sqlite3_step
and sqlite3_finalize just like with any regular access to SQLite
database.


Pavel


On Fri, Mar 2, 2012 at 5:51 PM, Grace Simon Batumbya
 wrote:
>
>   That's a wrong approach. First, you don't need to modify functions in
>   SQLite code, you need to create your own. Your function will convert
>   using locale saved in some variable in your application.
>
> I have created my own function  which uses a global variable for the default
> locale. (see attached)
>
> At application startup you fill this variable with value
>   from table db_locale or with some default value if there's nothing in
>   db_locale. Then each time you change your application's variable you
>   save new value in table db_locale. That's it.
>
> I am struggling with this one, could someone help me out with sample code.
> I figure that the code would go to  openDatabase(..), but that is about all
> I know at the moment.
>
> Thanks.
>
> Regards,
>
> Grace Batumbya
> Research Assistant | Seneca CDOT
> Phone: 416-491-5050 x3548
> cdot.senecac.on.ca
>
> On 3/1/2012 09:48, Pavel Ivanov wrote:
>
> Given that there exists a table db_locale [CREATE TABLE db_locale (locale
> text)],
> what lines of code would be used to query that table from inside this
> function?
>
> That's a wrong approach. First, you don't need to modify functions in
> SQLite code, you need to create your own. Your function will convert
> using locale saved in some variable in your application. At
> application startup you fill this variable with value from table
> db_locale or with some default value if there's nothing in db_locale.
> Then each time you change your application's variable you save new
> value in table db_locale. That's it.
>
>
> Pavel
>
>
> On Thu, Mar 1, 2012 at 9:33 AM, Grace Simon Batumbya
>  wrote:
>
> I found the function that I would need to modify (see below).
>
> static void icuCaseFunc16(sqlite3_context *p, int nArg, sqlite3_value
> **apArg){
>   const UChar *zInput;
>   UChar *zOutput;
>   int nInput;
>   int nOutput;
>
>   UErrorCode status = U_ZERO_ERROR;
>   const char *zLocale = 0;
>
>   assert(nArg==1 || nArg==2);
>   if( nArg==2 ){
>     zLocale = (const char *)sqlite3_value_text(apArg[1]);
>   }
>
>   zInput = sqlite3_value_text16(apArg[0]);
>   if( !zInput ){
>     return;
>   }
>   nInput = sqlite3_value_bytes16(apArg[0]);
>
>   nOutput = nInput * 2 + 2;
>   zOutput = sqlite3_malloc(nOutput);
>   if( !zOutput ){
>     return;
>   }
>
>   if( sqlite3_user_data(p) ){
>     u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale, );
>   }else{
>     u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale, );
>   }
>
>   if( !U_SUCCESS(status) ){
>     icuFunctionError(p, "u_strToLower()/u_strToUpper", status);
>     return;
>   }
>
>   sqlite3_result_text16(p, zOutput, -1, xFree);
> }
>
> Given that there exists a table db_locale [CREATE TABLE db_locale (locale
> text)],
> what lines of code would be used to query that table from inside this
> function?
>
> Grace Batumbya
> Research Assistant | Seneca CDOT
> Phone: 416-491-5050 x3548
> cdot.senecac.on.ca
>
> On 3/1/2012 08:56, Grace Batumbya wrote:
>
> Is there an example extension you know that I could look at that does this?
> (i am a novice at SQLite)
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Pavel Ivanov [paiva...@gmail.com]
> Sent: March 1, 2012 8:52 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Set Locale for upper() and lower() using a pragma
> variable
>
> On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya
>  wrote:
>
> You can simply register your
> own lower/upper with one argument that looks wherever you want to know
> what locale to use.
>
> The part of registering a function to override lower/upper I think

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold

>
> If you have a BEGIN command in your script, then you should also have END or 
> COMMIT at the end (the two are synonyms).
>

can i do this by shell command after ".read" if my SQL script has miss that?

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Igor Tandetnik


On 3/2/2012 5:45 PM, Steffen Mangold wrote:

WHAT THE  !

I now delete the malform message and the rollback command from the *.sql file and run 
".read".
Sqlite shell runs complete and the shell ask me for new command ">  " (DB file 
seems to have the right size.
I'm happy now and enter command ".exit" and bam Db file has 0kb?!?!?
What have i done wrong? Any Commit command or something?


If you have a BEGIN command in your script, then you should also have 
END or COMMIT at the end (the two are synonyms).

--
Igor Tandetnik

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

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

2012-03-02 Thread Grace Simon Batumbya




   That's a wrong approach. First, you don't need to modify functions in
   SQLite code, you need to create your own. Your function will convert
   using locale saved in some variable in your application.
I have created my own function  which uses a global variable for the 
default locale. (see attached)

At application startup you fill this variable with value
   from table db_locale or with some default value if there's nothing in
   db_locale. Then each time you change your application's variable you
   save new value in table db_locale. That's it.

I am struggling with this one, could someone help me out with sample code.
I figure that the code would go to openDatabase(..), but that is about 
all I know at the moment.


Thanks.

Regards,
*Grace Batumbya*
Research Assistant | Seneca CDOT
Phone: 416-491-5050 x3548
cdot.senecac.on.ca 

On 3/1/2012 09:48, Pavel Ivanov wrote:

Given that there exists a table db_locale [CREATE TABLE db_locale (locale
text)],
what lines of code would be used to query that table from inside this
function?

That's a wrong approach. First, you don't need to modify functions in
SQLite code, you need to create your own. Your function will convert
using locale saved in some variable in your application. At
application startup you fill this variable with value from table
db_locale or with some default value if there's nothing in db_locale.
Then each time you change your application's variable you save new
value in table db_locale. That's it.


Pavel


On Thu, Mar 1, 2012 at 9:33 AM, Grace Simon Batumbya
  wrote:

I found the function that I would need to modify (see below).

static void icuCaseFunc16(sqlite3_context *p, int nArg, sqlite3_value
**apArg){
   const UChar *zInput;
   UChar *zOutput;
   int nInput;
   int nOutput;

   UErrorCode status = U_ZERO_ERROR;
   const char *zLocale = 0;

   assert(nArg==1 || nArg==2);
   if( nArg==2 ){
 zLocale = (const char *)sqlite3_value_text(apArg[1]);
   }

   zInput = sqlite3_value_text16(apArg[0]);
   if( !zInput ){
 return;
   }
   nInput = sqlite3_value_bytes16(apArg[0]);

   nOutput = nInput * 2 + 2;
   zOutput = sqlite3_malloc(nOutput);
   if( !zOutput ){
 return;
   }

   if( sqlite3_user_data(p) ){
 u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale,);
   }else{
 u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale,);
   }

   if( !U_SUCCESS(status) ){
 icuFunctionError(p, "u_strToLower()/u_strToUpper", status);
 return;
   }

   sqlite3_result_text16(p, zOutput, -1, xFree);
}

Given that there exists a table db_locale [CREATE TABLE db_locale (locale
text)],
what lines of code would be used to query that table from inside this
function?

Grace Batumbya
Research Assistant | Seneca CDOT
Phone: 416-491-5050 x3548
cdot.senecac.on.ca

On 3/1/2012 08:56, Grace Batumbya wrote:

Is there an example extension you know that I could look at that does this?
(i am a novice at SQLite)

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Pavel Ivanov [paiva...@gmail.com]
Sent: March 1, 2012 8:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Set Locale for upper() and lower() using a pragma
variable

On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya
  wrote:

You can simply register your
own lower/upper with one argument that looks wherever you want to know
what locale to use.

The part of registering a function to override lower/upper I think I
understand.

But if I wanted to persist the locale, so that even if I disconnect and
reconnect it is still set to the last time I set it, how do I go about
accomplishing this.

Create a special table for that and store your last locale value in
it. Then after reconnect read the locale value from this table.


Pavel
___
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
// DB's default locale
char *DBLocale = 0;
int nLength = 0;

void setLocale(char *zLocale)
{
   sqlite3_free(DBLocale);
   nLength = strlen(zLocale);
   DBLocale = (char *) sqlite3_malloc(nLength);

   strcpy(DBLocale, zLocale);
}

void deleteDBLocale(void * zLocale)
{
   // do thing, since zLocale points to DBLocale.
}

// lower and upper using the DB locale 
static void lowerUpperDefaultLocale(sqlite3_context *p, int nArg, sqlite3_value 
**apArg)
{
   sqlite3_value *args[2];

   if (!DBLocale)
   {
  // hand off to inbuilt functions
  icuCaseFunc16(p, nArg, apArg);

  return;
   }

args[0] = apArg[0];
   args[1] = (sqlite3_value *)malloc(sizeof(sqlite3_value));
   args[1]->db= apArg[0]->db;   /* The associated database

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold

WHAT THE  !

I now delete the malform message and the rollback command from the *.sql file 
and run ".read".
Sqlite shell runs complete and the shell ask me for new command "> " (DB file 
seems to have the right size.
I'm happy now and enter command ".exit" and bam Db file has 0kb?!?!?
What have i done wrong? Any Commit command or something?

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

Re: [sqlite] how to replace connection string at runtime

2012-03-02 Thread Kevin Benson

--
   --
  --
 --ô¿ô--
K e V i N


On Fri, Mar 2, 2012 at 2:47 PM, Agrawal, Manish  wrote:

>  My question is: what is the simplest way to replace the connection string
> in a C# application with the file location at runtime?  I do want the
> convenience of the table objects that have been auto-generated.
>
>  I am trying to specify the location of a sqlite file in my application at
> runtime. Currently, the dataset is created through the designer as:
>
> DemoDataset ds = new DemoDataSet();
>
>  Browsing through the application, I find that the app.config file has the
> connection string to the sqlite file:
>
> 
>connectionString="data
> source=C:\Users\test\Database\Demo.sqlite"
>providerName="System.Data.SQLite" />
>
>
>And the auto-generated DemoDataset.Designer.cs file has a reference
> to the connection string as:
>
> private void InitConnection() {
>this._connection = new
> global::System.Data.SQLite.SQLiteConnection();
>this._connection.ConnectionString =
> global::DemoApplication.Properties.Settings.Default.DemoConnectionString;
>}
>

https://www.google.com/search?num=100=en==c%23+sqlite+app.config+%22how+to+change%22+connection+string
 --
   --
  --
 --ô¿ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ADO.NET provider and sqlite3.exe (command shell) .load command

2012-03-02 Thread Levi Haskell





Hi,

Is it possible to create a managed library with custom SQLite functions (based 
on System.Data.SQLite.SQLiteFunction class) and load it into sqlite3.exe 
command shell (using .load command) for ease of testing and visual data 
examination?

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

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

2012-03-02 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/03/12 10:55, Rose, John B wrote:
> In this particular example, a particular researcher would be creating
> the initial version of the database on their own computer, but once
> they got out in the field they may update the copy they have on their
> mobile device and take that copy back to replace the original on their
> desktop.

Android still remains a red herring.  Forget the mobile device.  How are
you going to cope with all these copies of data, multiple databases,
multiple users, multiple updates and people wanting to share their data?

Sure you could stick your head in the sand and hope for the best but you
will get burned *very* badly.  You will end up with lost data, corrupt
data etc.  You have humans in the loop - they are fallible.

So how would you handle it if the mobile device was actually a regular
Windows laptop?  How would databases get generated, copied and uploaded
without tears?

By far the best thing you can do is use a server to hold the master data,
and be how various devices synchronize.  Devices would then have the
equivalent of two databases.  One is a read-only cache of existing data,
and then second would be a log of local changes (updates, deletions,
additions etc).

Synchronization would then involve telling the server of the log of local
changes which it can integrate into the existing data in the most
applicable way, and it can provide the read-only cache data too.  With
this approach the master copy of the data exists only once, and in only
one place.  All the concurrency issues go away.  And if you use HTTP/REST
then pretty much every programming language and device supports it.

Heck you could even write your client app in HTML5 using offline support
so the user doesn't have to be connected, and local storage until you can
upload the changes.  (Local storage is implemented using SQLite in most
browsers btw!)

http://www.html5rocks.com/en/features/offline

If you did this then it would work on pretty much any mobile device
including future ones.  You can then still write specific apps for
specific platforms if you wanted deeper integration on those.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9RJhUACgkQmOOfHg372QRLrQCeJeEQ+Osth0TssuEfkpHfH8hv
xPoAoOJIKSgy9CL9fLVFxHFKKkz9FWOY
=Aqb8
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to replace connection string at runtime

2012-03-02 Thread Agrawal, Manish

Hello

  I suspect my question may already have been answered many times before, but I 
could not find a way to search through the archives of the sqlite users mailing 
list at: http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/. Is there 
a way to search the archives?

  My question is: what is the simplest way to replace the connection string in 
a C# application with the file location at runtime?  I do want the 
convenience of the table objects that have been auto-generated.

  I am trying to specify the location of a sqlite file in my application at 
runtime. Currently, the dataset is created through the designer as:

DemoDataset ds = new DemoDataSet();

  Browsing through the application, I find that the app.config file has the 
connection string to the sqlite file:





And the auto-generated DemoDataset.Designer.cs file has a reference to 
the connection string as:

private void InitConnection() {
this._connection = new 
global::System.Data.SQLite.SQLiteConnection();
this._connection.ConnectionString = 
global::DemoApplication.Properties.Settings.Default.DemoConnectionString;
}

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

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

2012-03-02 Thread Simon Slavin

On 2 Mar 2012, at 7:40pm, "Rose, John B"  wrote:

>> So once again, how would you answer Roger's question ?  Suppose changes were
>> made on both computers at the same time.  How would you reconcile the two
>> copies of the databases ?  If you want to use the copy from the computer to
>> /update/ the copy in the mobile device then the mobile device needs access to
>> /both/ copies of the database when it's doing the updating, so it can see
>> what's new.  So you really have not one database but two databases
> 
> Changes would not be made on both at the same time. Initially you upload
> your base DB on the mobile device, gather samples somewhere in field and
> occasionally change/add/delete data. Come back to the lab and download the
> latest version of the database. Then the lab staff uses the new updated
> version until you go out again and repeat that sequence.

Okay.  In that case, if the data is stored in a SQLite database, then yes, the 
data can all be stored in one SQLite database file and all you need to know how 
to copy that file onto and off of your Android device.  This doesn't need to 
have anything to do with how your sampling Android app accesses the file once 
it is on the device.

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

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

2012-03-02 Thread Rose, John B

> So once again, how would you answer Roger's question ?  Suppose changes were
> made on both computers at the same time.  How would you reconcile the two
> copies of the databases ?  If you want to use the copy from the computer to
> /update/ the copy in the mobile device then the mobile device needs access to
> /both/ copies of the database when it's doing the updating, so it can see
> what's new.  So you really have not one database but two databases

Changes would not be made on both at the same time. Initially you upload
your base DB on the mobile device, gather samples somewhere in field and
occasionally change/add/delete data. Come back to the lab and download the
latest version of the database. Then the lab staff uses the new updated
version until you go out again and repeat that sequence.

John

On 3/2/12 2:17 PM, "Simon Slavin"  wrote:

> 
> On 2 Mar 2012, at 6:55pm, "Rose, John B"  wrote:
> 
>> Thanks for the very thorough reply.
>> 
>>> Android is a red herring in this and your approach is not a good one.  How
>>> would you do this using two different regular computers?  How would you
>>> deal with changes being made on both machines at the same time?  How would
>>> you copy databases ensuring you picked up the journals
>> 
>> In this particular example, a particular researcher would be creating the
>> initial version of the database on their own computer, but once they got out
>> in the field they may update the copy they have on their mobile device and
>> take that copy back to replace the original on their desktop.
> 
> So once again, how would you answer Roger's question ?  Suppose changes were
> made on both computers at the same time.  How would you reconcile the two
> copies of the databases ?  If you want to use the copy from the computer to
> /update/ the copy in the mobile device then the mobile device needs access to
> /both/ copies of the database when it's doing the updating, so it can see
> what's new.  So you really have not one database but two databases.
> 
> 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] Sqlite3 command shell dump possible bug

2012-03-02 Thread Simon Slavin

On 2 Mar 2012, at 7:08pm, Steffen Mangold  wrote:

> Ok maybe i found it in the sql file is written (file end):
> 
> [...]
> INSERT INTO "InverterData" VALUES(2478,'2012-02-28 
> 15:00:00',1435.73,429173.78,170.28,170.75,169.38,397.56,397.38,396.69,NULL,210976,31,NULL,NULL,1,304,NULL,NULL,NULL,694,NULL,NULL,NULL);
> / ERROR: (11) database disk image is malformed */
> / ERROR: (11) database disk image is malformed */
> CREATE TRIGGER SensorData_InsertUpdate
> [...]
> ROLLBACK; -- due to errors
> 
> So sqlite shell can not understand "/ ERROR: (11) database disk image is 
> malformed */" I think.
> and make a rollback?
> 
> Do you think that's it?

Yes, that's probably what's causing the errors.  If you remove those lines (or 
perhaps just the ROLLBACK) from the text file then you might get a successful 
'.read' session.

But what it means is that the original database ... the one you did a '.dump' 
of, was so corrupt that the shell tool couldn't dump it to a text file 
successfully.  So your .sql file may not be any use since it is missing at 
least some of the data, perhaps an entire table or some other structure.  If 
you need absolutely complete data from it you may need expert help in trying to 
rescue data from the original database file.  On the other hand if you 
understand the data structure yourself you might be able to patch it up to a 
state good enough to let you continue.

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

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

2012-03-02 Thread Simon Slavin

On 2 Mar 2012, at 6:55pm, "Rose, John B"  wrote:

> Thanks for the very thorough reply.
> 
>> Android is a red herring in this and your approach is not a good one.  How
>> would you do this using two different regular computers?  How would you
>> deal with changes being made on both machines at the same time?  How would
>> you copy databases ensuring you picked up the journals
> 
> In this particular example, a particular researcher would be creating the
> initial version of the database on their own computer, but once they got out
> in the field they may update the copy they have on their mobile device and
> take that copy back to replace the original on their desktop.

So once again, how would you answer Roger's question ?  Suppose changes were 
made on both computers at the same time.  How would you reconcile the two 
copies of the databases ?  If you want to use the copy from the computer to 
/update/ the copy in the mobile device then the mobile device needs access to 
/both/ copies of the database when it's doing the updating, so it can see 
what's new.  So you really have not one database but two databases.

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold

Ok maybe i found it in the sql file is written (file end):

[...]
INSERT INTO "InverterData" VALUES(2478,'2012-02-28 
15:00:00',1435.73,429173.78,170.28,170.75,169.38,397.56,397.38,396.69,NULL,210976,31,NULL,NULL,1,304,NULL,NULL,NULL,694,NULL,NULL,NULL);
/ ERROR: (11) database disk image is malformed */
/ ERROR: (11) database disk image is malformed */
CREATE TRIGGER SensorData_InsertUpdate
[...]
ROLLBACK; -- due to errors

So sqlite shell can not understand "/ ERROR: (11) database disk image is 
malformed */" I think.
and make a rollback?

Do you think that's it?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

2012-03-02 Thread Rose, John B


Thanks for the very thorough reply.

> Android is a red herring in this and your approach is not a good one.  How
> would you do this using two different regular computers?  How would you
> deal with changes being made on both machines at the same time?  How would
> you copy databases ensuring you picked up the journals?
> 

In this particular example, a particular researcher would be creating the
initial version of the database on their own computer, but once they got out
in the field they may update the copy they have on their mobile device and
take that copy back to replace the original on their desktop.

Thanks


On 3/2/12 12:52 PM, "Roger Binns"  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> [I reordered your message in my response]
> 
> On 02/03/12 08:59, Rose, John B wrote:
>> I had assumed we just moved the .db file back and forth between our
>> desktop and Android and the simplicity is part of the "coolness" of
>> SQLite.
> 
> Yes, Android includes a version of the SQLite library as standard and you
> can move the files between platforms just fine.  The only minor thing to
> watch out for is that newer Android versions have WAL turned on by default
> while SQLite versions older than 3.7.0 (released mid-2010) cannot access
> WAL databases.
> 
> The major thing to watch out for is that Android (like all mobile
> platforms) can and does terminate processes abruptly and routinely.
> Consequently there is a far higher probability that there will be a WAL or
> journal file lying around.  If you are copying databases around then your
> must also copy the journal/WAL file too.  See 1.3 of
> 
>   http://www.sqlite.org/howtocorrupt.html
> 
>> Someone else in our group came across something called
>> "ContentProvider"
> 
> If that person posted to this group earlier then the response came from me
> and a lot has been lost in translation.  Assuming not.
> 
> Android uses Java as the normal programming language so you will use a
> different API than the normal SQLite C API.  (There is a native
> development kit and you can use the C api plus cross compilers, but that
> approach is not normal or recommended.)
> 
> This is the normal Java API on Android for SQLite.  It is also rarely used:
> 
> 
> http://developer.android.com/reference/android/database/sqlite/package-summary
> .html
> 
>> Frankly we are not understanding it.
> 
> It is to do with how Android applications are structured.  The idea is
> that applications are broken into components including Activities (a full
> screen UI), Services (background work), Content Providers (data access)
> and Broadcast Receivers (cross-process event dispatch).
> 
> These components are composed into the user experience and the components
> used can come from multiple different applications.  It is trivial to add
> Google Maps (or Bing Maps for that matter) into your app as though it was
> part of the app, or a barcode reader, or contacts access, or nyan cat
> icons, or battery drain notification.
> 
>  http://developer.android.com/guide/topics/fundamentals.html
> 
> ContentProvider is a way for some code to export and allow manipulation of
> data and it decouples the consumer of that data from how it is actually
> stored under the hood.  The consumer can be a component within the same
> application or at your option other apps on the system.
> 
> If you are writing a ContentProvider and do want to store the data in a
> SQLite database then you'll find a lot of helper classes on Android to do
> most of the work for you.  You'll also note that some aspects of the
> ContentProvider API have a SQLite flavour (eg the query interface).
> 
>> A step-by-step example taking a .db file created with some data on a
>> desktop and putting it on Android and using/adding data to it there,
>> then putting it back on the desktop with the new data while it was on
>> Android would be good to see.
> 
> Android is a red herring in this and your approach is not a good one.  How
> would you do this using two different regular computers?  How would you
> deal with changes being made on both machines at the same time?  How would
> you copy databases ensuring you picked up the journals?
> 
> The reality is that Android devices have intermittent connectivity and you
> should allow the user to work with data even when the connectivity is not
> present.  Your best practises are as follows:
> 
> - - Make the data available to the app or other apps using a ContentProvider
> 
> - - Your ContentProvider can store the data behind the scenes in a SQLite
> database
> 
> - - You need to provide a way of synchronizing data (pushing and pulling
> changes) which requires a networked server front ending the master copy of
> the data
> 
> The best way of doing this is to expose your data from a server using a
> REST API.  This will then allow Android apps, web apps and anything else
> to work with the data.
> 
>

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold

>
> Is it very long ?  Can you read it with a dump utility or a text editor 
> (don't try it with a word processor) and see the SQL commands in it ?
>

Yes 14 GB. 4 Table, roundabout 200.000.000 inserts.

I opened it with a textviewer for large files. Sql seams well formed and 
readable till the end.

I now make a complet integrity_check.
Only four error types:

On tree page xxx cell xx: invalid page number
On tree page xxx cell xx: child page depth differs
On page xxx at right child: child page depth differs
On page xxx at right child: invalid page number xxx

But this error round about 100 times.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Simon Slavin

On 2 Mar 2012, at 6:30pm, Steffen Mangold  wrote:

>> Are you saying it creates a database file but doesn't put anything into it 
>> (zero filesize) or that it doesn't even create a blank file ?
> 
> With dump its write the complete DB File new but nearly at the end (new DD 
> file size compared to the malformed) Sqlite shell breaks and set the file 
> size of the new DB to 0kb.
> No error is written. :(

Okay.  Take that long text file (db.sql ?) and use a text editor or some other 
tool to split it into two.  Perhaps you can find a break point where it stops 
creating one table and starts creating another one.  While you're doing that, 
you can look at it by eye and see if the CREATE TABLE commands look right to 
you.

Then you can '.read' the first half, see if that worked, then '.read' the 
second half separately.  Perhaps one half will work and not the other.

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

Re: [sqlite] WinRT (WAS: Status)

2012-03-02 Thread Joe Mistachkin


Steven Nesbit wrote:
>
> What is the status of this effort?  We actually need to have the
> platform determined at runtime since we need to run on WinRT,
> Android and iOS.
>

I'm not really following you here...  Those are completely different
platforms.

--
Joe Mistachkin

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold

>
> Are you saying it creates a database file but doesn't put anything into it 
> (zero filesize) or that it doesn't even create a blank file ?
>
With dump its write the complete DB File new but nearly at the end (new DD file 
size compared to the malformed) Sqlite shell breaks and set the file size of 
the new DB to 0kb.
No error is written. :(
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Simon Slavin

On 2 Mar 2012, at 6:16pm, Steffen Mangold  wrote:

>> There's no magic tool for repairing damaged database files.  But by using 
>> the .dump command (if necessary on each individual table and view) then 
>> creating a new database file and using the .read command you can often 
>> rescue some or all of the data in the original > > database.
>> 
> 
> Ok, with .dumb i now created a "db.sql" file successfully. 

Is it very long ?  Can you read it with a dump utility or a text editor (don't 
try it with a word processor) and see the SQL commands in it ?

> but I don't get the read command!? How create a new DB file with that command?
> With "sqlite> .read db.sql" it does much reading but no file is created.

Start the shell tool supplying it with the name for a new blank database file 
(I think you were using fred.temp in your earlier example).  Then do a '.read 
db.sql'.

prompt$ sqlite3 db.temp
SQLite version 3.7.7 2011-10-10 22:11:44
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read db.sql

Then do something like a '.schema' and see if it has created the tables you 
expected.
Then quit the shell tool using '.quit' and see if the file you created exists.

Are you saying it creates a database file but doesn't put anything into it 
(zero filesize) or that it doesn't even create a blank file ?

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold

>
> Ok, with .dumb i now created a "db.sql" file successfully. 
> but I don't get the read command!? How create a new DB file with that command?
> With "sqlite> .read db.sql" it does much reading but no file is created.
>

Ok I get it, must attach a DB first.
now sqlite writes the data to the DB, hopes this helps.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold

>
> There's no magic tool for repairing damaged database files.  But by using the 
> .dump command (if necessary on each individual table and view) then creating 
> a new database file and using the .read command you can often rescue some or 
> all of the data in the original > > database.
>

Ok, with .dumb i now created a "db.sql" file successfully. 
but I don't get the read command!? How create a new DB file with that command?
With "sqlite> .read db.sql" it does much reading but no file is created.

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

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

2012-03-02 Thread Simon Slavin


On 2 Mar 2012, at 5:58pm, Roger Binns  wrote:

> On 02/03/12 09:40, Simon Slavin wrote:
>> What ContentProvider is, as far as I can work out, is a way of
>> accessing a SQLite database file in a form convenient for Android
>> apps.
> 
> No, it is a way for Android app components to expose and manipulate data
> with those components being in the same or different apps.  How the data
> is actually stored (in a database, in SQLite, on a server, rot13 encoded
> steganographically in gif files) is immaterial.
> 
> All Android apps are made up of multiple components, and the APIs for
> components decouples them as much as possible so they can be mixed and
> matched with an app or across apps.  ContentProvider is one of those
> decoupling APIs for data.
> 
>  http://developer.android.com/guide/topics/fundamentals.html

I see.  And I read your previous post too (it just appeared after I'd posted).  
Okay so even if you were using ContentProvider you'd still be using another 
thing to read the SQLite database.  No problem.

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

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

2012-03-02 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/03/12 09:40, Simon Slavin wrote:
> What ContentProvider is, as far as I can work out, is a way of
> accessing a SQLite database file in a form convenient for Android
> apps.

No, it is a way for Android app components to expose and manipulate data
with those components being in the same or different apps.  How the data
is actually stored (in a database, in SQLite, on a server, rot13 encoded
steganographically in gif files) is immaterial.

All Android apps are made up of multiple components, and the APIs for
components decouples them as much as possible so they can be mixed and
matched with an app or across apps.  ContentProvider is one of those
decoupling APIs for data.

  http://developer.android.com/guide/topics/fundamentals.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9RCioACgkQmOOfHg372QRxNQCffdDEx7EyGHt6Cqjl1B7hqmwr
hyAAoLQ3w0Qjj03c/tqfaLYQ/aV1KYan
=OZT/
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

2012-03-02 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

[I reordered your message in my response]

On 02/03/12 08:59, Rose, John B wrote:
> I had assumed we just moved the .db file back and forth between our
> desktop and Android and the simplicity is part of the "coolness" of
> SQLite.

Yes, Android includes a version of the SQLite library as standard and you
can move the files between platforms just fine.  The only minor thing to
watch out for is that newer Android versions have WAL turned on by default
while SQLite versions older than 3.7.0 (released mid-2010) cannot access
WAL databases.

The major thing to watch out for is that Android (like all mobile
platforms) can and does terminate processes abruptly and routinely.
Consequently there is a far higher probability that there will be a WAL or
journal file lying around.  If you are copying databases around then your
must also copy the journal/WAL file too.  See 1.3 of

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

> Someone else in our group came across something called
> "ContentProvider"

If that person posted to this group earlier then the response came from me
and a lot has been lost in translation.  Assuming not.

Android uses Java as the normal programming language so you will use a
different API than the normal SQLite C API.  (There is a native
development kit and you can use the C api plus cross compilers, but that
approach is not normal or recommended.)

This is the normal Java API on Android for SQLite.  It is also rarely used:

http://developer.android.com/reference/android/database/sqlite/package-summary.html

> Frankly we are not understanding it.

It is to do with how Android applications are structured.  The idea is
that applications are broken into components including Activities (a full
screen UI), Services (background work), Content Providers (data access)
and Broadcast Receivers (cross-process event dispatch).

These components are composed into the user experience and the components
used can come from multiple different applications.  It is trivial to add
Google Maps (or Bing Maps for that matter) into your app as though it was
part of the app, or a barcode reader, or contacts access, or nyan cat
icons, or battery drain notification.

 http://developer.android.com/guide/topics/fundamentals.html

ContentProvider is a way for some code to export and allow manipulation of
data and it decouples the consumer of that data from how it is actually
stored under the hood.  The consumer can be a component within the same
application or at your option other apps on the system.

If you are writing a ContentProvider and do want to store the data in a
SQLite database then you'll find a lot of helper classes on Android to do
most of the work for you.  You'll also note that some aspects of the
ContentProvider API have a SQLite flavour (eg the query interface).

> A step-by-step example taking a .db file created with some data on a
> desktop and putting it on Android and using/adding data to it there,
> then putting it back on the desktop with the new data while it was on
> Android would be good to see.

Android is a red herring in this and your approach is not a good one.  How
would you do this using two different regular computers?  How would you
deal with changes being made on both machines at the same time?  How would
you copy databases ensuring you picked up the journals?

The reality is that Android devices have intermittent connectivity and you
should allow the user to work with data even when the connectivity is not
present.  Your best practises are as follows:

- - Make the data available to the app or other apps using a ContentProvider

- - Your ContentProvider can store the data behind the scenes in a SQLite
database

- - You need to provide a way of synchronizing data (pushing and pulling
changes) which requires a networked server front ending the master copy of
the data

The best way of doing this is to expose your data from a server using a
REST API.  This will then allow Android apps, web apps and anything else
to work with the data.

http://en.wikipedia.org/wiki/Representational_state_transfer

Here is a deep tech presentation about best practises for Android REST
applications.  Note that a lot of the complexity is because apps can be
killed at any point, the user interface comes and goes, networks are flaky
and have unpredictable latency etc:

http://www.youtube.com/watch?v=xHXn3Kg2IQE

You don't have to use SyncAdaptors, but if you do here is some more doc
and example code:

http://developer.android.com/resources/samples/SampleSyncAdapter/index.html

If you want to ignore all that then you can use adb push/adb pull from the
Android dev kit to transfer files between a computer and an Android
device.  Make sure you also transfer the journals.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9RCOYACgkQmOOfHg372QT/igCgvjniTOllolwtiwFfXXOtUc+a
ovkAoIOfIRVt36PM3ms0Y2CKljVoghv/
=abD0
-END PGP SIGNATURE-

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

2012-03-02 Thread Simon Slavin

On 2 Mar 2012, at 4:59pm, "Rose, John B"  wrote:

> Someone else in our group came across something called "ContentProvider" and
> is under the impression we have to do some file conversion of the .db file
> use ContenProvider somehow if we want to use the original .db file created
> on our desktop. Frankly we are not understanding it.
> 
> I had assumed we just moved the .db file back and forth between our desktop
> and Android and the simplicity is part of the "coolness" of SQLite.

That is the way things should work.  The SQLite file format is specially 
designed to be the same on every platform.  If anything requires a database 
file to be converted into another format I don't think Doctor Hipp would accept 
it as genuine SQLite.

What ContentProvider is, as far as I can work out, is a way of accessing a 
SQLite database file in a form convenient for Android apps.  An alternative to 
using the SQLite API as downloadable from the SQLite site.  But I'm not an 
Android programmer so I don't know.

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

Re: [sqlite] Sqlite Bug Report!

2012-03-02 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

> When I compile sqlite3 in VC++6.0, it does not work, but in VS2010 it
> can work.

http://www.beiww.com/doc/oss/smart-questions.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9Q/kIACgkQmOOfHg372QTAsQCgz126gE6l6fJPFolDA7CROFhi
EhQAnidLD7eg4Zgh9Iyfo9iFJnKGJY4W
=IICY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

2012-03-02 Thread Rose, John B

Someone else in our group came across something called "ContentProvider" and
is under the impression we have to do some file conversion of the .db file
use ContenProvider somehow if we want to use the original .db file created
on our desktop. Frankly we are not understanding it.

I had assumed we just moved the .db file back and forth between our desktop
and Android and the simplicity is part of the "coolness" of SQLite.

A step-by-step example taking a .db file created with some data on a desktop
and putting it on Android and using/adding data to it there, then putting it
back on the desktop with the new data while it was on Android would be good
to see.

Thanks
John

On 3/2/12 11:38 AM, "Pavel Ivanov"  wrote:

>> Is there an example(s?) of a step-by-step for moving/using an SQLite .db file
>> interchangeably between a desktop and an Android mobile device?
> 
> What kind of example you want? SQLite's database format is the same
> for any platform. So just copy the file (when it's not open by any
> application) and you are done.
> 
> 
> Pavel
> 
> 
> On Fri, Mar 2, 2012 at 11:34 AM, Rose, John B  wrote:
>> Hello
>> 
>> We are new to SQLite and are working on an intro tutorial for people here.
>> 
>> We would like to create an example where we create an SQLite database on our
>> desktop and access it there via command line, GUI like Navicon, and a web
>> based application, then upload the .db file to our Android mobile device and
>> users can use an database there via an interface we create. Ideally they
>> could add or edit values in the database
>> On the Android device.
>> 
>> Is there an example(s?) of a step-by-step for moving/using an SQLite .db file
>> interchangeably between a desktop and an Android mobile device?
>> 
>> Thanks
>> John
>> ___
>> 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] Views and Performance

2012-03-02 Thread Igor Tandetnik


On 3/2/2012 11:38 AM, Duquette, William H (318K) wrote:

On 3/2/12 8:29 AM, "Igor Tandetnik"  wrote:

On 3/2/2012 11:29 AM, Pavel Ivanov wrote:

If I am querying data just from t1, is there a performance penalty
for using myview in the query?  Or will the query planner generate
approximately the same bytecode as it would if I'd simply queried
t1?


Yes, there is performance penalty and no it can't generate the same
bytecode. If you ask why the answer is because result set from the
view can be different than from the table alone - several rows in the
view can contain information from the same row of t1.


... while some other rows from t1 may not appear at all.


I was assuming that there's a strict many-to-one relationship between t1
and t2. (Should have said.)


You may know there is, but SQLite doesn't, and has to plan for the worst 
case.

--
Igor Tandetnik

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

Re: [sqlite] Views and Performance

2012-03-02 Thread Duquette, William H (318K)

On 3/2/12 8:31 AM, "Simon Davies"  wrote:


>On 2 March 2012 16:23, Duquette, William H (318K)
> wrote:
>> Howdy!
>>
>> Suppose I have two related tables, t1 and t2, and I write a view like
>>this:
>>
>>CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column);
>>
>> If I am querying data just from t1, is there a performance penalty for
>>using myview in the query?  Or will the query planner generate
>>approximately the same bytecode as it would if I'd simply queried t1?
>>
>> --
>> Will Duquette -- william.h.duque...@jpl.nasa.gov
>
>SQLite version 3.6.11
>Enter ".help" for instructions
>sqlite> create table t1( id integer primary key, data text );
>sqlite> create table t2( id integer primary key, data text );
>sqlite>
>sqlite> create view v1 as select t1.id as id, t1.data as d1, t2.data
>as d2 from t1 join t2 on t1.id=t2.id;
>sqlite>
>sqlite>
>sqlite> explain query plan select data from t1 where id>10;
>0|0|TABLE t1 USING PRIMARY KEY
>sqlite> explain query plan select d1 from v1 where id>10;
>0|0|TABLE t1 USING PRIMARY KEY
>1|1|TABLE t2 USING PRIMARY KEY
>sqlite>
>
>It seems not for v 3.6.11

OK, so it's going to look up the id in both tables whether it needs to or
not...because, given that it's an inner join, you don't get the record
from t1 unless there's a matching record in t2.  Got it!

Thanks!




>
>Regards,
>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] Views and Performance

2012-03-02 Thread Duquette, William H (318K)

On 3/2/12 8:29 AM, "Igor Tandetnik"  wrote:


>On 3/2/2012 11:29 AM, Pavel Ivanov wrote:
>>> If I am querying data just from t1, is there a performance penalty
>>> for using myview in the query?  Or will the query planner generate
>>> approximately the same bytecode as it would if I'd simply queried
>>> t1?
>>
>> Yes, there is performance penalty and no it can't generate the same
>> bytecode. If you ask why the answer is because result set from the
>> view can be different than from the table alone - several rows in the
>> view can contain information from the same row of t1.
>
>... while some other rows from t1 may not appear at all.

I was assuming that there's a strict many-to-one relationship between t1
and t2. (Should have said.)


>-- 
>Igor Tandetnik
>
>___
>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] Interchangeably using SQLite .db file between desktop and android mobile device

2012-03-02 Thread Pavel Ivanov

> Is there an example(s?) of a step-by-step for moving/using an SQLite .db file 
> interchangeably between a desktop and an Android mobile device?

What kind of example you want? SQLite's database format is the same
for any platform. So just copy the file (when it's not open by any
application) and you are done.


Pavel


On Fri, Mar 2, 2012 at 11:34 AM, Rose, John B  wrote:
> Hello
>
> We are new to SQLite and are working on an intro tutorial for people here.
>
> We would like to create an example where we create an SQLite database on our 
> desktop and access it there via command line, GUI like Navicon, and a web 
> based application, then upload the .db file to our Android mobile device and 
> users can use an database there via an interface we create. Ideally they 
> could add or edit values in the database
> On the Android device.
>
> Is there an example(s?) of a step-by-step for moving/using an SQLite .db file 
> interchangeably between a desktop and an Android mobile device?
>
> Thanks
> John
> ___
> 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] Views and Performance

2012-03-02 Thread Pavel Ivanov

> What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not 
> specified?

INNER is default.


Pavel


On Fri, Mar 2, 2012 at 11:37 AM, Rob Richardson
 wrote:
> What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not 
> specified?
>
> RobR
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Duquette, William H 
> (318K)
> Sent: Friday, March 02, 2012 11:23 AM
> To: Discussion of SQLite Database
> Subject: [sqlite] Views and Performance
>
> Howdy!
>
> Suppose I have two related tables, t1 and t2, and I write a view like this:
>
>    CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column);
>
> If I am querying data just from t1, is there a performance penalty for using 
> myview in the query?  Or will the query planner generate approximately the 
> same bytecode as it would if I'd simply queried t1?
> ___
> 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] Interchangeably using SQLite .db file between desktop and android mobile device

2012-03-02 Thread Rose, John B

Hello

We are new to SQLite and are working on an intro tutorial for people here.

We would like to create an example where we create an SQLite database on our 
desktop and access it there via command line, GUI like Navicon, and a web based 
application, then upload the .db file to our Android mobile device and users 
can use an database there via an interface we create. Ideally they could add or 
edit values in the database
On the Android device.

Is there an example(s?) of a step-by-step for moving/using an SQLite .db file 
interchangeably between a desktop and an Android mobile device?

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

Re: [sqlite] Views and Performance

2012-03-02 Thread Rob Richardson

What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not specified? 

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Duquette, William H (318K)
Sent: Friday, March 02, 2012 11:23 AM
To: Discussion of SQLite Database
Subject: [sqlite] Views and Performance

Howdy!

Suppose I have two related tables, t1 and t2, and I write a view like this:

CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column);

If I am querying data just from t1, is there a performance penalty for using 
myview in the query?  Or will the query planner generate approximately the same 
bytecode as it would if I'd simply queried t1?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views and Performance

2012-03-02 Thread Igor Tandetnik


On 3/2/2012 11:29 AM, Pavel Ivanov wrote:

If I am querying data just from t1, is there a performance penalty
for using myview in the query?  Or will the query planner generate
approximately the same bytecode as it would if I'd simply queried
t1?


Yes, there is performance penalty and no it can't generate the same
bytecode. If you ask why the answer is because result set from the
view can be different than from the table alone - several rows in the
view can contain information from the same row of t1.


... while some other rows from t1 may not appear at all.
--
Igor Tandetnik

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

Re: [sqlite] Views and Performance

2012-03-02 Thread Simon Davies

On 2 March 2012 16:23, Duquette, William H (318K)
 wrote:
> Howdy!
>
> Suppose I have two related tables, t1 and t2, and I write a view like this:
>
>    CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column);
>
> If I am querying data just from t1, is there a performance penalty for using 
> myview in the query?  Or will the query planner generate approximately the 
> same bytecode as it would if I'd simply queried t1?
>
> --
> Will Duquette -- william.h.duque...@jpl.nasa.gov

SQLite version 3.6.11
Enter ".help" for instructions
sqlite> create table t1( id integer primary key, data text );
sqlite> create table t2( id integer primary key, data text );
sqlite>
sqlite> create view v1 as select t1.id as id, t1.data as d1, t2.data
as d2 from t1 join t2 on t1.id=t2.id;
sqlite>
sqlite>
sqlite> explain query plan select data from t1 where id>10;
0|0|TABLE t1 USING PRIMARY KEY
sqlite> explain query plan select d1 from v1 where id>10;
0|0|TABLE t1 USING PRIMARY KEY
1|1|TABLE t2 USING PRIMARY KEY
sqlite>

It seems not for v 3.6.11

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

Re: [sqlite] Views and Performance

2012-03-02 Thread Pavel Ivanov

> If I am querying data just from t1, is there a performance penalty for using 
> myview in the query?  Or will the query planner generate approximately the 
> same bytecode as it would if I'd simply queried t1?

Yes, there is performance penalty and no it can't generate the same
bytecode. If you ask why the answer is because result set from the
view can be different than from the table alone - several rows in the
view can contain information from the same row of t1.

Pavel


On Fri, Mar 2, 2012 at 11:23 AM, Duquette, William H (318K)
 wrote:
> Howdy!
>
> Suppose I have two related tables, t1 and t2, and I write a view like this:
>
>    CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column);
>
> If I am querying data just from t1, is there a performance penalty for using 
> myview in the query?  Or will the query planner generate approximately the 
> same bytecode as it would if I'd simply queried t1?
>
>
> --
> Will Duquette -- william.h.duque...@jpl.nasa.gov
> Athena Development Lead -- Jet Propulsion Laboratory
> "It's amazing what you can do with the right tools."
>
> ___
> 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] Views and Performance

2012-03-02 Thread Duquette, William H (318K)

Howdy!

Suppose I have two related tables, t1 and t2, and I write a view like this:

CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column);

If I am querying data just from t1, is there a performance penalty for using 
myview in the query?  Or will the query planner generate approximately the same 
bytecode as it would if I'd simply queried t1?


--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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

Re: [sqlite] Create Temp Table from Query

2012-03-02 Thread Marc L. Allen

CREATE TEMPORARY TABLE XYZ AS
SELECT ...

Is that what you're looking for?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Joe Bennett
> Sent: Friday, March 02, 2012 10:44 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Create Temp Table from Query
> 
> Hi,
> 
> I am looking for some info on how to take an sqlite query result and
> move that into a temp table. My hope is to focus the subsequent queries
> down to a smaller dataset... I've been searching Google a bit but have
> not been able to find what I am loking for... I'm not sure if that
> means this is not the optimum way to acheve what I'm looking for...??
> 
> 
> 
> 
> -Joe
> ___
> 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] Create Temp Table from Query

2012-03-02 Thread Joe Bennett

Hi,

I am looking for some info on how to take an sqlite query result and move
that into a temp table. My hope is to focus the subsequent queries down to
a smaller dataset... I've been searching Google a bit but have not been
able to find what I am loking for... I'm not sure if that means this is not
the optimum way to acheve what I'm looking for...??




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

Re: [sqlite] Status

2012-03-02 Thread Steven Nesbit

Sorry about that, WinRT Sqlite 

Steve


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

Re: [sqlite] Status

2012-03-02 Thread Marc L. Allen

Sorry... What effort?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Steven Nesbit
> Sent: Friday, March 02, 2012 10:35 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Status
> 
> What is the status of this effort?  We actually need to have the
> platform determined at runtime since we need to run on WinRT, Android
> and iOS.
> 
> 
> 
> Steve
> 
> ___
> 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] Status

2012-03-02 Thread Steven Nesbit

What is the status of this effort?  We actually need to have the platform 
determined at runtime since we need to run on WinRT, Android and iOS.



Steve

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Simon Slavin

On 2 Mar 2012, at 2:36pm, Steffen Mangold  wrote:

> I read in some forums that .dumb is the best way to repair "malformed" DBs. 
> Do you have an other way?

It doesn't repair anything.  And the .dump command may, or may not, work on a 
malformed database file.  It depends on what'sw wrong with the database file.  
Might help if you understood what .dump does.

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

It reads a SQLite database and produces the SQL instructions it would take to 
reproduce that database.  So you start off with a database file in SQLite 
format and end up with a huge file of text containing SQL instructions.  Since 
a human can understand that file you can then read that text file yourself, or 
use a text editor to make changes to it before using the .read command to 
execute the instructions and make another database from them.

There's no magic tool for repairing damaged database files.  But by using the 
.dump command (if necessary on each individual table and view) then creating a 
new database file and using the .read command you can often rescue some or all 
of the data in the original database.

One thing you might like to try is "PRAGMA integrity_check;"

which will give you a good idea about what's wrong with the original database 
file.  It might tell you which table is defective, or whether the problem is 
with data (which may lead to problems reconstructing the file) or an index 
(which will probably be ignored when reconstructing the file).

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

Re: [sqlite] TEXT PRIMARY KEY

2012-03-02 Thread Jay A. Kreibich

On Fri, Mar 02, 2012 at 02:21:19PM +0100, Benoit Mortgat scratched on the wall:
> On Fri, Mar 2, 2012 at 13:59, Jay A. Kreibich  wrote:
> > On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies
> > scratched on the wall:
> >
> > ??Kind of. ??It implies uniqueness in the SQL sense, which does not
> > ??include NULLs (remember, NULL != NULL).
> 
> Actually, NULL != NULL is unknown.

  As an expression, yes (if you consider NULL to be "unknown").

  As a general statement, not really.

  Setting aside the theoretical argument of what, exactly, NULL means
  (e.g. "unknown" or something else), it is true that the SQL expression
  "NULL != NULL" will evaluate to "NULL", not "true."

  However, I was only trying to make a general statement that "one
  NULL is not equal to another," in the sense that "NULL == NULL" will
  not evaluate to "true"... which means it will not trip a UNIQUE
  constraint.  This is why the PK constraint normally implies both
  UNIQUE and NOT NULL.  Not only do NULLs not make sense in a PK as a
  fundamental identifier, allowing NULLs also breaks the concept that a
  PK should have a *known* unique (i.e. no NULLs allowed in comparisons)
  value for each row.

  SQLite does not imply NOT NULL when you specify a PK constraint.
  This is in contradiction to the SQL standard, but has been wrong so
  long nobody wants to risk changing it.  Hence, if you want your
  SQLite PKs to have known unique values, you must specify not only PK,
  but explicitly specify NOT NULL.

   -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] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold

>
> First, get all the other databases done, so you're worried only about the one 
> which doesn't work.
>
> Then do the .dump part for that database, putting the output into a file on 
> disk, which should leave you with a huge file of SQL commands which should 
> rebuild it.
>
> It's likely that the .dump stage will fail because your original database is 
> corrupt.  That's your problem.
>
> If it doesn't fail, split it up into parts, and rebuild your database using 
> '.read' by reading the parts in one at a time.  One of those parts should 
> cause an error message or a crash.  That's your problem.
>
> Either way, you should be able to narrow down the possible scope for the 
> crash.

Thank you simon i try this,

I read in some forums that .dumb is the best way to repair "malformed" DBs. Do 
you have an other way?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Simon Slavin

On 2 Mar 2012, at 1:20pm, Steffen Mangold  wrote:

> i have a problem with the sqlite2.exe under windows.
> Ok, I have here 20 corrupted DBs and want to repair they all.
> I do this with CMD and the command ".dump | sqlite3 rebuild.db3 | sqlite3 
> rebuild.temp"
> 
> This works perfect for all DBs except one.
> The DB where it is not working has a size of 15GB.
> During processing I can see the "rebuild.temp" is going bigger and bigger.
> But at the end the hole file is set empty!! It has a size of 0kb after 
> sqlite3.exe is finished?!?!

First, get all the other databases done, so you're worried only about the one 
which doesn't work.

Then do the .dump part for that database, putting the output into a file on 
disk, which should leave you with a huge file of SQL commands which should 
rebuild it.

It's likely that the .dump stage will fail because your original database is 
corrupt.  That's your problem.

If it doesn't fail, split it up into parts, and rebuild your database using 
'.read' by reading the parts in one at a time.  One of those parts should cause 
an error message or a crash.  That's your problem.

Either way, you should be able to narrow down the possible scope for the crash.

Simon.

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

[sqlite] Sqlite Bug Report!

2012-03-02 Thread liaoyan

Hello.
(I am Chinese, not good at English. ^_^)
When I compile sqlite3 in VC++6.0, it does not work, but in VS2010 it can work.
It report a runtime errro.
My code as following.

#include 
#include 
#include 
#include 
#include "sqlite3.h"
#include 
#include 

int main() {
char db[] = "db.db";
sqlite3 * p_db = NULL;

printf("pre open\n");
sqlite3_open(db, _db);
printf("aft open\n");
   
return 0;
}

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

Re: [sqlite] TEXT PRIMARY KEY

2012-03-02 Thread Benoit Mortgat

On Fri, Mar 2, 2012 at 13:59, Jay A. Kreibich  wrote:
> On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies
> scratched on the wall:
>
>  Kind of.  It implies uniqueness in the SQL sense, which does not
>  include NULLs (remember, NULL != NULL).

Actually, NULL != NULL is unknown. Any expression compared to NULL
with any of the operators == != < > <= >= LIKE GLOB will have unknown
result.

 C:\> sqlite3
 SQLite version 3.7.10 2012-01-16 13:28:40
 Enter ".help" for instructions
 Enter SQL statements terminated with a ";"
 sqlite> SELECT CASE WHEN NULL = NULL  THEN 0
...> WHEN NULL <> NULL THEN 1
...> ELSE 2
...>END;
 2
 sqlite> .q



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

[sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold

Hi guys,

i have a problem with the sqlite2.exe under windows.
Ok, I have here 20 corrupted DBs and want to repair they all.
I do this with CMD and the command ".dump | sqlite3 rebuild.db3 | sqlite3 
rebuild.temp"

This works perfect for all DBs except one.
The DB where it is not working has a size of 15GB.
During processing I can see the "rebuild.temp" is going bigger and bigger.
But at the end the hole file is set empty!! It has a size of 0kb after 
sqlite3.exe is finished?!?!

All DBs are same format, not compressed, no decryption and no password.

Is this a bug?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TEXT PRIMARY KEY

2012-03-02 Thread Black, Michael (IS)

Hmmm...works for me...



On Windows:

SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test(a text primary key);
sqlite> insert into test values('1');
sqlite> insert into test values('1');
Error: column a is not unique

On Linux:

SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test (a text primary key);
sqlite> insert into test values('1');
sqlite> insert into test values('1');
Error: column a is not unique



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Oliver Peters [oliver@web.de]
Sent: Friday, March 02, 2012 4:13 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] TEXT PRIMARY KEY

Am 02.03.2012 11:03, schrieb Oliver Peters:


sorry I meant

CREATE TABLE test(
a TEXT PRIMARY KEY
);

(without INTEGER, usually I write INTEGER and not TEXT :-) )

> Am 02.03.2012 10:44, schrieb Christoph P.U. Kukulies:
>> When defining a column TEXT PRIMARY KEY (is that possible on TEXT?),
>
> yes
>
>> would this imply uniqueness?
>
> yes
>
>> Or would I have to write something like TEXT PRIMARY KEY UNIQUE ?
>
> no and that doesn't make sense I'd say
>
>>
>
> [...]
>
>
> simply try (untested)
>
> CREATE TABLE test(
> a TEXT INTEGER PRIMARY KEY
> );
>
> INSERT INTO test(a) VALUES('1');
> INSERT INTO test(a) VALUES('1');
>
>
>
> oliver
> ___
> 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] TEXT PRIMARY KEY

2012-03-02 Thread Jay A. Kreibich

On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies scratched on 
the wall:
> When defining a column TEXT PRIMARY KEY (is that possible on TEXT?),
> would this imply uniqueness?

  Kind of.  It implies uniqueness in the SQL sense, which does not
  include NULLs (remember, NULL != NULL).  The SQL term "PRIMARY KEY"
  should imply both "UNIQUE" and "NOT NULL", but there is a long
  standing issue in SQLite that allows NULLs in non-integer PRIMARY
  KEY columns.  This allows "duplicate" NULL entries in a PK column.

  Normally this isn't an issue, as you shouldn't have NULLs in a
  single-column PK anyways.
  
> Or would I have to write something like TEXT PRIMARY KEY UNIQUE ?

  No, but to be extra safe you should write "TEXT PRIMARY KEY NOT NULL."

   -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] compressing BLOB

2012-03-02 Thread Stephan Beal

On Fri, Mar 2, 2012 at 12:54 PM, Benoit Mortgat  wrote:

> SQLite does not compress your blob and you will have to do that
> programatically.  However you can define your own with
> sqlite_create_function_v2(): the prototype of your function would be
>

There's an implementation in the fossil source repo:

http://fossil-scm.org/index.html/artifact/41357470cd8b147dcea8c684ed131ebf29643650?ln=53-105

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressing BLOB

2012-03-02 Thread Benoit Mortgat

SQLite does not compress your blob and you will have to do that
programatically.  However you can define your own with
sqlite_create_function_v2(): the prototype of your function would be

void compress(sqlite3_context *context, int argc, sqlite3_value **argv)
{
   assert(argc==1);
   void *data = sqlite3_value_blob(argv[0]);
   int nBytes = sqlite3_value_bytes(argv[0]);

   // allocate memory for result
   .
   sqlite3_result_blob(...);
}

On Fri, Mar 2, 2012 at 10:42, Christoph P.U. Kukulies
 wrote:
> Since I'm inserting large files into the DB I'm wondering whether
> Sqlite can do compression on the data BLOB by itself or whether I
> should do that by programming when creating the BLOB?
>
> -- Christoph Kukulies ___
> sqlite-users mailing list sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
Benoit Mortgat
20, avenue Marcel Paul
69200 Vénissieux, France
+33 6 17 15 41 58
+33 4 27 11 61 23
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TEXT PRIMARY KEY

2012-03-02 Thread Oliver Peters


Am 02.03.2012 11:03, schrieb Oliver Peters:


sorry I meant

CREATE TABLE test(
a TEXT PRIMARY KEY
);

(without INTEGER, usually I write INTEGER and not TEXT :-) )


Am 02.03.2012 10:44, schrieb Christoph P.U. Kukulies:

When defining a column TEXT PRIMARY KEY (is that possible on TEXT?),


yes


would this imply uniqueness?


yes


Or would I have to write something like TEXT PRIMARY KEY UNIQUE ?


no and that doesn't make sense I'd say





[...]


simply try (untested)

CREATE TABLE test(
a TEXT INTEGER PRIMARY KEY
);

INSERT INTO test(a) VALUES('1');
INSERT INTO test(a) VALUES('1');



oliver
___
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] TEXT PRIMARY KEY

2012-03-02 Thread Oliver Peters


Am 02.03.2012 10:44, schrieb Christoph P.U. Kukulies:

When defining a column TEXT PRIMARY KEY (is that possible on TEXT?),


yes


would this imply uniqueness?


yes


Or would I have to write something like TEXT PRIMARY KEY UNIQUE ?


no and that doesn't make sense I'd say





[...]


simply try (untested)

CREATE TABLE test(
a TEXT INTEGER PRIMARY KEY
);

INSERT INTO test(a) VALUES('1');
INSERT INTO test(a) VALUES('1');



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

Re: [sqlite] TEXT PRIMARY KEY

2012-03-02 Thread Kit

2012/3/2, Christoph P.U. Kukulies :
> When defining a column TEXT PRIMARY KEY (is that possible on TEXT?),

Yes.

> would this imply uniqueness?

Yes.

> Or would I have to write something like TEXT PRIMARY KEY UNIQUE ?
> Christoph Kukulies

No. PRIMARY KEY is always UNIQUE.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TEXT PRIMARY KEY

2012-03-02 Thread Kees Nuyt

On Fri, 02 Mar 2012 10:44:20 +0100, "Christoph P.U. Kukulies"
 wrote:

> When defining a column TEXT PRIMARY KEY 
> (is that possible on TEXT?), 

Yes that is possible on any data type.

> would this imply uniqueness?

It would. 

> Or would I have to write something like TEXT PRIMARY KEY UNIQUE ?

No. Neither should you create a UNIQUE INDEX on the primary key column.

-- 
Regards,

Kees Nuyt

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

[sqlite] TEXT PRIMARY KEY

2012-03-02 Thread Christoph P.U. Kukulies

When defining a column TEXT PRIMARY KEY (is that possible on TEXT?), 
would this imply uniqueness?

Or would I have to write something like TEXT PRIMARY KEY UNIQUE ?

--
Christoph Kukulies

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

[sqlite] compressing BLOB

2012-03-02 Thread Christoph P.U. Kukulies

Since I'm inserting large files into the DB I'm wondering whether Sqlite 
can do compression on the data BLOB by itself

or whether I should do that by programming when creating the BLOB?

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

Re: [sqlite] Possible Solution to Sqlite3BTreeMovetoUnpacked SQLite SELECT Statement Workaround paradox

2012-03-02 Thread Frank Chang


Dan Kennedy, I discovered yesterday that one can use sqlite prepared SQLITE 
statements to reduce the CPU and memory utilization of parsing SELECT 
statements used to substitute for Sqlite3BTreeMovetoUnpacked and 
sqlite3_blob_reopen. Thank you
 



From: frank_chan...@hotmail.com
To: sqlite-users@sqlite.org
Subject: RE: Sqlite3BTreeMovetoUnpacked SQLite SELECT Statement Workaround is 
I/O Bound and uses all the Physical Memory
Date: Wed, 29 Feb 2012 11:59:56 -0500







Daniel Kennedy, I tried your SQLite SELECT Statement workaround(the profiler 
output is attached) but the profiler is full of sqlite functions and the 
application runs slower because it is I/O bound and uses almost all the 
physical memory. 
I was thinking maybe we could write only one SQLITE SELECT statement and cache 
the blobs in memory
Could you please suggest how I could fix this I/O Bound SQLIte SELECT Statement 
Problem.
void cIntersectingDedupe::GetSubGraphBlob(sSUBGRAPHINFO *SubGraph_,/*unsigned 
long*/ int*& SubGraphBlob_,
  int *Size_) {
 int Size;

  //sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);

 // Grab a BLOB and put it into a unsigned long buffer. As the BLOB contains 
record numbers,
 //   we will never see a value of zero. Thus, we use 0 to mark the end of the 
array.
 //
 // Note that we can have BLOBs of size 0, though. If we used DistillSubGraph to
 //   dedupe the subgraph, some records will be consolidated into others. The
 //   donor record's BLOB gets zapped because all of it's BLOB was rolled into 
the
 //   donee (All your BLOB are belong to us!)
 
 // First time, open the BLOB for real, else we can re-open (faster):
   char SelectStatement[256];
   char WhereClause[256];
 strcpy(SelectStatement, "select [Rows] from AggregatedData");
   sprintf(WhereClause," where [RowId] = %d",SubGraph_->IteratorPos+1);
   strcat(SelectStatement, WhereClause);
   int ReturnValue=sqlite3_prepare(SubGraph_->Database,
SelectStatement,-1,
_->Statement);
 
   int status =  sqlite3_step(SubGraph_->Statement);
   if (status == SQLITE_ROW) {
  
SubGraphBlob_ =  (int*)sqlite3_column_blob(SubGraph_->Statement,0); //2);
Size = (sqlite3_column_bytes(SubGraph_->Statement,0)); //2);
// if (SubGraph_->hBlob==0)
//  
sqlite3_blob_open(SubGraph_->Database,"main","AggregatedData","Rows",SubGraph_->IteratorPos+1,0,_->hBlob);
// else
//  sqlite3_blob_reopen(SubGraph_->hBlob,SubGraph_->IteratorPos+1);
//
// Size=sqlite3_blob_bytes(SubGraph_->hBlob)/sizeof(unsigned long);
// sqlite3_blob_read(SubGraph_->hBlob,SubGraphBlob_,Size*sizeof(unsigned 
long),0);
// SubGraphBlob_[Size]=0;
 if (Size_!=0)
  *Size_=Size;
 }
}
void cIntersectingDedupe::IntersectBlobs(sSUBGRAPHINFO *SubGraph_,int 
*IntersectionBlob_,
  /*unsigned long*/int *SubGraphBlob_) {
 int Pos1,Pos2,PosOut;
 GetSubGraphBlob(SubGraph_,SubGraphBlob_);
 // Perform the intersection. We walk though the two blobs, if the blobs 
contain the same
 //   value, that value is copied to PosOut in Blob_, else, the blob that is 
'behind' is
 //   incremented so it can 'catch up' to the other:
 Pos1=Pos2=PosOut=0;
 while (IntersectionBlob_[Pos1]!=0 && SubGraphBlob_[Pos2]!=0) {
  if (IntersectionBlob_[Pos1]==SubGraphBlob_[Pos2]) {
   IntersectionBlob_[PosOut++]=IntersectionBlob_[Pos1++];
   Pos2++;
  } else if (IntersectionBlob_[Pos1]

77 matches

Mail list logo