Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#1 2008-03-03 23:24:49

venkatesaya
Member
Registered: 2008-03-03
Posts: 20

table diagram

I would like to have the diagram of the tables of the textpattern database

love


Om Namah Shivaya

Offline

#2 2008-03-04 00:41:51

jm
Plugin Author
From: Missoula, MT
Registered: 2005-11-27
Posts: 1,746
Website

Re: table diagram

mysql> SHOW TABLES;
+-------------------+
| Tables_in_txp     |
+-------------------+
| textpattern       | 
| txp_category      | 
| txp_css           | 
| txp_discuss       | 
| txp_discuss_ipban | 
| txp_discuss_nonce | 
| txp_file          | 
| txp_form          | 
| txp_image         | 
| txp_lang          | 
| txp_link          | 
| txp_log           | 
| txp_page          | 
| txp_plugin        | 
| txp_prefs         | 
| txp_section       | 
| txp_users         | 
+-------------------+
17 rows in set (0.00 sec)

Last edited by jm (2008-03-04 00:42:18)

Offline

#3 2008-03-04 06:22:07

venkatesaya
Member
Registered: 2008-03-03
Posts: 20

Re: table diagram

thank you for this quick reply
what I really want to discover is which id field or other field is relating the tables
with a program like http://www.navicat.com/ you easily connect to the mysql database and tables on your server – you can also easily transfer the data on your local machine, or to an other server
navicat also imports/exports perfectly the data from/to a microsoft access database
I do so for my data of www.venkatesaya.com , in order to print in access the data beautifully – but here I know the relating fields between the tables
if I would know the relations between the id’s in the different tables, it would help me
it is mainly the relating field between the tables ‘textpattern’ and ‘discuss’ – also ‘category’, ‘users’, and ‘section’


Om Namah Shivaya

Offline

#4 2008-03-04 07:41:51

jm
Plugin Author
From: Missoula, MT
Registered: 2005-11-27
Posts: 1,746
Website

Re: table diagram

Why don’t you just unleash Navicat on your Textpattern database? Or run MySQL from the command line?

  • txp_discuss is “linked” to the textpattern table by parentid.
  • Sections and categories are stored as names in the textpattern table.
mysql> DESCRIBE textpattern;
+-----------------+--------------+------+-----+---------------------+----------------+
| Field           | Type         | Null | Key | Default             | Extra          |
+-----------------+--------------+------+-----+---------------------+----------------+
| ID              | int(11)      | NO   | PRI | NULL                | auto_increment | 
| Posted          | datetime     | NO   | MUL | 0000-00-00 00:00:00 |                | 
| AuthorID        | varchar(64)  | NO   |     |                     |                | 
| LastMod         | datetime     | NO   |     | 0000-00-00 00:00:00 |                | 
| LastModID       | varchar(64)  | NO   |     |                     |                | 
| Title           | varchar(255) | NO   | MUL |                     |                | 
| Title_html      | varchar(255) | NO   |     |                     |                | 
| Body            | mediumtext   | NO   |     |                     |                | 
| Body_html       | mediumtext   | NO   |     |                     |                | 
| Excerpt         | text         | NO   |     |                     |                | 
| Excerpt_html    | mediumtext   | NO   |     |                     |                | 
| Image           | varchar(255) | NO   |     |                     |                | 
| Category1       | varchar(128) | NO   | MUL |                     |                | 
| Category2       | varchar(128) | NO   |     |                     |                | 
| Annotate        | int(2)       | NO   |     | 0                   |                | 
| AnnotateInvite  | varchar(255) | NO   |     |                     |                | 
| comments_count  | int(8)       | NO   |     | 0                   |                | 
| Status          | int(2)       | NO   |     | 4                   |                | 
| textile_body    | int(2)       | NO   |     | 1                   |                | 
| textile_excerpt | int(2)       | NO   |     | 1                   |                | 
| Section         | varchar(64)  | NO   | MUL |                     |                | 
| override_form   | varchar(255) | NO   |     |                     |                | 
| Keywords        | varchar(255) | NO   |     |                     |                | 
| url_title       | varchar(255) | NO   |     |                     |                | 
| custom_1        | varchar(255) | NO   |     |                     |                | 
| custom_2        | varchar(255) | NO   |     |                     |                | 
| custom_3        | varchar(255) | NO   |     |                     |                | 
| custom_4        | varchar(255) | NO   |     |                     |                | 
| custom_5        | varchar(255) | NO   |     |                     |                | 
| custom_6        | varchar(255) | NO   |     |                     |                | 
| custom_7        | varchar(255) | NO   |     |                     |                | 
| custom_8        | varchar(255) | NO   |     |                     |                | 
| custom_9        | varchar(255) | NO   |     |                     |                | 
| custom_10       | varchar(255) | NO   |     |                     |                | 
| uid             | varchar(32)  | NO   |     |                     |                | 
| feed_time       | date         | NO   |     | 0000-00-00          |                | 
+-----------------+--------------+------+-----+---------------------+----------------+
36 rows in set (0.01 sec)

mysql> DESCRIBE txp_discuss;
+-----------+--------------------------+------+-----+---------------------+----------------+
| Field     | Type                     | Null | Key | Default             | Extra          |
+-----------+--------------------------+------+-----+---------------------+----------------+
| discussid | int(6) unsigned zerofill | NO   | PRI | NULL                | auto_increment | 
| parentid  | int(8)                   | NO   | MUL | 0                   |                | 
| name      | varchar(255)             | NO   |     |                     |                | 
| email     | varchar(50)              | NO   |     |                     |                | 
| web       | varchar(255)             | NO   |     |                     |                | 
| ip        | varchar(100)             | NO   |     |                     |                | 
| posted    | datetime                 | NO   |     | 0000-00-00 00:00:00 |                | 
| message   | text                     | NO   |     |                     |                | 
| visible   | tinyint(4)               | NO   |     | 1                   |                | 
+-----------+--------------------------+------+-----+---------------------+----------------+
9 rows in set (0.00 sec)

mysql> DESCRIBE txp_category;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int(6)       | NO   | PRI | NULL    | auto_increment | 
| name   | varchar(64)  | NO   |     |         |                | 
| type   | varchar(64)  | NO   |     |         |                | 
| parent | varchar(64)  | NO   |     |         |                | 
| lft    | int(6)       | NO   |     | 0       |                | 
| rgt    | int(6)       | NO   |     | 0       |                | 
| title  | varchar(255) | NO   |     |         |                | 
+--------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM txp_category;
+----+----------------------+---------+--------+-----+-----+----------------------+
| id | name                 | type    | parent | lft | rgt | title                |
+----+----------------------+---------+--------+-----+-----+----------------------+
| 13 | root                 | file    |        |   1 |   2 | root                 | 
| 12 | root                 | image   |        |   1 |   4 | root                 | 
| 11 | root                 | link    |        |   1 |   4 | root                 | 
| 10 | root                 | article |        |   1 |   8 | root                 | 
|  5 | hope-for-the-future  | article | root   |   2 |   3 | Hope for the Future  | 
|  6 | meaningful-labor     | article | root   |   4 |   5 | Meaningful Labor     | 
|  7 | reciprocal-affection | article | root   |   6 |   7 | Reciprocal Affection | 
|  8 | textpattern          | link    | root   |   2 |   3 | Textpattern          | 
|  9 | site-design          | image   | root   |   2 |   3 | Site Design          | 
+----+----------------------+---------+--------+-----+-----+----------------------+
9 rows in set (0.00 sec)

Last edited by jm (2008-03-04 07:42:24)

Offline

#5 2008-03-04 07:56:47

venkatesaya
Member
Registered: 2008-03-03
Posts: 20

Re: table diagram

a courageous senior will be happy with a confirmation

[textpattern] has <ID>
[discuss] has <discussid> and <parentid>

<ID> from [textpattern] and <parentid> from [discuss] are the relating field

please confirm jm
God bless you

Last edited by venkatesaya (2008-03-04 07:57:15)


Om Namah Shivaya

Offline

#6 2008-03-04 08:56:46

jm
Plugin Author
From: Missoula, MT
Registered: 2005-11-27
Posts: 1,746
Website

Re: table diagram

venkatesaya wrote:

<ID> from [textpattern] and <parentid> from [discuss] are the relating field

Correct.

mysql> SELECT textpattern.ID, txp_discuss.parentid FROM textpattern, txp_discuss WHERE textpattern.ID=1;
+----+----------+
| ID | parentid |
+----+----------+
|  1 |        1 | 
+----+----------+
1 row in set (0.00 sec)

Last edited by jm (2008-03-04 08:57:08)

Offline

Board footer

Powered by FluxBB