Writing a string to RM8's BLOB
My Python script was built to compare the Bibliography with the ShortFootnote and decide whether to erase either or both. What I see in my live RM8 database is stuff which, IMO, is not what I'd call a Bibliography. Mostly it's a copy of the ShortFootnote.
Examining the results of the Python updates to the test database with SQLite Expert looks good to me ... all the HTML like it was but with my <Value> changes. And RootsMagic 8 appears to be happy with the changes. It displays and reports the 3 fields in the BLOB correctly.
My SQL knowledge is rudimentary, and I don't know how to do what I want in SQL. Is writing a STRING to a column defined as BLOB OK, i.e., I don't have to 'binaryize' it?
Your suggestions?
The Free-form source type has invisible, default sentence templates, similar to this in the sentence template language:
Footnote sentence = [Footnote]; [Page].
Short Footnote = <[ShortFootnote]|[Footnote]>; [Page].
Bibliography = <[Bibliography]|[Footnote]>.
Designed, I think, as an expedient on which the user could edit the Short Footnote and Bibliography down to what they want.
Sqlite does not enforce Type and in earlier versions than RM8, it was my impression that the RM application was not consistent. Certainly, I was not with my scripts and I've seen both blob and text in a column for a given table with nothing adverse. I was surprised to see RM8 forcing such a column to all blob. I don't know if there is any adverse consequence to it becoming text. Any SQLite string function operating on a blob outputs text. You have to CAST it to get it back to BLOB.
Feel free to share your Python scripts here. I'm sure there will be interest, me being one.
Thanks, Tom. Although Python3 strings appear to work well in the Fields BLOB column, I wasn't comfortable doing it that way. I'll convert the strings back into the encoding it had on input. I can't find a way in SQLite3 to CAST a Python3 string variable to BLOB, but I do have a way to binaryize the string with the proper ASCII encoding - just takes a few more statements, and SQLite3 and RM8 are happy with it. I'll post the appropriate code after some more testing.
Tom, here's some code copied from my Python program which may be relevant
to RM8 Python/SQLite3 users. I use Python 3.8.5. with PyCharm 2021.2 editor.
rm_database = get_rootsmagic_database_file()
db_name = os.path.split(rm_database)
# strip the RootsMagic filetype (.rmtree) from the full filename
just_dbname = os.path.splitext(db_name[1])
# Open the sqlite database. "uri" means "Uniform Resource Identifier"
connection = _sqlite3.connect(rm_database, uri=True)
cursor = connection.cursor()
# enable the loading of SQlite3 extensions so I can enable 'unifuzz64.dll'
connection.enable_load_extension(True)
# load the unifuzz extension. unifuzz has to be in the same folder as the RM8 database.
connection.load_extension(db_name[0] + '/unifuzz64.dll')
# Get all SourceTable rows source ID, source name and Fields BLOB which do not use a template.
cursor.execute('select sourceID, name, cast(fields as TEXT) from SourceTable where templateID = 0')
SQL_sources = cursor.fetchall() # easier for me this way
-------------- do stuff -------------------
for source in SQL_sources:
------ do more stuff to create final fields - the string form -----
# Converter functions are always passed a 'bytes' object, no matter the underlying
# SQLite data type.
SQL_blob = sqlite3.Binary(bytes(final_fields, "UTF-8"))
# Update the SQL SourceTable row 'SourceID', column 'Fields'.
# The qmark ? is a placeholder for the DB-API interface for variable data.
# The Python variable with my binary BLOB data is supplied in the 'execute' command.
sql_command = "UPDATE SourceTable SET Fields=? where sourceid=" + str(source_ID)
# Use DB-API 2.0 interface second parameter to supply variable data.
# The second parameter of the 'execute' has to be a Python tupple, therefore
# the variable name is enclosed in parenthesis followed by a comma ,
cursor.execute(sql_command, (SQL_blob,))
connection.commit() # save the changes