Getting data out of MySQL (and then back in)
There seems to be lots of ways to get your databases out of a MySQL database and an even more bewildering array of ways to get it back in. I have found that the following commands seem to be the best way to do it (for me):
Out
mysqldump –databases database_name -u username -p > dumpfile.sql
In
mysql -u username -p database_name < dumpfile.sql
Find the reference here.
15/02/10: Here is another variation on getting stuff out:
mysqldump db_name table1 table2 table3 -u username -p > dumpfile.sql
Zipfiles, CSV and UUIDs
MethodBox currently uses a java servlet to create Comma Separated Value (CSV) files inside a zip file. These are then sent back to the rails application on demand over http. What we needed was a metadata file to go into the zip file explaining what the CSV files had in them. It was easiest to use the active records about the CSV columns to get the metadata.
For the zipfile manipulation thanks go to this post and the original rubyzip API.
So, first make the HTTP request:
http = Net::HTTP.new(”localhost”, 10000)
http.read_timeout=6000
response = http.get(’csvserver/download/+ filename)
#check that the request was OK
if response.response.class == Net::HTTPOK
#check what the response contains
if response.content_type == ‘application/zip’
#make a new file to hold the zipped data
uuid = UUIDTools::UUID.random_create.to_s
#you might need to install the uuidtools gem
File.makedirs(RAILS_ROOT + “/” + “filestore” + “/” +filename)
uf = File.open(RAILS_ROOT + “/” + “filestore” + “/” + filename + “/” + uuid+ “.zip”,”w”)
#write the content in the response out to the zip file
uf.write(response.body)
uf.close
After this the metadata is created and written to a string called metadata which can then be added to the zip we just created from the http response. This next line adds the metadata string to the zip inside a file called metadata.txt.
Zip::ZipFile.open(RAILS_ROOT + “/” + “filestore” + “/” + filename + “/” + uuid+ “.zip”, Zip::ZipFile::CREATE) {|zip| zip.get_output_stream(”metadata.txt”) { |f| f.puts metadata}}
We can then send the completed file back to the requester
send_file RAILS_ROOT + “/” + “filestore” + “/” + filename + “/” + uuid+ “.zip”, :filename => “something” + “.zip”, :content_type => @archive.content_type, :disposition => ‘attachment’
I have also been looking at using Ruby to do all the CSV file creation but so far speed is proving to be an issue. However, I found the following link which mentions several libraries. I am now looking at using a combination of FasterCSV and CSVScan (also available as a gem). This enables me to write incredibly complex code like this (don’t you just love ruby’s blocks!):
CSVScan.scan(infile) { |row| line = FCSV.parse(row[0], :col_sep => “\t”); new_row = Array.new; if i==0: variable_hash[key].each {|var| pos.push(line[0].rindex(var))} end; i=i+1; pos.each {|col| val = line[0][col];new_row.push(val)}; csv_arr.push(new_row)}
Yeah, it scared me as well!! It parses a CSV file for several headers and then pulls out the columns which correspond to these headers and adds them into an array. In MethodBox these files can have 2000 header (ie columns) and 20000 rows so you can see why speed is important when only pulling out a subset of these.
We can then use FasterCSV to write out the new CSV file:
FasterCSV.open(outfile, “w”) do |csv_file| csv_arr.each {|csv| csv_file << csv} end
More bits == better? AKA Snow Leopard broke my Gems
Snow Leopard install went fine. Running methodbox after this didn’t. ImageMagick, libxml and mysql gems were all broken. Followed this advice to get it all working. Boils down to re-install MacPorts, install ImageMagick and libxml2 from ports, re-install the RMagick and libxml-ruby gems, install mysql-64bit, re-install mysql gem (in 64 bit mode).
Easy!! Only took 2 hours. So that’s about 4 hours (and 2GB+ downloads) for the snow leopard upgrade so far!
Also found this script for installing ImageMagick. I couldn’t get it to work but maybe you can.
There’s something good around the corner
Up until now we have been using images to fake rounded edges on divs etc. This is fine until you want to change the styling and resize anything. So, I had a look at doing it in pure CSS. Luckily, so have lots of other people eg. Nifty Corners and Spiffy Corners. I used the Spiffy Corners one since it comes with a nice code creator which saves lots of time and is easy to re-use. All you have to do is enter in the class name for the CSS, colour of the bar and the background colour and it generates both the CSS and the html. Job done.
Sorry for the delay - we’ve been on the rails
So it’s been a few months since the last post about adventures in flex. The final verdict, quite nice but without more developer support the obesity-elab project was too big for one person. So, we decided to go with the flow and follow the rails path like myExperiment and Sysmo. This gave us a big codebase to play with. We still needed some back end components to handle csv file parsing and creation so a jetty based webapp was created using the Ostermiller Utilities for the CSV handling. We (well, me) also used Apache solr with the ruby acts_as_solr plugin for searching the metadata.
Writing complex apps in html is certainly an interesting challenge and after having created the first version of what we now call methodbox I can see why frameworks like Google Web Toolkit and flex are so appealing. Having to insert hidden objects into the html so the application can remember them between clicks gets a bit tedious after a while! However, we overcame a few hurdles along the way and here are some things I found useful:
Things I needed to know about Rails but was too much of a newby to find out from the books
1. routes.rb. How to specify routes correctly. Part of the functionality of methodbox is selecting multiple surveys and searching them. I couldn’t get the app to return to the correct route after doing the search. Of course, I had not specified the route correctly. So, if you are returning a collection you need something like this:
map.resources:surveys, :member => {:download => :get}, :collection => {:add_to_pseudo_cart => :get, :data => :get, :datagrid => :get,:hide_info => :get, :more_info => :get, :search_variables => :get,:sort_variables => :get } do |survey|
2. Check box tag. Use check_box_tag to allow you to select multiple objects in the page and send them across inside a hash to the controller.
<%= check_box_tag “entry_ids[]”, item.id,false, :class=>’survey_checkbox’ %>
where item is the object which you want to select or not, false means unchecked when created
3. Selecting, unselecting checkboxes programatically. Lets say you want a gmail style select all, none (and an ‘invert’ selection - ie. select those not selected and vice versa). Use link to function and some javascript like this:
<%= link_to_function ‘All’, “$$(’input.survey_checkbox’).each(function(checkbox) { checkbox.checked = true; });” %>
<%= link_to_function ‘None’, “$$(’input.survey_checkbox’).each(function(checkbox) { checkbox.checked = false; });” %>
<%= link_to_function ‘Invert selection’, “$$(’input.survey_checkbox’).each(function(checkbox) { if (checkbox.checked == true) {checkbox.checked = false;} else {checkbox.checked = true; } });” %>
where ’survey_checkbox’ is the class name of the check_box_tag (see 2 above).
4. XML creation and parsing. Tried the various libraries but found libxml the fastest. I needed to parse some fairly big (1 meg+) xml files which contained the metadata for the surveys we are using. These surveys take the form
<metadata year=“2000″> <variable> <name>difbpc1</name> <description>BP Probs: No problems taking blood pressure</description> <information> <info> <Value>-9</Value> <Label>Refusal (8)</Label> </info><info><Value>-8</Value> <Label>Don’t know (9)</Label> </info></information> <MissingValues>-99 thru -1</MissingValues> </variable> …. …. </metadata>
Here is some sample code to parse (some of) this using libxml (don’t forget your require ‘xml’ bit):
parser = XML::Parser.file(’metadata.xml’)
doc = parser.parse
nodes = doc.find(’//metadata/variable’)
nodes.each do |node|
namenode = node.find(’child::name’)
namecontent = namenode.first.content
variable.name = namecontent
descnode = node.find(’child::description’)
desccontent = descnode.first.content
catnode = node.find(’child::category’)
catcontent = catnode.first.content
dernode = node.find(’child::derivation’)
dercontent = dernode.first
dertype = dercontent.find(’child::type’)
dertypecontent = dertype.first.content
dermethod = dercontent.find(’child::method’)
dermethodcontent = dermethod.first.content
infonode = node.find(’child::information’)
infocontent = infonode.first.content
end
5. gotAPI. I used this online resource ‘a lot’. A superb go to when the books are confusing or hard to navigate.
So, a few things which confused me and one major resource you really need. See you on the Rails.
