Using stylesheets with DBToy

last update: Jan 24, 2007

In this tutorial I'll show you how to modify dbtoy's output, using custom stylesheets.

How it works

As of release 0.6, dbtoy lets you insert a fragment of your choice in its xml files, between the header and the root tag. This feature can be used to add a reference to an xsl stylesheet to all the files, another use could be the insertion of a disclaimer or a comment.
To add a stylesheet to dbtoy files, just add the '-x' option to your command line arguments, e.g.:

[user@testlinux]$ dbtoy -u user -p my_pwd -d mysql -x'<?xml-stylesheet type="text/xsl" href="file:///path/to/file.xsl"?>' /mnt/dbtoy

Example A: An HTML Report with your data

Transforming xml files in html is a common task, there are many examples on the net of doing such transformation, so I'll show you a very simple xsl, to play with as you like. Please consider that I'm not a web designer and not even an xsl expert, so don't complain if the resulting pages will not be so good-looking, ok?
Here is the script:

Looking at the code, pleas note that:

the same stylesheet is applied to both types and data files, so there are distinct templates to handle them
even and odd rows in data files are distinghished using two classes (r_odd, r_even) defined in a css file
the table's name is read from a root tag's attribute

Paste the script in a file (or download it from the link at the end of the page) and run dbtoy with the right string for '-x'. To see the result of the transormation, open some file within firefox. This is what I got using a test table: (click for a larger version)

And this is part of the generated html code:

That's it. With a bit of work you could make it prettier, and add some nice feature like: sorting, pagination, etc.

Example B: Generate SQL scripts

In this second example, I'll show you how to generate SQL code from dbtoy xml files. Data files will be transformed in a sequence of insert statements, and types will become ddl scripts. Here is the script:

And this is the output with my test table:

-- DDL script. Generated from dbtoy output.
create table user (
	int(11) id PRIMARY KEY,
	varchar(64) login ,
	varchar(64) password ,
	enum('yes','no') enabled ,
);


-- Table data insert script. Generated from dbtoy output 
	
insert into user values(
		'1',
		'domenico',
		'domepw1',
		'yes',
	);

insert into user values(
		'2',
		'andrea',
		'ris1pl',
		'yes',
	);

insert into user values(
		'3',
		'luca',
		'def_12p',
		'yes',
	);

insert into user values(
		'4',
		'maek',
		'rino76',
		'yes',
	);

insert into user values(
		'5',
		'pino_dd',
		'para0m9',
		'yes',
	);

insert into user values(
		'6',
		'enzo',
		'enzo2',
		'no',
	);

insert into user values(
		'7',
		'roby',
		'u4ea',
		'yes',
	);

insert into user values(
		'8',
		'lucio',
		'21mar77',
		'yes',
	);

insert into user values(
		'9',
		'franko',
		'xxx0x',
		'no',
	);

insert into user values(
		'10',
		'frank',
		'ald9k',
		'no',
	);

insert into user values(
		'11',
		'dome',
		'ping12_d',
		'yes',
	);

insert into user values(
		'12',
		'andrew',
		'cs_best',
		'yes',
	);

insert into user values(
		'13',
		'lucy',
		'beatl3',
		'yes',
	);

insert into user values(
		'14',
		'pinuccio',
		'zagara11',
		'yes',
	);

insert into user values(
		'15',
		'gio',
		'jj21p',
		'yes',
	);

insert into user values(
		'16',
		'giove',
		'sun4u',
		'yes',
	);

Conclusion

I hope this tutorial has been helpful, feel free to write me a comment at pinguino{at}thesaguaros[d0t]com.
You can download the files used in the examples here