first_run usage

[mvallevand]

A utility that parsed that metadata would be better rather than a hack but it won't be perfect. You can increase the length of the substring to capture more uniqueness but I wouldn't count on that duration being unique. If you browse this you will see what I mean

Code:

select title, count(title), substring(description,instr(description,'episode-num:')+13,8) as key1,
substring(description,instr(description,' length: ')+9,3) as key2, description
from epg_event
where description like '%episode-num:%'
group by title,key1, key2
order by title;

Martin

[janoonk]

The query `update_unique_id.sql` to update `unique_id` column is run in `nextpvr\config\scripts\PostLoadEPG.sh`:

Code:

cd /nextpvr/config/scripts
sqlite3 ../npvr.db3 < update_unique_id.sql

`update_unique_id.sql` is:

Code:

-- Enable extension loading (sometimes needed)
PRAGMA load_extension = 1;

-- Load your compiled extension (adjust path as needed)
.load 'crypto.so'

WITH epg_with_epnum AS (
  SELECT
    oid,
    title,
    channel_oid,
    description,
    TRIM(
      CASE
        WHEN description LIKE '%[%' THEN
          SUBSTR(description, 1, INSTR(description, '[') - 1)
        ELSE
          description
      END
    ) AS clean_description,
    start_time,
    TRIM(
      RTRIM(
        SUBSTR(
          description,
          INSTR(description, 'episode-num:') + LENGTH('episode-num:'),
          INSTR(SUBSTR(description, INSTR(description, 'episode-num:')), ',') - LENGTH('episode-num:')
        ),
        ', '
      )
    ) AS episode_num
  FROM EPG_EVENT
  WHERE TRIM(description) LIKE '%[%episode-num:%]'
),
epg_with_epnum_and_simplified_cleaned_description AS (
  SELECT *,
        LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
          clean_description, ' ', ''), ',', ''), '.', ''), '!', ''), '?', ''), '''', ''), '-', ''), '_', ''), ':', ''), ';', '')
        ) AS simplified_cleaned_description
  FROM epg_with_epnum
),
final_data AS (
  SELECT
    oid,
    title || '-' || md5(simplified_cleaned_description) || '-' || episode_num AS unique_id
  FROM epg_with_epnum_and_simplified_cleaned_description
)
UPDATE EPG_EVENT
SET unique_id = (
  SELECT unique_id
  FROM final_data
  WHERE final_data.oid = EPG_EVENT.oid
)
WHERE oid IN (SELECT oid FROM final_data);

The query uses sqlite3 app and query uses md5() which is not natively supported.

Installation of SQLite inside NextPVR container:

Code:

sudo docker exec -it nextpvr /bin/bash
apt update
apt install sqlite3

SQLite crypto extension is used to have access to hash functions like md5() or sha1()
When you want to build crypto.c into crypto.so yourself:

Code:

apt install gcc make libc6-dev libsqlite3-dev libssl-dev
gcc -fPIC -shared -o crypto.so crypto.c -I/usr/include/sqlite3 -lcrypto

crypto.c source since I could not find a reliable download site:

Code:

/*
** 2018-04-06
**
** The author disclaims copyright to this source code.
**
** This is an SQLite extension implementing SQL functions that
** compute a variety of hash algorithms: md5(), sha1(), sha256(),
** sha384(), sha512(), hmac_md5(), hmac_sha1(), hmac_sha256(), hmac_sha384(),
** hmac_sha512(), and hex().
*/

#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1

#include <string.h>
#include <stdlib.h>
#include <openssl/md5.h>
#include <openssl/sha.h>
#include <openssl/hmac.h>

/*
** Helper function to convert binary data to a hex string.
*/
static void toHex(const unsigned char *in, int len, char *out){
  static const char hexDigits[] = "0123456789abcdef";
  int i;
  for(i=0; i<len; i++){
    out[(i<<1)] = hexDigits[(in[i]>>4)&0xF];
    out[(i<<1)+1] = hexDigits[in[i]&0xF];
  }
  out[len<<1] = 0;
}

/*
** Compute a digest using a given hash function.
*/
static void digestFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv,
  const EVP_MD *(*hash_func)(void)
){
  if(argc!=1){
    sqlite3_result_null(context);
    return;
  }

  const unsigned char *data = sqlite3_value_text(argv[0]);
  if(data==NULL){
    sqlite3_result_null(context);
    return;
  }

  unsigned char md[EVP_MAX_MD_SIZE];
  unsigned int md_len = 0;
  EVP_MD_CTX *mdctx = EVP_MD_CTX_new();
  if(mdctx==NULL){
    sqlite3_result_error_nomem(context);
    return;
  }

  EVP_DigestInit_ex(mdctx, hash_func(), NULL);
  EVP_DigestUpdate(mdctx, data, strlen((const char*)data));
  EVP_DigestFinal_ex(mdctx, md, &md_len);
  EVP_MD_CTX_free(mdctx);

  char hex[EVP_MAX_MD_SIZE*2+1];
  toHex(md, md_len, hex);
  sqlite3_result_text(context, hex, -1, SQLITE_TRANSIENT);
}

/*
** Compute HMAC digest.
*/
static void hmacFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv,
  const EVP_MD *(*hash_func)(void)
){
  if(argc!=2){
    sqlite3_result_null(context);
    return;
  }

  const unsigned char *key = sqlite3_value_text(argv[0]);
  const unsigned char *data = sqlite3_value_text(argv[1]);
  if(key==NULL || data==NULL){
    sqlite3_result_null(context);
    return;
  }

  unsigned char md[EVP_MAX_MD_SIZE];
  unsigned int md_len = 0;

  unsigned char *result = HMAC(hash_func(), key, strlen((const char*)key), data, strlen((const char*)data), md, &md_len);
  if(result==NULL){
    sqlite3_result_null(context);
    return;
  }

  char hex[EVP_MAX_MD_SIZE*2+1];
  toHex(md, md_len, hex);
  sqlite3_result_text(context, hex, -1, SQLITE_TRANSIENT);
}

/* MD5 */
static void md5Func(sqlite3_context *context, int argc, sqlite3_value **argv){
  digestFunc(context, argc, argv, EVP_md5);
}

/* SHA1 */
static void sha1Func(sqlite3_context *context, int argc, sqlite3_value **argv){
  digestFunc(context, argc, argv, EVP_sha1);
}

/* SHA256 */
static void sha256Func(sqlite3_context *context, int argc, sqlite3_value **argv){
  digestFunc(context, argc, argv, EVP_sha256);
}

/* SHA384 */
static void sha384Func(sqlite3_context *context, int argc, sqlite3_value **argv){
  digestFunc(context, argc, argv, EVP_sha384);
}

/* SHA512 */
static void sha512Func(sqlite3_context *context, int argc, sqlite3_value **argv){
  digestFunc(context, argc, argv, EVP_sha512);
}

/* HMAC MD5 */
static void hmacMd5Func(sqlite3_context *context, int argc, sqlite3_value **argv){
  hmacFunc(context, argc, argv, EVP_md5);
}

/* HMAC SHA1 */
static void hmacSha1Func(sqlite3_context *context, int argc, sqlite3_value **argv){
  hmacFunc(context, argc, argv, EVP_sha1);
}

/* HMAC SHA256 */
static void hmacSha256Func(sqlite3_context *context, int argc, sqlite3_value **argv){
  hmacFunc(context, argc, argv, EVP_sha256);
}

/* HMAC SHA384 */
static void hmacSha384Func(sqlite3_context *context, int argc, sqlite3_value **argv){
  hmacFunc(context, argc, argv, EVP_sha384);
}

/* HMAC SHA512 */
static void hmacSha512Func(sqlite3_context *context, int argc, sqlite3_value **argv){
  hmacFunc(context, argc, argv, EVP_sha512);
}

/* HEX */
static void hexFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
  if(argc!=1){
    sqlite3_result_null(context);
    return;
  }
  const unsigned char *data = sqlite3_value_text(argv[0]);
  if(data==NULL){
    sqlite3_result_null(context);
    return;
  }
  int len = strlen((const char*)data);
  char *hex = (char*)sqlite3_malloc(len*2+1);
  if(hex==NULL){
    sqlite3_result_error_nomem(context);
    return;
  }
  toHex(data, len, hex);
  sqlite3_result_text(context, hex, -1, SQLITE_TRANSIENT);
  sqlite3_free(hex);
}

int sqlite3_crypto_init(
  sqlite3 *db,
  char **pzErrMsg,
  const sqlite3_api_routines *pApi
){
  SQLITE_EXTENSION_INIT2(pApi);
  sqlite3_create_function(db, "md5", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, md5Func, 0, 0);
  sqlite3_create_function(db, "sha1", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, sha1Func, 0, 0);
  sqlite3_create_function(db, "sha256", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, sha256Func, 0, 0);
  sqlite3_create_function(db, "sha384", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, sha384Func, 0, 0);
  sqlite3_create_function(db, "sha512", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, sha512Func, 0, 0);
  sqlite3_create_function(db, "hmac_md5", 2, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, hmacMd5Func, 0, 0);
  sqlite3_create_function(db, "hmac_sha1", 2, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, hmacSha1Func, 0, 0);
  sqlite3_create_function(db, "hmac_sha256", 2, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, hmacSha256Func, 0, 0);
  sqlite3_create_function(db, "hmac_sha384", 2, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, hmacSha384Func, 0, 0);
  sqlite3_create_function(db, "hmac_sha512", 2, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, hmacSha512Func, 0, 0);
  sqlite3_create_function(db, "hex", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, hexFunc, 0, 0);
  return SQLITE_OK;
}