DevelopMENTAL Madness

Thursday, January 29, 2009

Lost in Translation – Episode 2: Tablespaces

Recap

In Episode 1 I addressed database instances but I’d like to correct a technicality. I compared SQL Server instances with Oracle Database instances. While this is pretty much correct, I’d like to add that technically this isn’t correct. At least not according to Oracle’s documentation. If you look on your installation disk for the docs directory I recommend reading the 2 Day DBA document. So far I’m finding it very valuable.

Here’s a quote from the Chapter 2 overview:

After you create a database, either during installation or as a
standalone operation, you do not need to create another. Each Oracle
instance works with a single database only. Rather than requiring that
you to create multiple databases to accommodate different
applications, Oracle Database uses a single database, and
accommodates multiple applications by enabling you to separate data
into different schemas within the single database.

According to the docs, an instance of Oracle is the same as a database. This is really all about semantics here, because I still view my original viewpoint as correct. But I also want to make sure what I post here is correct. To reference my language analogy from the first episode, two words or phrases from different languages can be considered direct translations, but there can often be cases where there is a better translation. That doesn’t make the translation incorrect. That’s the case here and it will be in other comparisons I make. I see it as a many to many relationship between terms in language as well as RDBMS vendors.

Tablespaces

From reading the documentation I would translate the term Tablespace in Oracle as a SQL Server database. To quote from the documentation:

A database is divided into logical storage units called tablespaces, which group
together related logical structures (such as tables, views, and other database objects).

Which sounds like the definition of a database to me. An Oracle instance comes with a set of default tablespaces installed. EXAMPLE, SYSTEM, SYSAUX, TEMP, UNDOTBS1 and USERS.

  • EXAMPLE is an optional tablespace. Example is analogous to Northwind and is used by samples and the documentation to provide guidance and a source for demos.
  • SYSTEM is Oracle’s version of SQL Server’s master database. It is a master catalog of the objects in the database.
  • SYSAUX is a compliment to System and is used to reduce the demand on System by offloading some of its data into a separate data file, which could be placed on separate physical drive to increase system performance.
  • TEMP is basically the same as SQL Server.
  • UNDOTBS1 is related to undo files, which are like SQL Server transaction logs. I’m not real clear on how this tablespace is used with undo files yet, I’m just pretty much rehashing the documentation here.
  • USERS is a user tablespace. If you create a database object and don’t have your own tablespace or don’t specify which tablespace your object will be stored in then it will be stored in USERS. SQL Server doesn’t have an equivalent here. SQL Server requires you to create a user database for your data and when you create an object, unless you use a fully-qualified object name the object you create will be placed in whichever database you’re connected to.

Each of the built-in tablespaces has its own data file and each user tablespace you create will contain one or more data file.

Just to touch on the system tables in SQL Server for a second, SQL Server also has two other system databases named “model” and “msdb”:

  • model is actually attached to an instance of SQL Server and there is no equivalent tablespace in Oracle. However, Oracle does have a different way of accomplishing the same thing. The DBCA has templates you can use, both default and user defined which can be used to create a new Oracle Database Instance.  This is generally the purpose of SQL Server’s model database, to act as a template for new user databases.
  • msdb is where objects like scheduled jobs are stored. I don’t know the exact correlation with Oracle, I imagine these are either in SYS or SYSAUX. But since I don’t know what is stored in the specific system tables in Oracle, I can’t make the comparison at this time.

Creating Tablespaces

Creating a new tablespace using the Oracle Enterprise Manager is much like it is in SQL Server, the options are very similar and once you locate the tools for creating a tablespace the rest should come naturally, assuming of course you’re familiar with how this is done in SQL Server.

If you’re logged into Enterprise Manager, click on the “Server” link located in the top nav bar. Then click on “Tablespaces” under the “Storage” heading.

OracleTablespaces

To create a new tablespace, click “Create” located just above the list of tablespaces.

Oracle_CreateTablespace

One the next screen, name the tablespace and accept the default settings. But before saving the settings, you need to add a data file:

Oracle_CreateTablespaceGeneral

After clicking the “Add” button, fill out the file name, check the AUTOEXTEND option and set the amount the file will grow by each time you exceed the currently allocated file space. Unfortunately, Oracle doesn’t have a option to grow by percent, which is what I always select in SQL Server. Once you’re selected the options, click “Continue”.

OracleEM_AddDatafile

Before saving these changes, click “Show SQL” at the bottom right. You can skip this step, of course, but I usually script my own DDL in SQL Server and so I want to learn the syntax by looking at what is generated by Enterprise Manager. Here’s what you’ll see:

CREATE SMALLFILE TABLESPACE "MYFIRSTORACLEDB" DATAFILE 'C:\APP\MARK\ORADATA\ORACLE11\DataFile1' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGINGEXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS 

To return to “Create Tablespace”, just click “Return”. Now click “OK” and you’re set. You’ve created your tablespace.

Conclusion

Now you can either script a tablespace or use the GUI to create one. Either way, you’ve done the equivalent of creating a SQL Server database file by creating your own Oracle tablespace. Next episode, we’ll look into creating tables. I don’t know about you, but already I feel like things have cleared up considerably and I’m much farther along. Once we’ve created our database objects we’ll look into connecting to our database from a client application and writing DML statements against our schema. Then we can look at other database objects like views, procedures, functions, triggers. These are obviously the same as SQL Server, but will of course have their own syntax and that’s what we’ll focus on at that point.

Labels: , , ,

Lost In Translation - Episode 1: Database Instances

I have been using SQL Server for about 8 years now. When compared to my peers (read: coworkers) I would classify myself as a SQL Server guru. When compared to many I meet on sites like SQLServerCentral.com where there seem to be those I would classify as SQL Server gods I feel small. When working for MaxPreps.com I was forced to sink or swim and had to learn in depth details about things like replication, mirroring, partitioning and the intricacy of the proceedure cache, execution plans and the query optimizer. So I have had the opportunity to really learn a lot of things most developers don't even want to understand about database engine internals. 

Now for the first time I am working for a client who uses Oracle and I have to admit I feel a bit helpless. The terminology is all different and it messes with my mind. Where in the past I've been confident, I feel lost. So as I take the opportunity to understand Oracle I hope to document the "translation" from SQL Server to Oracle. In large part this effort is for my own benefit, but I hope this will also benefit others who feel as overwhelmed as I do. 

In the spirit of a disclaimer, I understand that not everything will have a translation. But RDBMS is RDBMS and I believe (read: hope?) there will be much overlap between the two systems and this transition between the two will be like learning a new language. When I learned French in junior high and high school I learned that there are different ways of expressing things and not everything has a direct translation (ex. idiomatic expressions) and there are gotchas (ex. false friends) . But there is still a way of conveying the same ideas. My analogy may not work entirely in the world of RDBMS, but I will hold to it as my general hypothesis.  

Database Instances

The first translation I will attempt is that of Database Instance. This tripped me up and has been my first barrier to adoption. The reason for this is that is seems to be a bit of a "false friend". In language, a false friend is a word in another language that sounds similar a word in your own language, but isn't. For example, in French the word "blesser" sounds like "bless" in English. However, it means "to hurt". 

So to compare database systems, in SQL Server a database instance, is a file or grouping of files (aka. filegroup) which represent data which is locially stored together. But in Oracle a database instance is the collection of services which comprise the Oracle database system on the host machine. Which makes the Oracle database instance analogous to a SQL Server instance. Oracle doesn't have a default instance (at least not that I know of) like SQL Server, all instances are named. 

The mistake I made here was when I wanted to setup a database for a sample application. I opened up the Database Configuration Assistant (aka DBCA) to create a new database and installed a new instance. As I did this I found it strange that I was setting up duplicate accounts with different passwords for the "sys" account and other similar accounts. But what really tipped me off was when I finished the installation and my machine crawled to a painfully slow pace. When task manager finally opened up I noticed two instances of the "oracle.exe" process running alongside two instances of "java.exe". 

When I opened up the services mmc snap in (Start - Run... - "services.msc" - Ok), I saw that there were a set of services running for each "instance" I had setup. Stopping the services of the second instance brought my laptop back to a normal level of performance. 

Conclusion

So my next step will be to uninstall my second instance. As long as that goes smoothly, my next episode will address the translation for a SQL Server database. I think I know what that is, but until I'm sure I'll refrain from stating it so I can avoid having to make too many edits in this post after I get corrections from those who actually know Oracle.

Labels: , , ,

Tuesday, January 27, 2009

ASP.NET MVC: Compile Your Views for Release Build Only

First of all I have to say I'm excited. Today Scott Guthrie announced the Release Candidate (RC) for ASP.NET MVC is now available. And just yesterday my client signed off on the use of ASP.NET MVC for the project I'm currently ramping up. 

As I'm reading through the Release Notes and Scott's post, the first thing I wanted to setup was compile checking of my views. The recommendation is to only use this project setting when releasing your build to staging or production because of the time required. I gave it a shot with a brand-new MVC application and the difference was several seconds when the setting is enabled, compared to instant when the setting is disabled. 

Visual Studio doesn't support this setting yet, but if you open your project (*.csproj | *.vbproj) up in notepad you'll see the following somewhere in the topmost PropertyGroup element:

false

If you're updating an existing MVC project created with a pre-RC version of the MVC Framework the Release Notes tell you to add this "under the top-most element".  To turn on the setting, just change "false" to "true".

But as I mentioned before, both Scott and the Release Notes recommend against turning this on in the development environment. But if you're like me I will forget to turn this on when I'm ready for a release build. So I decided to try and move the MvcBuildViews element to both the debug and release PropertyGroups like this:



<PropertyGroup>
<Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
<Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
.... other settings ...
</PropertyGroup>
<PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
<MvcBuildViews>false</MvcBuildViews>
.... other settings ...
</PropertyGroup>
<PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
<MvcBuildViews>true</MvcBuildViews>
.... other settings ...
</PropertyGroup>


Now you have your compile-time checking for your release build and you won't get the extended build time during development.  To check it out add the following somewhere in your Views/Home/Index.aspx view file:

<p><%= (Int32) "String" %></p>

If your build is "debug" and you build your project you'll get a successful build. Now change your build to "release". This time you'll get a build error.

If you want this setting in all your MVC projects, just unzip the MVC project template (
C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\ProjectTemplates\CSharp\Web\1033\MvcWebApplicationProjectTemplateRC.cs.zip), modify the project file, then zip the project back up and replace it. 

I'm looking forward to finally be working on a production application using MVC, I'll try and provide updates as often as possible. 

Labels: , , ,

Tuesday, January 20, 2009

Entity Framework POCO Adapter + System.Web.DynamicData == POCO Loco

This month I started with a new client. I'm excited because we'll be rewriting a legacy app using Entity Framework against an Oracle backend, and possibly ASP.NET MVC. The team is full of good, experinced developers, any of which could lead their own team. 

But as with all new technologies we're experiencing pains with early adoption. One of design goals is to be testible. We're not quite talking TDD here. None of us have been on a TDD project here so we're more just talking about trying to move in a direction we think will be beneficial. 

Additionally, the project specifications require us to store meta data about our data model and create a relationship between the meta data and the users' roles. The relationship will tell us which fields a user can view or edit based on their role. So we're looking into wither we can use System.Web.DynamicData.MetaModel to collect the meta data about our model by using the MetaModel.RegisterContext method. 

Because we'd like to make as much of this testible as possible we decided to try Jaroslaw Kowalski's POCO Adapter to allow our data layer to be more testible. So as an exercise I fired up a new project, added a new ADO.NET Entity Data Model (.edmx) file and created a data model using the AdventureWorks database. Then I used the EFPocoClassGen utility to create my POCO classes, container and adapter based on my edmx file. Here's the Pre-build event commands in my project:

"$(SolutionDir)EFPocoClassGen.exe" /mode:PocoClasses /inedmx:"$(ProjectDir)Data\AdventureWorks.edmx" /outputDir:"$(ProjectDir)Data"

"$(SolutionDir)EFPocoClassGen.exe" /mode:PocoContainer /inedmx:"$(ProjectDir)Data\AdventureWorks.edmx" /outputfile:"$(ProjectDir)Data\PocoContainer.cs"

"$(SolutionDir)EFPocoClassGen.exe" /mode:PocoAdapter /inedmx:"$(ProjectDir)Data\AdventureWorks.edmx" /outputfile:"$(ProjectDir)Data\PocoAdapter.cs"

Then I added the RegisterContext code to my global.asax:

var model = new MetaModel();
model.RegisterContext(typeof(AdventureWorksEntitiesAdapter),
new ContextConfiguration { ScaffoldAllTables = true });

I ran my project and was greeted by with yellow screen of death bearing this message:

Error: An item with the same key has already been added.



[ArgumentException: An item with the same key has already been added.]
System.ThrowHelper.ThrowArgumentException(ExceptionResource resource) +51
System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add) +7460188
System.Data.Metadata.Edm.AssemblyCacheEntry.LoadRelationshipTypes(LoadingContext context) +2061
System.Data.Metadata.Edm.AssemblyCacheEntry.LoadTypesFromAssembly(LoadingContext context) +17
System.Data.Metadata.Edm.AssemblyCacheEntry.InternalLoadAssemblyFromCache(LoadingContext context) +244
System.Data.Metadata.Edm.AssemblyCacheEntry.LoadAssemblyFromCache(Assembly assembly, Boolean loadReferencedAssemblies, Dictionary`2 knownAssemblies, Dictionary`2& typesInLoading, List`1& errors) +137
System.Data.Metadata.Edm.ObjectItemCollection.LoadAssemblyFromCache(ObjectItemCollection objectItemCollection, Assembly assembly, Boolean loadReferencedAssemblies) +278
System.Data.Metadata.Edm.ObjectItemCollection.LoadFromAssembly(Assembly assembly) +61
System.Data.Metadata.Edm.MetadataWorkspace.LoadFromAssembly(Assembly assembly) +77
System.Web.DynamicData.ModelProviders.EFDataModelProvider..ctor(Object contextInstance, Func`1 contextFactory) +327
System.Web.DynamicData.ModelProviders.SchemaCreator.CreateDataModel(Object contextInstance, Func`1 contextFactory) +110
System.Web.DynamicData.MetaModel.RegisterContext(Func`1 contextFactory, ContextConfiguration configuration) +347
System.Web.DynamicData.MetaModel.RegisterContext(Type contextType, ContextConfiguration configuration) +79
AdventureWorksDDMVC.MvcApplication.RegisterRoutes(RouteCollection routes) in C:\Dev\AdventureWorksDDMVC\AdventureWorksDDMVC\Global.asax.cs:30
AdventureWorksDDMVC.MvcApplication.Application_Start() in C:\Dev\AdventureWorksDDMVC\AdventureWorksDDMVC\Global.asax.cs:36


If I removed the adapter and container created by the EFPocoClassGen utility the problem goes away. I tried debugging the DynamicData assembly to see what the key was which was causing the exception, but it seems like the version of System.Web.DynamicData on my system (3.5 Sp 1) and the debug symbols on the server don't match because stepping through most of the code the current line didn't match what the debugger was seeing.

Finally a collegue who had been using the DynamicData sample application (and wasn't having problems) compared his project with mine and pointed out that the sample project didn't have an edmx file, but was using the csdl, ssdl and msl from a compiled edmx. So we re-added the adapter and container classes from the POCO generator and deleted the designer.cs file generated by Visual Studio for the edmx. This fixed the problem. So then we went into the properties for the edmx and removed the value for the "CustomTool" property setting to prevent the designer.cs class from being regenerated.

I'd like to dig further and figure out exactly what was causing the duplicate key issue, but for now I just wanted to describe the issue and point out the solution/workaround.

Labels: , , ,

Friday, January 09, 2009

Modifying XP Pro Permissions For Workgroup Computers

Sql Server best practices recommend using a local, low-permissions account as the service account. But if your machine isn't a member of a domain then you don't get a security tab in the folder properties window. There's many reasons to want to control permissions when you're not on a domain, but how can you do it?

I had to do this today and I realized this wasn't something that was obvious to everyone, so I thought I'd make a quick post about it.

UPDATE - 1/28/2009

I have found there is a much better (read: safer) way to accomplish what is described below. I'm going to leave the instructions as I originally wrote them because they are still useful for using cacls, but here's a much safer way to do it.

Open Windows Explorer, then from the "Tools" menu select "Folder Options...". Select the "View" tab and uncheck the option "Use simple file sharing (Recommended)".

Changing setting now gives you the "Security" tab the next time you open the property window for any folder. The "Sharing" tab also has more options for advanced users. The reason this option is "recommended" is because it is for advanced users. If you're like me and you know what you're doing I prefer this setting turned off. 

END UPDATE

The command console has a command named "cacls" which allows you to manage NTFS permissions and this is built-in to XP and you can use it even if the UI doesn't give you a tool to manage these. Does this mean you can use this for XP Home as well? I don't know. I don't own Home Edition. I never have so I haven't been able to test this, but it would be easy for someone to confirm. If you do, please post a comment.

Anyway here's an example of the command. I created a folder with the path C:\Data for my Sql server account named (what else?) "sqlserver". I want to grant "change" (aka modify) permissions. You could use "Full Control", but Sql Server doesn't require Full Control, so why grant unnecessary rights?

WARNING: Incorrect use of cacls can cause you to loose access to the folder, even for administrators. Always use the /E switch as an argument unless you know what you're doing and intent to completely replace permissions on the folder.


  1. From the Windows "Start" menu, select "Run..."
  2. Type "cmd", then hit "Ok"
  3. Type the following in your command window:
    cacls /E /T /G sqlserver:C C:\Data
The /E switch (mentioned in the warning above) tells cacls to Edit the permissions. If you omit /E then the permissions will be completely replaced.

/T will include the change for all subfolders.

/G is the "Grant" switch. Following /G should be the account name, then a colon followed by the permissions to grant. In this case "C" represents "Change". Then add the path of the file/folder which you're updating.

If you want to see additional options/parameters for cacls, just type "cacls /?" and you'll get the help printed to the window.

Labels: , ,