Posts categorized “Databases”


June 22, 2013
  Oracle SQL Developer 3.2 Debian Package

Oracle has released version 3.2 (.20.09) of their SQL Developer tool. They’re still releasing RPMs, so developers on Debian-based systems need to use alien to install it on their machines. We have done that, and have made this available for others to use as well. What makes this particular release of SQL Developer so great is that it now runs reliably under Java 1.7 - no more keeping a 1.6 JDK floating around just for SQL Developer!

The .deb package can be downloaded here, or you can browse current and previously posted packages in the “SQL Developer” directory of the Bit Badger Solutions Software Repository.

Categorized under , , ,
Tagged , , ,


October 22, 2011
  Database Abstraction v0.8

When we began developing C# web applications, we found ourselves in the position of determining what the best way of accessing the database is. We evaluated several technologies…

  • NHibernate - May be very good, but it was overkill for what we were trying to do.
  • LINQ to SQL - This brings C#’s LINQ (Language-Integrated Query) to SQL databases. You create database-aware classes and use LINQ to select from collections, which LINQ to SQL converts to database access. This is a good abstraction, but it relies on SQL Server; as we typically deploy to PostgreSQL, this didn’t work. (We also couldn’t get DBLinq, a database-agnostic implementation, to work.)
  • ADO.NET - This is the tried-and-true database access methodology, released as part of the initial release of the .NET framework. The downside to this is that it encourages SQL in the code at the point of data retrieval; it does not provide a clean separation of data access from data processing.
  • EF Code First - This didn’t exist; it’s also very SQL Server-centric. Not faulting Microsoft for that, especially since they release a free version now; but, as we deploy on Linux, until they release a Linux version, SQL Server is not an option.

With our PHP applications, we had written a database service that read queries from XML files. Then, queries were accessed by name, with parameters passed via arrays. The one thing that ADO.NET has that was useful was the fact that it is based on interfaces. This means that if we wrote something that exposed, manipulated, and depended on IDataConnection (instead of SqlConnection, the SQL Server implementation of that interface), we could support any implementation of database. The SqlDataReader implements IDataReader as well. Our solution was becoming apparent.

Over time, we developed what is now the Database Abstraction project hosted on CodePlex (UPDATE: migrated project to GitHub). On Thursday, we released the first public release (although the DLLs are in the repository, and are usually current at every commit). If you are looking for a way to separate your data access from the rest of your code, or want a solution that’s database-agnostic, check it out. It supports SQL Server, MySQL, PostgreSQL, SQLite, and ODBC connections , using the data provider name to derive the proper connection to implement. There is also a Mock implementation to support unit tests; this mock can provide data, providing a useful way to test methods. Finally, there is a membership and role provider based on Database Abstraction; simply configure the connection string, create the database tables, and away you go! *

A pre-release version is already in production use in our PrayerTracker application, and others are being built around it. If this sounds like something that could help your project, certainly feel free to check it out!

* Oracle is omitted from this list, as their DLL had redistribution restrictions; this meant that the source code repository, upon check-out, would have build errors. There may be an Oracle implementation in the future (it would be trivial), but there is not one now.

** The membership and role providers are untested; they will be tested and tweaked by version 0.9.

Categorized under , , , , , , , , ,
Tagged , , , , ,


March 2, 2010
  Oracle SQL Developer 2.1 Debian Package

It had been a while since I had updated SQL Developer. It turns out that version 2.1 was released March 1st of this year. I’ve downloaded it and created a Debian package. It can be downloaded from the Bit Badger Solutions Linux Software Repository.

I’ve used it with Sun’s Java 6 Update 18; I have not tested it with OpenJDK. If you have problems getting it to work, you may want to check the previous post on this topic.

Categorized under ,
Tagged , ,


October 29, 2008
  Oracle SQL Developer Debian Package

Oracle SQL Developer is a Java-based tool that provides a graphical interface to a database. While it’s main focus is Oracle (of course), it can be hooked up, via JDBC, to many other databases, such as MySQL, PostgreSQL, and SQL Server. It’s similar to Toad, but is provided by Oracle at no cost.

Oracle provides SQL Developer in either an RPM, or a generic binary install. I like the ability to manage packages, but I’ve never had much luck at getting RPM to run on Ubuntu. I downloaded the RPM file, and, using alien, I converted the package to a .deb package (Debian package format) and installed it. It worked like a charm!

I haven’t tested it with gcj, but using Sun’s Java 6 update 7 from the Ubuntu repositories, it ran just fine. After you install the package, do a directory list on /usr/lib/jvm. You’re looking for the Sun JDK - if it’s installed, you’ll have a symlink java-6-sun that points to java-6-sun-1.6.0.07. Once you’ve determined the location of the JDK, run “sqldeveloper” from the command line - the program will prompt you for the path to your JDK. Enter it (probably /usr/lib/jvm/java-6-sun) and you’re good to go. (You have to install the package as root - but, for the rest of these steps, use your normal user, not root, as this puts settings in a .sqldeveloper directory off your home directory.) The package installs an icon in the “Programming” or “Development” group. Once you’ve told it where the JDK is, you can use this to launch it.

Download SQL Developer 1.5.1 Debian Package

Categorized under , , , , , ,
Tagged , , , , ,


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 , , , , ,