Re: [sqlite] database filename
I understand the pragma but was hoping for something like this const char *sqlite3_column_database_name(sqlite3_stmt*,int); but with the sqlite3* and not a statement? Thanks again --- On Sat, 10/18/08, Igor Tandetnik <[EMAIL PROTECTED]> wrote: From: Igor Tandetnik <[EMAIL PROTECTED]> Subject: Re: [sqlite] database filename To: sqlite-users@sqlite.org Date: Saturday, October 18, 2008, 3:56 PM "Mike Johnston" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Given a sqlite3 * database handle, is there a way to retrieve the > filename used to open the database? Run this statement on your handle: PRAGMA database_list; Note that there may be more than one file associated with the handle (see ATTACH statement). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database filename
"Mike Johnston" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Given a sqlite3 * database handle, is there a way to retrieve the > filename used to open the database? Run this statement on your handle: PRAGMA database_list; Note that there may be more than one file associated with the handle (see ATTACH statement). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple Selects
"Andrew Gatt" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I'm guessing my SQL is the worst way of doing things so i've been > trying to find a better method. I stumbled across "SELECT name FROM > table WHERE id IN (x,y,z) however this doesn't allow me to specify > the order the rows are returned, which i must have. Create a temporary table, populate it with your IDs, then join to it: create temp table ids(ordno integer primary key, id integer); insert into ids(id) values (45); insert into ids(id) values (32); ... select t.name from mytable t join ids on (t.id = ids.id) order by ids.ordno; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database filename
Given a sqlite3 * database handle, is there a way to retrieve the filename used to open the database? TIA __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple Selects
On 10/18/08, Andrew Gatt <[EMAIL PROTECTED]> wrote: > Jonas Sandman wrote: > > Just to point out the obvious, have you tried ORDER BY? > > > > "SELECT name FROM table ORDER BY name;" will return your list in > > alphabetical order. > > > > /Jonas > > > > > > Thanks for the suggestion, but it needs to be an order i can specify, > not just ordered. I.e. i may want row 45 first, then 32 then 67 etc... > Create a column to control your specified order and populate it accordingly. Then SELECT ... FROM ... WHERE .. IN () ... ORDER BY col_custom_order > > > On Sat, Oct 18, 2008 at 6:53 PM, Andrew Gatt <[EMAIL PROTECTED]> wrote: > > > >> Andrew Gatt wrote: > >> > >>> I'm not sure if i'm missing something, but is there an efficient way of > >>> retrieving multiple rows based on different conditions in order. For > >>> example i have a table with rows of ids, i want to select multiple rows > >>> at a time. At present i am doing a "SELECT name FROM table WHERE id = x" > >>> for each row i want and then stitching it all together. But i'm finding > >>> this is quite slow even on a moderately small database (2000 entries). > >>> > >>> I'm guessing my SQL is the worst way of doing things so i've been trying > >>> to find a better method. I stumbled across "SELECT name FROM table WHERE > >>> id IN (x,y,z) however this doesn't allow me to specify the order the > >>> rows are returned, which i must have. > >>> > >>> The only other option i can find is using UNION ALL in between multiple > >>> SELECT statements, but would this give me a large performance increase > >>> over doing this progammatically as i've got it? > >>> > >>> Unless i've missed something obvious which could well be the case! > >>> > >>> > >>> > >>> > >> After trying several methods to improve the SQL the only thing that > >> really made a difference was creating an index on the ids. Using a UNION > >> ALL did improve matters, but you end up have to concatenate a very long > >> string for the query, so if anyone does have any SQL ideas i'd like to > >> hear them. > >> > >> Andrew > >> > >> > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple Selects
If it's completely arbitrary I think you are stuck with using union unless it's an order that you might know beforehand. Then you can add an extra column with the index. /Jonas On Sat, Oct 18, 2008 at 7:05 PM, Andrew Gatt <[EMAIL PROTECTED]> wrote: > Jonas Sandman wrote: >> Just to point out the obvious, have you tried ORDER BY? >> >> "SELECT name FROM table ORDER BY name;" will return your list in >> alphabetical order. >> >> /Jonas >> >> > Thanks for the suggestion, but it needs to be an order i can specify, > not just ordered. I.e. i may want row 45 first, then 32 then 67 etc... > >> On Sat, Oct 18, 2008 at 6:53 PM, Andrew Gatt <[EMAIL PROTECTED]> wrote: >> >>> Andrew Gatt wrote: >>> I'm not sure if i'm missing something, but is there an efficient way of retrieving multiple rows based on different conditions in order. For example i have a table with rows of ids, i want to select multiple rows at a time. At present i am doing a "SELECT name FROM table WHERE id = x" for each row i want and then stitching it all together. But i'm finding this is quite slow even on a moderately small database (2000 entries). I'm guessing my SQL is the worst way of doing things so i've been trying to find a better method. I stumbled across "SELECT name FROM table WHERE id IN (x,y,z) however this doesn't allow me to specify the order the rows are returned, which i must have. The only other option i can find is using UNION ALL in between multiple SELECT statements, but would this give me a large performance increase over doing this progammatically as i've got it? Unless i've missed something obvious which could well be the case! >>> After trying several methods to improve the SQL the only thing that >>> really made a difference was creating an index on the ids. Using a UNION >>> ALL did improve matters, but you end up have to concatenate a very long >>> string for the query, so if anyone does have any SQL ideas i'd like to >>> hear them. >>> >>> Andrew >>> >>> > > ___ > 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 Selects
Jonas Sandman wrote: > Just to point out the obvious, have you tried ORDER BY? > > "SELECT name FROM table ORDER BY name;" will return your list in > alphabetical order. > > /Jonas > > Thanks for the suggestion, but it needs to be an order i can specify, not just ordered. I.e. i may want row 45 first, then 32 then 67 etc... > On Sat, Oct 18, 2008 at 6:53 PM, Andrew Gatt <[EMAIL PROTECTED]> wrote: > >> Andrew Gatt wrote: >> >>> I'm not sure if i'm missing something, but is there an efficient way of >>> retrieving multiple rows based on different conditions in order. For >>> example i have a table with rows of ids, i want to select multiple rows >>> at a time. At present i am doing a "SELECT name FROM table WHERE id = x" >>> for each row i want and then stitching it all together. But i'm finding >>> this is quite slow even on a moderately small database (2000 entries). >>> >>> I'm guessing my SQL is the worst way of doing things so i've been trying >>> to find a better method. I stumbled across "SELECT name FROM table WHERE >>> id IN (x,y,z) however this doesn't allow me to specify the order the >>> rows are returned, which i must have. >>> >>> The only other option i can find is using UNION ALL in between multiple >>> SELECT statements, but would this give me a large performance increase >>> over doing this progammatically as i've got it? >>> >>> Unless i've missed something obvious which could well be the case! >>> >>> >>> >>> >> After trying several methods to improve the SQL the only thing that >> really made a difference was creating an index on the ids. Using a UNION >> ALL did improve matters, but you end up have to concatenate a very long >> string for the query, so if anyone does have any SQL ideas i'd like to >> hear them. >> >> Andrew >> >> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple Selects
Just to point out the obvious, have you tried ORDER BY? "SELECT name FROM table ORDER BY name;" will return your list in alphabetical order. /Jonas On Sat, Oct 18, 2008 at 6:53 PM, Andrew Gatt <[EMAIL PROTECTED]> wrote: > Andrew Gatt wrote: >> I'm not sure if i'm missing something, but is there an efficient way of >> retrieving multiple rows based on different conditions in order. For >> example i have a table with rows of ids, i want to select multiple rows >> at a time. At present i am doing a "SELECT name FROM table WHERE id = x" >> for each row i want and then stitching it all together. But i'm finding >> this is quite slow even on a moderately small database (2000 entries). >> >> I'm guessing my SQL is the worst way of doing things so i've been trying >> to find a better method. I stumbled across "SELECT name FROM table WHERE >> id IN (x,y,z) however this doesn't allow me to specify the order the >> rows are returned, which i must have. >> >> The only other option i can find is using UNION ALL in between multiple >> SELECT statements, but would this give me a large performance increase >> over doing this progammatically as i've got it? >> >> Unless i've missed something obvious which could well be the case! >> >> >> > After trying several methods to improve the SQL the only thing that > really made a difference was creating an index on the ids. Using a UNION > ALL did improve matters, but you end up have to concatenate a very long > string for the query, so if anyone does have any SQL ideas i'd like to > hear them. > > Andrew > > ___ > 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 Selects
Andrew Gatt wrote: > I'm not sure if i'm missing something, but is there an efficient way of > retrieving multiple rows based on different conditions in order. For > example i have a table with rows of ids, i want to select multiple rows > at a time. At present i am doing a "SELECT name FROM table WHERE id = x" > for each row i want and then stitching it all together. But i'm finding > this is quite slow even on a moderately small database (2000 entries). > > I'm guessing my SQL is the worst way of doing things so i've been trying > to find a better method. I stumbled across "SELECT name FROM table WHERE > id IN (x,y,z) however this doesn't allow me to specify the order the > rows are returned, which i must have. > > The only other option i can find is using UNION ALL in between multiple > SELECT statements, but would this give me a large performance increase > over doing this progammatically as i've got it? > > Unless i've missed something obvious which could well be the case! > > > After trying several methods to improve the SQL the only thing that really made a difference was creating an index on the ids. Using a UNION ALL did improve matters, but you end up have to concatenate a very long string for the query, so if anyone does have any SQL ideas i'd like to hear them. Andrew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] AFTER INSERT vs AFTER UPDATE
On 10/18/08, Kristofer Hindersson <[EMAIL PROTECTED]> wrote:
>
>
> Hi,
>
> Thank you for answering my question regarding a trigger for updating my
> updated-field. I was just wondering if I should really be using the AFTER
> INSERT ON statement instead of AFTER UPDATE ON? Wouldn't the first one only
> work if I'm creating a new entry? (Instead of just modifying an already
> existing entry.)
>
You are correct. I did not read your question carefully. The following
should do the trick --
CREATE TRIGGER add_date AFTER UPDATE ON Entries
BEGIN
UPDATE Entries SET updated = datetime('now') WHERE entryID = old.entryID;
END;
And yes, as Enrique pointed out, keep in mind that all times are UTC
unless explicitly set to your localtime.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple Selects
I'm not sure if i'm missing something, but is there an efficient way of retrieving multiple rows based on different conditions in order. For example i have a table with rows of ids, i want to select multiple rows at a time. At present i am doing a "SELECT name FROM table WHERE id = x" for each row i want and then stitching it all together. But i'm finding this is quite slow even on a moderately small database (2000 entries). I'm guessing my SQL is the worst way of doing things so i've been trying to find a better method. I stumbled across "SELECT name FROM table WHERE id IN (x,y,z) however this doesn't allow me to specify the order the rows are returned, which i must have. The only other option i can find is using UNION ALL in between multiple SELECT statements, but would this give me a large performance increase over doing this progammatically as i've got it? Unless i've missed something obvious which could well be the case! Andrew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling with mingw32/msys on Windows
-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT is not being passed in make to your object compiles... I'll try to duplicate this here. -Shane On Fri, Oct 17, 2008 at 5:40 PM, John Belli <[EMAIL PROTECTED]> wrote: > OK, I should have all the required utils, and configure apparently > worked properly, but make barfs (log below): > MinGW-5.1.4 > MSYS-1.0.10 > ActiveTcl8.5.4.0.286921-win32-ix86-threaded (after install, linked > bin/tclsh85.exe to tclsh.exe) > > Any idea? > > > JAB > -- > John A. Belli > Software Engineer > Refrigerated Transport Electronics, Inc. > http://www.rtelectronics.com > > > [EMAIL PROTECTED] ~/bld $ ../sqlite/configure --with-tcl=/c/Tcl/lib > --enable-load-extension OPTS="-DSQLITE_ENABLE_IOTRACE > -DSQLITE_THREADSAFE=1 > -DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_ENABLE_FTS3 > -DSQLITE_ENABLE_MEMORY_MANAGEMENT -DSQLITE_ENABLE_RTREE -DSQLITE_ > DEFAULT_FILE_FORMAT=4 -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT" > > checking build system type... i686-pc-mingw32 > checking host system type... i686-pc-mingw32 > checking for gcc... gcc > checking for C compiler default output file name... a.exe > checking whether the C compiler works... yes > checking whether we are cross compiling... no > checking for suffix of executables... .exe > checking for suffix of object files... o > checking whether we are using the GNU C compiler... yes > checking whether gcc accepts -g... yes > checking for gcc option to accept ANSI C... none needed > checking for a sed that does not truncate output... /bin/sed > checking for egrep... grep -E > checking for fgrep... grep -F > checking for ld used by gcc... c:/MinGW/mingw32/bin/ld.exe > checking if the linker (c:/MinGW/mingw32/bin/ld.exe) is GNU ld... yes > checking for BSD- or MS-compatible name lister (nm)... /mingw/bin/nm > checking the name lister (/mingw/bin/nm) interface... BSD nm > checking whether ln -s works... yes > checking the maximum length of command line arguments... 8192 > checking whether the shell understands some XSI constructs... yes > checking whether the shell understands "+="... no > checking for c:/MinGW/mingw32/bin/ld.exe option to reload object > files... -r > checking how to recognize dependent libraries... file_magic file > format pei*-i386(.*architecture: i386)? > checking for ar... ar > checking for strip... strip > checking for ranlib... ranlib > checking command to parse /mingw/bin/nm output from gcc object... ok > checking how to run the C preprocessor... gcc -E > checking for ANSI C header files... yes > checking for sys/types.h... yes > checking for sys/stat.h... yes > checking for stdlib.h... yes > checking for string.h... yes > checking for memory.h... yes > checking for strings.h... yes > checking for inttypes.h... yes > checking for stdint.h... yes > checking for unistd.h... yes > checking for dlfcn.h... no > checking for objdir... .libs > checking if gcc supports -fno-rtti -fno-exceptions... no > checking for gcc option to produce PIC... -DDLL_EXPORT -DPIC > checking if gcc PIC flag -DDLL_EXPORT -DPIC works... yes > checking if gcc static flag -static works... yes > checking if gcc supports -c -o file.o... yes > checking if gcc supports -c -o file.o... (cached) yes > checking whether the gcc linker (c:/MinGW/mingw32/bin/ld.exe) supports > shared libraries... yes > checking whether -lc should be explicitly linked in... yes > checking dynamic linker characteristics... Win32 ld.exe > checking how to hardcode library paths into programs... immediate > checking whether stripping libraries is possible... yes > checking if libtool supports shared libraries... yes > checking whether to build shared libraries... yes > checking whether to build static libraries... yes > checking for a BSD-compatible install... /bin/install -c > checking for gawk... gawk > checking for special C compiler options needed for large files... no > checking for _FILE_OFFSET_BITS value needed for large files... no > checking for _LARGE_FILES value needed for large files... no > checking for int8_t... yes > checking for int16_t... yes > checking for int32_t... yes > checking for int64_t... yes > checking for intptr_t... yes > checking for uint8_t... yes > checking for uint16_t... yes > checking for uint32_t... yes > checking for uint64_t... yes > checking for uintptr_t... yes > checking for sys/types.h... (cached) yes > checking for stdlib.h... (cached) yes > checking for stdint.h... (cached) yes > checking for inttypes.h... (cached) yes > checking for usleep... no > checking for fdatasync... no > checking for localtime_r... no > checking for gmtime_r... no > checking for localtime_s... no > checking for tclsh8.4... no > checking for tclsh... tclsh > Version set to 3.6 > Release set to 3.6.4 > Version number set to 3006004 > checking whether to support threadsafe operation... yes > checking for library containing pthread_create... no > checking whether to allow connections to be shared across threads... > no > checking whether threads can override each others locks... no > c
Re: [sqlite] Questions about "analyze"
Thanks for feedback. "The right data"::: we don't have any wrong data Imagine to have a table with millions of records, for example records about website, with a field="needtoupdate=false" normally. A background job that mark few records at time by setting "needtoupdate=true". And another background job that search records to be updated, and turn back to "needtoupdate=false". He need to localize them with a "where needtoupdate = true". Actually if a do an "analyze" in a moment when all "needtoupdate" are false, a simple "select count(*) from mytable where needtoupdate = true" will do a full-table-scan over millions of records. I'm not sure, but i think the index contain the info "there isn't record with value 'true'", right? Actually, i use with success the new syntax "INDEXED BY", but Sqlite docs say that is not a right solution. My next solution maybe only (if nobody can help me) use a datetime and not a boolean, but i'm searching another solution because require more useless disk-space only for this problem. Bye > Clodo wrote: > >> Many thanks, it's a good news that resolve my problem. >> >> But still remain "a trick", i think the behaviour descripted in my >> original feedback is "strange".. i understand, if all fields have the >> same value, an index on that have a zero "height" in computing the best >> indexes to use, but not use index at all and do a full-table-scan, for >> what i understand about sqlite, imho is strange... >> >> > If an index is useless for the query, then a full table scan will > generally be quicker than an indexed scan. > > An indexed scan requires accessing two things (the index plus the data) > and also traversing the index which is more 'random access' than > sequentially scanning through the table. > > If the analyse has analysed the right data, then letting it do the table > scan is probably the best thing. If it hasn't, then put the right data > in the table before doing the analyse... > > (PostgreSQL does the same thing, if the index is useless, or the > database clustering is highly correlated with the index, then it won't > use the index at all). > ___ > 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
