[sqlite] Table Locked Error Recovery

[Alex Katebi]

Does anyone know what the course of action is for recovering from a table
locked error?

I dropped a table and I got the error code 6 (Table Locked Error). After two
weeks of pulling my hair I guessed that the error was not for my table being
dropped. It was for the sqlite_master table being modified by another
thread. If this is the case then I have two questions:

1) sqlite_master table does not belong to the application (for writing) it
belongs to the sqlite library. So sqlite should take care of this recovery
not the application.  The application should not even get this error because
the application had nothing to do with the sqlite_master table directly.

2) The sqlite3_errmsg(db) should mention which table is locked (if it is
possible). So that the next person does not spend two weeks on this.

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

[sqlite] SQLite in memory database concurrency model

[Alex Katebi]

There is a lot of information about disk file concurrency model.
I have not found much information regarding the in memory database
concurrency model.

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

Re: [sqlite] sqlite3_stmt declaration

[Alex Katebi]

Write a C test.c program that uses the desired type. Then do "gcc -E test.c
> test.txt".
This will expand/flatten all the macros. Open the test.txt file and look for
the type.

On Mon, Aug 25, 2008 at 5:30 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote:

> Good afternoon list,
>
> I'm attempting to wrap SQLite with Managed C++ and I'm getting some
> compiler warnings as the compiler/linker is have trouble finding the
> declaration of the structure 'sqlite3_stmt', I've tried looking for it
> manually but I can't find it either all I can find is a typedef on line
> 2569 of slite3.h.  This isn't enough to stop the CLR compiler form
> complaining, I had a similar warning with the structure 'sqlite3' but
> including sqlite3Int.h resolved that warning as the structure is defined
> there, however tracking down the header file that defines 'sqlite3_stmt'
> seems to be leading no where...
>
> Cheers,
>
> Daniel Brown | Software Engineer @ EA Canada
> "The best laid schemes o' mice an' men, gang aft agley"
>
>
> ___
> 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] Table Lock Error Question

[Alex Katebi]

Hi,

  What can cause an in memory database a table lock error besides not
finalizing statements?

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

Re: [sqlite] database and table lock

[Alex Katebi]

Just a note. If you change the #define settings to the below values you also
get table locks

#define MAX_THREAD 100
#define MAX_LOOP 100


Thanks,
-Alex

On Sat, Aug 23, 2008 at 3:35 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:

> Hi,
>
> I am getting a table lock for no apparent reason in my application.
>
> To reproduce this I have written a test file that behaves the same way with
> the difference that it reports database lock instead of table lock. My
> application database is in memory the test uses a file database. I think it
> is the same locking issue.
>
> Attached is "test.c"
> gcc -Wall test.c -lsqlite3 -o test
> run two test at the same time you will see the problem.
>
> Thanks,
> -Alex
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database and table lock

[Alex Katebi]

Hi,

I am getting a table lock for no apparent reason in my application.

To reproduce this I have written a test file that behaves the same way with
the difference that it reports database lock instead of table lock. My
application database is in memory the test uses a file database. I think it
is the same locking issue.

Attached is "test.c"
gcc -Wall test.c -lsqlite3 -o test
run two test at the same time you will see the problem.

Thanks,
-Alex
// gcc -Wall test.c -lsqlite3

#include 
#include 
#include 
#include 
#include 
#include 

sqlite3* gdb;

int ass(int a, int b)
{
  if(a != b)
{
  printf("Expected %d Got %d\n", a, b);
  printf("%s\n", sqlite3_errmsg(gdb));
  return 0;
}
  return 1;
}


#define MAX_LOOP 1
#define MAX_THREAD 1

void xTrace(void* self, const char* zSql)
{
  printf(" %d %s\n", (int) self, zSql);
}

void init_trace(void* self)
{
  sqlite3_trace(gdb, xTrace, self);
}

void* test(void* val)
{
  int i, id = (int)val;
  for(i = 0 ; i < MAX_LOOP ; i++)
{
  char* zSql;
  sqlite3_stmt* stmt;

  char* tbl = sqlite3_mprintf("tbl_%d_%d", getpid(), id);

  zSql = sqlite3_mprintf("create table %s(a)", tbl);
  assert(ass(SQLITE_OK, sqlite3_prepare_v2(gdb, zSql, -1, , 0)));
  assert(ass(SQLITE_DONE, sqlite3_step(stmt)));
  assert(ass(SQLITE_OK, sqlite3_finalize(stmt)));
  sqlite3_free(zSql);
  //printf("create stmt %p\n", stmt);

  zSql = sqlite3_mprintf("insert into %s values(7)", tbl);
  assert(ass(SQLITE_OK, sqlite3_prepare_v2(gdb, zSql, -1, , 0)));
  assert(ass(SQLITE_DONE, sqlite3_step(stmt)));
  assert(ass(SQLITE_OK, sqlite3_finalize(stmt))); 
  sqlite3_free(zSql);
  //printf("insert stmt %p\n", stmt);

  zSql = sqlite3_mprintf("drop table %s", tbl);
  assert(ass(SQLITE_OK, sqlite3_prepare_v2(gdb, zSql, -1, , 0)));
  assert(ass(SQLITE_DONE, sqlite3_step(stmt)));
  assert(ass(SQLITE_OK, sqlite3_finalize(stmt))); 
  sqlite3_free(zSql);
  //printf("drop stmt %p\n", stmt);

  //zSql = sqlite3_mprintf("select tbl_name from sqlite_master where 
tbl_name=%Q", tbl);
  //assert(ass(SQLITE_OK, sqlite3_prepare_v2(gdb, zSql, -1, , 0)));
  //assert(ass(SQLITE_DONE, sqlite3_step(stmt)));
  //assert(ass(SQLITE_OK, sqlite3_finalize(stmt)));
  //sqlite3_free(zSql);
  //printf("select stmt %p\n", stmt);

  sqlite3_free(tbl);
}
  return 0;
}

int main()
{
  char* file = "test.db";
  //char* file = "/dev/shm/test.db";
  assert(ass(SQLITE_OK, sqlite3_open(file, )));
  init_trace(0);

  int i;
  pthread_t threads[MAX_THREAD];
  for(i = 0 ; i < MAX_THREAD ; i++)
{
  assert(pthread_create([i], 0, test, (void*) i) == 0);  
}
  for(i = 0 ; i < MAX_THREAD ; i++)
{
  assert(pthread_join(threads[i], 0) == 0);
}

  sqlite3_stmt *pStmt;
  while( (pStmt = sqlite3_next_stmt(gdb, 0))!=0 )
{
  static int count = 0;
  printf("%d: next %p\n", count++, pStmt);
  sqlite3_finalize(pStmt);
}

  assert(ass(SQLITE_OK, sqlite3_close(gdb)));
  return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite freestanding.

[Alex Katebi]

Most people are using it the way you described. I assume they had success
since it is the most widely deployed data base engine.

On Thu, Aug 21, 2008 at 12:07 PM, Ricardo Hawerroth Wiggers - Terceiro <
[EMAIL PROTECTED]> wrote:

> Hello.
>
> Has anyone used sqlite in a freestanding embedded environment? If anyone
> had success with it, how about the footprint? And storage medium, direct
> flash access?
>
> Thanks,
> Ricardo
> ___
> 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] Multiple connection to in-memory database

[Alex Katebi]

   This was the model that I was using. But I found out that I get a table
lock for dropping tables for no reason when you do interleave steps for
different prepares. Any body needs a proof I can create a test case for you.
No I did not forget to do finalize for the prior prepares.

   The better way to do this is:

   attach ':memory:' as db2
   attach ':memory:' as db3

   You can do 10 of these puppies and possibly increase it to 32 or 64
depending on your CPU. Your main database can be ':memory:' as well.

 I am going to change my test script to see if the lock problem is solved.

Thanks,
-Alex


On Tue, Aug 19, 2008 at 9:17 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:

> You don't need to open a second connection. The sole connection can be used
> from any thread.
>
>
> On Tue, Aug 19, 2008 at 6:17 PM, vincent cridlig <[EMAIL PROTECTED]>wrote:
>
>> Hi,
>>
>> I would like to use transactions from separate threads, each thread having
>> one connection to a single in-memory db.
>>
>> I just read in the sqlite doc that in-memory sqlite databases (using
>> sqlite3_open(":memory:", ...)) are private to a single connection.
>> Is there a way to open a second connection to the same in-memory database
>> (for example from a second thread)? Has someone ever tried to do (or
>> implement) that?
>>
>> Any help appreciated.
>>
>> Thanks
>> Vincent
>>
>>
>>
>>  
>> _
>> Envoyez avec Yahoo! Mail. Une boite mail plus intelligente
>> http://mail.yahoo.fr
>> ___
>> 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] Table Lock Error

[Alex Katebi]

Hi,
   For a in memory connection I get table lock error 6.
I have one in memory connection with many prepared statements for different
tables.
Depending how the statements are interleaved I get a table lock error when
dropping a table.
What is causing of this?

I did turn on tracing. I am confused by the tracing output. Sometimes it
shows some zSql statements twice. I see inserts and selects two times. Even
though my code is doing it once. Any help is appreciated.

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

Re: [sqlite] sqlite3_trace

[Alex Katebi]

Boy I was off on that one. The stmt is not going to help my case. The users
context that is already there can help me.
Sorry,
-Alex

On Wed, Aug 20, 2008 at 8:52 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:

> Hi All,
>
> I am using the sqlite3_trace to track down a bug. It is lacking a vital
> information that is needed for tracing. The stmt handle is necessary in
> order to identify related statements. Notice the sqlite3_stmt that I have
> added below.
>
> void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*, 
> sqlite3_stmt*), void*);
>
> Thankd,
> -Alex
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_trace

[Alex Katebi]

Hi All,

I am using the sqlite3_trace to track down a bug. It is lacking a vital
information that is needed for tracing. The stmt handle is necessary in
order to identify related statements. Notice the sqlite3_stmt that I have
added below.

void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*,
sqlite3_stmt*), void*);

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

Re: [sqlite] Problems building for VxWorks...

[Alex Katebi]

Write the missing functions with using the equivalent VxWorks functions.

example:

int exit(int a)
{
vxworks_exit(a);
}

On Wed, Aug 20, 2008 at 4:16 AM, Kershaw, Anthony (UK) <
[EMAIL PROTECTED]> wrote:

>
> Hi,
> Have been trying to compile (and run) the amalgamated version of sqlite
> 3.6.1 for VxWorks   5.5,
> which is proving quite trouble-some.
>
> I am currently using these switches
> -DSQLITE_OMIT_BUILTIN_TEST
> -DSQLITE_TEMP_STORE=3
> -DSQLITE_HOMEGROWN_RECURSIVE_MUTEX
> -DSQLITE_OMIT_AUTOVACUUM
> -DSQLITE_OMIT_LOAD_EXTENSION
> -DSQLITE_OMIT_LOCALTIME
> -DSQLITE_OMIT_TCL_VARIABLE
> -DNO_GETTOD
>
> Which will allow it to compile, but there are missing symbols, mainly
> because they don't occur in vxworks, the following functions don't
> exist.
> fctrl
> dup
> fsync
> access
> getpid
>
> Any work arounds people could think of would be greatly appreciated. I
> appreciate this heavily hits the locking mechanism code within sqlite...
>
> Regards,
>  Ant
>
>
>
> 
> This email and any attachments are confidential to the intended
> recipient and may also be privileged. If you are not the intended
> recipient please delete it from your system and notify the sender.
> You should not copy it or use it for any purpose nor disclose or
> distribute its contents to any other person.
> 
> ___
> 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] Multiple connection to in-memory database

[Alex Katebi]

You don't need to open a second connection. The sole connection can be used
from any thread.


On Tue, Aug 19, 2008 at 6:17 PM, vincent cridlig <[EMAIL PROTECTED]> wrote:

> Hi,
>
> I would like to use transactions from separate threads, each thread having
> one connection to a single in-memory db.
>
> I just read in the sqlite doc that in-memory sqlite databases (using
> sqlite3_open(":memory:", ...)) are private to a single connection.
> Is there a way to open a second connection to the same in-memory database
> (for example from a second thread)? Has someone ever tried to do (or
> implement) that?
>
> Any help appreciated.
>
> Thanks
> Vincent
>
>
>
>  _
> Envoyez avec Yahoo! Mail. Une boite mail plus intelligente
> http://mail.yahoo.fr
> ___
> 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] SQLite Test Scripts

[Alex Katebi]

How can I run the SQLite TCL test scripts?

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

Re: [sqlite] regarding internal design of SQLite

[Alex Katebi]

No I don't mean offline use. I mean it would be nice to have links on the
sqlite.org for all documents and resources.

On Tue, Jul 8, 2008 at 1:42 PM, Mihai Limbasan <[EMAIL PROTECTED]> wrote:

> Alex Katebi wrote:
> > Is there any way to get to all of these docs you mentioned from the home
> > page of the sqlite.org?
> > Thanks,
> > -Alex
> You mean, for offline use? If yes, then I'd click on Download, then
> scroll down to Documentation, then I'd clock on sqlite_docs_3_5_9.zip:
> http://sqlite.org/sqlite_docs_3_5_9.zip
> ___
> 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] regarding internal design of SQLite

[Alex Katebi]

Is there any way to get to all of these docs you mentioned from the home
page of the sqlite.org?
Thanks,
-Alex


On Tue, Jul 8, 2008 at 2:31 AM, Roger Binns <[EMAIL PROTECTED]> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Aditya Bhave (adbhave) wrote:
> > I am interested in learning about the internals of SQLite.
>
> The SQLite documentation page at http://sqlite.org/docs.html contains
> almost all the information you could possibly want.
>
> > How it parses
> > SQL strings, what goes on the parsing stack,
>
> A parser generator named Lemon is used which is documented at
> http://www.hwaci.com/sw/lemon/
>
> The file containing the SQLite grammar and what actions are performed is
> src/parse.y
>
> > how it executes SQL statements etc.
>
> A virtual machine is used since it provides an easy way to encode state
> (eg between calls to sqlite3_step).  The broad principles are documented
> at http://sqlite.org/vdbe.html and the machine opcodes are documented at
> http://sqlite.org/opcode.html
>
> This paper contrasts stack based vs register based virtual machines.
> SQLite used to be stack based and moved to register based a few
> revisions ago.  (Note that the virtual machine is not exposed to users
> of SQLite so the backend is irrelevant to the user, as long as it works)
>
>  http://www.usenix.org/events/vee05/full_papers/p153-yunhe.pdf
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFIcwm1mOOfHg372QQRAuVbAJ9cli9pD9enCHZr8yDDWgJ9ghsvfACgyjXe
> zIcyPCpKe1zu5TPKOFefbvY=
> =+mJS
> -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] Is this a bug?

[Alex Katebi]

I am not sure about the email server. You're probably correct.
I spent couple of painful hours on this issue. I just moved the error printf
after finalize now.

Thanks Roger!
-Alex

On Fri, Jul 4, 2008 at 7:03 PM, Roger Binns <[EMAIL PROTECTED]> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Alex Katebi wrote:
> > I was able to get the attachment myself. It could be your email server.
>
> Are you sure?  I was looking at the actual raw message.  My mail server
> is postfix with messages funneled through clamassassin and spamassassin.
>  It could be the SQLite mailing list manager that stripped out the
> attachment when adding the list trailer part.
>
> >   sqlite3_step(pStmt);
> >   printf("%s\n", sqlite3_errmsg(db));
>
> If you add sqlite3_reset(pStmt) or sqlite3_finalize(pStmt) after the
> step then you get the correct error message.  Also note that you should
> use sqlite3_prepare_v2.
>
> In the "olden days" sqlite3_step() used to only return SQLITE_ERROR and
> then you had to call reset or finalize to get the actual error code and
> message.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFIbqw6mOOfHg372QQRAmeFAKChAns1ELMHkCNdlAoBajWmQE4MnQCeJE9V
> qbl2fYgpqCwcbeCe1WAvEKU=
> =608O
> -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] Is this a bug?

[Alex Katebi]

How about this one?

On Thu, Jul 3, 2008 at 9:39 PM, Roger Binns <[EMAIL PROTECTED]> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Alex Katebi wrote:
> > sqlite3_errmsg(db) does not provide the correct error message but
> > sqlite_exec(...) does.
>
> The message gets cleared on various calls so you'll want to get it as
> early as possible after knowing there is an error.  For example before
> 3.5.9 sqlite3_clear_bindings used to clear the message.
>
> > I have attached my test code.
>
> It isn't in the message sent to list members :-)
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFIbX9JmOOfHg372QQRAnT+AJ9+y5+H+4w8R3SG8nG4vOnZTPb/9gCfWfSA
> kmDrsFz0u516u1b+QF7pDHA=
> =EJN7
> -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

[sqlite] Is this a bug?

[Alex Katebi]

sqlite3_errmsg(db) does not provide the correct error message but
sqlite_exec(...) does.
I have attached my test code. You can compile it by doing below:

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

Re: [sqlite] validate SQL Statement

[Alex Katebi]

You can use sqlite3_complete. It does not check the grammar. It just makes
makes sure that command has a semicolin.  Why you need grammar checking?

On Wed, Jul 2, 2008 at 11:03 PM, Umaa Krishnan <[EMAIL PROTECTED]> wrote:

> Hello,
>
> I was wondering if there a way in sqlite, wherein I could validate the SQL
> statement (for correct grammar, resource name - column name, table name
> etc), w/o having to do prepare.
>
> Thanks in advance
>
>
>
> --- On Wed, 7/2/08, Alex Katebi <[EMAIL PROTECTED]> wrote:
> From: Alex Katebi <[EMAIL PROTECTED]>
> Subject: Re: [sqlite] Table Level Locking
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Wednesday, July 2, 2008, 7:21 PM
>
> This is the way I hoped it should work, and it does.
> Thanks so much Igor!
> -Alex
>
> On Wed, Jul 2, 2008 at 9:39 PM, Igor Tandetnik <[EMAIL PROTECTED]>
> wrote:
>
> > "Alex Katebi" <[EMAIL PROTECTED]>
> > wrote in message
> >
> news:[EMAIL PROTECTED]<[EMAIL PROTECTED]>
> <[EMAIL PROTECTED]<[EMAIL PROTECTED]>
> >
> > > Below is a section from The Definitive Guide to SQLite book
> > > Is this not valid any more for the newer releases of SQLite.
> >
> > This is not valid anymore. See the message from Dr. Hipp in this thread:
> >
> >
> >
>
> http://archives.devshed.com/forums/databases-124/database-table-is-locked-again-sorry-2068902.html
> >
> > Igor Tandetnik
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> 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] Table Level Locking

[Alex Katebi]

This is the way I hoped it should work, and it does.
Thanks so much Igor!
-Alex

On Wed, Jul 2, 2008 at 9:39 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> "Alex Katebi" <[EMAIL PROTECTED]>
> wrote in message
> news:[EMAIL PROTECTED]<[EMAIL PROTECTED]>
> > Below is a section from The Definitive Guide to SQLite book
> > Is this not valid any more for the newer releases of SQLite.
>
> This is not valid anymore. See the message from Dr. Hipp in this thread:
>
>
> http://archives.devshed.com/forums/databases-124/database-table-is-locked-again-sorry-2068902.html
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table Level Locking

[Alex Katebi]

Below is a section from The Definitive Guide to SQLite book
Is this not valid any more for the newer releases of SQLite.
==
Table Locks
Even if you are using just one connection, there is a special edge case that
sometimes trips
people up. While you would think that two statements from the same
connection could work
on the database with impunity, there is one important exception.
When you execute a SELECT command on a table, the resulting statement object
creates a
B-tree cursor on that table. As long as there is a B-tree cursor active on a
table, other statements—
even in the same connection—cannot modify it. If they try, they will get
SQLITE_BUSY. Consider
the following example:
c = sqlite.open("foods.db")
stmt1 = c.compile('SELECT * FROM episodes LIMIT 10')
while stmt1.step() do
# Try to update the row
row = stm1.row()
stmt2 = c.compile('UPDATE episodes SET …')
# Uh oh: ain't gonna happen
stmt2.step()
end
stmt1.finalize()
stmt2.finalize ()
c.close()
CHAPTER 5 ■ DES IGN AND CONCEPTS 199
We are only using one connection here. Regardless, when stmt2.step() is
called, it won't
work because stmt1 has a cursor on the episodes table. In this case,
stmt2.step() may actually
succeed in promoting the connection's database lock to EXCLUSIVE, but it
will still return
SQLITE_BUSY. The cursor on episodes prevents it from modifying the table. In
order to get around
this, you can do one of two things:
‧ Iterate over the results with one statement, storing the information you
need in memory.
Then finalize the reading statement, and then do the updates.
‧ Store the SELECT results in a temporary table (as described in a moment)
and open the
read cursor on it. In this case you can have both a reading statement and a
writing statement
working at the same time. The reading statement's cursor will be on a
different
table—the temporary table—and won't block the updates on the main table from
the
second statement. Then when you are done, simply drop the temporary table.
When a statement is open on a table, its B-tree cursor will be removed from
the table when
one of two things happens:
‧ The statement reaches the end of the result set. When this happens, step()
will automatically
close the statement's cursor(s). In VDBE terms, when the end of the results
set is
reached, the VDBE encounters a Close instruction, which causes all
associated cursors
to be closed.
‧ The statement is finalized. The program explicitly calls finalize(),
thereby removing all
associated cursors.
In many extensions, the call to sqlite3_finalize() is done automatically in
the statement
object's close() function, or something similar.
■Note As a matter of interest, there are exceptions to these scenarios where
you could theoretically get
away with reading and writing to the same table at the same time. In order
to do so, you would have to
convince the optimizer to use a temporary table, using something like an
ORDER BY, for example. When this
happens, the optimizer will automatically create a temporary table for the
SELECT statement and place the
reading statement's cursor on it rather than the actual table itself. In
this case, it is technically possible for a
writer to then modify the real table because the reader's cursor is on a
temporary table. The problem with this
approach is that the decision to use temporary tables is made by the
optimizer. It is not safe to presume what
the optimizer will and will not do. Unless you like to gamble, or are just
intimately acquainted with the ins and
outs of the optimizer, it is best to just follow the general rule of thumb:
don't read and write to the same table
at the same time.
Fun with Temporary Tables
Temporary tables let you bend the rules. If you absolutely have to have two
connections going
in the same block of code, or two statements operating on the same table,
you can safely do so
if you use temporary tables. When a connection creates or writes to a
temporary table, it does
not have to get a RESERVED lock, because temporary tables are maintained
outside of the database


On Wed, Jul 2, 2008 at 7:25 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> Alex Katebi <[EMAIL PROTECTED]> wrote:
> >   Notice that I have multiple stmts stepping over the same table at
> > the
> > same time.
> >   Why is this OK?
>
> Why shouldn't it be?
>
> > There isn't a table level lock?
>
> A file level lock, even. It happily locks out other connections (of
> which you have none). But a connection cannot lock _itself_ out.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table Level Locking

[Alex Katebi]

Igor,

   Notice that I have multiple stmts stepping over the same table at the
same time.
   Why is this OK? There isn't a table level lock?
   When is a table locked?

Thanks,
-Alex

On Wed, Jul 2, 2008 at 5:12 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> Alex Katebi <[EMAIL PROTECTED]> wrote:
> >> I created a test file. It is attached in this email. I can not see
> >> any
> >> locking happening at all.
>
> Which part of "you should never experience any locking at all in this
> scenario" did you find unclear the first time round? Why exactly are you
> surprised?
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table Level Locking

[Alex Katebi]

I created a test file. It is attached in this email. I can not see any
locking happening at all.


On Wed, Jul 2, 2008 at 4:25 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> Alex Katebi <[EMAIL PROTECTED]> wrote:
> > Do I need to enable shared cache mode plus read uncommitted option?
>
> You only have one connection (one call to sqlite3_open), right? Then it
> doesn't matter. "Shared" only makes a difference if there are at least
> two connections to share between.
>
> > Also you mentioned earlier:
> > "(but you will experience "dirty reads" with all the attendant
> > problems)."
> >
> > What is a dirty read?  What problems does it cause?
>
> Dirty read is another term for read uncommitted. Your select statement
> may see changes made to the database while the statement is still
> active. This is especially "interesting" if you have a query that may
> scan the same table several times. For example:
>
> select * from table1
> where exists (select * from table2 where table2.value = table1.value);
>
> Suppose you have two records in table1 both having value=1 - let's call
> them A and B. Looking at the statement, one would think that, regardless
> of what's in table2, it should always return both A and B, or neither.
>
> So, you step through the statement above. For each record in table1, it
> scans table2 in search of a matching record. At some point, a call to
> sqlite3_step returns record A. Then you run another statement that
> deletes one and only record from table2 that had value=1. A subsequent
> sqlite3_step call won't find record B anymore. So you get A but not B,
> which may be surprising.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

#include 
#include 
#include 

int busy(void* arg, int cnt)
{
  printf("%s: arg %p, cnt %d\n", __func__, arg, cnt);
  return 1;
}

int main()
{
  const char* zSql;
  sqlite3* db;
  sqlite3_stmt* stmt, *stmt2, *stmt3;
  int rc;

  if((rc = sqlite3_open(":memory:", )))
{
  printf("open rc = %d\n", rc);
  exit(1);
}

  rc = sqlite3_busy_handler(db, busy, 0);
  printf("busy_handler rc = %d\n", rc);

  zSql = "create table t(a,b)"; 
  rc = sqlite3_prepare_v2(db, zSql, -1, , 0);
  printf("prepare rc = %d, %s\n", rc, zSql);
  rc = sqlite3_step(stmt);
  printf("step rc = %d\n", rc);
  rc = sqlite3_finalize(stmt);
  printf("finalize rc = %d\n", rc);

  zSql = "insert into t values('alex','katebi')"; 
  rc = sqlite3_prepare_v2(db, zSql, -1, , 0);
  printf("prepare rc = %d, %s\n", rc, zSql);
  rc = sqlite3_step(stmt);
  printf("step rc = %d\n", rc);
  rc = sqlite3_finalize(stmt);
  printf("finalize rc = %d\n", rc);


  zSql = "select * from t";

  rc = sqlite3_prepare_v2(db, zSql, -1, , 0);
  printf("stmt2: prepare rc = %d, %s\n", rc, zSql);
  rc = sqlite3_step(stmt2);
  printf("stmt2: step rc = %d\n", rc);

  zSql = "insert into t values('alex','katebi')"; 
  rc = sqlite3_prepare_v2(db, zSql, -1, , 0);
  printf("prepare rc = %d, %s\n", rc, zSql);
  rc = sqlite3_step(stmt);
  printf("step rc = %d\n", rc);
  rc = sqlite3_finalize(stmt);
  printf("finalize rc = %d\n", rc);

  rc = sqlite3_prepare_v2(db, zSql, -1, , 0);
  printf("stmt3: prepare rc = %d, %s\n", rc, zSql);
  rc = sqlite3_step(stmt3);
  printf("stmt3: step rc = %d\n", rc);

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

Re: [sqlite] Table Level Locking

[Alex Katebi]

Do I need to enable shared cache mode plus read uncommitted option?
Also you mentioned earlier:
"(but you will experience "dirty reads" with all the attendant problems)."

What is a dirty read?  What problems does it cause?

Thanks,
-Alex

On Wed, Jul 2, 2008 at 2:55 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> Alex Katebi <[EMAIL PROTECTED]> wrote:
> > Just to make myself clearer.  I have one memory connection and many
> > stmts.
> > Each stmt multiplexes the thread. This means that a stmt could give
> > up the
> > thread without finalizing itself.
>
> That's OK. It used to be that, say, a SELECT statement in progress (not
> yet finalized or reset) would block an UPDATE statement on the same
> connection. This has not been the case for a long time now. Just use a
> reasonlably recent version of SQLite.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table Level Locking

[Alex Katebi]

Alexey,

   Do you mean the sqlite3_busy_timeout( ) ?
I never thought I could use it for simulating this.
I will give that a shot.

Thanks,
-Alex


On Wed, Jul 2, 2008 at 11:40 AM, Alexey Pechnikov <[EMAIL PROTECTED]>
wrote:

> В сообщении от Wednesday 02 July 2008 19:11:58 Alex Katebi написал(а):
> >I have an in memory database and a single multiplexed thread for all
> > readers and writes.
> > I like to be able to read tables without locking out other readers and
> > writers.
> > Is this possible? I don't mind writers using locks but some of my readers
> > are slow and I don't want them to hold locks for long periods.
>
> You can simulate this. See "db timeout" function.
> ___
> 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] Table Level Locking

[Alex Katebi]

Just to make myself clearer.  I have one memory connection and many stmts.
Each stmt multiplexes the thread. This means that a stmt could give up the
thread without finalizing itself.



On Wed, Jul 2, 2008 at 11:19 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> Alex Katebi <[EMAIL PROTECTED]> wrote:
> >   I have an in memory database and a single multiplexed thread for all
> > readers and writes.
> > I like to be able to read tables without locking out other readers and
> > writers.
>
> As far as I can tell, you do everything on a single thread using a
> single connection. You should never experience any locking at all in
> this scenario (but you will experience "dirty reads" with all the
> attendant problems).
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table Level Locking

[Alex Katebi]

Hi Igor,

   I have an in memory database and a single multiplexed thread for all
readers and writes.
I like to be able to read tables without locking out other readers and
writers.
Is this possible? I don't mind writers using locks but some of my readers
are slow and I don't want them to hold locks for long periods.

Thanks,
-Alex


On Wed, Jul 2, 2008 at 10:51 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> Joanne Pham <[EMAIL PROTECTED]> wrote:
> > I read the online document regarding "Table Level Locking" as below:
> > At any one time, a single table may have any number of active
> > read-locks or a single active write lock. To read data a table, a
> > connection must first obtain a read-lock. To write to a table, a
> > connection must obtain a write-lock on that table. If a required
> > table lock cannot be obtained, the query fails and SQLITE_LOCKED is
> > returned to the caller
> > So the question that I had is while writing the data to table(write
> > lock) another process can read the data from same table without any
> > problem?
>
> The article you quote applies to connections that have opted into shared
> cache. Only connections in the same process can share cache. Connections
> from different process use the traditional file-level locking.
>
> Even connections with shared cache cannot read and write the same table
> simultaneously (unless you also turn on "read uncommitted" option).
> Consider the passage you yourself have just quoted: "at any one time, a
> single table may have any number of active read-locks *OR* a single
> active write lock" (emphasis mine).
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] blob /transaction wierdness

[Alex Katebi]

Can you update your SQLite to the latest revision?

On Tue, Jul 1, 2008 at 3:42 PM, smlacc1 leador <[EMAIL PROTECTED]> wrote:

> Hi,
>
> I'm having some trouble with blobs.  I have 4 blobs tht I want to insert
> into a db, and it works fine when I execute each insert as a single commit.
> However, when i try to use transactions to input blocks of 255 inserts,
> blob
> 4 gets inputted in the position of blob3, blob 3 in position of blob 2 etc
> etc.  Very strange.  I've written some code to explain what I mean.  The
> code takes a long time to finish, so if you want to try it, compile it,
> then
> execute for a few seconds before ctrl-c'ing it to quit.  It should give you
> enough data to see what I mean.  In example 1, the fields are where they
> should be.  In code 2, the fields all get shifted left by 1 column.  But
> the
> code is identical except for where the "begin" and "commit" statements are
> located.  Anyone know what could be the problem?  Im using sqlite v3, th
> specific version is the one that came with fedora core 8 - 3.4.2.  The
> code
> is in "c".
>
> here are 2 pieces of code - 1 using transactions, 1 without - but otherwise
> identical.
>
> Any help much apreciated.  What I'm trying to acheive would be way too slow
> without transactions.
> 
> #include 
> #include 
> #include 
>
> sqlite3* db;
> sqlite3_stmt *state;
>
> void execblobsql(const char* statement,int v1,int v2,int v3,int v4){
> char c1[1];
> c1[0] = v1;
> char c2[1];
> c2[0] = v2;
> char c3[1];
> c3[0] = v3;
> char c4[1];
> c4[0] = v4;
> sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
> sqlite3_prepare(db,statement,-1,,NULL);
> sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT);
> sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT);
> sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT);
> sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT);
>
> sqlite3_step(state);
> sqlite3_finalize(state);
> sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
> }
>
> int main (){
>  sqlite3_open("./blobs.db", );
>  if (db == 0){
>  printf ("database could not be opened.\n");
>  return 1;
>  }
>  char *errmsg;
>  sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4
> BLOB)",0,0,);
>  char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)";
>  int looper1 = 0;
>  int looper2 = 0;
>  int looper3 = 0;
>  int looper4 = 0;
>
>
>  while (looper1 < 256){
>  while (looper2 < 256){
>   while (looper3 < 256){
>  /*  sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
>sqlite3_prepare(db,stata,-1,,NULL);*/
>while (looper4 < 256){
> execblobsql(stata, looper1, looper2, looper3, looper4);
> looper4++;
>}
>looper3++;
>looper4=0;
>  /*  sqlite3_finalize(state);
>sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/
>   }
>   looper2++;
>   looper3=0;
>   looper4=0;
>  }
>  looper1++;
>  looper2=0;
>  looper3=0;
>  looper4=0;
>  }
>
> }
>
> 
> #include 
> #include 
> #include 
>
> sqlite3* db;
> sqlite3_stmt *state;
>
> void execblobsql(const char* statement,int v1,int v2,int v3,int v4){
> char c1[1];
> c1[0] = v1;
> char c2[1];
> c2[0] = v2;
> char c3[1];
> c3[0] = v3;
> char c4[1];
> c4[0] = v4;
>  /*sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
> sqlite3_prepare(db,statement,-1,,NULL);*/
> sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT);
> sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT);
> sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT);
> sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT);
>
> sqlite3_step(state);
>  /*sqlite3_finalize(state);
> sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/
> }
>
> int main (){
>  sqlite3_open("./blobs.db", );
>  if (db == 0){
>  printf ("database could not be opened.\n");
>  return 1;
>  }
>  char *errmsg;
>  sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4
> BLOB)",0,0,);
>  char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)";
>  int looper1 = 0;
>  int looper2 = 0;
>  int looper3 = 0;
>  int looper4 = 0;
>
>
>  while (looper1 < 256){
>  while (looper2 < 256){
>   while (looper3 < 256){
>sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
>sqlite3_prepare(db,stata,-1,,NULL);
>while (looper4 < 256){
> execblobsql(stata, looper1, looper2, looper3, looper4);
> looper4++;
>}
>looper3++;
>looper4=0;
>sqlite3_finalize(state);
>sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
>   }
>   looper2++;
>   looper3=0;
>   looper4=0;
>  }
>  looper1++;
>  looper2=0;
>  looper3=0;
>  looper4=0;
>  }
>
> }
> ___
> 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] prepackaged sql statement

[Alex Katebi]

Hi Steve,
This is my problem:

create table inet0 (dest, mask, nexthop);
create index inet0_idx on inet0(mask, destination);
insert into inet0 values(x'0100', x'FF00', x'08080808');
insert into inet0 values(x'0101', x'', x'16161616');
insert into inet0 values(x'01010100', x'FF00', x'24242424');
insert into inet0 values(x'01010100', x'FF00', x'');

select * from inet0 where destination=bitand(x'01010101', (select mask from
inet0 where destination=bitand(x'01010101, mask) order by mask desc)) order
by mask desc;

The objective of the above select is to find one or more longest mask
nexthop values.
I want to make a view for the above select and pass in a destination
variable as parameter.

Thanks,
-Alex

On Tue, Jul 1, 2008 at 2:41 PM, Stephen Woodbridge <[EMAIL PROTECTED]>
wrote:

> Alex Katebi wrote:
> > The problem with the view is that you can not pass a parameter or
> variable
> > from the outer select to the views select.
>
> Right that is not the purpose of a view. Think of a view as a virtual
> TABLE. You can not pass a parameter to a table either. You can just
> query the table or view and you CAN query with a parameter in the select
> statement.
>
> select * from  where 
>
> Maybe it would be help to restate what you are trying to do.
>
> -Steve
>
> > On Mon, Jun 30, 2008 at 12:49 AM, Igor Tandetnik <[EMAIL PROTECTED]>
> > wrote:
> >
> >> "Henrik Bechmann" <[EMAIL PROTECTED]>
> >> wrote in message news:[EMAIL PROTECTED]
> >>> Thanks Igor! And the SQL statement can be bound to parameters in the
> >>> usual ways?
> >> No, not in the view. You can, of course, select from the view (as if it
> >> were a table), and _that_ query can be parameterized.
> >>
> >> Igor Tandetnik
> >>
> >>
> >>
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> 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] Table Level Locking

[Alex Katebi]

Table level locking is used among statements for the same connection. File
level locking is used among connections. Your case is file level.

On Tue, Jul 1, 2008 at 8:00 PM, Joanne Pham <[EMAIL PROTECTED]> wrote:

> Hi All,
> I read the online document regarding "Table Level Locking" as below:
>  At any one time, a single table may have any number of active
> read-locks or a single active write lock. To read data a table, a connection
> must first obtain a read-lock. To write to a table, a
> connection must obtain a write-lock on that table. If a required table lock
> cannot be obtained, the query fails and SQLITE_LOCKED is
> returned to the caller
> So the question that I had is  while writing the data to table(write lock)
> another process can read the data from same table without any problem?
> Thanks,
> Joanne
>
>
>
> ___
> 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] prepackaged sql statement

[Alex Katebi]

The problem with the view is that you can not pass a parameter or variable
from the outer select to the views select.

On Mon, Jun 30, 2008 at 12:49 AM, Igor Tandetnik <[EMAIL PROTECTED]>
wrote:

> "Henrik Bechmann" <[EMAIL PROTECTED]>
> wrote in message news:[EMAIL PROTECTED]
> > Thanks Igor! And the SQL statement can be bound to parameters in the
> > usual ways?
>
> No, not in the view. You can, of course, select from the view (as if it
> were a table), and _that_ query can be parameterized.
>
> Igor Tandetnik
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unsigned 64 bit integers

[Alex Katebi]

No. The range is for 64 bit signed. SQLite has manifest typing it is like
Ruby Duck Typing or Dynamic Typing. The value defines the type not the
column type or lack there of it.

If you value is a small integer it will only use 1 byte.

On Sat, Jun 28, 2008 at 6:02 PM, freeav8r <[EMAIL PROTECTED]> wrote:

> Hi.  I have a newbie question.
>
> When trying to store 64-bit unsiged integers in sqlite, some of them come
> back as floats.  There is some internal reference on the web page to 64-bit
> unsiged integers; http://www.sqlite.org/c3ref/int64.html.  On the other
> hand, the faq's entry on AUTOINCREMENT fields suggests that they may not be
> supported above 9223372036854775807.
>
> Does sqlite support 64-bit unsigned integers?
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite in embedded environment

[Alex Katebi]

Steven,

   If SQLite runs on ARM9/Linux, then I don't see any problems. I think you
should watch the SQLite video on youtube.
http://www.youtube.com/watch?v=giAMt8Tj-84

Enjoy!
-Alex

2008/6/24 Steven Woody <[EMAIL PROTECTED]>:

> Hi,
>
> I am considering to use SQLite in my current embedded application
> project.  It's a ARM9/Linux.  Do you experts think it is a good idea？
> And, is there any tips or considerations in this combination?
>
> Thank you.
> ___
> 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] selecting an indexed table

[Alex Katebi]

I have no way of knowing which rows a select command has visited for an
indexed table.

create table t(a);
create index it on t(a);
insert ...
select * from t where a=5;

Is there a select hook for debugging?

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

Re: [sqlite] Virtual table sample code

[Alex Katebi]

In sqlite source code. src/test_schema.c

On Tue, Jun 24, 2008 at 4:57 PM, <[EMAIL PROTECTED]> wrote:

> Is there any example source code available that demonstrates a working
> virtual table implementation?
>
> Thanks
> Dan Winslow, GamePlan
> 402-991-5875 x219
> [EMAIL PROTECTED]
>
> Third Nerd from the left,Technology Defenestration Office
>
> ___
> 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] Client/Srever SQLite

[Alex Katebi]

Hi John,

I was writing a message into the socket partially. I had done this to
avoid coping. This caused many transmissions for a single message. At the
other end I was doing MSG_PEEK optional flag which was wasteful. I had done
this to dump the message for debugging.

   By building my message into memory. I was able to send the entire message
in one socket write. At the other end of the socket I did the
MSG_WAITALLoptional for the
recv function. This caused the receiver to be active only when a
usable message peace was received.

One might say that a misbehaving client could still slow down the server. A
server should respond with a notification before dropping this client for
sending runt messages.

What is your design like?

Thanks,
-Alex


On Tue, Jun 17, 2008 at 11:57 AM, John Stanton <[EMAIL PROTECTED]> wrote:

> What did you change?  What was causing the lag?
>
> Alex Katebi wrote:
> > slowness is fixed. Can't tell the difference between client/server speed
> > from library.
> >
> > On Sat, Jun 14, 2008 at 8:32 PM, Alex Katebi <[EMAIL PROTECTED]>
> wrote:
> >
> >
> >>Hi All,
> >>
> >>   Looks like there is some interest. I will announce when I release it.
> >>Currently I am developing an interactive user shell client. This shell is
> >>used for my client/server development. It can also be embedded for any
> >>clients user interface.
> >>The request/response is a little slow for some reason. I need to fix this
> >>issue.
> >>
> >>Thanks,
> >>-Alex
> >>
> >>
> >>
> >>On Mon, Jun 2, 2008 at 11:40 AM, Alex Katebi <[EMAIL PROTECTED]>
> >>wrote:
> >>
> >>
> >>>Hi All,
> >>>
> >>>  I am using remote procedure calls (RPC) for SQLite in my application.
> I
> >>>have implemented a few SQLite RPC functions that I needed successfully.
> >>>I am wondering if there are other people like me who need this.
> >>>If there are enough people who could benefit from this I can make it
> >>>available as an open source public domain software.
> >>>Then people can add more functions as needed.
> >>>
> >>>Thanks,
> >>>-Alex
> >>>
> >>
> >>
>  > ___
> > 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] Client/Srever SQLite

[Alex Katebi]

slowness is fixed. Can't tell the difference between client/server speed
from library.

On Sat, Jun 14, 2008 at 8:32 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:

> Hi All,
>
>Looks like there is some interest. I will announce when I release it.
> Currently I am developing an interactive user shell client. This shell is
> used for my client/server development. It can also be embedded for any
> clients user interface.
> The request/response is a little slow for some reason. I need to fix this
> issue.
>
> Thanks,
> -Alex
>
>
>
> On Mon, Jun 2, 2008 at 11:40 AM, Alex Katebi <[EMAIL PROTECTED]>
> wrote:
>
>>
>> Hi All,
>>
>>   I am using remote procedure calls (RPC) for SQLite in my application. I
>> have implemented a few SQLite RPC functions that I needed successfully.
>> I am wondering if there are other people like me who need this.
>> If there are enough people who could benefit from this I can make it
>> available as an open source public domain software.
>> Then people can add more functions as needed.
>>
>> Thanks,
>> -Alex
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Client/Srever SQLite

[Alex Katebi]

Hi All,

   Looks like there is some interest. I will announce when I release it.
Currently I am developing an interactive user shell client. This shell is
used for my client/server development. It can also be embedded for any
clients user interface.
The request/response is a little slow for some reason. I need to fix this
issue.

Thanks,
-Alex



On Mon, Jun 2, 2008 at 11:40 AM, Alex Katebi <[EMAIL PROTECTED]> wrote:

>
> Hi All,
>
>   I am using remote procedure calls (RPC) for SQLite in my application. I
> have implemented a few SQLite RPC functions that I needed successfully.
> I am wondering if there are other people like me who need this.
> If there are enough people who could benefit from this I can make it
> available as an open source public domain software.
> Then people can add more functions as needed.
>
> Thanks,
> -Alex
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Collation & blobs

[Alex Katebi]

I can still keep my routes in text presentation notation like 1.1.0.0/16 or
fe80::/16.
Thanks,
-Alex

On Thu, Jun 12, 2008 at 7:32 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

>
> On Jun 12, 2008, at 6:35 PM, Alex Katebi wrote:
>
> >   The custom sqlite3_create_collation( ) is a perfect solution for
> > implementing an Internet Protocol Routing Table.
> > But I am not sure if SQLite will call a custom collation function if
> > column
> > values are blobs. In other word, are blobs considered for collation?
> >
>
> Blob always sort in memcmp() order.  The sorting rules for SQLite are
> (and always have been):
>
>  1.  NULLs sort first
>  2.  Numbers sort next in numerical order (floating point values
> and integers interleave as appropriate).
>  3.  Text sorts next in the order determined by the collating
> sequence
>  4.  BLOBs sort last in memcmp() order.
>
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] Collation & blobs

[Alex Katebi]

   The custom sqlite3_create_collation( ) is a perfect solution for
implementing an Internet Protocol Routing Table.
But I am not sure if SQLite will call a custom collation function if column
values are blobs. In other word, are blobs considered for collation?

   One can format a blob with route mask length in the first byte followed
by the destination address bytes.

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

Re: [sqlite] On wheel re-invention (Re: Writing double into a socket file)

[Alex Katebi]

Nico,

 I needed a custom wheel that fit my specifications and gave me complete
control. Since my background is networking, it wasn't too hard for me to do.
Let me put it this way. If the learning curve is equal or greater than my
own implementation I will do my own. I would not attempt to implement my own
SQL Engine. :-)

Thanks,
-Alex


On Wed, Jun 11, 2008 at 3:21 PM, Nicolas Williams <[EMAIL PROTECTED]>
wrote:

> On Tue, Jun 10, 2008 at 12:35:14PM -0400, Alex Katebi wrote:
> > Peter,
> >I am using my own implementation. I found RPC and others too
> complicated
> > to use and it did not give me enough control on the transport layer. I
> need
> > my socket to be non-blocking and I am using epoll( ) which is very
> > efficient.
>
> You can use XDR without using ONC/RPC.  That puts you in full control of
> the transport layer, but let's you reuse a existing, mature structured
> data marshalling/unmarshalling technology.
>
> XDR is a data representation language and encoding, much like IDL+NDR
> are in the DCE RPC (and MSRPC) world, or like ASN.1+BER/DER/CER/PER/XER,
> or like...  There are many, many such languages and encodings.
>
> So, why re-invent the wheel?
>
> Heck, SQLite itself has its own encoding for on-disk sotrage, and IIRC
> it's endian-neutral (i.e., the same DB works on little-endian and
> big-endian systems).  Perhaps libsqlite ought to export functions for
> encoding/decoding values.
>
> Nico
> --
> ___
> 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] Writing double into a socket file

[Alex Katebi]

  Hi John,

  Yes the negative with the single thread is single CPU utilization. Then
again you can run 4 or more servers for a quad CPU. For security I could
either use SSH Port Forwarding or use a MD5 implementation in my
client/server code.

Thanks,
-Alex



On Tue, Jun 10, 2008 at 11:25 PM, John Stanton <[EMAIL PROTECTED]> wrote:

> Alex,
>
> Thankyou for the comments.  I use non-blocking fd's also but implement
> multiple threads to take advantage of multiple processor servers.  Since
> threads carry quite a bit of baggage your single thread approach would
> probably be superior on a single processor machine.
>
> It has been experience that a well conceived single thread process gives
> the best performance on a single processor, as one would expect.
>
> I would hand out my code except that the whole things incorporates extra
> processors such as an embedded server page processor and and compiler
> plus a security shell and would be a handful to maintain and document in
> great detail but if you are interested in chunks of code contact me.
>
> Alex Katebi wrote:
> > John,
> >
> >My server uses epoll( ) and runs non-blocking in a single thread. I
> did
> > some google and found out that people who need a very fast server that is
> > highly scalable are using this model. non-blocking seems to be more
> > complicated at first glance but it actually makes the server design much
> > simpler. I am planning to release my code as open source when it is more
> > complete.
> >
> > Thanks,
> > -Alex
> >
> > On Tue, Jun 10, 2008 at 8:48 PM, John Stanton <[EMAIL PROTECTED]>
> wrote:
> >
> >> We use an application server I wrote which handles HTTP, serves file and
> >> has embedded Sqlite for the RPCs.  The RPC can deliver its result either
> >> in XML for widely distributed applications or as JSON if it is
> >> responding to a WWW browser in AJAX mode.
> >>
> >> We keep a local library of SQL RPCs so that SQl never appears on the
> >> network and we have immunity from injection attacks.  It also means that
> >> we can cache compiled SQL, a useful performance win.
> >>
> >> We use the Expat parser in remote programs using the XML format.  A
> >> wrapper makes it a verifying parser to ensure well formed XML.
> >>
> >> The server is multi threaded and maintains a pool of live threads so it
> >> can respond quickly and assign multiple threads to one browser
> >> connection.  Shared cache in Sqlite and some extra caching to maintain
> >> multiple open databases and results makes Sqlite behave like a simple to
> >> use enterprise DB server, but without the overhead of extra processes.
> >> We use mutexes for synchronization, set up as read and write locks and
> >> avoid the POSIX file locks.
> >>
> >> We installed Javascript as a procedural language to be used by Sqlite
> >> instead of PL/SQL but that is not a great success (v. slow) and we are
> >> going to experiment with using Python.
> >>
> >> Based on our experience you should be very happy with your Sqlite based
> >> RPC capability.
> >>
> >> Alex Katebi wrote:
> >>> John & John,
> >>>
> >>>Actually my API used to be XML using SCEW a DOM like XML parser that
> >> uses
> >>> Expat.
> >>>
> >>>For my particular application RPC made more sense to me. What could
> be
> >>> easier than a function call? Another advantage was that I did not have
> to
> >>> create any functions. I am just using SQLite's C API. Now the users of
> my
> >>> application can query any table on the server side using select. Since
> my
> >>> application is a network server, and network debugging capability is
> >>> crucial.
> >>> The only ugliness is that select locks the tables. I wish D. Hipp would
> >> give
> >>> us an option for pStmt to create a temporary table of the select result
> >> set
> >>> and delete that temp table after finalize automatically. This way a
> >> client
> >>> can sit on a prepare/step for a long time.
> >>>
> >>>I solved the endian issue pretty easy by sending the type code.
> >>>
> >>> Thanks,
> >>> -Alex
> >>>
> >>>
> >>> On Tue, Jun 10, 2008 at 3:07 PM, John Elrick <[EMAIL PROTECTED]
> >
> >>> wrote:
> >>>
> >>>> Alex Katebi wrote:
> >>>>> Yes I need to do it as 

Re: [sqlite] Writing double into a socket file

[Alex Katebi]

Dennis,

  After your explanation the prefixing doesn't look so bad.

  The client might do lots of queries before it exits. But I can drop that
temp table when client does finalize. I have to have a state machine for
clients so they don't crash the server by misusing the API anyways.

  Thank you!
  -Alex



On Wed, Jun 11, 2008 at 9:36 AM, Dennis Cote <[EMAIL PROTECTED]> wrote:

> Alex Katebi wrote:
> > However there are two things I don't like about this method. One is that
> I
> > need to parse the prepared statments and prefix queries, and replace the
> > table name with the temp table name in the surrogate query. Two is that
> if
> > the client does lots of queries before disconnecting from the server it
> > can waste whole lot of memory. Three is that it is a hack.
> >
>
> Alex,
>
> There should be no need to parse the queries other than to check for
> "select" at the beginning to identify it as a select statement. All
> select statements would be prefixed with the same prefix string, "create
> tmp.result as", regardless of the complexity of the original query. You
> are simply storing the result set of the query into a temp table.
>
> Since the temporary result table is always the same, the surrogate query
> is always the same, "select * from from tmp.result". There is no need to
> replace any table names.
>
> I imagined the temporary memory database would be closed, and hence its
> memory released, after each query completes. The memory footprint
> wouldn't grow with each query, it would be the same as that required for
> the query with the largest result set.
>
> It is not a hack of any sort. It simply uses the public APIs to quickly
> copy the result data into a private table to avoid locking the entire
> database while those results are slowly scanned by a client.
>
> Dennis Cote
>
> ___
> 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] Writing double into a socket file

[Alex Katebi]

John,

   My server uses epoll( ) and runs non-blocking in a single thread. I did
some google and found out that people who need a very fast server that is
highly scalable are using this model. non-blocking seems to be more
complicated at first glance but it actually makes the server design much
simpler. I am planning to release my code as open source when it is more
complete.

Thanks,
-Alex

On Tue, Jun 10, 2008 at 8:48 PM, John Stanton <[EMAIL PROTECTED]> wrote:

> We use an application server I wrote which handles HTTP, serves file and
> has embedded Sqlite for the RPCs.  The RPC can deliver its result either
> in XML for widely distributed applications or as JSON if it is
> responding to a WWW browser in AJAX mode.
>
> We keep a local library of SQL RPCs so that SQl never appears on the
> network and we have immunity from injection attacks.  It also means that
> we can cache compiled SQL, a useful performance win.
>
> We use the Expat parser in remote programs using the XML format.  A
> wrapper makes it a verifying parser to ensure well formed XML.
>
> The server is multi threaded and maintains a pool of live threads so it
> can respond quickly and assign multiple threads to one browser
> connection.  Shared cache in Sqlite and some extra caching to maintain
> multiple open databases and results makes Sqlite behave like a simple to
> use enterprise DB server, but without the overhead of extra processes.
> We use mutexes for synchronization, set up as read and write locks and
> avoid the POSIX file locks.
>
> We installed Javascript as a procedural language to be used by Sqlite
> instead of PL/SQL but that is not a great success (v. slow) and we are
> going to experiment with using Python.
>
> Based on our experience you should be very happy with your Sqlite based
> RPC capability.
>
> Alex Katebi wrote:
> > John & John,
> >
> >Actually my API used to be XML using SCEW a DOM like XML parser that
> uses
> > Expat.
> >
> >For my particular application RPC made more sense to me. What could be
> > easier than a function call? Another advantage was that I did not have to
> > create any functions. I am just using SQLite's C API. Now the users of my
> > application can query any table on the server side using select. Since my
> > application is a network server, and network debugging capability is
> > crucial.
> > The only ugliness is that select locks the tables. I wish D. Hipp would
> give
> > us an option for pStmt to create a temporary table of the select result
> set
> > and delete that temp table after finalize automatically. This way a
> client
> > can sit on a prepare/step for a long time.
> >
> >I solved the endian issue pretty easy by sending the type code.
> >
> > Thanks,
> > -Alex
> >
> >
> > On Tue, Jun 10, 2008 at 3:07 PM, John Elrick <[EMAIL PROTECTED]>
> > wrote:
> >
> >> Alex Katebi wrote:
> >>> Yes I need to do it as 8 byte buffer. Convert the endianess to the
> >> network
> >>> then back to host for 8 byte integer.
> >>> I think XML is great for command validation and CLI auto typing, help
> >> etc.
> >>> Besides parsing issue, XML can not handle binary data directly.
> >>>
> >> As John pointed out, XML is not intended to handle binary data
> >> directly.  We use XML as a transfer medium for binary data and simply
> >> base64 encode it before encapsulation.
> >>
> >>
> >> John Elrick
> >> Fenestra Technologies
> >> ___
> >> 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] How to build an extension that gets autoloaded

[Alex Katebi]

Try this link.

http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions

On Tue, Jun 10, 2008 at 12:39 AM, Stephen Woodbridge <
[EMAIL PROTECTED]> wrote:

> Hi all,
>
> I was reading through the sqlite source and noticed that there appears
> to be an ability to build and extension that is statically linked and
> autoloaded, but I can not find any details on how to do this.
>
> For example, if I want to build an application that uses the rtree
> extension on Linux, how would I compile and link link it and get it
> autoloaded.
>
> For a simple example, maybe just an explanation of how to do this for
> the sqlite3 shell would suffice. It makes it much simpler to statically
> link these into the application when you want to deploy it, because
> there are less files and paths and things that can go wrong if the user
> messes with things and since it is a dedicated application it will
> always need the extension.
>
> If this is already documented somewhere, please just point to it.
>
> Thanks,
>   -Stephen Woodbridge
>http://imaptools.com/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with sqlite overall or not?

[Alex Katebi]

I just noticed that you are returning from your function before finalize.

On Tue, Jun 10, 2008 at 10:12 AM, piotro <[EMAIL PROTECTED]> wrote:

> Hello. I really need help with this, because Im almost out of
> ideas here. Help please!
>
> sqldata is a vector< vector < char* > >
> row is a vector < char* >
>
> sqldata sql3wrapper::pobierzPodmiot(){
>const char* tail;
>sqlite3_stmt* statement;
>
>std::string query = squery("SELECT * FROM %;",PODMIOTY_TABLE);
>
>sqlite3_prepare_v2(interface,query.c_str(),-1,,);
>sqldata data;
>for(int a=0;sqlite3_step(statement)!=101;a++){
>row set;
>data.push_back(set);
>for(int az=1; azdata[a].push_back((char*)sqlite3_column_text(statement,az));
>}
>std::cout<<(data[a])[2]<}
>return data;
>sqlite3_finalize(statement);
> }
>
> this does cout properly. But when I'm using it in a different place:
>
> int main(){
> sqldata est = sql3wrapper().pobierzPodmiot();
>
>row one = est[0];
>row two = est[1];
>row three = est[2];
>
>std::cout<<'\n'<std::cout<std::cout<std::cout< }
>
> the data is couted properly, but the values are just the values coming
> from the last row.
>
> I have three rows of data in the sqlite database. Then I use the
> pobierzPodmiot
> function, get the data correctly, but when the function is used
> somewhere else,
> it hickups pretty much.
>
> Can someone come up with a solution or something, coping with this had
> been tiresome.
>
> Cheers!
>
> ___
> 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] Problem with sqlite overall or not?

[Alex Katebi]

For starters sqlite3_column_count( ) should be after prepare once.
You don't need to do it for every step.

The other thing is do you have to use STL when you can do everything by
using SQLite?
Tell us in English what you want to accomplish with your code. We can come
up with a pure SQLite solution.

Thanks,
-Alex

On Tue, Jun 10, 2008 at 10:12 AM, piotro <[EMAIL PROTECTED]> wrote:

> Hello. I really need help with this, because Im almost out of
> ideas here. Help please!
>
> sqldata is a vector< vector < char* > >
> row is a vector < char* >
>
> sqldata sql3wrapper::pobierzPodmiot(){
>const char* tail;
>sqlite3_stmt* statement;
>
>std::string query = squery("SELECT * FROM %;",PODMIOTY_TABLE);
>
>sqlite3_prepare_v2(interface,query.c_str(),-1,,);
>sqldata data;
>for(int a=0;sqlite3_step(statement)!=101;a++){
>row set;
>data.push_back(set);
>for(int az=1; azdata[a].push_back((char*)sqlite3_column_text(statement,az));
>}
>std::cout<<(data[a])[2]<}
>return data;
>sqlite3_finalize(statement);
> }
>
> this does cout properly. But when I'm using it in a different place:
>
> int main(){
> sqldata est = sql3wrapper().pobierzPodmiot();
>
>row one = est[0];
>row two = est[1];
>row three = est[2];
>
>std::cout<<'\n'<std::cout<std::cout<std::cout< }
>
> the data is couted properly, but the values are just the values coming
> from the last row.
>
> I have three rows of data in the sqlite database. Then I use the
> pobierzPodmiot
> function, get the data correctly, but when the function is used
> somewhere else,
> it hickups pretty much.
>
> Can someone come up with a solution or something, coping with this had
> been tiresome.
>
> Cheers!
>
> ___
> 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] Writing double into a socket file

[Alex Katebi]

Dennis,

My servers main connection itself is in :memory:, but your suggestion
will still work.
However there are two things I don't like about this method. One is that I
need to parse the prepared statments and prefix queries, and replace the
table name with the temp table name in the surrogate query. Two is that if
the client does lots of queries before disconnecting from the server it
can waste whole lot of memory. Three is that it is a hack.

   However, server can keep track of client temp tables and drop them after
finalize. Parsing might not be as bad as I think. Right now this seems to be
the only choice.

Thanks,
-Alex

On Tue, Jun 10, 2008 at 4:33 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:

> Alex Katebi wrote:
> > The only ugliness is that select locks the tables. I wish D. Hipp would
> give
> > us an option for pStmt to create a temporary table of the select result
> set
> > and delete that temp table after finalize automatically. This way a
> client
> > can sit on a prepare/step for a long time.
> >
>
> Alex,
>
> Your application can do this by itself without any changes to the SQLite
> core. You can attach a :memory: database as tmp to your main database,
> then prefix your client's query with "create table tmp.result as", and
> then return the result of a surrogate query "select * from tmp.result"
> instead of the actual result of the client's query. This will only hold
> the lock on the main database while the temp table is created, since
> memory database don't use any locking because they are private to a
> single connection. The client can then scan through the surrogate
> results at its leisure. When the client is done you can close the memory
> database.
>
> HTH
> Dennis Cote
>  ___
> 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] Writing double into a socket file

[Alex Katebi]

D. Hipp,

   I have since identified and fixed my problem, thanks to our user group!

   A lot of the networking gear like IP routers use big-endian machines
since this is the format used by the network protocol control messages.
PowerPC is an example.

  Thanks,
  -Alex


On Mon, Jun 9, 2008 at 10:09 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

>
> On Jun 9, 2008, at 9:58 PM, Russell Leighton wrote:
>
> >
> > On Jun 9, 2008, at 8:52 PM, Igor Tandetnik wrote:
> >
> >> "Alex Katebi" <[EMAIL PROTECTED]>
> >> wrote in message
> >> news:[EMAIL PROTECTED]<[EMAIL PROTECTED]>
> >>> I am trying to implement remote procedure calls (RPC) for SQLite API
> >>> to be used in my application.
> >>> In particular sqlite3_column_double( ) returns a floating point
> >>> double. How can I write this double value into a TCP socket?
> >>
> >> How do you write an int, or a string, into a socket? A double
> >> wouldn't
> >> be much different: at the end of the day, it's just an 8-byte buffer.
> >>
> >
> > Aren' t there aligment and endian issues as well as potential floating
> > point representations between platforms?
> >
>
>
> Endianness might be a problem, though these days it is becoming
> increasingly difficult to find a big-endian processor.  I don't think
> different floating point representations are an issue since I am not
> aware of any modern machine that does anything other than IEEE754.
> SQLite assumes IEEE754 floating point representation, so if you have a
> machine that uses something different, SQLite won't work on it (or at
> least it won't have a compatible file format.)  I have never yet heard
> of this being a problem for anyone.
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] Writing double into a socket file

[Alex Katebi]

Arjen,
Thanks!

On Tue, Jun 10, 2008 at 1:39 PM, Arjen Markus <[EMAIL PROTECTED]>
wrote:

> > I am going to guess yes.
> >
> > On Mon, Jun 9, 2008 at 9:58 PM, Russell Leighton
> > <[EMAIL PROTECTED]>
> > wrote:
> >
>
> >>
> >> Aren' t there aligment and endian issues as well as potential floating
> >> point representations between platforms?
> >>
>
> As most computers nowadays use the IEEE standard to represent
> floating-point numbers this problem is much less complicated
> than it used to be. I think only endian problems are left and
> these are (relatively) easy to deal with.
>
> Regards,
>
> Arjen
> ___
> 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] Writing double into a socket file

[Alex Katebi]

Yes I need to do it as 8 byte buffer. Convert the endianess to the network
then back to host for 8 byte integer.
I think XML is great for command validation and CLI auto typing, help etc.
Besides parsing issue, XML can not handle binary data directly.


On Tue, Jun 10, 2008 at 12:31 PM, John Stanton <[EMAIL PROTECTED]> wrote:

> Can you explain how you are trying to write to the socket and how you
> are receiving?
>
> If you use write or send you just supply a pointer to the value and a
> length to write, viz - written = write(sokfd, (char *)fptr, 8); where
> fptr is a pointer to your floating point number.
>
> We use Sqlite embedded as RPCs but actually encapsulate the data as XML
> and send floating point or other numbers in text form so that there can
> never be byte ordering or format issues.  SQL also maps quite nicely to
> XML.  The downside is the complexity of the XML parser at the receive end.
>
> Alex Katebi wrote:
> > I am trying to implement remote procedure calls (RPC) for SQLite API to
> be
> > used in my application.
> > In particular sqlite3_column_double( ) returns a floating point double.
> > How can I write this double value into a TCP socket?
> > I have tried writing 8 bytes as integer values but the received valued at
> > the other end of the socket is incorrect.
> > I don't have a lot of experience with real numbers. Can someone help?
> >
> > Thanks,
> > -Alex
>  > ___
> > 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] Writing double into a socket file

[Alex Katebi]

I am going to guess yes.

On Mon, Jun 9, 2008 at 9:58 PM, Russell Leighton <[EMAIL PROTECTED]>
wrote:

>
> On Jun 9, 2008, at 8:52 PM, Igor Tandetnik wrote:
>
> > "Alex Katebi" <[EMAIL PROTECTED]>
> > wrote in message
> > news:[EMAIL PROTECTED]<[EMAIL PROTECTED]>
> >> I am trying to implement remote procedure calls (RPC) for SQLite API
> >> to be used in my application.
> >> In particular sqlite3_column_double( ) returns a floating point
> >> double. How can I write this double value into a TCP socket?
> >
> > How do you write an int, or a string, into a socket? A double wouldn't
> > be much different: at the end of the day, it's just an 8-byte buffer.
> >
>
> Aren' t there aligment and endian issues as well as potential floating
> point representations between platforms?
>
>
> ___
> 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] Writing double into a socket file

[Alex Katebi]

Igor,
   You are right. The answer is to use 8 byte buffer and don't forget to
account for the endianess.
I had a bug in my code.

On Mon, Jun 9, 2008 at 8:52 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> "Alex Katebi" <[EMAIL PROTECTED]>
> wrote in message
> news:[EMAIL PROTECTED]<[EMAIL PROTECTED]>
> > I am trying to implement remote procedure calls (RPC) for SQLite API
> > to be used in my application.
> > In particular sqlite3_column_double( ) returns a floating point
> > double. How can I write this double value into a TCP socket?
>
> How do you write an int, or a string, into a socket? A double wouldn't
> be much different: at the end of the day, it's just an 8-byte buffer.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Writing double into a socket file

[Alex Katebi]

Peter,
   I am using my own implementation. I found RPC and others too complicated
to use and it did not give me enough control on the transport layer. I need
my socket to be non-blocking and I am using epoll( ) which is very
efficient.
Thanks!
-Alex
On Mon, Jun 9, 2008 at 10:53 PM, Peter A. Friend <[EMAIL PROTECTED]>
wrote:

>  Alex Katebi wrote:
> > I am trying to implement remote procedure calls (RPC) for SQLite API to
> be
> > used in my application.
> > In particular sqlite3_column_double( ) returns a floating point double.
> > How can I write this double value into a TCP socket?
> > I have tried writing 8 bytes as integer values but the received valued at
> > the other end of the socket is incorrect.
> > I don't have a lot of experience with real numbers. Can someone help?
> >
> Are you using something like ONC or Sun RPC, or are you rolling your
> own? XDR already handles these types of problems for you.
>
> Peter
>
> ___
> 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] Database locked error, while deleting

[Alex Katebi]

There can only be one prepare per table at a time. The first prepare has to
be finalized before another one. Table is locked by the first prepare until
finalized.

On Tue, Jun 10, 2008 at 7:36 AM, Sabyasachi Ruj <[EMAIL PROTECTED]> wrote:

> Hi,
>
> I have a very big table with around 40,00, 000 rows. 4 columns. 220 MB.
>
> Now, I have two threads:-
> Thread1: Is deleting 7,00, 000 rows from the table.
> Thread2: Is doing SELECT on the same table.
>
> Now the problem is sometimes the sqlite3_prepare for the SELECT query is
> failing with SQLITE_BUSY error.
>
> My questions: -
> 1. What is the best way to handle this error, and continue working
> normally?
> 2. Is there any documentation in sqlite3.org, which discuses the locking
> mechanism for DELETEs? Exacly in what phase of DELETE sqlite creates the
> exclusive lock, so, the SELECT is failing?
>
>
> --
> Sabyasachi
> ___
> 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] Writing double into a socket file

[Alex Katebi]

I am trying to implement remote procedure calls (RPC) for SQLite API to be
used in my application.
In particular sqlite3_column_double( ) returns a floating point double.
How can I write this double value into a TCP socket?
I have tried writing 8 bytes as integer values but the received valued at
the other end of the socket is incorrect.
I don't have a lot of experience with real numbers. Can someone help?

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

Re: [sqlite] Math Functions

[Alex Katebi]

You can do your own custom functions using the C extentions. You should buy
the book.
"The Definitive Guide SQLite"
On Thu, Jun 5, 2008 at 6:55 PM, Scott Baker <[EMAIL PROTECTED]> wrote:

> I have a database with coordinates in it. I'd like to calculate distance at
> the SQL level, but the equation for calculating distance requires square
> root and I'm not sure if I can do this with sqlite. Something like:
>
> SELECT PlaceName, sqrt((PlaceX - 1)^2 - (PlaceY - 3)^2) AS DistFromHome
> FROM Table WHERE PlaceGroup = 3;
>
> What mathematical functions can I do at the sqlite level? I'll preface this
> with, with stock SQLite (no added components) as it's on a shared web host.
>
> --
> Scott Baker - Canby Telcom
> RHCE - System Administrator - 503.266.8253
> ___
> 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] Bind arguments for insert and not null columns with default values

[Alex Katebi]

It would be nice to be able to revert back to the default value for a
column. I don't think SQLite support this right now.
The closest thing I found is "pragma table_info(foo)". If you prepare this
and then grab the dflt_value for your column.
On Tue, May 20, 2008 at 2:33 PM, Jeff Hamilton <[EMAIL PROTECTED]> wrote:

> Hi all,
>
> I have a table like this
>
> CREATE TABLE foo (bar TEXT NOT NULL DEFAULT 'default_value');
>
> and I'd like to create a reusable statement to do inserts into foo, like
> this:
>
> INSERT INTO foo (bar) VALUES (?);
>
> Sometimes I have values for bar and sometimes I don't and want the
> default. Is there any way to indicate to the statement that I want the
> bound parameter to be "nothing" therefore giving me the default value?
> If I bind that column to NULL I get a constraint error.
>
> -Jeff
> ___
> 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 Authorizer Feature Suggestion

[Alex Katebi]

Hi Mihia,

   Since I am using in-memory database I only have one connection. I don't
want the limiting factors to limit all stmt, only some.
Regarding pointers used in a remote process. There is no harm if one
is aware of the pointer belonging to a remote process. A handle can be
anything as long as it is unique.

Thanks,
-Alex



On Tue, Jun 3, 2008 at 2:23 PM, Mihai Limbasan <[EMAIL PROTECTED]> wrote:

> Alex Katebi wrote:
>
>> Hi All,
>>
>> For those of us that use SQLite mostly in-memory. Our context is mostly
>> not
>> {sqlite3*} database pointer, it is {sqlite3_stmt*}.
>>
>> Current API?
>>
>> int sqlite3_set_authorizer(
>>  sqlite3*,
>>  int (*xAuth)(void*,int,const char*,const char*,const char*,const char*),
>>  void *pUserData
>> );
>>
>> Can we add the following API in the future?
>>
>> int sqlite3_stmt_set_authorizer(
>>  sqlite3_stmt*,
>>  int (*xAuth)(void*,int,const char*,const char*,const char*,const char*),
>>  void *pUserData
>> );
>>
>>
>>
> Why overload the API when you could simply use sqlite3_db_handle? Pass it
> the your sqlite3_stmt* and you'll get the sqlite3 * to which your prepared
> statement belongs. You could even (ab)use the preprocessor to redefine the
> authorizer callback setter to always implicitely call sqlite3_db_handle (I
> personally dislike preprocessor magic that violates the principle of least
> astonishment, but you expect to be the sole maintainer for the code it's
> very much OK.)
>
> I have a user interface RPC for my application that configures and gets
>> status from my in-memory server database.
>>
>>
> That sounds like you are using raw pointers as handles passing them to
> remote consumers. If at all possible, I recommend you avoid this practice -
> it's terrible from a security standpoint, and it's questionable from a
> robustness standpoint. User mode pointers should be considered valid only
> within their defined domain, i.e. the address space of the process, and any
> type of pointers (including kernel pointers) should be considered valid only
> on the local machine.
>
> ___
> 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] SQLite Authorizer Feature Suggestion

[Alex Katebi]

Hi All,

For those of us that use SQLite mostly in-memory. Our context is mostly not
{sqlite3*} database pointer, it is {sqlite3_stmt*}.

Current API?

int sqlite3_set_authorizer(
  sqlite3*,
  int (*xAuth)(void*,int,const char*,const char*,const char*,const char*),
  void *pUserData
);

Can we add the following API in the future?

int sqlite3_stmt_set_authorizer(
  sqlite3_stmt*,
  int (*xAuth)(void*,int,const char*,const char*,const char*,const char*),
  void *pUserData
);

I have a user interface RPC for my application that configures and gets
status from my in-memory server database.
I like to be able to limit that activity. Any body else would like to see
this feature?

I hope Dr. Hipp reads this email.

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

Re: [sqlite] Sqlite on RAM

[Alex Katebi]

All your dbs will have different values. It is a C pointer value not an
enumeration value.

On Tue, Jun 3, 2008 at 8:28 AM, Hildemaro Carrasquel <
[EMAIL PROTECTED]> wrote:

> Hello.-
>
> but how can i make a different between all db on RAM if you define as
> :memory: ?
>
> --
> Ing. Hildemaro Carrasquel
> Ingeniero de Proyectos
> Cel.: 04164388917/04121832139
> ___
> 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] reading a row that has been deleted

[Alex Katebi]

Keith,

   For normal operations the writer will wait until the reading is done. But
I have a client that is remote and is very slow and could sit on a select
statement indefinitly.  In this case I would need to create a temp table.
Thanks,
-Alex


On Mon, Jun 2, 2008 at 11:53 AM, Keith Goodman <[EMAIL PROTECTED]> wrote:

> On Mon, Jun 2, 2008 at 8:12 AM, Alex Katebi <[EMAIL PROTECTED]> wrote:
> > Hi Keith,
> >
> >   Your observation is correct. I did not know that when selecting a table
> a
> > shared lock is aquired by the reader and writes are locked out until the
> > last row is read or stmt is finialized. This is true even for in-memory
> > database.
> >
> >   One cure for this problem is to create a temorary table based on the
> > result set of the select statement. Then this temp table can be read
> without
> > locking out writers from the original table.
> >
> > CREATE TABLE t1select AS SELECT * FROM t1;
>
> How about keep trying to write until the database is not busy? Would
> that work? Then you only have one copy of the data.
>
> Creating the temp table may be faster than a fancy select statement
> but the problem, while less frequent, still remains (reading while the
> db is locked for writing).
>  ___
> 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] reading a row that has been deleted

[Alex Katebi]

Hi Keith,

   Your observation is correct. I did not know that when selecting a table a
shared lock is aquired by the reader and writes are locked out until the
last row is read or stmt is finialized. This is true even for in-memory
database.

   One cure for this problem is to create a temorary table based on the
result set of the select statement. Then this temp table can be read without
locking out writers from the original table.

CREATE TABLE t1select AS SELECT * FROM t1;

Thanks,
-Alex



On Sun, Jun 1, 2008 at 5:37 PM, Keith Goodman <[EMAIL PROTECTED]> wrote:

>  On Sun, Jun 1, 2008 at 2:19 PM, Alex Katebi <[EMAIL PROTECTED]>
> wrote:
> > Hi All,
> >
> >   I have a in-memory database with some tables. Each table has a single
> > record writer and multiple readers.
> > Readers and writes prepare their own sqlite3_stmt for the db. Everyone
> > operates within a single thread.
> > What happens if a reader wants to read a record that has been deleted by
> the
> > writer?
>
> I must be too new to understand the question. But if the record is
> deleted then you can't select it. Are you worried about a race
> condition? I think sqlite takes care of those with locks.
> ___
> 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] reading a row that has been deleted

[Alex Katebi]

Hi All,

   I have a in-memory database with some tables. Each table has a single
record writer and multiple readers.
Readers and writes prepare their own sqlite3_stmt for the db. Everyone
operates within a single thread.
What happens if a reader wants to read a record that has been deleted by the
writer?

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

Re: [sqlite] In memory data base questions ???

[Alex Katebi]

THANKS Igor!

On Tue, Apr 29, 2008 at 6:38 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> Alex Katebi <[EMAIL PROTECTED]> wrote:
> > Just want to make a note that when I say in-memory data base I don't
> > mean
> > the cache memory I mean the :memory: date base.
>
> That's how I understood you, yes.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Authorize Functions Too Coarse

[Alex Katebi]

   I have an embedded application that uses a single :memory:  database
connection. This application has a user interface via a networking socket
that receives SQL commands. The application tasks and the user interface
will use the single :memory: connection. I want to limit the user interface
for access. Unfortunately the Authorise Functions are tied to the database
connections rather than statements.

   Basically the application tasks should be able to do anything and user
interface task to be limited. Since I am running in-memory database, the
Authorization Functions do not give me the differentiation that I need.

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

Re: [sqlite] a suggestion to write tutorial for sqlite

[Alex Katebi]

http://www.amazon.com/Definitive-Guide-SQLite/dp/1590596730

Excellent book!



On Sat, Apr 19, 2008 at 12:37 PM, mikeobe <[EMAIL PROTECTED]> wrote:

> i found it boring to learn how to use sqlite, maybe we can write a
> tutorial for it, with examples,
> it will be much easier for the beginner to start with sqlite.
>
> 2008-04-19
> mikeobe
>
>
> ___
> 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 data base questions ???

[Alex Katebi]

Just want to make a note that when I say in-memory data base I don't mean
the cache memory I mean the :memory: date base.

On Tue, Apr 29, 2008 at 5:39 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> Alex Katebi <[EMAIL PROTECTED]> wrote:
> >   I am planning to use only in-memory data base for my application. I
> > have couple of questions?
> >
> >  1) Would SQLite still make Rollback Journal files?
>
> No.
>
> >  2) If I have a single in-memory connection with multiple
> > statements, should I worry about table locks?
>
> No.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In memory data base questions ???

[Alex Katebi]

Hi Igor,

   So are you telling me that if a statement has started a select on a
table with a delayed finalize another statement can update the same table?

   The reason I ask this question is that in my application a user can start
a query and take his time before he ends it. Meanwhile the same table needs
to be updated by my application. Can you tell be why there would be no lock
contention?

Thanks!
-Alex



On Tue, Apr 29, 2008 at 5:39 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> Alex Katebi <[EMAIL PROTECTED]> wrote:
> >   I am planning to use only in-memory data base for my application. I
> > have couple of questions?
> >
> >  1) Would SQLite still make Rollback Journal files?
>
> No.
>
> >  2) If I have a single in-memory connection with multiple
> > statements, should I worry about table locks?
>
> No.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] In memory data base questions ???

[Alex Katebi]

Hi,

   I am planning to use only in-memory data base for my application. I have
couple of questions?

  1) Would SQLite still make Rollback Journal files? If yes can it be turned
off with pragma, etc.?
  2) If I have a single in-memory connection with multiple
statements, should I worry about table locks?

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

Re: [sqlite] One connection with 2 statement handle.

[Alex Katebi]

Does this mean that if I have one connection and multiple statements
operating on a in-memory table within a single thread no statement will lock
out another? In other words locks will not be used?

On Sat, Apr 26, 2008 at 7:40 PM, Joanne Pham <[EMAIL PROTECTED]> wrote:

> Hi,
> One connection(sqlite3 *pDb) uses by multiple statements
> handle(sqlite3_stmt).
> So one connection is used by two multiple statements concurrently one for
> insertion and one for selection. Is that ok.
>
> Have any one experienced this situation.
> Thanks,
> JP
>
>
>
>  
> 
> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile.  Try it now.
> http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
> ___
> 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] long queries ???

[Alex Katebi]

Hello Everyone,

   I am designing an IP Routing Software. Work arrives via telnet clients or
routing control packets. I am planning to use the POSIX select function to
handle all work in a single loop in one thread. I will have one
SQLite memory connection for my routing tables. When a user starts a query
into one of the routing tables the telnet socket will block on a write. So
when other work arrives via the select function my SQLite connection is in a
middle of a query. I can not use my database connection any more until the
blocked query is done. I don't want to abort the query. I looked into the
shared cache mode a bit but I am not sure if this is the answer. Is it
possible to break a querie into multiple SQLite compilations.

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

Re: [sqlite] how to select uncomitted rows?

[Alex Katebi]

Scott,

   Because of the issues that you have raised I realized that multiple
configuration commands rolled into one transaction is not a good idea and is
not necessary. The router will act on a single command from any CLI session.
As far as command action goes it will be connection less towards CLI
sessions. When any user asks to show configuration I will show running
configuration to that user. I think a complicated design is a bad design.

Thanks!
-Alex

On Thu, Apr 17, 2008 at 8:17 PM, Scott Hess <[EMAIL PROTECTED]> wrote:

> What will happen if you use BEGIN is that multiple users can get into
> the configuration mode, but once one user gets past BEGIN and runs
> anything which updates the database, the updates in other transactions
> will start throwing SQLITE_LOCKED.  Spin up two sqlite3 command-line
> tools against the same database and check it out.
>
> If you use BEGIN IMMEDIATE, then this problem won't occur, because
> multiple threads can't get past BEGIN IMMEDIATE on the same database
> in the first place :-).
>
> What you have is basically a revision-control problem.  If you let
> multiple users configure at the same time, you're going to handle
> merging the config changes in a sensible way and handle conflicts.
>
> -scott
>
>
> On Thu, Apr 17, 2008 at 5:08 PM, Alex Katebi <[EMAIL PROTECTED]>
> wrote:
> > Scott,
> >
> >   Every user will have thier own sqlite connection. So multiple users
> are
> > allowed for configuration. There will be one router connection to
> actually
> > act on the commited configurations. The router will act on
> > individual configuration rows. The router and the users interact with
> each
> > other via the database file only. They run on seperate processes. All
> the
> > router sees is configuration rows being inserted deleted or updated by
> > whomever. It does not care. So here is my next question. If I have
> temporary
> > triggers for the CLI users to keep track of the uncommited rows. Then I
> have
> > another temoprary trigger for the router to act on the configurations
> after
> > being committed, would this work?  Would the temporary trigger in the
> router
> > connection actually trigger? I guess I need to try this out.
> >
> >   This is my own home project. I am my own boss. Once I have my design
> > figured out I will post it for analysis.
> >
> > Thanks,
> > -Alex
> >
> >
> > On Thu, Apr 17, 2008 at 7:26 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> >
> >> OK.  I suspect that you might find exposing the SQLite transaction
> >> semantics as part of your user interface may be ... unsatisfactory.
> >> For instance, by keeping a long-lived transaction in this way, you
> >> cannot set any of the _other_ data in the config and commit it.  This
> >> would include other users, so, for instance, while one user is
> >> configuring something really complex, like firewall rules, another
> >> user would not be able to set the timezone, or turn on logging, or
> >> something like that.  I don't know, this may be satisfactory, but it
> >> seems like a regrettable thing to design into the system at such a low
> >> level (not letting multiple people configure so that they don't screw
> >> up is good, but not allowing it just because your design didn't allow
> >> it, less good).
> >>
> >> As an alternative, you might consider layering your config-management
> >> over something like the undo/redo example (*).  Since this is more
> >> explicit (_you_ craft the structures in terms of SQLite, rather than
> >> relying on SQLite's internal semantics), when upper management comes
> >> to you with some crazy feature request which does not conform to the
> >> SQL transaction model, you'll be able to change things without too
> >> much pain.
> >>
> >> -scott
> >>
> >> (*) http://www.sqlite.org/cvstrac/wiki?p=UndoRedo
> >>
> >>
> >> On Thu, Apr 17, 2008 at 3:56 PM, Alex Katebi <[EMAIL PROTECTED]>
> >> wrote:
> >> >   I am glad you asked. I am designing an interactive command line
> >> > interface to an ip router. A user will begin a transaction and start
> >> > configuring. At any time he can query for his configurations since
> the
> >> > begining of the transaction. When he is satisfied with his
> configuration
> >> he
> >> > will commit the configuration. After this his query should show
> nothing
> >> > until he begins another transaction. Also he might press the ?mark
> key
> >> on
> 

Re: [sqlite] how to select uncomitted rows?

[Alex Katebi]

Scott,

   Every user will have thier own sqlite connection. So multiple users are
allowed for configuration. There will be one router connection to actually
act on the commited configurations. The router will act on
individual configuration rows. The router and the users interact with each
other via the database file only. They run on seperate processes. All the
router sees is configuration rows being inserted deleted or updated by
whomever. It does not care. So here is my next question. If I have temporary
triggers for the CLI users to keep track of the uncommited rows. Then I have
another temoprary trigger for the router to act on the configurations after
being committed, would this work?  Would the temporary trigger in the router
connection actually trigger? I guess I need to try this out.

   This is my own home project. I am my own boss. Once I have my design
figured out I will post it for analysis.

Thanks,
-Alex


On Thu, Apr 17, 2008 at 7:26 PM, Scott Hess <[EMAIL PROTECTED]> wrote:

> OK.  I suspect that you might find exposing the SQLite transaction
> semantics as part of your user interface may be ... unsatisfactory.
> For instance, by keeping a long-lived transaction in this way, you
> cannot set any of the _other_ data in the config and commit it.  This
> would include other users, so, for instance, while one user is
> configuring something really complex, like firewall rules, another
> user would not be able to set the timezone, or turn on logging, or
> something like that.  I don't know, this may be satisfactory, but it
> seems like a regrettable thing to design into the system at such a low
> level (not letting multiple people configure so that they don't screw
> up is good, but not allowing it just because your design didn't allow
> it, less good).
>
> As an alternative, you might consider layering your config-management
> over something like the undo/redo example (*).  Since this is more
> explicit (_you_ craft the structures in terms of SQLite, rather than
> relying on SQLite's internal semantics), when upper management comes
> to you with some crazy feature request which does not conform to the
> SQL transaction model, you'll be able to change things without too
> much pain.
>
> -scott
>
> (*) http://www.sqlite.org/cvstrac/wiki?p=UndoRedo
>
>
> On Thu, Apr 17, 2008 at 3:56 PM, Alex Katebi <[EMAIL PROTECTED]>
> wrote:
> >   I am glad you asked. I am designing an interactive command line
> > interface to an ip router. A user will begin a transaction and start
> > configuring. At any time he can query for his configurations since the
> > begining of the transaction. When he is satisfied with his configuration
> he
> > will commit the configuration. After this his query should show nothing
> > until he begins another transaction. Also he might press the ?mark key
> on
> > his keyboard at anytime for help information or tab key for automatic
> > command token completion.
> >
> > So I will have to know what are the list of commands since the beginning
> > of his transaction.
> >
> >
> >
> > On Thu, Apr 17, 2008 at 3:53 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> >
> >> I don't mean in a separate database table - I mean in an in-memory
> >> hashtable or array or something of the sort.  Depending on what the
> >> real goal you're trying to accomplish is, you might use triggers to
> >> call  custom function to accomplish this.
> >>
> >> You presumably desire to get this information in the interests of
> >> implementing a solution to a problem.  You should perhaps post asking
> >> for suggestions on how to solve the problem.  I think the question
> >> itself probably indicates that there's a disconnect in how you're
> >> trying to model the problem, but without knowing what the problem is,
> >> it's hard to do much.
> >>
> >> -scott
> >>
> >> On Thu, Apr 17, 2008 at 12:43 PM, Alex Katebi <[EMAIL PROTECTED]>
> >> wrote:
> >> > The reason I did not keep track in a seperate table was because I
> wanted
> >> to
> >> > do it using triggers. But triggers don't trigger until commit.
> >> >
> >> > On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> >> >
> >> >> Until the data is committed, it's not really in the database.  If
> you
> >> >> crash, it will be rolled back.  So if it's really important to know
> >> >> what data has been written to the database but not committed, why
> >> >> don't you just track what you're writing to the database in an
> >> >> in-memory 

Re: [sqlite] how to select uncomitted rows?

[Alex Katebi]

   I am glad you asked. I am designing an interactive command line
interface to an ip router. A user will begin a transaction and start
configuring. At any time he can query for his configurations since the
begining of the transaction. When he is satisfied with his configuration he
will commit the configuration. After this his query should show nothing
until he begins another transaction. Also he might press the ?mark key on
his keyboard at anytime for help information or tab key for automatic
command token completion.

So I will have to know what are the list of commands since the beginning
of his transaction.



On Thu, Apr 17, 2008 at 3:53 PM, Scott Hess <[EMAIL PROTECTED]> wrote:

> I don't mean in a separate database table - I mean in an in-memory
> hashtable or array or something of the sort.  Depending on what the
> real goal you're trying to accomplish is, you might use triggers to
> call  custom function to accomplish this.
>
> You presumably desire to get this information in the interests of
> implementing a solution to a problem.  You should perhaps post asking
> for suggestions on how to solve the problem.  I think the question
> itself probably indicates that there's a disconnect in how you're
> trying to model the problem, but without knowing what the problem is,
> it's hard to do much.
>
> -scott
>
> On Thu, Apr 17, 2008 at 12:43 PM, Alex Katebi <[EMAIL PROTECTED]>
> wrote:
> > The reason I did not keep track in a seperate table was because I wanted
> to
> > do it using triggers. But triggers don't trigger until commit.
> >
> > On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
> >
> >> Until the data is committed, it's not really in the database.  If you
> >> crash, it will be rolled back.  So if it's really important to know
> >> what data has been written to the database but not committed, why
> >> don't you just track what you're writing to the database in an
> >> in-memory data structure of some sort?  Or, to save space, just track
> >> the rowid of the rows you modify.
> >>
> >> -scott
> >>
> >>
> >> On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED]>
> >> wrote:
> >> > Hi Richard,
> >> >
> >> > create table t1 (name);
> >> > insert into t1 values ('Alex');
> >> > begin;
> >> > insert into t1 values ('Richard');
> >> > select * from t1;
> >> >
> >> > How can I select only the second row in the above example?
> >> > If there is not an easy way to do this I would probably have to use
> >> another
> >> > connection then diff the two selects right?
> >> >
> >> > Thanks,
> >> > -Alex
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]>
> wrote:
> >> >
> >> >>
> >> >> On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
> >> >> > Is there a way to select rows that have not been committed yet?
> >> >> >
> >> >>
> >> >> No.  SQLite doesn't really commit rows.  It commits pages.  A
> >> >> single page might hold multiple rows, only some of which might
> >> >> have changed.  Or a single row might span multiple pages.
> >> >>
> >> >>
> >> >> D. Richard Hipp
> >> >> [EMAIL PROTECTED]
> >> >>
> >> >>
> >> >>
> >> >> ___
> >> >> 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] how to select uncomitted rows?

[Alex Katebi]

Yap I was wrong about triggers. Triggers are part of the same connection. So
I will try your suggestions. I will let you know how I made out. And thanks
so much for clearing my mistakes.
-Alex

On Thu, Apr 17, 2008 at 6:06 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:

> I remember trying it before but I will try it again. Maybe I was wrong. I
> will let you know.
> Thanks!
>
>   On Thu, Apr 17, 2008 at 4:43 PM, Dennis Cote <[EMAIL PROTECTED]>
> wrote:
>
> > Alex Katebi wrote:
> > > But triggers don't trigger until commit.
> > >
> >
> > That is not true.
> >
> > Trigger code executes inline with the statement that caused the trigger
> > to fire.
> >
> > Try a few triggers with the command line shell to convince yourself.
> >
> > Dennis Cote
> >  ___
> > 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] selecting uncommited rows

[Alex Katebi]

Hi Dennis,

I am sorry I was wrong about triggers. My understanding of triggers was
incorrect. Triggers are part of the same connection. I will take your
suggestions. I will let you know.
Thanks so much for helping out!
-Alex

On Thu, Apr 17, 2008 at 6:11 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:

> Alex Katebi wrote:
> > My problem is that triggers don't trigger until after commit.
> >
>
> No, that is not your problem. You haven't tried anything yet.
>
> This is a trace of SQlite executing the code I posted. There is not a
> commit in sight, and yet it works as expected. I added an unqualified
> select to dump the entire table so you can see the difference.
>
> SQLite version 3.5.7
> Enter ".help" for instructions
> sqlite> create table t1(name);
> sqlite> insert into t1 values('Alex');
> sqlite>
> sqlite> create table change_log (id integer primary key);
> sqlite>
> sqlite> create trigger in_t1 after insert on t1
>...> begin
>...> insert into change_log values(new.rowid);
>...> end;
> sqlite>
> sqlite> delete from change_log;
> sqlite> begin;
> sqlite> insert into t1 values('Dennis');
> sqlite> select * from t1 where rowid in (select id from change_log);
> Dennis
> sqlite> select * from t1;
> Alex
> Dennis
> sqlite>
>
> Try it before you decide it won't work.
>
> Dennis Cote
> ___
> 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] how to select uncomitted rows?

[Alex Katebi]

I remember trying it before but I will try it again. Maybe I was wrong. I
will let you know.
Thanks!

On Thu, Apr 17, 2008 at 4:43 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:

> Alex Katebi wrote:
> > But triggers don't trigger until commit.
> >
>
> That is not true.
>
> Trigger code executes inline with the statement that caused the trigger
> to fire.
>
> Try a few triggers with the command line shell to convince yourself.
>
> Dennis Cote
>  ___
> 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] selecting uncommited rows

[Alex Katebi]

My problem is that triggers don't trigger until after commit.

On Thu, Apr 17, 2008 at 4:52 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:

> Alex Katebi wrote:
> > I will give a simple example:
> >
> > create table t1(name);
> > insert into t1('Alex');
> > begin;
> > insert into t1 values ('Dennis');
> > select * from t1;
> >
> > How can I see only the 'Dennis' row in this simple example.
> >
>
> As I said before, use a trigger to keep track of the changes.
>
> create table t1(name);
> insert into t1('Alex');
>
> create table change_log (id integer primary key);
>
> create trigger in_t1 after insert on t1
> begin
> insert into change_log values(new.rowid);
> end;
>
> delete from change_log;
> begin;
> insert into t1 values ('Dennis');
> select * from t1 where rowid in (select id from change_log);
>
> Now either commit or rollback to end your transaction.
>
> Dennis Cote
>
> ___
> 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] how to select uncomitted rows?

[Alex Katebi]

The reason I did not keep track in a seperate table was because I wanted to
do it using triggers. But triggers don't trigger until commit.

On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote:

> Until the data is committed, it's not really in the database.  If you
> crash, it will be rolled back.  So if it's really important to know
> what data has been written to the database but not committed, why
> don't you just track what you're writing to the database in an
> in-memory data structure of some sort?  Or, to save space, just track
> the rowid of the rows you modify.
>
> -scott
>
>
> On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED]>
> wrote:
> > Hi Richard,
> >
> > create table t1 (name);
> > insert into t1 values ('Alex');
> > begin;
> > insert into t1 values ('Richard');
> > select * from t1;
> >
> > How can I select only the second row in the above example?
> > If there is not an easy way to do this I would probably have to use
> another
> > connection then diff the two selects right?
> >
> > Thanks,
> > -Alex
> >
> >
> >
> >
> >
> > On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> >
> >>
> >> On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
> >> > Is there a way to select rows that have not been committed yet?
> >> >
> >>
> >> No.  SQLite doesn't really commit rows.  It commits pages.  A
> >> single page might hold multiple rows, only some of which might
> >> have changed.  Or a single row might span multiple pages.
> >>
> >>
> >> D. Richard Hipp
> >> [EMAIL PROTECTED]
> >>
> >>
> >>
> >> ___
> >> 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] how to select uncomitted rows?

[Alex Katebi]

Hi Richard,

create table t1 (name);
insert into t1 values ('Alex');
begin;
insert into t1 values ('Richard');
select * from t1;

How can I select only the second row in the above example?
If there is not an easy way to do this I would probably have to use another
connection then diff the two selects right?

Thanks,
-Alex





On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

>
> On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote:
> > Is there a way to select rows that have not been committed yet?
> >
>
> No.  SQLite doesn't really commit rows.  It commits pages.  A
> single page might hold multiple rows, only some of which might
> have changed.  Or a single row might span multiple pages.
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] selecting uncommited rows

[Alex Katebi]

I will give a simple example:

create table t1(name);
insert into t1('Alex');
begin;
insert into t1 values ('Dennis');
select * from t1;

The above will show two rows. How can I see only the 'Dennis' row in this
simple example.



On Thu, Apr 17, 2008 at 2:57 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:

> Alex Katebi wrote:
> > Actually I am not interested on rows that have been committed. I am
> > interested on the rows that have been changed but not commited yet. As I
> > understand the triggers trigger of of a commit.
> > The example that you are refering to is for undoing the already commited
> > rows. I am merely interested in seeing the rows that are in my
> transaction
> > queue before the commit.
> >
>
> SQLite does not have a transaction queue.
>
> The data that you have changed is already stored in the database before
> you do the commit. The commit simply removes the information that would
> be used to do a rollback.
>
> The page I referred you to was an example of using triggers to track
> changes to tables. This is what you want to do if I understand you
> correctly.
>
> Use triggers to track the rows that are changed by your transaction's
> insert, update, and delete statements. Then use a select to display the
> current values (i.e. the value that will be committed) for these rows
> only.
>

> HTH
> Dennis Cote
> ___
> 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] how to select uncomitted rows?

[Alex Katebi]

Is there a way to select rows that have not been committed yet?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] selecting uncommited rows

[Alex Katebi]

Actually I am not interested on rows that have been committed. I am
interested on the rows that have been changed but not commited yet. As I
understand the triggers trigger of of a commit.
The example that you are refering to is for undoing the already commited
rows. I am merely interested in seeing the rows that are in my transaction
queue before the commit.

On Thu, Apr 17, 2008 at 1:54 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:

> Alex Katebi wrote:
> >Can you give an example on how to use this. Basically I want to see
> > (select) only the uncommited rows.
> >
>
> You will have to keep track of the rows that have been changed yourself.
>
> You can have SQLite do it for you if you create a change_log table and
> then setup triggers to add the rowids of any rows modified during the
> transaction. You clear this table at the beginning of your transaction.
> The triggers will insert rows for each change to the table. At the end
> of the transaction you can select all the rows from the main table that
> have their rowids stored in the change_log table.
>
> If you want to get fancier you can look at this page for more ideas
> http://www.sqlite.org/cvstrac/wiki?p=UndoRedo
>
> HTH
> Dennis Cote
>  ___
> 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] selecting uncommited rows

[Alex Katebi]

Martin,
   Can you give an example on how to use this. Basically I want to see
(select) only the uncommited rows.

Thanks!
-Alex

On Thu, Apr 17, 2008 at 11:58 AM, Ken <[EMAIL PROTECTED]> wrote:

> I believe the pragma read_uncommitted only works for shared cache mode and
> threads.
>
> You'll have to select the data from the same connection that created the
> data.
>
>
>
> "Martin.Engelschalk" <[EMAIL PROTECTED]> wrote: Hi,
>
> there is a pragma: PRAGMA read_uncommitted = 1;
> You can select the uncommitted data and show ist before commit.
>
> Have a look here: http://www.sqlite.org/pragma.html
>
> Martin
>
> Alex Katebi schrieb:
> > Hi All,
> >
> > Let's say I start a transaction and do bunch of insertions etc. Before
> my
> > commit I like to show (select) what I have configured.
> > How can I accompilish this?
> >
> > Thanks,
> > -Alex
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
>
> --
>
> * Codeswift GmbH *
> Traunstr. 30
> A-5026 Salzburg-Aigen
> Tel: +49 (0) 8662 / 494330
> Mob: +49 (0) 171 / 4487687
> Fax: +49 (0) 12120 / 204645
> [EMAIL PROTECTED]
> www.codeswift.com / www.swiftcash.at
>
> Codeswift Professional IT Services GmbH
> Firmenbuch-Nr. FN 202820s
> UID-Nr. ATU 50576309
>
> ___
> 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] selecting uncommited rows

[Alex Katebi]

Hi All,

Let's say I start a transaction and do bunch of insertions etc. Before my
commit I like to show (select) what I have configured.
How can I accompilish this?

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

Re: [sqlite] resetting a column back to its default value

[Alex Katebi]

Hello Puneet,

  I guess I can select and reinsert all columns except for the column with
the default value.
I guess it would be nice (efficient) to update a single column to its
default value via the update command.

Thanks,
-Alex

On Tue, Mar 25, 2008 at 1:40 PM, P Kishor <[EMAIL PROTECTED]> wrote:

> On 3/25/08, Alex Katebi <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> >  I was woundering how I can update a column in my table back to its
> default
> >  value.
> >
> >  For example:
> >
> >  create table t1 (value integer default 55, name text);
> >  insert into t1(name) values('hello');
> >  update t1 set value=default;  /* for illustration only */
>
> you do realize that the above update will try to update the value of
> the column 'value' to the value of a mysterious column called
> 'default'. Still, going on...
>
> >
> >  How can I achive the desired behavior short of doing "update t1 set
> >  value=55"
> >
>
> insert into t1(name) values('hello');
>
> automatically sets the value of the column called 'value' to 55, so
> your question is answered by exactly what you are doing.
>
>
> >  Thanks,
> >  -Alex
>
>
> --
> Puneet Kishor
> ___
> 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] resetting a column back to its default value

[Alex Katebi]

Hello Puneet,



On Tue, Mar 25, 2008 at 1:40 PM, P Kishor <[EMAIL PROTECTED]> wrote:

> On 3/25/08, Alex Katebi <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> >  I was woundering how I can update a column in my table back to its
> default
> >  value.
> >
> >  For example:
> >
> >  create table t1 (value integer default 55, name text);
> >  insert into t1(name) values('hello');
> >  update t1 set value=default;  /* for illustration only */
>
> you do realize that the above update will try to update the value of
> the column 'value' to the value of a mysterious column called
> 'default'. Still, going on...
>
> >
> >  How can I achive the desired behavior short of doing "update t1 set
> >  value=55"
> >
>
> insert into t1(name) values('hello');
>
> automatically sets the value of the column called 'value' to 55, so
> your question is answered by exactly what you are doing.
>
>
> >  Thanks,
> >  -Alex
>
>
> --
> Puneet Kishor
> ___
> 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] resetting a column back to its default value

[Alex Katebi]

Hi,

I was woundering how I can update a column in my table back to its default
value.

For example:

create table t1 (value integer default 55, name text);
insert into t1(name) values('hello');
update t1 set value=default;  /* for illustration only */

How can I achive the desired behavior short of doing "update t1 set
value=55"

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

[sqlite] can not make indexing work fro me

[Alex Katebi]

Hi All,

I am using sqlite to make an IP routing table. A routing table has a
Destination and a Mask column. I chose blob for my types since IPv6 will be
16 bytes long, IPv4 is only 4 bytes.

The query will be some IP packet destination address that needs to be
forwarded in the internet. This address is anded bitwise with the mask in
the table then tested for equality with the desination. All the rows that
pass this test will be candidates, then the row with the longest mask length
is chosen. Packet is then forwarded towards the nexthop address that was
stored as another column in the same table row.

An IPv4 Example:

create table rtm(dest blob, mask blob, nh blob);
create index rtmi on rtm(dest);
select nh from rtm where dest = bitwise_and(addr, mask) order by mask desc
limit 1;

hex(dest) hex(mask)   rowid
  --   ---
01010100 FF00  1
 0   2
0101 3
0100 FF00 4
04048000 C0005
7780 FFF0   6

So for a packet dest address 01010165, there will be four hits row 1,2,3,4
but 1 has the longest bit mask.

Every thing works except for the indexing. My custom function bitwise_and is
always called for all the rows in my table. Which means that indexing is not
being used.

What should I do to get around this problem. Typically I will have about
200,000 enteries in my routing table for the internet and I want to be more
efficient and not query every row. I am open to all suggestions.

Thanks,
Alex

Re: [sqlite] SQLite --> PostGres

[alex katebi]

I don't think many people have your problem. I mean using Sqlite towards using 
PostGres. 
Please let's not pollute Sqlite. 
 
Thanks,
Alex Katebi



- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, January 11, 2008 2:19:23 PM
Subject: [sqlite] SQLite --> PostGres

I have been writing an app using SQLite (great to develop with, and to
serve with to an extent), always with the intent of one day upsizing
it to PostGres (pain in the tush to develop with, but great to serve
with to any extent). Much to my delight, I am finding that y'all (the
SQLite developers) have made many things (for example, datatypes)
similar to PostGres (yes, I know most all about how SQLite datatypes).
My question -- why not take it all the way, and make SQLite almost a
mini-PostGres... wait, before you chide me -- I don't mean in the "add
more features" way, but in the "make syntax and datatypes as similar
as possible" way.

For example, why have the "INTEGER PRIMARY KEY" when it could just as
easily be called "SERIAL"?

One way might be to allow for aliases -- so, SERIAL would mean the
same as INTEGER PRIMARY KEY, or CHAR(3) would mean the same as TEXT
with a CHECK CONSTRAINT, and so on.

Wouldn't that increase the already wildly popular appeal of SQLite
even more so without subtracting or weighing anything down?

By the way, I didn't find a BLOB kind in PostGres -- is that the same
as BYTEA? If yes, that would be another candidate for such an alias.


-- 
Puneet Kishor

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs