What is a back-end? A back-end is a stand-alone application program - PowerPoint PPT Presentation

What is a back-end? A back-end is a stand-alone application program or the part of an application program that manages storing data to and fetching data from secondary memory. It is often matched by a an application or part of an application that manages user interaction, called a front-end, and (mostly) by a “middle” module that manages business logic and/or data processing for the whole application, referred to as “middle-tier” or “business logic layer”. In most modern applications, the back-end leaves all kinds of processing, except perhaps conversions, to the middle-tier. DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 1 / 38

Back-end history When databases began being used for storing application data it didn’t take long to realize that it was necessary to manipulate the database from within the aplication. The first solution was to allow embedding SQL commands in the program code. A preprocessor was used to substitute the SQL code with function calls to a database management library and then compile the resulting code with a standard compiler. COBOL was the first language that supported SQL-embedding and later C followed. Among all the languages C and C++ became popular but had performance problems due to the translation to and from declarative expressions (SQL) as both are imperative languages. DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 2 / 38

Back-end history . . . When database back-end persistence began being used for the web it started with embedding SQL into languages that were already used to increase dynamics in web pages. Perl, that is made primarily for string manipulation, was a perfect tool and Perl, C and shell-scripts quickly became popular for building everything from back-ends to full-fledged applications using CGI. It was not unusual to see application programs made up by a number of CGI modules with a few HTML pages as a user interface. DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 3 / 38

Back-end nowadays Mainly Java, C#, Ruby or Python based, sometimes C/C++, with ODBC or (for Java) JDBC. On top of that a package that raises the abstraction level, with Java SQLJ, Hibernate or similar packages. Both SQLJ and Hibernate uses JDBC (can be configured to use ODBC). There are similar packages for other languages. My opinion is that you should avoid ODBC with Java as ODBC is implemented in C/C++ which gives you an extra transformation. There are good native Java SQL drivers. Use them instead. Use ODBC only with C, C++ and languages that have good APIs to C and C++ but no native implementations for DB communication. A Python equivalent to Hibenate+JDBC is e.g. Elixir+SQLAlchemy A Ruby equivalent is ActiveRecord DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 4 / 38

Some architectures I will mainly give examples in Java and assume that JDBC is installed on the CSC computers I will maintain a variety of drivers/packages (not only for Java) on /info/DD2471/moddb11/lib More may be installed if needed. DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 5 / 38

JDBC Java DataBase Connectivity (JDBC) API is (for Java) the most important API for developing database based or database driven applications. JDBC provides a standard API that is provider independant but also allows provider specific access JDBC is divided into two parts, the kernel (java.sql) that earlier was delivered with the standard package (JDK) while the other part, JDBC Optional Package (javax.sql.*), that contains the classes you need when developing applications using Enterprise Java Beans (EJB) was delivered with J2EE. Nowadays both libraries are delivered with J2SE (Java 2 Standard Edition) as database access is becoming increasingly popular (necessary?) DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 6 / 38

JDBC, conceptually Java application uses java−sql/javax.sql implements JDBC driver wraps 1..n DBMS interface Provider specific API Provider−independent API connects to connects to DBMS DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 7 / 38

JDBC, class diagram <<interface>> DriverManager registers 0..* Driver 0..* provides <<interface>> <<interface>> 1 0..* <<interface>> 0..* Connection Statement ResultSet retrieves 1 creates 1 1 provides provides <<interface>> PreparedStatement 1 1 <<interface>> <<interface>> <<interface>> DatabaseMetaData CallableStatement ResultSetMetaData DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 8 / 38

JDBC . . . There are four types of JDBC drivers, named type 1, 2, 3 and 4. It may be important to know which one to pick for your application. Really important when implementing applets. Type 1 and 2 use “native” libraries that are written in C or C++ for the specific machine or platform. It may be difficult to find such a JDBC driver for a certain platform and they must be considered ancient as they mainly need JDK ≤ 1.3 DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 9 / 38

Type 1 JDBC driver configuration A type 1 driver uses a general library database client (not db-specific) to connect to the DBMS providers connection library. Java appl The most common way is to use the database server JDK JDBC-ODBC bridge. JDBC API Data “flows” through many layers, DBMS JDBC type 1 easily giving performance problems, driver so use the method only for testing or DBMS API ODBC client driver library server library for platforms to which there are no other drivers. DBMS API client library I use it only for very odd DBMS (and haven’t used it for many years). DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 10 / 38

Type 2 JDBC driver configuration A type 2 driver uses the DBMS provider connection library directly Database client and must be delivered with the Java application DBMS or as an extra option. Database server It is a better solution than type 1 as JDBC API one layer is skipped but you still DBMS depend on things written in another JDBC type 2 driver language and get a performance loss DBMS API DBMS API because of the extra intermediary client library server library data structures that are necessary for the data transfer and tranformations. DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 11 / 38

Type 3 JDBC driver configuration Database client Database server Middle−tier Java application server application Middle−tier DBMS listener JDBC API DBMS API DBMS API JDBC type 3 client library server library driver A type 3 JDBC driver is implemented entirely in Java using a DBMS provider independant protocol to communicate with a “gateway”. The typical situation is when you need better security or you need to manage security closer to the data source or simply want to bypass applet security restrictions. Because of the middle tier slower than other solutions. The most common case for distributed applications is to divide them into tiers where parts of the application reside on the same server as the database server. On of these is the application middle tier containing application logics. DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 12 / 38

Type 4 JDBC driver configuration Before the advent of JIT Database client Database server (Just-In-Time) compilers, type 2 Java application drivers were popular because of their DBMS speed. JDBC API Nowadays type 4 are the most popular. No need for transformations JDBC type 4 DBMS API driver server library via JNI so 1 layer less. The fastest solution and works on all platforms but provider specific so you A type 4 JDBC driver is need one per DBMS provider. On the implemented entirely in Java other hand most DBMS providers and connects directly to the provide type 4 JDBC drivers. database server. Observe! that Java 1.6 and better require type 4 drivers DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 13 / 38

JDBC drivers and source code • Remember that your choise of driver doesn’t require changes in the source code. • You specify which driver ti use and it works (if there are no errors in your code). • Errors do not depend on the driver. • For the API the driver type doesn’t matter. • It is possible to make programs that read driver info from the command line or asks for it at application startup. Many commersial applications use a JDBC-ODBC bridge as default and allows for driver specification from the command line or in a configuration file. If there is a driver specification then that driver will be used instead of the default DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 14 / 38

Example database ER model I depart from the sample database (the department store/warehouse) implemented in postgreSQL on nestor(nestor2.csc.kth.se). dept floor name salary manager employee works_on department volume supply sales volume supplier item company address itemno type DD2471 (Lecture 10) Modern database systems & their applications Spring 2012 15 / 38