Wiki source code of Database Administration
Version 7.3 by Jean-Vincent Drean on 2008/03/17
Hide last authors
| author | version | line-number | content |
|---|---|---|---|
 |
1.1 | 1 | 1 Database Administration |
| 2 | |||
 |
1.3 | 3 | XWiki uses by default a database to store its data. You will find on this page tips and tricks to administer and tune your database setup. For Installation please check the [Installation>Installation#HInstallandconfigureaRelationalDatabase] page which contains information on how to setup XWiki for multiple databases. |
| |
1.1 | 4 | |
| |
7.1 | 5 | #toc("" "" "") |
| 6 | |||
| |
1.1 | 7 | 1.1 Indexes |
| 8 | |||
| |
1.3 | 9 | To improve your XWiki instance when running with many documents you should run the following script to create indexes for your database. This has been tested for MySQL 4.x and 5.0. |
| |
1.1 | 10 | |
| 11 | {code} | ||
| 12 | create index xwd_name on xwikidoc (xwd_name); | ||
| 13 | create index xwd_fullname on xwikidoc (xwd_fullname); | ||
| 14 | create index xwd_web on xwikidoc (xwd_web); | ||
| 15 | create index xwd_name on xwikiobjects (xwo_name); | ||
| 16 | create index xwl_value on xwikilongs (xwl_value); | ||
| 17 | create index xwi_value on xwikiintegers (xwi_value); | ||
| 18 | create index xws_value on xwikistrings (xws_value); | ||
| 19 | create index xwl_value on xwikilargestrings (xwl_value(50)); | ||
| 20 | create index xwo_classname on xwikiobjects (xwo_classname); | ||
| 21 | create index xwd_creation_date on xwikidoc (xwd_creation_date); | ||
| 22 | create index xwd_date on xwikidoc (xwd_date); | ||
| 23 | create index xwd_content_update_date on xwikidoc (xwd_content_update_date); | ||
| 24 | create index xwd_content_author on xwikidoc (xwd_content_author); | ||
| 25 | create index xwd_author on xwikidoc (xwd_author); | ||
| 26 | create index xwd_creator on xwikidoc (xwd_creator); | ||
| 27 | create index xwd_language on xwikidoc (xwd_language); | ||
| 28 | create index xwd_default_language on xwikidoc (xwd_default_language); | ||
| 29 | create index xwd_title on xwikidoc (xwd_title); | ||
| 30 | create index xwd_parent on xwikidoc (xwd_parent(50)); | ||
 |
4.1 | 31 | create index xwd_class_xml on xwikidoc (xwd_class_xml(20)); |
| |
1.1 | 32 | {code} |
| 33 | |||
| 34 | 1.1 Sanity Checks | ||
| 35 | |||
| |
1.6 | 36 | To verify the consistency of your XWiki database you can run the sanity check script. The script will select rows that are inconsistent with the logics of XWiki. If no rows are selected, it means the script itself can not detect any error. |
| |
1.7 | 37 | * If you use *MySQL* as your database : |
| 38 | ** The following {attach:sanity check script|sanitycheck.sql} has been tested for *MySQL 5.0*. | ||
| 39 | |||
| 40 | * If you use *postgresql* as your database : | ||
| 41 | ** Here is a {attach:modified version of the sanity check script|sanitycheck_postgresql.sql}, to be run in the "query" tool of pgAdmin. It is the same as the script for MySQL, except that the syntax for SQL comments is different. | ||
| |
1.6 | 42 | ** pgAdmin is the GUI tool provided with postgresql to access the database. Run pgAdmin, select the <code>xwiki</code> database, and choose the "Query" option in the "Tools" menu. Then just open the script and click on the play icon ("Execute query"). |
| |
7.1 | 43 | |
| 44 | 1.1 Database browsing | ||
| 45 | |||
| 46 | 1.1.1 DbVisualizer | ||
| 47 | |||
| 48 | http://www.dbvis.com/products/dbvis/download/install.jsp | ||
| 49 | |||
| |
7.2 | 50 | Natively supported DBs : |
| |
7.1 | 51 | |
| 52 | * DB2 for Windows/Linux | ||
| 53 | * JavaDB/Derby | ||
| 54 | * MySQL | ||
| 55 | * PostgreSQL | ||
| 56 | * [more>http://www.dbvis.com/products/dbvis/features/features.jsp?page=matrix] | ||
| 57 | |||
| |
7.2 | 58 | Any DB using the corresponding JDBC driver : |
| 59 | |||
| 60 | * Example HSQLDB by using the driver bundled with XWiki |
