Re: [sqlite] Trigger for updating timestamp-field
Just in case you also run into this: I had to do this recently on a
project of mine and found out that datetime('now') (on Windows)
doesn't give me the actual time since it uses UTC. To get the actual
local time, use datetime('now','localtime').
For the rest of the date/time documentation, you can refer to
http://sqlite.org/lang_datefunc.html
Hope it helps,
// --
Enrique Ramirez Irizarry
Lead Developer
Indie Code Labs
http://www.indiecodelabs.com
On Fri, Oct 17, 2008 at 9:21 PM, P Kishor <[EMAIL PROTECTED]> wrote:
> On 10/17/08, Kristofer Hindersson <[EMAIL PROTECTED]> wrote:
>> Hi,
>>
>> I've recently started to play around with SQLite and I'm currently trying
>> to create a trigger for updating a timestamp field inside a table
>> (SQL-syntax for the creation of the table is included below.) Could someone
>> please tell me how I should go about writing such a trigger. I'd like the
>> [updated]-field to be set to the current timestamp everytime an entry is
>> updated/modified.
>>
>> Thanks!
>>
>> /Kris
>>
>> CREATE TABLE IF NOT EXISTS Entries (
>> entryID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
>> title VARCHAR(50) NOT NULL,
>> created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
>> updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
>> signature VARCHAR(25) NOT NULL,
>> body TEXT NOT NULL);
>>
>
>
>CREATE TRIGGER add_date
>AFTER INSERT ON Entries
>BEGIN
> UPDATE Entries SET updated = datetime('now') WHERE entryID = new.
> entryID;
>END;
>
>
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger for updating timestamp-field
On 10/17/08, Kristofer Hindersson <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I've recently started to play around with SQLite and I'm currently trying to
> create a trigger for updating a timestamp field inside a table (SQL-syntax
> for the creation of the table is included below.) Could someone please tell
> me how I should go about writing such a trigger. I'd like the [updated]-field
> to be set to the current timestamp everytime an entry is updated/modified.
>
> Thanks!
>
> /Kris
>
> CREATE TABLE IF NOT EXISTS Entries (
> entryID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
> title VARCHAR(50) NOT NULL,
> created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
> updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
> signature VARCHAR(25) NOT NULL,
> body TEXT NOT NULL);
>
CREATE TRIGGER add_date
AFTER INSERT ON Entries
BEGIN
UPDATE Entries SET updated = datetime('now') WHERE entryID = new. entryID;
END;
--
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
[sqlite] Trigger for updating timestamp-field
Hi, I've recently started to play around with SQLite and I'm currently trying to create a trigger for updating a timestamp field inside a table (SQL-syntax for the creation of the table is included below.) Could someone please tell me how I should go about writing such a trigger. I'd like the [updated]-field to be set to the current timestamp everytime an entry is updated/modified. Thanks! /Kris CREATE TABLE IF NOT EXISTS Entries ( entryID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, title VARCHAR(50) NOT NULL, created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, signature VARCHAR(25) NOT NULL, body TEXT NOT NULL); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can you perform an ATTACH-type operation just using DB handles ?
On Fri, 17 Oct 2008 07:53:52 + (UTC), MikeW
<[EMAIL PROTECTED]> wrote:
>John Belli <[EMAIL PROTECTED]> writes:
>>
>> On Thu, 16 Oct 2008 17:54:52 + (UTC), MikeW
>> <[EMAIL PROTECTED]> wrote:
>>
>> >My code has two pre-opened DB connection handles, and would like to
>> >perform an SQL operation over the two databases.
>> >The DB filenames are not available at this point, hence an ATTACH is
>> >not feasible.
>> >Is there any way to achieve this by applying an ATTACH at the handle level ?
>>
>> I don't think so, but check into "PRAGMA database_list" (See
>> http://www.sqlite.org/pragma.html ).
>>
>> JAB
>
>Thanks, that might be useful in some situations but not in this one.
>
>The use case is a very specific one in which a database may contain one-off
>overrides for a second, normally independent (but already opened) database.
>I can obviously implement this by reading rows from the first one
>and updating the second, but if I could briefly refer to both DBs at
>the same time, it would be just one 'atomic' SQL statement -
>much simpler and more robust.
>
>Still, it seems surprising that there isn't a (SQLite-specific) handle-level
>operation to do this attach, maybe I should put in a feature request.
>(Or even have a look in the SQLite code to see if it's an easy mod ...)
GIVEN: db1 is the open handle of the first database, and db2 is the
open handle of the second database
WANTED: attach database open as db1 to db2 to do an atomic update
* * * WARNING: untested code ahead! * * *
char **table;
sqlite3_get_table(db1, "PRAGMA database_list", , NULL, NULL,
NULL);
char *tmp = sqlite3_mprintf("ATTACH %q AS a", table[5]);
sqlite3_free_table(table);
sqlite3_exec(db2, tmp, NULL, NULL, NULL);
sqlite3_free(tmp);
...do your stuff here...
sqlite3_exec(db2, "DETACH a", NULL, NULL, NULL);
There should be a bunch of error checking in there. This is left as an
exercise for the reader.
This will not work if db1 is a memory db. In that case, you would have
to do it the other way around. If both are memory dbs, then you're out
of luck.
JAB
--
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multithreaded query testing results, and a significant windows performance improvement (10x).
I would think maybe one performance demo that could be used from the
command line to demonstrate performance and then secondarily show how it is
achieved.
The TRANSACTIONS did do the trick. I saw 3,000 inserts per
second. Could most likely get more performance from it if I want away from
prepping the query each time like explained elsewhere, etc.
Now on the multi threaded demo, I ran into this result. I queried
the first 500 records in my database. And for a single thread all
queries completed below
1 ms. For 500 threads with SQlite serializing the access I would
expect the longest queries to take no more than maybe 500ms. But
alas within 6 queries
I was into queries that took so long I gave up waiting for them.
But if I put my own mutex around the query operation the longest
queries in a 500 thread demo where no longer than 500ms. Which makes
since. So
that is the way I will do things there.
Ok now for a offering. I did speed up performance on both inserts
and queries by a whopping 10x using this function replacement:
/*
** Make sure all writes to a particular file are committed to disk.
*/
static int winSync(sqlite3_file *id, int flags){
winFile *pFile = (winFile*)id;
HANDLE dup_handle;
int retval=SQLITE_IOERR; // assume failure
HANDLE prochandle=GetCurrentProcess(); // get the process handle
OSTRACE3("SYNC %d lock=%d\n", pFile->h, pFile->locktype);
#ifdef SQLITE_TEST
if( flags & SQLITE_SYNC_FULL ){
sqlite3_fullsync_count++;
}
sqlite3_sync_count++;
#endif
if (DuplicateHandle(prochandle, pFile->h, prochandle, _handle, 0, TRUE,
DUPLICATE_SAME_ACCESS) == TRUE) {
if (CloseHandle(dup_handle)!=0)
return SQLITE_OK; // ok the file buffers have been flushed.
}
return(retval);
}
Something that has worked for (11) years for me in application
logging functionality...let me know if anyone finds issue or knows of
issue with it. I have
only tried this on windows NT/2000/XP x64.
David Clark
At 03:31 PM 10/15/2008, Doug wrote:
>I don't mean to pick on the OP, but this is such a commonly asked question
>for people new to SQLite (including me not so long ago) that maybe putting
>something like what you just said many places on the website (besides just
>the Wiki) would help. Perhaps on the INSERT doc page? And maybe make it
>bold?
>
>Doug
>
> > -Original Message-
> > From: [EMAIL PROTECTED]
> [mailto:sqlite-users-
> > [EMAIL PROTECTED] On Behalf Of D. Richard Hipp
> > Sent: Wednesday, October 15, 2008 3:16 PM
> > To: [EMAIL PROTECTED]; General Discussion of SQLite Database
> > Subject: Re: [sqlite] basic insert questions...
> >
> >
> > On Oct 15, 2008, at 4:01 PM, Jay A. Kreibich wrote:
> > >
> > > Issue the command "BEGIN", do 100 to 1 INSERTs, issue a
> > "COMMIT".
> > > You should see a very noticeable difference in speed.
> > >>
> >
> > Just to amplify Jay's words: On a workstation, SQLite should do at
> > least 50,000 INSERTs per second. But due to limitations of spinning
> > disk drives, you can get at most 60 transactions per second. If you
> > do not use BEGIN...COMMIT, then each INSERT is a separate transaction,
> > regardless of whether or not they are in the same string. By doing
> > the BEGIN...COMMIT with the INSERT statements in between, you can do
> > thousands and thousands of fast INSERTs for each relatively slow
> > COMMIT.
> >
> >
> > 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] Compiling with mingw32/msys on Windows
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 checking whether to support shared library linked as release mode or not... no checking whether to use an in-ram database for temporary tables... no checking if executables have the .exe suffix... unknown checking for Tcl configuration... found /c/Tcl/lib/tclConfig.sh checking for existence of /c/Tcl/lib/tclConfig.sh... loading checking for library containing tgetent... no checking for readline in -lreadline... no checking
Re: [sqlite] Include a double quote inside a string-literal
SQL uses single quotes to delimit string literals. Aladdin Lampé wrote: > Hi! > Is it possible to include a \" (double quote) inside a string-literal? > I wanted to use a string-literal like "this is \"not working\"" but sqlite's > SQL parser doesn't seem to accept this. > Is it the intended behaviour or did I do something wrong? > Thanks for your help. > Aladdin > _ > Inédit ! Des Emoticônes Déjantées! Installez les dans votre Messenger ! > http://www.ilovemessenger.fr/Emoticones/EmoticonesDejantees.aspx > ___ > 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] FTS3, UPDATE of one column: why are all columns affected?
This turned out to be a bug in my code. I apologize. Yaroslav -- View this message in context: http://www.nabble.com/FTS3%2C-UPDATE-of-one-column%3A-why-are-all-columns-affected--tp20036520p20039068.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
Prepare your statements only once and then use bind. Do not use sqlite3_exec. Do not open and close the DB for each read, instead open once and let the cache work. Avoid row scans by defining indices. Use the new index selection functionality to force the use of the best index. Place large and less frequently accessed columns at the end of the Sqlite rows. When you obey these rules you will get very good read perfprmance from Sqlite. The cacheing is important if you are using a slow disk or flash memory. Look at shared cache mode if you have multiple users. JS MikeW wrote: > Christophe Leske <[EMAIL PROTECTED]> writes: > > >>Hi there, >> >>i am using a 120MB database in an embedded project (a DVD-ROM project) >>and was wondering what I can do to speed up its reading using diverse >>PRAGMA statements. >>The database is locked, meaning that no data is being inserted or >>deleted from it. I am solely after speeding up its reading performance. >> >>Indices have been set, would augmenting the cache size for Sqlite do >>something? >> >>Grateful for any info, >> > > Did you say that the PRAMGA locking_mode=EXCLUSIVE was set ? > The doc suggests this speeds things up ... > > You are not clear about which aspect is slow - are you already using > sqlite3_bind_x() and placeholders (?) in your SQL statements ? > Doing a sqlite3_prepare every time you do a query can slow things > down. > Sorry if you are already using all code-level best practise !! > > MikeW > > > > ___ > 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] sqlite3_open on non-DB file
I think there are some self-identifying bits at the start of a valid SQLite file... you could open the file directly and check for those. Or, if you're going to retrieve a list of table names from sqlite_master when first opening the database, you could trap the SQLITE_NOTADB when executing that query... that would do the trick as well. -T On Fri, Oct 17, 2008 at 12:48 PM, Antoine Caron <[EMAIL PROTECTED]> wrote: > Hi guys, > > > > 1st I'd like to say I'm pretty new to SQLite, about 1 week. I'm trying to > handle correctly the DB opening and here's some question on this. > > > > #1 I'm validating the db.sqlite file exist. if it doesn't, it creates it, > right ? so right after being created, is the sqlite_master table created > right away and a valid table for me to fetch the list of tablename (which > should be empty) ? > > > > #2 if my db.sqlite file exist, I'm doing a sqlite_open3 on it. I was hoping > the error code returned to actually help me determine is the file is a valid > DB but it don't seems to read at all from the file. In fact, that's only > when I 1st try to SELECT something from the file that I'm getting error_code > 26 > > #define SQLITE_NOTADB 26 /* File opened that is not a database file > */ > > As stated in http://www.sqlite.org/capi3.html , the error code from 24-26 > present but that might just be new stuff since 3.0 I'm using 3.6 I'm giving > an invalid DB file on purpose to simulate a corrupted DB. > > Is there a (Quick) way to check whether or not the opened file is a database > way other than using sql_exec ? > > > > ___ > 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] sqlite3_open on non-DB file
Hi guys, 1st I'd like to say I'm pretty new to SQLite, about 1 week. I'm trying to handle correctly the DB opening and here's some question on this. #1 I'm validating the db.sqlite file exist. if it doesn't, it creates it, right ? so right after being created, is the sqlite_master table created right away and a valid table for me to fetch the list of tablename (which should be empty) ? #2 if my db.sqlite file exist, I'm doing a sqlite_open3 on it. I was hoping the error code returned to actually help me determine is the file is a valid DB but it don't seems to read at all from the file. In fact, that's only when I 1st try to SELECT something from the file that I'm getting error_code 26 #define SQLITE_NOTADB 26 /* File opened that is not a database file */ As stated in http://www.sqlite.org/capi3.html , the error code from 24-26 present but that might just be new stuff since 3.0 I'm using 3.6 I'm giving an invalid DB file on purpose to simulate a corrupted DB. Is there a (Quick) way to check whether or not the opened file is a database way other than using sql_exec ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3, UPDATE of one column: why are all columns affected?
Hi All, I am using an FTS3 virtual table with multiple columns. This works very well, until I try to do an UPDATE on _one_ of the columns in the vtable. In that case, the other columns are cleared. After looking into the FTS3 code, I discovered that the columns in the original SQL statement are ignored. At least, the internal SQL query for the content (%_content) updating is generated regardless of what columns were referenced in the original SQL statement. (See contentUpdateStatement() function). The unreferenced columns come as empty strings and get happily written to the database. I assume that the same happens for other FTS3 stuff (%_segments, etc). I am not very familiar with FTS3 and Sqlite internals, though, and may be mistaken here. The workaround would be simple - reference each and every column in your UPDATE query, where the desired column is altered, and all the other are old values. This might be wasteful and inelegant, however, as it requires a redundant read. Any thougths or hints on this will be greatly appreciated :) Yaroslav -- View this message in context: http://www.nabble.com/FTS3%2C-UPDATE-of-one-column%3A-why-are-all-columns-affected--tp20036520p20036520.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Include a double quote inside a string-literal
Did you remember to put single quotes around your string? Your example shows double quotes: "this is \"not working\"" vs 'this is \"not working\"' // -- Enrique Ramirez Irizarry Lead Developer Indie Code Labs http://www.indiecodelabs.com On Fri, Oct 17, 2008 at 9:53 AM, Aladdin Lampé <[EMAIL PROTECTED]> wrote: > > Hi! > Is it possible to include a \" (double quote) inside a string-literal? > I wanted to use a string-literal like "this is \"not working\"" but sqlite's > SQL parser doesn't seem to accept this. > Is it the intended behaviour or did I do something wrong? > Thanks for your help. > Aladdin > _ > Inédit ! Des Emoticônes Déjantées! Installez les dans votre Messenger ! > http://www.ilovemessenger.fr/Emoticones/EmoticonesDejantees.aspx > ___ > 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 install SQLite on a shared linux hosting
I have a similar setup than you (Linux host, I have no control over the system, just the files in my directory) and I have been able to successfully use SQLite with PHP. You can choose to create your databases from within PHP code, or alternatively use a graphical frontend (like for example the firefox extension called SQLite Manager) to create the database file and upload it to a directory in your web host. You can start here http://www.php.net/manual/en/ref.sqlite.php reading on the different functions to interact with SQLite from within PHP code. Hope this helps! // -- Enrique Ramirez Irizarry Lead Developer Indie Code Labs http://www.indiecodelabs.com On Fri, Oct 17, 2008 at 7:52 AM, Graeme Pietersz <[EMAIL PROTECTED]> wrote: > > On Friday 17 October 2008 16:39:32 [EMAIL PROTECTED] wrote: >> > only compiled and linked into an application. There is no server. >> >> Martin, thank you for the answer. >> I do not really understand "compiled", "linked into an application" >> and "there is no server". > Use Google. There are lots of explanations out there. You should know these if > you are installing software on servers. > >> They are far too technical terms. > In simpe terms: > > Sqlite does not run by itself, it needs to be incorporated into another piece > of software. > > You said you are using PHP. It usually includes sqlite. If not, ask your > hosting provider. > > Graeme >> >> But, judging from your short answer, I believe that there is no way to >> use it to store/retrieve data for my site. >> Am I right? > > >> >> Luigi >> ___ >> 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] Questions about "analyze"
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
Re: [sqlite] Tcl/SQLite application best practices ???
On Fri, 17 Oct 2008, Michael Schlenker wrote: > It depends a bit on what you try to write and what other constraints you > have or are ok to accept. > > Michael Danke Michael. Yes, I see what you mean. My intent was of course to mate a generic procedural tcl with sqlite, and add a rich data dictionary layer. This would allow the gui to be generated from ddl at runtime. The tricky bits are the keys/links between relations, so that the forms will recognize, respect and "do the right thing" for 1:N relationships. I will review your pointers, but I had not wanted to add an additional oo layer if I can help it ... Cheers, Rob. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
> When you say 'sqlite.exe' I presume you are referring to 'sqlite3.exe' ? > (http://www.sqlite.org/sqlite.html) > Yes. And I am using v3.6.4. > Using the correct INDEX can speed queries up vastly, so if you can > identify how you are accessing the data, and then set that/those > columns as INDEXed, that will help ... > Have done that. It almost doubles my database, but it is worth it. How about the cache size? or does this only pertain to databases which get inserts? > (Techie note > http://20bits.com/2008/05/13/interview-questions-database-indexes/) > Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Include a double quote inside a string-literal
Hi! Is it possible to include a \" (double quote) inside a string-literal? I wanted to use a string-literal like "this is \"not working\"" but sqlite's SQL parser doesn't seem to accept this. Is it the intended behaviour or did I do something wrong? Thanks for your help. Aladdin _ Inédit ! Des Emoticônes Déjantées! Installez les dans votre Messenger ! http://www.ilovemessenger.fr/Emoticones/EmoticonesDejantees.aspx ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updating only the last raw
"Lloyd" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Lets assume a table having following data > > No. Name Address > > 1 A addrA > 2 B addrB > 3 C addrC > 4 B addrB1 > > I want to update the filed 'Address' based on field 'Name'. But if > there are multiple table entries (The case of B), I just want to > update the last entry in the table. How can I write a query for this? update mytab set Address='addr' where No = ( select No from mytab where name='B' order by No desc limit 1 ); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to install SQLite on a shared linux hosting
On Friday 17 October 2008 16:39:32 [EMAIL PROTECTED] wrote: > > only compiled and linked into an application. There is no server. > > Martin, thank you for the answer. > I do not really understand "compiled", "linked into an application" > and "there is no server". Use Google. There are lots of explanations out there. You should know these if you are installing software on servers. > They are far too technical terms. In simpe terms: Sqlite does not run by itself, it needs to be incorporated into another piece of software. You said you are using PHP. It usually includes sqlite. If not, ask your hosting provider. Graeme > > But, judging from your short answer, I believe that there is no way to > use it to store/retrieve data for my site. > Am I right? > > Luigi > ___ > 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] Questions about "analyze"
Greetings, Clodo, Regarding: "[is it]possible to force the use of the index?" The very latest release of sqlite, 3.6.4, implements precisely that, in the form of an "INDEXED BY" clause. See: http://www.sqlite.org/releaselog/3_6_4.html Specifically: http://www.sqlite.org/lang_indexedby.html There was a flurry of discussion regarding "INDEXED BY" just before its release if you want to search the archives of this list, but the link above may be all you need at this point. I can't speak to other parts of your question. Regards, Donald Griggs ___ 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] Questions about "analyze"
Hi to all,
I have a table with many record, that have a field normally with value '0'.
Occasionally i update some records by setting the field to '1' to mark
it, and after in a background job, i localized them for working on it.
For that, i have an index on that field.
My problem: if i run an "analyze" when all records have the field equal
to '0', the localization (select .. where field != 0) don't use the
index, and do a full-table-scan.
If at least one record is set to '1', the index are used.
It's seem that "analyze" discover that all records have same field
value, so mark the index "useless".
Solutions that i know:
- run "analyze" after updating to '1' some records. :(
- maintain at least one fake record with '1' :(
- never launch "analyze" :(
Or... (please help!)
- exists some options to control this behiavour of analyze?
- it's possible to force the use of the index?
Thanks for feedback!
-- Test case:
-- Creating test table
CREATE TABLE os_test
(
codeint NOT NULL,
markint NOT NULL,
PRIMARY KEY (code)
);
-- Creating an index
CREATE INDEX os_test_index_mark ON os_test (mark);
-- Filling with some data
insert into os_test values ('a',0);
insert into os_test values ('b',0);
insert into os_test values ('c',0);
-- Testing indexes
explain query plan
select code from os_test where mark == 1
-- Result: "TABLE os_test WITH INDEX os_test_index_mark"
-- Right.
-- Launch "analyze"
analyze
-- Re-Testing indexes
explain query plan
select code from os_test where mark == 1
-- Result: "TABLE os_test"
-- Wrong.
-- Updating one record
update os_test set mark=1 where code='a'
-- Re-Testing indexes
explain query plan
select code from os_test where mark == 1
-- Result: "TABLE os_test"
-- Wrong.
-- Re-Analyze
analyze
-- Re-Testing indexes
explain query plan
select code from os_test where mark == 1
-- Result: "TABLE os_test WITH INDEX os_test_index_mark"
-- Right.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to install SQLite on a shared linux hosting
Martin.Engelschalk <[EMAIL PROTECTED]> writes: > > Hello Luigi > > sqlite cannot be installed or run, but only compiled and inked into an > application. There is no server. > See http://www.sqlite.org/about.html > > Martin There is an executable: http://www.sqlite.org/sqlite.html for manual queries - but this is not a server, as you say ! MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
<[EMAIL PROTECTED]> writes: > > Hello Mike, > > Did you say that the PRAMGA locking_mode=EXCLUSIVE was set ? > The doc suggests this speeds things up ... > > I have that set, yes. > > You are not clear about which aspect is slow - are you already using > sqlite3_bind_x() and placeholders (?) in your SQL statements ? > Doing a sqlite3_prepare every time you do a query can slow things > down. > > I am using the sqlite.exe command line executable in a separate thread to query my database. I don't have > code level control over the access to it. > > I was thus wondering if there is anything i can do on a query level (as input to the command line executable) to > speed things up. > > Any help is much appreciated, > > christophe Leske > When you say 'sqlite.exe' I presume you are referring to 'sqlite3.exe' ? (http://www.sqlite.org/sqlite.html) If not, update your software ! Using the correct INDEX can speed queries up vastly, so if you can identify how you are accessing the data, and then set that/those columns as INDEXed, that will help ... http://www.sqlite.org/lang_createindex.html Best choice for index could be a) field that directly identifies record, eg. serial number b) field that allows quick rejection of most non-matching records, reducing amount of data to search by other methods, eg. surname Of course, creating an index uses more space in the DB, so there is always a tradeoff. (Techie note http://20bits.com/2008/05/13/interview-questions-database-indexes/) MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to install SQLite on a shared linux hosting
> only compiled and linked into an application. There is no server. Martin, thank you for the answer. I do not really understand "compiled", "linked into an application" and "there is no server". They are far too technical terms. But, judging from your short answer, I believe that there is no way to use it to store/retrieve data for my site. Am I right? Luigi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to install SQLite on a shared linux hosting
Hello Luigi sqlite cannot be installed or run, but only compiled and inked into an application. There is no server. See http://www.sqlite.org/about.html Martin [EMAIL PROTECTED] schrieb: > I have been trying to understand if and how it is it possible for me > to install SQLite, but due to my little knowledge I find no way. > Also, no information at all is given on the official site, apart from > a general "installation is trivial: just copy the sqlite or sqlite.exe > executable to the target machine and run it".[1] > > I have my own site hosted on a shared linux server[2][3] (of which I > have no direct control) which I can access with ftp. > Where exactly should I place the SQLite file, and which file should I > grab. please? > Then, how do I "run" it? > > Another tutorial[4] says: "Get a copy of the prebuilt binaries for > your machine, or get a copy of the sources and compile them yourself." > As I do not exactly know what compiling is, I targeted the > "Precompiled Binaries for Linux"[5], but there I found 4 different > things. > Which of them would be the right one in order to try and use for my > own php site, please? > > I understand that most users here are already super-experts, who do > not need any advice. > Is there a real (as opposed to [4]) tutorial for beginners, please? > > > Luigi > > > [1] http://www.sqlite.org/whentouse.html > [2] http://webx18.aruba.it/ver.php > [3] > http://translate.google.com/translate?u=http%3A%2F%2Fassistenza.aruba.it%2Fkb%2Fidx%2F45%2F142%2Farticle%2FLinguaggi-supportati-su-Hosting-Linux.html=it=UTF-8=it=en > > [4] http://www.sqlite.org/quickstart.html > [5] http://www.sqlite.org/download.html > ___ > 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] How to install SQLite on a shared linux hosting
I have been trying to understand if and how it is it possible for me to install SQLite, but due to my little knowledge I find no way. Also, no information at all is given on the official site, apart from a general "installation is trivial: just copy the sqlite or sqlite.exe executable to the target machine and run it".[1] I have my own site hosted on a shared linux server[2][3] (of which I have no direct control) which I can access with ftp. Where exactly should I place the SQLite file, and which file should I grab. please? Then, how do I "run" it? Another tutorial[4] says: "Get a copy of the prebuilt binaries for your machine, or get a copy of the sources and compile them yourself." As I do not exactly know what compiling is, I targeted the "Precompiled Binaries for Linux"[5], but there I found 4 different things. Which of them would be the right one in order to try and use for my own php site, please? I understand that most users here are already super-experts, who do not need any advice. Is there a real (as opposed to [4]) tutorial for beginners, please? Luigi [1] http://www.sqlite.org/whentouse.html [2] http://webx18.aruba.it/ver.php [3] http://translate.google.com/translate?u=http%3A%2F%2Fassistenza.aruba.it%2Fkb%2Fidx%2F45%2F142%2Farticle%2FLinguaggi-supportati-su-Hosting-Linux.html=it=UTF-8=it=en [4] http://www.sqlite.org/quickstart.html [5] http://www.sqlite.org/download.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
Hello Mike, Did you say that the PRAMGA locking_mode=EXCLUSIVE was set ? The doc suggests this speeds things up ... I have that set, yes. You are not clear about which aspect is slow - are you already using sqlite3_bind_x() and placeholders (?) in your SQL statements ? Doing a sqlite3_prepare every time you do a query can slow things down. I am using the sqlite.exe command line executable in a separate thread to query my database. I don't have code level control over the access to it. I was thus wondering if there is anything i can do on a query level (as input to the command line executable) to speed things up. Any help is much appreciated, christophe Leske ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tcl/SQLite application best practices ???
Rob Sciuk schrieb: > Is there an example application which demonstrates good coding technique > for both tcl and sqlite to perform C.R.U.D. type operations?? I'm about > to develop a small proof of concept using tcl/tk and SQLite, and while I > have some ideas about mating the two, I'm sure that there may be some > sample applications which have cobbled together a handy database library > in an elegant coding style ... > > Any pointers would be deeply appreciated ... There are a multitude of apps/libs that do such things, but not really a champion, because with Tcl you have some fractioned landscape regarding things like OO and db access currently. (hopefully this will change a bit with Tcl 8.6 which will ship with TDBC for database connections and the new core OO system). I found tcldb quite useful for this, but it depends on [incr Tcl], which isn't my OO system of choice. But it provides nice things already, see the screenshots: http://dqsoftware.sourceforge.net/ Other examples of CRUD operations could surely be extracted from the aolserver codebase, using the ns_ database layer. Other tools to have a look at might be the tcllib tie module and maybe the gridplus gui tools. Maybe also TAO (http://wiki.tcl.tk/tao). It depends a bit on what you try to write and what other constraints you have or are ok to accept. Michael -- Michael Schlenker Software Engineer CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
Christophe Leske <[EMAIL PROTECTED]> writes: > > Hi there, > > i am using a 120MB database in an embedded project (a DVD-ROM project) > and was wondering what I can do to speed up its reading using diverse > PRAGMA statements. > The database is locked, meaning that no data is being inserted or > deleted from it. I am solely after speeding up its reading performance. > > Indices have been set, would augmenting the cache size for Sqlite do > something? > > Grateful for any info, > Did you say that the PRAMGA locking_mode=EXCLUSIVE was set ? The doc suggests this speeds things up ... You are not clear about which aspect is slow - are you already using sqlite3_bind_x() and placeholders (?) in your SQL statements ? Doing a sqlite3_prepare every time you do a query can slow things down. Sorry if you are already using all code-level best practise !! MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can you perform an ATTACH-type operation just using DB handles ?
John Belli <[EMAIL PROTECTED]> writes: > > On Thu, 16 Oct 2008 17:54:52 + (UTC), MikeW > <[EMAIL PROTECTED]> wrote: > > >My code has two pre-opened DB connection handles, and would like to > >perform an SQL operation over the two databases. > >The DB filenames are not available at this point, hence an ATTACH is > >not feasible. > >Is there any way to achieve this by applying an ATTACH at the handle level ? > > I don't think so, but check into "PRAGMA database_list" (See > http://www.sqlite.org/pragma.html ). > > JAB Thanks, that might be useful in some situations but not in this one. The use case is a very specific one in which a database may contain one-off overrides for a second, normally independent (but already opened) database. I can obviously implement this by reading rows from the first one and updating the second, but if I could briefly refer to both DBs at the same time, it would be just one 'atomic' SQL statement - much simpler and more robust. Still, it seems surprising that there isn't a (SQLite-specific) handle-level operation to do this attach, maybe I should put in a feature request. (Or even have a look in the SQLite code to see if it's an easy mod ...) MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updating only the last raw
Thank you very much Dan. On Fri, 2008-10-17 at 14:10 +0700, Dan wrote: > On Oct 17, 2008, at 1:43 PM, Lloyd wrote: > > > Hi, > > > > Lets assume a table having following data > > > > No. NameAddress > > > > 1 A addrA > > 2 B addrB > > 3 C addrC > > 4 B addrB1 > > > > I want to update the filed 'Address' based on field 'Name'. But if > > there > > are multiple table entries (The case of B), I just want to update the > > last entry in the table. How can I write a query for this? > > > > My present query is > > > > update mytab set Address="addr" where name="B" > > If you are using version 3.6.4 and compile with > SQLITE_ENABLE_UPDATE_DELETE_LIMIT, then you can use the new > syntax: > >UPDATE mytab SET Address='addr' WHERE name='B' ORDER BY rowid DESC > LIMIT 1; > > Otherwise, you have to do: > >UPDATE mytab SET Address='addr' WHERE rowid = (SELECT max(rowid) > FROM mytab WHERE name = 'B'); > > > Dan. > > > > > > > > > > > > The output of this query would be > > > > No. NameAddress > > > > 1 A addrA > > 2 B addr > > 3 C addrC > > 4 B addr > > > > But the Output we want is > > > > No. NameAddress > > > > 1 A addrA > > 2 B addrB > > 3 C addrC > > 4 B addr > > > > > > If my question is not clear, I am ready to explain it more... > > > > > > > > Thanks, > > Lloyd > > > > > > > > __ > > Scanned and protected by Email scanner > > ___ > > 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 __ Scanned and protected by Email scanner ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updating only the last raw
On Oct 17, 2008, at 1:43 PM, Lloyd wrote: > Hi, > > Lets assume a table having following data > > No. NameAddress > > 1 A addrA > 2 B addrB > 3 C addrC > 4 B addrB1 > > I want to update the filed 'Address' based on field 'Name'. But if > there > are multiple table entries (The case of B), I just want to update the > last entry in the table. How can I write a query for this? > > My present query is > > update mytab set Address="addr" where name="B" If you are using version 3.6.4 and compile with SQLITE_ENABLE_UPDATE_DELETE_LIMIT, then you can use the new syntax: UPDATE mytab SET Address='addr' WHERE name='B' ORDER BY rowid DESC LIMIT 1; Otherwise, you have to do: UPDATE mytab SET Address='addr' WHERE rowid = (SELECT max(rowid) FROM mytab WHERE name = 'B'); Dan. > > > > The output of this query would be > > No. NameAddress > > 1 A addrA > 2 B addr > 3 C addrC > 4 B addr > > But the Output we want is > > No. NameAddress > > 1 A addrA > 2 B addrB > 3 C addrC > 4 B addr > > > If my question is not clear, I am ready to explain it more... > > > > Thanks, > Lloyd > > > > __ > Scanned and protected by Email scanner > ___ > 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
