08-05-2014, 08:33 AM | #1 |
Member
Posts: 21
Karma: 10
Join Date: Nov 2013
Device: Pocketbook Lux 3 / iPad Mini retina/iPhone 6
|
SQL error: sortconcat() unknown
Hello, I'm trying to extract some data out of the metadata.db SQLite file with SQL queries. Works fine till I try to use the VIEW meta. It is defined with a function that does not exist in the schema or in sqlite3 apparently...
Please tell me if there is another way to get that data w/o SQL. I'm trying to find which books have been added since N days or since a specific date in order to publish it some way (I can output json/xml/plain text). Thanks. SQL query: --- select id, title, authors, series from meta where id in (select books.id as bid, custom_column_1.value as v from books, custom_column_1 where v >= ? and bid = custom_column_1.book order by v) order by authors; --- it always fails with: no such function: sortconcat: select id, title, authors, series from meta where id in (select books.id as bid, custom_column_1.value as v from books, custom_column_1 where v >= ? and bid = custom_column_1.book order by v) order by authors; indeed in the meta VIEW description there is sortconcat(): --- CREATE VIEW meta AS SELECT id, title, (SELECT sortconcat(bal.id, name) FROM books_authors_link AS bal JOIN authors ON(author = authors.id) WHERE book = books.id) authors, (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher, (SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating, timestamp, (SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size, (SELECT concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags, (SELECT text FROM comments WHERE book=books.id) comments, (SELECT name FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series, series_index, sort, author_sort, (SELECT concat(format) FROM data WHERE data.book=books.id) formats, isbn, path, lccn, pubdate, flags, uuid FROM books; --- |
08-05-2014, 08:44 AM | #2 |
creator of calibre
Posts: 44,356
Karma: 23661992
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
|
Advert | |
|
08-05-2014, 08:57 AM | #3 | |
Member
Posts: 21
Karma: 10
Join Date: Nov 2013
Device: Pocketbook Lux 3 / iPad Mini retina/iPhone 6
|
Quote:
Even just a --csv like in other options for calibredb would be nice. Last edited by Keltia; 08-05-2014 at 09:00 AM. Reason: Added --csv |
|
08-05-2014, 09:02 AM | #4 |
creator of calibre
Posts: 44,356
Karma: 23661992
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
calibredb catalog t.csv
|
08-05-2014, 09:13 AM | #5 |
Member
Posts: 21
Karma: 10
Join Date: Nov 2013
Device: Pocketbook Lux 3 / iPad Mini retina/iPhone 6
|
|
Advert | |
|
08-05-2014, 12:35 PM | #6 |
Member
Posts: 21
Karma: 10
Join Date: Nov 2013
Device: Pocketbook Lux 3 / iPad Mini retina/iPhone 6
|
|
08-05-2014, 12:41 PM | #7 |
creator of calibre
Posts: 44,356
Karma: 23661992
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
Really? What tools are you using that cannot deal with a BOM.
calibredb catalog t.csv && calibre-debug -c "f = open('t.csv', 'r+b'); raw = f.read(); f.seek(0); f.truncate(); f.write(raw[3:])" |
08-05-2014, 12:51 PM | #8 |
Member
Posts: 21
Karma: 10
Join Date: Nov 2013
Device: Pocketbook Lux 3 / iPad Mini retina/iPhone 6
|
I ended up using that (but my script is in Ruby) to remove the BOM. The CSV converter was taking the BOM as part of the first header name. My point is that the BOM is not needed in UTF-8 at all.
|
08-05-2014, 12:55 PM | #9 |
creator of calibre
Posts: 44,356
Karma: 23661992
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
BOMs are not needed for UTF-8, they are needed to distinguish between files that might be in any of UTF-8, UTF-16-le, UTF-16-be, UTF-32-le, UTF-32-be
I dont know how this meme that UTF-8 is some kind of panacea has spread on the internet. There should be a minimum competence test before anyone is allowed to blog on the subject of unicode. |
Tags |
sqlite3 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
KindleGen error message: E1003 Unknown error in class String. | Doitsu | Kindle Formats | 1 | 06-25-2014 12:29 PM |
Unknown Book Type error v0.9.44 | aegisrose | Calibre | 0 | 08-21-2013 12:15 PM |
No space on temp = unknown error | Giuseppe Chillem | Calibre | 15 | 01-02-2013 10:29 AM |
ERROR: Could not open ebook: Unknown book type | wooboo | Conversion | 0 | 05-06-2012 08:55 PM |
Template error value:unknown field null | sfuller | Calibre | 4 | 06-13-2011 03:02 PM |