Re: [sqlite] performance regression when using "insert or replace"

[Richard Hipp]

On Thu, Jan 24, 2013 at 10:44 PM, Heiles, Katrina wrote:

> I am curious why versions prior to 3.6.19 don't enter the if
> (subjRequiresPage(pPg) ) in pager_write, but ever since 3.6.19 they do.
>

The change was needed to fix this bug:
http://www.sqlite.org/src/info/4a03ed

-- 
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] performance regression when using "insert or replace"

[Heiles, Katrina]

That makes sense.  Thanks for the explanation.

Unfortunately when I run the more extensive unit test that is built into our 
system, I still
see about a 10% regression (with temp_store=memory and any version >= 3.6.19).  
This is much 
better than the 25% regression I was getting in the extensive test when running 
with the default 
temp_store value.
I am curious why versions prior to 3.6.19 don't enter the if 
(subjRequiresPage(pPg) ) in pager_write, but ever since 3.6.19 they do.  (for 
my insert or replace unit tests).

Are there any options to avoid this? I have tried replacing my "insert or 
replace" with 
"delete" then "insert", which is almost as fast as the older code, so that is 
one option for me.


thanks againm, katrina



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dan Kennedy
Sent: Thursday, January 24, 2013 4:44 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] performance regression when using "insert or replace"

On 01/24/2013 02:48 AM, Heiles, Katrina wrote:
> Thank you so much!!  This is great news.
>
> One question: according to the tempfiles.html doc... Section 3.0 
> states that "The rollback journal, master journal, and statement 
> journal files are always written to disk. But the other kinds of 
> temporary files might be stored in memory only and never written to 
> disk."
>
> Section 2.3 states: "the statement journal is also omitted if an 
> alternative conflict resolution algorithm is used."

I think both of those statements are out of date. Rollback and master journal 
files (as well as *-wal files) are always created on disk, as these are 
required by database recovery in the event of an application crash or system 
(power) failure. But these days statement journals may be stored in memory if 
SQLite is configured with "PRAGMA temp_store=memory" or equivalent.

> --
> 
>
>
/* If the statement journal is open and the page is not in it,
> ** then write the current page to the statement journal.  Note that
> ** the statement journal format differs from the standard journal 
> format ** in that it omits the checksums and the header. */ if(
> subjRequiresPage(pPg) ){ rc = subjournalPage(pPg); }
> --
> 
>
> Doesn't going into this "if" statement mean that a statement journal 
> is getting created? If so, isn't a statement journal always written to 
> disk (based on above snipet from section 3)?

Yes and no, respectively. See function openSubJournal() in pager.c.

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

Re: [sqlite] using the same sqlite parameter more than once causes premature memory deallocation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24/01/13 00:24, abbood wrote:
> then it crashes with the following error: malloc: *** error for object 
> 0x9b6350: pointer being freed was not allocated *** set a breakpoint
> in malloc_error_break to debug

Coincidentally enough I am debugging this exact same issue right now.  Are
you sure the error has anything to do with your binds?  In my case I did
as it said and have found that ARC has added something to the autorelease
pool.  This has nothing directly to do with SQLite and everything to do
with the boundary between Objective C/ARC and C.

Roger

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

iEYEARECAAYFAlEBu6sACgkQmOOfHg372QSR5gCfU+FAhhp8QuzAB6q1sP8xq2T4
j3wAoMUx3i4dFzzIldgyGJghNtXmsJR6
=+wFc
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Running on windows 98

[Teg]

I think you kick '98 to the curb and let the 5 people who use it
maintain their own support for it. Bit-rot is a real problem with any
code that goes mostly unexercised. I'm thinking maybe the '98 people
should simply fork their own version of Sqlite. Then find their own
'98 maintainer, instead of trying to force the job onto Dr Hipp.




Thursday, January 24, 2013, 3:18:15 PM, you wrote:

GG> On Thu, Jan 24, 2013 at 2:21 PM, Richard Hipp  wrote:
>> On Thu, Jan 24, 2013 at 2:01 PM, Jose F. Gimenez wrote:
>>
>>> Richard,
>>>
>>> thanks for replying.
>>>
>>>
>>>  We have no way of testing SQLite on Win9x and so we do not intend to
 support Win9x moving forward.  Some older versions of SQLite are known to
 work on Win9x.  If you are still supporting Win9x applications, I suggest
 you use those older versions of SQLite.


GG> How about just supporting a compile time option to turn on or off that
GG> optimization (on by default)?  Then those compiling for Win9x could
GG> just turn it off yet it would not require explicit support and testing
GG> of Win9x since its the option being supported rather than the platform
GG> support.
GG> ___
GG> sqlite-users mailing list
GG> sqlite-users@sqlite.org
GG> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users




-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] Running on windows 98

[Gabor Grothendieck]

On Thu, Jan 24, 2013 at 2:21 PM, Richard Hipp  wrote:
> On Thu, Jan 24, 2013 at 2:01 PM, Jose F. Gimenez wrote:
>
>> Richard,
>>
>> thanks for replying.
>>
>>
>>  We have no way of testing SQLite on Win9x and so we do not intend to
>>> support Win9x moving forward.  Some older versions of SQLite are known to
>>> work on Win9x.  If you are still supporting Win9x applications, I suggest
>>> you use those older versions of SQLite.
>>>

How about just supporting a compile time option to turn on or off that
optimization (on by default)?  Then those compiling for Win9x could
just turn it off yet it would not require explicit support and testing
of Win9x since its the option being supported rather than the platform
support.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Running on windows 98

[Jose F. Gimenez]

Richard,

Ok, I fully understand your POV. I also write software for other 
developers, and many times I have to deal with these kind of issues.


Thanks again.

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

Re: [sqlite] Running on windows 98

[Richard Hipp]

On Thu, Jan 24, 2013 at 2:01 PM, Jose F. Gimenez wrote:

> Richard,
>
> thanks for replying.
>
>
>  We have no way of testing SQLite on Win9x and so we do not intend to
>> support Win9x moving forward.  Some older versions of SQLite are known to
>> work on Win9x.  If you are still supporting Win9x applications, I suggest
>> you use those older versions of SQLite.
>>
>
> Versions up to 3.7.11 are working fine on windows 98. Was version 3.7.12,
> where an optimization in read and write operations (overlapped mode) was
> introduced which broke compatibility with older windows versions.
>
> What I attached in my first post, was a minor change to avoid using that
> optimization for older windows, while keeping it for newer ones, so sqlite
> works fine in all windows versions. I've used isNT() function to check if
> that optimization is done or not. Please, review it; it doesn't break
> anything. I'm using it on Windows 98, XP, Vista and 7, and is working fine
> everywhere.
>

SQLite supports so many different platforms and so many different
compile-time options that it makes my head spin trying to test them all.
Each new platform or compile-time option is only a modest increase in
complexity itself.  And every time we start supporting a new platform or
option, it makes SQLite useful to a slightly larger audience.  But over
time, this support for a myriad of platforms and options adds up to some
serious complexity.  And complexity is the enemy of reliability.  And
complexity is the enemy of innovation, as all my time is eaten up
supporting obscure platforms and options such that I don't have time to
work on new features for a broad user base.

At some point you just have to draw and line and say "No!, We are not going
to support $obscure_platform_XYZ."  Windows 9x is across that line.

Yes, I could just blindly apply the supplied patch and be done with it.
But are you sure that won't break any legacy code?  You've done extensive
testing on WinXP, Vista, Win8, WinCE, and WinRT to make sure?  I'm guessing
you have not.  Which leaves it to me to do all that testing, and I don't
have the time right now.  Sorry.

Furthermore, those few extra lines of code added by this patch are lines of
code that we will need to support from now on.  Remember, initial code
development is only 10% of the effort - the other 90% is long term
support.  You can argue that a dozen extra lines out of 120,000 is not a
significant new support burden.  And you'd be right.  But a few dozen lines
here, and a few dozen lines there, and over the years this adds up.  Once
again, at some point you just have to draw and line and say "No!", the
requested feature is too obscure and has too small of a potential user base
to be worthwhile.

Note that the latest SQLite does still support win9x in this sense:  you
can plug in your own VFS module that supports win9x at runtime.  If you
want, you can copy the VFS code out of SQLite version 3.7.11 and add that
back into SQLite as a run-time VFS, if you like.  Your application can do
this.  So SQLite does still work on win9x - it is simply that the support
is no longer built-in.

The same thing happened for OS/2 a few releases back.  We removed the OS/2
VFS from the code so that OS/2 is no longer built in.  But any application
can still install their own OS/2 VFS at run-time, if they so desire.

So, to summarize, there will be no built-in win9x support added today.
Sorry to disappoint.

-- 
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] Running on windows 98

[Jose F. Gimenez]

Richard,

thanks for replying.


We have no way of testing SQLite on Win9x and so we do not intend to
support Win9x moving forward.  Some older versions of SQLite are known to
work on Win9x.  If you are still supporting Win9x applications, I suggest
you use those older versions of SQLite.


Versions up to 3.7.11 are working fine on windows 98. Was version 
3.7.12, where an optimization in read and write operations (overlapped 
mode) was introduced which broke compatibility with older windows versions.


What I attached in my first post, was a minor change to avoid using that 
optimization for older windows, while keeping it for newer ones, so 
sqlite works fine in all windows versions. I've used isNT() function to 
check if that optimization is done or not. Please, review it; it doesn't 
break anything. I'm using it on Windows 98, XP, Vista and 7, and is 
working fine everywhere.


Thanks,

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

Re: [sqlite] Running on windows 98

[Marc L. Allen]

Might I suggest you include his patch so it at least runs?  That way, if he's 
willing to test each new version, he doesn't need to modify the official source 
to do it.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Thursday, January 24, 2013 1:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Running on windows 98

On Wed, Jan 9, 2013 at 2:36 PM, Jose F. Gimenez wrote:

> Hi all,
>
> I've recently noticed that sqlite doesn't work on windows 98 since 
> version 3.7.12, due to the change to use OVERLAPPED when read and 
> write files. I've made a small modification to take care of it, and 
> avoid to use that way on win 98 (by using the function isNT() to check 
> that condition). I've attached a patch in this message.
>
> I've tested it and it seems to work fine (tested on win98, xp and 
> win7), but I'd like to know if it's correct or if there is any problem 
> which I can't see.
>

We have no way of testing SQLite on Win9x and so we do not intend to support 
Win9x moving forward.  Some older versions of SQLite are known to work on 
Win9x.  If you are still supporting Win9x applications, I suggest you use those 
older versions of SQLite.


>
> TIA,
>
> Jose F. Gimenez
>
> ___
> 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] Which C++ data structures might one use to cache a sqlite query result?

[Simon]

Sorry, I was in the process of shortening and smoothing my example
when my client sent it out.  Here's where I left off.

class Human {
  int age;
  string name;
public:
  ...
  bool TryToRob( int amount );
private:
  readAgeFromDB(...);
  readNameFromDB(...);
};

class Man : public Human {
...
};

class Woman : public Human {
public:
  void Impregnate( ... );
  bool isPregnant( ... );
  Human* foetus( ... );
};

How they read and write to the DB is in the implementation.

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

Re: [sqlite] Which C++ data structures might one use to cache a sqlite query result?

[Simon]

On Thu, Jan 24, 2013 at 10:31 AM, Frank Chang  wrote:
> Could anyone suggest which C++ data structure to use to cache a sqlite
> query result? Thank you for your help

Because of the strong typing benefits of C++, it makes little sense
(to me) to store the result in a container of a different type.  A
vector of strings would work to store anything, but that would require
lots of conversion.  A struct containing only data members would be a
better fit to avoid conversion.  But a class with members is the way
to go.  Let the class take care of getting the values, converting
them, let the object update its own data on the database.  Objects
contain data and offer services, objects have a lifetime.

class Human {
  int age;
  string name;
public:
  ...
  bool TryToRob( int amount );

};

class


Imagine the following "pseudo C++ code":


void CancelCourse(string School, int Course)
{
  StudentList s = PeopleList(STUDENT).Filter(SCHOOLNAME,
School).Filter(COURSEID, Course);

  for_each(s.begin(), s.end(), inform_student_of_course_cancelation );

  TeacherList t = PeopleList(TEACHER).Filter...;
  if ( t.size() != 1 )
 throw course_has_invalid_number_of_teachers;

  t[0]->RemoveFromCourse(Course);
  t[0]->AdjustSchedule();
  if ( t[0]->Courses.size() == 0 )
 t[0]->SendOnVacation();
}

In this code, the fact we're using a database and make queries has
been succesfully abstracted.  The way a teacher's schedule is handled
is probably different from how a student's is.  Also, the teacher gets
paid while the student pays, only students can cheat, but both can be
absent.  The data that holds this information can be stored in one
single table if it makes sense, but they are two different objects in
C++.

It depends on the context, but a query that joins two table (ie
persons attributes and a person address for instance) could be viewed
as a derived object (it is still a person, but now it has an address
also and supports shipping).

In C++ it is probably best to work as close to the problem domain
you're dealing with (teachers, courses, students) rather than remain
at the lower-level where queries are made in certain ways to emulate
the higher level tasks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Running on windows 98

[Jose F. Gimenez]

Hi,

any comment about this subject?

TIA,
Jose F. Gimenez


El 09/01/2013 20:36, Jose F. Gimenez escribió:

Hi all,

I've recently noticed that sqlite doesn't work on windows 98 since 
version 3.7.12, due to the change to use OVERLAPPED when read and 
write files. I've made a small modification to take care of it, and 
avoid to use that way on win 98 (by using the function isNT() to check 
that condition). I've attached a patch in this message.


I've tested it and it seems to work fine (tested on win98, xp and 
win7), but I'd like to know if it's correct or if there is any problem 
which I can't see.


TIA,

Jose F. Gimenez


___
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] Which C++ data structures might one use to cache a sqlite query result?

[James K. Lowden]

On Thu, 24 Jan 2013 07:31:04 -0800
Frank Chang  wrote:

> Could anyone suggest which C++ data structure to use to cache a sqlite
> query result?

std::deque, where T is a structure you define based on the result.  

Any container with O(1) insertion is your friend.  :-)

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

Re: [sqlite] internals. IPC or disk write based?

[Patrick]

You stated off by making some assumptions which would normally be correct, but 
are not valid for SQLite.  Your last two paragraphs suggest you realized what 
was really happening.  There is no persistent SQLite Server process, accepting 
requests from many users.  SQLite has no multi-user model and is not primarily 
intended for use as a server/client database.  All changes are performed by 
changing data in the database file or in temporary 'journal' files on disk.  
The only thing you could say is shared between users is whether the file on 
disk is locked (indicating that it's currently being updated by a user) or not.

Think of SQLite as the simple database system your TV uses to list channel 
names, or your mobile phone uses to keep its phone book (both of which are, in 
fact, things SQLite really is used for with literally millions of 
installations).  The fact that SQLite works at all with huge datasets being 
accessed by many users at once is just a result of good programming.

As your questions indicate, if you're starting off a programming project 
intending to use big datasets accessed concurrently by many users across a 
network, SQLite is probably not the right database engine to use.  More about 
this can be found at



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


Hi Simon

Thanks for your very helpful post.

Actually for everything I want to do DB-wise, sqlite is perfect . I 
actually wanted to understand this to use in a totally different project 
using the Ada language.


Have a great day-Patrick

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

Re: [sqlite] SELECT and UPDATE in single query

[James K. Lowden]

On Thu, 24 Jan 2013 16:47:02 +1100
Richard Baron Penman  wrote:

> How to find which keys have been updated from this query?

That's the problem with "limit N", right?  It's not based on the data.
Not only do you not know which rows were updated, you don't know which
ones were *selected* except by inspection.  

If you want to process a subset of rows with status=0, a better
approach is to use the primary key.  If the key is a date, say,
restrict the selection to a range of dates, perhaps a year or a
month, and increment the range on each iteration.  

If you want to know which rows were updated regardless of the key, what
you need is a column to hold a unique value for each update transaction,
and set it as part of the UPDATE.  You could add a datetime column,
for example, if the time resolution is fine enough.  

HTH.  

--jkl


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

Re: [sqlite] internals. IPC or disk write based?

[Simon Slavin]

On 24 Jan 2013, at 2:41pm, Patrick  wrote:

> I bought the Apress book. There is a chapter on internals. I have tried to 
> read what I could find on the net too but I am still mixed up about something.
> 
> I don't know how sqlite interacts with visitors while maintaining state. If 
> everything was read into memory it would be easy to imagine how things could 
> work but then how would a second application access it?
> 
> I am guessing other DBs that have authentication are socket based but I 
> guessing sqlite is not socket based.
> 
> If it was all based on disk writes then multiple applications could read and 
> write to it, via a queue with locked files. Is this the case? What sort of 
> inter-process communication is sqlite based on?

You stated off by making some assumptions which would normally be correct, but 
are not valid for SQLite.  Your last two paragraphs suggest you realized what 
was really happening.  There is no persistent SQLite Server process, accepting 
requests from many users.  SQLite has no multi-user model and is not primarily 
intended for use as a server/client database.  All changes are performed by 
changing data in the database file or in temporary 'journal' files on disk.  
The only thing you could say is shared between users is whether the file on 
disk is locked (indicating that it's currently being updated by a user) or not.

Think of SQLite as the simple database system your TV uses to list channel 
names, or your mobile phone uses to keep its phone book (both of which are, in 
fact, things SQLite really is used for with literally millions of 
installations).  The fact that SQLite works at all with huge datasets being 
accessed by many users at once is just a result of good programming.

As your questions indicate, if you're starting off a programming project 
intending to use big datasets accessed concurrently by many users across a 
network, SQLite is probably not the right database engine to use.  More about 
this can be found at



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

Re: [sqlite] Which C++ data structures might one use to cache a sqlite query result?

[Noel Frankinet]

a two dimensional array, a vector of vectors ?


On 24 January 2013 16:46, Cory Nelson  wrote:

> On Thu, Jan 24, 2013 at 9:31 AM, Frank Chang 
> wrote:
> > Could anyone suggest which C++ data structure to use to cache a sqlite
> > query result? Thank you for your help
>
> This is too vague, you'll need to provide more information. What have
> you tried so far?
>
> --
> Cory Nelson
> http://int64.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Noël Frankinet
Strategis sprl
0478/90.92.54
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Which C++ data structures might one use to cache a sqlite query result?

[Cory Nelson]

On Thu, Jan 24, 2013 at 9:31 AM, Frank Chang  wrote:
> Could anyone suggest which C++ data structure to use to cache a sqlite
> query result? Thank you for your help

This is too vague, you'll need to provide more information. What have
you tried so far?

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

[sqlite] Which C++ data structures might one use to cache a sqlite query result?

[Frank Chang]

Could anyone suggest which C++ data structure to use to cache a sqlite
query result? Thank you for your help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] internals. IPC or disk write based?

[Patrick]

Hi Everyone

I bought the Apress book. There is a chapter on internals. I have tried 
to read what I could find on the net too but I am still mixed up about 
something.


I don't know how sqlite interacts with visitors while maintaining state. 
If everything was read into memory it would be easy to imagine how 
things could work but then how would a second application access it?


I am guessing other DBs that have authentication are socket based but I 
guessing sqlite is not socket based.


If it was all based on disk writes then multiple applications could read 
and write to it, via a queue with locked files. Is this the case? What 
sort of inter-process communication is sqlite based on?



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

Re: [sqlite] SELECT and UPDATE in single query

[Yongil Jang]

Penman wrote:
How to find which keys have been updated from this query?

There is sqlite3_update_hook() function that returns rowid of changed
record.

Regards,
Yongil Jang.
On Jan 24, 2013 11:10 PM, "Igor Tandetnik"  wrote:

> On 1/24/2013 12:47 AM, Richard Baron Penman wrote:
>
>> How to find which keys have been updated from this query?
>>
>
> You can't, really. If you need a list of keys (or in fact a list of
> anything from the database), you need to run a SELECT statement. At which
> point you are back where you started and might as well keep the original
> design.
>
> If you really don't want to do that for some reason, you could create an
> AFTER UPDATE trigger on the table, which would call a custom function,
> passing each key to it as the status is being updated.
> --
> 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] SELECT and UPDATE in single query

[Igor Tandetnik]

On 1/24/2013 12:47 AM, Richard Baron Penman wrote:

How to find which keys have been updated from this query?


You can't, really. If you need a list of keys (or in fact a list of 
anything from the database), you need to run a SELECT statement. At 
which point you are back where you started and might as well keep the 
original design.


If you really don't want to do that for some reason, you could create an 
AFTER UPDATE trigger on the table, which would call a custom function, 
passing each key to it as the status is being updated.

--
Igor Tandetnik

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

[sqlite] using the same sqlite parameter more than once causes premature memory deallocation

[abbood]

if create an sqlite statement that uses the same parameter more than once ie

NSString* updateStmt = @"INSERT INTO search_email(..., subject, ...)"
" SELECT ..., :subject, ...,"
" coalesce((SELECT search_email.threadID "
" FROM search_email "
" WHERE search_email.subject MATCH :subject2 "
" ),"
" :uid"
" )";

int subjectIndex =
sqlite3_bind_parameter_index(searchEmailInsertStmt,":subject");
int subjectIndex2 =
sqlite3_bind_parameter_index(searchEmailInsertStmt,":subject2");

...
sqlite3_bind_text(searchEmailInsertStmt, subjectIndex, [subject UTF8String],
-1, SQLITE_TRANSIENT);// subject
sqlite3_bind_text(searchEmailInsertStmt, subjectIndex2, [subjectCopy
UTF8String], -1, SQLITE_TRANSIENT);// search_email.subject
then it crashes with the following error: malloc: *** error for object
0x9b6350: pointer being freed was not allocated *** set a breakpoint in
malloc_error_break to debug

any idea why?



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/using-the-same-sqlite-parameter-more-than-once-causes-premature-memory-deallocation-tp66687.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] performance regression when using "insert or replace"

[Dan Kennedy]

On 01/24/2013 03:25 AM, Teg wrote:



I use "insert or replace" heavily. In debug mode, I set it to use temp
file on disk so, I can watch the disk IO, in release mode I set it to
temp file in memory.

The danger is that if you ever do anything that requires a bunch of
temp file, you can easily run out of RAM. Adding and deleting indexes
for example while temp store is set to memory will run you out of
address space on a larger DB and a 32 bit system (windows).


VACUUM can also create large temporary files (since it uses a temp file
to construct the new database image before copying it over the top of
the old).

Other commands (other than CREATE INDEX and VACUUM) *usually* don't use
too much temp space.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] performance regression when using "insert or replace"

[Dan Kennedy]

On 01/24/2013 02:48 AM, Heiles, Katrina wrote:

Thank you so much!!  This is great news.

One question: according to the tempfiles.html doc... Section 3.0
states that "The rollback journal, master journal, and statement
journal files are always written to disk. But the other kinds of
temporary files might be stored in memory only and never written to
disk."

Section 2.3 states: "the statement journal is also omitted if an
alternative conflict resolution algorithm is used."


I think both of those statements are out of date. Rollback and
master journal files (as well as *-wal files) are always created
on disk, as these are required by database recovery in the event
of an application crash or system (power) failure. But these days
statement journals may be stored in memory if SQLite is configured
with "PRAGMA temp_store=memory" or equivalent.


--



/* If the statement journal is open and the page is not in it,

** then write the current page to the statement journal.  Note that
** the statement journal format differs from the standard journal
format ** in that it omits the checksums and the header. */ if(
subjRequiresPage(pPg) ){ rc = subjournalPage(pPg); }
--

Doesn't going into this "if" statement mean that a statement journal
is getting created? If so, isn't a statement journal always written
to disk (based on above snipet from section 3)?


Yes and no, respectively. See function openSubJournal() in pager.c.

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