Commas in LRF metadata

[kevin_boone]

Originally Posted by kovidgoyal

Not unless you're willing to a write python code

Nah. The very idea makes the hairs on my neck stand on end I already have to cope with C++, Java, and Perl on a daily basis.

I was wondering whether the metadata.db could be edited offline without breaking the program? But I'm afraid I could not make sense of your database schema...

I note that the `authors' table has a `sort' text column, but it doesn't seem to have anything in it -- at least in my setup. But the `books' table has an `author_sort' column, which does appear to be populated, and with the same text as appears in the GUI.

So I had rather hoped to be able to set all the author_sort fields to the author name by doing something like:

> update books set author_sort=(select authors.name from authors,books_authors_link where books_authors_link.book=books.id and books_authors_link.author=authors.id);

But it seems that this invokes triggers which use functions which are implemented somewhere other than in the database (in Python? C?) Anyway, it doesn't work.

It would have been nice to manipulate the database directly, but if it isn't possible, then it isn't possible :/

[gwynevans]

If you're familiar with C++ & Java, then Python should be no problem. The main issue will be that after using it, you'll probably be even more disaffected regarding Perl... (http://www.linuxjournal.com/article/3882)

[itimpi]

If you want to play around with editing the database directly then there are quite a few tools for manipulating sqlite3 databases. If you are using a PC then I recommend sqliteman as a good tool.

Obviously you should also first make a back-up in case things go wrong!

[kevin_boone]

Originally Posted by itimpi

If you want to play around with editing the database directly then there are quite a few tools for manipulating sqlite3 databases. If you are using a PC then I recommend sqliteman as a good tool.

Obviously you should also first make a back-up in case things go wrong!

I can manipulate the database easily enough. The problem is that it contains triggers, and those triggers relate to functions that are not actually in the database. I assume they are implemented in Python, but I don't know. I don't know of a client for sqlite that will allow the triggers to be disabled and, even if there were a way to do so, I don't know what damage it would do to the underlying data if I edited the data without the triggers having effect. My suspicion is that it would be fine for the changes I envisage, but I don't know.

It's not a huge bit deal -- I don't have enough books that I can't make these changes manually if I have to. I could probably have done it in less time than I've already spent fiddling around with the database :/

[itimpi]

I looked at the triggers stored in the database, and did not spot any off-hand that related to tables/fields that were not in the database. However I agree I was not necessarily doing an exhaustive examination of the triggers.

However, as you said for the change you proposed it is unlikely to matter. Triggers tend to be used to ensure integrity rather than simply amending the value of an existing field that is not used for linking tables or indexing.

[kevin_boone]

Originally Posted by itimpi

I looked at the triggers stored in the database, and did not spot any off-hand that related to tables/fields that were not in the database. However I agree I was not necessarily doing an exhaustive examination of the triggers.

However, as you said for the change you proposed it is unlikely to matter. Triggers tend to be used to ensure integrity rather than simply amending the value of an existing field that is not used for linking tables or indexing.

If you update a column in the `books' table, it triggers this:

CREATE TRIGGER books_update_trg
AFTER UPDATE ON books
BEGIN
UPDATE books SET sort=title_sort(NEW.title) WHERE id=NEW.id;
END;

I don't know what `title_sort' does, but it's not defined (so far as I can tell) in the database.

I could copy the data in books to another table with the same schema, but without the triggers, but then I couldn't copy it back. I could remove the triggers completely but then I guess Calibre would fall over.

Or am I missing something?

[kovidgoyal]

title_sort is defined in Python, it basically removes the prepositions from in fromt of the title so that when you sort on title calibre ignores leading prepositions. You can easily create a database connector to a sqlite database in C/Perl that defines this function. All the python functions/aggregators used in the database are defined in sqlite.py file in the calibre source

[kevin_boone]

Fair enough. It's probably quicker for me just to make the changes manually But thanks all the same.