Re: [sqlite] date field with default current date

avel On Thu, Apr 21, 2011 at 1:43 PM, Fabio Spadaro wrote: > Hi. > > 2011/4/21 Pavel Ivanov > >> > Does not work on python with sqlite3 module >> >> What does "SELECT sqlite_version()" gives you in python with sqlite3 >> module? >&

Re: [sqlite] date field with default current date

date and/or time." It won't be your local time. Pavel On Fri, Apr 22, 2011 at 5:46 AM, Fabio Spadaro wrote: > Hi. > > 2011/4/22 Fabio Spadaro > >> Hi >> >> >> 2011/4/22 Fabio Spadaro >> >>> Hi. >>> >>> >>&g

Re: [sqlite] query to find mixed values

> REQ3 is a problem because you have mixed signs in INV and only one record in > REP > > How would I craft a query to return REQ3? Could be something like this: select INV.REQ, count(case when INV.AMT > 0 then 1 else null end) positive, count(case when INV.AMT < 0 then 1 else null end) negative,

Re: [sqlite] vacuum and rowids

> After more poking, it appears that rowids might not be changed by a vacuum if > I have an index on the table. Is this true? If so, is it something I can rely > on going forward? No, it's not true. The only way to keep your rowids intact is to declare an INTEGER PRIMARY KEY alias for it. And yo

Re: [sqlite] vacuum and rowids

>> which suggests that referring to rowids is fine. > > It does not suggest referring to ROWIDs is fine, it only says that it > can be done.  I think Pavel's point is that referencing ROWIDs is bad > practice, so that is why he says you shouldn't do it. Yes, that's right. You can refer to rowid, b

Re: [sqlite] Compile Error

I believe any compilation options that require changes in SQL parser require compiling from original sources as well. They cannot be used with amalgamation file which has already generated SQL parser's code. Pavel On Tue, May 3, 2011 at 3:05 PM, jeff archer wrote: > I am attempting to compile

Re: [sqlite] INNER JOIN Optimization

> The problem is that I would like to avoid splitting the query into two parts. > I would expect SQLite to do the same thing for me automatically (at least in > the second scenario), but it does not seem to happen... Why is that? In short, because SQLite cannot read your mind. To understand the

Re: [sqlite] Addition: Re: SQLite locking question

>> Any other chance to speed this up (apart from the obvious "optimize the >> query, do not use distinct on large tables)= > > Without seeing the query or database schema? Not really... Depending > on the exact query an index on "xyz" might help. Another suggestion could be to turn off shared

Re: [sqlite] Query question

> sqlite> select * from a where a=10 group by b; > 10|1 > 10|2 > sqlite> select * from a where a=11 group by b; > 11|2 > 11|3 > > How can I do count equivalent of such a query to find out how many > distinct values of b there are for a given a? (That is get an answer of > 2 in the above) select co

Re: [sqlite] Addition: Re: SQLite locking question

> My understanding of the shared cache model was that it just ist intended > for solving our problem by relaxing the locking a little and that there > should not be any mutexes at all when using the uncomitted read mode. > > Have I missed anything? Yes, you are involved in a "magical" thinking. Al

Re: [sqlite] Question: Memory-Based Databases

> Is this true, or is the memory usage pretty much similar? Until you reach limit set by 'pragma cache_size' memory usage would be the same for in-memory database and on-disk database. When the size of your database grows beyond 'pragma cache_size' in-memory database starts to consume more memory

Re: [sqlite] Three questions

>> 00:01 Transaction A: BEGIN >> 00:02 Transaction B: BEGIN >> 00:03 Transaction A: INSERT INTO test VALUES (1) // works okay >> 00:04 Transaction B: INSERT INTO test VALUES (1) // aborts with 'duplicate >> key' error! why??? > > I get SQLITE_BUSY "database is locked" at this point, as I would exp

Re: [sqlite] Three questions

> Therefore, the second insert fails on every database system i ever > encountered. Apparently you didn't encounter Oracle. In such situation Oracle freezes transaction B until transaction A is committed or rollbacked. After that it knows what to return to transaction B - error or success corresp

Re: [sqlite] Can I dynamically select a table ?

> I can't trully construct sql statement piece by piece with SQL > db as I did with Oracle. Just wanted to confirm. Why do you need to construct SQL specifically with db's tools? Why can't you do that in your host language? Oracle needs dynamic SQL feature because it will work much faster than the

Re: [sqlite] Multi-threading Common Problem

> "After a BEGIN EXCLUSIVE, no other database connection except for > read_uncommitted connections will be able to read the database and no other > connection without exception will be able to write the database until the > transaction is complete." > > This tells me that reads outside of a tran

Re: [sqlite] Common Multi-treaded Problem

> Interesting is the impression I had with prepared statements was the reset > was only necessary if you wanted to reuse that statement.  Since each each DB > connection is in its own instance of a class (with it own set of prepared > statements) I would not think there would be any dependency o

Re: [sqlite] Common Multi-treaded Problem

select prepared statement will lock the DB from other threads > (or is it DB connections?) but not the current thread (or is it DB > connection). > > Thanks for the help! > > John > > --- On Thu, 5/12/11, Pavel Ivanov wrote: > >> From: Pavel Ivanov >> Sub

Re: [sqlite] Common Multi-treaded Problem

in the discussion... > > On Thu, 12 May 2011 17:58:40 -0400 > Pavel Ivanov wrote: > >> There's no dependency between different prepared statements, but there >> is dependency between transactions as they use the same database. And >> transaction cannot be finishe

Re: [sqlite] IN clause in search query to search a single field containing comma delimited values

> SELECT categories FROM myTable WHERE (","||categories||",") LIKE "%,7,%"; > > but I'm guessing LIKE isn't as efficient, and the query is more > awkward.  Any advise would be appreciated.  Thanks! This LIKE is the only way to get information from your table. But you can do it more efficiently by

Re: [sqlite] Common Multi-treaded Problem

stablished than the later. > > Again thanks for the information and I apologize for taking up so much list > bandwidth.  I hope others can benefit. > > John > > --- On Thu, 5/12/11, Pavel Ivanov wrote: > >> From: Pavel Ivanov >> Subject: Re: [sqlite] Common Multi

Re: [sqlite] threading and grand central dispatch (OS X/iOS)

> I'm still not 100% sure if there's a problem sharing connections across > threads with SQLITE_OPEN_NOMUTEX as long as I guarantee that they aren't > concurrent. I suspect there aren't, but I'm not 100% sure. Any case where > sqlite3 would be less than happy that pthread_self wasn't always the

Re: [sqlite] Query efficiency

> That is, is leaving it to the > query optimiser to figure out that I only need the sub select once the > best thing to do? AFAIK, SQLite's optimizer is not that smart to collapse two identical sub-queries and reuse once generated result. > Is the select I'm doing where both a_id1 & 2 are "in" t

Re: [sqlite] SQL Statement Help(selecting days).

> I need help to build a statement in order to select all days free from > events in a specific time range. This kind of task should be implemented in your application. SQL wasn't intended for and can't solve such tasks. Pavel On Wed, May 18, 2011 at 4:06 PM, Danilo Cicerone wrote: > Hi to al

Re: [sqlite] Query efficiency

gt; from ab where ab.a_id2 = 1 and ab.b_id = 1) ; > 1|1 > 1|2 > 2|1 > sqlite> select aa.* from aa ar, ab ab1, ab ab2; > Error: no such table: aa > sqlite> select aa.* from aa as ar, ab ab1, ab ab2; > Error: no such table: aa > sqlite> > > At this point I got

Re: [sqlite] (no subject)

> Is there a rationale for allowing such statements or is that an effect > of the 'Lite' nature?  (Note: I'm not complaining, just asking.) I believe that's an effect of the "typeless" design. As SQLite doesn't have strict type names for columns it accepts pretty much anything for that. So in your

Re: [sqlite] Multi-threading Common Problem

> I have all writes in transactions.  If I deactivate my pthread_rwlock() that > enforce the above, several writes fail with a "database locked" error (I > assume it is returning SQLITE_BUSY). > > So how do I implement the equivalent of a pthread_rwlock() using SQLite > mechinisms? When SQLITE_

Re: [sqlite] Multi-threading Common Problem

tioned I should be using SQLite-specific mechanisms to achieve the same > results.  I am just trying to understand how to do that.  Pthread_rwlock() > works fine. > > Thanks, > > John > > --- On Tue, 5/24/11, Pavel Ivanov wrote: > >> From: Pavel Ivanov >>

Re: [sqlite] Using sqlite.net with Linq to create database

> If I try to query database existence using Linq's "DatabaseExists" I get > a NotImplemented exception in the sqlite ChangeDatabase function - which > doesn't make sense to me. SQLite doesn't have a notion of server containing several databases. That's why it makes perfect sense that SQLite doesn

Re: [sqlite] Using sqlite.net with Linq to create database

without issuing this command then it can't be used with SQLite. Pavel On Tue, May 24, 2011 at 11:05 AM, Ruth Ivimey-Cook wrote: > Pavel Ivanov wrote: >>> If I try to query database existence using Linq's "DatabaseExists" I get >>> a NotImplemented except

Re: [sqlite] Incorrect left join behavior?

> I can use two left joins.  While writing the first left join, I discovered > that it is behaving like an inner join. > > select * > from test a >   left join test b on a.component = b.component > where a.machine = 'machine1' >   and b.machine = 'machine2'; By the WHERE condition you limit result

Re: [sqlite] SQLite in Xcode

> 2.)     I moved SQLite3.c and SQLite3.h into my source folder and added them > to the project.  I made no changes to the code nor did I do anything special > when I added them to my project (i.e. I did not set any special compile > flags-I simply added the two files to the project). > 3.)    

Re: [sqlite] Trigger blocks a single transaction?

Do you execute all updates as one call to sqlite_exec? Or as separate calls to sqlite_exec or sqlite_prepare/sqlite_step? If separate then do you check return value from the calls? And do you execute BEGIN/COMMIT somewhere? Pavel On Sun, Dec 23, 2012 at 2:37 PM, Alem Biscan wrote: > Hello, > > I

Re: [sqlite] Trigger blocks a single transaction?

#x27;t return any row affected value. Well it makes sense somehow.. It > cannot know how many view's visible rows were affected. It lowers the > coolnes of views and instead of trigs. > > Thanx > > On Mon, Dec 24, 2012 at 12:15 AM, Pavel Ivanov wrote: > >> Do you

Re: [sqlite] Bug in Cygwin SQLite: temporary table creation fails

I believe you can get this functionality now by compiling SQLite code using cygwin compiler, not a Win32 one (and not downloading dll library from sqlite.org website). Pavel On Wed, Dec 26, 2012 at 9:19 PM, Daniel Colascione wrote: > Creating temporary tables fails in Cygwin SQLite: > > sqlite>

Re: [sqlite] Bug in Cygwin SQLite: temporary table creation fails

On Wed, Dec 26, 2012 at 9:55 PM, Daniel Colascione wrote: > On 12/26/12 9:54 PM, Pavel Ivanov wrote: >> I believe you can get this functionality now by compiling SQLite code >> using cygwin compiler, not a Win32 one (and not downloading dll >> library from sqlite.org web

Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory

You may be hitting the memory fragmentation issue. Try to run your application with different memory managers (Hoard is my favorite - http://www.hoard.org/) and see if the memory consumption is the same. Also if you close all connections to your database (and other SQLite databases too) does amoun

Re: [sqlite] Problem whit sqlite_prepare_v2() in 3.7.5 version and later

On Wed, Jan 9, 2013 at 6:06 AM, wrote: > > > > > Mensaje original > > De: Clemens Ladisch > > Para: sqlite-users@sqlite.org > > Fecha: Wed, 09 Jan 2013 14:25:31 +0100 > > Asunto: Re: [sqlite] Problem whit sqlite_prepare_v2() in 3.7.5 version > andlater > > > > > > > >a...@zator.com

Re: [sqlite] Can I safely use the pragma synchronization = OFF?

On Sat, Jan 26, 2013 at 6:50 PM, Shuki Sasson wrote: > > Hi all, I read the documentation about the synchronization pragma. > It got to do with how often xSync method is called. > With synchronization = FULL xSync is called after each and every change to > the DataBase file as far as I understand.

Re: [sqlite] [sqlite-dev] Can I safely use the pragma synchronization = OFF?

t; makes a lot of sense as data blocks are not protected by the journal, > however with more robust File System that have full journal for metadata as > well as data it makes all the sense in the world to run with > synchronization = OFF and gain the additional performance benefits. > >

Re: [sqlite] [sqlite-dev] Can I safely use the pragma synchronization = OFF?

Sun, Jan 27, 2013 at 5:20 PM, Shuki Sasson wrote: > Pick up any book about UFS and read about the journal... > > Shuki > > On Sun, Jan 27, 2013 at 7:56 PM, Pavel Ivanov wrote: > >> > So in any file system that supports journaling fwrite is blocked until >> all >

Re: [sqlite] I suspect not - but is the database separator configurable?

On Fri, Feb 1, 2013 at 6:42 AM, message adams wrote: > Greetings; > > I've recently started using sqlite within Python, to help unit-test my > applications. > > My applications actually run against sybase, but I'd love to use a > connection to an in-memory sqlite to carry out my testing. > As part

Re: [sqlite] Deletion slow?

> Don't know if the index is updated after each row delete or after the whole > delete transaction is commited. For the first you can try: > > time sqlite3 trip.db "PRAGMA automatic_index= FALSE; delete from trip where > key<=1400;PRAGMA automatic_index= TRUE; reindex trip" PRAGMA automatic_inde

Re: [sqlite] Strange eviction or bypass of Linux page cache

> Anyone out there know how to correct this undesirable behaviour? > > PS. This only happens over NFS - local DB files behave as expected and fill > the OS page cache. Don't write your database to NFS. I'd guess that your problem is that NFS driver for some reason thinks that the file was changed

Re: [sqlite] SQLite error near "16": syntax error

On Tue, Feb 19, 2013 at 1:39 AM, mikkelzuuu wrote: > Hey guys, I'm getting this error (see title) > I'm using C# by the way. > > string StrQuery = @"INSERT INTO Test VALUES (" + > dataGridView1.Rows[i].Cells["Column1"].Value + ", " + > dataGridView1.Rows[i].Cells["Column2"].Value + ", " + > dataGr

Re: [sqlite] Question about aggregate returning empty row

That's SQL standard -- query with aggregate functions always return at least one row. Pavel On Fri, Feb 22, 2013 at 12:06 PM, Pierre Chatelier wrote: > Hello, > > [tested under 3.6.12 and 3.7.15.2] > > I have a question regarding the use of aggregate functions. > > Let's imagine the following d

Re: [sqlite] Memory DB - Query does not return all records after Update

> Example, If I insert 10 records and loop a query I receive > 10, 10, 10, 10, results > But, if I change one of the records during the loop I get > 10, 10, update record, 9, 10, This sounds gibberish. Could you please post your database schema and exact queries you issue in the exact or

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

On Tue, Feb 26, 2013 at 2:29 PM, Igor Tandetnik wrote: > On 2/26/2013 5:13 PM, anydacdev anydacdev wrote: >> >> I am struggling with SQLite's support for Oracle's DUAL table. >> >> The updated statement, now including Oracle's DUAL looks like. >> >> MERGE INTO x TGT >> USING (SELECT 'A_NAME' as na

Re: [sqlite] update the same record in multiple segments at once

On Thu, Feb 28, 2013 at 10:50 PM, Gert Van Assche wrote: > All, I don't know if this is possible. > > A segment in table "Files" contains a record "Content" with a fields like > this "XXX " > Needs to become "XXX 123 456 78 90" > > Based on a "Translation" ta

Re: [sqlite] inner vs. outer join inconsistency

On Sun, Mar 3, 2013 at 11:48 AM, Tom Matrix wrote: > Hello, > > I’ve encountered a problem, which is hardly reproducable on arbitrary > databases, therefore I attached one. > The problem is that inner join omits the result on large datasets. But > again, ’large’ does not necessarly refer simply th

Re: [sqlite] Efficient way to store counters

On Tue, Mar 12, 2013 at 8:29 PM, David King wrote: > I'm trying to find an efficient way to store simple incrementing integers but > I'm having trouble finding an efficient way to do it > > My database looks like: > > CREATE TABLE counters > k1, k2, > count, -- how many we've seen > expires

Re: [sqlite] Efficient way to store counters

>> First of all in the statement above you don't gain benefit from >> uniqueness and replace about 10k rows twice. > > Are you sure? The SELECT in the INSERT OR UPDATE selects "FROM > trans_counters_v AS c", the grouped temporary view. So it should only see any > given key pair once before it sta

Re: [sqlite] Efficient way to store counters

On Tue, Mar 12, 2013 at 11:03 PM, David King wrote: >> > At first I was just doing something like this pseducode: >> > update_counter(k1, k2, count=count+1, expires=now+count*1day) >> > if rows_updated != 1: insert_counter(k1, k2, count=1, expires=now+1day) >> >> Assuming these 2 statements consti

Re: [sqlite] Efficient way to store counters

On Wed, Mar 13, 2013 at 11:48 AM, David King wrote: >> BTW, in case you don't do that yet your best performance will be if >> you prepare your UPDATE and INSERT statements only once and then do >> bind + step + reset in that 100k times loop. > > > In principle I agree, but since the temporary-tabl

Re: [sqlite] How to achieve fastest possible write performance for a strange and limited case

On Fri, Mar 29, 2013 at 12:17 PM, Jeff Archer wrote: > I have previously made an apparently bad assumption about this so now I > would like to go back to the beginning of the problem and ask the most > basic question first without any preconceived ideas. > > This use case is from an image processi

Re: [sqlite] SQL Logic error or missing database

Do you know that VS2012 has known optimizer bugs? Pavel On Apr 6, 2013 5:01 AM, "ibrahim" wrote: > On 05.04.2013 17:01, Dan Kennedy wrote: > >> On 04/05/2013 09:08 PM, Rob Collie wrote: >> >>> Yeap, I'm on Visual Studio 2012. I've created a console app: >>> >>> >>> sqlite3 *oDatabase; >>> in

Re: [sqlite] Prepare SQL for Read-Only Database with Journal File

> If opening a DB read-only implies that the physical file is always opened > read-only, then I understand.  But, if read-only is just something for SQLite > to know that it can't modify the database, rollback would still be possible. You are right that opening database with SQLITE_OPEN_READONLY

Re: [sqlite] Multiple threads inserting to separate databases

The stack trace you've shown is a lock for allocating new page in page cache. It's probably the lock that helps SQLite ensure that total amount of consumed memory is not greater than the limit configured. But if you work only with in-memory databases then that check is moot and with next step SQLit

Re: [sqlite] Relative path to sqlite database

> Just a quick note though, when I move the .exe file to the desktop and > double-click on it, it crashes, which suggests that the bin/debug location is > only applicable when the application is run from within the IDE. Crash is probably because your desktop folder doesn't have db subdirectory w

Re: [sqlite] synchronization issue: no directory sync after unlink

AFAIK, before deleting journal file SQLite writes some zeroed header into it. This kind of header means that transaction is finished. And even if after power shutdown SQLite finds this journal persisting on disk it will see the header and will understand that this journal just needs to be deleted.

Re: [sqlite] Bug hunting in SQLite

My guess is you read blob data incorrectly. You get pointer to the data from SQLite, but actually read the data when memory has been already reused for other purposes. Pavel On Sunday, February 26, 2012, Patrik Nilsson wrote: > Dear All, > > I have a table called page (defined below) and have n

Re: [sqlite] Multiple WHENs in triggers

> I can't > believe that SQLite only allows only one trigger with only one WHEN per > action... can it? SQLite allows only one WHEN per trigger but any number of triggers per action (without any defined order of execution). So you can do it like this: CREATE TRIGGER UpdateSaleItemDescription1 AFT

Re: [sqlite] Constraint error messages

> (2) If one create an unique, named index, one cannot use an unique constraint > as there is no way to add constraints after the table creation (i.e. no alter > add constraint …). You don't need to create a unique constraint when you created a unique index. Unique index implies that you can't i

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

On Thu, Mar 1, 2012 at 8:50 AM, Grace Batumbya wrote: >>You can simply register your >>own lower/upper with one argument that looks wherever you want to know >>what locale to use. > > The part of registering a function to override lower/upper I think I > understand. > > But if I wanted to persist

Re: [sqlite] Constraint error messages

On Thu, Mar 1, 2012 at 9:01 AM, Petite Abeille wrote: > On Mar 1, 2012, at 2:51 PM, Pavel Ivanov wrote: > >>> (2) If one create an unique, named index, one cannot use an unique >>> constraint as there is no way to add constraints after the table creation >>&

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

ace Batumbya > Research Assistant | Seneca CDOT > Phone: 416-491-5050 x3548 > cdot.senecac.on.ca > > On 3/1/2012 08:56, Grace Batumbya wrote: > > Is there an example extension you know that I could look at that does this? > (i am a novice at SQLite) > __

Re: [sqlite] Views and Performance

> If I am querying data just from t1, is there a performance penalty for using > myview in the query?  Or will the query planner generate approximately the > same bytecode as it would if I'd simply queried t1? Yes, there is performance penalty and no it can't generate the same bytecode. If you a

Re: [sqlite] Views and Performance

> What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not > specified? INNER is default. Pavel On Fri, Mar 2, 2012 at 11:37 AM, Rob Richardson wrote: > What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not > specified? > > RobR > > -Original Message- > Fr

Re: [sqlite] Interchangeably using SQLite .db file between desktop and android mobile device

> Is there an example(s?) of a step-by-step for moving/using an SQLite .db file > interchangeably between a desktop and an Android mobile device? What kind of example you want? SQLite's database format is the same for any platform. So just copy the file (when it's not open by any application) and

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

..), but that is about all > I know at the moment. > > Thanks. > > Regards, > > Grace Batumbya > Research Assistant | Seneca CDOT > Phone: 416-491-5050 x3548 > cdot.senecac.on.ca > > On 3/1/2012 09:48, Pavel Ivanov wrote: > > Given that there exists a table db_loca

Re: [sqlite] processes stuck on database locked

First your second process gets a SHARED lock on the database to read it, then your first process gets RESERVED lock on the database to indicate that it will change it. Then your second process tries to promote its SHARED lock to RESERVED one, sees that RESERVED lock has been already taken and can't

Re: [sqlite] processes stuck on database locked

On Wed, Mar 7, 2012 at 8:49 AM, Simon Slavin wrote: > On 7 Mar 2012, at 1:41pm, Pavel Ivanov wrote: > >> First your second process gets a SHARED lock on the database to read >> it, then your first process gets RESERVED lock on the database to >> indicate that it will cha

Re: [sqlite] processes stuck on database locked

ransaction restarts" in this case, just statement retries. That's why it can be seen as fixing the issue. Pavel On Wed, Mar 7, 2012 at 12:28 PM, Jay A. Kreibich wrote: > On Wed, Mar 07, 2012 at 08:41:17AM -0500, Pavel Ivanov scratched on the wall: >> First your second proce

Re: [sqlite] SIGBUS error in case of disk full with WAL mode

> Question:  Does anybody know of a better way to get memory shared among > processes other than to create a fake file and mmap() it?  Are there some > magic options to mmap() (perhaps Linux-only options) that prevent it from > actually writing to disk? Why don't you use shm_open() instead of a re

Re: [sqlite] INSERT OR REPLACE

> So, please.. add INSERT OR UPDATE.  I'd ask that INSERT OR REPLACE be > modified to do an UPDATE to the record if the UNIQUE Constraint is violated > instead of the delete, but that would break existing applications. > > Wasn't that the original intent of INSERT OR REPLACE? No, because INSERT

Re: [sqlite] Bug? Empty string stored as NULL?

Where do you see NULL? I see empty string. SQLite version 3.7.5 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table a (t text); sqlite> insert into a select ''; sqlite> .nullvalue NULL sqlite> select * from a; sqlite> Pavel On Thu, Mar 8, 2012 at 2:4

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

> According to the documentation on SELECT statements > http://www.sqlite.org/lang_select.html > It seems possible to write join chains as A join (B join C). (using a '(' > join-source ')' single-source ) ... > It seems that parsing is ok (no syntax error) but sources in the sub join > can't be

Re: [sqlite] NaN in, 0.0 out?

> Storing +infinity, for example.  It seems that in IEEE terms +infinity is > different from NaN, but SQLite return NULL in both instances. This is apparently some problem with your test setup. SQLite version 3.7.5 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>

Re: [sqlite] Visual Studio 2008 Express and sqlite3.c

> Maybe you somehow include a file that redefines 'not'? That's a brilliant idea. Just change "not" to "!" here: >  int not;         /* True if the NOT keyword is present */ And after that I'm sure you'll see error saying > ..\Sources\sqlite3.c(107831) : error C2143: syntax error : missing ':'

Re: [sqlite] Foreign Key Problems

> I get a foreign Key mismatch error.  No matter what value I supply for > FKeyColumn, even NULL, I get the same error. Did you create unique index on t1 (RefColumn) ? IIRC, SQLite always fails foreign key check if there's no index on a referenced column. Pavel On Sun, Apr 1, 2012 at 8:22 PM,

Re: [sqlite] Foreign Key Problems

> Also, it appears that the foreign_keys setting only applies for the life of > a db connection and they have to be enabled every time a db is opened.  Is > that correct? Yes, that's correct. > Do all PRAGMA settings work like that? Almost. Some of exceptions are page_size, auto_vacuum, journal_

Re: [sqlite] SQlite3 Locking Question

> I have a question regarding the locking in Sqlite3: Does Sqlite3 lock the > entire database when there is a lock on only one table? Yes. > But if I don't fetch all the results and close the reading > connection, I cannot write to another table in the same database. Is this the > intended beha

Re: [sqlite] Integer Truncation Issue

> When I browse the table it shows one record > I open the db connection in c# and it still has 31821 records in it... Which means you open connections to different databases. Try to use absolute path for database name. Pavel On Fri, Apr 6, 2012 at 11:20 AM, Jay Howard wrote: > To add to the

Re: [sqlite] is it reliable to directly read sqlite3_sequence?

On Sun, Apr 8, 2012 at 9:25 PM, Simon Slavin wrote: > On 9 Apr 2012, at 2:14am, Stephan Beal wrote: > >> Is it reliable/portable/well-defined to use (read-only) the sqlite_sequence >> table to find the last insertion ID for a given table? i know about >> sqlite3_last_insert_id(), but i'm wonderin

Re: [sqlite] is it reliable to directly read sqlite3_sequence?

> i just (experimentally) > implemented a lookup on sqlite_sequence if the hint is provided, else > falling back to sqlite3_last_insert_id(). It "works for me", but if it's a > potential portability problem then i'll remove it. If you read the link Simon gave you'll see that sqlite_sequence have r

Re: [sqlite] Two potential memory leaks in sqlite-3.7.11

FYI: both leaks are fixed in trunk: http://www.sqlite.org/src/info/93a0f452a7. Pavel 2012/4/11 Zhenbo Xu : > What about the second one? > The heap object allocated at > 7547 home_dir = > find_home_dir()

Re: [sqlite] Using "attach database" to work around DB locking

On Wed, Apr 11, 2012 at 12:01 PM, Ian Katz wrote: > The Sqlite3 manual says that any locking operations affect the entire > database, not individual tables. > http://www.sqlite.org/lockingv3.html > > I was wondering if this effect could be compensated for by splitting > tables into separate databa

Re: [sqlite] Using "attach database" to work around DB locking

table -- not very good normalization, but retaining good read > performance without having write performance degrade over time.  The > join-all-tables-together query would be used for generating a logfile > in the old format, just in case we need it. > > Am I missing any features of S

Re: [sqlite] auto-incrementing integer in composite primary key

> So, if a query returns one or more rows today, the same query (that is, the > same query params with an additional time stamp param) should return exactly > the same result 3 years from now even if the rows themselves may have been > modified. I just want to note that to support this function

Re: [sqlite] auto-incrementing integer in composite primary key

> 1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than > `created_on <= :provided_date`? What if there are several versions created before your provided_date? Not all queries will allow to add `order by created_on desc limit 1`. Pavel On Mon, Apr 16, 2012 at 4:37 PM, Puneet

Re: [sqlite] BUG: No reliable way to import strings from a file

>>> The type you probably want is TEXT. >> >> The rules are such that VARCHAR column also gets TEXT affinity. > > I thought it was worth explaining to him what was really going on.  Didn't > want him going through 30 databases changing hundreds of fields to VARCHAR > when he should be changing th

Re: [sqlite] help

> 11       COMMENT         VARCHAR(150)       0                            0 > > So it is possible: ? > UPDATE t1 SET comment = 'here the text of 160 characters long' Yes, that's possible. You can put text of any length into VARCHAR or TEXT field no matter what size you declare for it (you can eve

Re: [sqlite] Memory Usage/ Drawbacks of Statements

> 1. Do statements do any thing that would require a lot of memory to be > maintained? No, they don't need a lot of memory, but still some memory is used. So if you have like thousands of statements you should worry about this. If you have 20 or 30 statements your database cache will likely consum

Re: [sqlite] free list performance

> I think that the PRAGMA for auto-vacuum might be the right thing for your > situation.  Unfortunately it would have to be done before any tables were > created.  The simplest way to do this for a database which already exists > might be to use the shell tool to dump the existing database to SQ

Re: [sqlite] SQLITE3 64-bit version

> I've also read Richard Hipp's > advice about potential issues if multiple threads of the same process access > the same DB so I figured I'd be safer if I have just one SQLITE module I guess you refer to this Richard's advice: http://www.mail-archive.com/sqlite-users@sqlite.org/msg69579.html. Not

Re: [sqlite] Potential Solution to -- Is it possible to preclude the latest sqlite 3.7.11 Windows warning message

Re: Permissions (Simon Slavin) >> 5. free list performance (Max Vlasov) >> 6. help (? ?) >> 7. Re: help (Simon Davies) >> 8. Re: Permissions (Steinar Midtskogen) >> 9. help (? ?) >> 10. Help (? ?) >> 11. Help (???

Re: [sqlite] Possible bug when rebuilding amalgamation without triggers or altertable

> To clarify, the below steps to reproduce include building the amalgamation > from scratch, and the errors I demonstrate occur after building and > compiling a new amalgamation with -DSQLITE_OMIT_ALTERTABLE and > -DSQLITE_OMIT_FOREIGN_KEY. It's still not clear, did you define these OMITs while bu

Re: [sqlite] Possible bug when rebuilding amalgamation without triggers or altertable

On Thu, Apr 26, 2012 at 10:09 AM, Stephan Beal wrote: > On Thu, Apr 26, 2012 at 4:05 PM, Pavel Ivanov wrote: >> amalgamation. If yes then these errors fall under the "unsupported >> options" statement Stephan points you to. But probably SQLite team >> will fix this

Re: [sqlite] the xRead method in sqlite3_io_methods

> All these codes are dealing with the header? They are not about header but they are essentially dealing with fatal errors. First example is (as comment says) executed when database tries to recover after a crashed process. And short read here means that journal is corrupted and should be ignored

Re: [sqlite] the xRead method in sqlite3_io_methods

nread portions of the buffer with zeros. A VFS that > fails to zero-fill short reads might seem to work. However, failure to > zero-fill short reads will eventually lead to database corruption. ", Which > listed in the official document: http://www.sqlite.org/c3ref/io_methods.html

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug?? -- test case!

> If two database connections share the same cache, and one connection rolls > back, that means it will be changing cache content out from under the other > database connection, so any queries ongoing in the other connection have to > abort. Richard, Could you please explain this? I understand th

<    4   5   6   7   8   9   10   11   12   >