This post was written in March, so the relative dates are relative to March 2007. However I haven’t got time to test it on before now.
Almost a year ago I started working on a new timetable web interface, since the old was broken, and before that it was slow as hell (>30sec). Well the old system used a Microsoft Access file as database. All the tables in the Access file, was in first normal form and the same data occurred multiple times, fx. you could have a date, dayoftheyear, ISO week, weekday number, weekday 3-letter abbr., full weekday columns in the same table. DOOH! 6 columns instead of just one single date column. Then add the fact that that table had 250k records, just for a few weeks of timetable information, one for each student / date / lesson / room / teacher combination.
The old data chain was:
Oracle (adm. db) -> ODBC -> Access (adm. network) -> FTP -> mdbtools (csv export) -> MySQL -> PHP web interface.
First revision of the new data chain was: Oracle (adm. db) -> ODBC -> Access (adm. network) -> FTP -> mdbtools (csv export) -> MySQL -> PHP web interface.
Because the old Access export worked and I could just use that. Despite all these extra columns, there where no column that identified the activity name. Hey! there are a activity column in the teacher_timetable table, so if the student have a teacher and they are in the same room then they must have the same activity. So after the timetable administrator assured me that I no students will ever be in the same room and have a teacher shared over multiple activities. I made the SQL, to split out the data in third normal form in MySQL. In September it happened all the teachers and students for the first 2 grades where gathered in the cafeteria. Therefore all students where now member of each other’s activities. Well, I tried to edit the SQL to not to link people together, if there was multiple activities present in the same room. But the query was to complex to succed in a reasonable amount of time. In late September my i386 server got some heat problems, so I moved the timetable system to a new domU on my amd64 Xen server. But mdbtools is a unmaintained piece of software with tons of open bugs, and it doesn’t work on amd64, so it was a bad component in chain.
So finally I got permission to replace the Access link in the chain. Therefore in november odbc2csv was in place.
This made the final chain:
Oracle (adm. db) -> ODBC -> ODBC SAK (adm. network) -> FTP -> MySQL -> PHP web interface.
First I wanted to name the tool odbc2csv, but that name was taken, so since this is only version 0.0.1 and I plan to at add multiple other functions, such as connection debugging, multiple connections, odbc2odbc etc. I believe odbc2csv would be too limited any way. I think ODBC Swiss Army Knife is more relevant, because it allows the project to expand beyond the the original scope of the project within the relatively limited scope of ODBC.
Features in 0.0.1:
- ODBC it is possible to have a single ODBC connecting and executing SQL statements and get the result in CSV.
- pwd, cwd and cd – Basic directory functions
- Interactive command-line interface
- Can read commands from file as alternative to the interactive interface
- Microsoft Windows support
- GNU/Linux support (requires unixODBC)
- UNIX/BSD/POSIX support (requires unixODBC)
Visit the odbc-sak project page at sourceforge.net
Update: Since I wrote this I have graduated, and the school has been merged with Grenaa Business School (GHS). There the Grenaa Technical School‘s IT infrastructure has been merged with GHS, in a new setup very similar to GHS’s old setup.
Recent Comments