SQL error: sortconcat() unknown

[Keltia]

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;
---

[kovidgoyal]

http://manual.calibre-ebook.com/gui....arch-interface

http://manual.calibre-ebook.com/cli/calibredb.html

[Keltia]

Originally Posted by kovidgoyal

http://manual.calibre-ebook.com/gui....arch-interface

http://manual.calibre-ebook.com/cli/calibredb.html

Ok, thanks a lot. Is there any way (I can't see) to output a different format for --for-machine like json/yaml/csv? Right now it is not easily parsable. Would you be interested in implement/have someone implement these output format?

Even just a --csv like in other options for calibredb would be nice.

[kovidgoyal]

calibredb catalog t.csv

[Keltia]

Originally Posted by kovidgoyal

calibredb catalog t.csv

Thanks, I can work with that as it does support "-s" and "--fields".

[Keltia]

Originally Posted by kovidgoyal

calibredb catalog t.csv

Could you please remove the BOM from the CSV? Please please pretty please. It is completely unnecessary in UTF-8 and messes up things later...

[kovidgoyal]

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:])"

[Keltia]

Originally Posted by kovidgoyal

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:])"

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.

[kovidgoyal]

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.