+ Statement aka_statement = null;
+ public int add_aka (string title, string aka) {
+ int rc;
+ int rowid;
+
+ if (!movie_exists (title, out rowid))
+ return 1;
+
+ if (aka_statement == null) {
+ string sql = "INSERT INTO Akas(Aka, TitleID) VALUES (?, ?);";
+ assert (db.prepare_v2 (sql, -1, out aka_statement) == Sqlite.OK);
+ }
+
+ assert (aka_statement.bind_text (1, aka) == Sqlite.OK);
+ assert (aka_statement.bind_int (2, rowid) == Sqlite.OK);
+
+ rc = aka_statement.step ();
+ if (rc != Sqlite.DONE) {
+ stderr.printf ("Failed to add aka \"%s\" to \"%s\": %d, %s\n", aka, title, rc, db.errmsg ());
+ aka_statement.reset ();
+ return 1;
+ }
+ aka_statement.reset ();
+
+ return 0;
+ }
+
+ Statement plot_statement = null;
+ public int add_plot (string title, string plot, string? author) {
+ int rc;
+ int rowid;
+
+ if (!movie_exists (title, out rowid))
+ return 1;
+
+ if (plot_statement == null) {
+ var sql = "INSERT INTO Plots(rowid, Plot, Author) VALUES (?, ?, ?);";
+ assert (db.prepare_v2 (sql, -1, out plot_statement) == Sqlite.OK);
+ }
+
+ assert (plot_statement.bind_int (1, rowid) == Sqlite.OK);
+ assert (plot_statement.bind_text (2, plot.replace ("\"", """)) == Sqlite.OK);
+ if (author != null)
+ assert (plot_statement.bind_text (3, author) == Sqlite.OK);
+ else
+ assert (plot_statement.bind_null (3) == Sqlite.OK);
+
+ rc = plot_statement.step ();
+ if (rc != Sqlite.DONE) {
+ stderr.printf ("Failed to add plot for \"%s\": %d, %s\n", title, rc, db.errmsg ());
+ plot_statement.reset ();
+ return 1;
+ }
+ plot_statement.reset ();
+
+ return 0;
+ }
+
+ Statement person_statement = null;
+ public int add_person (string name) {
+ int rc;
+
+ if (person_statement == null) {
+ string sql = "INSERT INTO People(Name) VALUES (?);";
+ rc = db.prepare_v2 (sql, -1, out person_statement);
+ if (rc != Sqlite.OK) {
+ error ("Failed to prepare statement \"" + sql + "\": " + db.errmsg ());
+ }
+ }
+
+ assert (person_statement.bind_text (1, name) == Sqlite.OK);
+
+ rc = person_statement.step ();
+ if (rc != Sqlite.DONE) {
+ if (rc == Sqlite.CONSTRAINT)
+ stderr.printf ("Skipped duplicate person \"%s\"\n", name);
+ else
+ stderr.printf ("Failed to insert person \"%s\": %d, %s\n", name, rc, db.errmsg ());
+ person_statement.reset ();
+ return 1;
+ }
+ person_statement.reset ();
+
+ return 0;
+ }
+
+ Statement actor_statement;
+ public int add_actor (string name, string title, string? info, string? character, int number = 0) {
+ int actorid;
+ int titleid;
+ int rc;
+
+ if (!person_exists (name, out actorid))
+ return 1;
+ if (!movie_exists (title, out titleid))
+ return 1;
+
+ if (actor_statement == null) {
+ string sql = "INSERT INTO Actors(ActorID, TitleID, Info, Character, Number) VALUES (?, ?, ?, ?, ?);";
+ assert (db.prepare_v2 (sql, -1, out actor_statement) == Sqlite.OK);
+ }
+
+ assert (actor_statement.bind_int (1, actorid) == Sqlite.OK);
+ assert (actor_statement.bind_int (2, titleid) == Sqlite.OK);
+ if (info != null)
+ assert (actor_statement.bind_text (3, info) == Sqlite.OK);
+ else
+ assert (actor_statement.bind_null (3) == Sqlite.OK);
+
+ if (character != null)
+ assert (actor_statement.bind_text (4, character) == Sqlite.OK);
+ else
+ assert (actor_statement.bind_null (4) == Sqlite.OK);
+ assert (actor_statement.bind_int (5, number) == Sqlite.OK);
+
+ rc = actor_statement.step ();
+ if (rc != Sqlite.DONE) {
+ stderr.printf ("Failed to insert actor \"%s\": %d, %s\n", name, rc, db.errmsg ());
+ actor_statement.reset ();
+ return 1;
+ }
+ actor_statement.reset ();
+
+ return 0;
+
+ }
+
+ Statement exists_statement = null;
+ public bool movie_exists (string title, out int rowid = null) {
+ if (exists_statement == null) {
+ string sql = "SELECT rowid FROM Movies WHERE Title=?";
+ assert (db.prepare_v2 (sql, -1, out exists_statement) == Sqlite.OK);
+ }
+
+ assert (exists_statement.bind_text (1, title) == Sqlite.OK);
+
+ int rc = Sqlite.OK;
+ do {
+ rc = exists_statement.step ();
+ if (rc == Sqlite.ROW) {
+ if (&rowid != null) {
+ rowid = exists_statement.column_int (0);
+ }
+ exists_statement.reset ();
+ return true;
+ }
+ } while (rc == Sqlite.ROW);
+ exists_statement.reset ();
+
+ return false;
+ }
+
+ Statement person_exists_statement = null;
+ public bool person_exists (string name, out int rowid = null) {
+ if (person_exists_statement == null) {
+ string sql = "SELECT rowid FROM People WHERE Name=?";
+ assert (db.prepare_v2 (sql, -1, out person_exists_statement) == Sqlite.OK);
+ }
+
+ assert (person_exists_statement.bind_text (1, name) == Sqlite.OK);
+
+ int rc = Sqlite.OK;
+ do {
+ rc = person_exists_statement.step ();
+ if (rc == Sqlite.ROW) {
+ if (&rowid != null) {
+ rowid = person_exists_statement.column_int (0);
+ }
+ person_exists_statement.reset ();
+
+ return true;
+ }
+ } while (rc == Sqlite.ROW);
+ person_exists_statement.reset ();
+
+ return false;
+ }
+
+ public int count (string table) {
+ string sql = "SELECT count(*) FROM %s".printf (table);
+ Statement stmt;
+ int rc;
+ int count = 0;
+
+ rc = db.prepare_v2 (sql, -1, out stmt);
+ if (rc != Sqlite.OK) {
+ stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
+ db.progress_handler (0, null);
+ return 0;
+ }
+
+ do {
+ rc = stmt.step ();
+ if (rc == Sqlite.ROW) {
+ count = stmt.column_int (0);
+ }
+ } while (rc == Sqlite.ROW);
+
+ return count;
+ }
+
+ public bool has_table (string table) {
+ string sql = "SELECT * FROM sqlite_master WHERE type=\"table\" AND name=\"%s\"".printf (table);
+ Statement stmt;
+ int rc;
+
+ rc = db.prepare_v2 (sql, -1, out stmt);
+ if (rc != Sqlite.OK) {
+ stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
+ db.progress_handler (0, null);
+ return false;
+ }
+
+ do {
+ rc = stmt.step ();
+ if (rc == Sqlite.ROW)
+ return true;
+ } while (rc == Sqlite.ROW);
+
+ return false;
+ }
+
+ public string get_plot (string title) {
+ string sql = "SELECT Plot FROM Plots WHERE rowid in (SELECT rowid FROM Movies WHERE Title=\"%s\")".printf (title);
+ Statement stmt;
+ int rc;
+ int count = 0;
+
+ rc = db.prepare_v2 (sql, -1, out stmt);
+ if (rc != Sqlite.OK) {
+ stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
+ db.progress_handler (0, null);
+ return "";
+ }
+
+ do {
+ rc = stmt.step ();
+ if (rc == Sqlite.ROW) {
+ return stmt.column_text (0).replace (""", "\"");
+ }
+ } while (rc == Sqlite.ROW);
+
+ return "";
+ }
+
+ public List<Role> get_cast (string title) {
+ int rowid;
+ string sql = "SELECT Name,Character FROM Actors,People WHERE TitleID IN (SELECT rowid FROM Movies WHERE Title=\"%s\") AND ActorID=People.rowid AND Number>0 ORDER BY NUMBER LIMIT 3".printf (title);
+ Statement stmt;
+ int rc;
+ int count = 0;
+ var cast = new List<Role> ();
+
+ if (!movie_exists (title, out rowid))
+ return null;
+
+ rc = db.prepare_v2 (sql, -1, out stmt);
+ if (rc != Sqlite.OK) {
+ stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
+ return null;
+ }
+
+ do {
+ rc = stmt.step ();
+ if (rc == Sqlite.ROW) {
+ var role = new Role ();
+ role.actor_name = stmt.column_text (0);
+ role.character = stmt.column_text (1);
+ cast.append (role);
+ }
+ } while (rc == Sqlite.ROW);
+
+ return cast;
+ }
+