Re: [sqlite] What is the best way to store date value in sqlite
Ah, sorry, didn't know about the ole version. I'll have to check to see if they implemented that in Excel etc. I had a lot of trouble with it under Access and Excel --- "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > HI Jay, > > CTime::GetTime() return time_t which is long integer. > > Ming > > Jay wrote: > > >The MFC date is stored as a floating point number, the unix > >date as a long integer. The MFC style date, used through out > >windows has a maximum date of somewhere around 2038AD. The unix > >variant goes several thousand years farther. > > > >You could certainly store the float date as a float type field > >but none of the date functions built into the sqlite engine would > >work with them. The unix variant will work with sqlite functions. > > > >Also, be aware that due to rounding of MFC > >dates you will occasionally get cases where dates that appear > >identical will not match because of floating point precision > >and Microsoft display routines that do not display fractional > seconds. > > > >--- "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > > > > > > >>Hi All, > >> > >>I want to store MFC date (CTime or COleDateTime) value in sqlite, > but > >> > >>don't know what is the best way to store it. I am running into > >>trouble > >>when I store date as Text in sqlite, because I can't no longer > apply > >>sqlite date time functions( datetime(), date()...) to it. Result in > I > >> > >>can't do a order by the date filed. > >> > >>The other question would be does sqlite have date limits (Upper > Bound > >> > >>and Lower Bound) for the datetime() functions. > >> > >> > >> > > > > > >= > > > >- > > > >"Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the > walls of the ancient tomb of the petrified pharaoh, he vowed there > would be no curse on him like on that other Lord, unless you count > his marriage to Lady Tarlington who, when the lost treasure was > found, will be dumped faster than that basket in the bulrushes." > > Melissa Rhodes > >- > > > >The Castles of Dereth Calendar: a tour of the art and architecture > of Asheron's Call > >http://www.lulu.com/content/77264 > > > > > > > >__ > >Do you Yahoo!? > >Yahoo! Mail - Helps protect you from nasty viruses. > >http://promotions.yahoo.com/new_mail > > > > > > > = - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] SQLite Tutorial: Request For Comments
[EMAIL PROTECTED] wrote: The html has been cleaned up on the tutorial: http://prdownloads.sourceforge.net/souptonuts/README_sqlite_tutorial.html?download Has this been updated recently for the 3.1.x series? Thanks Bob Cochran
RE: [sqlite] What is the best way to store date value in sqlite
Not correct (fully) - the CTime class does have this limit, but not COleDateTime (quote from MSDN: "The COleDateTime class handles dates from 1 January 100 - 31 December .") And you can access the double using COleDateTime::m_dt member variable. For comparison it's best to use GetYear(), GetMonth(), GetDay(), GetHour(), GetMinute(), GetSecond() functions - that's true. Dennis -Original Message- From: Jay [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 22, 2005 12:55 PM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: Re: [sqlite] What is the best way to store date value in sqlite The MFC date is stored as a floating point number, the unix date as a long integer. The MFC style date, used through out windows has a maximum date of somewhere around 2038AD. The unix variant goes several thousand years farther. You could certainly store the float date as a float type field but none of the date functions built into the sqlite engine would work with them. The unix variant will work with sqlite functions. Also, be aware that due to rounding of MFC dates you will occasionally get cases where dates that appear identical will not match because of floating point precision and Microsoft display routines that do not display fractional seconds. --- "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Hi All, > > I want to store MFC date (CTime or COleDateTime) value in sqlite, but > > don't know what is the best way to store it. I am running into trouble > when I store date as Text in sqlite, because I can't no longer apply > sqlite date time functions( datetime(), date()...) to it. Result in I > > can't do a order by the date filed. > > The other question would be does sqlite have date limits (Upper Bound > > and Lower Bound) for the datetime() functions. > = - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail
Re: [sqlite] What is the best way to store date value in sqlite
HI Jay, CTime::GetTime() return time_t which is long integer. Ming Jay wrote: The MFC date is stored as a floating point number, the unix date as a long integer. The MFC style date, used through out windows has a maximum date of somewhere around 2038AD. The unix variant goes several thousand years farther. You could certainly store the float date as a float type field but none of the date functions built into the sqlite engine would work with them. The unix variant will work with sqlite functions. Also, be aware that due to rounding of MFC dates you will occasionally get cases where dates that appear identical will not match because of floating point precision and Microsoft display routines that do not display fractional seconds. --- "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: Hi All, I want to store MFC date (CTime or COleDateTime) value in sqlite, but don't know what is the best way to store it. I am running into trouble when I store date as Text in sqlite, because I can't no longer apply sqlite date time functions( datetime(), date()...) to it. Result in I can't do a order by the date filed. The other question would be does sqlite have date limits (Upper Bound and Lower Bound) for the datetime() functions. = - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail
Re: [sqlite] tricky date time problem
--- Lloyd Thomas <[EMAIL PROTECTED]> wrote: > I have a query which calculates the number of events during an hour > by the > minute. It needs to work out which minute has the most events and > the > average events during that hour. So it should return an array of 60 > results > for an hour where I can use the MAX() feature in php to find the peak It's too bad Sqlite doesn't have the modulo operator, you could select and group by the modulo of the seconds of each date and get your nice groupings very simply. If you can write a user defined function in whatever language you're using you might try that. __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com
Re: [sqlite] What is the best way to store date value in sqlite
The MFC date is stored as a floating point number, the unix date as a long integer. The MFC style date, used through out windows has a maximum date of somewhere around 2038AD. The unix variant goes several thousand years farther. You could certainly store the float date as a float type field but none of the date functions built into the sqlite engine would work with them. The unix variant will work with sqlite functions. Also, be aware that due to rounding of MFC dates you will occasionally get cases where dates that appear identical will not match because of floating point precision and Microsoft display routines that do not display fractional seconds. --- "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Hi All, > > I want to store MFC date (CTime or COleDateTime) value in sqlite, but > > don't know what is the best way to store it. I am running into > trouble > when I store date as Text in sqlite, because I can't no longer apply > sqlite date time functions( datetime(), date()...) to it. Result in I > > can't do a order by the date filed. > > The other question would be does sqlite have date limits (Upper Bound > > and Lower Bound) for the datetime() functions. > = - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail
Re: [sqlite] What is the best way to store date value in sqlite
--- "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Anyway get get Unix Epoch Time in Windows MFC, or win32? Yes, the same way you do in unix. long ago; time( & ago ); = - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.com
[sqlite] sqlite_exec_printf and length modifiers
Hi, Does SQLite's sqlite_exec_printf function accept length modifiers, like printf from stdio does? It doesn't seem like it does, which is bad for me who want to insert 64 bit integers (using the ll modifier). If it doesn't, any other idea of how I can convert 64 bit integers to strings before inserting? Thanks! Tomas Franzén Lighthead Software http://www.lightheadsw.com/ I'm listening to Matchbox 20 - Bent
Re: [sqlite] tricky date time problem
I have a query which calculates the number of events during an hour by the minute. It needs to work out which minute has the most events and the average events during that hour. So it should return an array of 60 results for an hour where I can use the MAX() feature in php to find the peak minute. You may find it easier to write an aggregate function and use that. http://www.sqlite.org/capi3ref.html#sqlite3_create_function If you aren't using the SQLite C api directly, then the wrapper you use may provide this ability. Roger
Re: [sqlite] tricky date time problem
I am trying to simplify my query but seem to be failing at the first hurdle.
While if I do
select * from event_id from eveny_data where event_time between '2004-04-07
10:00:00' and '2004-04-07 10:59:59';
returns 15 rows
select * from event_id from eveny_data where event_time between
datetime('2004-04-07 10:00:00','+1 minutes') and datetime ('2004-04-07
10:59:59','+1 minutes');
returns nothing.
Am I using the right datetime modifiers?
- Original Message -
From: "Lloyd Thomas" <[EMAIL PROTECTED]>
To:
Sent: Monday, February 21, 2005 9:49 PM
Subject: [sqlite] tricky date time problem
I have a query which calculates the number of events during an hour by the
minute. It needs to work out which minute has the most events and the
average events during that hour. So it should return an array of 60
results for an hour where I can use the MAX() feature in php to find the
peak minute.
currently the query looks like this:
SELECT event_id, intdur FROM event_data where event_time <=
datetime('2004-04-07 00:00:00', '+ i minutes')
AND datetime('2004-04-07 00:00:00', '+ i minutes', '- intdur seconds') <=
event_time";
but it takes an age to work as I have to run the query 60 times for each
hour
I was thinking of creating a seperate table with a row for each minute and
then run a query against that table using the minute integer as part of a
sub-select against the event table. I have tried but can't get it to work.
so far I get an error: only a single result allowed for a SELECT that is
part of an expression
--
SELECT count(i) from integers60 WHERE (SELECT event_id, intdur from
event_data where event_time <= datetime('2004-04-07 10:00:00', '+ i
minutes') AND datetime('2004-04-07 10:00:00', '+ i minutes', '+ intdur) <=
event_time)
Can some one help?
Lloyd
Re: [sqlite] What is the best way to store date value in sqlite
You can use SQLite for this purpose (instead of C) as described in http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions. I don't use C under Microsoft so I can't help you there (I only use C under Linux). I do know that making Jan 1, 1970 00:00:00 the same as 0 seconds, you could write your own C subroutine to convert from DHMS2SEC (i.e. from Gregorian hour minute seconds) to seconds and viceversa, SEC2DHMS. Regards, [EMAIL PROTECTED] NCCI Boca Raton, Florida 561.893.2415 greetings / avec mes meilleures salutations / Cordialmente mit freundlichen Grüßen / Med vänlig hälsning "[EMAIL PROTECTED] co.ca" > >Ming: >I asked the same question about two weeks ago to this same list. I was >directed to the following location: > >http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions > >I ended up saving my date/time in Unix Epoch Time. Then you can retrieve >with the datetime function SELECT datetime(column-name,'unixepoch') from >table-name. I use perl under Unix so I actually convert to Unix Epoch >format before updating table. > >Regards, > >[EMAIL PROTECTED] >NCCI >Boca Raton, Florida >561.893.2415 >greetings / avec mes meilleures salutations / Cordialmente >mit freundlichen Grüßen / Med vänlig hälsning > > > > "[EMAIL PROTECTED] > co.ca"cc: > 02/21/2005 05:29 Subject: [sqlite] What is the best way to store date value in sqlite > PM > Please respond to > sqlite-users > > > > > > >Hi All, > >I want to store MFC date (CTime or COleDateTime) value in sqlite, but >don't know what is the best way to store it. I am running into trouble >when I store date as Text in sqlite, because I can't no longer apply >sqlite date time functions( datetime(), date()...) to it. Result in I >can't do a order by the date filed. > >The other question would be does sqlite have date limits (Upper Bound >and Lower Bound) for the datetime() functions. > >Many thanks, > >Ming > > > > > > >
Re: [sqlite] What is the best way to store date value in sqlite
Anyway get get Unix Epoch Time in Windows MFC, or win32? [EMAIL PROTECTED] wrote: Ming: I asked the same question about two weeks ago to this same list. I was directed to the following location: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions I ended up saving my date/time in Unix Epoch Time. Then you can retrieve with the datetime function SELECT datetime(column-name,'unixepoch') from table-name. I use perl under Unix so I actually convert to Unix Epoch format before updating table. Regards, [EMAIL PROTECTED] NCCI Boca Raton, Florida 561.893.2415 greetings / avec mes meilleures salutations / Cordialmente mit freundlichen Grüßen / Med vänlig hälsning "[EMAIL PROTECTED] co.ca" Hi All, I want to store MFC date (CTime or COleDateTime) value in sqlite, but don't know what is the best way to store it. I am running into trouble when I store date as Text in sqlite, because I can't no longer apply sqlite date time functions( datetime(), date()...) to it. Result in I can't do a order by the date filed. The other question would be does sqlite have date limits (Upper Bound and Lower Bound) for the datetime() functions. Many thanks, Ming
[sqlite] What is the best way to store date value in sqlite
Hi All, I want to store MFC date (CTime or COleDateTime) value in sqlite, but don't know what is the best way to store it. I am running into trouble when I store date as Text in sqlite, because I can't no longer apply sqlite date time functions( datetime(), date()...) to it. Result in I can't do a order by the date filed. The other question would be does sqlite have date limits (Upper Bound and Lower Bound) for the datetime() functions. Many thanks, Ming
Re: [sqlite] Feature request
On Mon, 2005-02-21 at 13:48 -0800, Jay wrote: > How about the ability to use the aliased column name in the > where, having, and order by clauses? > > I.E. > > SELECT People, ROUND(Sales,2) AS blah > FROM list_table > GROUP BY People > ORDER BY blah Works fine when I try it. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] tricky date time problem
I have a query which calculates the number of events during an hour by the
minute. It needs to work out which minute has the most events and the
average events during that hour. So it should return an array of 60 results
for an hour where I can use the MAX() feature in php to find the peak
minute.
currently the query looks like this:
SELECT event_id, intdur FROM event_data where event_time <=
datetime('2004-04-07 00:00:00', '+ i minutes')
AND datetime('2004-04-07 00:00:00', '+ i minutes', '- intdur seconds') <=
event_time";
but it takes an age to work as I have to run the query 60 times for each
hour
I was thinking of creating a seperate table with a row for each minute and
then run a query against that table using the minute integer as part of a
sub-select against the event table. I have tried but can't get it to work.
so far I get an error: only a single result allowed for a SELECT that is
part of an expression
--
SELECT count(i) from integers60 WHERE (SELECT event_id, intdur from
event_data where event_time <= datetime('2004-04-07 10:00:00', '+ i
minutes') AND datetime('2004-04-07 10:00:00', '+ i minutes', '+ intdur) <=
event_time)
Can some one help?
Lloyd
[sqlite] Feature request
How about the ability to use the aliased column name in the where, having, and order by clauses? I.E. SELECT People, ROUND(Sales,2) AS blah FROM list_table GROUP BY People ORDER BY blah = - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com
Re: [sqlite] sluggish operation on os x?
Very much so. And even to make it a runtime-changeable pragma. (Because, otherwise things like fink packages would have a difficult decision to make. only the application really knows, whether syncing is absolutely required, and to which degree. If the fink author needs to make the choice, I'd opt for regular 'fsync()', too (who plugs out their OS X anyway.. ;) 21.2.2005 kello 21:00, Curtis King kirjoitti: My application does this and the performance was still very poor on OS X with F_FULLSYNC on. Since OS X is the only OS which has F_FULLFSYNC it would be nice to make the use of it a configure option. ck
Re: [sqlite] sluggish operation on os x?
On Feb 21, 2005, at 11:44 AM, Curtis King wrote: On 21-Feb-05, at 11:11 AM, [EMAIL PROTECTED] wrote: OK -- so, you are willing to accept the risk of non-recoverable database corruption in the event of power outage or other kinds of catastrophic system failure (including the plug being pulled on a FireWire drive without it being properly unmounted)? I.e. that risk is perceived to be acceptably small that the performance hit is not justifiable? The performance hit is much larger than the risk, so in some cases, no FireWire drives and there is an UPS, the risk is acceptable for the performance gain. To ask the question an other way since FreeBSD, Linux, Solaris, etc do not support F_FULLFSYNC is it safe to run any kind of database on them ;) FreeBSD/Linux/Solaris are most often run in less hostile environments -- cages, racks, UPS'd, etc... Mac OS X has to deal with a very hostile computing environment -- lots of sleep/wake, power loss, drives being hot plugged (often without proper unmounting), etc... But, agreed, it should be an option. b.bum
Re: [sqlite] ORDER BY / HAVING clauses and aliased field names
Jay napisaÅ(a): There is no way I know of to avoid doing this. I couldn't find one in Oracle either. You can refer to them by their number: Select a, b, c FROM t Order by 1 is the same as: Select a, b, c FROM t Order by a If only this worked in HAVING clauses ;) But i guess HAVING 1 = query_parameter would be to confusing for the engine ;/ OK - I'll probably go into declaring another alias name and/or repeating the whole expression. The whole thing is however confusing. Is that a bug? Shouldn't aliased names get some kind of prority when thay are being referred to in HAVING / ORDER BY query sections? AFAIR those two clauses are being resolved at the very end , after WHERE and JOINs handling, also all the result columns are already defined - so how come is there any ambiguity? Either way - thanks for all your suggestions, With all regards, -- Krzysztof Kotowicz
Re: [sqlite] sluggish operation on os x?
On 21-Feb-05, at 11:11 AM, [EMAIL PROTECTED] wrote: OK -- so, you are willing to accept the risk of non-recoverable database corruption in the event of power outage or other kinds of catastrophic system failure (including the plug being pulled on a FireWire drive without it being properly unmounted)? I.e. that risk is perceived to be acceptably small that the performance hit is not justifiable? The performance hit is much larger than the risk, so in some cases, no FireWire drives and there is an UPS, the risk is acceptable for the performance gain. To ask the question an other way since FreeBSD, Linux, Solaris, etc do not support F_FULLFSYNC is it safe to run any kind of database on them ;) ck
Re: [sqlite] sluggish operation on os x?
On 21-Feb-05, at 10:39 AM, [EMAIL PROTECTED] wrote: It is a trade off between guaranteed data integrity and performance. If there happen to be a bunch of other apps writing to the disk when you do a SQLite transaction, then all of that data has to be flushed to the disk. As Domnic said, fsync() does not guarantee that the bytes hit the platter on any system. Pull the plug after a COMMIT and you are very likely going to see only part of the pages written. You can also use the 'synchronous' pragma to control the number of F_FULLSYNCs executed during any single transaction. By default, it will be three-- probably too excessive. I sill want the "normal" fsync() called and using this pragma means fsync() is not called. The best way to guarantee maximal performance is to bunch up your INSERT and UPDATE statements into transactions as much as possible. This is often true regardless of the presence of F_FULLSYNC. My application does this and the performance was still very poor on OS X with F_FULLSYNC on. Since OS X is the only OS which has F_FULLFSYNC it would be nice to make the use of it a configure option. ck
Re: [sqlite] sluggish operation on os x?
On Feb 21, 2005, at 9:54 AM, James Berry wrote: On Feb 21, 2005, at 9:40 AM, Curtis King wrote: I noticed this as well, so I profiled my call and found sync was taking forever. I removed the following fcntl call, rc = fcntl(fd, F_FULLFSYNC, 0);. Performance was back to normal. Here are some comments about F_FULLFSYNC, off the darwin list just two days ago. They mention why it's there, but don't mention how slow the performance might be... It is a trade off between guaranteed data integrity and performance. If there happen to be a bunch of other apps writing to the disk when you do a SQLite transaction, then all of that data has to be flushed to the disk. As Domnic said, fsync() does not guarantee that the bytes hit the platter on any system. Pull the plug after a COMMIT and you are very likely going to see only part of the pages written. You can also use the 'synchronous' pragma to control the number of F_FULLSYNCs executed during any single transaction. By default, it will be three-- probably too excessive. The best way to guarantee maximal performance is to bunch up your INSERT and UPDATE statements into transactions as much as possible. This is often true regardless of the presence of F_FULLSYNC. Note that this situation only arises in the case of catastrophic system failure such as a power failure or kernel panic. b.bum
Re: [sqlite] sluggish operation on os x?
On Feb 21, 2005, at 9:40 AM, Curtis King wrote: I noticed this as well, so I profiled my call and found sync was taking forever. I removed the following fcntl call, rc = fcntl(fd, F_FULLFSYNC, 0);. Performance was back to normal. Here are some comments about F_FULLFSYNC, off the darwin list just two days ago. They mention why it's there, but don't mention how slow the performance might be... -jdb From: [EMAIL PROTECTED] Subject: Re: bad fsync? (A.M.) Date: February 19, 2005 5:59:21 PM PST To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] MySQL makes the following claim at: http://dev.mysql.com/doc/mysql/en/news-4-1-9.html "InnoDB: Use the fcntl() file flush method on Mac OS X versions 10.3 and up. Apple had disabled fsync() in Mac OS X for internal disk drives, which caused corruption at power outages." First of all, is this accurate? A pointer to some docs or a tech note on this would be helpful. The comments about fsync() are wrong... On MacOS X, fsync() always has and always will flush all file data from host memory to the drive on which the file resides. The behavior of fsync() on MacOS X is the same as it is on every other version of Unix since the dawn of time (well, since the introduction of fsync anyway :-). I believe that what the above comment refers to is the fact that fsync() is not sufficient to guarantee that your data is on stable storage and on MacOS X we provide a fcntl(), called F_FULLFSYNC, to ask the drive to flush all buffered data to stable storage. Let me explain in more detail. With fsync() even though the OS writes the data through to the disk and the disk says "yes I wrote the data", the data is not actually on permanent storage. Unless you explicitly disable it, all disks have a write buffer which holds data you've written. The disk buffers the data you wrote until it decides to flush it to the platters (and the writes may not be in the order you wrote them). If you lose power or the system crashes before the data is written, you can wind up in a situation where only some of your data is actually on disk. What is worse is that even if you write blocks A, B and C, call fsync() and then write block D you may find after rebooting that blocks A and D are on disk but B and C are not (in fact any ordering of A, B, C, and D is possible). While this may seem like a rare case it is not. In fact if you sit down and pull the plug on a system you can make it happen in one or two plug pulls. I have even gone so far as to watch this behavior with a logic analyzer on the ATA bus: I saw the data for two writes come across the ATA cable, the drive replied and said the writes were successful and then when we rebooted the data from the second write was correct on disk but the data from the first write was not. To deal with this we introduced the F_FULLFSYNC fcntl which will ask the drive to flush all of its buffered data to disk. When an app needs to guarantee that data is on disk it should use F_FULLFSYNC. In most cases you do not need such a heavy handed operation and fsync() is good enough. But in an app like a database, it is essential if you want transactional integrity. Now, a little bit more detail: on ATA drives we implement F_FULLFSYNC with the FLUSH_TRACK_CACHE command. All drives sold by Apple will honor this command. Unfortunately quite a few firewire drive vendors disable this command and do not pass it to the drive. This means that most external firewire drives are not reliable if you lose power or the system crashes. We can't work-around that unless we ask the drive to disable the write cache completely (which hurts performance quite badly -- and even that may not be enough as some drives will ignore that request too). So in summary, I believe that the comments in the MySQL news posting are slightly confused. On MacOS X fsync() behaves the same as it does on all Unices. That's not good enough if you really care about data integrity and so we also provide the F_FULLFSYNC fcntl. As far as I know, MacOS X is the only OS to provide this feature for apps that need to truly guarantee their data is on disk. Hope this clears things up. --dominic ___ Do not post admin requests to the list. They will be ignored. Darwin-dev mailing list ([EMAIL PROTECTED]) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/darwin-dev/james%40jberry.us This email sent to [EMAIL PROTECTED]
RE: [sqlite] Making a SEQUENCE?
Ok - yes that works. Actually my problem was that I was using one of the GUI client interfaces to SQL lite, and it doesn't display returned results from SELECT statements when using transactions. I had actually tried the transaction approach but thought nothing was getting returned. Doh... Doing the UPDATE + SELECT in C++ gives me back the next id properly :) Thanks for the help. -Dave -Original Message- From: Steve O'Hara [mailto:[EMAIL PROTECTED] Sent: Monday, February 21, 2005 3:07 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Making a SEQUENCE? Hi Dave, Wrap your update and select inside a transaction - the transaction is atomic in this regard and locks the table after the update and doesn't let it go until the commit/rollback. Others can read (they get the uncommitted values of course) but can't update. You will always get the latest uncommitted values from selects within the transaction. Steve -Original Message- From: Ulrik Petersen [mailto:[EMAIL PROTECTED] Sent: 21 February 2005 10:21 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Making a SEQUENCE? Hi Dave, Brown, Dave wrote: >I read that faq, but I dont see how that solves this yet. I understand >how the insert will autoincrement, but I need to do "increment + return >new value" in one atomic operation. If I just issue an insert, then try >to use the new value from that table, I'm not guaranteed that someone >else hasn't also incremented it further before I got to it. > >Do you see what I mean? > > Yes. If you are using SQLite 2.8.X, then use: int sqlite_last_insert_rowid(sqlite*); http://www.sqlite.org/c_interface.html Section 3.0 If you are using SQLite 3, then there is an analogous function: http://www.sqlite.org/capi3ref.html#sqlite3_last_insert_rowid /Ulrik -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark Homepage: http://ulrikp.org
Re: [sqlite] sluggish operation on os x?
I noticed this as well, so I profiled my call and found sync was taking forever. I removed the following fcntl call, rc = fcntl(fd, F_FULLFSYNC, 0);. Performance was back to normal. ck
Re: [sqlite] ORDER BY / HAVING clauses and aliased field names
--- Krzysztof Kotowicz <[EMAIL PROTECTED]> wrote: > >>WHERE 1 > >> > >> > >What is this supposed to do? It's not a boolean expression. > > > > > AFAIK it would render the same result as 1=1 (it seems that it casts > to > boolean true), at least that behaviour was observed in MySQL. > Nevermind, > 1=1 or any other expression returning true for every row is > sufficient > for this query - this is just a placeholder when no search criteria > is > specified by the user (the query is dynamically generated). Ah :) > > >Did you try: > >ORDER BY coalesce(cd1.country_name, cd2.country_name) > >in place of: > >ORDER BY country_name > > > > > Yes - it does help, thanks for suggestion, however - it would > certainly > be better if I could keep the ORDER BY / HAVING clauses > expression-free > (i.e. with only the field names). Oracle does the same thing. I'm not sure what the reason is. > > Is there any other way I could: > a) avoid using expressions in ORDER BY / HAVING clauses > b) leave the aliased field name unchanged - for compatibility (e. g. > I > have other SQL statements for fallback lang. substitution disabled > and I > simply refer to country_description.country_name field in them - so > that > I have the same field names in query results). > > I don't have any idea - maybe aliased field names share some kind of > "namespace" I could refer them by in ORDER BY clauses like > '_aliased.country_name' ? There is no way I know of to avoid doing this. I couldn't find one in Oracle either. You can refer to them by their number: Select a, b, c FROM t Order by 1 is the same as: Select a, b, c FROM t Order by a = - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail
Re: [sqlite] 2 question about SQLite types
--- Witold Czarnecki <[EMAIL PROTECTED]> wrote: > SELECT TYPEOF(ROUND(1)); > ... returns 'text'. Is it OK? Sorry, I asked this question 2 days ago > but I still don't know - is it a bug or not? yes but there's a fix checked in > 2. > sqlite> CREATE TABLE test(id INTEGER NOT NULL PRIMARY KEY UNIQUE, fld > > INTEGER); > sqlite> INSERT INTO test(fld) VALUES(1); > sqlite> INSERT INTO test(fld) VALUES(2); > sqlite> SELECT * FROM test; > 1|1 > 2|2 > sqlite> SELECT * FROM test WHERE id < '2'; > 1|1 > 2|2 > sqlite> SELECT * FROM test WHERE fld < '2'; > 1|1 > > ... why fields id and fld behave different? Both are integers. There's a problem with the conversion from text to integer I think. If you remove the conversion it works: D:\temp\convention>sqlite3 test.db SQLite version 3.0.8 Enter ".help" for instructions sqlite> CREATE TABLE test(id INTEGER NOT NULL PRIMARY KEY UNIQUE, fld ...> INTEGER); sqlite> INSERT INTO test(fld) VALUES(1); sqlite> INSERT INTO test(fld) VALUES(2); sqlite> SELECT * FROM test; 1|1 2|2 sqlite> SELECT * FROM test WHERE id < 2; 1|1 sqlite> SELECT * FROM test WHERE fld < 2; 1|1 sqlite> note there are no quotes around the 2. __ Do you Yahoo!? Yahoo! Mail - 250MB free storage. Do more. Manage less. http://info.mail.yahoo.com/mail_250
Re: [sqlite] Getting a list of most recent queries
> If you are talking about SQLite in general, I believe the answer is "only > if you save 'the last 10 executed SQL commands' you've performed. It > shouldn't be difficult to set up a table to hold those, and a wrapper > function that updates that table every time a query is run. Well, I'm using the SQLite ADO.NET provider, which has the actual calls and such nicely wrapped up. But anyways, thanks for the help. That still falls under the "SQLite in general" classification. I'm using an ADO.NET provider as well, and there's nothing native to it. You can still set up a wrapper function and a table, though. Heh, yes, of course. But it's kind of difficult to change the program all over the place to use the wrappers ;-). But anyways, the problem is already solved. I left a DataReader open! Sijmen Mulder
Re: [sqlite] Getting a list of most recent queries
> > If you are talking about SQLite in general, I believe the answer is "only > > if you save 'the last 10 executed SQL commands' you've performed. It > > shouldn't be difficult to set up a table to hold those, and a wrapper > > function that updates that table every time a query is run. > > Well, I'm using the SQLite ADO.NET provider, which has the actual calls and > such nicely wrapped up. But anyways, thanks for the help. That still falls under the "SQLite in general" classification. I'm using an ADO.NET provider as well, and there's nothing native to it. You can still set up a wrapper function and a table, though.
Re: [sqlite] Getting a list of most recent queries
Hi, If you are talking about SQLite in general, I believe the answer is "only if you save 'the last 10 executed SQL commands' you've performed. It shouldn't be difficult to set up a table to hold those, and a wrapper function that updates that table every time a query is run. Well, I'm using the SQLite ADO.NET provider, which has the actual calls and such nicely wrapped up. But anyways, thanks for the help.
Re: [sqlite] Getting a list of most recent queries
> Hi there, > > Is it possible to get a list of say, the last 10 executed SQL commands? This > would be very usefull for debugging purposes for example. The FAQ doesn't > answer the question. If you are using the command line utility in a DOS shell in Windows, the up arrow key will show you every command you've executed. I'm sure there's a limit, but I don't know what it would be. If you are talking about SQLite in general, I believe the answer is "only if you save 'the last 10 executed SQL commands' you've performed. It shouldn't be difficult to set up a table to hold those, and a wrapper function that updates that table every time a query is run.
Re: [sqlite] 3.1.3 does not compile on Fedora3
Hi all, As I wrote earlier today, I have already submitted this as a bug with the work-around (i.e. to install the needed library or to configure without the tcl) for version 3.1.2. I am not sure what the rest of you prefer - should we write to the list about bug we find, should we fill in the bug report or should we do both? Maybe it is a good idea to automatically submit the bug reports to the list? Rani On Mon, 2005-02-21 at 12:20, D. Richard Hipp wrote: > On Mon, 2005-02-21 at 06:08 -0500, D. Richard Hipp wrote: > > The file tclsqlite.c is the TCL interface to SQLite. > > You do not need to compile that file in order to build > > just the SQLite library. You do need the TCL interface > > in order to do "make test" because most of the test code > > is written in TCL. But "make test" is not necessary to > > build the library. > > > > I am mistaken. It appears I changed the Makefile for > 3.1.0 so that it compiles the TCL interface by default. > If this is not what you want, just add --disable-tcl to > ./configure. --
Re: [sqlite] 3.1.3 does not compile on Fedora3
D. Richard Hipp wrote: [...] I am mistaken. It appears I changed the Makefile for 3.1.0 so that it compiles the TCL interface by default. If this is not what you want, just add --disable-tcl to ./configure. This option didn't help in my case - only adding Tcl-devel rpm package does solve the problem. Igor Gorbounov
Re: [sqlite] 3.1.3 does not compile on Fedora3
On Mon, 2005-02-21 at 06:08 -0500, D. Richard Hipp wrote: > The file tclsqlite.c is the TCL interface to SQLite. > You do not need to compile that file in order to build > just the SQLite library. You do need the TCL interface > in order to do "make test" because most of the test code > is written in TCL. But "make test" is not necessary to > build the library. > I am mistaken. It appears I changed the Makefile for 3.1.0 so that it compiles the TCL interface by default. If this is not what you want, just add --disable-tcl to ./configure. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] 3.1.3 does not compile on Fedora3
On Mon, 2005-02-21 at 10:18 +0300, Igor Gorbounov wrote: > Hi, All! > sqlite-3.1.3.tar.gz doesn't compile on Fedora Core 3 Linux. Compiler > complains > on some Tcl stuff: > > gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG > -I/usr/include -DSQLITE_OMIT_CURSOR -c ./src/tclsqlite.c -fPIC -DPIC -o > .libs/tclsqlite.o > ./src/tclsqlite.c:20:17: tcl.h: No such file or directory The file tclsqlite.c is the TCL interface to SQLite. You do not need to compile that file in order to build just the SQLite library. You do need the TCL interface in order to do "make test" because most of the test code is written in TCL. But "make test" is not necessary to build the library. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Making a SEQUENCE?
Hi Dave, Wrap your update and select inside a transaction - the transaction is atomic in this regard and locks the table after the update and doesn't let it go until the commit/rollback. Others can read (they get the uncommitted values of course) but can't update. You will always get the latest uncommitted values from selects within the transaction. Steve -Original Message- From: Ulrik Petersen [mailto:[EMAIL PROTECTED] Sent: 21 February 2005 10:21 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Making a SEQUENCE? Hi Dave, Brown, Dave wrote: >I read that faq, but I dont see how that solves this yet. I understand how >the insert will autoincrement, but I need to do "increment + return new >value" in one atomic operation. If I just issue an insert, then try to use >the new value from that table, I'm not guaranteed that someone else hasn't >also incremented it further before I got to it. > >Do you see what I mean? > > Yes. If you are using SQLite 2.8.X, then use: int sqlite_last_insert_rowid(sqlite*); http://www.sqlite.org/c_interface.html Section 3.0 If you are using SQLite 3, then there is an analogous function: http://www.sqlite.org/capi3ref.html#sqlite3_last_insert_rowid /Ulrik -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark Homepage: http://ulrikp.org
Re: [sqlite] 3.1.3 does not compile on Fedora3
Corwin Burgess wrote: Igor Gorbounov wrote: [...] At this point I remembered that the last time I compiled sqlite3 was on Fedora Core 1. I don't remembered where I got it but I had downloaded tcl8.4.7-src.tar.gz and installed it. After I installed it on this distro, tcl.h was found and the compile was successful. Yes, this hint has helped me: I've installed tcl-devel rpm package and now the sqlite-3.1.3 package has compiled. Igor Gorbounov
[sqlite] Getting a list of most recent queries
Hi there, Is it possible to get a list of say, the last 10 executed SQL commands? This would be very usefull for debugging purposes for example. The FAQ doesn't answer the question. Thanks, Sijmen Mulder
[sqlite] 2 question about SQLite types
Hello, two questions: 1. SELECT TYPEOF(ROUND(1)); ... returns 'text'. Is it OK? Sorry, I asked this question 2 days ago but I still don't know - is it a bug or not? 2. sqlite> CREATE TABLE test(id INTEGER NOT NULL PRIMARY KEY UNIQUE, fld INTEGER); sqlite> INSERT INTO test(fld) VALUES(1); sqlite> INSERT INTO test(fld) VALUES(2); sqlite> SELECT * FROM test; 1|1 2|2 sqlite> SELECT * FROM test WHERE id < '2'; 1|1 2|2 sqlite> SELECT * FROM test WHERE fld < '2'; 1|1 ... why fields id and fld behave different? Both are integers. Best regards, Witold
Re: [sqlite] How to compile sqlite-3.1.3 on Fedora 3?
I submitted a bug report about it, where I also explained how to work around this problem. http://www.sqlite.org/cvstrac/tktview?tn=1123 Rani On Mon, 2005-02-21 at 08:30, Igor Gorbounov wrote: > Hi, All! > sqlite-3.1.3.tar.gz doesn't compile on Fedora Core 3 Linux. Compiler > complains > on some Tcl stuff: > ... > gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG > -I/usr/include -DSQLITE_OMIT_CURSOR -c ./src/tclsqlite.c -fPIC -DPIC -o > .libs/tclsqlite.o > ./src/tclsqlite.c:20:17: tcl.h: No such file or directory > ./src/tclsqlite.c:44: error: syntax error before "Tcl_Interp" > ./src/tclsqlite.c:44: warning: no semicolon at end of struct or union > ./src/tclsqlite.c:47: error: syntax error before '}' token > ./src/tclsqlite.c:55: error: syntax error before "Tcl_Interp" > ... > The 3.0.8 compiles excelent. So what's wrong here and is there any > workaround? > Igor Gorbounov > --
Re: [sqlite] 3.1.3 does not compile on Fedora3
Igor Gorbounov wrote: Hi, All! sqlite-3.1.3.tar.gz doesn't compile on Fedora Core 3 Linux. Compiler complains on some Tcl stuff: gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG -I/usr/include -DSQLITE_OMIT_CURSOR -c ./src/tclsqlite.c -fPIC -DPIC -o .libs/tclsqlite.o ./src/tclsqlite.c:20:17: tcl.h: No such file or directory ./src/tclsqlite.c:44: error: syntax error before "Tcl_Interp" ./src/tclsqlite.c:44: warning: no semicolon at end of struct or union ./src/tclsqlite.c:47: error: syntax error before '}' token ./src/tclsqlite.c:55: error: syntax error before "Tcl_Interp" The 3.0.8 compiles excelent. So what's wrong here and is there any workaround? Igor Gorbounov It also failed on White Box Linux V3.0. After I ran ../sqlite/configure ... from a bld directory I ran make as usr. It couldn't find tcl.h and continued with about 350 lines of ../sqlite/src/tclsqlite.c:... errors. At this point I remembered that the last time I compiled sqlite3 was on Fedora Core 1. I don't remembered where I got it but I had downloaded tcl8.4.7-src.tar.gz and installed it. After I installed it on this distro, tcl.h was found and the compile was successful. Corwin
RE: [sqlite] Making a SEQUENCE?
I read that faq, but I dont see how that solves this yet. I understand how the insert will autoincrement, but I need to do "increment + return new value" in one atomic operation. If I just issue an insert, then try to use the new value from that table, I'm not guaranteed that someone else hasn't also incremented it further before I got to it. Do you see what I mean? -Dave -Original Message- From: Ulrik Petersen [mailto:[EMAIL PROTECTED] Sent: Monday, February 21, 2005 12:49 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Making a SEQUENCE? Hi again, Brown, Dave wrote: >Is it possible to create the behaviour of a sequence with SQLite? > >I need to do something like this: > >[pseudocode] >var id = SELECT next_val FROM my_sequence; INSERT INTO table1 >VALUES(id, ...); INSERT INTO table2 VALUES(id, ...); > > > I forgot to say that if you want to insert the same ID into different tables, you'll have to create a single table that is an "id generator" using the method provided in the FAQ. For an example of how this could be done, see my Open Source text database project: http://emdros.org/preview/ You'll want to look in EMdF/sqliteemdfdb.cpp and EMdF/sqliteconn.cpp as well as their counterparts in include/ Cheers, Ulrik
Re: [sqlite] Making a SEQUENCE?
Hi again, Brown, Dave wrote: Is it possible to create the behaviour of a sequence with SQLite? I need to do something like this: [pseudocode] var id = SELECT next_val FROM my_sequence; INSERT INTO table1 VALUES(id, ...); INSERT INTO table2 VALUES(id, ...); I forgot to say that if you want to insert the same ID into different tables, you'll have to create a single table that is an "id generator" using the method provided in the FAQ. For an example of how this could be done, see my Open Source text database project: http://emdros.org/preview/ You'll want to look in EMdF/sqliteemdfdb.cpp and EMdF/sqliteconn.cpp as well as their counterparts in include/ Cheers, Ulrik
Re: [sqlite] Making a SEQUENCE?
Hi Dave, Brown, Dave wrote: Is it possible to create the behaviour of a sequence with SQLite? I need to do something like this: [pseudocode] var id = SELECT next_val FROM my_sequence; INSERT INTO table1 VALUES(id, ...); INSERT INTO table2 VALUES(id, ...); This should return the next value, AND also increment it so that the next call will be +1. (I'm then taking this value and using it as key for a row inserted into multiple tables). I read the docs as well as searched the mailing list archives, but didnt' find much. There was something about using triggers, but would this guarantee an atomic increment? Does anyone have a solution to this? Check the FAQ: http://www.sqlite.org/faq.html#q1
[sqlite] sluggish operation on os x?
Finally getting my SQLite3 code working, I'm experiencing awfully slow performance when writing individual data on OS X. Linux (Ubuntu) is lightning fast, Win32 is.. tolerable, but OS X really crawls. I've tried both with built-in sources, and the new SQLite3 fink module. Same behaviour. Is there something I should be aware of concerning this (is the OS X port somehow special?) or might it just be a 'bug' in the implementation? For a sample, writing 100 integers takes 30+ seconds! Here's the code I'm using, as you see, it's not only about writing the data, I also need to remove any possible 'subtables' (not going into details here?) that might be on the way. Therefore, any write is a transaction such as: "BEGIN;" "DELETE FROM '%q' WHERE key LIKE ?1;" -- ?1 = "key.%" "INSERT OR REPLACE INTO '%q' (key,val) VALUES( ?1, ?2 );" -- ?1=key (str), ?2=val (int) "COMMIT;" I do precompile the SQL in advance, that is not causing the delay. Any ideas?? -ak :)
[sqlite] Making a SEQUENCE?
Is it possible to create the behaviour of a sequence with SQLite? I need to do something like this: [pseudocode] var id = SELECT next_val FROM my_sequence; INSERT INTO table1 VALUES(id, ...); INSERT INTO table2 VALUES(id, ...); This should return the next value, AND also increment it so that the next call will be +1. (I'm then taking this value and using it as key for a row inserted into multiple tables). I read the docs as well as searched the mailing list archives, but didnt' find much. There was something about using triggers, but would this guarantee an atomic increment? Does anyone have a solution to this? Thanks, Dave
