Re: [sqlite] SQLite bug?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/03/12 21:01, Steinar Midtskogen wrote:
> xCreate()/xConnect() will run through the argument list and do a PRAGMA
> table_info(%s) and then a couple of SELECT ... FROM %s.

The point I was trying to make is that this is not a generic SQLite issue,
but rather something specific to your code.  The pragmas and select are
specific to your virtual table implementation.

If you want further eyes on the code to work out the root cause then you
will need to post actual code to reproduce the issue as the descriptions
are too vague otherwise.  Some details will matter like exactly where the
sqlite3_stmt come from etc.

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

iEYEARECAAYFAk9YTS8ACgkQmOOfHg372QRB+gCfeC5UIHZoP9Pqat+GAHeZcd1K
xfAAn3T/pnHWoYl/T8qwHMehqE1WQevz
=yy/w
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Amalgamation

[Cory Nelson]

On Wed, Mar 7, 2012 at 9:58 PM, Andrew Cherednik
 wrote:
> Hi guys,
>
>
>
> Decided to open a discussion on SQLite amalgamation. Please forgive us, 
> Windows users, who unlucky enough to use Microsoft Visual Studio. As many of 
> you know, it is year 2012 now, and Visual Studio is up to version 10, but the 
> guys at Microsoft still use 16-bit indices for the debugger. As a result, the 
> source files with the line count greater than 65535 cannot be debugged.
>
>
>
> It is very annoying, and we, Windows users, should use a non-recommended 
> source for SQLite in our projects.
>
>
>
> Can you guys create a version with source split into files with each of them 
> not greater than 65535 lines (call it as you like, VC special version, 
> semi-amalgamated, etc.), but without the words "not recommended".
>
>
>


The compile will take longer, but using the non-amalgamation source
with LTO turned on will give the same effect as the amalgamation. (ie.
faster and smaller code)

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

Re: [sqlite] SQLite bug?

[Steinar Midtskogen]

Roger Binns  writes:

> On 07/03/12 13:46, Steinar Midtskogen wrote:
>> I think indeed that this is a problem:
>
> I cannot reproduce it using my own virtual tables.  This strongly implies
> that it is something to do with the code for your virtual tables.  Doesn't
> your 'interpolate' module use existing tables?

xCreate()/xConnect() will run through the argument list and do a
PRAGMA table_info(%s) and then a couple of SELECT ... FROM %s.  When
created with itself it loops in the PRAGMA query.  When called with a
second table which points back to it, it seems to pass the PRAGMA, but
it loops in the first SELECT query.

> It is also worth pointing out that table names, column names and column
> types live in separate namespaces.  This works just fine:
>
>   sqlite> create table a(a a);
>   sqlite>

Yes, but the arguments of the virtual tables are names, so "CREATE
VIRTUAL TABLE table USING interpolate(table)" will make
xCreate()/xConnect() fire off the queries PRAGMA table_info(table) and
SELECT * FROM table.

Anyway, I managed to break this loop by requiring the argument to have
an integer primary key, which virtual tables don't have, so it can't
be called recursively either.  I'm not sure if this is a restriction
that I want, though.

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

Re: [sqlite] sqlite-users Digest, Vol 51, Issue 1

[Andrew Cherednik]

CASE WHEN Name like 'A?'  THEN 1 ELSE 0 END

Regards,
 
Andrew Cherednik



"IMPORTANT - This email contains confidential information intended only for the 
person named above
and may be subject to legal privilege. If you are not the intended recipient, 
any disclosure, copying or use
of this information is prohibited. Healthscope provides no guarantee that this 
communication is free of
virus or that it has not been intercepted or interfered with. If you have 
received this email in error or have
any other concerns regarding its transmission, please notify 
postmas...@healthscope.com.au. You must
destroy the original transmission and its contents. Any views expressed within 
this communication are
those of the individual sender, except where the sender specifically states 
them to be the views of
Healthscope. If this document is not required for record keeping purposes 
please consider the
environment before storing or printing. This communication should not be copied 
or disseminated without
permission".


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

Re: [sqlite] sqlite-users Digest, Vol 51, Issue 1

[YAN HONG YE]

For a table named bb:
I have a sqlite database named bb:

> NamePrice1Price2Slevel
> A123 231  NULL
> A22212 NULL
> A3   21223   NULL

UPDATE bb SET Slevel =
CASE price1>12 WHEN 1 THEN 1 ELSE 0 END +
CASE price1>30 WHEN 1 THEN 1 ELSE 0 END +
CASE price2>20 WHEN 1 THEN 1 ELSE 0 END +
CASE price2>30 WHEN 1 THEN 1 ELSE 0 END +
CASE price2>80 WHEN 1 THEN 1 ELSE 0 END;
I want to judge  bb.name if has char ('A') then end+,
how to write this ?
CASE Name like 'A?'  WHEN 1 THEN 1 ELSE 0 END +

It's right?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Amalgamation

[Andrew Cherednik]

Hi guys,



Decided to open a discussion on SQLite amalgamation. Please forgive us, Windows 
users, who unlucky enough to use Microsoft Visual Studio. As many of you know, 
it is year 2012 now, and Visual Studio is up to version 10, but the guys at 
Microsoft still use 16-bit indices for the debugger. As a result, the source 
files with the line count greater than 65535 cannot be debugged.



It is very annoying, and we, Windows users, should use a non-recommended source 
for SQLite in our projects.



Can you guys create a version with source split into files with each of them 
not greater than 65535 lines (call it as you like, VC special version, 
semi-amalgamated, etc.), but without the words "not recommended".



This would be very helpful.



Regards,



Andrew Cherednik

Senior Analyst / Application Developer | Healthscope Limited Level 1, 312 St 
Kilda Road | Melbourne Victoria 3004



"IMPORTANT - This email contains confidential information intended only for the 
person named above
and may be subject to legal privilege. If you are not the intended recipient, 
any disclosure, copying or use
of this information is prohibited. Healthscope provides no guarantee that this 
communication is free of
virus or that it has not been intercepted or interfered with. If you have 
received this email in error or have
any other concerns regarding its transmission, please notify 
postmas...@healthscope.com.au. You must
destroy the original transmission and its contents. Any views expressed within 
this communication are
those of the individual sender, except where the sender specifically states 
them to be the views of
Healthscope. If this document is not required for record keeping purposes 
please consider the
environment before storing or printing. This communication should not be copied 
or disseminated without
permission".


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

Re: [sqlite] GUI for SQLite

[BareFeetWare]

On 08/03/2012, at 8:47 AM, Rose, John B wrote:

> We are new to SQLite and have experimented with a few GUIs, Firefox plugin, 
> SQLite DB Browser, and Navicat. Is there a single GUI that is considered the 
> best, with the most features?

Some time back, I compared several SQLite GUI editors, mainly for Mac, and 
published at:
http://www.barefeetware.com/sqlite/compare/?ml

Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

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

Re: [sqlite] Is it guaranteed that aggregate functions access tables sequentially?

[Jay A. Kreibich]

On Wed, Mar 07, 2012 at 10:30:54PM +, Emmanuel MacCaull scratched on the 
wall:
> Hi,
> 
> I'm writing a custom aggregate function whose result depends on the sort
> order of a subselect. It isn't clear whether this a good idea 

  Not a good idea.

> and the documentation on group_concat(X) seems to suggest that the
> order that rows are processed by an aggregate function is not
> guaranteed ("The order of the concatenated elements is arbitrary."
> -- http://www.sqlite.org/lang_aggfunc.html).

  That's true.

> Given something like this:
> 
> SELECT group_concat(name)
> FROM (SELECT * FROM table ORDER BY name)
> GROUP BY some_id;
> 
> I understood the documentation of group_concat() as meaning that even
> though there is an ORDER BY in the subselect, the order of the
> concatenated names is not guaranteed.
>
> If that is the case, it seems logical that the restriction is only there
> because the order in which the aggregate function processes each row is
> arbitrary. Does anyone know if this is true or is it safe to assume that
> the aggregate will access each row in order?

  Not so much "arbitrary" as undefined.  If you run the same query on
  the same data using the same version of SQLite, I'm sure you'll get
  the same results.  That said, you shouldn't depend on that.
  
  SQL tables themselves have no ordering... they are properly considered
  sets of rows (in the formal mathematical sense of "set").  Similarly,
  most working sets of data (relational variables) have no defined
  ordering.  A *result* set may have an order, defined by an ORDER BY,
  but that order may not hold when the result of a sub-select is cast
  back into a data source (such as above).

  In short, the database engine is able to reorder anything it wants,
  at any time, except for the final ORDER BY.  While a specific query
  may do what you expect (at least for this version of SQLite) things
  may change.  Heck, just adding an index can alter the query plan and
  change orderings.  SQLite even has a pragma to purposely re-order
  queries just to test for unintentional order dependencies.

  For example, most (but not all; and not all the time) database engines
  will implement a GROUP BY clause by sorting the rows according to the
  GROUP BY expressions.  This puts all "like" rows next to each other,
  making it easy to collapse the groups into individual rows.  There
  is no reason to assume that sorting process is stable, or that it
  will preserve the sub-select ordering in any way.  In fact, I
  wouldn't be surprised to find out some query engines just ignores a
  "data source" sub-select's ORDER BY all together, since it doesn't
  make semantic sense.  There are all kinds of additional optimizations
  that can open up.

   -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] GUI for SQLite

[Oliver Peters]

Am 07.03.2012 22:47, schrieb Rose, John B:

We are new to SQLite and have experimented with a few GUIs, Firefox plugin, 
SQLite DB Browser, and Navicat. Is there a single GUI that is considered the 
best, with the most features?


I prefer http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index 
because it's very fast and reliable (but only M$).


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

[sqlite] Is it guaranteed that aggregate functions access tables sequentially?

[Emmanuel MacCaull]

Hi,

I'm writing a custom aggregate function whose result depends on the sort order 
of a subselect. It isn't clear whether this a good idea and the documentation 
on group_concat(X) seems to suggest that the order that rows are processed by 
an aggregate function is not guaranteed ("The order of the concatenated 
elements is arbitrary." -- http://www.sqlite.org/lang_aggfunc.html).

Given something like this:

SELECT group_concat(name)
FROM (SELECT * FROM table ORDER BY name)
GROUP BY some_id;

I understood the documentation of group_concat() as meaning that even though 
there is an ORDER BY in the subselect, the order of the concatenated names is 
not guaranteed. If that is the case, it seems logical that the restriction is 
only there because the order in which the aggregate function processes each row 
is arbitrary. Does anyone know if this is true or is it safe to assume that the 
aggregate will access each row in order?

Thank you,
Emmanuel MacCaull
Software Developer
Research In Motion Limited

-
This transmission (including any attachments) may contain confidential 
information, privileged material (including material protected by the 
solicitor-client or other applicable privileges), or constitute non-public 
information. Any use of this information by anyone other than the intended 
recipient is prohibited. If you have received this transmission in error, 
please immediately reply to the sender and delete this information from your 
system. Use, dissemination, distribution, or reproduction of this transmission 
by unintended recipients is not authorized and may be unlawful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite bug?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/03/12 13:46, Steinar Midtskogen wrote:
> I think indeed that this is a problem:

I cannot reproduce it using my own virtual tables.  This strongly implies
that it is something to do with the code for your virtual tables.  Doesn't
your 'interpolate' module use existing tables?

It is also worth pointing out that table names, column names and column
types live in separate namespaces.  This works just fine:

  sqlite> create table a(a a);
  sqlite>

Roger


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

iEYEARECAAYFAk9X22QACgkQmOOfHg372QQIpgCgpWmgy2VcBJPXLZKXHmSowDcV
gmcAnRJS/sunJVbp9Ko9LMe+KngTVmMS
=SVUC
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GUI for SQLite

[J Glassy]

Hello,

 Everyone has their own opinion on which SQLite GUI is 'best'. I've
had the best consistent luck over the years using the SQLite
Manager/Firefox plugin, in terms of a good mix of features and
availability on different platforms.  There may be better ones out
there though, depending on exactly what you use it for.  Your mileage
may vary :)

joe

On Wed, Mar 7, 2012 at 2:47 PM, Rose, John B  wrote:
> We are new to SQLite and have experimented with a few GUIs, Firefox plugin, 
> SQLite DB Browser, and Navicat. Is there a single GUI that is considered the 
> best, with the most features?
>
> Thanks
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users




Joseph Glassy
Lead Software Engineer (UM faculty affiliate/contractor)
NASA Measures (Freeze/Thaw),Rm CFC 429
College of Forestry and Conservation
Univ. Montana, Missoula, MT 59812

Lupine Logic Inc.
www.lupinelogic.com
Scientific and Technical Programming
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] GUI for SQLite

[Rose, John B]

We are new to SQLite and have experimented with a few GUIs, Firefox plugin, 
SQLite DB Browser, and Navicat. Is there a single GUI that is considered the 
best, with the most features?

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

Re: [sqlite] SQLite bug?

[Steinar Midtskogen]

Steinar Midtskogen  writes:

> I can easily check that the argument doesn't match argv[2], but if
> it's still possible to get into a loop like this if virtual tables
> refer to eachother, then I don't think it's possible to detect this in
> the VT code.

I think indeed that this is a problem:

sqlite> create table a ( x INTEGER, PRIMARY KEY (x) );
sqlite> create virtual table b using interpolate(a);
sqlite> drop table a;
sqlite> create virtual table a using interpolate(b);

Program received signal SIGSEGV, Segmentation fault.

And the call backtrace goes on forever.

Yes, this is a user fault, but if the VT code must check this, it
would be nice to have a way to detect it.  It's possible to have some
kind of static counter in xCreate() which could be used for detecting
this, but that would limit the number of instances rather than the
level of nesting.

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

Re: [sqlite] SQLite bug?

[Steinar Midtskogen]

"Jay A. Kreibich"  writes:

> On Wed, Mar 07, 2012 at 08:58:27PM +0100, Steinar Midtskogen scratched on the 
> wall:
>> I've created a module which will take a table as an argument.  In
>> Xconnect it will run a query on that table.  Then I accidently used
>> the name of the virtual table in the argument list:
>> 
>>  CREATE VIRTUAL TABLE v USING my_module(v);
>> 
>> which seems to have triggered an infinite call loop.  I suppose what's
>> going on is that when Xconnect
>
>   It should be xCreate() if it is a new table.

Yes, probably.  xCreate() and xConnect() are the same function.  I
just happened to call it "connect".

>   I doubt that is what is happening.  More likely, the query is
>   blocking in some way.  SQLite would never automatically create a
>   non-existent table you tried to access.  How would it know what to
>   create?

I was guessing this having looked at the call trace in gdb.  It is:

#0  0x7fd0ed98d8a3 in sqlite3VXPrintf (pAccum=Cannot access memory at 
address 0x75644f08
) at sqlite3.c:19459
#1  0x7fd0ed98f97a in sqlite3VMPrintf (db=0x60e030, zFormat=0x7fd0eda087bf 
"%s", ap=0x75645120) at sqlite3.c:20096
#2  0x7fd0ed98fa9a in sqlite3MPrintf (db=0x60e030, zFormat=0x7fd0eda087bf 
"%s") at sqlite3.c:20112
#3  0x7fd0ed9f in vtabCallConstructor (db=0x60e030, pTab=0x6385e0, 
pMod=0x61eae0, xConstruct=0x7fd0ec345470 , 
pzErr=0x756452c0) at sqlite3.c:101732
#4  0x7fd0ed9f48e5 in sqlite3VtabCallConnect (pParse=0x19dd610, 
pTab=0x6385e0) at sqlite3.c:101848
#5  0x7fd0ed9d4b35 in sqlite3ViewGetColumnNames (pParse=0x19dd610, 
pTable=0x6385e0) at sqlite3.c:82813
#6  0x7fd0ed9e3fbd in sqlite3Pragma (pParse=0x19dd610, pId1=0x19dd938, 
pId2=0x19dd958, pValue=0x19dd998, minusFlag=0) at sqlite3.c:92812
#7  0x7fd0eda00cce in yy_reduce (yypParser=0x19dd8c0, yyruleno=257) at 
sqlite3.c:110533
#8  0x7fd0eda016af in sqlite3Parser (yyp=0x19dd8c0, yymajor=1, yyminor={z = 
0x19dd603 ")", n = 1}, pParse=0x19dd610) at sqlite3.c:110915
#9  0x7fd0eda024d7 in sqlite3RunParser (pParse=0x19dd610, zSql=0x19dd5f0 
"PRAGMA table_info(x)", pzErrMsg=0x75645888) at sqlite3.c:111752
#10 0x7fd0ed9e6b44 in sqlite3Prepare (db=0x60e030, zSql=0x19dd5f0 "PRAGMA 
table_info(x)", nBytes=-1, saveSqlFlag=1, pReprepare=0x0, 
ppStmt=0x75645a28, pzTail=0x0) at sqlite3.c:94079
#11 0x7fd0ed9e6e4f in sqlite3LockAndPrepare (db=0x60e030, zSql=0x19dd5f0 
"PRAGMA table_info(x)", nBytes=-1, saveSqlFlag=1, pOld=0x0, 
ppStmt=0x75645a28, pzTail=0x0) at sqlite3.c:94171
#12 0x7fd0ed9e7010 in sqlite3_prepare_v2 (db=0x60e030, zSql=0x19dd5f0 
"PRAGMA table_info(x)", nBytes=-1, ppStmt=0x75645a28, pzTail=0x0)
at sqlite3.c:94246
#13 0x7fd0ec34566e in interpolate_connect (db=0x60e030, pAux=, argc=, argv=0x638580, ppVtab=0x19dd4c0, 
pzErr=) at extension-functions.c:447
#14 0x7fd0ed9f44fa in vtabCallConstructor (db=0x60e030, pTab=0x6385e0, 
pMod=0x61eae0, xConstruct=0x7fd0ec345470 , 
pzErr=0x75645b40) at sqlite3.c:101752
#15 0x7fd0ed9f48e5 in sqlite3VtabCallConnect (pParse=0x19dc130, 
pTab=0x6385e0) at sqlite3.c:101848
#16 0x7fd0ed9d4b35 in sqlite3ViewGetColumnNames (pParse=0x19dc130, 
pTable=0x6385e0) at sqlite3.c:82813
#17 0x7fd0ed9e3fbd in sqlite3Pragma (pParse=0x19dc130, pId1=0x19dc458, 
pId2=0x19dc478, pValue=0x19dc4b8, minusFlag=0) at sqlite3.c:92812
#18 0x7fd0eda00cce in yy_reduce (yypParser=0x19dc3e0, yyruleno=257) at 
sqlite3.c:110533
#19 0x7fd0eda016af in sqlite3Parser (yyp=0x19dc3e0, yymajor=1, yyminor={z = 
0x19dc123 ")", n = 1}, pParse=0x19dc130) at sqlite3.c:110915
#20 0x7fd0eda024d7 in sqlite3RunParser (pParse=0x19dc130, zSql=0x19dc110 
"PRAGMA table_info(x)", pzErrMsg=0x75646108) at sqlite3.c:111752
#21 0x7fd0ed9e6b44 in sqlite3Prepare (db=0x60e030, zSql=0x19dc110 "PRAGMA 
table_info(x)", nBytes=-1, saveSqlFlag=1, pReprepare=0x0, 
ppStmt=0x756462a8, pzTail=0x0) at sqlite3.c:94079
#22 0x7fd0ed9e6e4f in sqlite3LockAndPrepare (db=0x60e030, zSql=0x19dc110 
"PRAGMA table_info(x)", nBytes=-1, saveSqlFlag=1, pOld=0x0, 
ppStmt=0x756462a8, pzTail=0x0) at sqlite3.c:94171
#23 0x7fd0ed9e7010 in sqlite3_prepare_v2 (db=0x60e030, zSql=0x19dc110 
"PRAGMA table_info(x)", nBytes=-1, ppStmt=0x756462a8, pzTail=0x0)
at sqlite3.c:94246
#24 0x7fd0ec34566e in interpolate_connect (db=0x60e030, pAux=, argc=, argv=0x638580, ppVtab=0x19dbfe0, 
pzErr=) at extension-functions.c:447
#25 0x7fd0ed9f44fa in vtabCallConstructor (db=0x60e030, pTab=0x6385e0, 
pMod=0x61eae0, xConstruct=0x7fd0ec345470 , 
pzErr=0x756463c0) at sqlite3.c:101752

... and so on, until:

#42352 0x7fd0ec34566e in interpolate_connect (db=0x60e030, pAux=, argc=, argv=0x638580, 
ppVtab=0x61d770, pzErr=) at extension-functions.c:447
#42353 0x7fd0ed9f44fa in vtabCallConstructor (db=0x60e030, pTab=0x6385e0, 
pMod=0x61eae0, xConstruct=0x7fd0ec345470 , 
pzErr=0x63a418) at 

Re: [sqlite] SQLite bug?

[Jay A. Kreibich]

On Wed, Mar 07, 2012 at 08:58:27PM +0100, Steinar Midtskogen scratched on the 
wall:
> I've created a module which will take a table as an argument.  In
> Xconnect it will run a query on that table.  Then I accidently used
> the name of the virtual table in the argument list:
> 
>  CREATE VIRTUAL TABLE v USING my_module(v);
> 
> which seems to have triggered an infinite call loop.  I suppose what's
> going on is that when Xconnect

  It should be xCreate() if it is a new table.

> tries to run a query on that table,
> SQLite will try to create the virtual table again, and then loops and
> quickly overflows the stack.

  I doubt that is what is happening.  More likely, the query is
  blocking in some way.  SQLite would never automatically create a
  non-existent table you tried to access.  How would it know what to
  create?

  I'm also not sure the VT name is valid until xCreate/xConnect has
  returned.

  What happens if you attempt to create a VT that references a
  non-existent table?  Are you sure you're checking all the return
  values from the queires issued inside xCreate/xConnect?

> Is this a SQLite bug, or should the virtual table code somehow detect
> that the user is trying to create a virtual table using itself?  Or
> should the user be blamed?

  Hard to say until you figure out exactly what is going on.

  Still, I would put the blame on the VT code.  Most VTs don't
  operate on existing tables, so there isn't any type of sanity
  checking.  Allowing the user do do something dumb is an application
  bug.  It is easy enough to check if the table that's being created
  and the arguments match.

> In my opinion the cleanest approach would be that SQLite itself
> determines that it has nested too deeply and gives an error.

  Except I doubt it is nesting or looping.  There might be some
  argument that a VT's name shouldn't be valid inside the xConnect()
  call, but I'm not convinced it is without digging a bit more.

  VTs are very advance pieces of code.  SQLite provides very little
  protection from dumb code when you're interfacing at that low a
  level.  Preventing dumb stuff is pretty much always up to the
  VT code.

   -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

[sqlite] SQLite bug?

[Steinar Midtskogen]

I've created a module which will take a table as an argument.  In
Xconnect it will run a query on that table.  Then I accidently used
the name of the virtual table in the argument list:

 CREATE VIRTUAL TABLE v USING my_module(v);

which seems to have triggered an infinite call loop.  I suppose what's
going on is that when Xconnect tries to run a query on that table,
SQLite will try to create the virtual table again, and then loops and
quickly overflows the stack.

Is this a SQLite bug, or should the virtual table code somehow detect
that the user is trying to create a virtual table using itself?  Or
should the user be blamed?

I suspect that this kind of loop could be less obvious if several
virtual tables are involved in a loop.

In my opinion the cleanest approach would be that SQLite itself
determines that it has nested too deeply and gives an error.

This is version 3.7.10.

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

Re: [sqlite] processes stuck on database locked

[Jay A. Kreibich]

On Wed, Mar 07, 2012 at 12:40:08PM -0500, Pavel Ivanov scratched on the wall:
> >> Another option is to start IMMEDIATE transaction in the second process
> >> to avoid this course of action altogether.
> >
> > ?Yes. ?That won't fix the issue (you'll still get contention issues
> > ?that will require restarting the transaction) but they'll show up
> > ?at the beginning of the transaction, rather than the end.
> 
> And because they happen at the beginning of transaction one can deal
> with that with a simple statement retry just like Mikolaj deals with
> SQLITE_BUSY after a single INSERT in the first process I guess. So he
> won't see it as "transaction restarts" in this case, just statement
> retries. That's why it can be seen as fixing the issue.

  Right... it doesn't get rid contention and of the posibility of an
  SQLITE_BUSY, but-- from a code sturcture standpoint-- it often makes
  recovery much simpler.

   -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] processes stuck on database locked

[Pavel Ivanov]

>> Another option is to start IMMEDIATE transaction in the second process
>> to avoid this course of action altogether.
>
>  Yes.  That won't fix the issue (you'll still get contention issues
>  that will require restarting the transaction) but they'll show up
>  at the beginning of the transaction, rather than the end.

And because they happen at the beginning of transaction one can deal
with that with a simple statement retry just like Mikolaj deals with
SQLITE_BUSY after a single INSERT in the first process I guess. So he
won't see it as "transaction restarts" in this case, just statement
retries. That's why it can be seen as fixing the issue.


Pavel


On Wed, Mar 7, 2012 at 12:28 PM, Jay A. Kreibich  wrote:
> On Wed, Mar 07, 2012 at 08:41:17AM -0500, Pavel Ivanov scratched on the wall:
>> First your second process gets a SHARED lock on the database to read
>> it, then your first process gets RESERVED lock on the database to
>> indicate that it will change it. Then your second process tries to
>> promote its SHARED lock to RESERVED one, sees that RESERVED lock has
>> been already taken and can't proceed (returns SQLITE_BUSY). At this
>> point first process can't commit its transaction because there's
>> SHARED lock on it and second process can't proceed with its
>> transaction because there's RESERVED lock on it. To continue you have
>> to rollback transaction in the second process and start it over again.
>
>  The SQLite docs explain a bit of what is going on here:
>
>    http://sqlite.org/c3ref/busy_handler.html
>
>  As Pavel says, sooner or later one of the connections needs to give
>  up and issue a ROLLBACK to kill the transaction and break the
>  deadlock.
>
>  "Using SQLite" spends several pages on this issue, as it is rather
>  non-obvious to someone that hasn't messed with SQLite quite a bit.
>
>> Another option is to start IMMEDIATE transaction in the second process
>> to avoid this course of action altogether.
>
>  Yes.  That won't fix the issue (you'll still get contention issues
>  that will require restarting the transaction) but they'll show up
>  at the beginning of the transaction, rather than the end.
>
>   -j
>
>
>
>
>> 2012/3/7 Miko??aj Radwan :
>> > Hi all,
>> >
>> > For a couple days now I've been trying to figure out this problem and
>> > can't seem to manage.
>> >
>> > I have two processes, both of which connect to the same SQLite database
>> > and do the following:
>> >
>> > process 1:
>> >
>> > # opens database
>> > PRAGMA foreign_keys = ON;
>> > PRAGMA journal_mode = PERSIST;
>> > INSERT INTO sometable (somecolumns) VALUES (somevalues);
>> > # closes database
>> >
>> > process 2:
>> >
>> > # opens database
>> > PRAGMA foreign_keys = ON;
>> > PRAGMA journal_mode = PERSIST;
>> > BEGIN DEFERRED TRANSACTION;
>> > SELECT somecolumns FROM sometable WHERE someconditions;
>> > DELETE FROM sometable WHERE someconditions;
>> > COMMIT TRANSACTION;
>> > # closes database
>> >
>> > After a couple iterations both processes start getting the "database
>> > locked" error and never work again unless one of them is killed.
>> >
>> > The funny thing is that I tested this on two configurations. One is a
>> > 32-bit Debian box with libsqlite-dev 3.7.3-1 installed. The other is a
>> > 64-bit Gentoo box with sqlite 3.7.7.1. The problem only arises on the
>> > first one. When I noticed it I looked through the version history but
>> > have not seen any mention of a bug like that being solved.
>> >
>> > Any ideas?
>> >
>> > Thanks in advance,
>> > Mikolaj
>> > ___
>> > 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
>
> --
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] processes stuck on database locked

[Jay A. Kreibich]

On Wed, Mar 07, 2012 at 08:41:17AM -0500, Pavel Ivanov scratched on the wall:
> First your second process gets a SHARED lock on the database to read
> it, then your first process gets RESERVED lock on the database to
> indicate that it will change it. Then your second process tries to
> promote its SHARED lock to RESERVED one, sees that RESERVED lock has
> been already taken and can't proceed (returns SQLITE_BUSY). At this
> point first process can't commit its transaction because there's
> SHARED lock on it and second process can't proceed with its
> transaction because there's RESERVED lock on it. To continue you have
> to rollback transaction in the second process and start it over again.

  The SQLite docs explain a bit of what is going on here:

http://sqlite.org/c3ref/busy_handler.html

  As Pavel says, sooner or later one of the connections needs to give
  up and issue a ROLLBACK to kill the transaction and break the
  deadlock.

  "Using SQLite" spends several pages on this issue, as it is rather
  non-obvious to someone that hasn't messed with SQLite quite a bit.

> Another option is to start IMMEDIATE transaction in the second process
> to avoid this course of action altogether.

  Yes.  That won't fix the issue (you'll still get contention issues
  that will require restarting the transaction) but they'll show up
  at the beginning of the transaction, rather than the end.

   -j




> 2012/3/7 Miko??aj Radwan :
> > Hi all,
> >
> > For a couple days now I've been trying to figure out this problem and
> > can't seem to manage.
> >
> > I have two processes, both of which connect to the same SQLite database
> > and do the following:
> >
> > process 1:
> >
> > # opens database
> > PRAGMA foreign_keys = ON;
> > PRAGMA journal_mode = PERSIST;
> > INSERT INTO sometable (somecolumns) VALUES (somevalues);
> > # closes database
> >
> > process 2:
> >
> > # opens database
> > PRAGMA foreign_keys = ON;
> > PRAGMA journal_mode = PERSIST;
> > BEGIN DEFERRED TRANSACTION;
> > SELECT somecolumns FROM sometable WHERE someconditions;
> > DELETE FROM sometable WHERE someconditions;
> > COMMIT TRANSACTION;
> > # closes database
> >
> > After a couple iterations both processes start getting the "database
> > locked" error and never work again unless one of them is killed.
> >
> > The funny thing is that I tested this on two configurations. One is a
> > 32-bit Debian box with libsqlite-dev 3.7.3-1 installed. The other is a
> > 64-bit Gentoo box with sqlite 3.7.7.1. The problem only arises on the
> > first one. When I noticed it I looked through the version history but
> > have not seen any mention of a bug like that being solved.
> >
> > Any ideas?
> >
> > Thanks in advance,
> > Mikolaj
> > ___
> > 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

-- 
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] What do people think of SQLite Root?

[Don V Nielsen]

Thumbs up on SqliteExpert.  I use it too.  Very nice.

dvn

On Tue, Mar 6, 2012 at 7:20 PM, Stephen Chrzanowski wrote:

> I use SQLite Expert.  There is a fee for the professional version, but it
> is one time, all updates are free.
>
> http://www.sqliteexpert.com/
>
>
> On Tue, Mar 6, 2012 at 1:43 PM, Fabio Spadaro  >wrote:
>
> > Hi.
> >
> > Il giorno 06 marzo 2012 17:31, Kit  ha scritto:
> >
> > > > Announcement of the release Sqlite Root 
> > >  now available for Linux.
> > > > Any feedback is appreciated.
> > > > Fabio Spadaro
> > >
> > > Two big problems:
> > > - license
> > > - size
> > >
> > > This software is unusable for me.
> > > --
> > > Kit
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> > What do you mean by "size".
> >
> > --
> > Fabio Spadaro
> >
> > Try Sqlite Root a GUI Admin Tools for manage Sqlite Database:
> > www.sqliteroot.com
> > ___
> > 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] Backup API handling of corrupt source DB

[Marcus Grimm]

>>> You can do the backup and after that do an integrity check on the
>>> backup. Surely you're backing up on a different
>>> server, don't you? If the back up pass the integrity check it a real
>>> backup, if not, launch a warning.
>>
>> Yeah, that's a good idea.
>> Ohh boy, why I didn't think about that my self ? :-)
>
> In this process, you have to prevent that you overwrite your last
> "working" backup! Which database will you use, if you realize, that the
> backup is a corrupt database?

Yes, it creates a few backups over last N Days prior starting
to overwrite the oldest one.

It is not a embedded environment, so no problem.
I could create backups of a few month if necessary..

Thanks

Marcus

>
> On a webserver you should have enough space to make additional copies.
> In an embedded environment, this could be difficult.
>
> Thomas
>
>>
>> Thanks
>>
>> Marcus
>>
>>>
 Sure I could try to simulate that, but probably somebody here
 knows the answer.

 Thank you.

 Marcus
>>>
>>>
>>> ___
>>> 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] processes stuck on database locked

[Simon Slavin]

On 7 Mar 2012, at 1:55pm, Pavel Ivanov  wrote:

> On Wed, Mar 7, 2012 at 8:49 AM, Simon Slavin  wrote:
>> On 7 Mar 2012, at 1:41pm, Pavel Ivanov  wrote:
>> 
>>> First your second process gets a SHARED lock on the database to read
>>> it, then your first process gets RESERVED lock on the database to
>>> indicate that it will change it. Then your second process tries to
>>> promote its SHARED lock to RESERVED one, sees that RESERVED lock has
>>> been already taken and can't proceed (returns SQLITE_BUSY). At this
>>> point first process can't commit its transaction because there's
>>> SHARED lock on it and second process can't proceed with its
>>> transaction because there's RESERVED lock on it. To continue you have
>>> to rollback transaction in the second process and start it over again.
>>> Another option is to start IMMEDIATE transaction in the second process
>>> to avoid this course of action altogether.
>> 
>> You can avoid writing your own busy-loop for anything involving locking by 
>> using
>> 
>> 
>> 
>> The default is zero, so SQLite wouldn't have been handling any timeouts for 
>> you.
> 
> Note: this won't help in this case because transaction have to be
> rolled back and it can be made by user of SQLite only.

Yeah, I should have explained that the call I wrote about must be used in 
combination with your recommendation for using IMMEDIATE, not instead of it.

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

Re: [sqlite] processes stuck on database locked

[Pavel Ivanov]

On Wed, Mar 7, 2012 at 8:49 AM, Simon Slavin  wrote:
> On 7 Mar 2012, at 1:41pm, Pavel Ivanov  wrote:
>
>> First your second process gets a SHARED lock on the database to read
>> it, then your first process gets RESERVED lock on the database to
>> indicate that it will change it. Then your second process tries to
>> promote its SHARED lock to RESERVED one, sees that RESERVED lock has
>> been already taken and can't proceed (returns SQLITE_BUSY). At this
>> point first process can't commit its transaction because there's
>> SHARED lock on it and second process can't proceed with its
>> transaction because there's RESERVED lock on it. To continue you have
>> to rollback transaction in the second process and start it over again.
>> Another option is to start IMMEDIATE transaction in the second process
>> to avoid this course of action altogether.
>
> You can avoid writing your own busy-loop for anything involving locking by 
> using
>
> 
>
> The default is zero, so SQLite wouldn't have been handling any timeouts for 
> you.

Note: this won't help in this case because transaction have to be
rolled back and it can be made by user of SQLite only.


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

Re: [sqlite] processes stuck on database locked

[Simon Slavin]

On 7 Mar 2012, at 1:41pm, Pavel Ivanov  wrote:

> First your second process gets a SHARED lock on the database to read
> it, then your first process gets RESERVED lock on the database to
> indicate that it will change it. Then your second process tries to
> promote its SHARED lock to RESERVED one, sees that RESERVED lock has
> been already taken and can't proceed (returns SQLITE_BUSY). At this
> point first process can't commit its transaction because there's
> SHARED lock on it and second process can't proceed with its
> transaction because there's RESERVED lock on it. To continue you have
> to rollback transaction in the second process and start it over again.
> Another option is to start IMMEDIATE transaction in the second process
> to avoid this course of action altogether.

You can avoid writing your own busy-loop for anything involving locking by using



The default is zero, so SQLite wouldn't have been handling any timeouts for you.

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

Re: [sqlite] processes stuck on database locked

[Pavel Ivanov]

First your second process gets a SHARED lock on the database to read
it, then your first process gets RESERVED lock on the database to
indicate that it will change it. Then your second process tries to
promote its SHARED lock to RESERVED one, sees that RESERVED lock has
been already taken and can't proceed (returns SQLITE_BUSY). At this
point first process can't commit its transaction because there's
SHARED lock on it and second process can't proceed with its
transaction because there's RESERVED lock on it. To continue you have
to rollback transaction in the second process and start it over again.
Another option is to start IMMEDIATE transaction in the second process
to avoid this course of action altogether.


Pavel


2012/3/7 Mikołaj Radwan :
> Hi all,
>
> For a couple days now I've been trying to figure out this problem and
> can't seem to manage.
>
> I have two processes, both of which connect to the same SQLite database
> and do the following:
>
> process 1:
>
> # opens database
> PRAGMA foreign_keys = ON;
> PRAGMA journal_mode = PERSIST;
> INSERT INTO sometable (somecolumns) VALUES (somevalues);
> # closes database
>
> process 2:
>
> # opens database
> PRAGMA foreign_keys = ON;
> PRAGMA journal_mode = PERSIST;
> BEGIN DEFERRED TRANSACTION;
> SELECT somecolumns FROM sometable WHERE someconditions;
> DELETE FROM sometable WHERE someconditions;
> COMMIT TRANSACTION;
> # closes database
>
> After a couple iterations both processes start getting the "database
> locked" error and never work again unless one of them is killed.
>
> The funny thing is that I tested this on two configurations. One is a
> 32-bit Debian box with libsqlite-dev 3.7.3-1 installed. The other is a
> 64-bit Gentoo box with sqlite 3.7.7.1. The problem only arises on the
> first one. When I noticed it I looked through the version history but
> have not seen any mention of a bug like that being solved.
>
> Any ideas?
>
> Thanks in advance,
> Mikolaj
> ___
> 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] What's the best way to organize this database?

[Simon Slavin]

On 6 Mar 2012, at 8:48pm, John Salerno  wrote:

> Thanks to both of you. The question of normalization was one thing I was 
> considering, I just wasn't sure how it should be done. A separate table 
> with Artist ID and Artist Name fields could be useful. Would it be good to 
> make an equivalent table for the songs, or should the songs simply be 
> listed individually in the main table?

I don't understand the purpose of the table you originally described.  If you 
are listing the same tracks again and again, listing different times it was 
played, then it would be useful to have three tables (artists, tracks, plays). 
If each track is listed just once then I don't think you need a separate table 
for tracks.

Having three tables would make it very easy to list some statistics you might 
not have originally planned on getting.  You could, for instance, very quickly 
list how many times each track had been played.  Having people type the full 
track name in every time would inevitably lead to them typing it slightly 
differently sometimes, which would make full reporting untrustworthy.

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

Re: [sqlite] About doc generation tool of SQLite

[linuxbbs]

Found it
Thx very much





At 2012-03-07 20:24:51,"Richard Hipp"  wrote:
>On Wed, Mar 7, 2012 at 2:09 AM, linuxbbs  wrote:
>
>> In document of SQLite in URL:http://www.sqlite.org/capi3ref.html
>> It says: "This document is created by a script which scans comments in the
>> source code files."
>> I tried to find this script in the repo of SQLite, but I didn't find it.
>> Is there any one know where is it.
>>
>
>Those scripts are in the documentation source code which is in a separate
>repository from the source code for SQLite itself.  See the bottom of
>the Download
>page  for links.
>http://www.sqlite.org/docsrc/timeline
>
>
>
>> Thx.
>>
>> Regards,
>> Yang
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
>-- 
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backup API handling of corrupt source DB

[TeDe]

Am 07.03.2012 13:21, schrieb Marcus Grimm:
>
> On 07.03.2012 13:13, Eduardo Morras wrote:
>> At 12:22 07/03/2012, you wrote:
>>> Dear list,
>>>
>>> I'm using the backup api to frequently backup
>>> a running sqlite database.
>>>
>>> I'm wondering if the backup API is able to detect a corrupt
>>> database or will it simply also backup a corrupt DB ?
>>>
>>> I evaluating the need to issue a (timeconsuming) "pragma
>>> integrity_check" prior
>>> running the backup to avoid that a backup will be overwritten
>>> with an invalid database.
>>
>> You can do the backup and after that do an integrity check on the
>> backup. Surely you're backing up on a different
>> server, don't you? If the back up pass the integrity check it a real
>> backup, if not, launch a warning.
>
> Yeah, that's a good idea.
> Ohh boy, why I didn't think about that my self ? :-)

In this process, you have to prevent that you overwrite your last
"working" backup! Which database will you use, if you realize, that the
backup is a corrupt database?

On a webserver you should have enough space to make additional copies.
In an embedded environment, this could be difficult.

Thomas

>
> Thanks
>
> Marcus
>
>>
>>> Sure I could try to simulate that, but probably somebody here
>>> knows the answer.
>>>
>>> Thank you.
>>>
>>> Marcus
>>
>>
>> ___
>> 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] What's the best way to organize this database?

[John Salerno]

Thanks to both of you. The question of normalization was one thing I was 
considering, I just wasn't sure how it should be done. A separate table 
with Artist ID and Artist Name fields could be useful. Would it be good to 
make an equivalent table for the songs, or should the songs simply be 
listed individually in the main table? I assume the dates and times have to 
be individual entries each time.

However, while reading the Wikipedia page on normalization, I noticed that 
some of the examples of a normalized table actually lists people's names as 
separate entries in the table. Of course, the alternative was some kind of 
nested tables, that looked messy, so perhaps the separate listings were the 
first step toward normalization, the next step being multiple tables with 
IDs for the names?

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

Re: [sqlite] About doc generation tool of SQLite

[Richard Hipp]

On Wed, Mar 7, 2012 at 2:09 AM, linuxbbs  wrote:

> In document of SQLite in URL:http://www.sqlite.org/capi3ref.html
> It says: "This document is created by a script which scans comments in the
> source code files."
> I tried to find this script in the repo of SQLite, but I didn't find it.
> Is there any one know where is it.
>

Those scripts are in the documentation source code which is in a separate
repository from the source code for SQLite itself.  See the bottom of
the Download
page  for links.
http://www.sqlite.org/docsrc/timeline



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



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

Re: [sqlite] Backup API handling of corrupt source DB

[Marcus Grimm]

On 07.03.2012 13:13, Eduardo Morras wrote:

At 12:22 07/03/2012, you wrote:

Dear list,

I'm using the backup api to frequently backup
a running sqlite database.

I'm wondering if the backup API is able to detect a corrupt
database or will it simply also backup a corrupt DB ?

I evaluating the need to issue a (timeconsuming) "pragma integrity_check" prior
running the backup to avoid that a backup will be overwritten
with an invalid database.


You can do the backup and after that do an integrity check on the backup. 
Surely you're backing up on a different
server, don't you? If the back up pass the integrity check it a real backup, if 
not, launch a warning.


Yeah, that's a good idea.
Ohh boy, why I didn't think about that my self ? :-)

Thanks

Marcus




Sure I could try to simulate that, but probably somebody here
knows the answer.

Thank you.

Marcus



___
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] Backup API handling of corrupt source DB

[Eduardo Morras]

At 12:22 07/03/2012, you wrote:

Dear list,

I'm using the backup api to frequently backup
a running sqlite database.

I'm wondering if the backup API is able to detect a corrupt
database or will it simply also backup a corrupt DB ?

I evaluating the need to issue a (timeconsuming) "pragma 
integrity_check" prior

running the backup to avoid that a backup will be overwritten
with an invalid database.


You can do the backup and after that do an integrity check on the 
backup. Surely you're backing up on a different server, don't you? If 
the back up pass the integrity check it a real backup, if not, launch 
a warning.



Sure I could try to simulate that, but probably somebody here
knows the answer.

Thank you.

Marcus



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

Re: [sqlite] Backup API handling of corrupt source DB

[Marcus Grimm]

On 07.03.2012 12:53, Simon Slavin wrote:


On 7 Mar 2012, at 11:22am, Marcus Grimm  wrote:


I evaluating the need to issue a (timeconsuming) "pragma integrity_check" prior
running the backup to avoid that a backup will be overwritten
with an invalid database.


Did you know about



?  It misses out a check, but the check it misses out is for data which can be 
recovered simply by recreating corrupt indexes.


Yes, I'm aware about that check.



On the other hand, if you are using this as a warning system that corruption 
has occurred and therefore you may have faulty hardware, then the more thorough 
the check, the better.


Yes, it is just about safe design. I'm currently reviewing my implementation 
and thought
about the unlikely event of a DB corruption - In that case my current backup
mechanism will become easily useless, depending if the corruption is so
small that it is not detected during the normal program flow.
(The application is a server that runs for weeks)

However, it is so far a theoretical issue - we haven't yet seen any corruption
in the field. :-)

Thanks also to Dan for the clarification.

Kind regards

Marcus



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] Backup API handling of corrupt source DB

[Simon Slavin]

On 7 Mar 2012, at 11:22am, Marcus Grimm  wrote:

> I evaluating the need to issue a (timeconsuming) "pragma integrity_check" 
> prior
> running the backup to avoid that a backup will be overwritten
> with an invalid database.

Did you know about



?  It misses out a check, but the check it misses out is for data which can be 
recovered simply by recreating corrupt indexes.

On the other hand, if you are using this as a warning system that corruption 
has occurred and therefore you may have faulty hardware, then the more thorough 
the check, the better.

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

Re: [sqlite] Backup API handling of corrupt source DB

[Dan Kennedy]

On 03/07/2012 06:22 PM, Marcus Grimm wrote:

Dear list,

I'm using the backup api to frequently backup
a running sqlite database.

I'm wondering if the backup API is able to detect a corrupt
database or will it simply also backup a corrupt DB ?


Backup just copies pages. It will not detect corruption.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] processes stuck on database locked

[Simon Slavin]

On 7 Mar 2012, at 9:00am, Mikołaj Radwan  
wrote:

> After a couple iterations both processes start getting the "database
> locked" error and never work again unless one of them is killed.

My guess is that one or other processes are not closing the connection properly 
on the run previous to the one which is locked.  Are you inspecting and dealing 
with the result returned by every call (including the _close() ones) ?  If any 
of them are anything except NOERROR then make your program report them.

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

[sqlite] Backup API handling of corrupt source DB

[Marcus Grimm]

Dear list,

I'm using the backup api to frequently backup
a running sqlite database.

I'm wondering if the backup API is able to detect a corrupt
database or will it simply also backup a corrupt DB ?

I evaluating the need to issue a (timeconsuming) "pragma integrity_check" prior
running the backup to avoid that a backup will be overwritten
with an invalid database.

Sure I could try to simulate that, but probably somebody here
knows the answer.

Thank you.

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

Re: [sqlite] UPDATE failure

[A . Azzolini]

I'm sure about 'Id_' presence (it is used successfully in other saving 
operations close to UPDATE) 

My doubt is about implicit sum: 

Gross=Gross+ integer_value

this operation works fine for about 64 consecutive times and then I found 
a mismatch.

It seems to be solved only by reducing the speed of open/close sequence
(but is not an acceptable solution ...)

Maybe have I to do an explicit sum by a SELECT + UPDATE?

Thanks,
Alessandro



From:
Kit 
To:
General Discussion of SQLite Database 
Date:
06/03/2012 18.00
Subject:
Re: [sqlite] UPDATE failure



2012/3/6  :
> UPDATE table_name SET IdDeptGroup=1, Gross=Gross+ integer_value  WHERE
> Id_= Id_value
>
> Have you ever seen a mismatch(sum is lower then expected) in the final 
sum
> result ?

Missing some `Id_` in the table?
-- 
Kit
___
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] Once again about file change counter

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

> Or did you mean something else?

What I meant was that you leave the main database using the normal SQLite
VFS.  Then you implement a custom VFS whose intent is to be used as the
target for backups, and source for restores.  ie the backup file is a
separate file than the database file.

Since backups are made under app control they can be done at logical
points for the user, and possibly even annotated as to why they were made.

Roger

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

iEYEARECAAYFAk9XHR0ACgkQmOOfHg372QRU3ACgppC5pULlwoloeNfttW+tyNvG
ueoAoLbpbkZD5SAtLLCfB/nUW5xXpZUg
=Zmf8
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users