Category Archives: Databases - Page 3

How to drop a column in SQLite 3

Posted by on September 25, 2012 2 comments

As you might know, SQLite 3 does not support dropping columns from tables with ALTER TABLE command so we have to do a workaround instead. The idea is to create a new table with all the columns (and data) that we want to keep and then drop the old table and rename the new table with the old name.
Here is an example:

First we create a new table with all the columns and data we want to save

CREATE TABLE my_table_temp AS (SELECT id, user_id, latitude, longitude FROM my_table);

Then we drop the old table

DROP my_table;

And finally we rename our new table with the old name

ALTER TABLE my_table_temp RENAME TO my_table

Done!

Tested on OSX 10.7.5 and SQLite v3.7.11

OS X Lion, PostgreSQL and MacPorts install and administration cheat sheet

Posted by on May 31, 2012 2 comments

Since PostgreSQL is a nightmare to administer I have to put these hints down here so I never have to deal with it again 🙂

Install PostgreSQL with Macports:

sudo port install postgresql90-server

Create the default database environment

sudo mkdir -p /opt/local/var/db/postgresql90/defaultdb
sudo chown postgres:postgres /opt/local/var/db/postgresql90/defaultdb
sudo su postgres -c '/opt/local/lib/postgresql90/bin/initdb -D /opt/local/var/db/postgresql90/defaultdb'

Start the database environment

sudo su postgres -c '/opt/local/lib/postgresql90/bin/postgres -D /opt/local/var/db/postgresql90/defaultdb'

or

sudo su postgres -c '/opt/local/lib/postgresql90/bin/pg_ctl -D /opt/local/var/db/postgresql90/defaultdb -l /tmp/pg.log start'

Now lets create the database we are going to use

sudo su postgres -c '/opt/local/lib/postgresql90/bin/createdb test'

and now we want to connect to the newly created database with psql

sudo su postgres -c '/opt/local/lib/postgresql90/bin/psql test'

To add a new user to the database and give him full privileges. Open psql (like above) and type:

CREATE USER niklas WITH PASSWORD 'myPassword';
GRANT ALL PRIVILEGES ON DATABASE myDatabase to niklas;

After this the user ‘niklas’ can logon to the database with this:

/opt/local/lib/postgresql90/bin/psql test

Tested on OS X Lion (10.7.3), PostgreSQL 9.0 and MacPorts 2.0.4

Document a database

Posted by on December 27, 2011 No comments

When working with legacy systems documentation is one of the most important things to keep up to date. One thing I have noticed is that the database documentation quite often fall behind when it comes to updates. Changes are often made to the database but the documentation is not always updated in the same way as new functions are. My trick here is to keep the database documentation inside the database. This way I do not have to search for a separate document every time you make changes to the databasen. I can update the documentation in the same view as the one I made the changes in.

To accomplish this I create a new table in the same schema as the program tables. I usually call it something like ‘db_doc’ or ‘db_info’
In this table I have the following columns:

  • ID – a unique id for the row (I often use ‘autoincrement’ in MySQL)
  • Table – textfield for the name of the table to describe
  • Column – textfield for the name of column to describe
  • Description – large textfield for the description
  • Author – textfield for the name of the person who updated the field
  • Timestamp – timestamp field to be updated on inserts and updates to the row

Here is a SQL example of the create statement for MySQL:

CREATE TABLE  `db_info` (
 `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 `table` VARCHAR( 255 ) NOT NULL ,
 `column` VARCHAR( 255 ) NOT NULL ,
 `description` TEXT NOT NULL ,
 `author` VARCHAR( 255 ) NOT NULL ,
 `timestamp` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
)

Here is an example view of a table working as database documentation:
Example of a table working as database documentation

Now if you wanted it would be pretty easy to make this into a web page to give it a little more “documentation” feel.