Difference between revisions of "Exploring the database"

From Dreamwidth Notes
Jump to: navigation, search
(→‎Signing into MySQL: replaced this overly complicated solution with use ljdb)
 
(4 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
This guide will help guide you in poking around your Dreamwidth installation's database.  This could help you during coding!   
+
This guide will help guide you in poking around your Dreamwidth installation's database.  This could help you during coding!  You might also want to read the [[Database Table Overview]] for more database information, as well as locations to certain information in the database.
  
 
== Signing into MySQL ==
 
== Signing into MySQL ==
Line 5: Line 5:
 
Sign into your development environment with SSH.
 
Sign into your development environment with SSH.
  
−
You can find the log in credentials you need in the <code>$LJHOME/etc/config-private.pl</code>:
+
cd $LJHOME
−
 
+
  bin/ljdb
−
<source lang=Perl>'user' => 'dh_foxfirefey',
+
−
'pass' => 'XXXXXXXXXXX',    # CHANGETHIS
+
−
'dbname' => 'dreamhack_foxfirefey',</source>
+
−
 
+
−
Replace the items in brackets with the values assigned above:
+
−
 
+
−
  mysql -u <user> -p <pass> <dbname>
+
−
 
+
−
For example:
+
−
 
+
−
mysql -u dh_foxfirefey -p dreamhack_foxfirefey
+
−
 
+
−
And enter the password when prompted.  You will then see information like this before it gives you a prompt:
+
−
 
+
−
<pre>Reading table information for completion of table and column names
+
−
You can turn off this feature to get a quicker startup with -A
+
−
 
+
−
Welcome to the MySQL monitor.  Commands end with ; or \g.
+
−
Your MySQL connection id is 12284107
+
−
<strong>Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)</strong>
+
−
 
+
−
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.</pre>
+
−
 
+
−
The emphasized portion lets you know what version of MySQL you are using, in case you have to look things up in [http://dev.mysql.com/doc/index.html the MySQL documentation].
+
  
 
== Basic Database Navigation ==
 
== Basic Database Navigation ==
Line 44: Line 20:
 
This command will show you a list of all of the columns in a table, which is all of the pieces of data a row has.
 
This command will show you a list of all of the columns in a table, which is all of the pieces of data a row has.
  
−
mysql> [http://dev.mysql.com/doc/refman/5.0/en/show-columns.html SHOW COLUMNS] FROM <tablename>;
+
mysql> [http://dev.mysql.com/doc/refman/5.0/en/show-columns.html SHOW COLUMNS] FROM <tablename>;
  
 
For example:
 
For example:
  
−
mysql> SHOW COLUMNS FROM user;
+
mysql> SHOW COLUMNS FROM user;
  
 
The list will have a table of information about each column (or field):
 
The list will have a table of information about each column (or field):
Line 92: Line 68:
  
 
See [http://dev.mysql.com/doc/refman/5.0/en/insert.html INSERT].
 
See [http://dev.mysql.com/doc/refman/5.0/en/insert.html INSERT].
−
 
−
== Important information locations ==
 
−
 
−
=== Account information ===
 
−
 
−
* <code>user</code>
 
−
 
−
=== Entry information ===
 
−
 
−
* <code>log2</code>
 
−
 
−
=== Comments ===
 
−
 
−
* <code>talk2</code>
 
−
 
−
=== Invites ===
 
−
 
−
* <code>acctcode</code>
 
  
 
[[Category: Development]]
 
[[Category: Development]]

Latest revision as of 03:18, 21 August 2012

This guide will help guide you in poking around your Dreamwidth installation's database. This could help you during coding! You might also want to read the Database Table Overview for more database information, as well as locations to certain information in the database.

Signing into MySQL

Sign into your development environment with SSH. 

cd $LJHOME
bin/ljdb

Basic Database Navigation

Show all tables

Data in the database is stored in a collection of tables, each of which is like a spreadsheet with rows containing a certain amount of columns. To present a list of all the tables in the database, use this command: 

mysql> SHOW TABLES;

Show all columns in a table

This command will show you a list of all of the columns in a table, which is all of the pieces of data a row has. 

mysql> SHOW COLUMNS FROM <tablename>;

For example: 

mysql> SHOW COLUMNS FROM user;

The list will have a table of information about each column (or field):

  • Field -- the name of the column
  • Type -- the type of data the column stores. You can read more about it at MySQL's documentation.
  • Null -- whether or not NULL (the absence of a value) is allowed for this column
  • Key -- A table's key determines how you can look up data in the database. PRI here means that is the tables primary key. UNI means that bit of data will be unique for that row--that is, if one row has "moo", another row cannot have the value "moo" in that table.
  • Default -- the default value for the column, if none is specified
  • Extra -- if auto_increment is here, that means that every new row to that table automatically gets a higher value than the last row added.

Viewing data

The most basic way of viewing is this command, but only run it if you have a few users on your system: 

mysql> SELECT * FROM user;

You can show only the columns you're interested in like this: 

mysql> SELECT userid,user,name FROM user;

And even limit the number of rows you get back: 

mysql> SELECT userid,user,name FROM user LIMIT 5;

You can even search for a row matching specific requirements: 

mysql> SELECT userid,user,name FROM user WHERE user='foxfirefey';

Changing data

See UPDATE. 

mysql> SELECT status FROM user WHERE userid=1;
mysql> UPDATE user SET status='A' WHERE userid=1;
mysql> SELECT status FROM user WHERE userid=1;

mysql> SELECT userid,user,email,status FROM user;
mysql> UPDATE user SET status='A' WHERE status='N';
mysql> SELECT userid,user,email,status FROM user;

Adding data

See INSERT.

Retrieved from "http://wiki.dreamwidth.net/wiki/index.php?title=Exploring_the_database&oldid=10710"