I recently had a run-in with very popular PHP ecommerce package which makes me want to voice a recurring mistake I see in how many web applications are architected. What is that mistake? The ecommerce package I was working with depended on caching.  Out of the box it couldn’t serve 10 pages/second unless I enabled some features [...]

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"

There is a new pane in the Data Builder for VistaDB 4.1.  The LINQ query pane is a very handy way to build and test LINQ queries against a simple Entity Framework model.  First understand that this pane is a work in progress.  It was designed to be an easy way to write LINQ queries (similar to LINQPad) for your VistaDB database.  The feature is of limited usefulness since the model cannot be customized at generation, or loaded from your own assembly.  But this is where we were with the feature when we had to ship, so here it is.  We have been using it a lot to help write LINQ queries, we hope you find it useful too.

The current implementation only supports C# (sorry VB guys, it was on the list).  You can right click on the Entity Model tree and Script the main query window with all your basic CRUD operations.

By default the dialog is grayed out until you generate a model.  The code shown will still be present once the model is generated.

Generate a Model

By default the dialog is grayed out until you generate a model.  The code shown will still be present once the model is generated.

Click the Generate Model button to have Data Builder call the EDMGEN tool and build a default model for the current database.  The default is to output one type per table.  This is the same as if you add a new model to your application and accept all the defaults.  If any errors occur during the generation of the model they will be output in the Log window at the bottom of the screen.

When to generate a model

Each time Data Builder loads you will have to regenerate a new model (they are not saved across application runs).  The models are kept loaded after the initial generation.  If you make schema or foreign key changes you should regenerate the model in order for the changes to be present in the model.

How to view the current model

The current model is displayed on the second tab (after a model has been generated).

 

Intellisense

Sorry, there is no intellisense in the current version for the LINQ queries.  It REALLY does help to have it, but there just was not time to get it implemented.  Use the Entity Model tab to see what the entity names look like.

Right Click for Scripting

Right clicking on an entity (the table name) will allow you to generate some default LINQ queries for the Query pane.

 

Each of the basic CRUD operations are demonstrated for the object.  Selecting them will put the script in the query window.

Putting queries into your code

Once you have a query working the way you want, you can copy and paste it into your code.  The only requirements are that you have a model that matches the default model, and that you have a data context of your model called context (or you can rename the internal context to be what your variable is named).

A best practice for this code might look something like this:

using( RealModel.ModelEntities context = new RealModel.ModelEntities(connection))
{
   var query = from i in context.OrderDetails select new { i.OrderID, i.Orders.OrderDate };
   // Your custom code to work with the LINQ results
}

Example LINQ Queries

The example script code will not all execute by default.  They are templates for you to edit.  For example the delete template doesn’t know what your criteria is for the delete.  It leaves a string in the place for you to change to your actual criteria.

The idea was to eventually give the user an interactive wizard type of experience to build up their LINQ queries against the model.  But what is there today is still useful.

Summary

The LINQ panel in Data Builder 4.1 is a very useful way to write LINQ queries against a default EF model quickly with VistaDB.  It is not feature complete, but is still useful enough for us to release it with this version.

We hope you find it useful for writing LINQ queries, and possibly to interest you enough to learn more about Entity Framework.

Kendal posted the announcement yesterday that we opened the design content on 99designs. I’ve used the site before and it’s a decent system, typically get a logo for around $200 – and you get to pick from a lot of options. I know most of us are data people, but probably more than a few of us know Photoshop or the equivalent, or someone that does, which means it could be someone from the SQL community that designs the next really big PASS logo. Why not give it a try?

One of the new products that Red Gate Software has released recently is SQL Source Control. I saw a demo of this product back in March as it was nearing completion, and I thought it was pretty cool. I have struggled with source control for years, and build a process that was mainly manual, and one that I’ve seen others use over the years.  It worked well, but it was prone to errors unless I was allowed to chastise developers on a regular basis for not adhering to the process.

Today I acted as emcee and moderator for a semi-private webinar that demo’d the product to the Friends of Red Gate, a group that is by invitation only, but lets some customers interact with Red Gate similar to how the MVP program grants some people access to Microsoft.

This was the first time I actually acted as a moderator, having been an attendee or presenter in the past. It went well, I think, though I definitely had a little dead air and struggled to keep up with questions and find a smooth way to interrupt the presenter. I would rather be the one presenting and let someone else handle the other work for sure.

I think we’ll be scheduling more of these on a regular basis for some Red Gate products to get the word out more and show how they can be used to solve problems. This is one of the few that I’d purchase myself if I was doing DBA work, so we’ll start here and see what happens.

I’m also hoping to do some other more regular webinars from SQLServerCentral, on more technical topics, across the next year.

I have a few people ask about this recently, so I thought I’d blog about it, and maybe get an explanation out there.

When we started accepting bloggers on SQLServerCentral, it was a challenge to get people to move their blogs. We had some people that just started blogging with us, but others that were already blogging elsewhere. At first I wasn’t sure what to do, but when SQLServerPedia started syndicating bloggers, using feeds, I requested similar functionality for SQLServerCentral.

We built it, and while it’s a little crude, and we’ve modified things a few times, here’s how it works:

• You send us a note with
  • Your SQLServerCentral registered email
  • A feed URL
• You blog in English (sorry, English only)
• We read the feed and publish your blog on SQLServerCentral.

The Scary DBA has syndicated his blog, so you can see how the different things look on our site v the source. Some things don’t come across cleanly, and I’m working on getting some of those things fixed.

Frequency

We used to read feeds once a day, now we do it once an hour. So if you blog about something, it ought to be on the SQLServerCentral site in under 2 hours. If you think there is an issue, please contact the webmaster.

Hints

I use Live Writer to actually publish this blog on Blogspot. It lets me test things, but also gives me a backup of my blog and control over what I write. I think it’s a good idea to own your content, as well as keep a backup of it on your own blog.

Live Writer is a great piece of software for drafting blogs, scheduling them, working offline, etc. I actually sync my Live Writer folders  across my desktop and laptops. That gives me some additional capabilities to draft stuff on one machine and finish it on another.

I highly recommend people tag stuff they want to syndicate with a specific tag and then send us the feed of that tag. That allows you to still blog about something that you want to publish, but not syndicate it if you don’t want to. I know some people do this to publish other stuff in other languages, or they mix their personal/career blog and don’t necessarily want to send everything to SSC.

Setup

If you want to get setup, send a note to the webmaster at SQLServerCentral.com and we’ll get you syndicated.

Back at the start of July I kicked off a survey around your plans for SSDs (see here) and now I present the results to you. There's not much to editorialize here, but the numbers are interesting to see.

 

The "other" answers were (verbatim):

• 3 x 'have bought and am trying them out'
• 3 x 'not sure if we need them or not'
• 2 x 'all production servers are hosted'
• 1 x 'bought them, tried them..not good enough yet for tempdb'
• 1 x 'Have some, want more, could you really every have enough?'
• 1 x 'We get every penny from or spinning media, and have no need for SSD'

The results reflect what I've been hearing when teaching classes and talking to customers/conference attendees over the last six months. People are becoming more interested in SSDs but there's still a lot of wariness about them and of course the whole money issue of being able to buy them. I'm also not surprised (given the general readership demographics of this blog) by the number of people who've analyzed their IOPS requirements and concluded that they don't need SSDs to accomplish that.

 

The "other" answers were (verbatim):

• 3 x 'not in the budget'
• 1 x 'I plan to buy expensive drives and throw them at you, paul! love, conor'
• 1 x 'I'm going to do the same thing Conor will do. Denny'
• 1 x 'OLAP Scale Out'
• 1 x 'Use them as cache'
• 1 x 'Using in an EMC V-MAX SAN to dynamically move high workloads to SSD temporarily'

Ahem - thanks Conor and Denny :-)

Another unsurprising set of results that reflects what I've been hearing. One number I'd be interested in drilling deeper into is answer #3 - are people putting/planning to put tempdb on SSDs because that's what they've heard is the best thing to do, or because tempdb truly is the largest I/O bottleneck that can benefit the most from SSDs? That's a set of experiments I'd like to try out with my Fusion-io drives.

The final "other" answer is also interesting - I was talking to a couple of folks from EMC in Ireland about the V-MAX when we were there earlier this month. Very cool idea to migrate data up and down a set of devices with varying latencies (at the block level, not the file level) - I'd like to see more on how the technology copes with one-off operations like consistency checks or backups - do those IOs affect which layer a block resides in?

Anyway, hope you find these results interesting.

Thanks to all those who responded!

According to a report from econsultancy.com 34% of shoppers prefer to buy online.  As buying habits change and consumers move more towards online shopping, the need for a robust and scalable IT infrastructure becomes greater.  According to Internet Retailing, internet retail sales in the UK hit £49.8 billion, up 21% from 2008. Overall, up to [...]

Victorious by Jack Campbell ($8 at Amazon) is the finale in the six part series about Black Jack Geary leading a space fleet back home from deep in enemy territory. I’ll try not to ruin it for you if you haven’t read them all yet!

The series is good, though I’ll admit to thinking it might have been done in five books or maybe even four. It’s naval battles set in space, with a lot of tactics in three dimensions. There is a bit of intrigue and politics, especially the latter in this last one. A big focus on honor and not descending to the level of opponents, stuff I largely agree with but is rarely so simple in real life, and the author manages to show some of that throughout the books.

The ending is reasonably happy, though parts of it feel rushed – a strange feeling given that overall the story was stretched too much. The villains end up feeling not quite as formidable as they seemed earlier on.

Still, I can easily recommend the series, and it’s always nice to know that when you read a book there are more to follow already done and waiting.

Hey you fellow Germans out there! O’Reilly published a new book on CouchDB just for you! It is Mario Scheliga’s CouchDB Kurz & Gut — roughly translated to CouchDB Short and Good. The Kurz & Gut series consists of compact books with enough content to get you running and that are later useful as a quick reference in day to day work (Jan happily remembers having the LaTeX edition at hand a couple of years back).

From all of us at Couchio:

Mario, awesome job! Thanks a lot for help getting the good word out and providing such a great resource for the German CouchDB community. Rock on! (or relax, either is fine :)

Nitpicker corner: this post discusses authorization, which assumes that you already know who the user is. Discussion of authentication methods, how we decide who the user is, would be outside the scope of this post.

I had a lot of experience with building security systems. After all, sooner or later, whatever your project is, you are going to need one. At some point, I got tired enough of doing that that I wrote Rhino Security, which codify a lot of the lessons that I learned from all of those times. And I learned a lot from using Rhino Security in real world projects as well.

When coming to design the authorization bundle for RavenDB, I had decided to make a conscious effort to detail the underlying premise that I have when I am approaching the design of a security system.

You can’t manage authorization at the infrastructure level

That seems to be an instinctual response by most developers when faced with the problem, “we will push it to the infrastructure and handle this automatically”. The usual arguments is that we want to avoid the possibility of the developer forgetting to include the security checks and that it makes it easier to develop.

The problem is that when you put security decisions in the infrastructure, you are losing the context in which a certain operation is performed. And context matters. It matters even more when we consider the fact that there are actually two separate levels of security that we need to consider:

• Infrastructure related – can I read / write to this document?
• Business related – can I perform [business operation] on this entity?

Very often, we try to use the first to apply the second. This is often the can when we have a business rule that specify that a user shouldn’t be able to access certain documents which we try to apply at the infrastructure level.

For a change, we will use the example of a debt collection agency.

As a debt collector, I can negotiate a settlement plan with a debtor, so the agency can resolve the debt.

• Debt collectors can only negotiate settlement plans for debts under 50,000$
• Only managers can negotiate settlement plans for debts over 50,000$

Seems simple, right? We will assume that we have a solution in store and say that the role of DebtCollectors can’t read/write to documents about settlement plans of over 50K$. I am not sure how you would actually implement this, but let us say that we did just that. We solved the problem at the infrastructure level and everyone is happy.

Then we run into a problem, a Debt Collector may not be allow to do the actual negotiation with a heavy debtor, but there is a whole lot of additional work that goes on that the Debt Collector should do (check for collateral, future prospects, background check, etc).

The way that the agency works, the Debt Collector does a lot of the preliminary work, then the manager does the actual negotiation. That means that for the same entity, under different contexts, we have very different security rules. And these sort of requirements are the ones that are going to give you fits when you try to apply them at the infrastructure level.

You can argue that those sort of rules are business logic, not security rules, but the way the business think of them, that is exactly what they are.

The logged on user isn’t the actual user

There is another aspect for this. Usually when we need to implement security system like this, people throw into the ring the notion of Row Level Security and allowing access to specific rows by specific logins. That is a non starter from the get go, for several reasons. The previous point about infrastructure level security applies here as well, but the major problem is that it just doesn’t work when you have more than a pittance of users.

All Row Level Security solutions that I am aware of (I am thinking specifically of some solutions provided by database vendors) requires you to login into the database using a specific user, from which your credentials can be checked against specific rows permissions.

Consider the case where you have a large number of users, and you have to login to the database for each user using their credentials. What is going to be the affect on the system?

Well, there are going to be two major problems. The first is that you can wave goodbye to small & unimportant things like connection pooling, since each user have their own login, they can’t share connections, which is going to substantially increase the cost of talking to the database.

The second is a bit more complex to explain. When the system perform an operation as a result of a user action, there are distinct differences between work that the system performs on behalf of the user and work that the system performs on behalf of the system.

Let us go back to our Debt Collection Agency and look at an example:

As a Debt Collector, I can finalize a settlement plan with a debtor, so the agency can make a lot of money.

• A Debt Collector may only view settlement plans for the vendors that they handle debt collection for.
• Settlement plan cannot be finalized if (along with other plans that may exists) the settlement plan would result in over 70% of the debtor salary going into paying debts.

This is pretty simple scenario. If I am collecting debts for ACME, I can’t take a peek and see how debts handle be EMCA, ACME’s competitor, are handled. And naturally, if the debtor’s income isn’t sufficient to pay the debt, it is pretty obvious that the settlement plan isn’t valid, and we need to consider something else.

Now, let us look at how we would actually implement this, the first rule specifies that we can’t see other settlement plans, but for us to enforce the second rule, we must see them, even if they belong to other creditors. In other words, we have a rule where the system need to execute in the context of the system and not in the context of the user.

You will be surprised how often such scenarios come up when building complex systems. When your security system is relying on the logged on user for handling security filtering, you are going to run into a pretty hard problem when it comes the time to handle those scenarios.

Considerations

So, where does this leave us? It leave us with the following considerations when the time comes to build  an authorization implementation:

• You can’t handle authorization in the infrastructure, there isn’t enough context to make decisions there.
• Relying on the logged on user for row/document level security is a good way to have a wall hit your head in a considerable speed.
• Authorization must be optional, because we need to execute some operations to ensure valid state outside the security context of a single user.
• Authorization isn’t limited to the small set of operations that you can perform from infrastructure perspective (Read / Write) but have business meaning that you need to consider.

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.

This document provides an example of installing and upgrading TM1 9.5 including TM1 9.5 Server, TM1 9.5 Web, and TM1 9.5 Contributor. This example is performed on a Single Server running MS Windows XP 32-bit and using IIS and Apache Tomcat.

Last week we sat down with our good friends Richard Campbell and Greg Hughes of RunAs Radio and recorded an interview about how to learn effectively. It was incented by Paul's recent post as part of TSQL Tuesday: T-SQL Tuesday #008: Top ten mistakes to make when attending a class. Additionally, I've written some related posts on types of training here.

Here's the specific link to the show: Kim Tripp and Paul Randal Talk about Learning Effectively!

Enjoy!
kt

This article will be in English and Portuguese.
Este artigo será em Inglês e Português

English version:

After the announcements in May, new changes were made to Informix packaging. Basically the free versions for Windows and Mac OSX (Ultimate-C) were withdrawn. To replace them the Innovator-C version (also free, but with lower usage limits) will be available for all platforms (including the Windows and Mac OSX).

Also a new edition will be available, called Choice Edition, just for Windows and Mac OSX. This one will have license costs, but is cheaper than Growth Edition. The limits are higher than Innovator-C and lower than Growth Edition.

I believe these changes have created some confusion, but there are some important points to note:

1. Clients will still be able to use a version without license costs for all platforms supported by Informix (Innovator-C). The usage limitations are fairly acceptable for a lot of usages
2. Windows and Mac OSX are target platforms with a more aggressive price, considering Choice Edition (again, with fairly respectable functionality and usage limits)
3. Existing customers are not necessarily affected by the new editions. They already payed for the licenses, and are (probably) paying maintenance/support. Obviously they can consider the new editions for new deployments
4. Existing Enterprise Edition customers get more value, because Ultimate Edition includes more features than the Enterprise

The last changes were announced a few days ago and are explained in IIUG website, more specifically here:
http://www.iiug.org/news/announcements/new_prices.php

I delayed the publishing of this article because I was expecting the website comparing the different versions to be updated:

http://www.ibm.com/developerworks/data/library/techarticle/dm-0801doe/

This is the best, and the official place to check each version available functionality.

Other links:

• Article from Andrew Ford about the same topic. It includes links some detailed analysis about each version limitations:
  http://www.informix-dba.com/2010/07/new-phone-bookss-here-informix.html
  

Versão Portuguesa:

Depois dos anúncios em Maio, novas alterações foram feitas nas versões de Informix disponíveis. Basicamente as versões grátis para Windows e Mac OSX (Ultimate-C) foram descontinuadas. Para as substituir a versão Innovator-C (igualmente grátis, mas com limites de utilização mais baixos) estará disponível para todas as plataformas (incluindo Windows e Mac OSX)

Uma nova versão estará disponível, designada por Choice Edition, apenas para Windows e Mac OSX. Esta versão terá custos de licenciamento, mas será mais barata que a Growth Edition. Os limites situam-se entre a Innovator-C e a Growth Edition.

Acredito que estas alterações provoquem alguma confusão, mas há que salientar alguns pontos:

1. Os clientes poderão utilizar uma versão sem custos em todas as plataformas suportadas pelo Informix (Innovator-C). As funcionalidades incluídas e os limites impostos não deverão ser obstáculo a um leque grande de ambientes.
2. Windows e Mac OSX são plataformas alvo, com preços mais agressivos, considerando a versão Choice (também com funcionalidades e limites de utilização bastante respeitáveis.
3. Os clientes existentes não são necessariamente afectados pelas novas versões. Na realidade o custo das licenças já foi pago, e o que estarão (provavelmente) a pagar são os custos de manutenção/suporte. Naturalmente poderão considerar as novas versões para novos projectos.
4. Clientes actuais da Enterprise Edition obtém mais valor, porque a versão Ultimate incluí mais funcionalidade que a Enterprise

As últimas alterações foram anunciadas alguns dias atrás e estão explicadas no website do IIUG, mais especificamente aqui:

http://www.iiug.org/news/announcements/new_prices.php

Atrasei um pouco a publicação deste artigo porque estava à espera que o website que tem a comparação entre as versões fosse actualizado:

http://www.ibm.com/developerworks/data/library/techarticle/dm-0801doe/

Este é o melhor sitio (e o oficial) para verificar as funcionalidades e limites de cada versão.

Outros links:

• Artigo do Andrew Ford (Inglês) sobre o mesmo tema. Tem links para análises detalhadas das limitações das várias versões:
  http://www.informix-dba.com/2010/07/new-phone-bookss-here-informix.html
  

Last week we sat down with our good friends Richard Campbell and Greg Hughes of RunAs Radio and recorded an interview about how to learn effectively, plus some of the usual nonsense and insults.

Check it out at http://www.runasradio.com/default.aspx?showNum=169.

Enjoy!

The OOW 2010 schedule builder is up and ready to run. It's pretty cool. You can do various searches for sessions, vendors, exhibits, etc. You can also find content by user tagging. Something I would like to get a look at is the recommendation engine that's using Oracle Data Mining.

PGDay.EU 2010 has finally been announced. It will be in Stuttgart, Germany, on December 6th to 8th. More details available on the conference website.

We have also sent out our call for papers. If you have done something interesting with PostgreSQL, please go ahead and submit a talk! We are currently looking for talks in both English and German!

If you have not looked at my recent presentation that I presented at ODTUG Kaleidoscope 2010, then feel free to join me on Tuesday, July 27, 2010 6:00 PM – 7:00 PM EDT tomorrow at 5pm EDT when I will giving a webinar on this talk. You can register online at https://www1.gotomeeting.com/register/730452824

Over the past couple years our local library system has been under financial pressure, resulting in some changes, and maybe not all of them bad, but still frustrating. Hoping those of you reading today might offer some insight into your local library policies so I can decide whether to dig deeper or just live with it!

• All branches closed on Friday
• Cannot checkout any books if any unpaid fine
• Cannot renew a checked out book more than 4 times
• Fines only paid in cash/check

The Friday closing I get, definitely savings to close the office, though I wish they would leave one branch open. More than once I’ve wanted to go on a Friday and couldn’t. They’ve told me that the reason behind requiring all fines to be paid is to increase circulation, but I find fault with that argument. You can return all your books, owe $.10, and not be able to check out any books. I rarely carry cash and there is no ATM machine in the building. I would have no problem paying the merchant fee on top of the fines. This is supposed to happen eventually, but it’s not been funded yet that I can see.

The limit of 4 renewals – don’t know. They only let you renew if no one has a hold on it, but I can see that there should be a max, whether 4 or 14. This one annoyed me recently when I returned books and forgot one that I’d had for a while (about growing tomatoes) and actually remembered to bring cash with me, but I couldn’t renew the book, couldn’t pay the fine on it because it wouldn’t calculate until returned – leaving me the option of paying for the book plus a $10 fee (which I considered), or having them hold all the books I’d selected on a Thursday evening that I wanted for a rare Friday at at the beach.

So, I’m trying not to rant, yet I’m not sure those policies are all that customer friendly.