Tuesday, 15 September 2015

A Python API for Pyrrho

As of today, OSPLink.py is available in the distribution and enables the open-source Pyrrho server OSPSvr to be accessed from Python 3.4 clients. The API has similarities to Pyrrho’s version of ADO.NET.
To use OSPLink.py, place it in the same folder as your Python script.

(Update 30 Sept 2015: OSPLink.py now includes Python implementations of the AWebSvr classes from TAWQT.com and does not depend on .NET. There is a tutorial on the PyrrhoDB website containing a fully-worked-out sample.)

For example (assuming OSPSvr.exe is running on the local machine):

from OSPLink import *
conn = PyrrhoConnect("Files=Temp;User=Fred")
    conn.act("create table a(b date)")
except DatabaseError as e:
conn.act("insert into a values(current_date)")
com = conn.createCommand()
com.commandText = 'select * from a'
rdr = com.executeReader()
while rdr.read():

For full details, see section 6.8 of the PyrrhoDB Manual.

Sunday, 16 August 2015

On versioning and "related data"

Over on TAWQT.com, there is some discussion on when a change in data should result in a new version. Several DBMS provide optimistic concurrency based on row versioning, including DB2 and SQL Server. But the versioning offered by these systems is confined to the base table and does not consider related data in other tables. That is, it is tied to the physical database, and not the logical database. If the database schema is changed, the version semantics may well be quite different, and the application logic may need to be redesigned.

One of the examples on the TAWQT site is about students’ marks in a set of subjects. Under what circumstances is there a new version of the data for a student? Is it (A) only when the student’s name or address has changed, or (B) also when the subject information changes? If the data is held in 3NF relational tables, the normal answer would be (A), but if the data for each student is held in a document, or the subject information is in a column of type XML or ARRAY, the answer would be (B).

For a great many business applications, answer (B) would be preferable, and many businesses prefer non-relational designs for this sort of reason. If the DBMS only offers (A), then application programmers may need to develop some customised versioning logic. This is not very difficult, and is done in any case for DBMS’s that don’t offer versioning.

So it is natural to ask whether a change to row versioning semantics would be helpful. In this note I would like to explore the consequences of updating the version of a row if values are changed in that row or any row that references it. As an experiment this change has been introduced in Pyrrho 5.3’s built-in CHECK and VERSIONING properties.

The references being considered are of course confined to the database: we won’t know about changes that take place elsewhere. But in the example cited, if there are two relational tables STUDENT and SUBJECT then a change to a mark in a subject will give a new version to one row in each table.

In making this change I have of course considered that a database may be intended to work with many different applications, and this particular sort of versioning may suit some applications but not others. I am convinced however that the use of foreign keys in relational databases is so widespread that this is the correct default behaviour, correct in the sense that it would be useful in a great many more cases. Programmers can still do something different if they want.

Some will no doubt see this as a departure from common practice. But this is an area where practice is far from common, and the SQL standard is silent on the issue. It would be possible to have an intermediate implementation where the relevance of related tables is determined by the role assigned to the application, with some sort of metadata associated with the foreign key. But it seems unattractive.

Monday, 10 August 2015

AngularJS and avoiding Entity Frameworks

Following the series of posts about ASP.NET and REST in this blog during May, I would now like to advertise a quite different approach, developed on www.tawqt.com .
The idea is that with the trend towards server virtualisation, it is becoming much more attractive to build systems where each web application has its own custom web server, instead of sharing web server such as IIS or even Apache. At the same time, an embedded database system is often more efficient that a client-server database.
Tawqt.com provides a basic tutorial revisiting a simple Web application from one of the AngularJS tutorials. And Pyrrho of course has its own version.
Tawqt.com's framework also brings a number of improvements over ASP.NET, by making all views cacheable and using AJAX for dynamic content, using JSON objects instead of HTML in the API. The obvious next step is then to use JSON objects in the database too, as MongoDB does, and of course Pyrrho now supports that too.
So, we also have a Pyrrho-Document version of the same Angular tutorial! I look forward to comments...

Tuesday, 26 May 2015

ASP.NET with a REST API: Part 1

Pyrrho's new REST API works well with ASP.NET. This sample was originally implemented using LocalDB/SQL Server and the Entity Framework. Pyrrho has some features that are much easier to use, as this sample shows. 
Visual Studio uses its own IIS, and for this sample the Pyrrho server needs to be running, with its HTTP service and Pyrrho service. It is not a simple matter to copy or deploy ASP.NET applications, and there are several web sites that discuss how to do so. To make it easier to deploy, you could use EmbeddedPyrrho and the real IIS from the start, but then you have to add more configuration steps during development.
We are still using SQL Server for the ASP.NET account control. You may find yourself accessing the DefaultConnection (Haikus) through Visual Studio’s Server Explorer to remove entries from AspNetUsers.
We will not use the Entity Framework. In my arrogant opinion, client side DataSets and data models are really bad for any form of concurrency and transaction management, and in this respect are a serious error in both the Microsoft and Java world. Instead I invite readers to examine direct use of an database Connection as in step 10 below, to Post (step 12) a strongly-typed class whose structure has been obtained from the DBMS. There are Get, Put and Delete too of course, and this sample will show their use. Put is in step 20, and Get and Delete will be in the next part of this posting. Post automatically supplies any missing components for a new primary key. We will use BLOBs for icons and author pictures.
1. Start up VS Express 2013 as administrator, and from the start Page, select New project. Make sure Visual C# and Web is selected. Give the Name as Haikus, and make sure the location is somewhere suitable such as C:\Temp.Click OK.

2. Select MVC, and keep the the other defaults. Click OK
3. After a few minutes, you get an empty project. Try Run. A browser starts up to access your MVC app running on your local machine. This is the default MVC 5 application. It has many features, but don’t touch them just now. Close the browser window.

Build the underlying database

5. Make sure the Pyrrho server OSP.exe is running, we assume on localhost with the default port 5433.

From a command prompt, start up the PyrrhoCmd utility for the database haikus: PyrrhoCmd haikus and enter the commands shown to create the database tables we will use.
The square brackets here help with line wraparound.
[create table author(id char primary key,name char,pic blob)]

[create table verse(id int primary key,ownr char references author,cont char)]
[create table tag(id int primary key,name char)]
[create table rating(vid int references verse,uid char references author,score int)]
[create table tagging(tid int references tag,vid int references verse)]
[create table comment(id int primary key,vid int references verse,aid char references author, cont char)]
[create table likes(vid int references verse,uid char references author)]
6. Still at the SQL prompt, use the command: table “Role$Class”, to get class definitions for our application.

“Your mileage may vary”: the Schema keys may be different for you. They are used in Pyrrho’s REST API to check that the class definition remains valid in the database and role. 
In the command prompt, right click and Select All, then Enter to copy the text to the clipboard, and paste it into a notepad.
We will use each of the class definitions to define classes AUTHOR, VERSE etc. in the next step.

7. Back in Visual Studio, in Solution Explorer, right-click References, Add Reference.. and Browse.. to add OSPLink.dll from the Pyrrho distribution. Click Add.

8. In Solution Explorer, right-click Models select Add/New Item../Visual C#/Code/Class, change the Name to AUTHOR.cs and click Add.

9. In the AUTHOR class, paste in the class definition from AUTHOR that you obtained in step 6.

Similarly add Model classes to your project for VERSE, TAG, RATING, TAGGING, COMMENT and LIKES. Click the Save All icon in Visual Studio.
10. Add another Models class called Connect and edit it to look like this.
using System;

namespace Haikus.Models
    using Pyrrho;

    public class Connect
        public static PyrrhoConnect conn;
        internal Connect()
            conn = new PyrrhoConnect("Files=haikus");

11. Open the Startup.cs file and add a call to make the connection to the database as shown
   public partial class Startup
        public void Configuration(IAppBuilder app)
            new Models.Connect();

12. Now let’s start wiring this up. Any time that a user registers a new account, we want to record them in the Author table.

First add a Find method to the Author class, two curly braces in from the end of AUTHOR.cs
internal static AUTHOR Find(string us)
   var au = Connect.conn.FindOne<AUTHOR>(us);
   if (au != null)
        return au;
   au = new AUTHOR { ID = us };
   return au;
13. In Solution Explorer, double-click Controllers>AccountController.cs. Use the drop-down boxes to find the Register(RegisterViewModel model) method and add the line AUTHOR.Find(..) shown.
public async Task<ActionResult> Register(RegisterViewModel model)
   if (ModelState.IsValid)
   {     var user = new ApplicationUser() { UserName = model.Email, Email = model.Email };
      IdentityResult result = await UserManager.CreateAsync(user, model.Password);
      if (result.Succeeded)
          await SignInAsync(user, isPersistent: false);
// For more information on how to enable account …..
// ...
           return RedirectToAction("Index", "Home");
  // If we got this far, something failed, redisplay form
   return View(model);
14. Before we run the program, in Solution Explorer, left-click the project. In the Properties pane, ensure that WindowsAuthentication is Disabled and AnonymousAuthentication is Enabled.

Run the program, and register a user called fred@abc.com with password Pa$$w0rd.
Click Register and close the browser.

15. When you are returned to the home page, verify that you have a welcome message for your new user. Close the browser.  In the command prompt we had before, do “table author” to see that fred got defined by the code we added.

Author Profule Pictures

16. Let’s show pictures for logged-in users. We’ll add a URL for this in the AccountController. We’ll create a default image to use if the user hasn’t given a picture. In AccountController.cs add using System.Drawing; and using System.IO; Add this code two closing braces before the end of AccountController.cs:
public ActionResult Picture(string id)
   var au = AUTHOR.Find(id);
   var b = au.PIC;
   if (b==null)
    var im = new Bitmap(24, 24);
    using (var g = Graphics.FromImage(im))
      g.FillRectangle(Brushes.White, new Rectangle(0, 0, 24, 24));
      g.DrawEllipse(Pens.Black, new Rectangle(6, 6, 12, 12));
      g.DrawLine(Pens.Black, 2, 22, 7, 17);
      g.DrawLine(Pens.Black, 22, 22, 17, 17);
    var ms = new MemoryStream();
    im.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
    b = ms.ToArray();
   return File(b, "image/jpg");
17. Now replace the Hello greeting with the user's picture:
@using Microsoft.AspNet.Identity
@if (Request.IsAuthenticated)
    using (Html.BeginForm("LogOff", "Account", FormMethod.Post, new { id = "logoutForm", @class = "navbar-right" }))
        var id = User.Identity.Name;

    <ul class="nav navbar-nav navbar-right">
 <a href="@Url.Action("Index","Manage")"> <img title="@id" alt="@id" src="@("/Account/Picture/'" + id + "'/")" />  @id</a>
18. Now let’s start to provide a way for authors to add their profile picture. In Views/Manage/Index.cshtml add the following code somewhere sensible:
<legend>Choose a profile picture:</legend>
@using (Html.BeginForm("Profile", "Account", FormMethod.Post, new { enctype = "multipart/form-data" }))
    <input type="file" name="file" />
    <input type="submit" value="Upload Picture" />
19.  We need to handle the postback.

Add the following code two closing braces in from the end of AccountController:
// This action handles the form POST and the upload

/// A user has just updated their profile picture

public new ActionResult Profile(HttpPostedFileBase file)
    var del = new System.Drawing.Image.GetThumbnailImageAbort(dummy);
    IntPtr p = new IntPtr();
    // Verify that the user selected a file
    if (file != null && file.ContentLength > 0) 
        var sr = file.InputStream;
        var im = System.Drawing.Image.FromStream(sr);
        if (im.Width>24 || im.Height>24)
            im = im.GetThumbnailImage(24, 24, del, p);
        var ms = new MemoryStream();
     // redirect back to the index action to show the form once again
     return RedirectToAction("../Home/Index");        
 // Kludge for GetThumbnail
 bool dummy()
     return false;
 }20. Finally define the SetPicture method in the AUTHOR.cs class:
internal static void SetPicture(string user, byte[] pic)
    var au = Find(user);
    au.PIC = pic;
Next steps - in Part 2: Editing Haikus, Likes and Comments.