Monday, February 18, 2008

MPlayer is the best video player, ever. It's also open source, and more versatile than Video Lan Client (VLC). On Windows platforms, MPlayer for Windows - contains SMPlayer, MPUI, MPlayer and codecs in a convenient package.

The problem is it will revert to Aero basic when running on Vista. Here's how to fix that:

As you can see, apart from the poor graphical performance (as expected from Virtualization software), performance is very good. Especially disk I/O.

And for all the people who wish to convert their VMware and QEmu virtual machines to Solaris / VirtualBox, read this.

Expect a FreeBSD version of VirtualBox soon.

Running Windows 2008 Enterprise and Vista Ultimate on VirtualBox:

If you want VirtualBox Guest Additions, just download a debian package or whatever, and use ar -x to unpack it. Grab the ISO and mount it in the virtual machine. You need them for AMD PCNet drivers (networking) on Vista and 2008 for example.

And, once VBoxGuest Additions are installed, you can work in seamless mode. It basically means the host and the guest Windows both appear on the same host desktop:

Shared folders:

Want to have some fun? Use VirtualBox virtual machines on ZFS with compression and snapshots :-). You'll get at least 1.5x compression with virtually no performance hit (less data to transfer == faster with a minor hit on CPU for compression / decompression).

db2 => CREATE TABLE leaptest (thedate date)DB20000I The SQL command completed successfully.db2 => INSERT INTO cmihai.leaptest VALUES ('2008-02-28')DB20000I The SQL command completed successfully.db2 => INSERT INTO cmihai.leaptest VALUES ('2008-02-29')DB20000I The SQL command completed successfully.db2 => INSERT INTO cmihai.leaptest VALUES ('2008-02-30')DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:SQL0181N The string representation of a datetime value is out of range.SQLSTATE=22007db2 => INSERT INTO cmihai.leaptest VALUES ('2007-02-29')DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:SQL0181N The string representation of a datetime value is out of range.SQLSTATE=22007db2 => SELECT * FROM cmihai.leaptest

Download and install PostgreSQL. If you're using some kind of BSD or Linux, it's probably already in the ports / packages repository. You may need to use initdb to create a new database cluster, and edit pg_hba.conf to grant your users / machines connect privileges to the server. If you're in a UNIX-like OS, you'll need to "su - postgres". After that:

Connect as postgres:

psql -U postgres

Create a role:

CREATE ROLE testuser LOGIN PASSWORD 'test123';

Create a database:

CREATE DATABASE testdb;

Note that the database is created in the cluster ENCODING. If you want to specify a different encoding (not recommended) you can use something like:

Now that you've got the feel of psql, you can use PgAdmin III (or PHPPgAdmin or whatever interface you like, like OpenOffice.Org Base or Microsoft Office Access). PgAdmin III also shows the SQL commands it's going to run, so it's a good way to learn SQL.

Download the postgresql-sdbc-ver.zip file, start OpenOffice (if you start writer type Ctrl-W to close it) so you can access Tools - Extension Manager. Add the downloaded driver, then restart OpenOffice (you also need to exit the QuickStarter. Just right click the SystemTray icon - Exit).

Now you can start OpenOffice.org Base and connect to a PostgreSQL database:

That's pretty much it for the database setup, now you can start creating your database. You can use data already available in the PostgreSQL server, or create new tables. You then build Queries, Forms and Reports based on those tables.

You can use the Table Design or Table Wizard to create tables:

You can use the Relationship Editor to create Foreign Key constraints:

You can use the Form Design and Form Wizard to create Forms and the Report Design / Wizard for Reports:

It's usually a good idea to take a look with psql every now and then. OpenOffice.org Base isn't exactly perfect when it comes to creating the database schema, and especially when it comes to constraints. Of course, neither is the SDBG drivers, so it's best to consider this "just for fun" for the moment.

Sunday, February 03, 2008

Microsoft Office Access is a RDBMS that uses a GUI interface and RAD tools and a Jet Database Engine backend. It can also use external data stored in SQL servers such as MSSQL or Oracle, PostgreSQL, DB2, etc. via ODBC.

PostgreSQL is "the most advanced" open source database. It is a BSD-style licensed ORDBMS that supports advanced SQL features such as referential integrity constraints (foreign keys, column checks, etc), full ACID compliance, ANSI SQL compliance, views, rules, sub-selects, transactions, triggers, sequences, inheritance and has a built in language (PL/pgSQL) similar to Oracle PL/SQL. It also exhibits almost linear scalability up to 16 cores, being much more scalable then say, MySQL. As of version 8.3 it gets a performance and scalability boost too. The pgbench benchmark here shows an almost 50% performance boost (under certain workloads) from 8.1.

Using an ODBC connection you can use Access as a Rapid Application Development interface to develop Forms, Reports and Applications using PostgreSQL as a backend database, via PSQLODBC.

Install psqlodbc on your Access machine, and all you need to get started now is a PosgreSQL server (if you don't have one on your network you can install one on your Windows machine from:http://www.postgresql.org/download/

Fire up Access and open up a database. We are going to create the ODBC connection and save the settings. First, let's export some table to the PostgreSQL database via ODBC. We can later link or import the table (in any database).

Create a new Data Source using the "PostgreSQL Unicode" driver. The goods database was created using "CREATE DATABASE goods OWNER cmihai ENCODING 'UTF8'" and we are planning on storing Unicode data.

Once you've selected a Server hostname (or IP), username, password and database (like the newly created "goods" database in your PostgreSQL server) you need to set some advanced options. Uncheck the "Bools as Char" box.

Check "True is -1":

Now we can Import a table using an external data source (it will import the structure and data of a Table in PostgreSQL as a table in Access. The tables will not be linked).

We can also Link tables using an external data source. This means that the data and structure of the table (schema) is only modified in the PostgreSQL database. We can link multiple applications to the same database (and tables). Just use Get External Data - Link to Data Source and select the PostgreSQL ODBC connection.

As you have noticed, PostgreSQL is case sensitive.

The linked tables are shown in the Table view with a sphere icon.

While you can use the Query Builder (in Design view or SQL view) to query data in the Access database (it can use both access tables and ODBC connected tables), there is an option to pass the SQL query directly to PostgreSQL, in Pass-Through mode.