Re: [sqlite] How do I speed up CREATE INDEX ?
Mohd Radzi Ibrahim [EMAIL PROTECTED] wrote: Hi, I was loading a file to sqlite (3.3.8), and it took 4 mins to load 6 million rows (with no index). But then when I run CREATE INDEX it took me 40 mins to do that. What could I do to speed up the indexing process ? The reason index creation slows down when creating large indices is a problem with locality of reference in your disk cache. I've learned a lot about dealing with locality while working on full-text search, and I think I can probably implement a CREATE INDEX that runs much faster for a large table. There are some plans in the works that might permit me the time to do this in the spring. But in the meantime, the only thing I can suggest is to add more RAM to your machine so that you disk cache is larger. Or get a faster disk drive. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] database permanently locked
Max Barry [EMAIL PROTECTED] wrote: My database is permanently locked, and I've spent two fruitless days trying to unlock it. The problem: $ sqlite trac.db SQLite version 3.3.6 Enter .help for instructions sqlite .databases Error: database is locked Is the database on an NFS filesystem. The locking is busted on some (many?) implementations of NFS, resulting in behavior like shown above. Your work-around is to copy the database to a local filesystem that actually supports posix advisory locks. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Dealing with dates in the format yyyymmdd
When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it will be displayed in Excel like mm/dd/ if that would be a possible date. This is due to the US date format of Excel. So, would it be possible in SQLite to make a date format like this: dd/mmm/ so that would be 03/dec/2006 This would prevent Excel from putting the month first. or alternatively make it the Excel integer date format so the above date would be: 39054 I could handle the date formatting in VBA, but I would like to do as much as possible in SQLite as it will be faster and it would keep the code neater. Thanks for any advice. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Dealing with dates in the format yyyymmdd
Forgot to say that I could stick a single quote in front of the dates in SQLite and that would prevent the US date format, but it means I have no dates anymore in the sheet, but strings, so I can't sort properly and I can't do date calculations. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 14:21 To: sqlite-users@sqlite.org Subject: [sqlite] Dealing with dates in the format mmdd When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it will be displayed in Excel like mm/dd/ if that would be a possible date. This is due to the US date format of Excel. So, would it be possible in SQLite to make a date format like this: dd/mmm/ so that would be 03/dec/2006 This would prevent Excel from putting the month first. or alternatively make it the Excel integer date format so the above date would be: 39054 I could handle the date formatting in VBA, but I would like to do as much as possible in SQLite as it will be faster and it would keep the code neater. Thanks for any advice. RBS - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Dealing with dates in the format yyyymmdd
Is there a reason you can use Excel's Format Cells to accomplish what you wish? Enter a Custom format of \mm\dd in a cell and enter =today() as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:21 AM To: sqlite-users@sqlite.org Subject: [sqlite] Dealing with dates in the format mmdd When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it will be displayed in Excel like mm/dd/ if that would be a possible date. This is due to the US date format of Excel. So, would it be possible in SQLite to make a date format like this: dd/mmm/ so that would be 03/dec/2006 This would prevent Excel from putting the month first. or alternatively make it the Excel integer date format so the above date would be: 39054 I could handle the date formatting in VBA, but I would like to do as much as possible in SQLite as it will be faster and it would keep the code neater. Thanks for any advice. RBS -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How do I speed up CREATE INDEX ?
Thanks for the suggestion. I'm a bit lost now. I've tried to load 80million rows now. It took 40 minutes to load into non-index tables; but creating index now take almost forever. It's already 12 hrs, not yet complete. regards, Radzi. - Original Message - From: [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Sunday, December 03, 2006 8:21 PM Subject: Re: [sqlite] How do I speed up CREATE INDEX ? Mohd Radzi Ibrahim [EMAIL PROTECTED] wrote: Hi, I was loading a file to sqlite (3.3.8), and it took 4 mins to load 6 million rows (with no index). But then when I run CREATE INDEX it took me 40 mins to do that. What could I do to speed up the indexing process ? The reason index creation slows down when creating large indices is a problem with locality of reference in your disk cache. I've learned a lot about dealing with locality while working on full-text search, and I think I can probably implement a CREATE INDEX that runs much faster for a large table. There are some plans in the works that might permit me the time to do this in the spring. But in the meantime, the only thing I can suggest is to add more RAM to your machine so that you disk cache is larger. Or get a faster disk drive. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Dealing with dates in the format yyyymmdd
I am familiar with Excel and I have tried that but it doesn't work. I find the only reliable way to put dates in Excel is to put the integer Excel date in like for example 39054 and then set the date format in the sheet. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 14:41 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is there a reason you can use Excel's Format Cells to accomplish what you wish? Enter a Custom format of \mm\dd in a cell and enter =today() as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:21 AM To: sqlite-users@sqlite.org Subject: [sqlite] Dealing with dates in the format mmdd When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it will be displayed in Excel like mm/dd/ if that would be a possible date. This is due to the US date format of Excel. So, would it be possible in SQLite to make a date format like this: dd/mmm/ so that would be 03/dec/2006 This would prevent Excel from putting the month first. or alternatively make it the Excel integer date format so the above date would be: 39054 I could handle the date formatting in VBA, but I would like to do as much as possible in SQLite as it will be faster and it would keep the code neater. Thanks for any advice. RBS -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Dealing with dates in the format yyyymmdd
I think it is easier to do this in VBA and as the main work is done in a VBA array it is quite fast as well. Not as elegant maybe as doing it in SQLite, but it will do. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 14:41 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is there a reason you can use Excel's Format Cells to accomplish what you wish? Enter a Custom format of \mm\dd in a cell and enter =today() as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:21 AM To: sqlite-users@sqlite.org Subject: [sqlite] Dealing with dates in the format mmdd When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it will be displayed in Excel like mm/dd/ if that would be a possible date. This is due to the US date format of Excel. So, would it be possible in SQLite to make a date format like this: dd/mmm/ so that would be 03/dec/2006 This would prevent Excel from putting the month first. or alternatively make it the Excel integer date format so the above date would be: 39054 I could handle the date formatting in VBA, but I would like to do as much as possible in SQLite as it will be faster and it would keep the code neater. Thanks for any advice. RBS -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: REPLACE INTO Help Once Again
Cnichols [EMAIL PROTECTED] wrote:
REPLACE INTO (AccountId, MemberId, SessionId, State)
SELECT (M.Id || '-' || S.Id), M.Id, S.Id, 4
From Sessions AS S, Members AS M
LEFT JOIN AccountState AS AST ON S.Id = AST.SessionId
WHERE S.OverdueHandled = 0
AND S.Grace DATE('NOW')
--AND AST.State != 1
Right off the bat, I don't see Members table being joined to anything.
Do you really mean to use a full cartesian product?
Based on the nature of how REPLACE INTO works I created the AccountID
field which is just unique text so a replace can occur.
You don't need an artificial single field for that. You can declare a
composite key or index, e.g.
create table AccountState (
MemberId, SessionId, State,
PRIMARY KEY (MemberId, SessionId)
)
To save on diskspace I only keep the alert only when the session has
been paid or becomes dilequent so for the other X days there is no
need to keep neutral status in the alert table.
So a record of an account will not be there unless paid or dilequent
(which includes due, grace, overdue, cumalative) so I need to insert
a record if its not there but I don't want to replace and paid
accounts, but i can overwrite all the other dilquent with a high
predecesor
This narrative assumes that the reader knows a) banking terminology; b)
the exact way it is represented in your data model. You are much likely
to get help if you restate all these conditions in terms of your tables
and fields.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Dealing with dates in the format yyyymmdd
Just thought of one reason why it I need something else in SQLite than mmdd in the date field and that is because I need date comparisons between different tables. So, I need to do: is fieldA + x days fieldB? etc. This will be difficult with the mmdd format. I could of course update all the date fields in a VBA loop, but that might be a bit slow. So, if anybody has an idea how to convert integer mmdd to the Excel date format in SQLite I would be interested. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 15:37 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd I think it is easier to do this in VBA and as the main work is done in a VBA array it is quite fast as well. Not as elegant maybe as doing it in SQLite, but it will do. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 14:41 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is there a reason you can use Excel's Format Cells to accomplish what you wish? Enter a Custom format of \mm\dd in a cell and enter =today() as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:21 AM To: sqlite-users@sqlite.org Subject: [sqlite] Dealing with dates in the format mmdd When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it will be displayed in Excel like mm/dd/ if that would be a possible date. This is due to the US date format of Excel. So, would it be possible in SQLite to make a date format like this: dd/mmm/ so that would be 03/dec/2006 This would prevent Excel from putting the month first. or alternatively make it the Excel integer date format so the above date would be: 39054 I could handle the date formatting in VBA, but I would like to do as much as possible in SQLite as it will be faster and it would keep the code neater. Thanks for any advice. RBS -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
Why not use the internal Sqlite date format. Then date processing is straight forward and you can use the conversion routines to present it in the various national and ISO formats. Sqlite uses a very correct date storage format based on an epoch way back in antiquity so that you can present it in any national format. Using the internal format date comparison is just a numeric compare, which is efficient. If you need any special date presentation you can add a custome function to Sqlite to achieve it from your SQL statement. RB Smissaert wrote: Just thought of one reason why it I need something else in SQLite than mmdd in the date field and that is because I need date comparisons between different tables. So, I need to do: is fieldA + x days fieldB? etc. This will be difficult with the mmdd format. I could of course update all the date fields in a VBA loop, but that might be a bit slow. So, if anybody has an idea how to convert integer mmdd to the Excel date format in SQLite I would be interested. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 15:37 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd I think it is easier to do this in VBA and as the main work is done in a VBA array it is quite fast as well. Not as elegant maybe as doing it in SQLite, but it will do. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 14:41 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is there a reason you can use Excel's Format Cells to accomplish what you wish? Enter a Custom format of \mm\dd in a cell and enter =today() as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:21 AM To: sqlite-users@sqlite.org Subject: [sqlite] Dealing with dates in the format mmdd When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it will be displayed in Excel like mm/dd/ if that would be a possible date. This is due to the US date format of Excel. So, would it be possible in SQLite to make a date format like this: dd/mmm/ so that would be 03/dec/2006 This would prevent Excel from putting the month first. or alternatively make it the Excel integer date format so the above date would be: 39054 I could handle the date formatting in VBA, but I would like to do as much as possible in SQLite as it will be faster and it would keep the code neater. Thanks for any advice. RBS -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Dealing with dates in the format yyyymmdd
Still don't understand the issue. Excel stores the date internally as the integer as you describe, no matter how you chose to format the cell's display. Changing the display format does not change the cell's internal format. And to the best of my knowledge changing Excel's internal cell storage structure is not possible. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:56 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd I am familiar with Excel and I have tried that but it doesn't work. I find the only reliable way to put dates in Excel is to put the integer Excel date in like for example 39054 and then set the date format in the sheet. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 14:41 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is there a reason you can use Excel's Format Cells to accomplish what you wish? Enter a Custom format of \mm\dd in a cell and enter =today() as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:21 AM To: sqlite-users@sqlite.org Subject: [sqlite] Dealing with dates in the format mmdd When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it will be displayed in Excel like mm/dd/ if that would be a possible date. This is due to the US date format of Excel. So, would it be possible in SQLite to make a date format like this: dd/mmm/ so that would be 03/dec/2006 This would prevent Excel from putting the month first. or alternatively make it the Excel integer date format so the above date would be: 39054 I could handle the date formatting in VBA, but I would like to do as much as possible in SQLite as it will be faster and it would keep the code neater. Thanks for any advice. RBS -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- -- -- - To unsubscribe, send email to [EMAIL PROTECTED] -- -- - -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Dealing with dates in the format yyyymmdd
Trouble is I need to import dates from Interbase that have the integer format mmdd. How would I convert that (at import) to the SQLite date format? I do the import via an ADO recordset and then move the data via SQLite inserts. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 17:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use the internal Sqlite date format. Then date processing is straight forward and you can use the conversion routines to present it in the various national and ISO formats. Sqlite uses a very correct date storage format based on an epoch way back in antiquity so that you can present it in any national format. Using the internal format date comparison is just a numeric compare, which is efficient. If you need any special date presentation you can add a custome function to Sqlite to achieve it from your SQL statement. RB Smissaert wrote: Just thought of one reason why it I need something else in SQLite than mmdd in the date field and that is because I need date comparisons between different tables. So, I need to do: is fieldA + x days fieldB? etc. This will be difficult with the mmdd format. I could of course update all the date fields in a VBA loop, but that might be a bit slow. So, if anybody has an idea how to convert integer mmdd to the Excel date format in SQLite I would be interested. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 15:37 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd I think it is easier to do this in VBA and as the main work is done in a VBA array it is quite fast as well. Not as elegant maybe as doing it in SQLite, but it will do. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 14:41 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is there a reason you can use Excel's Format Cells to accomplish what you wish? Enter a Custom format of \mm\dd in a cell and enter =today() as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:21 AM To: sqlite-users@sqlite.org Subject: [sqlite] Dealing with dates in the format mmdd When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it will be displayed in Excel like mm/dd/ if that would be a possible date. This is due to the US date format of Excel. So, would it be possible in SQLite to make a date format like this: dd/mmm/ so that would be 03/dec/2006 This would prevent Excel from putting the month first. or alternatively make it the Excel integer date format so the above date would be: 39054 I could handle the date formatting in VBA, but I would like to do as much as possible in SQLite as it will be faster and it would keep the code neater. Thanks for any advice. RBS -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Dealing with dates in the format yyyymmdd
I have an integer, but that is mmdd and has nil to do with the Excel integer date format. Did you try putting a value in the integer format mmdd in Excel and have it as a normal Excel date? Or did you try it with a string in the format dd/mm/? Come to think of it, maybe I should make it mm/dd/ with the SQLite function substr. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 17:11 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Still don't understand the issue. Excel stores the date internally as the integer as you describe, no matter how you chose to format the cell's display. Changing the display format does not change the cell's internal format. And to the best of my knowledge changing Excel's internal cell storage structure is not possible. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:56 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd I am familiar with Excel and I have tried that but it doesn't work. I find the only reliable way to put dates in Excel is to put the integer Excel date in like for example 39054 and then set the date format in the sheet. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 14:41 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is there a reason you can use Excel's Format Cells to accomplish what you wish? Enter a Custom format of \mm\dd in a cell and enter =today() as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:21 AM To: sqlite-users@sqlite.org Subject: [sqlite] Dealing with dates in the format mmdd When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it will be displayed in Excel like mm/dd/ if that would be a possible date. This is due to the US date format of Excel. So, would it be possible in SQLite to make a date format like this: dd/mmm/ so that would be 03/dec/2006 This would prevent Excel from putting the month first. or alternatively make it the Excel integer date format so the above date would be: 39054 I could handle the date formatting in VBA, but I would like to do as much as possible in SQLite as it will be faster and it would keep the code neater. Thanks for any advice. RBS -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- -- -- - To unsubscribe, send email to [EMAIL PROTECTED] -- -- - -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Is sqlite, a java wrapper and java for a small app a good solution?
Hello, i plan to code a quiet small application with a small dbms for data storage. So i looked around for a proper programming language and an appropriate dbms. My first thoughts went around microsoft acces and vb/vba. But i remebered a project a few years ago when i had to enhance a small programm written in vb and used ms access. I was very unhappy with this and so searched for alternatives to vb and ms access. I read through the mailinglist and now i'm not sure anymore if it's a good idea to use sqlite and java for this project i'm working on. The interface to sqlite is a javawrapper i found on the sqlite website URL: http://www.zentus.com/sqlitejdbc/ A few words about the requirements of my application i'm working on: an app with about 5 oder 10 tables, filled with up to 1000 rows of data. It's planned as a singleuser GUI application and i don't think it's becomming very large, maybe 2 lines of code or somthing like this. What's your opinion about my descision for java, sqlitejdbc and sqlite? Thanks for your support! kind regards Christian - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Dealing with dates in the format yyyymmdd
Come to think of it, maybe I should make it mm/dd/ with the SQLite function substr(). That seems to work fine. So, when I have 20061203, meaning 3 December 2006 then if I make it 12/03/2006 with substr() then Excel picks it up as the right date and I also can apply any date format. Still have the problem though how to compare dates in SQLite when the format is the integer mmdd. Maybe I will need some custom SQLite function. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 17:22 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd I have an integer, but that is mmdd and has nil to do with the Excel integer date format. Did you try putting a value in the integer format mmdd in Excel and have it as a normal Excel date? Or did you try it with a string in the format dd/mm/? Come to think of it, maybe I should make it mm/dd/ with the SQLite function substr. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 17:11 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Still don't understand the issue. Excel stores the date internally as the integer as you describe, no matter how you chose to format the cell's display. Changing the display format does not change the cell's internal format. And to the best of my knowledge changing Excel's internal cell storage structure is not possible. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:56 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd I am familiar with Excel and I have tried that but it doesn't work. I find the only reliable way to put dates in Excel is to put the integer Excel date in like for example 39054 and then set the date format in the sheet. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 14:41 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is there a reason you can use Excel's Format Cells to accomplish what you wish? Enter a Custom format of \mm\dd in a cell and enter =today() as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:21 AM To: sqlite-users@sqlite.org Subject: [sqlite] Dealing with dates in the format mmdd When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it will be displayed in Excel like mm/dd/ if that would be a possible date. This is due to the US date format of Excel. So, would it be possible in SQLite to make a date format like this: dd/mmm/ so that would be 03/dec/2006 This would prevent Excel from putting the month first. or alternatively make it the Excel integer date format so the above date would be: 39054 I could handle the date formatting in VBA, but I would like to do as much as possible in SQLite as it will be faster and it would keep the code neater. Thanks for any advice. RBS -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- -- -- - To unsubscribe, send email to [EMAIL PROTECTED] -- -- - -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
What do you mean by integer format yyymmdd? Is it an integer based on an epoch or is it a number like 20061203 stored in a 32 bit word? We use Sqlite format dates and times and thereby get good SQL capability and have a small library of date function which interface into Unix and Windows date and time retrieval APIs. A couple of simple Sqlite custom functions to do some date manipulation made the SQL simpler. Custom date functions are very easy to implement using Sqlite because you can use the Sqlite date routines and style as a basis. The Sqlite epoch based date format is elegant because it permits you to present date and time not only according to any time zone but to comply with more than just the Gregorian calendar - with Middle Eastern and Oriental ones should the need arise. RB Smissaert wrote: Trouble is I need to import dates from Interbase that have the integer format mmdd. How would I convert that (at import) to the SQLite date format? I do the import via an ADO recordset and then move the data via SQLite inserts. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 17:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use the internal Sqlite date format. Then date processing is straight forward and you can use the conversion routines to present it in the various national and ISO formats. Sqlite uses a very correct date storage format based on an epoch way back in antiquity so that you can present it in any national format. Using the internal format date comparison is just a numeric compare, which is efficient. If you need any special date presentation you can add a custome function to Sqlite to achieve it from your SQL statement. RB Smissaert wrote: Just thought of one reason why it I need something else in SQLite than mmdd in the date field and that is because I need date comparisons between different tables. So, I need to do: is fieldA + x days fieldB? etc. This will be difficult with the mmdd format. I could of course update all the date fields in a VBA loop, but that might be a bit slow. So, if anybody has an idea how to convert integer mmdd to the Excel date format in SQLite I would be interested. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 15:37 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd I think it is easier to do this in VBA and as the main work is done in a VBA array it is quite fast as well. Not as elegant maybe as doing it in SQLite, but it will do. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 14:41 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is there a reason you can use Excel's Format Cells to accomplish what you wish? Enter a Custom format of \mm\dd in a cell and enter =today() as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:21 AM To: sqlite-users@sqlite.org Subject: [sqlite] Dealing with dates in the format mmdd When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it will be displayed in Excel like mm/dd/ if that would be a possible date. This is due to the US date format of Excel. So, would it be possible in SQLite to make a date format like this: dd/mmm/ so that would be 03/dec/2006 This would prevent Excel from putting the month first. or alternatively make it the Excel integer date format so the above date would be: 39054 I could handle the date formatting in VBA, but I would like to do as much as possible in SQLite as it will be faster and it would keep the code neater. Thanks for any advice. RBS -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email
Re: [sqlite] 2PC / two-phase commit?
SQLite uses a 2-phase commit internally when it is making changes to two or more ATTACHed database files. Are there any plans to expose this at the API level? This could be useful for implementing reliable data exchange with other database systems. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Dealing with dates in the format yyyymmdd
What do you mean by integer format yyymmdd? This is an Interbase database and the Create statement of such a field would be like this: CREATE TABLE ENTRY(ADDED_DATE INTEGER, etc. Not sure what an epoch is, not sure how else to describe it. So, do you reckon I could, given the fact that I have an integer number in SQLite like 20061203, convert this in SQLite to another integer number that would give the number of days since 31 December 1899? Or do you think that I could make accurate dates comparisons in SQLite with 20061203 etc.? I suppose the trouble will be with weeks and days, years and months would be fine. So, for example it won't be that simple to say if 20061203 is more than 10 weeks past 20060920, whereas it will be easy to calculate that it is less than 3 months passed that date. Is there such a thing as the VB/VBA DateSerial in SQLite? This means given the year, the month number and day number you can make a date? RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 18:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd What do you mean by integer format yyymmdd? Is it an integer based on an epoch or is it a number like 20061203 stored in a 32 bit word? We use Sqlite format dates and times and thereby get good SQL capability and have a small library of date function which interface into Unix and Windows date and time retrieval APIs. A couple of simple Sqlite custom functions to do some date manipulation made the SQL simpler. Custom date functions are very easy to implement using Sqlite because you can use the Sqlite date routines and style as a basis. The Sqlite epoch based date format is elegant because it permits you to present date and time not only according to any time zone but to comply with more than just the Gregorian calendar - with Middle Eastern and Oriental ones should the need arise. RB Smissaert wrote: Trouble is I need to import dates from Interbase that have the integer format mmdd. How would I convert that (at import) to the SQLite date format? I do the import via an ADO recordset and then move the data via SQLite inserts. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 17:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use the internal Sqlite date format. Then date processing is straight forward and you can use the conversion routines to present it in the various national and ISO formats. Sqlite uses a very correct date storage format based on an epoch way back in antiquity so that you can present it in any national format. Using the internal format date comparison is just a numeric compare, which is efficient. If you need any special date presentation you can add a custome function to Sqlite to achieve it from your SQL statement. RB Smissaert wrote: Just thought of one reason why it I need something else in SQLite than mmdd in the date field and that is because I need date comparisons between different tables. So, I need to do: is fieldA + x days fieldB? etc. This will be difficult with the mmdd format. I could of course update all the date fields in a VBA loop, but that might be a bit slow. So, if anybody has an idea how to convert integer mmdd to the Excel date format in SQLite I would be interested. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 15:37 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd I think it is easier to do this in VBA and as the main work is done in a VBA array it is quite fast as well. Not as elegant maybe as doing it in SQLite, but it will do. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 14:41 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is there a reason you can use Excel's Format Cells to accomplish what you wish? Enter a Custom format of \mm\dd in a cell and enter =today() as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:21 AM To: sqlite-users@sqlite.org Subject: [sqlite] Dealing with dates in the format mmdd When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it will be displayed in Excel like mm/dd/ if that would be a possible date. This is due to the US date format of Excel. So, would it be possible in SQLite
Re: [sqlite] REPLACE INTO Help Once Again
I was not aware of composite keys so I quickly learned what they where and I
have decided to contemplate my database design. I searched for performance
issues with using composite keys and I was not able to find much information
related to SQLite.
Igor Tandetnik wrote:
Cnichols [EMAIL PROTECTED] wrote:
REPLACE INTO (AccountId, MemberId, SessionId, State)
SELECT (M.Id || '-' || S.Id), M.Id, S.Id, 4
From Sessions AS S, Members AS M
LEFT JOIN AccountState AS AST ON S.Id = AST.SessionId
WHERE S.OverdueHandled = 0
AND S.Grace DATE('NOW')
--AND AST.State != 1
Right off the bat, I don't see Members table being joined to anything.
Do you really mean to use a full cartesian product?
Based on the nature of how REPLACE INTO works I created the AccountID
field which is just unique text so a replace can occur.
You don't need an artificial single field for that. You can declare a
composite key or index, e.g.
create table AccountState (
MemberId, SessionId, State,
PRIMARY KEY (MemberId, SessionId)
)
To save on diskspace I only keep the alert only when the session has
been paid or becomes dilequent so for the other X days there is no
need to keep neutral status in the alert table.
So a record of an account will not be there unless paid or dilequent
(which includes due, grace, overdue, cumalative) so I need to insert
a record if its not there but I don't want to replace and paid
accounts, but i can overwrite all the other dilquent with a high
predecesor
This narrative assumes that the reader knows a) banking terminology; b)
the exact way it is represented in your data model. You are much likely
to get help if you restate all these conditions in terms of your tables
and fields.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
View this message in context:
http://www.nabble.com/REPLACE-INTO-Help-Once-Again-tf2745497.html#a7666128
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Dealing with dates in the format yyyymmdd
It looks like Interbase uses a traditional date format based on an epoch. That is a system where a base date is set (the epoch) and the date is stored as an offset from that date. It is stored as an integer. Sqlite uses a similar system, except that it uses a special epoch compatible with other calendars and incorporates the time, storing all of it in a 64 bit floating point format. You should be able to translate the date into a yymmdd format of some description from Interbase and use that to insert into Sqlite. The ISO 8601 date and time format is a widely used standard. RB Smissaert wrote: What do you mean by integer format yyymmdd? This is an Interbase database and the Create statement of such a field would be like this: CREATE TABLE ENTRY(ADDED_DATE INTEGER, etc. Not sure what an epoch is, not sure how else to describe it. So, do you reckon I could, given the fact that I have an integer number in SQLite like 20061203, convert this in SQLite to another integer number that would give the number of days since 31 December 1899? Or do you think that I could make accurate dates comparisons in SQLite with 20061203 etc.? I suppose the trouble will be with weeks and days, years and months would be fine. So, for example it won't be that simple to say if 20061203 is more than 10 weeks past 20060920, whereas it will be easy to calculate that it is less than 3 months passed that date. Is there such a thing as the VB/VBA DateSerial in SQLite? This means given the year, the month number and day number you can make a date? RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 18:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd What do you mean by integer format yyymmdd? Is it an integer based on an epoch or is it a number like 20061203 stored in a 32 bit word? We use Sqlite format dates and times and thereby get good SQL capability and have a small library of date function which interface into Unix and Windows date and time retrieval APIs. A couple of simple Sqlite custom functions to do some date manipulation made the SQL simpler. Custom date functions are very easy to implement using Sqlite because you can use the Sqlite date routines and style as a basis. The Sqlite epoch based date format is elegant because it permits you to present date and time not only according to any time zone but to comply with more than just the Gregorian calendar - with Middle Eastern and Oriental ones should the need arise. RB Smissaert wrote: Trouble is I need to import dates from Interbase that have the integer format mmdd. How would I convert that (at import) to the SQLite date format? I do the import via an ADO recordset and then move the data via SQLite inserts. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 17:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use the internal Sqlite date format. Then date processing is straight forward and you can use the conversion routines to present it in the various national and ISO formats. Sqlite uses a very correct date storage format based on an epoch way back in antiquity so that you can present it in any national format. Using the internal format date comparison is just a numeric compare, which is efficient. If you need any special date presentation you can add a custome function to Sqlite to achieve it from your SQL statement. RB Smissaert wrote: Just thought of one reason why it I need something else in SQLite than mmdd in the date field and that is because I need date comparisons between different tables. So, I need to do: is fieldA + x days fieldB? etc. This will be difficult with the mmdd format. I could of course update all the date fields in a VBA loop, but that might be a bit slow. So, if anybody has an idea how to convert integer mmdd to the Excel date format in SQLite I would be interested. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 15:37 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd I think it is easier to do this in VBA and as the main work is done in a VBA array it is quite fast as well. Not as elegant maybe as doing it in SQLite, but it will do. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 14:41 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is there a reason you can use Excel's Format Cells to accomplish what you wish? Enter a Custom format of \mm\dd in a cell and enter =today() as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred -Original Message- From: RB Smissaert [mailto
RE: [sqlite] Dealing with dates in the format yyyymmdd
Worked out a function to convert integer mmdd to an Excel date: Function IBDates2ExcelDates(lIBDate As Long) As Long Dim lYear As Long Dim lMonth As Long Dim lDay As Long Dim a As Long Dim y As Long Dim m As Long Dim lJulianDay As Long lYear = lIBDate \ 1 lMonth = (lIBDate - lYear * 1) \ 100 lDay = lIBDate - (lYear * 1 + lMonth * 100) a = (14 - lMonth) / 12 y = lYear + 4800 - a m = lMonth + 12 * a - 3 lJulianDay = lDay + (153 * m + 2) / 5 + y * 365 + y / 4 - 32083 IBDates2ExcelDates = lJulianDay - 2415033 End Function Seems to work well. I know there is a bug in the Excel date where somewhere shortly after 30 December 1899 it is one day out, but that is OK. And I suppose this is something I can do in SQLite? If so should I just put this in the query statement or can I somehow make a UDF? RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 18:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd What do you mean by integer format yyymmdd? Is it an integer based on an epoch or is it a number like 20061203 stored in a 32 bit word? We use Sqlite format dates and times and thereby get good SQL capability and have a small library of date function which interface into Unix and Windows date and time retrieval APIs. A couple of simple Sqlite custom functions to do some date manipulation made the SQL simpler. Custom date functions are very easy to implement using Sqlite because you can use the Sqlite date routines and style as a basis. The Sqlite epoch based date format is elegant because it permits you to present date and time not only according to any time zone but to comply with more than just the Gregorian calendar - with Middle Eastern and Oriental ones should the need arise. RB Smissaert wrote: Trouble is I need to import dates from Interbase that have the integer format mmdd. How would I convert that (at import) to the SQLite date format? I do the import via an ADO recordset and then move the data via SQLite inserts. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 17:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use the internal Sqlite date format. Then date processing is straight forward and you can use the conversion routines to present it in the various national and ISO formats. Sqlite uses a very correct date storage format based on an epoch way back in antiquity so that you can present it in any national format. Using the internal format date comparison is just a numeric compare, which is efficient. If you need any special date presentation you can add a custome function to Sqlite to achieve it from your SQL statement. RB Smissaert wrote: Just thought of one reason why it I need something else in SQLite than mmdd in the date field and that is because I need date comparisons between different tables. So, I need to do: is fieldA + x days fieldB? etc. This will be difficult with the mmdd format. I could of course update all the date fields in a VBA loop, but that might be a bit slow. So, if anybody has an idea how to convert integer mmdd to the Excel date format in SQLite I would be interested. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 15:37 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd I think it is easier to do this in VBA and as the main work is done in a VBA array it is quite fast as well. Not as elegant maybe as doing it in SQLite, but it will do. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 14:41 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is there a reason you can use Excel's Format Cells to accomplish what you wish? Enter a Custom format of \mm\dd in a cell and enter =today() as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:21 AM To: sqlite-users@sqlite.org Subject: [sqlite] Dealing with dates in the format mmdd When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it will be displayed in Excel like mm/dd/ if that would be a possible date. This is due to the US date format of Excel. So, would it be possible in SQLite to make a date format like this: dd/mmm/ so that would be 03/dec
RE: [sqlite] Dealing with dates in the format yyyymmdd
It looks like Interbase uses a traditional date format based on an epoch. It just does year * 1 + month * 100 + day I suppose I could something like this in SQL: ((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) * 1) \ 100) * 100)) + _ (153 * (((lIBDate - (lIBDate \ 1) * 1) \ 100) + _ 12 * ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12) - 3) + 2) / 5 + _ ((lIBDate \ 1) + 4800 - ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _ 365 + ((lIBDate \ 1) + 4800 - _ ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) / 4 - 32083) - _ 2415033 Except it looks a mess and it is one day out, I take it due to not declaring variables as long as in my previously posted function. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:03 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd It looks like Interbase uses a traditional date format based on an epoch. That is a system where a base date is set (the epoch) and the date is stored as an offset from that date. It is stored as an integer. Sqlite uses a similar system, except that it uses a special epoch compatible with other calendars and incorporates the time, storing all of it in a 64 bit floating point format. You should be able to translate the date into a yymmdd format of some description from Interbase and use that to insert into Sqlite. The ISO 8601 date and time format is a widely used standard. RB Smissaert wrote: What do you mean by integer format yyymmdd? This is an Interbase database and the Create statement of such a field would be like this: CREATE TABLE ENTRY(ADDED_DATE INTEGER, etc. Not sure what an epoch is, not sure how else to describe it. So, do you reckon I could, given the fact that I have an integer number in SQLite like 20061203, convert this in SQLite to another integer number that would give the number of days since 31 December 1899? Or do you think that I could make accurate dates comparisons in SQLite with 20061203 etc.? I suppose the trouble will be with weeks and days, years and months would be fine. So, for example it won't be that simple to say if 20061203 is more than 10 weeks past 20060920, whereas it will be easy to calculate that it is less than 3 months passed that date. Is there such a thing as the VB/VBA DateSerial in SQLite? This means given the year, the month number and day number you can make a date? RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 18:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd What do you mean by integer format yyymmdd? Is it an integer based on an epoch or is it a number like 20061203 stored in a 32 bit word? We use Sqlite format dates and times and thereby get good SQL capability and have a small library of date function which interface into Unix and Windows date and time retrieval APIs. A couple of simple Sqlite custom functions to do some date manipulation made the SQL simpler. Custom date functions are very easy to implement using Sqlite because you can use the Sqlite date routines and style as a basis. The Sqlite epoch based date format is elegant because it permits you to present date and time not only according to any time zone but to comply with more than just the Gregorian calendar - with Middle Eastern and Oriental ones should the need arise. RB Smissaert wrote: Trouble is I need to import dates from Interbase that have the integer format mmdd. How would I convert that (at import) to the SQLite date format? I do the import via an ADO recordset and then move the data via SQLite inserts. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 17:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use the internal Sqlite date format. Then date processing is straight forward and you can use the conversion routines to present it in the various national and ISO formats. Sqlite uses a very correct date storage format based on an epoch way back in antiquity so that you can present it in any national format. Using the internal format date comparison is just a numeric compare, which is efficient. If you need any special date presentation you can add a custome function to Sqlite to achieve it from your SQL statement. RB Smissaert wrote: Just thought of one reason why it I need something else in SQLite than mmdd in the date field and that is because I need date comparisons between different tables. So, I need to do: is fieldA + x days
Re: [sqlite] Dealing with dates in the format yyyymmdd
Why not use Interbase to transform the date into a character string, like an ISI8601 date or yymmdd, dd-mm- etc and then use the Sqlite date function to insert it into Sqlite? RB Smissaert wrote: It looks like Interbase uses a traditional date format based on an epoch. It just does year * 1 + month * 100 + day I suppose I could something like this in SQL: ((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) * 1) \ 100) * 100)) + _ (153 * (((lIBDate - (lIBDate \ 1) * 1) \ 100) + _ 12 * ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12) - 3) + 2) / 5 + _ ((lIBDate \ 1) + 4800 - ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _ 365 + ((lIBDate \ 1) + 4800 - _ ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) / 4 - 32083) - _ 2415033 Except it looks a mess and it is one day out, I take it due to not declaring variables as long as in my previously posted function. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:03 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd It looks like Interbase uses a traditional date format based on an epoch. That is a system where a base date is set (the epoch) and the date is stored as an offset from that date. It is stored as an integer. Sqlite uses a similar system, except that it uses a special epoch compatible with other calendars and incorporates the time, storing all of it in a 64 bit floating point format. You should be able to translate the date into a yymmdd format of some description from Interbase and use that to insert into Sqlite. The ISO 8601 date and time format is a widely used standard. RB Smissaert wrote: What do you mean by integer format yyymmdd? This is an Interbase database and the Create statement of such a field would be like this: CREATE TABLE ENTRY(ADDED_DATE INTEGER, etc. Not sure what an epoch is, not sure how else to describe it. So, do you reckon I could, given the fact that I have an integer number in SQLite like 20061203, convert this in SQLite to another integer number that would give the number of days since 31 December 1899? Or do you think that I could make accurate dates comparisons in SQLite with 20061203 etc.? I suppose the trouble will be with weeks and days, years and months would be fine. So, for example it won't be that simple to say if 20061203 is more than 10 weeks past 20060920, whereas it will be easy to calculate that it is less than 3 months passed that date. Is there such a thing as the VB/VBA DateSerial in SQLite? This means given the year, the month number and day number you can make a date? RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 18:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd What do you mean by integer format yyymmdd? Is it an integer based on an epoch or is it a number like 20061203 stored in a 32 bit word? We use Sqlite format dates and times and thereby get good SQL capability and have a small library of date function which interface into Unix and Windows date and time retrieval APIs. A couple of simple Sqlite custom functions to do some date manipulation made the SQL simpler. Custom date functions are very easy to implement using Sqlite because you can use the Sqlite date routines and style as a basis. The Sqlite epoch based date format is elegant because it permits you to present date and time not only according to any time zone but to comply with more than just the Gregorian calendar - with Middle Eastern and Oriental ones should the need arise. RB Smissaert wrote: Trouble is I need to import dates from Interbase that have the integer format mmdd. How would I convert that (at import) to the SQLite date format? I do the import via an ADO recordset and then move the data via SQLite inserts. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 17:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use the internal Sqlite date format. Then date processing is straight forward and you can use the conversion routines to present it in the various national and ISO formats. Sqlite uses a very correct date storage format based on an epoch way back in antiquity so that you can present it in any national format. Using the internal format date comparison is just a numeric compare, which is efficient. If you need any special date presentation you can add a custome function to Sqlite to achieve it from your SQL statement. RB Smissaert wrote: Just
RE: [sqlite] Dealing with dates in the format yyyymmdd
Is the date function as in: Function Overview Five date and time functions are available, as follows: 1. date( timestring, modifier, modifier, ...) ? Will give that a go. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:46 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use Interbase to transform the date into a character string, like an ISI8601 date or yymmdd, dd-mm- etc and then use the Sqlite date function to insert it into Sqlite? RB Smissaert wrote: It looks like Interbase uses a traditional date format based on an epoch. It just does year * 1 + month * 100 + day I suppose I could something like this in SQL: ((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) * 1) \ 100) * 100)) + _ (153 * (((lIBDate - (lIBDate \ 1) * 1) \ 100) + _ 12 * ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12) - 3) + 2) / 5 + _ ((lIBDate \ 1) + 4800 - ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _ 365 + ((lIBDate \ 1) + 4800 - _ ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) / 4 - 32083) - _ 2415033 Except it looks a mess and it is one day out, I take it due to not declaring variables as long as in my previously posted function. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:03 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd It looks like Interbase uses a traditional date format based on an epoch. That is a system where a base date is set (the epoch) and the date is stored as an offset from that date. It is stored as an integer. Sqlite uses a similar system, except that it uses a special epoch compatible with other calendars and incorporates the time, storing all of it in a 64 bit floating point format. You should be able to translate the date into a yymmdd format of some description from Interbase and use that to insert into Sqlite. The ISO 8601 date and time format is a widely used standard. RB Smissaert wrote: What do you mean by integer format yyymmdd? This is an Interbase database and the Create statement of such a field would be like this: CREATE TABLE ENTRY(ADDED_DATE INTEGER, etc. Not sure what an epoch is, not sure how else to describe it. So, do you reckon I could, given the fact that I have an integer number in SQLite like 20061203, convert this in SQLite to another integer number that would give the number of days since 31 December 1899? Or do you think that I could make accurate dates comparisons in SQLite with 20061203 etc.? I suppose the trouble will be with weeks and days, years and months would be fine. So, for example it won't be that simple to say if 20061203 is more than 10 weeks past 20060920, whereas it will be easy to calculate that it is less than 3 months passed that date. Is there such a thing as the VB/VBA DateSerial in SQLite? This means given the year, the month number and day number you can make a date? RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 18:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd What do you mean by integer format yyymmdd? Is it an integer based on an epoch or is it a number like 20061203 stored in a 32 bit word? We use Sqlite format dates and times and thereby get good SQL capability and have a small library of date function which interface into Unix and Windows date and time retrieval APIs. A couple of simple Sqlite custom functions to do some date manipulation made the SQL simpler. Custom date functions are very easy to implement using Sqlite because you can use the Sqlite date routines and style as a basis. The Sqlite epoch based date format is elegant because it permits you to present date and time not only according to any time zone but to comply with more than just the Gregorian calendar - with Middle Eastern and Oriental ones should the need arise. RB Smissaert wrote: Trouble is I need to import dates from Interbase that have the integer format mmdd. How would I convert that (at import) to the SQLite date format? I do the import via an ADO recordset and then move the data via SQLite inserts. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 17:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use the internal Sqlite date format. Then date processing is straight forward and you can use the conversion routines to present it in the various
RE: [sqlite] Dealing with dates in the format yyyymmdd
If I try to convert the integer Interbase date with standard SQL I get something like this: CAST(CAST((E.ADDED_DATE - (CAST(E.ADDED_DATE / 1 AS INT) * 1)) - _ CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS INT) AS INT) || '/' ||CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) / 100 AS INT) || '/' ||CAST(E.ADDED_DATE / 1 AS INT) AS VARCHAR(10)) But I haven't got it working yet. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 21:05 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is the date function as in: Function Overview Five date and time functions are available, as follows: 1. date( timestring, modifier, modifier, ...) ? Will give that a go. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:46 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use Interbase to transform the date into a character string, like an ISI8601 date or yymmdd, dd-mm- etc and then use the Sqlite date function to insert it into Sqlite? RB Smissaert wrote: It looks like Interbase uses a traditional date format based on an epoch. It just does year * 1 + month * 100 + day I suppose I could something like this in SQL: ((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) * 1) \ 100) * 100)) + _ (153 * (((lIBDate - (lIBDate \ 1) * 1) \ 100) + _ 12 * ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12) - 3) + 2) / 5 + _ ((lIBDate \ 1) + 4800 - ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _ 365 + ((lIBDate \ 1) + 4800 - _ ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) / 4 - 32083) - _ 2415033 Except it looks a mess and it is one day out, I take it due to not declaring variables as long as in my previously posted function. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:03 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd It looks like Interbase uses a traditional date format based on an epoch. That is a system where a base date is set (the epoch) and the date is stored as an offset from that date. It is stored as an integer. Sqlite uses a similar system, except that it uses a special epoch compatible with other calendars and incorporates the time, storing all of it in a 64 bit floating point format. You should be able to translate the date into a yymmdd format of some description from Interbase and use that to insert into Sqlite. The ISO 8601 date and time format is a widely used standard. RB Smissaert wrote: What do you mean by integer format yyymmdd? This is an Interbase database and the Create statement of such a field would be like this: CREATE TABLE ENTRY(ADDED_DATE INTEGER, etc. Not sure what an epoch is, not sure how else to describe it. So, do you reckon I could, given the fact that I have an integer number in SQLite like 20061203, convert this in SQLite to another integer number that would give the number of days since 31 December 1899? Or do you think that I could make accurate dates comparisons in SQLite with 20061203 etc.? I suppose the trouble will be with weeks and days, years and months would be fine. So, for example it won't be that simple to say if 20061203 is more than 10 weeks past 20060920, whereas it will be easy to calculate that it is less than 3 months passed that date. Is there such a thing as the VB/VBA DateSerial in SQLite? This means given the year, the month number and day number you can make a date? RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 18:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd What do you mean by integer format yyymmdd? Is it an integer based on an epoch or is it a number like 20061203 stored in a 32 bit word? We use Sqlite format dates and times and thereby get good SQL capability and have a small library of date function which interface into Unix and Windows date and time retrieval APIs. A couple of simple Sqlite custom functions to do some date manipulation made the SQL simpler. Custom date functions are very easy to implement using Sqlite because you can use the Sqlite date routines and style as a basis. The Sqlite epoch based date format is elegant because it permits you to present date and time not only according to any time zone but to comply with more than just the Gregorian calendar - with Middle Eastern and Oriental ones
Re: [sqlite] database permanently locked
Christian Smith wrote: Max Barry uttered: My database is permanently locked, and I've spent two fruitless days trying to unlock it. You haven't said what sort of box this is. I guess a generic Unix. If Linux, you'll probably have fuser installed. Run fuser against the database file, and it'll tell you the PID of any process that has the file open. I'm not sure if other Unix have fuser installed by default. Sorry, yes: it's a Linux box with a 2.4.32 kernel. If the file is owned by a trac processs, you can kill it using fuser -k, assuming you have permission. Unfortunately fuser doesn't show any process holding a lock, and I've already tried killing every process I have permission to. I suspect that fuser might not show me the answer because I'm non-root. Certainly ps, top, etc, only show processes I own. I've asked the box owner to run 'fuser' for me and see if that shows something. drh wrote: Is the database on an NFS filesystem. The locking is busted on some (many?) implementations of NFS, resulting in behavior like shown above. Yep, it's NFS. The line from /etc/fstab is: 10.3.100.108:/vol/boot/spunky/maitland /home/.maitland nfs defaults,intr,vers=3,bg,rsize=8192,wsize=8192 0 0 Your work-around is to copy the database to a local filesystem that actually supports posix advisory locks. Ah. Except I don't think I can do that, because I don't have root on this box. I discovered I could make a copy of the trac.db file and edit my trac.ini file to make point to that instead (database = sqlite:db/trac2.db). Ta da, no more lock! So at least my site is usable again. But it sounds like this will happen again with my current configuration. Is there anything I can do as non-root, or do I need to just be prepared for lots of db copying? Thanks very much to drh and Christian for the fast replies! Max. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
I know nothing of Interbase, but cannot imagine that it does not have a set of date manipulation functions. RB Smissaert wrote: If I try to convert the integer Interbase date with standard SQL I get something like this: CAST(CAST((E.ADDED_DATE - (CAST(E.ADDED_DATE / 1 AS INT) * 1)) - _ CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS INT) AS INT) || '/' ||CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) / 100 AS INT) || '/' ||CAST(E.ADDED_DATE / 1 AS INT) AS VARCHAR(10)) But I haven't got it working yet. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 21:05 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is the date function as in: Function Overview Five date and time functions are available, as follows: 1. date( timestring, modifier, modifier, ...) ? Will give that a go. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:46 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use Interbase to transform the date into a character string, like an ISI8601 date or yymmdd, dd-mm- etc and then use the Sqlite date function to insert it into Sqlite? RB Smissaert wrote: It looks like Interbase uses a traditional date format based on an epoch. It just does year * 1 + month * 100 + day I suppose I could something like this in SQL: ((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) * 1) \ 100) * 100)) + _ (153 * (((lIBDate - (lIBDate \ 1) * 1) \ 100) + _ 12 * ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12) - 3) + 2) / 5 + _ ((lIBDate \ 1) + 4800 - ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _ 365 + ((lIBDate \ 1) + 4800 - _ ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) / 4 - 32083) - _ 2415033 Except it looks a mess and it is one day out, I take it due to not declaring variables as long as in my previously posted function. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:03 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd It looks like Interbase uses a traditional date format based on an epoch. That is a system where a base date is set (the epoch) and the date is stored as an offset from that date. It is stored as an integer. Sqlite uses a similar system, except that it uses a special epoch compatible with other calendars and incorporates the time, storing all of it in a 64 bit floating point format. You should be able to translate the date into a yymmdd format of some description from Interbase and use that to insert into Sqlite. The ISO 8601 date and time format is a widely used standard. RB Smissaert wrote: What do you mean by integer format yyymmdd? This is an Interbase database and the Create statement of such a field would be like this: CREATE TABLE ENTRY(ADDED_DATE INTEGER, etc. Not sure what an epoch is, not sure how else to describe it. So, do you reckon I could, given the fact that I have an integer number in SQLite like 20061203, convert this in SQLite to another integer number that would give the number of days since 31 December 1899? Or do you think that I could make accurate dates comparisons in SQLite with 20061203 etc.? I suppose the trouble will be with weeks and days, years and months would be fine. So, for example it won't be that simple to say if 20061203 is more than 10 weeks past 20060920, whereas it will be easy to calculate that it is less than 3 months passed that date. Is there such a thing as the VB/VBA DateSerial in SQLite? This means given the year, the month number and day number you can make a date? RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 18:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd What do you mean by integer format yyymmdd? Is it an integer based on an epoch or is it a number like 20061203 stored in a 32 bit word? We use Sqlite format dates and times and thereby get good SQL capability and have a small library of date function which interface into Unix and Windows date and time retrieval APIs. A couple of simple Sqlite custom functions to do some date manipulation made the SQL simpler. Custom date functions are very easy to implement using Sqlite because you can use the Sqlite date routines and style as a basis. The Sqlite epoch based date format is elegant because it permits you to present date and time not only according to any
Re: [sqlite] database permanently locked
Have you tried using lsof to report what is holding your file open? Can you copy the file? Max Barry wrote: Christian Smith wrote: Max Barry uttered: My database is permanently locked, and I've spent two fruitless days trying to unlock it. You haven't said what sort of box this is. I guess a generic Unix. If Linux, you'll probably have fuser installed. Run fuser against the database file, and it'll tell you the PID of any process that has the file open. I'm not sure if other Unix have fuser installed by default. Sorry, yes: it's a Linux box with a 2.4.32 kernel. If the file is owned by a trac processs, you can kill it using fuser -k, assuming you have permission. Unfortunately fuser doesn't show any process holding a lock, and I've already tried killing every process I have permission to. I suspect that fuser might not show me the answer because I'm non-root. Certainly ps, top, etc, only show processes I own. I've asked the box owner to run 'fuser' for me and see if that shows something. drh wrote: Is the database on an NFS filesystem. The locking is busted on some (many?) implementations of NFS, resulting in behavior like shown above. Yep, it's NFS. The line from /etc/fstab is: 10.3.100.108:/vol/boot/spunky/maitland /home/.maitland nfs defaults,intr,vers=3,bg,rsize=8192,wsize=8192 0 0 Your work-around is to copy the database to a local filesystem that actually supports posix advisory locks. Ah. Except I don't think I can do that, because I don't have root on this box. I discovered I could make a copy of the trac.db file and edit my trac.ini file to make point to that instead (database = sqlite:db/trac2.db). Ta da, no more lock! So at least my site is usable again. But it sounds like this will happen again with my current configuration. Is there anything I can do as non-root, or do I need to just be prepared for lots of db copying? Thanks very much to drh and Christian for the fast replies! Max. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Dealing with dates in the format yyyymmdd
Don't know that much about it myself, but there are no functions for this that I know of. There are third party UDF's though and that is probably the best way forward. We are still on Interbase 5.6 and I think the latest is 7.5, so that might explain something. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 23:30 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd I know nothing of Interbase, but cannot imagine that it does not have a set of date manipulation functions. RB Smissaert wrote: If I try to convert the integer Interbase date with standard SQL I get something like this: CAST(CAST((E.ADDED_DATE - (CAST(E.ADDED_DATE / 1 AS INT) * 1)) - _ CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS INT) AS INT) || '/' ||CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) / 100 AS INT) || '/' ||CAST(E.ADDED_DATE / 1 AS INT) AS VARCHAR(10)) But I haven't got it working yet. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 21:05 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is the date function as in: Function Overview Five date and time functions are available, as follows: 1. date( timestring, modifier, modifier, ...) ? Will give that a go. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:46 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use Interbase to transform the date into a character string, like an ISI8601 date or yymmdd, dd-mm- etc and then use the Sqlite date function to insert it into Sqlite? RB Smissaert wrote: It looks like Interbase uses a traditional date format based on an epoch. It just does year * 1 + month * 100 + day I suppose I could something like this in SQL: ((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) * 1) \ 100) * 100)) + _ (153 * (((lIBDate - (lIBDate \ 1) * 1) \ 100) + _ 12 * ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12) - 3) + 2) / 5 + _ ((lIBDate \ 1) + 4800 - ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _ 365 + ((lIBDate \ 1) + 4800 - _ ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) / 4 - 32083) - _ 2415033 Except it looks a mess and it is one day out, I take it due to not declaring variables as long as in my previously posted function. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:03 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd It looks like Interbase uses a traditional date format based on an epoch. That is a system where a base date is set (the epoch) and the date is stored as an offset from that date. It is stored as an integer. Sqlite uses a similar system, except that it uses a special epoch compatible with other calendars and incorporates the time, storing all of it in a 64 bit floating point format. You should be able to translate the date into a yymmdd format of some description from Interbase and use that to insert into Sqlite. The ISO 8601 date and time format is a widely used standard. RB Smissaert wrote: What do you mean by integer format yyymmdd? This is an Interbase database and the Create statement of such a field would be like this: CREATE TABLE ENTRY(ADDED_DATE INTEGER, etc. Not sure what an epoch is, not sure how else to describe it. So, do you reckon I could, given the fact that I have an integer number in SQLite like 20061203, convert this in SQLite to another integer number that would give the number of days since 31 December 1899? Or do you think that I could make accurate dates comparisons in SQLite with 20061203 etc.? I suppose the trouble will be with weeks and days, years and months would be fine. So, for example it won't be that simple to say if 20061203 is more than 10 weeks past 20060920, whereas it will be easy to calculate that it is less than 3 months passed that date. Is there such a thing as the VB/VBA DateSerial in SQLite? This means given the year, the month number and day number you can make a date? RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 18:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd What do you mean by integer format yyymmdd? Is it an integer based on an epoch or is it a number like 20061203 stored in a 32 bit word? We use Sqlite format dates and times
Re: [sqlite] Dealing with dates in the format yyyymmdd
I suspect that you are not using the Interbase date type, which is a 64 bit object encapsulating date and time and having an epoch November 17, 1898. You seem to have some private packed format. Interbase has a rich set of date and time handling functions built in, provided you use the date type. You have two options in your application. The first is to just do with Sqlite what you did with interbase and have your own private date format. The second is to transform your dates into the Sqlite date format. It depends upon your application and reuse of legacy code. RB Smissaert wrote: Don't know that much about it myself, but there are no functions for this that I know of. There are third party UDF's though and that is probably the best way forward. We are still on Interbase 5.6 and I think the latest is 7.5, so that might explain something. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 23:30 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd I know nothing of Interbase, but cannot imagine that it does not have a set of date manipulation functions. RB Smissaert wrote: If I try to convert the integer Interbase date with standard SQL I get something like this: CAST(CAST((E.ADDED_DATE - (CAST(E.ADDED_DATE / 1 AS INT) * 1)) - _ CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS INT) AS INT) || '/' ||CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) / 100 AS INT) || '/' ||CAST(E.ADDED_DATE / 1 AS INT) AS VARCHAR(10)) But I haven't got it working yet. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 21:05 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is the date function as in: Function Overview Five date and time functions are available, as follows: 1. date( timestring, modifier, modifier, ...) ? Will give that a go. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:46 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use Interbase to transform the date into a character string, like an ISI8601 date or yymmdd, dd-mm- etc and then use the Sqlite date function to insert it into Sqlite? RB Smissaert wrote: It looks like Interbase uses a traditional date format based on an epoch. It just does year * 1 + month * 100 + day I suppose I could something like this in SQL: ((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) * 1) \ 100) * 100)) + _ (153 * (((lIBDate - (lIBDate \ 1) * 1) \ 100) + _ 12 * ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12) - 3) + 2) / 5 + _ ((lIBDate \ 1) + 4800 - ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _ 365 + ((lIBDate \ 1) + 4800 - _ ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) / 4 - 32083) - _ 2415033 Except it looks a mess and it is one day out, I take it due to not declaring variables as long as in my previously posted function. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:03 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd It looks like Interbase uses a traditional date format based on an epoch. That is a system where a base date is set (the epoch) and the date is stored as an offset from that date. It is stored as an integer. Sqlite uses a similar system, except that it uses a special epoch compatible with other calendars and incorporates the time, storing all of it in a 64 bit floating point format. You should be able to translate the date into a yymmdd format of some description from Interbase and use that to insert into Sqlite. The ISO 8601 date and time format is a widely used standard. RB Smissaert wrote: What do you mean by integer format yyymmdd? This is an Interbase database and the Create statement of such a field would be like this: CREATE TABLE ENTRY(ADDED_DATE INTEGER, etc. Not sure what an epoch is, not sure how else to describe it. So, do you reckon I could, given the fact that I have an integer number in SQLite like 20061203, convert this in SQLite to another integer number that would give the number of days since 31 December 1899? Or do you think that I could make accurate dates comparisons in SQLite with 20061203 etc.? I suppose the trouble will be with weeks and days, years and months would be fine. So, for example it won't be that simple to say if 20061203 is more than 10 weeks past 20060920, whereas it will be easy
RE: [sqlite] Dealing with dates in the format yyyymmdd
It has been a long time since I worked with Interbase, but I don't remember the format you describe as being an Interbase date format. Is this some kind of cobbled up date field? I seem to remember that Interbase supported pretty much standard date management routines, including date formatting for various uses. I don't have Interbase installed anywhere right now or I would look into this issue a little closer. I do know that I did both data imports and exports to and from Interbase with little or no problems other than the normal self inflicted wounds. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 3:40 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd If I try to convert the integer Interbase date with standard SQL I get something like this: CAST(CAST((E.ADDED_DATE - (CAST(E.ADDED_DATE / 1 AS INT) * 1)) - _ CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS INT) AS INT) || '/' ||CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) / 100 AS INT) || '/' ||CAST(E.ADDED_DATE / 1 AS INT) AS VARCHAR(10)) But I haven't got it working yet. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 21:05 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is the date function as in: Function Overview Five date and time functions are available, as follows: 1. date( timestring, modifier, modifier, ...) ? Will give that a go. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:46 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use Interbase to transform the date into a character string, like an ISI8601 date or yymmdd, dd-mm- etc and then use the Sqlite date function to insert it into Sqlite? RB Smissaert wrote: It looks like Interbase uses a traditional date format based on an epoch. It just does year * 1 + month * 100 + day I suppose I could something like this in SQL: ((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) * 1) \ 100) * 100)) + _ (153 * (((lIBDate - (lIBDate \ 1) * 1) \ 100) + _ 12 * ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12) - 3) + 2) / 5 + _ ((lIBDate \ 1) + 4800 - ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _ 365 + ((lIBDate \ 1) + 4800 - _ ((14 - ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) / 4 - 32083) - _ 2415033 Except it looks a mess and it is one day out, I take it due to not declaring variables as long as in my previously posted function. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:03 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd It looks like Interbase uses a traditional date format based on an epoch. That is a system where a base date is set (the epoch) and the date is stored as an offset from that date. It is stored as an integer. Sqlite uses a similar system, except that it uses a special epoch compatible with other calendars and incorporates the time, storing all of it in a 64 bit floating point format. You should be able to translate the date into a yymmdd format of some description from Interbase and use that to insert into Sqlite. The ISO 8601 date and time format is a widely used standard. RB Smissaert wrote: What do you mean by integer format yyymmdd? This is an Interbase database and the Create statement of such a field would be like this: CREATE TABLE ENTRY(ADDED_DATE INTEGER, etc. Not sure what an epoch is, not sure how else to describe it. So, do you reckon I could, given the fact that I have an integer number in SQLite like 20061203, convert this in SQLite to another integer number that would give the number of days since 31 December 1899? Or do you think that I could make accurate dates comparisons in SQLite with 20061203 etc.? I suppose the trouble will be with weeks and days, years and months would be fine. So, for example it won't be that simple to say if 20061203 is more than 10 weeks past 20060920, whereas it will be easy to calculate that it is less than 3 months passed that date. Is there such a thing as the VB/VBA DateSerial in SQLite? This means given the year, the month number and day number you can make a date? RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03
RE: [sqlite] Dealing with dates in the format yyyymmdd
by integer format yyymmdd? This is an Interbase database and the Create statement of such a field would be like this: CREATE TABLE ENTRY(ADDED_DATE INTEGER, etc. Not sure what an epoch is, not sure how else to describe it. So, do you reckon I could, given the fact that I have an integer number in SQLite like 20061203, convert this in SQLite to another integer number that would give the number of days since 31 December 1899? Or do you think that I could make accurate dates comparisons in SQLite with 20061203 etc.? I suppose the trouble will be with weeks and days, years and months would be fine. So, for example it won't be that simple to say if 20061203 is more than 10 weeks past 20060920, whereas it will be easy to calculate that it is less than 3 months passed that date. Is there such a thing as the VB/VBA DateSerial in SQLite? This means given the year, the month number and day number you can make a date? RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 18:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd What do you mean by integer format yyymmdd? Is it an integer based on an epoch or is it a number like 20061203 stored in a 32 bit word? We use Sqlite format dates and times and thereby get good SQL capability and have a small library of date function which interface into Unix and Windows date and time retrieval APIs. A couple of simple Sqlite custom functions to do some date manipulation made the SQL simpler. Custom date functions are very easy to implement using Sqlite because you can use the Sqlite date routines and style as a basis. The Sqlite epoch based date format is elegant because it permits you to present date and time not only according to any time zone but to comply with more than just the Gregorian calendar - with Middle Eastern and Oriental ones should the need arise. RB Smissaert wrote: Trouble is I need to import dates from Interbase that have the integer format mmdd. How would I convert that (at import) to the SQLite date format? I do the import via an ADO recordset and then move the data via SQLite inserts. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 17:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use the internal Sqlite date format. Then date processing is straight forward and you can use the conversion routines to present it in the various national and ISO formats. Sqlite uses a very correct date storage format based on an epoch way back in antiquity so that you can present it in any national format. Using the internal format date comparison is just a numeric compare, which is efficient. If you need any special date presentation you can add a custome function to Sqlite to achieve it from your SQL statement. RB Smissaert wrote: Just thought of one reason why it I need something else in SQLite than mmdd in the date field and that is because I need date comparisons between different tables. So, I need to do: is fieldA + x days fieldB? etc. This will be difficult with the mmdd format. I could of course update all the date fields in a VBA loop, but that might be a bit slow. So, if anybody has an idea how to convert integer mmdd to the Excel date format in SQLite I would be interested. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 15:37 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd I think it is easier to do this in VBA and as the main work is done in a VBA array it is quite fast as well. Not as elegant maybe as doing it in SQLite, but it will do. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 14:41 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is there a reason you can use Excel's Format Cells to accomplish what you wish? Enter a Custom format of \mm\dd in a cell and enter =today() as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:21 AM To: sqlite-users@sqlite.org Subject: [sqlite] Dealing with dates in the format mmdd When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem is it will be displayed in Excel like mm/dd/ if that would be a possible date. This is due to the US date format of Excel. So, would it be possible
Re: [sqlite] How do I speed up CREATE INDEX ?
Radzi, are the ids of the Transaction table ordered when inserted? I have discovered that it is very bad for performance of huge tables, if the rows are inserted with random ids. If you use an integer id (primary key )for such a table, SQLite uses the ROWID column to store the integer primary key. SQLite will put the records physically in the order you insert them but logically in ROWID order. Suppose you insert the following data: id data 9 -- disk 1 6 -- disk 2 8 -- disk 3 1 -- disk 3 5 -- disk 5 2 -- disk 6 7 -- disk 7 4 -- disk 8 3 -- disk 9 The recorders are on disk in order 'disk 1' .. 'disk 9'. But SQLite accesses the in id order. If the table is huge, then the head of your hard disk jumps around like crazy. When you create an index, SQLite uses the id order to access your entries. This takes for ever. If you can order the data on id before you insert should dramatically speed up the indexing. If this is not possible, don't make the id column primary key, but create an index for id instead. I wonder how this would change the performance of your application Michael Thanks for the suggestion. I'm a bit lost now. I've tried to load 80million rows now. It took 40 minutes to load into non-index tables; but creating index now take almost forever. It's already 12 hrs, not yet complete. regards, Radzi. - Original Message - From: [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Sunday, December 03, 2006 8:21 PM Subject: Re: [sqlite] How do I speed up CREATE INDEX ? Mohd Radzi Ibrahim [EMAIL PROTECTED] wrote: Hi, I was loading a file to sqlite (3.3.8), and it took 4 mins to load 6 million rows (with no index). But then when I run CREATE INDEX it took me 40 mins to do that. What could I do to speed up the indexing process ? The reason index creation slows down when creating large indices is a problem with locality of reference in your disk cache. I've learned a lot about dealing with locality while working on full-text search, and I think I can probably implement a CREATE INDEX that runs much faster for a large table. There are some plans in the works that might permit me the time to do this in the spring. But in the meantime, the only thing I can suggest is to add more RAM to your machine so that you disk cache is larger. Or get a faster disk drive. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
is a widely used standard. RB Smissaert wrote: What do you mean by integer format yyymmdd? This is an Interbase database and the Create statement of such a field would be like this: CREATE TABLE ENTRY(ADDED_DATE INTEGER, etc. Not sure what an epoch is, not sure how else to describe it. So, do you reckon I could, given the fact that I have an integer number in SQLite like 20061203, convert this in SQLite to another integer number that would give the number of days since 31 December 1899? Or do you think that I could make accurate dates comparisons in SQLite with 20061203 etc.? I suppose the trouble will be with weeks and days, years and months would be fine. So, for example it won't be that simple to say if 20061203 is more than 10 weeks past 20060920, whereas it will be easy to calculate that it is less than 3 months passed that date. Is there such a thing as the VB/VBA DateSerial in SQLite? This means given the year, the month number and day number you can make a date? RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 18:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd What do you mean by integer format yyymmdd? Is it an integer based on an epoch or is it a number like 20061203 stored in a 32 bit word? We use Sqlite format dates and times and thereby get good SQL capability and have a small library of date function which interface into Unix and Windows date and time retrieval APIs. A couple of simple Sqlite custom functions to do some date manipulation made the SQL simpler. Custom date functions are very easy to implement using Sqlite because you can use the Sqlite date routines and style as a basis. The Sqlite epoch based date format is elegant because it permits you to present date and time not only according to any time zone but to comply with more than just the Gregorian calendar - with Middle Eastern and Oriental ones should the need arise. RB Smissaert wrote: Trouble is I need to import dates from Interbase that have the integer format mmdd. How would I convert that (at import) to the SQLite date format? I do the import via an ADO recordset and then move the data via SQLite inserts. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 17:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use the internal Sqlite date format. Then date processing is straight forward and you can use the conversion routines to present it in the various national and ISO formats. Sqlite uses a very correct date storage format based on an epoch way back in antiquity so that you can present it in any national format. Using the internal format date comparison is just a numeric compare, which is efficient. If you need any special date presentation you can add a custome function to Sqlite to achieve it from your SQL statement. RB Smissaert wrote: Just thought of one reason why it I need something else in SQLite than mmdd in the date field and that is because I need date comparisons between different tables. So, I need to do: is fieldA + x days fieldB? etc. This will be difficult with the mmdd format. I could of course update all the date fields in a VBA loop, but that might be a bit slow. So, if anybody has an idea how to convert integer mmdd to the Excel date format in SQLite I would be interested. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 15:37 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd I think it is easier to do this in VBA and as the main work is done in a VBA array it is quite fast as well. Not as elegant maybe as doing it in SQLite, but it will do. RBS -Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 14:41 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is there a reason you can use Excel's Format Cells to accomplish what you wish? Enter a Custom format of \mm\dd in a cell and enter =today() as a value in that cell. Have not fooled with Excel much lately, but I think you can even format a spreadsheet programmatically. Fred -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Sunday, December 03, 2006 8:21 AM To: sqlite-users@sqlite.org Subject: [sqlite] Dealing with dates in the format mmdd When moving data from Interbase to SQLite I have to convert integer dates in the format mmdd to Excel dates. These are integer numbers counting the days past 31 December 1899. With substr I can make it dd/mm/ (I am in the UK and that is the normal way to format dates) but the problem
[sqlite] Batching functions
Suppose I want to add a user-defined function that may perform remote
lookups. E.g., a function that maps user names, e-mail addresses, or
what have you to internal ID forms (SIDs, POSIX UIDs, GUIDs, etc...) by
asking a remote server to perform this mapping.
Now suppose I wanted to do something like:
INTERT INTO FOO SELECT lookup(a) FROM BAR;
This should cause SQLite to call the user-defined function as many times
as there are rows in BAR.
Which would result is as many round-trips to the remote server, which
would be very slow.
Preferably one could batch up many calls to this function so that fewer
round-trips to the server should be needed.
I can't see a way to do this now, so I'm wondering if such a facility
could be added to SQLite.
[I expect some will answer don't make user-defined functions that block
on I/O. Yes, I agree. But I have relational data some of which can
come from remote servers and which can't easily be pre-populated into
tables on a local DB; it'd be ever so much more comfortable to have one
way of handling such data regardless of its source than to have to write
code that batches such lookups and the writes multiple INSERT INTO
statements to store the results. A reaonable way of dealing with
timeouts is needed, yes.]
The number of calls to batch could be specified like so:
INTERT INTO FOO SELECT lookup(a) BATCH 10 FROM BAR;
or
INTERT INTO FOO SELECT batch(lookup(a), 10) FROM BAR;
I can see several ways to design the interface for defining batch-eable
user functions. The simplest perhaps would be to overload the existing
sqlite3_create_function() as follows: batcheable functions would consist
of xFunc and xStep functions, but no xFinal function. SQLite would call
xFunc N times to pass arguments to the function for N calls to be
batched, then it would call xStep N times to retrieve the results of the
N calls; the xStep function would execute the N batched calls when it is
first called after a call to the xFunc.
I'm guessing the complexity here would be in the compiler, in making it
deal with suspended state, so that something like this
INTERT INTO FOO SELECT a, lookup(a) BATCH 10 FROM BAR;
uses a temporary table to store the 'a' column of every 10 records from
BAR, and a termporary table to store the results of the corresponding
batched function calls for 'lookup(a)', followed by a join of the two
tables to produce N rows of the form {a, lookup(a)}.
Nico
--
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Is sqlite, a java wrapper and java for a small app a good solution?
Christian Steinherr [EMAIL PROTECTED] wrote: A few words about the requirements of my application i'm working on: an app with about 5 oder 10 tables, filled with up to 1000 rows of data. It's planned as a singleuser GUI application and i don't think it's becomming very large, maybe 2 lines of code or somthing like this. Java and SQLite will do the job, but whatever language you pick, you will spend very little of the time working with SQLite and most of it working on the GUI. Download Netbeans or Eclipse and play with Java and see if it is to your tastes. d - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Batching functions
How about running a daemon on your machine which gets the request from
your user defined function, does the lookup with a persistent connection
and asynchronously updates the row in the DB? It does not need to be a
daemon, it could be a thread in your program if that is more apprpriate.
When it has a few requests answered it can deliver them in one transaction.
If your async process is fed by a FIFO buffer then netwrok slowness and
glitches will not stop your main process. You can optimize traffic by
not sending partially packets, by accumulating requests until you have a
full packet. There is little value in grouping more than that, but
having a persistent connection is valuable.
Nicolas Williams wrote:
Suppose I want to add a user-defined function that may perform remote
lookups. E.g., a function that maps user names, e-mail addresses, or
what have you to internal ID forms (SIDs, POSIX UIDs, GUIDs, etc...) by
asking a remote server to perform this mapping.
Now suppose I wanted to do something like:
INTERT INTO FOO SELECT lookup(a) FROM BAR;
This should cause SQLite to call the user-defined function as many times
as there are rows in BAR.
Which would result is as many round-trips to the remote server, which
would be very slow.
Preferably one could batch up many calls to this function so that fewer
round-trips to the server should be needed.
I can't see a way to do this now, so I'm wondering if such a facility
could be added to SQLite.
[I expect some will answer don't make user-defined functions that block
on I/O. Yes, I agree. But I have relational data some of which can
come from remote servers and which can't easily be pre-populated into
tables on a local DB; it'd be ever so much more comfortable to have one
way of handling such data regardless of its source than to have to write
code that batches such lookups and the writes multiple INSERT INTO
statements to store the results. A reaonable way of dealing with
timeouts is needed, yes.]
The number of calls to batch could be specified like so:
INTERT INTO FOO SELECT lookup(a) BATCH 10 FROM BAR;
or
INTERT INTO FOO SELECT batch(lookup(a), 10) FROM BAR;
I can see several ways to design the interface for defining batch-eable
user functions. The simplest perhaps would be to overload the existing
sqlite3_create_function() as follows: batcheable functions would consist
of xFunc and xStep functions, but no xFinal function. SQLite would call
xFunc N times to pass arguments to the function for N calls to be
batched, then it would call xStep N times to retrieve the results of the
N calls; the xStep function would execute the N batched calls when it is
first called after a call to the xFunc.
I'm guessing the complexity here would be in the compiler, in making it
deal with suspended state, so that something like this
INTERT INTO FOO SELECT a, lookup(a) BATCH 10 FROM BAR;
uses a temporary table to store the 'a' column of every 10 records from
BAR, and a termporary table to store the results of the corresponding
batched function calls for 'lookup(a)', followed by a join of the two
tables to produce N rows of the form {a, lookup(a)}.
Nico
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
