Re: [sqlite] defalut value of col

On Oct 23, 2008, at 4:51 AM, Dennis Cote wrote: > Antoine Caron wrote: >> >> I was expecting -1 instead of NULL as default value, can anyone >> explain me >> that ? >> > > That looks like a bug in the table_info pragma. I think so too. Fix is here: http://www.sqlite.org/cvstrac/chngview?cn

Re: [sqlite] defalut value of col

Antoine Caron wrote: > > I was expecting -1 instead of NULL as default value, can anyone explain me > that ? > That looks like a bug in the table_info pragma. You should probably report it. The following test script shows that the correct default value is used even though the an incorrect nul

[sqlite] Default Row value

Hi guys, I'm not sure I've posted at all my previous msg so here it is again with few more explanation I'm creating a table using the following : # CREATE TABLE IF NOT EXISTS `test_table` ( `test_field1` INTEGER DEFAULT -1, `test_field2` INTEGER DEFAULT NULL ); #

Re: [sqlite] Compiling with mingw32/msys on Windows

I got the CVS version and it mostly works, but I'm hitting a bug in mingw32 gcc now (which, after some investigation, may have been fixed in a later version than the current release). At least I can take the processed amalgamation and use it in a VS2k5 project. Note: SQLITE_ENABLE_IOTRACE doesn't

Re: [sqlite] Compiling with mingw32/msys on Windows

On Wed, 22 Oct 2008 14:34:04 -0400, "Shane Harrelson" <[EMAIL PROTECTED]> wrote: >I was able to duplicate the issue, and I've updated the configure >scripts to correct the problem. OK, great. Is the tarball updated, or do I need to get it from CVS? >If you are generating your makefile using conf

[sqlite] INSERT OR REPLACE

Dear All, According to the documentation, and as far as I can see, also in reality, INSERT OR REPLACE will remove existing columns which conflict regarding a unique key. Quite contrary to my initial foolish belief, it doesn't work at all like a hypothetical INSERT OR UPDATE, which in case of mult

[sqlite] defalut value of col

Hi guys, Here's a quick one I'm creating a table using the following : # CREATE TABLE IF NOT EXISTS `test_table` ( `test_field` INT NOT NULL DEFAULT -1 ); # Note the default -1 I then do # PRAGMA table_info (test_table) # i'm getti

Re: [sqlite] Compiling with mingw32/msys on Windows

I was able to duplicate the issue, and I've updated the configure scripts to correct the problem. If you are generating your makefile using configure, you can pass it options like this: configure CFLAGS=-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1 This will generate a makefile which passes the SQLITE

Re: [sqlite] Update question

Jon Dixon <[EMAIL PROTECTED]> wrote: > I am updating how I handle dates/durations in a database of mine and > I would like to come up with an SQL command to make the change for > me. > > The old format had a field Departure (-MM-DD) and a field Nights > (an integer), where the new format > stil

Re: [sqlite] Shared cache clarification.

Excellent, that's what I was hoping for. Thanks, Dan! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dan Sent: Wednesday, October 22, 2008 5:54 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Shared cache clarification. On Oct 22, 20

Re: [sqlite] Update question

> My stab at an SQL statement to pull this change off is as follows: > UPDATE > Events SET Return = date(d, n + ' days') WHERE pkIndex IN (SELECT > pkIndex, Nights AS n, Departure AS d FROM Events WHERE Return = '' AND Nights > != '') Why do you think you need the sub-select? Assuming that they

Re: [sqlite] CURRENT_DATE Behavior

On Oct 21, 2008, at 10:44 PM, jonwood wrote: > Given some of the comments here, one might wonder how those poor MS > SQL Server > folks are able to get anything working at all. ;-) Good point. Usually, "they" don't have anything working at all :)) Cheers, PA. -- http://alt.textdrive.com/nan

[sqlite] Update question

Hello all, I am updating how I handle dates/durations in a database of mine and I would like to come up with an SQL command to make the change for me. The old format had a field Departure (-MM-DD) and a field Nights (an integer), where the new format still uses Departure but adds Return (YY

Re: [sqlite] Shared cache clarification.

On Oct 22, 2008, at 11:32 PM, Hynes, Tom wrote: > Hello, > > > > I noticed that the documentation for PRAGMA read_uncommitted in > http://www.sqlite.org/pragma.html says: > > > > "...Cache sharing is enabled using the sqlite3_enable_shared_cache() >

Re: [sqlite] FTS3 snippet generation speed

On 10/22/08, Tim Mohler <[EMAIL PROTECTED]> wrote: > Hi all, > > I'm happily using snippet generation in FTS3, but I notice that it has a > fairly large performance impact (5 seconds or more) on queries that return > lots of data (tens of thousands of rows) In my application, I never display >

[sqlite] Shared cache clarification.

Hello, I noticed that the documentation for PRAGMA read_uncommitted in http://www.sqlite.org/pragma.html says: "...Cache sharing is enabled using the sqlite3_enable_shared_cache() API and is only available between connections running t

[sqlite] FTS3 snippet generation speed

Hi all, I'm happily using snippet generation in FTS3, but I notice that it has a fairly large performance impact (5 seconds or more) on queries that return lots of data (tens of thousands of rows) In my application, I never display more than the first 20 rows returned by the MATCH operator, and I

[sqlite] Tip: Perfromance over large datasets

Hi I suspect most of you know this, but since it helped me I decided to post it. I have around 5 million records in a sqlite file of about 400MB. Running joins over this was taking hours! And the disk went mental. Tweaking the following two pragmas solved the problem though. Hours to seconds! PRA

Re: [sqlite] CURRENT_DATE Behavior

P Kishor-3 wrote: > > 1. I don't fully understand how to convert UTC to local time? > 2. on a Website > For reasons I don't understand, you split one question in half and called it two questions. P Kishor-3 wrote: > > b. On the other hand, if you want to display on the web page, time > loca

Re: [sqlite] CURRENT_DATE Behavior

On 10/21/2008 05:23 AM, Doug wrote: > In an effort to share some hard-won insight: > > I used to store local times in the database, but it's a bad idea and I got > bit. Think about what gets stored: A number of seconds/nanoseconds/whatever > from some time in the past (Jan 1, 1970 for example). W

Re: [sqlite] CURRENT_DATE Behavior

jonwood wrote: > > cmartin-2 wrote: > >>I suspect it is quite common, e.g., web apps built on SQLServer backends >>are quite likely to use UTC. At any rate, it is definitely a design >>decision, if one expects that local times will always work in all >>scenarios, but all means use local times.

Re: [sqlite] CURRENT_DATE Behavior

On 10/22/08, jonwood <[EMAIL PROTECTED]> wrote: > > > P Kishor-3 wrote: > > > > I thought it was very clearly indicated in one of the answers in this > > thread on how to do that. Here it is again... > > > > datetime('now', 'localtime') > > > > > Yes, that was the answer. And it was implemen

Re: [sqlite] CURRENT_DATE Behavior

P Kishor-3 wrote: > > I thought it was very clearly indicated in one of the answers in this > thread on how to do that. Here it is again... > > datetime('now', 'localtime') > Yes, that was the answer. And it was implemented in my application within moments of being posted. The discussion has

Re: [sqlite] CURRENT_DATE Behavior

On 10/22/08, jonwood <[EMAIL PROTECTED]> wrote: > > > cmartin-2 wrote: > > > > I suspect it is quite common, e.g., web apps built on SQLServer backends > > are quite likely to use UTC. At any rate, it is definitely a design > > decision, if one expects that local times will always work in all

Re: [sqlite] want that particular time and date when a row is inserted in a table

On 10/22/08, Hari <[EMAIL PROTECTED]> wrote: > Hi All, > i want to know the particular that time and date when a row is inserted > in a table. > as i am using sqlite3_exec() function for sql query. > my sql statements are like: > sqlite3_exec(--, "create table my_table (key INTEGER,tag TEXT, t

Re: [sqlite] [sqlite3] Macports Build Failed

When it comes to the nuts and bolts of building this stuff I understand the basic stuff. I'll look into what you've suggested but I can't guarantee that it'll help me ;) -Brent From: Shane Harrelson <[EMAIL PROTECTED]> To: General Discussion of SQLite Database

Re: [sqlite] [sqlite3] Macports Build Failed

I believe it's 3.6.2 as provided by the MacPorts system (used to be DarwinPorts similar to FINK). I've successfully installed 3.6.4 from source outside of the MacPorts system but it won't register when I'm building other sources or programs. From: Shane Harrel

Re: [sqlite] [sqlite3] Macports Build Failed

Taking a closer look at your output, it looks like you're using version 3.6.2. And if you were building from the amalgamation downloads the TK_* opcodes would have not been undefined (they are in sqlite3.c), which means you are building from the individual sources. The TK_* opcodes will be in par

Re: [sqlite] [sqlite3] Macports Build Failed

What version are you trying to build? 3.6.4? Where did you get your sources? The SQLite website? Are you building from the amalgamation? or the individual sources? -Shane On Wed, Oct 22, 2008 at 8:07 AM, Brent Austin <[EMAIL PROTECTED]> wrote: > I've been trying to get sqlite3 to build on my M

[sqlite] [sqlite3] Macports Build Failed

I've been trying to get sqlite3 to build on my Mac under macports for ages. Any help would be appreciated-- client-6x-1xx-17-x14:~ brent1a$ sudo port install sqlite3 ---> Building sqlite3 with target all Error: Target org.macports.build returned: shell command " cd "/opt/local/var/macports/buil

Re: [sqlite] want that particular time and date when a row is insertedin a table

"Hari" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > i want to know the particular that time and date when a row is > inserted in a table. > as i am using sqlite3_exec() function for sql query. > my sql statements are like: > sqlite3_exec(--, "create table my_table (key INTEGER,tag

Re: [sqlite] Foreign Key Constraints

"Daniel Zingaro" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I'm wondering if anyone can help me understand the discrepancy here. > Are recursive triggers required to implement some FK constraints? I can't think of any reason why, except perhaps if you have foreign keys referrin

Re: [sqlite] Why is subselect much faster than join?

cody <[EMAIL PROTECTED]> writes: > > Why is this (1,44 seconds) > > select * from movies m > where m.movieid in (select mp.movieid from people p, moviepeople mp > where p.fullname="Spencer, Bud" and mp.personid=p.personid) > > much faster than the following (Runtime several minutes!): > > sel

[sqlite] Why is subselect much faster than join?

Why is this (1,44 seconds) select * from movies m where m.movieid in (select mp.movieid from people p, moviepeople mp where p.fullname="Spencer, Bud" and mp.personid=p.personid) much faster than the following (Runtime several minutes!): select * from movies m join moviepeople mp on mp.movieid=m

Re: [sqlite] SqLite Report Generator For Windows

Thanks a lot. Actually I don't want to use ODBC. If I use ODBC then I will be able to use Crystall Report. I don't want ODBC in my project, don't even want to create it using code itself. That's why I am using SQLITE. I don't have enough time to develop a reporting control of my own ( Only to fullf

Re: [sqlite] CURRENT_DATE Behavior

MikeW <[EMAIL PROTECTED]> writes: > > jonwood <[EMAIL PROTECTED]> writes: > >> Greetings, >> ... >> Is there ANY way to override this behaviour? Or must I simply initialize all >> such fields explicitly if I would like to set it to the current date in my >> particular time zone? >> >> Thanks. > ..