Warning: this is an htmlized version!
The original is here, and
the conversion rules are here.
#######
#
# E-scripts on SQLite.
#
# 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/sqlite.e>
#           or at <http://angg.twu.net/e/sqlite.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-debs»	(to "sqlite-debs")
# «.pr.sqlitedb»	(to "pr.sqlitedb")
# «.sqlite-upstream»	(to "sqlite-upstream")
# «.music-2022»		(to "music-2022")
# «.bypass-prompt»	(to "bypass-prompt")
# «.eepitch-sqlite»	(to "eepitch-sqlite")
# «.litecli»		(to "litecli")
# «.sqlite3»		(to "sqlite3")
# «.sql-mode»		(to "sql-mode")
# «.lsqlite»		(to "lsqlite")



# (find-es "recutils")

# (find-es "sql" "sqlite")
# (find-zsh "installeddebs | sort | grep sqlite")
# (find-zsh "availabledebs | sort | grep sqlite")
# (find-status   "sqlite")
# (find-vldifile "sqlite.list")
# (find-udfile   "sqlite/")
# (find-status   "sqlite-doc")
# (find-vldifile "sqlite-doc.list")
# (find-udfile   "sqlite-doc/")
# file:///usr/share/doc/sqlite-doc/
# file:///usr/share/doc/sqlite-doc/index.html
# file:///usr/share/doc/sqlite-doc/quickstart.html

apti sqlite sqlite-doc sqlite3 sqlite3-doc

# (find-status   "sqlite")
# (find-vldifile "sqlite.list")
# (find-udfile   "sqlite/")
# (find-status   "sqlite-doc")
# (find-vldifile "sqlite-doc.list")
# (find-udfile   "sqlite-doc/")
# (find-status   "sqlite3")
# (find-vldifile "sqlite3.list")
# (find-udfile   "sqlite3/")
# (find-status   "sqlite3-doc")
# (find-vldifile "sqlite3-doc.list")
# (find-udfile   "sqlite3-doc/")

apti sqlite3-doc



#####
#
# debs for sqlite (Obsolete! Uses sqlite2...)
# 2007dec27
#
#####

# «sqlite-debs»  (to ".sqlite-debs")
# (find-zsh "installeddebs | sort | grep sqlite")
# (find-zsh "availabledebs | sort | grep sqlite")
apti sqlite sqlite-doc

# (find-status   "sqlite3")
# (find-vldifile "sqlite3.list")
# (find-udfile   "sqlite3/")
# (find-status   "sqlite3-doc")
# (find-vldifile "sqlite3-doc.list")
# (find-udfile   "sqlite3-doc/")
# (find-status   "libsqlite3-0")
# (find-vldifile "libsqlite3-0.list")
# (find-udfile   "libsqlite3-0/")
# (find-status   "libsqlite3-tcl")
# (find-vldifile "libsqlite3-tcl.list")
# (find-udfile   "libsqlite3-tcl/")
# (code-c-d "sqlitedoc" "/usr/share/doc/sqlite3-doc/")
# (find-sqlitedocfile "")
# (find-sqlitedocw3m "index.html")
# (find-sqlitedocw3m "lang.html")
# (find-sqlitedocw3m "lang_insert.html")
# (find-sqlitedocsh "cat *.html")
# (find-sqlitedocsh "cat *.html | unhtml")

# A simple test:
# (find-man "1 sqlite3")

* (eepitch-shell)
* (eepitch-kill)
* (eepitch-shell)
sqlite3 /tmp/ex1
create table tbl1 (one varchar(10), two smallint);
insert into tbl1 values ('hello!',  10);
insert into tbl1 values ('goodbye', 20);
.mode columns
.headers on
select * from tbl1;
.help
.databases
.dump tbl1
.exit

# (find-fline "/tmp/")
# (find-fline "/tmp/ex1")




#####
#
# notes on converting the "pr" text database to sqlite
# 2007dec27
#
#####

# «pr.sqlitedb»  (to ".pr.sqlitedb")

* (eepitch-shell)
* (eepitch-kill)
* (eepitch-shell)
rm -v   /tmp/pr.sqlitedb
sqlite3 /tmp/pr.sqlitedb
create table tbl1 (
  title text,
  state text,
  responsible text,
  class text,
  release text,
  arrivaldate text
);

insert into tbl1 (title, state, responsible, class, release, arrivaldate)
  values (
    'aplicar filtro para melhorar visualizacao da velocidade dos servos',
    'open',
    'andre.
luiz@',
    'feature-request',
    'ihmtelm-0-9',
    '2007dez21'
  );

.dump tbl1





#####
#
# sqlite-3.5.7
# 2007may14
#
#####

# «sqlite-upstream»  (to ".sqlite-upstream")
# http://www.sqlite.org/
# http://www.sqlite.org/sqlite-3.5.7.tar.gz
#*
rm -Rv ~/usrc/sqlite-3.5.7/
tar -C ~/usrc/ -xvzf \
  $S/http/www.sqlite.org/sqlite-3.5.7.tar.gz
cd     ~/usrc/sqlite-3.5.7/

#*
# (code-c-d "sqlite" "~/usrc/sqlite-3.5.7/")
# (find-sqlitefile "")





#####
#
# music-2022
# 2022oct09
#
#####

# «music-2022»  (to ".music-2022")

* (eepitch-shell)
* (eepitch-kill)
* (eepitch-shell)
rm -v   /tmp/music.sqlitedb
sqlite3 /tmp/music.sqlitedb
create table albums (
  artist text,
  album  text
  );
.dump albums
insert into albums (artist, album) values("Hugo Largo", "Drum");
insert into albums (artist, album) values("Medicine","Her Highness");
insert into albums (artist, album) values("Pink Industry","Retrospective");
insert into albums (artist, album) values("David Bowie", "Low");
.dump albums
.quit



#####
#
# bypass-prompt
# 2022oct29
#
#####

# «bypass-prompt»  (to ".bypass-prompt")
# https://lists.gnu.org/archive/html/help-gnu-emacs/2022-10/msg00807.html
# (find-efunctiondescr 'sql-sqlite)
# (find-efunction      'sql-sqlite)
# Bypassing the prompts in M-x sql-sqlite

Hi all,

this is - or should be - a very basic question about sql.el...
Let me start by some context.


1. Some context
===============
A person who has never used SQL in her life, but who knows this,

  http://angg.twu.net/eepitch.html

can have a very quick introduction to how to use sqlite - in a very
primitive way - by just running this, by typing <f8> on each line:


 (eepitch-shell)
 (eepitch-kill)
 (eepitch-shell)
rm -v   /tmp/music.sqlitedb
sqlite3 /tmp/music.sqlitedb
create table albums (
  artist text,
  album  text
  );
.dump albums
insert into albums (artist, album) values("Hugo Largo", "Drum");
insert into albums (artist, album) values("Medicine","Her Highness");
insert into albums (artist, album) values("Pink Industry","Retrospective");
insert into albums (artist, album) values("David Bowie", "Low");
.dump albums
.quit


The next step is to show her how to use sql.el. This

  M-x sql-sqlite RET /tmp/music.sqlitedb RET

creates a buffer called "*SQL: SQLite*" in sql-interactive-mode mode.
The comments at the top of sql.el say:

  This file provides a sql-mode and a sql-interactive-mode.  The
  original goals were two simple modes providing syntactic
  highlighting.  The interactive mode had to provide a command-line
  history; the other mode had to provide "send region/buffer to SQL
  interpreter" functions.  "simple" in this context means easy to
  use, easy to maintain and little or no bells and whistles.  This
  has changed somewhat as experience with the mode has accumulated.

It should be possible to have both methods of sending commands to
"*SQL: SQLite*" available at the same time - eepitch-line and
sql-send-{paragraph,region}...


2. The question
===============
How do I write a sexp that does the same as

  M-x sql-sqlite RET /tmp/music.sqlitedb RET

when the SQL buffer doesn't exist, and the same as M-x sql-sqlite RET
when the SQL buffer already exists? I am probably misreading the code
in sql.el and, ahem, mislooking some important things, because all my
attempts to write code that bypasses the prompts have failed...

  Thanks in advance! =)
    Eduardo Ochs
    http://angg.twu.net/eepitch.html#other-targets




#####
#
# eepitch-sqlite
# 2022oct29
#
#####

# «eepitch-sqlite»  (to ".eepitch-sqlite")
# (find-efunctiondescr 'sql-sqlite)
# (find-efunction      'sql-sqlite)
# (find-efunctiondescr 'sql-product-interactive)
# (find-efunction      'sql-product-interactive)

(sql-product-interactive product new-name)
(sql-product-interactive 'sqlite '(4))
(sql-product-interactive 'sqlite 16)

(defun foo (arg) (interactive "P") (insert (format "\n%S" arg)))


4
1
(4)
(16)




#####
#
# litecli
# 2023jan27
#
#####

# «litecli»  (to ".litecli")
# https://github.com/dbcli/litecli
# https://litecli.com/
# (find-fline "~/LOGS/2023jan27.emacs" "<grym> i usually use litecli")
# (find-git-links "https://github.com/dbcli/litecli" "litecli")
# (code-c-d "litecli" "~/usrc/litecli/")
# (find-liteclifile "")
# (find-pip3-links "litecli")




#####
#
# sqlite3
# 2008apr05
#
#####

# «sqlite3»  (to ".sqlite3")
# http://www.sqlite.org/
# (find-zsh "availabledebs | sort | grep sqlite")
# (find-zsh "installeddebs | sort | grep sqlite")
# (find-status   "sqlite3-doc")
# (find-vldifile "sqlite3-doc.list")
# (find-udfile   "sqlite3-doc/")
# (find-status   "sqlite3")
# (find-vldifile "sqlite3.list")
# (find-udfile   "sqlite3/")
# (find-status   "libsqlite3-tcl")
# (find-vldifile "libsqlite3-tcl.list")
# (find-udfile   "libsqlite3-tcl/")
# (code-c-d "sqlitedoc" "/usr/share/doc/sqlite3-doc/")
# (find-sqlitedocfile "")
# (find-sqlitedocsh "cat *.html")
# (find-sqlitedocsh "cat *.html | unhtml")
# (find-sqlitedocgrep "grep -nH -e 'Datatypes In SQLite Version 3' *.html")
# (find-sqlitedocgrep "grep -nH -e 'insert' *.html")
# (find-sqlitedocw3m "index.html")
# (find-sqlitedocw3m "docs.html")
# (find-sqlitedocw3m "lang.html")
# (find-sqlitedocw3m "lang_createtable.html")
# (find-sqlitedocw3m "lang_insert.html")
# (find-sqlitedocw3m "datatype3.html")
# (find-sqlitedocw3m "quickstart.html")
# (find-man "1 sqlite3")
# (find-man "1 sqlite3" "SQL statements")



* (eepitch-shell)
* (eepitch-kill)
* (eepitch-shell)
rm -v   /tmp/music.sqlitedb
sqlite3 /tmp/music.sqlitedb
create table albums (
  stars  real,
  artist text,
  album  text
  );
.dump albums
insert into albums(stars, artist, album) values(20, "The Stooges", "Funhouse");
insert into albums(stars, artist, album) values(16, "Medicine","Her Highness");
insert into albums(stars, artist, album) values(16, "David Bowie", "Low");
.dump albums
.quit



(defun eepitch-music ()
  "Like `eepitch-shell', but runs \"sqlite3 /tmp/music.sqlitedb\"."
  (interactive)
  (eepitch-comint "sqlite: music" "sqlite3 /tmp/music.sqlitedb"))

(defun find-musicsl (sqlstatements &rest rest)
  "Like `find-sh', but runs \"sqlite3 /tmp/music.sqlitedb SQLSTATEMENTS\"."
  (apply 'find-eoutput-reuse
         (format "*music: %s*" sqlstatements)
	 `(insert (find-callprocess00
		   '("sqlite3" "/tmp/music.sqlitedb" ,sqlstatements)))
	 rest))



* (eepitch-music)
* (eepitch-kill)
* (eepitch-music)
.dump
-- (find-musicsl ".dump")
-- (find-musicsl ".dump" "Bowie")



# (find-sqlitedocw3m "quickstart.html")
# (find-es "tcl" "prompts")
# (find-man "3tcl package")
# (find-man "3tcl package" "DESCRIPTION")
# (find-man "3tcl package" "DESCRIPTION" "package require ?-exact? package")
# (find-fline "/usr/lib/sqlite3/pkgIndex.tcl")



* (eepitch-tcl)
* (eepitch-kill)
* (eepitch-tcl)
set tcl_prompt2 {puts -nonewline "> "}
package names
foreach n [lsort [package names]] {puts $n}
package require sqlite3
sqlite3 musicdb /tmp/music.sqlitedb
# musicdb help
proc ins {stars artist album} {
  musicdb eval "insert into albums(stars, artist, album) values($stars, \"$artist\", \"$album\");"
}
ins 20 "The Stooges"            "Funhouse"
ins 16 "Medicine"               "Her Highness"
ins 16 "David Bowie"            "Low"
ins 12 "The Jesus & Mary Chain" "Psychocandy"
ins 12 "Secos & Molhados"       "Secos & Molhados (1973)"
ins  9 "Bauhaus"                "In the Flat Field (vynil)"
ins  9 "Dinosaur Jr."           "Just Like Heaven EP (vynil)"
ins  9 "Patti Smith"            "Horses"
ins  8 "Neil Young"             "Rock in Rio 2001 (bootleg)"
ins  8 "Velvet Underground"     "White Light/White Heat"
ins  7 "Young Marble Giants"    "Colossal Youth"
ins  7 "Hugo Largo"             "Drum"
ins  6 "Linton Kwesi Johnson"   "Dread Beat & Blood"
ins  6 "Rolling Stones"         "Sticky Fingers"

# (find-musicsl ".dump")
# (find-musicsl ".dump" "Bowie")






#####
#
# sql-mode (not working yet - this is just for discussing with xavier)
# 2008apr11
#
#####

# «sql-mode»  (to ".sql-mode")
# (require 'sql)
# (find-efunction 'sql-mode)
# (find-efile "progmodes/sql.el" "SQLi buffer")
# (find-efunction 'sql-help)
# (find-efunction 'sql-sqlite)
# (find-efunction 'sql-interactive-mode)
# (find-efunction 'sql-help)
# (find-fline "/tmp/foo.sql")

#*
cat > /tmp/foo.sql <<'%%%'

-- (sql-sqlite)

create table albums (
  stars  real,
  artist text,
  album  text
  );
.dump albums
insert into albums(stars, artist, album) values(20, "The Stooges", "Funhouse");
insert into albums(stars, artist, album) values(16, "Medicine","Her Highness");
insert into albums(stars, artist, album) values(16, "David Bowie", "Low");
.dump albums
.quit

-- Local Variables:
-- mode:        sql
-- sql-product: SQLite
-- sql-sqlite-options: "/tmp/music.sqlitedb"
-- sql-sqlite-program: "sqlite3"
-- End:
%%%

#*



# Xma's fix (2009feb12):
# (require 'sql)
# (find-evariable 'sql-database)

This is where you should set your SQLite database name (see below)

You can then call (sql-sqlite) where you *must* leave user,
password and the rest as *empty* (at least for sqlite3).

If all is going ok, you should be able to C-x h the buffer and
C-x C-r (send the region).

There is, I think, a bug in sql-interactive-mode since you have
to tell him what is the comint buffer to use via M-x
sql-set-sqli-buffer

# (find-efunction 'sql-set-sqli-buffer)

I just modified your last section like this:

-- Local Variables:
-- mode:        sql
-- sql-product: SQLite
-- sql-sqlite-program: "sqlite3"
-- sql-database: "/tmp/music.sqlitedb"
-- End:




#####
#
# lsqlite
# 2011nov13
#
#####

# «lsqlite»  (to ".lsqlite")

It's working for me in lsqlite3 version 0.8 ( http://lua.sqlite.org/index.cgi/home )

tmp e$ sqlite3 tmp.db
SQLite version 3.7.7 2011-06-23 19:49:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table items (x integer, y, z);
sqlite> insert into items values (1, 2, 3);
sqlite> insert into items values (null, 5, 6);
sqlite> select * from items;
1|2|3
|5|6
sqlite> .exit
tmp e$ lua
Lua 5.1.4  Copyright (C) 1994-2008 Lua.org, PUC-Rio
> require "lsqlite3"
> =sqlite3.version()
3.7.7
> db = sqlite3.open("tmp.db");
> b = 7;
> c = 8;
> stmt = db:prepare("INSERT INTO items (x, y, z) VALUES (?, ?, ?)")
> stmt:bind_values(a, b, c)
> stmt:step()
> stmt:finalize()
> for row in db:rows "select x, y, z from items" do print (row[1], row[2], row[3]) end
1       2       3
nil     5       6
nil     7       8




http://leveldb.googlecode.com/svn/trunk/doc/benchmark.html

http://www.sqlite.org/wal.html

https://www.sqlite.org/whentouse.html

sqlitestudio

https://corecursive.com/066-sqlite-with-richard-hipp/

https://lars.ingebrigtsen.no/2018/08/28/imdb-in-emacs-or-honey-i-made-an-orm/

https://github.com/mhayashi1120/Emacs-esqlite

https://buttondown.email/jaffray/archive/databases-are-not-compilers/ the closure property




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