class: center, middle, inverse, title-slide # Introduction to Data Science ## Session 4: Relational databases and SQL ### Simon Munzert ### Hertie School |
GRAD-C11/E1339
---  # Table of contents
 1. [Why databases?](#whydb) 2. [Relational database fundamentals](#relationaldb) 3. [Back to `dplyr`: joins](#joins) 4. [SQL](#sql) 5. [Talking to databases with R](#dbr) 7. [Summary](#summary) --- class: inverse, center, middle name: whydb # Why databases?

 --- # The ubiqity of multi-dimensional data structures .pull-left[ ### From data frames... - When you have a background in social sciences, your top-of-the-head mental image of data might be a rectangular [spreadsheet](https://en.wikipedia.org/wiki/Spreadsheet). - In fact, much of classical "statistical" software (SPSS, Stata, MS Excel) operates with rectangular data frames by default. - At the same time, your perception might be [file-based](https://en.wikipedia.org/wiki/List_of_file_formats). Data is stored in files, and these files are read (and produced) by our data management software. - In many cases, the **two-dimensional structure** makes sense. For instance, we observe - persons x attitudes - countries x characteristics - social media posts x text features ] .pull-right-center[

 ] --- # The ubiqity of multi-dimensional data structures .pull-left-center[

 `Credit` [saschagobel/legislatoR](https://github.com/saschagobel/legislatoR) ] .pull-right[ ### ... to complex data structures - However, the longer you think about it, the more problematic it becomes to store your data in two-dimensional structures. - **Examples**: - countries x persons x characteristics x time - countries x states x communities x time x variables - social media posts x retweets x users x user characteristics x network features x meta data - Mapping three- onto two-dimensional structures is easy (think: `pivot_longer`, `pivot_wider`). - With **multiple heterogeneous data sources**, things get messy. - Managing complex data structures is just one perk of using databases. ] --- # When databases become useful .pull-left[ ### Size and speed - You have **loads of data that exceed the working memory** on your computer. Databases are only limited by available disk size (or can be distributed across multiple disks/machines). - Your **data structure is complex**. Databases allow/encourage you to store, retrieve and subset data with complex data structures. - Your data is big and you have to **access/subset/operate frequently**. Querying databases is fast. - You care about **data quality** and have clear expectations how data should look like. Using databases you can define specific rules for extending and updating your database. ] -- .pull-right[ ### Accessibility and concurrency - You **collaborate with others** on a data collection project. With a database, you have a common, simultaneously accessible, and reliable infrastructure at hand that multiple users can access at the same time. - When several parties are involved, who is allowed to do what with the database might differ (e.g., read-only, access to parts of the data, limited admin rights, etc.). Most databases allow **defining different usage rights for different users**. ] --- # Talking about databases .pull-left[ ### What we should distinguish - The **types of databases**, e.g.: relational, navigational, NoSQL, NewSQL - The **database management system**, e.g.: PostgreSQL, Oracle, SQL Server, SQLite - The **data structure**, e.g.: tables, columns, keys, normal forms - The **data manipulations**, e.g.: selects, joins, grouping - The **query language**, e.g., SQL, SPARQL Also, there are so many more ways to [classify databases](https://en.wikipedia.org/wiki/Database#Classification). But that's enough for now. Today, **we focus on relational databases**. They are by no means the only type of databases (see above), but they're ubiquitous and won't go away any time soon. ] -- .pull-right[ ### Databases versus data frames When reading/talking about features of databases, you will encounter a particular jargon. Here's how database concepts map onto R data frame jargon: | R jargon | Database jargon | |---|---| | column | attribute/field | | row | tuple/record | | element/cell | attribute value | | data frame | relation/table | | column types | table schema | | bunch of related data frames | database | ] --- class: inverse, center, middle name: relationaldb # Relational database fundamentals

 --- # Codd's relational model for databases .pull-left[ - The concept of relational databases builds on the [relational model (RM) for database management](https://en.wikipedia.org/wiki/Relational_model), as proposed by [Edgar F. "Ted" Codd](https://en.wikipedia.org/wiki/Edgar_F._Codd) in 1969/1970. - Todd described the RM formally, but also introduced it using concepts that are still in use today (normalization, keys, joins, redundancy, etc.). - The key assumption of the relational model is that all data can be represented as relations (tables). - Information is then represented by data values in relations. - When you think this is trivial, check out the [history of databases](https://en.wikipedia.org/wiki/Database) and live through the pain of the early era of navigational DBMS in the 1960s and the NoSQL era that we've (not yet) overcome.  ] .pull-right-center[

 `Credit` [Communications of the ACM 13(6), 1970](https://dl.acm.org/doi/10.1145/362384.362685) ] --- # Codd's relational model for databases (cont.) .pull-left[ ### Storing data in tables - Again, the key concept of relational databases is that all information can be represented in a table. - A single table already introduces relations: All data in one row belongs to the same record. - If we want to represent more complex relations (i.e., measuring a person's weight twice or measuring the weight of their children as well), we can relate data from one table to another. ### Example - We have collected data on Peter, Paul, and Mary. - We have information on birthdays, telephone numbers, and favorite foods. - How can we represent this information in tables? ] -- .pull-right[

 - We start representing the data in two tables. - They are linked via the key `nameid`, so we don't have to add the full names to the phone numbers table. - Note that we have a 1:m (one-to-many) relation here because Peter has two phone numbers. ] --- # Codd's relational model for databases (cont.) .pull-left[ ### Storing data in tables - Again, the key concept of relational databases is that all information can be represented in a table. - A single table already introduces relations: All data in one row belongs to the same record. - If we want to represent more complex relations (i.e., measuring a person's weight twice or measuring the weight of their children as well), we can relate data from one table to another. ### Example - We have collected data on Peter, Paul, and Mary. - We have information on birthdays, telephone numbers, and favorite foods. - How can we represent this information in tables? ] .pull-right[

 - However, the way we store the data is not ideal. In the first table, we have three columns measuring effectively the same thing. And what if there's more favorite food? Adding information in such a fashion creates a lot of redundant information. ] --- # Codd's relational model for databases (cont.) .pull-left[ ### Storing data in tables - Again, the key concept of relational databases is that all information can be represented in a table. - A single table already introduces relations: All data in one row belongs to the same record. - If we want to represent more complex relations (i.e., measuring a person's weight twice or measuring the weight of their children as well), we can relate data from one table to another. ### Example - We have collected data on Peter, Paul, and Mary. - We have information on birthdays, telephone numbers, and favorite foods. - How can we represent this information in tables? ] .pull-right[

 - Splitting up the information by creating another table for food preferences is better. - There's still some redundancy left. Is it really necessary to have `hamburger` in the table twice? ] --- # Codd's relational model for databases (cont.) .pull-left[ ### Storing data in tables - Again, the key concept of relational databases is that all information can be represented in a table. - A single table already introduces relations: All data in one row belongs to the same record. - If we want to represent more complex relations (i.e., measuring a person's weight twice or measuring the weight of their children as well), we can relate data from one table to another. ### Example - We have collected data on Peter, Paul, and Mary. - We have information on birthdays, telephone numbers, and favorite foods. - How can we represent this information in tables? ] .pull-right[

 - Now that's better. - In restructuring the information in our database, we **avoided redundancy (duplication)**. - This is the process of **database normalization**. ] --- # Database normalization .pull-left[ ### What is database normalization? **From the [Wikipedia](https://en.wikipedia.org/wiki/Database_normalization)**: "Database normalization is the process of **structuring a database**, usually a relational database, in accordance with a series of so-called **normal forms** in order to **reduce data redundancy and improve data integrity**. It was first proposed by Edgar F. Codd as part of his relational model." - You'll probably not have to apply normalization yourself because you are a user not a designer of databases. - However, it helps to have an idea of what the first normal forms are. - Higher-order normal forms imply lower-order normal forms (e.g., in order to satisfy the 3rd normal form, the 1st and 2nd normal forms have to be satisfied, too). ] -- .pull-right[ ### Normalization and tidy data There is also a straightforward link to [Hadley Wickham's "tidy data"](https://www.jstatsoft.org/article/view/v059i10): 1. Each variable forms a column. 2. Each observation forms a row. 3. Each type of observational unit forms a table. This is Codd's 3rd normal form using "statistical" jargon and applied to a single dataset. ] --- # Database normalization (cont.) The **normal forms** (from least normalized to most normalized):

 `Credit` [English Wikipedia, "Database normalization"](https://en.wikipedia.org/wiki/Database_normalization) --- # Database normalization (cont.)

 --- # Database schema .pull-left[ ### What schemas are - The database schema describes the structure of a database. It represents the map or blueprint of how the database is constructed. - The schema specifies all core ingredients of the database, including tables, fields, keys relationships, views, etc. - The visualization helps database users understand the relationships between the tables.

 ] .pull-right[ ### How they can look like

 `Credit` [Timo Tijhof/Wikimedia Commons](https://en.wikipedia.org/wiki/Database_schema#/media/File:MediaWiki_1.28.0_database_schema.svg) ] --- # Databases and Database Management Systems .pull-left-wide[ ### What are databases? - Databases are an organized collection of data. - They are organized to afford efficient retrieval of (selections) of data. - They entail data + metadata about structure and organization. - They are generally accessed through a database management system. ### Where are databases? - Databases can exist locally or remotely, in-memory or on-disk. - When they are stored locally, they are stored as binary file (not text file). - Commonly, we think of a **client-server model**: - Databases live on a **server**, which manages them - Users interact with the server through a **client** program. - Lets multiple users **access** the same database **simultaneously**. ] --- # Databases and Database Management Systems .pull-left-wide[ ### What are DBMS? - Database Management Systems (DBMS) provide **efficient**, **reliable**, **convenient**, **safe**, **multi-user** storage of and access to **massive** data. - **Massive**: Think Terabytes, not Gigabytes. Handle data that resides outside memory. - **Safe**: Robust to power outages, node failures, etc. - **Multi-user**: Concurrency control. Not one user, but multiple. - **Convenient**: High-level query languages. - **Efficient**: Just fast. - **Reliable**: High uptime. - There are [so many DBMS](https://en.wikipedia.org/wiki/List_of_relational_database_management_systems) for relational database structures alone. - RDBMS differ in terms of capabilities, implemented features, operating system support, and much more. - You'll probably not be in the position to decide which database to use. If you're still interested in the differences, [this](https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems) or [this overview](https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems) might be a good starting point. - Also, I've heard good things about [DuckDB](https://duckdb.org/). ] .pull-right-small[

 ] --- class: inverse, center, middle name: joins # Back to dplyr: joins

 --- # Relational data in R For the simple examples that I'm going to show here, we'll need some data sets that come bundled with the [**nycflights13**](http://github.com/hadley/nycflights13) package. Let's load it now and then inspect these data frames in your own console. ```r R> library(nycflights13) ``` --- # Relational data in R (cont.) The package contains the tables `flights`, `airlines`, `airports`, `planes`, and `weather`. -- The `airlines` data frame lets you look up the full carrier name from its [abbreviated code](https://en.wikipedia.org/wiki/List_of_airline_codes): ```r R> head(airlines, 10) ``` ``` ## # A tibble: 10 × 2 ## carrier name ## <chr> <chr> ## 1 9E Endeavor Air Inc. ## 2 AA American Airlines Inc. ## 3 AS Alaska Airlines Inc. ## 4 B6 JetBlue Airways ## 5 DL Delta Air Lines Inc. ## 6 EV ExpressJet Airlines Inc. ## 7 F9 Frontier Airlines Inc. ## 8 FL AirTran Airways Corporation ## 9 HA Hawaiian Airlines Inc. ## 10 MQ Envoy Air ``` --- # Relational data in R (cont.) The package contains the tables `flights`, `airlines`, `airports`, `planes`, and `weather`. `airports` gives information about each airport, identified by the `faa` [airport code](https://airportcodes.io/en/faa-codes/): ```r R> head(airports, 10) ``` ``` ## # A tibble: 10 × 8 ## faa name lat lon alt tz dst tzone ## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <chr> <chr> ## 1 04G Lansdowne Airport 41.1 -80.6 1044 -5 A America/New_Yo… ## 2 06A Moton Field Municipal A… 32.5 -85.7 264 -6 A America/Chicago ## 3 06C Schaumburg Regional 42.0 -88.1 801 -6 A America/Chicago ## 4 06N Randall Airport 41.4 -74.4 523 -5 A America/New_Yo… ## 5 09J Jekyll Island Airport 31.1 -81.4 11 -5 A America/New_Yo… ## 6 0A9 Elizabethton Municipal … 36.4 -82.2 1593 -5 A America/New_Yo… ## 7 0G6 Williams County Airport 41.5 -84.5 730 -5 A America/New_Yo… ## 8 0G7 Finger Lakes Regional A… 42.9 -76.8 492 -5 A America/New_Yo… ## 9 0P2 Shoestring Aviation Air… 39.8 -76.6 1000 -5 U America/New_Yo… ## 10 0S9 Jefferson County Intl 48.1 -123. 108 -8 A America/Los_An… ``` --- # Relational data in R (cont.) The package contains the tables `flights`, `airlines`, `airports`, `planes`, and `weather`. `planes` gives information about each plane, identified by its `tailnum` (aircraft registration a.k.a. [tail number](https://en.wikipedia.org/wiki/Aircraft_registration)): ```r R> head(planes, 10) ``` ``` ## # A tibble: 10 × 9 ## tailnum year type manufacturer model engines seats speed engine ## <chr> <int> <chr> <chr> <chr> <int> <int> <int> <chr> ## 1 N10156 2004 Fixed wing m… EMBRAER EMB-1… 2 55 NA Turbo-… ## 2 N102UW 1998 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ## 3 N103US 1999 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ## 4 N104UW 1999 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ## 5 N10575 2002 Fixed wing m… EMBRAER EMB-1… 2 55 NA Turbo-… ## 6 N105UW 1999 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ## 7 N107US 1999 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ## 8 N108UW 1999 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ## 9 N109UW 1999 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ## 10 N110UW 1999 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ``` --- # Relational data in R (cont.)  The package contains the tables `flights`, `airlines`, `airports`, `planes`, and `weather`. Finally, `flights` gives data on each of the 336776 flights in the dataset: ```r R> head(flights, 10) ``` ``` ## # A tibble: 10 × 19 ## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time ## <int> <int> <int> <int> <int> <dbl> <int> <int> ## 1 2013 1 1 517 515 2 830 819 ## 2 2013 1 1 533 529 4 850 830 ## 3 2013 1 1 542 540 2 923 850 ## 4 2013 1 1 544 545 -1 1004 1022 ## 5 2013 1 1 554 600 -6 812 837 ## 6 2013 1 1 554 558 -4 740 728 ## 7 2013 1 1 555 600 -5 913 854 ## 8 2013 1 1 557 600 -3 709 723 ## 9 2013 1 1 557 600 -3 838 846 ## 10 2013 1 1 558 600 -2 753 745 ## # … with 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>, ## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>, ## # hour <dbl>, minute <dbl>, time_hour <dttm> ``` --- # Relational data in R (cont.) .pull-left[ - We can illustrate the relationships between the different tables with a schematic drawing.1 - One table seems central (`flights`), but that's not a necessary feature of relational databases. - Key to understanding the diagram is that each relation always concerns a pair of tables: - `flights` connects to `planes` via `tailnum`. - `flights` connects to `airlines` via `carrier`. - `flights` connects to `airports` via `origin` and `dest`. - `flights` connects to `weather` via `origin` (location) and `year`, `month`, `day`, and `hour` (time). ] .pull-right[

 ] .footnote[1 Of course there are R packages that help you create such data models visually, e.g., the [dm package](https://cynkra.github.io/dm/).] --- # Relational data in R (cont.) .pull-left[ ### Question time 1. Imagine you wanted to draw (approximately) the route each plane flies from its origin to its destination. What variables would you need? What tables would you need to combine? 2. We know that some days of the year are “special”, and fewer people than usual fly on them. How might you represent that data as a data frame? What would be the primary keys of that table? How would it connect to the existing tables? ] .pull-right[

 ] --- # Joins .pull-left[ One of the mainstays of the `dplyr` package is merging data with the family of [join operations](https://cran.r-project.org/web/packages/dplyr/vignettes/two-table.html). Luckily, the functions are both intuitive to apply and consistent with SQL join vocabulary. The main functions are: - `inner_join(df1, df2)` - `left_join(df1, df2)` - `right_join(df1, df2)` - `full_join(df1, df2)` - `semi_join(df1, df2)` - `anti_join(df1, df2)` ] .pull-right[

 ] --- # The logic of joins We start with a simple setup of two tables `x` and `y`:

 -- The **colored columns** represent the key variables, the **gray columns** the value variables. The basic ideas of joining will generalize to multiple keys and values. -- Joining is about connecting each row in `x` to zero, one, or more rows in `y`:

 --- # The logic of joins We start with a simple setup of two tables `x` and `y`:

 The **colored columns** represent the key variables, the **gray columns** the value variables. The basic ideas of joining will generalize to multiple keys and values. Joining is about connecting each row in `x` to zero, one, or more rows in `y`:

 In an actual join, matches will be indicated with dots. The number of dots = the number of matches = the number of rows in the output. --- # The logic of joins (cont.) .pull-left-wide[ ### Inner join - An inner join matches pairs of observations when their keys are equal. - The output of an inner join is a new table that contains the key and values of both tables. - Unmatched rows are not included in the result. To be used with caution, because [it's easy to lose observations](https://twitter.com/SolomonMg/status/1436742352039669760)! ### Outer joins - Outer joins keep observations that appear in at least one of the tables. - There are three types of outer joins: - A **left join** keeps all observations in `x`. - A **right join** keeps all observations in `y`. - A **full join** keeps all observations in `x` and `y`. - Use the left join unless you have a strong reason not to do so; it preserves original observations even when there isn't a match. ] .pull-right-narrow[

 ] --- # Keys - Variables used to connect each pair of tables are called keys. - A key is a variable (or set of variables) that uniquely identifies an observation. - In simple cases, a single variable is sufficient to identify an observation (plane → `tailnum`). - In other cases, multiple variables are needed (look again at the `weather` table). -- ### Primary keys - A primary key uniquely identifies an observation in its own table. - It is either a column containing a (sometimes autogenerated and otherwise meaningless) identifier that uniquely identifies each row, or - Several substantively meaningful columns whose row values *taken together* uniquely identify each row. -- ### Foreign keys - A foreign key is a column containing primary key(s) from another table. - It is the piece of information necessary to join both tables. - Note that a variable can be both a primary *and* a foreign key. In our example, `origin` is part of the `weather` primary key, and is also a foreign key for the `airports` table. --- # Joins in R Let's perform a [left join](https://stat545.com/bit001_dplyr-cheatsheet.html#left_joinsuperheroes-publishers) on the flights and planes datasets. - *Note*: I'm going subset columns after the join, but only to keep text on the slide. -- ```r R> left_join(flights, planes) %>% + select(year, month, day, dep_time, arr_time, carrier, flight, tailnum, type, model) ``` ``` ## Joining, by = c("year", "tailnum") ``` ``` ## # A tibble: 336,776 × 10 ## year month day dep_time arr_time carrier flight tailnum type model ## <int> <int> <int> <int> <int> <chr> <int> <chr> <chr> <chr> ## 1 2013 1 1 517 830 UA 1545 N14228 <NA> <NA> ## 2 2013 1 1 533 850 UA 1714 N24211 <NA> <NA> ## 3 2013 1 1 542 923 AA 1141 N619AA <NA> <NA> ## 4 2013 1 1 544 1004 B6 725 N804JB <NA> <NA> ## 5 2013 1 1 554 812 DL 461 N668DN <NA> <NA> ## 6 2013 1 1 554 740 UA 1696 N39463 <NA> <NA> ## 7 2013 1 1 555 913 B6 507 N516JB <NA> <NA> ## 8 2013 1 1 557 709 EV 5708 N829AS <NA> <NA> ## 9 2013 1 1 557 838 B6 79 N593JB <NA> <NA> ## 10 2013 1 1 558 753 AA 301 N3ALAA <NA> <NA> ## # … with 336,766 more rows ``` --- # Joins in R (cont.) Note that `dplyr` made a reasonable guess about which columns to join on (i.e. columns that share the same name). It also told us its choices: ``` *### Joining, by = c("year", "tailnum") ``` However, there's an obvious problem here: the variable "year" does not have a consistent meaning across our joining datasets! - In one it refers to the *year of flight*, in the other it refers to *year of construction*. -- Luckily, there's an easy way to avoid this problem. - See if you can figure it out before turning to the next slide. - Try `?dplyr::join`. --- # Joins in R (cont.) You just need to be more explicit in your join call by using the `by = ` argument. - You can also rename any ambiguous columns to avoid confusion. ```r R> left_join( + flights, + planes %>% rename(year_built = year), ### Not necessary w/ below line, but helpful + by = "tailnum" ### Be specific about the joining column + ) %>% + select(year, month, day, dep_time, arr_time, carrier, flight, tailnum, year_built, type, model) %>% + head(3) ### Just to save vertical space on the slide ``` ``` ## # A tibble: 3 × 11 ## year month day dep_time arr_time carrier flight tailnum year_built type ## <int> <int> <int> <int> <int> <chr> <int> <chr> <int> <chr> ## 1 2013 1 1 517 830 UA 1545 N14228 1999 Fixed w… ## 2 2013 1 1 533 850 UA 1714 N24211 1998 Fixed w… ## 3 2013 1 1 542 923 AA 1141 N619AA 1990 Fixed w… ## # … with 1 more variable: model <chr> ``` --- # Joins in R (cont.) Last thing to mention on joins for now; note what happens if we again specify the join column... but don't rename the ambiguous "year" column in at least one of the given data frames: ```r R> left_join( + flights, + planes, ### Not renaming "year" to "year_built" this time + by = "tailnum" + ) %>% + select(contains("year"), month, day, dep_time, arr_time, carrier, flight, tailnum, type, model) %>% + head(3) ``` ``` ## # A tibble: 3 × 11 ## year.x year.y month day dep_time arr_time carrier flight tailnum type model ## <int> <int> <int> <int> <int> <int> <chr> <int> <chr> <chr> <chr> ## 1 2013 1999 1 1 517 830 UA 1545 N14228 Fixe… 737-… ## 2 2013 1998 1 1 533 850 UA 1714 N24211 Fixe… 737-… ## 3 2013 1990 1 1 542 923 AA 1141 N619AA Fixe… 757-… ``` -- Make sure you know what "year.x" and "year.y" are. Again, it pays to be specific. --- # Duplicate keys If you're lucky, keys are unique. But that's not always the case. There are two common scenarios: -- 1. One table has duplicate keys, the other hasn't. This gives us a one-to-many relationship.

 --

Both tables have duplicate keys. This is usually an error because in neither table do the keys uniquely identify an observation. When joining duplicated keys, we get all possible combinations (the Cartesian product, a many-to-many relationship):

 --- class: inverse, center, middle name: sql # SQL

 --- # What is SQL? ### Background and history - SQL (pronounced [ɛsˌkjuːˈɛl] as in S-Q-L, or [siːkwəl] as in sequel) stands for **Structured Query Language**. Initially it was called SEQUEL (Structured English Query Language), but this was dropped due to trademark issues. - It's a domain-specific language designed to query data contained in relational databases. - Initially developed at IBM by [Donald D. Chamberlin](https://en.wikipedia.org/wiki/Donald_D._Chamberlin) and [Raymond F. Boyce](https://en.wikipedia.org/wiki/Raymond_F._Boyce) in 1974. -- ### Why SQL? - While database types differ, most of the relational databases you'll encounter speak SQL. - The key skill to work with databases (outside R) is to learn how to speak SQL. Once you've mastered this, you should be able to work with any of them. - SQL is featured as a required skill in many (most?) data science job ads out there. --- # General SQL syntax ### Classes of SQL syntax - **Data query language** (DQL) to perform queries on the data [`SELECT`, `FROM`, `WHERE`] - **Data definition language** (DDL) to describe data structure and its relations (create tables, columns, define data types, keys, constraints) [`CREATE`, `ALTER`, `DROP`] - **Data manipulation language** (DML) to fill database or retrieve information from it [`SELECT`, `FROM`, `WHERE`, `INSERT`, `UPDATE`, `DELETE`] - **Data control language** (DCL) to define usage/admin rights [`GRANT`, `REVOKE`] -- ### A generic query The main SQL tool is `SELECT`, which allows you to perform queries on a table in a database. It has the generic form: ```text R> SELECT columns or computations + FROM table + WHERE condition + GROUP BY columns + HAVING condition + ORDER BY column [ASC | DESC] + LIMIT offset,count; ``` --- # General SQL syntax (cont.) .pull-left[ ### Basic rules - SQL statements start with a command describing the desired action (`SELECT`), followed by the unit on which it should be executed (`SELECT column1`), and one or more clauses (`WHERE column 2 = 1`). - Although it's customary to write all SQL statements in capital letters, SQL is actually case insensitive towards its key words. - Each SQL statement ends with a semicolon, so SQL statements can span across multiple lines. - Comments either start with `--` or have to be put in between `/*` and `*/`. ] .pull-right[
 ```text R> CREATE DATABASE database1 ; + SELECT column1 FROM table1 WHERE column2 = 1 ; + UPDATE table1 SET column1 = 1 WHERE column2 > 3 ; + INSERT INTO table1 (column1, column2) + VALUES ('rc11', 'rc12'), ('rc21', 'rc22') ; ``` ```text R> -- One line comment. + /* + Comment spanning + several lines + */ ``` ] --- # General SQL syntax (cont.) ### Notes from a dplyr user - SQL syntax is intuitive [until it isn't](https://blog.jooq.org/a-beginners-guide-to-the-true-order-of-sql-operations/). - One problem: SQL imposes a **lexical order** of operations ("order of execution") which does not necessarily match the **logical order** of operations you'd have in mind. - Conceptually, every step (like "`WHERE`") of the query transforms its input. However, the query's steps don't happen in the order they're written (e.g., `SELECT` is written first but the operation comes much later in the process). - Compare this to our `dplyr` logic: Take this, do this, then do this, etc. -- ### The good news - With `dplyr` we have a package that can effectively speak SQL for us. - Those queries (formulated with `dplyr` commands!) can then easily be submitted as SQL queries to the database (also thanks to the `DBI` package). - So, do you still have to learn SQL? Probably not, but it won't hurt! --- # More resources to get started with SQL Probably the best way to learn SQL hands-on is the interactive tutorial at [SQLBolt](https://sqlbolt.com/). Another hands-on beginner SQL tutorial is at [DataQuest](https://www.dataquest.io/blog/sql-basics). Yet another good starting point to learn about the basics of SQL lives at [Codeacademy](https://www.codecademy.com/learn/learn-sql). If you want to know more about how indexes work in SQLite, check out [this resource](https://www.sqlite.org/queryplanner.html). Finally, another good read is [10 easy steps to a complete understanding of SQL](https://blog.jooq.org/10-easy-steps-to-a-complete-understanding-of-sql/) by Lukas Eder. --- class: inverse, center, middle name: dbr # Talking to databases with R

 --- # Connecting to databases with R .pull-left-wide[ ### A database interface - DBMS implement SQL but all have somewhat different conventions. - R can connect to all major existing databases types. - The R package [`DBI`](https://cran.r-project.org/web/packages/DBI/vignettes/DBI-proposal.html) (**D**ata**B**ase **I**nterface) is a unified interface to them (car analogy: wheel, pedals). - In addition, we need a separate "driver" to connect to the database engine (car analogy: fuel (?)). ### Drivers for open-source/commercial DBs - There are various R packages that allow you to connect to particular **open-source** database types including SQLite (via [`RSQLite`](https://cran.r-project.org/web/packages/RSQLite/index.html)), MySQL ([`RMySQL`](https://cran.r-project.org/web/packages/RMySQL/index.html)), PostgreSQL ([`RPostgres`](https://cran.r-project.org/web/packages/RPostgres/index.html)), Google BigQuery ([`bigrquery`](https://cran.r-project.org/web/packages/bigrquery/index.html)), MariaDB ([`RMariaDB`](https://cran.r-project.org/web/packages/RMariaDB/index.html)), DuckDB ([`duckdb`](https://cran.r-project.org/web/packages/duckdb/index.html)), and more. - For **commercial databases** such as Microsoft SQL Server or Oracle, the [`odbc`](https://github.com/r-dbi/odbc) package provides a DBI-compliant interface to Open Database Connectivity (ODBC) drivers. ] .pull-right-small-center[

 `Credit` [db.rstudio.com](https://db.rstudio.com/) ] --- # Talking to databases .pull-left[ ### dplyr as a database interface - Good news: `dplyr` is able to interact with databases directly by translating the `dplyr` verbs into SQL queries. This convenient feature allows you to "speak" directly with the database from R. - Using `dplyr` as an interface allows you to: 1. Run data exploration routines over all of the data, instead of importing parts of the data into R. 2. Use the SQL engine to run the data transformations. In effect, computation is being pushed to the database. 3. Collect into R only a targeted dataset. 4. Keep all your code in R. There is no need to alternate between languages or tools to perform the data exploration. ] .pull-right-center[

 `Credit` [db.rstudio.com](https://db.rstudio.com/) ] --- # A hands-on database session with R Before we start, we load the `DBI` package to connect to a DBMS and the `RSQLite` package to communicate with an SQLite database. Tidyverse and `dplyr`/`dbplyr` will be used, too. Also, we will rely on `nycflights13` for some toy data sets. ```r R> library(DBI) R> library(RSQLite) R> library(tidyverse) R> library(nycflights13) ``` --- # Connecting to the database Now, let's set up a connection with an SQLite database. In fact, we will not interact with an existing one but build up our own, which will live in the memory: ```r R> con <- dbConnect(RSQLite::SQLite(), dbname = ":memory:") ``` `con` represents our database connection via which we'll interact with the database. -- The arguments to `DBI::dbConnect()` vary from database to database, but the first argument is always the database backend. For instance, it’s `RSQLite::SQLite()` for RSQLite, `RMariaDB::MariaDB()` for RMariaDB, `RPostgres::Postgres()`, and `bigrquery::bigquery()` for BigQuery. -- Also, in real life, chances are that the database lives on a server and you have to authenticate to connect to it. This could look as follows: ```r R> con <- DBI::dbConnect(RSQLite::SQLite(), + host = "mydatabase.host.com", + user = "simon", + password = "mypassword" + ) ``` --- # Filling the database Next, we upload a local data frame into the remote data source; here: our database. Note that this is specific to our (toy) example. You'll probably not have to build up your own database. ```r R> dplyr::copy_to( + dest = con, + df = nycflights13::flights, + name = "flights") ``` --- # Filling the database Next, we upload a local data frame into the remote data source; here: our database. Note that this is specific to our (toy) example. You'll probably not have to build up your own database. ```r R> dplyr::copy_to( + dest = con, + df = nycflights13::flights, + name = "flights", + temporary = FALSE, + indexes = list( + c("year", "month", "day"), + "carrier", + "tailnum", + "dest" + ) + ) ``` We can also explicitly set up indexes that will allow us to quickly process the data by day, carrier, plane, and destination. While creating the right indices is key to good database performance, in common applications this will be taken care of by the database maintainer. --- # Querying the database Now it's time to start querying our database. First, we generate a reference table from the database using `dplyr`'s `tbl`(): ```r R> flights_db <- tbl(con, "flights") ``` Note that `flights_db` is a remote source; the table is not stored in our local environment. We can use it as a "pointer" to the actual database.  --- # Querying the database The most important difference between ordinary data frames and remote database queries is that your R code is translated into SQL and executed in the database on the remote server, not in R on your local machine. When working with databases, `dplyr` **tries to be as lazy as possible**: - It never pulls data into R unless you explicitly ask for it. - It delays doing any work until the last possible moment: it collects together everything you want to do and then sends it to the database in one step. This even applies when you assign the output of a database query to an object: ```r R> tailnum_delay_db <- flights_db %>% + group_by(tailnum) %>% + summarise( + delay = mean(arr_delay), + n = n() + ) %>% + arrange(desc(delay)) %>% + filter(n > 100) ``` --- # Querying the database Laziness also has some downsides. Because there’s generally no way to determine how many rows a query will return unless you actually run it, nrow() is always NA: ```r R> nrow(tailnum_delay_db) ``` ``` ## [1] NA ``` Because you can’t find the last few rows without executing the whole query, you can’t use tail(): ```r R> tail(tailnum_delay_db) ``` ``` ## Error: tail() is not supported by sql sources ``` If you then want to pull the data into a local data frame, use `dplyr::collect()`: ```r R> tailnum_delay <- tailnum_delay_db %>% collect() R> tailnum_delay ``` ``` ## # A tibble: 1,201 × 3 ## tailnum delay n ## <chr> <dbl> <int> ## 1 <NA> NA 2512 ## 2 N0EGMQ 9.98 371 ## 3 N10156 12.7 153 ## 4 N10575 20.7 289 ## 5 N11106 14.9 129 ## 6 N11107 15.0 148 ## 7 N11109 14.9 148 ## 8 N11113 15.8 138 ## 9 N11119 30.3 148 ## 10 N11121 10.3 154 ## # … with 1,191 more rows ``` --- # Using SQL directly in R Again, because it is so cool: Yes, we can use `dplyr` syntax to do database queries! Behind the scenes, `dplyr` is translating your R code into SQL. The `dbplyr` package is doing the work for us. You can use the `show_query()` function to display the SQL code that was used to generate a queried table: ```r R> tailnum_delay_db %>% show_query() ``` ``` ## <SQL> ## SELECT * ## FROM (SELECT `tailnum`, AVG(`arr_delay`) AS `delay`, COUNT(*) AS `n` ## FROM `flights` ## GROUP BY `tailnum`) ## WHERE (`n` > 100.0) ``` If you still want to formulate SQL queries and pass them on to the DBMS, use `DBI::dbGetQuery()`: ```r R> sql_query <- "SELECT * FROM flights WHERE dep_delay > 240.0 LIMIT 5" R> dbGetQuery(con, sql_query) ``` ``` ## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time ## 1 2013 1 1 848 1835 853 1001 1950 ## 2 2013 1 1 1815 1325 290 2120 1542 ## 3 2013 1 1 1842 1422 260 1958 1535 ## 4 2013 1 1 2115 1700 255 2330 1920 ## 5 2013 1 1 2205 1720 285 46 2040 ## arr_delay carrier flight tailnum origin dest air_time distance hour minute ## 1 851 MQ 3944 N942MQ JFK BWI 41 184 18 35 ## 2 338 EV 4417 N17185 EWR OMA 213 1134 13 25 ## 3 263 EV 4633 N18120 EWR BTV 46 266 14 22 ## 4 250 9E 3347 N924XJ JFK CVG 115 589 17 0 ## 5 246 AA 1999 N5DNAA EWR MIA 146 1085 17 20 ## time_hour ## 1<PHONE_NUMBER> ## 2<PHONE_NUMBER> ## 3<PHONE_NUMBER> ## 4<PHONE_NUMBER> ## 5<PHONE_NUMBER> ``` --- # More useful resources to get started with databases ### Relational databases FTW A comprehensive overview of database interaction using R is available at [db.rstudio.com](https://db.rstudio.com/getting-started/overview). It also features a set of best practices that go beyond what we covered today. Not to mention [Databases 101](https://raw.githack.com/uo-ec607/lectures/master/16-databases/16-databases.html) by Grant McDermott, which also features example code to connect to DuckDB and Google BigQuery. Finally, there is the [introduction to `dbplyr`](https://cran.r-project.org/web/packages/dbplyr/vignettes/dbplyr.html) which comes as a package vignette. --- # And what about NoSQL databases? We haven't talked about non-relational databases so far, but you might encounter them in the future. NoSQL became popular in the 2000s as a consequence of several developments: Cost of data storage decreased, which made the need of efficient, non-redundant but complex data storage less urgent. At the same time, heterogeneity in data formats increased and coming up with well-defined schemas was difficult. NoSQL offered more flexibility. There are several types of NoSQL databases, including document DBs where documents contain pairs of fields and values (think: JSON), key-value DBs, or graph DBs. Depending on your use case, this might be exactly what you need. If you want to learn more, start [here](https://www.mongodb.com/nosql-explained) or [here](https://www.digitalvidya.com/blog/nosql-tutorial/). --- class: inverse, center, middle name: summary # Summary

 --- # Summing it up .pull-left[ Most data scientists **never design a database**. - But they almost all end up interacting with them. - Also, with database administrators (DBAs). Lots of academic data work consists of working with *bad re-inventions* of the relational database. - Looking at you, Excel users. - Looking at you, instructor. Thinking about data relationally **will help you with statistics**. - Multilevel models - Time-series - Network analysis - NLP ] .pull-right[
 | Database concept | Statistical concept | |---|---| | Table | Sample | | Column | Variable | | Row | Unit | | Value | Observation | | Foreign key relationships | Nested variables | | Many-to-many relationships | Crossed variables (possibly unbalanced) | ] --- class: center background-image: url("pics/excel-not-database-simpsons.png") background-size: contain background-color: #000000 # Databases: Maybe not the most exciting technology...

... but awesomely useful and not going away.
 --- # Coming up

 ### Assignment None! But you'll get the chance to practice databasing with R in the lab. ### Next lecture Web data and technologies - relational data structures FTW!


See also the list of recently created word clouds
|
Sherlock Holmes Shaped Cloud
|
Heart Shaped Cloud
|
|
Comments of Pacific Rim Trailer on YouTube
|
Cloud Shaped Cloud
|
|
University of Arizona Wikipedia Page
|
University of Washington Wikipedia Page
|


Fork me on GitHub
Draw2D
6.1.2
Open
Undo
Redo
Delete
About
Shape Properties
Start
Position
x:
y:
End
Position
x:
y:


Plot the reaction graph data produced by
parseModuleReaction
using the visNetwork
package.
plotReactionGraph(vertices, edges, vertexWeightColumn = NA, palette = "YlOrRd", title = NA)
| vertices | data.frame of vertex data |
|---|---|
| edges | data.frame of edge data |
| vertexWeightColumn | name of column in vertices to use for assigning colors |
| palette | any valid palette argument to
the |
a visNetwork object
The vertices
and edges
data.frames may be passed directly
as they are produced by parseModuleReaction
or additional
columns may be added as specified below.
Vertices:
title
Provides the mouseover text of a node. If unspecified this is constructed from the node id or weight column if provided.
shape
Name of a shape. Default is 'box' for reactions and 'dot' for compounds.
label
The label to appear in the node. By default this is the id for reactions and blank for compounds.
color
String representing color hex code (e.g. '#FFFFFF')
or color name (e.g. 'red'). If vertexWeightColumn
is provided
and there is no 'color' column, the color will be determined by the
weight.
Edges:
color
String representation of color as above. Default is 'black'.
arrows
Where to place arrowheads on edges. Default is 'to' directed edges and 'undefined' otherwise. Valid values include for 'to', 'from', 'middle' or any combination with any separating symbol.
parseModuleReaction
, col_numeric
,
visNetwork


CloneCanvas ( src, dst )
Sets the canvas dst as a reference to the canvas src.
Example:
CloneCanvas ( 0, 1 )


You or Someone has been downloading this too many times. To Fix:
* Wait for 24 Hours and try again. * Try clearing your browser's history and cache, and try again. * Request for an alternative link. * Dont cry.


WITNESS LIST Criminal Justice Committee February 14, 2001 - 1:00P SB 16 FOR: Braley, Jana Elementary School Counselor (Self), Fort Worth, TX Cook, James Asst. District Attorney, Tarrant County (Self), Fort Worth, TX Hyde, Heath Asst. District Attorney, Dallas (TX Council on Family Violence), Dallas, TX Williams, Robert (Self), Dallas, TX AGAINST: Hampton, Keith Attorney (TX Criminal Defense Lawyers Assn.), Austin, TX ON: Buchanan, Bree Public Policy Director (TX Council on Family Violence), Austin, TX Registering, but not testifying: For: Boyles, Sherry (TX Association Against Sexual Assualt), Austin, TX Against: Harrell, William Attorney (ACLU - Texas), Austin, TX SB 17 FOR: Hyde, Heath Asst. District Attorney, Dallas (TX Council on Family Violence), Dallas, TX Williams, Robert (Self) ON: Buchanan, Bree Public Policy Director (TX Council on Family Violence), Austin, TX Registering, but not testifying: For: Boyles, Sherry (TX Association Against Sexual Assualt) Heaton, Chris Executive Director (TX Municipal Police Assn.), Austin, TX McLaughlin, James General Counsel (TX Police Chiefs Association), Austin, TX Against: Hampton, Keith Attorney (TX Criminal Defense Lawyers Assn.), Austin, TX SB 18 FOR: Chalmers, Debborah (Self), The Woodlands, TX Hyde, Heath Assistant Disrict Attorney (TX Council on Family Violence), Dallas, TX Kline, Sandy Sgt. - Houston Police (Houston Police Department), Houston, TX ON: Buchanan, Bree Public Policy Director (TX Council on Family Violence), Austin, TX Registering, but not testifying: For: Boyles, Sherry (TX Association Against Sexual Assualt), Austin, TX Heaton, Chris Executive Director (TX Municipal Police Association), Austin, TX McLaughlin, James General Counsel (TX Police Chiefs Association), Austin, TX Welsh, Dinah Director, State Affairs (TX Hospital Association), Austin, TX SB 107 FOR: Frasier, Margo Sheriff, Travis County (Travis County Sheriff's Office), Austin, TX Grayson, Mark Editor (St. Austin School Parents), Ausitn, TX Hilgers, William Attorney (Self), Austin, TX ON: Hampton, Keith Attorney (TX Criminal Defense Lawyers Association), Austin, TX Registering, but not testifying: For: Boyles, Sherry (TX Association Against Sexual Assualt), Austin, TX Cimino, F.S. Pastoral Council Member (St. Austin Church), Austin, TX Eastman, Mitzi (Self), Austin, TX Fickel, Ann Director of Legislation (TX Classroom Teachers Association), Austin, TX McHone, Michael (University Area Parents) McLaughlin, James General Counsel (TX Police Chiefs Association), Austin, TX Rodriguez, Fernando Attorney (St. Austin School Parents), Austin, TX SB 219 FOR: Ott, Judge Patricia Justice of the Peace (Justices of the Peace & Constables Associaiton of Texas), Round Rock, TX Registering, but not testifying: For: Heaton, Chris Executive Director (TX Municipal Police Association), Austin, TX Williams, John (Justice of the Peace & Constable Association of TX), Robstown, TX SB 240 AGAINST: Hampton, Keith Attorney (TX Criminal Defense Lawyers Association), Austin, TX Henson, Scott (ACLU of TX), Austin, TX ON: Jermsted, Todd Attorney (Self) Registering, but not testifying: Against: Harrell, William Attorney (ACLU of TX), Austin, TX SB 347 ON: Hightower, Allen Executive Director (Correctional Managed Health Care Committee), Huntsville, TX Keith, Matthew Pharmacist (UTMB Correctional Managed Care), Huntsville, TX Patterson, M.D., Patti (Texas Tech University - Health Sciences Center), Lubbock, TX Sapp, Allen Assistant Director (Correctional Managed Health Care Committee), Huntsville, TX Registering, but not testifying: On: Coombes, Phyllis Financial Analyst (Comptroller's Office), Austin, TX Laible, James Vice President (Texas Tech University - Health Sciences Center), Lubbock, TX Mendez, Maria Financial Analyst (Comptroller's Office), Austin, TX SB 402 FOR: Mica, Tara Reilly Lobbyist (National Rifle Association), Fairfax, VA Registering, but not testifying: For: Carpenter, Bill Asst. Superintendent (Houston Independent School District/ TX School Alliance), Houston, TX Fickel, Ann Director of Legislation (TX Classroom Teachers Association), Austin, TX Patterson, Jerry (Self), Austin, TX Ramos, Joe Government Relations (TX Association of School Boards), Austin, TX SB 455 FOR: Heaton, Chris Executive Director (TX Municipal Police Association), Austin, TX Registering, but not testifying: For: Rodriguez, John Police Officer (TX Municipal Police Association), San Angelo, TX Ybarbo, Ray Police Officer (San Antonio Police Department), San Antonio, TX Against: Westbrook, Steve Executive Director (Sheriff's Association of TX), Austin, TX SB 483 ON: Heaton, Chris Executive Director (TX Municipal Police Association), Austin, TX Oakley, Keith Consultant (TX Association of Licensed Investigators), Terrell, TX Registering, but not testifying: For: Westbrook, Steve Executive Director (Sheriff's Association of TX), Austin, TX


{% extends "base_with_margins.html" %} {% load extras %} {% load static %} {% load adv_cache %} {% block title %}{{CITY_COUNCIL_NAME}} Committees{% endblock %} {% block content %} {% cache 600 committees_wrapper 'committees' %}
| Committee | Chairperson(s) | Members |
|---|---|---|
| {{ committee.name | committee_topic_only }} | {% for chair in committee.chairs %} {{ chair.person.link_html|safe }} {% endfor %} | {{ committee.all_members | length }} |
| Subcommittee | Chairperson(s) | Members |
|---|---|---|
| {{ committee.name | committee_topic_only}} | {% for chair in committee.chairs %} {{ chair.person.link_html|safe }} {% endfor %} | {{ committee.memberships.all | length }} |
| Task Force | Chairperson(s) | Members |
|---|---|---|
| {{ committee.name }} | {% for chair in committee.chairs.all %} {{ chair.person.link_html|safe }} {% endfor %} | {{ committee.memberships.all | length }} |
{{CITY_COUNCIL_NAME}} is currently composed of {{committees.count }} Committees, {{subcommittees | length }} Subcommittees and {{taskforces | length }} Task Forces.
{% else %}{{CITY_COUNCIL_NAME}} is currently composed of {{committees | length }} Committees{% if subcommittees %} and {{subcommittees | length }} Subcommittees{% endif %}.
{% endif %}


VIEWS
02
London, UK
November 2012
https://brickdlane.wordpress.com/2012/11/20/
Playlist
Banksy on Brick Lane
VIEWS
02
https://brickdlane.wordpress.com/2012/11/20/
Playlist
Banksy on Brick Lane
VIEWS
02
http://streetartnyc.org/blog/2014/10/30/writer-and-photographer-ray-mock-chronicles-banksys-new-york-residency/
Playlist
Banksy in The Big Apple
VIEWS
10
https://allaussiehiphop.com/2011/08/30/banksys-works-heading-to-sydney-as-part-of-the-outpost-project/
Playlist
Banksy Down Under
VIEWS
18
https://www.theurbandeveloper.com/worlds-greatest-street-art-graffiti/
Playlist
West Coast Banksy
VIEWS
02
http://avax.news/touching/Graffiti.html
Playlist
Banksy of Tokyo
VIEWS
12
https://richardtullochwriter.com/tag/street-art/
Playlist
Ireland's Great Banksy
VIEWS
05
http://www.ananasamiami.com/2010/05/banksys-north-america-tour-continues.html
Playlist
Cuban Cigars & Banksy
VIEWS
07
http://www.justluxe.com/lifestyle/arts/feature-1965194.php
Playlist
The Banksy Wall


ACME Stock Co
Demo User
Settings
Help
| Client 2
Portfolio Overview
Client Details
PORTFOLIO VALUE
CASH BALANCE
ROI
$0.00
$0.00
0%
Stock Portfolio
.
Symbol
Shares
Commision
Price
Date Quoted
Total
IBM
100
$7.99
$155.31
2020-02-12
$15,531.00
AAPL
100
$7.99
$155.31
2020-02-12
$15,531.00
Feedback
Sell Stock
Buy Stock
Name
Client 2
Email
<EMAIL_ADDRESS>Cell Phone
1-512-555-1212
Preferred Contact Method
Text message
Loyalty Level
Basic
Account Balance
$50.00
Commissions Paid
$0.00
Commission for Next Trade
$9.99
Free Trades Available
0
Retrieved 02/12/2020 7:21:45 AM


Country Population: People add/remove Project v.0.1
Home
(current)
About
Services
Contact
Logout
Just using Functions
Search
Add Person
This region population
ID
FirstName
Family Name
Age
Address
Marriage
Nationality
Education
Change Values
ID
Name
Family Name
Age
Address
Marriage
Nationality
Eduction
Edit
Delete
Total Pages: [[${totalItems}]]
[[${i}]]
[[${i}]]
Next
Next
Last
Last


Mixing Color for the Web with Sass
by Justin McDowell · 6 Comments
We use color to influence mood, create an environment, and tell a story. 125 years ago, impressionist painter Claude Monet changed the way we think about color by describing light’s role in it. His observations have largely been lost on design for the web—but a preprocessor like Sass offers us unprecedented power to mix tints and shades that will imbue our palettes with more nuance.


title = "Sign up" url = "/" layout = "default" == == {{ form_open({ request: 'onHandleSignup' }) }} Name: Address: City: Device Id: {{ form_close() }}


bgvideo
Home
Services
Events
Gallery
Contact
Latest News :: Welcome To Sample Project
Terms of Service
Privacy Policy
Legal & Disclaimer
Designed and Developed by
Siva Phaneendra Babu


Walk into the future with me! - Kornel Labun
About me
I am Kornel, a sole architect of this website, therefore all that you find here is about me and my meandering endeavors.  Currently a postdoc, working with ValenLab at the University of Bergen. Do you like to fixate on some matter for a while? I do this quite often. Actually, all the time. If I manage to bring to completion any of my projects I will share them here. I hope you will find my fixations as entertaining as I did. Currently, the dream is to build a knowledge graph using all scientific publications, solve off-target search problems in CRISPR, and use Nanopore to know more about translation regulation.
Resume
Here is my pdf resume.
Figure 1. My growth over years. Measured using my subjective and personal perspective, and normalized toward my all time performance.
Projects
Below you can find a selection of some of my projects, the topics that interest me are usually related to bioinformatics, blockchain, RPGs, manga & japan, design patterns, game theory, longevity, and origin of life. Projects I have put here are just some things I think are worth sharing, maybe you will find them useful for some purpose!
Contact
If you wish to contact me, here are some links:
- e-mail: moc.liamg@ nubal.lenrok
- https://github.com/JokingHero
- Google Scholar
- ResearchGate


Music Object Version 1.1.0
RELEASE DATE: 1997-01-11
Version 1.1.0 is the first publicly released version of the Music Object API.


If true the view is potentially visible, if false the view is impossible to be seen.
If true the view will be displayed, if false the view is hidden.
The reference is null if this view has no parent (for exemple a view not in the view traversal stack).
child
The child view to add.?priority
The sorting priority of the child view to add. Higher numbers will appear towards the top of the view stack. Default value is 0.A view can have multiple children, and when you add a child to a view, it is automatically connected to the parent node through its parent property.
The children are still in memory, if you want to free them completely call view.dispose() from their owner object.
The view itself is still in memory, if you want to free it completely call dispose().


Ellipticity
Ellipticity is the ratio between the long and short axis of an ellipse. It is a deformation measure.


Match Play. 0-0 to 5. Should Red double? If doubled, should White take?
pips Red=141 White=144
(CorrectAnswer= 0/0, ErrorScore= 0)
The key to this problem is the match score.
For money this a perfectly correct double and a perfectly correct take.
At this score White should drop although it is quite close. Going 0-4 (Crawford) down in a five-point match is too big a risk. This is an important match play concept that I see ignored time and again.
For money this a perfectly correct double and a perfectly correct take.
At this score White should drop although it is quite close. Going 0-4 (Crawford) down in a five-point match is too big a risk. This is an important match play concept that I see ignored time and again.
Cube analysis 2-ply cubeless equity +0.509 (Money: +0.531) 0.634 0.375 0.008 - 0.366 0.113 0.006 Cubeful equities: 1. Double, take +0.868 2. Double, pass +1.000 (+0.132) 3. No double +0.725 (-0.142) Proper cube action: Double, take


Before Change
distribution over classes for a given token in the sentence.
text_field.index(self.vocab)
padding_lengths = text_field.get_padding_lengths()
array_input = text_field.as_array(padding_lengths)
model_input = arrays_to_variables(array_input, add_batch_dimension=True)
output_dict = self.forward(tokens=model_input)
// Remove batch dimension, as we only had one input.
After Change
instance = Instance({"tokens": text_field})
instance.index_fields(self.vocab)
model_input = arrays_to_variables(instance.as_array_dict(), add_batch_dimension=True)
output_dict = self.forward(**model_input)
// Remove batch dimension, as we only had one input.


--- title: EasyRPG Player menu_weight: 3 --- <% # The following variables can be quite handy ;) ver="0.7.0" oldver="<IP_ADDRESS>" ver_master="0.7.0" ver_pkg="0.7" ver_lcf="0.7.0" oldver_lcf="0.6.2" %>
Release date: 2021-10-29. Release notes, changes since <%= oldver %>: ">Player, ">liblcf.
Windows 32 bit executable,
Windows 64 bit executable
These are meant as a drop-in replacement for the RPG_RT.exe and require at least Windows Vista.
Some Distributions (Fedora, NixOS, Guix...) provide packages nowadays, so use your distribution's package if possible!
Linux amd64 executable
This is a debug build mainly used to find bugs, there are no guarantees it works on your installation.
It is not optimized, so you likely want to
build Player from source code for faster game play.
Arch Linux, Debian, Ubuntu and openSUSE packages
These are provided by us on the Open Build Service.
Snapcraft Linux packages
These builds are not directly controlled by us.
macOS 64-bit (Intel) App Bundle
Player is also available on homebrew, use this command in your terminal:
brew install easyrpg-player
The App is hosted on Google Play.
You can also scan this QRCode with your device to access it directly:
Locally hosted App (.apk)
Installing from "unknown sources" must be activated in the Android settings.
Available on the libretro buildbot. (or use the RetroArch core downloader)
The libretro core can be executed by RetroArch and other libretro frontends.
For Windows and macOS we offer our own builds.
Supported platforms:
Wii (.dol)
This needs a homebrew-enabled console with "Homebrew Channel" installed. Games are searched in the same directory as the dol.
Nintendo 3DS (3dsx),
Nintendo 3DS (CIA)
This needs a homebrew-enabled console with "Homebrew Launcher" (for 3dsx) or a custom firmware (for CIA) installed. The CIA searches for games in sdcard:/3ds/easyrpg-player
PlayStation Vita
This needs a homebrew-enabled console by running the HENkaku exploit. Games are searched in ux0:/data/easyrpg-player.
Nintendo Switch
This needs a homebrew-enabled console with "Homebrew Launcher" installed. Games are searched in the same directory as the nro.
The 3DS, PSVita and Switch ports have originally been contributed by Rinnegatamante.
Open Pandora (.pnd)
Maintained by ptitSeb in the OpenPandora repository.
The AROS, AmigaOS4 and Morphos ports are maintained by BSzili on his website.
Other external packages (e.g. BSD/Unix) can be found on Repology.
Windows RT (ARM) (0.5.3)
This needs a device running in test-signing mode. 0.5.3 is the last release on this platform, since it is dead.
GCW Zero (.opk) (0.6.1)
0.6.1 is the last release on this platform, because the official toolchain is too old. Though we have built 0.6.2 with an updated toolchain, this requires non-trivial updates of the device. So we do not provide this version anymore.
Changes since <%= ver %>: Player, liblcf
Disclaimer: these are the newest builds, representing the current development state.
However, they might contain new bugs, so they are not recommended for general use.
Windows 32 bit executable,
Windows 64 bit executable
These are meant as a drop-in replacement for the RPG_RT.exe and require at least Windows Vista.
Flatpak (amd64) (recommended)
Linux amd64 executable
This is a debug build mainly used to find bugs, there are no guarantees it works on your installation.
It is not optimized, so you likely want to
build Player from source code for faster game play.
Locally hosted App (.apk)
Installing from "unknown sources" must be activated in the Android settings.
Windows 32 bit core,
Windows 64 bit core
These cores require at least Windows Vista.
Wii (.dol)
This needs a homebrew-enabled console with "Homebrew Channel" installed. Games are searched in the same directory as the dol.
Nintendo 3DS (3dsx),
Nintendo 3DS (CIA)
This needs a homebrew-enabled console with "Homebrew Launcher" (for 3dsx) or a custom firmware (for CIA) installed. The CIA searches for games in sdcard:/3ds/easyrpg-player.
PlayStation Vita
This needs a homebrew-enabled console by running the HENkaku exploit. Games are searched in ux0:/data/easyrpg-player.
Nintendo Switch
This needs a homebrew-enabled console with "Homebrew Launcher" installed. Games are searched in the same directory as the nro.
The 3DS, PSVita and Switch Ports have originally been contributed by Rinnegatamante.


 Our growth service uses real Instagram marketing techniques to get you real, targeted results. Our Instagram growth packages have been tested and proven to work. Located in the marketing capital of the world (New York City), we work with celebrities and brands from various industries! Everyone knows the benefits of having a large number of followers and we can help you.
Your identity will never be revealed. Your account will only be managed by one account manager and will not change hands and “get lost in the shuffle”. Please keep in mind that building a presence on Instagram is not an overnight task. It takes months of engagement
Our Instagram growth packages have been tested and proven to work. We are so sure you will love the growth and quality of followers our services will bring you that we stand fully behind our work. If your Instagram account doesn’t grow in your first 30-days, we will increase your following month’s follower growth rates free of charge until you are satisfied with our work. That means you can purchase with confidence knowing that, even if it takes a little longer than planned, you WILL get the right results.
  No questions asked.


Komodo
feed-github
Auto feed for all activities in GitHub. New notification as they happen in real-time.
36 messages
Between 09-Dec-19 12:00 AM and 10-Dec-19 12:00 AM
GitHub
BOT
09-Dec-19 06:09 AM
jl777
[jl777/SuperNET] Pull request opened: #1154 Beta season3
jl777
[SuperNET:beta_season3] 112 new commits
`c49ab96`
Update IP NN - etszombi
`d5584e4`
Add interest in native LP_withdraw path - jl777
`ac4498f`
Merge pull request #1084 from etszombi/patch-3 - jl777
`3537959`
One vin:2 support - jl777
`d46d59d`
Select utxo2 - jl777
jl777
[jl777/SuperNET] Pull request closed: #1154 Beta season3
jl777
[SuperNET:beta] 84 new commits
`0bcbad7`
Merge pull request #26 from jl777/beta - blackjok3rtt
`b93822a`
season change over - blackjok3rtt
`ca697c4`
fix json - blackjok3rtt
`5230232`
remove VRSC - blackjok3rtt
`222bd88`
add some seeds and remove dpow for COQUI. - blackjok3rtt
GitHub
BOT
09-Dec-19 11:07 AM
Mixa84
[komodo:importgateway] 1 new commit
`bfd2aae`
Fix - Mixa84
[komodo] macOS-latest CI success on importgateway
Mixa84
[komodo:FSM] 18 new commits
`1e5e7e4`
check notaryproofvintx - ca333
`437151f`
Merge pull request #230 from KomodoPlatform/che... - DeckerSU
`4871965`
update github actions - ca333
`38fb5a4`
update github actions - ca333
`5263f6d`
update github actions - ca333
Mixa84
[komodo] Branch importgateway was force-pushed to
c836402
Compare changes
[komodo] macOS-latest CI success on FSM
[komodo] macOS-latest CI success on importgateway
GitHub
BOT
09-Dec-19 11:20 AM
Mixa84
[Mixa84/komodo] Pull request opened: #44 Refactor of gateway and importgateway
GitHub
BOT
09-Dec-19 11:53 AM
[komodo] Win Build success on importgateway
[komodo] Win Build success on FSM
GitHub
BOT
09-Dec-19 12:11 PM
Mixa84
[Mixa84/komodo] Pull request closed: #44 Refactor of gateway and importgateway
Mixa84
[komodo:FSM] 1 new commit
`532b034`
Refactor of gateway and importgateway (#44) - Mixa84
GitHub
BOT
09-Dec-19 12:12 PM
Mixa84
[jl777/komodo] Pull request opened: #1777 Refactor of gateway and importgateway (#44)
[komodo] macOS-latest CI success on FSM
GitHub
BOT
09-Dec-19 12:43 PM
[komodo] ubuntu-18.04 CI failure on importgateway
[komodo] GitHub Actions checks failure on importgateway
[komodo] ubuntu-18.04 CI success on importgateway
[komodo] GitHub Actions checks success on importgateway
[komodo] ubuntu-18.04 CI success on importgateway
[komodo] GitHub Actions checks success on importgateway
GitHub
BOT
09-Dec-19 01:02 PM
[komodo] Win Build success on FSM
GitHub
BOT
09-Dec-19 01:54 PM
[komodo] ubuntu-18.04 CI success on FSM
[komodo] GitHub Actions checks success on FSM
GitHub
BOT
09-Dec-19 02:37 PM
jl777
[jl777/komodo] Pull request closed: #1777 Refactor of gateway and importgateway (#44)
jl777
[komodo:FSM] 2 new commits
`532b034`
Refactor of gateway and importgateway (#44) - Mixa84
`ed2f868`
Merge pull request #1777 from Mixa84/FSM - jl777
GitHub
BOT
09-Dec-19 02:37 PM
Mixa84
[Mixa84/komodo] branch deleted: pegsCC
GitHub
BOT
09-Dec-19 02:37 PM
[komodo] macOS-latest CI success on FSM
GitHub
BOT
09-Dec-19 03:24 PM
[komodo] Win Build success on FSM
GitHub
BOT
09-Dec-19 04:17 PM
[komodo] ubuntu-18.04 CI success on FSM
[komodo] GitHub Actions checks success on FSM
GitHub
BOT
09-Dec-19 05:27 PM
ca333
[jl777/SuperNET] Fork created: ca333/SuperNET
GitHub
BOT
09-Dec-19 05:28 PM
gcharang
[developer-docs:gcharang] 2 new commits
`fd3a5aa`
adds global ratio details to pegs doc - gcharang
`498c1cd`
comments out unfinished addition to atomicDEX g... - gcharang
GitHub
BOT
09-Dec-19 11:16 PM
ca333
[jl777/SuperNET] Pull request opened: #1155 sync beta
RC for dpow v0.1 (dpow2)


No need of AI in Education.“But why?”
Artificial Intelligence in education might sound a little strange to some people
about how AI can teach us ‘how to learn’ or ‘what to learn’. Basically, we have been in a bubble
of limited thoughts where haven’t we used that big data
that is already Read More
Transformation in Computational Linguistics with Sanskrit- A Brand New
Approach to NLP
In the past two decades, computational linguistics has evolved swiftly. There
are different fields where the use of natural language processing, where a large corpus for
natural language... Read
More


Locations
Search
Create
Location Name:
Description:
Keywords:
Address:
City:
State:
Zipcode:
Phone number:
Email:
Search
Location Name:
Description:
Keywords:
Address Line 1:
Address Line 2:
City:
State:
Zipcode:
Phone number:
Fax number:
Email:
Create


_A remote synchornized IRIDA project where you are listed as the synchronization user has failed to sync because your token is no longer valid._
_See docs here_


Returns a list of the ones that were satisfied from the cache. These don"t require building.
if not vts:
return [], []
cached_vts = []
uncached_vts = OrderedSet(vts)
Returns a pair (cached, uncached) of VersionedTargets that were
satisfied/unsatisfied from the cache.
return self.do_check_artifact_cache(vts)
def do_check_artifact_cache(self, vts, post_process_cached_vts=None):
Checks the artifact cache for the specified list of VersionedTargetSets.


rss_feed Subscribe to our RSS or ATOM feed and get the latest news about Tools from the Web DevRel team in your favorite feed reader!


Please enter minimum 3 chars
Please enter correctly Zip-Code
Please enter correctly Phone
Please enter correctly Email


{% extends "layout.html" %} {% block title %} Selen-kaa {% endblock %} {% block main_container %}
text text text text text text text text text
second data second data second data second data
some data some data some data some data
third data
forth data
fifth data
sixth data


Let's advance the world technology tree and build great systems that work for everyone while surviving the test of time.
| General Email |<EMAIL_ADDRESS>|
| Company | Guidebolt Inc. |
| https://ca.linkedin.com/in/yoonseokang | |
| Github | https://github.com/openyk |
| https://twitter.com/yoonseokang | |
| Location | Ontario, Canada |
| Citizenship | Canada |
Broad and deep technical skillset. Excellent quality-sense.
Special: system design, improvement intuition, UI/UX practical-power-user perspective
Typing Speed: 110 WPM (2015 certificate)
Reading Speed: 1000+ WPM
Mechanical Design (part and assembly modeling): Autodesk Inventor, Autodesk Inventor CAM
Advanced Fabrication: 5-Axis CNC Machining
Electrical Design (schematic and PCB design): KiCAD
Infra Electrical: 10kVA work inspected and approved by the Electrical Safety Authority in Canada
Software (general and embedded development): C, C++, Python, PHP, Linux
Only a few generations ago, slavery and serfdom were considered normal. What do we consider normal today that will be looked back in disgust by future generations?
People tend to ignore serious problems until they themselves get seriously hurt. People tend to accept the status quo for as long as they themselves live in relative comfort.
I applaud current efforts for clean energy and automotive/industrial electrification.
Whoever was part of planning the Global Innovation Clusters in Canada did a really good job.
Childhood: Born in 1993, moved to Canada with my parents and little sister in 2000. Good memories playing Yugioh; learning about card valuation and fair trades. Hundreds of hours dropped off at Chapters to read game/hobby books (this is how I got into chess and combat robotics). I used to be a super-committed Runescape miner, then got scammed when trying to sell 1M gp worth of coal (I cried).
Adolescence: Started hobby robotics. Learned mechatronics through early internet resources and applied engineering books. Appreciated the community pioneering of Jim Smenstowski (Team Nightmare) and robust designs of Carlo Bertocchini (Biohazard robot). Fond memories of Super Smash Bros Melee at a friend's house regularly after school.
Adulthood: Empathy matured. Systems engineering interest expanded to economics. Hobby technnical enthusiasm evolved to passion for real product development with free-information/open-source/modular-compatibility/DIY-friendly ideals. Over 10 years of technical experience, major 1st hand frustrations with so many bad products/designs out there (all the way from when I was 14, my new fishing rod broke on the first cast) that they really compelled me to design and build things properly! Tired of non-constructive office politics, getting effective communication is a relief. I like the principles of being kind yet firm, push-up focused systems (instead of pull-down), bridging abstract ideals to specific experiences, and discovering the good truths that move you forward.
List ignores a ton of datasheets, application notes, etc.
Games that I've completed or reasonably progressed into. Intended to convey my history; should not be considered a list of recommendations.
Ongoing Games: Dota 2, Counterstrike: Global Offensive, Hunt: Showdown, Beyond All Reason, Omega Strikers, Magic: The Gathering Arena, Yugioh: Master Duel, Chess
Single-Player: I enjoy games with new ideas, stories, and feel.
RTS: I used to play a lot of custom games in Starcraft 1 and 2. Really enjoyed the history-driven campaign in Age of Empires 2. Recently discovered Beyond All Reason and it's the first time I'm consistently having fun with RTS PVP.
Childhood: Pokemon Yellow, Pokemon Silver, Dragon Warrior Monsters 2, The Legend of Zelda: Oracle of Seasons, Chessmaster: 10th Edition
Other:
The value of a personal website seems limited because it is temporary (dies with the person), niche value (fragments of the person's ideas), and harder to search for (than finding profiles on typical social platforms).
That said, they create opportunities for interested people to efficiently learn about a person at a deeper level, passively building rapport and natural unity.


Boxxi is a custom video player plugin written in plain/vanilla JavaScript.
It's supported in all modern browsers.
No dependency required.
Check out the docs @GitHub


CrimPapers
Articles published on 23 March 2018
On 23 March 2018, 3 new articles were published in 3 journals.
Are Children Involved in Cyberbullying Low on Empathy? A Systematic Review and Meta-Analysis of Research on Empathy Versus Different Cyberbullying Roles
"They Chase Us Like Dogs": Exploring the Vulnerabilities of "Ladyboys" in the Cambodian Sex Trade
A Quasi-Experiment Testing A Public Participation Process For Designing and Implementing An Enforcement Program Among Minorities


Landing Page
Special Projects
This story is part of a series of specially designed stories that represents some of the best journalism The Post has to offer. Check out the rest of the special projects here.
MEAGAN HALL
08.24.17
If you miss Athens as much as the next Bobcat, you probably made sure to keep up on everything going on at Ohio University as well as your favorite places in town. From President Nellis’ first day to a rainbow horse, a lot has occurred throughout the summer and has been shared on Twitter. Here’s a recap of the best tweets of the summer from Athens.
President Duane Nellis’ first day as OU’s new president was June 12. Events of his day were shared on Twitter via @OHIOPrezOffice. The president’s twitter account shared a video recap at the end of Nellis’ busy first day including him getting his ID photo, meeting future Bobcats at orientation, and touring the campus.
OU’s organizations and staff members, such as Dean of Students Jenny Hall-Jones, also shared tweets welcoming the new president.
The Ohio University Police Department shared a photo of Chloe and K9 Brody. For her birthday, Chloe asked for items to make care packages for the K9s instead of presents.
Hall-Jones also shared tweets from incoming freshman and their parents about their excitement for when they become a Bobcat with #BSOOHIO. Current Bobcats remembered their orientation and her excitement while leading the “OU, oh yeah” chant and warning students never to wear Miami gear on campus.
A tweet by @csattrell featured his own version of the Hollywood star meme featuring OU’s Student Body President Landen Lama.
Other members of the Student Senate shared their summer adventures with #OHIOSenateSummer. The tweets included photos of the senate members on all their summer adventures.
Athens Pride Fest occurred on June 9 to 11 with events going on around campus including a Pride Rally, a concert, and a drag show. However, what people were not planning on seeing was a rainbow horse walking through College Green for the festivities.
Lastly, in case you missed the crazy antics of Court Street at night, Red Brick Tavern tweeted about a customer who had an interesting question for the workers.
Landing Page
This story is part of a series of specially designed stories that represents some of the best journalism The Post has to offer. Check out the rest of the special projects here.


This function will draw the display object to the RenderTexture.
In versions of Phaser prior to 2.4.0 the second parameter was a Phaser.Point object. This is now a Matrix allowing you much more control over how the Display Object is rendered. If you need to replicate the earlier behavior please use Phaser.RenderTexture.renderXY instead.
If you wish for the displayObject to be rendered taking its current scale, rotation and translation into account then either pass null
, leave it undefined or pass displayObject.worldTransform
as the matrix value.


Id: 246
Name: Mrs. Pancakes
Status: Alive
Species: Human
Type:
Gender: Female
OriginName: Earth (C-137)
OriginUrl: https://rickandmortyapi.com/api/location/1
LocationName: Mr. Goldenfold's dream
LocationUrl: https://rickandmortyapi.com/api/location/18
Image: https://rickandmortyapi.com/api/character/avatar/246.jpeg
Episode: ['https://rickandmortyapi.com/api/episode/2', 'https://rickandmortyapi.com/api/episode/27']
Url: https://rickandmortyapi.com/api/character/246
Created: 2017-12-30T18:09:39.304Z


{% extends 'products/product_base.html' %} {% load static %} {% block css %}
By creating an account you agree to our User Agreement and acknowledge reading our User Privacy Notice.


Click here to download the default configuration file, which can be viewed in R or in Notepad.
If a customized configuration file is not uploaded prior to running the QC function, the default thresholds and units listed below will be used for the four QC flag tests.
The QC function runs each data point in the input file through the following four ‘flag’ tests.
| Flag Test | Description |
|---|---|
| Unrealistic values (‘Gross range’) | Entries are flagged if values are above or below upper and lower limits |
| Spikes | Entries are flagged if adjacent points change by more than ‘x’ amount |
| Rate of change (RoC) | Entries are flagged if the RoC exceeds a given threshold (e.g., = 3 st dev within 25 hrs) |
| Flat line | Entries are flagged if a certain number of consecutive measurements are within a certain amount of each other (e.g., >10 consecutive temperature measurements are within 0.01 degrees C of one another) |
The ‘flag’ tests were selected based on the following references:
U.S. Integrated Ocean Observing System, 2017. Manual for the Use of Real-Time Oceanographic Data Quality Control Flags, Version 1.1. 43 pp. https://cdn.ioos.noaa.gov/media/2017/12/QARTOD-Data-Flags-Manual_Final_version1.1.pdf
Wagner, R.J., Boulger, R.W., Oblinger, C.J., Smith, B.A., 2006. Guidelines and Standard Procedures for Continuous Water-quality Monitors: Station Operation, Record Computation, and Data Reporting. U.S. Geological Survey Techniques and Methods 1-D3, 51 pp. þ 8 attachments. http://pubs.water.usgs.gov/tm1d3.
R assigns the following flags to each data point:
The default thresholds were developed for temperature and hydrologic data in cool, medium to high gradient stream RMN sites in the eastern US. However, we had very little data to go on at the time and we strongly encourage people with one or more years of continuous data at a site to evaluate the performance of the QC test thresholds for each parameter at each site and customize the configuration file as needed (for more information, see the ‘Customize QC flag test thresholds’ tab).
Units are an important consideration as well. The default puts hydrologic parameters (like water level) in feet, which RMN partners generally seem to prefer for streams. However, with lakes, the RMN partners prefer to use meters. If units are changed from feet to meters but there isn’t a corresponding change in thresholds, the QC tests lose relevance.
This table shows the default QC test thresholds for each parameter as of 6/17/2021.
| Parameter | Units | Flag | Gross | Spike | Rate of Change | Flat Line |
|---|---|---|---|---|---|---|
| Temperature, Water | deg C | Fail | >30 or <-2 | =1.5 (±) | >30 consecutive measurements within 0.01 units of one another | |
| Suspect | >25 or <-0.1 | =1.0 (±) | =3 stdev within 25 hrs | >20 consecutive measurements within 0.01 units of one another | ||
| Temperature, Air | deg C | Fail | >38 or <-25 | =10 (±) | >20 consecutive measurements within 0.01 units of one another | |
| Suspect | >35 or <-23 | =8 (±) | =3 stdev within 25 hrs | >15 consecutive measurements within 0.01 units of one another | ||
| Sensor Depth or Water Level | feet | Fail | > 10^5 or < -1 | =5 (±) | >60 consecutive measurements within 0 units of one another | |
| Suspect | > 1000 or < 1 | =3 (±) | =3 stdev within 25 hrs | >20 consecutive measurements within 0 units of one another | ||
| Discharge | ft3/s | Fail | > 10^5 or < -1 | =10^4 (±) | > 60 consecutive measurements within 0.01 units of one another | |
| Suspect | > 1000 or < 1 | =10^3 (±) | =3 stdev within 25 hrs | > 30 consecutive measurements within 0.01 units of one another | ||
| Dissolved oxygen | mg/L | Fail | > 20 or < 1 | >= 10 (±) | > 60 consecutive measurements within 0.01 units of one another | |
| Suspect | > 18 or < 2 | >= 5 (±) | =3 stdev within 25 hrs | > 30 consecutive measurements within 0.01 units of one another | ||
| Conductivity | uS/cm | Fail | > 1500 or < 10 | >= 10 (±) | > 60 consecutive measurements within 0.01 units of one another | |
| Suspect | >1200 or < 20 | >= 5 (±) | =3 stdev within 25 hrs | > 30 consecutive measurements within 0.01 units of one another | ||
| pH | SU | Fail | > 12 or < 3 | >= 10 (±) | > 60 consecutive measurements within 0.01 units of one another | |
| Suspect | > 11 or < 4 | >= 5 (±) | =3 stdev within 25 hrs | > 30 consecutive measurements within 0.01 units of one another | ||
| Turbidity | NTU | Fail | > 10^5 or < -1 | =10^4 (±) | > 60 consecutive measurements within 0.01 units of one another | |
| Suspect | > 1000 or < -1 | =10^3 (±) | =3 stdev within 25 hrs | > 30 consecutive measurements within 0.01 units of one another | ||
| Pressure, Water | psi | Fail | >17 or <13 | =0.7 (±) | >15 consecutive measurements within 0.001 units of one another | |
| Suspect | >16.8 or <13.5 | =0.5 (±) | =3 stdev within 25 hrs | >10 consecutive measurements within 0.001 units of one another | ||
| Barometric Pressure, Air | psi | Fail | >15 or <13 | =0.25 (±) | >15 consecutive measurements within 0.001 units of one another | |
| Suspect | >14.8 or <13 | =0.15 (±) | =3 stdev within 25 hrs | >10 consecutive measurements within 0.001 units of one another | ||
| Chlorophyll a | g/cm3 | Fail | > 10^5 or < -1 | =10^4 (±) | > 60 consecutive measurements within 0.01 units of one another | |
| Suspect | > 1000 or < -1 | =10^3 (±) | =3 stdev within 25 hrs | > 30 consecutive measurements within 0.01 units of one another |


The second approach is to have Prolog be the master and Tk the slave. This is suitable when heavy processing is done in the Prolog code and Tk is used mostly to display the state of the computation in some way rather than as a traditional GUI; i.e. during computation Prolog often makes calls to Tk to show some state, but the user rarely interacts with the application.
In our Prolog+Tcl/Tk setting this involves the following steps:
tk_new/2
tcl_eval/3
tcl_eval
to update the Tk display
tcl_eval
Again it its purest form, Prolog makes calls to Tcl, but Tcl does not
make calls to Prolog. The result of a call to Tcl is either passed back
through the Result
variable of a tcl_eval/3
call.
A good example of this is the Tcl/Tk display for our 8-queens problem, that we saw earlier; see Queens Display.
We will now fill out the example by presenting the Prolog master part. The Prolog program calculates a solution to the 8-queens problem and then makes calls Tcl/Tk to display the solution. In this way Tcl/Tk is the slave, just being used as a simple display.
We have already seen the Tcl/Tk part, but here is the Prolog part for generating a solution and displaying it:
:- use_module(library(tcltk)). :- use_module(library(lists)). go :- tk_new([name('SICStus+Tcl/Tk - Queens')], Tcl), tcl_eval(Tcl, 'source queens.tcl', _), tk_next_event(Tcl, Event), queens(8, Qs), reverse(L, LR), tcl_eval(Tcl, [show_solution, br(LR)], _), fail. go. queens(N, Qs) :- range(1, N, Ns), queens(Ns, [], Qs). queens(UnplacedQs, SafeQs, Qs) :- select(Q, UnplacedQs, UnplacedQs1), \+ attack(Q, SafeQs), queens(UnplacedQs1, [Q|SafeQs], Qs). queens([], Qs, Qs). attack(X, Xs) :- attack(X, 1, Xs). attack(X, N, [Y|_Ys]) :- X is Y + N. attack(X, N, [Y|_Ys]) :- X is Y - N. attack(X, N, [_Y|Ys]) :- N1 is N + 1, attack(X, N1, Ys). range(M, N, [M|Ns]) :- M < N, M1 is M + 1, range(M1, N, Ns). range(N, N, [N]). :- go.
All this simply does it to create a Tcl/Tk interpreter, load the Tcl
code for displaying queens into it, generate a solution to the 8-queens
problem as a list of integers, and then calls show_solution/2
in
the Tcl interpreter to display the solution. At the end of first
clause for go/0
is a fail clause that turns
go/0
into a failure driven loop. The result of this is that the
program will calculate all the solutions to the 8-queens problem,
displaying them rapidly one after the other, until there are none left.


Sweet Free Stuff
///////////////////////////////////////////
Free Sample of Sheer Tint Broad Spectrum SPF 45
Posted: 04 Jul 2016 10:43 AM PDT
http://feedproxy.google.com/&edburner&utm_medium=email
Scroll down and click Take survey. Free Sample of Sheer Tint Broad Spectrum
SPF 45
The post Free Sample of Sheer Tint Broad Spectrum SPF 45 appeared first on
SweetFreeStuff.com.
///////////////////////////////////////////
Free Wink Naturals 30 Day Sleep Aid Sample
Posted: 04 Jul 2016 06:38 AM PDT
http://feedproxy.google.com/&edburner&utm_medium=email
Free Wink Naturals 30 Day Sleep Aid Sample
The post Free Wink Naturals 30 Day Sleep Aid Sample appeared first on
SweetFreeStuff.com.
///////////////////////////////////////////
Free Slurpee at 7-Eleven on July 11th
Posted: 03 Jul 2016 07:20 AM PDT
http://feedproxy.google.com/&edburner&utm_medium=email
On July 11th from 11am and 7pm get a free Slurpee drink at 7-Eleven for
7-Eleven Day.
The post Free Slurpee at 7-Eleven on July 11th appeared first on
SweetFreeStuff.com.


Class: ActiveStorage::Service::Configurator
Do not use. This class is for internal use only.
| Relationships & Source Files | |
| Inherits: | Object |
| Defined in: | activestorage/lib/active_storage/service/configurator.rb |
Class Method Summary
Instance Attribute Summary
- #configurations readonly
Instance Method Summary
- #build(service_name)
- #config_for(name) private
- #resolve(class_name) private
Constructor Details
.new(configurations) ⇒ Configurator
# File 'activestorage/lib/active_storage/service/configurator.rb', line 11
def initialize(configurations) @configurations = configurations.deep_symbolize_keys end
Class Method Details
.build(service_name, configurations)
[ GitHub ]# File 'activestorage/lib/active_storage/service/configurator.rb', line 7
def self.build(service_name, configurations) new(configurations).build(service_name) end
Instance Attribute Details
#configurations (readonly)
[ GitHub ]# File 'activestorage/lib/active_storage/service/configurator.rb', line 5
attr_reader :configurations
Instance Method Details
#build(service_name)
[ GitHub ]# File 'activestorage/lib/active_storage/service/configurator.rb', line 15
def build(service_name) config = config_for(service_name.to_sym) resolve(config.fetch(:service)).build( **config, configurator: self, name: service_name ) end
#config_for(name) (private)
[ GitHub ]# File 'activestorage/lib/active_storage/service/configurator.rb', line 23
def config_for(name) configurations.fetch name do raise "Missing configuration for the #{name.inspect} Active Storage service. Configurations available for #{configurations.keys.inspect}" end end
#resolve(class_name) (private)
[ GitHub ]# File 'activestorage/lib/active_storage/service/configurator.rb', line 29
def resolve(class_name) require "active_storage/service/#{class_name.to_s.underscore}_service" ActiveStorage::Service.const_get(:"#{class_name.camelize}Service") rescue LoadError raise "Missing service adapter for #{class_name.inspect}" end


Layer0 Terraform Provider Reference#
Terraform is an open-source tool for provisioning and managing infrastructure. If you are new to Terraform, we recommend checking out their documentation.
Layer0 has built a custom provider for Layer0. This provider allows users to create, manage, and update Layer0 entities using Terraform.
Prerequisites#
- Terraform v0.11+ (download), accessible in your system path.
Install#
Download a Layer0 v0.8.4+ release.
The Terraform plugin binary is located in the release zip file as terraform-provider-layer0
.
Copy this terraform-provider-layer0
binary into $HOME/.terraform.d/plugins/
- and you're done!
For further information, see Terraform's documentation on installing a Terraform plugin here.
Getting Started#
- Checkout the
Terraform
section of the Guestbook walkthrough here. - We've added some tips and links to helpful resources in the Best Practices section below.
Provider#
The Layer0 provider is used to interact with a Layer0 API. The provider needs to be configured with the proper credentials before it can be used.
Example Usage#
# Add 'endpoint' and 'token' variables
variable "endpoint" {}
variable "token" {}
# Configure the layer0 provider
provider "layer0" {
endpoint = "${var.endpoint}"
token = "${var.token}"
skip_ssl_verify = true
}
Argument Reference#
The following arguments are supported:
Note
The endpoint
and token
variables for your layer0 api can be found using the l0-setup endpoint command.
endpoint
- (Required) The endpoint of the layer0 apitoken
- (Required) The authentication token for the layer0 apiskip_ssl_verify
- (Optional) If true, ssl certificate mismatch warnings will be ignored
API Data Source#
The API data source is used to extract useful read-only variables from the Layer0 API.
Example Usage#
# Configure the api data source
data "layer0_api" "config" {}
# Output the layer0 vpc id
output "vpc id" {
val = "${data.layer0_api.config.vpc_id}"
}
Attribute Reference#
The following attributes are exported:
prefix
- The prefix of the layer0 instancevpc_id
- The vpc id of the layer0 instancepublic_subnets
- A list containing the 2 public subnet ids in the layer0 vpcprivate_subnets
- A list containing the 2 private subnet ids in the layer0 vpc
Deploy Data Source#
The Deploy data source is used to extract Layer0 Deploy attributes.
Example Usage#
# Configure the deploy data source
data "layer0_deploy" "dpl" {
name = "my-deploy"
version = "1"
}
# Output the layer0 deploy id
output "deploy_id" {
val = "${data.layer0_deploy.dpl.id}"
}
Argument Reference#
The following arguments are supported:
name
- (Required) The name of the deployversion
- (Required) The version of the deploy
Attribute Reference#
The following attributes are exported:
name
- The name of the deployversion
- The version of the deployid
- The id of the deploy
Environment Data Source#
The Environment data source is used to extract Layer0 Environment attributes.
Example Usage#
# Configure the environment data source
data "layer0_environment" "env" {
name = "my-environment"
}
# Output the layer0 environment id
output "environment_id" {
val = "${data.layer0_environment.env.id}"
}
Argument Reference#
The following arguments are supported:
name
- (Required) The name of the environment
Attribute Reference#
The following attributes are exported:
id
- The id of the environmentname
- The name of the environmentsize
- The size of the instances in the environmentmin_count
- The current number instances in the environmentos
- The operating system used for the environmentami
- The AMI ID used for the environment
Load Balancer Data Source#
The Load Balancer data source is used to extract Layer0 Load Balancer attributes.
Example Usage#
# Configure the load balancer source
data "layer0_load_balancer" "lb" {
name = "my-loadbalancer"
environment_id = "${data.layer0_environment.env.environment_id}"
}
# Output the layer0 load balancer id
output "load_balancer_id" {
val = "${data.layer0_load_balancer.lb.id}"
}
Argument Reference#
The following arguments are supported:
name
- (required) The name of the load balancerenvironment_id
- (required) The id of the environment the load balancer exists in
Attribute Reference#
The following attributes are exported:
id
- The id of the load balancername
- The name of the load balancerenvironment_id
- The id of the environment the load balancer exists inenvironment_name
- The name of the environment the load balancer exists inprivate
- Whether or not the load balancer is privateurl
- The URL of the load balancer
Service Data Source#
The Service data source is used to extract Layer0 Service attributes.
Example Usage#
# Configure the service data source
data "layer0_service" "svc" {
name = "my-service"
environment_id = "${data.layer0_environment.env.environment_id}"
}
# Output the layer0 service id
output "service_id" {
val = "${data.layer0_service.svc.id}"
}
Argument Reference#
The following arguments are supported:
name
- (required) The name of the serviceenvironment_id
- (required) The id of the environment the service exists in
Attribute Reference#
The following attributes are exported:
id
- The id of the servicename
- The name of the serviceenvironment_id
- The id of the environment the service exists inenvironment_name
- The name of the environment the service exists inscale
- The current desired scale of the service
Deploy Resource#
Provides a Layer0 Deploy.
Performing variable substitution inside of your deploy's json file (typically named Dockerrun.aws.json
) can be done through Terraform's template_file.
For a working example, please see the sample Guestbook application
Example Usage#
# Configure the deploy template
data "template_file" "guestbook" {
template = "${file("Dockerrun.aws.json")}"
vars {
docker_image_tag = "latest"
}
}
# Create a deploy using the rendered template
resource "layer0_deploy" "guestbook" {
name = "guestbook"
content = "${data.template_file.guestbook.rendered}"
}
Argument Reference#
The following arguments are supported:
name
- (Required) The name of the deploycontent
- (Required) The content of the deploy
Attribute Reference#
The following attributes are exported:
id
- The id of the deployname
- The name of the deployversion
- The version number of the deploy
Environment Resource#
Provides a Layer0 Environment
Example Usage#
# Create a new environment
resource "layer0_environment" "demo" {
name = "demo"
size = "m3.medium"
min_count = 0
user_data = "echo hello, world"
os = "linux"
ami = "ami123"
}
Argument Reference#
The following arguments are supported:
name
- (Required) The name of the environmentsize
- (Optional, Default: "m3.medium") The size of the instances in the environment. Available instance sizes can be found heremin_count
- (Optional, Default: 0) The minimum number of instances allowed in the environmentuser-data
- (Optional) The user data template to use for the environment's autoscaling group. See the cli reference for the default template.os
- (Optional, Default: "linux") Specifies the type of operating system used in the environment. Options are "linux" or "windows".ami
- (Optional) A custom AMI ID to use in the environment. If not specified, Layer0 will use its default AMI ID for the specified operating system.
Attribute Reference#
The following attributes are exported:
id
- The id of the environmentname
- The name of the environmentsize
- The size of the instances in the environmentcluster_count
- The current number instances in the environmentsecurity_group_id
- The ID of the environment's security groupos
- The operating system used for the environmentami
- The AMI ID used for the environment
Load Balancer Resource#
Provides a Layer0 Load Balancer
Example Usage#
# Create a new load balancer
resource "layer0_load_balancer" "guestbook" {
name = "guestbook"
environment = "demo123"
private = false
port {
host_port = 80
container_port = 80
protocol = "http"
}
port {
host_port = 443
container_port = 443
protocol = "https"
certificate = "cert"
}
health_check {
target = "tcp:80"
interval = 30
timeout = 5
healthy_threshold = 2
unhealthy_threshold = 2
}
idle_timeout = 300
cross_zone = false
}
Argument Reference#
The following arguments are supported:
name
- (Required) The name of the load balancerenvironment
- (Required) The id of the environment to place the load balancer inside ofprivate
- (Optional) If true, the load balancer will not be exposed to the public internetport
- (Optional, Default: 80:80/tcp) A list of port blocks. Ports documented belowhealth_check
- (Optional, Default:{"TCP:80" 30 5 2 2}
) A health_check block. Health check documented belowidle_timeout
- (Optional, Default: 60) The idle timeout of the load balancer in secondscross_zone
- (Optional, Default:true
) A boolean for whether or not to enable cross-zone load balancing
Ports (port
) support the following:
host_port
- (Required) The port on the load balancer to listen oncontainer_port
- (Required) The port on the docker container to route toprotocol
- (Required) The protocol to listen on. Valid values areHTTP, HTTPS, TCP, or SSL
certificate
- (Optional) The name of an SSL certificate. Only required if theHTTP
orSSL
protocol is used.
Healthcheck (health_check
) supports the following:
target
- (Required) The target of the check. Valid pattern isPROTOCOL:PORT/PATH
, wherePROTOCOL
values are:HTTP
,HTTPS
-PORT
andPATH
are requiredTCP
,SSL
-PORT
is required,PATH
is not supported
interval
- (Required) The interval between checks.timeout
- (Required) The length of time before the check times out.healthy_threshold
- (Required) The number of checks before the instance is declared healthy.unhealthy_threshold
- (Required) The number of checks before the instance is declared unhealthy.
The Idle Timeout (idle_timeout
) is managed by the load balancer and is triggered when no data is sent over a connection for the specified time period. If no data has been sent or received by the time that the idle timeout period elapses, the load balancer closes the connection. See the following documentation for more information: https://docs.aws.amazon.com/elasticloadbalancing/latest/application/application-load-balancers.html#connection-idle-timeout
Attribute Reference#
The following attributes are exported:
id
- The id of the load balancername
- The name of the load balancerenvironment
- The id of the environment the load balancer exists inprivate
- Whether or not the load balancer is privateurl
- The URL of the load balancer
Service Resource#
Provides a Layer0 Service
Example Usage#
# Create a new service
resource "layer0_service" "guestbook" {
name = "guestbook"
environment = "environment123"
deploy = "deploy123"
load_balancer = "loadbalancer123"
scale = 3
}
Argument Reference#
The following arguments are supported:
name
- (Required) The name of the serviceenvironment
- (Required) The id of the environment to place the service inside ofdeploy
- (Required) The id of the deploy for the service to runload_balancer
(Optional) The id of the load balancer to place the service behindscale
(Optional, Default: 1) The number of copies of the service to run
Attribute Reference#
The following attributes are exported:
id
- The id of the servicename
- The name of the serviceenvironment
- The id of the environment the service exists indeploy
- The id of the deploy the service is runningload_balancer
- The id of the load balancer the service is behind (ifload_balancer
was set)scale
- The current desired scale of the service
Best Practices#
- Always run
Terraform plan
beforeterraform apply
. This will show you what action(s) Terraform plans to make before actually executing them. - Use variables to reference secrets.
Secrets can be placed in a file named
terraform.tfvars
, or by settingTF_VAR_*
environment variables. More information can be found here. - Use Terraform's
remote
command to backup and sync yourterraform.tfstate
file across different members in your organization. Terraform has documentation for using S3 as a backend here. - Terraform modules allow you to define and consume reusable components.
- Example configurations can be found here


Top Stories
Belarus sprinter given Polish humanitarian visa
This is text to show that this page is created for while training.
🕓 3 hours ago
Tokyo 2020: Simone Biles will return to action
🕓 1 day ago.
Canada beat USA for the first time in 20 years
🕓 3 hours ago


This is bold text
This is bold text
This is italic text
This is italic text
Strikethrough
Blockquotes can also be nested...
...by using additional greater-than signs right next to each other...
...or with spaces between arrows.
Unordered
+
, -
, or *
Ordered
Or:
1.
Start numbering with offset:
Loose lists?
foo
bar
Inline code
Indented code
// Some comments
line 1 of code
line 2 of code
line 3 of code
Block code "fences"
Sample text here...
Syntax highlighting
var foo = function (bar) {
return bar++;
};
console.log(foo(5));
| Tag | Use |
|---|---|
| p | Paragraph |
| table | Table |
| em | Emphasis |
Left/right aligned columns
| Project | Stars |
|---|---|
| React | 80 759 |
| Vue.js | 73 322 |
| sse-channel | 50 |
Autoconverted link https://github.com/remarkjs/react-markdown
Like links, Images also have a footnote style syntax
With a reference later in the document defining the URL location:
Yeah, hard breaks
can be useful too.
Some characters, like æ, & and similar should be handled properly.
Does anyone actually like the fact that you can embed HTML in markdown?
, as long as it doesn't contain any attributes. If you have weird ordering on your tags, it won't work either. It does support nested tags, however. And with therehype-raw
plugin, it can now properly handle HTML! Which is pretty sweet.
Cool, eh?