gentoo packages that need lovin'

I mentioned not too long ago that I was working on getting portage details crammed into postgresql, and here is the end result.

GPNL is meant to be a QA tool for treecleaners to use, making it easier to find packages and ebuilds that … well, need some lovin.

Though it’s primarily intended for quality assurance, I’ve written the frontend to be hopefully pretty generic so anyone can browse the portage tree and just see some interesting statistics all around. There’s still a lot more to be done on the website, but I think it’s to a point right now where it’s at least ready for some public consumption.

One thing I’m excited about is setting up the advanced search page, where you’ll be able to run all kinds of funky queries. I’m going to be adding some more QA checks as well, once I get some time. Getting this much done though was quite a lot of work though, and I’m probably going to take a break and focus more on other things for a while. However, if anyone has some reasonable feature requests, I’m all ears.

Oh, also the source code for the database schema and the import scripts is available online. I’ll setup SVN access and some documentation on the db layout sometime soon, not to mention how to get it working (short howto: emerge php, pkgcore, postgresql and portage-utils).

Also, a huge shout out to marienz and ferringb who put together pkgcore and my little python scripts that made importing the data incredibly simple. Thanks, guys. :)

giving thanks

Something I find interesting lately, is that with the Playstation 3 coming out, I haven’t seen hardly any positive press about how this thing freely lets you run other operating systems on it. Geeks will get all up in arms about DRM and lockdowns (which is a good thing), but nobody seems to really give say too much when someone actually does open up a platform. I must be reading the wrong blogs, or something.

Well, I think it’s cool. They even have a page about how to get started hacking your playstation. I’m not much of a gamer, and certainly not a console one, but just the fact that you can freely screw around with it makes me think about getting one someday.

ivtv-0.8.1 released

Just another quick Gentoo public service announcement. Hans released a new version of ivtv, v0.8.1, and this comes with a lot of cool updates. It’s already been bumped in portage as well, so for those of you using Hauppage PVR cards, give it a whirl. Note that you’ll have to install it with a 2.6.18 kernel because of the new MPEG2 API.

I’ve been crazy busy lately, but I installed mine and managed to record and watch about 15 seconds of TV just fine with MPlayer’s new PVR functionality, so it seems like it’s okay. I’ve been rebuilding my server all weekend, and hopefully I’ll get around to finishing up with mythtv and testing it out again. With luck, my squelchy audio issues will go away too. :)

lds-scriptures postgresql update snapshot

I have not worked on my LDS Scriptures project in a very long time.  In fact, annual realeases are becoming an embarrassing reality.  However, I’ve been itching to update the project for a while now, since I’ve been learning quite a lot more about databases since my last release.

I had an interesting idea for doing some custom RSS feeds of pulling down scriptures (which I’ll get into at a later time), but I quickly realized the current db schema wasn’t quite up to par. I started poking around the postgres one, and it was much easier than I imagined to fix it up quite a bit.  I dropped all the tables and recreated them, this time with primary and foreign keys, and I got rid of some columns that were completely unnecessary.  I renamed some too, along with the other general cleanup.

I tarballed the snapshot once I was finished.  This isn’t what I’m going to release as the final 3.0 version, since I still need to add things like views and more indexes, but this is the direction I’m going to be heading.  You can download the snapshot tarball here.  Feedback is welcome, as always. :)

icewind dale

I’ve been re-repping all my music CDs (again), this time to MP3s because I want to be able to listen to them in my car. I’ve got all the love in the world for Ogg Vorbis, but let’s face it, nothing supports it, and music is my life. I’m always listening to music. I’m rewriting a little frontend of mine that I’ve been using for years to access my music too. I used to kludge the MP3 metadata into tags and sort my music that way, but now I’m just going to tag it in a database or something and pull them out that way.

Anyway, the cool thing going through all my CDs (and my crap, there are a lot of them) is that I get to handle all the ones I haven’t listened to in a long time, and as a result sort of forget that I have. The soundtrack to Icewind Dale is one of them.

Now, first of all, it’s a real shame there are not more soundtracks to computer games out there. The concept slowly seems to be becoming more popular though. This one is well worth it if you are into celtic, ambient, new age or instrumental music. Most of the tracks are pretty short, but still amazingly good. “Kuldahar’s Theme” is one of the most beautiful pieces I’ve ever heard across my entire collection. The entire score is very high quality.

If you are interested in getting your own, just buy a copy of the Icewind Dale Ultimate Edition package. It will come with a copy of the CD.

The game itself is incredibly fun. I’ve played (and solved) the first one, along with the expansion pack, Heart of Winter. I really wish there were a lot more games out there based on the Baldur’s Gate game engine. Hopefully they will open source it someday so development can continue. I haven’t solved Icewind Dale II yet, or played it in a while … I kind of remember it as being a little bit harder.

The crazy thing I found out while doing my research about the soundtrack again was that there was one released for Neverwinter Nights … perhaps my most favorite game evah! And I don’t even have a copy! Not only that, but Neverwinter Nights 2 came out last month. Man, I am really not on the ball. Unfortunately, I’m too poor to rush out and buy one off the shelf right now (car payments and all), but I certainly can’t wait. I tell you what.

death to sql server (part 4)

I’ve written about this before, but to rehash … the date functions inside SQL server suck. What’s really weird is that there’s an undocumented way to retrieve out certain datetime formats, and even that is inconsistent in its numbering scheme.

The way to pull them out is by running “SELECT CONVERT(VARCHAR, GETDATE(), @x);” where @x is a positive integer. If you can find the right integer, you can save time and pull out something directly like ’11-10-2006′ as your variable.

One of the problems you’ll run into though is that you can’t just do 1 through $integer. Only some of them return something, and the ones that don’t just throw an SQL error, so you get to hunt down which integers return something.

Well, digging for them manually once is something I don’t want to repeat, so I wrote a query statement to pull out some of them. This could be a handy reference inside your database somewhere.

DECLARE @x int;
SET @x = 1;
WHILE @x 0 AND (@x 20 AND @x <25) OR (@x > 99 AND @x < 115) OR @x IN(126,130,131)) BEGIN
SELECT @x = (@x +1);


title of liberty

Reading my scriptures this morning, I came upon Alma 46 (in the Book of Mormon). This is the chapter where Moroni raises the title of liberty, and though I’ve read this many times before, for some reason it really stirred me up this morning. I thought it was pretty cool, and wanted to share a select part of it, but it’s so good, I’m just gonna copy most of it, since it’s such a great story.

A quick background explanation is probably in order, too. This takes place in the Americas, 73 B.C., to a people then called the Nephites and Lamanites. The Lamanites (native Americans, now … all the Nephites were destroyed, which is a major topic in the Book of Mormon) had just lost a major war to the Nephites, but now there is dissension from within by a man named Amalackiah who wants to be king.

1. And it came to pass that as many as would not hearken to the words of Helaman and his brethren were gathered together against their brethren.

2. And now behold, they were exceedingly wroth, insomuch that they were determined to slay them.

3. Now the leader of those who were wroth against their brethren was a large and a strong man; and his name was Amalickiah.

4. And Amalickiah was desirous to be a king; and those people who were wroth were also desirous that he should be their king; and they were the greater part of them the lower judges of the land, and they were seeking for power.

5. And they had been led by the flatteries of Amalickiah, that if they would support him and establish him to be their king that he would make them rulers over the people.

6. Thus they were led away by Amalickiah to dissensions, notwithstanding the preaching of Helaman and his brethren, yea, notwithstanding their exceedingly great care over the church, for they were high priests over the church.

7. And there were many in the church who believed in the flattering words of Amalickiah, therefore they dissented even from the church; and thus were the affairs of the people of Nephi exceedingly precarious and dangerous, notwithstanding their great victory which they had had over the Lamanites, and their great rejoicings which they had had because of their deliverance by the hand of the Lord.

8. Thus we see how quick the children of men do forget the Lord their God, yea, how quick to do iniquity, and to be led away by the evil one.

9. Yea, and we also see the great wickedness one very wicked man can cause to take place among the children of men.

10. Yea, we see that Amalickiah, because he was a man of cunning device and a man of many flattering words, that he led away the hearts of many people to do wickedly; yea, and to seek to destroy the church of God, and to destroy the foundation of liberty which God had granted unto them, or which blessing God had sent upon the face of the land for the righteous’ sake.

11. And now it came to pass that when Moroni, who was the chief commander of the armies of the Nephites, had heard of these dissensions, he was angry with Amalickiah.

12. And it came to pass that he rent his coat; and he took a piece thereof, and wrote upon it–In memory of our God, our religion, and freedom, and our peace, our wives, and our children–and he fastened it upon the end of a pole.

13. And he fastened on his head-plate, and his breastplate, and his shields, and girded on his armor about his loins; and he took the pole, which had on the end thereof his rent coat, (and he called it the title of liberty) and he bowed himself to the earth, and he prayed mightily unto his God for the blessings of liberty to rest upon his brethren, so long as there should a band of Christians remain to possess the land–

14. For thus were all the true believers of Christ, who belonged to the church of God, called by those who did not belong to the church.

15. And those who did belong to the church were faithful; yea, all those who were true believers in Christ took upon them, gladly, the name of Christ, or Christians as they were called, because of their belief in Christ who should come.

16. And therefore, at this time, Moroni prayed that the cause of the Christians, and the freedom of the land might be favored.

17. And it came to pass that when he had poured out his soul to God, he named all the land which was south of the land Desolation, yea, and in fine, all the land, both on the north and on the south–A chosen land, and the land of liberty.

18. And he said: Surely God shall not suffer that we, who are despised because we take upon us the name of Christ, shall be trodden down and destroyed, until we bring it upon us by our own transgressions.

19. And when Moroni had said these words, he went forth among the people, waving the rent part of his garment in the air, that all might see the writing which he had written upon the rent part, and crying with a loud voice, saying:

20. Behold, whosoever will maintain this title upon the land, let them come forth in the strength of the Lord, and enter into a covenant that they will maintain their rights, and their religion, that the Lord God may bless them.

21. And it came to pass that when Moroni had proclaimed these words, behold, the people came running together with their armor girded about their loins, rending their garments in token, or as a covenant, that they would not forsake the Lord their God; or, in other words, if they should transgress the commandments of God, or fall into transgression, and be ashamed to take upon them the name of Christ, the Lord should rend them even as they had rent their garments.

22. Now this was the covenant which they made, and they cast their garments at the feet of Moroni, saying: We covenant with our God, that we shall be destroyed, even as our brethren in the land northward, if we shall fall into transgression; yea, he may cast us at the feet of our enemies, even as we have cast our garments at thy feet to be trodden under foot, if we shall fall into transgression.

23. Moroni said unto them: Behold, we are a remnant of the seed of Jacob; yea, we are a remnant of the seed of Joseph, whose coat was rent by his brethren into many pieces; yea, and now behold, let us remember to keep the commandments of God, or our garments shall be rent by our brethren, and we be cast into prison, or be sold, or be slain.

24. Yea, let us preserve our liberty as a remnant of Joseph; yea, let us remember the words of Jacob, before his death, for behold, he saw that a part of the remnant of the coat of Joseph was preserved and had not decayed. And he said–Even as this remnant of garment of my son hath been preserved, so shall a remnant of the seed of my son be preserved by the hand of God, and be taken unto himself, while the remainder of the seed of Joseph shall perish, even as the remnant of his garment.

25. Now behold, this giveth my soul sorrow; nevertheless, my soul hath joy in my son, because of that part of his seed which shall be taken unto God.

26. Now behold, this was the language of Jacob.

27. And now who knoweth but what the remnant of the seed of Joseph, which shall perish as his garment, are those who have dissented from us? Yea, and even it shall be ourselves if we do not stand fast in the faith of Christ.

28. And now it came to pass that when Moroni had said these words he went forth, and also sent forth in all the parts of the land where there were dissensions, and gathered together all the people who were desirous to maintain their liberty, to stand against Amalickiah and those who had dissented, who were called Amalickiahites.

postgresql functions

One thing I love about (advanced) databases is that you can write functions. They speed up the query time quite a bit, and you can do fun stuff like IF … ELSE … THEN statements.

In working on getting portage into postgres, part of the problem I’m trying to solve is find out where QA issues are so they can be fixed. Unfortunately, in the early stages of my little script, it always assumes that the everything is working correctly across the board, so I’ll write my queries assuming the foreign keys won’t break. In reality, that doesn’t happen, and I end up killing a transaction with hundreds of thousands of statements because there’s 25 queries that break.

So, I had to write a postgres function to do the checking for me. This is going to be absolutely boring to those db gurus out there, but this is still slightly new to me, so I’m really enjoying it.

use_id integer;
SELECT id FROM use WHERE name = $2 AND
(package IS NULL OR package = $3) LIMIT 1 INTO use_id;
INSERT INTO ebuild_use (ebuild, use) VALUES ($1, use_id);

A really simple function, I know. All it does is run a SELECT statement to see if my foreign key is going to break *before* running the INSERT statement. That way, I can continue on my happy way with my transaction, and at the same time, if I want to turn on ‘qa mode’ when inserting the data, I can check for a false return on the recordset, and know which ebuilds need attention.

Pretty cool stuff, I think. Also, for the record, pgaccess is a *great* little GUI tool to quickly and easily edit your functions.

portage in postgresql

Well, I’m bored, so I figured I’d spill the beans on a project I’ve been keeping under wraps for a while.

I’ve been working on getting everything about the portage tree into postgresql so you can run all kinds of queries. What kinds of queries? How many ebuilds use eclass ‘eutils’ and USE flag ‘alsa’ and are in ‘video’ herd and amd64 is masked but x86 isn’t. That kind. Funky ones. :)

I must say, I really love postgresql even though I haven’t been using it regularly for a long time, I’m quickly getting back into it. The simplicity, the standards, the power, the tools … postgres has it all. Ahh, fanboyism.

Anyway, getting the details of the ebuilds was made incredibly easy thanks to marienz and ferringb and their work on pkgcore (and a custom python script). After that, it was just a matter of parsing the information and setting up the schemas. My importer is written in PHP and the class to import / read the data is still in its slightly butt-ugly stage. It can use some cleaning up, for sure. The database layout is going to be where the real optimizations are though. I’m going to work on setting up some good views so it will be easy to query. Right now, here’s the list of tables I have setup: arch, category, ebuild, ebuild_arch, ebuild_eclass, ebuild_homepage, ebuild_license, ebuild_use, eclass, eclass_use, herd, license, package and use. All of them can already be populated by the scripts except for eclass_use and herd. I haven’t setup the dependency ones yet, though that’ll be pretty simple too.

So there’s my big announcement. Woots. I’m working on creating the SQL to import everything right now (which takes a long time), and once that’s done, I’ll throw up a db dump somewhere. There’s still lots to be done, like finishing the import scripts and setting up some webpages to browse the tree, but it shouldn’t be too hard. I’m definately over the worst of it.