I am not entirely clear on the use case for a mirrored Toolhub database, but why not just repeat the full import periodically instead of attempting to build a changes-only sync? Do you have any empirical data showing that a full import would somehow be too slow for your needs?

In T329029#8594388, @bd808 wrote:

I am not entirely clear on the use case for a mirrored Toolhub database, but why not just repeat the full import periodically instead of attempting to build a changes-only sync? Do you have any empirical data showing that a full import would somehow be too slow for your needs?

It's not really going to be mirrored; we're gathering all of the information but only storing a subset. Even then, having our own database isn't strictly necessary -- I did some work with the Toolhub API last month and determined that we could achieve our primary goals without one -- but having the data configured and stored in the way I envision should make it easier to generate the information that we're intending to display, and to implement a few things that we hope to add somewhere down the line.

Regarding updates: the import speed was a concern, but we don't actually have any data to support that assertion. It's worth reconsidering updating via a full import; it would simplify matters considerably. I'll bring it up at the team meeting on Thursday.

I've implemented the simplified approach in a branch: database_entry_testing (WIP/proof-of-concept)

To check it:

1. Do all of the installation/database initialization steps as outlined in the README.

2. From the command line: docker exec -it flask-web flask shell Or, from Docker desktop, access the flask container, select the terminal and enter flask shell

3. Inside the shell:

from api.jobs.insertion import *
check_for_entry(pywikibot)

4. Access the database, SELECT * FROM task;

I haven't fully tested/reviewed this yet, but just a note that this won't work if there already are entries in the db, i.e if you try to insert an entry for pywikibot and there already is one, an error will be thrown rather than the entry being updated.

In T329029#8603968, @Slst2020 wrote:

I haven't fully tested/reviewed this yet, but just a note that this won't work if there already are entries in the db, i.e if you try to insert an entry for pywikibot and there already is one, an error will be thrown rather than the entry being updated.

I know! I check for an entry first and then try to add one. 😄 But it occurs to me that it is possible to update a tool's description, title and URL, so I should probably include an update check, too.

In T329029#8604014, @NicoleLBee wrote:

In T329029#8603968, @Slst2020 wrote:

I haven't fully tested/reviewed this yet, but just a note that this won't work if there already are entries in the db, i.e if you try to insert an entry for pywikibot and there already is one, an error will be thrown rather than the entry being updated.

I know! I check for an entry first and then try to add one. 😄 But it occurs to me that it is possible to update a tool's description, title and URL, so I should probably include an update check, too.

https://docs.sqlalchemy.org/en/20/dialects/mysql.html#insert-on-duplicate-key-update-upsert might be helpful. Sqlalchemy's MySQL specific insert handler supports an on_duplicate_key_update modifier that lets you tell the backend MySQL/MariaDB how to handle a primary key conflict by updating the existing row rather than erroring. This is the MySQL/MariaDB non-standard equivalent of the SQL:2003 "MERGE" statement. This pattern is also commonly called an "upsert" (a portmanteau of update and insert).

In T329029#8605824, @bd808 wrote:

https://docs.sqlalchemy.org/en/20/dialects/mysql.html#insert-on-duplicate-key-update-upsert might be helpful. Sqlalchemy's MySQL specific insert handler supports an on_duplicate_key_update modifier that lets you tell the backend MySQL/MariaDB how to handle a primary key conflict by updating the existing row rather than erroring. This is the MySQL/MariaDB non-standard equivalent of the SQL:2003 "MERGE" statement. This pattern is also commonly called an "upsert" (a portmanteau of update and insert).

Oh, that is perfect, thank you!

Latest update: we can now get and insert real data into the db. I've opened a draft pull request to mark the current state of my work. At the moment the program becomes angry and dies when it encounters a tool title with a special character, but that doesn't happen right away, so we're still left with plenty to look at.

Since we have no completed tasks as yet, using real data will give the frontend nothing to work with in terms of populating the "high scores" and "latest activity" tables; OTOH, they won't have any information in them at launch, either, so it's worth thinking about what to display when "contributions = []"

For reference, the error I'm encountering.

sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1366, "Incorrect string value: '\\xC4\\xB1s\\xC4\\xB1' for column `mydatabase`.`tool`.`title` at row 1")
[SQL: INSERT INTO tool (name, title, description, url) VALUES (%s, %s, %s, %s)]
[parameters: ('toolforge-superyetkin', 'Kaynak avcısı', 'Citation Hunt for Turkish Wikipedia', 'https://superyetkin.toolforge.org/kaynak_avcisi.php')]

There's something happening under the hood with encoding and decoding that isn't working right, specifically with regards to characters in a certain range (I've tried working with é, ö, ä, etc and not had the same problem), but again I'm running up against the limits of my understanding. I've played around with .encode() and .decode() and have determined that if I manually encode the title and then convert the resulting byte object to a string, I can enter it into the database, but that doesn't really solve my problem.

I seem to have solved the encoding issue by using .encode() on both the tool title and the tool description, both of which were found to contain occasional characters that made the database upset; as far as I can tell, this is because MariaDB defaults to latin1 encoding in some cases. I haven't been able to figure out how to get it to apply utf8mb4 consistently, but storing the information as byte objects rather than strings seems to be working and as far as I can tell SQLAlchemy is clever enough to decode things properly.

This got me as far as 1453 tools and 14717 tasks (for reference, when we're deciding between a full data dump update vs. trying to finesse recent edits, this took almost 3 and a half minutes), before I ran into the next encoding issue:

sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='")
[SQL: SELECT tool.name, tool.title, tool.description, tool.url
FROM tool
WHERE tool.name = %s]
[parameters: ('toolforge-dīgerō',)]

So I converted the name column into a BLOB as well, which caused me some issues as it had previously been my primary key. This has necessitated adding a new id column to the tool table and for some reason it isn't working properly. The work continues...

In T329029#8610647, @NicoleLBee wrote:

I haven't been able to figure out how to get it to apply utf8mb4 consistently

You need to set the character encoding for both the tables you have created in your ToolsDB hosted database and also in your client when you connect to the database. For the client part, you can add ?charset=utf8mb4 to the database URL that you use with sqlalchemy -- https://docs-sqlalchemy.readthedocs.io/ko/latest/dialects/mysql.html#charset-selection.

It turns out that the problem was with Alembic, which wasn't recognizing the encoding changes I was trying to make. I wiped the existing migrations and started over again, and was able to get the tables properly encoded.

It took ~3 minutes to pull all of the data from Toolhub and generate the tasks.