Throughout this semester, I have learned an incredible amount about the ways computers work, and how many of their function directly relate to the archival field. I truly believe much of this would have been over my head had I not had the hands-on experience of creating my own virtual machine and working in it to complete tasks and solve problems. Once I pulled myself away from simply following RPM's instructions verbatim and trying to understand the "why" behind each direction, I came to appreciate the assignments on a deeper level.
For me, the final assignment really brought home how much I had learned. I was able to work in the command line to fix an issue with my directory access that would have completely stumped me before. I have found that through a little research, I can solve many problems on my own, but having the support and findings of my colleagues, as well as RPM on speed dial, made working with technology interesting, worthwhile, and much less frightening than I had previously assumed.
Wednesday, December 7, 2011
Tuesday, December 6, 2011
November 30, 2011 - Querying a database
For the final assignment in ARST5100, I needed to query my database. I anticipated this being a relatively simple assignment, as RPM had provided the query language and noted if it was not possible for us to save the query result to a .txt document, we could provide screenshots from MySQL.
After opening my virtual machine and MySQL, Is igned in as root (my standard MySQL sign-in) and double-clicked on catalog to query from the tables I had created. I entered the first query:
SELECT DISTINCT title FROM readings ORDER BY title INTO OUTFILE ' /tmp/task1.txt' ;
After hitting ctrl+enter, I received the following error message:
Message: Error Code: 1. Can’t create/write to file ‘/var/lib/mysql/ /tmp/task1.txt’ (Errcode: 2)
Looking closely at what the error message said and realizing that having permission to write to a directory was something RPM had discussed earlier in the semester. I decided to go back and review my notes on chmod and try to make /var/lib/mysql a writable directory.
I minimized MySQL and opened the terminal window. Using the command line, I checked the var file directory by using the following commands.
cd /var
ls –l
The list that appeared showed the lib directory did not have read/write access so I entered the following command:
sudo chmod g+wr lib (which worked after I was prompted for my password)
I then wanted to ensure /var/lib/mysql had read/write access so I entered the commands:
cd /var/lib
ls -l
And seeing the same issue again, I changed the mysql directly to have group read/write access.
sudo chmod g+wr mysql
Hopeful this would solve my error issue, I returned to MySQL and re-entered the first query:
SELECT DISTINCT title FROM readings ORDER BY title INTO OUTFILE ' /tmp/task1.txt' ;
I received a message stating 13 records had been affected, so I went to the text editor to see if the file I was supposed to create could be opened. The file task1.txt existed, so I saved it to the desktop for safe keeping.
I returned to MySQL and entered the second query:
SELECT DISTINCT apvalue FROM accesspoints ORDER BY apvalueWHERE apname = 'author" INTO OUTFILE ' /tmp/task2.txt' ;
I consistently received the red dot indicating an error every time I tried to enter this query. I attempted to put the entire query into one line, and try each line of text separately, but neither returned the correct result. I then checked the MySQL reference manual and after A LOT of searching. I found a SELECT query which showed the ORDER BY command following the WHERE command, so I entered the query like this:
SELECT DISTINCT apvalue FROM acccesspoints
WHERE apname = 'author' ORDERY BY apvalue INTO OUTFILE ' /tmp/task2.txt' ;
This query successfully executed and I saved task2.txt to my desktop.
Going back to MySQL, I entered the third query, changing RPM's apname = 'course' to apname ' = 'form_genre' as I had not entered course information in my table:
SELECT DISTINCT accesspoints.apvalue, readings.title FROM accesspoints, readings
WHERE (accesspoints.readingsid _FK= readings.readingsid)
AND (accesspoints.apname = ‘form_genre’)
ORDER BY accesspoints.apvalue, readings.title
INTO OUTFILE ‘/tmp/task3.txt’ ;
I received an error message after entering this query, and decided to remove the _FK from the second line:
WHERE accesspoints.readingsid _FK= readings.readingsid)
because the foreign key designation was not part of the actual attribute name.
Upon doing that, the third query worked, and I saved task3.txt to my desktop.
Next I entered the fourth query:
SELECT DISTINCT accesspoints.apvalue, readings.title FROM accesspoints, readings
WHERE (accesspoints.readingsid = readings.readingsid)
AND (accesspoints.apname = ‘author’)
ORDER BY accesspoints.apvalue, readings.title
INTO OUTFILE ‘/tmp/task4.txt’ ;
This query worked without issue, and I saved task4.txt to my desktop.
Finally, I completed the fifth query:
SELECT apvalue, COUNT (*) FROM accesspoints
WHERE (apname = 'subject')
GROUP BY apvalue
INTO OUTFILE ' /tmp/task5.txt' ;
The final query worked, and I saved the last document, task5.txt to my desktop.
I shut down MySQL and ensured all of the tasks were saved to my desktop so I could access them at a later time.
November 28, 2011 - Database data entry
After receiving feedback from RPM on my table attributes, I decided to make some small changes to field characteristics before starting table data entry.
I opened my virtual machine, ran the Update Manager, and then navigated to Applications, Programming to open MySQL Workbench. I opened the readings connection and double-clicked on catalog to access my tables. I right-clicked on accesspoints and chose Alter Table from the menu which appeared. First of all, I labeled the readingsid as an integer(10) datatype in my readings table and an integer (11) datatype in my accesspoints table. I changed the accesspoints table integer to reflect the readings table.readingsid characteristic. I then changed notes to a text datatype, because they could be much longer than 255 characters. I clicked Apply twice and these changes were made.
I chose 13 readings from the ARST5100 class to input into my database. These readings came from a variety of sources, which I thought would provide a wider range of information for my queries. Starting with the readings table, I right-clicked, bring up the menu from which I chose Edit Table Data. Leaving readingsid blank, I began to enter information into each of the remaining columns:
title
source
imprint
page_range
file_name
notes
link
For each of the readings, I was able to fill in title, source, and imprint. However, depending on whether the reading was a website or a more traditional report, book excerpt, or article, I input the appropriate information in the columns and left others blank. This was not an issue as I did not specify any of these as non-null. Once all of this information was entered, I hit the Verify button on the upper row within the Query to save the information.
After entering all of this data, I realized it would be more efficient to create a separate spreadsheet for the accesspoints table in order to have all the information in one place before data entry. I created a spreadsheet listing the subject, form_genre, and author of each of the readings I chose for my readings table. I believed I may be able to import this spreadsheet into MySQL; but per RPM's warning this was quite complicated and I decided not to attempt it.
When I completed entering all of this information for both tables, I right-clicked on each table and selected Select Rows - Limit 100 to double-check that my readingsids corresponded.
This completed my data entry.
I opened my virtual machine, ran the Update Manager, and then navigated to Applications, Programming to open MySQL Workbench. I opened the readings connection and double-clicked on catalog to access my tables. I right-clicked on accesspoints and chose Alter Table from the menu which appeared. First of all, I labeled the readingsid as an integer(10) datatype in my readings table and an integer (11) datatype in my accesspoints table. I changed the accesspoints table integer to reflect the readings table.readingsid characteristic. I then changed notes to a text datatype, because they could be much longer than 255 characters. I clicked Apply twice and these changes were made.
I chose 13 readings from the ARST5100 class to input into my database. These readings came from a variety of sources, which I thought would provide a wider range of information for my queries. Starting with the readings table, I right-clicked, bring up the menu from which I chose Edit Table Data. Leaving readingsid blank, I began to enter information into each of the remaining columns:
title
source
imprint
page_range
file_name
notes
link
For each of the readings, I was able to fill in title, source, and imprint. However, depending on whether the reading was a website or a more traditional report, book excerpt, or article, I input the appropriate information in the columns and left others blank. This was not an issue as I did not specify any of these as non-null. Once all of this information was entered, I hit the Verify button on the upper row within the Query to save the information.
After entering all of this data, I realized it would be more efficient to create a separate spreadsheet for the accesspoints table in order to have all the information in one place before data entry. I created a spreadsheet listing the subject, form_genre, and author of each of the readings I chose for my readings table. I believed I may be able to import this spreadsheet into MySQL; but per RPM's warning this was quite complicated and I decided not to attempt it.
When I completed entering all of this information for both tables, I right-clicked on each table and selected Select Rows - Limit 100 to double-check that my readingsids corresponded.
This completed my data entry.
November 13, 2011 - Creating a database
To start creating a database in MySQL, I opened my virtual machine and navigated to Applications, Programming, MySQL Workbench.
When MySQL opened I clicked on the Readings connection I created last week and entered my root password:
******
I clicked on Add Schema under Actions, typed catalog into the Name field on the dialog box that popped up, and clicked Apply. Another dialog box appeared, showing the command that was about to be performed. I clicked Apply again and the schema was created.
As part of last week's assignment, I had conceived of potential fields for this database, as can be seen here:
While these columns were a start, I took several of RPM's recommendations and made changed when I created the tables in MySQL.
The main readings screen was still on display with the catalog schema on the lower left. I double-clicked on catalog to ensure I was working within it and then double-clicked Add Table on the upper left. This opened a dialog box to create my first table. I named the table accesspoints and then navigated to the Columns tab at the top of the box. I entered four columns:
accesspointsid
readingsid
apname
apvalue
A drop-down menu and series of check boxes followed each column, letting me choose the characteristics of the field. Based upon my understanding of tables, I chose integers for the ids and varchar for the apname and apvalue columns. For accesspointsid, I selected PK (primary key), NN (non-null), UQ (unique), and AI (auto-increment). I also selected NN for readingsid, as this attribute will always need to be present. I clicked apply, bringing up the dialog box showing the MySQL command, and clicked Apply again. The table was created.
I then double-clicked Add Table again to create my second table: readings. I entered the table name into the Name field and clicked on the Columns tab. I created a longer list of columns for this table:
readingsid
title
source
imprint
page_range
file_name
notes
link
I decided to make all of the columns for both tables lower case because MySQL is case sensitive. I also either combined words without spaces (as in readingsid) or joined them with an underscore (as in page_range) so that spaces did not cause any issues with querying. As with the accesspoints table, I chose the Datatype and characteristics of each of these columns. I made all of the columns varchar (per RPM) with the exception of readingsid, whose integer datatype carried over from the accesspoints table. readingsid became my PK, NN, UQ, AI attribute in this table; with title also being NN. I used fewer field than I did in my intitial design for reasons of simplicity.
After inputting all of this information, I clicked Apply, which brought up the MySQL command. I clicked Apply again and both of my tables were created.
The final results can be seen here:
When MySQL opened I clicked on the Readings connection I created last week and entered my root password:
******
I clicked on Add Schema under Actions, typed catalog into the Name field on the dialog box that popped up, and clicked Apply. Another dialog box appeared, showing the command that was about to be performed. I clicked Apply again and the schema was created.
As part of last week's assignment, I had conceived of potential fields for this database, as can be seen here:
| Name | Repeating | Unique | Data Type | Max Length | Notes |
| id | N | Y | Integer | 4 |
|
| title | N | Y | Text | char255 |
|
| author | Y | N | Text | char100 |
|
| form_genre | Y | N | Text | char30 |
|
| publisher | Y | N | Text | char30 |
|
| source | Y | N | Text | char100 | See Note #1 |
| volume_issue | Y | N | Text | char10 |
|
| edition | Y | N | Text | char30 |
|
| access_date | Y | N | Date | YYYY-MM-DD |
|
| pub_date | Y | N | Text | char10 | Publication date |
| page_range | N | N | Text | char30 |
|
| class_number | Y | N | Text | char10 |
|
| class_title | Y | N | Text | char50 |
|
| subject | Y | N | Text | char20 |
|
| abstract | N | Y | Text | varchar |
|
| file_name | N | Y | Text | char50 |
|
| location | Y | N | Text | varchar | Directory location or URL |
| notes | N | N | Text | varchar |
|
While these columns were a start, I took several of RPM's recommendations and made changed when I created the tables in MySQL.
The main readings screen was still on display with the catalog schema on the lower left. I double-clicked on catalog to ensure I was working within it and then double-clicked Add Table on the upper left. This opened a dialog box to create my first table. I named the table accesspoints and then navigated to the Columns tab at the top of the box. I entered four columns:
accesspointsid
readingsid
apname
apvalue
A drop-down menu and series of check boxes followed each column, letting me choose the characteristics of the field. Based upon my understanding of tables, I chose integers for the ids and varchar for the apname and apvalue columns. For accesspointsid, I selected PK (primary key), NN (non-null), UQ (unique), and AI (auto-increment). I also selected NN for readingsid, as this attribute will always need to be present. I clicked apply, bringing up the dialog box showing the MySQL command, and clicked Apply again. The table was created.
I then double-clicked Add Table again to create my second table: readings. I entered the table name into the Name field and clicked on the Columns tab. I created a longer list of columns for this table:
readingsid
title
source
imprint
page_range
file_name
notes
link
I decided to make all of the columns for both tables lower case because MySQL is case sensitive. I also either combined words without spaces (as in readingsid) or joined them with an underscore (as in page_range) so that spaces did not cause any issues with querying. As with the accesspoints table, I chose the Datatype and characteristics of each of these columns. I made all of the columns varchar (per RPM) with the exception of readingsid, whose integer datatype carried over from the accesspoints table. readingsid became my PK, NN, UQ, AI attribute in this table; with title also being NN. I used fewer field than I did in my intitial design for reasons of simplicity.
After inputting all of this information, I clicked Apply, which brought up the MySQL command. I clicked Apply again and both of my tables were created.
The final results can be seen here:
Subscribe to:
Posts (Atom)