10/5

ACID is about Transactions

It stands for,

Atomicity: Means all or nothing, either it all works or nothing works. Much like how an atom is stable or unstable, and an aborted transaction doesn’t happen, works with innodb using commit and rollback commands

Consistency: Ensures that transactions will be valid and take the DB into states that are valid at start and finish.

Isolation: Isolation makes sure that transaction results result in a system that is the same if the transactions were done all in the same order, however, you can set the isolation level within the innodb system. And low level locking.

Durability: Makes sure that committed transactions are saved when done, and can be recovered in the event of disaster, errors or crashes, handled by innodb innodb_doublewrite config option.

https://docs.microsoft.com/en-us/dotnet/articles/visual-basic/language-reference/statements/using-statement

Using resource As New resourceType

‘ Insert code to work with resource.

End Using

 

‘ For the acquisition and disposal of resource, the following

‘ Try construction is equivalent to the Using block.

Dim resource As New resourceType

Try

‘ Insert code to work with resource.

Finally

If resource IsNot Nothing Then

resource.Dispose()

End If

End Try

Private Sub WriteFile()

Using writer As System.IO.TextWriter = System.IO.File.CreateText(“log.txt”)

writer.WriteLine(“This is line one.”)

writer.WriteLine(“This is line two.”)

End Using

End Sub

 

Private Sub ReadFile()

Using reader As System.IO.TextReader = System.IO.File.OpenText(“log.txt”)

Dim line As String

 

line = reader.ReadLine()

Do Until line Is Nothing

Console.WriteLine(line)

line = reader.ReadLine()

Loop

End Using

End Sub

 

8/May

Subqueries, this blog contains copy and pasting.

https://dev.mysql.com/doc/refman/5.5/en/subqueries.html

Subqueries are like joins, where you have a query within a query. Typically a select

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

Allows to isolate queries, and altenatve ways to peform joins.

DELETE FROM t1

WHERE s11 > ANY

(SELECT COUNT(*) /* no hint */ FROM t2

WHERE NOT EXISTS

(SELECT * FROM t3

WHERE ROW(5*t2.s1,77)=

(SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM

(SELECT * FROM t5) AS t5)));

 

In MySQL, you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT, REPLACE, UPDATE, and (because subqueries can be used in the SET clause) LOAD DATA INFILE.

 

https://dev.mysql.com/doc/refman/5.7/en/scalar-subqueries.html

Result of a subquery has to be a single value, not a row or more.

Simple scalar query,

CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);INSERT INTO t1 VALUES(100, ‘abcde’);SELECT (SELECT s2 FROM t1);

 

Producing a single result abcde.

Suppose that we make two tables:

CREATE TABLE t1 (s1 INT);INSERT INTO t1 VALUES (1);CREATE TABLE t2 (s1 INT);INSERT INTO t2 VALUES (2);

Then perform a SELECT:

SELECT (SELECT s1 FROM t2) FROM t1;

The result is 2 because there is a row in t2 containing a column s1 that has a value of 2.

A scalar subquery can be part of an expression, but remember the parentheses, even if the subquery is an operand that provides an argument for a function. For example:

SELECT UPPER((SELECT s1 FROM t1)) FROM t2;

 

The most common use of a subquery is in the form:

non_subquery_operand comparison_operator (subquery)

Where comparison_operator is one of these operators:

=  >  <  >=  <=  <>  !=  <=>

For example:

… WHERE ‘a’ = (SELECT column1 FROM t1)

MySQL also permits this construct:

non_subquery_operand LIKE (subquery)

At one time the only legal place for a subquery was on the right side of a comparison, and you might still find some old DBMSs that insist on this.

Here is an example of a common-form subquery comparison that you cannot do with a join. It finds all the rows in table t1 for which the column1 value is equal to a maximum value in table t2:

SELECT * FROM t1  WHERE column1 = (SELECT MAX(column2) FROM t2);

Here is another example, which again is impossible with a join because it involves aggregating for one of the tables. It finds all rows in table t1 containing a value that occurs twice in a given column:

SELECT * FROM t1 AS t  WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

For a comparison of the subquery to a scalar, the subquery must return a scalar. For a comparison of the subquery to a row constructor, the subquery must be a row subquery that returns a row with the same number of values as the row constructor.

The ANY keyword, which must follow a comparison operator, means “return TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns.” For example:

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

Suppose that there is a row in table t1 containing (10). The expression is TRUE if table t2 contains (21,14,7) because there is a value 7 in t2 that is less than 10. The expression is FALSE if table t2 contains (20,10), or if table t2 is empty. The expression is unknown (that is, NULL) if table t2 contains (NULL,NULL,NULL).

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

IN and = ANY are not synonyms when used with an expression list. IN can take an expression list, but = ANY cannot.

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

Suppose that there is a row in table t1 containing (10). The expression is TRUE if table t2 contains (-5,0,+5) because 10 is greater than all three values in t2. The expression is FALSE if table t2 contains (12,6,NULL,-100) because there is a single value 12 in table t2 that is greater than 10. The expression is unknown (that is, NULL) if table t2 contains (0,NULL,1).

 

What kind of store is present in one or more cities?

SELECT DISTINCT store_type FROM stores

WHERE EXISTS (SELECT * FROM cities_stores

WHERE cities_stores.store_type = stores.store_type);

What kind of store is present in no cities?

SELECT DISTINCT store_type FROM stores

WHERE NOT EXISTS (SELECT * FROM cities_stores

WHERE cities_stores.store_type = stores.store_type);

What kind of store is present in all cities?

SELECT DISTINCT store_type FROM stores s1

WHERE NOT EXISTS (

SELECT * FROM cities WHERE NOT EXISTS (

SELECT * FROM cities_stores

WHERE cities_stores.city = cities.city

AND cities_stores.store_type = stores.store_type));

Queries can be rewritten as joins if necessary.

For example, this query:

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);

Can be rewritten as:

SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id;

The queries:

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

Can be rewritten as:

SELECT table1.*  FROM table1 LEFT JOIN table2 ON table1.id=table2.id  WHERE table2.id IS NULL;

 

 

 

 

5/Apr

Sorry i was sick on Monday, constant shitting etc. nofun.

From what i grasp the class on monday was about SQL Procedures and functions.

These are  commands designed to push information, edit or update database rows and tables from other sources.

Our games will need to do this nearly constantly, so using procedures will be a must.

Heres a big SQL command that todd gave us, will be useful in the future to draw and get an idea of the correct syntax

 

DROP procedure p2;
DROP procedure AddUser;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddUser`(in pUserName varchar(255))
DETERMINISTIC
COMMENT ‘A procedure’
BEGIN
INSERT INTO tblPlayer(PlayerID,PlayerName)
VALUES (23123123,pUserName);
END$$
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p2`(in pUserName varchar(255))
DETERMINISTIC
COMMENT ‘A procedure’
BEGIN
DECLARE aplayerID  INT;

SELECT PlayerID
FROM `tblplayer`

WHERE PlayerName = pUserName
into aplayerID;

— SELECT aplayerID as ‘test’;

IF aplayerID IS NULL THEN
— INSERT
CALL AddUser(pUserName);
SELECT ‘Success (name does not exist)’ as Success;
ELSE
SELECT ‘Failed have name’ as Success;
END IF;

END$$

DELIMITER ;
CALL p2(‘Phot’);

SELECT PlayerID
FROM `tblplayer`
WHERE PlayerName = ‘Phot’;

This creates a new player, and also checks if they exist.

Add a line for password, and this can be used to make new players in my game.

29/mar

Today Todd discussed modules and some visual basic stuff, things that will be expanded upon once we started to work on m2/3.

In regards to m1 however, i’m doing pretty well. still lost and confused when it comes to some foreign key stuff and having it update, but otherwise everything else is done.

Tomorrow i’m going to stitch all my work together into a report and then submit.

Talking to Stephen and hes barely even started the schema, have fun with that matey.

22/3

Today was pretty boring tbh, i could of easily skipped this class and missed nothing.

For people who haven’t done SQL and Modeling before it was useful, but as I’m already versed and comfortable with how my m1 is going it wasn’t much use.

Anyway, regarding m1.

Finished my CRUD today. and for the most part have my SQL commands ‘Done’

Need to make the storyboards, as i only have a paint mockup for the timebeing.

Still need to generate some insert into data but that’s just lot of busywork, will be testing and designing the schema in the next few days. hopefully by Monday i’ll be able to show Todd  a “Complete” product for his thoughts.

 

Joins and Wasps.

Today in dat we were terrorised by mutiple deadly wasps, we may never be the same

also, we discussed Joins.

Joins are a select modifier that allows the developer and viewer to view infomation from mutiple different tables in the same query.

Here’s some example code.

 

Use adventureworks2008br2;
Go
Select e.buisnessidentiy.id
From humanresources.employee as e
Inner join sales.salesperson as s
On e.buisnessentityID = s.buisnessentityid

USE adventureworks2008br2
GO
SELECT distinct pl.productid, pl.listprice
From production.product p1
INNER JOIN production.product p2
ON pl.productsubcatagoryID =p2.productsubcatagoryID
And pl.listprice <> [2.listprice
Where pl.listprice < $15 and p2.listprice <$15
Order by productsubcatagoryID;

Inner and outer joins SQL examples and the Join block

 

I’ve also laid out what i’m going to be doing with milestone one and will be doing some use case today.

Create table and Insert into drafts

Working on my tables and insert into commands today.

CREATE TABLE tblPlayer(
PlayerID INTEGER PRIMARY KEY,
FOREIGN KEY(PlayerStatusID) REFERENCES tblPlayerStatus(PlayerStatusID),
FOREIGN KEY(AccID) REFERENCES tblAccountDetails(AccID)
hp INTEGER (3),
end INTEGER (3),
mp INTEGER (3),
agi INTEGER (3),
int INTEGER (3),
str INTEGER (3),
lvl INTEGER (3),
alive ENUM (‘Yes’, “No”)
);

CREATE TABLE tblAccountDetails(
AccID INTEGER PRIMARY KEY,
FOREIGN KEY (PlayerID) REFERENCES tblPlayer (PlayerID),
PlayerName VARCHAR(32) NOT NULL,
PlayerPassword VARCHAR(32) NOT NULL)
);

CREATE TABLE tblPlayerCoord
FOREIGN KEY (PlayerID) REFERENCES tblPlayer (PlayerID),
PCoordX INTEGER (2) NOT NULL,
PCoordY INTEGER (2) NOT NULL)
);

CREATE TABLE tblMobCoord
FOREIGN KEY (MobID) REFERENCES tblMob (MobID),
MCoordX INTEGER (2) NOT NULL,
MCoordY INTEGER (2) NOT NULL)
);

CREATE TABLE tblMob(
MobID INTEGER PRIMARY KEY,
hp INTEGER (3),
end INTEGER (3),
mp INTEGER (3),
agi INTEGER (3),
int INTEGER (3),
str INTEGER (3),
lvl INTEGER (3),
alive ENUM (‘Yes’, “No”)
);

Kinda dislike that mysql doesnt have a BOOLEAN y/n command.

Enum is more versitile, but boolean is nice and simple.

INSERT INTO tblPlayerCoord(PCoordX,PCoordY)
VALUES (20,10);

INSERT INTO tblPlayerID(PlayerID, hp, end, mp, agi, int, str, lvl, alive)
VALUES (1, 20, 5, 50, 6, 10, 2, 13, ‘Yes’);

These just continue on for the other tables in the same formats.

Thinking about how it will draw and move the spirtes onscreen, coords will need to be labled as player and mob coords  [ex MCoordY MCoord PCoordY PCoordX] so the program knows where they are on the grid.

3/7/17

Sorry about not doing a blog for Monday/not being at class, couldn’t make it in

Todd posted a large sql string that will be a useful resource to draw from come the first milestone.

http://ecampus.nmit.ac.nz/moodle/pluginfile.php/678027/mod_resource/content/1/IntroSQLCreateInsert.sql

MySQL Table Creation

The mysql manual has in depth templates for all forms of query used with it, including table creation that goes into depth the syntax that can be used within it.

and then offers examples, the examples are generally a easier way to grasp what they want and how it works. its a long and technical document.

DB storage types

Foreign Keys

Foreign Keys allow tables to refer to other tables upon creation and viewing them via querys, when a table that has forgein keys is given a command like

SELECT * FROM TableFK

and has two FK form another table it will print something like

ID NAME FK1

1, Steve, 3

2, John, 4

3, Mike, 7

etc.

Foreign keys if used right are a great way to transfer data between tables and cut down on the amount of work needed to enter the data into the tables, and cuts down on insert into query compexity, they arent without their problems though, badly designed table creation commands and not doing the correct order can create issues where its trying to refer to tables that don’t exist yet. so the designer must be aware of the order they are doing.

For instance.

CREATE TABLE tblStudent( StudentID INTEGER PRIMARY KEY , StudentName VARCHAR(50) );



CREATE TABLE tblStudentCourse(
   StudentID INTEGER NOT NULL, 
   CourseID INTEGER NOT NULL, 
   EnrolmentStatus VARCHAR(5) NOT NULL,
   PRIMARY KEY(StudentID,CourseID), 
   FOREIGN KEY(StudentID) REFERENCES tblStudent(StudentID),
);

the latter refers to the first table.

Good use of Foreign keys can also be helpful in updating mass amounts of rows, without foreign keys the data entry person needs to go through every row and update each line upon a change, whilst with well designed tables and queries you can update the whole db with a single command to the base tables rows.

some commands

• The CONSTRAINT clause allows you to define constraint name for the foreign key constraint. If you omit it, MySQL will generate a name automatically.
• The FOREIGN KEY clause specifies the columns in the child table that refers to primary key columns in the parent table. You can put a foreign key name after FOREIGN KEY clause or leave it to let MySQL create a name for you. Notice that MySQL automatically creates an index with the foreign_key_name name.
• The REFERENCES clause specifies the parent table and its columns to which the columns in the child table refer. The number of columns in the child table and parent table specified in the FOREIGN KEY and REFERENCES must be the same.
• The ON DELETE clause allows you to define what happens to the records in the child table when the records in the parent table are deleted. If you omit the ON DELETE clause and delete a record in the parent table that has records in the child table refer to, MySQL will reject the deletion. In addition, MySQL also provides you with actions so that you can have other options such as ON DELETE CASCADE that ask MySQL to delete records in the child table that refers to a record in the parent table when the record in the parent table is deleted. If you don’t want the related records in the child table to be deleted, you use the ON DELETE SET NULL action instead. MySQL will set the foreign key column values in the child table to NULL when the record in the parent table is deleted, with a condition that the foreign key column in the child table must accept NULL values. Notice that if you use ON DELETE NO ACTION or ON DELETE RESTRICT action, MySQL will reject the deletion.
• The ON UPDATE clause enables you to specify what happens to the rows in the child table when rows in the parent table are updated. You can omit the ON UPDATE clause to let MySQL reject any updates to the rows in the child table when the rows in the parent table are updated. The ON UPDATE CASCADE action allows you to perform a cross-table update, and the ON UPDATE SET NULL action resets the values in the rows in the child table to NULL values when the rows in the parent table are updated. The ON UPDATE NO ACTION or UPDATE RESTRICT actions reject any updates.

 

Milestone 1

This week i’ll put together a proper storyboard and start to design the sql db code to be used with it, i’ll have to discuss with Steffen with what happened on Monday in depth and what i need to read on the moodle.

I’ve only recently got this laptop so i’ll have to get mysql and vb installed.

Class 2, 2/3/17

Today Todd talked about CREATE and SELECT

These are two things I’ve gone over in the past and are experienced with, but i’ll provide a couple of examples nevertheless

CREATE statements are at the core of a database, they lay out how the DB will be made and what constraints the data going into the tables will have.

Example

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

Many different limits can be applied to the different columns with data types, limits of what can be placed in them etc

The core of a SELECT is to ask the DBMS to grab a peice of data from a table and row that you specify, it can be as simple a

SELECT column–names

FROM table–name

or

SELECT *

FROM table-name

To grab everything.

Selects can become very complex forming the backbone of the majority of SQL commands.

But at their core they are build around the concept of

SELECT x

FROM y

WHERE constraints

Things like joins can be applied to combine from different tables and columns, and basically anything the designer wants that the DBMS can handle.

One must of cause be careful of creating cartesian products where tables are joined incorrectly, these can run amok and create billions of results crashing the entire system.

Not good.

 

 

Logical ERD Draft

Everything is 1:1 so far, can’t think of what would require 1:m or m;n right now.

Still thinking about it in my head, not sure if I’ll need a entity for map yet.

And what other entities will be required.

https://puu.sh/un4Ev/5c4f831e50.png

Uploading that to word press murders the resolution.