[sqlite] Re: suggest an index to make this faster

[Igor Tandetnik]

Dr Gerard Hammond 
wrote:

Could somebody suggest an index(es) I could add to my sqlite v3 (REAL
SQL database)  to make this SELECT faster?

SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total,
TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked FROM
Transn t LEFT OUTER JOIN Category c  on c.CategoryID=t.categoryID
and AcctID in  (12) and t.categoryID in (261, 262, 263, 264, 265,
266, 267, 268, 269)  ORDER BY date_trans


An index on Category(CategoryID). Another one on Transn(AcctID) or 
Transn(AcctID, categoryID): the latter would be preferable over the 
former if you have many distinct values of categoryID for each AcctID.


It may also help a little to replace the IN clause with

261<=t.categoryID and t.categoryID<=269

especially if you go with the composite index.

Igor Tandetnik 



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

[sqlite] suggest an index to make this faster

[Dr Gerard Hammond]

Hi

Could somebody suggest an index(es) I could add to my sqlite v3 (REAL 
SQL database)  to make this SELECT faster?


SELECT date_trans, t.description, c.CategoryAbsolutePath, GST, total, 
TransID, HasSplitTrans, t.categoryID, t.Currency, t.locked FROM 
Transn t LEFT OUTER JOIN Category c  on c.CategoryID=t.categoryID  
and AcctID in  (12) and t.categoryID in (261, 262, 263, 264, 265, 
266, 267, 268, 269)  ORDER BY date_trans





CREATE TABLE Category (CatParent Integer, Description varchar, 
CategoryID integer NOT NULL DEFAULT '0', CategoryAbsolutePath 
varchar, PRIMARY KEY(CategoryID));


CREATE TABLE Transn (GST double DEFAULT '0', AcctID integer, TransID 
integer NOT NULL, CategoryID integer, Total double DEFAULT '0', 
Description varchar, Date_Trans date, Notes varchar, NeedsAttention 
boolean DEFAULT 'False', Reconciled boolean DEFAULT 'False', 
GSTClaimed boolean DEFAULT 'False', HasSplitTrans boolean DEFAULT 
'False', Currency varchar(10) DEFAULT 'AUD', CurrConverter float 
DEFAULT '1.00', ForeignCurrencyAmount double DEFAULT '0', locked 
boolean DEFAULT 'false', PRIMARY KEY(TransID));



TIA
--

Cheers,

Dr Gerard Hammond
MacSOS Solutions Pty Ltd
[EMAIL PROTECTED]  http://www.macsos.com.au

Proofread carefully to see if you any words out.

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

[sqlite] 2 Threads - lock after Commit:

[Matthew Veenstra]

Hello,

I was wondering if someone can shed a bit of light on a problem I am  
having.  I am using SQLite in a client setting on Mac and Windows.  I  
have two threads.  That I use SQLite in.  The main thread uses this  
to get data and display it to screen.  Then I have a worker or  
secondary thread that batch process many commands with a Transaction  
using BEGIN: and COMMIT:.


As the transaction happens on the second thread the main thread  
continues about it's business and displays information.  This seems  
to work just fine...until COMMIT: is called.  Once COMMIT is called I  
get database is locked and I am not able to proceed.


I am not a master of SQLite so I am not sure if this is expected  
behavior.


Here are some things I am doing that might be relevant.
- I have many temp tables that are created and can be opened at this  
time.
- I am using my own built frameworks on the Mac and the default ones  
on Windows.  I build this framework from the preprocessed Windows  
files.  I set these C flags at compile time.

-DTHREADSAFE=1 \
-DSQLITE_THREAD_OVERRIDE_LOCK=-1 \
-DOS_UNIX=1 \
-DHAVE_USLEEP=1 \
-DSQLITE_OMIT_CURSOR

I would expect SQLite to wait for the COMMIT to finish and then be  
able to use the data base just as before.


Am I missing something?

Thx,
Matt
tribalmedia




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

[sqlite] Re: DB design questions

[A. Pagaltzis]

Hi Michael,

* Michael Ruck <[EMAIL PROTECTED]> [2007-04-21 22:35]:
> Thanks for your response. Do you have a recommendation for a
> simpler data store, which supports only simple queries (like,
> equals, not equals on attributes) and transactions?

BerkeleyDB might be a candidate. It only stores key-value pairs,
but keys may have multiple values, and it’s easy to come up with
some convention for composite key names in order to store more
complex objects. (If need be, you store a list of keys under
another key or some such. Depends on what you want to do.) It has
transaction support and as a bonus, it’s much faster than SQLite.

(SQLite is significantly slower than many simpler datastores such
as BDB. The benefit is that you get to write arbitrarily complex
queries abstractly in SQL rather than having to spell them out as
scads of imperative data structure examination code, that then
also has to be debugged and maintained.)

Regards,
-- 
Aristotle Pagaltzis // 

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

AW: [sqlite] Re: DB design questions

[Michael Ruck]

Thanks for your response. Do you have a recommendation for a simpler data
store, which supports only simple queries (like, equals, not equals on
attributes) and transactions?

Thanks,
Mike

-Ursprüngliche Nachricht-
Von: A. Pagaltzis [mailto:[EMAIL PROTECTED] 
Gesendet: Samstag, 21. April 2007 21:17
An: sqlite-users@sqlite.org
Betreff: [sqlite] Re: DB design questions

* Michael Ruck <[EMAIL PROTECTED]> [2007-04-20 16:15]:
> Is there anyone who has experience with this kind of design, do you 
> have better ideas on modelling this kind of data?

This is actually a very typical approach to storing arbitrarily structured
data entities in an SQL database that everyone discovers independently, much
like the adjancecy model is the first thing anyone comes up with for storing
trees in an SQL database.

The problem with this sort of schema (just as with the adjacency
model) is that it makes it very hard to formulate any kind of interesting
query over the data. You’d need a vendor-specific facility for recursive
queries in order to ask anything non- trivial of the database, but such
queries are expensive even where supported, which in SQLite they’re not.
Essentially, you are reducing the SQL engine to a dumb backend store
incapable of complex query logic; complex queries have to be performed in
application code after retrieving the entire set of possibly- relevant data.

You’re better off using some other kind of data store than an SQL database
if you really need storage for that kind of model.

Regards,
--
Aristotle Pagaltzis // 


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



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

Re: [sqlite] sqlite3 shell doesn't install correctly

[Ulrich Schöbel]

On Saturday 21 April 2007 17:52, [EMAIL PROTECTED] wrote:
> Ulrich =?iso-8859-1?q?Sch=F6bel?= <[EMAIL PROTECTED]> wrote:
> > On Saturday 21 April 2007 15:40, Joe Wilson wrote:
> > > Apply this patch to fix this problem.
> >
> > Thanks for the patch, but there is still something wrong in the
> > linking stage.
>
> I do not understand why you are having problems.  Nor can
> I figure out why Joe's patch might help.
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--
It was a case of Murphy's Law enforcement.

I must have changed ldconfig, don't know why and when.
A simple rerun of ldconfig and everything was ok.

I apologize for the noise.

Ulrich

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

[sqlite] Re: DB design questions

[A. Pagaltzis]

* Michael Ruck <[EMAIL PROTECTED]> [2007-04-20 16:15]:
> Is there anyone who has experience with this kind of design, do
> you have better ideas on modelling this kind of data?

This is actually a very typical approach to storing arbitrarily
structured data entities in an SQL database that everyone
discovers independently, much like the adjancecy model is the
first thing anyone comes up with for storing trees in an SQL
database.

The problem with this sort of schema (just as with the adjacency
model) is that it makes it very hard to formulate any kind of
interesting query over the data. You’d need a vendor-specific
facility for recursive queries in order to ask anything non-
trivial of the database, but such queries are expensive even
where supported, which in SQLite they’re not. Essentially, you
are reducing the SQL engine to a dumb backend store incapable of
complex query logic; complex queries have to be performed in
application code after retrieving the entire set of possibly-
relevant data.

You’re better off using some other kind of data store than an SQL
database if you really need storage for that kind of model.

Regards,
-- 
Aristotle Pagaltzis // 

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

Re: [sqlite] sqlite3 shell doesn't install correctly

[drh]

Ulrich =?iso-8859-1?q?Sch=F6bel?= <[EMAIL PROTECTED]> wrote:
> On Saturday 21 April 2007 15:40, Joe Wilson wrote:
> >
> > Apply this patch to fix this problem.
> >
> 
> Thanks for the patch, but there is still something wrong in the
> linking stage.
> 

I do not understand why you are having problems.  Nor can
I figure out why Joe's patch might help.

--
D. Richard Hipp <[EMAIL PROTECTED]>


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

Re: [sqlite] Re: sqlite3 shell doesn't install correctly

[Ulrich Schöbel]

On Saturday 21 April 2007 15:40, Joe Wilson wrote:
> > I've used SQLite-3.3.4 for quite a while, mostly the Tcl binding.
> > A few days ago I installed 3.3.15 and the Tcl binding worked fine.
> > Tonight I downloaded 3.3.16 and compiled it without any errors
> > or warnings and then installed it. When I tried to execute sqlite3
> > the following error appered:
> >
> > sqlite3: symbol lookup error: sqlite3: undefined symbol: sqlite3_io_trace
> >
> > I switched back to 3.3.15 with the same result. A switch back to
> > 3.3.4 behaved normal.
> >
> > What's wrong with 3.3.15 and 3.3.16?
>
> Apply this patch to fix this problem.
>
>
> Index: src/main.c
> ===
> RCS file: /sqlite/sqlite/src/main.c,v
> retrieving revision 1.370
> diff -u -3 -p -r1.370 main.c
> --- src/main.c  18 Apr 2007 14:24:33 -  1.370
> +++ src/main.c  21 Apr 2007 13:35:18 -
> @@ -27,6 +27,7 @@ const char sqlite3_version[] = SQLITE_VE
>  const char *sqlite3_libversion(void){ return sqlite3_version; }
>  int sqlite3_libversion_number(void){ return SQLITE_VERSION_NUMBER; }
>
> +#if !defined(SQLITE_OMIT_TRACE) && defined(SQLITE_ENABLE_IOTRACE)
>  /*
>  ** If the following function pointer is not NULL and if
>  ** SQLITE_ENABLE_IOTRACE is enabled, then messages describing
> @@ -34,6 +35,7 @@ int sqlite3_libversion_number(void){ ret
>  ** are intended for debugging activity only.
>  */
>  void (*sqlite3_io_trace)(const char*, ...) = 0;
> +#endif
>
>  /*
>  ** If the following global variable points to a string which is the
> Index: src/shell.c
> ===
> RCS file: /sqlite/sqlite/src/shell.c,v
> retrieving revision 1.160
> diff -u -3 -p -r1.160 shell.c
> --- src/shell.c 28 Feb 2007 06:14:25 -  1.160
> +++ src/shell.c 21 Apr 2007 13:35:19 -
> @@ -1242,6 +1242,7 @@ static int do_meta_command(char *zLine,
>  }
>}else
>
> +#if !defined(SQLITE_OMIT_TRACE) && defined(SQLITE_ENABLE_IOTRACE)
>if( c=='i' && strncmp(azArg[0], "iotrace", n)==0 ){
>  extern void (*sqlite3_io_trace)(const char*, ...);
>  if( iotrace && iotrace!=stdout ) fclose(iotrace);
> @@ -1261,6 +1262,7 @@ static int do_meta_command(char *zLine,
>}
>  }
>}else
> +#endif
>
>  #ifndef SQLITE_OMIT_LOAD_EXTENSION
>if( c=='l' && strncmp(azArg[0], "load", n)==0 && nArg>=2 ){
>
>
> __
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

Thanks for the patch, but there is still something wrong in the
linking stage.

I have 3 instances of SQLite on my system:
The first is 3.2.1, installed with the system and not used, at least
not by me, library in /usr/lib.
The second is installed in a separate directory tree, version 3.3.4,
which I don't want to change, at least not now.
The third is in /usr/local/..., version 3.3.4 until now, to be substituted
by 3.3.16.

When I run make it produces an sqlite3 binary linked against the
3.3.4 library. I made it temporarily unsearchable, now it links
against the 3.2.1. Next step was to remove the 3.2.1 lib in /usr/lib.
Now the linker doesn't find a library. It simply refuses to accept the
right one. Scanning libtool showed all three lib directories in place.

The shell script sqlite3 runs fine as it did yesterday without the patch.

I'm stuck.

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

[sqlite] Re: sqlite3 shell doesn't install correctly

[Joe Wilson]

> I've used SQLite-3.3.4 for quite a while, mostly the Tcl binding.
> A few days ago I installed 3.3.15 and the Tcl binding worked fine.
> Tonight I downloaded 3.3.16 and compiled it without any errors
> or warnings and then installed it. When I tried to execute sqlite3
> the following error appered:
> 
> sqlite3: symbol lookup error: sqlite3: undefined symbol: sqlite3_io_trace
> 
> I switched back to 3.3.15 with the same result. A switch back to
> 3.3.4 behaved normal.
> 
> What's wrong with 3.3.15 and 3.3.16?


Apply this patch to fix this problem.


Index: src/main.c
===
RCS file: /sqlite/sqlite/src/main.c,v
retrieving revision 1.370
diff -u -3 -p -r1.370 main.c
--- src/main.c  18 Apr 2007 14:24:33 -  1.370
+++ src/main.c  21 Apr 2007 13:35:18 -
@@ -27,6 +27,7 @@ const char sqlite3_version[] = SQLITE_VE
 const char *sqlite3_libversion(void){ return sqlite3_version; }
 int sqlite3_libversion_number(void){ return SQLITE_VERSION_NUMBER; }
 
+#if !defined(SQLITE_OMIT_TRACE) && defined(SQLITE_ENABLE_IOTRACE)
 /*
 ** If the following function pointer is not NULL and if
 ** SQLITE_ENABLE_IOTRACE is enabled, then messages describing
@@ -34,6 +35,7 @@ int sqlite3_libversion_number(void){ ret
 ** are intended for debugging activity only.
 */
 void (*sqlite3_io_trace)(const char*, ...) = 0;
+#endif
 
 /*
 ** If the following global variable points to a string which is the
Index: src/shell.c
===
RCS file: /sqlite/sqlite/src/shell.c,v
retrieving revision 1.160
diff -u -3 -p -r1.160 shell.c
--- src/shell.c 28 Feb 2007 06:14:25 -  1.160
+++ src/shell.c 21 Apr 2007 13:35:19 -
@@ -1242,6 +1242,7 @@ static int do_meta_command(char *zLine, 
 }
   }else
 
+#if !defined(SQLITE_OMIT_TRACE) && defined(SQLITE_ENABLE_IOTRACE)
   if( c=='i' && strncmp(azArg[0], "iotrace", n)==0 ){
 extern void (*sqlite3_io_trace)(const char*, ...);
 if( iotrace && iotrace!=stdout ) fclose(iotrace);
@@ -1261,6 +1262,7 @@ static int do_meta_command(char *zLine, 
   }
 }
   }else
+#endif
 
 #ifndef SQLITE_OMIT_LOAD_EXTENSION
   if( c=='l' && strncmp(azArg[0], "load", n)==0 && nArg>=2 ){


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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

Re: Re: [sqlite] How is the windows binary built?

[Joe Wilson]

--- Miha Vrhovnik <[EMAIL PROTECTED]> wrote:
> >  strip sqlite3.exe
> is not ok at least for DLL, because it strips everything even reallocation 
> info.
> strip --strip-unneeded sqlite3.dll

The GNU toolchain's strip command corrupts the reallocation information for 
MinGW DLLs, so I would not recommend using it.

  http://www.sqlite.org/cvstrac/tktview?tn=1474

Use the configure generated Makefile target "make sqlite3.dll" instead for 
MinGW to have the linker not generate the extra stuff in the first place:

#
# Windows section
#
dll: sqlite3.dll

REAL_LIBOBJ = $(LIBOBJ:%.lo=.libs/%.o)

$(REAL_LIBOBJ): $(LIBOBJ)

sqlite3.def: $(REAL_LIBOBJ)
echo 'EXPORTS' >sqlite3.def
nm $(REAL_LIBOBJ) | grep ' T ' | grep ' _sqlite3_' \
| sed 's/^.* _//' >>sqlite3.def

sqlite3.dll: $(REAL_LIBOBJ) sqlite3.def
$(TCC) -shared -o sqlite3.dll sqlite3.def \
-Wl,"--strip-all" $(REAL_LIBOBJ)


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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

Re: Re: [sqlite] How is the windows binary built?

[Miha Vrhovnik]

>  strip sqlite3.exe
is not ok at least for DLL, because it strips everything even reallocation info.
strip --strip-unneeded sqlite3.dll

Regards,
Miha

"Joe Wilson" <[EMAIL PROTECTED]> wrote on 21.4.2007 5:48:00:
>--- [EMAIL PROTECTED] wrote:
>> I'm using cygwin under windows XP.
>> gcc version 3.4.4
>>
>> I unzipped the sqlite-3.3.16.tar.gz to the directory sqlite-3.3.16.
>>
>> Executed the following:
>>
>> cd sqlite-3.3.16
>> mkdir build
>> cd build
>> ./configure
>> make
>>
>> The resulting sqlite3.exe is 4 times bigger than the windows release in 
>> sqlite-3.3.16.zip.
>>
>> Can anyone explain the size difference?
>
>The debug symbols.
>
>  strip sqlite3.exe
>
>The sqlite3.exe posted on the website is built with a MinGW gcc cross
>compiler hosted on Linux. It is not dependent on cygwin DLLs, unlike the
>cygwin version:
>
># cygwin
>$ cygcheck ./sqlite3.exe
>.\sqlite3.exe
>  c:\cygwin\bin\cygwin1.dll
>C:\WINNT\system32\ADVAPI32.DLL
>  C:\WINNT\system32\NTDLL.DLL
>  C:\WINNT\system32\KERNEL32.DLL
>  C:\WINNT\system32\RPCRT4.DLL
>  c:\cygwin\bin\cygreadline6.dll
>c:\cygwin\bin\cygncurses-8.dll
>C:\WINNT\system32\USER32.dll
>  C:\WINNT\system32\GDI32.DLL
>
># MinGW
>$ cygcheck ./sqlite3.exe
>.\sqlite3.exe
>  C:\WINNT\system32\KERNEL32.dll
>C:\WINNT\system32\NTDLL.DLL
>  C:\WINNT\system32\msvcrt.dll
>
>DLLs aside, the cygwin version has the advantage of playing nice with
>rxvt and xterm.
>
>__
>Do You Yahoo!?
>Tired of spam?  Yahoo! Mail has the best spam protection around
>http://mail.yahoo.com
>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-
>
>


--
It's time to get rid of your current e-mail client ...
... and start using si.Mail.

It's small & free. ( http://simail.sourceforge.net/ )

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