Posts categorized “Databases”


March 28, 2008
  A Handy PHP Backup Script

I found a script over on the Lunarpages Forums about using PHP to back up your site. I have taken it, modified it a little, beefed up the documentation a lot, and am now posting it here. You can copy and paste it from below to customize it for your own use.

backup.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
<?php
/**
* Generic Backup Script.
*
* To configure this script for your purposes, just edit the parameters below.
* Once you have the parameters set properly, when the script executes, it will
* create an archive file, gzip it, and e-mail it to the address specified. It
* can be executed through cron with the command
*
* php -q [name of script]
*
* You are free to use this, modify it, copy it, etc. However, neither DJS
* Consulting nor Daniel J. Summers assume any responsibility for good or bad
* things that happen when modifications of this script are run.
*
* @author Daniel J. Summers <daniel@djs-consulting.com>
*/

// --- SCRIPT PARAMETERS ---

/* -- File Name --
This is the name of the file that you're backing up, and should contain no
slashes. For example, if you're backing up a database, this might look
something like...
$sFilename = "backup-my_database_name-" . date("Y-m-d") . ".sql"; */
$sFilename = "backup-[whatever-it-is]-" . date("Y-m-d") . ".[extension]";

/* -- E-mail Address --
This is the e-mail address to which the message will be sent. */
$sEmailAddress = "[your e-mail address]";

/* -- E-mail Subject --
This is the subject that will be on the e-mail you receive. */
$sEmailSubject = "[something meaningful]";

/* -- E-mail Message --
This is the text of the message that will be sent. */
$sMessage = "Compressed database backup file $sFilename.gz attached.";

/* -- Backup Command --
This is the command that does the work.

A note on the database commands - your setup likely requires a password
for these commands, and they each allow you to pass a password on the
command line. However, this is very insecure, as anyone who runs "ps" can
see your password! For MySQL, you can create a ~&#47;.my.cnf file - it is
detailed at //dev.mysql.com/doc/refman/4.1/en/password-security.html .
For PostgreSQL, the file is ~&#47;.pgpass, and it is detailed at
//www.postgresql.org/docs/8.0/interactive/libpq-pgpass.html . Both of
these files should be chmod-ded to 600, so that they can only be viewed by
you, the creator.

That being said, some common commands are...

- Backing Up a MySQL Database
$sBackupCommand = "mysqldump -u [user_name] [db_name] > $sFilename";

- Backing Up a PostgreSQL Database
$sBackupCommand = "pg_dump [db_name] -h localhost -U [user_name] -d -O > $sFilename";

- Backing Up a set of files (tar and gzip)
$sBackupCommand = "tar cvf $sFilename [directory]

Whatever command you use, this script appends .gz to the filename after the command is executed. */
$sBackupCommand = "[a backup command]";

// --- END OF SCRIPT PARAMETERS ---
//
// Edit below at your own risk. :)

// Do the backup.
$sResult = passthru($sBackupCommand . "; gzip $sFilename");
$sFilename .= ".gz";

// Create the message.
$sMessage = "Compressed database backup file $sFilename attached.";
$sMimeBoundary = "<<<:" . md5(time());
$sData = chunk_split(base64_encode(implode("", file($sFilename))));

$sHeaders = "From: $sEmailAddress\r\n"
. "MIME-Version: 1.0\r\n"
. "Content-type: multipart&#47;mixed;\r\n"
. " boundary=\"$sMimeBoundary\"\r\n";

$sContent = "This is a multi-part message in MIME format.\r\n\r\n"
. "--$sMimeBoundary\r\n"
. "Content-Type: text/plain; charset=\"iso-8859-1\"\r\n"
. "Content-Transfer-Encoding: 7bit\r\n\r\n"
. $sMessage."\r\n"
. "--$sMimeBoundary\r\n"
. "Content-Disposition: attachment;\r\n"
. "Content-Type: Application/Octet-Stream; name=\"$sFilename\"\r\n"
. "Content-Transfer-Encoding: base64\r\n\r\n"
. $sData."\r\n"
. "--$sMimeBoundary\r\n";

// Send the message.
mail($sEmailAddress, $sEmailSubject, $sContent, $sHeaders);

// Delete the file - we don't need it any more.
unlink($sFilename);
Categorized under , , , ,
Tagged , , ,


March 28, 2008
  Algorithm for One-to-Many Child Table Updates

While working on the Not So Extreme Makeover: Community Edition site, I came up with an algorithm that simplifies anything else I’ve ever written to deal with this condition. I’ll set the scenario, explain the algorithm, share how I implemented it in PHP, and provide a modification if the scenario is a bit more complicated.

Scenario - You have two parent tables, and a child table with a many-to-one relationship with both parent tables, used to map entries in the two parent tables to each other. For this example, we’ll use these three tables…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create table volunteer (
vol_id integer not null,
vol_last_name varchar(50) not null,
...etc...
primary key (vol_id)
);

create table r_volunteer_area (
rva_id integer not null,
rva_description varchar(255) not null,
primary key (rva_id)
);

create table volunteer_area (
va_volunteer_id integer not null,
va_area_id integer not null,
primary key (va_volunteer_id, va_area_id),
foreign key (va_volunteer_id) references volunteer (vol_id),
foreign key (va_area_id) references r_volunteer_area (rva_id)
);

Algorithm - The three-step algorithm is as follows…

  1. Create a comma-delimited string of IDs for the child table.
  2. Delete the IDs from the child table that are not in the list.
  3. Insert the IDs into the child table that are not there already.

Implementation - In PHP, if you have an array, it’s easy to come up with comma-delimited list. To get an array of values back in a post, define your fields with “[]” after the name…

1
2
3
4
<input type="checkbox" name="area[]" id="chkArea1" value="1" />
<label for="chkArea1">Do Something</label><br />
<input type="checkbox" name="area[]" id="chkArea7" value="7" />
<label for="chkArea7">Do Something Else</label>

Here’s the PHP code, using PHP Data Objects (PDO) as the database interface, behind a helper class that creates the statement, appends the parameters, and executes it. (The “quoting” escapes the statement to avoid potential SQL injection attacks - putting it in its own class would make the implementation here much cleaner.)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
/**
* STEP 1
* Create a comma-delimited list of IDs.
*/

// Quote will return the string as '2,3,4' - since we're using this
// as an IN clause of integers, we'll strip the quotes off.
$sAreas = $pdo->quote(join(",", $_POST["area"]));
$sAreas = substr($sAreas, 1, strlen($sAreas) - 1);

// Quote the volunteer ID.
$iVol = $pdo->quote($_POST["vol"], PDO::PARAM_INT);

/**
* STEP 2
* Delete the IDs that are no longer in the list.
*/
$dbService->executeCommand(
"DELETE FROM volunteer_area
WHERE va_volunteer_id = ?
AND va_area_id NOT IN ($sAreas)",
array($iVol);

/**
* STEP 3
* Insert the IDs that are not yet in the list.
*/
$dbService->executeCommand(
"INSERT INTO volunteer_area
SELECT $iVol, rva_id
FROM r_volunteer_area
WHERE rva_id IN ($sAreas)
AND rva_id NOT IN
(SELECT va_area_id
FROM volunteer_area
WHERE va_volunteer_id = ?)",
array($iVol));

Modification - Suppose that now you accepted comments along with each of the checkboxes, so a simple two-integer insert/delete is no longer sufficient. You would still only need to break step 3 into two steps.

  1. Get a list of IDs to update.
  2. For each ID in the posted list
    1. If the ID exists in the update list, update it.
    2. Otherwise, insert it.

The implementation would then be able to use this list to make the decision without hitting the database every time.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// Assume this returns an associative array of IDs.
$aUpdates = $dbService->performSelect(
"SELECT va_area_id
FROM volunteer_area
WHERE va_volunteer_id = ?
AND va_area_id IN ($sAreas)",
array($iVol));

foreach($_POST["area"] as $iArea) {
if (in_array($iArea, $aUpdates)) {
// Update the table
...etc...
}
else {
// Insert into the table
...etc...
}
}

I think you’ll agree that this is much better than spinning through a loop, doing a count on each ID to see if it exists, then either doing an update or an insert based on the count. And, while the implementation here is PHP, it could easily be implemented in any language that supports arrays and database access.

Categorized under , , ,
Tagged , , , , ,


July 9, 2007
  Transferring Data Between Oracle and SQL Server

There are lots of “how to” articles on sharing data between Oracle and SQL Server. Most of these involve installing Oracle’s code base on the SQL Server machine, then using that instance to link tables within Oracle. This technique does not require that, thanks to a product from Oracle called Oracle Instant Client.

To set up the Oracle piece, download the packages for “Basic” and “ODBC Supplement”, and follow the instructions for installation, on the machine with SQL Server. (This is not an “install” per se - it’s basically an unzip.) Next, you’ll need to provide a TNSNAMES.ORA file - this can be any valid file, including a simple shell with an “ifile=” statement pointing to a common TNSNAMES.ORA file. Finally, set the environment variable TNS_ADMIN to point to the directory where this TNSNAMES.ORA file resides.

Now, you can easily create a DTS script through SQL Server to push or pull data however you’d like. Oracle Instant Client will appear in the drop-down list of providers, and you’ll be able to specify your connection the way you normally do (i.e., “DB01.WORLD”).

Happy migrating!

Categorized under , ,
Tagged , , , ,


June 15, 2007
  Transferring CLOBs Across Linked Oracle Databases

Linking databases in Oracle make it easy to share data, and can be useful for replication. However, there is a limitation in Oracle that prevents Character Large Objects (CLOBs) from coming across these links. The following technique uses stored procedures and a temporary table to pull CLOBs across a database link.

First, you’ll need the temporary table, which will hold a sequence number, the primary key for the table where you’ll want to reconstruct the CLOB, and some text. This table can reside in the source or destination database, but must be linked from the other one. For our purposes, it looks like this…

1
2
3
4
5
6
7
8
9
10
11
create table clob_xfer_area
(
cxa_pk number(12),
cxa_number number(12),
cxa_text varchar2(4000 byte)
);
alter table clob_xfer_area add
(
constraint pk_cxa_id
primary key (cxa_pk, cxa_number)
);

Second, you’ll need the procedure in the source database that breaks the CLOB apart and populates the temporary table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
set serveroutput on size 1000000
set lines 1000
set pages 0
set tab off
set feedback on
create or replace
procedure break_clobs_apart
is
v_line_number number(3);
v_text_piece varchar2(4000);
v_total_length number(12);
cursor clob_cur is
select twc_pk, twc_clob_field
from table_with_clob;
begin /* { */
for clob_rec in clob_cur loop /* { */
v_total_length := 1;
v_line_number := 0;
while (v_total_length <=
DBMS_LOB.GETLENGTH(clob_rec.twc_clob_field)) loop /* { */
v_line_number := v_line_number + 1;
v_text_piece := DBMS_LOB.SUBSTR(clob_rec.twc_clob_field,
3999, v_total_length);
v_total_length := v_total_length + 3999;
insert into clob_xfer_area (
cxa_pk,
cxa_number,
cxa_text
)
values (
clob_rec.twc_pk, -- cxa_pk
v_line_number, -- cxa_number
v_text_piece -- cxa_text
);
end loop; /* } of while */
end loop; /* } of clob_cur */
end; /* } of procedure break_clobs_apart */

Third, you’ll need a procedure in the destination database that puts the CLOB back together, and deletes the data from the temporary table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
set serveroutput on size 1000000
set lines 1000
set pages 0
set feedback on
set tab off
create or replace
procedure put_clobs_together
is
v_new_clob clob;
cursor pk_cur is
select distinct cxa_pk
from clob_xfer_area;
cursor piece_cur(p_cxa_pk number) is
select cxa_text
from clob_xfer_area
where cxa_pk = p_cxa_pk
order by cxa_number;
begin /* { */
for pk_rec in pk_cur loop /* { */
DBMS_LOB.CREATETEMPORARY(v_new_clob, TRUE);
DBMS_LOB.OPEN(v_new_clob, DBMS_LOB.LOB_READWRITE);
for piece_rec in piece_cur(pk_rec.cxa_pk) loop /* { */
DBMS_LOB.WRITEAPPEND(v_new_clob, LENGTH(piece_rec.cxa_text),
piece_rec.cxa_text);
end loop; /* } of piece_cur */
DBMS_LOB.CLOSE(v_new_clob);
update dest_table_with_clob
set migrated_clob = v_new_clob
where dtwc_pk = pk_rec.cxa_pk;
end loop; /* } of pk_cur */
delete from clob_xfer_area;
end; /* } of procedure put_clobs_together */

Finally, you’ll need a procedure that controls the whole thing. We’ll assume that this procedure is loaded in the destination database, and the source database is linked with the name “source”.

1
2
3
4
5
6
7
8
9
10
11
set lines 1000
set pages 0
set feedback on
set tab off
create or replace
procedure xfer_clobs
is
begin /* { */
break_clobs_apart@source;
put_clobs_together;
end; /* } */

(This does not include a commit - the changes will not be persistent unless they are committed.)

Of course, these processes could (and, to be useful, likely would) be integrated into other procedures and scripts. But, this framework will successfully transfer CLOBs across linked databases in Oracle.

Categorized under , , , ,
Tagged , , , ,


September 8, 2004
  Apache and MySQL Are Back

I was finally able to resolve my problems with Apache and MySQL. When I decided to mount my FAT32 drive under /home/summersd, I inadvertently caused myself some problems. From talking to a Linux guy at work, I found that no processes that weren’t running under my user ID could access those files. The reason is that Linux looks up the entire diretory tree, back to /, to determine if you can access the file. So, although I had -rwxrwxrwx summersd summersd on every file, /home/summersd was drwx—— summersd summersd, and /home was drwxr-xr-x. The permissions on /home/summersd was keeping Apache from seeing /home/summersd/drive_d/wwwroot, and MySQL from seeing or writing to /home/summersd/drive_d/mysql/data. I moved the drive to /mnt/drive_d, with the mount point being owned by “root”, still mounting the drive with my user name, and everything worked.

In the process of reconfiguring Thunderbird, I believe I may have found out how to share the address book across operating systems. The file ~/.thunderbird/default.[something]/prefs.js has a listing of all the preferences and settings. I modified this file to change the location of my mail files, and there is a setting there for an address book (which isn’t shown in the configuration dialog - after all, it is 0.7.3…) I’ll play with that later - right now I’m just elated to have Apache and MySQL working again.

Categorized under , , , , ,


September 5, 2004
  Success with Wine & Diagnostics

At work, we use an editor called Visual SlickEdit (VSlick). It’s got a lot of features, and supports color-coding for many different languages. I decided that I’d give wine another shot, as we only have the Windows version of this program. I installed wine and winesetuptk, used winesetuptk to configure the installation, then ran the installation program. Everything installed, and the program ran up to a point, when it started complaining about a missing DLL. I booted to WXP, found the DLL, copied it to the FAT32 drive, rebooted to Linux, and copied the DLL into the “fake windows” system directory. Soon, it was working great! I can’t believe it - success with wine!

I also have made little headway towards getting Apache and MySQL to working. I changed the process that Apache uses to run as “summersd”, and I was able to see pages (although any pages that relied on a database didn’t work). I still haven’t figured this one out yet…

I’m still getting kernel panics from time to time, and it seems to be whenever I access networking. A suggestion from one of the folks on the WBEL users list was to download the Ultimate Boot CD, filled with diagnostic programs. I downloaded it, burned it, and ran some memory checks. Those checked out, so I’m going to run a “CPU Burn-In” program to see if it can detect errors from the CPU. It runs for up to 7 days, but I think I’ll just run it overnight - folding@home didn’t take nearly that long to crash it before.

Categorized under , , , , , ,