Griffith SQL for Dummies

[CinéphOli]

Griffith being still in an early stage of development (no criticism implied here, just stating a fact ), there are functionalities which are not yet implemented.
Such a missing functionality is, for instance, mass editing.
Fortunately, by the proper use of SQL queries directly on the Griffith database, those (momentary) shortcomings can be addressed. Maybe not all of them, but definitely some of them…

So, let's use this topic to discuss (and gather in one place) some useful SQL techniques! And please don't hesitate to make your own requests!

Preliminaries

• A word of caution! (Don't break everything!)
• Tools (How can I do it?)
• Tutorials (Where can I find some SQL basic tuitions?)
• The Griffith database (How are the tables organized?)

Categories

   Country codes

• How can I replace the country names with their international two-letter codes equivalent?

   Flags

• Mark all films as seen/unseen

   Languages (audio, subtitles)

• How can I add language information (audio, subtitles) of a given type to a set of films?

   Maintenance

• How can I list all films without poster?
• How can I list all films without runtime?

   Statistics

   Tags

• How can I add a tag to all existing films that don't have it yet?

[CinéphOli]

A WORD OF CAUTION

Reading from a file (any kind of file) is harmless because the content is not modified, but writing to a file (any kind of file) is potentially dangerous if not done properly!

Therefore, use extreme caution when working on your Griffith database! Even better, work on a copy first, then use the command again on your main database once you are confident that everything is correct.

Also, most editing applies to some records, not all of them. Therefore, it is important to have a fair understanding of the proper use of the SQL WHERE and HAVING clauses, which  are used to select a subset of records.

One last word of caution: as far as SQL is concerned, I consider myself more a Dummy than a Guru (for instance, I never used triggers so far, I don't even know how they work…). So please don't take my word for it, test everything before applying it! I will of course do my best to avoid errors, but nobody is perfect…


_____
(Back to 1^st message)

[CinéphOli]

SOME TOOLS

Q: How can I modify my Griffith database with SQL?

A: Well, that depends on both your level of expertise and the SQL server on which you use Griffith.

By default, Griffith is an SQLite3 database, a self-sustained format where everything is embedded in one file only: very simple, very effective. The other available formats are PostgreSQL, MySQL and Microsoft SQL. Some tools will manage some formats but not others, so for the purpose of this topic let's assume that the beginner user will stick to the default format, ie SQLite3.

SQLiteman is a graphic interface to an SQLite database. It is available for both Windows and Linux. Simple to use yet powerful. It has the ability to store the SQL queries in a history file, so nothing is lost.

For those who use Firefox, there is an extension called SQLite Manager that will do the job. In my opinion, slightly more complex to use, but probably more powerful. For instance, there are more export options to CSV files than in SQLiteman.

For the more advanced users, there is also the embedded griffith shell, which is invoked with:

Code:

griffith --shell

And, of course, there is always SQL in the console mode for the expert users, those who are advanced enough to write scripts. Maybe off-topic?


_____
(Back to 1^st message)

[CinéphOli]

TUTORIALS

Q: I need to learn SQL/brush up my SQL. Where can I find some (good?) tutorials?

A:

• 1keydata.com: SQL Tutorial - SQL Query Reference (in many different languages)
• sql-tutorial.net: SQL Tutorial - Learn SQL
• w3schools.com: SQL Tutorial

_____
(Back to 1^st message)

[CinéphOli]

The Griffith database

Q: How is the data stored in the Griffith database? How are the tables organized?

A: Do I need to develop? Please let me know.


_____
(Back to 1^st message)

[POX]

I know more people are familiar with SQL than Python & SQLAlchemy but I think `griffith --shell` is a better tool for advance users (+ it works with all databases out of the box

[CinéphOli]

How can I add a tag to all existing films that don't have it yet?

Scenario: You already have 500 films in your database and you just thought of a new tag that could apply by default to all films. For instance, a tag that would let you know whether a label has already been printed for that film or not. You have already modified some of the films, but it is really too tedious a task to do everything manually.

Tables involved:
The films are stored in the table movies.
The tags are stored in the table tags.
The relations between films and tags are stored in the table movie_tag.

1. First of all, issue the following SQL query:

Code:

SELECT * FROM tags ;

This will list all the existing tags. Take note of the tag_id value matching the tag you wish to add to all the films.
If the tag doesn't already exist, you can add it with the following query:

Code:

INSERT INTO tags (name) VALUES ('Some text describing the tag') ;

Let's suppose for the sake of this example that the matching tag_id = 5.

2. Next, issue the following query:

Code:

INSERT INTO movie_tag (movie_id, tag_id)
 SELECT movie_id, '5' /* tag_id value */
   FROM movies
  WHERE movie_id NOT IN (SELECT DISTINCT movie_id
                           FROM movie_tag
                          WHERE tag_id = 5) ;

That's all, you should have the expected result.


_____
(Back to 1^st message)

[CinéphOli]

I know more people are familiar with SQL than Python & SQLAlchemy but I think `griffith --shell` is a better tool for advance users (+ it works with all databases out of the box

Thank you POX, I just had a look at it, it is really for advanced users ! Me poor Dummy didn't understand much of it …
I'll mention it in the list of dedicated tools. Is this shell available in Linux, Windows and Mac?

[POX]

I'd say it's even easier than raw SQL queries if you know Python a bit, f.e. scenario from your previous message can be accomplished with:

$ griffith --shell
>>> movies = sess.query(db.Movie)
>>> tag = db.Tag()
>>> tag.name = 'Some text describing the tag'
>>> for movie in movies:
...         movie.tags.append(tag)
>>> sess.commit()

and you still get most of verification that Griffith does (with plain SQL, you can break Griffith easily)

[CinéphOli]

When I read your example, I understand it and I can see how straightforward it is, but I also realise that I am totally unable to 'create' it myself unless I know SQL, OOP, Python plus all the available methods! The learning curve seems steeper than 'just' SQL. Moreover, SQL is closer to the natural English language, and therefore easier for a beginner to understand. It is true one must pay attention not to break the database, especially by not tempering with the *_id fields.

But I feel that there is more possibilities with SQLAlchemy than with raw SQL. For instance, the country field in the movies table: I like to replace the country names with their matching two-letter country codes: FR for France, PL for Poland, DE for Germany and so on. When there is only one country in the field, that is easy enough in SQL:

Code:

UPDATE movies SET country = 'FR' WHERE UPPER(country) == 'FRANCE' ;

But when there are several countries in the field, it involves some string manipulations with regular expressions, something like:

Code:

s/(.*)France(.*)/\1FR\2/

I don't think it is possible to do such things in SQL, but I feel that it is possible with SQLAlchemy: am I right? Or are they regexp functions you can 'add' to SQL? Could anyone please tell me how to do this?

[CinéphOli]

OK, actually I found a solution for this replacement in SQLite:

Code:

UPDATE movies
   SET country = replace(country,'France','FR') ;

I don't think that this function replace() is standard SQL, but it is embedded within SQLite. And it is not as powerful as regexp.

[CinéphOli]

How can I replace the country names with their international two-letter codes equivalent?

Each country is represented by a unique two-letter (or three-letter) country code. Let's use this two-letter country code instead of the full country name in the Griffith database, avoiding so country names in different languages after having fetched the data from sources in different languages (DE instead of Deutschland or Germany or Allemagne). This will result in  a more coherent, systematic database.

Table involded:
The country names are stored in the table movies, in the field country.

1. Get the list of all existing country names in your database:

Code:

SELECT DISTINCT country
  FROM movies
 ORDER BY country ;

2. Pick a country name in the list from step 1 (let's say Germany) and replace it with its two-letter code equivalent (DE):

Code:

UPDATE movies
   SET country = replace(country,'Germany','DE') ;

-- If different languages are used for the same country,
-- then repeat this step using the correct translations:
UPDATE movies
   SET country = replace(country,'Deutschland','DE') ;
UPDATE movies
   SET country = replace(country,'Allemagne','DE') ;

3. Repeat steps 1 and 2 until all country names are changed.


_____
(Back to 1^st message)

[CinéphOli]

How can I add language information (audio, subtitles) of a given type to a set of films?

Scenario: I would like to add French audio systematically to all my French films.

Most of the time, a film from France is in French (would you believe it?). Most of the time, a film from France is a film where the country field begins with 'France' (or 'FR'). Most of the time, if 'France' (or 'FR') is found elsewhere in the country field, then it is just an associated producer, with no guarantee whatsoever for the film to be in French.

All this to say that this mass editing will not return 100% foolproof results, some fine-tuning will still be necessary, there will be exceptions to be coped with manually:

• there are French-speaking films not coming from France,
• when there are several languages used in one country (like BE, CH, CA), then the country is not enough to tell which language is used for the film,
• …

Tables involved:
The audio and subtitles data is stored in the table movie_lang, in the following fields:

• type, hard-coded in Griffith: 0=empty/nothing? / 1=lector / 2=dubbing / 3=subtitles / 4=commentary
• lang_id, taken from the table languages
• acodec_id, taken from the table acodecs
• achannel_id, taken from the table achannels
• subformat_id, taken from the table subformats

To add an audio track to a film, one must provide the matching lang_id of the language. One can also provide the codec and the channels, but these IMO vary too much to be included in a mass editing.

1. Get the list of all the languages:

Code:

SELECT *
  FROM languages
 ORDER BY name ;

Take note of the lang_id that matches the chosen language (French). Let's suppose it is 9.

2. Let's assume that two-letter country codes are used. Issue the following query:

Code:

INSERT INTO movie_lang (movie_id, lang_id)
 SELECT movie_id, '9' /* 9=French */
   FROM movies
  WHERE movie_id IN (SELECT movie_id
                       FROM movies
      WHERE substr(country,1,2) = 'FR') /* Use here whatever string (and substr length!)
                                                           needed to represent the chosen country */
    AND movie_id NOT IN (SELECT DISTINCT movie_id
                           FROM movie_lang
  WHERE lang_id='9'
                            AND (type='0' OR type IS NULL)) /* Avoids duplicates */ ;

WARNING: I would like to emphasize that this works when adding new records with INSERT INTO. If you wish to UPDATE existing records in movie_lang, then a lot more of integrity check is required. For instance, there is no point in having codec and channels for subtitles.


_____
(Back to 1^st message)

[CinéphOli]

How can I list all films without poster?

This can be useful for maintenance purpose:

Code:

SELECT movie_id,
       o_title    AS Title,
       poster_md5
  FROM movies
 WHERE poster_md5 IS NULL
 ORDER BY Title ;

_____
(Back to 1^st message)

[CinéphOli]

Mark all films as seen/unseen

This can be useful to set/reset your whole database.

Tables involved:
The flag seen/unseen is stored in the table movies, in the field seen.

Seen:

Code:

UPDATE movies SET seen=1 ;

Unseen:

Code:

UPDATE movies SET seen=0 ;

_____
(Back to 1^st message)