Please, please, consider adding columns for a uuid. See my previous e-mail from May:

Subject: UUIDs: help transwiki, unifying user tables, etc...

All that would need to be done is:

- add a CHAR(16) BINARY column to table page as 'page_uuid'. - add a CHAR(16) BINARY column to table revision as 'revision_uuid'. - add a CHAR(16) BINARY column to table text as 'text_uuid'.

This would enable so many things to happen. I am not going to specifically name what they are, because I keep to keep tripping over pet issues that people have, and the central point does not get discussed.

For now, nothing would even need to be done with these columns.

We can take a phased approach:

1 - add the columns now

2 - start to look at how to get the uuid added to the table at create of page/revision/text. There are many examples of how to create UUIDs in different languages.

3 - test and optimize same

4 - start a process to retroactively add uuid numbers to all page/revision/text rows. These can be added, dropped, re-added, etc and etc. When an id that you generate is unique and differentiable from any uuid generated in the entire rest of the universe, it is possible to stage this process, generate new uuids at any time, mix and match from different tables, or whatever.

5 - start to look at queries that can use these uuid numbers.

6 - implement and optimize same.

Brion can do this first step and, once that is done, I can get started on 2 and 3, offline. And if nobody has ideas by the time we come to step 5, I'll eat my words and those columns can be dropped from the table. No harm, no foul.

I am only going to say that it will be useful to have an id that is not only unique among all the pages and all the revisions and all the text rows, but that is also unique among all other servers that might be sharing the load, and is also unique among all languages in all the wikipedias, and is unique among all users of all wikipedias anywhere on the planet.

I would be surprised if anyone cannot see at least one thing that having this information would enable.

And once the columns are in place it will be worth it to fight those battles, individually looking at what has been enabled and whether any given feature is desirable or not.

- ray

On Oct 3, 2004, at 3:19 AM, Brion Vibber wrote:

I'm working on code for the much-discussed database schema change.

See overview & in-progress schema conversion code: http://meta.wikimedia.org/wiki/Proposed_Database_Schema_Changes/ October_2004

I've got various features up and running on the new schema and will check in some code on a branch until it's sorted out and we've done some test conversion runs to make sure it's not too out of whack.

This is intended to solve a number of major issues including:

• eliminate denial-of-service problem on page renames
• make revision ID number available and consistent for old and current

revisions

• speed up aggregate queries by not having to slog through text

storage to get at revision metadata

It also should reduce the update burden on the slave database servers by not having to save every edit's text and data twice.

This current schema co-opts the existing old table for text storage, ignoring the extra fields which are now moved off to other tables. This should keep the initial conversion cost relatively low as the table doesn't need to be rebuilt, just added to. The smaller metadata-only tables should be easier to alter in the future as needed.

-- brion vibber (brion @ pobox.com) _______________________________________________ Wikitech-l mailing list Wikitech-l@wikimedia.org http://mail.wikipedia.org/mailman/listinfo/wikitech-l