Thus, all of the above disadvantages of the file-server scheme are eliminated in the client-server architecture. Client-server technology

Previously, local databases were considered, when both the database and the application interacting with it are located on the same computer. This section will discuss some of the features of working with remote databases used on a network, where the application and the database are located on different computers.

In principle, a local database can also be used for collective access, i.e. in a network version. In this case, the database files and the application for working with it are located on the network server. The user launches an application located on the server from his computer, and a copy of the application is launched for him. You can install the application directly on the user's computer; in this case, the application must know the location of the shared database, specified, for example, through an alias. This network option for using a local database corresponds to a file-server architecture.

The advantages of the file-server network architecture are the ease of development and operation of the database and application. The developer actually creates a local database and application, which are then simply used in the network version. In this case, no additional software is required to organize work with the database. However, the file-server architecture also has significant disadvantages. To work with data, a navigational access method is used, and large volumes of data circulate through the network. As a result, the network becomes overloaded, which is the reason for its low speed and poor performance when working with the database. Work synchronization required individual users, associated with blocking in tables those records that are edited by another user. Applications not only process data, but also manage the database itself. Due to the fact that the database is managed from different computers, it becomes difficult to manage access, maintain confidentiality, and maintain the integrity of the database.

Because of these disadvantages, file server architecture is typically used in small networks. For networks with a large number For users, the preferred option (and sometimes the only possible one) is the client-server architecture. In the client-server network architecture, the database is located on the network server computer (server or remote server) and is also called a remote database. The application that works with this database is located on the user's computer. The user application is the client, also called the client application. The client and server interact as follows. The client generates and sends a request to the server on which the database is located. The server executes the request and provides the client with the required data as results. Thus, in a client-server architecture, the client sends a request and receives only the data that it really needs. All request processing is performed on the remote server. The advantages of such an architecture include the following factors. To work with data, a relational access method is used, which reduces the load on the network. Applications do not directly manage the database; only the server is involved in management. In this regard, a high degree of data protection can be ensured. There is no code associated with database management in the application, so applications are simplified.

Note that a server is called not only a computer, but also a special program that manages the database. Since the basis for organizing data exchange between client and server is SQL language such a program is also called a SQL server, and the database is also called a SQL database. In the broad sense of the word, a server means a computer, a program and the database itself. SQL servers are industrial DBMSs, such as InterBase, Oracle, etc. Each of the servers has its own advantages and features associated, for example, with the structure of the database and the implementation of the SQL language, which must be taken into account when developing the application. Further, we will understand the server as a program (i.e., a SQL server), and the database installed on the server computer will be called a remote database.

When running in a client-server architecture, the application must:

· establish a connection with the server and terminate it;

· generate and send a request to the server, receiving from it the results of the request;

· process the received data.

In this case, data processing does not have fundamental differences compared to data processing in local databases.

A remote database, like a local one, is a collection of interconnected tables. However, the data in these tables is usually contained in one shared file. As in the case of a local database, connections (relationships) and restrictions can be established for tables in a remote database referential integrity, restrictions on column values, etc. For remote databases, the field is called a column. To manage the database, the server uses:

· triggers;

· generators;

· stored procedures;

· user defined functions;

· transaction mechanism;

· mechanism of cached changes;

Many of these elements are provided by language capabilities SQL server, which, compared to the local version, has significant features discussed below.

The Delphi system supports the development of applications for various servers, providing the appropriate tools for this. Note that many of the application development principles and tools for working with local databases described earlier also apply to working with remote databases. In particular, components such as DataSource, data_setsTable, ADOTable, SQLTable, IBTable, Query, ADOQuery, SQLQuery, DBGrid, etc. are used for application development.

To implement a relational method of accessing a remote database using BDE, you must use only SQL language tools. Therefore, components such as Query, StoredProc, UpdateSQL should be selected. Additionally, the data set cannot use navigation-specific methods.

Let us remind you that if, when executing a query modifying a database using the Query component, the resulting data set is not needed, then it is preferable to execute this query using the ExecSQL method. You can still use programs such as Database Desktop and SQL Explorer to work with tables and queries.

Delphi tools designed for working with remote databases can be divided into two types: tools and components.

Tools include special programs and packages that provide database maintenance outside of the applications being developed. Among them:

· InterBase Server Manager - program for managing the launch of the InterBase server;

· IBConsole - InterBase server console;

· SQL Monitor is a program for tracking the order of execution of SQL queries to remote databases.

The components are intended for creating applications that perform operations with a remote database. We list the most important of them:

· Database (database connection);

· Session (current session of working with the database);

· StoredProc(stored procedure call);

· UpdateSQL (modification of a data set based on an SQL query);

· DCOMConnection(DCOM connection);

· page components ADO, dbExpress, Interbase Component palettes.

Note that many of the named components, for example, Database, Session, UpdateSQL, are also used when working with local databases. Thus, the Database component allows you to implement a transaction mechanism with a navigational method of accessing data using the BDE mechanism. However, these components are most often used when working with remote databases. Some components, for example, the client data set ClientDataSet and the connection to the server DCOMConnection, are designed to work in a three-tier (three-tier) client-server architecture (thin client) and are used to build an application server.

Operations performed with remote databases, both using tools and programmatically, are based on the SQL language. For example, when creating a table using the IBConsole program, you need to type and execute the SQL query (instruction) Create Table. If a table is created using the BDE mechanism from the user's application, then the Query data set is used for this purpose, which performs the same query. The main difference is how the SQL query is executed against the remote database.

So, for remote databases, the difference between the tools used in the application and the tools is much less than for local databases.

InterBase server.All servers have similar principles for organizing and managing data. As an example, let's look at working with the InterBase 6.x server, which is native to Delphi. Together with Delphi, two parts of the InterBase 6.x server are supplied: server and client. The InterBase server part is a local version of the InterBase server and is used for debugging applications designed to work with remote databases, allowing you to test them in a network version on one computer. After debugging on the local computer, the application can be transferred to network computers without changes, for which you need:

· copy the database to the server;

· set new connection parameters for the application with the remote database.

You can copy the database using programs like Windows Explorer. The client part is needed to provide application access to a remote database. When developing databases and applications using the local version of the InterBase server, you need to keep in mind that it has a number of limitations and may not support, for example, the server event mechanism or user-defined functions. A full-featured version of the InterBase server is purchased and installed separately from Delphi. As mentioned, working with a remote database is based on the capabilities of the SQL language, which provide the corresponding operations. The purpose and capabilities of the SQL language for remote databases are, in principle, the same as the purpose and capabilities of this language for local databases.

Business rules. As noted, business rules are database management mechanisms and are designed to maintain the database in an integral state. In addition, they are needed to implement database restrictions, as well as to perform a number of other actions, for example, accumulating statistics on working with the database.

Business rules can be implemented at the physical and software levels. In the first case, these rules (for example, referential integrity constraints for related tables) are specified when creating tables and are included in the database structure. To do this, the syntax of the Create Table statement includes appropriate operands, for example, Default. In further work, it is impossible to violate or bypass the restriction specified at the physical level.

At the software level, business rules can be implemented in the server and in the application. Moreover, these business rules do not have to be defined at the physical level. Triggers are typically used to implement business rules on the server. The advantages of this approach are that the computational load for managing the database falls entirely on the server, which reduces the load on the application and network, and also that the restrictions apply to all applications accessing the database. However, at the same time, the flexibility of database management is reduced. In addition, you need to take into account that the tools for debugging server triggers and stored procedures are not well developed.

To program business rules in the application, components and their tools are used. The advantage of this approach is the ease of changing business rules and the ability to define the rules of “your” application. The disadvantage is the reduction in database security due to the fact that each application can set its own database management rules.

Information from the entire InterBase server database is stored in one file with the gdb extension. The size of this file can be units or even tens of gigabytes. Note that the Microsoft SOL Server DBMS has a similar database size, while for the more powerful Oracle and SyBase DBMSs the database size reaches tens and hundreds of gigabytes.

Unlike a local database, the structure of which was made up of tables (separate or related), a remote database has a more complex structure, which includes the following elements: tables, triggers, indexes, user functions. constraints, stored procedures, domains, views, generators, exceptions, privileges.

Elements of a remote database structure are also called metadata. The word "meta" has the meaning "above", i.e. metadata is data that describes the structure of the database. For InterBase, the maximum number of tables in a database is 65,536, and the maximum number of columns in a table is 1000. Note that InterBase tables have fewer allowed column (field) types than local Paradox database tables.

Domain represents a named description of a column. Once a domain is defined, its name can be used to describe other columns. The equivalent of a domain is a data type.

View is a logical (virtual) table whose records are selected using the Select statement. The advantage of browsing is that once you select records, they can be used in the future without running Select again. This is beneficial when running the same queries frequently.

Stored procedure is a subroutine located on the server and called from the client application. Using these objects increases the speed of access to the database for the following reasons:

· instead of the request text, a short call to a stored procedure is sent to the server over the network;

· The stored procedure does not require any syntax checking beforehand.

Trigger is a procedure that is located on the database server and is called automatically when modifying database records, i.e. when columns are changed or when they are removed or added. Unlike stored procedures, triggers cannot be called from a client application, nor can you pass parameters to them or receive results from them.

User Defined Function is a regular function written in an algorithmic language such as Pascal. The created function is designed as a dynamic DLLs, from where it can be called in the usual way. To enable a function call, Windows must know the path to the corresponding library. The use of such functions expands the range of functions of the SQL language.

Mechanism of cached changes is that a local copy of the data is created in a cache (buffer) on the client’s computer, and all changes to the data are made in this copy. A special buffer (cache) is used to store a local copy. The changes made can be confirmed by transferring them to the main database stored on the server, or abandoned. This mechanism is similar to the transaction mechanism, but, unlike it, it reduces the network load, since all changes are transferred to the main database in one packet. Please note that all records in the local copy are not locked to change their values. Locks can be set by other applications for the main database located on the server. The mechanism of cached changes is implemented in the application, for which components, primarily Database, Table and Query (used when accessed using BDE), have appropriate tools. In addition, the mechanism of cached changes is supported by the UpdateSQL component intended for this. The main advantages of the mechanism in question appear for remote databases, but it can also be used when working with local databases.

Privilege represent access rights to the database. Managing privileges involves setting and removing them. Once a database object (for example, a table) is created, access to it is limited to the creator and the system administrator named SYSDBA. To access the database for other users, they need to be assigned the appropriate privileges. Immediately after the appearance of a new user, created for example using the InterBase Manager Server program, this user has minimal access rights: he is only allowed to enter the database (connect to it) by specifying his name and password, but not a single object of this database is available to him . To ensure the ability to actively work with the database, you need to define (override) privileges.

Privileges are set by the Grant instruction. Privileges allow you to restrict user access to tables and views. In this case, the “user” is understood as any object accessing the data. In addition to the user (application) itself, such objects can be tables, views, stored procedures and triggers. If a privilege is granted to several users at the same time, their names are separated by commas.

In this example, we will develop a simple server and a simple client program that conduct a “leisurely” dialogue with each other. We will build the client according to technology Windows Forms, and the server - Windows Service . The server will have a set of ready-made marked responses, wait for marked requests from clients and respond to them with appropriate messages. This will set us up to create an even more complex system - viewing remote drawings from the database, which we will deal with later.

Creating a client

Let's start with a client program that can run in many instances ("http://msdn.microsoft.com/ru-ru/library/system.net.sockets.tcplistener.accepttcpclient.aspx")

Rest required settings We will add form elements programmatically.

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; // Additional namespaces using System.IO; using System.Net; using System.Net.Sockets; using System.Threading; namespace SimpleClient ( public partial class Form1: Form ( int port = 12000; String hostName = "127.0.0.1"; // local TcpClient client = null; // Client reference public Form1() ( InitializeComponent(); // Select the first list item listBox.SelectedIndex = 0; listBox.Focus(); // Context menu for clearing TextBox ContextMenuStrip contextMenu = new ContextMenuStrip(); textBox.ContextMenuStrip = contextMenu item = new ToolStripMenuItem("Clear"); Add(item); item.MouseDown += new MouseEventHandler(item_MouseDown); // Send a request and receive a response private void btnSubmit_Click(object sender, EventArgs e) ( if (listBox.SelectedIndices.Count == 0) ( MessageBox.Show ("Select message"); return; ) try ( // Create a client connected to the server client = new TcpClient(hostName, port); // Set the size of the clipboards yourself (Optional!) client.SendBufferSize = client.ReceiveBufferSize = 1024 ; ) catch ( MessageBox.Show("Server is not ready!"); return; ) // Write the request to the protocol AddString("Client: " + listBox.SelectedItem.ToString()); // Create NetworkStream streams connected to the server NetworkStream streamIn = client.GetStream(); NetworkStream streamOut = client.GetStream(); StreamReader readerStream = new StreamReader(streamIn); StreamWriter writerStream = new StreamWriter(streamOut); // Send a request to the server writerStream.WriteLine(listBox.SelectedItem.ToString()); writerStream.Flush(); // Read the response String receiverData = readerStream.ReadLine(); // Write the response to the protocol AddString("Server: " + receiverData); // Close the connection and streams, the order is not important client.Close(); writerStream.Close(); readerStream.Close(); ) // Adding a line when the TextBox is enabled in Multiline mode private void AddString(String line) ( StringBuilder sb = new StringBuilder(textBox.Text); StringWriter sw = new StringWriter(sb); sw.WriteLine(line); textBox.Text = sw.ToString(); ) // Clearing the list via the context menu void item_MouseDown(object sender, MouseEventArgs e) ( textBox.Clear(); listBox.Focus(); ) )

Having received a connection to the server, we create two threads NetworkStream and pack them into shells that are convenient for reading/writing control. The exchange with the server is displayed in the protocol TextBox. To clear the protocol, a context menu was dynamically created.

Class TcpClient, which we used in the code, is a high-level (and simplified) wrapper around the socket (class Socket). If lower-level (more detailed) socket control is required, then a link to it is stored in the TcpClient.Client property. But since this property is protected ( protected), then it can only be accessed from a derivative of TcpClient class.

If you run the application now SimpleClient, then it will work, but when you try to send something to the server, a message will be displayed that the server is not ready. There is no server yet, let's create one.

Creating a server

Let's make the server program resident according to the template Windows Service, as we did in the previous example, although it can be done with the interface, the main thing is that it is launched in a single copy on the local computer. If the server program is included in the global network, then with the used IP and it should be the only port in this network. Therefore, the use of network IP For the global network you need to obtain permission.

using System; using System.ComponentModel; using System.Configuration.Install; using System.ServiceProcess; namespace SimpleServer ( // During installation of the assembly, the installer should be called public partial class Installer1: Installer ( private ServiceInstaller serviceInstaller; private ServiceProcessInstaller serviceProcessInstaller; public Installer1() ( // Create settings for the Service serviceInstaller = new ServiceInstaller(); serviceProcessInstaller = new ServiceProcessInstaller( ); // Service name for the machine and user serviceInstaller.ServiceName = "SimpleServerServiceName"; serviceInstaller.DisplayName = "SimpleServer"; serviceInstaller.StartType = ServiceStartMode.Manual; // Manual start // How the Service will be started this.serviceProcessInstaller.Account = ServiceAccount.LocalService; this.serviceProcessInstaller.Password = null; this.serviceProcessInstaller.Username = null; // Add settings to the collection of the current object this.Installers.AddRange(new Installer ( serviceInstaller, serviceProcessInstaller ) ) )

using System; using System.Collections.Generic; using System.Text; // Additional namespaces using System.IO; using System.Net; using System.Net.Sockets; using System.Threading; using System.ServiceProcess; using System.Collections; namespace SimpleServer ( class Service1: ServiceBase ( TcpListener server = null; // Link to server int port = 12000; String hostName = "127.0.0.1"; // local IPAddress localAddr; String answers = ( "1. Who are you?", "2. Hello, Lelik!", "3. Best of all!", "4. Of course, to the fullest", "5. See you in the evening!" ); // Constructor public Service1() ( localAddr = IPAddress.Parse( hostName);// Convert to another format Thread thread = new Thread(ExecuteLoop); thread.IsBackground = true; thread.Start(); ( try ( server = new TcpListener(localAddr, port);/ / Create a listener server server.Start();// Start the server String data; // Infinite loop listening to clients while (true) ( ​​if (!server.Pending()) // The request queue is empty continue; TcpClient client = server.AcceptTcpClient(); // Current client // We set the sizes of the clipboards ourselves (Optional!) // By By default, both buffers are set to 8192 bytes in size client.SendBufferSize = client.ReceiveBufferSize = 1024; // Connect NetworkStream and load it into shells for convenience NetworkStream streamIn = client.GetStream(); NetworkStream streamOut = client.GetStream(); StreamReader(streamIn); StreamWriter writerStream = new StreamWriter(streamOut); // Read the request data = readerStream.ReadLine(); // Send the response int index; if (int.TryParse(data.Substring(0, data.IndexOf(" ")), out index)) data = answers; else data = data.ToUpper(); writerStream.WriteLine(data); writerStream.Flush(); // Close the connection and streams, the order is not important client.Close(); readerStream.Close(); writerStream.Close(); ) catch (SocketException) ( ) finally ( // Stop the server server.Stop(); ) ) ) )

To send data and receive a response, client application creates a bidirectional socket (or two unidirectional ones), in which it specifies the address for connecting to the socket of another server application. If the connection is established (the server is running), then the client application connects a network stream to the socket NetworkStream and through it transmits and receives data.

There is a server on the other side of the connection TcpListener listens in an endless loop connection queue with clients. If some client connected to it ( server.Pending()!=false), then the server retrieves this client using AcceptTcpClient()- creates a socket for receiving/transmitting with a ready return address, creates a bidirectional flow (or two unidirectional ones), then reads the request and transmits the response.

Please note that if the code of our server program is not packaged into a separate thread Thread(execution thread), then the operating system will not launch this program in the services window (try it!). The reason is that in the method code ExecuteLoop() The server uses an endless loop of listening to a queue of client requests. If this loop is left in the main thread of execution ( Thread) application, it will simply go into a loop and will not be able to terminate normally. Therefore, we place the code with the loop in a separate thread (thread) and make it background so that it closes along with the main application thread (server thread).

Important Note

Flow NetworkStream is double-sided fixed length. Method GetStream() it only establishes an address connection between the client and server sockets. But its actual length is determined by the message from the sending party. You can use one thread for receiving/transmitting, but then the length of the message sent by the server should not exceed the length of the message it received from the client (I almost lost my eye!). That's why we use two streams on each side for separate unidirectional transmission between two network connection nodes.

Example 3. Client-server application for viewing pictures from a database

In the previous simple example, we became acquainted (a little) with the principles of creating network applications. Now let's build a more complex example, where the client requests pictures, and the server retrieves them from storage and sends them to the client. IN Exercise 7 We have developed three different image repositories and three viewing programs. IN in this example let's use the database Pictures.my2.mdb with ready-made drawings and based on it we will create network application(for those who haven't Exercise 7, DB is attached in the catalog Source/Data).

Building a client

For the client, we will build a window application like WPF With user interface, partly borrowed from Example 6 Exercises 7.

The server is not ready, please wait! We are trying to contact sorry for the inconvenience...

To display a splash screen with the text that the server is not ready, we used the element Viewbox, in which another element was placed Border with text content. This “garden” will allow you to increase the screensaver in proportion to the size of the window. However, the introduction of the element Viewbox begins to noticeably slow down the redrawing of the interface when moving the window, because it tries to constantly recalculate the scales of its child elements. We assigned names only to those interface elements that we are going to manage in procedural code.

using System; using System.Collections.Generic; using System.Text; using System.Windows; using System.Windows.Controls; using System.Windows.Data; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Animation; using System.Windows.Media.Imaging; using System.Windows.Shapes; // Additional namespaces for Stream using System.IO; using IO = System.IO; // Alias ​​for addressing Path using System.Windows.Threading; // For DispatcherTimer // Additional namespaces for Socket //using System.Net; using System.Net.Sockets; using System.Collections; // List namespace PicturesClientDB ( public partial class Window1: Window ( int port = 12000; String hostName = "127.0.0.1"; // local TcpClient client = null; // Client link String sendMessage = "!!!GetNames!!!"; // Request for a list (tricky) Char separator = ( "#" ); // To convert the response to an array of names DispatcherTimer timer; // Timer // Constructor public Window1() ( InitializeComponent(); // Create and start a timer timer = new DispatcherTimer(); timer.Tick += new EventHandler(timer_Tick); timer.Interval = TimeSpan.FromSeconds(1); timer.Start(); // Initiates a call to the server void timer_Tick(object sender, EventArgs e) ( Execute(listBox); ) private void listBox_SelectionChanged(object sender, SelectionChangedEventArgs e) ( Execute((ListBox)sender); ) void Execute(ListBox lst) ( // Fill the list with picture names try ( // If the server is available, create a client client = new TcpClient(hostName, port) catch ( // The server is not ready, start the timer and exit if (Prompt.Visibility != Visibility.Visible) ( Prompt.Visibility = Visibility.Visible; timer.Start(); ) return; ) switch (sendMessage) ( case "!!!GetNames!!!": // Receive and bind the names of pictures to the list lst.ItemsSource = GetNames(); // Select the first element of the list to call SelectionChanged lst.SelectedIndex = 0; lst.Focus(); sendMessage = ""; break; default: // Hide the message and stop the timer if (Prompt.Visibility == Visibility.Visible) ( Prompt.Visibility = Visibility.Hidden; timer.Stop(); ) / / Receive the image and display it to the user with a brush String name = lst.SelectedValue.ToString(); BitmapImage bi = new BitmapImage(); // Receive the image from the server and wrap it in a memory stream bi.StreamSource = new MemoryStream (GetPicture(name)); bi.EndInit(); Pictures.picture.ImageSource = bi;// Pass the picture to the background Border break) ) private String GetNames() ( String names; // Create network connection streams StreamReader readerStream = new StreamReader(client.GetStream()); StreamWriter writerStream = new StreamWriter(client.GetStream()); // Send a request to the server writerStream.WriteLine(sendMessage); writerStream.Flush(); // Read the response String receiverData = readerStream.ReadLine(); names = receiverData.Split(separator); // Convert to a string array // Close the connection and streams, the order is not important client.Close(); writerStream. Close(); readerStream.Close(); return names; ) Byte GetPicture(String name) ( // Create streams of network connections NetworkStream readerStream = client.GetStream(); StreamWriter writerStream = new StreamWriter(client.GetStream()); // Send a request to the server writerStream.WriteLine(name); writerStream. Flush(); // Read the response // ReceiveBufferSize - buffer size for incoming data // SendBufferSize - buffer size for outgoing data List list = new List (client.ReceiveBufferSize); // Incremental capacity Byte bytes = new Byte; // Socket buffer size int count = 0; // Portions of incoming data while ((count = readerStream.Read(bytes, 0, bytes.Length)) != 0) for (int i = 0; i< count; i++) list.Add(bytes[i]); // Преобразуем в массив результата bytes = new Byte; list.CopyTo(bytes); // Закрываем соединение и потоки, порядок неважен client.Close(); writerStream.Close(); readerStream.Close(); return bytes; } } // Для привязки к ресурсу class Pictures { // Поле public static ImageBrush picture = new ImageBrush(); static Pictures() { // Дежурный рисунок заставки picture.ImageSource = new BitmapImage(new Uri(@"flower2.jpg", UriKind.Relative)); picture.Stretch = Stretch.Fill; picture.Opacity = 1.0D; } // Привязываемое в интерфейсе свойство public static ImageBrush Picture { get { return picture; } } } }

Please note that when displaying pictures we have abandoned the traditional element Image, as was done in the previous exercise. And for a change, they acted completely unconventionally (in Turkish). Now we will display the drawings with a brush ImageBrush in the background of a rectangle Border through an object attached to it Pictures. Of course, in life it’s unlikely that you’ll have to pervert this way, but this option might come in handy somewhere.

The splash screen will appear as soon as the fact that the server is missing or stopped is detected. And after the server is detected, the screensaver will disappear. This mechanism will work immediately thanks to the system timer. However, there is no server yet and it should be made.

Building a database server as a service

• Team File/Add/New Project add to solution NetworkStream new project named PicturesServerDB type Windows Service

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Diagnostics; using System.ServiceProcess; using System.Text; // Additional namespaces for ADO.NET using System.Data.OleDb; using System.Data.Common; // Additional namespaces using System.IO; using System.Net; using System.Net.Sockets; using System.Threading; using System.Collections; namespace PicturesServerDB ( public partial class Service1: ServiceBase ( int port = 12000; String hostName = "127.0.0.1"; // local IPAddress localAddr; TcpListener server = null; // Link to the server String separator = "#"; // Separator names in the response line String connectionString; // Database connection string public Service1() ( // Extract the database connection string from the App.config file into the field connectionString = System.Configuration.ConfigurationManager. ConnectionStrings["PicturesDB"].ConnectionString; / / Convert the IP to another format localAddr = IPAddress.Parse(hostName); // Run in a new thread (thread) Thread thread = new Thread(ExecuteLoop); thread.IsBackground = true; thread.Start(); ) ( try ( server = new TcpListener(localAddr, port);// Create a server-listener server.Start();// Start the server // Endless loop of listening to clients while (true) ( ​​// Check the connection queue if (!server .Pending()) // The request queue is empty continue; TcpClient client = server.AcceptTcpClient();// Current client // Create network connection streams StreamReader readerStream = new StreamReader(client.GetStream()); NetworkStream streamOut = client.GetStream(); StreamWriter writerStream = new StreamWriter(streamOut); // Read the client command String receiverData = readerStream.ReadLine(); // Recognize and execute switch (receiverData) ( case "!!!GetNames!!!":// Send names separated by a separator String names = GetNames(); writerStream.WriteLine(names); // Use writerStream.Flush through the shell (); break; default:// Send the picture Byte bytes = GetPicture(receiverData); streamOut.Write(bytes, 0, bytes.Length); // Use directly streamOut.Flush(); break; threads, order does not matter client.Close(); readerStream.Close(); writerStream.Close(); ) ) finally ( // Stop the server server.Stop(); ) ) // Retrieving picture names from the database and packaging them into one line for sending to the client string GetNames() ( // Create and configure the ADO infrastructure. NET OleDbConnection conn = new OleDbConnection(connectionString); OleDbCommand cmd = new OleDbCommand("SELECT FileName FROM MyTable"); cmd.Connection = conn; conn.Open(); // Retrieve picture names OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // Form a string of outgoing data StringBuilder sb = new StringBuilder(); foreach (DbDataRecord record in reader)// Equivalent to reading reader.Read() sb.Append(((string)record["FileName"]).Trim() + separator); // The connection here will close the DataReader object itself after reading all the data // in accordance with the convention when it was created CommandBehavior.CloseConnection // Remove the extra last character of the separator sb.Replace(separator, String.Empty, sb.ToString(). LastIndexOf(separator ), separator.Length); return sb.ToString(); ) // Retrieving the picture itself from the database to send to the client byte GetPicture(String name) ( // Create and configure the ADO.NET OleDbConnection infrastructure conn = new OleDbConnection(); conn.ConnectionString = connectionString; // Create and configure a command object parameterized by picture name OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; // Set to default cmd.CommandText = "SELECT Picture FROM MyTable WHERE FileName=?"; .Add(new OleDbParameter()); cmd.Parameters.Value = name;// Name of the picture OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); // Retrieving the picture from the database DataTable table = new DataTable(); ; byte bytes = (byte)table.Rows["Picture"]; // Connect to the picture return bytes;

By client-server application we will mean an information system based on the use of database servers (see the long note at the end of section 2.1). A general representation of an information system in a client-server architecture is shown in Figure 2.3.

• On the client side, the application code is executed, which necessarily includes components that support the end-user interface, produce reports, and perform other application-specific functions (for now, we will not be interested in how the application code is built).
• The client part of the application interacts with the client part of the database management software, which, in fact, is the individual representative of the DBMS for the application.

(Here again shortcomings in terminology appear. Usually, when a company announces the release of another database server, it is implicitly understood that there is also a client component of this product. The combination “client part of the database server” seems somewhat strange, but we will have to use this term.)

Rice. 2.3. General representation of an information system in a client-server architecture

Note that the interface between the client part of the application and the client part of the database server is usually based on the use of the SQL language. Therefore, functions such as, for example, pre-processing forms intended for database queries or generating the resulting reports are performed in the application code.

Finally, the client side of the database server, using the tools network access, accesses the database server, passing it the text of the SQL statement.

Two more remarks need to be made here.

1. Typically, companies producing advanced database servers strive to ensure that their products can be used not only in today's standard TCP/IP-based networks, but in networks based on other protocols (for example, SNA or IPX/SPX) . Therefore, when organizing network interactions between the client and server parts of the DBMS, non-standard tools are often used high level(for example, mechanisms of software nests or remote procedure calls), and their own functionally similar tools, less dependent on the characteristics of network transport protocols.
2. When we talk about an interface based on the SQL language, we need to be aware that despite the titanic efforts to standardize this language, there is no implementation in which the standard language features would not be extended. Careless use of language extensions leads to complete dependence of the application on a specific database server manufacturer.

We will look at these issues in more detail in Part 4 of the course.

Let's now see what happens on the database server side. In the products of almost all companies, the server receives the operator text in SQL from the client.

• The server compiles the resulting statement. We will not dwell here on which target language is used by a particular compiler; used in different implementations different approaches(see part 4 for examples). The main thing is that in any case, based on the information contained in the database catalog tables, the non-procedural representation of the operator is converted into some procedure for its execution.
• Next (if compilation is completed successfully), the statement is executed. Again we will not discuss technical details as they vary between implementations. Let's consider possible actions SQL statements.
• An operator may belong to the class of operators for defining (or creating) database objects (it would be more accurate and correct to talk about database schema elements or metadatabase objects). In particular, domains, tables, integrity constraints, triggers, user privileges, and stored procedures can be defined. In any case, when the database schema element creation statement is executed, the corresponding information is placed in the database catalog tables (in the metadatabase tables). Integrity constraints are typically stored in the metadatabase directly in a textual representation. For actions defined in triggers and stored procedures, procedural executable code is generated and stored in catalog tables. Note that integrity constraints, triggers, and stored procedures are, in a sense, representatives of the application in the server-maintained database; they form the basis of the application backend (see below).
• When data fetch statements are executed, based on the contents of the tables affected by the query, and possibly using indexes maintained in the database, a result set of data is formed (we intentionally do not use the term “result table” here because, depending on the specific type of statement, the result may be ordered, and tables, i.e., relationships are unordered by definition). The server part of the DBMS sends the result to the client part, and final processing is performed in the client part of the application.
• When executing database content modification statements (INSERT, UPDATE, DELETE), it is checked that the integrity constraints defined at this point (those that are immediately verified) will not be violated, after which the corresponding action is performed (accompanied by modification of all relevant indexes and logging changes). Next, the server checks whether this change affects the trigger condition of any trigger, and if such a trigger is detected, it executes the procedure for its action. This procedure may include additional database modification statements that may cause other triggers to fire, etc. We can consider the actions that are performed on the database server when checking whether integrity constraints are satisfied and when triggers are fired to represent the actions of the server side of the application.

When executing database schema modification statements (adding or removing columns of existing tables, changing the data type of an existing column of an existing table, etc.), triggers can also fire, i.e., in other words, the server side of the application can be executed.

In general, in a file server architecture we have a "thick" client and a very "thin" server in the sense that almost all the work is done on the client side and only sufficient capacity is required from the server disk memory(Figure 2.2).

Rice. 2.2. "Fat" client and "thin" server in file-server architecture

Brief conclusions. Simple, small-scale, and single-user-ready, a file server application can be designed, developed, and debugged very quickly. Very often, for a small company to maintain, for example, personnel records, it is enough to have an isolated system running on a separate PC. Of course, in this case too, great care is required by end users (or administrators, whose availability in this case is questionable) to securely store and maintain the integrity of the data. However, in little more than difficult cases(for example, when organizing an information system to support a project carried out by a group), file-server architectures become insufficient.

Are unequal components information network. Some own a resource and are therefore called servers; others access these resources and are called clients. Let's look at how they interact with each other and what the client-server architecture is.

The “Client-Server” architecture is the interaction of structural components in a network based on those defined by a given network, where the structural components are the server and nodes that provide certain specialized functions (services), as well as clients who use this service. Specific functions are usually divided into three groups based on solving specific problems:

• data input and presentation functions are designed for user interaction with the system;
• applied functions - each has its own set;
• resource management functions are designed to manage the file system, various databases and other components.

For example, a computer without network connection, represents the presentation, application and control components on various levels. These levels include the operating system, application and utility software, and various utilities. In the same way, all the above components are presented on the Internet. The main thing is to properly ensure network interaction between these components.

Client-server architecture is most often used to create corporate databases in which information is not only stored, but also periodically processed various methods. The database is the main element of any corporate information system, and the server houses the core of this database. Thus, the most complex operations related to data entry, storage, processing and modification take place on the server. When a user (client) accesses a database (server), the request is processed: directly accessing the database and returning a response (processing result). The result of processing is a network message about the success of the operation or an error. Server computers can handle simultaneous requests from multiple clients for the same file. Such work over the network allows you to speed up the work of the applications you use.

This architecture is used to access various resources using network technologies: databases, mail servers, firewalls, proxy servers. The development of client-server applications can improve the security, reliability and performance of the applications used and the network as a whole. Client-server applications are most often used for business automation.

A modern DBMS must satisfy a number of requirements, the most important of which is a high-performance intelligent database server. Next, we will consider the main trends in its development and discuss the specific mechanisms in which they are embodied.

The process of technical improvement of the database server still remains invisible to most users of modern DBMSs. Therefore, when choosing a particular system, they, as a rule, do not take into account either the technical level of the solutions embedded in the mechanism of its operation, or the impact of these decisions on the overall performance of the DBMS. Meanwhile, its quality is not determined by the richness of user interfaces or the variety of development support tools, but primarily depends on the features of the database server architecture. Next, models of the client-server technology will be considered, the place of the database server in these models will be determined, and the most important mechanisms of the database server - procedures, rules (triggers), events - will be briefly described. The latter will be illustrated with examples that use the SQL dialect adopted in the Ingres DBMS.

Technology and client-server models.

"Client-server" is a model of interaction between computers on a network. As a rule, computers are not equal. Each of them has its own purpose, different from the others, and plays its own role. Some computers on the network own and manage information and computing resources, such as processors, a file system, a mail service, a print service, and a database. Other computers have the ability to access these services using the services of the former. The computer that manages this or that resource is usually called the server of this resource, and the computer that wants to use it is called the client. A specific server is determined by the type of resource it owns. So, if the resource is a database, then we are talking about database server, whose purpose is to serve customer requests related to data processing; if the resource is a file system, then we talk about file server, or file server, etc.

On a network, the same computer can act as both a client and a server. For example, in an information system that includes personal computers, a mainframe computer, and a minicomputer running UNIX, the latter can act both as a database server, serving requests from clients - personal computers, and as a client, sending requests to the mainframe computer.

The same principle applies to the interaction of programs. If one of them performs certain functions, providing others with a corresponding set of services, then such a program acts as a server. Programs that use these services are called clients. Thus, the core of a relational SQL-oriented DBMS is often called a database server, or SQL server, and the program that accesses it for data processing services is called a SQL client.

Initially, DBMSs had a centralized architecture (Figure 10). In it, the DBMS itself and application programs that worked with databases operated on a central computer (mainframe or minicomputer). The databases were also located there. Terminals were connected to the central computer and acted as user workstations. All processes associated with data processing, such as: support for user input, generation, optimization and execution of queries, exchange with external memory devices, etc., were performed on the central computer, which placed strict demands on its performance. Features of the first generation DBMS are directly related to the architecture of mainframe and minicomputer systems and adequately reflect all their advantages and disadvantages. However, we are more interested in the current state of multi-user DBMS, for which the client-server architecture has become the de facto standard.

Figure 10 – Systems with centralized architecture

To get a clearer idea of ​​its features, it is necessary to consider several models of client-server technology, which will be done.

If it is assumed that the projected information system(IS) will have a client-server technology, which means that application programs implemented within its framework will be distributed in nature. In other words, some of the functions of the application program (or, more simply, the application) will be implemented in the client program, others - in the server program, and a certain protocol will be defined for their interaction.

The basic principle of client-server technology is to separate the functions of a standard interactive application into four groups of different nature. The first group is the functions for entering and displaying data. The second group combines purely applied functions characteristic of a given subject area (for example, for banking system- opening an account, transferring money from one account to another, etc.). The third group includes the fundamental functions of storing and managing information resources (databases, file systems, etc.). Finally, the functions of the fourth group are service functions (playing the role of links between the functions of the first three groups.

In accordance with this, the following logical components are distinguished in any application:

A presentation component that implements the functions of the first group;

An application component that supports the functions of the second group;

A component for access to information resources that supports the functions of the third group, and agreements on the methods of their interaction are introduced and clarified (interaction protocol).

Differences in implementations of client-server technology are determined by four factors. First, what types of software each of these components are integrated into. Secondly, what software mechanisms are used to implement the functions of all three groups. Third, how logical components are distributed among computers on the network. Fourthly, what mechanisms are used to connect the components with each other.

There are four approaches implemented in the models:

· file server model (File Server - FS);

· remote data access model (Remote Data Access - RDA);

· database server model (DataBase Server - DBS);

· application server model (Application Server - AS).

The FS model is the basic one for local networks of personal computers. Not so long ago it was extremely popular among domestic developers who used systems such as FoxPRO, Clipper, Clarion, Paradox, etc. The essence of the model is simple and known to everyone. One of the computers on the network is considered file server and provides file processing services to other computers. The file server runs under network control operating system(For example, Novell NetWare) and plays the role of a component for accessing information resources (that is, files). On other computers on the network, an application is running, the code of which combines a presentation component and an application component (Figure 11). The exchange protocol is a set of low-level calls that provide an application with access to the file system on the file server.

Figure 11 – File server model

The FS model served as the foundation for expanding the capabilities of personal DBMSs in the direction of supporting multi-user mode. In such systems, both the application program and a copy of the DBMS are executed on several personal computers, and the databases are contained in shared files that are located on a file server. When an application program accesses a database, the DBMS forwards the request to the file server. This request specifies the files where the requested data is located. In response to the request, the file server sends the required block of data over the network. The DBMS, having received it, performs the actions on the data that were declared in application program.

The technological disadvantages of the model include high network traffic (transfer of many files, required by the application), a narrow range of data manipulation operations (“data are files”), lack of adequate security means for accessing data (protection only at the level file system), etc. Actually, the above are not shortcomings, but are a consequence of the inherent limitations of the FS model, determined by its nature. Misunderstandings arise when the FS model is used for other purposes, for example, when they try to interpret it as a database server model. The place of the FS model in the hierarchy of client-server models is the place of the file server model, and nothing more. This is why attempts to create large scale projects based on the FS model are doomed to failure. corporate systems- attempts that have been made in the recent past and are often being made now.

The more technologically advanced RDA model differs significantly from the FS model in the nature of the access component to information resources. This is usually a SQL server. In the RDA model, the codes of the presentation component and the application component are combined and executed on the client computer. The latter supports both data input and display functions, as well as purely application functions. Access to information resources is provided either by operators of a special language (SQL language, for example, if we are talking about databases), or by calls to functions of a special library (if there is an appropriate application programming interface - API).

The client sends requests to information resources (for example, databases) over the network to a remote computer. It runs the DBMS kernel, which processes queries, performing the actions prescribed in them, and returns the result to the client, formatted as a data block (Figure 12). In this case, the initiator of data manipulations are programs running on client computers, while the DBMS core is assigned a passive role - servicing queries and processing data.

Figure 12 – Remote data access model

The RDA model eliminates the disadvantages inherent in both systems with a centralized architecture and systems with a file server.

First of all, moving the presentation component and the application component to the client computers significantly relieves the load on the database server, minimizing the total number of operating system processes. The database server is freed from functions unusual for it; The server processor or processors are completely loaded with data processing, queries and transactions. This is made possible by eliminating terminals and equipping workstations with computers that have their own local computing resources, fully used by front-end programs. On the other hand, the network load is sharply reduced, since it is not requests for I/O that are transmitted from the client to the server (as in systems with a file server), but requests in the SQL language, their volume is significantly smaller.

The main advantage of the RDA model is the unification of the client-server interface in the form of the SQL language. Indeed, interaction of an application component with the DBMS kernel is impossible without a standardized means of communication. Requests made by a program to the kernel must be understandable to both. To do this, they should be formulated in special language. But the DBMS already has the SQL language, which has already been discussed. Therefore, it is advisable to use it not only as a means of accessing data, but also as a standard for communication between client and server.

Such communication can be compared to a conversation between several people, when one answers the questions of the others (questions are asked simultaneously). Moreover, he does this so quickly that the waiting time for a response is approaching zero. High speed of communication is achieved primarily due to the clear formulation of the question, when the questioner and the answerer do not need additional consultations on the essence of the issue. The talkers exchange several short, unambiguous phrases; they do not need to clarify anything.

Unfortunately, the RDA model is not without a number of disadvantages. First, the interaction between client and server via SQL queries significantly loads the network. Secondly, satisfactory administration of applications in the RDA model is almost impossible due to the combination of functions of different nature (presentation and application functions) in one program.

Along with the RDA model, the promising DBS model is becoming increasingly popular (Figure 13). The latter is implemented in some relational DBMS(Informix, Ingres, Sybase, Oracle). It is based on the mechanism stored procedures- SQL server programming tool. Procedures are stored in a database dictionary, shared among multiple clients, and executed on the same computer where the SQL server is running. The language in which stored procedures are developed is a procedural extension of the language SQL queries and is unique for each specific DBMS.

Figure 13 – Database server model

In the DBS model, the presentation component runs on the client computer, while the application component is designed as a set of stored procedures and runs on the database server computer. The data access component, that is, the DBMS kernel, is also executed there. The advantages of the DBS model are obvious: this is the possibility of centralized administration of application functions, and reduction of traffic (instead of SQL queries, calls to stored procedures are sent over the network), and the ability to divide a procedure between several applications, and saving computer resources by using a once created procedure execution plan . The disadvantages of the model include the limited tools used to write stored procedures, which are various procedural extensions of SQL that cannot be compared in terms of visual tools and functionality with third-generation languages ​​such as C or Pascal. The scope of their use is limited to a specific DBMS; most DBMSs do not have the ability to debug and test developed stored procedures.

In practice, mixed models are often used, when database integrity support and some simple application functions are supported by stored procedures (DBS model), and more complex functions are implemented directly in an application program that runs on a client computer (RDA model). One way or another, modern multi-user DBMSs rely on the RDA and DBS models, and when creating an IS that involves using only a DBMS, they choose one of these two models or a reasonable combination of them.

Figure 14.

Application server model.

In the AS model (Figure 14), the process running on the client computer is, as usual, responsible for the interface with the user (that is, it performs the functions of the first group). When requesting services from an application component, this process plays the role of application client(Application Client - AC). An application component is implemented as a group of processes that perform application functions and is called an application server ( Application Server - AS). All operations on information resources are performed by the corresponding component, in relation to which the AS plays the role of a client. Resources available from application components various types- databases, queues, postal services etc.

RDA and DBS models are based on a two-tier separation of functions. In the RDA model, application functions are assigned to the client program; in the DBS model, the DBMS kernel takes responsibility for their execution. In the first case, the application component merges with the presentation component, and in the second, it is integrated into the component accessing information resources. The AS model implements a three-tier function separation scheme, where the application component is singled out as the most important isolated element of the application, universal mechanisms of a multitasking operating system are used to define it, and interfaces with two other components are standardized. The AS model is the foundation for transaction processing monitors ( Transaction Processing Monitors - TPM), or, more simply, transaction monitors, which stand out as a special type of software.

In conclusion, we note that when speaking about a database server, we often mean both a computer and software - the DBMS core. When describing the Client-Server architecture, by database server we meant a computer. Further, the database server will be understood as software - the DBMS core.

Related information.