Tänään tuli vastaan tapaus jossa piti hakea tietokannasta SQL-kysellä
kaikkein yleisimmin esiintyvä arvo tietyssä taulun sarakkeessa.
Ei ollut varmaankaan aikaisemmin ollut tarvetta moiselle kyselylle,
mutta lopulta googlen kautta vastauksen löysin.
I think its new in PostgreSQL 8.4, there is a new field type called citext, which stands for case insensitive text. Currently on Ubuntu 9.10 its in the PostgreSQL contrib package, which often serves as a testbed for features before they are adopted into PostgreSQL proper.
Using citext ensures that all comparisons are case insensitive, which includes referential integrity on inserts, updates and deletes, and SQL queries for lookups for example, which I think will work well for those fields that should always be compared case insensitive.
Although citext (and probably many other modules) are not listed on the contrib page, they are included, you can see them in the complete file list.
Steps to install on Ubuntu 9.10 (Karmic Koala):
# Install the contrib package
sudo apt-get install postgresql-contrib-8.4
# Add the citext module to a database of your choice
psql [username] -d [databaseName] -f /usr/share/postgresql/8.4/contrib/citext.sql
Where in this case /usr/share/postgresql/8.4 is the PostgreSQL SHARE_DIR folder.
Now you can use the field citext instead of text. If you'd like to use citext on Ubuntu 9.04 Jaunty (which only includes PostgreSQL 8.3), then check out Mark's post about installing PostgreSQL 8.4 on Ubuntu 9.04.
Your life of Ruby + Java is going to be fun and painful. I hope this will get you started quickly so you can move on to the more annoying stuff. (JRuby is still amazing though. JVM is also quite lovely from what I've read.)
Required: Make sure you have a Java VM and Postgresql setup.
Download JRuby 1.3.1. (Archived. Already built.):
http://kenai.com/projects/jruby/pages/Home
Extract the contents.
Go to that folder:
cd jruby-1.3.1
Check everything is ok:
bin/jruby -v
Now do:
bin/jruby -S gem install sequel
Now go to: http://jdbc.postgresql.org/download.html
Download a driver (.jar file) where it says "Current Version."
Remember the location.
Now to test if it works:
# FILE NAME: quick_start.rb
require 'rubygems'require '/Path/To/Driver/postgres-..-..jdbc4.jar' # Put the path/filename of the driver you downloaded. require 'sequel'host = 'localhost'
db_name = 'my-database' # <=== Change this.
user = 'my-username' # <=== Change this.
pass = 'my-password' # <=== Change this.
DB = Sequel.connect( "jdbc:postgresql://%s/%s?user=%s&password=%s" % [ host, db_name, user, pass] )my_table = :schema_info # <=== Change this.puts DB[ my_table ].all.inspect
Now do:
bin/jruby ~/Path/To/quick_start.rb
If it works, you can go and properly install and integrate JRuby into your system: http://kenai.com/projects/jruby/pages/Home
(Go to: "JRuby Basics" > "Getting Started")
There is also: "JRuby Basics" > "Walkthroughs and Tutorials"
COMMON MISTAKES:
Eventually, I gave up JRuby for Ruby Enterprise Edition. http://www.rubyenterpriseedition.com/
|
After this comment to a my question, im thinking if is better using 1 database with X schemas or viceversa. My situation: im developing a web-app where, when people do register, i create (actually) a database (no, its not a social network: everyone must have access to his own data and never see the data of the other user). Thats the way i used for the previus verison of my application (that is still running on mysql): throught the plesk api, for every registration i do:
Now, i'll need to do the same with posrtgresql (the project is getting mature and mysql.. dont fulfill all the needes) I need to have all the databases/schemas backups indipendent: pg_dump works perfectly in both ways, the same for the users that can be configured to access just 1 schema or 1 database. So, assuming you are more experienced potsgres users than me, what do you think is the best solution for my situation, and why? There will be performance differences using $x db instead of $x schemas? And what solution will be better to mantein in future (reliability)? Every help and suggestion is really appreciated. Edit: i almost forgot: all of my databases/schemas will allways have the same structure! Edit2: For the backups issue (using pg_dump), is maybe better using 1 db and many schemas, dumping all the schemas at once: recovering will be quite simple loading the main dump in a dev machine and then dump and restore just the schema needed: there is 1 additional step, but dumping all the schema seem faster then dumpin them one by one. p.s: sorry if i forgot some 'W' char in the text, my keyboard suffer that button ;) |
||||||||
|
 1   |
A PostgreSQL "schema" is roughly the same as a MySQL "database". Having many databases on a PostgreSQL installation can get problematic; having many schemas will work with no trouble. So you definitely want to go with one database and multiple schemas within that database. |
||||||||
|
0  |
I would say, go with multiple databases AND multiple schemas :) Schemas in postgres are a lot like packages in Oracle, in case you are familiar with those. Databases are meant to differentiate between entire sets of data, while schemas are more like data entities. For instance, you could have one database for an entire application with the schemas "UserManagement", "LongTermStorage" and so on. "UserManagement" would then contain the "User" table, as well as all stored procedures, triggers, sequences etc. that are needed for the user management. Databases are entire programs, schemas are components. |
||
|
|
0 |
A number of schemas should be more lightweight than a number of databases, although I cannot find a reference which confirms this. But if you really want to keep things very separate (instead of refactoring the web application so that a "costomer" column is added to your tables), you may still want to use separate databases: I assert that you can more easily make restores of a particular customer's database this way -- without disturbing the other customers. |
||
|
0 |
Definitely, i'll go for the 1-db-many-schemas approach. This let me to dump all the database but restore just 1 very easly, in many ways:
Otherwise, googlin around iv'se seen that there is no auto-procedure to duplicate a schema (using one as a template), but many suggest this way:
I've written 2 rows in python to do that, hope thay can help someone (in-2-seconds-written-code, dont use it in production): #Take the ne shcema name from the second cmd arguments (the first is the filename) newSchema = sys.argv[1] #Temp folder for the dumps #Settings db_pass = 'db_pass'
#Rename schema with the new name #Dump it os.system(command) pgConnect.query("ALTER SCHEMA " + str(newSchema) + " RENAME TO " + schema_as_template) #Restore the previus dump to create the new schema os.system(restore) os.remove(dumpFile) pgConnect.close() |
http://explain.depesz.com/
Copy'n'paste your query plan, it will highligth all the 'bad steps'.
Dont know how does this works withour knowing the database structure, data-types and indexes, but.. its not bad
CREATE TABLE main (...);
CREATE TABLE child () INHERITS (main);
ALTER TABLE child ADD PRIMARY KEY(id)
CREATE INDEX $index_name ON child USING btree (field)
ALTER TABLE child ADD FOREIGN KEY ($field) REFERENCES other_table($field) ON DELETE SET NULL
This tutorial is about howto installing PostgreSQL on Debian Server.
apt-get update
apt-get install pgsql
Example plpgsql
su postgres
createlang plpgsql template1
exit
We need to edit file pg_hba.conf to change authentification method for accessing PostgreSQL database.
cp /etc/postgresql/pg_hba.conf /etc/postgresql/pg_hba.confbak
vi /etc/postgresql/pg_hba.conf
Find this section
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
# Database administrative login by UNIX sockets
local all postgres ident sameuser
#
# All other connections by UNIX sockets
local all all ident sameuser
#
# All IPv4 connections from localhost
host all all 127.0.0.1 255.255.255.255 ident sameuser
#
# All IPv6 localhost connections
host all all ::1 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff ident sameuser
host all all ::ffff:127.0.0.1/128 ident sameuser
#
# reject all other connection attempts
host all all 0.0.0.0 0.0.0.0 reject
Edit that section to
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
# Database administrative login by UNIX sockets
local all postgres ident sameuser
#
# All other connections by UNIX sockets
local all all password
#
# All IPv4 connections from localhost
host all all 127.0.0.1 255.255.255.255 password
#
# All IPv6 localhost connections
host all all ::1 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff password
host all all ::ffff:127.0.0.1/128 password
#
# reject all other connection attempts
host all all 0.0.0.0 0.0.0.0 reject
/etc/init.d/postgresql restart
Example wordpress
su postgres
createdb -T template1 wordpress
exit
Example: User supriyadisw with password cak3p
su postgres
createuser supriyadisw -P
Enter password for new user: cak3p [enter]
Enter it again: cak3p [enter]
Shall the new user be allowed to create databases? (y/n) y [enter]
Shall the new user be allowed to create more new users? (y/n) n [enter]
CREATE USER
exit
pgsql -U supriyadisw wordpress [enter]
Password: cak3p [enter]
Good Luck 
I really like that clsql will create database tables for me using create-view-from-class. (For some reason I haven't gotten drop-view-from-class to work--it just returns "No Value" and leaves the table intact--but that doesn't really bother, since it's easy enough to drop tables.
I do, however, wish that there were an updated-view-from-class command that I could run when I added a slot to a class. Dropping the table myself, then creating it again certainly works, but then I lose my data, which is rarely what I'm going for. Usually, I don't care much, because I can just log into phpPgAdmin and update the table manually. In the past, it hasn't been too hard for me to figure out how to do so. I'm capable of converting hyphens to underscores, and it was always obvious which type to make the table, integer would be integer, float a double precision, string a character varying. I just had a symbol type slot in my object though, and I wasn't sure what that would be.
Annoying as it was, I deleted the table and used create-view-from class.
The answer is: clsql converts the symbol type to a "character varying", just as it does with strings. So now I know, and I won't have to go through that again.
This latest chapter in the saga of me trying to getting Weblocks working on my Ubuntu slice took me quite a few hours to figure out. After configuring my .clsql-test.config file to run just the PostgreSQL tests and running (asdf:oos 'asdf:test-op 'clsql), I kept getting this error:
debugger invoked on a SIMPLE-ERROR in thread #<THREAD "hunchentoot-worker-2" RUNNING {10035C3B11}>:
Couldn't load foreign library "libpq". (searched CLSQL-SYS:*FOREIGN-LIBRARY-SEARCH-PATHS*)
Okay, so probably my terminology exposes how I am not nearly fluent in darcs, but am translating it to svn in my head. Darcs has tags, patches, and changesets, but not versions, and it has get instead of checkout.
So far, I've been doing Weblocks development on my Macbook, but I wanted to be able to show what we've been working on to other people, so I decided to set it up on the Slicehost slice I've been paying for and not using for months now. Mostly, installing everything on Ubuntu was much easier than it had been on OS X, party because I'd done it before, but more because I didn't have to recompile SBCL. I was basically able to apt-get install everything I needed: sbcl, postgresql, cvs, svn, git, mercurial, darcs, and possibly some things I'm forgetting. I got clbuild from darcs and did ./clbuild install weblocks prevalence, just as I had done in the past. I updated my clsql to the clsql-fluid branch, and installed bordeaux-threads. I got an error saying I needed usocket, and I installed that. Then, I got this error:
; SB-INT:SIMPLE-READER-PACKAGE-ERROR at 437 (line 14, column 28)
on #<SB-SYS:FD-STREAM for "file /home/smee/clbuild/source/weblocks/src/
debug-mode.lisp" {100437C231}>:
; package "HUNCHENTOOT-MP" not found
; compilation aborted after 0:00:00
WARNING:
COMPILE-FILE warned while performing #<COMPILE-OP NIL {1002CF3021}>
on
#<CL-SOURCE-FILE "debug-mode" {10029E26C1}>.
debugger invoked on a ASDF:COMPILE-FAILED in thread #<THREAD "initial
thread" RUNNING {100266B2E1}>:
erred while invoking #<COMPILE-OP NIL {1002CF3021}> on
#<CL-SOURCE-FILE "debug-mode" {10029E26C1}>
basically and didn't know what to do, so I asked the ever-helpful Weblocks google group, and was informed that the current stable version of Weblocks didn't yet work with the latest version of Hunchentoot, which had been just updated, but that it should work if I downgraded my Hunchentoot installation. This is where, as usual, I ran into some problems.
It took me a while to actually figure out how to get an old version from a darcs repository. Here's an example of one of the less helpful bits of darcs documentation I encountered:
How do I go back to an older version of my repository?
Short answer: darcs get --to-match
Long answer: What exactly do you mean by go back to an older version?
I went here and saw this:
To reproduce the state of a repository `R' as at tag `t', use the command `darcs get -tag t R'. The command `darcs show tags' lists all tags in the current repository.
but got confused when running "darcs show tags" revealed that there were no tags in this repository. The same page also said:
If you want to get a specific version of a repository, you have a few options. You can either use the --tag, --to-patch or --to-match options, or you can use the --context=FILENAME option, which specifies a file containing a context generated with darcs changes --context.
which seemed like it was getting me closer, but which I still got wrong, because I was stuck on the idea of tags. This page was the one that finally made sense to me:
Here's what did end up working:
And that did it! Weblocks is now working on my slice! As usual with me, the way darcs works seems pretty clear from the documentation after the fact.
