SQL queries & updates directly with sqlite3 databases

Hey, good evening, and thank you for GRAMPS!!
I can access to my sqlite3 database sqlite.db, it’s OK
But when i change something with sql, ie
update person set surname=UPPER(surname); it’s OK
But when i open Gramps with this database, no changes!!!
Someboby knows how to modify the database with sqlite?
Best regards
Fred

Welcome!

Not sure you’re approaching the issue the most efficient option. Have you tried changing the Name Display formatting in Gramps Preferences? Maybe you don’t need to UPPER() the surname, you could do it dynamically via the Display Name Editor formatting.

But that aside, I suspect that commits inside Gramps set a dirty flag. The Gramps does a cascade of validations & updates.

And as a final distraction, have you explored the Query Gramplet add-on? It seems like a good toy for those with SQL experience that they want to use from inside the Gramps GUI. (Although there are a lot of enthusiastic SuperTool users too.)

Have you used commit to save the changes?

2 Likes

Hey Fred,

Do you have specific goals for your SQL operations?

You’ve probably already noticed that most columns in our database are blobs, meaning that you can’t do much with direct database access.

This is why I hack .gramps backup files. They’re easy to read with a little bit of C#, even on Linux, using Visual Studio Code. With that, I can write a modified .gramps file, and import that in a new tree. And that’s worth it for the mods that I like to make.

Tank you very much, Enno, i am going to install VS!
Best regards
Fred

Frédéric Ethève :grinning:

On Linux or Windows?

I have a simple program that reads a compressed .gramps file, modifies it, and writes the result to an uncompressed output file, but the latest is not on GitHub yet. I usually run that on Linux, with Microsoft’s .NET tools.

Thank you, Brian!!!
I 'like to have this query gramplet!!!
I ve downloaded it, unzipped ans copied into the plugins directory ,then restarted gramps…
(On linux and windows10)
Nothing, please how to enable it?
Best regards
Fred

And as a final distraction, have you explored the Query Gramplet add-on? It seems like a good toy for those with SQL experience that they want to use from inside the Gramps GUI. (Although there are a lot of enthusiastic SuperTool users too.)

Frédéric Ethève :grinning:

1 Like

Thank you Matthias!
Yes i’ve done a commit and my sqlite.db is updated.
But when i start gramps, no changes!!!
Best regards
Fred

Frédéric Ethève :grinning:

Gramplets can be view specific but the Query Gramplet is generic. It can be opened in the dashboard view category or splitbar (i.e., the sidebar or bottombar) of any view category.

Since the Query gramplet is used to affect records, the Dashboard probably is not the optimum choice. For better feedback, use the view corresponding to the category of records you will be querying. i.e., if you are Querying Note records, use the sidebar/bottombar of the Note view category.

(If the sidebar/bottombar is not displayed in the category where you wish to use a Gramplet, use the View menu or to enable it. The only reason to use one splitbar over the other is if the layout of the Gramplet fits more easily within maximized vertical or horizontal space.)

Clicking the button add the right of the Gramplet bar menu offers an Add a Gramplet submenu with an alphabetically sorted list of Gramplets available for that view.

Fred: You note several times that changes in the sqlite.db file don’t seem to show any changes in Gramps.

This is because Gramps internals stores all its data in blobs; the extra columns and fields in sqlite db are there strictly to allow certain types of lookup more efficiently. So changing any of those fields (other than the blob) doesn’t change Gramps data (you could corrupt the data by messing with handle, ID, or a few other fields).

2 Likes

Thank you, Paul, i understand!!
So, to upper or modify my surnames , 2 ways :

  • install Query gramplet, or
  • export a gedcom file, modify them and reimport… ?
    Best regards
    Fred

Frédéric Ethève :grinning:
+33 6 70 75 56 86

-------- Message d’origine --------

If you are going this route, it is always best to use the .gramps XML backup file. This is the native Gramps standard and if you are going from Gramps to Gramps it’s best to stay with Gramps.

You can create a .gramps (non-media) XML file using export. This method allows for the option to not compress the file. As always with export, make sure there are no filters in the Export options screen.

  • Export to xml, not compressed.

  • Open the file in VSCODE (or similar)

Use regex to find and replace anything you want to change (search for the xml key before the surnames you want to change).

Save the changed file and import it to a new Gramps database.

Someone else need to help you with the correct regex “code”.’

1 Like