Query Languages and Data Functions (Programming the Perl DBI)

2.2. Query Languages and Data Functions

Database operations can be split into those manipulating the database itself (that is, the logical and physical structure of the files comprising the database) and those manipulating the data stored within these files. The former topic is generally database-specific and can be implemented in various ways, but the latter is typically carried out by using a query language.[4]

[4]We use the term "query language" very loosely. We stretch it from verb-based command languages, like SQL, all the way down to hard-coded logic written in a programming language like Perl.

All query languages, from the lowest level of using Perl's string and numerical handling functions to a high-level query language such as SQL, implement four main operations with which you can manipulate the data. These operations are:

Fetching: The most commonly used database operation is that of retrieving data stored within a database. This operation is known as fetching, and returns the appropriate data in a form understood by the API host language being used to query the database. For example, if you were to use Perl to query an Oracle database for data, the data would be requested by using the SQL query language, and the rows returned would be in the form of Perl strings and numerics. This operation is also known as selecting data, from the SQL SELECT keyword used to fetch data from a database.
Storing: The corollary operation to fetching data is storing data for later retrieval. The storage manager layers translate values from the programming language into values understood by the database. The storage managers then store that value within the data files. This operation is also known as inserting data.
Updating: Once data is stored within a database, it is not necessarily immutable. It can be changed if required. For example, in a database storing information on products that can be purchased, the pricing information for each product may change over time. The operation of changing a value of existing data within the database is known as updating. It is important to note that this operation doesn't add items to or remove items from the database; rather, it just changes existing items.[5]

[5]Logically, that is. Physically, the updates may be implemented as deletes and inserts.
Deleting: The final core operation that you generally want to perform on data is to delete any old or redundant data from your database. This operation will completely remove the items from the database, again using the storage managers to excise the data from the data files. Once data has been deleted, it cannot be recovered or replaced except by reinserting the data into the database.[6]

[6]Unless you are using transactions to control your data. More about that in Chapter 6, "Advanced DBI ".

These operations are quite often referred to by the acronym C.R.U.D. (Create, Read, Update, Delete). This book discusses these topics in a slightly different order primarily because we feel that most readers, at least initially, will be extracting data from existing databases rather than creating new databases in which to store data.