All Things Open 2016 -- Database Programming for Newbies

All Things Open -- Database Programming for Newbies 101-2 track

Database Programming for Newbies This is a very short introduction to database programming and can not cover all the things you need to know to be effective. You have to build your skills from many sources including this talk Example Database The examples in this talk are from the MySQL World database. The content is dated but the schema/database is still useful. https://dev.mysql.com/doc/index-o ther.html MySQL Workbench Workbench is the second most popular FREE download from MySQL. It is a very useful tool and has dozens of uses (most can’t be covered here) Programming examples -- No, I can not provide examples in everyone’s favorite programming language. The examples following are in a variety of languages but all use the same concepts to communicate with a MySQL server. 2

Hello world! I AM Dave Stokes I am a MySQL Community Manager for Oracle You can find me at: @stoker David.Stokes@Oracle.com Elephantanddolphin.blogger.com OpensourceDBA.wordpress.com Slides: slideshare.net/davidmstokes 3

1 The Basics You have to start somewhere 4

Cat: Where are you going? Alice: Which way should I go? Cat: That depends on where you are going. Alice: I don’t know. Cat: Then it doesn’t matter which way you go. -- Lewis Carroll 5

Database Generic ▪ The concepts are generic for most all databases ▪ Implementation are MySQL specific ▪ Differences between version will bite you Pay attention to the flow not the syntax at this point 6

Database Server You need to know where to contact the server and how to connect to it 7

Server Information IP Address -- You need to connect via a network to the server (unless instance is local) PORT -- Socket on server at IP Address where database server is accepting requests Authentication -- Username, password or authentication string, and maybe more 8

<?php $link = mysqli_connect("127.0.0.1", "my_user", "my_password"); if (!$link) { echo "Error: Unable to connect to MySQL." . PHP_EOL; echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL; echo "Debugging error: " . mysqli_connect_error() . PHP_EOL; exit; } echo "Success: A proper connection to MySQL was made! The my_db database is great." . PHP_EOL; echo "Host information: " . mysqli_get_host_info($link) . PHP_EOL; mysqli_close($link); ?> IP Address Username Password Link or Handle -- our conduit to database 9

import mysql.connector cnx = mysql.connector.connect(user='scott', password='tiger', host='127.0.0.1', database='employees') cnx.close() Again we have an IP Address, the username/authentication and set up a handle 10

We pass query to server Usually this is in form of a query written in Structured Query Language (SQL) Sending query to a server We receive info back Hopefully we will receive data back but we may get an error message back or at least some return code 11

Structured Query Language Declarative SQL is a fairly simple to learn declarative language -- made up of DDL & DDL. DDL Data Description Language describes how the data is to represented -- data type, length, default values, NULLable ... DML Data Manipulation Language is used to handle the actual data -- Find all the Cities in California with a population greater than 500K, delete records older than 90 days, change zip code to 12345 from 11345 12

What your server does with a query 1. Is your system allowed to connect to server? 2. Are you allowed to access server/data? 3. Is the query syntax correct? 4. Generate Query Plan 5. Return requested data 13

What the heck is A Query Plan?!?!? 14

You query ... - is examined for what data is needed to fulfill the query. - statistics from past queries used to estimate lowest cost. - every added column to a query is potentially a new factorial of complexity. - Query plan is developed to get data. 15

Your data - is returned to your application (if all goes correctly, or data is returned, or a status code) - Many options for reception, depending on language 16

10,000’ View Server -- Process Application -- Data Application - Query 17

2 A Little More Depth A little more detail 18

"The time has come," the Walrus said, "To talk of many things: Of shoes--and ships--and sealing-wax-- Of cabbages--and kings-- And why the sea is boiling hot-- And whether pigs have wings." -- Lewis Carroll 19

We want to get a list of CITY NAMES with the corresponding COUNTRY NAME from the World database. Example Query 20

SHOW CREATE TABLE City; CREATE TABLE `city` ( `ID` int(11) AUTO_INCREMENT, `Name` char(35), `CountryCode` char(3), `District` char(20), `Population` int(11)', `Name_ci` char(35) CHARACTER SET utf8 GENERATED ALWAYS AS (Name) STORED, PRIMARY KEY (`ID`), KEY `CountryCode` (`CountryCode`), KEY `Name_ci` (`Name_ci`), CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 21

Sample City Data From Query ID Name CountryCode District Population Name_ci 1 Kabul AFG Kabol 1780000 Kabul 2 Qandahar AFG Qandahar 237500 Qandahar 3 Herat AFG Herat 186800 Herat 4 Mazar-e-Sharif AFG Balkh 127800 Mazar-e-Sharif 5 Amsterdam NLD Noord-Holl and 731200 Amsterdam 22

SHOW CREATE TABLE Country 'CREATE TABLE `country` ( `Code` char(3), `Name` char(52), `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America'), `Region` char(26), `SurfaceArea` float(10,2), `IndepYear` smallint(6), `Population` int(11), `LifeExpectancy` float(3,1), `GNP` float(10,2), `GNPOld` float(10,2), `LocalName` char(45), `GovernmentForm` char(45), `HeadOfState` char(60), `Capital` int(11), `Code2` char(2), PRIMARY KEY (`Code`)) 23

Generic Query (DML) SELECT City.Name, Country.Name FROM City JOIN Country ON (City.CountryCode = Country.Code) The desired data From which table How to connect the tables 24

Generic Query (DML) as a string $query = “SELECT City.Name, Country.Name FROM City JOIN Country ON (City.CountryCode = Country.Code)”; 25 Do your older self a big favor and go for clarity over space savings when you write queries in your code.

Send $query to server $result = $conn->query($query); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { echo "City: " . $row[0]. " Country: " . $row[1] . "<br>"; } } else { echo "0 results"; } $conn->close(); 26

Roughly same thing in Python import datetime import mysql.connector cnx = mysql.connector.connect(user='dave', database='world') cursor = cnx.cursor() query = ("SELECT City.Name, Country.Name FROM City JOIN Country ON (City.CountryCode = Country.Code)") cursor.execute(query) for (city_name, country_name in cursor: print("{}, {}".format( city_name, country_name, hire_date)) cursor.close() cnx.close() 27

Many Options to retrieve data while($row = $result->fetch_assoc()) { echo "City: " . $row[0]. " Country: " . $row[1] . "<br>"; } 28 mysqli_result::fetch_all — Fetches all result rows as an associative array, a numeric array, or both mysqli_result::fetch_array — Fetch a result row as an associative, a numeric array, or both mysqli_result::fetch_assoc — Fetch a result row as an associative array mysqli_result::fetch_field_direct — Fetch meta-data for a single field mysqli_result::fetch_field — Returns the next field in the result set mysqli_result::fetch_fields — Returns an array of objects representing the fields in a result set mysqli_result::fetch_object — Returns the current row of a resultset as an object mysqli_result::fetch_row — Get a result row as an enumerated array

3 SQL -- Structure Query Language Basics Five minutes on a Relatively simple language that can drive you crazy for years!! 29

“But I don’t want to go among mad people," Alice remarked. "Oh, you can’t help that," said the Cat: "we’re all mad here. I’m mad. You’re mad." "How do you know I’m mad?" said Alice. "You must be," said the Cat, "or you wouldn’t have come here.” 30

DDL - data definition language CREATE DROP ALTER TRUNCATE RENAME 31 The Major Verbs of Structured Query Language DML - data manipulation language SELECT INSERT UPDATE DELETE Usually used by DBAs to set up the data Used to access the data by just about everyone

Check your manpage for details on each verb SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [MAX_STATEMENT_TIME = N] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [PARTITION partition_list] [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]] 32

99% of your SELECTs will be much simpler SELECT select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name} [ORDER BY {col_name} [LIMIT {row_count] 33 SELECT Name, District, ID FROM City WHERE ID < 100000 GROUP BY District ORDER BY Name LIMIT 5 Most of the VERBs simplify down to a few, more common qualifiers. Best way to learn is to follow examples in a manual or book.

You DESCRIBE what you want, sort of like ordering a pizza! 35 SQL is a Declarative Language The bad part: Like a pizza, you have to know the ingredients to be able to order what you want! You can’t look at an SQL query and tell if it is bad … Just like a menu does not tell you if the cheese has gone fuzzy and the pepperoni meows

You now know -Client server database model -Queries passed to server -Data returned (sometimes) -A little DML & DDL -The MySQL authentical system -Basic query & SQL VERBs 36 Running summary What to add -JOINs -SQL Injection -N+1 Problem -Transactions -Indexes -Wrap-up -Q/A

4 JOINing tables A good glimpse at the Relational model 37

“Everything is funny, if you can laugh at it.” 38

JOINs JOINs allow you to connect two tables on (hopefully) a common element. -Depends on data normalization (weeks can be spent on just that!) -- For now just accept that some has broken up all the data into component data -- For our example we have a table with City information, Country information, and Languages spoke in a Country information and that someone architected the data so that one field can help tie them together. 39

40 CountryCode All three tables in our example database can be linked easily by these columns Also note that each of the columns used to link are INDEXed or Keys

41 A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indices can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records. -- Wikipedia

42 Remember the Query Plan??? The query plan tried to find efficient indexes to help retrieve data.

Optimizer Trace { "query_block": { "select_id": 1, "cost_info": { "query_cost": "5132.14" }, "nested_loop": [ { "table": { "table_name": "Country", "access_type": "ALL", "possible_keys": [ "PRIMARY" ], "rows_examined_per_scan": 239, "rows_produced_per_join": 239, "filtered": "100.00", "cost_info": { "read_cost": "6.00", "eval_cost": "47.80", "prefix_cost": "53.80", "data_read_per_join": "61K" }, "used_columns": [ "Code", "Name" ] } }, { "table": { "table_name": "City", "access_type": "ref", "possible_keys": [ "CountryCode" ], "key": "CountryCode", "used_key_parts": [ "CountryCode" ], "key_length": "3", "ref": [ "world.Country.Code" ], "rows_examined_per_scan": 17, "rows_produced_per_join": 4231, "filtered": "100.00", "cost_info": { "read_cost": "4231.95", "eval_cost": "846.39", "prefix_cost": "5132.14", "data_read_per_join": "727K" }, "used_columns": [ "Name", "CountryCode" ] } } ] } } 43 The KEY used to perform the join on our example query is in both tables

Optimizer What fields are needed? -- Cost -- Can a INDEX(es) be used -- Which INDEX -- WHERE clause (later) -- SORT (later) 44

EXPLAIN -- prepend ‘EXPLAIN’ to query Explain details what the optimizer wants to do to run the query. For the City table the CountryCode index is used to link to the Code column in the Country Table. There is a possible key on the Country table but it is not needed/used for this query 45

Visual Explain -- MySQL Workbench 46

5 Problems Where the train Starts to go Off the tracks 48

She generally gave herself very good advice, (though she very seldom followed it) 49

1. Network connection 2. Is host okay to connect? 3. Does user have proper access? 4. Syntax check 5. Query Plan Generation 6. Return data 50 Each Connection to the database has a cost Therefore seek to use the fewest calls to the database to get the needed data!!!

N+1 Example -- Ride to work 1. Ask database for a list of employees. 2. Do any of these employees have a parking permit. 3. Do any of those in step two live in same zip code. 51 1. Ask database for a list of employes with a parking permit living in your zipcode. Think of data in big sets -- Let the database do the heavy lifting

Which is more efficient -- Sales staff 20% raise foreach (sale_emp in sales_employees) $pay += $pay * .20; UPDATE employees SET pay_rate = pay_rate + pay_rate * .20 WHERE department = ‘sales’; 52 START TRANSACTION; COMMIT; What is easier to recover in case of a crash of a PHB correction?

SQL Injection SQL Injection is where you -- the programmer -- take raw, unfiltered data from the end user and plop it into an SQL query. The ‘injection’ is SQL code put into your query that can give the end user access to your data. NEVER, NEVER put raw input data from end user into your SQL queries. Check for validity -- Are numbers really numbers? Are there special characters that you do not want? Are you really certain that the inputs are in the write format. 53

SELECT * FROM customer WHERE id=$x Expected an Integer Received ‘TRUE’ Expected an Integer Received ‘TRUE’;SELECT * FROM mysql.user; 55 EXAMPLE of vulnerable code

Last BIG PROBLEM for today 56 Check return codes -- Do not assume all went well. Be prepared to roll back pr resubmit or handle and error

BOOKS YOU REALLY REALLY NEED if you want CJ Date Bill Karwin 57

BOOKS YOU REALLY REALLY NEED if you want to be GOOD Any of the top 20 AMAZON books on {Your Favorite Programming Language Here} and {Your Database Of Choice Here} Bill Karwin 58

What if I do not want to do SQL?!?! Key/Value Pairs JSON - Document Storage Graph Databases Other NoSQL approaches Keep everything in memory all the time 59

Thanks! ANY QUESTIONS? You can find me at: @stoker david.stokes@oracle.com elephantanddolphin.blogger.com 60

All Things Open 2016 -- Database Programming for Newbies

More Related Content

What's hot

Viewers also liked

Similar to All Things Open 2016 -- Database Programming for Newbies

More from Dave Stokes

Recently uploaded

All Things Open 2016 -- Database Programming for Newbies