There are scripts that convert CSV to SQL, but most modern database systems support reading CSV data such as:

• PostgreSQL
• MySQL
• Oracle
• Microsoft SQL Server
• Sybase Adaptive Server Enterprise (which uses the same Transact-SQL dialect as MS-SQL).

They all require different syntax to import CSV data, some examples of which are detailed here.

Laying Out the Data

This reference relies on a few assumptions. Firstly, it assumes you have obtained your data in CSV format. Secondly, good sense assumes you would not be doing this if you had a better way. Important metadata can be lost by using CSV as an intermediate format. Details like Foreign Keys, column constraints, and so on.

For the purposes of this guide, we will be using the following dataset.

"id","firstName","lastName"
12345,"Ingrid","Insert"
23456,"Eric","Example"
12543,"Dave","Dataset"

All examples that follow are based on this CSV set.

Creating the Table

A simple example of a table we may be importing the CSV file to.

CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY,
firstName VARCHAR(32) NOT NULL,
lastName VARCHAR(48) NOT NULL
);

Importing the Data

This process isn’t terribly complicated, but it does vary according to the database system you are importing to.

MySQL

LOAD DATA INFILE '/path/to/employees.csv' INTO TABLE employees
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Microsoft SQL Server and Sybase ASE

BULK INSERT employees FROM ‘c:\path\to\employees.csv’ WITH (
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
);
GO

PostgreSQL

COPY employees FROM '/path/to/employees.csv' DELIMITERS ',' CSV;

Oracle

Offhand I don’t know a way to import a CSV file into Oracle using only SQL or PL/SQL, but there’s a great article on Dev Shed that covers how to do it with the Oracle 10G XE administration interface. Here is a convenient link to the good part, since if you’re reading this, you have likely already exported the CSV data, and want to know how to import that CSV to SQL.

Alternatively, you could convert your CSV file to SQL INSERT statements, and then execute those INSERTS. An simple CSV to SQL Conversion Tool can do this effectively.

If anyone knows how to import CSV files into Oracle without using the admin GUI or an external tool, please share!

References

• http://dev.mysql.com/doc/refman/5.1/en/load-data.html
• http://www.ensode.net/postgresql_csv_import.html
• http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

Post from: Slack/Tux

• Changing footer links in a template.
• Updating copyright information on a static HTML site.
• Replacing contact information in many files.

One solution is very simple shell script. It requires sed (a classic text replacement utility), which should be available on any Linux or Unix system. We can feed it a very simple regular expression, dump the output to a temporary file, and overwrite the original with it.

Batch text replacement for multiple files with a shell script:

TMPFILE=./tmp.$$

for filename in *.txt; do
  sed 's/text_to_replace/something_to_replace_it_with/g' $filename > $TMPFILE
  mv $TMPFILE $filename # Comment this line out to not overwrite the original file.
fi

As mentioned above, this is just one way to accomplish this. Phil Dufault suggests two alternative methods (non-recursive and recursive)

Replacing one string with another in multiple files using sed:

sed -i 's,text,replacement,g' *.txt

Recursively substituting text in many files with find and sed:

find . -type f -iname '*.txt' -exec sed -i 's,this,that,g' {} +

Post from: Slack/Tux

Cappy has released a handy script to detect and install libraries and other dependencies for 32-bit applications on 64-bit Ubuntu GNU/Linux. Handy things it can do include fetching missing libraries either by name, and figuring out which ones are needed when presented with a given binary.

Note: This script makes use of the Debian package management system, and is unlikely to function properly on distributions that are not Debian-based.

Installing getlibs

Installation couldn’t be simpler. Download getlibs, and double-click the .deb package. If you download getlibs via Firefox, you should get an ‘Open with gdebi’ option or something equivalent. To install via commandline, try:


$ wget http://www.boundlesssupremacy.com/Cappy/getlibs/getlibs-all.deb
$ sudo dpkg -i getlibs-all.deb


Installing Libraries with getlibs

Usage is pretty straightforward. If you know the name of the library you need, you can feed it to getlibs, and it should fetch it.

$ sudo getlibs -l libogg.so.0 libSDL-1.2.so.0
Matched library libogg.so.0 to libogg0
Matched library libSDL-1.2.so.0 to libsdl1.2debian-all
Reading package lists… Done
Building dependency tree
Reading state information… Done
libogg0 is already the newest version.
The following NEW packages will be installed:
libsdl1.2debian-all
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 213kB of archives.
After unpacking 20.5kB of additional disk space will be used.
Do you want to continue [Y/n]?


Alternatively, provide getlibs with the location of a given binary, and it should figure out what is missing (if anything) and install it. For example, say the binary for Second Life was located at /opt/32/secondlife/secondlife.bin:


$ sudo getlibs /opt/32/secondlife/secondlife.bin


At which point it should match any libraries that are not currently installed and fetch them, assuming they are available (see above for output example).

Sources

• http://ubuntuforums.org/showthread.php?t=474790

Post from: Slack/Tux

The configuration outlined here may also have the added benefit of caching web requests, which can provide not-insignificant performance gains for many home users, most often in the case of frequent accesses to the same site (such as Facebook, which seems to be popular at the time of this writing).

Requirements

• Proxy software (we will use Squid in this example)
• Something to filter the content (we will use DansGuardian)
• A blacklist of some sort (Optional)

Why Content Filtering
A blacklist alone is no longer sufficient for filtering web content effectively. Given the rate at which the web is growing: Approximately 334 new domains were registered every minute over the last 24 hours, according to Domain Tools. Add the amount of new content being added to existing domains, and we have a lot of sites to filter. Managing a list of it all would not only require a massive amount of human effort (or computational resources), the size of such a list would be ridiculous, even if compressed. If every request to retreive a site were checked against such a list, the verification delay would be very inconvenient (think of feeding a 5GB+ file to grep, looking for a specific address).

Enter Content Filtering, a technology that inspects individual pages for ‘objectionable’ material, and decides based on the contents of those pages whether or not to allow access to them. A blacklist can supplement this technology to improve performance (if the blacklist is small enough, it can be faster to search than the contents of a large page).

Together, they form an effective means of ‘cleaning’ the content served to users of your system (or network).

Installing and Configuring Squid

First, we need to install and configure a proxy. We’ll be using squid for this example, as the caching capabilities it offers provide benefits not found in many competing proxies.

Download Squid and install it.


$ tar xzvf squid-*RELEASE*.tar.gz
$ cd squid-*
$ ./configure
$ make
# make install


In the case of Debian/Ubuntu users compiling from source, some minor tweaks should be made to the configure line, to accomodate the subtle differences in filesystem layout from what Squid would otherwise expect.


$ ./configure --prefix=/usr --localstatedir=/var --libexecdir=/usr/lib/squid --srcdir=. --datadir=/usr/share/squid --sysconfdir=/etc/squid


While it is technically possible to build and install Squid on a Debian or Ubuntu system without these changes, they will ensure that the installation aligns with the general filesystem layout of those distributions, and should make distribution-specific troubleshooting a bit easier.

Additionally, a minor tweak to ./src/Makefile.am in the directory you extracted to, prior to running make will avoid the need to do silly things. Change this line:


DEFAULT_LOG_PREFIX = $(localstatedir)/logs


To make it look like this:


DEFAULT_LOG_PREFIX = $(localstatedir)/log


Users of Debian-derivatives (such as Ubuntu or MEPIS) who do not wish to compile from source can simply fetch a recent binary from the supported repositories, and install it. This is easily accomplished with the provided package management tools:


$ sudo apt-get install squid


Edit /etc/squid/squid.conf with your editor of choice, paying special attention to the acl/http_access section. Something similar to the following configuration should work (configure according to your network range, of course):


acl local_network src 192.168.0.0/24 192.168.254.0/24
http_access allow local_network


Installing and Configuring DansGuardian

Download DansGuardian and install it:


$ tar xzvf DansGuardian-*
$ ./configure
$ make
# make install


Debian/Ubuntu users can do the following:


$ sudo apt-get install dansguardian


Edit /etc/dansguardian/dansguardian.conf, commenting out the UNCONFIGURED line once complete. Things to pay attention to are…


filterip =
filterport = 8080
proxyip = 127.0.0.1
proxyport = 3128


Configuring Firefox to Filter Content

Setting up a content filtering proxy is effective, but only if it filters information. If requests are not directed through the proxy, it cannot effectively ‘clean’ the content of ‘objectionable’ material.

• Edit -> Preferences -> General -> Connection Settings -> Manual Proxy Configuration
• Input 127.0.0.1 as the proxy IP
• Input 8080 as the proxy port
• Enable the checkbox for ‘Use this proxy server for all protocols’

Please note that these settings only affect Firefox. Other web browsers and software will need to be configured as well. Configuration steps should be similar. If you would like specific examples, please post your requests in the comments for this article.

Preventing Users from Disabling the Filter

The restrictions created by a content-filtering proxy can be easily circumvented by simply not using the proxy. Assuming that the users so restricted do not have administrative access, this can be prevented as follows:

Edit /usr/lib/firefox/firefox.cfg and add the following entries:


lockPref("network.proxy.http","127.0.0.1");
lockPref("network.proxy.http_port",8080);
lockPref("network.proxy.type,1);
lockPref("network.proxy.no_proxies_on","localhost,127.0.0.1");


Sources

• http://unixadmintalk.com/f11/content-filtering-proxy-82337/
• http://wiki.squid-cache.org/SquidFaq/CompilingSquid
• http://ubuntuforums.org/showthread.php?t=207008
• http://ubuntuforums.org/showthread.php?t=320733
• http://dansguardian.org/downloads/content_filtering_challenges.pdf

Post from: Slack/Tux

If a URI identifies a specific document, two different URIs are often treated as two different documents. If the contents of each are identical, it could be assumed that one is a copy of the other. In fact, if we treat them as two documents, that is the only logical conclusion. How then, if both are truly identical in all ways other than URI (same timestamps, content, etc), are we to determine which is official?

This is a canonical problem, and it can be fixed using mod_rewrite.

The most common type of canonical problem occurs when search engines recognize www.yourdomain.com, and yourdomain.com as different sites. A quick fix can be implemented in your .htaccess file like so (using mod_rewrite):

Enable mod_rewrite if it is not already active:


RewriteEngine On


And set up a rule to direct the unofficial (non-www in this case) to the official (www) with a status code indicating that this is a permanent redirect (HTTP 301).


RewriteCond %{HTTP_HOST} ^yourdomain.com$
RewriteRule (.*) http://www.yourdomain.com/$1 [R=301,L]


This tells browsers and search bots that the canonical (official) URL for your site is the one with www, and that all requests should be directed there.

Post from: Slack/Tux

Note: The timestamp on this article may seem unusual. This was written before Slack/Tux was purchased, and relocated to this site, where it was more fitting.

Update: The concepts outlined here also work with virtual interfaces (aliases). This can be used to configure multiple IPs on a single interface. While this doesn’t have a lot of practical advantages for desktop users, there is significant value for servers. Thanks to Mickael Maddison for testing this.

Keep in mind that multiple virtual interfaces would still be a single physical connection though, so the maximum throughput would remain the same. This could also be used to allow a single ethernet card to span multiple subnets.

Requirements

• PC or router running GNU/Linux
• Multiple WAN Connections, either from the same ISP or different ones
• A dedicated ethernet adapter for each connection.

Update 2008-05-23: It should be noted that this example requires all involved interfaces to be configured already. It assumes IPs have been assigned without using DHCP, though minor changes could account for that. The interfaces must also be active (not stopped). Thanks for Mickael Maddison pointing out that I hadn’t mentioned that.

Example Setup
In this example, I have a 10MBit Cable connection via Shaw on eth1, and a 6MBit ADSL connection via TELUS on eth2.

• eth1 - IP 192.168.254.100 / Gateway 192.168.254.1
• eth2 - IP 192.168.1.100 / Gateway 192.168.1.254

Simple Configuration
First, we need to add two lines to /etc/iproute2/rt_tables

1 Shaw
2 TELUS

And then set up the routing for those tables.

# ip route add 192.168.254.0/24 dev eth1 src 192.168.254.100 table Shaw
# ip route add default via 192.168.254.1 table Shaw
# ip route add 192.168.1.0/24 dev eth2 src 192.168.1.100 table TELUS
# ip route add default via 192.168.1.254 table TELUS
# ip rule add from 192.168.254.100 table Shaw
# ip rule add from 192.168.1.100 table TELUS


Set up evenly weighted round-robin routing for the interfaces.

# ip route add default scope global nexthop via 192.168.254.1 dev eth1 weight 1 nexthop via 192.168.1.254 dev eth2 weight 1


Fixes and workarounds
In the event that you receive a RTNETLINK answers: File exists error, replace the previous entry with…

# ip route append default scope global nexthop via 192.168.254.1 dev eth1 weight 1 nexthop via 192.168.1.254 dev eth2 weight 1

Then remove the earlier route:

# ip route del


Alternatively, omiting both

# ip route add default via 192.168.254.1 table Shaw
# ip route add default via 192.168.1.254 table TELUS

should prevent this as well.

Slightly more complex configurations
In addition to the basic setup here, the interfaces can be weighted differently, to favour one over the other (useful if one is a larger pipe, as in my setup here).


# ip route append default scope global nexthop via 192.168.254.1 dev eth1 weight 5 nexthop via 192.168.1.254 dev eth2 weight 3

In the case of IP-bound services (example: a GigaNews account, which does not allow simultaneous connections from different IPs), a static route is simple to configure:


# ip route add 216.196.97.131 via 192.168.254.1


If one of your ISP blocks DNS queries from non-subscribers, then you will need to ensure that your primary DNS server is ISP-agnostic. OpenDNS is a great solution for this. Add the appropriate entries to /etc/resolv.conf

nameserver 208.67.222.222
nameserver 208.67.220.220


Sources

• http://lartc.org/howto/lartc.rpdb.multiple-links.html
• http://www.linuxquestions.org/linux/answers/Networking/Spanning_Multiple_DSLs
• http://mailman.icsi.berkeley.edu/pipermail/xorp-users/2004-October/000266.html

Post from: Slack/Tux

At least two solutions exist, each suited to a different purpose.

apt-build

The first method relies on the apt-build package, which fetches the latest source available in the repositories listed in your /etc/apt/sources.list file. This source is then compiled into a package (generally the same configuration as if you had installed the binary, unless you specify otherwise) and installed. Updates should still be managed as normal.

Note: This method is ideal if the package has source available in one of the repositories you have configured.

To install apt-build:

$ sudo apt-get install apt-build


This should add a local repository to /etc/apt/sources.list for any built packages. To ensure that the package manager is aware of this:


$ sudo apt-get update


Building a package with the default options can be accomplished as easily as installing a precompiled binary package. For example, to build Prism:


$ sudo apt-build install prism


checkinstall

On occasion, the need may arise for an application that is not included in the standard repositories (or any supplemental ones that have been configured). This is common with niche software, software with licensing complications that prevent it from being hosted in one of the repositories, abandonware, and applications that are have no maintainer beyond the upstream developers.

For cases like these, checkinstall is an ideal solution, as it allows us to build a .deb package for easy removal later. This package will not have updates tracked, but does offer the most flexibility.

To install checkinstall:


$ sudo apt-get install checkinstall


With it installed, building from source follows the usual documentation for a package, with one minor adjustment: We substitute the checkinstall command in place of make install. For example:


$ tar xjf package-source.tar.bz2
$ cd package-source
$ ./configure
$ make
$ sudo checkinstall


At this point, checkinstall will ask you for a few details about the application being built (package name, version number and such), and construct a .deb package with the appropriate metadata. It will be installed automatically, and the package-name.deb will reside in the current directory. It can be backed up or deleted, depending on your need. Reinstalling it is as simple as:


$ sudo dpkg -i package-name.deb


Removal follows the typical style:


$ sudo apt-get remove --purge package-name


Where package-name is the name provided to checkinstall when it asked for it.

Again, it is important to note that packages built with checkinstall are not tracked for updates. You need to ensure that you stay aware of any important updates for packages built like this. Many applications have RSS feeds or mailing lists for releases and updates, and subscribing to these could allow you to patch a vulnerable application before it becomes a problem.

Post from: Slack/Tux

If you have SSL support enabled (either with a verified SSL certificate, or a self-signed one), you can require all connections to your site to use SSL with a .htaccess file. Note that this uses mod_rewrite.

Requirements

• Web Server running Apache
• mod_rewrite installed and active
• An SSL certificate installed and configured
• Read/Write permissions on the file ‘.htaccess’ in whatever directory you are configuring

Enable mod_rewrite if you have not already done so, by adding the following line to your .htaccess file:


RewriteEngine On


Important Note! Setting RewriteEngine On multiple times in the same file or in multiple files (such as in a subdirectory) can trigger Internal Server Errors. If some (or all) pages return an HTTP 500 status code after enabling this, check to see if it is already enabled in the current .htaccess, or in the parent directory (check all the way back until you hit your DocumentRoot (usually public_html).

With it enabled, we can check which port the request is for (by default 80 is HTTP, 443 is HTTPS). If it is HTTP, silently rewrite the request to HTTPS:

RewriteCond %{SERVER_PORT} 80
RewriteRule ^(.*)$ https://www.example.com/$1 [R,L]


As mentioned about, this can cause problems with RSS feeds. For example, FeedBurner is not SSL-aware at the time of this writing, and is unable to parse feeds that have SSL enforced like this. For the specific case of FeedBurner, add the following line before the RewriteRule line:

RewriteCond %{HTTP_USER_AGENT} !FeedBurner [NC]


Post from: Slack/Tux

Anyone familiar with the previous site will notice that the ad-farm has been taken down in favour of a quality content (in time, of course. I’m sure it looks rather barren at the moment). Though a large number of the links floating around out there indicate that Slackware-related content is available here (Italian documentation, as far as I understand), this is not the case. Allow me to extend my apologies for this deception. These links exist from days long past, and are beyond the reach of my editing powers.

I look forward to this space becoming something of value to the GNU/Linux community, and welcome you all (regardless of your chosen distribution).

Post from: Slack/Tux