update db

Du code pour relire le dossier des kara et mettre à jour la bdd sqlite.

On pourra appeler ce code par une commande (eg. update) pour ajouter les karas dynamiquement.

dépend de !1 (merged)

todolist :

• update_directory realloc quand la queue est pleine
• update_file
• astyle

Merged dans client-cmd car l'update fait partie des commandes possibles par un client.

player/db/update.c

@@ -5,8 +5,22 @@
 #include <stdio.h>
 #include <stdlib.h>
 #include <string.h>
+#include <sys/stat.h>
 
-#define SQL_LAST_UPDATE "SELECT last_update FROM misc;"
+static const char SQL_LAST_UPDATE[] = "SELECT last_update FROM misc;";
+static const char SQL_INSERT_OLD_KARA[] =
+        "INSERT INTO "
+        "kara (song_name, source_name, category, language, file_path, is_new) "
+        "VALUES (?, ?, ?, ?, ?, 0)";
+static const char SQL_INSERT_NEW_KARA[] =
+        "INSERT INTO "
+        "kara (song_name, source_name, category, language, file_path,"
+        "      is_new, author_name) "
+        "VALUES (?, ?, ?, ?, ?, 1, ?)";
+
+static void
+convert_legacy_category_type(const char *category, size_t category_len,
+                             const char *type, size_t type_len);
 
 static void
 serror(sqlite3 *db, const char *msg)
@@ -23,24 +37,131 @@ mallocf(size_t n)
     return res;
 }
 
+static inline void *
+reallocf(void *ptr, size_t n)
+{
+    void *res = realloc(ptr, n);
+    if (!res)
+        fprintf(stderr, "Failed to allocate %ld bytes.\n", n);
+    return res;
+}
+
 static int
 directories_and_files(const struct dirent *entry)
 {
-    return entry->d_type == DT_REG || entry->d_type == DT_DIR;
+    return (entry->d_type == DT_REG || entry->d_type == DT_DIR) &&
+            entry->d_name[0] != '.';
+}
+
+static void
+convert_legacy_category_type(const char *category, size_t category_len,
+                             const char *type, size_t type_len)
+{
+    // TODO convert category and type -> category and language
 }
 
 static int
-update_file(sqlite3 *db, const char *filename, int last_update)
+update_legacy_file(sqlite3 *db, const char *filename, size_t prefix, time_t last_update)
 {
-    // TODO
-    return 0;
+    struct stat attrs;
+    if (stat(filename, &attrs) < 0)
+        return -1;
+    if (attrs.st_mtim.tv_sec < last_update)
+        return 0;
+
+    const char *pseudo = 0;
+    size_t pseudo_len = 0;
+    const char *category = 0;
+    size_t category_len = 0;
+    const char *name = 0;
+    size_t name_len = 0;
+    const char *type = 0;
+    size_t type_len = 0;
+    const char *title = 0;
+    size_t title_len = 0;
+
+    const char *file_path = filename + prefix;
+    const char *f = file_path;
+    category_len = strcspn(f, "/");
+    if (!strncmp(f, "nouveaux", category_len)) {
+        // "Nouveau" kara, in nouveaux/{pseudo}/{category}/...
+        f += category_len + 1;  // Skip "nouveaux/"
+        pseudo_len = strcspn(f, "/");
+        pseudo = f;
+        f += pseudo_len + 1;  // Skip "{pseudo}/"
+        category_len = strcspn(f, "/");
+    }
+
+    category = f;
+    f += category_len + 1;  // Skip "{category}/"
+    name_len = strcspn(f, "-");
+    name = f;
+    f += name_len + 1;
+    type_len = strcspn(f, "-");
+    type = f;
+    f += type_len + 1;
+    title_len = strcspn(f, ".");
+    title = f;
+
+    if (!title) {
+        fprintf(stderr, "Bad file path: '%s'.\n", filename);
+        return -1;
+    }
+
+    convert_legacy_category_type(category, category_len, type, type_len);
+
+    int status_code = -1;
+    sqlite3_stmt *stmt = 0;
+    const char *sql = pseudo ? SQL_INSERT_NEW_KARA : SQL_INSERT_OLD_KARA;
+
+    if (sqlite3_prepare_v2(db, sql, -1, &stmt, 0) != SQLITE_OK) {
+        serror(db, "Failed to prepare statement");
+        goto error;
+    }
+
+    if (sqlite3_bind_text(stmt, 0, name, name_len, 0) != SQLITE_OK) {
+        serror("Failed to bind song_name");
+        goto error;
+    }
+
+    if (sqlite3_bind_text(stmt, 0, title, title_len, 0) != SQLITE_OK) {
+        serror("Failed to bind song_source");
+        goto error;
+    }
+
+    if (sqlite3_bind_text(stmt, 0, category, -1, 0) != SQLITE_OK) {
+        serror("Failed to bind category");
+        goto error;
+    }
+
+    if (sqlite3_bind_text(stmt, 0, type, -1, 0) != SQLITE_OK) {
+        serror("Failed to bind language");
+        goto error;
+    }
+
+    if (sqlite3_bind_text(stmt, 0, file_path, -1, 0) != SQLITE_OK) {
+        serror("Failed to bind file_path");
+        goto error;
+    }
+
+    if (sqlite3_step(stmt) != SQLITE_DONE) {
+        // TODO handle SQLITE_BUSY (should rollback)
+        serror("Failed to execute insert statement");
+        goto error;
+    }
+
+    status_code = 0;
+error:
+    sqlite3_finalize(stmt);
+    return status_code;
 }
 
 static int
-update_directory(sqlite3 *db, const char *directory, int last_update)
+update_directory(sqlite3 *db, const char *root, time_t last_update)
 {
     int status_code = -1;
-    char **queue;
+    const size_t root_len = strlen(root) + 1;
+    char **queue = 0;
     size_t queue_max = 128;
     size_t queue_len = 0;
 
@@ -48,7 +169,7 @@ update_directory(sqlite3 *db, const char *directory, int last_update)
     if (!queue)
         goto error;
 
-    queue[0] = strdup(directory);
+    queue[0] = strdup(root);
     queue_len++;
 
     while (queue_len) {
@@ -61,18 +182,28 @@ update_directory(sqlite3 *db, const char *directory, int last_update)
             fprintf(stderr, "Failed to open '%s': %s.\n", dir, strerror(errno));
 
         for (int i = 0; i < n; i++) {
-            size_t dirlen = strlen(dir);
-            char *child = mallocf(dirlen + 258);  // child length (256) + null byte + slash
+            size_t dir_len = strlen(dir);
+
+            // child length (256) + null byte + slash
+            char *child = mallocf(dir_len + 258);
             if (!child)
                 goto error;
 
-            strcpy(child, dir);
-            child[dirlen] = '/';
-            strcpy(child + dirlen + 1, children[i]->d_name);
+            memcpy(child, dir, dir_len);
+            child[dir_len] = '/';
+            strcpy(child + dir_len + 1, children[i]->d_name);
 
             if (children[i]->d_type == DT_REG) {
-                update_file(db, child, last_update);
+                if (update_legacy_file(db, child, root_len, last_update) < 0)
+                    fprintf(stderr, "Failed to add '%s'.\n", child);
                 free(child);
+            } else if (queue_len == queue_max) {
+                queue_max *= 2;
+                char **new_queue = reallocf(queue, queue_max * sizeof(char *));
+                if (!new_queue)
+                    goto error;
+                queue = new_queue;
+                queue[queue_len++] = child;
             } else {
                 queue[queue_len++] = child;
             }
@@ -96,7 +227,7 @@ lektor_db_update(sqlite3 *db, const char *directory)
     }
 
     sqlite3_stmt *stmt = 0;
-    int last_update = 0;
+    time_t last_update = 0;
 
     if (sqlite3_prepare_v2(db, SQL_LAST_UPDATE, -1, &stmt, 0) != SQLITE_OK) {
         serror(db, "Failed to get last update time");
@@ -104,7 +235,7 @@ lektor_db_update(sqlite3 *db, const char *directory)
     }
     switch (sqlite3_step(stmt)) {
     case SQLITE_ROW:
-        last_update = sqlite3_column_int(stmt, 0);
+        last_update = (time_t) sqlite3_column_int(stmt, 0);
         break;
     case SQLITE_DONE:
         fprintf(stderr, "Failed to get last update time: table misc is empty");