Specify: possible solutions?

Brainstorming database solutions.

– Use Specify and jettison some data that won’t fit. This wouldn’t go over well.

– Use Access and deal with not having the data schema or the Specify software (which seems like it can work fairly well).

– Use MySQL and build a frontend in PHP that mimics Q&A, including the ability to rearrange fields, sort things easily, and add new fields when needed.

– Use MySQL and steal the schema from Specify, and build a frontend in PHP.

– Use MySQL and steal the schema from Specify, and use Access as a frontend.

– Use Specify and add the columns to the tables that need to be added with Access (I think this can be done), use Specify for the web interface and Access for on-site.

– Keep bugging the Specify team to tell me how the program knows when it needs to display new tables and hope they eventually tell me (see a couple posts back).

– Waste a lot of time figuring it out on my own.

Why does it seem like the last option would be the best?

Links that need to be made into a tutorial.

Trying something new . . . again (part 2) (Moving an xampp installation on Mac OS X)

I spent most of the day dealing with MS SQL Server Express and converting a database to MySQL (more on that later, hopefully), but then I came home and happened to restart my laptop, which is what the last post was about.

I did manage to move the XAMPP folder to another directory and out of /Applications. I chose to move it to /Users/Matt/xampp, so that if I need to upgrade in the future all my server stuff will remain intact if I use Migration Assistant. I also pointed the server at /Users/Matt/Sites rather than XAMPP’s own htdocs so that all my web stuff was still in the same place.

To do this, I created one symbolic link and modified two files.

1. To create the symbolic link in /Applications that would point to /Users/Matt/xampp/, I opened Terminal and ran this command:
ln -s /Users/Matt/xampp /Applications/xampp

2. To point the XAMPP Apache server at the new folder, I changed this line in /Users/Matt/xampp/etc/httpd.conf from
ServerRoot “/Applications/xampp/xamppfiles”
to ServerRoot “/Users/Matt/xampp/xamppfiles”

In fact, I seem to have been overzealous and changed every instance of that path in httpd.conf, which probably wasn’t necessary, since we can usually trust the symbolic link to do its job.

3. To get MySQL to be recognized, I changed the location of the socket (mysql.sock) in /Users/Matt/xampp/etc/my.cnf from
socket= /Applications/xampp/xamppfiles/var/mysql/mysql.sock to socket= /Users/Matt/xampp/xamppfiles/var/mysql/mysql.sock
Again, this may not have been necessary, but things work now and I don’t want to mess them up again.
You may feel pretty cocky now and want to change the setting for mysql.default_socket = in php.ini. This will most likely break your connection between PHP and MySQL, and I’m not sure why.

So there you go: your very own XAMPP server, sitting inside your Home folder where it will be nice and protected.

Trying something new . . . again (part 1)

Since I just had to reinstall Leopard and lost all my custom PHP and MySQL installations (and the MySQL databases, unfortunately), I tried to remedy that yesterday and today.

I’m still pissed off that OS X doesn’t come with working PHP and MySQL out of the box, including the PHP libraries. With a simple way to know where the data are being stored, and a way to back it up (in one’s Home folder, perhaps). But I digress–I’m sick of spending hours getting things to work when they ought to work.

So I decided to try out XAMPP for Mac (I tried it a few years back for Windows and enjoyed it), and it worked. Here are a few notes.

1. Everything is installed in the Applications folder. I’m not sure I like this, since I hate having stupid little folders (and DATA) in the Applications folder. I’m not sure if this can be easily changed, perhaps with a symbolic link to somewhere else (such as my Home folder, so it will get backed up).

2. Leopard MySQL was running when I installed, so I had to kill it with ‘sudo killall mysql’ and it will hopefully not start up again when I restart. The main reason I was having issues was that Leopard AGAIN lost mysql.sock.

3. I turned off Web Sharing in System Preferences. This is probably a good thing, and it will at least keep my computer from trying to run two Apache servers at once.

4. I wanted to keep the Sites folder as my web root for localhost, so I opened /Applications/xampp/etc/httpd.conf and changed DocumentRoot to point toward my Sites directory (e.g., “/Users/Matt/Sites”). I then copied everything in /Applications/xampp/xamppfiles/htdocs into /Users/Matt/Sites, and things seem to still work fine.

5. The MySQL databases, in case you want them backed up with the rest of the file structure rather than using phpMyAdmin, are stored in /Applications/xampp/xamppfiles/var/mysql. I may try to move them (with a symbolik link) to be stored in my Home folder since I won’t be keeping a lot of data in them but it’s annoying when I lose settings for various PHP applications (Simple Machines Forum, OpenX, Drupal, Serendipity, etc.).

6. I cannot get MySQL to work if I set a password for it, which means no logging into phpMyAdmin and no access to the MySQL databases. I get “Access denied for user ‘root’@’localhost’ (using password: NO)” instead, until I reset the password to blank. (Note to self, to do this, the command is [sudo ./bin/mysqladmin -u root -p password ”] (no brackets). The -p is important, because it will them ask you for your current password rather than assuming that you just didn’t want to supply it. Idiotic.)

7. Overall, aside from having to install all my databases again (not the fault of XAMPP), it seems to be working well so far. It just miffs me that I have a non-functioning version of MySQL on my laptop as well as an unused (although missing certain useful libraries) version of PHP 5 floating around. At this point I’ll take what I can get.

Specify 5.2 and my quest to import lots of data

I’ve been unable to figure out how to get a username and password to login to the Specify forum; I’ve requested help with that but until I get a response, I’m going to shout my questions out to the ether and hope I get some echos of answers back.

UPDATE: Apparently you have to email specify@ku.edu to get a login (source).

1. When is Specify 6 coming out? Originally it was early 2008, then it was mid 2008, and I haven’t seen any updates recently. I hope it comes out soon.

2. Is there a Specify mailing list? If there isn’t, there should be, especially since it seems like nobody is able to log into the official forum. I might be interested in getting one going depending on interest, since there are supposedly lots of people using the software. There is a mailing list here.

3. The big question: is it possible to add more than the default offering of a few new fields to each form? I’m on the cusp of moving over a great deal of data, and I’d like to be able to include everything and add fields as necessary, but that doesn’t seem to be an option.

I’ve tried adding fields to the database itself (through Server Management Studio Express), but I think the number and name of each field is hard-coded into the Specify program rather than being stored as an expandable list in the database, because the new field doesn’t show up when I go to edit forms. For example, each field has both a name and a set of properties–the properties (such as what kind of data is allowed, how long it is allowed to be, etc.) are described in the database tables, but I have not been able to determine where the name of each field is stored. Hold up. It has to be stored somewhere, because it can be changed by the user. I may have to dig deeper. Sometimes talking through things does lead to answers. We’ll see what I can dig up.

In any case, I’m very comfortable with messing with the database tables themselves in order to get the data in, which is what I’m going to have to do anyway, because the data will be coming out of a tab-delimited file when I’m done with it and should be easy to get into SQL. It’s just a question of making sure those columns exist in the tables first…

Anyway, that’s where I stand. I’ll update when I know more.