Month: May 2016

HACK: Change issue creator in Bitnami Redmine

geek Leave a comment

I own the administrator account of a Bitnami Redmine that I installed, but I usually work using a regular user account (Unix rule of not using root). Unfortunately I made the unforgivable mistake of creating a regular issue using the Admin account. For “correctness” sake I tried, and searched if I could modify the creator… (talk about non-repudiation…)

Nope, no default method, or requires a plugin. I don’t intend to do this regularly, so I don’t really need a plugin. I decided to mess with the database directly and see if it was easy to understand the schema. Turns out it was too straightforward.

Notes:

  1. The mysql root password is the same password as the Redmine admin.
  2. I am using a Bitnami Redmine 3.1.0-0 instance, you may need to use “SHOW DATABASES;” to figure out which database.
  3. In the process I used “SHOW TABLES;” and “DESC issues;” to probe the schema. I am just showing the final necessary commands to run.
  4. You can get the issue ID by looking at the URL when the issue is displayed in your browser.
  5. You can mouseover the desired user in the browser to peek at the user’s ID to be used as the author_id.
> ./mysql -u root -p
Enter password: 
mysql> USE bitnami_redmine;
Database changed

mysql> UPDATE issues SET author_id=3 WHERE id=59;

Refresh your browser.

R: Find rows that contains vector

geek Leave a comment

Imagine stations on several train lines. Given a station pair, find the lines that allow travel between these stations (no transfers!)


> # install.packages("qpcR")
> library(qpcR)
> stations = qpcR:::cbind.na(EWL=c("Pasir Ris", "Tampines", "Simei", "Tanah Merah", "Bedok", "Kembangan", "Eunos", "Paya Lebar", "Aljunied", "Kallang", "Lavender", "Bugis", "City Hall", "Raffles Place", "Tanjong Pagar", "Outram Park", "Tiong Bahru", "Redhill", "Queenstown", "Commonwealth", "Buona Vista", "Dover", "Clementi", "Jurong East", "Chinese Garden", "Lakeside", "Boon Lay", "Pioneer", "Joo Koon"),
+                 NSL=c("Jurong East", "Bukit Batok", "Bukit Gombak", "Choa Chu Kang", "Yew Tee", "Kranji", "Marsiling", "Woodlands", "Admiralty", "Sembawang", "Canberra", "Yishun", "Khatib", "Yio Chu Kang", "Ang Mo Kio", "Bishan", "Braddell", "Toa Payoh", "Novena", "Newton", "Orchard", "Somerset", "Dhoby Ghaut", "City Hall", "Raffles Place", "Marina Bay", "Marina South Pier"), 
+                 NEL=c("HarbourFront", "Outram Park", "Chinatown", "Clarke Quay", "Dhoby Ghaut", "Little India", "Farrer Park", "Boon Keng", "Potong Pasir", "Woodleigh", "Serangoon", "Kovan", "Hougang", "Buangkok", "Sengkang", "Punggol"),
+                 CCL=c("Dhoby Ghaut", "Bras Basah", "Esplanade", "Promenade", "Nicoll Highway", "Stadium", "Mountbatten", "Dakota", "Paya Lebar", "MacPherson", "Tai Seng", "Bartley", "Serangoon", "Lorong Chuan", "Bishan", "Marymount", "Caldecott", "Bukit Brown", "Botanic Gardens", "Farrer Road", "Holland Village", "Buona Vista", "one-north", "Kent Ridge", "Haw Par Villa", "Pasir Panjang", "Labrador Park", "Telok Blangah", "HarbourFront"),
+                 DTL=c("Bukit Panjang", "Cashew", "Hillview", "Beauty World", "King Albert Park", "Sixth Avenue", "Tan Kah Kee", "Botanic Gardens", "Stevens", "Newton", "Little India", "Rochor", "Bugis", "Promenade", "Bayfront", "Downtown", "Telok Ayer", "Chinatown"))

> apply(stations, 2, function(route) { all(c("Dhoby Ghaut", "Bishan") %in% route) })
  EWL   NSL   NEL   CCL   DTL 
FALSE  TRUE FALSE  TRUE FALSE

Excel VLOOKUP in R via Rolling Join

geek Leave a comment

Imagine a car park with different parking costs for parking per hour or part thereof. Assume also there is no pattern, thus a mapping table of hour -> cost:

hr cost
0 0.30
1 0.60
2 0.80
3 1.20
4 1.30
5+ 1.60

Parking beyond 5 hours will max your charges at $1.60.

In Excel there is the VLOOKUP function, with Range_lookup=TRUE to find the nearest match.

In R we can do a rolling join on a data table. Without the roll, it works like Range_lookup=FALSE; it finds an exact match.

> # install.packages("data.table")
> library(data.table)
> fees <- data.table(hr=c(0, 1, 2, 3, 4, 5), 
                   cost=c(0.3, 0.6, 0.8, 1.2, 1.3, 1.6))
> fees
   hr cost
1:  0  0.3
2:  1  0.6
3:  2  0.8
4:  3  1.2
5:  4  1.3
6:  5  1.6
> query <- data.table(parked=c(0.4, 1.5, 2, 2.14, 4.5, 10))
> setkey(fees, hr)
> fees[query]
      hr cost
1:  0.40   NA
2:  1.50   NA
3:  2.00  0.8
4:  2.14   NA
5:  4.50   NA
6: 10.00   NA
> fees[query, roll=TRUE]
      hr cost
1:  0.40  0.3
2:  1.50  0.6
3:  2.00  0.8
4:  2.14  0.8
5:  6.00  1.6
6: 10.00  1.6

PostgreSQL 9.4 on CentOS 6.6

geek Leave a comment

As usual there are many guides out there on installing something on some OS, but with Linux I never got a guide that could bring me straight through (every environment, every version requires different setup). So here’s my very own steps for installing PostgresSQL 9.4 on CentOS 6.6. (also for my future self-reference)

Prerequisites: Ensure DNS and HTTP(S) working for yum, otherwise you may encounter Host not found, etc. (This is out of scope as it may be nameservers or firewall settings)

1. Configure yum repo
Ref: http://tecadmin.net/install-postgresql-on-centos-rhel-and-fedora/

sudo rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm
sudo yum install postgresql94-server postgresql94 postgresql94-contrib

2. Initialize the database
Ref: https://wiki.postgresql.org/wiki/YUM_Installation

sudo service postgresql-9.4 initdb
sudo service postgresql-9.4 start

3. Connect and create the database
Ref: http://serverfault.com/questions/110154/whats-the-default-superuser-username-password-for-postgres-after-a-new-install
After default installation, only the “postgres” user can access the database, but it has no password.
Create the database and grant a user access, which you will use to manage the database subsequently (don’t use “postgres” user)

sudo -u postgres psql postgres
    CREATE DATABASE devdb;
    CREATE USER devuser WITH PASSWORD 'devpass';
    GRANT ALL ON DATABASE devdb TO devuser;

4. Allow remote connections
Ref: http://www.thegeekstuff.com/2014/02/enable-remote-postgresql-connection/
pg_hba.conf allows any IP to connect (0.0.0.0) and authenticate using md5. You can also restrict this to your webserver IP only.
postgresql.conf will let the server listen on all attached IPs.

sudo vi /var/lib/pgsql/9.4/data/pg_hba.conf
	host    all     all     0.0.0.0/0       md5

sudo vi /var/lib/pgsql/9.4/data/postgresql.conf
	listen_addresses = '*'

sudo service postgresql-9.4 restart

5. Move the data to another disk
Ref: http://stackoverflow.com/questions/28414558/moving-postgresql-main-folder-out-of-var-lib-postgresql-9-4
My main disk was a default 10GB, enough for OS and programs but not for the database data. I have a spanking new 300GB disk attached, and I want to move the table space to the new disk.
There were several methods involving specifying the data directory but I found it was easier to just link it.

sudo service postgresql-9.4 stop
sudo mv /var/lib/pgsql/9.4/data /media/xvdb1/pgsql/9.4/
sudo ln -s /media/xvdb1/pgsql/9.4/data/ /var/lib/pgsql/9.4/data
sudo chown postgres:postgres /var/lib/pgsql/9.4/data
sudo service postgresql-9.4 start

6. Autostart
Finally, configure PostgreSQL to start itself on boot.

sudo chkconfig postgresql-9.4 on

Ready-to-use PostgreSQL.

ng-admin + JAX-RS: 400 Bad Request on DELETE

geek Leave a comment

I’m tried of building admin UIs and I’m trying out ng-admin. It’s pretty straightforward to setup given the guides and demos.

List, create, updates were fine until I got to the DELETE method. The server was throwing 400 Bad Requests and upon Chrome network inspection I discover that ng-admin was sending a JSON body in the request. I don’t really care who is “following the standard” as long they work together (think browsers and jquery), so I’m fine to fix either side to either the client not send the body, or the server accepting the non-empty body.

ng-admin uses Restangular under the hood to make REST requests. Restangular did have this FAQ about DELETEs with body(s).

A little refactoring and presto! DELETE now works.


app.config(['RestangularProvider', function(RestangularProvider) {
  RestangularProvider.setRequestInterceptor(function(elem, operation) {
    return (operation === "remove") ? undefined : elem;
  });
}]);