Thread: DB-shell connect

Can an SQL database (PostgreSQL) be connected to shell in a way that if
a directory is changed on shell it will update the database with the
changes?  If so how?
A prompt reply would be appreciated.

Thank in advance,
Greg

>
> Can an SQL database (PostgreSQL) be connected to shell in a way that if
> a directory is changed on shell it will update the database with the
> changes?  If so how?
> A prompt reply would be appreciated.
>
> Thank in advance,
> Greg
>
>
>

That is tough.  It is normally very difficult to trigger 'anything' if
someone changes a directory in their shell.  It would have to be
triggered in the kernel, and out of the kernel up to an SQL process.
Very tough.

Their are Unix tools that monitor the everyone's current directory, but
they are polling tools, not some automatic thing on every directory
change.

It is the chdir() kernel call you want to trigger on.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

-----BEGIN PGP SIGNED MESSAGE-----

> Can an SQL database (PostgreSQL) be connected to shell in a way that if
> a directory is changed on shell it will update the database with the
> changes?  If so how?
> A prompt reply would be appreciated.

On most *nix systems, this feature is called "auditing."  You may not
realize this, Greg, but what you *really* want to do is:
1)  Turn on auditing (if auditing is not available, try new OS).
2)  Write a script which grovels through the audit log periodically
and inserts the appropriate rows in your database.

Actually, there *is* another alternative:
1)  Make everybody's shell /bin/bash (or translate the following)
2)  Create the following alias in /etc/profile
alias cd="\cd $1;psql database -c \"insert into cdtable values (\'$PWD\');\\g\""
3)  Hope your users don't unalias that.

BTW, the alternative offered above is BAD:
1)  Every cd invokes psql == suckful performance.
2)  Can be trivially broken by users.
3)  Will not notice chdir()'s in shell-scripts and executables.

An improvement can be attained by grabbing the source to bash, and
modifying both the init code (attach to database) and the "cd" code
(insert row, COMMIT).  However, this shell should *never* be used by
root (never futz with the root shell) or PostgreSQL superuser
(chicken-and-egg).

- --
=====================================================================
| JAVA must have been developed in the wilds of West Virginia.      |
| After all, why else would it support only single inheritance??    |
=====================================================================
| Finger geek@andrew.cmu.edu for my public key.                     |
=====================================================================

-----BEGIN PGP SIGNATURE-----
Version: 2.6.2

iQBVAwUBNZpaRYdzVnzma+gdAQFASQH+NaiN0JnlDGxd5mP7gjmQ/7mXqS8bvds9
GiYHmTuOZ1gTJeW1zWiz60nc4xHQYz6jTNE/JqLaIBqP44NKHwQiUA==
=gWRI
-----END PGP SIGNATURE-----

>> Can an SQL database (PostgreSQL) be connected to shell in a way that if
>> a directory is changed on shell it will update the database with the
>> changes?  If so how?

I assumed what he really meant was detecting additions or deletions of
files in a particular Unix directory, not trapping chdir's all over
the system...

If instant response isn't too critical, having a daemon process scan
the directory every five minutes or so seems like the least expensive/
intrusive way to do it.

            regards, tom lane