A fellow user of this site wrote on a blog about how easy and simple it is to work with relational databases (DB). It is surprising, however, that no matter how broadcasted that it is simple, the forums on the database are alive, well and, it seems, by the number of elementary questions asked there, occupy the first place. If everything is so simple, then what's the matter? It seems that I made a query to the database and - voila! - got the answer.
The point is that a database management system (DBMS), like a server, exists by itself; The database, as a specially organized file system, also exists on its own, and the application that the long-suffering programmer develops also exists on its own. Moreover, these parts can be located in different places on the disk and even on different computers. Often remote. Therefore, before understanding what the SQL query language is, it would be necessary to understand some of the essences without which these very SQL do not work. Those. absolutely computer hardware engineer education requirements.
For a programmer, a database exists in two forms - a real database, as structured data somewhere on some medium, and their reflection in his program, which must be linked to a real database. If there is no such connection, then all further work is meaningless. To establish such a connection, the programmer must know the name of the computer or its IP-address (in the simplest case - "localhost"), the path to the real database, its name, login and password. And also the so-called. "role" (well, let's omit this for now). These are all parameters of the Connect () command. I will consider an example further, and in order not to bother with a specific IDE, I will use the ancient, but still living IBPP class library, which works everywhere where the father of the C ++ programming languages and the industrial database "Firebird" is used. In addition, IBPP runs on Linux operating systems (OS).here . Library files must be included in your project using the #include preprocessing directive. After connecting the files that make up the IBPP library to the project, add a new member to the IBPP class, declaring the database reflection at the beginning of the program or in its header:
IBPP :: Database db;
First of all, we create a reflection of the database in our program:
try
{
db = IBPP :: DatabaseFactory ("localhost", DBName.c_str (), "SYSDBA", "masterkey");
}
catch (IBPP :: Exception & e)
{
// put a message here that something is wrong
}
It is assumed that a database named DBName already exists. The name must consist of the IP address of the database server, a colon, the path and the actual name of the database. Exceptions must be monitored - the LAW FOR A PROGRAMMER WORKING WITH A DB. Further, for brevity, I omit tracking exceptions, but we will assume that they are always there. We are trying to connect the database reflection, i.e. db object, with physical DB:
db-> Connect ();
If the connection is successful (there are no exceptions), you're in luck. You can be unlucky for a variety of reasons. Especially on Linux: the database file must be owned by the operational user firebird (assigned with the chown command) and this user must be granted unlimited rights. In addition, if the database was created in Windows using some expert program for the SYSDBA user (this is a database user, not an OS, please do not confuse), then by default the password for accessing the database will not be masterkey, but masterke, i.e. ... 8 characters.
Don't rush with SQL just yet. The request will not work if it is outside of the transaction. What is a transaction? Oh, there are a lot of lies on the Internet about this. Either inexperienced programmers gave definitions, or amateurs just to write. A transaction is a communication session between the reflection of the database in your program and the physical database, and not at all "an operation that is either completed to the end or not at all." A transaction can frame one or more requests. During a transaction, in a special buffer of the DBMS server, with each request, copies of the records in the physical database are created, which are accessed by your database reflection. Requests may or may not be executed, for example, due to their incorrectness. The session ends and the transaction becomes inactive either after confirmation (the so-called "commit"), or after a rollback of changes - "rollback". So, if you ignore the "wrong" queries and inadvertently commit the transaction, the real database will only have changes for the "correct" queries. As a result, the money in the accounts may multiply or disappear altogether. Funny, huh? But this happens if, through negligence, the programmer ignores the exception and does not roll back the transaction. Let's declare a transaction:
IBPP :: Transaction tr;
And then let's create it:
tr = IBPP :: TransactionFactory (db);
Stop stop. What have we created? In fact, we did not create a transaction, but a control point for it. It is such an object - a class or a subclass. It has its own methods. And the real transaction resides on the DBMS server. But, as soon as we created a transaction in the program with a real database connected, a real transaction was created on the DBMS server and received its number. For simplicity, the tr object will also be called a transaction. She's inactive yet. We will activate it when required. Now let's declare the SQL query as a member of the IBPP class:
IBPP :: Statement st;
Put the query text in line S. We assume that the query is a selection from one column with some conditions. For example, we have a statistical table for the census of the sizes of the bust of girls and we need to build a histogram of the sizes of the bust 1,2,3 for a sample of 1000 people, then the text of the SQL query will look like this:
String S = "select first 1000 count (boops_size) from table group by boops_size";
Let's zero the row counter in the response, create the request body, start the transaction, load the request text S into its body, execute the request, and if the DBMS has accepted and executed the request, then we will start polling the response buffer in order to put the data received from the database into our application:
int rc = 0;
int array [10];
try
{
st = IBPP :: StatementFactory (db, tr);
try
{
tr-> Start ();
st-> Execute (S.c_str ());
}
catch (...)
{
tr-> Rollback ();
return rc;
}
while (st-> Fetch ())
{
st-> Get (1, array [rc]);
rc ++;
}
tr-> Commit ();
}
catch (IBPP :: Exception & e)
{
cout << e.ErrorMessage ();
}
return rc;
We can judge that the request was fulfilled by the absence of exceptions and by the nonzero number of records, if, of course, we know that some records should be. In general, the array buffer should have three numbers in the sum of 1000.
In any case, in case of failures, we must roll back the transaction, otherwise the integrity of the database will suffer. We are also obliged to think about the safety of the request: in this case, the request seems to be safe, since the size of the response, it seems, cannot exceed the size of the array buffer. It cannot, if the dimensions in the table were set correctly, i.e. 1,2 or 3. But if an attacker, knowing the login and password, writes in the table the number of different sizes exceeding 10, then a buffer overflow will occur, which is highly undesirable. Therefore, before our request, we should check the values of the boops_size column for validity. This is done by prior request. You see how difficult it is.
Transactions have a lifetime, isolation level, collision resolution methods. Much has been written about this, I do not give links, use the search, who is interested. So, the minimum gentleman's set for working with relational databases: database reflection, transaction, query. And examples of query texts - look in any reference book on the used SQL dialect.
Yes, I completely forgot. When completing work with the database, do not forget to clean up after yourself, otherwise you will cause problems for users due to unclosed operations. Well, like this:
bool res;
try
{
db-> Disconnect ();
res = true;
}
catch (IBPP :: Exception & e)
{
res = false;
}
No comments:
Post a Comment