I think I tl;dr a few of the posts in this topic, but a lot of the ones I've read were good ones.
Personally, I agree that magic in Dwarf Fortress mode should be rare. Not minor or ineffective - just rare. I think that sorcery should all-in-all have the same impact that artifacts do. I think that there should be skills such as healing, alchemy, etc. which look close to magic, but ultimately either fall short while still being more practical and common than actual magic, or - if a magic user so desires - may be a tool for a magic user's studies.
When it exists, I think that magic should be able to be applied to any area of life, but rarely be worth whatever price tag associated. On the subject of prices, I think that the prices of any particular magical effect should be pretty much randomized, and I think a great deal of a mage's time won't be spent casting magic so much as studying it. I think that entire years could by with a spellcaster doing nothing more than constructing and researching a new spell.
Speaking of randomness, I think that should be a huge factor in making magic generally useless. How useful, for example, is a spell which the sole function of it is to turn a wooden chair into a iron chair, at the cost of a piece of fruit, some of the caster's vitality, and a few hours of casting, particularly if it has the nasty side effect of pushing everyone in a 50 square radius two steps closer to madness, and can at times kill small infants who are nearby? Not very. We've got a large set of side effects and problems, and all we get from it is being able to - very slowly - turn wood and fruit into iron, but only if we've converted the wood first into a chair shape, and after which we must melt the iron down, unless a chair is exactly what we wanted.
But then what's the use of magic if it can't be useful? A spellcaster will answer that any spell, potion, or enchantment can be refined. Over years, a few of those naunces might be removed. Someday, you may even get a powerful art of converting wood into iron, and should you train an army of spellcasters into archmagi, you might even get a working factory.
But I include that I think magic should take time - time to learn, time to master, time to perform. Barring all other costs, magic should take large investments of time. I don't think any mage short of Legendary in whatever magic skills there might be should be able to perform anything remotely useful. A mage short of legendary may be able to learn a few spells, and might even contribute to research - much in the same way a dabbling miner may be able to help mine out a fortress for five hundred dwarves. And even once a mage masters the general skills and theories, it should still take long periods of time studying and mastering each individual spell - even moreso if they're the ones inventing the spell. Time to master the spell, time to copy the spell, make spellbooks, sort through them, research created spells, study the effects of various objects.
One thing common in fantasy settings is that wizards of any reasonable talent are generally old men who have been performing their art since a young age, spending a good amount of time studying under another old man who followed the same pattern. They just seem to forget to include this vital point during actual gameplay for the sake of mechanical balance.
And of course, the point that others raised is the extensive resources it costs to research magic things. Even if you get that fireball spell to accurately function as a weapon with no real side effects and fairly efficient costs, and even if you do spend years training a small squad of mages to be able to cast it effectively... the sheer monetary value of all the resources that would have gone just to research and master such a spell would probably be equivilant of a typical artifact, and the costs to train mages to cast it would likely be even higher, nevermind the original wizard who had to invent and master the spell in the first place.
That all said, I do believe there's a Wizard arc planned, where instead of playing a fortress, you play a wizard. That'll be awesome. I cannot wait to research spells that turn doors into walls and walls into chairs and stuff. Oh yes.
I like the guy's "research spells" idea.
I'd sort of like to elaborate on how it would work gameplay wise (if you don't agree, roara or anybody, feel free to debate. Compromise is always good).
First off, world gen should start with innate magical force. This magical ability should manifest itself in natural applications and in people. These people, who would be, in and of their own right, legends (after all, they are taking the pure magical essence of the world and using it, with their own magic diluting throughout the ages) and should have the ability to, over time, learn more abilities like the previously suggested ones, but randomly. Once we get done with all those world gen super wizards (who should be dead by the time your fortress starts. If you start a fortress in year 1 of your world, you probably aren't going to have anything written down) the magicians Roara suggested could kick in. The grand magicians will have passed down some of their magical secrets, and as other magicians rise and fall their secrets could be written down or die with them. Eventually, you would have access to this research, either through traders or through the king, and then you can start it.
Also, magic, while random, should be limited to the materials at hand. There shouldn't be a spell that requires whale bones to turn aluminum into adamantine if you are on an inland fortress with neither aluminum nor whales and with a mage who never saw adamantine in his life. (Although speaking of aluminum, a spell that extracts it from bauxite would be nice). I think magic should probably come in a few seperate categories which could then be altered.
First off, is damage magic. Obviously, these spells cause damage, either by directly wounding the creature or all creatures in an area, or by causing high/low heat or creating poisonous gas. It could even lead to useless things, such as destroying the fur off hoary marmots or a poison that only affects turtles, but could then be refined to a spell which damages any creatures skin or a general use poison, which could then be re-refined into a more powerful poison or spell just for use on one creature. These spells should generally have high costs.
Research on this magic should be at least somewhat directable: After your magician has a spell, he should work on mastering it and then should have you decide what direction you want to take it (do you want to increase the power of a knockout gas spell to make it deadly, or do you want to make it work on dwarves only, so you have a safe dwarf containment spell).
Second is transformation magic. It can start like Roara said, wood chairs to iron chairs, and eventually get stronger and broader in it's use. Not much else to do on this.
Third should be magic that affects creatures bodies. It could be used to cure them or alter them, so it is transformation magic, in a way. You could have magic that heals wounds caused by bronze at the expense of ripping up your spellcaster, or a spell that stops bleeding but causes all other creatures in the area to lose a large amount of blood, and once again, after a spell is mastered you should be able to tailor the research to some degree.
And so on and so forth. Really, magic seems like it could be a really fun thing to do with the game.