Intern News

At the end of the fourth week in Adelaide a short recall of topics:

Linux

Debian:

  • server setup, port configuration, JAVA application deployment
  • services: apache2, SSH, webmin, firestarter
  • configuration of sudoers, proxy configuration;
  • issues with blocking site partially solved with increasing time between requests (other options: running several instances of the application, randomize requests);
  • moved to server room, up and running.

Ubuntu:

  • desktop GUI setup;
  • Oracle setup following an excellent tutorial on http://www.pythian.com/blogs/968/installing-oracle-11g-on-ubuntu-804-lts-hardy-heron A tricky part was the creation of a database using dbca. All wizard steps where pretty smooth. There were no errors at all. But the last screen of the wizard just showed a Grey window without any buttons, so the process couldn’t be completed. Problem solved with disabling all visual effects in Ubuntu (System->Preferences->Appearance->Visual Effects->’None’);
  • without a startup script, the $ORACLE_SID has to be set to the database name before connecting with SQL Plus: export ORACLE_SID=dbname
  • connect to database using command history recall (‘Up’-Key): rlwrap sqlplus username

Commands: sed, cp, mv, nc, ssh, dhclient, ifconfig,…

Databases

Oracle

  • played with several SQL statements and PL/SQL procedures to determine relationship between queries and documents, starting with (query, doc1, doc2) triples. Each triple represents a graph; the graphs connected result in a ‘forest’ which can then be explored (emergent semantics);
  • wrote documentation

PostGres

As we’re running Macs in our Lab, we’d like to keep the TimeMachine funcionality on the Mac Pro where the database is going to run. Therefore, no Linux. In fact, there’s no Oracle 11g server distribution for MacOS. As we won’t need Oracle’s Spatial functionality like we thought before, we might port to PostGres.

Oracle – PostGres differences

Oracle PostGres
VARCHAR2(n) no VARCHAR2(n), use VARCHAR(n) instead
no autoincremention, use triggers instead auto incrementation data type SERIAL4
sequence.NEXTVAL, sequence.CURRVAL
  • nextval(‘sequence’)
  • currval(‘sequence’)
  • lastval (returns value most recently obtained)
  • setval(‘sequence’, bigintvalue)
PL/SQL unique constraint exception DUP_VAL_ON_INDEX UNIQUE_VIOLATION
RETURN RETURNS
DROP FUNCTION fname; DROP FUNCTION fname(TYPE, TYPE,…);
EXEC fname(arg1, arg2,…); SELECT fname(arg1, arg2,…);
Before creating a function: CREATE LANGUAGE plpgsql; define to use this language in function header: … RETURNS void AS $$ DECLARE …; after function creation: $$ LANGUAGE ‘plpgsql';
DESC tablename; \d tablename;
START script.sql; \i script.sql;
Start client from command line: rlwrap sqlplus username sudo -u user psql database
Exit command line: exit \q
Show user tables: select table_name from user_tables; \dt
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s