Re: [sqlite] sqlite-src-3070701.zip compilation issues

[sreekumar . tp]

Pls list the compilation errors..

--Original Message--
From: Raja Kondu
Sender: sqlite-users-boun...@sqlite.org
To: sqlite-users@sqlite.org
ReplyTo: General Discussion of SQLite Database
Subject: [sqlite] sqlite-src-3070701.zip compilation issues
Sent: Aug 11, 2011 4:36 AM

Hi,

I have downloaded the SQLite version 3.7.7.1 from the *Legacy Source Code
Distribution Formats . *
**
Here I am just compiling the source code from the "src" folder and I am
seeing lot of compilation errors by saying the header files missing and some
of the header files are really missing.

Could some one please tell me is there any issue in this specific release ?

Earlier I use to compile the 3.6.17 version and now I am trying with the
latest version 3.7.7.1.

Please help me to get rid of these compilation errors.


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


Sent from BlackBerry® on Airtel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how let readonly connection wait for locked database ?

[Simon Slavin]

On 11 Aug 2011, at 3:35am, Wenbo Zhao wrote:

> I want a readonly connection wait for locked database instead of error
> return immediately.
> The question is equivalence to how to let a reaonly connection have
> busy_handler triggered
> when the database is locked.
> In my practice, the readonly connection will fail when db locked and
> busy_handler does not work.

Does this help ?



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

[sqlite] how let readonly connection wait for locked database ?

[Wenbo Zhao]

Hi guys,
I want a readonly connection wait for locked database instead of error
return immediately.
The question is equivalence to how to let a reaonly connection have
busy_handler triggered
when the database is locked.
In my practice, the readonly connection will fail when db locked and
busy_handler does not work.

Thanks.

-- 

Best Regards,
ZHAO, Wenbo

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

Re: [sqlite] sqlite3_wal_checkpoint wrror

[Richard Hipp]

On Wed, Aug 10, 2011 at 9:35 PM, Lisa Davey  wrote:

> In the past few weeks I installed uptates to Bonjour, itues, safari, apple
> application support. A program called HyperCam 3, The Garmin communicator
> plugin and usb drivers.
>

And you don't know which of these was the last to be installed when the
problem first appeared?  Bummer.  I was hoping you could help us narrow down
which application installer is causing this bug.  It's their problem, not
our, but we don't know who to report it to

When I say "reinstall iTunes", I'm merely guessing that will fix it.  It's
my standard answer to this problem.  And nobody every writes back, so I
assume it always works.  Let us know if you find out differently.


>
>
> On Wed, Aug 10, 2011 at 9:30 PM, Richard Hipp  wrote:
>
>>
>>
>> On Wed, Aug 10, 2011 at 9:15 PM, Lisa Davey  wrote:
>>
>>> Hi, I'm getting an error message whenever I reboot my computer stating
>>> "procedure entry point sqlite3_wal_checkpoint could not be located in the
>>> dynamic link library sqlite3.dll"
>>> How do I fix this?
>>>
>>
>> Reinstall iTunes.
>>
>> What software did you install recently that caused this to stop working?
>>
>>
>>
>>> Thanks
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>>
>
>


-- 
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] sqlite3_wal_checkpoint wrror

[Richard Hipp]

On Wed, Aug 10, 2011 at 9:15 PM, Lisa Davey  wrote:

> Hi, I'm getting an error message whenever I reboot my computer stating
> "procedure entry point sqlite3_wal_checkpoint could not be located in the
> dynamic link library sqlite3.dll"
> How do I fix this?
>

Reinstall iTunes.

What software did you install recently that caused this to stop working?



> Thanks
> ___
> 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] sqlite3_wal_checkpoint wrror

[Lisa Davey]

Hi, I'm getting an error message whenever I reboot my computer stating
"procedure entry point sqlite3_wal_checkpoint could not be located in the
dynamic link library sqlite3.dll"
How do I fix this?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite-src-3070701.zip compilation issues

[Raja Kondu]

Hi,

I have downloaded the SQLite version 3.7.7.1 from the *Legacy Source Code
Distribution Formats . *
**
Here I am just compiling the source code from the "src" folder and I am
seeing lot of compilation errors by saying the header files missing and some
of the header files are really missing.

Could some one please tell me is there any issue in this specific release ?

Earlier I use to compile the 3.6.17 version and now I am trying with the
latest version 3.7.7.1.

Please help me to get rid of these compilation errors.


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

Re: [sqlite] In memory v/s tmpfs

[Pavel Ivanov]

> But I sqlite can still check the number of db connections open

Even though as Michael have shown this is possible (with guaranteed
results only when run as root) it will still have races. Because if
you see only one process accessing database file at the end of
transaction and only one process at the beginning of the next one it
doesn't guarantee that there was no other processes writing to
database between those checks.

And btw I think scanning all contents in /proc directory will be much
much slower than checking for hot-journal.


Pavel


On Wed, Aug 10, 2011 at 2:13 PM, Sreekumar TP  wrote:
> Ok.. But I sqlite can still check the number of db connections open and
> optimise the checking=> as long as there is only one writer, do not check
> for hot journals.
>
> Can a database have two connections opened with two different journal modes
> ?
>
> On Wed, Aug 10, 2011 at 11:35 PM, Pavel Ivanov  wrote:
>
>> > If you have one reader and many writers, consider PRAGMA
>> journal_mode=WAL;
>>
>> Richard meant "one writer and many readers" of course.
>>
>> >> If the other process opens the db connection as read_only, will the hot
>> >> journal check be still done (during queries operations of the second
>> >> process)?
>> >>
>> >
>> > How is the second process suppose to know whether or not the other
>> process
>> > used read_only?
>>
>> And even those who opened db connection as read_only will check for
>> hot journal before executing any query.
>>
>>
>> Pavel
>>
>> On Wed, Aug 10, 2011 at 1:44 PM, Richard Hipp  wrote:
>> > On Wed, Aug 10, 2011 at 1:35 PM, Sreekumar TP > >wrote:
>> >
>> >> Ok, its getting a bit clear.
>> >> If there is only one process that is writing to the database, but has
>> more
>> >> than one process that reads the database, the locking mode can still be
>> >> exclusive  ?
>> >>
>> >
>> > PRAGMA locking_mode=EXCLUSIVE;  means "exclusive'.  Only one process can
>> > access.
>> >
>> > If you have one reader and many writers, consider PRAGMA
>> journal_mode=WAL;
>> >
>> >
>> >
>> >> If the other process opens the db connection as read_only, will the hot
>> >> journal check be still done (during queries operations of the second
>> >> process)?
>> >>
>> >
>> > How is the second process suppose to know whether or not the other
>> process
>> > used read_only?
>> >
>> >
>> >>
>> >>
>> >>
>> >>
>> >> On Wed, Aug 10, 2011 at 10:54 PM, Richard Hipp  wrote:
>> >>
>> >> > On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin 
>> >> > wrote:
>> >> >
>> >> > >
>> >> > > On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote:
>> >> > >
>> >> > > > Thanks for the explanation. The journal mode was OFF  which means
>> >> there
>> >> > > is
>> >> > > > no journal file created. So why is it the check still performed ?
>> >> > >
>> >> > >  Because the journal mode might have been 'ON' the last time that
>> >> > database
>> >> > > was used.
>> >> > >
>> >> >
>> >> > More importantly, the journal mode might be 'ON' in the other process
>> >> that
>> >> > crashed in the middle of a transaction.
>> >> >
>> >> >
>> >> >
>> >> > >
>> >> > > > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp 
>> >> wrote:
>> >> > > >
>> >> > > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP <
>> >> > sreekumar...@gmail.com
>> >> > > >>> wrote:
>> >> > > >>
>> >> > > >>> The 179148 failures on 'access' system call is due to access
>> check
>> >> of
>> >> > > two
>> >> > > >>> files - the journal file and the wal-file.
>> >> > >
>> >> > > Well, the file is obviously not being opened 179148 times.  I
>> thought
>> >> it
>> >> > > checked for these files only when sqlite3_open() was being run.  Is
>> it
>> >> > > checking for these files every time a command is executed ?  Surely
>> >> that
>> >> > is
>> >> > > unnecessary and a source of a large reduction in speed.
>> >> > >
>> >> >
>> >> > SQLite must check for hot journals at the start of each transaction.
>> >> >
>> >> > Or, more precisely, SQLite must check for hot journals whenever it
>> starts
>> >> > reading the database again after a period of time when another process
>> >> > might
>> >> > have been writing to the database (and might have crashed while
>> writing).
>> >> >
>> >> > That's when PRAGMA locking_mode=EXCLUSIVE blocks the hot-journal
>> checks.
>> >> >  If
>> >> > your process has exclusive access to the database, it is not possible
>> for
>> >> > another process to write to the database (and crash in the middle of
>> >> > writing).
>> >> >
>> >> >
>> >> >
>> >> > >
>> >> > > Simon.
>> >> > > ___
>> >> > > sqlite-users mailing list
>> >> > > sqlite-users@sqlite.org
>> >> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >> > >
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > D. Richard Hipp
>> >> > d...@sqlite.org
>> >> >  ___
>> >> > 

Re: [sqlite] In memory v/s tmpfs

[Black, Michael (IS)]

Under Linux:
man fuser

Will detect other processes which have the DB open.

It's done by looking at all the /proc entries.

Under windows
http://www.codeproject.com/KB/IP/OpenFiles.aspx


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, August 10, 2011 1:27 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] In memory v/s tmpfs


On Wed, Aug 10, 2011 at 2:13 PM, Sreekumar TP wrote:

> But I sqlite can still check the number of db connections open
>


No it cannot.

In unix, there is no way for one process to know whether or not another
process has a particular file open.  And if there is a mechanism to do that
in windows, I'm not familiar with it.

And when you think about it, any mechanism that allowed one process to spy
on the file connections of another unrelated process would probably be
considered a security risk and hence undesirable.


--
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] In memory v/s tmpfs

[Sreekumar TP]

Oh Yes. I missed that point !

On Wed, Aug 10, 2011 at 11:57 PM, Richard Hipp  wrote:

> On Wed, Aug 10, 2011 at 2:13 PM, Sreekumar TP  >wrote:
>
> > But I sqlite can still check the number of db connections open
> >
>
>
> No it cannot.
>
> In unix, there is no way for one process to know whether or not another
> process has a particular file open.  And if there is a mechanism to do that
> in windows, I'm not familiar with it.
>
> And when you think about it, any mechanism that allowed one process to spy
> on the file connections of another unrelated process would probably be
> considered a security risk and hence undesirable.
>
>
> --
>  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] In memory v/s tmpfs

[Richard Hipp]

On Wed, Aug 10, 2011 at 2:13 PM, Sreekumar TP wrote:

> But I sqlite can still check the number of db connections open
>


No it cannot.

In unix, there is no way for one process to know whether or not another
process has a particular file open.  And if there is a mechanism to do that
in windows, I'm not familiar with it.

And when you think about it, any mechanism that allowed one process to spy
on the file connections of another unrelated process would probably be
considered a security risk and hence undesirable.


-- 
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] In memory v/s tmpfs

[Sreekumar TP]

Ok.. But I sqlite can still check the number of db connections open and
optimise the checking=> as long as there is only one writer, do not check
for hot journals.

Can a database have two connections opened with two different journal modes
?

On Wed, Aug 10, 2011 at 11:35 PM, Pavel Ivanov  wrote:

> > If you have one reader and many writers, consider PRAGMA
> journal_mode=WAL;
>
> Richard meant "one writer and many readers" of course.
>
> >> If the other process opens the db connection as read_only, will the hot
> >> journal check be still done (during queries operations of the second
> >> process)?
> >>
> >
> > How is the second process suppose to know whether or not the other
> process
> > used read_only?
>
> And even those who opened db connection as read_only will check for
> hot journal before executing any query.
>
>
> Pavel
>
> On Wed, Aug 10, 2011 at 1:44 PM, Richard Hipp  wrote:
> > On Wed, Aug 10, 2011 at 1:35 PM, Sreekumar TP  >wrote:
> >
> >> Ok, its getting a bit clear.
> >> If there is only one process that is writing to the database, but has
> more
> >> than one process that reads the database, the locking mode can still be
> >> exclusive  ?
> >>
> >
> > PRAGMA locking_mode=EXCLUSIVE;  means "exclusive'.  Only one process can
> > access.
> >
> > If you have one reader and many writers, consider PRAGMA
> journal_mode=WAL;
> >
> >
> >
> >> If the other process opens the db connection as read_only, will the hot
> >> journal check be still done (during queries operations of the second
> >> process)?
> >>
> >
> > How is the second process suppose to know whether or not the other
> process
> > used read_only?
> >
> >
> >>
> >>
> >>
> >>
> >> On Wed, Aug 10, 2011 at 10:54 PM, Richard Hipp  wrote:
> >>
> >> > On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin 
> >> > wrote:
> >> >
> >> > >
> >> > > On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote:
> >> > >
> >> > > > Thanks for the explanation. The journal mode was OFF  which means
> >> there
> >> > > is
> >> > > > no journal file created. So why is it the check still performed ?
> >> > >
> >> > >  Because the journal mode might have been 'ON' the last time that
> >> > database
> >> > > was used.
> >> > >
> >> >
> >> > More importantly, the journal mode might be 'ON' in the other process
> >> that
> >> > crashed in the middle of a transaction.
> >> >
> >> >
> >> >
> >> > >
> >> > > > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp 
> >> wrote:
> >> > > >
> >> > > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP <
> >> > sreekumar...@gmail.com
> >> > > >>> wrote:
> >> > > >>
> >> > > >>> The 179148 failures on 'access' system call is due to access
> check
> >> of
> >> > > two
> >> > > >>> files - the journal file and the wal-file.
> >> > >
> >> > > Well, the file is obviously not being opened 179148 times.  I
> thought
> >> it
> >> > > checked for these files only when sqlite3_open() was being run.  Is
> it
> >> > > checking for these files every time a command is executed ?  Surely
> >> that
> >> > is
> >> > > unnecessary and a source of a large reduction in speed.
> >> > >
> >> >
> >> > SQLite must check for hot journals at the start of each transaction.
> >> >
> >> > Or, more precisely, SQLite must check for hot journals whenever it
> starts
> >> > reading the database again after a period of time when another process
> >> > might
> >> > have been writing to the database (and might have crashed while
> writing).
> >> >
> >> > That's when PRAGMA locking_mode=EXCLUSIVE blocks the hot-journal
> checks.
> >> >  If
> >> > your process has exclusive access to the database, it is not possible
> for
> >> > another process to write to the database (and crash in the middle of
> >> > writing).
> >> >
> >> >
> >> >
> >> > >
> >> > > Simon.
> >> > > ___
> >> > > sqlite-users mailing list
> >> > > sqlite-users@sqlite.org
> >> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> > >
> >> >
> >> >
> >> >
> >> > --
> >> > D. Richard Hipp
> >> > d...@sqlite.org
> >> >  ___
> >> > sqlite-users mailing list
> >> > sqlite-users@sqlite.org
> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users 

Re: [sqlite] In memory v/s tmpfs

[Pavel Ivanov]

> If you have one reader and many writers, consider PRAGMA journal_mode=WAL;

Richard meant "one writer and many readers" of course.

>> If the other process opens the db connection as read_only, will the hot
>> journal check be still done (during queries operations of the second
>> process)?
>>
>
> How is the second process suppose to know whether or not the other process
> used read_only?

And even those who opened db connection as read_only will check for
hot journal before executing any query.


Pavel

On Wed, Aug 10, 2011 at 1:44 PM, Richard Hipp  wrote:
> On Wed, Aug 10, 2011 at 1:35 PM, Sreekumar TP wrote:
>
>> Ok, its getting a bit clear.
>> If there is only one process that is writing to the database, but has more
>> than one process that reads the database, the locking mode can still be
>> exclusive  ?
>>
>
> PRAGMA locking_mode=EXCLUSIVE;  means "exclusive'.  Only one process can
> access.
>
> If you have one reader and many writers, consider PRAGMA journal_mode=WAL;
>
>
>
>> If the other process opens the db connection as read_only, will the hot
>> journal check be still done (during queries operations of the second
>> process)?
>>
>
> How is the second process suppose to know whether or not the other process
> used read_only?
>
>
>>
>>
>>
>>
>> On Wed, Aug 10, 2011 at 10:54 PM, Richard Hipp  wrote:
>>
>> > On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin 
>> > wrote:
>> >
>> > >
>> > > On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote:
>> > >
>> > > > Thanks for the explanation. The journal mode was OFF  which means
>> there
>> > > is
>> > > > no journal file created. So why is it the check still performed ?
>> > >
>> > >  Because the journal mode might have been 'ON' the last time that
>> > database
>> > > was used.
>> > >
>> >
>> > More importantly, the journal mode might be 'ON' in the other process
>> that
>> > crashed in the middle of a transaction.
>> >
>> >
>> >
>> > >
>> > > > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp 
>> wrote:
>> > > >
>> > > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP <
>> > sreekumar...@gmail.com
>> > > >>> wrote:
>> > > >>
>> > > >>> The 179148 failures on 'access' system call is due to access check
>> of
>> > > two
>> > > >>> files - the journal file and the wal-file.
>> > >
>> > > Well, the file is obviously not being opened 179148 times.  I thought
>> it
>> > > checked for these files only when sqlite3_open() was being run.  Is it
>> > > checking for these files every time a command is executed ?  Surely
>> that
>> > is
>> > > unnecessary and a source of a large reduction in speed.
>> > >
>> >
>> > SQLite must check for hot journals at the start of each transaction.
>> >
>> > Or, more precisely, SQLite must check for hot journals whenever it starts
>> > reading the database again after a period of time when another process
>> > might
>> > have been writing to the database (and might have crashed while writing).
>> >
>> > That's when PRAGMA locking_mode=EXCLUSIVE blocks the hot-journal checks.
>> >  If
>> > your process has exclusive access to the database, it is not possible for
>> > another process to write to the database (and crash in the middle of
>> > writing).
>> >
>> >
>> >
>> > >
>> > > Simon.
>> > > ___
>> > > sqlite-users mailing list
>> > > sqlite-users@sqlite.org
>> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> > >
>> >
>> >
>> >
>> > --
>> > D. Richard Hipp
>> > d...@sqlite.org
>> >  ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Shell doesn't do

[Kit]

2011/8/10 Simon Slavin :
> However, the tags the shell tool generates are upper case.  There are two 
> possible fixes:
> a) change the tool to generate lower-case tags.
> b) change the documentation to say that the tags are HTML, not XHTML.
> Simon.

OK, here is the patch.
-- 
Kit

diff --git a/src/shell.c b/src/shell.c
index a54c922..337f35a 100644
--- a/src/shell.c
+++ b/src/shell.c
@@ -746,22 +746,22 @@ static int shell_callback(void *pArg, int nArg,
char **azArg, char **azCol, int
 }
 case MODE_Html: {
   if( p->cnt++==0 && p->showHeader ){
-fprintf(p->out,"");
+fprintf(p->out,"");
 for(i=0; iout,"");
+  fprintf(p->out,"");
   output_html_string(p->out, azCol[i]);
-  fprintf(p->out,"\n");
+  fprintf(p->out,"\n");
 }
-fprintf(p->out,"\n");
+fprintf(p->out,"\n");
   }
   if( azArg==0 ) break;
-  fprintf(p->out,"");
+  fprintf(p->out,"");
   for(i=0; iout,"");
+fprintf(p->out,"");
 output_html_string(p->out, azArg[i] ? azArg[i] : p->nullvalue);
-fprintf(p->out,"\n");
+fprintf(p->out,"\n");
   }
-  fprintf(p->out,"\n");
+  fprintf(p->out,"\n");
   break;
 }
 case MODE_Tcl: {
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In memory v/s tmpfs

[Richard Hipp]

On Wed, Aug 10, 2011 at 1:35 PM, Sreekumar TP wrote:

> Ok, its getting a bit clear.
> If there is only one process that is writing to the database, but has more
> than one process that reads the database, the locking mode can still be
> exclusive  ?
>

PRAGMA locking_mode=EXCLUSIVE;  means "exclusive'.  Only one process can
access.

If you have one reader and many writers, consider PRAGMA journal_mode=WAL;



> If the other process opens the db connection as read_only, will the hot
> journal check be still done (during queries operations of the second
> process)?
>

How is the second process suppose to know whether or not the other process
used read_only?


>
>
>
>
> On Wed, Aug 10, 2011 at 10:54 PM, Richard Hipp  wrote:
>
> > On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin 
> > wrote:
> >
> > >
> > > On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote:
> > >
> > > > Thanks for the explanation. The journal mode was OFF  which means
> there
> > > is
> > > > no journal file created. So why is it the check still performed ?
> > >
> > >  Because the journal mode might have been 'ON' the last time that
> > database
> > > was used.
> > >
> >
> > More importantly, the journal mode might be 'ON' in the other process
> that
> > crashed in the middle of a transaction.
> >
> >
> >
> > >
> > > > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp 
> wrote:
> > > >
> > > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP <
> > sreekumar...@gmail.com
> > > >>> wrote:
> > > >>
> > > >>> The 179148 failures on 'access' system call is due to access check
> of
> > > two
> > > >>> files - the journal file and the wal-file.
> > >
> > > Well, the file is obviously not being opened 179148 times.  I thought
> it
> > > checked for these files only when sqlite3_open() was being run.  Is it
> > > checking for these files every time a command is executed ?  Surely
> that
> > is
> > > unnecessary and a source of a large reduction in speed.
> > >
> >
> > SQLite must check for hot journals at the start of each transaction.
> >
> > Or, more precisely, SQLite must check for hot journals whenever it starts
> > reading the database again after a period of time when another process
> > might
> > have been writing to the database (and might have crashed while writing).
> >
> > That's when PRAGMA locking_mode=EXCLUSIVE blocks the hot-journal checks.
> >  If
> > your process has exclusive access to the database, it is not possible for
> > another process to write to the database (and crash in the middle of
> > writing).
> >
> >
> >
> > >
> > > Simon.
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> >  ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] In memory v/s tmpfs

[Sreekumar TP]

Ok, its getting a bit clear.
If there is only one process that is writing to the database, but has more
than one process that reads the database, the locking mode can still be
exclusive  ?
If the other process opens the db connection as read_only, will the hot
journal check be still done (during queries operations of the second
process)?




On Wed, Aug 10, 2011 at 10:54 PM, Richard Hipp  wrote:

> On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin 
> wrote:
>
> >
> > On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote:
> >
> > > Thanks for the explanation. The journal mode was OFF  which means there
> > is
> > > no journal file created. So why is it the check still performed ?
> >
> >  Because the journal mode might have been 'ON' the last time that
> database
> > was used.
> >
>
> More importantly, the journal mode might be 'ON' in the other process that
> crashed in the middle of a transaction.
>
>
>
> >
> > > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp  wrote:
> > >
> > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP <
> sreekumar...@gmail.com
> > >>> wrote:
> > >>
> > >>> The 179148 failures on 'access' system call is due to access check of
> > two
> > >>> files - the journal file and the wal-file.
> >
> > Well, the file is obviously not being opened 179148 times.  I thought it
> > checked for these files only when sqlite3_open() was being run.  Is it
> > checking for these files every time a command is executed ?  Surely that
> is
> > unnecessary and a source of a large reduction in speed.
> >
>
> SQLite must check for hot journals at the start of each transaction.
>
> Or, more precisely, SQLite must check for hot journals whenever it starts
> reading the database again after a period of time when another process
> might
> have been writing to the database (and might have crashed while writing).
>
> That's when PRAGMA locking_mode=EXCLUSIVE blocks the hot-journal checks.
>  If
> your process has exclusive access to the database, it is not possible for
> another process to write to the database (and crash in the middle of
> writing).
>
>
>
> >
> > Simon.
> > ___
> > 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] In memory v/s tmpfs

[Richard Hipp]

On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin  wrote:

>
> On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote:
>
> > Thanks for the explanation. The journal mode was OFF  which means there
> is
> > no journal file created. So why is it the check still performed ?
>
>  Because the journal mode might have been 'ON' the last time that database
> was used.
>

More importantly, the journal mode might be 'ON' in the other process that
crashed in the middle of a transaction.



>
> > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp  wrote:
> >
> >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP  >>> wrote:
> >>
> >>> The 179148 failures on 'access' system call is due to access check of
> two
> >>> files - the journal file and the wal-file.
>
> Well, the file is obviously not being opened 179148 times.  I thought it
> checked for these files only when sqlite3_open() was being run.  Is it
> checking for these files every time a command is executed ?  Surely that is
> unnecessary and a source of a large reduction in speed.
>

SQLite must check for hot journals at the start of each transaction.

Or, more precisely, SQLite must check for hot journals whenever it starts
reading the database again after a period of time when another process might
have been writing to the database (and might have crashed while writing).

That's when PRAGMA locking_mode=EXCLUSIVE blocks the hot-journal checks.  If
your process has exclusive access to the database, it is not possible for
another process to write to the database (and crash in the middle of
writing).



>
> Simon.
> ___
> 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] In memory v/s tmpfs

[Sreekumar TP]

The journal mode was always OFF when the code executes. The db was/is never
opened with a journal.
I havent analysed when exactly these calls are made, but definitely, it
quite a lot..

On Wed, Aug 10, 2011 at 10:39 PM, Simon Slavin  wrote:

>
> On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote:
>
> > Thanks for the explanation. The journal mode was OFF  which means there
> is
> > no journal file created. So why is it the check still performed ?
>
>  Because the journal mode might have been 'ON' the last time that database
> was used.
>
> > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp  wrote:
> >
> >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP  >>> wrote:
> >>
> >>> The 179148 failures on 'access' system call is due to access check of
> two
> >>> files - the journal file and the wal-file.
>
> Well, the file is obviously not being opened 179148 times.  I thought it
> checked for these files only when sqlite3_open() was being run.  Is it
> checking for these files every time a command is executed ?  Surely that is
> unnecessary and a source of a large reduction in speed.
>
> 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] Shell doesn't do

[Simon Slavin]

On 10 Aug 2011, at 6:10pm, Brian Curley wrote:

> @Simon: I'd disagree, unless you mean to make it less specific, since it's
> really an exercise for the reader to infer mark-up...not just HTML over
> XHTML.  But that's just me...

The SQLite page states specifically XHTML:



"The last output mode is "html". In this mode, sqlite3 writes the results of 
the query as an XHTML table."


All XHTML tags /must/ be lower case:



"XHTML documents must use lower case for all HTML element and attribute names."


However, the tags the shell tool generates are upper case.  There are two 
possible fixes:

a) change the tool to generate lower-case tags.
b) change the documentation to say that the tags are HTML, not XHTML.

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

Re: [sqlite] Shell doesn't do

[Brian Curley]

@Simon: I'd disagree, unless you mean to make it less specific, since it's
really an exercise for the reader to infer mark-up...not just HTML over
XHTML.  But that's just me...
On Aug 10, 2011 1:06 PM, "Simon Slavin"  wrote:
>
> On 10 Aug 2011, at 5:58pm, Kit wrote:
>
>> 2011/8/10 Brian Curley :
>>> Depending on your preferred shell...the sqlite CLI is just crazy
flexible.
>>> Just pipe your output through sed for upper/lower preferences.
>>
>> It is not entirely primitive. It needs only tags in lowercase.
>>
>> http://www.sqlite.org/sqlite.html;>
>> The last output mode is "html". In this mode, sqlite3 writes the
>> results of the query as an XHTML table.
>> 
>>
>> XHTML tags are in lowercase...
>
> Actually, Kit's right. If that format is meant to be XHTML (and not HTML,
as the in-app help says) then those tags MUST be lower case. The web page
needs changing.
>
> 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] In memory v/s tmpfs

[Simon Slavin]

On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote:

> Thanks for the explanation. The journal mode was OFF  which means there is
> no journal file created. So why is it the check still performed ?

 Because the journal mode might have been 'ON' the last time that database was 
used.

> On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp  wrote:
> 
>> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP >> wrote:
>> 
>>> The 179148 failures on 'access' system call is due to access check of two
>>> files - the journal file and the wal-file.

Well, the file is obviously not being opened 179148 times.  I thought it 
checked for these files only when sqlite3_open() was being run.  Is it checking 
for these files every time a command is executed ?  Surely that is unnecessary 
and a source of a large reduction in speed.

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

[sqlite] Constraint failed infos

[Julien Laffaye]

Hello,

Is it possible to get more info when I get a constraint failed error 
from the C API?
I am interested in the column name, table name and type of constraint.

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

Re: [sqlite] Shell doesn't do

[Simon Slavin]

On 10 Aug 2011, at 5:58pm, Kit wrote:

> 2011/8/10 Brian Curley :
>> Depending on your preferred shell...the sqlite CLI is just crazy flexible.
>> Just pipe your output through sed for upper/lower preferences.
> 
> It is not entirely primitive. It needs only tags in lowercase.
> 
> http://www.sqlite.org/sqlite.html;>
> The last output mode is "html". In this mode, sqlite3 writes the
> results of the query as an XHTML table.
> 
> 
> XHTML tags are in lowercase...

Actually, Kit's right.  If that format is meant to be XHTML (and not HTML, as 
the in-app help says) then those tags MUST be lower case.  The web page needs 
changing.

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

Re: [sqlite] Shell doesn't do

[Brian Curley]

Then you'd need your sed regex to handle that mask accordingly. The CLI is
understandably your best friend in this case.

A rogue's gallery just awaiting you to tell it what to do...I prefer to
model my app using views, and feed it .import files. You might even replace
time and date retrieval logic by piping sqlite output into use.  It's much
better than some on this forum give it credit.

But as mentioned elsewhere: you've got the recompile option too...
On Aug 10, 2011 12:58 PM, "Kit"  wrote:
> 2011/8/10 Brian Curley :
>> Depending on your preferred shell...the sqlite CLI is just crazy
flexible.
>> Just pipe your output through sed for upper/lower preferences.
>
> It is not entirely primitive. It needs only tags in lowercase.
>
> http://www.sqlite.org/sqlite.html;>
> The last output mode is "html". In this mode, sqlite3 writes the
> results of the query as an XHTML table.
> 
>
> XHTML tags are in lowercase...
> --
> 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] SQLite + unicode

[Igor Tandetnik]

On 8/10/2011 12:39 PM, NOCaut wrote:
> I work in VS2008 c++
> i create data base my.db and wont use U N I C O D E function from this DLL

Why won't you?

Which DLL is 'this DLL'?

> i find class or unit for connect to my base from VS2008
> http://sqlite.org/download.html - this link help me?

I imagine it should. You might also fine these useful: the API reference

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

And a very simple sample:

http://sqlite.org/quickstart.html

> you understand me?

No, I don't think I do, I must admit.
-- 
Igor Tandetnik

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

Re: [sqlite] In memory v/s tmpfs

[Sreekumar TP]

Thanks for the explanation. The journal mode was OFF  which means there is
no journal file created. So why is it the check still performed ?

On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp  wrote:

> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP  >wrote:
>
> > The 179148 failures on 'access' system call is due to access check of
>  two
> > files - the journal file and the wal-file.
> > The journal mode was OFF as also WAL mode.
> > Why is sqlite checking access permissions for this file 179140 times?
> > Removing or optimising this will make it faster by 20% atleast!
> >
>
>
> SQLite must check for the existence of hot journals that were left behind
> by
> other processes that were writing to the database but crashed before they
> finished.  If SQLite did not check for these, and if other processes did
> crash in the middle of a transaction, the database file would go corrupt.
>
> If your process is the only one accessing the database file, you can
> disable
> the journal-checks by doing:
>
> PRAGMA locking_mode=EXCLUSIVE;
>
>
>
>
> >
> >
> > access("/dev/shm/test.db-journal", F_OK) = -1 ENOENT (No such file or
> > directory)
> >
> > access("/dev/shm/test.db-wal", F_OK)   = -1 ENOENT (No such file or
> > directory)
> >
> > -Sreekumar
> > On Wed, Aug 10, 2011 at 5:28 PM, Sreekumar TP  > >wrote:
> >
> > > Hi,
> > >
> > > I have the results from the tests (below).  Alot of the time is spent
> in
> > > checking file permissions and locking the file (40 %).
> > >
> > >
> > > Inmem
> > >
> > > % time seconds  usecs/call callserrors syscall
> > >
> > > -- --- --- - - 
> > >
> > >  28.530.124727   1118524   write
> > >
> > >  23.420.102382   0414624   gettimeofday
> > >
> > >  20.780.090840   1 76513   read
> > >
> > >  13.720.059977   0191255   _llseek
> > >
> > >
> > >
> > > Db in tmpfs
> > >
> > > % time seconds  usecs/call callserrors syscall
> > >
> > > -- --- --- - - 
> > >
> > >  21.830.257073   1263306   write
> > >
> > >  21.180.249488   1179148179148 access
> > >
> > >  20.610.242725   0509292   fcntl64
> > >
> > >  13.040.153551   0448720   _llseek
> > >
> > >   9.440.94   1189370   read
> > >
> > >   8.160.096124   0414624   gettimeofday
> > >   2.550.033750 8   fdatasync
> > >
> > >   On Tue, Aug 9, 2011 at 7:34 PM, Pavel Ivanov 
> > wrote:
> > >
> > >> > Journal mode is WAL
> > >>
> > >> I believe in-memory database can't have journal mode WAL. So you
> > >> compare completely different settings.
> > >>
> > >>
> > >> Pavel
> > >>
> > >>
> > >> On Tue, Aug 9, 2011 at 5:15 AM,   wrote:
> > >> >
> > >> > Journal mode is WAL
> > >> >
> > >> >
> > >> > --Original Message--
> > >> > From: Roger Binns
> > >> > Sender: sqlite-users-boun...@sqlite.org
> > >> > To: General Discussion of SQLite Database
> > >> > ReplyTo: General Discussion of SQLite Database
> > >> > Subject: Re: [sqlite] In memory v/s tmpfs
> > >> > Sent: Aug 9, 2011 2:42 PM
> > >> >
> > >> > -BEGIN PGP SIGNED MESSAGE-
> > >> > Hash: SHA1
> > >> >
> > >> > On 08/08/2011 06:34 PM, sreekumar...@gmail.com wrote:
> > >> >> From the point of view of performance, I expected similar
> performance
> > ,
> > >> tmpfs being a little slower due to filesystem overhead. However, the
> > >> operations on tmpfs was much slower than expected.
> > >> >
> > >> > Using tmpfs requires many kernel calls which is considerably more
> > effort
> > >> > than the occasional malloc call.  Additionally files have to be
> > locked,
> > >> > journals made etc (you didn't mention your journal setting).
> > >> >
> > >> > Roger
> > >> > -BEGIN PGP SIGNATURE-
> > >> > Version: GnuPG v1.4.11 (GNU/Linux)
> > >> > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
> > >> >
> > >> > iEYEARECAAYFAk5A+g0ACgkQmOOfHg372QTR8ACgqNeeuOxHRy7+hMH5RY/OAyV2
> > >> > Wq0AoMaSRtoFN4obCgmgHlpHthd9z5Zp
> > >> > =pkJt
> > >> > -END PGP SIGNATURE-
> > >> > ___
> > >> > sqlite-users mailing list
> > >> > sqlite-users@sqlite.org
> > >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >> >
> > >> >
> > >> > Sent from BlackBerry® on Airtel
> > >> > ___
> > >> > 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
> > 

Re: [sqlite] SQLite + unicode

[Doug Currie]

On Aug 10, 2011, at 12:39 PM, NOCaut wrote:

> I work in VS2008 c++ 
> i create data base my.db and wont use U N I C O D E function from this DLL 
> i find class or unit for connect to my base from VS2008
> http://sqlite.org/download.html - this link help me?
> 
> you understand me?

No, but maybe these links will help...

http://www.sqlite.org/faq.html#q18

http://old.nabble.com/enable-ICU-in-SQLite-on-windows-platform-td27371403.html

http://www.urban-eye.com/pagesqliteicu.html

http://site.icu-project.org/

e

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

Re: [sqlite] Shell doesn't do

[Kit]

2011/8/10 Brian Curley :
> Depending on your preferred shell...the sqlite CLI is just crazy flexible.
> Just pipe your output through sed for upper/lower preferences.

It is not entirely primitive. It needs only tags in lowercase.

http://www.sqlite.org/sqlite.html;>
The last output mode is "html". In this mode, sqlite3 writes the
results of the query as an XHTML table.


XHTML tags are in lowercase...
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In memory v/s tmpfs

[Richard Hipp]

On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP wrote:

> The 179148 failures on 'access' system call is due to access check of  two
> files - the journal file and the wal-file.
> The journal mode was OFF as also WAL mode.
> Why is sqlite checking access permissions for this file 179140 times?
> Removing or optimising this will make it faster by 20% atleast!
>


SQLite must check for the existence of hot journals that were left behind by
other processes that were writing to the database but crashed before they
finished.  If SQLite did not check for these, and if other processes did
crash in the middle of a transaction, the database file would go corrupt.

If your process is the only one accessing the database file, you can disable
the journal-checks by doing:

 PRAGMA locking_mode=EXCLUSIVE;




>
>
> access("/dev/shm/test.db-journal", F_OK) = -1 ENOENT (No such file or
> directory)
>
> access("/dev/shm/test.db-wal", F_OK)   = -1 ENOENT (No such file or
> directory)
>
> -Sreekumar
> On Wed, Aug 10, 2011 at 5:28 PM, Sreekumar TP  >wrote:
>
> > Hi,
> >
> > I have the results from the tests (below).  Alot of the time is spent in
> > checking file permissions and locking the file (40 %).
> >
> >
> > Inmem
> >
> > % time seconds  usecs/call callserrors syscall
> >
> > -- --- --- - - 
> >
> >  28.530.124727   1118524   write
> >
> >  23.420.102382   0414624   gettimeofday
> >
> >  20.780.090840   1 76513   read
> >
> >  13.720.059977   0191255   _llseek
> >
> >
> >
> > Db in tmpfs
> >
> > % time seconds  usecs/call callserrors syscall
> >
> > -- --- --- - - 
> >
> >  21.830.257073   1263306   write
> >
> >  21.180.249488   1179148179148 access
> >
> >  20.610.242725   0509292   fcntl64
> >
> >  13.040.153551   0448720   _llseek
> >
> >   9.440.94   1189370   read
> >
> >   8.160.096124   0414624   gettimeofday
> >   2.550.033750 8   fdatasync
> >
> >   On Tue, Aug 9, 2011 at 7:34 PM, Pavel Ivanov 
> wrote:
> >
> >> > Journal mode is WAL
> >>
> >> I believe in-memory database can't have journal mode WAL. So you
> >> compare completely different settings.
> >>
> >>
> >> Pavel
> >>
> >>
> >> On Tue, Aug 9, 2011 at 5:15 AM,   wrote:
> >> >
> >> > Journal mode is WAL
> >> >
> >> >
> >> > --Original Message--
> >> > From: Roger Binns
> >> > Sender: sqlite-users-boun...@sqlite.org
> >> > To: General Discussion of SQLite Database
> >> > ReplyTo: General Discussion of SQLite Database
> >> > Subject: Re: [sqlite] In memory v/s tmpfs
> >> > Sent: Aug 9, 2011 2:42 PM
> >> >
> >> > -BEGIN PGP SIGNED MESSAGE-
> >> > Hash: SHA1
> >> >
> >> > On 08/08/2011 06:34 PM, sreekumar...@gmail.com wrote:
> >> >> From the point of view of performance, I expected similar performance
> ,
> >> tmpfs being a little slower due to filesystem overhead. However, the
> >> operations on tmpfs was much slower than expected.
> >> >
> >> > Using tmpfs requires many kernel calls which is considerably more
> effort
> >> > than the occasional malloc call.  Additionally files have to be
> locked,
> >> > journals made etc (you didn't mention your journal setting).
> >> >
> >> > Roger
> >> > -BEGIN PGP SIGNATURE-
> >> > Version: GnuPG v1.4.11 (GNU/Linux)
> >> > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
> >> >
> >> > iEYEARECAAYFAk5A+g0ACgkQmOOfHg372QTR8ACgqNeeuOxHRy7+hMH5RY/OAyV2
> >> > Wq0AoMaSRtoFN4obCgmgHlpHthd9z5Zp
> >> > =pkJt
> >> > -END PGP SIGNATURE-
> >> > ___
> >> > sqlite-users mailing list
> >> > sqlite-users@sqlite.org
> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >> >
> >> > Sent from BlackBerry® on Airtel
> >> > ___
> >> > sqlite-users mailing list
> >> > sqlite-users@sqlite.org
> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >>
> >
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] tcl incrblob interface

[Richard Hipp]

On Wed, Aug 10, 2011 at 12:24 PM, Victor Mayevski  wrote:

> Hello,
>
> I am trying to learn how to use the "incrblob" command in the Tcl
> interface and I can't get it to work. I create a one column table "t",
> insert one empty value into it, than do "db incrblob t a 1", which
> works fine, I get a file pointer back "incrblob_1".
> Then I do "puts incrblob_1 "hello world"", which also works. However,
> "chan close incrblob_1" fails with "Error: invalid argument" and doing
> "select * from t" shows no new data inserted.
> Any ideas what I am doing wroing?
>

The TCL test scripts for SQLite use this interface a lot.  Have you looked
at them to see what they are doing?  Example:
http://www.sqlite.org/src/artifact/3307c04876


>
> Thanks
> ___
> 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] In memory v/s tmpfs

[Sreekumar TP]

The 179148 failures on 'access' system call is due to access check of  two
files - the journal file and the wal-file.
The journal mode was OFF as also WAL mode.
Why is sqlite checking access permissions for this file 179140 times?
Removing or optimising this will make it faster by 20% atleast!



access("/dev/shm/test.db-journal", F_OK) = -1 ENOENT (No such file or
directory)

access("/dev/shm/test.db-wal", F_OK)   = -1 ENOENT (No such file or
directory)

-Sreekumar
On Wed, Aug 10, 2011 at 5:28 PM, Sreekumar TP wrote:

> Hi,
>
> I have the results from the tests (below).  Alot of the time is spent in
> checking file permissions and locking the file (40 %).
>
>
> Inmem
>
> % time seconds  usecs/call callserrors syscall
>
> -- --- --- - - 
>
>  28.530.124727   1118524   write
>
>  23.420.102382   0414624   gettimeofday
>
>  20.780.090840   1 76513   read
>
>  13.720.059977   0191255   _llseek
>
>
>
> Db in tmpfs
>
> % time seconds  usecs/call callserrors syscall
>
> -- --- --- - - 
>
>  21.830.257073   1263306   write
>
>  21.180.249488   1179148179148 access
>
>  20.610.242725   0509292   fcntl64
>
>  13.040.153551   0448720   _llseek
>
>   9.440.94   1189370   read
>
>   8.160.096124   0414624   gettimeofday
>   2.550.033750 8   fdatasync
>
>   On Tue, Aug 9, 2011 at 7:34 PM, Pavel Ivanov  wrote:
>
>> > Journal mode is WAL
>>
>> I believe in-memory database can't have journal mode WAL. So you
>> compare completely different settings.
>>
>>
>> Pavel
>>
>>
>> On Tue, Aug 9, 2011 at 5:15 AM,   wrote:
>> >
>> > Journal mode is WAL
>> >
>> >
>> > --Original Message--
>> > From: Roger Binns
>> > Sender: sqlite-users-boun...@sqlite.org
>> > To: General Discussion of SQLite Database
>> > ReplyTo: General Discussion of SQLite Database
>> > Subject: Re: [sqlite] In memory v/s tmpfs
>> > Sent: Aug 9, 2011 2:42 PM
>> >
>> > -BEGIN PGP SIGNED MESSAGE-
>> > Hash: SHA1
>> >
>> > On 08/08/2011 06:34 PM, sreekumar...@gmail.com wrote:
>> >> From the point of view of performance, I expected similar performance ,
>> tmpfs being a little slower due to filesystem overhead. However, the
>> operations on tmpfs was much slower than expected.
>> >
>> > Using tmpfs requires many kernel calls which is considerably more effort
>> > than the occasional malloc call.  Additionally files have to be locked,
>> > journals made etc (you didn't mention your journal setting).
>> >
>> > Roger
>> > -BEGIN PGP SIGNATURE-
>> > Version: GnuPG v1.4.11 (GNU/Linux)
>> > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>> >
>> > iEYEARECAAYFAk5A+g0ACgkQmOOfHg372QTR8ACgqNeeuOxHRy7+hMH5RY/OAyV2
>> > Wq0AoMaSRtoFN4obCgmgHlpHthd9z5Zp
>> > =pkJt
>> > -END PGP SIGNATURE-
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> >
>> > Sent from BlackBerry® on Airtel
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite + unicode

[NOCaut]

I work in VS2008 c++ 
i create data base my.db and wont use U N I C O D E function from this DLL 
i find class or unit for connect to my base from VS2008
http://sqlite.org/download.html - this link help me?

you understand me?
-- 
View this message in context: 
http://old.nabble.com/SQLite-%2B-unicode-tp32235242p32235681.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] Shell doesn't do

[Black, Michael (IS)]

If you don't like upper case then change the code and re-compile.  That's the 
nice part of source code.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Kit [kit.sa...@gmail.com]
Sent: Wednesday, August 10, 2011 11:18 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Shell doesn't do 

2011/8/10 Simon Slavin :
> I've never tried using this before for some reason but in a recent OS X 
> version of the command-line shell I tried using
> .mode html
> today.  The content is fine, but it doesn't do  or .
> Intentional ?  Bug ?  Oversight ?  Trying hard to believe I'm not the first 
> person who has tried this.
> If someone feels like fixing this, then it should also include  and 
>  as well, but most browsers will infer these.
> If someone claims 'no fix because we have users who rely on this' is there 
> any chance of another mode, perhaps 'htmlfull' which does this ?
> Simon.

Much more I dislike that the tags are uppercase on output. I prefer
lowercase, so this functionality can not be used practically.
--
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] Shell doesn't do

[Brian Curley]

Depending on your preferred shell...the sqlite CLI is just crazy flexible.
Just pipe your output through sed for upper/lower preferences.
 On Aug 10, 2011 12:18 PM, "Kit"  wrote:
> 2011/8/10 Simon Slavin :
>> I've never tried using this before for some reason but in a recent OS X
version of the command-line shell I tried using
>> .mode html
>> today.  The content is fine, but it doesn't do  or .
>> Intentional ?  Bug ?  Oversight ?  Trying hard to believe I'm not the
first person who has tried this.
>> If someone feels like fixing this, then it should also include 
and  as well, but most browsers will infer these.
>> If someone claims 'no fix because we have users who rely on this' is
there any chance of another mode, perhaps 'htmlfull' which does this ?
>> Simon.
>
> Much more I dislike that the tags are uppercase on output. I prefer
> lowercase, so this functionality can not be used practically.
> --
> 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

[sqlite] tcl incrblob interface

[Victor Mayevski]

Hello,

I am trying to learn how to use the "incrblob" command in the Tcl
interface and I can't get it to work. I create a one column table "t",
insert one empty value into it, than do "db incrblob t a 1", which
works fine, I get a file pointer back "incrblob_1".
Then I do "puts incrblob_1 "hello world"", which also works. However,
"chan close incrblob_1" fails with "Error: invalid argument" and doing
"select * from t" shows no new data inserted.
Any ideas what I am doing wroing?

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

Re: [sqlite] Shell doesn't do

[Kit]

2011/8/10 Simon Slavin :
> I've never tried using this before for some reason but in a recent OS X 
> version of the command-line shell I tried using
> .mode html
> today.  The content is fine, but it doesn't do  or .
> Intentional ?  Bug ?  Oversight ?  Trying hard to believe I'm not the first 
> person who has tried this.
> If someone feels like fixing this, then it should also include  and 
>  as well, but most browsers will infer these.
> If someone claims 'no fix because we have users who rely on this' is there 
> any chance of another mode, perhaps 'htmlfull' which does this ?
> Simon.

Much more I dislike that the tags are uppercase on output. I prefer
lowercase, so this functionality can not be used practically.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite + unicode

[Igor Tandetnik]

On 8/10/2011 12:08 PM, NOCaut wrote:
> in the other forum say: "You can get the SQLite source code and compile it
> directly with C++ Builder (2010 and XE tested)."

If you need SQLite source code, it's here: 
http://sqlite.org/download.html . See also 
http://sqlite.org/amalgamation.html
-- 
Igor Tandetnik

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

Re: [sqlite] Shell doesn't do

[Simon Slavin]

Thanks to everyone, including Michael for a fast work-around.

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

Re: [sqlite] SQLite + unicode

[NOCaut]

in the other forum say: "You can get the SQLite source code and compile it
directly with C++ Builder (2010 and XE tested)." 

Come to home and see
-- 
View this message in context: 
http://old.nabble.com/SQLite-%2B-unicode-tp32235242p32235384.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] SQLite + unicode

[Stephan Beal]

On Wed, Aug 10, 2011 at 5:55 PM, NOCaut  wrote:

> Where i can find c++ unicode unit for work with SQLite database? Thanks.
>

If you're looking for a generic unicode C++ library i can highly recommend:

http://utfcpp.sourceforge.net/

it's easy to use, header-only, and liberally licensed.

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

Re: [sqlite] SQLite + unicode

[NOCaut]

Sorry for my bad english)) I want find source code for work with sqlite..
-- 
View this message in context: 
http://old.nabble.com/SQLite-%2B-unicode-tp32235242p32235334.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] Shell doesn't do

[Black, Michael (IS)]

If you want it just DIY...





.mode html

select "";

select * from stuff;

select "";





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Roger Binns [rog...@rogerbinns.com]
Sent: Wednesday, August 10, 2011 10:56 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Shell doesn't do 

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/10/2011 09:13 PM, Simon Slavin wrote:
> The content is fine, but it doesn't do  or .

My shell copies the SQLite shell and also outputs only rows.  The reason
is that multiple statements could be used to generate output so you do
not assume each one is complete.  eg:

  .mode html
  select x,y from tablea where ...;
  select x,y from tableb where ...;

A full html output would also have to generate html/head/body etc where
it is virtually impossible to guess what the requirements are.

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

iEYEARECAAYFAk5CqkAACgkQmOOfHg372QT8KgCgrCiEoFK6/73GN7+PxLDXdSZL
qNUAoKFbRqojRSxpe/KjHZ49BYd/nPhR
=kql0
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite + unicode

[Igor Tandetnik]

On 8/10/2011 11:55 AM, NOCaut wrote:
> Where i can find c++ unicode unit for work with SQLite database? Thanks.

What kind of "unit"? What is it that you want to do, but cannot, without 
such a "unit"?
-- 
Igor Tandetnik

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

Re: [sqlite] Shell doesn't do

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/10/2011 09:13 PM, Simon Slavin wrote:
> The content is fine, but it doesn't do  or .

My shell copies the SQLite shell and also outputs only rows.  The reason
is that multiple statements could be used to generate output so you do
not assume each one is complete.  eg:

  .mode html
  select x,y from tablea where ...;
  select x,y from tableb where ...;

A full html output would also have to generate html/head/body etc where
it is virtually impossible to guess what the requirements are.

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

iEYEARECAAYFAk5CqkAACgkQmOOfHg372QT8KgCgrCiEoFK6/73GN7+PxLDXdSZL
qNUAoKFbRqojRSxpe/KjHZ49BYd/nPhR
=kql0
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite + unicode

[NOCaut]

Where i can find c++ unicode unit for work with SQLite database? Thanks.
-- 
View this message in context: 
http://old.nabble.com/SQLite-%2B-unicode-tp32235242p32235242.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] Shell doesn't do

[Richard Hipp]

On Wed, Aug 10, 2011 at 11:43 AM, Simon Slavin  wrote:

> I've never tried using this before for some reason but in a recent OS X
> version of the command-line shell I tried using
>
> .mode html
>
> today.  The content is fine, but it doesn't do  or .
>
> Intentional ?  Bug ?  Oversight ?  Trying hard to believe I'm not the first
> person who has tried this.
>

Intentional.  The idea is that the output from the shell would be
copy/pasted into a larger table that perhaps contains other rows from other
sources.  The SQLite shell was never intended to output a complete HTML
document.


>
> If someone feels like fixing this, then it should also include  and
>  as well, but most browsers will infer these.
>
> If someone claims 'no fix because we have users who rely on this' is there
> any chance of another mode, perhaps 'htmlfull' which does this ?
>
> Simon.
> ___
> 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] Shell doesn't do

[Simon Slavin]

I've never tried using this before for some reason but in a recent OS X version 
of the command-line shell I tried using

.mode html

today.  The content is fine, but it doesn't do  or .

Intentional ?  Bug ?  Oversight ?  Trying hard to believe I'm not the first 
person who has tried this.

If someone feels like fixing this, then it should also include  and 
 as well, but most browsers will infer these.

If someone claims 'no fix because we have users who rely on this' is there any 
chance of another mode, perhaps 'htmlfull' which does this ?

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

Re: [sqlite] Read only scaling optimization

[Drew Kozicki]

To Answer several questions at once.

Simon,
Just checking: by 'queries' you mean 'SELECT', right ?  You're not making
changes, just searching

Yes to optimize we average about 5-6 indexes per table.

D. Richard Hipp,
Open a separate database connection for each thread.  Don't try to use the
same database connection on all threads because access to the database
connection is serialized.

I'll look into this. Thank you

Teg,
Why multiple threads? What kind of performance do you get if you only
use a single thread?

Is it one thread per database perhaps?

This program is ran on massive servers and the people that use it are
talking of running 100's of millions of records through and we're trying to
let them scale so that they can benefit from the new servers. We seem to
have peeked out single thread performance at aprox. 2-10 million
records/hour.

Thank you once again in advance,
Drew
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data deleted

[Malcolm Lander]

Thanks again, I will have a go at that



On 10/08/2011 13:53, Simon Slavin wrote:
>
> On 10 Aug 2011, at 1:39pm, Malcolm Lander wrote:
>
>> Thanks for that.  Although that disables the DDL trigger, on running the
>> import I get a abort due to constraint violation error:
>
> You will probably need to import data into the TABLEs in an order that suits 
> the data hierarchy.  For instance, the TRIGGER you deleted implies that you 
> will need to put data into the Aircraft TABLE before the Flight TABLE.  I 
> don't know what other tables you have, but similar logic might apply to those 
> too.
>
> 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] Data deleted

[Simon Slavin]

On 10 Aug 2011, at 1:39pm, Malcolm Lander wrote:

> Thanks for that.  Although that disables the DDL trigger, on running the 
> import I get a abort due to constraint violation error:

You will probably need to import data into the TABLEs in an order that suits 
the data hierarchy.  For instance, the TRIGGER you deleted implies that you 
will need to put data into the Aircraft TABLE before the Flight TABLE.  I don't 
know what other tables you have, but similar logic might apply to those too.

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

Re: [sqlite] Read only scaling optimization

[Alexey Pechnikov]

2011/8/10 Wiktor Adamski :
> You
> may try increasing page size - bigger block means less near-random
> reads from the disc.

It's good way. With page size 8k instead of default 1k selects performance
may increasing ~3x. Note: PostgreSQL use 8k disk pages.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data deleted

[Malcolm Lander]

Thanks for that.  Although that disables the DDL trigger, on running the 
import I get a abort due to constraint violation error:

Exception:
-
   2.1 Date  : Wed, 10 Aug 2011 13:35:56 +0100
   2.2 Address   : 00A13665
   2.3 Module Name   : SQLiteExpertPro.exe - (The complete 
administration tool for SQLite)
   2.4 Module Version: 3.3.33.2174
   2.5 Type  : ESQLiteException
   2.6 Message   : Abort due to constraint violation.
   2.7 ID: 36A3
   2.8 Count : 1
   2.9 Status: New
   2.10 Note :



On 10/08/2011 13:29, Simon Slavin wrote:
>
> On 10 Aug 2011, at 1:27pm, Malcolm Lander wrote:
>
>> Thanks for the reply. It looks like this in the DDL is the culprit:
>>
>> CREATE TRIGGER [AircraftIDdeltrig]
>> BEFORE DELETE
>> ON [Aircraft]
>> FOR EACH ROW
>> BEGIN DELETE FROM Flights WHERE AircraftID = OLD.AircraftID;END;
>>
>> Is there a way to disable it?
>
> Issue the SQLite command
>
> DROP TRIGGER AircraftIDdeltrig
>
> Once you've done your import, to restore functionality, reissue the 'CREATE 
> TRIGGER' command as shown above.
>
> 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] Data deleted

[Simon Slavin]

On 10 Aug 2011, at 1:27pm, Malcolm Lander wrote:

> Thanks for the reply. It looks like this in the DDL is the culprit:
> 
> CREATE TRIGGER [AircraftIDdeltrig]
> BEFORE DELETE
> ON [Aircraft]
> FOR EACH ROW
> BEGIN DELETE FROM Flights WHERE AircraftID = OLD.AircraftID;END;
> 
> Is there a way to disable it?

Issue the SQLite command

DROP TRIGGER AircraftIDdeltrig

Once you've done your import, to restore functionality, reissue the 'CREATE 
TRIGGER' command as shown above.

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

Re: [sqlite] Data deleted

[Malcolm Lander]

Thanks for the reply. It looks like this in the DDL is the culprit:

CREATE TRIGGER [AircraftIDdeltrig]
BEFORE DELETE
ON [Aircraft]
FOR EACH ROW
BEGIN DELETE FROM Flights WHERE AircraftID = OLD.AircraftID;END;

Is there a way to disable it?



On 10/08/2011 13:14, Igor Tandetnik wrote:
> Malcolm Lander  wrote:
>> I have just tried using SQLite pro.  In the existing database I am
>> trying to manipulate there are several tables.  On trying to import a
>> .csv file into one of them and replacing the current data in that table
>> it works OK.
>>
>> However, all the data in one of the other tables also gets deleted
>> during the operation.  Why is this?
>
> There's either a trigger doing the deletion, or a foreign key with ON DELETE 
> CASCADE clause.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update one table from matches in another

[flakpit]

>You can do both in one pass:

>update locations set ItemCount =
>case when Location in (select Location from hive)
>  then '1' else '0'
>end;

Thanks for your help Igor, extremely elegant solution.
-- 
View this message in context: 
http://old.nabble.com/Update-one-table-from-matches-in-another-tp32232409p32233560.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] Read only scaling optimization

[Wiktor Adamski]

Look at disc transfer. With 16 queries in one thread on single disc,
disc may be accesed linearly (depeding on query). With 16 threads
accesing disc at the same time linear disc access is impossible
(however os may do some prefetching) and queries will by slower. You
may try increasing page size - bigger block means less near-random
reads from the disc.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data deleted

[Igor Tandetnik]

Malcolm Lander  wrote:
> I have just tried using SQLite pro.  In the existing database I am
> trying to manipulate there are several tables.  On trying to import a
> .csv file into one of them and replacing the current data in that table
> it works OK.
> 
> However, all the data in one of the other tables also gets deleted
> during the operation.  Why is this?

There's either a trigger doing the deletion, or a foreign key with ON DELETE 
CASCADE clause.
-- 
Igor Tandetnik

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

[sqlite] Data deleted

[Malcolm Lander]

I have just tried using SQLite pro.  In the existing database I am 
trying to manipulate there are several tables.  On trying to import a 
.csv file into one of them and replacing the current data in that table 
it works OK.

However, all the data in one of the other tables also gets deleted 
during the operation.  Why is this?  If I rename the second table I get 
a 'not found' error during the import to the original table so they are 
presumably linked somehow.

Is it posible to stop the data in the second table being deleted?


thanks
Malcolm

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

Re: [sqlite] In memory v/s tmpfs

[Sreekumar TP]

Hi,

I have the results from the tests (below).  Alot of the time is spent in
checking file permissions and locking the file (40 %).


Inmem

% time seconds  usecs/call callserrors syscall

-- --- --- - - 

 28.530.124727   1118524   write

 23.420.102382   0414624   gettimeofday

 20.780.090840   1 76513   read

 13.720.059977   0191255   _llseek



Db in tmpfs

% time seconds  usecs/call callserrors syscall

-- --- --- - - 

 21.830.257073   1263306   write

 21.180.249488   1179148179148 access

 20.610.242725   0509292   fcntl64

 13.040.153551   0448720   _llseek

  9.440.94   1189370   read

  8.160.096124   0414624   gettimeofday
  2.550.033750 8   fdatasync

On Tue, Aug 9, 2011 at 7:34 PM, Pavel Ivanov  wrote:

> > Journal mode is WAL
>
> I believe in-memory database can't have journal mode WAL. So you
> compare completely different settings.
>
>
> Pavel
>
>
> On Tue, Aug 9, 2011 at 5:15 AM,   wrote:
> >
> > Journal mode is WAL
> >
> >
> > --Original Message--
> > From: Roger Binns
> > Sender: sqlite-users-boun...@sqlite.org
> > To: General Discussion of SQLite Database
> > ReplyTo: General Discussion of SQLite Database
> > Subject: Re: [sqlite] In memory v/s tmpfs
> > Sent: Aug 9, 2011 2:42 PM
> >
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > On 08/08/2011 06:34 PM, sreekumar...@gmail.com wrote:
> >> From the point of view of performance, I expected similar performance ,
> tmpfs being a little slower due to filesystem overhead. However, the
> operations on tmpfs was much slower than expected.
> >
> > Using tmpfs requires many kernel calls which is considerably more effort
> > than the occasional malloc call.  Additionally files have to be locked,
> > journals made etc (you didn't mention your journal setting).
> >
> > Roger
> > -BEGIN PGP SIGNATURE-
> > Version: GnuPG v1.4.11 (GNU/Linux)
> > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
> >
> > iEYEARECAAYFAk5A+g0ACgkQmOOfHg372QTR8ACgqNeeuOxHRy7+hMH5RY/OAyV2
> > Wq0AoMaSRtoFN4obCgmgHlpHthd9z5Zp
> > =pkJt
> > -END PGP SIGNATURE-
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > Sent from BlackBerry® on Airtel
> > ___
> > 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] Update one table from matches in another

[Igor Tandetnik]

flakpit  wrote:
> I was just using this to make sure my matches were going to be correct.
> 
> select * from locations t1 where exists (
>  select * from hive t2
>  where t1.Location=t2.Location
>  )

A slightly shorter form:

select * from locations where Location in (
  select Location from hive);

> Now, I need to update the ItemCount column in the locations table with
> '1'when there is a corresponding match in the inventory table but using the
> query below marks all locations in the locations table so I am out of my
> depth here.
> 
> 
> update locations
>  set ItemCount='1'
>  where exists(
>  select Location from hive t2 where Location=t2.Location
>  )
> 
> Anyone know how to adjust this?
> 
> I also want to do the reverse, mark any ItemCount in the locations table
> with '0' when there is no matching entry in the location column in the
> inventory table
> 
> update locations
>  set ItemCount='0'
>  where exists(
>  select Location from hive t2 where Location<>t2.Location
>  )
> 
> Have tried several varieties of these commands with errors, getting mixed
> results.

You can do both in one pass:

update locations set ItemCount =
case when Location in (select Location from hive)
  then '1' else '0'
end;

-- 
Igor Tandetnik

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

Re: [sqlite] Update one table from matches in another

[flakpit]

>update locations
>   set ItemCount='1'
>   where exists(
>   select Location from hive where locations.Location=hive.Location
>   )

Okay, seems that I wasn't too far off. Thank you very much for the help, I
certainly needed it:):)

>update locations
> set ItemCount='0'
> where NOT exists(
> select Location from hive where locations.Location=hive.Location
>  )

I must be having a brain fade day, was sure that I had tried this. Ah well,
living and learning as much as I can. Thanks once again.
-- 
View this message in context: 
http://old.nabble.com/Update-one-table-from-matches-in-another-tp32232409p32233295.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] Update one table from matches in another

[Simon Slavin]

On 10 Aug 2011, at 10:27am, Martin Engelschalk wrote:

> Am 10.08.2011 11:14, schrieb flakpit:
>> I need to update the ItemCount column in the locations table with
>> '1'when there is a corresponding match in the inventory table but using the
>> query below marks all locations in the locations table so I am out of my
>> depth here.
>> 
>> 
>> update locations
>>   set ItemCount='1'
>>   where exists(
>>   select Location from hive t2 where Location=t2.Location
>>   )
> Try this:
> 
> update locations
>   set ItemCount='1'
>   where exists(
>   select Location from hive where locations.Location=hive.Location
>   )

Also, there are SQLite functions which will count related records for you:

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

So you can do things like

SELECT count(*) FROM hive WHERE hive.Location = locations.Location

which will return the number of related records very quickly.  Your form might 
be something like

update locations
set ItemCount = (
SELECT count(*) FROM hive WHERE hive.Location = 
locations.Location
)

Speed on both Martin's and my forms will be improved if you have an index on 
the Location column of the hive TABLE.

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

Re: [sqlite] Update one table from matches in another

[Martin Engelschalk]

Hi,

Am 10.08.2011 11:14, schrieb flakpit:
> To see what matches the location table with locations in the inventory table,
> I can issue the following command and it works returning 17 locations
> matched and is correct. There are 21 locations in the locations table but
> only 17 used in the inventory table so I know the query is returning correct
> data.
>
> I was just using this to make sure my matches were going to be correct.
>
> select * from locations t1 where exists (
>select * from hive t2
>where t1.Location=t2.Location
>)
>
>
> Now, I need to update the ItemCount column in the locations table with
> '1'when there is a corresponding match in the inventory table but using the
> query below marks all locations in the locations table so I am out of my
> depth here.
>
>
> update locations
>set ItemCount='1'
>where exists(
>select Location from hive t2 where Location=t2.Location
>)
Try this:

update locations
   set ItemCount='1'
   where exists(
   select Location from hive where locations.Location=hive.Location
   )


>
> Anyone know how to adjust this?
>
> I also want to do the reverse, mark any ItemCount in the locations table
> with '0' when there is no matching entry in the location column in the
> inventory table
>
> update locations
>set ItemCount='0'
>where exists(
>select Location from hive t2 where Location<>t2.Location
>)
Try this:

update locations
   set ItemCount='0'
   where NOT exists(
   select Location from hive where locations.Location=hive.Location
   )

> Have tried several varieties of these commands with errors, getting mixed
> results.

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