Homemade Wine Tutorial

Background

In QEDWiki, a {{List}}widget is a mechanism for defining an SQL table using Wiki markup. The QEDWiki framework does the heavy lifting to map a List definition to an SQL Table. Once a List has been defined, a user can use a QEDWiki Form to manage the data contained in the table or the user can prime the table with external content using the SQL Import capabilities of phpMyAdmin.

Overview

The purpose of this tutorial is to demonstrate through example how a user would import external data into QEDWiki for use with mashups. This method of provisioning QEDWiki with external data is quite common for QEDWiki users. Typically, users have ad-hoc data readily available in other application such as MS-Access, MS-Excel, Lotus 123. Many enterprise applications allow for the exporting of report data into XML, SQL, CSV, and Text formats. Once a user primes QEDWiki with external data, the QEDWiki Lists are now available for mashing with other widgets. Since QEDWiki implements the lists as SQL tables, migrating data from QEDWiki becomes as simple as migrating or exporting and SQL database. Finally, because QEDWiki is database agnostic, users are shielded from the database engine specific implementations of a List.

Tutorial Scenario

For our demo, we will consider a group of people who make homemade wine. This group has created a cooperative that has invested in the production of four different barrels of wine. The coop team lead, John Cusomano, desires to use QEDWiki to manage various data processing activities associated with his lead responsibilities. When John originally started the coop he created an Excel spreadsheet to capture key aspects of the coop's activity. In order for John to use this data in QEDWiki, he will need to export the data to an intermediary format, define a QEDWIKi List and import the data into QEDWiki.

Lesson Objectives

  1. Defining a QEDWIki List
  2. Preparing external data for import into QEDWIki
  3. Best Practice for Organizing Project Data
  4. Importing external data into a QEDWiki List

Tasks

  1. Setup Tutorial Environment
    1. Open your instance of QEDWiki
    2. Log into QEDWiki
    3. Create a new page to capture the subpage elements of our tutorial project
      • Click on Page--> New Page
      • Clear the entry field for page name and type: HomemadeWine/MyWine
      • Select Blank Page as your template
      • Click on the Create Page button
    1. This is the root page for your tutorial workspace. We will create all associated project pages as sub-pages to this page.
    2. Click on Page--> Edit Page
    3. Type some text into the page, such as "My Wine Making Project" and Save the page.
  2. Defining a QEDWiki List
    1. Create a new page to define the QEDWiki List
      • Click on Page--> New Page
      • Clear the entry field for page name and type: HomemadeWine/MyWine/BarrelCoop
      • Select Blank Page as your template
      • Click on the Create Page button
    2. BEST PRACTICE: It is recommended that whenever you define a List that will be used by more than one workspace page, you should create a unique project page for the definition of the List. This way other pages can use relative references to your workspace project data. The reason for this practice is that QEDWIki will create a new SQL table for every page that contains a list definition. If a List is used on more than one page you're your database will actually have multiple tables defined and you will then need to prime each table with data. If you desire to share one table of data across an entire workspace, you should follow this best practice.
    3. Click on Page--> Edit Page
    4. Visual Assembly Approach
      • Click on the Assemble button
      • In the Data Access draw of your QEDWiki Widget Palette, scroll down until you see the "List Definition" widget.
      • Drag the List Definition Widget to your new page canvas
      • Click on the "new_list" eyecatcher to bring up the List Definition Property Editor.
      • Fill out the form as depicted by [[../HomemadeWine/AppendixD Appendix D]]
      • Click on Apply
      • Drag a SearchList  widget from the palette
      • This is a non-visual widget so to view the property editor, click on the eyeball icon in the "Components in use Section" at the right lower bottom of the screen.
      • Set the Query parameter to "barrel_coop" which is the identifier you provided in your List definition in step 2. Set the ID field to "coopList".
      • Click Apply
      • Drag a SearchData widget from the palette
      • Click on the eyecatcher icon to bring up the property editor.
      • Set the Data parameter to "#(coopList/available)" and set the ID field to "coopData". Set the edit filed to true.
      • Click Apply
    5. WikiMarkup Approach
      • Click on the Source button
      • Type the Wiki Commands specified in [[../HomemadeWine/AppendixC Appendix C]]
      • Click on the Save button
  3. Importing external data into a QEDWiki List
    1. For convinience, we provide the data (depicted on [[../HomemadeWine/AppendixA Appendix A]] ) required for this tutorial in CVS format available from barrel_coop.csv
    2. Open your browser to your phpMyAdmin panel.
    3. Select your QEDWiki database
    4. Take notice of the table name that ends with "_barrel_coop". The prefix to this table name is the actual framework identifier for the HomemadeWine/MyWine/BarrelCoop page in your workspace. It should look something like wiki_list_p537_barrel_coop_lists where the p537 will be a random identifier.
    5. Select your barrel_coop table
    6. Click on the Import button
    7. Browse to your barrel_coop.csv file
    8. Change the "Number of records to skip from 0 to 1. This is because our dataset specified in [[../HomemadeWine/AppendixA Appendix A]] contains a record header as the first record. A common occurrence for data derived from a spreadsheet.
    9. Click on the "CSV using LOAD DATA"  button in the Format of Input File section (see phpMyAdmin 2.7.0-pl1)
    10. Change field delimited by to a ","
    11. Cancel about the next two fields
    12. Leave lines terminated as "auto"
    13. Enter the comma delimited string for the filed names of our data: Partners,Barrel,Cases,Bottles,Investment,PricePerBottle
    14. Click on Go button
    15. See [[../HomemadeWine/AppendixB Appendix B]] for an example of this task and for the proper resulting SQL statement.
    16. Now upon success, you can click on the Browse button to view your data.
  4. View Data
    1. Now go back to your QEDWiki Instance and open the HomemadeWine/MyWine/BarrelCooppage.
    2. You should now have something similar to the content depicted by Appendix E.
    3. For an completed example see Investor Shares