ASP.NET Membership – implementare login come altro utente

Source file: Membership13.zip
Examining ASP.NET's Membership, Roles, and Profile - Part 13
By Scott Mitchell
Introduction
ASP.NET's forms-based authentication system in tandem with the Membership API and Login Web controls make it a cinch to create a user store, create user accounts, and allow visitors to log into the site. What's more, with little effort it's possible to define roles, associate user accounts with roles, and determine what functionality is available based on the currently logged in user's role (see Part 2). Many ASP.NET sites that use Membership have an Admin role, and users in that role are granted certain functionality not available to non-Admin users. Consider an online store - Admin users might be able to manage inventory, whereas the only way normal members could interact with the inventory was by adding items to their shopping cart.
I was recently working with a client who had an interesting request: he needed the ability for Admin users to be able to log into the site as another user, and perform actions as if that other person had logged in herself. Returning to the online store example, imagine that some customers periodically phone in their order, or mail or fax in an order form. An Admin, receiving this order, could then log into the site as that customer and place the order on the customer's behalf.
This article shows how to allow an Admin user to log into a Membership-based website as another user, and includes a complete working demo available for download at the end of the article. Read on to learn more!
Logging In As Another User
The ASP.NET Login control provides the user interface and logic for logging into a Membership-enabled website. The Login control presents two textboxes, one for the Username and one for the Password, along with a Login button. Clicking the Login button causes a postback during which the Login control attempts to validate the supplied credentials (the username and password) against the Membership system. If the credentials are valid, the user is logged into the system by means of a forms authentication ticket, which is a cookie that is saved to the user's browser that serves as the identity for the request. This cookie is sent from the browser to the website on subsequent requests and is the means by which a user remains "logged on" as they visit different pages on the site.
For certain websites it may make sense to allow Admin users to log into the site as another user in the system. (See "The Pros and Cons of Logging In As Another User" sidebar for a discussion that weighs the benefits of such a service against the potential harms.) If the Admin user knew the password of the user he wanted to log in as, then logging in as another user would be straightforward - the Admin user would simply enter the name and password of the user to log in as in the appropriate textboxes in the login page. However, it is usually the case that the Admin user does not know, and cannot find out, another user's password. By default, passwords in the SQL Server-based Membership system store the passwords in a hashed format. Because it is impossible to translate from the hashed format back to the original password, the Admin user cannot determine a user's password (unless the user divulges that information).
What's important to keep in mind is that the Login Web control is used simply to validate the supplied credentials and then create a corresponding forms authentication ticket, and that the forms authentication ticket is what identifies the visitor. Therefore, it is possible to create an ASP.NET page that "logs in" a user without knowing their password - all you have to do is create a valid forms authentication ticket with the username of the person you want to log in as. (After all, that's all the Login control does after it validates the supplied credentials.)
This article shows how to create an alternative login page that consists of three textboxes:

• One for the Admin user's username,
  
• One for his password, and
  
• One for the name of the user to log in as

In addition to these textboxes the page includes a Button Web control that, when clicked:

• Validates the Admin username and password,
  
• Ensures that the valid credentials are for an Admin user, and
  
• Ensures that the username of the user to log in as exists in the Membership system

If all of these checks pass then the page creates a forms authentication ticket for the username of the user to log in as. The net effect is that an Admin user can visit the page, enter his credentials, type in another user's username, and then log in as that user.

Creating the User Interface The user interface shown in the screen shot above is implemented as an HTML <table> with five rows in total - one row for each of the three TextBox controls, one for the Login Button control, and one that contains displays a message upon log in failure. In addition to the three TextBox controls the page contains three RequiredFieldValidator controls to ensure that the user supplies values for each of the inputs. (For more information on ASP.NET's validation controls, see Form Validation with ASP.NET - It Doesn't Get Any Easier!.)
You can build the above user interface by hand. A quicker way, though, is to add a Login control to the page and then turn it into a template (one of the options from the control's smart tag when viewed in the Designer). Doing so generates an HTML <table> within the Login control's declarative syntax. I then deleted the Login control tags (<asp:Login> and </asp:Login>), leaving the markup. Finally, I added a new table row for the "Log In As User Name" UI and removed the "Remember Me" CheckBox row (because I do not want to allow an Admin user to log on as another user and have that remembered across browser restarts).
Here's a snippet of the HTML and Web controls used for the user interface. Here you see the HTML <table> and the first row that contains the TextBox for the Admin user's username.

<table border="0" cellpadding="2" cellspacing="0">
   <tr class="AdminUserPrompt">
      <td align="right">
         <asp:Label ID="AdminUserNameLabel" runat="server" AssociatedControlID="AdminUserName">An <b>Admin</b> User Name:</asp:Label>
      </td>
      <td>
         <asp:TextBox ID="AdminUserName" runat="server"></asp:TextBox>
         <asp:RequiredFieldValidator ID="AdminUserNameRequired" runat="server"
            ControlToValidate="AdminUserName" ErrorMessage="The Admin User Name is required."
            ToolTip="The Admin User Name is required." ValidationGroup="LogInAs">*</asp:RequiredFieldValidator>
      </td>
   </tr>
</table>

Logging in the User After entering the Admin user's credentials, the name of the user to login as, and clicking the Login button, a postback occurs. In the Login Button's Click event handler we need to: validate the Admin user's credentials; assure that the Admin user is actually in the Admin role; and verify that the username to log in as exists in the system. If all that checks out, we need to create a forms authentication ticket for the user. The following code shows the Click event handler that handles this logic:

Protected Sub LoginButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LoginButton.Click
   'Make sure that the Admin username & password are valid
   If Membership.ValidateUser(AdminUserName.Text, AdminPassword.Text) Then
      'Yes, this username/password is good, but is this user in the Admin role?
      If Roles.IsUserInRole(AdminUserName.Text, "Admin") Then
         'Great, this user is in the Admin role! Now, is the username to login as a valid user?
         Dim LogInAsUser As MembershipUser = Membership.GetUser(LogInAsUserName.Text)
         If LogInAsUser IsNot Nothing Then
            'Yes, this user is valid! Great, let's log in as that user!
            FormsAuthentication.RedirectFromLoginPage(LogInAsUserName.Text, False)
         Else
            'The Admin username/password is kosher, but the user to log in as was not found
            FailureText.Text = String.Format("The user {0} does not exist in the Membership database.", AdminUserName.Text)
         End If
      Else
         'The user credentials for Admin user are valid, but the user is not an Admin
         FailureText.Text = "Only Admins can log into the site as another user. To login as yourself, please visit the standard Login page."
      End If
   Else
      'The Admin username/password are invalid
      FailureText.Text = "Your login attempt was not successful. Please try again."
   End If
End Sub

The code starts by calling the Membership.Validate(username, password) method, which returns True if the supplied credentials are valid, False otherwise. The username and password entered into the Admin username and password TextBox controls are passed in as the username and password parameters to this method. If this returns True then we next use the Roles API to ensure that the user is a member of the Admin role. If it returns False, the message "Your login attempt was not successful. Please try again" is displayed.
To determine if the Admin user credentials supplied belong to a user in the Admin role, the Roles API is used. A call to Roles.IsUserInRole(username, role) returns a Boolean value indiciating whether username exists in the role role. If the user is indeed an Admin then the last step is to check whether the username of the user to log in as is indeed valid. If the user is not in the Admin role then the message "Only Admins can log into the site as another user. To login as yourself, please visit the standard Login page" is displayed.
To determine if the username of the user to log in as is valid we call the Membership.GetUser(username) , which returns a MembershipUser object if the username is valid. If the user is not found then it returns Nothing (null in C#). If the user account exist we log in as that user by creating a forms authentication ticket with that user's identity. The FormsAuthentication class contains methods for creating and managing forms authentication tickets. The RedirectFromLoginPage(username, persistentCookie) method creates a forms authentication ticket for user username, adds it to the visitor's Cookies collection, and then redirects the user to the appropriate page (the URL specified in the ReturnUrl querystring value, if present; Default.aspx otherwise).
If the user to log in as is not found in the system the message "The user username does not exist in the Membership database" is displayed.
That's all there is to it! With this code in place an Admin user can visit the site, enter his credentials and the name of another user on the site, and then log in as that user. The following screen shots show this interaction. In the first screen shot, Admin user Scott enters his credentials and the name of another user, Sam. Clicking the Login button causes a postback and Scott is signed in as Sam and redirected to the homepage.

As you can see, Scott has logged in as Sam. For all intents and purposes, Scott is Sam. Scott will have the same user experience as Sam would have, if she was logged on. Also, any actions performed by Scott will be as if they were performed by Sam.

The Pros and Cons of Logging In As Another User
I've helped several small businesses move their paper-based business processes to a more modern electronic-based system, many of which have resulted in websites that have used the ASP.NET Membership system. Many of these businesses have requested the ability for Admin users to be able to log on as other users, and each have their own reasons. As noted in the Introduction, one business still had customers that faxed in orders, so they wanted to be able to place orders through the online system as that user who faxed in the order. Another client of mine had users who spent most of their day on a jobsite and recorded hours and other information into the system at the end of the day. If there was a data entry error or some missing piece of information, it was convenient for the secretary to be able to go in and fix the problem or add the data immediately than to wait for the user to return to the home office.
Allowing Admin users to log in as any other user has the advantage that the Admin users can very easily enter the system and make changes to another user's data or information. There's no need to coorindate with the user (who may be on a jobsite, for instance). Of course, this approach is a bit hamfisted. In a perfect world, there would be a set of administrative pages that would enable Admin users to make these changes as needed. But when you're working with a client on a tight budget and their choice is, "Allow users to log in as any other user to make changes," or, "Spend several days creating administration pages," the first choice is more attractive. It also has the benefit that it makes it easier for an Admin to diagnose a bug that is happening for a specific user because of user-specific data. That is, the Admin can log in as that user who is getting the error and is able to replicate it and test later to ensure that it's been fixed properly.
The downside to letting Admin users log in as other users is that while the Admin is logged in as another user, they can accidentally (or purposefully) wreck havoc. The Admin might forget that she's logged in as another user, and go and enter data specific to her, not realizing that she's entering that data for the user she is logged on as. Another problem is that there is no auditing capabilities, at least not how the feature has been implemented thus far. Imagine that a user asks, "Why does my order contain XYZ? I didn't order XYZ." You look in the database and see that the customer did indeed order XYZ and refuse a refund. But wait, did the customer really order that or was an Admin user logged on as that customer and placed that order? There's no way to tell!
If you are going to allow Admin users to log on as any other user I would, at minimum, encourage you to place very strict limits as to what users are Admins (or what users can use this "log on as" feature). I would also encourage you to extend the functionality we discussed above to remember the Admin user when he logs in as another user. The final section of this article discusses how to do this!
Storing the Username of the Admin User Who Logged On As Another User The FormsAuthentication.RedirectFromLoginPage method automatically creates a forms authentication ticket and adds it to the visitor'sCookies collection. The FormsAuthentication class also contains a GetAuthCookie(username, persistCookie) method that creates the forms authentication ticket and returns it, but does not add it to the UserId value, making it clear that Scott placed the order on Sam's behalf.)
To use this functionality, replace the call to FormsAuthentication.RedirectFromLoginPage with the following code:

' Create the cookie that contains the forms authentication ticket
Dim authCookie As HttpCookie = FormsAuthentication.GetAuthCookie(LogInAsUserName.Text, False)

' Get the FormsAuthenticationTicket out of the encrypted cookie
Dim ticket As FormsAuthenticationTicket = FormsAuthentication.Decrypt(authCookie.Value)

' Create a new FormsAuthenticationTicket that includes our custom User Data
Dim newTicket As FormsAuthenticationTicket = New FormsAuthenticationTicket(ticket.Version, ticket.Name, ticket.IssueDate, ticket.Expiration, ticket.IsPersistent, AdminUserName.Text)

' Update the authCookie's Value to use the encrypted version of newTicket
authCookie.Value = FormsAuthentication.Encrypt(newTicket)

' Manually add the authCookie to the Cookies collection
Response.Cookies.Add(authCookie)

'Return the user to the homepage
Response.Redirect("~/Default.aspx")

The above code starts by calling FormsAuthentication.GetAuthCookie to create the forms authentication cookie for the user who the Admin user wants to log in as. This cookie is actually encrypted so as to protect its contents as the cookie travels over the wire from the visitor's browser to the web server. Therefore, we need to descrypt it before we can work with it; this is accomplished by using theFormsAuthentication.Decrypt method. Next, we create a new forms authentication ticket, this time indicating that the Admin user's username should be included in the ticket's contents. This new authentication ticket is encrypted and added to the Cookies collection. Finally, the user is redirected to the homepage (Default.aspx ).
At this point whenever an Admin user logs on as another user the resulting forms authentication ticket identifies the user to log on as, but it also includes the Admin user's name (in the user data portion of the ticket). We can programmatically retrieve the Admin user's name using the following code:

If Page.User.Identity IsNot Nothing AndAlso TypeOf Page.User.Identity Is FormsIdentity Then
   Dim ident As FormsIdentity = CType(Page.User.Identity, FormsIdentity)
   Dim ticket As FormsAuthenticationTicket = ident.Ticket
   Dim AdminUserName As String = ticket.UserData
   If Not String.IsNullOrEmpty(AdminUserName) Then
      'An Admin user is logged on as another user...
      'The variable AdminUserName returns the Admin user's name
      'To get the currently logged on user's name, use Page.User.Identity.Name
   Else
      'The user logged on directly (the typical scenario)
   End If
End If

For more information on how to programmatically add and retrieve user data to a forms authentication ticket, see Forms Authentication Configuration and Advanced Topics.
I've updated the master page (~/MasterPage.master ) to include a Panel that displays a prominent message if an Admin user is currently logged in as another user. This message is shown only if an Admin user is logged on as another user; if a user logs on as himself, this message is not displayed.

Conclusion Forms-based authentication, the Membership system, and ASP.NET's Login controls make it easy to build a web application that supports user accounts. Unfortunately, there are no built-in tools to allow administrative users to log into the site as another user in the system. The good news is that such functionality can be implemented with a couple dozen lines of code, as evidenced in this article. Be sure to download the demo application to see this functionality in action.
Happy Programming!
Trovato qui.

Alternatives to generic collections for COM Interop

After some more research and trial-and-error, I think I found a solution by usingSystem.Collections.ArrayList. However, this does not work with getting a value by index. To do so, I created a new class ComArrayList that inherits from ArrayList and adds new methodsGetByIndex and SetByIndex.

COM Interop compatible collection:

 

public class ComArrayList : System.Collections.ArrayList {
    public virtual object GetByIndex(int index) {
        return base[index];
    }

    public virtual void SetByIndex(int index, object value) {
        base[index] = value;
    }
}

.NET component MyLibrary.GetDepartments:

public class Department {
    public string Code { get; private set; }
    public string Name { get; private set; }
    // ...
}


public ComArrayList GetDepartments() {
    // return a List(of Department) from the database
}

ASP:

<h1>The third department</h1>
<%= departments.GetByIndex(2).Name %>

ArrayList vs List vs HashTable vs Dictionary vs SortedList vs SortedDictionary

• Array - represents an old-school memory array - kind of like a alias for a normal type[] array. Can enumerate. Can't grow automatically. I would assume very fast insertion and retriv. speed.
• ArrayList - automatically growing array. Adds more overhead. Can enum., probably slower than a normal array but still pretty fast. These are used a lot in .NET [utilizzabile per esporre a COM una List(of T): vedi questo altro post -- è inutile??? Basta ritornare un IList!!!!]
• List - one of my favs - can be used with generics, so you can have a strongly typed array, e.g.List<string>. Other than that, acts very much like ArrayList.
• Hashtable - plain old hashtable. O(1) to O(n) worst case. Can enumerate the value and keys properties, and do key/val pairs.
• Dictionary - same as above only strongly typed via generics, such as Dictionary<string, string>
• SortedList - a sorted generic list. Slowed on insertion since it has to figure out where to put things. Can enum., probably the same on retrieval since it doesn't have to resort, but deletion will be slower than a plain old list.

I tend to use List and Dictionary all the time - once you start using them strongly typed with generics, its really hard to go back to the standard non-generic ones.
There are lots of other data structures too - there's KeyValuePair which you can use to do some interesting things, there's a SortedDictionary which can be useful as well.

---

So, although the generic collections likely add features, for the most part:

• List is a generic implementation of ArrayList.
  
• Dictionary is a generic implementation of Hashtable

Comma Separated Values (CSV) from Table Column

vedi anche qui.

USE AdventureWorks
GO

-- Check Table Column
SELECT Name
FROM HumanResources.Shift
GO

-- Get CSV values
SELECT SUBSTRING(
(SELECT ',' + s.Name
FROM HumanResources.Shift s
ORDER BY s.Name
FOR XML PATH('')),2,200000) AS CSV
GO

Risultati:

Name                                               
--------------------------------------------------
Day
Evening
Night

CSV
--------------------------------------------------
Day,Evening,Night

---

Altro metodo:

DECLARE @a AS VARCHAR(4000)
SET @a = ''
SELECT @a = @a + Nome + ','
FROM Argomenti_tb

SELECT @a

Risultati:

---------------------------------------------------------------------------------
Notizie,Primo Piano,Galleria Fotografica,Argomento pubblico 1,Aromento Privato 1,

---

Altro metodo: COALESCE

DECLARE @fruitNames VARCHAR(8000)
SELECT @fruitNames = COALESCE(@fruitNames + ', ', '') + FruitName FROM Fruits
SELECT FruitNames = @fruitNames

Risultati:

FruitNames
‐‐‐‐‐‐‐‐‐‐
Apple, Orange, Mango, Banana, Grape

The COALESCE function is used to ensure that there is no comma (,) after the last FruitName.

trovati qui.

Jaw-dropping experience with custom build providers

Source file: DalGeneratorBuildProvider.zip

Every once in a while when I'm exploring a technology, I experience a jaw-dropping moment where I'm blown away by how elegant, or well designed, or rife with potential something is. This weekend I had such a moment with ASP.NET 2.0 (yes, I know it was a holiday weekend, but I suddenly have a lot of deadlines looming). In preparation for a conference talk I'm giving at WinDev this year on compiliation in ASP.NET 2.0, I had a chance to try building my own custom build provider. As you may already know, when you place a source code file (like mycomponent.cs) under the top level /code directory it is compiled into an assembly and the rest of your pages can then reference the classes in that assembly. This is a nice feature in that it completes the delay-compile picture for deployment. In the ASP.NET 1.x you had to pre-compile any components you wanted to include and deploy them in the /bin directory (or find a page that wasn't using code behind and hijack its src= attribute, or use the assembly directive, each of which had its own set of issues). Now you can truly deploy nothing but .as*x and source files to a site, or even work in that mode and deploy with only binary.

What's even more interesting about the /code directory is that you can place other types of files in it as well, including .wsdl and .xsd files. Dropping a .wsdl file into /code will trigger a custom build step that involves generating a webservice proxy class which you then have immediate access to in your pages. VS.NET 2005 has really nice support for this too - as soon as you drop a .wsdl file into the directory you can use the object browser to view the generated proxy class, and you immediately have intellisense in all your pages for the proxy. Dropping a .xsd file into /code generates a typesafe DataSet-derived class like you may have used in the past when you ran the xsd.exe utility or selected 'Generate typesafe DataSet' from the designer.

Anyway, these are nice features, but this was not the jaw-dropping momemt for me. It turns out that you can build your own 'builder' and associate it with a specific extension. Once you deploy it, any file with your extension placed in the /code directory will then use your builder to spit out whatever code you want and compile it as part of the generated assembly for the /code directory. I have recently been working on building a data access layer for a client of mine, and for a number of reasons we chose not to go with typed DataSets, so instead I had written a code generator to extract schema information from a database connection and spit out a class with all the appropriate fields and properties based on the column types (actually similar to what the typed DataSet generator does, but without a lot of the other baggage). This seemed like a perfect candidate to test out as a custom builder, so I set to work.

The first step was to define the input file for my builder (this would be the file you place in the /code directory once I was done). I had an XML format I had been using, so I stuck with it and decided to use the extension of .dal for the file association. Here's a sample .dal file that would generate a class for the authors, publishers, discounts, and sales tables of the pubs database:

<!-- file: pubs.dal -->
<dalGenerator>
  <connectionString>server=.;trusted_connection=yes;database=pubs</connectionString>
  
  <namespace>MyDAL</namespace>
  
  <tables>
    <table>
      <name>authors</name>
    </table>
    <table>
      <name>publishers</name>
    </table>
    <table>
      <name>discounts</name>
    </table>
    <table>
      <name>sales</name>
    </table>
  </tables>
  
</dalGenerator>

Now, to associate the .dal extension with a custom build provider, you add an entry to your web.config file that looks like:

<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
  <system.web>
    <compilation>
      <buildProviders>
        <add extension=".dal" appliesTo="Code" type="PS.DalBuildProvider" />
      </buildProviders>
    </compilation>
  </system.web>
</configuration>

Where the PS.DalBuildProvider was the class I had yet to build. So the next step is obviously to go build the build provider class. To do this, you create a class the derives from the BuildProviderabstract base class, and most of the time you will just override the GenerateCode method which takes a reference to a AssemblyBuilder class as a paramter. Your job in this method is to create a newCodeCompileUnit (yes, you have to use the CodeDom) filled with whatever code you want compiled and add it to the AssemblyBuilder as a compile unit.

Here's a shell of such a class to give you an idea of what must be done - I've used theCodeSnippetCompileUnit in this sample so I can easily drop in a chunk of C# code. In the realDalBuildProvider I used the CodeDom classes (I'll provide a link to the complete sample below) which I recommend you do too for language independence.

using System;
using System.Text;
using System.Web.Compilation;
using System.Web;
using System.Web.UI;
using System.CodeDom;
using System.Web.Hosting;
using System.IO;
namespace PS
{
  public class DalBuildProvider : BuildProvider
  {
    public override void GenerateCode(AssemblyBuilder ab)
    {
   // retrieve input file
      string filename = base.VirtualPath;
      XmlDocument doc = new XmlDocument();
      using (Stream inFile = VirtualPathProvider.OpenFile(base.VirtualPath))
      {
        doc.Load(inFile);
      }
   // this is where I would navigate the DOM to generate code
   string sampleCode = "class Foo {}";
      ab.AddCodeCompileUnit(this, 
        new CodeSnippetCompileUnit(samplecode));
    }
  }
}

Once this class was built, I deployed it in my local /bin and added the pubs.dal file shown above to the /code directory, and it worked! Here's a screenshot of what the screen looked like when my jaw actually hit the floor :)

by fritz-onion.

Trovato qui.

Automatically generate classes and enums from sql datatables

Source code: RepositoryBuilder_src.zip.

Enable intellisense for sql repositories of messages, defaults etc in asp.net using BuildProvider

Introduction

With many applications, the SQL database doesn't only hold the business data. There is a good chance that the sys.messages table holds custom messages for the application and that another table may be used for application wide defaults. These repositories for messages, defaults and so on help the developer to maintain the vital attribute of consistency. When it comes to developing the asp.net application we need to make regular reference to these tables to determine the message id of a particular message from the database, or the exact name or value of a default. My experience has been that this can lead to short cuts and inconsistency. What was needed was an hassle free way of generating a class and enums for my messages.

What I required was all my messages for the application to be held in sys.messages and all my application defaults to be held in my table PortDefaults. In SQL they are available to other developers who want to run adhoc queries directly on the database, and to stored procedures and user defined functions which is the only access the asp.net application has to the database. When writing vb.net code for the application I required intellisense to offer me a list of the available messages and defaults for me to chose from so I did not need to constantly be refering back to my sql tables. Most of all I wanted all this to be fuss and maintenance free. If another developer had added a new set of messages, I wanted them to be available to me without any need to change other tables or code. If I added a new default to the database, I wanted it to show on my intellisense prompting.

The fabulous BuildProvider class in conjunction with the CodeDom allowed these goals to be acheived easily with considerable help from two excellent articles: Jaw-dropping experience with custom build providers by Friz Onion and Dino Esposito's Cutting Edge article.

Background

The application I was dealing with has hundreds of messages associated with it, and I realized that having the entire list appear in the intellisense drop down each time was going to be too much, so I decided that tables that I would use would have three columns:

1. An ID column. An integer unique to the message, this would be the integer value of my enum field value.
2. A text column. The message text which would be the enum field name. Of course I would have to get rid of any punctuation for the name to be valid.
3. A group column. A group name for the message to belong to which would be the enum's name. Fortunately, like many, I had ready made groups for my messages in sys.messages in as much as different number ranges represented different type of message eg. 50001-59999 for information, 70000-79999 for error etc.

sys.messages doesn't have a "group" column so I created a view to supply one.

SELECT message_id,
CASE
WHEN message_id < 60000 THEN 'Information'
WHEN message_id < 70000 THEN 'Warning'
WHEN message_id < 80000 THEN 'Error'
END AS [group],
text FROM sys.messages
WHERE (message_id > 50000)

Using the code

There are three distinct elements to our task. The first is to establish where our data tables are and which columns we are interested in. The second is generating the code based on the contents of our data tables. The third is to get visual studio to create the code automatically when we are developing code. The code we want to create is going to be something similar to:

Namespace repository

    Class SqlMessage

        Enum Information
            The_task_has_completed_successfully = 50001
            Your_password_was_changed = 50002
        End Enum

        Enum Warning
            Stock_of_this_item_is_now_low = 60001
            This_supplier_will_not_deliver_at_weekends = 60002
            Your_password_will_expire_in_PARM_days
        End Enum

        Enum [Error]
            No_items_were_found = 70001
            This_account_has_not_been_authorised = 70002
            Your_password_has_expired = 70003
        End Enum

    End Class

End Namespace

We start by creating an XML file to hold information about our sql connection, data tables and columns and a few details about what we want to create. We will give the file an extension of .repos. Any unused extension will do, but the extension will be important later. The name of the file is not important. Our XML file will look similar to this

	
	tableName="PortMessagesView"
	numberColumnName="message_id"
	groupColumnName="group"
	textColumnName="text"
	className="SqlMessage">

	
	tableName="PortDefaultsView"
	numberColumnName="uid"
	groupColumnName="group"
	textColumnName="name"
	className="PortDefaults">

The has the namespace attribute which specifies the namespace that our created code will be in. I have shown two s here to demonstrate that multiple repository entries can be made in the same file. The attributes for the are:

• connectionString - to get connected to the database.
• tableName - to specify which table (or in our case, view) the data we want is in.
• numberColumnName - to specify the column in the table containing the unique integer identifier.
• groupColumnName - to specify the column in the table containing the group that the message belongs to.
• textColumnName - to specify the column containing the text of the message.
• className - to specify the name of our class in our created code.

You must specify all attributes for each repository. We now know enough to move on to the second task: generating the code. If you are not familiar with the CodeDom, this is not the article to learn much from, but hopefully will be enough to inspire you to investigate further. We will navigate through our XML file creating a CodeCompileUnit and adding our namespace on the way

        'get the xml input file

        Try
            Dim filename As String = MyBase.VirtualPath
            Dim xmlStream As Stream = VirtualPathProvider.OpenFile(MyBase.VirtualPath)
            xmlFile.Load(xmlStream)
        Catch ex As XPath.XPathException
            System.Console.WriteLine("XML Exception:" & ex.Message)
        Catch ex As Exception
            System.Console.WriteLine("Exception:" & ex.Message)
        End Try

        'and create our navigator
        navigator = xmlFile.CreateNavigator

        'now on to the business of creating the code


        'somewhere to put our code
        Dim createdCode As New CodeCompileUnit

        'create the namespace
        Dim createdNamespace As New CodeNamespace

        'and find its name and name it
        Dim ns As String = ""
        iterator = navigator.Select("/repositorys")
        iterator.MoveNext()
        ns = iterator.Current.GetAttribute("namespace", "")
        If ns = "" Then
            ns = "DefaultRepository"
            System.Console.WriteLine("No namespace found - using default")
        End If
        createdNamespace.Name = ns
        createdCode.Namespaces.Add(createdNamespace)

        'add commentary
        Dim comment As New CodeCommentStatement("This code has been generated by the message repository tool")
        createdNamespace.Comments.Add(comment)

        'now we iterate through the individual repository(s) pulling of the attributes we need to access the data

        'so that we can enumerate the datarows
        iterator = navigator.Select("/repositorys/repository")

        Do While iterator.MoveNext
            Dim cs As String = iterator.Current.GetAttribute("connectionString", "")
            If cs = "" Then
                System.Console.WriteLine("connectionString not specified for repository " & iterator.Current.Name)
                Exit Sub
            End If

            '... and so on for our other attributes (tn(tablename), nc(numberColumn), gc(groupColumn) tc(textColumn) and cn(className) ...

we now know what all our attributes are so we can go on to fill the namespace with a class using CodeTypeDeclaration. Then fill the class with one or more enums (depending on how many groups there are) using CodeTypeDeclaration with isEnum set true. Each enum is filled with declarations using CodeMemberField to create the field and CodePrimitiveExpression to set its value. The field name must comprise only alphas and underscores so a quick function filterName will clean the text up for use.

    Private Function filterName(ByVal source As String) As String
        Dim filtered As String = ""
        For Each letter As Char In source.ToCharArray
            If Not Char.IsLetter(letter) Then
                If letter = "%"c Then
                    filtered &= "PARM"
                Else
                    letter = "_"c
                    filtered &= letter
                End If
            Else
                filtered &= letter
            End If
        Next
        Return filtered
    End Function

The filtered function returns PARM in place of the percent sign, just to highlight that a parameter is expected for the message. Not perfect as it does not deal with escaped % signs, but adequate for our purposes.

        'create our  top level class with the classname
        Dim messageClass As CodeTypeDeclaration = New CodeTypeDeclaration(cn)
        messageClass.Name = cn
        createdNamespace.Types.Add(messageClass) 'class is the default type


        'now access the data


        'get the data we need             
        Dim allDa As SqlDataAdapter = New SqlDataAdapter("select * from " & tn, cs)
        Dim allDs As DataSet = New DataSet
        allDa.Fill(allDs)

        'and and a list of the distinct groups in the table which will become enums
        Dim groupsDa As SqlDataAdapter = New SqlDataAdapter("select distinct [" & GC & "] from " & tn, cs)
        Dim groupsDs As DataSet = New DataSet
        groupsDa.Fill(groupsDs)

        For Each group As DataRow In groupsDs.Tables(0).Rows 'zero is the only table

            Dim currentGroup As String = group.Item(0) ' there is only column zero

            'now create an enum for this group
            Dim createEnum As CodeTypeDeclaration = New CodeTypeDeclaration(currentGroup)
            createEnum.IsEnum = True 'need to specify enum for this type

            'and add it to our message class
            messageClass.Members.Add(createEnum)

            'now fill it with declarations
            For Each datarow As DataRow In allDs.Tables(0).Select("[" & GC & "]='" & currentGroup & "'")
                'our field name is derived from the text, replacing punctuation with underscores using filterName function
                Dim fieldName As String = filterName(datarow.Item(tc).ToString)

                'and our value is the value form the numbercolumn
                Dim fieldValue As Integer = CInt(datarow.Item(nc))

                'create the field
                Dim field As CodeMemberField = New CodeMemberField
                field.Name = fieldName
                field.InitExpression = New CodePrimitiveExpression(fieldValue)

                'add to the current group enumeration
                createEnum.Members.Add(field)
            Next
        Next

We now have everything in our CodeCompileUnit. Of course we have not done anything with it yet. Our next task is to get the code in our CodeCompileUnit to be made available to our application. For this we use the BuildProvider facilities available to asp.net. If you have never come across the BuildProvider before, then be warned - THIS REALLY IS AS EASY AS IT LOOKS!

Firstly we need to tell asp.net about our provider which we do in web.config. I have created a folder in my App_Code folder called CustomBuilders which is where I will put the builder. We specify this in . The namespace and class of my BuildProvider will be CustomBuilders.ReposBuilder. We specify this in the type attribute of the in . Earlier, you will remember, we created our input XML file with a file extension of .repos. This is specifed in the extension attribute of the . The entry in the web.config file will be similar to the example below:

	
	
		
			
		
		
			
			
		
	
	...

The extension attribute of .repos (or whatever extension you chose for your XML input file earlier on) is the wonderful thing about the build provider. Now, everytime you place a file with the .repos (or whatever) extension into the App_Code folder, the build provider will be triggered to generate the code you have specified. You won't see the code (just as you don't see so much of the code in asp.net 2.0) but its there, and as if by magic your newly generated namespace and classes will be there for you use.

So (at last!) it is time to bring things together by creating our custom builder namespace (CustomBuilders) containing our build provider (ReposBuilder) . We inherit the BuildProvider class and provide just one override for the GenerateCode method which will contain our code-generating code and a couple of lines to write out the code.

Imports Microsoft.VisualBasic
Imports System
Imports System.IO
Imports System.Text
Imports System.Web
Imports System.Web.UI
Imports System.Web.Hosting
Imports System.Web.Compilation
Imports System.CodeDom
Imports System.Xml
Imports System.Data
Imports System.Data.SqlClient


Namespace CustomBuilders

     _
    Public Class ReposBuilder
        Inherits BuildProvider
        Private xmlFile As New XmlDocument
        Private navigator As XPath.XPathNavigator
        Private iterator As XPath.XPathNodeIterator


        Public Overrides Sub GenerateCode(ByVal assemblyBuilder As System.Web.Compilation.AssemblyBuilder)
            MyBase.GenerateCode(assemblyBuilder)

            '...
            'in here, our code for reading our attributes and creating 
            'our CodeComplieUnit
            '...

            If Not (createdCode Is Nothing) Then
                assemblyBuilder.AddCodeCompileUnit(Me, createdCode)
            End If
        End Sub
    End Class
End Namespace

This vb file needs to be placed in the App_Code/CustomBuilders folder that we created earlier. No need to compile - nothing else required beyond this code, the web.config entries and our XML input file with the .repos extension in the App_Code folder.

So what do we get?

When you add you .repos file to the App_Code folder asp.net will see to the code creation for you. If you go to the vb code for a page and add an import, you will see (in our case) {}repository come up on the list. Having imported it you can use a simple statement like:

dim t as integer = message.error.No_items_were_found

When you enter the dot after message, the intellisense will offer you Error|Information|Warning, and as you enter the dot after error, the intellisense dropdown offers you all you error messages. The variable t will be assigned the message number from you message table. The designer is even kind enough to put them all in alphabetical order for you!

Points of Interest

If, like me, you tend to shy away from some of the less obvious features of asp.net, because you don't have time to acquire the skills or feel that the return on the effort would not be worthwhile, think again when it comes to the BuildProvider. It really is so straightforward to use and even a simple application like this could reap gains in a very short time, not to mention improvements in consistency and reductions in maintenance. By stewartamackenzie. Trovato qui.

Dynamic Enumerations from Database Tables

Create an assembly containing enumerations defined in your database.

Introduction

Dynamic enumerations can easily be generated by your application by using the attached code.

Background

A vital part of database application programming is the identification and use of lookup items similar in functionality to constants. Prior to .NET, a developer could define their lookup values in database tables, but to use them as constants in their applications, they would have a separate step of defining them in their applications in the form of CONST values or as enumerations. This poses a problem at times - how to ensure that the database values stay synchronized with the application values. Using the EnumBuilder class and a few lines of code in your application, this is all possible without any intervention on your part. Using the code First, set a reference in your code to System.Reflection.Emit.

Imports System.Reflection.Emit

The code below is amazingly simple to use. Just set a few variables at the start of the code to your values and you are off. This code will create a new assembly, designated by the assemblyName variable, with a DLL extension, in the application folder.

        OpenDatabase() 'you provide this
        Try
            Dim assemblyName As String = "DynEnum"
            Dim lookupSQL As String = "SELECT ID, Name FROM AllMessageTypes"
            Dim nameField As String = "Name"
            Dim valueField As String = "ID"
            Dim enumerationName As String = "MessageTypes"
            Dim currentDomain As AppDomain = AppDomain.CurrentDomain
            Dim aName As AssemblyName = New AssemblyName(assemblyName)
            Dim ab As AssemblyBuilder = currentDomain.DefineDynamicAssembly(aName, AssemblyBuilderAccess.RunAndSave)
            Dim mb As ModuleBuilder = ab.DefineDynamicModule(aName.Name, aName.Name & ".dll")
            Dim eb As EnumBuilder = mb.DefineEnum(enumerationName, TypeAttributes.Public, GetType(Integer))
            'your saved connection
            Dim cmd As New SqlCommand(lookupSQL, _Connection)
            Dim dr As SqlDataReader = cmd.ExecuteReader
            If dr.HasRows Then
                Do While dr.Read
                    eb.DefineLiteral(dr.GetValue(dr.GetOrdinal(nameField)), dr.GetValue(dr.GetOrdinal(valueField)))
                Loop
            End If
            dr.Close()
            eb.CreateType()
            ab.Save(aName.Name & ".dll")
        Catch ex As Exception
            Throw ex
        End Try

From this point, all you have to do after you've run your application once and created the resulting DLL, is add the DLL to the references of your application. If you add or remove any values in the lookup table, it is automatically reflected in the enumeration, as it is generated by the application. I place this code in the constructor of the class that will consume the enumerations, although I don't believe that it matters where it's placed in relation to the rest of the application. If you find that's not the case, please let me know. Possible problems with this approach I haven't verified this yet, but there may be instances where anti-virus applications would see a change in a DLL during application execution as an infected file, so you may wish to keep an eye out for such a situation.

Points of interest

Interestingly enough, even though you may have a reference in the application to the newly created DLL, a file in use exception is not thrown when the application is run and the DLL is regenerated. I would have thought this would be the case, but it goes right through the code and creates the new DLL. If someone can explain this to me, it would be appreciated. Also, if you place a new value in the table and run your application, the new values are available to the application immediately. Of course, the new values are not available to Visual Studio for intellisense until after you've stopped the application.

History

By Greg Osborne. Original submission - Friday, November 14, 2008. Trovato qui.

SQLite Transactions and Parameters

Take for example, the following loop:

string lookupValue;
using (SQLiteCommand cmd = cnn.CreateCommand())
{
for (int i = 0; i < 100; i++)
{
lookupValue = getSomeLookupValue(i);
cmd.CommandText = @"UPDATE [Foo] SET [Value] = [Value] + 1
WHERE [Customer] LIKE '" + lookupValue + "'";
cmd.ExecuteNonQuery();
}
}

Now this code may look innocent enough, but it suffers some performance penalties and some security risks.  First, the CommandText has to be re-evaluated every time the command is executed.  SQLite must parse the statement and construct a query plan 100 times in this loop.  There are also a lot of memory allocations being done here.  The previously-prepared CommandText is freed, the new CommandText allocated.  A statement is compiled and strings are concatenated causing even more allocations and deallocations.  There are also a great number of interop calls being performed behind the scenes.

SQLite supports named and unnamed parameters.  Named parameters must appear in the SQL statement with either a $ (dollar), :(colon) or @ (at sign) prefix.  Unnamed parameters consist of a single question mark ? character.  Rewriting the above code to use a named parameter looks like this:

using (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = @"UPDATE [Foo] SET [Value] = [Value] + 1
WHERE [Customer] LIKE @lookupValue";
SQLiteParameter lookupValue = new SQLiteParameter("@lookupValue");
cmd.Parameters.Add(lookupValue);
  for (int i = 0; i < 100; i++)
{
lookupValue.Value = getSomeLookupValue(i);
cmd.ExecuteNonQuery();
}
}

The same code now using an unnamed parameter (compatible with Jet/Access)

using (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = @"UPDATE [Foo] SET [Value] = [Value] + 1
WHERE [Customer] LIKE ?";
SQLiteParameter lookupValue = new SQLiteParameter();
cmd.Parameters.Add(lookupValue);
for (int i = 0; i < 100; i++)
{
lookupValue.Value = getSomeLookupValue(i);
cmd.ExecuteNonQuery();

}
} 

---

Using trans As DbTransaction = Cnn.BeginTransaction()
....
trans.Commit()
End Using

or

Dim trans As DbTransaction 
Try
trans = Cnn.BeginTransaction()
....
trans.Commit()
Finally
If trans IsNot Nothing Then trans.Dispose()
End Try

SQLite is somehow special regarding transactions. SQLite executes much faster one single large transaction than many small transactions. This is a SQLite characteristic, other database engines might behave different.

Datatypes In SQLite Version 3

Trovato qui.

Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static typing. With static typing, the datatype of a value is determined by its container - the particular column the value is stored in.

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with the container in which it is stored. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statement that work on statically typed databases should would the same way in SQLite. However, the dynamic typing in SQLite allowed it to do things which are not possible in traditional statically typed databases.

1. Storage Classes

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

• NULL. The value is a NULL value.
• INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
• REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
• TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
• BLOB. The value is a blob of data, stored exactly as it was input.

Any column in a version 3 database, except an INTEGER PRIMARY KEY column, may be used to store any type of value.

All values supplied to SQLite, whether as literals embedded in SQL statements or values bound to pre-compiled SQL statements are assigned a storage class before the SQL statement is executed. Under circumstances described below, the database engine may convert values between numeric storage classes (INTEGER and REAL) and TEXT during query execution.

Storage classes are initially assigned as follows:

• Values specified as literals as part of SQL statements are assigned storage class TEXT if they are enclosed by single or double quotes, INTEGER if the literal is specified as an unquoted number with no decimal point or exponent, REAL if the literal is an unquoted number with a decimal point or exponent and NULL if the value is a NULL. Literals with storage class BLOB are specified using the X'ABCD' notation.
• Values supplied using the sqlite3_bind_* APIs are assigned the storage class that most closely matches the native type bound (i.e. sqlite3_bind_blob() binds a value with storage class BLOB).

The storage class of a value that is the result of an SQL scalar operator depends on the outermost operator of the expression. User-defined functions may return values with any storage class. It is not generally possible to determine the storage class of the result of an expression at compile time.

2. Column Affinity

In SQLite version 3, the type of a value is associated with the value itself, not with the column or variable in which the value is stored. (This is sometimes called manifest typing or duck typing.) All other SQL databases engines that we are aware of use the more restrictive system of static typing where the type is associated with the container, not the value. To look at it another way, SQLite provides dynamic datatypes such as one finds in "script" programming languages such as Awk, Perl, Tcl, Python, and Ruby, whereas other SQL database engines provide only compile-time fixed, static typing such as found in Pascal, C++, and Java.

In order to maximize compatibility between SQLite and other database engines, SQLite support the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The key here is that the type is recommended, not required. Any column can still store any type of data, in theory. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".

Each column in an SQLite 3 database is assigned one of the following type affinities:

• TEXT
• NUMERIC
• INTEGER
• REAL
• NONE

A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted to text form before being stored.

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, an attempt is made to convert it to an integer or real number before it is stored. If the conversion is successful (meaning that the conversion occurs without loss of information), then the value is stored using the INTEGER or REAL storage class. If the conversion cannot be performed without loss of information then the value is stored using the TEXT storage class. No attempt is made to convert NULL or blob values.

A column that uses INTEGER affinity behaves in the same way as a column with NUMERIC affinity, except that if a real value with no fractional component and a magnitude that is less than or equal to the largest possible integer (or text value that converts to such) is inserted it is converted to an integer and stored using the INTEGER storage class.

A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. (As an internal optimization, small floating point values with no fractional component are stored on disk as integers in order to take up less space and are converted back into floating point as the value is read out.)

A column with affinity NONE does not prefer one storage class over another. No attempt is made to coerce data from one storage class into another. The data is stored on disk exactly as specified.

2.1 Determination Of Column Affinity

The type affinity of a column is determined by the declared type of the column, according to the following rules:

1. If the datatype contains the string "INT" then it is assigned INTEGER affinity.
2. If the datatype of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
3. If the datatype for a column contains the string "BLOB" or if no datatype is specified then the column has affinity NONE.
4. If the datatype for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity
5. Otherwise, the affinity is NUMERIC.

If a table is created using a "CREATE TABLE <table> AS SELECT..." statement, then all columns have no datatype specified and they are given no affinity.

2.2 Column Affinity Example

CREATE TABLE t1(
    t  TEXT,
    nu NUMERIC, 
    i  INTEGER,
    no BLOB
);

-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, TEXT
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0');

-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, REAL
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0);

3. Comparison Expressions

Like SQLite version 2, version 3 features the binary comparison operators '=', '<', '<=', '>=' and '!=', an operation to test for set membership, 'IN', and the ternary comparison operator 'BETWEEN'.

The results of a comparison depend on the storage classes of the two values being compared, according to the following rules:

• A value with storage class NULL is considered less than any other value (including another value with storage class NULL).
• An INTEGER or REAL value is less than any TEXT or BLOB value. When an INTEGER or REAL is compared to another INTEGER or REAL, a numerical comparison is performed.
• A TEXT value is less than a BLOB value. When two TEXT values are compared, the C library function memcmp() is usually used to determine the result. However this can be overridden, as described under 'User-defined collation Sequences' below.
• When two BLOB values are compared, the result is always determined using memcmp().

SQLite may attempt to convert values between the numeric storage classes (INTEGER and REAL) and TEXT before performing a comparison. Whether or not any conversions are attempted before the comparison takes place depends on the nominal affinity assigned to the expressions on either side of the binary operator. Affinities are assigned to expressions in the following cases:

• An expression that is a simple reference to a column value has the same affinity as the column it refers to. Note that if X and Y.Z are column names, then +X and +Y.Z are considered expressions.
• An expression of the form "CAST(<expr> TO <type>)" is assigned an affinity as if it were a reference to a column declared with type <type>

Conversions are applied before the comparison as described below. In the following bullet points, the two operands are refered to as expression A and expression B. Expressions A and B may appear as either the left or right operands - the following statements are true when considering both "A <op>B" and "B <op>A".

• When two expressions are compared, if expression A has INTEGER or REAL or NUMERIC affinity and expression B does not, then NUMERIC affinity is applied to the value of expression B before the comparison takes place.
• When two expressions are compared, if expression A has been assigned an affinity and expression B has not, then the affinity of expression A is applied to the value of expression B before the comparison takes place.
• Otherwise, if neither of the above applies, no conversions occur. The results are compared as is. If a string is compared to a number, the number will always be less than the string.

In SQLite, the expression "a BETWEEN b AND c" is equivalent to "a >= b AND a <= c", even if this means that different affinities are applied to 'a' in each of the comparisons required to evaluate the expression.

Expressions of the type "a IN (SELECT b ....)" are handled by the three rules enumerated above for binary comparisons (e.g. in a similar manner to "a = b"). For example if 'b' is a column value and 'a' is an expression, then the affinity of 'b' is applied to 'a' before any comparisons take place.

SQLite treats the expression "a IN (x, y, z)" as equivalent to "a = +x OR a = +y OR a = +z". The values to the right of the IN operator (the "x", "y", and "z" values in this example) are considered to be expressions, even if they happen to be column values. If the value of the left of the IN operator is a column, then the affinity of that column is used. If the value is an expression then no conversions occur.

3.1 Comparison Example

CREATE TABLE t1(
    a TEXT,
    b NUMERIC,
    c BLOB
);

-- Storage classes for the following row:
-- TEXT, REAL, TEXT
INSERT INTO t1 VALUES('500', '500', '500');

-- 60 and 40 are converted to '60' and '40' and values are compared as TEXT.
SELECT a < 60, a < 40 FROM t1;
1|0

-- Comparisons are numeric. No conversions are required.
SELECT b < 60, b < 600 FROM t1;
0|1

-- Both 60 and 600 (storage class NUMERIC) are less than '500'
-- (storage class TEXT).
SELECT c < 60, c < 600 FROM t1;
0|0

4. Operators

All mathematical operators (which is to say, all operators other than the concatenation operator "||") apply NUMERIC affinity to all operands prior to being carried out. If one or both operands cannot be converted to NUMERIC then the result of the operation is NULL.

For the concatenation operator, TEXT affinity is applied to both operands. If either operand cannot be converted to TEXT (because it is NULL or a BLOB) then the result of the concatenation is NULL.

5. Sorting, Grouping and Compound SELECTs

When values are sorted by an ORDER by clause, values with storage class NULL come first, followed by INTEGER and REAL values interspersed in numeric order, followed by TEXT values usually in memcmp() order, and finally BLOB values in memcmp() order. No storage class conversions occur before the sort.

When grouping values with the GROUP BY clause values with different storage classes are considered distinct, except for INTEGER and REAL values which are considered equal if they are numerically equal. No affinities are applied to any values as the result of a GROUP by clause.

The compound SELECT operators UNION, INTERSECT and EXCEPT perform implicit comparisons between values. Before these comparisons are performed an affinity may be applied to each value. The same affinity, if any, is applied to all values that may be returned in a single column of the compound SELECT result set. The affinity applied is the affinity of the column returned by the left most component SELECTs that has a column value (and not some other kind of expression) in that position. If for a given compound SELECT column none of the component SELECTs return a column value, no affinity is applied to the values from that column before they are compared.

6. Other Affinity Modes

The above sections describe the operation of the database engine in 'normal' affinity mode. SQLite version 3 will feature two other affinity modes, as follows:

• Strict affinity mode. In this mode if a conversion between storage classes is ever required, the database engine returns an error and the current statement is rolled back.
• No affinity mode. In this mode no conversions between storage classes are ever performed. Comparisons between values of different storage classes (except for INTEGER and REAL) are always false.

7. User-defined Collation Sequences

By default, when SQLite compares two text values, the result of the comparison is determined using memcmp(), regardless of the encoding of the string. SQLite v3 provides the ability for users to supply arbitrary comparison functions, known as user-defined "collation sequences" or "collating functions", to be used instead of memcmp().

Aside from the default collation sequence BINARY, implemented using memcmp(), SQLite features two extra built-in collation sequences intended for testing purposes, the NOCASE and RTRIM collations:

• BINARY - Compares string data using memcmp(), regardless of text encoding.
• NOCASE - The same as binary, except the 26 upper case characters of ASCII are folded to their lower case equivalents before the comparison is performed. Note that only ASCII characters are case folded. SQLite does not attempt to do full UTF case folding due to the size of the tables required.
• RTRIM - The same as binary, except that trailing space characters are ignored.

7.1 Assigning Collation Sequences from SQL

Each column of each table has a default collation type. If a collation type other than BINARY is required, a COLLATE clause is specified as part of the column definition to define it.

Whenever two text values are compared by SQLite, a collation sequence is used to determine the results of the comparison according to the following rules. Sections 3 and 5 of this document describe the circumstances under which such a comparison takes place.

For binary comparison operators (=, <, >, <= and >=) if either operand is a column, then the default collation type of the column determines the collation sequence to use for the comparison. If both operands are columns, then the collation type for the left operand determines the collation sequence used. If neither operand is a column, then the BINARY collation sequence is used. For the purposes of this paragraph, a column name preceded by one or more unary "+" operators is considered a column name.

The expression "x BETWEEN y and z" is equivalent to "x >= y AND x <= z". The expression "x IN (SELECT y ...)" is handled in the same way as the expression "x = y" for the purposes of determining the collation sequence to use. The collation sequence used for expressions of the form "x IN (y, z ...)" is the default collation type of x if x is a column, or BINARY otherwise.

An ORDER BY clause that is part of a SELECT statement may be assigned a collation sequence to be used for the sort operation explicitly. In this case the explicit collation sequence is always used. Otherwise, if the expression sorted by an ORDER BY clause is a column, then the default collation type of the column is used to determine sort order. If the expression is not a column, then the BINARY collation sequence is used.

7.2 Collation Sequences Example

The examples below identify the collation sequences that would be used to determine the results of text comparisons that may be performed by various SQL statements. Note that a text comparison may not be required, and no collation sequence used, in the case of numeric, blob or NULL values.

CREATE TABLE t1(
    a,                 -- default collation type BINARY
    b COLLATE BINARY,  -- default collation type BINARY
    c COLLATE REVERSE, -- default collation type REVERSE
    d COLLATE NOCASE   -- default collation type NOCASE
);

-- Text comparison is performed using the BINARY collation sequence.
SELECT (a = b) FROM t1;

-- Text comparison is performed using the NOCASE collation sequence.
SELECT (d = a) FROM t1;

-- Text comparison is performed using the BINARY collation sequence.
SELECT (a = d) FROM t1;

-- Text comparison is performed using the REVERSE collation sequence.
SELECT ('abc' = c) FROM t1;

-- Text comparison is performed using the REVERSE collation sequence.
SELECT (c = 'abc') FROM t1;

-- Grouping is performed using the NOCASE collation sequence (i.e. values
-- 'abc' and 'ABC' are placed in the same group).
SELECT count(*) GROUP BY d FROM t1;

-- Grouping is performed using the BINARY collation sequence.
SELECT count(*) GROUP BY (d || '') FROM t1;

-- Sorting is performed using the REVERSE collation sequence.
SELECT * FROM t1 ORDER BY c;

-- Sorting is performed using the BINARY collation sequence.
SELECT * FROM t1 ORDER BY (c || '');

-- Sorting is performed using the NOCASE collation sequence.
SELECT * FROM t1 ORDER BY c COLLATE NOCASE;