Apr 152010
 

So, you have a nice new database table, and you forgot (or couldn’t) add comments to the columns when you created it. I recently ran into this problem, and my usual internet searches didn’t turn up much.  One would expect a statement similar to this:
ALTER COLUMN `columnname` ADD COMMENT 'a comment'
However, that statement doesn’t exist.

If you have gotten to my post, you probably realize that there is no easy way to add comments (unless you have a nice application that does it for you). The real statement is something along the lines of this:
ALTER TABLE `courses` CHANGE `courseid` `courseid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'a new comment'

Which means you need to know the table definition, and restate it every time you want to change the comment. If you are doing this in bulk, that is a pain in the butt.

So, I wrote this little script that will take a create table definition, and turn it into Alter Table commands, that will add comments to each of your columns.

All you need to do is run ‘SHOW CREATE TABLE `tablename`’ on your database table, and insert that definition into my script here, and it will spit out a series of Alter Table calls with a space for a comment appended to the end.

The script is very basic at this point, and expects the formatting as shown in the example. The GUI I use, DbVisualizer, as well as the mysql command line client spit this out. So hopefully yours will to.

If you find this useful, or run into any problems with the script, please let me know.

Edit (April 15, 2010): Uploaded v0.2. Small mistake that was placing the column name in place of the table name. Sorry to anyone that was used it before and noticed it.
Edit (November 17, 2013): You can download the php script here for running on your own server.

Dec 062009
 
Edit: I’ve made a new post regarding Hesk and these enhancements for the new version 2.2. You can read it here: http://cornempire.net/2010/07/31/hesk-enhancements-for-version-2-2/

I came across the free, open source Hesk Helpdesk (http://www.hesk.com) while I was looking for a light weight help system for a project that was starting up. The project has since been put on hold, but Hesk is still a great application for a helpdesk/knowledge base. Hesk is offered as a free downloadable PHP script, as well as a Hosted Version if you do not have a server. The hosted version will cost you $20 US a month. You can also by a license for the downloadable version for $39.95 US to remove the Hesk branding.

While this application is great…

  1. Allows unlimited staff
  2. Granular permissions to the system and help desk categories
  3. Excellent knowledge base built in
  4. Innovative system that doesn’t require user accounts for clients

It is lacking a few features. I plan to use this application to respond to clients from a number of different projects. As such, it would be nice if they didn’t see ALL of the problem types when submitting tickets. Because Hesk is open source, we can make a few tweaks to get directly to a specific problem type.

Hesk also allows custom fields for your tickets, but it shows all custom fields on all problem types. This can get confusing for users in a multi-project environment. I’m developing a little hack to get around this limitation.

I’ve outlined both of these changes required on my wiki, you can take a look here.

Jul 072009
 

Note:

Instead of editing my previous post to pieces, I thought it might be best if I repost my script with a better description of how it works. I’ve written up documentation of the script on the CornEmpire Software wiki which is available at http://wiki.cornempire.net. The documentation directly related to this script is available at http://wiki.cornempire.net/doku.php?id=d2l:bulkdeactivate

Introduction

One of the often requested features is to be able to bulk deactivate courses in D2L after they have completed. You can currently deactivate courses, but this is a one at a time effort. This has become more important as version 8.3 of D2L brought along a new My Courses widget, which allows users to see updates from each of their courses. Unfortunately, when course access ends, they can still see the updates. This has caused confusion for many users.
Another reason to deactivate courses is minimize the amount of clutter in the view of users. If course access has ended, and they cannot access the course, there is no need for the course to remain active (this could vary depending on how your roles are configured). A way around this is to script the bulk deactivation of courses. Through the use of some Javascript coding, we can instruct the web browser to visit each course, and deactivate it for us.

See the script in action.

Would you like to see more? I have the script, and it’s documentation available on this page: http://wiki.cornempire.net/doku.php?id=d2l:bulkdeactivate

If you have any questions, please post them below.

Jun 102009
 

Update

Please see my new post on this topic. Available at: http://www.cornempire.net/2009/07/07/d2l-bulk-deactivation-part-2/

Introduction

One of the often requested features is to be able to bulk deactivate courses in D2L after they have completed. You can currently deactivate courses, but this is a one at a time effort. This has become more important as version 8.3 of D2L brought along a new My Courses widget, which allows users to see updates from each of their courses. Unfortunately, when course access ends, they can still see the updates. This has caused confusion for many users.

Another reason to deactivate courses is minimize the amount of clutter in the view of users. If course access has ended, and they cannot access the course, there is no need for the course to remain active (this could vary depending on how your roles are configured). A way around this is to script the bulk deactivation of courses. Through the use of some Javascript coding, we can instruct the web browser to visit each course, and deactivate it for us.

Example

Take a look here for this script in action.

If you think that this might be useful for your organization, you can download the html file which includes the javascript.

For D2L Version 8.3
For D2L Version 8.4 (Thanks Dennis!)

Instructions on Use

  • You will need to edit the file and modify line 5. This contains the URL to your learning environment, and the URL to your Course Offering Information screen.
  • This file will need to be placed in one of your courses in your learning environment in the Manage Files area.
  • You need to open the file from your Manage Files section and provide the script with the OU numbers of the courses you need to deactivate. (Take a read below to see how we get the ou numbers)
  • Once the OU numbers are loaded, you can click the Start Processing button and your web browser should go to all the courses and deactivate them.

Limitations

  • You are required to have administrative access to any courses you need to deactivate (I haven’t been able to figure out why this is, but a javascript error is thrown if you do not have admin access)
  • This has been tested in Firefox 3 on Windows XP and Ubuntu 9.04 as well as a Chrome Development Beta for Linux but may not work in other web browsers (especially IE).
  • This has been tested on D2L Version 8.3 MR01. I’d suspect it would work on any version of 8.3 but not sure about 8.4 yet, or any earlier versions.
  • I’m not responsible if this code melts your CPU. or causes any other horrible damage to your D2L installation (although it shouldn’t….it’s only about 35 lines of Javascript. :p)

Getting a List Of Courses

The tricky part in all of this is actually getting the OU numbers for each of the courses you need to deactivate. For those who don’t know, the OU number is the org unit number, and is an internal identifier for D2L. You will notice the ou number in the URL of courses as ou=#####.

We run a report using the reporting tool that gives us the ou numbers (in one, non-ideal form. Hopefully D2L web services will eliminate the need for this).

In the Reporting tool, create a report using the Org Units dataset. With in the report, include the column for the course Path. This path contains the OU number for the course. Under the filter tab, add a few filters that will narrow down your search to the information you require. Our organization adds a semester code to all of our courses, so all courses that end in 200802 are courses that ran in the last semester. We can query these courses, and get a list of the ou numbers through the course offering paths. See below for some images of our report in action.

report1

report2

report3

report4

Then you have to do some work in manipulating the report to filter out the OU numbers. I select the whole column using firefox, and dump it into OpenOffice. Customize the import to break the lines on all of the dividing characters, and then I have a column of ou numbers. Then take that, and add in commas after all of the entries using a find and replace. You will need a text editor that can find and replace on characters you cannot see. Replace the new lines with commas.

Hopefully you can find a better way to get your list of OU numbers ;) . If you have a good way, leave a comment and let me know.