e/sql.e (htmlized)

Warning: this is an htmlized version!
The original is here, and
the conversion rules are here.
#######
#
# 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.
# An introduction to eev can be found here:
#
#   (find-eev-quick-intro)
#   http://angg.twu.net/eev-intros/find-eev-quick-intro.html
#
# 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/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/.emacs[.html]>,
#                 <http://angg.twu.net/.zshrc[.html]>,
#                 <http://angg.twu.net/escripts.html>,
#             and <http://angg.twu.net/>.
#
#######




# «.sqlite»		(to "sqlite")
# «.gdbm»		(to "gdbm")
# «.tokyocabinet»	(to "tokyocabinet")

# (find-es "postgres")


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



#####
#
# 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
# 2000jun20
#
#####

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/")




#####
#
# postgresql
# 2000jun26
#
#####

# (find-status "postgresql")
# (find-vldifile "postgresql.list")
# (find-fline "/usr/doc/postgresql/")

# (find-vldifile "postgresql.preinst")
# (find-vldifile "postgresql.postinst")
# (find-vldifile "postgresql.postinst" "these commands must be run by")
# (find-vldifile "postgresql.prerm")
# (find-vldifile "postgresql.postrm")

# (find-fline "/etc/postgresql/")
# (find-fline "/etc/postgresql/pg_hba.conf")
# (find-fline "/etc/postgresql/pg_ident.conf")
# (find-fline "/etc/postgresql/postgresql.env")
# (find-fline "/etc/postgresql/postmaster.init")

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

. /etc/postgresql/postgresql.env





#####
#
# pgaccess
# 2000jun26
#
#####

# (find-status "pgaccess")
# (find-vldifile "pgaccess.list")
# (find-fline "/usr/doc/pgaccess/")

# (code-c-d "pga" "/usr/lib/postgresql/pgaccess/")
# (find-pgafile "")
# (find-pgafile "lib/help/")




#####
#
# postgresql-client
# 2000jun26
#
#####

# (find-status "postgresql-client")
# (find-vldifile "postgresql-client.list")
# (find-fline "/usr/doc/postgresql-client/")

# (eeman "1 createdb")
# (eeman "1 destroyuser")
# (eeman "1 destroydb")
# (eeman "1 pg_dump")
# (eeman "1 pg_wrapper")
# (eeman "1 psql")
# (eeman "1 createuser")
# (find-fline "/usr/lib/postgresql/bin/createuser")
# (find-fline "/usr/lib/postgresql/bin/createdb")
# (find-fline "/usr/lib/postgresql/bin/destroyuser")
# (find-fline "/usr/lib/postgresql/bin/destroydb")

# (find-fline "/var/lib/postgres/data/")
# (find-fline "/etc/services" "postgres")




#####
#
# postgresql-doc
# 2000jun26
#
#####

# (find-status "postgresql-doc")
# (find-vldifile "postgresql-doc.list")
# (find-fline "/usr/doc/postgresql-doc/")

# (code-c-d "pgdoc" "/usr/doc/postgresql-doc/")
# (find-pgdocfile "")
# (find-pgdocfile "README.passwords")
# (find-pgdocw3 "postgres/postgres.htm")
# (find-pgdocw3 "postgres/install13251.htm" "Playing with Postgres")
# (find-pgdocw3 "postgres/postgres.htm" "Managing a Database")
# (find-fline "/var/lib/postgres/data/base/")
# (find-fline "/var/lib/postgres/data/base/foo/")

#*
cat > $EEG <<'---'
create database foo;
\c foo
create table bar (i int4, c char(16));
\d bar
\q
---
eeg psql template1

#*
cat > $EEG <<'---'
\?
\l
\dS
# bye:
\q
---
PAGER=cat \
  eeg psql template1
#*




#####
#
# pgaccess
# 2000aug21
#
#####

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

# (code-c-d "pgalh" "/usr/lib/postgresql/pgaccess/lib/help/")
# (find-pgalhfile "")
# (find-pgalhfile "create_database.hlp")





#####
#
# libpgtcl
# 2000aug21
#
#####

apti libpgtcl
# (find-status "libpgtcl")
# (find-vldifile "libpgtcl.list")
# (find-fline "/usr/doc/libpgtcl/")

# (find-status "postgresql-doc")
# (find-vldifile "postgresql-doc.list")
# (find-vldifile "postgresql-doc.list" "pgtcl")
# (find-fline "/usr/doc/postgresql-doc/")

dmissingp pgtcl

# (code-c-d "pgdoc" "/usr/share/doc/postgresql-doc/postgres/")
# (find-pgdocw3 "" "pgtcl")
# (find-pgdocfile "" "pgtcl")
# (find-pgdocw3 "pgtcl-pgconndefaults.htm")
# (find-pgdocw3 "pgtcl-pgconnect.htm")
# (find-pgdocw3 "pgtcl-pgdisconnect.htm")
# (find-pgdocw3 "pgtcl-pgexec.htm")
# (find-pgdocw3 "pgtcl-pglisten.htm")
# (find-pgdocw3 "pgtcl-pgloclose.htm")
# (find-pgdocw3 "pgtcl-pglocreat.htm")
# (find-pgdocw3 "pgtcl-pgloexport.htm")
# (find-pgdocw3 "pgtcl-pgloimport.htm")
# (find-pgdocw3 "pgtcl-pglolseek.htm")
# (find-pgdocw3 "pgtcl-pgloopen.htm")
# (find-pgdocw3 "pgtcl-pgloread.htm")
# (find-pgdocw3 "pgtcl-pglotell.htm")
# (find-pgdocw3 "pgtcl-pglounlink.htm")
# (find-pgdocw3 "pgtcl-pglowrite.htm")
# (find-pgdocw3 "pgtcl-pgresult.htm")
# (find-pgdocw3 "pgtcl-pgselect.htm")
# (find-pgdocw3 "pgtcl.htm")
# (find-pgdocw3 "pgtcl18759.htm")
# (find-pgdocw3 "pgtcl18763.htm")

# (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/

#*
expect -c '
  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 }
'
#*

# (find-pgdocw3 "pgtcl18759.htm")
cat > $EEG <<'---'
  load libpgtcl.so
  # set conn [pg_connect template1 -host localhost -port 5432]
  set conn [pg_connect template1]
  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]
  }
  puts $datnames
  pg_disconnect $conn
  exit
---
eeg tclsh

#*





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

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


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

# (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-htetfile "PostgreSQL-HOWTO.txt.gz")
# (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



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

# (find-fline "/var/lib/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 127.0.0.1 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 "")





#####
#
# postgres
# 2000may26
#
#####

# (find-status "libpgtcl")
# (find-status "pgaccess")
# (find-status "postgresql")
# (find-status "postgresql-client")
# (find-status "postgresql-doc")
# (find-status "postgresql-pl")

# (find-fline "/usr/doc/libpgtcl/")
# (find-fline "/usr/doc/pgaccess/")
# (find-fline "/usr/doc/postgresql-client/")
# (find-fline "/usr/doc/postgresql-doc/")
# (find-fline "/usr/doc/postgresql-pl/")
# (find-fline "/usr/doc/postgresql/")

# (find-vldifile "libpgtcl.list")
# (find-vldifile "pgaccess.list")
# (find-vldifile "postgresql-client.list")
# (find-vldifile "postgresql-doc.list")
# (find-vldifile "postgresql-pl.list")
# (find-vldifile "postgresql.list")




#####
#
# sql.el
# 2004apr05
#
#####

# http://www.emacswiki.org/elisp/sql.el




#####
#
# sqlite
# 2007apr22
#
#####

# «sqlite»  (to ".sqlite")
# (find-es "sqlite")




#####
#
# gdbm
# 2013jun06
#
#####

# «gdbm» (to ".gdbm")
# (find-zsh "installeddebs | sort | grep gdbm")
# (find-zsh "availabledebs | sort | grep gdbm")
# http://en.wikipedia.org/wiki/Gdbm



#####
#
# Tokyo Cabinet
#
#####

# «tokyocabinet» (to ".tokyocabinet")
# (find-zsh "installeddebs | sort | grep cabinet")
# (find-zsh "availabledebs | sort | grep cabinet")
# (find-status   "tokyocabinet-doc")
# (find-vldifile "tokyocabinet-doc.list")
# (find-udfile   "tokyocabinet-doc/")




https://xkcd.com/327/ little bobby tables

https://news.ycombinator.com/item?id=28771656 Sqlfluff the SQL Linter for Humans (sqlfluff.com)

https://howqueryengineswork.com/



# (find-status   "postgresql")
# (find-vldifile "postgresql.list")
# (find-udfile   "postgresql/")
# (find-status   "postgresql-client")
# (find-vldifile "postgresql-client.list")
# (find-udfile   "postgresql-client/")
# (find-status   "postgresql-doc-13")
# (find-vldifile "postgresql-doc-13.list")
# (find-udfile   "postgresql-doc-13/")
# (find-udfile   "postgresql-doc-13/html/")
# (find-udfile   "postgresql-doc-13/tutorial/")
# file:///usr/share/doc/postgresql-doc-13/html/app-createuser.html
# file:///usr/share/doc/postgresql-doc-13/html/app-dropuser.html
# (find-file "/usr/share/doc/postgresql-doc-13/html/app-createuser.html")


# (find-man "1 createuser")
# (find-man "1 createuser" "-s" "--superuser")
# (find-man "1 dropuser")

* (eepitch-shell)
* (eepitch-kill)
* (eepitch-shell)
sudo sudo -u postgres dropuser      edrx
sudo sudo -u postgres createuser -s edrx

* (eepitch-shell)
* (eepitch-kill)
* (eepitch-shell)
export PGCLIENTENCODING=UTF8
export PGUSER=edrx
export PGDATABASE=MyDatabaseName
createdb $PGDATABASE

* (eepitch-shell3)

> Jean Louis:
To create super user, you do this:
createuser -s edrx

> Jean Louis:
I just guess that you need to switch to "postgres" user to do that first time.

> Jean Louis:
But once you have done that, you would then setup environment variable in .bashrc, $PGUSER to be edrx

> Jean Louis:
at that point you need not switch to postgres user any more, as you are using edrx as your super user.

> Jean Louis:
Single database can hold all your tables, I rarely need different database, but why not. I do have, like CIA World Facts — that is good, and other geographic stuff.

> Jean Louis:
Set up environment variables:

export PGCLIENTENCODING=UTF8
export PGUSER=edrx
export PGDATABASE=MyDatabaseName

> Jean Louis:
but you setup PGDATABASE after you created it

psql

https://news.ycombinator.com/item?id=41897526 WITH all_numbers AS
https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause
https://news.ycombinator.com/item?id=41992870 INNER JOIN ON vs WHERE clause (2009) (stackoverflow.com)
https://lackofimagination.org/2024/11/writing-composable-sql-using-knex-and-pipelines/
https://news.ycombinator.com/item?id=42265668 Writing Composable SQL Using Knex and Pipelines (lackofimagination.org)
https://notso.boringsql.com/posts/deletes-are-difficult/
https://matt.blwt.io/post/7-databases-in-7-weeks-for-2025/
https://www.depesz.com/2024/12/01/sql-best-practices-dont-compare-count-with-0/
https://news.ycombinator.com/item?id=43713842 Manga Guide To Databases (2004) [pdf] (oberstar.eu.org)
https://news.ycombinator.com/item?id=44260964 Wrong ways to use the databases, when the pendulum swung too far (luu.io) - stored procedures





#  Local Variables:
#  coding:               utf-8-unix
#  End: