Tag Archives: SQL

Pulling options from the sql db is done.

Thought I’d give an update to my Christmas plans. I ended up taking longer than just the Christmas break. I used the extra time to do some refactoring and cut the size of DB.pm by a thousand lines or so.

Filters will slowly be pulled from DB.pm and moved into a logical hierarchy within the new ‘filter’ directory. As it is now DB.pm is simply too large to wrap your head around. When I started this summer DB.pm was almost 10k lines. Since then it has slowly shrunk to 8k. Due to the size some dead code has built up.

An excellent example of this is get_javascript2() which the old openprinting website used. The funny part is that there is not get_javascript1() or get_javascript().

Oh and then there is comment_filter() which filters user input on a web page with regex. It actually works! Which is quite a feat considering the many got-ya’s with user input

My strategy right now is to remove the proper functionality into modules. My hope is that the dead code will then become more obvious.

Final thoughts on GSoC 2011

Overall I am very happy with how my project progressed. The addition of xmlParse has reduced foomatic-db-engine by over 5500 lines of code, a 85% reduction. SqlLayer allows for near pyppd levels of performance for non-cups users. Perhaps most importantly I’ve grown as a programmer and am more familiar with our linux printing architecture.

A design decision I made early that I am especially proud of is phonebook.pm. With it I was able to write xmlParse and sqlLayer much more abstractly than their C and php counterparts, which meant a substantial reduced codebase . My only regret was not making phonebook even more general, it would have been a design challenge but I think it is possible. This might just be the second-system effect talking.

While GSoC 2011 may be over I do intend to participate throughout the school year. I have already assigned myself two feature requests[1][2] and I still have option name consistency to work on prior to the new semester. This summer’s work will ship in Foomatic 5.

sqlLayer, pushing foomatic data into the database

The second portion of my project is to write a perl lib to push foomatic’s data into a relational database. This would allow the use of SQLite instead of the xml database for foomatic-db-engine. This isn’t going to affect CUPS users (the vast majority of people) since last year’s project (vitor’s) created pyppd which can side-step foomatic-db-engine entierly for end users. What it does though is provide considerable performance increases for users of legacy spoolers.

Like with xmlParse I am not treading new ground, openprinting already has a script to import the data set. This script was written as part of another GSoC project two years ago as part of the openprinting website re-design. A few months before this year’s GSoC Till gave me a copy of the script along with a database dump. I was able to convert this database dump into a sqlite database. With those I’ve been able to make considerable progress. Currently I’m adding support for about one table a day.

Thinking about the project as a whole I am rather proud of the phonebooks, by extending them to document the database schema I’ve been able to operate at a fairly high abstraction level. Whereas the C programs and the PHP import script had 100s of lines of simple ‘if def assign’ the phonebooks let a single* loop handle all the simple renaming and processing for xmlParse. For complex types the raw data is handed to special case code to process.

Sample special case code for option’s complex data:
#The specific groups
} elsif ($group == 11) { #constraints
	setConstraint($node, \$perlData{$destinationKey});

} elsif ($group == 12) { #enum_values
	foreach my $subnode ($node->findnodes("./enum_val")) {
		my %enumValue;

		foreach my $subsubnode ($subnode->findnodes('./@id[1]')) {
			$enumValue{"idx"} = $subsubnode->to_literal;

		foreach my $longnames ($subnode->findnodes('./ev_longname')) {
			$this->setHumanReadableText(\%enumValue,\"comment", $longnames);

If I were to redo my work I would make the phonebooks document the structures of the complex data. This would allow an even further generalisation and do away with the special case code for the complex types.

That isn’t going to happen though, the current code has been tested and is working. And while the special cases could have been done better I do realise that a more general approach would have had a much harder time conforming to the behaviour of the C programs.


*Not necessarily a single instance of the loop. I’m a bit ashamed to admit but there are actually three copies of the same loop, one for each xml type. It is this way because when I created the phonebooks I made groups above 10 be namespace specific groups. Thus group 11 for an option xml is different from printer’s group 11. In xmlParse this is implemented by the fact that the option loop is separate from the printer’s loop. The groups that all loops share are in a separate function, so really only the loop structure is copy pasted. In sqlLayer I’ve kept the loop singular and simply added support for namespaces, support which will be made cleaner if I can think of a way.

Reusable code in DB.pm for import/export to/from DB

After asking Till some guestions I’ve figured out what’s left to write.

DB.pm already has:
Perl -> XML
SQL -> Perl (For printers and drivers)
XML -> Perl (For printers and drivers)

That only leaves me to write:
Perl -> SQL
SQL -> Perl (For options)
XML -> Perl (For options)

Functionality wise a good majority of hte code is already there. I also already have a function for create the tables from schema. After this I need to create a function to import a single XML and then a second function which calls the first function on all the XMLs.

Foomatic SQLite Progress as of November 22nd

As of today, November 22nd, Foomatic can operate from a sqlite DB. On the Foomatic side this required only two lines of code.

my $dbfile = "$libdir/db/openprinting.db";
$this->{'dbh'} = DBI->connect("dbi:SQLite:dbname=$dbfile","","");

Foomatic will only use SQLite if there is not a mySQL credentials file yet is a SQLite DB. To make my SQLite copy of the DB I exported it from mySQL. The mySQL db was created using a php script written for openprint.org. Since XML -> mySQL -> SQLite is a most impractical build process my next task is to write what ever is needed to go XML -> SQLite.

When I was first consulting Till about what I needed to do there was the general assumption that I would be expending DB.pm with this importation functionality. Yet when I sat down to plan my approach I designed an external script. When I mentioned this to Till he reiterated about including everything in DB.pm. A bit of a facepalm moment since his point is so logical. DB.pm already has quite a bit of the functionality I need. So far I have only taken a cursory glance at what DB.pm has to offer. I know that there is functions in there I can use but I will have to take pen and paper and figure out what exactly I’ll use and what I need to write.