Personal Video Database alternate Forum

Hello meriator.

This looks amazing. Is there a way we could try your work? Is it too much to ask for a step-by-step guide to set up this setup? Are you willing to upload these templates? I am an absolute programming noob, but a quick learner, willing at least to use logic in order to get this...

Thanks in advance

P.S. Thanks for keeping forum alive and yes - PVD is still by far the most superior movie database out there.

There are of course several different reasons that slow down PVD.

One is a lot of pictures that are way too big.

In my humble opinion, pictures with a maximum size of 500 x 500 px are completely sufficient. With an average compression ratio of 75%, the quality is still very good. So that a file size is between approx. 30 - 90 kb depending on the picture.
With these settings, PVD runs very quickly even with 10,000+ films with an average of 3-4 images per film and person.

In my opinion, images with sizes of 2000 x 2000 px and more that quickly reach file sizes of 2 - 5 MB have not been lost in the database. Not that you shouldn't have such large pictures, especially if you want to print out covers from time to time. Then an image can often not be big enough to achieve proper printing results. I can understand that and I also have scans of my DVDs, but not in the database. Especially when you consider that such large pictures do not fit 100% on the display without having to scroll.

This fact also creates a problem that cannot be easily solved with PVD. How do I find pictures that are too big?
PVD itself offers the possibility of executing SQL queries, but it is of no use because PVD does not display the result, so that something could be done with it.
e.g. the query

SELECT r. *, M. "Title" FROM IMAGES r
JOIN MOVIES m ON r. "Mid" = m. "Mid"
WHERE OCTET_LENGTH (r. "Imgdata")> 100000;

This query should display all entries in the IMAGES table and the associated title of the film from the MOVIES table.
Ha doesn't work like that.
Now this is where "FlameRobin" comes into play as the tool of the first choice. A freeware program that you don't even have to install.
If you have added the PVD database to this program as a resource, you can do a lot more.
BUT CAUTION you can also destroy the DB.
So it is always a good thing to work with a copy of the database first.

Back to the SQL query. The print OCTET_LENGTH is a built-in Firebird function and returns the size of the image in bytes.
If you first want to know how many images exceed the size of 100,000 bytes are in the database, you have to change the query accordingly.
SELECT COUNT (*) as sum_pics FROM IMAGES r
WHERE OCTET_LENGTH (r. "Imgdata")> 100000;

Ah, now you can see one next to the displayed result. The execution obviously takes longer. For this and other reasons I changed my table images. I've added a few, actually 4 fields to the database.
The corresponding SQL statement is also here

ALTER TABLE IMAGES ADD
"caption" Varchar (150) CHARACTER SET UNICODE_FSS COLLATE UNICODE_FSS;
Alter table IMAGES ADD "imgheight" integer;
Alter table IMAGES ADD "imgwidth" integer;
Alter table IMAGES ADD "imgsize" integer;

and immediately afterwards I executed this SQL statement.
Update IMAGES r set r. "Imgsize" = OCTET_LENGTH (r. "Imgdata");

And now I get with this statement

SELECT COUNT (*) as sum_pics FROM IMAGES r
WHERE r. "Imgsize"> 100000;

the result is displayed like a flash
however, I would have to execute the UPDATE statement every time after adding images. Oops, boring, I don't want it, it has to be automated.
Whoops, briefly postponed this instruction, which is called a TRIGGER, which should do this in the future.

SET TERM ^;
CREATE TRIGGER MOVIES_IMAGE_SIZE FOR IMAGES ACTIVE
BEFORE update OR insert POSITION 0
AS BEGIN NEW. "Imgsize" = OCTET_LENGTH (NEW. "Imgdata"); END ^
SET TERM;

so and now the value for imgsize is entered automatically with each new insertion or change of a picture.
I now change the first query to

SELECT r. *, M. "Title" FROM IMAGES r
JOIN MOVIES m ON r. "Mid" = m. "Mid"
WHERE r. "Imgsize"> 100000;

Unfortunately, now I still have to reduce all of them by hand and the height and width of the pictures do not register by themselves.
But we already have the option of inserting a short line of text for the caption.

how we can automate the whole thing more conveniently soon.
ps. we also can do that with

ALTER TABLE PEOPLEIMAGES ADD
"caption" Varchar(150) CHARACTER SET UNICODE_FSS COLLATE UNICODE_FSS;
Alter table PEOPLEIMAGES ADD "imgheight" integer;
Alter table PEOPLEIMAGES ADD "imgwidth" integer;
Alter table PEOPLEIMAGES ADD "imgsize" integer;

Alter table THUMBNAILS ADD "imgheight" integer;
Alter table THUMBNAILS ADD "imgwidth" integer;
Alter table THUMBNAILS ADD "imgsize" integer;


Alter table PEOPLETHUMBNAILS ADD "imgheight" integer;
Alter table PEOPLETHUMBNAILS ADD "imgwidth" integer;
Alter table PEOPLETHUMBNAILS ADD "imgsize" integer;


Update THUMBNAILS r set r."imgsize" = OCTET_LENGTH(r."imgdata");
Update PEOPLEIMAGES r  set r."imgsize" = OCTET_LENGTH(r."imgdata");
Update PEOPLETHUMBNAILS r  set r."imgsize" = OCTET_LENGTH(r."imgdata");


SET TERM ^ ;
CREATE TRIGGER THUMBNAILS_IMAGE_SIZE FOR THUMBNAILS ACTIVE
BEFORE update OR insert POSITION 0
AS BEGIN  NEW."imgsize" = OCTET_LENGTH(NEW."imgdata"); END ^
SET TERM ; ^

SET TERM ^ ;
CREATE TRIGGER PEOPLEIMAGE_SIZE FOR PEOPLEIMAGES ACTIVE
BEFORE update OR insert POSITION 0
AS BEGIN  NEW."imgsize" = OCTET_LENGTH(NEW."imgdata"); END ^
SET TERM ; ^

SET TERM ^ ;
CREATE TRIGGER PEOPLETHUMBNAILS_SIZE FOR PEOPLETHUMBNAILS ACTIVE
BEFORE update OR insert POSITION 0
AS BEGIN  NEW."imgsize" = OCTET_LENGTH(NEW."imgdata"); END ^
SET TERM ; ^
----------------------------------------------------------------------------------------
und in deutsch
----------------------------------------------------------------------------------------
Es gibt natürlich mehere verschiedene Gründe die PVD verlangsamen.

Einer ist, viele viel zu große bilder.

Meiner  bescheidenen Meinug nach genügen Bilder mit einer maximalen Grüße von 500 x 500 px völlig. Bei einer durchschnitlichen Kompresionsrat von 75% ist auch die Qualität immernoch sehr gut. So das man bei einer Dateigrüße je nach bild zwischen ca. 30 - 90 kb liegt.
Mit diesen Einstellungen läuft PVD auch bei 10000++ Filmen mit durchschnitlich 3-4 Bildern pro Film und Person noch sehr zügig.

Bilder mit Größen von 2000 x 2000 px und mehr die schnell auch Dateigrößen von 2 - 5 MB ereichen haben meiner Meinung nach nicht in der Datenbank verloren. Nicht dass man solch große Bilder nicht haben sollte, vorallem wenn man ab und an auch covers ausducken möchte. Dann kann oft ein Bild garnicht groß genug sein um ordentlich Druckergebnisse zu erreichen. Das kann ich verstehen und auch ich habe von meinen DVDs solche scanns, aber eben nicht in der Datenbank. Vorallem wenn man bedenkt dass solch großen Bilder noch nicht zu 100% auf das Display passen ohne srollen zu müssen.

Mit diesem Umstand entsteht auch ein Problem das mit PVD so nicht einfach lösbar ist. Wie finde ich zu große Bilder?
PVD selbst bietet zwar die möglicht SQL-Abfragen aus zu führen nur nützt das nichts da PVD das Resultat nicht anzeigt , so dass man damit etwas anfangen könnte.
z.B die Abfrage

SELECT r.*, m."title" FROM IMAGES r
JOIN MOVIES m ON r."mid" = m."mid"
WHERE OCTET_LENGTH(r."imgdata") > 100000;

Mit dieser Abfrage sollten alle Eintrage der Tabelle IMAGES sowie der dazugehörige Titel des Film aus der Tabelle  MOVIES angezeigt werden.
Ha geht so nicht.
Nun hier kommt als Das Hilfsmittel der erten Wahl "FlameRobin" ins Spiel. Ein freeware Program das man noch nicht einmal unbedingt installieren muß.
Hat man die PVD-Datenbank in diesem Programm als resource hinzugefügt kann man sehr viel mehr machen.
ABER VORSICHT, man kann auch die DB zerstören.
So ist es immer eine gute Sache zu nächst mit einer Kopie der Datenbank zu arbeiten.

Zurück zur SQL-Abfrage. Der aus Druck OCTET_LENGTH ist eine eingebaute Firebird function und gibt die Größe des Bildes in Bytes zurück.
Möchte man erst ein mal wissen wieviel bilder die Größe von 100000 Bytes überschreiten in der Datenbank sind, muß man die Abfrage dahingehend ändern.
SELECT COUNT(*) as sum_pics FROM IMAGES r
WHERE OCTET_LENGTH(r."imgdata") > 100000;

Aha jetzt sieht man schon mal eines neben dem angezeigten ergebnis. Die Ausführung dauert sichtlich länger. Aus diesem und weiteren Gründen habe ich meine Tabelle Images geändert. Ich habe ein paar, genau genommen 4 Felder der Datenbank hinzugefügt.
Hier auch gleich die entsprechende SQL-anweisung

ALTER TABLE IMAGES ADD
"caption" Varchar(150) CHARACTER SET UNICODE_FSS COLLATE UNICODE_FSS;
Alter table IMAGES ADD "imgheight" integer;
Alter table IMAGES ADD "imgwidth" integer;
Alter table IMAGES ADD "imgsize" integer;

und direct danach habe ich diese SQL-Anweisung ausgeführt.
Update IMAGES r set r."imgsize" = OCTET_LENGTH(r."imgdata");

Und jetzt bekomme ich mit diesem Statement

SELECT COUNT(*) as sum_pics FROM IMAGES r
WHERE r."imgsize" > 100000;

wie ein blitzartig das ergebnis angezeigt
allerdings müsste ich jetzt jedes mal nach dem ich Bilder hinzugefügt habe die UPDATE-Anweisung erneut ausführen. UUps langweilig, will ich nicht, das muß automatisiert werden.
schwupps, noch kurz diese anweisung nach geschoben die man einen TRIGGER nennt, der das zukünftig erledigen soll.

SET TERM ^ ;
CREATE TRIGGER MOVIES_IMAGE_SIZE FOR IMAGES ACTIVE
BEFORE update OR insert POSITION 0
AS BEGIN  NEW."imgsize" = OCTET_LENGTH(NEW."imgdata"); END ^
SET TERM ;

so und nun wird bei jedem  neuen einfügen oder ändern eines bildes der wert für imgsize automatisch eingetragen.
ändere ich nun noch die erste Abfrage um in

SELECT r.*, m."title" FROM IMAGES r
JOIN MOVIES m ON r."mid" = m."mid"
WHERE r."imgsize" > 100000;

jetzt muß ich aber leider noch immer alle von Hand verkleinern und auch die Höhe und Breite der bilder trägt sich nicht von alleine ein.
Aber wir haben schon mal die Möglichkeit ein kurzes textzeile die Bildunterschrift einzufügen.

wie wir das ganze bequemer automatisieren können demnächst.
ps. das Ganze können wir auch mit den Tabelle: THUMBNAILS , PEOPLEIMAGES, PEOPLETHUMBNAILS machen sie oben

and a view more

first image = settings and results of my local netflix-parser

second image = debug & results view of my local proxy

third image = proxy config view

and more pics

some more pics

No doubt about it, PVD is still the best movie database out there ..
but some requirements, features and tables (e.g. for seasons) and table fields (
such as in the IMAGES table img_height, immg_width, img_size, caption ...,
or in the Persons table death_place, eye_color, hair_color, hair_length, body_height, citzenship, spoken_languages, primary_job, spouse, children etc ..)
last but not least, the lack of processing of HTTPS Requests

... so is there a way to change this
yes there is

The solution is basically simple, called a local web server that can access the database.
Then it is only a matter of programming to display or change films, (filtered) film lists, people, etc.
As well as writing suitable parsers that can be configured individually for each host.

I have been using something like this for a long time, but unfortunately this solution is so specific to my (changed) database at the moment that I cannot publish it in this way.
But hopefully soon I will find time to write a simple test version and make it available as a pre-alpha version.

There are two possible ways of parsing data and writing it to the database, whereby both methods can exist and can be used.

1.) PVD requests a website to parse (mostly now as an HTTPS request)
PVD -> Proxomitron -> local proxy of our web server -> local parser -> (formatting of the data) -> return of the parsed data

2.) directly from the local web server -> local parser -> (formatting the data) -> ((possibly) overview of the data and selection of the desired results) -> insert the results in the corresponding fields

Ok, of course this scenario has a couple of certain requirements, the so-called OVERHEAD.
we need
1. Firebird (in my case version 2.5 -> to access the database via webserver and PVD at the same time)
2. Proxomitron (with appropriate configuration)
3. an installed local web server (with the appropriate configuration) (in my case Apache 2.4)
4. We need an installed PHP version (with the appropriate configuration) (in my case still version 5.6)
5. SQLite (is available as a library part of PHP, and completely sufficient)
(6. *) MySQL (can but is not absolutely necessary) (available in my case) (v. 5.6) (regulates local user access and settings, as well as individual user votings) (but can also be implemented via SQLite)
(7.) Curl (can but is not absolutely necessary) (available in my case)
(8.) Perl (can but is not absolutely necessary) (available in my case)
(9.) ImageMagick (can but is not absolutely necessary) (in my case available v. 6.9.3 Q16 (64-bit)) (simply enables the best image results when converting or cropping images)

10. Last but not least, we need two local IPs.
One for the web server, which can therefore also be reached in the entire local network.
As well as one for the local proxy (the folder of which is located in that of the local web server.
But for functional and configuration reasons, it must be addressable separately)

I know that all this is not so easy to implement for every PVD friend, but it is a way to realize the things that are impossible at the moment, unless Nostra emerges from oblivion and finds the time and the will to PVD to raise a more contemporary level.

I am also working on converting the PVD-firebird database to SQLite and MySQL.


there are some images too

these images of this an the next to posts are related to my local webserver using the firebird database
to show a bit of what could be done
this local site is curently mainly used to view and find movies and to provide a quicklink to watch the movie from all local PCs (VLC.icon if present)
(as Guest = not logged in)

Hello there.

I was afraid PVD is lost for good! I don't know if Russian forum is still working, and if so, mazbe it would be a good idea to post it's link somewhere and to stick it, just in case.
Is it possible to retrieve a copy of original forum and to transfer it here?
Best regards.