99K7huSR

1using System;
2using System.IO;
3using System.Collections.Generic;
4using System.Threading.Tasks;
5using Microsoft.Data.Sqlite;
6
7namespace MovieDeathDate
8{
9  class Program
10  {
11    static async Task Main(string[] args)
12    {
13      bool shouldRefresh = args.Length > 0 && args[0] == "refresh";
14
15      const string fileName = "movies.db";
16      if (!File.Exists(fileName))
17      {
18        shouldRefresh = true;
19      }
20      
21      using (SqliteConnection sqliteConnection = new SqliteConnection(shouldRefresh ? $"Filename={fileName}" : $"Filename={fileName};Mode=ReadOnly"))
22      {        
23        await sqliteConnection.OpenAsync();
24        
25        if (shouldRefresh)
26        {
27          Console.WriteLine("Loading info from disk...");
28          var transaction = sqliteConnection.BeginTransaction();
29          
30          SqliteCommand createDeadActorsTable = new SqliteCommand("DROP TABLE IF EXISTS dead_actors; CREATE TABLE dead_actors (actor_id INTEGER PRIMARY KEY, name TEXT, death_year INTEGER)", sqliteConnection, transaction);
31          await createDeadActorsTable.ExecuteNonQueryAsync();
32          
33          SqliteCommand updateDeadActors = new SqliteCommand("INSERT OR REPLACE INTO dead_actors VALUES($actor_id,$name,$death_year);", sqliteConnection, transaction);
34          var actorIdParameter = updateDeadActors.CreateParameter();
35          actorIdParameter.ParameterName = "$actor_id";
36          var actorDeathYearParameter = updateDeadActors.CreateParameter();
37          actorDeathYearParameter.ParameterName = "$death_year";
38          var actorNameParameter = updateDeadActors.CreateParameter();
39          actorNameParameter.ParameterName = "$name";
40
41          updateDeadActors.Parameters.Add(actorIdParameter);
42          updateDeadActors.Parameters.Add(actorDeathYearParameter);
43          updateDeadActors.Parameters.Add(actorNameParameter);
44
45          using (var sr = new StreamReader(@"F:\Downloads\name.basics.tsv"))
46          {
47            while (!sr.EndOfStream)
48            {
49              var line = await sr.ReadLineAsync();
50              var fields = line.Split('\t');
51
52              if (!uint.TryParse(fields[0].Substring(2), out uint actorId))
53              {
54                continue;
55              }
56              
57              if (!uint.TryParse(fields[3], out uint deathYear))
58              {
59                continue;
60              }              
61              actorIdParameter.Value = actorId;
62              actorNameParameter.Value = fields[1];
63              actorDeathYearParameter.Value = deathYear;
64              await updateDeadActors.ExecuteNonQueryAsync();            
65            }
66          }
67
68          SqliteCommand createActorsInMovieTable = new SqliteCommand("DROP TABLE IF EXISTS actors_in_movie; CREATE TABLE actors_in_movie (movie_id INTEGER, actor_id INTEGER)", sqliteConnection, transaction);
69          await createActorsInMovieTable.ExecuteNonQueryAsync();
70
71          SqliteCommand createActorsInMovieIndex = new SqliteCommand("DROP INDEX IF EXISTS actors_in_movie_index; CREATE INDEX actors_in_movie_index ON actors_in_movie(movie_id)", sqliteConnection, transaction);
72          await createActorsInMovieIndex.ExecuteNonQueryAsync();
73
74          SqliteCommand updatePrincipals = new SqliteCommand("INSERT INTO actors_in_movie VALUES($movie_id, $actor_id);", sqliteConnection, transaction);
75          var movieIdParameter = updatePrincipals.CreateParameter();
76          movieIdParameter.ParameterName = "$movie_id";
77          var actorIdParameter2 = updatePrincipals.CreateParameter();
78          actorIdParameter2.ParameterName = "$actor_id";
79
80          updatePrincipals.Parameters.Add(movieIdParameter);
81          updatePrincipals.Parameters.Add(actorIdParameter2);
82          
83          using (var sr = new StreamReader(@"F:\Downloads\title.principals.tsv"))
84          {
85            while (!sr.EndOfStream)
86            {
87              var line = await sr.ReadLineAsync();
88              var fields = line.Split('\t');
89
90              if (fields[3] != "actor" && fields[3] != "actress" && fields[3] != "self")
91              {
92                continue;
93              }
94
95              if (!uint.TryParse(fields[0].Substring(2), out uint titleId))
96              {
97                continue;
98              }
99
100              if (!uint.TryParse(fields[2].Substring(2), out uint actorId))
101              {
102                continue;
103              }
104
105              movieIdParameter.Value = titleId;
106              actorIdParameter2.Value = actorId;
107              await updatePrincipals.ExecuteNonQueryAsync();             
108            }
109          }
110
111          // Maps the type of show to a friendly name
112          var titleTypeMap = new Dictionary<string, string>()
113          {
114            {"movie", "" },
115            {"tvSeries", "[TV]" },
116            {"short", "[Short]" },
117            {"videoGame", "[Video Game]" },
118            {"tvMovie", "[TV Movie]" },
119            {"tvMiniSeries", "[TV Mini-Series]" },
120            {"video", "[Video]" }
121          };
122
123          // This table supports full text search so we can quickly find the movie id from an approximate title
124          SqliteCommand createMovieLookup = new SqliteCommand("DROP TABLE IF EXISTS movie_names; CREATE VIRTUAL TABLE movie_names USING fts4(movie_id INTEGER PRIMARY KEY, name TEXT, display_name TEXT)", sqliteConnection, transaction);
125          await createMovieLookup.ExecuteNonQueryAsync();
126
127          SqliteCommand updateMovieLookup = new SqliteCommand("INSERT INTO movie_names VALUES($movie_id, $name, $display_name);", sqliteConnection, transaction);
128          var movieLookupID = updatePrincipals.CreateParameter();
129          movieLookupID.ParameterName = "$movie_id";
130          var movieLookupName = updatePrincipals.CreateParameter();
131          movieLookupName.ParameterName = "$name";
132          var movieLookupDisplayName = updatePrincipals.CreateParameter();
133          movieLookupDisplayName.ParameterName = "$display_name";
134
135          updateMovieLookup.Parameters.Add(movieLookupID);
136          updateMovieLookup.Parameters.Add(movieLookupName);
137          updateMovieLookup.Parameters.Add(movieLookupDisplayName);
138
139          using (var sr = new StreamReader(@"F:\Downloads\title.basics.tsv"))
140          {
141            while (!sr.EndOfStream)
142            {
143              var line = await sr.ReadLineAsync();
144              var fields = line.Split('\t');
145
146              if (fields[1] == "tvEpisode")
147              {
148                continue; // Skip individual episodes, 'cause there's just too much noise
149              }
150
151              var name = fields[2].ToLower();
152              if (!uint.TryParse(fields[0].Substring(2), out uint titleId))
153              {
154                continue;
155              }
156
157              // Add formatted name
158              var titleType = titleTypeMap.GetValueOrDefault(fields[1], "[Other]");
159              string displayName = string.Format("{0} ({1}) {2}", fields[2], fields[5], titleType);
160
161              movieLookupID.Value = titleId;
162              movieLookupName.Value = name;
163              movieLookupDisplayName.Value = displayName;
164              await updateMovieLookup.ExecuteNonQueryAsync();
165            }
166          }
167
168          transaction.Commit();
169        }
170
171        // One giant join to go through and find all the actors in a given movie, with their name and death
172        // date (only if they are dead). 
173        SqliteCommand getDeadActorsFromMovieName = new SqliteCommand(
174          @"SELECT movie_names.display_name, dead_actors.name, death_year 
175            FROM movie_names  
176            LEFT JOIN actors_in_movie ON movie_names.movie_id = actors_in_movie.movie_id 
177            LEFT JOIN dead_actors ON actors_in_movie.actor_id = dead_actors.actor_id             
178            WHERE movie_names.name MATCH $movie_name", sqliteConnection);
179        var movieNameParameter = getDeadActorsFromMovieName.CreateParameter();
180        movieNameParameter.ParameterName = "$movie_name";
181        getDeadActorsFromMovieName.Parameters.Add(movieNameParameter);
182      
183        for (; ; )
184        {
185          Console.Write("\nEnter movie name: ");
186          var movieName = Console.ReadLine();
187
188          movieNameParameter.Value = movieName.ToLower();
189          using (var resultsQuery = await getDeadActorsFromMovieName.ExecuteReaderAsync())
190          {
191            string lastMovieDisplayName = null;
192            List<Tuple<string, int>> deadActorsInMovie = new List<Tuple<string,int>>();
193            while(await resultsQuery.ReadAsync())
194            {
195              string movieDisplayName = resultsQuery.GetString(0);
196
197              // If the movie display name has changed, then we're in a new movie, so print out the last one
198              if (lastMovieDisplayName != movieDisplayName)
199              {
200                PrintMovieList(lastMovieDisplayName, deadActorsInMovie);
201                lastMovieDisplayName = movieDisplayName;
202                deadActorsInMovie.Clear();
203              }
204
205              try
206              {
207                string actorName = resultsQuery.GetString(1);
208                int deathYear = resultsQuery.GetInt32(2);
209                deadActorsInMovie.Add(Tuple.Create(actorName, deathYear));                     
210              }
211              catch (System.InvalidOperationException)
212              {
213                continue; // Invalid row, must not have any dead actors for this movie
214              }
215            }
216
217            // Print out last movie too
218            PrintMovieList(lastMovieDisplayName, deadActorsInMovie);            
219          }          
220        }
221      }
222
223    }
224
225    private static void PrintMovieList(string lastMovieDisplayName, List<Tuple<string, int>> deadActorsInMovie)
226    {
227      if (lastMovieDisplayName != null)
228      {
229        Console.WriteLine("{0}", lastMovieDisplayName);
230
231        // Print last list
232        if (deadActorsInMovie.Count == 0)
233        {
234          Console.WriteLine("    No dead actors");
235        }
236        else
237        {
238          foreach (var a in deadActorsInMovie)
239          {
240            Console.WriteLine("    {0} (dead: {1})", a.Item1, a.Item2);
241          }
242        }
243      }
244    }
245  }
246}