sql.e (htmlized)

Warning: this is a htmlized version!
The original is across this link.
#######
#
# E-scripts on SQL.
#
# Note 1: use the eev command (defined in eev.el) and the
# ee alias (in my .zshrc) to execute parts of this file.
# Executing this file as a whole makes no sense.
#
# Note 2: be VERY careful and make sure you understand what
# you're doing.
#
# Note 3: If you use a shell other than zsh things like |&
# and the for loops may not work.
#
# Note 4: I always run as root.
#
# Note 5: some parts are too old and don't work anymore. Some
# never worked.
#
# Note 6: the definitions for the find-xxxfile commands are on my
# .emacs.
#
# Note 7: if you see a strange command check my .zshrc -- it may
# be defined there as a function or an alias.
#
# Note 8: the sections without dates are always older than the
# sections with dates.
#
# This file is at <http://angg.twu.net/a/e/sql.e>
#           or at <http://angg.twu.net/e/sql.e.html>.
#        See also <http://angg.twu.net/emacs.html>,
#                 <http://angg.twu.net/a/.emacs[.html]>,
#                 <http://angg.twu.net/a/.zshrc[.html]>,
#                 <http://angg.twu.net/escripts.html>,
#             and <http://angg.twu.net/>.
#
#######




######
#
# create a user "postgres" (for createdb, etc)
#
######

# (find-fline "/etc/passwd" "postgres")
# postgres::31:32:postgres:/home/postgres:/usr/bin/zsh
# postgres0:*:31:32:postgres:/var/postgres:/bin/sh

cd /home
mkdir postgres
cd /home/postgres
ln -s ../root/.zshrc .
ln -s ../root/.emacs .
ln -s ../root/eev.el .
ln -s ../root/bin    .
cd /home
chown -Rv postgres: postgres




######
#
# postgres: installation
#
######

Pgrep m/postgres/i  |& tee ~/o
# (find-fline "~/o")

apti libpgsql libpgtcl postgresql postgresql-doc postgresql-dev

# Allow TCP/IP connections (for pgaccess):
#
# (find-fline "/etc/rc2.d/S20postgresql" "Usage:")
# (find-fline "/etc/init.d/postgresql")
#
# (find-fline "/etc/postgresql/postmaster.init" "PGALLOWTCPIP")
# PGALLOWTCPIP=yes

/etc/init.d/postgresql restart

# (find-fline "/usr/doc/libpgsql/")
# (find-fline "/usr/doc/libpgtcl/")
# (find-fline "/usr/doc/postgresql-dev/")
# (find-fline "/usr/doc/postgresql-doc/")
# (find-fline "/usr/doc/postgresql/")
# (find-vldifile "libpgsql.list")
# (find-vldifile "libpgtcl.list")
# (find-vldifile "postgresql-dev.list")
# (find-vldifile "postgresql-doc.list")
# (find-vldifile "postgresql.list")

# (find-fline "/usr/doc/postgresql-doc/")


# (find-fline "/usr/X11R6/bin/pgaccess")
# (find-fline "/etc/postgresql/")




#####
#
# libpgtcl
#
#####

# (find-vldifile "libpgtcl.list")
# (find-fline "/usr/doc/libpgtcl/")
# (find-fline "/usr/lib/postgresql/lib/pgaccess.tcl")

# (find-vldifile "tcl8.0-dev.list")
# (find-fline "/usr/doc/tcl8.0-dev/")

pdsc /big/slinks2/dists/slink/main/source/misc/postgresql_6.3.2-15.dsc
cd /usr/src/postgresql-6.3.2/

# (find-pgfile "")
# (find-pgfile "src/interfaces/libpgtcl/")
# (find-pgfile "src/interfaces/libpgtcl/pgtcl.c" "Tcl_CreateCommand")
# (find-pgfile "src/interfaces/libpgtcl/pgtclCmds.c")
# (find-pgfile "src/bin/pgaccess/")
# (find-pgfile "src/bin/pgtclsh/")
# (find-pgfile "debian/" "tcl")
# (find-pgfile "")

# (code-c-d "pgtcl" "/usr/src/postgresql-6.3.2/src/interfaces/libpgtcl/")
eecd pgtcl
etags *.[ch]
# (find-pgtcltag "pg_connect")


lynx /usr/doc/libpgtcl/html/


# What commands?
# Run eet on this:

set list0 [info commands]
load libpgtcl.so
foreach p [info commands] {
  # puts "[lsearch $list0 $p] $p"
  if {[lsearch $list0 $p]==-1} {
    lappend list1 $p
  }
}
foreach p [lsort $list1] { puts $p }

# pg_conndefaults
# pg_connect
# pg_disconnect
# pg_exec
# pg_listen
# pg_lo_close
# pg_lo_creat
# pg_lo_export
# pg_lo_import
# pg_lo_lseek
# pg_lo_open
# pg_lo_read
# pg_lo_tell
# pg_lo_unlink
# pg_lo_write
# pg_notifies
# pg_result
# pg_select










alias pgaccessp='su -s /usr/X11R6/bin/pgaccess postgres'

function pgdo () { su -s $1 postgres -- $*[2,-1] }
pgdo `w createdb` edrx


load libpgtcl.so
set conn [pg_connect cds -host localhost -port 5432]
puts $conn
set res [pg_exec $conn {select t0.artist, t0.title from "basic" t0  order by t0.artist}]

select t0.artist, t0.title from "basic" t0  order by t0.artist





# (find-pgfile "src/bin/pgtclsh/")
# (find-pgfile "doc/src/sgml/libpgtcl.sgml")
# (find-pgfile "src/pl/tcl/")

proc getDBs { {host "localhost"} {port "5432"} } {
    # datnames is the list to be result
    set conn [pg_connect template1 -host $host -port $port]
    set res [pg_exec $conn "SELECT datname FROM pg_database ORDER BY datname"]
    set ntups [pg_result $res -numTuples]
    for {set i 0} {$i < $ntups} {incr i} {
	lappend datnames [pg_result $res -getTuple $i]
    }
    pg_disconnect $conn
    return $datnames
}



pdsc /big/slinks2/dists/slink/main/source/misc/postgresql_6.3.2-15.dsc
cd /usr/src/postgresql-6.3.2/

# (find-pgfile "src/bin/")
# (find-pgfile "src/bin/createdb/createdb.sh")
# (find-pgfile "debian/pg_wrapper.c")
# (find-fline "/usr/lib/postgresql/bin/")
# (eeman "pg_wrapper")

# (find-fline "/usr/bin/createdb")
# (find-fline "/usr/bin/createuser")

# (find-fline "/usr/lib/postgresql/bin/createdb")
# (find-fline "/usr/lib/postgresql/bin/createuser")



# (find-fline "/usr/doc/HOWTO/PostgreSQL-HOWTO.gz")
# (find-vldifile "www-pgsql.list")

# (find-fline "/usr/doc/postgresql-doc/")
lynx	     /usr/doc/postgresql-doc/postgres/index.html
edrxnetscape /usr/doc/postgresql-doc/postgres/index.html

# (find-fline "/usr/doc/libpgtcl/README")
# (find-fline "/usr/X11R6/bin/pgaccess")
# (find-fline "/usr/lib/postgresql/lib/pgaccess.tcl")
# (find-fline "/usr/lib/postgresql/lib/pgaccess.tcl" "Open database")
# (find-fline "/usr/lib/postgresql/lib/pgaccess.tcl" "open_database")
# (find-fline "/usr/lib/postgresql/lib/pgaccess.tcl" "libpgtcl.so")


# (find-fline "/var/postgres/")
# (find-fline "/etc/postgresql/postgresql.env")
# (format "%x" 5432)

cd /proc/net/
mycat * | l +/1538

psql edrx







#
# Velhos:
#

debsource /debian/main/source/misc postgresql 6.3.2 -8

# (code-c-d "pg" "/usr/src/postgresql-6.3.2/")
# (find-pgfile "src/bin/pgaccess/")
# (find-pgfile "src/bin/pgtclsh/")
# (find-pgfile "src/interfaces/libpgtcl/")
# (find-pgfile "src/pl/tcl/")
# (find-pgfile "debian/")

cd /usr/src/postgresql-6.3.2/
debian/rules binary |& tee odrb

# (find-hamm "libpgtcl")

# (find-fline "/etc/init.d/postgresql")
# (find-fline "/etc/postgresql/postmaster.init")
# Adicionar essas linhas no final:
#   PGALLOWTCPIP=yes
#   PGPORT=5432

# (find-fline "/var/postgres/data/")
# (find-fline "/etc/services" "postgres")
# (format "%x" 5432)

#
# Ele não estava se atachando na porta 5432.
# Solução porca:
#

ps | awk '/work/{print$2}'
pidof workbone

l < /proc/$(pidof postmaster)/cmdline

/etc/init.d/postgresql stop
export PGALLOWTCPIP=yes
/etc/init.d/postgresql start
netstat -veao

telnet 0 5432




wish -f /usr/lib/postgresql/lib/pgaccess.tcl
wish -f /usr/src/postgresql-6.3.2/src/bin/pgaccess/pgaccess.tcl

cd /usr/src/postgresql-6.3.2/

cd /usr/src/postgresql-6.3.2/src/bin/pgaccess/
agrep ^proc pgaccess.tcl

# (find-pgfile "doc/src/sgml/")
# (find-pgfile "doc/src/sgml/install.sgml" "create database foo")
cd /usr/src/postgresql-6.3.2/doc/src/sgml/

cd /usr/src/postgresql-6.3.2/doc/
for i in *.ps; do gv $i; done

gv /usr/src/postgresql-6.3.2/doc/user.ps
gv /usr/src/postgresql-6.3.2/doc/tutorial.ps

psql
psql -p 5432

# (find-pgfile "")





#####
#
# pltcl
# (Not working! 99sep04)
#
#####

# (find-pgfile "src/pl/tcl/")

groff -Tascii /usr/src/postgresql-6.3.2/src/pl/tcl/pltcl_guide.nr > ~/o
# (find-fline "~/o")

cd /usr/src/postgresql-6.3.2/src/pl/tcl/
chmod 777 test
cd test
su -s runtest postgres
# (find-pgfile "src/pl/tcl/test/")





########
#
# mysql
#
########

cd /cdrom/
cp -diPv $(<<'---'
debian/contrib/source/misc/xmysql_1.8-1.diff.gz
debian/contrib/source/misc/xmysql_1.8-1.dsc
debian/contrib/source/misc/xmysql_1.8.orig.tar.gz
debian/non-free/source/devel/mysql-manual_0.95-1-1.dsc
debian/non-free/source/devel/mysql-manual_0.95-1-1.tar.gz
debian/non-free/source/devel/mysql_3.21.31-1.diff.gz
debian/non-free/source/devel/mysql_3.21.31-1.dsc
debian/non-free/source/devel/mysql_3.21.31.orig.tar.gz
) /

debsource /debian/non-free/source/devel mysql 3.21.31 -1
debsource /debian/non-free/source/devel mysql-manual 0.95-1 -1

cd /usr/src/mysql-manual-0.95-1/


pdsc /debian/non-free/source/devel/mysql_3.21.31-1.dsc
cd /usr/src/mysql-3.21.31/
debian/rules binary	|& tee odrb

# Não funciona.






######
#
# postgres docs
#
######

pdsc /big/slinks2/dists/slink/main/source/misc/postgresql_6.3.2-15.dsc

# (find-pgfile "")
# (find-pgfile "doc/")
# (find-pgfile "doc/src/sgml/")
# (find-pgfile "doc/src/sgml/ref/")




######
#
# Some SQL commands taken from a program that my father wrote
#
######



# (find-fline "/smilc/edrx/")

SELECT	qryConjStok.ID,
	qryConjStok.IdCompt,
	qryConjStok.Expr1,
	qryConjStok.NumAtivo,
	qryConjStok.AlugDia,
	tblComptStok.ID,
	tblComptStok.IdCompt,
	[Descricao] & " " & [NomeFabric] & " " & [Modelo] AS Expr2,
	tblComptStok.NumSerie,
	tblComptStok.NumAtivo,
	tblComptCad.AlugDia
	FROM
(((qryConjStok INNER JOIN
   tblComptStok ON
     qryConjStok.ConjPai = tblComptStok.ComptFilho
  ) INNER JOIN
  tblComptCad ON
    tblComptStok.IdCompt = tblComptCad.IdComponte
 ) INNER JOIN
 tblComptTipoCad ON
   tblComptCad.RefTipo = tblComptTipoCad.RefTipo
) INNER JOIN
tblFabricCad ON
  tblComptCad.NumFabric = tblFabricCad.NumFabric;


Databases envolvidos:
  tblComptCad
  tblComptStok
  tblComptTipoCad
  tblFabricCad


----------


SELECT	tblComptStok.ID,
	tblComptStok.IdCompt,
	[Descricao] & " " & [Modelo] & " " AS Expr1,
	tblComptCad.AlugDia,
	tblComptStok.NumAtivo,
	tblComptStok.ConjPai
	FROM
(tblComptStok INNER JOIN
 tblComptCad  ON
   tblComptStok.IdCompt = tblComptCad.IdComponte
) INNER JOIN
tblComptTipoCad ON
  tblComptCad.RefTipo = tblComptTipoCad.RefTipo
    WHERE (((tblComptStok.NumSerie) Is Null));


Databases envolvidos:
  tblComptCad
  tblComptStok
  tblComptTipoCad



-----


C:\TecnoLoc\LocRefrs.mdb    Quinta-feira, 19 de Agosto de 1999
Consulta: qryMontagem                                Página: 1

SQL

SELECT	qryConjStok.ID,
	qryConjStok.IdCompt,
	qryConjStok.Expr1,
	qryConjStok.NumAtivo,
	qryConjStok.AlugDia,
	tblComptStok.ID,
	tblComptStok.IdCompt,
	[Descricao] & " " & [NomeFabric] & " " & [Modelo] AS Expr2,
	tblComptStok.NumSerie,
	tblComptStok.NumAtivo,
	tblComptCad.AlugDia
	FROM
(((qryConjStok INNER JOIN
   tblComptStok ON
    qryConjStok.ConjPai = tblComptStok.ComptFilho
  ) INNER JOIN
  tblComptCad ON
   tblComptStok.IdCompt = tblComptCad.IdComponte
 ) INNER JOIN
 tblComptTipoCad ON
 tblComptCad.RefTipo = tblComptTipoCad.RefTipo
) INNER JOIN
tblFabricCad ON
tblComptCad.NumFabric = tblFabricCad.NumFabric;


Columns

  Name                   Type              Size

  qryConjStok.ID         Número (Longo)       4
  qryConjStok.IdCompt    Número (Longo)       4

  Expr1                  Texto                0
  qryConjStok.NumAtivo   Número (Longo)       4
  qryConjStok.AlugDia    Número (Simples)     4

  tblComptStok.ID        Número (Longo)       4
  tblComptStok.IdCompt   Número (Longo)       4
  Expr2                  Texto                0

  NumSerie               Texto               10
  tblComptStok.NumAtivo  Número (Longo)       4
  tblComptCad.AlugDia    Número (Simples)     4







postgresql-doc

# (find-vldifile "postgresql-doc.list")
# (find-fline "/usr/doc/postgresql-doc/")
# (find-fline "/usr/doc/postgresql-doc/libpgsql/libpq/examples/")

cd /usr/doc/postgresql-doc/postgres/