Raspberry Pi ATX power management using an Arduino (Pt. 2)

Well, I finally posted all my source code and schematics on github at https://github.com/cyrusbuilt/CyrusBuiltHTPC.  Now some details on the Arduino code: To enable debug mode (if necessary…. it’s been thoroughly tested and I’m currently using it on my own box everyday), uncomment line 45 in Raspi_ATX_PMU.ino.  If you do, the firmware will not complete initialization until it connects to the host that will be monitoring the serial output.  It will attempt a serial connection at 9600 BAUD and will not do anything else until connected.  This is intentional so that you can see the boot screen which displays the pin definitions and their current state. Otherwise, you’d miss the boot screen by the time the connection is actually made.  Once connected, all actions and pin states are output to the serial port as the events are handled.  The 2 libraries in the “lib” folder will need to be copied to the “libraries” folder of your Arduino installation in order to compile.

And now some notes on the HTPC platform installation stuff:  Use the following procedure to set up the platform correctly: 1) Make sure SSH is enabled on the Raspberry Pi.  2) Navigate to the CyrusBuiltHTPC folder from a terminal and type “chmod +rx remote_install.sh”. This will make the remote installation script executable. 3) Type “./remote_install.sh”.  This will just push all the platform installation stuff to the Pi using SCP.  If you’ve already copied the platform installation stuff to the Pi by other means, then just skip steps 2 and 3. 4) Type “chmod +rx setup.sh” in the terminal on the Pi (or via SSH), and then type “./setup.sh”.  This will perform the initial setup by setting the appropriate permissions on the other scripts and installing the utility scripts (systemreboot, systemshutdown, systemupdate) in /usr/bin. 5) Navigate to /home/pi/xbmc_install in the terminal and type “./build-and-install-xbmc.sh”.  This will pull XBMC from its github repository and proceed to compile and install XBMC.  This may take up to 13 hours to complete. 6) After rebooting, navigate to “~/” and type “./configure-htpc-platform.sh”.  This will configure the platform appropriately and set up the Pi to boot into XBMC automatically (you can still exit XBMC to get to a command prompt).

The whole system performs well.  I’ve been streaming TV shows in HD from my TVersity server for months without any problem.  I think I can reduce boot time by relocating rootfs to the USB HDD, as described here.  I will post some pictures of the HTPC and some additional schematics laying out how the whole thing is supposed to be connected together.

Cheers!

Arduinos!!

Ok, so I’ve been playing with a couple of Arduinos for a while now.  I love these things…. soooo many possibilities.  I used to use the Parallax BASIC Stamp 2P24 for similar purposes, but quite frankly, they are just too expensive to compete with the Arduino (or Raspberry PI for that matter).  Plus, I don’t really care for the PBasic programming language either. The Arduino essentially uses C/C++ and the libraries are extensive.

Anyway, I bought 2 of these guys.  I started with an Arduino Uno and did some experiments and prototyping with it.  After playing around I had a cool idea….. remember when I said I got a Raspberry PI a while back?  Well, I had decided to build an HTPC using the PI, a Mini-ITX chassis w/power supply, an RTC (real-time clock), and some other goodies.  I found that Raspberry Pi + Raspbian + XBMC (Xbox Media Center) = One badass HTPC.  I mostly stream video from my TVersity server, but I also added a 250GB 2.5″ SATA HDD using a USB-to-SATA adapter.  The power supply that came with Mini-ITX chassis is more than ample considering the Pi only uses 5V @ 0.5A DC.  The bulk of the power consumption is spent on the HDD.  I also got a SWEET mini wireless keyboard/mouse combo so I can work the whole thing while sittin across the room in bed.  I can even surf the web!

Caveats:
1) There is a lot of work to getting XBMC compiled and installed on Raspbian.  There are pre-built images that have a stripped-down OS based on Raspbian with XBMC already integrated as a self-contained HTPC OS.  They were great, but did not support I2C or the DS1307 (both needed for the RTC module), nor did it have adequate support for my USB-to-SATA controller.  I found it best to just build XMBC on top of Raspbian and then customize the whole thing as needed.

2) Power.  The Mini-ITX chassis uses an ATX-style power supply.  It also has power and reset buttons, as well as power indicator LED in the front bezel just as you’d expect from any other PC.  The problem is this:  The Pi does not behave like an ATX-class PC.  You can soft-off through the OS, but it does not power off the board.  You have to disconnect and then reconnect the power in order to get it to boot up again.  However, there are 2 solder pads on the Pi used for reset.  So then I got the idea:  I could build a PMU (power management unit) using an Arduino!!

As it turns out, the ATX specification has a 5V standby pin (purple wire) that is always on as long as it has power.  This is what keeps certain functions of your motherboard alive so it can monitor power button presses, which then tells the power supply to turn on by driving the trigger pin (green wire) low, or in other words, ties it to ground.  So my thought was to use the Arduino to sense power and reset buttons and then drive relays to to control them.

But that is where I ran into problems.  I started off trying to use an ATtiny85 and just program it using my Arduino Uno as an ISP, but that didn’t work out because I couldn’t get the damn thing programmed.  I eventually gave up and bought an Arduino Micro from AdaFruit.  Not as cheap as the ATtiny85, but far more versatile and a breeze to program.  Plus it supported a greater instruction set, which I ended up needing anyway.

Then I ran into another problem:  Power draw.  The Arduino Micro, plus LED and a couple of relays is just too much power draw from the 5V standby pin.  I did not have sufficient power to run the whole circuit.  So, plan B: tie the trigger pin low leaving the ATX supply on full-time (or while it to a switch for hard-disconnect).  Then I can use the full 12V and 5V rails from the ATX supply.  The only thing is, I had to add a third relay.  The relay assignments are as follows:

Relay 1 = Reset switch.
Relay 2 = 5V power switch for Raspberry Pi and HDD.
Relay 3 = 12V power switch for HDD.

So the basic operation is simple:

Power button press (momentary):
Power currently on? no-> turn on LED and Relays 2 and 3. Otherwise, turn them all off.

Reset button press (momentary):
Power currently on? yes->turn off LED and turn on Relay 1. Do not switch states back until button release. During this time, ignore power button presses; Otherwise, do nothing.

This was actually quite effective.  The prototyping phase is done.  I just need to start soldering this stuff to the PCB!!  I’ll be posting schematics, source code, etc to github as soon as I get everything put together and tested.

MonoPluginFramework Released

I released my MonoPluginFramework.  You can get it over at https://github.com/cyrusbuilt/MonoPluginFramework.  Its a simple easy to use plugin framework for Mono/.NET supporting dynamically loading plugin assemblies, independant plugin configurations, plugin diagnostics, and plugin management. This is meant to be lighter and easier to implement than the Addins framework.

While this framework has not yet been throughly unit tested, it is based on a .NET/WinForms variation that used in another project that worked quite well.  If anyone gets a chance to look at this and test it, by all means, give’er a whirl.  I’ll be generating API documentation soon.  In the meantime, the code itself is well documented.

It works like almost any other plugin framework.  The plugin itself implements the IPlugin interface.  The host application then uses the PluginManager to load and manage the plugins.  You can also write your own plugin management class(es) by implementing the IPluginHost interface.  The CyrusBuilt.MonoPluginFramework.UI namespace includes a couple of GUI classes used for reading/writing  plugin configurations if the plugin has an associated config.

More updates will be coming soon, so keep an eye on the github page.

Defragmenting Databases in MS SQL

So I’ve been busy building an enterprise intelligence gathering application that uses MS SQL on the back-end.  The database has many tables and views.  One of the tables acts as storage for global log entries, to be able to see what user did what while using the software (for accountability purposes).  The problem I’ve found with tables that have a lot of I/O is eventually, database fragmentation occurs.  You’re probably already aware that you can query the fragmentation percentages using the built-in stored procedure sys.dm_db_index_physical_stats.  Essentially, what I did was to create a stored procedure, which I could later call as part of a maintenance task to defragment the database.

There are basically 2 ways to defragment a table.  You can do an offline rebuild of the indexes using “alter index all on ‘<your tablename>’ rebuild”, which is recommended for moderate to high fragmentation – or – you can do an online reorganization of the indexes using “alter index all on ‘<your tablename>’ reorganize, which is recommended for tables with low fragmentation.  40% or higher is considered moderate fragmentation.  Obviously, to do an offline rebuild, you will need all users to be not accessing the tables being deframented.

For a complete comparison of rebuilding vs. reorganizing see http://technet.microsoft.com/en-us/library/ms188388.aspx.

Below is the procedure I used defragment tables in my application.  Where ‘<your database>’, you should insert your own database name:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
set ansi_nulls on
go
set quoted_identifier on
go
-- =============================================
-- Author:		Chris Brunner
-- Create date: 06/07/2012
-- Description:	Defragments the database by
-- checking the statistics for each table and
-- either reorganizing the indices or doing an
-- offline rebuild of the indices, depending on
-- the fragmentation percentage. A rebuild will
-- occur on any table with fragmentation &gt; 40%.
-- Otherwise, the indices are reorganized instead.
--
-- WARNING: An offline rebuild will lock the table
-- during processing. This will prevent modifications
-- to the table until processing is complete.
-- It is recommended that this procedure only be
-- executed when there are no users accessing the
-- database. Conversely, reorganizing the indices
-- does *not* require table locking. 
-- =============================================
create procedure [dbo].[DefragDatabase] 
as
begin
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	set nocount on;
 
        -- drop our temporary table if it already exists.
	if exists(select [name] from tempdb.dbo.sysobjects where xtype = 'U' and [name] like '#frag%')
	begin
	   drop table #frag
	end
 
	-- create new temp table to store our results in.
	-- get the table name and fragmentation percentage from the physical index stats
	-- system view and store them in the temp table.
	create table #frag(tableName varchar(200), p int)
	insert into #frag
	select 'dbo.' + object_name(object_id) as table_name, avg_fragmentation_in_percent
	from sys.dm_db_index_physical_stats
	(db_id(N'[YOUR DATABASE NAME]'), null, null, null, 'sampled')
        order by avg_fragmentation_in_percent desc
 
        declare @name as varchar(200)
        declare @sql varchar(1000)
        declare @percent as int
 
        -- go through each result and check fragmentation.
        while exists(select top 1 tableName from #frag)
        begin
		-- for tables that have less than moderate fragmentation, we just reorganize
		-- them.  Otherwise, we do an offline rebuild of the indexes. We consider
		-- "moderate" fragmentation to be 40% or higher.
		select top 1 @name = tableName, @percent = p from #frag
		if (@percent > 40)
		begin
		   set @sql = 'alter index all on ' + @name + ' rebuild'
		end
		else
		begin
		   set @sql = 'alter index all on ' + @name + ' reorganize'
		end
 
		-- delete each result from the table as we go. This is a dequeuing mechanism.
		-- then execute the rebuild or reorganize command.
		select @sql
		delete from #frag where tableName = @name
		exec(@sql)
	end
 
	-- destroy the temporary table.
	drop table #frag
 
	-- update the index statistics.
	exec sp_updatestats
end
go