Archive for the ‘database’ Category

« Older Entries

Functional dependencies: How databases relate

Tuesday, December 8th, 2009

“The single most important concept in relational schema design theory is that of a functional dependency,” write Ramez Elmasri and Shamkant Navathe in “Fundamentals of Database Systems.”

But what is a functional dependency? It is the actual relationships in the relational database. It is the relations among the attributes within a table. It is a constraint between two sets of attributes. The relations are permanent and unalterable.

For instance, a table may have two attributes, or columns. If one is the primary key, we can then say we can always determine the value of the second attribute using the primary key. This means we can use the primary key as an index to look up the second attribute. It is a mathematical certainty. The primary key is the determinant and the other is the dependent, the parlance of database-speak.

(Keep in mind that this relationship does not work in reverse. You can not use a dependent value to definitively determine the primary key, chiefly because the dependent value may not be unique in a given table).

Beyond the simple connection drawn between the primary key and the dependents, a number of other inferences can be made as well, using Armstrong’s inference rules, which cover the laws of logic such as transitivity and reflexivity.

All material taken from a class I’m attending at UMUC on relational databases, as well as from the book….



…All mistakes are my own, though..
–Joab Jackson





Tags: ,
Posted in database, SQL, Uncategorized | Comments Off

SQL: Constraining the Database

Friday, October 30th, 2009

Constraining what gets entered into a relational database is a good thing. It maintains the data integrity that is so important for database use. You don’t want users to enter the wrong types of information (I.e. letters instead of numbers)

My database class teaches that there are three different ways to constrain input for a database:

1. Declarative Integrity
2. Procedural Application Code
3. Business Procedure

Declarative Integrity means you put restraints directly in the table design. The constraints specify what input the database accepts. More on them below.

When you restrain by using the Procedural Application Code, you put the constraints not in the database tables, but in the programming logic that handles the input of the data (PL/SQL), with actions such as “triggers.”

Business rules or for those cases when you can not (easily) check the integrity of the data by computer, so you put the rules in the employee handbook (“Enter your real birth date, not a false birth date” would be a silly example).

Here’s some basics on Declarative Integrity. When you create a data table, you can add in constraints on what data is accepted. This can be done as part of a column definition, or at the end of the “create table” statement.

Here are the basic types of Integrity Constraints you can use, at least for Oracle databases:

NOT NULL: When an insert is made, a column defined as NOT NULL must be given some data. The NOT NULL declaration goes right after the data type. For instance, when creating the column with the DATE data type, you would add:

[NameOfColumn] DATE NOT NULL,

UNIQUE: UNIQUE requires each new value entered into that column be different from all those value entered before.

PRIMARY KEY: The PRIMARY KEY is the one column that identifies the column from all the others. As such it is considered UNIQUE, meaning each value entered will be different from all the other values entered. However, you don’t use the UNIQUE qualifier when declaring the PRIMARY KEY (it is implied). Here is an example:

[NameOfColumn] [DataType] PRIMARY KEY,

The PRIMARY KEY can be a composite of multiple column entries, which means each each key must be comprised of a unique combination of values from the participating columns. The composite key is defined at the end of the table creation statement, after the last column definition:

constraint [Name_Of_Composite_Primary_Key] PRIMARY KEY ([Name_of_1st_Participating_column], [Name_of_2nd_Participating_column], [...] )

FOREIGN KEY: a FOREIGN KEY uses as its domain of possible values a PRIMARY KEY from another table. This is written as a column definition:

constraint [Name_Of_Foreign_Key] foreign key ([Name_of_External_Column])

references [Name_Of_External_Table]([Name_of_External_Column])

Note, you can not refer to a table in another database, only to another table in the same database. But you can refer to the primary key even in the same table.

The references clause tells the database to delete the dependent row when the corresponding row in the parent table is deleted.

CHECK: The CHECK constraint allows you to specify only certain values can be inserted, as such:

[Name_Of_Column] [Datatype] Check([Name_Of_Column] [operator] [value]),

For example,

Stats VARCHAR2(2) CHECK (Stats => 0)

….means that any values entered for the Stats column must be 0 or higher.

Material taken from this book….



…As well as from a class I’m taking on database design. All mistakes are my own…–Joab Jackson





Tags: , , ,
Posted in data types, database, datatypes, MySql, Oracle | Comments Off

PHP: Post the results of a simple MySql Query on a Web Page

Saturday, October 24th, 2009

Say you want to post the results of a simple query from a MySQL database on a Web page, using PHP. You would think that all you’d need to do is assign a variable name to the results of the SQL query, and then ask PHP to print the variable.

It doesn’t work that way. Instead of PHP printing the result, what gets printed is a mysterious message, like “Resource ID #3″

As explained here, the variable itself points to a place holder of sorts. To get the actual value, you have to use another MySQL function.

In this case that function would be mysql-fetch-row.

For example, within the PHP body of code, you do something like this:

$QueryResult = mysql_query(“select avg(Height) from Boys);

$ResultInBetweenStep = mysql_fetch_row($QueryResult);

$ResultPresent = $ResultInBetweenStep[0];

echo($ResultPresent);

In the above quote, we’re getting the result of a query from a table called Boys that is the average of all the entries in the Height column. It is assigned to the variable $QueryResult.

In order to get the actual data from the query, the function mysql_fetch_row is applied to $QueryResult, and the results are stored in another variable, $ResultInBetweenStep.

The final step is to assign a variable to the first row of $ResultInBetweenStep only (which would be the *only* row in the query, as the average function will return a single number), which, here, is called $ResultPresent.

$ResultPresent can then be printed.

There are other MySql functions that allow you to extract more complex bits of information from a MySQL query. Check the mysql_fetch_* entries here for more info.–Joab Jackson





Posted in database, MySql, PHP, SQL, Web | Comments Off

Databases: Further Defining Entities

Sunday, October 11th, 2009

In a previous post, I sketched out the basic structure of a database model. The basic elements are entities, relations and attributes. This post will describe the further definitions that can be made under this model, using the Enhanced Entity Relation Model (EER).

First, we can specify different types of entities a bit further, into subclasses and superclasses. Subclasses are groupings within a set of entities that are clustered in different roles they play, within the category of the entity. If “employee” is an entity, then “technician” and “manager” could be two different subclasses. Inversely, “employee” is a superclass of “technician” and “manager.”

Defining how subclasses is a process called specialization. The employee entity can be broken into specialization subclasses, such as “technician” or “secretary.” Some attributes can only be applied to certain subclasses (a secretary’s typing speed, for instance). These are called specific attributes or local attributes.

In some cases, inclusion into a subclass can be determined by the existence of a certain attribute. These subclasses are called predicate-defined subclasses. Membership is defined by a particular value of an attribute. (Subclasses can also be defined without any particular attribute, though they must be defined manually–Those are called user-defined subclasses).

The reverse operation of specialization–namely to summarize a set of subclasses into a master entity, is called a generalization.

All material taken from a class I’m attending at UMUC on relational databases, as well as from the book….



…All mistakes are my own, though..
–Joab Jackson





Tags: , , , ,
Posted in database, SQL | Comments Off

Databases: Entities, Relationships and the Attributes That Describe Them

Sunday, October 4th, 2009

At their most basic, relational databases are composed of three sets of elements: An entity, a relationship or an attribute. Relationships tie together different entities, and both can have attributes.

This rule-of-thumb comes from the Entity-Relationship (ER) model, which sets the formal rules for how different attributes relate to one another, at an abstract level.

An entity can be anything, or, to be more precise, any thing. An entity can be a person, or a type of motorbike, or a particular motorbike. An entity’s attributes are a set of properties used to describe the entity. An person may have a first name, last name, birthdate—entities all.

Attributes can be single-valued or have multiple values (the names of the person’s siblings, for instance). An attribute can be a composite of multiple values: A person’s address itself an attribute. It could be composed of an address, city name, zip code, and other elements.

Attributes may also be derived, meaning that they don’t actually reside anywhere on the database, but can be calculated when needed, i.e. how many people live within a certain zip code.

Also, in many cases, one of the attributes will what is known as a key attribute. This attribute must be a unique value, at least against for each and every other entity kept in the database. The key attribute allows a query to pick out an individual entry, among all the entries. A person’s social security number, for instance, can work as a primary key, because every person’s SSN is different.

Relationships are used to tie together different entities, explaining the relation between the two. A PERSON (one entity) WORKS FOR (a relationship) a COMPANY (another entity).

Relationships can have different levels of cardinality, meaning that one entity can have one or more than one relationships with other entities. A PERSON can work for more than one COMPANY. And a COMPANY can employ more than one person. Such rules are usually defined in the database model.

Relationships, like entities, can have attributes. In the example above, the WORKS FOR relationship can have an attribute such as DATE STARTED.

All material taken from a class I’m attending at UMUC on relational databases, as well as from the book….



…All mistakes are my own, though..
–Joab Jackson

[ad]

Tags:
Posted in database, SQL | Comments Off

Databases: Understanding the relational model

Wednesday, September 23rd, 2009

A relational database is different from a normal collection of stuff written down in a computer file insofar as a good database tries to map out the relationships among all the elements.

IBM computer scientist E. F. Codd first developed the relational model. While the Structured Query Language (SQL) is an implementation of the relational model, it is sometimes helpful to understand the underlying relational model itself.

Thinking abstractly, a relational database can be thought of in terms of three interlocking parts: The object, the integrity and the operators. The object is how the data is structured. The integrity restrains the data to fit the appropriate formats outlined in the Object, as well as keep everything tidy and, well, relational. Finally, the operators are the ways in which that data is allowed to be manipulated.

The Object

A database requires that a set of data to be entered in a uniform fashion. Each set of data is called a relation. A database can have multiple sets of relations, perhaps joined by foreign keys (more on that later).

A relation defines a set of columns in which each column, called an attribute, is devoted to a predefined subset of the data. Each entry into this relation, called a tuple, should have a data for each attribute (though null values can be permitted, depending on the database’s predicate, or definition of possible contents for each tuple).

For example, a video store may keep a database of customers. This relation may consist of attributes such as “customer ID,” “last name,” “first name,” “zip code,” and so on. In this case, a tuple is an individual entry describing one person (#42, “John” “Smith” “20707”).

Thinking in terms of attributes, the set of all the implied possible values for an attribute is called a domain. For instance, the Domain for Social Security numbers would be all the possible sets of numbers that fit in the NNN-NN-NNNN format (though an entry with a letter or letters inserted in place of a number would not be part of the domain).

For more defining characteristics of relational databases, see Dodd’s 12 Rules.

Wrapping up, the entire set of relations is called the schema. The collected values at any one point of time is called its state.

Keep in mind that the terminology for the relational model does not always map exactly to terminology in SQL. For the most part they are pretty close. In SQL, term relation is equivalent to table, tuple means row, and attribute is a column.

The Integrity

Integrity constraints are the rules that ensure the relation keeps order of its data.

At the tuple level, identifying each tuple with some unique characteristic is important. A superkey is a combination of attributes that uniquely defines a tuple. Alternately, one of the keys can be designated as a primary key. A primary key must be completely different for each tuple. If a new entry is added for each new date, with no two entries ever falling on the same date, then the date could be the primary key. Or, in a sales environment, an “order number” could serve as a primary key, since each order number is unique. Obviously, no primary key can have a null value.

Alternately, the could be as simple as an incremented number assigned to each new entry, by the database (This entry, generated solely for the purpose of creating a primary key, is called a surrogate key). If more than one entry is used is to fashion a unique identifier, this approach is called a composite primary key.

Foreign keys are the way to link different tuples. A e-commerce store may have a regular customer buy a new item. In the customer relation, the customer ID is the primary key, while in the transactions relation, the order number is the primary key. However, the order tuple includes the customer ID. In this tuple, the customer ID is a foreign key.

It should be noted that these two relations must maintain what is called referential integrity, meaning that any customer ID in the transactions relation must be in the customer relation.

In addition to these internal constraints, the database operator may apply some own rules, derived from the operations being recorded in the database. These rules can be enforced through the CHECK constraints in SQL.

The Operations

Now that we’ve described the database structure (the “object”), as well as how the pieces are related to one another (the “integrity”), lastly we get to the operations, or what can be done with the database.

Basically, you can do one of four things with a database data:

*You can retrieve existing data
*You can insert new data
*You can update existing data
*You can delete existing data

Of course, there are a lot of different things you can do within these four broad categories. All of these options are contained within what is generically called SQL’s data manipulation language, or DML.

All material taken from a class I’m attending at UMUC on databases, as well as from the book….



…All mistakes are my own, though..
–Joab Jackson





Tags:
Posted in database, SQL | Comments Off

SQL: Adding a New Column to a Database Table

Sunday, May 24th, 2009

When you have already created a database, and a table, and you need to add another column to the table This is what you add:

ALTER TABLE [TableName] ADD [ColumnName DataType]

To see what you’ve done, use the SHOW COLUMNS command:

SHOW COLUMNS FROM [name of database table]

Taken from >SQL-Tutorial.com and the MySQL Reference Manual. MySQL 5.1 was used here.
–Joab Jackson





Posted in database, MySql, SQL | Comments Off

SQL: Fetching & Parsing with SELECT

Wednesday, May 20th, 2009

The SELECT statement is used in SQL to select data from the database.

Some background first: SQL stands for structured query language. It is the language used for working with a relational database.

SQL commands can be split into two groups. One is for working with the data within the database (called the The Data Manipulation Language, or DML). The other works designing the tables, as well as defining the relationship among them (The Data Definition Language, or DDL).

All of these commands can be used from the command line prompt, once you’ve gained admittance to the Relational Database Management System (that is the MySQL software itself, or whatever RDMS you are using).

SELECT Falls under DML. It is the basic command for pulling data from the database, either directly from the command line, or as part of a Java or PHP connector.

The basic format for SELECT is:

SELECT [column(s)] FROM [table]

(Note for all these blog pages, you fill your own values in the brackets “[ ]“).

So, if you wanted to see all the info in the database “test,” you would write:

SELECT * from test;

(“*” is a wildcard operator. MySQL requires a “;” at the end of each statement, not shown here. Multiple columns can be selected to show, separated by a comma.)

If you just wanted to see one column of data, say the column of numbers called “measure” you would write:

SELECT measure FROM test;

So SELECT is pretty easy, yes? But it is also rather coarse-grained, returning a tree when what you want is a branch. You need to do some more whittlin’!

You build a more precise query from SELECT using additional qualifiers.

The main way of doing this is through the WHERE keyword, which come after the FROM statement that specifies the table. To further qualify you can also append, in various mixes, the BETWEEN, LIKE, IN, AND or OR keywords, along with selected values to filter by.

Finally, you can order the results by ORDER BY and filter them by DISTINCT, niether of which require WHERE, but could be used in conjunction with WHERE.

What follows are the details:

* * *

With WHERE you can pull only those records from a table that meet some “specific criteria,” as the W3C puts it. WHERE is part of a SELECT statement.

SELECT [column] FROM [table] WHERE [column]=[value you seek]

i.e.,

SELECT measure FROM test WHERE measure=0;

In this example, I used the “=” But other conditionals include…

<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal

Now, for some other keywords:

* * *

BETWEEN: BETWEEN returns a range of data specified between two values.

The format for this is:

SELECT [column(s)] FROM [table] WHERE [column]
BETWEEN [lower value] AND [upper value]

The values can be numbers, date or even text! Text is ranked, ascending or descending, by alphabetical order. Here is an example:

SELECT * FROM Bike WHERE Date
BETWEEN 20090501 AND 20090515

…Will return all the data in all the columns in the table Bike that have a date between May 1 and May 15 2009.

* * *

IN: IN specifies specific entries. When you are looking for values within known entries, then IN is what you’d use.

SELECT [column] FROM [table] WHERE [column] IN ([value(s)])

Commas separate multiple values in the IN set. And, as always if the values are text, they should be in single quotes. Example:

SELECT * FROM Bike WHERE RideTime IN [30, 60]

..Returns all the rows in the Bike table where the RideTime column equals 30 or 60.

* * *

LIKE and NOT LIKE: LIKE is a pattern-matcher. You can structure a query with the elements that you know are in a value that you are looking for, with wild card elements both before and after the known-nugget of info.

The format:

SELECT [column(s)] FROM [table] WHERE [column] LIKE [pattern]

For these operations the percentage sign, %, is the wildcard operator. In a hypothetical database, “Turkey%” will return “TurkeyShoot” and “TurkeyBreast” and “%Turkey” will return “unTurkey” and “%Turkey%” will return all three i.e.

NOT LIKE is the same but returns all the results that DON’T match the criteria you select.

SELECT * FROM BirdWords WHERE Fowl LIKE ‘%Turkey%’

NOTE: Single quotes are used also for fetching numerical responses, dig?

* * *

DISTINCT: DISTINCT is actually a precursor to WHERE–It comes before WHERE in the SQL statement and, in fact, can be used without WHERE. It picks out all of the original values in a column, eliminating any duplicates.

SELECT DISTINCT [column] FROM [Table]

* * *

AND/OR An AND or OR statement may be placed after WHERE for further distinction:

SELECT [column(s)] FROM table WHERE [column]=[value1] AND [column]=[value2]

SELECT [column(s)] FROM table WHERE [column]=[value1] OR [column]=[value2]

SELECT [column(s)] FROM [table] WHERE [column]=[value1] AND ([column]=[value2] OR [column]=[value3])

Note, operators other than the “=” could be used. See above. In the final example AND and OR can be combined, with a parenthesis to clarify order of evaluation with the machine. Play around with these for greater nuances, i.e.

SELECT * from Run WHERE Time=60 OR Time=50 AND (Date=20090101 OR Date=20090201);

…Will return all those entries with a date of Jan 1 2009 or Feb 1 2009 in which the Run Time was 50 or 60.

* * *

ORDER BY The ORDER BY specifies how the results of a query should be returned. They results can be returned either in a numerically ascending (or alphabetical), or descending (reverse alphabetical) for whatever column you choose. Default is ascending.

Again, ORDER BY can be done without WHERE. It is placed at the end of the SQL statement:

SELECT [column(s)] FROM [table] ORDER BY [column] [ASC or DESC]

Example:

SELECT * FROM Run ORDER BY Time DESC

…Will return all the entries from the Run table ranked by time, longest time first.

Taken from the W3C School’s tutorial on SQL, as well as from SQLCourse, a helpful site I found on the Web.

–Joab Jackson





Posted in database, MySql, SQL, Unix | Comments Off

Apache: Redirecting Web page requests

Sunday, April 5th, 2009

If you move a Web page on your site to another location, or give it a another address, there are a number of ways you can have the Apache Web server software automatically redirect browser requests that come in for the page to the new location.

The easiest way is to put a page at the old address that automatically directs the browser to the new location, i.e.:

<html>
<head>
<meta http-equiv=”refresh” content=”0;url=http://www.TheNewAddress.com”>
</head>
</html>

In the above page, the meta tag redirects the browser to the new location (here, it is “http://www.TheNewAddress.com”), with a delay of 0 seconds (“0″) . The user just sees the page at its current location.

This process of setting up a new page for each updated address is a but cumbersome though. Far better would be to put all the old addresses and their new replacements in a single file, which Apache could check every time a new request for a page comes in.

Fortunately, the HTTP protocol has something called 301 Status code, which is basically a permanent change-of-address notification.

For Apache, doing a 301 redirect involves setting a .htaccess blank file (or appending an existing one). The period in front of the the name means it will be a hidden file—to see hidden files, use the “ls-a” command.

To create such a file, just name a blank text file .htaccess. Place it in the root directory of your Web server (Or if all the pages you are redirecting are in one directory, place the file in that directory).

Then, add a new line for each redirect in the following form:

[old address] [new address]

For example, this entry at the bozo.com site…

/OldFiles/OldBozo.html /NewDirectory/NewFile.html

…clicking on the link “http://bozo.com/OldFiles/OldBozo.html,” the user’s browser will automatically pull up the “http://bozo.com/NewDirectory/NewFile.html.”

Note that when the new page is outside the control of the Web server, the full address (including “http://”) of the destination address must be used, not just the internal directory tree.

Setting up an Apache .htaccess file, if one didn’t previously exist, requires letting your copy of Apache know that this file exists and should be consulted. In Ubuntu, and probably other distributions as well, Apache ignores the .htaccess page in the default install.

(Note, for this instruction, I am using Apache 2.2.8 on Ubuntu server 8.0.4).

Doing this requires two steps. First of all, find the “apache2.conf” file. In Ubuntu, it is located in “/etc/apache2″ directory. It can be edited at the command line with a text editor, such as vi, emacs or Pico.

Open the file and search for the mention “.htaccess.” Check to see that “.htaccess” follows the “AccessFileName” option. If it is enabled, there will be no ‘#’ at the beginning of the line (meaning it is not commented out). This tells Apache to look in this file for directives, such as a page address substitute as the one above. It should read:

AccessFileName .htaccess

That is probably already set correctly, but the second step probably involves some changes in configuration. Namely you have to set something called “AllowOverride,” which is the configuration setting that tells Apache whether or not to follow the .htaccess requests

This option can be found in another file, one showing the directories that Apache should use for the Web site. In Ubuntu, it is the “default” file in the “sites-available” folder (“/etc/apache2/sites-available”). (NOTE: In Ubuntu this file is also under another name as a symbolic link, in the “sites-enabled” folder.)

In this “default” file, you will find a list of directories on your server that have been enabled as Web server pages.

<Directory /var/www/>
Options Indexes FollowSymLinks MultiViews
AllowOverride None
Order allow,deny
allow from all
</Directory>

NOTE: This is the not the “document root” entry, but the one right after it. The “document root” entry also has an AllowOverride. It is set as “none” and can stay that way.

Each entry (framed by <Directory> and the </Directory> tags) in this list specifies the options that Apache should use for that directory. In the above entry, change “AllowOverride None” to “AllowOverride All”.

A bite of explanation: “AllowOverride None” means Apache does not look for the .htaccess file, and does not follow its instructions. “AllowOverride All” means that it does.

After you make this change, or any changes to these configuration files, you need restart the Apache server software. In Ubuntu it is done thusly from the command line:

/etc/init.d/apache2 restart

(Note, you do not need to restart Apache when new entries to .htaccess are made. That seemed obvious but I should mention this anyway)

Also, if you know that all your rerouting is being done from one folder. You can place the .htaccess file in that folder, and, instead of changing the “AllowOverride” setting for the whole site, just make a new entry in the “default” configuration file for that one directory.

For instance, I wish to redirect addresses of expired Web pages in the “/var/www/L/” folder. I would place an .htaccess file in that folder and add this entry into the “default” configuration file:

<Directory /var/www/L/>
AllowOverride All
</directory>

This seems to be all you need to add–the other options are inherited from the listing of the parent directory.

End-note: I’ve found that Apache is extremely fussy about what is put into an .htaccess file. Don’t put junk in just as a way of testing something else out. Only properly formed URL’s or internal links should be added. Anything else will halt all Apache redirects, giving users only error messages.

Note: Other forms of redirection are discussed here.

–Joab Jackson

And now, a word from our sponsor:





Tags: ,
Posted in Apache, database, Javascript, Linux Server Administration, Ubuntu, Web | Comments Off

Unix: Keepin’ things regul’r w/ crontab

Sunday, March 29th, 2009

In Unix, to run a script at some regular interval, notate it in a crontab file. A crontab file is a list of programs that cron should run at specified times.

Cron itself is an OS service that runs scheduled jobs, those in any one of a number of crontab files on the machine. It is Unix daemon–It basically wakes up every minute, looks to see if any jobs need to be completed. If there are, it starts the job. If not, it goes back to sleep.

To find if cron is running on your system, type in, at the command line “ps -aux,” which will give you a list of all the processes running on the machine. Look for the name cron under the far-left column, called commands i.e.

root 4556 0.0 0.2 2100 888 ? Ss Feb17 0:01 /usr/sbin/cron

While there a few ways to get cron to execute a job (cron is another topic) , crontab is one way to get the job done. Like I said, crontab is basically a list of scripts, commands and programs that Unix can execute.

In Ubuntu (as w/ most other Linux distros), you edit the crontab file directly, by evoking it from the command line, i.e.:

#crontab -e

Listing jobs in crontab can be done by typing in:

#crontab -l

Anyway, when you edit, you will get a file. It may already have some jobs in it, i.e.:

# m h dom mon dow command

17 * * * * echo “hello”

In the first block above, the line (“# m h dom…”) is a header that is the key to explaining each of regularly scheduled jobs that will follow in subsuquent lines. m=minute, h-hour, dom=day of month, mon=month, dow=day of week, and command is the command or program that will be executed at the time indicated on the left.

Each job gets its own line. You add a job to be scheduled by adding a new line.

In the first five columns that specify time, “*” is null–means that column is unset. All the values are numeric and occasionally 3-letter abbreviations (i.e. week is 0-7 [0 & 7 = Sunday], though can also be sun “Sun”; month is 0-12 can also be name of the month. Hour is 0-23, with 23 being midnight).

After the first five entries are filled out, indicating when the job is to be run, the rest of the line is the job itself, expressed as a standard command line statement: It can be a command or series of commands that need to be run, or a program w/ the pathname.

In the above example, the command (“echo ‘hello’”) is run on the 17th minute of each hour. It prints the word “hello” on the screen.

With the day/week/month, you should specify on what time that job executes (If you run two times, say a day of of week and a day of the month, cron will run twice unless they fall on the same day). If you don’t specify what day/week/month, it will run every day, at the time you specify. You can also specify times -per unit, i.e., in the minute column, you can write “*/10″ to signify to run the job every 10 minutes.

(UBUNTU note: The User Geeks page says the crontab file is found in the /etc folder. Ubuntu’s own documentation advises you not to use this file–evidently it can be replaced by updates. Maybe it is only used for the configuration settings)

So, for instance, say I want to run a script, called “backup,” which backs up my files to another location. I want it to run, say, once a day (I don’t want to back up too often, in case I mess up a file, I can quickly retrieve). I’m usually never awake at 3:20 a.m., so I’ll specify that time each day. My new line in the crontab would look like this:

m h dom mon dow command
22 3 * * * /home/jobs/backup

If you want to run multiple commands simultaneously, use the “&&” between the two commands.

Getting a log file of your cron jobs

If you want a record of how things went, you can specify a (plain text) log file that can write out any results that would have otherwise be returned from the command line. You add the “>>” onto the end of your job, followed by the name of the log file, and its location:

30 16 * * * root /root/scripts/ServerBack >> /root/scripts/backup.log

NOTES:

*Ubuntu server does not initially allow user access to cron. In order to get Cron, you can either put the user name in a file called cron.allow, in the /etc directory, and create a cron.deny and not put that user name in that file.)

*If things aren’t running properly, check the var/log/syslog file for any error messages. For instance, my crontab entries did not work under root. When checking the log I found the

Mar 29 22:26:01 warehouse CRON[20531]: User account has expired

According to this article, when you lock a user account (so that it can’t be accessed externally), it also “expires” the password (Note: This isn’t a problem with Ubuntu out the box–it only happens when you unlock, then lock the root account). The log entry suggested running this command:

#chage -E-1 root

Which permanently unlocks the password. I have no idea *why* this works, but it has.

*For purposes of backup, you should track down the crontab files for each user (Again, this is not in the etc/ folder), so they can be saved. Beats rewriting them again when you set up a new server.

–Joab Jackson

And now, a word from our sponsor:





Tags: , ,
Posted in Backups, Bash, commands, database, Linux Server Administration, Time, Ubuntu, Unix | Comments Off

« Older Entries