text:
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Chapter 8. SQL-Invoked Routines</title>
<link href="../docbook.css" rel="stylesheet" type="text/css">
<meta content="DocBook XSL-NS Stylesheets V1.74.0" name="generator">
<meta name="keywords" content="Hsqldb, HyperSQL, SQL">
<meta name="keywords" content="Hsqldb, HyperSQL, Database, JDBC, Java">
<link rel="home" href="index.html" title="HyperSQL User Guide">
<link rel="up" href="index.html" title="HyperSQL User Guide">
<link rel="prev" href="dataaccess-chapt.html" title="Chapter 7. Data Access and Change">
<link rel="next" href="triggers-chapt.html" title="Chapter 9. Triggers">

 	Chapter 8. SQL-Invoked Routines

Chapter 7. Data Access and Change 		 Chapter 9. Triggers

Chapter 8. SQL-Invoked Routines

Fred Toussi

The HSQL Development Group

$Revision: 3643 $

Copyright 2010 Fred Toussi. Permission is granted to distribute
      this document without any alteration under the terms of the HSQLDB
      license. Additional permission is granted to the HSQL Development Group
      to distribute this document with or without alterations under the terms
      of the HSQLDB license.

$Date: 2010-06-06 23:04:17 -0400 (Sun, 06 Jun 2010) $

Table of Contents

SQL Language Routines (PSM)

Routine Statements

Compound Statement

Variables

Handlers

Assignment Statement

Select Statement : Single Row

Formal Parameters

Iterated Statements

Conditional Statements

Return Statement

Control Statements

Routine Polymorphism

Returning Data From Routines

Java Language Routines (SQL/JRT)

Polymorphism

Java Language Procedures

Legacy Support

SQL Language Aggregate Functions

Definition of Aggregate Functions

SQL PSM Aggregate Functions

Java Aggregate Functions

Routine Definition

Routine Characteristics

SQL-invoked routines are functions and procedures called from SQL.
  HyperSQL 2.0 supports routines conforming to two parts of the SQL Standard.
  Routines written in the SQL language are supported in conformance to SQL/PSM
  (Persistent Stored Modules) specification. Routines written in Java are
  supported in (loose) conformance to SQL/JRT specification. In addition,
  HyperSQL’s previous non-standard support for calling Java routines without
  prior method definition is retained and enhanced in the latest version by
  extending the SQL/JRT specification.

HyperSQL also supports user defined aggregate functions written in the
  SQL language. This feature is an extension to the SQL Standard.

SQL-invoked routines are schema objects. Naming and referencing
  follows conventions common to all schema objects. The same routine name can
  be defined in two different schemas and used with schema-qualified
  references.

A routine is either a procedure or a function.

A function:

is defined with CREATE FUNCTION

always returns a value

does not modify the data in the database

is called as part of an SQL statement

can have parameters

can be polymorphic

A procedure:

is defined with CREATE PROCEDURE

can return one or more values

can modify the data in the database

is called separately, using the CALL statement

can have parameters

can be polymorphic

Definition of routine signature and characteristics, name resolution
  and invocation are all implemented uniformly for routines written in SQL or
  Java.

SQL Language Routines (PSM)

The PSM (Persistent Stored Module) specification extends the SQL
    language to allow definition of both SQL Function and SQL procedure bodies
    with the same structure and the same control statements (such as
    conditional and loop statements) with minor exceptions.

The routine body is a SQL statement. In its simplest form, the body
    is a single SQL statement. A simple example of a function is given
    below:

CREATE FUNCTION an_hour_before (t TIMESTAMP)
  RETURNS TIMESTAMP
  RETURN t - 1 HOUR

An example of the use of the function in an SQL statement is given
    below:

SELECT an_hour_before(event_timestamp) AS notification_timestamp, event_name FROM events;

A simple example of a procedure is given below:

CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname VARCHAR(50))
  MODIFIES SQL DATA
  INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP)

The procedure inserts a row into an existing table with the
    definition given below:

CREATE TABLE customers(id INTEGER GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR(50), lastname VARCHAR(50), added TIMESTAMP);

An example of the use of the procedure is given below:

CALL new_customer('JOHN', 'SMITH');

The routine body is often a compound statement. A compound statement
    can contain one or more SQL statements, which can include control
    statements, as well as nested compound statements.

Routine Statements

The following SQL Statements can be used only in routines.

<handler declaration>

<assignment statement>

<compound statement>

<case statement>

<if statement>

<while statement>

<repeat statement>

<for statement>

<loop statement>

<iterate statement

<leave statement>

<signal statement>

<resignal statement>

<return statement>

<select statement: single
      row>

The following SQL Statements can be used in procedures but not in
      functions.

<call statement>

<delete statement>

<insert statement>

<update statement>

<merge statement>

As shown in the examples below, the formal parameters and the
      variables of the routine can be used in statements, similar to the way a
      column reference is used.

Compound Statement

A compound statement is enclosed in a BEGIN / END block with
      optional labels. It can contain one or more <SQL variable
      declaration> or <handler
      declaration> before at least one SQL statement. The BNF is
      given below:

<compound statement> ::= [ <beginning
      label> <colon> ] BEGIN [[NOT] ATOMIC] [{<SQL variable
      declaration> <semicolon>} ...] [{<handler declaration>
      <semicolon>}...] {<SQL procedure statement>
      <semicolon>} ... END [ <ending label> ]

An example of a simple compound statement body is given below. It
      performs the common task of inserting related data into two table. The
      IDENTITY value that is automatically inserted in the first table is
      retrieved using the IDENTITY() function and inserted into the second
      table.

CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname  VARCHAR(50), address VARCHAR(100))
  MODIFIES SQL DATA
    BEGIN ATOMIC
    INSERT INTO customers VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP);
    INSERT INTO addresses VALUES (DEFAULT, IDENTITY(), address);
  END

Variables

A <variable declaration> defines the name
      and data type of the variable and, optionally, its default value. In the
      next example, a variable is used to hold the IDENTITY value. In
      addition, the formal parameters of the procedure are identified as input
      parameters with the use of the optional IN keyword. This procedure does
      exactly the same job as the procedure in the previous example.

CREATE PROCEDURE new_customer(IN firstname VARCHAR(50), IN lastname VARCHAR(50), IN address VARCHAR(100))
  MODIFIES SQL DATA
  BEGIN ATOMIC
    DECLARE temp_id INTEGER;
    INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP);
    SET temp_id = IDENTITY();
    INSERT INTO ADDRESSES VALUES (DEFAULT, temp_id, address);
  END

The BNF for variable declaration is given below:

<SQL variable declaration> ::= DECLARE
      <variable name list> <data type> [DEFAULT <default
      value>]

<variable name list> ::= <variable name> [
      { <comma> <variable name> }... ]

Examples of variable declaration are given below. Note that in a
      DECLARE statement with multiple comma-separated variable names, the type
      and the default value applies to all the variables in the list:

  BEGIN ATOMIC
    DECLARE temp_zero DATE;
    DECLARE temp_one, temp_two INTEGER DEFAULT 2;
    DECLARE temp_three VARCHAR(20) DEFAULT 'no name';
    -- more statements ...
    SET temp_zero = DATE '2010-03-18';
    SET temp_two = 5;
    -- more statements ...
  END

Handlers

A <handler declaration> defines the
      course of action when an exception or warning is raised during the
      execution of the compound statement. A compound statement may have one
      or more handler declarations. These handlers become active when code
      execution enters the compound statement block and remain active in any
      sub-block and statement within the block. The handlers become inactive
      when code execution leaves the block.

In the previous example, if an exception is thrown during the
      execution of either SQL statement, the execution of the compound
      statement is terminated and the exception is propagated and thrown by
      the CALL statement for the procedure. A handler declaration can resolve
      the thrown exception within the compound statement without propagating
      it, and allow the execution of the <compound statement> to
      continue.

In the example below, the UNDO handler declaration catches any
      exception that is thrown during the execution of the compound statement
      inside the BEGIN / END block. As it is an UNDO handler, all the changes
      to data performed within the compound statement (BEGIN / END) block are
      rolled back. The procedure then returns without throwing an
      exception.

CREATE PROCEDURE NEW_CUSTOMER(IN firstname VARCHAR(50), IN lastname VARCHAR(50), IN address VARCHAR(100))
    MODIFIES SQL DATA
  label_one: BEGIN ATOMIC
    DECLARE temp_id INTEGER;
    DECLARE UNDO HANDLER FOR SQLEXCEPTION LEAVE label_one;
    INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP);
    SET temp_id = IDENTITY();
    INSERT INTO ADDRESSES VALUES (DEFAULT, temp_id, address);
  END

Other types of hander are CONTINUE and EXIT handlers. A CONTINUE
      handler ignores any exception and proceeds to the next statement in the
      block. An EXIT handler terminates execution without undoing the data
      changes performed by the previous (successful) statements.

The conditions can be general conditions, or specific conditions.
      Among general conditions that can be specified, SQLEXCEPTION covers all
      exceptions, SQLWARNING covers all warnings, while NOT FOUND covers the
      not-found condition, which is raised when a DELETE, UPDATE, INSERT or
      MERGE statement completes without actually affecting any row.
      Alternatively, one or more specific conditions can be specified
      (separated with commas) which apply to specific exceptions or warnings
      or classes or exceptions or warnings. A specific condition is specified
      with SQLSTATE <value>, for example SQLSTATE
      'W_01003' specifies the warning raised after a SQL statement is executed
      which contains an aggregate function which encounters a null value
      during execution. An example is given below which activates the handler
      when either of the two warnings is raised:

DECLARE UNDO HANDLER FOR SQLSTATE 'W_01003', 'W_01004' LEAVE label_one;

The BNF for <handler declaration> is
      given below:

<handler declaration> ::= DECLARE {UNDO |
      CONTINUE | EXIT} HANDLER FOR {SQLEXCEPTION | SQLWARNING | NOT FOUND} | {
      SQL_STATE <state value> [, ...]} [<SQL procedure
      statement>];

A handler declaration may specify an SQL procedure statement to be
      performed when the handler is activated. When an exception occurs, the
      example below performs the UNDO as in the previous example, then inserts
      the (invalid) data into a separate table.

DECLARE UNDO HANDLER FOR SQLEXCEPTION
    INSERT INTO invalid_customers VALUES(firstanme, lastname, address);

The <SQL procedure statement> is required
      by the SQL Standard but is optional in HyperSQL. If the execution of the
      <SQL procedure statement> specified in the
      handler declaration throws an exception itself, then it is handled by
      the handlers that are currently active. The <SQL procedure
      statement> can itself be a compound statement with its own
      handlers.

Assignment Statement

The SET statement is used for assignment. It can be used flexibly
      with rows or single values. The BNF is given below:

<assignment statement> ::= <singleton
      variable assignment> | <multiple variable
      assignment>

<singleton variable assignment> ::= SET
      <assignment target> <equals operator> <assignment
      source>

<multiple variable assignment> ::= SET
      (<variable or parameter>, ...) = <row value
      expression>

In the example below, the result of the SELECT is assigned to two
      OUT or INOUT arguments. The SELECT must return one row. If it returns
      more than one, an exception is raised. If it returns no row, no change
      is made to ARG1 and ARG2.

SET (arg1, arg2) = (SELECT col1, col2 FROM atable WHERE id = 10);

In the example below, the result of a function call is assigned to
      VAR1.

SET var1 = SQRT(var2);

Select Statement : Single Row

A special form of SELECT can also be used for assigning values
      from a query to one or more arguments or variables. This works similar
      to a SET statement that has a SELECT statement as the source.

SELECT : SINGLE ROW

select statement: single row

<select statement: single row> ::= SELECT [
      <set quantifier> ] <select list> INTO <select target
      list> <table expression>

<select target list> ::= <target
      specification> [ { <comma> <target specification> }...
      ]

Retrieve values from a specified row of a table and assign the
      fields to the specified targets. The example below has an identical
      effect to the example of SET statement given above.

SELECT col1, col2 INTO arg1, arg2 FROM atable WHERE id = 10;

Formal Parameters

Each parameter of a procedure can be defined as IN, OUT or INOUT.
      An IN parameter is an input to the procedure and is passed by value. The
      value cannot be modified inside the procedure body. An OUT parameter is
      a reference for output. An INOUT parameter is a reference for both input
      and output. An OUT or INOUT parameter argument is passed by reference,
      therefore only a dynamic parameter argument or a variable within an
      enclosing procedure can be passed for it. The assignment statement is
      used to assign a value to an OUT or INOUT parameter.

In the example below, the procedure is declared with an OUT
      parameter.

CREATE PROCEDURE new_customer(OUT newid INT, IN firstname VARCHAR(50), IN lastname VARCHAR(50), IN address VARCHAR(100))
  MODIFIES SQL DATA
  BEGIN ATOMIC
    DECLARE temp_id INTEGER;
    INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP);
    SET temp_id = IDENTITY();
    INSERT INTO ADDRESSES VALUES (DEFAULT, temp_id, address);
    SET newid = temp_id;
  END

In the SQL session, or in the body of another stored procedure, a
      variable must be assigned to the OUT parameter. After the procedure
      call, this variable will hold the new identity value that was generated
      inside the procedure.

In the example below, a session variable,
      the_new_id is declared. After the call to
      new_customer, the value for the identity is stored in
      the_new_id variable. This is returned via the next
      CALL statement. Alternatively, the_new_id can be used
      as an argument to another CALL statement.

DECLARE the_new_id INT DEFAULT NULL;
CALL new_customer(the_new_id, 'John', 'Smith', '10 Parliament Square');
CALL the_new_id;

Iterated Statements

Various iterated statements can be used in routines. In these
      statements, the <SQL statement list> consists
      of one or more SQL statements. The <search
      condition> can be any valid SQL expression of BOOLEAN
      type.

<loop statement> ::= [ <beginning label>
      <colon> ] LOOP <SQL statement list> END LOOP [ <ending
      label> ]

<while statement> ::= [ <beginning label>
      <colon> ] WHILE <search condition> DO <SQL statement
      list> END WHILE [ <ending label> ]

<repeat statement> ::= [ <beginning label>
      <colon> ]

REPEAT <SQL statement list> UNTIL <search
      condition> END REPEAT [ <ending label>

In the example below, a multiple rows are inserted into a table in
      a WHILE loop:

loop_label: WHILE my_var > 0 DO
  INSERT INTO CUSTOMERS VALUES (DEFAULT, my_var);
  SET my_var = my_var - 1;
  IF my_var = 10 THEN SET my_var = 8; END IF;
  IF my_var = 22 THEN LEAVE loop_label; END IF;
END WHILE loop_label;

Conditional Statements

There are two types of CASE ... WHEN statement and the IF ... THEN
      statement.

CASE WHEN

case when statement

The simple case statement uses a <case
      operand> as the predicand of one or more predicates. For
      the right part of each predicate, it specifies one or more SQL
      statements to execute if the predicate evaluates TRUE. If the ELSE
      clause is not specified, at least one of the search conditions must be
      true, otherwise an exception is raised.

<simple case statement> ::= CASE <case
      operand> <simple case statement when clause>... [ <case
      statement else clause> ] END CASE

<simple case statement when clause> ::= WHEN
      <when operand list> THEN <SQL statement
      list>

<case statement else clause> ::= ELSE <SQL
      statement list>

A skeletal example is given below. The variable var_one is first
      tested for equality with 22 or 23 and if the test evaluates to TRUE,
      then the INSERT statement is performed and the statement ends. If the
      test does not evaluate to TRUE, the next condition test, which is an IN
      predicate, is performed with var_one and so on. The statement after the
      ELSE clause is performed if none the previous tests returns TRUE.

CASE var_one
  WHEN 22, 23 THEN INSERT INTO t_one ...;
  WHEN IN (2, 4, 5) THEN DELETE FROM t_one WHERE ...;
  ELSE UPDATE t_one ...;
  END CASE

The searched case statement uses one or more search conditions,
      and for each search condition, it specifies one or more SQL statements
      to execute if the search condition evaluates TRUE. An exception is
      raised if there is no ELSE clause and none of the search conditions
      evaluates TRUE.

<searched case statement> ::= CASE <searched
      case statement when clause>... [ <case statement else clause> ]
      END CASE

<searched case statement when clause> ::= WHEN
      <search condition> THEN <SQL statement
      list>

The example below is partly a rewrite of the previous example, but
      a new condition is added:

CASE WHEN var_one = 22 OR var_one = 23 THEN INSERT INTO t_one ...;
  WHEN var_one IN (2, 4, 5) THEN DELETE FROM t_one WHERE ...;
  WHEN var_two IS NULL THEN UPDATE t_one ...;
  ELSE UPDATE t_one ...;
  END CASE

IF

if statement

The if statement is very similar to the searched case statement.
      The difference is that no exception is raised if there is no ELSE clause
      and no search condition evaluates TRUE.

<if statement> ::= IF <search condition>
      <if statement then clause> [ <if statement elseif clause>...
      ] [ <if statement else clause> ] END IF

<if statement then clause> ::= THEN <SQL
      statement list>

<if statement elseif clause> ::= ELSEIF
      <search condition> THEN <SQL statement
      list>

<if statement else clause> ::= ELSE <SQL
      statement list>

Return Statement

The RETURN statement is required and used only in functions. The
      body of a function is either a RETURN statement, or a compound statement
      that contains a RETURN statement.

RETURN

return statement

<return statement> ::= RETURN <return
      value>

<return value> ::= <value expression> |
      NULL

Return a value from an SQL function. If the function is defined
      as RETURNS TABLE, then the value is a TABLE expression such as RETURN
      TABLE(SELECT ...) otherwise, the value expression can be any scalar
      expression. In the examples below, the same function is written with or
      without a BEGIN END block. In both versions, the RETURN value is a
      scalar expression.

CREATE FUNCTION an_hour_before_max (e_type INT)
  RETURNS TIMESTAMP
  RETURN (SELECT MAX(event_time) FROM atable WHERE event_type = e_type) - 1 HOUR

CREATE FUNCTION an_hour_before_max (e_type INT)
  RETURNS TIMESTAMP
  BEGIN ATOMIC
    DECLAR max_event TIMESTAMP;
    SET max_event = SELECT MAX(event_time) FROM atable WHERE event_type = e_type;
    RETURN max_event - 1 HOUR;
  END

Control Statements

In addition to the RETURN statement, the following statements can
      be used in specific contexts.

ITERATE STATEMENT

The ITERATE statement can be used to cause the next iteration of a
      labeled iterated statement (a WHILE, REPEAT or LOOP statement). It is
      similar to the "continue" statement in C and Java.

<iterate statement> ::= ITERATE <statement
      label>

LEAVE STATEMENT

The LEAVE statement can be used to leave a labeled block. When
      used in an iterated statement, it is similar to the "break" statement is
      C and Java. But it can be used in compound statements as well.

<leave statement> ::= LEAVE <statement
      label>

Signal and Resignal Statements

The SIGNAL statement is used to throw an exception (or force an
      exception). When invoked, any exception handler for the given exception
      is in turn invoked. If there is no handler, the exception is propagated
      to the enclosing context.

<signal statement> ::= SIGNAL SQL_STATE
      <state value>

The RESIGNAL statement is used to throw an exception from an
      exception handler’s <SQL procedure statement>,
      in effect propagating the exception to the enclosing context without
      further action by the currently active handlers.

<resignal statement> ::= RESIGNAL SQL_STATE
      <state value>

Routine Polymorphism

More than one version of a routine can be created.

For procedures, the different versions must have different
      parameter counts.  When the procedure is called, the parameter count
      determines which version is called.

For functions, the different versions can have the same or
      different parameter counts. When the parameter count of two versions of
      a function is the same, the type of parameters must be different. The
      best matching version of the function is called, according to both the
      parameter count and parameter types.

Two versions of an overloaded function are given below. One
      version accepts TIMESTAMP while the other accepts TIME arguments.

CREATE FUNCTION an_hour_before_or_now(t TIMESTAMP)
  RETURNS TIMESTAMP
  IF t > CURRENT_TIMESTAMP THEN
    RETURN CURRENT_TIMESTAMP;
  ELSE
    RETURN t - 1 HOUR;
  END IF

CREATE FUNCTION an_hour_before_or_now(t TIME)
  RETURNS TIME
  CASE t
    WHEN > CURRENT_TIME THEN
      RETURN CURRENT_TIME;
    WHEN >= TIME'01:00:00' THEN
      RETURN t - 1 HOUR;
    ELSE
      RETURN CURRENT_TIME;
  END CASE

more ..

Returning Data From Routines

The OUT or INOUT parameters of a procedure are used to assign
      simple values to dynamic parameters or to variables in the calling
      context. In addition, a SQL/PSM procedure may return result sets to the
      calling context. These result sets are dynamic in the sense that a
      procedure may return a different number of result sets or none at all in
      different invocations.

The SQL Standard uses a mechanism called CURSORS for accessing and
      modifying rows of a result set one by one. This mechanism is absolutely
      necessary when the database is accessed from an external application
      program. The JDBC ResultSet interface allows this method of access from
      Java programs and is supported by HyperSQL.

The SQL Standard uses cursors within the body of a procedure to
      return result sets. It specifies a somewhat complex mechanism to allow
      access to these cursors from the calling contexts. HyperSQL does not
      support access to such result sets within a calling SQL/PSM procedure.
      This is considered redundant as all operations on data can be performed
      with non-cursor SQL statements.

(feature to be implemented) HyperSQL will support returning single
      or multiple result sets from SQL/PSM procedures only via the JDBC
      CallableStatement interface. Cursors are declared and opened within the
      body of the procedure. No further operation is performed on the cursors
      within the procedure. When the execution of the procedure is complete,
      the cursors become available as Java ResultSet objects via the
      CallableStatement instance that called the SQL/PSM procedure.

Currently, a single result can be returned from FUNCTION routines,
      when the function is defined as RETURNS TABLE ( .. )

To return a table from a SELECT statement, you should use a return
      statement such as RETURN TABLE( SELECT ...); in a SQL/PSM function. A
      Java function should return a JDBCResultSet instance. For an example of
      how to construct a JDBCResultSet for this purpose, see the source code
      for the org.hsqldb.jdbc.JDBCArray class.

The JDBC CallableStatement class is used with the SQL statement
      CALL <routine name> ( <argument 1>, ... )
      to call both functions and procedures. The getXXX()
      methods can be used to retrieve INOUT or OUT arguments after the call.
      The getResultSet() call can be used to access the
      ResultSet returned from a function that returns a result set.

Java Language Routines (SQL/JRT)

The body of a Java language routine is a static method of a Java
    class, specified with a fully qualified method name in the routine
    definition.

In the example below, the static method named
    toZeroPaddedString is specified to be called when
    the function is invoked.

CREATE FUNCTION zero_pad(x BIGINT, digits INT, maxsize INT)
  RETURNS CHAR VARYING(100)
  NO SQL
  LANGUAGE JAVA PARAMETER STYLE JAVA
  EXTERNAL NAME
  'CLASSPATH:org.hsqldb.lib.StringUtil.toZeroPaddedString'

The signature of the Java method (used in the Java code but not in
    SQL code to create the function) is given below:

public static String toZeroPaddedString(long value, int precision, int maxSize)

The parameter and return types and of the SQL routine definition
    must match those of the Java method according to the table below:

<colgroup>
<col>
<col>
</colgroup>

SMALLINT

Short or Short

INT

int or Integer

BIGINT

long or Long

NUMERIC  or DECIMAL

BigDecimal

FLOAT  or DOUBLE

Double or Double

CHAR or VARCHAR

String

DATE

java.sql.Date

TIME

java.sql.Time

TIMESTAMP

java.sql.Timestamp

BINARY

Byte[]

BOOLEAN

boolean or Boolean

ARRAY of any type	java.sql.Array

TABLE

java.sql.ResultSet

If the specified Java method is not found or its parameters and
    return types do not match the definition, an exception is raised. If more
    than one version of the Java method exist, then the one with matching
    parameter and return types is found and registered. If two “equivalent”
    methods exist, the first one is registered. (This situation arises only
    when a parameter is a primitive in one version and an Object in another
    version, e.g. long and
    java.lang.Long.).

When the Java method of an SQL/JRT routine returns a value, it
    should be within the size and precision limits defined in the return type
    of the SQL-invoked routine, otherwise an exception is raised. The scale
    difference are ignored and corrected. For example, in the above example,
    the RETURNS CHAR VARYING(100) clause limits the length
    of the strings returned from the Java method to 100. But if the number of
    digits after the decimal point (scale) of a returned BigDecimal value is
    larger than the scale specified in the RETURNS clause, the decimal
    fraction is silently truncated and no exception of warning is
    raised.

Polymorphism

If two versions of the same SQL invoked routine with different
      parameter types are required, they can be defined to point to the same
      method name or different method names, or even methods in different
      classes. In the example below, the first two definitions refer to the
      same method name in the same class. In the Java class, the two static
      methods are defined with corresponding method signatures.

In the third example, the Java function returns a result set and
      the SQL declaration includes RETURNS TABLE.

CREATE FUNCTION an_hour_before_or_now(t TIME)
  RETURNS TIME
  NO SQL
  LANGUAGE JAVA PARAMETER STYLE JAVA
  EXTERNAL NAME 'CLASSPATH:org.npo.lib.nowLessAnHour'

CREATE FUNCTION an_hour_before_or_now(t TIMESTAMP)
  RETURNS TIMESTAMP
  NO SQL
  LANGUAGE JAVA PARAMETER STYLE JAVA
  EXTERNAL NAME 'CLASSPATH:org.npo.lib.nowLessAnHour'

CREATE FUNCTION testquery(INTEGER)
  RETURNS TABLE(n VARCHAR(20), i INT)
  READS SQL DATA
  LANGUAGE JAVA
  EXTERNAL NAME 'CLASSPATH:org.hsqldb.test.TestJavaFunctions.getQueryResult'

In the Java class:

    public static java.sql.Time nowLessAnHour(java.sql.Time value) {
        ...
    }
    public static java.sql.Timestamp nowLessAnHour(java.sql.Timestamp value)
        ...
    }

    public static ResultSet getQueryResult(Connection connection, int i) throws SQLException {
        Statement st = connection.createStatement();
        return st.executeQuery("SELECT * FROM T WHERE I < " + i);
    }

Java Language Procedures

Java procedures are defined similarly to functions. The
      differences are:

The return type of the Java static method must be void.

If a parameter is defined as OUT or INOUT, the corresponding
          Java static method parameter must be defined as an array of the JDBC
          non-primitive type.

When the Java static method is invoked, the OUT and INOUT
          arguments are passed as a single-element array.

The static method can modify the OUT or INOUT param by
          assigning a value to the sole element of the argument array.

If the procedure contains SQL statements, only statements for
          data access and manipulation are allowed. The java method should not
          perform commit or rollback. The SQL statements should not change the
          session settings and should not include statements at create or
          modify tables definitions or other database objects. These rules are
          generally enforced by the engine, but additional enforcement may be
          added in future versions

An example of a procedure definition is given below:

CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname  VARCHAR(50), address VARCHAR(100))
  MODIFIES SQL DATA
  LANGUAGE JAVA
  EXTERNAL NAME 'CLASSPATH:org.hsqldb.test.Test01.newCustomerProcedure'

Legacy Support

The legacy HyperSQL statement, CREATE ALIAS <name>
      FOR <fully qualified Java method name> is no longer
      supported directly. It is supported when importing databases and
      translates to a special CREATE FUNCTION <name>
      statement that creates the function in the PUBLIC schema.

The direct use of a Java method as a function is still supported
      but deprecated. It is internally translated to a special CREATE
      FUNCTION statement where the name of the function is the
      double quoted, fully qualified name of the Java method used.

SQL Language Aggregate Functions

HyperSQL adds an extension to the SQL Standard to allow user defined
    aggregate functions. A user defined aggregate function has a single
    parameter when it is used in SQL statements. Unlike the predefined
    aggregate functions, the keyword DISTINCT cannot be used when a user
    defined aggregate function is invoked. Like all user defined functions, an
    aggregate function belongs to a schema and can be polymorphic.

A user defined aggregate function can be used in SQL statements
    where a predefined aggregate function is allowed.

Definition of Aggregate Functions

An aggregate function is always defined with 4 parameters. The
      first parameter is the parameter that is used when the function is
      invoked in SQL statements, the rest of the parameter are invisible to
      the invoking statement. The type of the first parameter is user defined.
      The type of the second parameter must be BOOLEAN. The third and fourth
      parameters have user defined types and must be defined as INOUT
      parameters. The defined return type of the function determines the type
      of the value returned when the function is invoked.

CREATE AGGREGATE
      FUNCTION

user defined aggregate function
      definition

Aggregate function definition is similar to normal function
      definition and has the mandatory <returns
      clause>. The BNF is given below.

<user defined aggregate function> ::= CREATE
      AGGREGATE FUNCTION <schema qualified routine name> <SQL
      aggregate parameter declaration list> <returns clause>
      <routine characteristics> <routine body>

The parameter declaration list BNF is given below. The type of the
      first parameter is used when the function is invoked as part of an SQL
      statement. When multiple versions of a function are required, each
      version will have the first parameter of a different type.

<SQL aggregate declaration list> ::= <left
      paren> [IN] [ <SQL parameter name> ] <parameter type>
      <comma> [IN] [ <SQL parameter name> ] BOOLEAN <comma>
      INOUT [ <SQL parameter name> ] <parameter type>
      <comma> INOUT [ <SQL parameter name> ] <parameter
      type> <right paren>

The return type is user defined. This is the type of the resulting
      value when the function is called. Usually an aggregate function is
      defined with CONTAINS SQL, as it normally does not read the data in
      database tables, but it is possible to define the function with READS
      SQL DATA and access the database tables.

HyperSQL invokes the aggregate function, with all the arguments
      set, once per each row in order to compute the values. Finally, it
      invokes the function once more to return the final result.

In the computation phase, the first argument is the value of the
      user argument as specified in the SQL statement, computed for the
      current row. The second argument is the boolean FALSE. The third and
      fourth argument values are initially null, but they can be updated in
      the body of the function during each invocation. The third and fourth
      arguments act as registers and hold their values between invocations.
      The return value of the function is ignored during the computation phase
      (when the second parameter is FALSE).

After the computation phase, the function is invoked once more to
      get the final result. In this invocation, the first argument is NULL and
      the second argument is boolean TRUE. The third and fourth arguments hold
      the values they held at the end of the last invocation. The value
      returned by the function in this invocation is used as the result of the
      aggregate function computation in the invoking SQL statement. In SQL
      queries with GROUP BY, the call sequence is repeated for each separate
      group.

SQL PSM Aggregate Functions

The example below features a user defined version of the Standard
      AVG(<value expression>) aggregate function for
      INTEGER input and output types. This function behaves differently from
      the Standard AVG function as it returns 0 when all the input values are
      null.

CREATE AGGREGATE FUNCTION udavg(IN x INTEGER, IN flag BOOLEAN, INOUT addup BIGINT, INOUT counter INT)
  RETURNS INTEGER
  CONTAINS SQL
  BEGIN ATOMIC
    IF flag THEN
      RETURN addup / counter;
    ELSE
      SET counter = COALESCE(counter, 0) + 1;
      SET addup = COALESCE(addup, 0) || COALESCE(x, 0);
      RETURN NULL;
    END IF;
  END

The user defined aggregate function is used in a select statement
      in the example below. Only the first parameter is visible and utilised
      in the select statement.

SELECT udavg(id) FROM customers GROUP BY lastname;

In the example below, the function returns an array that contains
      all the values passed for the aggregated column. For use with longer
      arrays, you can optimise the function by defining a larger array in the
      first iteration, and using the TRIM_ARRAY function on the RETURN to cut
      the array to size :

CREATE AGGREGATE FUNCTION array_aggregate(IN val VARCHAR(100), IN flag boolean, INOUT buffer VARCHAR(100) ARRAY, INOUT counter INT)
  RETURNS VARCHAR(100) ARRAY
  CONTAINS SQL
  BEGIN ATOMIC
    IF flag THEN
      RETURN buffer;
    ELSE
      IF val IS NULL THEN RETURN NULL; END IF;
      IF counter IS NULL THEN SET counter = 0; END IF;
      SET counter = counter + 1;
      IF counter = 1 THEN SET buffer = ARRAY[val];
      ELSE SET buffer[counter] = val; END IF;
      RETURN NULL;
    END IF;
  END

The tables and data for the select statement below are created
      with the DatabaseManager or DatabaseManagerSwing GUI apps. Part of the
      output is shown. Each row of the output includes an array containing the
      values for the invoices for each customer.

SELECT ID, FIRSTNAME, LASTNAME, ARRAY_AGGREGATE(CAST(INVOICE.TOTAL AS VARCHAR(100)))
  FROM customer JOIN INVOICE ON ID =CUSTOMERID
  GROUP BY ID, FIRSTNAME, LASTNAME

11 Susanne   Karsen    ARRAY['3988.20']
12 John      Peterson  ARRAY['2903.10','4382.10','4139.70','3316.50']
13 Michael   Clancy    ARRAY['6525.30']
14 James     King      ARRAY['3665.40','905.10','498.00']
18 Sylvia    Clancy    ARRAY['634.20','4883.10']
20 Bob       Clancy    ARRAY['3414.60','744.60']

Java Aggregate Functions

A Java aggregate function is defined similarly to PSM functions,
      apart from the routine body, which is defined as EXTERNAL NAME
      ... The Java function signature must follow the rules for both
      nullable and INOUT parameters, therefore:

No agrument is defined as a primitive or primitive array type.
      This allows nulls to be passed to the function. The second and third
      arguments must be defined as arrays of the JDBC non-primitive types
      listed in the table in the previous section.

In the example below, a user-defined aggregate function for
      geometric mean is defined.

CREATE AGGREGATE FUNCTION geometric_mean(IN val DOUBLE, IN flag BOOLEAN, INOUT register DOUBLE, INOUT counter INT)
 RETURNS DOUBLE
 NO SQL
 LANGUAGE JAVA
 EXTERNAL NAME 'CLASSPATH:org.hsqldb.test.Test01.geometricMean'

The Java function definition is given below:

public static Double geometricMean(Double in, Boolean flag,
        Double[] register, Integer[] counter) {

    if (flag) {
        if (register[0] == null) { return null; }
        double a = register[0].doubleValue();
        double b = 1 / (double) counter[0];
        return Double.valueOf(java.lang.Math.pow(a, b));
    }
    if (in == null) { return null; }
    if (in.doubleValue() == 0) { return null; }
    if (register[0] == null) {
        register[0] = in;
        counter[0]  = Integer.valueOf(1);
    } else {
        register[0] = Double.valueOf(register[0].doubleValue() * in.doubleValue());
        counter[0] = Integer.valueOf(counter[0].intValue() + 1);
    }
    return null;
}

In a select statement, the function is used like built in
      aggregate functions:

SELECT geometric_mean(age) FROM  FROM customer

Routine Definition

As discussed in the previous pages, routine definition has several
    mandatory or optional clauses. The complete BNF supported by HyperSQL and
    the remaining clauses are documented in this section.

CREATE FUNCTION

CREATE PROCEDURE

routine definition

Routine definition is similar for procedures and functions. A
    function definition has the mandatory <returns
    clause> which is discussed later. The description given so
    far covers the essential elements of the specification with the BNF given
    below.

<schema procedure> ::= CREATE PROCEDURE <schema
    qualified routine name> <SQL parameter declaration list>
    <routine characteristics> <routine body>

<schema function> ::= CREATE FUNCTION <schema
    qualified routine name> <SQL parameter declaration list>
    <returns clause> <routine characteristics> <routine
    body>

Parameter declaration list has been described above. For SQL/JRT
    routines, the <SQL parameter name> is optional
    while for SQL/PSM routines, it is required. If the <parameter
    mode> of a parameter is OUT or INOUT, it must be specified.
    The BNF is given below:

<SQL parameter declaration list> ::= <left
    paren> [ <SQL parameter declaration> [ { <comma> <SQL
    parameter declaration> }... ] ] <right paren>

<SQL parameter declaration> ::= [ <parameter
    mode> ] [ <SQL parameter name> ] <parameter
    type>

<parameter mode> ::= IN | OUT |
    INOUT

<parameter type> ::= <data
    type>

Return Value and Table Functions

RETURNS

returns clause

The <returns clause> specifies the type of
    the return value of a function. For all SQL/PSM functions and ordinary
    SQL/JRT functions, this is simply a type definition which can be a
    built-in type, a DOMAIN type or a DISTINCT type, or alternatively, a TABLE
    definition. For example, RETURNS INTEGER.

For a SQL/JRT function, it is possible to define a
    <returns table type> for a Java method that
    returns a java.sql.ResultSet object. Such SQL/JRT
    functions are called table functions. Table
    functions are used differently from normal functions. A table function can
    be used in an SQL query expression exactly where a normal table or view is
    allowed. At the time of invocation, the Java method is called and the
    returned ResultSet is transformed into an SQL table. The column types of
    the declared TABLE must match those of the ResultSet, otherwise an
    exception is raised at the time of invocation.

If a <returns table type> is defined for an
    SQL/PSM function, the following expression is used inside the function to
    return a table: RETURN TABLE ( <query expression>
    ); In the example blow, a table with two columns is
    returned.

RETURN TABLE ( SELECT a, b FROM atable WHERE e = 10 );

If a JDBC CallableStatement is used to CALL the
    function, the table returned from the function call is returned and can be
    accessed with the getResultSet() method of the
    CallableStatement.

<returns clause> ::= RETURNS <returns
    type>

<returns type> ::= <returns data type> |
    <returns table type>

<returns table type> ::= TABLE <table function
    column list>

<table function column list> ::= <left
    paren> <table function column list element> [ { <comma>
    <table function column list element> } ... ] <right
    paren>

<table function column list element> ::=
    <column name> <data type>

<returns data type> ::= <data
    type>

routine body

routine body

Routine body is either one or more SQL statements or a Java
    reference, as described. The user that defines the routine by issuing the
    CREATE FUNCTION or CREATE SCHEMA command must have the relevant access
    rights to all tables, sequences, routines, etc. that are accessed by the
    routine. If another user is given EXECUTE privilege on the routine, then
    there are two possibilities, depending on the <rights
    clause>. This clause refers to the access rights that are
    checked when a routine is invoked. The default is SQL SECURITY
    DEFINER, which means access rights of the definer are used;
    therefore no extra checks are performed when the other user invokes the
    routine. The alternative SQL SECURITY INVOKER means
    access rights on all the database objects referenced by the routine are
    checked for the invoker. This alternative is not supported by
    HyperSQL.

<routine body> ::= <SQL routine spec> |
    <external body reference>

<SQL routine spec> ::= [ <rights clause> ]
    <SQL routine body>

<rights clause> ::= SQL SECURITY INVOKER | SQL
    SECURITY DEFINER

SQL routine body

SQL routine body

The routine body of a an SQL routine consists of an
    statement.

<SQL routine body> ::= <SQL procedure
    statement>

EXTERNAL NAME

external body reference

External name specifies the qualified name of the Java method
    associated with this routine. Early releases of HyperSQL 2.0 only supports
    Java methods within the classpath. The <external Java
    reference string> is a quoted string which starts with
    CLASSPATH: and is followed by the Java package, class and method names
    separated with dots. HyperSQL does not currently support the optional
    <Java parameter declaration list>.

<external body reference> ::= EXTERNAL NAME
    <external Java reference string>

<external Java reference string> ::= <jar and
    class name> <period> <Java method name> [ <Java
    parameter declaration list> ]

Routine Characteristics

The <routine characteristics> clause
      covers several sub-clauses

<routine characteristics> ::= [ <routine
      characteristic>... ]

<routine characteristic> ::= <language
      clause> | <parameter style clause> | SPECIFIC <specific
      name> | <deterministic characteristic> | <SQL-data access
      indication> | <null-call clause> | <returned result sets
      characteristic> | <savepoint level
      indication>

LANGUAGE

language clause

The <language clause> refers to the
      language in which the routine body is written. It is either SQL or Java.
      The default is SQL, so JAVA must be specified for SQL/JRT
      routines.

<language clause> ::= LANGUAGE <language
      name>

<language name> ::= SQL |
      JAVA

The parameter style is not allowed for SQL routines. It is
      optional for Java routines and, in HyperSQL, the only value allowed is
      JAVA.

<parameter style> ::= JAVA

SPECIFIC NAME

specific name

The SPECIFIC <specific name> clause is
      optional but the engine will creates an automatic name if it is not
      present. When there are several versions of the same routine, the
      <specific name> is used in schema manipulation
      statements to drop or alter a specific version. The
      <specific name> is a user-defined name. It
      applies to both functions and procedures. In the examples below, a
      specific name is specified for each function.

CREATE FUNCTION an_hour_before_or_now(t TIMESTAMP)
  RETURNS TIMESTAMP
  NO SQL
  LANGUAGE JAVA PARAMETER STYLE JAVA
  SPECIFIC an_hour_before_or_now_with_timestamp
  EXTERNAL NAME 'CLASSPATH:org.npo.lib.nowLessAnHour'

CREATE FUNCTION an_hour_before_max (e_type INT)
  RETURNS TIMESTAMP SPECIFIC an_hour_before_max_with_int
  RETURN (SELECT MAX(event_time) FROM atable WHERE event_type = e_type) - 1 HOUR

DETERMINISTIC

deterministic characteristic

The <deterministic characteristic> clause
      indicates that a routine is deterministic or not. Deterministic means
      the routine does not reference random values, external variables, or
      time of invocation. The default is NOT DETERMINISTIC.
      It is essential to declare this characteristics correctly for an SQL/JRT
      routine, as the engine does not know the contents of the Java code,
      which could include calls to methods returning random or time sensitive
      values.

<deterministic characteristic> ::= DETERMINISTIC
      | NOT DETERMINISTIC

SQL DATA access

SQL DATA access characteristic

The <SQL-data access indication>  clause
      indicates the extent to which a routine interacts with the database or
      the data stored in the database tables (SQL data).  NO SQL means no SQL
      command is issued in the routine body and can be used only for SQL/JRT
      functions. CONTAINS SQL means some SQL commands are
      used, but they do not read or modify the SQL data. READS SQL
      DATA and MODIFIES SQL DATA are self
      explanatory.

<SQL-data access indication> ::= NO SQL |
      CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA

NULL INPUT

null call clause

Null Arguments

The <null-call clause> is used only for
      functions. If a function returns NULL when any of the calling arguments
      is null, then by specifying RETURNS NULL ON NULL
      INPUT, calls to the function are known to be redundant and do
      not take place when an argument is null. This simplifies the coding of
      the SQL/JRT Java methods and improves performance at the same
      time.

<null-call clause> ::= RETURNS NULL ON NULL
      INPUT | CALLED ON NULL INPUT

SAVEPOINT LEVEL

transaction impact

The <savepoint level indication> is used
      only for procedures and refers to the visibility of existing savepoints
      within the body of the procedure. If NEW SAVEPOINT
      LEVEL is specified, savepoints that have been declared prior
      to calling the procedure become invisible within the body of the
      procedure. HyperSQL’s implementation accepts only NEW SAVEPOINT
      LEVEL, which must be specified.

<savepoint level indication> ::= NEW SAVEPOINT
      LEVEL | OLD SAVEPOINT LEVEL

DYNAMIC RESULT SETS

returned result sets
      characteristic

The <returned result sets characteristic>
      is used only for SQL/PSM procedures. The maximum number of result sets
      that a procedure may return can be specified with the clause below. The
      default is zero. Details are discussed in the previous sections.

<returned result sets characteristic> ::=
      DYNAMIC RESULT SETS <maximum returned result
      sets>

$Revision: 3601 $

Chapter 7. Data Access and Change 		 Chapter 9. Triggers


text:
<!DOCTYPE html>

        <meta name="generator" content="HTML Tidy for HTML5 for Mac OS X version 5.4.0">
        <meta charset="utf-8">
        <title>
            Intro to SQL - Vicki Boykis
        </title>
        <meta name="description" content=
        "Girl Develop It framework for easily creating beautiful presentations using HTML in GDI theme. Forked from Hakim El Hattab's reveal.js">
        <meta name="author" content="Girl Develop It">
        <meta name="apple-mobile-web-app-capable" content="yes">
        <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
        <link rel="stylesheet" href="css/reveal.css">
        <link rel="stylesheet" href="css/theme/simple.css" id="theme">
        <link rel="stylesheet" href="lib/css/zenburn.css">
        <link rel="stylesheet" href="lib/css/light.css">
        <link rel="stylesheet" href="plugin/accessibility-helper/css/accessibility-helper.css">

        <link rel="stylesheet" href="css/print/pdf.css" type="text/css" media="print">
        <link rel="icon" type="image/x-icon" href="favicon.ico">

                    All slides on GitHub:
                    https://github.com/gdiphilly/intro-to-sql

                        Intro to SQL

                        Vicki Boykis; vickiboykis.com / @vboykis

                        Welcome!

                            Girl Develop It is here to provide affordable and accessible programs to learn software through mentorship and hands-on
                            instruction.

                            Our code of conduct

                            Some rules

We are here for you!

Every question is important

Help each other

Have fun

                        Tell Us About Yourself

Who are you?

What's your experience level with SQL?

What do you hope to get out of the class?

What is your favorite movie, and how many times have you watched it?

                        We're going to make a table out of this info in part 2, and the data will live here: [Table Link]

                        About Me

Data scientist

9+ years of experience with SQL

Favorite movie is Beauty and the Beast, watched 25 times. (and that was just yesterday)

                        What to expect from the class

This is a complement to the database design class

We'll be using the same terminology and building on those concepts

But we'll be hands-on

                        By the end of the class, you will be able to query and modify a SQL table to make sense of the data you have in a relational way.

                        Plan for the day: CRUD

                            Morning (10-12): cRud

                                    What's a database? What's SQL?

                                How to query a database:

                                    Select statement (from statement)

                                        Select, Distinct

                                        Count

                                    Group By, Order By

                                    Clauses

                                        Where

                                        Like

                                    Joins(Inner/Outer Left/Right)

                                    Practice

                            Afternoon(12:30 - 3): CrUD

                                    Super-awesome bonus round:

                                        Subqueries

                                    Practice

                                    How to modify a database

                                        Creating a table

                                        Udating a table with rows

                                        Deleting Rows

                                            v.s. deleting tables

                                    Optimizing queries

                                    Resoures and links

                                    Practice

                                    GDI Survey

                        What's a relational database?

                        A virtual organized file cabinet that makes data available to people and computers across the organization.

                        The language of relational databases (1/2)

                                Database: A group of data organized in a logical way(Excel workbook)

                                    Schema: Layout of the database, sometimes interchangeable with database (Excel workbook + name of
                                    workbook)

Table: A subset of a database (Excel worksheet)

Rows (records) and columns (fields): Parts of a table

                                CRUD (Create, Read, Update, Delete): How you change the databases and tables in the database

                                Relational Data Model : Data related to each other are stored a certain way and "point" to each other (Like Excel cell
                                references)

												The language of relational databases (2/2)

																Database keys (primary and foreign): The pointers in each table that can link to other tables

																Indexes: Make databases easier to search

																RDBMS systems: Types of relational databases that have all the properties of relational data models: MySQL, Oracle,
																Access, Postgres

																Queries: How to ask questions of the tables and databases

                        Databases, schemas, and tables

                        DB Schema→ Table → Row → Value

                        Excel File → Workbook → Row → Cell

                        What's a relational database?

Excel spreadsheets you can join without Vlookups!

                        What's SQL?

                                SELECT user_name, SUM(total_purchases)

                                FROM billing_database.user_table

                                WHERE user_name LIKE 'Henderson%'

                                AND billing_month = 'January'

                                GROUP BY user_name;

It's the language that the database speaks to bring you back data

Has a lot of words similar to English, but they have their own meaning as key words

Like Siri or Alexa

                        Different Flavors of SQL

How do you say that you want a can of Coca-Cola?

Coke, soda or pop?

How do you write SQL?

                                Oracle

                                MySQL

                                SQL Server

                                Postgres

                                Access

                                Sybase

                                DB2

                        All different flavors of the same language

                        Databases, schemas, and tables

                            Reading tables other people wrote

                            Modifying those tables to add your own information/ creating your own tables to read from CSV files, other
                            SQL tables, and other systems.

                        Databases and CRUD

                                Create INSERT, CREATE

Adding rows to your table

                                Read SELECT

Picking specific information from your table

                                Update UPDATE, ALTER

Changing specifc information in your table

                                Delete DELETE, DROP, TRUNCATE

Erase your data

                        What can you do with SQL?

                        The power is in the relationship between data sets.

How many customers do we have in Los Angeles?

What's that person's Facebook and Twitter handle?

Did profits go up or down this quarter?

How many users are clicking on our new site?

                        SQL clauses: required order

                                    1 SELECT

                                    selects variables

                                    2 FROM

                                    opens datasets

                                    3 WHERE

                                    restricts observations

                                    4 GROUP BY

                                    groups observations

                                    5 HAVING

                                    restricts groups

                                    6 ORDER BY

                                    sorts results

                        Let's develop it!

                        Let's get started reading tables

                        We'll use MySQL, a popular version of SQL.

                        If you've done anything online, chances are the transation went through MySQL (Walmart, Kayak, Facebook, Etsy, Verizon)

                        We'll log on to the front-end of MySQL, PHPMYAdmin.

                            Logon site: [site]

                            Your Password: [password]

                        You should see a login screen:

                        How PHPMyAdmin Works

                        Two ways to execute in MySQL: PHPMyAdmin and command line

                        Focusing on phpMyAdmin for the first half of the class.

                        We have a test dataset of employee records we'll be using in the employee schema.

                        Employee data:

                        6 tables in the employees schema

                    Data Source

                        Tables and Schemas

                        employees is the database and it has 6 tables.

                        Database/schema used interchangeably

                        Database → Schema ---> Table

                        Book ---> Chapter ---> Page

                        MySQL Database → Employees Schema → employees Table

                        How to name schemas and tables

Schema/Databse: employees

Tables:

employees.employees,

employees.departments,

employees.salaries

And so on.

                        Usually it's bad practice to name tables the same thing as the schema (confusing), but we'll be ok with the small test data set.

                        Let's develop it!

                        Let's run our first query!

                        Go to the SQL tab and type in:

SELECT * FROM employees.employees;

                        Type out all the SQL you write. It'll be easier to learn.

                        SELECT * from different tables

                        Try a couple of these:

SELECT * FROM employees.titles;
SELECT * FROM employees.dept_emp;
SELECT * FROM employees.salaries;

                        and check out the tables and info.

                        Stretch break!

                        Selecting specific columns

                        Now let's try selecting only certain columns

SELECT title, emp_no FROM employees.titles;

                        A note about the semi-colon: you don't really need it, depending on which flavor of SQL you're using, but it's good
                        practice in general to include it. Like a period at the end of an English sentence.

                        More specific columns. Let's get to know our data.

                        Selecting one column:

SELECT dept_name FROM employees.departments;

                        Selecting multiple columns:

SELECT emp_no, dept_no FROM employees.dept_emp;

                        Distinct: selecting only unique instatnces of that column.

SELECT DISTINCT  title FROM employees.titles;

                                    title

                                    Senior Engineer

                                    Staff

                                    Engineer

                                    Senior Staff

                                    Assistant Engineer

                                    Technique Leader

                                    Manager

                        Difference between distinct and *

SELECT COUNT(DISTINCT title) FROM employees.titles;
7

SELECT COUNT(title) FROM employees.titles;
443,308

                        Difference between distinct and *

                        Distinct: Selecting only unique instances

                        Which result set is bigger?

                        What is each one telling us?

SELECT COUNT(title) FROM employees.titles;
433,308

SELECT COUNT(*) FROM employees.salaries;
2,844,047

                        Which table is the largest?

                        (most records)

SELECT COUNT(*) from employees.departments;
9

SELECT COUNT(*) FROM employees.dept_emp;
331,603

SELECT COUNT(*) FROM employees.dept_manager;
24

SELECT COUNT(*) FROM employees.employees;
300,024

SELECT COUNT(*) FROM employees.salaries;
2,844,047

SELECT COUNT(*) FROM employees.titles;
443,308

                        Three parts of SQL

                        SQL clauses: required order

                                    1 SELECT

                                    selects variables

                                    2 FROM

                                    opens datasets

                                    3 WHERE

                                    restricts observations

                                    4 GROUP BY

                                    groups observations

                                    5 HAVING

                                    restricts groups

                                    6 ORDER BY

                                    sorts results

                        Now let's group stuff!

                        SELECT type_of_dog,COUNT(*)
                        FROM animals.total_dogs
                        GROUP BY type_of_dog;

                        Group statements corral data together into categories when we want to count things by groups of things

                        i.e. How many total dogs vs puppies vs parents.

                        Counting titles and salaries

What is this telling us? How many instances of that title are there in the table?

SELECT title, COUNT(*) FROM employees.titles
GROUP by title;

                        More group by examples

SELECT dept_name, COUNT(*) FROM departments
GROUP BY dept_name;

                        How many instances of that department are there in that table?

                        Group by more than one item: making smaller groups

                        Let's try some exercises that combine COUNT(*), DISTINCT and GROUP BY

                        How many unique salaries does each employee have? (how many times have they been given raises?)

SELECT emp_no, COUNT(DISTINCT salary)
FROM salaries
GROUP BY emp_no;

                        How many managers has each department had?

SELECT dept_no, COUNT(emp_no) FROM dept_manager
GROUP BY dept_no;

                        How many people that were born on the same day, have the same first name?

SELECT birth_date,first_name, COUNT(*) FROM employees.employees
GROUP BY birth_date, first_name;

                        Another kind of group-by:

                        maximum and minimum

                        When was your youngest employee born? (i.e. are you following labor laws?)

 SELECT MAX(birth_date) FROM employees;

                            In this case, you don't need the group-by statement since you're not counting groups of people, but looking at the whole table

                        Which title has been around the longest?(Smallest employee number)

SELECT title, MIN(emp_no) from titles
GROUP BY title;

                        Order by: Sorts by the terms you want

                        Will sort by first name ascending, unless you specify desc.

SELECT first_name, gender, COUNT(*)
FROM employees.employees
GROUP BY first_name, gender
ORDER BY first_name;

SELECT first_name, gender, COUNT(*)
FROM employees.employees
GROUP BY first_name, gender
ORDER by first_name DESC;

                        Ordering by more than one column, will sort by the first, then by the second, ascending

SELECT gender, birth_date FROM employees.employees
ORDER BY gender, birth_date;

                        Order by exercises

                        Order by a number ascending

SELECT emp_no, salary FROM employees.salaries
ORDER BY salary ASC;

                        Order by a number descending

SELECT emp_no, salary FROM employees.salaries
ORDER BY salary DESC;

                        SQL clauses: required order

                                    1 SELECT

                                    selects variables

                                    2 FROM

                                    opens datasets

                                    3 WHERE

                                    restricts observations

                                    4 GROUP BY

                                    groups observations

                                    5 HAVING

                                    restricts groups

                                    6 ORDER BY

                                    sorts results

                        Where Clause: Like a Filter

                        You only want to select certain things.

SELECT first_name, last_name, COUNT(*)
FROM employees.employees
WHERE first_name='Adam'
GROUP BY first_name, last_name;

                                first_name

                                last_name

                                COUNT(*)

                                Adam

                                Alpin

                                1

                                Adam

                                Alvarado

                                1

                        Like statements are really useful in where clauses

                        "Like" will pick up anything similar to what you entered.

SELECT last_name, COUNT( * )
FROM employees
WHERE last_name LIKE 'S%'
GROUP BY last_name;

                        Exercises:

What happens when you search in all caps?

What happens when you do last_name = 'S%''

What's the difference between like and =?

Like is more like a search engine

= means you have to have an exact match

                        Where clause exercises

                        Only all people named Mary

SELECT * FROM employees
WHERE first_name='Mary';

                        Only all of the engineers

SELECT * FROM employees.titles
WHERE title LIKE '%Engineer%';

                        A couple of things to note about greater/less than/equal to

 WHERE number >='5'; --returns (5,6,7,8...)

 WHERE number > '5'; --returns (6,7,8...)

 WHERE day <= '2017-01-01'; --returns (Jan 1, Dec. 31, Dec. 30…)

 WHERE day < '2017-01-01'; --returns (Dec. 31, Dec. 30…)

 WHERE day != '2017-01-01'; --returns anything other than Jan 1

                        Cooking with the good suff: joins

                        One table is great. But the power of SQL comes from being able to join related tables to get information.

                        Plan for the day: CRUD

                            Morning (10-12): cRud

                                    What's a database? What's SQL?

                                How to query a database:

                                    Select statement (from statement)

                                        Select, Distinct

                                        Count

                                    Group By, Order By

                                    Clauses

                                        Where

                                        Like

                                    Joins(Inner/Outer Left/Right)

                                    Practice

                            Afternoon(12:30 - 3): CrUD

                                    Super-awesome bonus round:

                                        Subqueries

                                    Practice

                                    How to modify a database

                                        Creating a table

                                        Udating a table with rows

                                        Deleting Rows

                                            v.s. deleting tables

                                    Optimizing queries

                                    Resoures and links

                                    Practice

                                    GDI Survey

                        Let's connect multiple tables

                        emp_no is the column that's the primary key for all three tables that allows us to join them

We join by primary keys: A field (or set of fields) that uniquely determine a row.

Values in PK must be unique in table (no duplicates allowed); Only one PK per table allowed

To foreign keys: A field (or set of fields) that is a PK in some other table.

                        How all the tables join in our schema

                        In SQL, a join looks like this:

                        Often when we join tables, we make them easier to reference later on by aliasing them, but at this point it's a style choice.

                        You can now select columns that, before, only existed in either table

                        Find out salary history:

SELECT first_name, last_name, hire_date,salary, from_date, to_date
FROM employees.employees a
JOIN employees.salaries b ON a.emp_no = b.emp_no

                                first_name

                                last_name

                                hire_date

                                salary

                                from_date

                                to_date

                                Georgi

                                Facello

                                6/26/86

                                60117

                                6/26/86

                                6/26/87

                                Georgi

                                Facello

                                6/26/86

                                62102

                                6/26/87

                                6/25/88

                                Georgi

                                Facello

                                6/26/86

                                66074

                                6/25/88

                                6/25/89

                        Another join example

                        When you have two columns from different tables with the same name, you have to reference them specifically.

                        Different types of joins

                    Source

                        Let's develop it!

                        Left join example

                        Left join: All the fields in the first table, plus the ones that match in the second table

SELECT first_name, last_name, hire_date,title, c.from_date, c.to_date
FROM employees.employees a
LEFT JOIN titles c ON a.emp_no = c.emp_no;

                        What if you'd done a right join instead?

                        Using everything we've learned

                        What is this query telling us?

SELECT first_name, last_name,a.emp_no, hire_date,
COUNT(DISTINCT title) , COUNT(DISTINCT salary)
FROM employees.employees a
JOIN employees.salaries b ON a.emp_no = b.emp_no
JOIN employees.titles c ON a.emp_no = c.emp_no
where first_name like 'A%'
GROUP BY first_name, last_name, hire_date,a.emp_no;

                        Going all in again:

                        What is this query bring back?

SELECT first_name, last_name,hire_date,a.emp_no, salary, from_date as
salary_start_date, to_date AS salary_end_date
FROM employees.employees a
JOIN employees.salaries b ON a.emp_no = b.emp_no
WHERE salary >60000
AND from_date >'2000-01-01'
ORDER BY last_name

                        Anyone whose salary is greater than $60,000 and who was hired after 1/1/2000

                        Lunch!

                        Now the power of subqueries

                        Using nested logic datasets to query other data sets

                        Subqueries

                        A subquery or inner query or nested query is a query within a query. Most of the time, a subquery is used when you know how to search
                        for a value using a SELECT statement, but not the exact value

                        Subqueries: How much money did only the engineers make?

                        What kind of business questions can we answer now? SQL Power!

                        Exercises:

How many employees do we have?

SELECT COUNT(DISTINCT emp_no)
FROM employees.employees;

How many people got raises in 1999?

 SELECT * from (
        SELECT emp_no, COUNT(*) AS number_of_salaries
        FROM salaries
        WHERE from_date>'1998-01-01'
        AND from_date<'2000-01-01'
        GROUP BY emp_no) a
        WHERE number_of_salaries >1

What's the most common job title we have?

SELECT title, COUNT(DISTINCT a.emp_no)
FROM titles a
JOIN employees b
ON a.emp_no=b.emp_no
GROUP BY title

                        What kind of business questions can we answer now? SQL Power!

                        Exercises:

What's the top salary by department?

How much money did non-engineers make?

Extra-hard question:

How many women engineers got raises in 2000 versus male engineer, and who were their managers?

                        Rehash

                        What did we do so far?

What a database is

What a table is

Why relationships are good

How to query a table or database with SQL

How to read a SQL query

How to ask business questions with SQL

                        We learned cRud; now we learn CrUD

                        Now that you see how powerful SQL is, let's make some tables of our own. What if we have tons of data we want to query?

                        Or we want to join our Excel tables to other data?

                        With great power comes great responsibility

                        You have the ability to create tables. But also to delete them by accident, to create duplicate data,or to confuse people with data.

                        You have the power to make people hate you. Use it wisely :)

                        Databases, schemas, and tables

                        DB Schema→ Table → Row → Value

                        Excel File → Workbook → Row → Cell

                        First we create our schema

                        Name it with your first name or something easy you can remember; You'll have to keep typing it, so under 10 characters is optimal

                        And you should see it show up:

                        Now we create a table to put in the schema

                        Let's do favorite movies that we did at the beginning of the class.

                        Let's have: First Name, Favorite Movie, Number of times watched

                        First, we need to design it a bit in our head. Which columns do we want? Which one will be the unique primary key? How to we want to
                        name the columns?

                        Things to think about when designing relational tables

How will you join to other tables?

What kind of data are you storing?

Who else will use your tables?

                        Quick Overview of Database Design(from Sondra's class)

                        Relational Database Model

                        Storing the data as relations

eliminates redundancy

saves space

reduces mistakes (ties in to consistency)

                        Quick Overview of Database Design(from Sondra's class)

                        Tables are joined on special fields called keys

Primary Key (PK)

A field (or set of fields) that uniquely identify a row

In math speak, the minimal set of fields that the row is functionally dependent upon

Foreign Reference Key (FK)

A field (or set of fields) that is a PK in some other table

IThere can be multiple FK in a table

Points to a row in another table

                        Data Types

                        Data usually comes in two types: numbers and charactes

Numbers

Boolean: True or False [0,1]

Int/TinyInt: whole numbers, negatives [5, -7]

Decimal: [.5555, 2.40404040]

Strings

Varchar(can't add/subtract):[Belle, five,5]

Have to specify length

Blob: [9999349234023403204300000]

Dates/Times

Date: 2013-01-01, 9/1/2012]

Timestamp(UTC) [1385496316]

                        *Accepted mySQL formats: https://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

                        Let's look at the data types in the tables we have

                        By going to STRUCTURE

                        Our movie database (with real data)

First Name as the Primary Key and Foreign Key

We can join by the name, as long as it's unique

But our names aren't unique, so we'll create an incrementer

First Name Should be a Character Field.

Favorite Movie should be a character Field.

Times Watched should be an Integer. We want to be able to add it.

                        Two ways to execute in MySQL: PHPMyAdmin and command line

                        For the second half of the class we'll be working with commands more than the UI, but there is usually more than 1 way to do everything
                        in MySQL.

                        How to create a table with SQL

                        Run in the sample place you did your select statements:

                        Some Data types in MySQL(Part 2)

                                CHAR()

                                A fixed section from 0 to 255 characters long.

                                VARCHAR( )

                                A variable section from 0 to 255 characters long.

                                TINYTEXT

                                A string with a maximum length of 255 characters.

                                TEXT

                                A string with a maximum length of 65,535 characters.

                                BLOB

                                A string with a maximum length of 65,535 characters.

                                MEDIUMTEXT

                                A string with a maximum length of 16777215 characters.

                                MEDIUMBLOB

                                A string with a maximum length of 16777215 characters.

                                LONGTEXT

                                A string with a maximum length of 4294967295 characters.

                                LONGBLOB

                                A string with a maximum length of 4294967295 characters.

                        Here's the create statement for one of the tables we've been working with

CREATE TABLE employees (
emp_no      INT             NOT NULL,
first_name  VARCHAR(14)     NOT NULL,
last_name   VARCHAR(16)     NOT NULL,
gender      ENUM ('M','F')  NOT NULL,
hire_date   DATE            NOT NULL,
PRIMARY KEY (emp_no);

                        Which field is the primary key?

                        Which fields are numbers?

                        What's enum*?

                        Let's get to it

                        In PHPMYADMIN: STEP 1

                        In PHPMYADMIN: STEP 2

                        In PHPMYADMIN: STEP 3

                        Congratulations, it's a table!

                        Now let's do some quick analysis

SELECT * FROM vicki.moviesample;
SELECT first_name FROM vicki.moviesample;
SELECT COUNT(*) FROM vicki.moviesample;

                        What do we get?

                        Nothing! because our table has no data. :(...yet

                        To the SQL: Inserting data into a SQL table

                        Now that we have the table set up, we can insert the values. Let's load a row of data.

INSERT INTO
yourname.tablename(first_name, favorite_movie,
times_watched) -- column names
VALUES ('Jasmine', 'Aladdin', '456'); -- column values

                        Adding data to tables

INSERT INTO vicki.moviesample(first_name, favorite_movie, times_watched)
VALUES ('Alice','Alice in Wonderland', 5);

                        Now let's check it..wahoo! We have rows!

                        How do we add the rest of the data?

INSERT INTO vicki.moviesample
(first_name, favorite_movie,times_watched)
VALUES ('Vicki', 'Beauty and the Beast', '5');

INSERT INTO vicki.movie_sample
(first_name, favorite_movie,times_watched)
VALUES ('Jasmine', 'Aladdin','456');

                        Break to enter the rest of the data into the table

                        What kind of issues do you see arising from wrong data entry?

                        There are much easier ways of getting data into tables

Bulk-loading ETL

Writing a script in Perl, Ruby, Python, or any other scripting language

Manipulating Excel to write your insert/update statements

                        Our beautiful movie table!

                        Let's answer some exercises with SQL:

What's the most popular movie?

SELECT favorite_movie,
COUNT(DISTINCT first_name)
FROM yourname.moviesample
GROUP BY favorite_movie
ORDER BY COUNT(DISTINCT first_name) DESC;

What's the most popular name this Saturday?

How many times was the most someone watched a movie?

How many movies have 'The' in the title?

How many people watched a favorite movie more than 3 times?

                        Now, let's make another table

                        We want to see which decade was more popular among GDI members. This one has the data for the movie name and the year it was made.

                        How do we add the rest of the data?

SELECT * FROM vicki.moviesample a
JOIN vicki.movie_year b
ON a.movie_title=b. movie_title

What happens when you have two fields with the same name in your select statement?

What is movie title called in year_released (special role?)

What would be a better field to join on?

What's the year with the most popular movies?

And, what's the decade with the most popular movies? Hint: Take the first three digits of the year

select left(year_released,3), count(*) from..

                        Deleting SQL Values

                            What kind of issues do you see arising from wrong data entry?

                            This is the part where you have to be really careful. Most users won't have access to delete tables.

                            truncate table; Leaves the table structure in tact, just deletes the values. (deleting all values in a
                            spreadsheet, keeping file)

                            drop table; Deletes EVERYTHING. BADBADBAD. (file goes in the trash)

                        Dropping Rows Example

                        Easy way in UI: The delete button. Be careful!

SQL...more accurate.

DELETE FROM user1.moviesample WHERE first_name='Shrek';

                        You can delete with any type of where statement

DELETE FROM moviesample
WHERE first_name like '%S';

DELETE FROM moviesample
WHERE times_watched >='500';

                        Updating the table

                            SELECT is to count things

                            CREATEis to create a table

                            DELETE is to erase

                            UPDATE is to change specific entries

UPDATE moviesample
SET first_name = 'Chihiro', times_watched= 3
WHERE movie = 'Spirited Away';

                                Try it now

                        Altering the Table

                            SELECT is to count things

                            CREATEis to create a table

                            DELETE is to erase

                            ALTER is to fundamentally change a table:

ALTER TABLE icecream DROP COLUMN flavor;
ALTER TABLE icecream ADD unique (quantity);
ALTER TABLE icecream MODIFY flavor VARCHAR(35);

                                Try it now:

ALTER TABLE vicki.moviesample ADD COLUMN release_yr
varchar(4) after times_watched;

                        Troubleshooting SQL Errors

You misspelled something

Selection from table you don't have rights to

Your group by statements don't have enough columns

Query's running too long

Format for date/time doesn't match

You're selecting from the wrong table in the schema

Date type is the wrong type for a join

You haven't aliased your table

                        Best Practices

Limit your queries by timerange

Only do joins on primary keys

Profile your data beforehand

Save your SQL queries for future use

Be friends with your database people

Write explain plans!

                        A word about non-relational "big data"

                        The end!

                                Here's what we've learned:

Reading tables (cRud)

Basic SQL Logic

Writing tables (CrUD)

Basic deletes and updates

Best practices for writing SQL and writing tables

                        More SQL Resources

                            Head First SQL Good visual book

                            Learn SQL The Hard Way Highly recommended, and not hard
                            at all

                            Visualizing SQL Joins
                            Great visual intro

Good visual book

                            Popular SQL Questions
                            Highly recommended, and not hard at all

                        Practice, practice, practice! Upload your Excel spreadsheets.

SamplePractice Data Set that we used in class

                        Get Involved

Join Slack community

Teaching assist

Teach something

Mentor or be mentored

                        All forms here

                        More SQL: Local Groups

Philly DB http://www.meetup.com/PhillyDB/

Data Science Philly http://www.meetup.com/DataPhilly/

Philly Open Data http://www.meetup.com/Philly-Open-Data/

Philly PostrgreSQL UG http://www.meetup.com/phlpug/

                        Survey

                        We value your feedback and are always trying to improve.

                        Vicki Boykis vicki.boykis@gmail.com

                        vickiboykis.com | @vboykis

                        http://bit.ly/gdi-sql-2

 Thank you to the following people:

                    Sarah Canfield - converting slides to Reveal
                    A.E.Lavender - Sanity and syntax checks

                    Intro to SQL -- Philadelphia --

redpajama_set_name: RedPajamaGithub


text:
using System;
using System.Diagnostics.CodeAnalysis;
using System.Runtime.InteropServices;
using System.Text;

namespace Alphaleonis.Win32.Filesystem
{
   internal static partial class NativeMethods
   {
      /// Retrieves the full path and file name of the specified file or directory.
      /// <returns>If the function fails for any other reason, the return value is zero. To get extended error information, call GetLastError.</returns>
      /// <remarks>The GetFullPathName function is not recommended for multithreaded applications or shared library code.</remarks>
      /// <remarks>Minimum supported client: Windows XP [desktop apps only]</remarks>
      /// <remarks>Minimum supported server: Windows Server 2003 [desktop apps only]</remarks>
      [SuppressMessage("Microsoft.Security", "CA5122:PInvokesShouldNotBeSafeCriticalFxCopRule")]
      [DllImport("kernel32.dll", SetLastError = true, CharSet = CharSet.Unicode, EntryPoint = "GetFullPathNameW")]
      [return: MarshalAs(UnmanagedType.U4)]
      internal static extern uint GetFullPathName([MarshalAs(UnmanagedType.LPWStr)] string lpFileName, [MarshalAs(UnmanagedType.U4)] uint nBufferLength, StringBuilder lpBuffer, IntPtr lpFilePart);

      /// Retrieves the full path and file name of the specified file or directory as a transacted operation.
      /// <returns>If the function fails for any other reason, the return value is zero. To get extended error information, call GetLastError.</returns>
      /// <remarks>Minimum supported client: Windows Vista [desktop apps only]</remarks>
      /// <remarks>Minimum supported server: Windows Server 2008 [desktop apps only]</remarks>
      [SuppressMessage("Microsoft.Security", "CA5122:PInvokesShouldNotBeSafeCriticalFxCopRule")]
      [DllImport("kernel32.dll", SetLastError = true, CharSet = CharSet.Unicode, EntryPoint = "GetFullPathNameTransactedW")]
      [return: MarshalAs(UnmanagedType.U4)]
      internal static extern uint GetFullPathNameTransacted([MarshalAs(UnmanagedType.LPWStr)] string lpFileName, [MarshalAs(UnmanagedType.U4)] uint nBufferLength, StringBuilder lpBuffer, IntPtr lpFilePart, SafeHandle hTransaction);

      /// Converts the specified path to its long form.
      /// <returns>
      /// If the function succeeds, the return value is nonzero.
      /// If the function fails, the return value is zero. To get extended error information, call GetLastError.
      /// </returns>
      /// <remarks>Minimum supported client: Windows XP [desktop apps only]</remarks>
      /// <remarks>Minimum supported server: Windows Server 2003 [desktop apps only]</remarks>
      [SuppressMessage("Microsoft.Security", "CA5122:PInvokesShouldNotBeSafeCriticalFxCopRule")]
      [DllImport("kernel32.dll", SetLastError = true, CharSet = CharSet.Unicode, EntryPoint = "GetLongPathNameW")]
      [return: MarshalAs(UnmanagedType.U4)]
      internal static extern uint GetLongPathName([MarshalAs(UnmanagedType.LPWStr)] string lpszShortPath, StringBuilder lpszLongPath, [MarshalAs(UnmanagedType.U4)] uint cchBuffer);

      /// Converts the specified path to its long form as a transacted operation.
      /// <returns>
      /// If the function succeeds, the return value is nonzero.
      /// If the function fails, the return value is zero. To get extended error information, call GetLastError.
      /// </returns>
      /// <remarks>Minimum supported client: Windows Vista [desktop apps only]</remarks>
      /// <remarks>Minimum supported server: Windows Server 2008 [desktop apps only]</remarks>
      [SuppressMessage("Microsoft.Security", "CA5122:PInvokesShouldNotBeSafeCriticalFxCopRule")]
      [DllImport("kernel32.dll", SetLastError = true, CharSet = CharSet.Unicode, EntryPoint = "GetLongPathNameTransactedW")]
      [return: MarshalAs(UnmanagedType.U4)]
      internal static extern uint GetLongPathNameTransacted([MarshalAs(UnmanagedType.LPWStr)] string lpszShortPath, StringBuilder lpszLongPath, [MarshalAs(UnmanagedType.U4)] uint cchBuffer, SafeHandle hTransaction);

      /// Retrieves the short path form of the specified path.
      /// <returns>
      /// If the function succeeds, the return value is nonzero.
      /// If the function fails, the return value is zero. To get extended error information, call GetLastError.
      /// </returns>
      /// <remarks>Minimum supported client: Windows XP</remarks>
      /// <remarks>Minimum supported server: Windows Server 2003</remarks>
      [SuppressMessage("Microsoft.Security", "CA5122:PInvokesShouldNotBeSafeCriticalFxCopRule")]
      [DllImport("kernel32.dll", SetLastError = true, CharSet = CharSet.Unicode, EntryPoint = "GetShortPathNameW")]
      [return: MarshalAs(UnmanagedType.U4)]
      internal static extern uint GetShortPathName([MarshalAs(UnmanagedType.LPWStr)] string lpszLongPath, StringBuilder lpszShortPath, [MarshalAs(UnmanagedType.U4)] uint cchBuffer);

      /// Converts a file URL to a Microsoft MS-DOS path.
      /// <returns>Type: HRESULT
      /// If this function succeeds, it returns S_OK. Otherwise, it returns an HRESULT error code.
      /// </returns>
      /// <remarks>Minimum supported client: Windows 2000 Professional, Windows XP [desktop apps only]</remarks>
      /// <remarks>Minimum supported server: Windows 2000 Server [desktop apps only]</remarks>
      [SuppressMessage("Microsoft.Security", "CA5122:PInvokesShouldNotBeSafeCriticalFxCopRule")]
      [DllImport("shlwapi.dll", SetLastError = true, CharSet = CharSet.Unicode, EntryPoint = "PathCreateFromUrlW")]
      [return: MarshalAs(UnmanagedType.U4)]
      internal static extern uint PathCreateFromUrl([MarshalAs(UnmanagedType.LPWStr)] string pszUrl, StringBuilder pszPath, [MarshalAs(UnmanagedType.U4)] ref uint pcchPath, [MarshalAs(UnmanagedType.U4)] uint dwFlags);

      /// Creates a path from a file URL.
      /// <returns>Type: HRESULT
      /// If this function succeeds, it returns S_OK. Otherwise, it returns an HRESULT error code.
      /// </returns>
      /// <remarks>Minimum supported client: Windows Vista [desktop apps only]</remarks>
      /// <remarks>Minimum supported server: Windows Server 2008 [desktop apps only]</remarks>
      [SuppressMessage("Microsoft.Security", "CA5122:PInvokesShouldNotBeSafeCriticalFxCopRule")]
      [DllImport("shlwapi.dll", SetLastError = true, CharSet = CharSet.Unicode)]
      [return: MarshalAs(UnmanagedType.U4)]
      internal static extern uint PathCreateFromUrlAlloc([MarshalAs(UnmanagedType.LPWStr)] string pszIn, out StringBuilder pszPath, [MarshalAs(UnmanagedType.U4)] uint dwFlags);

      /// Converts a Microsoft MS-DOS path to a canonicalized URL.
      /// <returns>Type: HRESULT
      /// Returns S_FALSE if pszPath is already in URL format. In this case, pszPath will simply be copied to pszUrl.
      /// Otherwise, it returns S_OK if successful or a standard COM error value if not.
      /// </returns>
      /// <remarks>
      /// UrlCreateFromPath does not support extended paths. These are paths that include the extended-length path prefix "\\?\".
      /// </remarks>
      /// <remarks>Minimum supported client: Windows 2000 Professional, Windows XP [desktop apps only]</remarks>
      /// <remarks>Minimum supported server: Windows 2000 Server [desktop apps only]</remarks>
      [SuppressMessage("Microsoft.Security", "CA5122:PInvokesShouldNotBeSafeCriticalFxCopRule")]
      [DllImport("shlwapi.dll", SetLastError = true, CharSet = CharSet.Unicode, EntryPoint = "UrlCreateFromPathW")]
      [return: MarshalAs(UnmanagedType.U4)]
      internal static extern uint UrlCreateFromPath([MarshalAs(UnmanagedType.LPWStr)] string pszPath, StringBuilder pszUrl, ref uint pcchUrl, [MarshalAs(UnmanagedType.U4)] uint dwFlags);

      /// Tests whether a URL is a specified type.
      /// <returns>
      /// Type: BOOL
      /// For all but one of the URL types, UrlIs returns <see langword="true"/> if the URL is the specified type, <see langword="true"/> otherwise.
      /// If UrlIs is set to <see cref="Shell32.UrlType.IsAppliable"/>, UrlIs will attempt to determine the URL scheme.
      /// If the function is able to determine a scheme, it returns <see langword="true"/>, or <see langword="false"/>.
      /// </returns>
      /// <remarks>Minimum supported client: Windows 2000 Professional, Windows XP [desktop apps only]</remarks>
      /// <remarks>Minimum supported server: Windows 2000 Server [desktop apps only]</remarks>
      [SuppressMessage("Microsoft.Security", "CA5122:PInvokesShouldNotBeSafeCriticalFxCopRule")]
      [DllImport("shlwapi.dll", SetLastError = true, CharSet = CharSet.Unicode, EntryPoint = "UrlIsW")]
      [return: MarshalAs(UnmanagedType.Bool)]
      internal static extern bool UrlIs([MarshalAs(UnmanagedType.LPWStr)] string pszUrl, Shell32.UrlType urlIs);
   }
}


text:
define([
	'app',
	'backbone'
],

function(app, Backbone,Config) {
    'use strict';

	var Models = {};

	return Models;

});


text:
/* eslint no-console: 'off' */
'use strict';

const fs = require('fs');
const path = require('path');
const yaml = require('js-yaml');

const filePath = path.resolve(__dirname, './default.yml');
let config;

try {
  config = yaml.safeLoad(fs.readFileSync(filePath, 'utf8')) || {};
} catch (e) {
  console.error(`Error reading YAML file: ${filePath}`);
  e.message = `Cannot read config file: ${filePath} \nError: ${e.message}`;
  throw e;
}

module.exports = config;


text:
var QUERY = 'lab puppies';
var PAGE = 0;

var puppyGenerator = {
  init: function() {
    document.body.innerHTML = "";
    this.requestPuppies();
    this.reloadPuppies();
  },
  _searchOnFlickr: function(page) {
    return 'https://secure.flickr.com/services/rest/?' +
    'method=flickr.photos.search&' +
    'api_key=53caa6bf9c68533d9fc34ca096bc6c72&' +
    'text=' + encodeURIComponent(QUERY) + '&' +
    'safe_search=1&' + 'content_type=1&' +
    'sort=interestingness-desc&' + 'per_page=100&' +
    'page=' + page.toString();
  },
  requestPuppies: function() {
    var req = new XMLHttpRequest();
    req.open('GET', this._searchOnFlickr(++PAGE), true);
    req.onload = this.showPuppyPhotos.bind(this);
    req.send(null);
  },
  showPuppyPhotos: function(event) {
    var puppies = event.target.responseXML.querySelectorAll('photo');
    for (var i = 0; i < puppies.length; i++) {
      var img = document.createElement('img');
      img.src = this._constructPuppyURL(puppies[i]);
      img.setAttribute('alt', puppies[i].getAttribute('title'));
      this.setPuppyLayout(img);
      document.body.appendChild(img);
    }
  },
  _constructPuppyURL: function(photo) {
    return 'https://farm' + photo.getAttribute('farm') +
        '.staticflickr.com/' + photo.getAttribute('server') +
        '/' + photo.getAttribute('id') +
        '_' + photo.getAttribute('secret') +
        '_q.jpg';
  },
  setPuppyLayout: function(photo) {
    photo.setAttribute('style', 'border: 1px solid black; margin: 5px');
    photo.setAttribute('align', 'justify');
    photo.setAttribute('width', '143px');
    photo.setAttribute('height', '143px');
  },
  reloadPuppies: function() {
    document.addEventListener('scroll', function(event) {
      var scrollLocation = document.body.scrollHeight;
      var scrollDistance = document.body.scrollTop + window.innerHeight;
      if (scrollLocation == scrollDistance) {
        puppyGenerator.requestPuppies();
      }
    });
  }
};

(function(document) {
  console.log('Puppy overloadddd...');
  puppyGenerator.init();
})(document);