Creating an App with Google App Engine and PHP: Tutorial 2; Google SQL Cloud

Tags

app programming, google app engine, mobile development, php, sql

Kind Reader,

It has taken me far too long to produce what is a second and even more brief overview of the functionality of GAE; particularly in this case the Google Cloud SQL and connection to it. Though I hope it is useful, I apologise for it not being anywhere near the quality of my previous tutorial, and I shall no doubt revise it as and when I have time.

_________________________________________________________________

Contents
1.1: Requirements/Resources

1.2: Setting up the Database
– 1.2.1: Setting up the Cloud SQL Access Point
– 1.2.2: Creating the Database
– 1.2.3: Getting Data from the Cloud

1.3: Final Notes
– 1.5.1: Program Overview
– 1.5.3: Good luck and Have Fun!

_________________________________________________________________

1.1: Requirements/Resources

(If you have followed the previous tutorial you’ll know this bit.)This app is being developed in Google App Engine 1.8.5, available here. It requires Python to be installed on your computer, at this time version 2.7 seems to be the most stable with the app engine so that’s what I’m using. If you don’t have it, it’s around about here.

1.2: Setting up the Database
1.2.1: Setting up the Cloud SQL Access Point

First, go to https://cloud.google.com/console and log in, this is the Google Cloud console and from there you can set up an SQL project to connect your app to.

Click the red button near the top that says Create Project and enter a project name and ID to create your app’s very own space on the Google Cloud.

On the left hand side there’s a menu, the third option from the bottom is called Cloud SQL, click it to set up the SQL Instance – basically the connection settings for using SQL from your app.

Now click the red button at the top labelled New Instance.

Here you create the instance – basically rent space on the Google Cloud – useful, but sadly not free. Fill in each of the boxes; enter an Instance ID, Region (this is the physical location of the Google servers you want to use, not your location), then select billing options and add any authorization specifics.

At the bottom make sure that your Google app engine project is listed under Authorized App Engine Applications.

1.2.2: Creating the Database

Now go into the menu option on the left called Cloud Datastore and click the red button at the top called Create Entity. Give it a ‘Kind’, which basically seems to be the database name(?). Then you have a drop down called Key, and you can either have this as ID, in which case its name will be a randomly assigned number, or you can change that to Name, and give the Key a name yourself. Keys, as far as I can tell, are much like tables, for the purposes of my database I will make a table called Players, who will have a name and a score.

You add the fields of the table, in my case name and score, at the bottom of the form where there is a blank text box followed by ‘a string’ or whatever, then indexed or not by blank. Not the best way to lay it out, perhaps.

Anyway, the first blank box to the left of ‘a string’ is the name of the field; my first one is called name. You can then change the ‘a string’ drop down to whatever is the applicable variable type – in my case I keep it as ‘a string’ since it is just text. If you want to index your data you can. To add more fields, simply press the plus symbol at the end of each field.

Sometimes there seems to be a problem saving fields, if this happens then try changing the variable type. Once you’re done, just press the blue Create Entity button at the bottom of the form.

1.2.3: Getting Data from the Cloud

You can connect to your database using PDO, MySQL, and MySQLi; being most comfortable with MySQL, I’m going to quickly explain how to implement the connection in your app in that language, but Google explain all of the options fairly well here.

Go into your PHP app file, if you’re following on from my last tutorial then in the main.php that we created earlier, somewhere in <?php ?> tags add SQL code similar to mine that selects whatever SQL Instance/Database fields you’ve set.

$db_connect = mysql_connect(":/cloudsql/mikesbrandnewapp:sqlinstance", "<google username>", "<google password>");
if (!$db_connect) {
    die('Ah shoot, this happened: ' . mysql_error());
}

Now you’re connected to the database, you can query and search for your fields or upload data. I should note that I have not played around much with this code, and for further Cloud SQL help this tutorial looks very helpful. Since Google Cloud PHP/SQL isn’t particularly well documented at the moment, it may not be the best option for major projects; I plan to do a Google Datastore tutorial soon.

1.3: Final Notes
1.3.1: Program Overview

While GAE is a very sensible option for app development, especially apps that interact with the web, it still has a way to go as far as documentation is concerned, and that is the major weakness of it since if you have a problem there isn’t a great deal of expert knowledge widely available. That’s part of the reason why this tutorial has taken so long to put together and why it is much briefer than I’d like. (Possibly Appcelerator, Android Studio, and PhoneGap tutorials to come – I make no promises though with a dissertation to finish! Also, Unity app development is a fairly nice experience too.)

1.3.2: Good Luck and Have Fun

Good luck and have fun.

A History of Aberystwyth Castle

Kind Reader,

Since I am currently considering researching a short history of Aberystwyth’s long lost ancient townhouse of the Pryse family, their home before Castle House, I thought in the mean time a small history of Aberystwyth Castle itself might be interesting.

The castle at Aberystwyth is the fourth to be built within the vicinity of the town. The first was by Gilbert Fitz Richard, a Norman nobleman, in 1109 near where the river Ystwyth then opened out into the sea, it was extensive enough to have motte and bailey earthworks put up and a chapel built within the walls. (This was, arguably, the first ‘Aberystwyth’ church.) This first castle was besieged and soon after burnt down by Cadwalader, Gruffudd ap Rhys, and Owen Gwynedd in 1135. Cadwalader built the second castle, possibly on the same site, soon after. However, after problems between Cadwalader and his fellow Welshman, Owen Gwynedd, Gwynedd returned to Aberystwyth and destroyed the second castle as well.

A third castle was built by Gilbert Fitz Richard’s grandson, Roger Fitz Richard, in 1158 at an unknown site, but survived less than a decade and was destroyed by Welsh forces c1165. There are some suggestions that a fortified position continued after the castle’s destruction, but with little evidence, and it is likely that there was no proper fortified construction again in Aberystwyth until the current castle was begun 112 years later.

In 1277, while Edward I was busy conquering Wales, his brother, Edmund of Lancaster, was sent to Ceredigion to quell any chance of an uprising and construct a new castle to stand against any problems in the future. It was Edmund who must have visited Llanbadarn some time that year and decided to build his castle nearby, choosing a secure site on the cliffs with an ability to both keep the coast defended from raiders, and to be replenished by ships in times of siege.

The actual command of the construction came to a man called James of Saint George, who had already designed and overseen the construction of two other castles built for Edward I. In 1282 construction was briefly halted by a Welsh attack on the castle, who attempted to hold it for themselves until they were quickly routed by English forces. Some suppose that James only commanded the construction of the castle after this attack, but it is probable that he was in control of the project for the entire building process, since he was such an important castle builder in the area.

The present structure was finally completed in 1289, and quickly proved its strength after holding out through a siege in the winter between 1294 and 1295. Shortly afterwards Edward I granted permission for the small settlement outside of the castle to be walled around to protect it from Welsh attacks, at that time it was called ‘Ville de Llanpadarn’, but this was the very first seeds of Aberystwyth as we know it today.

Originally there was a chapel in the castle, probably on the first floor of the great tower by the great hall, but a small medieval church was built next to the castle probably at the very end of the 1200s.

We get some idea of what the castle contained from the numerous repairs that the castle constantly needed due to its close proximity to the sea. In 1342, when it belonged to the Black Prince, it is recorded as having a Long Chamber, King’s Hall, Old Hall, a kitchen, bakehouse, stable, and two granaries. It had two draw bridges; one from the great gate to the town and one over to the small partial-island that the war memorial now stands on. Also, interestingly, it mentions a ‘third bailey’ that had, even at that time, almost been completely destroyed by the sea, this could suggest that once in the area where the war memorial stands there was a castled enclosure, now entirely lost.

In 1450 there is recorded a Knights’ Hall, a Somerhall, and a chapel. While in 1466 a new bridge was built somewhere within the castle complex.

The stable and bakehouse are recorded as under one roof against the wall on the west side of the castle, and adjacent to this was a guard’s chamber with the King’s Hall above. The great hall itself, to the south of the Great Towers, was at some point divided in half and then had an oven added at the south end by the garde-robe chute. I did at one point have a very useful source that explained the location of the chapel, but I have sadly misplaced its name so until then that’s the best description I can offer.

It was besieged in 1404 by Owain Glyndŵr, who was building a power base at Machynlleth where he had that same year crowned himself Prince of Wales. His dream of an independent Welsh state did not succeed, however, and in 1408 Aberystwyth was recaptured by Henry IV.

The castle quickly lost its importance once Wales was pacified and its extraordinary repair costs due to the sea no longer seemed worth paying for. Only the most necessary repairs were made to the castle as it became a simple centre of administration for the small town around it.

As a centre of mining, Aberystwyth remained a great source of revenue for the crown, and a surprisingly vast fortune in coins minted every year in London from silver mined in the Welsh hills of Ceredigion. This was the case when Thomas Bushell took over the lease for mining rights in 1637 and, after getting the attention of Charles I, on the 9th of July that year he was granted the right to erect a mint at the castle. There is a doorway on the southern wall of the Great Gate-corridor, between the two Great Towers, that dates from about that time and very possibly had something to do with the construction of the mint.

It didn’t take long for Bushell to set up a small mint possibly in the small complex of rooms on the north-west corner of the Great Towers, with storage supposedly above. (I must admit, I recall this from memory from a text I read about a year ago now and cannot guarantee I remember it correctly.) It is possible that the ‘King’s Hall’ was converted for use as part of the mint. This ran ran until until September 1642, when the outbreak of civil war even stalled the silversmiths of Aberystwyth. In that time, just about five years, the mint had created £13,069 out of 4,052 lbs of silver. Some silver mined later during the civil war was taken instead to Bristol, since in 1645 Bushell was present in that city when the king requested he minted coins for the payment of the army.

In November 1645 the castle, at that time under command of Roger Whitley, was besieged by Parliamentarian forces who were probably being lead by John Vaughan. Sadly and unlike many other parliamentary attacks on castles, few records remain of the siege from either side of the battle. However, the mint was briefly re-opened in January 1646, when it ran until March before again being stopped as the siege became more intense. By April the castle had been captured; there is a heroic idea that John Vaughan himself triumphantly marched his men into the courtyard and oversaw the expulsion of the Royalists, but he may by this time have had second thoughts about his loyalties, and have in fact returned to his own estates and allowed another unknown commander to enter the castle in his stead.

Some mysterious cavaliers entered the castle and attempted to mint coins in February 1649, when £8 alone was minted, before the minting tools were quickly smuggled out of the castle. Shortly before the 23rd of February 1649 the tools for minting were hurriedly removed from the castle and returned to Royalists outside of Aberystwyth, before they could be found by Cromwell.

Perhaps it was discovered by Parliament that this had occurred, since not long after this the castle was entirely demolished by Cromwell’s order. The catastrophic destruction of the castle compared to others shows how important it was not just to destroy possible Royalist strong-points, but also to obliterate a royal mint that had funded the armies of the king. Enough dynamite was used, in fact, that the following winter the church collapsed in the storms. It is popular belief that the church fell into the sea, but, since the line of the town wall is still quite far from the sea it is impossible for the church to have fallen in unless it stood outside the walls, which is unlikely. It is more probable that it was either destroyed at the same time as the castle by the explosion, or the explosion weakened it so much that the winter storms just blew it down.

For a century afterwards the remains of the castle itself suffered at the hands of the sea as well as being taken down by townsfolk for use in other buildings. The inner bailey wall towards the present church shows the clearest signs of having been dismantled after the castle was slighted by Cromwell. This was until in 1739, when the local council ordered that no one should take any more stones from the castle on pain of a £5 fine.

Most suppose that the town wall, dismantled largely between 1650 and 1850, is now entirely gone without any trace, but there is in fact the stub of the town wall on the outside of the north-west external wall of the outer-bailey.

Some time at the end of the eighteenth century the castle was landscaped and the paths put in, the pair of signal cannons appearing a few decades later as decoration. There was for some time the camera obscura on the site of the war memorial, before the memorial was put there in the 20s. And that, in a nutshell, are the major events that have changed the castle and its buildings since it was built.

The Litany

Accustomed Reader,

May I humbly introduce a rather simple poem, written on my phone while travelling. I pray, kind reader, that you enjoy. (I am also posting this with my phone, so apologise for the formatting!)

I saw King Arthur yesternight;
Tall he stood amidst his train
Of opals, pearls, and jewels all bright,
The wealth of heroes past, long slain.
And here about this wealth-gilt garb;
Hems of ev’ry gemstone nam’d,
Rose the sword, the mace, the barb,
As came his swordsmen hist’ry fam’d.

A litany of heralds high
Claimed the summit of the sky;
Angelic crews of ten and ten
Sent to test the world of men,
And raise those souls that lie.

There came Old Cromwell to their cause,
Thick with blood he wish’d undone,
And his breth’ren in their scores
Of men who’s aims deeds had outrun.
Then Charles arose beside his prince,
The Bonny and Pretender too;
And all the kings that suffer’d since
And people that ‘the people’ slew.

A litany &c.

There roll’d from Tyburn heads and more
Of noblemen undone by pride,
Who live’d their cause by English shore
And upon their hope had died.
There was Raleigh all aboard
A ship of some long sunken fleet;
Bearing his encrusted sword
Done with cutting Spanish meat.

A litany &c.

Then Boudicca, her health betray’d,
Stoop’d aloft her bloodless smile,
And step by step her horses neigh’d
And hunger’d for another mile.
Now high from out the moors there came
Treading low with crunch of steel
The legionnaires of Roman fame,
Crushed beneath Britannia’s heel.

A litany &c.

At last the Dukes, and Earls, and more;
The Knights, and Lords drew up in state,
As every soul that sought out war
Crawl’d out from hell in seething hate.
Pandemonium was rais’d;
That capitol of Milton’s blood,
Where once entomb’d and lost they laz’d,
They pour’d as rats who fear a flood.

A litany &c.

Now Milton came himself indeed;
That bloody pen the poets fame,
And more from out the earth were freed;
Of infamous and famous name.
A sonnet of the sober grave
And tombstones penn’d in misty rhyme,
Were sunder’d as old nature’s slave –
Old ‘Death’ had lost his hold on time.

A litany &c.

That throne – Saint Pauls – rose up in black,
That crippl’d heart from out the blaze,
A sign of every sign we lack;
“The end has come, repent your ways.”
And even More came out to look
With Hundreds crushed beneath the tread
Of holy feet that bore the crook
That changed the words of God they read.

A litany &c.

That litany! Oh chant be done
And let’s forget our judgements wrong;
A war with pasts cannot be won,
We ask to hear forgiving song.
We may have damned the right and well,
Condemned so much without a thought,
But listen, ’tis one truth to tell;
Forgiveness through hard work is bought.

A litany &c.

I pray forgiveness may be ours;
From high you angels and your God,
From out those fine immortal towers
To us down here on dreary sod.
May all ours tears, our laughs and song,
May our gifts, our hopes and deeds,
Sow on things old Time proved wrong
Our very own forgiving seeds.

Some Essex History!

Accomplished Reader,

It is in amongst this riddle of revision, which I’m presently immersed in, that I have produced something quite un-revision like.

May it please the reader, then, that I may present my humble work called Danbury Place; a Minor History.