Thu, 08 Mar 2007

Accessing a PostgreSQL database from OpenOffice.org base under Debian.

Solution:
apt-get install unixodbc odbc-postgresql
# odbcinst -i -d -f /usr/share/psqlodbc/odbcinst.ini.template
$ cat /usr/share/doc/odbc-postgresql/examples/odbc.ini.template >> ~/.odbc.ini

   1. Set connection parameters in ~/.odbc.ini (username, password, host...)
   2. Check "Connect to an existing database" from the oobase Database Wizard, select ODBC, click on "Browse".

At this point your PostgreSQL database should be listed among the available data sources.

Posted at: 13:05 | category: /database | Comments ()

Sat, 12 Aug 2006

psql commands


 \a             toggle between unaligned and aligned output mode
 \c[onnect] [DBNAME|- [USER]]
                connect to new database (currently "cap")
 \C [STRING]    set table title, or unset if none
 \cd [DIR]      change the current working directory
 \copy ...      perform SQL COPY with data stream to the client host
 \copyright     show PostgreSQL usage and distribution terms
 \d [NAME]      describe table, index, sequence, or view
 \d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
                list tables/indexes/sequences/views/system tables
 \da [PATTERN]  list aggregate functions
 \dd [PATTERN]  show comment for object
 \dD [PATTERN]  list domains
 \df [PATTERN]  list functions (add "+" for more detail)
 \do [NAME]     list operators
 \dl            list large objects, same as \lo_list
 \dp [PATTERN]  list table access privileges
 \dT [PATTERN]  list data types (add "+" for more detail)
 \du [PATTERN]  list users
 \e [FILE]      edit the query buffer (or file) with external editor
 \echo [STRING] write string to standard output
 \encoding [ENCODING]  show or set client encoding
 \f [STRING]    show or set field separator for unaligned query output
 \g [FILE]      send query buffer to server (and results to file or |pipe)
 \h [NAME]      help on syntax of SQL commands, * for all commands
 \H             toggle HTML output mode (currently off)
 \i FILE        execute commands from file
 \l             list all databases
 \lo_export, \lo_import, \lo_list, \lo_unlink
                large object operations
 \o FILE        send all query results to file or |pipe
 \p             show the contents of the query buffer
 \pset NAME [VALUE]  set table output option
                (NAME := {format|border|expanded|fieldsep|null|recordsep|
                tuples_only|title|tableattr|pager})
 \q             quit psql
 \qecho [STRING]  write string to query output stream (see \o)
 \r             reset (clear) the query buffer
 \s [FILE]      display history or save it to file
 \set [NAME [VALUE]]  set internal variable, or list all if no parameters
 \t             show only rows (currently off)
 \T [STRING]    set HTML table tag attributes, or unset if none
 \timing        toggle timing of commands (currently off)
 \unset NAME    unset (delete) internal variable
 \w [FILE]      write query buffer to file
 \x             toggle expanded output (currently off)
 \z [PATTERN]   list table access privileges (same as \dp)
 \! [COMMAND]   execute command in shell or start interactive shell

Posted at: 03:19 | category: /database/postgresql | Comments ()

Fri, 11 Aug 2006

MySQL - set initial root password

Remember to set a password for the MySQL root user!
This is done with:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h coyner.net password 'new-password'

Posted at: 01:38 | category: /database/mysql | Comments ()

mysqldump

// To backup a database

mysqldump -u ironmossman -p ironmoss_db > backup.sql


// To backup a database via SSH

ssh ironmossman@teammossman.com "mysqldump -u ironmossman -psecret ironmoss" > backup.sql

Posted at: 01:38 | category: /database/mysql | Comments ()

Using sqlobject in cherryPy

from sqlobject import *

conn = 'mysql://dbuser:dbpass@host/dbname'

class NameOfTable(SQLObject):
    _connection = conn
    _fromDatabase = True
    _style = MixedCaseStyle(longID=False)

class Newspage(Page):
    def index(self):
        article = NameOfTable.select(NameOfTable.q.field=='Joe', orderBy=-NameOfTable.q.dateAdded)


Notes:
    _fromDatabase = True means sqlobject will simply take column names from
    the DB.  It won't create any, which it is capable of doing.

    MixedCaseStyle - sqlobject uses pythonic names like my_name.  This allows
    the use of names like myName.

    longID=False - prevents sqlobject from automatically creating an ID column

    - in front of NameOfTable.q.dateAdded means reverse sort

Posted at: 01:38 | category: /database | Comments ()

MySql Cheat Sheet 

These are the main MySQL clients and processes (mysqld):

mysqld - MySQL server daemon
safe_mysqld - Server process monitor
mysqlaccess - Tool for creating MySQL users
mysqladmin - Utility for administering MySQL
mysqldump - Tool for dumping the contents of a MySQL database
mysql - Command line interface to MySQL
mysqlshow - List all MySQL database


These are the main Field Types in SQL:

INTEGER - A whole number
VARCHAR(10) - Up to 10 characters.
CHAR(10) - Fixed number of characters
DATE - A date
DATETIME - Date and time
FLOAT - Floating point numbers



These are some Field Types specific to MySQL:

TEXT - Allows up to 65535 characters
DECIMAL(10,2) - Up to 10 digits before the point, 2 after.


Here we Create a database:

$ mysqladmin --user=Theoden --password=xxx create database addressdb


Using that database:

$ mysql --user=Theoden --password=xxx
mysql> USE addressdb


Create a table in that database:

mysql> CREATE TABLE p_addr (i INTEGER PRIMARY KEY,address TEXT,email VARCHAR(30),pincode
DECIMAL(10),phone DECIMAL(15),website TEXT);


Add a column called "name" to the table:

mysql> ALTER TABLE p_addr ADD name VARCHAR(30);


Inserting values into table:

mysql> INSERT INTO p_addr VALUES (1,"My, present,
address","Theoden@localhost",681024,2122536,
"http://your.website.com","Theoden");


List the contents of the table:

mysql> SELECT * FROM p_addr;


Delete a row from the table:

mysql> DELETE FROM p_addr WHERE i=1;


Rename a column in the table from "address" to "home_address":

mysql> ALTER TABLE p_addr CHANGE address home_address INTEGER;
Note: You cannot use this method to rename a column which is a primary key.


Change an existing record in the table:

mysql> UPDATE p_addr SET name="William" WHERE i=2;


Delete the table from the database:

mysql> DROP TABLE p_addr;


List the databases available in 'your' MySQL installation:

$ mysqlshow --user=Theoden --password=xxx

This will return the following:

+-----------+
| Databases |
+-----------+
| addressdb |
| myblog |
| mysql |
| test |
+-----------+


List the tables in the database "addressdb":

$ mysqlshow --user=Theoden --password=xxx addressdb
Database: addressdb

This will return the following:

+---------+
| Tables |
+---------+
| p_addr |
| mytble |
| phonebk |
+---------+

Posted at: 01:38 | category: /database/mysql | Comments ()