Re: [sqlite] System.Data.SQLite and VS2012 RTM designer support

2012-09-10 Thread Joe Mistachkin 

Browser IA wrote:
>
> Um, I'm sorry, that is not consistent with...our experience. My teammates
and I have
> been running 1.0.66 with full designer support in VS 2010 for well over
the past two
> years, that is, as long as VS 2010 has been out! It may be the case that
this is not
> an "officially supported" configuration for 1.0.66, but that didn't
prevent it from
> working just fine in VS 2010, all the way up through today. The reason I
know about
> the install.exe file in the bin\Designer directory is because we would
have to use
> it occasionally when a "patch Tuesday" update would temporarily make the
designer
> support "disappear" from VS 2010 (it just happened with the last patch
Tuesday),
> running this file put the support back, without having to reinstall SQLite
or VS
> 2010...
> 

The official release of 1.0.66.0 (from http://sqlite.phxsoftware.com/) never
supported
Visual Studio 2010, nor the .NET Framework 4.0.  There were several
"unofficial"
modifications to 1.0.66.0 that attempted to support Visual Studio 2010;
however, I have
no idea who worked on those efforts.  Officially, support for Visual Studio
2010 and
the .NET Framework 4.0 was added sometime around version 1.0.67.0, as seen
here:

https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki

The newly refactored designer support for Visual Studio (including 2010) was
not added
until around version 1.0.78.0.

> 
> Yes I discovered that prior to posting here, I had tried debugging it as
it was
> running (BREAK=1, attach), but I have no experience with Visual Studio
extensions,
> nor how to interface designers with the Visual Studio shell, so I quickly
realized
> I have no idea what is "right" vs. what is "wrong" when I was stepping
through the
> code.
> 

The code for the "installer.exe" file basically sets up a bunch of registry
keys and
modifies a couple .NET Framework configuration files.  The complexity is
mostly due
to the extra safety checks and validation it performs during the process,
including
supporting a "what-if" mode and full logging support.

>
> I have found this:
http://stackoverflow.com/questions/11291423/sqlite-with-visual-
> studio-2012-and-net-4-5, someone seems to have worked out a way to make
the 1.0.66
> designer support continue to work for VS 2012 (apparently, there is
something
> different that prevents this from "just working" like it did from VS 2008
to VS
> 2010), but I don't feel comfortable with the author's solution, as it is
an opaque
> binary that I have no real idea what it is doing. I'd also rather be at a
more
> current version of SQLite. If I knew support for VS 2012 was coming
sometime in the
> near future, that would help me decide whether to move to the latest
(broken)
> release with VS 2012, or stick with 1.0.66 without a designer until such
time as
> support can be added, neither situation is ideal, but I need to decide on
one path
> or the other, quickly.
> 

I'm not familiar with the various links in that post; however, version
1.0.66.0 is
quite old (circa April 2010) and many issues have been fixed since then.

--
Joe Mistachkin

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

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Dan Kennedy 

On 09/11/2012 11:12 AM, Keith Chew wrote:

Hi Jay


   In WAL mode that's only half incorrect.  Your description of NORMAL
   seems correct, but FULL should be fully durable.  The WAL file may
   need to be checkpointed on startup, but the if the commit happens in
   WAL/FULL, you should have full durability.



This is the reason for my question. As far as I can see, the
difference between NORMAL and FULL in WAL mode is that FULL has one
transaction in WAL to be checkpointed, and NORMAL has multiple. Since
both of them need to checkpoint for durability, how is it that FULL
can guarantee durability for 1 transaction, but not NORMAL for
multiple? From a development point of view, I would imagine that both
will use the same checkpoint routine, but yet I cannot understand why
FULL has a guarantee and NORMAL does not..


In WAL mode, when a transaction is written to disk, the modified
pages are appended to the *-wal file. Later on, during a checkpoint, all 
the modified pages in the *-wal file are copied back into the

database file. In both synchronous=NORMAL and synchronous=FULL the
*-wal file may contain more than one transaction.

The WAL file includes a running checksum so that if a power failure
occurs, the next client to read the database can determine the prefix
of the WAL file that can be considered trustworthy (as the power
failure may have corrupted more recently written parts of the file).

In both NORMAL mode, we do the following:

  1. Write a bunch of transactions into the WAL file.
  2. fsync() the WAL file.
  3. Copy the data from the WAL file into the database file.
  4. fsync() the database file.

If a power failure occurs at any time, the next process to read the
database scans the WAL file and attempts to read as many transactions
as possible. If the checksum fails at any point, it stops reading.

So you can lose data. Say a power failure occurs between steps 1
and 2 above. If your WAL had 5 unsynced transactions in it then
following recovery you may find that none, some or all of them have
survived, depending on how much of the WAL file actually made it
to disk before the power failed.

Synchronous=FULL mode is different. During step 1 above in FULL
mode, SQLite calls fsync() on the WAL file after writing each
transaction to it - before the users COMMIT command returns.

In this case if the same power failure occurs there is no chance
that any successfully committed transactions will be lost, as they
are guaranteed to have made it to disk.

So, in WAL mode, both sync=NORMAL and sync=FULL prevent database
corruption. However if a power failure occurs in NORMAL mode, some
recently committed transactions may have disappeared following
recovery.

Dan.








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

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Keith Chew 
Hi Jay
>
>   In WAL mode that's only half incorrect.  Your description of NORMAL
>   seems correct, but FULL should be fully durable.  The WAL file may
>   need to be checkpointed on startup, but the if the commit happens in
>   WAL/FULL, you should have full durability.
>

This is the reason for my question. As far as I can see, the
difference between NORMAL and FULL in WAL mode is that FULL has one
transaction in WAL to be checkpointed, and NORMAL has multiple. Since
both of them need to checkpoint for durability, how is it that FULL
can guarantee durability for 1 transaction, but not NORMAL for
multiple? From a development point of view, I would imagine that both
will use the same checkpoint routine, but yet I cannot understand why
FULL has a guarantee and NORMAL does not..

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

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Jay A. Kreibich 
On Mon, Sep 10, 2012 at 09:50:58PM -0500, Jay A. Kreibich scratched on the wall:
> On Tue, Sep 11, 2012 at 01:58:23AM +0100, Simon Slavin scratched on the wall:
> > On 11 Sep 2012, at 12:55am, Keith Chew  wrote:
> 
> > > and I know FULL (1)  will provide that. The question is why
> > > NORMAL (1) cannot provide the same.
> > 
> > Because NORMAL doesn't flush changes to disk after every single
> > transaction.  It queues a few of them up and flushes them all in one go. 
> 
>   That's not quite true.

  Oh wait, you're talking about WAL mode, aren't you?  Sorry,
  apparently I wasn't following the whole thread as closely as I should
  have been.

  In that case, yes... the WAL file will "save up" several transactions
  and only fully sync them when the checkpoint happens to clear the WAL
  file.

> > NORMAL) Faster but if you lose power more transactions are lost, but
> > the database is still not corrupt.
> >   FULL) Every COMMIT will take more time, but a power-failure can
> > lose a maximum of one transaction.
> 
>   That's also incorrect.

  In WAL mode that's only half incorrect.  Your description of NORMAL
  seems correct, but FULL should be fully durable.  The WAL file may
  need to be checkpointed on startup, but the if the commit happens in
  WAL/FULL, you should have full durability.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Jay A. Kreibich 
On Tue, Sep 11, 2012 at 03:11:57PM +1200, Keith Chew scratched on the wall:
> Hi Jay
> 
> >   http://www.sqlite.org/pragma.html#pragma_synchronous
> >
> >   When synchronous is FULL (2), the SQLite database engine will use
> >   the xSync method of the VFS to ensure that all content is safely
> >   written to the disk surface prior to continuing. This ensures
> >   that an operating system crash or power failure will not corrupt
> >   the database. FULL synchronous is very safe, but it is also
> >   slower. When synchronous is NORMAL (1), the SQLite database
> >   engine will still sync at the most critical moments, but less
> >   often than in FULL mode. There is a very small (though non-zero)
> >   chance that a power failure at just the wrong time could corrupt
> >   the database in NORMAL mode. But in practice, you are more likely
> >   to suffer a catastrophic disk failure or some other unrecoverable
> >   hardware fault.
> >
> 
> >From what I have read so far, my understanding is consistent with your
> explanation (except that I didn't realise corruption can happen in
> NORMAL, only lost of data).

  In the case of both NORMAL and FULL (in the case of non-WAL mode), if
  the commit is successful, there should never be data loss and the
  changes described by the transaction should become part of the
  database state.  If the commit did not finish, the changes described
  in the transaction were never part of the database state to start
  with and are not considered "lost".  In no case should NORMAL or
  FULL allow a commit to succeed, but not have the transaction be durable.

  ...except if the disks lie.  Which, as Simon has pointed out, is most
  of the time.

> Regardless, I would really like to hear
> from a developer that the above paragraph also applies to the WAL
> journal mode, and not just the older journal modes, since WAL was
> introduced later in 3.7 onwards.

  It does not.  The transaction model in WAL mode is totally different,
  so the journal modes are as well.

> Because of the architecture change in WAL, I was hoping that the
> durability can still be preserved while using NORMAL.

  WAL plays by a slightly different set of rules.  The docs describe
  this fairly well, but from the sound of it you need FULL for
  durability.  On the other hand, WAL requires fewer write to commit a
  transaction, so (if I'm reading this correctly) FULL in WAL mode is
  much faster than FULL in non-WAL mode.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Keith Chew 
Hi Jay

>   http://www.sqlite.org/pragma.html#pragma_synchronous
>
>   When synchronous is FULL (2), the SQLite database engine will use
>   the xSync method of the VFS to ensure that all content is safely
>   written to the disk surface prior to continuing. This ensures
>   that an operating system crash or power failure will not corrupt
>   the database. FULL synchronous is very safe, but it is also
>   slower. When synchronous is NORMAL (1), the SQLite database
>   engine will still sync at the most critical moments, but less
>   often than in FULL mode. There is a very small (though non-zero)
>   chance that a power failure at just the wrong time could corrupt
>   the database in NORMAL mode. But in practice, you are more likely
>   to suffer a catastrophic disk failure or some other unrecoverable
>   hardware fault.
>

>From what I have read so far, my understanding is consistent with your
explanation (except that I didn't realise corruption can happen in
NORMAL, only lost of data). Regardless, I would really like to hear
from a developer that the above paragraph also applies to the WAL
journal mode, and not just the older journal modes, since WAL was
introduced later in 3.7 onwards. Because of the architecture change in
WAL, I was hoping that the durability can still be preserved while
using NORMAL.

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

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Jay A. Kreibich 
On Tue, Sep 11, 2012 at 01:58:23AM +0100, Simon Slavin scratched on the wall:
> On 11 Sep 2012, at 12:55am, Keith Chew  wrote:

> > and I know FULL (1)  will provide that. The question is why
> > NORMAL (1) cannot provide the same.
> 
> Because NORMAL doesn't flush changes to disk after every single
> transaction.  It queues a few of them up and flushes them all in one go. 

  That's not quite true.

  Committing a transaction takes more than one disk write.  As I
  understand it, in FULL mode, the disk is sync'ed after each and
  every write.  In NORMAL mode, all of the writes required to commit a
  transaction are made in quick succession, but the disk is only
  sync'ed after the last write.  In most practical situations, my guess
  is that the sync takes longer than the writes (since the writes are
  mostly to the OS file-system buffers anyways).

  That means, for a very, very short time during the final commit
  process (microseconds, most likely), there are pending buffered
  writes.  If the system were to lose power between one of these
  writes and the final sync, there is a very-small-but-non-zero
  chance the database could become corrupt.

  But the disk is still fully sync'ed, to the best of SQLite's ability,
  after each and every commit.  In NORMAL mode, commits are still fully
  durable.

> NORMAL) Faster but if you lose power more transactions are lost, but
> the database is still not corrupt.
>   FULL) Every COMMIT will take more time, but a power-failure can
> lose a maximum of one transaction.

  That's also incorrect.  In both cases, if a transaction fully
  commits, you're golden.  Transactions are fully durable.  If COMMIT
  returned success, you should not lose the transaction.

  FULL mode, to the best of the SQLite developers' ability, protects
  against all corruption, but at a very high performance cost.  NORMAL
  mode opens up an extremely small chance of corruption for a
  significant performance increase.

  The PRAGMA docs explain much of this:

  http://www.sqlite.org/pragma.html#pragma_synchronous

  When synchronous is FULL (2), the SQLite database engine will use
  the xSync method of the VFS to ensure that all content is safely
  written to the disk surface prior to continuing. This ensures
  that an operating system crash or power failure will not corrupt
  the database. FULL synchronous is very safe, but it is also
  slower. When synchronous is NORMAL (1), the SQLite database
  engine will still sync at the most critical moments, but less
  often than in FULL mode. There is a very small (though non-zero)
  chance that a power failure at just the wrong time could corrupt
  the database in NORMAL mode. But in practice, you are more likely
  to suffer a catastrophic disk failure or some other unrecoverable
  hardware fault.


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite and VS2012 RTM designer support

2012-09-10 Thread Browser IA 

> >
> > Does System.Data.SQLite support Visual Studio 2012 Ultimate RTM?
> >
>
> Not yet. It might or might not be in the future. See:
>
> https://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki#q2
>

I saw this in the FAQ, however it says nothing specific about support for VS 
2012, or designer support for VS 2012, hence my query.

> >
> > It was available in the 1.0.66 version - that version also had an
> > install.exe file in the bin\Designer directory that would
> > re-install the VS support, if needed.
> >
>
> No, it was not. That version did not even support Visual Studio 2010.
>

Um, I'm sorry, that is not consistent with...our experience. My teammates and I 
have been running 1.0.66 with full designer support in VS 2010 for well over 
the past two years, that is, as long as VS 2010 has been out! It may be the 
case that this is not an "officially supported" configuration for 1.0.66, but 
that didn't prevent it from working just fine in VS 2010, all the way up 
through today. The reason I know about the install.exe file in the bin\Designer 
directory is because we would have to use it occasionally when a "patch 
Tuesday" update would temporarily make the designer support "disappear" from VS 
2010 (it just happened with the last patch Tuesday), running this file put the 
support back, without having to reinstall SQLite or VS 2010...

> >
> > I found a similar "installer.exe" in 1.0.82's bin directory, but
> > it is different in that it doesn't try to reinstall the VS support
> > when you run it.
> >
>
> The new "installer.exe" was written from the ground up. None of the
> original code was reused. It works entirely differently now. Also,
> running it directly is not recommended, not to mention unsupported,
> because it has a very complex set of command line options needed
> to use it correctly.
>

Yes I discovered that prior to posting here, I had tried debugging it as it was 
running (BREAK=1, attach), but I have no experience with Visual Studio 
extensions, nor how to interface designers with the Visual Studio shell, so I 
quickly realized I have no idea what is "right" vs. what is "wrong" when I was 
stepping through the code.

I have found this: 
http://stackoverflow.com/questions/11291423/sqlite-with-visual-studio-2012-and-net-4-5,
 someone seems to have worked out a way to make the 1.0.66 designer support 
continue to work for VS 2012 (apparently, there is something different that 
prevents this from "just working" like it did from VS 2008 to VS 2010), but I 
don't feel comfortable with the author's solution, as it is an opaque binary 
that I have no real idea what it is doing. I'd also rather be at a more current 
version of SQLite. If I knew support for VS 2012 was coming sometime in the 
near future, that would help me decide whether to move to the latest (broken) 
release with VS 2012, or stick with 1.0.66 without a designer until such time 
as support can be added, neither situation is ideal, but I need to decide on 
one path or the other, quickly.
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite and VS2012 RTM designer support

2012-09-10 Thread Joe Mistachkin 

Browser IA wrote:
> 
> Does System.Data.SQLite support Visual Studio 2012 Ultimate RTM? 
> 

Not yet.  It might or might not be in the future.  See:

https://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki#q2

>
> It was available in the 1.0.66 version - that version also had an
install.exe
> file in the bin\Designer directory that would re-install the VS support,
if
> needed. 
>

No, it was not.  That version did not even support Visual Studio 2010.

>
> I found a similar "installer.exe" in 1.0.82's bin directory, but it is
different
> in that it doesn't try to reinstall the VS support when you run it.
>

The new "installer.exe" was written from the ground up.  None of the
original
code was reused.  It works entirely differently now.  Also, running it
directly
is not recommended, not to mention unsupported, because it has a very
complex
set of command line options needed to use it correctly.
 
--
Joe Mistachkin

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

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Simon Slavin 

On 11 Sep 2012, at 12:55am, Keith Chew  wrote:

> Durability means a transaction cannot be lost

When is a transaction a transaction.  Under the definition of ACID a 
transaction is a transaction as soon is your COMMIT returns with a non-error 
result code.

> , and I know FULL (1)
> will provide that. The question is why NORMAL (1) cannot provide the
> same.

Because NORMAL doesn't flush changes to disk after every single transaction.  
It queues a few of them up and flushes them all in one go.  This makes things 
far faster because it's the flushing to disk which takes a lot of time.  So 
your payoff equation is this:

NORMAL) Faster but if you lose power more transactions are lost, but the 
database is still not corrupt.
  FULL) Every COMMIT will take more time, but a power-failure can lose a 
maximum of one transaction.

Just to remind you, all this depends on your hardware supporting ACID.  Which 
almost no hard disks do.

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

[sqlite] System.Data.SQLite and VS2012 RTM designer support

2012-09-10 Thread Browser IA 

Does System.Data.SQLite support Visual Studio 2012 Ultimate RTM? I ran the 
installation package (sqlite-netFx40-setup-bundle-x86-2010-1.0.82.0.exe). When 
I open up Visual Studio 2012 Ultimate, and navigate to "Server Explorer" and 
"Add Connection", under the "Data source" list SQLite is not available. It was 
available in the 1.0.66 version - that version also had an install.exe file in 
the bin\Designer directory that would re-install the VS support, if needed. I 
found a similar "installer.exe" in 1.0.82's bin directory, but it is different 
in that it doesn't try to reinstall the VS support when you run it.

I have not been able to find any information on how to address the issue. Any 
help is appreciated.
Thanks

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

Re: [sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Keith Chew 
Hi Simon

Thank you for your reply.

>
> Probably worth us knowing whether you've read the 'Performance 
> Considerations' section of
>
> 
>

Yes, I have read that, along with all the threads/posts I could find
from the Internet. The thing that I cannot fully understand is how can
FULL (2) guarantee durability, but NORMAL (1) cannot. Before making an
attempt to look into the code, I just wanted to see if anyone can help
provide some explanation.

> before anyone gives an in-depth answer.  The basic difference between the two 
> settings is that FULL (2) synchronises far more frequently than NORMAL (1), 
> meaning that in the even of power loss, fewer transactions will be lost.  
> Though if your OS and hardware does perform as documented then neither mode 
> will actually lose data or cause database corruption.

Durability means a transaction cannot be lost, and I know FULL (1)
will provide that. The question is why NORMAL (1) cannot provide the
same.

I am using EXT3 with barrier=1 and write-cache disabled from the HDD,
as far as I know this is as good as it gets for making the system
reliable. So, in this case, will the NORMAL (1) actually cause data
loss on power loss? I already know that corruption will not happen, I
am just interested in the "losing transactions" or "sacrifice
durability" as suggested in various threads/posts I have read.

Regards
Keith

>
> 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] WAL, synchronous=1 and durability

2012-09-10 Thread Simon Slavin 

On 10 Sep 2012, at 9:00pm, Keith Chew  wrote:

> Using journal_mode=WAL and synchronous=2, it is my understanding (from
> the docs and my own power loss testing) that sqlite will maintain
> durability on a power loss.
> 
> My question is, if sqlite can guarantee durability with synchronous=2,
> why can't it (from reading the docs and other posts) do that with
> synchronous=1?

Probably worth us knowing whether you've read the 'Performance Considerations' 
section of



before anyone gives an in-depth answer.  The basic difference between the two 
settings is that FULL (2) synchronises far more frequently than NORMAL (1), 
meaning that in the even of power loss, fewer transactions will be lost.  
Though if your OS and hardware does perform as documented then neither mode 
will actually lose data or cause database corruption.

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

[sqlite] WAL, synchronous=1 and durability

2012-09-10 Thread Keith Chew 
Hi

Using journal_mode=WAL and synchronous=2, it is my understanding (from
the docs and my own power loss testing) that sqlite will maintain
durability on a power loss.

My question is, if sqlite can guarantee durability with synchronous=2,
why can't it (from reading the docs and other posts) do that with
synchronous=1?

They both perform the checkpoint routine, except synchronous=1 does
the checkpoints with multiple transactions in the WAL file.

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

[sqlite] Registration Open for 19th Annual Tcl/Tk Conference (Tcl'2012)

2012-09-10 Thread Andreas Kupries 
19th Annual Tcl/Tk Conference (Tcl'2012)
http://www.tcl.tk/community/tcl2012/

November 12 - 16, 2012
Sessions:
National Museum of Health and Medicine Chicago
175 W. Washington
Chicago, IL 60602

Rooms:
Holiday Inn Chicago Mart Plaza
350 West Mart Center Drive
Chicago, Illinois, USA

Map/Transport:

https://maps.google.com/maps/ms?msid=204739899073144451536.0004c144222a9036c99f6=0=41.885266,-87.633734=0.008443,0.018818
 
http://wiki.tcl.tk/28843#pagetoca7e55932


I am pleased to announce that registration for the Conference is now
open at

http://www.tcl.tk/community/tcl2012/reg.html

To book a room at the conference hotel at reduced rates please follow
the instructions on that page. Note that the offer of reduced rates
expires on October 20. Book early.

Our schedule can be found at

http://www.tcl.tk/community/tcl2012/schedule.html

Conference Committee

Clif Flynt  Noumena CorpGeneral 
Chair, Website Admin
Andreas Kupries ActiveState Software Inc.   Program 
Chair
Cyndy Lilagan   Nat. Museum of Health & Medicine, Chicago   
Site/Facilities Chair
Arjen MarkusDeltares
Brian Griffin   Mentor Graphics
Donal Fellows   University of Manchester
Gerald Lester   KnG Consulting, LLC
Jeffrey Hobbs   ActiveState Software Inc.
Kevin Kenny GE Global Research Center
Larry Virden
Mike Doyle  National Museum of Health & Medicine, Chicago
Ron Fox NSCL/FRIB Michigan State University
Steve Landers   Digital Smarties

Contact Information tclconfere...@googlegroups.com


Tcl'2012 would like to thank those who are sponsoring the conference:

ActiveState Software Inc.
Buonacorsi Foundation
Mentor Graphics
Noumena Corp.
SR Technology
Tcl Community Association

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

Re: [sqlite] instr function or equivalent

2012-09-10 Thread Igor Tandetnik 
On 9/10/2012 1:22 PM, 
kball...@kennethballard.com wrote:

Here's one that I wrote a while back:

void sqlite3_instr(sqlite3_context* pContext, int argc, sqlite3_value** argv)
{
 const char *str1 = (const char *) sqlite3_value_text(argv[0]);
 const char *str2 = (const char *) sqlite3_value_text(argv[1]);

 char *p = strstr(str1, str2);
 int nResult = 0;
 if(p != NULL)
 {
 nResult = p - str1 + 1;
 }

 sqlite3_result_int(pContext, nResult);
}


This returns an offset in bytes, not in characters. I would expect a 
reasonable instr() implementation to satisfy this identity:


substr(X, instr(X, Y), length(Y)) == Y

for all strings X and Y, or at least for those where Y does in fact 
appear in X. I don't think yours would satisfy this condition for 
strings containing non-ASCII characters.


One would probably want to study the implementation of substr and length 
before implementing instr, to make sure the three play well together.

--
Igor Tandetnik

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

Re: [sqlite] instr function or equivalent

2012-09-10 Thread Petite Abeille 

On Sep 10, 2012, at 4:21 PM, Sébastien Roux  wrote:

> I'm looking for the[in]famous sqlite "*instr"* function which doesn't exist
> (searched the web so far without success). Also searched for a
> "*position"*function, without success too!
> 
> Any idea or help? I found some threads about custom functions but no
> tutorial nor deeper explanations!

(not sure if anyone mentioned it already, but…)

Take a look at Liam Healy's extension-functions.c:

http://www.sqlite.org/contrib/download/extension-functions.c?get=25

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

Re: [sqlite] instr function or equivalent

2012-09-10 Thread kball...@kennethballard.com 
Here's one that I wrote a while back:

void sqlite3_instr(sqlite3_context* pContext, int argc, sqlite3_value** argv)
{
const char *str1 = (const char *) sqlite3_value_text(argv[0]);
const char *str2 = (const char *) sqlite3_value_text(argv[1]);

char *p = strstr(str1, str2);
int nResult = 0;
if(p != NULL)
{
nResult = p - str1 + 1;
}

sqlite3_result_int(pContext, nResult);
}


You'll need to register it as an extension function on the database connection,
but it should do the job.

Kenneth Ballard



On September 10, 2012 at 9:21 AM "Sébastien Roux" 
wrote:

> Hi,
>
> I'm looking for the[in]famous sqlite "*instr"* function which doesn't exist
> (searched the web so far without success). Also searched for a
> "*position"*function, without success too!
>
> Any idea or help? I found some threads about custom functions but no
> tutorial nor deeper explanations!
>
> Many thanks.
>
> Sébastien Roux
> ___
> 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] EXTERNAL: Re: Error: no such access mode :memory

2012-09-10 Thread Sum, Eric B 
Hi Simon,

> Please tell us how you are specifying memory access mode.  For instance, are 
> you using the URI string ?  If so, can you post the string ?  Or are you 
> passing ":memory:" along as a filename somewhere ?

Thanks for such a quick response.  I am using the URI string to specify memory 
access mode.  This is the URI string:

"file:/memory1?mode=memory"

Thanks,
Eric

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Monday, September 10, 2012 12:41 PM
To: General Discussion of SQLite Database
Subject: EXTERNAL: Re: [sqlite] Error: no such access mode :memory


On 10 Sep 2012, at 5:24pm, esum 
> wrote:

> I am
> getting the sqlite error "1: no such access mode :memory" in the
> following two situations that I would like to implement.

Please tell us how you are specifying memory access mode.  For instance, are 
you using the URI string ?  If so, can you post the string ?  Or are you 
passing ":memory:" along as a filename somewhere ?

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] instr function or equivalent

2012-09-10 Thread Igor Tandetnik 

On 9/10/2012 12:45 PM, Simon Slavin wrote:

Similar but it only every has to (recursively) look at the rightmost character. 
 'instr' has to be able to look at substrings all the way along the string.


So does replace(), which SQLite does implement.
--
Igor Tandetnik

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

Re: [sqlite] instr function or equivalent

2012-09-10 Thread Carlos Milon Silva 

Hi,
You could check the sqlite extensions from www.monkeybreadsoftware.de at:

http://www.monkeybreadsoftware.de/SQLiteExtension/functions.shtml

Best Regards,
Carlos.

Em 10/09/2012 10:21, Sébastien Roux escreveu:

Hi,

I'm looking for the[in]famous sqlite "*instr"* function which doesn't exist
(searched the web so far without success). Also searched for a
"*position"*function, without success too!

Any idea or help? I found some threads about custom functions but no
tutorial nor deeper explanations!

Many thanks.

Sébastien Roux
___
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] instr function or equivalent

2012-09-10 Thread Simon Slavin 

On 10 Sep 2012, at 5:43pm, Bart Smissaert  wrote:

> Yes, but doesn't rtrim do an instr function with the same problems as
> you mention?

Similar but it only every has to (recursively) look at the rightmost character. 
 'instr' has to be able to look at substrings all the way along the string.  
I'm not saying it's impossible, merely that it isn't a trivial three line C 
function.

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

Re: [sqlite] instr function or equivalent

2012-09-10 Thread Bart Smissaert 
Yes, but doesn't rtrim do an instr function with the same problems as
you mention?

RBS



On 9/10/12, Simon Slavin  wrote:
>
> On 10 Sep 2012, at 5:36pm, Bart Smissaert  wrote:
>
>> Yes, you are right there.
>> As rtrim incorporates an instr type of function I am not sure why there
>> is no plain instr function in SQLite. It would make things a bit simpler.
>
> Two ways to add appropriate functions to SQLite to do it.  Either supply a
> 'find substring' function which returns the character number of where the
> substring is found, or supply a GLOB-type or regexp-type 'replace' function.
>  Neither of them are trivial given that strings /may/ be 16-bit.
>
> 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] Error: no such access mode :memory

2012-09-10 Thread Simon Slavin 

On 10 Sep 2012, at 5:24pm, esum  wrote:

> I am
> getting the sqlite error "1: no such access mode :memory" in the following
> two situations that I would like to implement.

Please tell us how you are specifying memory access mode.  For instance, are 
you using the URI string ?  If so, can you post the string ?  Or are you 
passing ":memory:" along as a filename somewhere ?

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

Re: [sqlite] instr function or equivalent

2012-09-10 Thread Simon Slavin 

On 10 Sep 2012, at 5:36pm, Bart Smissaert  wrote:

> Yes, you are right there.
> As rtrim incorporates an instr type of function I am not sure why there
> is no plain instr function in SQLite. It would make things a bit simpler.

Two ways to add appropriate functions to SQLite to do it.  Either supply a 
'find substring' function which returns the character number of where the 
substring is found, or supply a GLOB-type or regexp-type 'replace' function.  
Neither of them are trivial given that strings /may/ be 16-bit.

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

Re: [sqlite] instr function or equivalent

2012-09-10 Thread Bart Smissaert 
Yes, you are right there.
As rtrim incorporates an instr type of function I am not sure why there
is no plain instr function in SQLite. It would make things a bit simpler.

RBS


On 9/10/12, Black, Michael (IS)  wrote:
> It might be faster but it doesn't work for anybody who has any letters in
> "from" in their name.
>
> sqlite> insert into t values('132|2012-09-07|Logging in user [tom] from
> [10.169.22.59]');
> sqlite> select rtrim(s,' from [.0123456789]') || ']' from t;
> Logging in user [aa]
> 194|2012-09-07|Logging in user [a]
> 160|2012-09-04|Logging in user [aaa]
> 136|2012-09-07|Logging in user [aaa]
> 132|2012-09-07|Logging in user [aaa]
> 132|2012-09-07|Logging in user [t]
>
> The original way still works just fine.
> sqlite> select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
> Logging in user [aa]
> 194|2012-09-07|Logging in user [a]
> 160|2012-09-04|Logging in user [aaa]
> 136|2012-09-07|Logging in user [aaa]
> 132|2012-09-07|Logging in user [aaa]
> 132|2012-09-07|Logging in user [tom]
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> You have to be very careful when parsing char sets like this to ensure your
> barriers are valid.
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Bart Smissaert [bart.smissa...@gmail.com]
> Sent: Monday, September 10, 2012 11:19 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] instr function or equivalent
>
> This is slightly faster:
>
> select rtrim(s,' from [.0123456789]') || ']' from t
>
> RBS
>
>
> On 9/10/12, Bart Smissaert  wrote:
>> Nice one! Works here.
>>
>> RBS
>>
>>
>> On 9/10/12, Black, Michael (IS)  wrote:
>>> Does this work for you?
>>>
>>> SQLite version 3.7.13 2012-06-11 02:05:22
>>> Enter ".help" for instructions
>>> Enter SQL statements terminated with a ";"
>>> sqlite> create table t(s);
>>> sqlite> insert into t values('Logging in user [aa] from
>>> [10.165.69.247]');
>>> sqlite> insert into t values('194|2012-09-07|Logging in user [a]
>>> from
>>> [10.296.44.163]');
>>> sqlite> insert into t values('160|2012-09-04|Logging in user [aaa]
>>> from
>>> [10.164.69.248]');
>>> sqlite> insert into t values('136|2012-09-07|Logging in user [aaa] from
>>> [10.168.59.169]');
>>> sqlite> insert into t values('132|2012-09-07|Logging in user
>>> [aaa]
>>> from [10.169.22.58]');
>>> sqlite> select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
>>> Logging in user [aa]
>>> 194|2012-09-07|Logging in user [a]
>>> 160|2012-09-04|Logging in user [aaa]
>>> 136|2012-09-07|Logging in user [aaa]
>>> 132|2012-09-07|Logging in user [aaa]
>>>
>>> Michael D. Black
>>> Senior Scientist
>>> Advanced Analytics Directorate
>>> Advanced GEOINT Solutions Operating Unit
>>> Northrop Grumman Information Systems
>>>
>>> 
>>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>>> on
>>> behalf of Sébastien Roux [roux.sebast...@gmail.com]
>>> Sent: Monday, September 10, 2012 10:22 AM
>>> To: General Discussion of SQLite Database
>>> Subject: EXT :Re: [sqlite] instr function or equivalent
>>>
>>> Sad! Would you have any link toward SQLite's user defined SQLite
>>> function?
>>>
>>> Many thanks.
>>>
>>> Sébastien Roux
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] instr function or equivalent

2012-09-10 Thread Black, Michael (IS) 
It might be faster but it doesn't work for anybody who has any letters in 
"from" in their name.

sqlite> insert into t values('132|2012-09-07|Logging in user [tom] from 
[10.169.22.59]');
sqlite> select rtrim(s,' from [.0123456789]') || ']' from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [t]

The original way still works just fine.
sqlite> select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [tom]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems

You have to be very careful when parsing char sets like this to ensure your 
barriers are valid.

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Monday, September 10, 2012 11:19 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent

This is slightly faster:

select rtrim(s,' from [.0123456789]') || ']' from t

RBS


On 9/10/12, Bart Smissaert  wrote:
> Nice one! Works here.
>
> RBS
>
>
> On 9/10/12, Black, Michael (IS)  wrote:
>> Does this work for you?
>>
>> SQLite version 3.7.13 2012-06-11 02:05:22
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> create table t(s);
>> sqlite> insert into t values('Logging in user [aa] from
>> [10.165.69.247]');
>> sqlite> insert into t values('194|2012-09-07|Logging in user [a] from
>> [10.296.44.163]');
>> sqlite> insert into t values('160|2012-09-04|Logging in user [aaa]
>> from
>> [10.164.69.248]');
>> sqlite> insert into t values('136|2012-09-07|Logging in user [aaa] from
>> [10.168.59.169]');
>> sqlite> insert into t values('132|2012-09-07|Logging in user
>> [aaa]
>> from [10.169.22.58]');
>> sqlite> select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
>> Logging in user [aa]
>> 194|2012-09-07|Logging in user [a]
>> 160|2012-09-04|Logging in user [aaa]
>> 136|2012-09-07|Logging in user [aaa]
>> 132|2012-09-07|Logging in user [aaa]
>>
>> Michael D. Black
>> Senior Scientist
>> Advanced Analytics Directorate
>> Advanced GEOINT Solutions Operating Unit
>> Northrop Grumman Information Systems
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> on
>> behalf of Sébastien Roux [roux.sebast...@gmail.com]
>> Sent: Monday, September 10, 2012 10:22 AM
>> To: General Discussion of SQLite Database
>> Subject: EXT :Re: [sqlite] instr function or equivalent
>>
>> Sad! Would you have any link toward SQLite's user defined SQLite
>> function?
>>
>> Many thanks.
>>
>> Sébastien Roux
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error: no such access mode :memory

2012-09-10 Thread esum 
Hi,

I am using sqlite version 3.7.13 in a multithreaded application.  I am
getting the sqlite error "1: no such access mode :memory" in the following
two situations that I would like to implement.  I would like to get
situation 1 working the most.

1. I open an in memory database with shared_cache enabled using the URI file
syntax.  I then attach the sqlite tracer with the sqlite_trace () function
to all connections to this in memory database to log all transactions
performed on the database.  If I try to start any transaction at all in
either a multithreaded or singlethreaded environment, I get "1: no such
access mode :memory".  When I remove the tracer from the connections, all
database operations work fine.  When I open an on disk database with the
tracer attached, all database operations and trace operations work fine.

2. I try to maintain two databases.  One of them is an in memory one with
the same configurations listed above, and the other is an on disk database
using the sqlite asynchronous io module (no tracer is involved here).  The
sqlite asynchronous io vfs is attached only to the on disk database and not
the in memory one.  When I try to start any transaction with the in memory
database, I get the same "1: no such access mode :memory" error.  The on
disk database seems to be ok.

Any ideas on why I'm getting this error in these two situations especially
situation 1?  Am I just using a setup that involves incompatible elements?

Thanks,
Eric



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Error-no-such-access-mode-memory-tp64137.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] instr function or equivalent

2012-09-10 Thread Bart Smissaert 
This is slightly faster:

select rtrim(s,' from [.0123456789]') || ']' from t

RBS


On 9/10/12, Bart Smissaert  wrote:
> Nice one! Works here.
>
> RBS
>
>
> On 9/10/12, Black, Michael (IS)  wrote:
>> Does this work for you?
>>
>> SQLite version 3.7.13 2012-06-11 02:05:22
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> create table t(s);
>> sqlite> insert into t values('Logging in user [aa] from
>> [10.165.69.247]');
>> sqlite> insert into t values('194|2012-09-07|Logging in user [a] from
>> [10.296.44.163]');
>> sqlite> insert into t values('160|2012-09-04|Logging in user [aaa]
>> from
>> [10.164.69.248]');
>> sqlite> insert into t values('136|2012-09-07|Logging in user [aaa] from
>> [10.168.59.169]');
>> sqlite> insert into t values('132|2012-09-07|Logging in user
>> [aaa]
>> from [10.169.22.58]');
>> sqlite> select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
>> Logging in user [aa]
>> 194|2012-09-07|Logging in user [a]
>> 160|2012-09-04|Logging in user [aaa]
>> 136|2012-09-07|Logging in user [aaa]
>> 132|2012-09-07|Logging in user [aaa]
>>
>> Michael D. Black
>> Senior Scientist
>> Advanced Analytics Directorate
>> Advanced GEOINT Solutions Operating Unit
>> Northrop Grumman Information Systems
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> on
>> behalf of Sébastien Roux [roux.sebast...@gmail.com]
>> Sent: Monday, September 10, 2012 10:22 AM
>> To: General Discussion of SQLite Database
>> Subject: EXT :Re: [sqlite] instr function or equivalent
>>
>> Sad! Would you have any link toward SQLite's user defined SQLite
>> function?
>>
>> Many thanks.
>>
>> Sébastien Roux
>>
>>
>> ___
>> 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] instr function or equivalent

2012-09-10 Thread Bart Smissaert 
Nice one! Works here.

RBS


On 9/10/12, Black, Michael (IS)  wrote:
> Does this work for you?
>
> SQLite version 3.7.13 2012-06-11 02:05:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t(s);
> sqlite> insert into t values('Logging in user [aa] from
> [10.165.69.247]');
> sqlite> insert into t values('194|2012-09-07|Logging in user [a] from
> [10.296.44.163]');
> sqlite> insert into t values('160|2012-09-04|Logging in user [aaa] from
> [10.164.69.248]');
> sqlite> insert into t values('136|2012-09-07|Logging in user [aaa] from
> [10.168.59.169]');
> sqlite> insert into t values('132|2012-09-07|Logging in user [aaa]
> from [10.169.22.58]');
> sqlite> select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
> Logging in user [aa]
> 194|2012-09-07|Logging in user [a]
> 160|2012-09-04|Logging in user [aaa]
> 136|2012-09-07|Logging in user [aaa]
> 132|2012-09-07|Logging in user [aaa]
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Sébastien Roux [roux.sebast...@gmail.com]
> Sent: Monday, September 10, 2012 10:22 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] instr function or equivalent
>
> Sad! Would you have any link toward SQLite's user defined SQLite function?
>
> Many thanks.
>
> Sébastien Roux
>
>
> ___
> 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] instr function or equivalent

2012-09-10 Thread Sébastien Roux 
Enormous! Thanks it works!

Sébastien Roux

2012/9/10 Black, Michael (IS) 

> Does this work for you?
>
> SQLite version 3.7.13 2012-06-11 02:05:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t(s);
> sqlite> insert into t values('Logging in user [aa] from
> [10.165.69.247]');
> sqlite> insert into t values('194|2012-09-07|Logging in user [a] from
> [10.296.44.163]');
> sqlite> insert into t values('160|2012-09-04|Logging in user [aaa]
> from [10.164.69.248]');
> sqlite> insert into t values('136|2012-09-07|Logging in user [aaa] from
> [10.168.59.169]');
> sqlite> insert into t values('132|2012-09-07|Logging in user [aaa]
> from [10.169.22.58]');
> sqlite> select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
> Logging in user [aa]
> 194|2012-09-07|Logging in user [a]
> 160|2012-09-04|Logging in user [aaa]
> 136|2012-09-07|Logging in user [aaa]
> 132|2012-09-07|Logging in user [aaa]
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Sébastien Roux [roux.sebast...@gmail.com]
> Sent: Monday, September 10, 2012 10:22 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] instr function or equivalent
>
> Sad! Would you have any link toward SQLite's user defined SQLite function?
>
> Many thanks.
>
> Sébastien Roux
>
>
> ___
> 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] instr function or equivalent

2012-09-10 Thread Black, Michael (IS) 
Does this work for you?

SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(s);
sqlite> insert into t values('Logging in user [aa] from 
[10.165.69.247]');
sqlite> insert into t values('194|2012-09-07|Logging in user [a] from 
[10.296.44.163]');
sqlite> insert into t values('160|2012-09-04|Logging in user [aaa] from 
[10.164.69.248]');
sqlite> insert into t values('136|2012-09-07|Logging in user [aaa] from 
[10.168.59.169]');
sqlite> insert into t values('132|2012-09-07|Logging in user [aaa] from 
[10.169.22.58]');
sqlite> select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Sébastien Roux [roux.sebast...@gmail.com]
Sent: Monday, September 10, 2012 10:22 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent

Sad! Would you have any link toward SQLite's user defined SQLite function?

Many thanks.

Sébastien Roux


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

Re: [sqlite] instr function or equivalent

2012-09-10 Thread Sébastien Roux 
Sad! Would you have any link toward SQLite's user defined SQLite function?

Many thanks.

Sébastien Roux


2012/9/10 Bart Smissaert 

> Ah, OK. I have a feeling that needs to be done either in your application
> code
> or with a user defined SQLite function.
> Somebody may prove me wrong.
>
> RBS
>
>
>
> On 9/10/12, Sébastien Roux  wrote:
> > I want to remove/trim characters strating from "from" til "]"
> >
> > Logging in user [aa] from [10.165.69.247]
> > 194|2012-09-07|Logging in user [a] from [10.296.44.163]
> > 160|2012-09-04|Logging in user [aaa] from [10.164.69.248]
> > 136|2012-09-07|Logging in user [aaa] from [10.168.59.169]
> > 132|2012-09-07|Logging in user [aaa] from [10.169.22.58]
> >
> > Sébastien Roux
> >
> >
> > 2012/9/10 Bart Smissaert 
> >
> >> Could your application supply the values in code?
> >> What exactly are you trying to do?
> >>
> >> RBS
> >>
> >>
> >>
> >> On 9/10/12, Sébastien Roux  wrote:
> >> > Thanks Bart but substr require hard coded positions I guess, I need to
> >> get
> >> > this position dynamically!
> >> >
> >> > Sébastien Roux
> >> >
> >> > 2012/9/10 Bart Smissaert 
> >> >
> >> >> http://www.sqlite.org/lang_corefunc.html
> >> >>
> >> >> Look at substr
> >> >>
> >> >>
> >> >> RBS
> >> >>
> >> >>
> >> >> On 9/10/12, Sébastien Roux  wrote:
> >> >> > Hi,
> >> >> >
> >> >> > I'm looking for the[in]famous sqlite "*instr"* function which
> >> >> > doesn't
> >> >> exist
> >> >> > (searched the web so far without success). Also searched for a
> >> >> > "*position"*function, without success too!
> >> >> >
> >> >> > Any idea or help? I found some threads about custom functions but
> no
> >> >> > tutorial nor deeper explanations!
> >> >> >
> >> >> > Many thanks.
> >> >> >
> >> >> > Sébastien Roux
> >> >> > ___
> >> >> > sqlite-users mailing list
> >> >> > sqlite-users@sqlite.org
> >> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >> >
> >> >> ___
> >> >> sqlite-users mailing list
> >> >> sqlite-users@sqlite.org
> >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >>
> >> > ___
> >> > sqlite-users mailing list
> >> > sqlite-users@sqlite.org
> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> 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] instr function or equivalent

2012-09-10 Thread Bart Smissaert 
Ah, OK. I have a feeling that needs to be done either in your application code
or with a user defined SQLite function.
Somebody may prove me wrong.

RBS



On 9/10/12, Sébastien Roux  wrote:
> I want to remove/trim characters strating from "from" til "]"
>
> Logging in user [aa] from [10.165.69.247]
> 194|2012-09-07|Logging in user [a] from [10.296.44.163]
> 160|2012-09-04|Logging in user [aaa] from [10.164.69.248]
> 136|2012-09-07|Logging in user [aaa] from [10.168.59.169]
> 132|2012-09-07|Logging in user [aaa] from [10.169.22.58]
>
> Sébastien Roux
>
>
> 2012/9/10 Bart Smissaert 
>
>> Could your application supply the values in code?
>> What exactly are you trying to do?
>>
>> RBS
>>
>>
>>
>> On 9/10/12, Sébastien Roux  wrote:
>> > Thanks Bart but substr require hard coded positions I guess, I need to
>> get
>> > this position dynamically!
>> >
>> > Sébastien Roux
>> >
>> > 2012/9/10 Bart Smissaert 
>> >
>> >> http://www.sqlite.org/lang_corefunc.html
>> >>
>> >> Look at substr
>> >>
>> >>
>> >> RBS
>> >>
>> >>
>> >> On 9/10/12, Sébastien Roux  wrote:
>> >> > Hi,
>> >> >
>> >> > I'm looking for the[in]famous sqlite "*instr"* function which
>> >> > doesn't
>> >> exist
>> >> > (searched the web so far without success). Also searched for a
>> >> > "*position"*function, without success too!
>> >> >
>> >> > Any idea or help? I found some threads about custom functions but no
>> >> > tutorial nor deeper explanations!
>> >> >
>> >> > Many thanks.
>> >> >
>> >> > Sébastien Roux
>> >> > ___
>> >> > sqlite-users mailing list
>> >> > sqlite-users@sqlite.org
>> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >> >
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@sqlite.org
>> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] instr function or equivalent

2012-09-10 Thread Sébastien Roux 
I want to remove/trim characters strating from "from" til "]"

Logging in user [aa] from [10.165.69.247]
194|2012-09-07|Logging in user [a] from [10.296.44.163]
160|2012-09-04|Logging in user [aaa] from [10.164.69.248]
136|2012-09-07|Logging in user [aaa] from [10.168.59.169]
132|2012-09-07|Logging in user [aaa] from [10.169.22.58]

Sébastien Roux


2012/9/10 Bart Smissaert 

> Could your application supply the values in code?
> What exactly are you trying to do?
>
> RBS
>
>
>
> On 9/10/12, Sébastien Roux  wrote:
> > Thanks Bart but substr require hard coded positions I guess, I need to
> get
> > this position dynamically!
> >
> > Sébastien Roux
> >
> > 2012/9/10 Bart Smissaert 
> >
> >> http://www.sqlite.org/lang_corefunc.html
> >>
> >> Look at substr
> >>
> >>
> >> RBS
> >>
> >>
> >> On 9/10/12, Sébastien Roux  wrote:
> >> > Hi,
> >> >
> >> > I'm looking for the[in]famous sqlite "*instr"* function which doesn't
> >> exist
> >> > (searched the web so far without success). Also searched for a
> >> > "*position"*function, without success too!
> >> >
> >> > Any idea or help? I found some threads about custom functions but no
> >> > tutorial nor deeper explanations!
> >> >
> >> > Many thanks.
> >> >
> >> > Sébastien Roux
> >> > ___
> >> > sqlite-users mailing list
> >> > sqlite-users@sqlite.org
> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] instr function or equivalent

2012-09-10 Thread Bart Smissaert 
Could your application supply the values in code?
What exactly are you trying to do?

RBS



On 9/10/12, Sébastien Roux  wrote:
> Thanks Bart but substr require hard coded positions I guess, I need to get
> this position dynamically!
>
> Sébastien Roux
>
> 2012/9/10 Bart Smissaert 
>
>> http://www.sqlite.org/lang_corefunc.html
>>
>> Look at substr
>>
>>
>> RBS
>>
>>
>> On 9/10/12, Sébastien Roux  wrote:
>> > Hi,
>> >
>> > I'm looking for the[in]famous sqlite "*instr"* function which doesn't
>> exist
>> > (searched the web so far without success). Also searched for a
>> > "*position"*function, without success too!
>> >
>> > Any idea or help? I found some threads about custom functions but no
>> > tutorial nor deeper explanations!
>> >
>> > Many thanks.
>> >
>> > Sébastien Roux
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] instr function or equivalent

2012-09-10 Thread Sébastien Roux 
Thanks Bart but substr require hard coded positions I guess, I need to get
this position dynamically!

Sébastien Roux

2012/9/10 Bart Smissaert 

> http://www.sqlite.org/lang_corefunc.html
>
> Look at substr
>
>
> RBS
>
>
> On 9/10/12, Sébastien Roux  wrote:
> > Hi,
> >
> > I'm looking for the[in]famous sqlite "*instr"* function which doesn't
> exist
> > (searched the web so far without success). Also searched for a
> > "*position"*function, without success too!
> >
> > Any idea or help? I found some threads about custom functions but no
> > tutorial nor deeper explanations!
> >
> > Many thanks.
> >
> > Sébastien Roux
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] instr function or equivalent

2012-09-10 Thread Bart Smissaert 
http://www.sqlite.org/lang_corefunc.html

Look at substr


RBS


On 9/10/12, Sébastien Roux  wrote:
> Hi,
>
> I'm looking for the[in]famous sqlite "*instr"* function which doesn't exist
> (searched the web so far without success). Also searched for a
> "*position"*function, without success too!
>
> Any idea or help? I found some threads about custom functions but no
> tutorial nor deeper explanations!
>
> Many thanks.
>
> Sébastien Roux
> ___
> 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] instr function or equivalent

2012-09-10 Thread Sébastien Roux 
Hi,

I'm looking for the[in]famous sqlite "*instr"* function which doesn't exist
(searched the web so far without success). Also searched for a
"*position"*function, without success too!

Any idea or help? I found some threads about custom functions but no
tutorial nor deeper explanations!

Many thanks.

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

Re: [sqlite] indexing for integer column

2012-09-10 Thread Durga D 
Hi,

   Can I create INDEX for the particular column when database journal mode
is WAL?

   Any impact If I INDEXed database with some triggers?

   Thanks in advance.

Best Regards,

On Thu, Sep 6, 2012 at 4:04 PM, Durga D  wrote:

> Thank you Kees.
>
>
> On Thu, Sep 6, 2012 at 3:34 PM, Kees Nuyt  wrote:
>
>> On Thu, 6 Sep 2012 12:02:03 +0400, Durga D  wrote:
>>
>> >Hi All,
>> >
>> >Somehow  "original author" missed one INTETER column as searchable
>> >field ( like unique or primary key) in the table. When execute queries
>> >based on this integer field in where clause/joins, huge performance hit.
>> >So, I am planning to add INDEXING for this integer column.
>> >
>> >Is there any alternative/best solution?
>>
>> Make it a UNIQUE INDEX if possible.
>>
>> Yet, the best thing to do is to refactor the schema.
>>
>> sqlite3 olddbfile .dump >dumpfile
>> edit dumpfile to adjust schema
>> sqlite3 newdbfile > rename olddbfile to olddbfile.ORIGINAL
>> rename newdbfile to olddbfile
>> start the application, test thoroughly
>>
>> Inform the original author, so a new release is correct from the
>> beginning.
>>
>>
>> --
>> Regards,
>>
>> Kees Nuyt
>>
>> ___
>> 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] Possible bug - journal_mode DELETE/TRUNCATE

2012-09-10 Thread Daniel Frimerman 
Yes! The journal file was indeed there.  I deleted it before openning the
database, and read 50 records. I repeated the process; it's consistent.

The way I see it that even though the synchronous setting is FULL, there is
no API to tell the OS to physically delete a file from disk, just like
there is no API to physically force a file to truncate.  Well, maybe there
is a way (or an API), but sqlite doesn't presently do it.  The only way
sqlite forces physical writes to disk is via FlushFileBuffers(). So with:
journal_mode=DELETE: no buffers written, the OS delays in deleting the
file, and during a hard reset, the journal file will remain -- and in my
case, that's causing 1 record to get lost.
journal_mode=TRUNCATE: no buffers written, the OS delays in truncating the
file; same as above.
journal_mode=PERSIST: header information is physically written to disk.
journal_mode=WAL (synchronous=FULL): all data is physically written to
disk, no journal files are truncated or deleted.

I think your asking the question indicates you suspected what I would find,
no?

If I don't opt for WAL, I will use the PERSIST mode instead of the default
DELETE mode.

On Mon, Sep 10, 2012 at 6:47 AM, Dan Kennedy  wrote:

> On 09/10/2012 03:15 AM, Daniel Frimerman wrote:
>
>> My apologies about the attachment; should have known better.
>> This should be better:
>> https://dl.dropbox.com/u/**50838941/SQLite3_Test.zip
>>
>> I only get the problem with DELETE and TRUNCATE journal_mode (synchronous
>> set to NORMAL or FULL), but not with PERSIST (synchronous set to NORMAL or
>> FULL) or WAL (synchronous set to FULL).
>>
>
> When using "PRAGMA journal_mode=DELETE", after you reboot the
> system is there a *-journal file present in the directory
> next to your database file?
>
> If so and you rename it before opening the database, are all
> 50 records present?
>
>
>
>
>> The reason I think there has to be 50 rows is because on FULL mode for
>> example, the I/O buffers are flushed, and it's consistently missing 1
>> record as opposed to any other number of records. I insert 50 records, the
>> sqlite3 command line utility executes the script, and I get to a stage
>> where I can write commands to the console.  What I mean is that as far as
>> sqlite is concerned, it has written the data to disk and also instructed
>> the OS to flush the buffers.  Perhaps it finalises something from the last
>> insert only when the next insert comes in?
>> It could be a coincidence of some sort, by sheer difference of
>> implementation of different journals that the "problem" doesn't show
>> itself
>> with PERSIST or WAL journals.
>>
>> I turned off host I/O cache in VirtualBox, so any writes by the guest OS
>> have to be physically written to the virtual disk on the host.  The guest
>> has "standard" I/O caching on disk, but FlushFileBuffers() should have
>> done
>> its job.  There is no reason why any link in the chain should report data
>> written to disk without actually doing it, unless there is a problem.
>>
>> I suppose I should just go ahead and test it on the physical PC.  If there
>> is a problem with that also, then I suppose I could blame the OS for not
>> flushing stuff to disk properly.
>>
>> Now I gotta find me a machine
>>
>> Regards,
>>
>> Dan
>>
>>
>>
>> On Sun, Sep 9, 2012 at 8:14 PM, Pavel Ivanov  wrote:
>>
>>  Note: attachments are stripped out of this list. So if you want for
>>> anybody else to see your zip file you need to put it on some website
>>> and post link here.
>>>
>>> About the problem you have: I wonder how are you sure that there
>>> should be 50 rows in the database and not 49? If you are resetting the
>>> OS before it has a chance to properly commit everything then it's okay
>>> for last transaction to be missing. But if you are sure that you are
>>> resetting the OS after everything is settled then maybe you are not
>>> committing your last transaction properly? Or maybe there's some bug
>>> in your virtualization layer and you need to try the same thing on a
>>> real hardware?
>>>
>>>
>>> Pavel
>>>
>>>
>>> On Sun, Sep 9, 2012 at 8:50 AM, Daniel Frimerman
>>>   wrote:
>>>
 I am fairly new to sqlite and as a result of not reading the manual and

>>> not
>>>
 doing some performance testing, I got punished somewhat.  I did not
 anticipate that on journal_mode=DELETE and synchronous=FULL, I would get

>>> no
>>>
 more than 5 inserts (in auto-commit mode) per second.  It crippled a
 certain batch operation on a live system.  That's water under the
 bridge;
 it's the testing afterwards and a potential minor problem that I found
 is
 what I am now interested in.

 I tested all journal mode settings for sqlite, as well as the
 synchronous
 setting.  Some things that I discovered were not so obvious from reading
 the docs, such as the