How to search for dates (published or entered)?

[chaley]

Originally Posted by theducks

SQL lite allows 2 dates within one field? I was looking for a second Date capable field

Nope. Both are in the books table. One is named timestamp (and is a timestamp), and the other is named pubdate.

[theducks]

Originally Posted by chaley

Nope. Both are in the books table. One is named timestamp (and is a timestamp), and the other is named pubdate.

I am looking at the file /opt/calibre/resources/metadata_sqlite.sql and that field is not there
I assumed this was the file used to create the original (empty) DB. Is there a altertable somewhere else? (I am NOT a SQL expert)

[Starson17]

Originally Posted by theducks

I am looking at the file /opt/calibre/resources/metadata_sqlite.sql and that field is not there
I assumed this was the file used to create the original (empty) DB. Is there a altertable somewhere else? (I am NOT a SQL expert)

Look at metadata.db with SQLiteSpy or a similar tool.

[itimpi]

Originally Posted by theducks

SQL lite allows 2 dates within one field? I was looking for a second Date capable field

There ARE two date capable fields in the books table. One is called Timestamp and the other is called pubDate.

[theducks]

Thanks,
I was relying on what I thought was the current .sql create script.
Something must have come along later and added additional fields.

I will get sqllitespy

[chaley]

Originally Posted by theducks

I am looking at the file /opt/calibre/resources/metadata_sqlite.sql and that field is not there
I assumed this was the file used to create the original (empty) DB.

It is. However, as you suspected, it isn't the end of the story. There is code in library.database and library.database2 that upgrades the database to the current version. When initializing, calibre creates a V1 database (the sql you were looking at), then upgrades it.

It is worth noting that metadata is a view, so is never ALTERed. It is simply recreated as needed.

[Starson17]

Originally Posted by chaley

I am submitting ticket with the code now.

Congratulations, that was fast! I knew you were deep into that code.

BTW, are you aware that the back yard fence needs whitewashing? If you give me your apple core, you can have that one, too.

There's a bug ticket enhancement request for remembering the regex expressions used in the Add/Save preferences when "get metadata only from filename is turned on". It would be nice for this to have a simple remembered history, like the old search history pulldown that you just rewrote. If you're up to speed on that, it might be another simple fix for you.

It's ticket #4940.

[theducks]

Originally Posted by chaley

It is. However, as you suspected, it isn't the end of the story. There is code in library.database and library.database2 that upgrades the database to the current version. When initializing, calibre creates a V1 database (the sql you were looking at), then upgrades it.

It is worth noting that metadata is a view, so is never ALTERed. It is simply recreated as needed.

Make sense, the upgrade method code can work correctly from almost any version to the current version this way, including an initial install. Elegant.

[Sabardeyn]

Before the date search programming goes too far... Just though I should question the validity of the date format. Internationally I know dates are frequently written as 10 Apr 2010 (or it's equivalent of 10-04-10). Would the search be able to account for something like this?

[chaley]

There are many date formats. I personally use all of 'd/m/y', 'd-m-y', 'd-spelled_month-y', 'd spelled_month y', 'm/d/y', 'm-d-y', and 'spelled_month d, y', and 'y-m-d'. In addition, y is 2 or 4 digits, and the spelled month might be fully spelled or abbreviated in some way.

In the end, one must either pick a format that is predictable and understandable, or suffer from mysterious conversion errors. If someone types in 10-10-10, what date is it? Using y-m-d solves the problem, and is trivially explainable.

[kovidgoyal]

@chaley: IIRC, the implemetation uses parse_date. In which case it will parse a rather wide range of date specifications.

[chaley]

Originally Posted by kovidgoyal

@chaley: IIRC, the implementation uses parse_date. In which case it will parse a rather wide range of date specifications.

Yes it does use parse_date, which (as you are implying) means that it will make educated guesses about the date. Unfortunately, the guessing does not use the locale, so ambiguous dates are parsed as m-d-y. For example, entering 5-1-10 will result in 1 June 2010 even if the locale specifies d-m-y. This is the behavior for parsing all dates in calibre, which may or may not be a bug (I don't know).

Other than the above locale issue, the current implementation has two problems. The first is that to implement partial matching, the code must know what the date segment separator is. Currently it accepts only '-'. I will change this to accept '-' or '/' (any others?). The second is that using guessable formats will be far more likely to throw a value exception, and these currently are badly (i.e., not) handled. I will fix this as well.

[DoctorOhh]

Originally Posted by chaley

For example, entering 5-1-10 will result in 1 June 2010 even if the locale specifies d-m-y.

Thanks for the effort.

Did you mean for June above to be May?

[chaley]

Originally Posted by dwanthny

Did you mean for June above to be May?

Nah. March doesn't exist in my universe.

[Sabardeyn]

Chaley,

I just thought I would broach the idea before any assumptions made in the programming became a problem. But you've given more thought to this than was apparent in that first post.

As to separators, I vaguely recall seeing periods used in dates, but cannot cite a specific example. I know they're used for phone numbers (for example, (123) 456-7890 == 123.456.7890).