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: