When it comes to naming things in databases and languages, there are various common standards. For many languages the camel family of namings is very popular. For unix based databases usually UPPER or lower _ is the choice and for databases such as SQL Server and MySQL which allow you to name your columns with mixed casing but could care less what case you express them in selects, you get a mish mush of styles depending on what camp the database user originated from.

So to summarize the key styles and the family of people

• camelCase : lastName - employed by Java and Flex and C and various other C style languages, sometimes PHP developers.
• Pascal Case: (a variant of Camel Case) -- LastName which is employed by C#, VB.NET, Pascal (and Delphi), and SQL Server (and some MySQL windows converts)
• lower case _ last_name : a favorite among PostgreSQL database users. (some MySQL)
• upper case _ LAST_NAME : a favorite among Oracle Users (some MySQL Oracle defectors)

Being at the cross roads of all the above, we often have to deal with the various above as well as having internal schizophrenic strife and external fights. The internal turmoil is the worst and is worse than an ambidextrous person trying to figure out which hand to use in battle. For these exercises, we'll demonstrate one way how to convert between the various conventions. These are the first thoughts that came to our mind, so may not be the most elegant.

Continue reading "Of Camels and People: Converting back and forth from Camel Case, Pascal Case to underscore lower case"

One of the more important tasks in the process of rolling out incremental developments to a multi-server production system is to double-check that all of the planned modifications, and nothing else, have been deployed. An Oracle expert, Bruce Armstrong, comes across SQL Compare for Oracle, and sees if it helps with this time-consuming task.

To make progress as a relational Database programmer, you have to think in terms of sets, rather than lists, arrays or sequential data. Until that point, you'll feel the need to pass lists, arrays and the like to functions and procedures. Joe suggests some unusual ways around the difficulty and broods on the power of the VALUES constructor.

In retrospect, it was probably the inclusion of the OUTPUT clause in the MERGE statement that gave SQL Server 2008 its most powerful SQL enhancement.. It isn't the easiest of features to explain, but Bob does it in his usual clear and careful way.

Often, there is a huge difference between software being easy to use, and easy to develop. When your pilot users tell you "by week three of any development project you've thrown out anything that takes time or just gets in your way", then it is time to be serious about usability, as well as quality. We decided to find out from some of those at the sharp end of the development of Red Gate's SQL Source Control.

In an earlier article Where is Soundex and other Fuzzy string things we covered the PostgreSQL contrib module fuzzstrmatch which contains the very popular function soundex that is found in other popular relational databases. We also covered the more powerful levenshtein distance, metaphone and dmetaphone functions included in fuzzstrmatch, but rarely found in other relational databases.

As far as fuzzy string matching goes, PostgreSQL has other functions up its sleeves. This time we will cover the contrib module pg_trgm which was introduced in PostgreSQL 8.3. pgtrgm uses a concept called trigrams for doing string comparisons. The pg_trgm module has several functions and gist/gin operators. Like other contrib modules, you just need to run the /share/contrib/pg_trgm.sql file packaged in your PostgreSQL install to enable it in your database.

For this set of exercises, we'll use trigrams to compare words using the same set of data we tested with soundex and metaphones. For the next set of exercises, we will be using the places dataset we created in Importing Fixed width data into PostgreSQL with just PSQL.

The most useful are the similarity function and the % operator. The % operator allows for using a GIST/GIN index and the similarity function allows for narrowing your filter similar to what levenshtein did for us in fuzzstrmatch.

Continue reading "Fuzzy string matching with Trigram and Trigraphs"

Why are they here? Why is anyone?

This the first delivery in a series of articles targeted at showing developers how db4o (an open source database that leverages today's object-oriented languages, systems, and mindset) is being used in several Android projects to avoid all the pitfalls and hassles of object-relational mapping while benefiting from an elegant and straight forward way to evolve a domain model which, in the end, translates into faster, easier upgrades for users.

More: http://java.dzone.com/articles/leverage-db4o-your-android

A new 8.0 development release is online. Below  is the list of changes since our last 8.0.146 release. We highly recommend all 8.0 users to upgrade to this build since we have fixed 2 serious issues ( bold ). Both issues were introduced with the new BTreeIdSystem in the 8.0 branch. Production and Stable builds are not affected. COR-2008 - [Android] ClassCastException trying to cast Db4oDatabase to GenericObject COR-2003 - [.Net] Investigate failing.Net 4.0 tests due to cecil update COR-2001 - With low custom max stack depth, PendingSet processing order expected by static field handling is not maintained COR-2000 - Defragmenting string field indexes is broken with btree id system COR-1998 - Introduce ID mapping sanity checking to be used in dedicated db4ounit fixture COR-1997 - Commit throw an UniqueFieldValueConstraintViolationException after defragment COR-1996 - java.lang.OutOfMemoryError in UnicodeStringIO.read COR-1992 - [Android] Review all ' ...

A new 7.12 production release is online. Here is the list of changes since our last 7.12.145 release: COR-2001 - With low custom max stack depth, PendingSet processing order expected by static field handling is not maintained COR-1978 - Transient field modifier is not being respected in C/S mode without classes in the server COR-1977 - Execution of sub queries descending into collection members only seem to use a single collection member as root COR-1950 - Respect transient modifier on static fields COR-1946 - Storage implementation to be able to call #saveAs() on an open database COR-1945 - Transaction.objectContainer() should always return an ObjectContainer with transactional behaviour, also in events. COR-1902 - System.Byte and System.SByte casting problem in Db4objects.Db4o.Internal.Handlers.ByteHandler COR-1822 - Committed callbacks should provide a means to discover if the committed event was raised by this clients commit or another one in C/S mode  Yo ...

If you ever feel uneasy when you set about handcrafting database maintenance jobs for SQL Server, it may be the voice of your conscience saying 'Ola, Ola!'. Follow your conscience. Ola Hallengren has already crafted a superb set of routines to do this for you. Why reinvent something that does it all, safely and well. Brad McGehee explains....

It's a little like having a security firm visit your house and tell you about the weaknesses that a criminal might exploit and best practices that you can adopt to make yourself less vulnerable. Alex covers clearly and explains how and why they could be problems before providing working examples of how to avoid getting caught out.

Quite often at Red Gate, we are some of our own most enthusiastic software-users. SmartAssembly is a case in point. In the words of the current IT cliché, 'we eat our own dogfood'. We sent Andrew Clarke to find out more....

It is an interesting time for any .NET developer wanting to develop software for mobile phones. We've always taken it for granted that there would be a good .NET platform for mobile phones. Surely Microsoft aren't going to fumble the key component, Windows Phone 7, after getting the development tools so right.

Djonatas Tenfen demonstrates in fine detail how to quickly and easily build a mashup Silverlight application to consume and manipulate multiple data sources, and plot airports' realtime status onto a map.

ICOODB 2010 is approaching and we'll be there helping promote object database technology. Please join us!

Here are the details for the event:

3rd International Conference on Objects and Databases
September 28-30, 2010
Frankfurt am Main, Germany

2 tutorials, 1 workshop, 10 research papers, 10 industry
presentations,  3 keynotes and 1 keynote panel...

(continues...)

Its long been a constant concern of mine that there are changes being made to databases that I manage that will one day trip me up. Someone will add just one too many columns to a table, or even drop a table, and an application will stop working. When was the change made, who made it, why, was it tested before it was rolled into live?

It has been said by many people that C# is 'Java done right'. However, there is one feature not included in C# that I do miss from my time in Java - method override covariance - so I decided to see if anything could be done to add this small but useful feature to C#...

The latest evolution of VB in Visual Studio will bring with it a bundle of C# functionality, nudging the two languages even closer together. To ensure we're ready to make the most of these enhancements, Hima Vejella takes us through each new feature.

When Eli was writing a WPF MVVM tutorial, he decided to use .NET Reflector as his sample, and created a WPF UI for .NET Reflector using its public interface, mimicking the original. A WPF/Prism version of Reflector? Eli explains what he did, and supplies the complete project and source to Reflectoron