/Library/PostgreSQL/9.0/share/postgresql/contrib/uuid-ossp.sql
CREATE OR REPLACE FUNCTION uuid_generate_v1()
RETURNS uuid
AS '$libdir/uuid-ossp', 'uuid_generate_v1'
VOLATILE STRICT LANGUAGE C;
While PostgreSQL has always been extensible, now users can easily create, load, upgrade, and manage any of dozens of database extensions using the EXTENSION database object. The new site PGXN.org offers a repository for contributing and downloading extensions.To see what extensions are currently installed, try this SQL command:
select * from pg_extension;select * from pg_available_extensions;select pg_available_extensions();CREATE EXTENSION "uuid-ossp";
/Library/PostgreSQL/9.0/share/postgresql/contrib/uuid-ossp.sqlSELECT uuid_generate_v1();
?restartApplicationAnother way is to append:
?debugThat approach displays a JavaScript window on top of your app’s window with buttons for restarting the app, as well as debugging your window's layout, and other features.
<Manager pathname="" />The trick is which "context.xml" to edit. No need to edit the one your Tomcat installation, which would affect all web apps. Instead, copy the "context.xml" from the tomcat/conf folder. Place that copy nested inside your project folder, and edit there:
jar c0mfv META-INF/MANIFEST.MF whatever.jar source_folders_&_filesTo break it down:
Timezones are a presentation-layer problem!
Most of your code shouldn't be dealing with timezones or local time.
…/YourJettyFolder/bin/jetty.sh/Users/basilbourque/jetty-distribution-8.0.0.v20110901/bin/jetty.sh startStarting Jetty: STARTED Jetty Mon Sep 5 16:39:26 PDT 2011Stopping Jetty: OKcheck rather than start or stop.http://localhost:8080/
defaults write com.apple.NetworkBrowser EnableODiskBrowsing -bool truedefaults write com.apple.NetworkBrowser ODSSupported -bool true /Library/PostgreSQL/9.0/data/pg_log
su postgres sudo su - postgres /Library/PostgreSQL/9.0/bin/pg_ctlCopy Path > Unix menu item. sudo su - postgres
/Library/PostgreSQL/9.0/bin/pg_ctl status -D /Library/PostgreSQL/9.0/dataTools > Server Configuration > postgresql.conf. > Library > PostgreSQL > 9.0 > uinstall-postgresql.app. Running that app will remove all of Postgres except for two pieces: sudo dscl . delete /users/postgres "OLD." || myColumnNameVar OLD.first_name (for example) EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO t USING NEW;
CREATE TABLE history_
(
pkey_ uuid NOT NULL DEFAULT uuid_generate_v1mc(), -- The primary key for this table, though no primary key constraint was created (for the sake of performance and conservation). This column and timestamp_ column are the only two columns about this table itself. All other columns are about the inserted/modified/deleted record in some other table.
table_name_ character varying(120) NOT NULL, -- Name of table whose row is being affected (inserted, deleted, or modified).
column_name_ character varying(120) NOT NULL, -- Name of the column in some other table whose row value is being modified. This column's value is empty string if the operation was DELETE.
timestamp_ timestamp with time zone NOT NULL DEFAULT clock_timestamp(), -- The moment this record was created. Using the clock_timestamp() function as a default, to capture the actual moment in time rather than moment when transaction began.
db_user_name_ character varying(120) NOT NULL DEFAULT "current_user"(), -- The name of the Postgres user logged in to this database connection/session.
app_name_ character varying(120) NOT NULL DEFAULT current_setting('application_name'::text), -- The name of the application connected to the database. May also include the version number of app, and the name of the human user authenticated within the app.
old_value_ character varying(120) NOT NULL DEFAULT ''::character varying,
new_value_ character varying(120) NOT NULL DEFAULT ''::character varying,
uuid_ uuid NOT NULL, -- The UUID of the row being affected, the row being inserted, updated, or deleted. Assumes every table whose history is being recorded uses the 'uuid' data type as its primary key.
operation_ character varying(120) NOT NULL, -- What database operation resulted in this trigger running: INSERT, UPDATE, DELETE, or TRUNCATE.
table_oid_ oid NOT NULL, -- The oid of the table whose record is being modified. May be helpful if a table name changes over time.
ordinal_position_of_column_ integer NOT NULL, -- The position of the affected column in its table. Every new column gets a number, incremented by one for each. This may be helpful when analyzing changes across a stretch of time during which a column's name was changed. Apparently columns have no oid, so we are recording this number instead.
transaction_began_ timestamp with time zone NOT NULL DEFAULT transaction_timestamp() -- The time when the current transaction began. Can act like a transaction identifier, to group multiple "history_" rows of the same transaction together. This is not foolproof, as multiple transaction could possibly start in the same split second moment. Assuming the computer's clock has a fine resolution, this chance of a coincidence should be quite miniscule.
)
CREATE TRIGGER XXX_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON XXX_
FOR EACH ROW EXECUTE PROCEDURE make_history_();
CREATE OR REPLACE FUNCTION make_history_()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$BODY$
/* Purpose: Make a history of changes to most fields in the table calling this trigger function.
This kind of history tracking is also known as an "audit trail".
This function works by detecting each change in value for important fields in a certain table.
This trigger function then calls another function to create a row in the "history_" table.
This kind of feature is often called an "audit trail" by software developers. I avoid using that term in this context as a real
audit trail in accounting terms involves more than this simple field change tracking.
*/
/* © 2011 Basil Bourque. This source code may be used freely forever by anyone taking full responsibility for doing so, without warranty.
Thanks so very much to John DeSoi of the pgsql-general@postgresql.org mailing list for pointing me to this crucial code example:
http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers
Before reading that example, my previous efforts led me to conclude a generic history facility written in PL/pgSQL was impossible.
*/
/* We make these assumptions about any table using this function in its trigger:
• Has a primary key named "pkey_" of type uuid.
• Has a field tracking the datetime the record was last modified, named "record_modified_" of type timestamptz.
• Is in the default/current schema.
While it might be nice to rewrite this function to escape these assumptions, I've spent all my energies to get this far.
I welcome feedback from anyone who want to take this further.
*//*For each table on which you want history, create a trigger by executing SQL like this:CREATE TRIGGER XXX_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON XXX_
FOR EACH ROW EXECUTE PROCEDURE make_history_();where XXX is the name of the specific table.*/
/* Notes:
The 'OLD' and 'NEW' variables represent the entire row whose INSERT/UPDATE/DELETE caused this trigger to run.
The 'TG_xxx' variables are special variables created automatically by Postgres for the trigger function.
For example, TG_OP indicates which modification operation is happening: INSERT, UPDATE, or DELETE.
http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
"clock_timestamp()" gets the actual time at the moment of execution. In contrast, most other timestamp
functions return the time when the current transaction began.
For more information, see: http://www.postgresql.org/docs/current/static/functions-datetime.html
The "history_" table also includes a column "transaction_began_" defaulting to "transaction_timestamp()". This timestamp can act
like a transaction identifier, to group multiple "history_" rows of the same transaction together. This is not foolproof, as
multiple transaction could possibly start in the same split second moment. Assuming the computer's clock has a fine resolution,
this chance of a coincidence should be quite miniscule. If someone knows a way to get a true transaction id, please share.
*/
/* History:
2011-04-31 • Published on the general Postgres mailing list.
2011-05-01 • Revised to not ignore the ".record_created_" field.
• Published on my blog at http://crafted-software.blogspot.com/.
*/
DECLARE
ri RECORD; -- About this data type "RECORD": http://www.postgresql.org/docs/current/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS
oldValue TEXT;
newValue TEXT;
isColumnSignificant BOOLEAN;
isValueModified BOOLEAN;
BEGIN
/*RAISE NOTICE E'\n Running function: make_history_ ----------------\n\n Operation: %\n Schema: %\n Table: %\n',
TG_OP,
TG_TABLE_SCHEMA,
TG_TABLE_NAME;*/
IF (TG_OP = 'INSERT') OR (TG_OP = 'UPDATE') THEN
NEW.record_modified_ = clock_timestamp(); -- Record the moment this row is being saved.
FOR ri IN
-- Fetch a ResultSet listing columns defined for this trigger's table.
SELECT ordinal_position, column_name, data_type
FROM information_schema.columns
WHERE
table_schema = quote_ident(TG_TABLE_SCHEMA)
AND table_name = quote_ident(TG_TABLE_NAME)
ORDER BY ordinal_position
LOOP
-- For each column in this trigger's table, copy the OLD & NEW values into respective variables.
-- NEW value
EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO newValue USING NEW;
-- OLD value
IF (TG_OP = 'INSERT') THEN -- If operation is an INSERT, we have no OLD value, so use an empty string.
oldValue := ''::varchar;
ELSE -- Else operation is an UPDATE, so capture the OLD value.
EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO oldValue USING OLD;
END IF;
-- Make noise for debugging.
/*RAISE NOTICE E'\n Column #: %\n Name: %\n Type: %\n Old: %\n New: %\n',
ri.ordinal_position,
ri.column_name,
ri.data_type,
oldValue,
newValue;*/
-- ToDo: Add code to throw an Exception if the primary key value is changing (other than from NULL on an INSERT).
-- ToDo: Add code to ignore columns whose data type does not cast well to TEXT/VARCHAR.
-- Ignore some columns:
-- • Those whose names are marked with a trailing x.
-- • The primary key.
-- • Our timestamp field recording the row's most recent modification.
isColumnSignificant := (position( '_x_' in ri.column_name ) < 1) AND (ri.column_name <> 'pkey_') AND (ri.column_name <> 'record_modified_');
IF isColumnSignificant THEN
isValueModified := oldValue <> newValue; -- If this nthField in the table was modified, make history.
IF isValueModified THEN
/*RAISE NOTICE E'Inserting history_ row for INSERT or UPDATE.\n';*/
INSERT INTO history_ ( operation_, table_oid_, table_name_, uuid_, column_name_, ordinal_position_of_column_, old_value_, new_value_ )
VALUES ( TG_OP, TG_RELID, TG_TABLE_NAME, NEW.pkey_, ri.column_name::VARCHAR, ri.ordinal_position, oldValue::VARCHAR, newValue::VARCHAR );
END IF;
END IF;
END LOOP;
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
/*RAISE NOTICE E'Inserting history_ row for DELETE.\n';*/
-- Similar to INSERT above, but refers to OLD instead of NEW, and passes empty values for last 4 fields.
INSERT INTO history_ ( operation_, table_oid_, table_name_, uuid_, column_name_, ordinal_position_of_column_, old_value_, new_value_ )
VALUES ( TG_OP, TG_RELID, TG_TABLE_NAME, OLD.pkey_, ''::VARCHAR, 0, ''::VARCHAR, ''::VARCHAR );
RETURN OLD;
END IF;
/* Should never reach this point. Branching in code above should always reach a call to RETURN. */
RAISE EXCEPTION 'Unexpectedly reached the bottom of this function without calling RETURN.';
END;
$BODY$;
SELECT ordinal_position, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'YourSchemaNameGoesHere' -- Default is usually 'public'.
AND table_name = 'YourTableNameGoesHere'
ORDER BY ordinal_position;
CREATE OR REPLACE FUNCTION make_history_( varchar, varchar, varchar, varchar, varchar, varchar, text ) RETURNS boolean AS $$
BEGIN
INSERT INTO history_ ( app_user_name_, table_name_, column_name_, affected_uuid_, old_value_, new_value_, notes_, triggered_ )
VALUES ( $1, $2, $3, $4, $5, $6, $7, True );
RETURN True;
END;
LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION make_history_( varchar, varchar, varchar, varchar, varchar, varchar, text ) RETURNS boolean AS $$DECLARE
uuid_arg uuid;
BEGIN
uuid_arg := $4;
INSERT INTO history_ ( app_user_name_, table_name_, column_name_, affected_uuid_, old_value_, new_value_, notes_, triggered_ )
VALUES ( $1, $2, $3, uuid_arg, $5, $6, $7, True );
RETURN True;
END;
LANGUAGE plpgsql; VALUES ( $1, $2, $3, CAST( $4 AS uuid ), $5, $6, $7, True );
sql = "SELECT attname, attnum FROM pg_attribute, pg_class WHERE attrelid = pg_class.oid AND relname = " + DBUtils.escapeAndAddQuotes(tableName) + " AND attnum > 0 ;"DBUtils.escapeAndAddQuotes is unrelated. That is my own method for 3 things:"select * from tablename where 0=1"
// Purpose: Create a table in Postgres, insert a row, and retrieve that row.
// Shows a bug in the Postgres database driver of REAL Studio 2011 Release 1
// where dates ('timestamp with time zone' at least) are nil in the RecordSet.
// © 2011 Basil Bourque. This source code may be used freely by anyone taking full responsibility for doing so.
dim db as PostgreSQLDatabase = new PostgreSQLDatabase
dim sql as String
dim rs as RecordSet
// ---------------- Prepare connection.
db.Host = "127.0.0.1"
db.port = 5432 // 5432 is default port for PostgreSQL.
//db.Encryption = 128
db.UserName = "postgres" // "postgres" is the default admin user name.
db.Password = "YourPasswordGoesHere"
db.DatabaseName = "YourDatabaseNameGoesHere" // Postgres supports multiple databases. Which one do you want? Default is named the same name as user, such as 'postgres'.
// ---------------- Try connecting.
dim status as String
if( db.Connect ) then
status = "Success connecting to db server"
else // Error connecting
status = "Error occurred when connecting to database server. Code: " + str(db.ErrorCode) + " " + db.ErrorMessage
Break
return
end if
// ---------------- Create a table named "bogus_spacetime_".
sql = sql + "DROP TABLE IF EXISTS bogus_spacetime_ ;" + EndOfLine
db.SQLExecute( sql)
if(db.Error) then
status = "Error when running SQLExecute. Code: " + str(db.ErrorCode) + " " + db.ErrorMessage
Break
Return
end if
// Assumes you have enabled the UUID generation feature in PostgreSQL.
// If not, kill or edit 2 lines below ('pkey_' and 'CONSTRAINT').
sql = "CREATE TABLE bogus_spacetime_" + EndOfLine
sql = sql + "(" + EndOfLine
sql = sql + " pkey_ uuid NOT NULL DEFAULT uuid_generate_v1mc()," + EndOfLine
sql = sql + " moment_ timestamp with time zone NOT NULL DEFAULT clock_timestamp()," + EndOfLine
sql = sql + " CONSTRAINT bogus_spacetime_primary_key_ PRIMARY KEY (pkey_)" + EndOfLine
sql = sql + ")" + EndOfLine
sql = sql + "WITH (" + EndOfLine
sql = sql + " OIDS=FALSE" + EndOfLine
sql = sql + ");" + EndOfLine
db.SQLExecute( sql)
if(db.Error) then
status = "Error when running SQLExecute. Code: " + str(db.ErrorCode) + " " + db.ErrorMessage
Break
Return
end if
sql = "ALTER TABLE bogus_spacetime_ OWNER TO postgres;"
db.SQLExecute( sql)
if(db.Error) then
status = "Error when running SQLExecute. Code: " + str(db.ErrorCode) + " " + db.ErrorMessage
Break
Return
end if
// ---------------- Create a record
sql = "INSERT INTO bogus_spacetime_ DEFAULT VALUES;"
db.SQLExecute( sql)
if(db.Error) then
status = "Error when running SQLExecute. Code: " + str(db.ErrorCode) + " " + db.ErrorMessage
Break
Return
end if
// ---------------- Execute a "SELECT *"
sql = "SELECT * FROM bogus_spacetime_;"
rs = db.SQLSelect( sql)
if(db.Error) then
status = "Error when running a SELECT. Code: " + str(db.ErrorCode) + " " + db.ErrorMessage
else // Else normal.
if( rs = nil ) then
status = "ERROR - RecordSet is nil"
else
status = "Record count = " + str( rs.RecordCount ) // Replace with more useful code.
// Access the timestamp value from the 2nd column.
dim f as DatabaseField = rs.IdxField(2) // Despite name, "IdxField" is 1-based.
// BUG = The DatabaseField object contains a nil value rather than a date.
dim d as Date = f.DateValue // Crash.
MsgBox d.SQLDateTime
end if
end if
db.Close
// ---------------- Catch any exception thrown by code above.
Exception
if( db <> nil) then
db.Close
end if
// ---------------- End of code.
sudo su postgresType that into Terminal, hit return, and use your regular Mac admin account password as prompted. The word 'postgres' is the name of the special user created by the Postgres installer. Be very careful – you will be working with all the "safeties" turned off, enabling you to take actions that are normally forbidden to protect you from yourself.
exit