Talk:WikiDB/Tables

Just going through the setup here. Not sure what Key - MUL refers to. I guess 'mutual' from a Google search but I don't see this in phpmyadmin.

Also, not sure how to setup the indexes but haven't quite gotten to that yet. Blckdmnd99 18:16, 1 October 2006 (BST)

Hi! I originally set this page up as a way to get feedback on the schema, and have not officially released the extension yet, as it is still very much in an alpha stage of development. Ultimately I will include a .sql file that does the update for you. In the meantime feel free to recreate the tables from this definition if you like. However, two caveats: (1) the schema may change as the extension develops and change may be incompatible with the current version, and (2) I cannot be held responsible for any problems or damage that may arise from using this pre-release software... standard disclaimer, etc. :)

Anyway, in answer to your questions, I believe MUL means that it is used in multiple index, or possibly that it is part of a multi-field index. Either way, that is not very important regarding the setup of the tables. Ditto indexes - you can safely omit these (apart from the primary key). Their purpose is to speed up queries, so without them everything will work fine, but possibly slightly slower. Given that the code is horribly unoptimised at the moment, it's probably no big deal...

I would appreciate any feedback you have from using the extension - let me know how you get on! --HappyDog 18:31, 1 October 2006 (BST)

Thanks for the disclaimer... certainly understandable! :) Glad to hear about the tables. I'll offer my thoughts as best I can... feel free to disregard any of my ramblings! Blckdmnd99 19:19, 1 October 2006 (BST)

---

wikidb_fielddata 1071 - Specified key was too long. Max key length is 500 :(

I'm guessing that this error is occuring when you are creating the indexes in your local copy of the wikidb_fielddata table. This may be because the version of MySQL you are using is incompatible with my DB - I haven't tested on anything except 3.23.58. I'm guessing the binary fields require 2 bytes per character, and with a 255 character limit, that means a key of 510 bytes. Perhaps this is a config setting. Please let me know if you find a good fix for this, or if I am off the mark with my diagnosis, and thanks for taking the trouble to write. --HappyDog 23:54, 5 January 2007 (GMT)

sql scripts

Hi, can you provide an sql extract to have the database creation (without data). with phpMyAdmin It's just to start installation quickly and easier. Even it's a snapshot not up-to-date, it's a begenning. Thx Christophe --207.45.248.18 17:57, 21 May 2007 (BST)

Creating Tables for WikiDB extension

Hi, I'm new to this site, but very interested in using/helping develop the WikiDB extension.

I've taken a stab at creating the three new tables to the wiki database using phpMyAdmin 2.8.1-Debian-1~dapper1 on a Mepis 6.5 linux installation running MySQL v5.0.22-Debian_0ubuntu6.06.3-log.

I'm new to MySQL and PHP, so I'm not very well learned with either technology, however, I made my best attempt to use phpMyAdmin to create the three tables listed here. At first, I had some confusion as to how you were actually able to create TWO indexes with the same name and when I tried it, MySQL failed telling me there was an index with that name that already existed, so I worked around the problem by selecting two columns for just the one index name. Other than that, I saw no way to select a KEY type of "MUL", select a COLLATION of "A", set the Cardinality to something other than "0", or set the NON_UNIQUE or SEQ_IN_INDEX fields.

Here is the SQL code that I captured from phpMyAdmin for all of the steps I went through to attempt to create the tables.

CREATE TABLE `wikidb_tables` (
`table_namespace` INT( 11 ) NOT NULL DEFAULT '0',
`table_title` VARCHAR( 255 ) NOT NULL ,
`table_def` MEDIUMBLOB NOT NULL
) ENGINE = innodb;

ALTER TABLE `wikidb_tables` ADD INDEX `table_name_title` ( `table_namespace` , `table_title` )

CREATE TABLE `wikidb_rowdata` (
`row_id` INT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`page_namespace` INT( 11 ) NOT NULL DEFAULT '0',
`page_title` VARCHAR( 255 ) NOT NULL ,
`table_namespace` INT( 11 ) NOT NULL DEFAULT '0',
`table_title` VARCHAR( 255 ) NOT NULL ,
`raw_data` MEDIUMBLOB NOT NULL ,
`parsed_data` MEDIUMBLOB NOT NULL
) ENGINE = innodb;

ALTER TABLE `wikidb_rowdata` ADD INDEX `name_title` ( `page_namespace` , `page_title` )

ALTER TABLE `wikidb_rowdata` ADD INDEX `table_name_title` ( `table_namespace` , `table_title` )

CREATE TABLE `wikidb_fielddata` (
`table_namespace` INT( 11 ) NOT NULL DEFAULT '0',
`table_title` VARCHAR( 255 ) NOT NULL ,
`row_id` INT( 8 ) NOT NULL DEFAULT '0',
`field_name` VARCHAR( 255 ) NOT NULL ,
`field_value` VARCHAR( 255 ) NOT NULL
) ENGINE = innodb;

ALTER TABLE `wikidb_fielddata` ADD INDEX `name_title` ( `table_namespace` , `table_title` )

ALTER TABLE `wikidb_fielddata` ADD INDEX `row_id` ( `row_id` )

ALTER TABLE `wikidb_fielddata` ADD INDEX `field_name` ( `field_name` , `field_value` )

All three tables were created to use the InnoDB storage engine. Since MediaWiki's installation scripts created most of the wiki database's tables using the InnoDB storage engine and your article page did not specify which one to use, I assumed InnoDB.

If this is still wrong and doesn't accurately reproduce the tables listed in the article page, then perhaps you could give a more detailed description on how to create these tables, since your debug tool <describe_table> doesn't seem to match with how phpMyAdmin displays information about MySQL databases.

--Mdrayman 03:44, 24 June 2007 (BST)

Just a further comment, I got your extension fully installed using the tables as described by the MySQL code above and the versions stated above for MySQL and phpMyAdmin, MediaWiki v1.10.0 and so far it's working!!! Adding data to an undefined table and printing into the wiki page using the "default" template works as does the Special:UndefinedPages link, which correctly reports the undefined table and its data. I'll keep you posted as to how things progress as I use it more to build a customer database in my Personal wiki. I would encourage others to try the SQL code above with phpMyAdmin to get started using this extension. GREAT WORK HappyDog!! --Mdrayman 05:39, 24 June 2007 (BST)

I use MyISAM, but InnoDB probably works fine too. I will modify by Debugging tools extension so that it generates usable SQL as well, which would be a lot more useful, I think! --HappyDog 15:09, 25 June 2007 (BST)

OK, that sounds great. I look forward to that. --Mdrayman 00:31, 26 June 2007 (BST)