COPS : Calibre OPDS PHP Server

[josce]

Hi everyone

I'm not sure whether COPS is at all the tool I'm looking for, but here's what I'd like to do: we are a group of people sourcing/producing a lot of PDF documentation. It seems to me that managing this lot of documentation with a tool like Calibre would be optimal in terms of archiving capabilities (sorting by tags + authors + dates). But every single member of the group needs to be able both to download and upload books to the server.

Is that something that can possibly be achieved with COPS?
Thanks

[mariosipad]

With COPS you can download (pdf) files but NOT upload.

You need to upload files to the Calibre "server", import them in Calibre and only then you can use COPS to select a (pdf) file and download it.

[eschwartz]

The main reason to use COPS is to make your calibre library available in a situation where you cannot leave calibre's builtin server running, either because it is a NAS or because of the lower dependencies of COPS.



If you want to upload books to calibre's library, calibre includes an auto-add-from-watched-folder feature which may come in useful, also the currently-underway content server rewrite (for calibre v3.0) is supposed to have read-write capabilities.
There is no schedule for when it may be done, but it might be your (long-term) solution.

[josce]

Thanks both. Eschwartz, I think the directory-watch feature will not suit my usage as metadata needs to be manually added with uploads. So maybe I have to wait for v3, and find a server where I can install calibre (so far I'm hosted by gandi, and I don't think I can have calibre there).

Or maybe an alternative technical solution for me might be to use the "send to folder" feature to a dropbox-like cloud folder -- I can actually install ownCloud on the server, and have all my collaborators install the ownCloud desktop client. They can then use this server folder as a device and upload books there with their metadata.

Maybe I could in addition have COPS running on the server for a convenient mobile access ? (read-only access, that'd remove the need of the ownCloud client for this)

Anyone can comment? I'll give it a try and report if no one sees a big fault in this plan.

[eschwartz]

So where are you going to manage the calibre library?

You can actually just use the command-line tools, see `calibredb add --recurse --one-book-per-directory` which can import the product of calibre's Save-to-disk feature. This will retain metadata recorded in the metadata.opf

Collaborators would run a local calibre instance, tweak the book record, save to disk and upload it to a shared OwnCloud instance.

You would run a script (cron/systemd/inotifywait) to periodically add newly-added book folders, and cleanup those files after they are added.

Then sync the calibre library to the server (assumes you cannot install custom software on the server) and let COPS handle it.

[josce]

OK, what I hadn't realised (I think) is that the save-to-disk is not a Calibre library... so there's the need to import, that you describe, and to clean up the ownCloud folder. I'll give it some thought and a few tries...

[teejcee]

@mariosipad

Hi....due some user problems, I've decided to take on a new challenge that may interest you.

I'm looking into how I can not only log user logins but if a user downloads
a book, I'd like to log the title.

I'll probably need to delve into mysql to achieve this, so, here goes......

Interested?

Cheers,
TC

[mariosipad]

@teejcee

Before you delve into sqlite3 it's, I think, important to check if the information needed to extract the book title (and author) from the calibre database is available in a log file. Otherwise it might be a non-starter

I will dedicate some time to it tomorrow (your today).

Maybe I can learn from and build on your progress?

Greetz
Mario

[teejcee]

@mariosipad

Here is an extract from a log line which I believe to be pertinent...

"GET /cops/fetch.php?data=6&type=azw3&id=3&db=3 HTTP/1.1"

And my "educated guesses" so far are...

fetch.php seems to be used to set the download up
data=6 no idea
type=azw3 * self explanatory
id=3 * id of book in the db
db=3 * library position in array defined in config_local.php

* = I'm pretty certain after a few tests against different titles in
different libraries

There is a possible show stopper and I'm uncertain to whether
there's a way around it.

This log line is written as a result of the user selecting the epub, azw3,
mobi, whatever icon to download the book. However, when the user's
browser displays the download box, the user can cancel the download,
or close the download box, therefore we are left with a log line that is false.
What I'm uncertain about is if the browser returns any value
to the calling program to indicate what the user
selected.... "OK" or "Cancel" or "X" ( close )

Cheers,
TC

[mariosipad]

@teejcee

I agree with your "guesses".

I was able, by adapting your pal's script, to put the values into $variables and use these variables to get the wanted response from sqlite3 using the same script:

SQL="select title, author_sort from books where id="$VAR" ;" 
TITLE=$(sqlite3 $DATABASE "$SQL")

Where:
$VAR = (the value of id=)
$DATABASE = (/path/to/calibredatabase/metadata.db) -> needs work to make this variable with db=[value])

I'll work on the script some more tomorrow (it's not publishable now. It confuses even me now and then with all the commented out lines with failed attempts.)

As to your "showstopper":
1 - I observed that some clients had a distinguishable response when something was actually downloaded compared to when it was not, but for instance Firefox had not.
2 - But what is the problem? What's the difference between backing out of downloading and downloading and directly deleting the downloaded content (what I usually do when I do not like what I just downloaded).

Did you arrive at the same code as I did?

[teejcee]

@mariosipad

2 - But what is the problem? What's the difference between backing out of downloading and downloading and directly deleting the downloaded content (what I usually do when I do not like what I just downloaded).

You haven't got the user I have and if it keeps going, neither will I

Did you arrive at the same code as I did?

I haven't got any further as I'm on baby sitting duties with my
grandson again....

[mariosipad]

You haven't got the user I have and if it keeps going, neither will I

Yeah, but if you delete all the problems, there are no challenges left.

I haven't got any further as I'm on baby sitting duties with my
grandson again....

Well family should come first! I'm "dogsitting" this week, lots of excercise for me.

Here is my preliminary solution. No error checking etc, it just works (for me).

#!/bin/bash
# email log entries of COPS users for previous 24 hours

DATE=$(date +%d-%b-%Y)
#LOGFILE=/var/log/apache2/other_vhosts_access.log
LOGFILE=/var/log/apache2/access.log

DATABASE[0]='/path/one/to/metadata.db'
DATABASE[1]='/path/two/to/metadata.db'
DATABASE[2]="/path/three/to/metadata.db"
DATABASE[3]='/path/four/to/metadata.db'
DATABASE[4]='/path/five/to/metadata.db' 

grep -e "epub" -e "mobi"  $LOGFILE | grep -e "200" > book-log.txt

LOGFILE=book-log.txt
TMPFILE=tmp.$LOGFILE

rm -f $TMPFILE 2> /dev/null

cat $LOGFILE 2> /dev/null | while read LINE
do
  IP=$(echo $LINE | cut -f 1 -d' ')
  USER=$(echo $LINE | cut -d ' ' -f 3)
  TIME=$(echo $LINE | cut -f2 -d'[' | cut -f1 -d' ')

  QSTR=$(echo $LINE | cut -f7 -d' ' | cut -f2 -d'?')
  DATA=$(echo $QSTR | cut -f1 -d'&' | cut -f2 -d'=')
  TYPE=$(echo $QSTR | cut -f2 -d'&' | cut -f2 -d'=')
  ID=$(echo $QSTR | cut -f3 -d'&' | cut -f2 -d'=')
  DB=$(echo $QSTR | cut -f4 -d'&' | cut -f2 -d'=')

  DBASE=${DATABASE["$DB"]}

  SQL="select title, author_sort from books where id="$ID" ;" 

  TITLE=$(sqlite3 $DBASE "$SQL")

#  echo "$TIME $USER $IP $BOOK $DATA $TYPE $ID $DB $TITLE" >> $TMPFILE
  echo "$TIME $USER $IP $TITLE" >> $TMPFILE

done

if [[ ! -s $TMPFILE ]]
then
  echo "There is no data to report." >> $TMPFILE
fi

# cat $TMPFILE | mail -s "Today's COPS users $DATE" -r "yourfrommailaddress@mail.com>" yourmail@mail.com

#rm -f $TMPFILE 2> /dev/null

# END

helped a lot!

[teejcee]

@mariosipad

I'll get back to you when I get some time to absorb your script. I'm
still tied up with family duties.

Meanwhile, have a great weekend...!
TC

[teejcee]

Ok....I've now had time to make progress with this little project. I've added
my code ( a mixture of yours & mine ) and inserted it in your original
post below. You'll need to make some changes given we are addressing
different apache2 logs. That's an area where I got caught out. I forgot
that you were accessing a different log file & it all kept going
pear-shaped until the little light globe went on

Plus I've created a minor problem by naming my main library
"Calibre Library" ; note the space. I could not get this working at
all until I realised this space was probably the culprit. It was just
a matter of creating a link to it and referencing the link in the
code. sqlite3 worked fine then. A lesson learned.

So just substitute your original code for mine if you're so inclined.
I've commented the areas.

#!/bin/bash
# email log entries of COPS users for previous 24 hours

DATE=$(date +%d-%b-%Y)
#LOGFILE=/var/log/apache2/other_vhosts_access.log
LOGFILE=/var/log/apache2/access.log

DATABASE[0]='/path/one/to/metadata.db'
DATABASE[1]='/path/two/to/metadata.db'
DATABASE[2]="/path/three/to/metadata.db"
DATABASE[3]='/path/four/to/metadata.db'
DATABASE[4]='/path/five/to/metadata.db' 

grep -e "azw3"-e "epub" -e "mobi"  $LOGFILE | grep -e "200" > book-log.txt

LOGFILE=book-log.txt
TMPFILE=tmp.$LOGFILE

rm -f $TMPFILE 2> /dev/null

###########################################
Your original code starts here....code above is pretty much common
###########################################

cat $LOGFILE 2> /dev/null | while read LINE
do
  IP=$(echo $LINE | cut -f 1 -d' ')
  USER=$(echo $LINE | cut -d ' ' -f 3)
  TIME=$(echo $LINE | cut -f2 -d'[' | cut -f1 -d' ')

  QSTR=$(echo $LINE | cut -f7 -d' ' | cut -f2 -d'?')
  DATA=$(echo $QSTR | cut -f1 -d'&' | cut -f2 -d'=')
  TYPE=$(echo $QSTR | cut -f2 -d'&' | cut -f2 -d'=')
  ID=$(echo $QSTR | cut -f3 -d'&' | cut -f2 -d'=')
  DB=$(echo $QSTR | cut -f4 -d'&' | cut -f2 -d'=')

  DBASE=${DATABASE["$DB"]}

  SQL="select title, author_sort from books where id="$ID" ;" 

  TITLE=$(sqlite3 $DBASE "$SQL")

#  echo "$TIME $USER $IP $BOOK $DATA $TYPE $ID $DB $TITLE" >> $TMPFILE
  echo "$TIME $USER $IP $TITLE" >> $TMPFILE

done

if [[ ! -s $TMPFILE ]]
then
  echo "There is no data to report." >> $TMPFILE
fi

cat $TMPFILE | mail -s "Today's COPS users $DATE" -r "yourfrommailaddress@mail.com>" yourmail@mail.com

# END

####################################
Your original code ends here and mine starts below
####################################

cat $LOGFILE 2> /dev/null | while read LINE
do
  IP=$(echo $LINE | cut -f2 -d' ')
  USER=$(echo $LINE | cut -f4 -d' ')
  TIME=$(echo $LINE | cut -f2 -d'[' | cut -f1 -d' ')
  QSTR=$(echo $LINE | cut -f8 -d' ' | cut -f2 -d'?')
  DATA=$(echo $QSTR | cut -f1 -d'&' | cut -f2 -d'=')
  TYPE=$(echo $QSTR | cut -f2 -d'&' | cut -f2 -d'=')
  ID=$(echo $QSTR | cut -f3 -d'&' | cut -f2 -d'=')
  DB=$(echo $QSTR | cut -f4 -d'&' | cut -f2 -d'=')

  #  Look to get some more interesting information about the IP address.

  unset CITY REGION COUNTRY POSTAL LOC

  if [[ ${IP:0:7} == "192.168" ]]
  then
    CITY="---Internal IP address.----" 
  else
    IPINFO=$(curl ipinfo.io/$IP 2> /dev/null)
    CITY=$(echo ${IPINFO##*city\":} | cut -f2 -d'"') 
    REGION=$(echo ${IPINFO##*region\":} | cut -f2 -d'"') 
    COUNTRY=$(echo ${IPINFO##*country\":} | cut -f2 -d'"') 
    POSTAL=$(echo ${IPINFO##*postal\":} | cut -f2 -d'"') 
    LOC=$(echo ${IPINFO##*loc\":} | cut -f2 -d'"') 
  fi 

  echo "$DATE $USER $IP $CITY $REGION $COUNTRY $POSTAL $LOC" >> $TMPFILE

  DBASE=${DATABASE["$DB"]}

  SQL="select title, author_sort from books where id="$ID" ;"

  TITLE=$(sqlite3 $DBASE "$SQL") 


  AUTHOR=$(echo $TITLE | cut -f2 -d"|")

  TITLE=$(echo $TITLE | cut -f1 -d"|")

  GIVEN=$(echo $AUTHOR | cut -f2 -d",")
  SURNAME=$(echo $AUTHOR | cut -f1 -d",")

  echo "$TITLE "      " $GIVEN $SURNAME" >> $TMPFILE

  echo " " >> $TMPFILE

done

if [[ ! -s $TMPFILE ]]
then
  echo "There is no data to report." >> $TMPFILE
fi

cat $TMPFILE | mail -s "Today's COPS users $DATE" -r "yourfrommailaddress@mail.com>" yourmail@mail.com
 
# END

As you can see it's pretty much your code modified & added to

helped a lot!

...... Yes...me too

Sample output...

23-Feb-2016 userA 192.168.1.1 ---Internal IP address.----
Title Author Given-name Surname

23-Feb-2016 userB 58.nnn.nn.nn Concord New South Wales AU 2137 -33.8429,151.1107
Title Author Given-name Surname

Yes...I know....GPS coordinates are a little over the top but it's
interesting that you can do this kind of stuff

Any questions, just yell

Cheers
TC

[mariosipad]

@teejcee

Plus I've created a minor problem by naming my main library
"Calibre Library" ; note the space.

I had the same problem . I "fixed" it by renaming the library to "Calibre_Library".

Yes...I know....GPS coordinates are a little over the top but it's interesting that you can do this kind of stuff

I agree, it's over the top. But I also agree that is is very nice one can do this kind of stuff.

I see you have the same "issue" with the name of the author as I had. I did not swap around the $GIVEN and $SURNAME, but I queried the database some more. See this bit of code:

  DBASE=${DATABASE["$DB"]}

  SQL1="select title from books where id="$ID" ;" 
  TITLE=$(sqlite3 $DBASE "$SQL1")

  SQL2="select name from authors where id="$(sqlite3 $DBASE "select author from books_authors_link where book="$ID" ;")" ;"
  AUTHOR=$(sqlite3 $DBASE "$SQL2")

  echo "$TIME $USER $IP : $TITLE  by  $AUTHOR" >> $TMPFILE

It now depends on how correct one has used the right given and surname in Calibre.

Another enhancement I made to the original code is:

Original:
grep -e "azw3"-e "epub" -e "mobi"  $LOGFILE | grep -e "200" > book-log.txt

New:
grep -E "(epub|mobi|azw3|azw|cbr|cbz)" $LOGFILE | grep -e "200" > book-log.txt

I think this little project is done and we deserve a
Or am I missing something and is there more room for improvement?

Greetz,
Mario