When developing the conceptual level of a database, new structure can be added by using views and generated columns. The programming model for this in the SQL standard is theoretically complete, but in practical examples the standard machinery using triggers can seem over-complex.
Recent programming frameworks (e.g. .NET 4) have focussed instead on providing up to 4 pieces of SQL for the GET, PUT, POST and DELETE operations for any named structure. This suggests an extension to the CREATE VIEW syntax as follows:
CREATE VIEW id AS QueryExpression [UPDATE SqlStatement] [INSERT SqlStatement] [DELETE SqlStatement]
Similarly the syntax for a generated column can be extended:
GenerationRule = GENERATED ALWAYS AS '('Value')' [UPDATE '(' Assignments ')' ]
The implementation of these ideas is extremely straightfoward so they are good candidates for inclusion in the next version of Pyrrho.
More importantly, together with the following syntax extensions:
SqlStatement =| HTTP (POST|UPDATE|DELETE) url ['('Value')']
QueryExpression =| HTTP GET url .
we can see how a local database include or refer to data selected from an external database.
It is quite interesting to consider the effect of such extensions on the transaction model. One reasonable approach would be to recommend that for long transactions database designers should use Web-based transaction control as in JAX-RS. Another reasonable suggestion would be that such web requests should be tunnelled in duplex streams established for each web host referenced in the transaction, and this would be quite suitable for short transactions. For example, instead of the sequence
PUT http://myhost/mydb/url1 (+20)
PUT http://myhost/mydb/url2 (-20)
send
POST http://myhost/mydb/strtrans (PUT /url1 (+20) PUT /url2 (-20) COMMIT)
Monday, 25 July 2011
Saturday, 9 July 2011
Reflection and Relationships
Relational databases need to provide support two-way relationships especially now that such relationships are so easily navigated using REST-style URLs. This article proposes a simple approach to defining path navigation in the relational database at physical or conceptual level. The resulting generated columns (or properties) are of type ROW ARRAY and are quite difficult to use in SQL, but very easy from REST.
Interestingly the Java Persistence Query Language provides query language support for such two-way relationships, but required a lot of annotation infrastructure.
For example (inspired by the Java Tutorial), suppose we have tables
player: (pid int primary key, pname char)
team: (tid int primary key, tname char, capt int references player)
playerteam: (pid references player, tid references team)
This club has a number of teams for different purposes, and players can belong to (or even captain) more than one team. Then the syntax proposed here would allow us to write definitions as simple as
alter table player add captains reflects team
alter table player add playsin reflects playerteam
Then in the REST interface we can write
http://myclub.org/db/db/player(pname=’Fred Bloggs’)/captains/tname
to obtain the names of all the teams Fred captains.
The syntax is one of the alternatives for ColumnDefinition, and defines a generated-always column:
| id REFLECTS [Cols IN] Table_id [Cols]
Recall that ColumnDefinition is also used to define a property in a role-based data model, and of course in that context entity and property identifiers can be used in place of table and column identifiers.
The REFLECTS definition sets up an array-valued generated column as a reverse reference: e.g. if t.f references g(i) then “alter g add h reflects t” is a shorthand for “alter g add h row t array generated always as (select * from t where f=i)”. That is, each cell in the generated column contains a list of rows from the referenced table.
The optional [Cols] following the referenced Table_id allows selection of a particular foreign key if there is more than one.
For many-many relationships the referenced table will be a secondary table, so that the type of the generated column is defined only implicitly. Still the basic idea is simple enough. Suppose we have a many-many relationship R between tables A and B. In a relational database this will be implemented by means of a secondary table AB consisting of pairs of primary keys for A and B. In both A and B we can easily define additional generated columns for our convenience (e.g. for REST navigation):
alter table A add X reflects AB
alter table B add Y reflects AB
Then in each row of A, X will be a list of rows from table B; and in each row of B, Y will be a list of rows from table A. In practice we would probably name these properties B and A rather than X and Y to make the resulting REST-based navigation more intuitive.
Again, the optional column specification [Cols IN] allows selection of a particular foreign key in the secondary table. In the above player and team example we could define
alter table player add captains reflects team(capt)
alter table team add players reflects (pid) in playerteam(tid)
The full details of the [Cols IN] option are as follows. [Cols IN] is only permitted in relationships that navigate a secondary table. In a definition such as “alter S add C reflects (TF) in MM (SF)”, MM will be a secondary table implementing a many-many relationship between table S and some other table T, containing (at least) foreign keys SF and F where TF references the target table T and SF references the source table S. Then the values of C will be lists of rows of T.
The final [Cols] syntax allows selection of a foreign key that references the source table, and can be used in any REFLECTS construct.
Interestingly the Java Persistence Query Language provides query language support for such two-way relationships, but required a lot of annotation infrastructure.
For example (inspired by the Java Tutorial), suppose we have tables
player: (pid int primary key, pname char)
team: (tid int primary key, tname char, capt int references player)
playerteam: (pid references player, tid references team)
This club has a number of teams for different purposes, and players can belong to (or even captain) more than one team. Then the syntax proposed here would allow us to write definitions as simple as
alter table player add captains reflects team
alter table player add playsin reflects playerteam
Then in the REST interface we can write
http://myclub.org/db/db/player(pname=’Fred Bloggs’)/captains/tname
to obtain the names of all the teams Fred captains.
The syntax is one of the alternatives for ColumnDefinition, and defines a generated-always column:
| id REFLECTS [Cols IN] Table_id [Cols]
Recall that ColumnDefinition is also used to define a property in a role-based data model, and of course in that context entity and property identifiers can be used in place of table and column identifiers.
The REFLECTS definition sets up an array-valued generated column as a reverse reference: e.g. if t.f references g(i) then “alter g add h reflects t” is a shorthand for “alter g add h row t array generated always as (select * from t where f=i)”. That is, each cell in the generated column contains a list of rows from the referenced table.
The optional [Cols] following the referenced Table_id allows selection of a particular foreign key if there is more than one.
For many-many relationships the referenced table will be a secondary table, so that the type of the generated column is defined only implicitly. Still the basic idea is simple enough. Suppose we have a many-many relationship R between tables A and B. In a relational database this will be implemented by means of a secondary table AB consisting of pairs of primary keys for A and B. In both A and B we can easily define additional generated columns for our convenience (e.g. for REST navigation):
alter table A add X reflects AB
alter table B add Y reflects AB
Then in each row of A, X will be a list of rows from table B; and in each row of B, Y will be a list of rows from table A. In practice we would probably name these properties B and A rather than X and Y to make the resulting REST-based navigation more intuitive.
Again, the optional column specification [Cols IN] allows selection of a particular foreign key in the secondary table. In the above player and team example we could define
alter table player add captains reflects team(capt)
alter table team add players reflects (pid) in playerteam(tid)
The full details of the [Cols IN] option are as follows. [Cols IN] is only permitted in relationships that navigate a secondary table. In a definition such as “alter S add C reflects (TF) in MM (SF)”, MM will be a secondary table implementing a many-many relationship between table S and some other table T, containing (at least) foreign keys SF and F where TF references the target table T and SF references the source table S. Then the values of C will be lists of rows of T.
The final [Cols] syntax allows selection of a foreign key that references the source table, and can be used in any REFLECTS construct.
Wednesday, 6 July 2011
On concurrency tokens
Microsoft states for .NET Framework 4: "WCF Data Services supports the optimistic concurrency model by enabling you to define a concurrency token for an entity. This concurrency token, which includes one or more properties of the entity, is used by the data service to determine whether a change has occurred in the data that is being requested, updated, or deleted."
Now this is interesting as it suggests a growing realisation that more needs to be done about optimistic concurrency. But alas, all that is done here is to declare a property as useful for applications to test: it comes with no guarantees.
Pyrrho already has (mandatory) optimistic concurrency control, but still, it is an attractive notion to have a test to perform during a transaction ("is it already too late to commit?").
So from the next version (4.5) of Pyrrho, there will be a boolean property of transactions. Requesting the value of Transaction.Conflict will cause a server round trip to discover if other transaction commits have already made committing this transaction impossible.
Whatever the result of this test, the Transaction will remain open as it probably still contains useful information, until Commit or Rollback is called.
Now this is interesting as it suggests a growing realisation that more needs to be done about optimistic concurrency. But alas, all that is done here is to declare a property as useful for applications to test: it comes with no guarantees.
Pyrrho already has (mandatory) optimistic concurrency control, but still, it is an attractive notion to have a test to perform during a transaction ("is it already too late to commit?").
So from the next version (4.5) of Pyrrho, there will be a boolean property of transactions. Requesting the value of Transaction.Conflict will cause a server round trip to discover if other transaction commits have already made committing this transaction impossible.
Whatever the result of this test, the Transaction will remain open as it probably still contains useful information, until Commit or Rollback is called.
Tuesday, 5 July 2011
Role-based Data Models
It does appear that Entity Frameworks loom ever larger in the data access world, whether they are used in a code-first or model-first way. But any database can support several data models.
It has been a common occurrence that Java programmers will use different data models (as defined by annotations in their program) to access the same database. This is because annotations can provide different entity and property names and map their relationships in different ways. Pyrrho (up to version 4.4) therefore regarded the data model in use as a thread-specific structure. This now seems quite the wrong thing to do.
Instead, Pyrrho now takes its cue from the business analytics and business modelling field, and considers that it will be best practice for each role to have a different view of the data in the database, and it is at such a conceptual model level that entity modelling should take place.
So, from version 4.5 of Pyrrho, a data model will be regarded as a property of a Role. The physical database defines a default data model, and an associated default Role. This default Role treats each base table with a primary key as an entity, and each column of such a table as a property, and allows navigation using foreign keys. Multi-column keys are handled as lists whose property name is the reserved word KEY.
[Updated 2 August: Users with admin access to a role can create new database objects for their role. Granting access to database objects for a Role copies the necessary entitiy and property definitions into the Role's data model. The data model that is applied is determined by the current Role: this is initially set in the connection but can be changed during the session to any other role that the user has been granted.]
The data model can be modified for a Role by hiding entity types or properties, or defining entity types and properties by renaming inherited entities and properties tables or views and their columns. Descriptive text can be associated with the entity and property definitions. An additional syntax for ALTER ROLE has been created for providing this facility.
This allows data models to have more user-friendly names and allows Roles to be localised for different languages. Pyrrho 4.5 enhances generated columns by introducing updatable generated columns in addition to updatable views so additional derived columns can be added to the database schema where required for making data models more natural. It is a feature of Pyrrho that such derived columns are only computed when required, so there is little overhead in using them.
[Added 2 August: The SQL contained within e.g. stored procedures executes under definer's rights. Although there is only one copy of the SQL in the database, a developer viewing the SQL will see a version adapted to their current data model. The SQL will execute with definer's rights, although the code will reflect the user's data model. The resulting dependencies between roles and their data models are tracked by Pyrrho, and the usual restrict/cascade protocols apply to changes to the data model schema.]
The data model can be retrieved from the database is the usual way in EDMX format, and will determine the URL mapping used in Pyrrho's new secure REST service. SQL will of course continue to be applied at the level of base tables. Pyrrho's implementation of LINQ and Java Persistence will also use the new role-based data models: for these APIs the change will only be noticed if a Role is specified in the connection string.
In Pyrrho, strong types, including types with embedded metadata, will continue to be supported in the physical database. The new derived column machinery can present the same physical data as different types as appropriate for role-based data models.
Over the summer I hope to publish more articles on the version 4.5 goodies, including some better support for role base security.
It has been a common occurrence that Java programmers will use different data models (as defined by annotations in their program) to access the same database. This is because annotations can provide different entity and property names and map their relationships in different ways. Pyrrho (up to version 4.4) therefore regarded the data model in use as a thread-specific structure. This now seems quite the wrong thing to do.
Instead, Pyrrho now takes its cue from the business analytics and business modelling field, and considers that it will be best practice for each role to have a different view of the data in the database, and it is at such a conceptual model level that entity modelling should take place.
So, from version 4.5 of Pyrrho, a data model will be regarded as a property of a Role. The physical database defines a default data model, and an associated default Role. This default Role treats each base table with a primary key as an entity, and each column of such a table as a property, and allows navigation using foreign keys. Multi-column keys are handled as lists whose property name is the reserved word KEY.
[Updated 2 August: Users with admin access to a role can create new database objects for their role. Granting access to database objects for a Role copies the necessary entitiy and property definitions into the Role's data model. The data model that is applied is determined by the current Role: this is initially set in the connection but can be changed during the session to any other role that the user has been granted.]
The data model can be modified for a Role by hiding entity types or properties, or defining entity types and properties by renaming inherited entities and properties tables or views and their columns. Descriptive text can be associated with the entity and property definitions. An additional syntax for ALTER ROLE has been created for providing this facility.
This allows data models to have more user-friendly names and allows Roles to be localised for different languages. Pyrrho 4.5 enhances generated columns by introducing updatable generated columns in addition to updatable views so additional derived columns can be added to the database schema where required for making data models more natural. It is a feature of Pyrrho that such derived columns are only computed when required, so there is little overhead in using them.
[Added 2 August: The SQL contained within e.g. stored procedures executes under definer's rights. Although there is only one copy of the SQL in the database, a developer viewing the SQL will see a version adapted to their current data model. The SQL will execute with definer's rights, although the code will reflect the user's data model. The resulting dependencies between roles and their data models are tracked by Pyrrho, and the usual restrict/cascade protocols apply to changes to the data model schema.]
The data model can be retrieved from the database is the usual way in EDMX format, and will determine the URL mapping used in Pyrrho's new secure REST service. SQL will of course continue to be applied at the level of base tables. Pyrrho's implementation of LINQ and Java Persistence will also use the new role-based data models: for these APIs the change will only be noticed if a Role is specified in the connection string.
In Pyrrho, strong types, including types with embedded metadata, will continue to be supported in the physical database. The new derived column machinery can present the same physical data as different types as appropriate for role-based data models.
Over the summer I hope to publish more articles on the version 4.5 goodies, including some better support for role base security.
Saturday, 5 March 2011
A Database for the Windows Phone
The purpose of this demonstration is to introduce the embedded Pyrrho engine that is now available for the Windows Phone 7. This uses Web local storage on the client device to hold any databases used.
The demo application is a simple persistent shopping list, and for simplicity only addition and deletion of items is supported. The application has a reference to PhoneOSP.dll. This document contains step-by-step instructions to build this complete application. You will need Windows Phone developer tools, which you can get from Microsoft
and the PhoneOSP.dll from the Pyrrho distribution.
The new project
1. Open Visual Studio 2010, File>New>Project.. In the New Project dialogue box, find the Silverlight for Windows Phone entry under Visual C#, ensure that .NET Framework 4 is selected, and select Windows Phone Application. Set the name of the application to ShoppingList, and the Location to somewhere you control, such as C:\Temp. Select OK.
The Xaml Markup
2. Position the mouse cursor just after “LayoutRoot”, and type a space and H. From the Intellisense dropdown, double-click HorizontalAlignment and then Left. Similarly add VerticalAlignment=”Top”.
3. Position the mouse cursor at the start of the blank line between <Grid ..> and </Grid> . Type an opening pointy bracket < and from the IntelliSense dropdown, double-click StackPanel. Type a closing pointy bracket >.
4. The cursor is now between <StackPanel> and <StackPanel> . View>ToolBox, and double-click ListBox. Change the Height attribute of the ListBox to 350 and the Width to 400.
5. Place the cursor just before </StackPanel> again. Type <S and from the IntelliSense dropdown, double-click StackPanel and type > .
6. Place the cursor just before the closing pointy bracket of the new <StackPanel> element. Type a space followed by O. Double-click Orientation, and then Horizontal.
7. Move the cursor to just before the first </StackPanel>, and double-click TextBox from the Toolbox. Set the width property to 240.
8. Now double-click Button in the Toolbox . In the Properties window, change the name of the Button to bAdd, set the Content property to Add, and press the Enter key.
9. Move the mouse cursor to just before the second </StackPanel> and select Button from the ToolBox. Change the name of the button to bDEl and the Content to Delete Selected. Change the Width to 100. Now select Debug (the green arrow in the toolbar). The application should display as shown in a browser. Close the browser window.
The Shopping class
10. If you want to know how Pyrrho works, you can add the source code for Pyrrho by right-clicking the Solution, and Add PhoneOSP as an Existing Project…., and browse in to PhoneOSP\PhoneOSP to open PhoneOSP.csproj.
11. Right-click on References in the ShoppingList Project, select AddReference.. If you have just done step 10, use the Projects tab to add PhoneOSP. If not, Browse to add PhoneOSP.dll.
12. In the Solution Explorer, right-click on the ShoppingList project and select Add> Class. In the Add New Item – ShoppingList , given the name as Shopping.cs, and select Add. The Shopping.cs file opens in the designer.
13. Change the Shopping.cs file to contain the following:
Adding code to add and delete items
14. In the MainPage.xaml markup, place the mouse cursor in the Grid element. In the Properties window, select the Events tab, and double-click the Loaded event. The MainPage.xaml.cs file opens and you should add the following lines to the LayoutRoot_Loaded event handler:
16. In the MainPage.xaml designer, double-click the Add button. The MainPage.xaml.cs file opens in the designer window. Insert the following code in the the bAdd_Click method (between the curly brackets):
The demo application is a simple persistent shopping list, and for simplicity only addition and deletion of items is supported. The application has a reference to PhoneOSP.dll. This document contains step-by-step instructions to build this complete application. You will need Windows Phone developer tools, which you can get from Microsoft
and the PhoneOSP.dll from the Pyrrho distribution.
The new project
1. Open Visual Studio 2010, File>New>Project.. In the New Project dialogue box, find the Silverlight for Windows Phone entry under Visual C#, ensure that .NET Framework 4 is selected, and select Windows Phone Application. Set the name of the application to ShoppingList, and the Location to somewhere you control, such as C:\Temp. Select OK.
The Xaml Markup
2. Position the mouse cursor just after “LayoutRoot”, and type a space and H. From the Intellisense dropdown, double-click HorizontalAlignment and then Left. Similarly add VerticalAlignment=”Top”.
3. Position the mouse cursor at the start of the blank line between <Grid ..> and </Grid> . Type an opening pointy bracket < and from the IntelliSense dropdown, double-click StackPanel. Type a closing pointy bracket >.
4. The cursor is now between <StackPanel> and <StackPanel> . View>ToolBox, and double-click ListBox. Change the Height attribute of the ListBox to 350 and the Width to 400.
5. Place the cursor just before </StackPanel> again. Type <S and from the IntelliSense dropdown, double-click StackPanel and type > .
6. Place the cursor just before the closing pointy bracket of the new <StackPanel> element. Type a space followed by O. Double-click Orientation, and then Horizontal.
7. Move the cursor to just before the first </StackPanel>, and double-click TextBox from the Toolbox. Set the width property to 240.
8. Now double-click Button in the Toolbox . In the Properties window, change the name of the Button to bAdd, set the Content property to Add, and press the Enter key.
9. Move the mouse cursor to just before the second </StackPanel> and select Button from the ToolBox. Change the name of the button to bDEl and the Content to Delete Selected. Change the Width to 100. Now select Debug (the green arrow in the toolbar). The application should display as shown in a browser. Close the browser window.
The Shopping class
10. If you want to know how Pyrrho works, you can add the source code for Pyrrho by right-clicking the Solution, and Add PhoneOSP as an Existing Project…., and browse in to PhoneOSP\PhoneOSP to open PhoneOSP.csproj.
11. Right-click on References in the ShoppingList Project, select AddReference.. If you have just done step 10, use the Projects tab to add PhoneOSP. If not, Browse to add PhoneOSP.dll.
12. In the Solution Explorer, right-click on the ShoppingList project and select Add> Class. In the Add New Item – ShoppingList , given the name as Shopping.cs, and select Add. The Shopping.cs file opens in the designer.
13. Change the Shopping.cs file to contain the following:
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Collections.ObjectModel;
using Pyrrho;
namespace WindowsPhoneShopping
{
public class Shopping
{
public PyrrhoConnect conn = null;
MainPage main = null;
public Shopping(MainPage page)
{
main = page;
FillData();
}
void FillData()
{
try
{
conn = new PyrrhoConnect("Files=Shopping");
conn.Open();
PyrrhoCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from Items";
PyrrhoReader rdr = null;
try
{
rdr = cmd.ExecuteReader();
}
catch (Exception)
{
rdr = null;
}
if (rdr == null)
{
conn.Act("create table Items(Name char primary key)");
}
else
{
while (rdr.Read())
{
main.listBox1.Items.Add(rdr.GetString(0));
}
rdr.Close();
}
}
catch (Exception) { }
}
public void Refresh()
{
main.listBox1.Items.Clear();
FillData();
}
}
}
Check this still runs as before. Close the browser window.Adding code to add and delete items
14. In the MainPage.xaml markup, place the mouse cursor in the Grid element. In the Properties window, select the Events tab, and double-click the Loaded event. The MainPage.xaml.cs file opens and you should add the following lines to the LayoutRoot_Loaded event handler:
shopping = new Shopping(this);
textBox1.Text = "";
15. Still in the MainPage.xaml.cs file, position the mouse cursor just inside the first curly bracket, and enterShopping shopping = null;
16. In the MainPage.xaml designer, double-click the Add button. The MainPage.xaml.cs file opens in the designer window. Insert the following code in the the bAdd_Click method (between the curly brackets):
try
{
shopping.conn.Act("insert into Items values('" + textBox1.Text + "')");
shopping.Refresh();
textBox1.Text = "";
}
catch (Exception) { }
17. In the MainPage.xaml designer, double-click the Delete Selected button. When the MainPage.xaml.cs file opens again, insert the following code in the bDel_Click method:try
{
var it = listBox1.SelectedItem as string;
if (it != null)
shopping.conn.Act("delete from Items where name='" + it + "'");
shopping.Refresh();
}
catch (Exception) { }
18. The application should now run.
Friday, 4 March 2011
A Silverlight OSP Tutorial
The purpose of this demonstration is to introduce the embedded Pyrrho engine that is now available for Silverlight 4. This uses Web local storage on the client machine to hold any databases used.
The demo application is a simple persistent shopping list, and for simplicity only addition and deletion of items is supported. The application has a reference to SilverlightOSP. This document contains step-by-step instructions to build this complete application. You will need the Silverlight 4 SDK and Tools for Visual Studio 2010, which you can get from www.silverlight.net, and the SilverlightOSP.dll from the Pyrrho distribution.
The new project
1. Open Visual Studio 2010, File>New>Project.. In the New Project dialogue box, find the Silverlight entry under Visual C#, ensure that .NET Framework 4 is selected, and select Silverlight Application. Set the name of the application to ShoppingList, and the Location to somewhere you control, such as C:\Temp. Select OK.
2. In the New Silverlight Application dialogue box, accept the defaults and click OK. The designer for MainPage.xaml opens.
The Xaml Markup
3. Position the mouse cursor just after "LayoutRoot", and type a space and H. From the Intellisense dropdown, double-click HorizontalAlignment and then Left. Similarly add VerticalAlignment="Top".
4. Position the mouse cursor at the start of the blank line between <Grid ..> and </Grid> .
Type an opening pointy bracket < and from the IntelliSense dropdown,
double-click StackPanel. Type a closing pointy
bracket >.
5. The cursor is now between <StackPanel> and <StackPanel> . View>ToolBox, and double-click DataGrid. Change the Height attribute of the DataGrid to 150 and the Width to 200.
6. Place the cursor just before </StackPanel> again. Type <S and from the IntelliSense dropdown, double-click StackPanel and type > .
7. Place the cursor just before the closing pointy bracket of the new <StackPanel> element. Type a space followed by O. Double-click Orientation, and then Horizontal.
8. Move the cursor to just before the first </StackPanel>, and double-click TextBox
from the Toolbox.
9. Now double-click Button in the Toolbox . In the Properties window, change the name of the Button to bAdd, set the Content property to Add, and press the Enter key.
10. Move the mouse cursor to just before the second </StackPanel> and select Button from the ToolBox. Change the name of the button to bDEl and the Content to Delete Selected. Change the Width to 100. Now select Debug (the green arrow in the toolbar). The application should display as shown in a browser. Close the browser window.
You can see the MainPage.xaml markup: here
The Shopping class
11. If you want to know how Pyrrho works, you can add the source code for Pyrrho by right-clicking the Solution, and Add SilverlightOSP as an Existing Project…., and browse in to SilverlistOSP\SilverlightOSP to open SilverlightOSP.csproj.
12. Right-click on References in the ShoppingList Project, select AddReference.. If you have just done step 11, use the Projects tab to add SilverlightOSP. If not, Browse to add SilverlightOSP.dll.
13. In the Solution Explorer, right-click on the ShoppingList project and select Add> Class. In the Add New Item – ShoppingList , given the name as Shopping.cs, and select Add. The Shopping.cs file opens in the designer.
14. Change the Shopping.cs file to contain the following:
16. Return to the MainPage.xaml designer by double-clicking its tab. Place the mouse cursor just after mc:ignorable=”d” and enter xmlns:my="clr-namespace:ShoppingList"
17. Place the mouse cursor just before <Grid and paste the following code:
<UserControl.Resources>
<my:ShoppingManager x:Key="shoppingListViewSource" />
</UserControl.Resources>
18. Place the mouse cursor after “LayoutRoot” and paste the following:
DataContext="{Binding Path=ItemsRequired, Source={StaticResource shoppingListViewSource}}"
19. In the DataGrid markup, change the value of AutoGenerateColumns to True.
20. Place the mouse cursor just after "True" and paste the following:
HeadersVisibility="Column" ItemsSource ="{Binding}"
21. Notice that the DataGrid already shows the Name column header. Run the application. Close the browser window.
Adding code to add and delete items
22. In the MainPage.xaml designer, double-click the Add button. The MainPage.xaml.cs file opens in the designer window. Insert the following code in the the bAdd_Click method (between the curly brackets):
try
{
Shopping.conn.Act("insert into Items values('" + textBox1.Text + "')");
Shopping.theList.Refresh();
textBox1.Text = "";
}
catch (Exception) { }
23. In the MainPage.xaml designer, double-click the Delete Selected button. When the MainPage.xaml.cs file opens again, insert the following code in the bDel_Click method:
try
{
var it = dataGrid1.SelectedItem as ItemToBuy;
if (it != null)
Shopping.conn.Act("delete from Items where name='" + it.Name + "'");
Shopping.theList.Refresh();
}
catch (Exception) { }
24. Now test out the application. You should be able to add and remove items using the buttons. (To add an item, type its name in the textbox and click Add.)
The demo application is a simple persistent shopping list, and for simplicity only addition and deletion of items is supported. The application has a reference to SilverlightOSP. This document contains step-by-step instructions to build this complete application. You will need the Silverlight 4 SDK and Tools for Visual Studio 2010, which you can get from www.silverlight.net, and the SilverlightOSP.dll from the Pyrrho distribution.
1. Open Visual Studio 2010, File>New>Project.. In the New Project dialogue box, find the Silverlight entry under Visual C#, ensure that .NET Framework 4 is selected, and select Silverlight Application. Set the name of the application to ShoppingList, and the Location to somewhere you control, such as C:\Temp. Select OK.
The Xaml Markup
3. Position the mouse cursor just after "LayoutRoot", and type a space and H. From the Intellisense dropdown, double-click HorizontalAlignment and then Left. Similarly add VerticalAlignment="Top".
4. Position the mouse cursor at the start of the blank line between <Grid ..> and </Grid> .
Type an opening pointy bracket < and from the IntelliSense dropdown,
double-click StackPanel. Type a closing pointy
bracket >.
5. The cursor is now between <StackPanel> and <StackPanel> . View>ToolBox, and double-click DataGrid. Change the Height attribute of the DataGrid to 150 and the Width to 200.
6. Place the cursor just before </StackPanel> again. Type <S and from the IntelliSense dropdown, double-click StackPanel and type > .
7. Place the cursor just before the closing pointy bracket of the new <StackPanel> element. Type a space followed by O. Double-click Orientation, and then Horizontal.
8. Move the cursor to just before the first </StackPanel>, and double-click TextBox
from the Toolbox.
9. Now double-click Button in the Toolbox . In the Properties window, change the name of the Button to bAdd, set the Content property to Add, and press the Enter key.
10. Move the mouse cursor to just before the second </StackPanel> and select Button from the ToolBox. Change the name of the button to bDEl and the Content to Delete Selected. Change the Width to 100. Now select Debug (the green arrow in the toolbar). The application should display as shown in a browser. Close the browser window.
You can see the MainPage.xaml markup: here
The Shopping class
11. If you want to know how Pyrrho works, you can add the source code for Pyrrho by right-clicking the Solution, and Add SilverlightOSP as an Existing Project…., and browse in to SilverlistOSP\SilverlightOSP to open SilverlightOSP.csproj.
12. Right-click on References in the ShoppingList Project, select AddReference.. If you have just done step 11, use the Projects tab to add SilverlightOSP. If not, Browse to add SilverlightOSP.dll.
13. In the Solution Explorer, right-click on the ShoppingList project and select Add> Class. In the Add New Item – ShoppingList , given the name as Shopping.cs, and select Add. The Shopping.cs file opens in the designer.
14. Change the Shopping.cs file to contain the following:
using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Collections.ObjectModel;
using Pyrrho;
namespace ShoppingList
{
public class Shopping
{
public ObservableCollection ItemsRequired { get; set; }
public static PyrrhoConnect conn = null;
public static Shopping theList = null;
public Shopping()
{
ItemsRequired = new ObservableCollection();
theList = this;
FillData();
}
void FillData()
{
try
{
conn = new PyrrhoConnect("Files=Shopping");
conn.Open();
PyrrhoCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from Items";
PyrrhoReader rdr = null;
try
{
rdr = cmd.ExecuteReader();
}
catch (Exception)
{
rdr = null;
}
if (rdr == null)
{
conn.Act("create table Items(Name char primary key)");
}
else
{
while (rdr.Read())
{
ItemsRequired.Add(new ItemToBuy()
{
Name = rdr.GetString(0)
});
}
rdr.Close();
}
}
catch (Exception) { }
}
public void Refresh()
{
ItemsRequired.Clear();
FillData();
}
}
public class ItemToBuy
{
string name;
public string Name { get; set; }
}
}
15. Check this still runs as before. Close the browser window.16. Return to the MainPage.xaml designer by double-clicking its tab. Place the mouse cursor just after mc:ignorable=”d” and enter xmlns:my="clr-namespace:ShoppingList"
17. Place the mouse cursor just before <Grid and paste the following code:
<UserControl.Resources>
<my:ShoppingManager x:Key="shoppingListViewSource" />
</UserControl.Resources>
18. Place the mouse cursor after “LayoutRoot” and paste the following:
DataContext="{Binding Path=ItemsRequired, Source={StaticResource shoppingListViewSource}}"
19. In the DataGrid markup, change the value of AutoGenerateColumns to True.
20. Place the mouse cursor just after "True" and paste the following:
HeadersVisibility="Column" ItemsSource ="{Binding}"
21. Notice that the DataGrid already shows the Name column header. Run the application. Close the browser window.
Adding code to add and delete items
22. In the MainPage.xaml designer, double-click the Add button. The MainPage.xaml.cs file opens in the designer window. Insert the following code in the the bAdd_Click method (between the curly brackets):
try
{
Shopping.conn.Act("insert into Items values('" + textBox1.Text + "')");
Shopping.theList.Refresh();
textBox1.Text = "";
}
catch (Exception) { }
23. In the MainPage.xaml designer, double-click the Delete Selected button. When the MainPage.xaml.cs file opens again, insert the following code in the bDel_Click method:
try
{
var it = dataGrid1.SelectedItem as ItemToBuy;
if (it != null)
Shopping.conn.Act("delete from Items where name='" + it.Name + "'");
Shopping.theList.Refresh();
}
catch (Exception) { }
24. Now test out the application. You should be able to add and remove items using the buttons. (To add an item, type its name in the textbox and click Add.)
Tuesday, 1 March 2011
Silverlight and OWL2
Version 4.4 of PyrrhoDBMS is released today, with support for OWL 2 subtypes and Silverlight applications.
I am particularly excited by the Silverlight version. This is a dll that is included in your Silverlight project, and via inclusion in the .xap file, runs on the client machine, using secure local storage in the client machine for the database(s) used by your application. I really feel this brings a whole new perspective on persistence, since the application can organise the data flexibly using SQL. Full SQL is available (apart from the xmlquery primitive) in a compact engine (600KB !).
Okay, I've removed a few previous technologies in this edition. Many people thought I was mad to include SPARQL in a DBMS, and this has now been removed, as have the built-in HTTP services, and the datamodel machinery.
I will publish some samples for both of the above innovations on the PyrrhoDB web site in the next few days. So much has changed that there are bound to be some bugs. For the moment the download pages also provide previous versions of the PyrrhoDBMS. Please let me know if anything strange happens for you.
UPDATE 2 March: The 1 March versions had a serious bug affecting Alter table add, with database corruption. The 2 March versions fix this problem.
I am particularly excited by the Silverlight version. This is a dll that is included in your Silverlight project, and via inclusion in the .xap file, runs on the client machine, using secure local storage in the client machine for the database(s) used by your application. I really feel this brings a whole new perspective on persistence, since the application can organise the data flexibly using SQL. Full SQL is available (apart from the xmlquery primitive) in a compact engine (600KB !).
Okay, I've removed a few previous technologies in this edition. Many people thought I was mad to include SPARQL in a DBMS, and this has now been removed, as have the built-in HTTP services, and the datamodel machinery.
I will publish some samples for both of the above innovations on the PyrrhoDB web site in the next few days. So much has changed that there are bound to be some bugs. For the moment the download pages also provide previous versions of the PyrrhoDBMS. Please let me know if anything strange happens for you.
UPDATE 2 March: The 1 March versions had a serious bug affecting Alter table add, with database corruption. The 2 March versions fix this problem.
Subscribe to:
Posts (Atom)
