Recent Changes - Search:

(:shoutbox:)
(:shoutboxend:)

(:toggle show box1 button=1 show="More>>" hide="Hide Options" :)

Name:
Lines to Show:

(:feralsimplecalendar:)

PmWiki

pmwiki.org

edit SideBar

SQL

SELECT

sql SQL.Foo#Bar | while read --sql a b c
do
done

Obviousl SQL.Foo#Bar contains a SELECT statement

INSERT

echo "a:::b:::c" | insert --IFS:::: SQL.Foo#insert
echo "a:::b:::c\nd:::e:::f" | insert SQL.Foo#insert

That's fine and allows multi-record inserts, but how to insert a record with a text field containing a newline?

echo "a:::b\nc:::d" | insert --embeddednewline SQL.Foo#insert ## and this one doesn't allow multi-record inserts

UPDATE

set -s SQLwhere = "abc = 'hello'"
echo "a:::b:::c" | sql SQL.Foo#update

DELETE

set -s SQLwhere = "abc = 'deleteme'"
echo "a:::b:::c" | sql SQL.Foo#delete

Generic options

  • sql/select outputs with a field-separator and record separator set correctly by --sql
  • sql/insert takes stdin with field-sep and rec-sep set by --IFS and --IRS
  • sql/update takes stdin with field-sep and rec-sep set by --IFS and --IRS
  • any SQL statements can have embedded WikiShVars or PVs or PTVs and will fill them in (i.e., ) (also see note below regarding macros)

Security

Security is handled like this:

  1. The page with the script (e.g., Group.ScriptPage) has to have permission to use the given page or section:
Group.ScriptPage: SQL.Foo#insert, SQL.Foo#delete, SQL.Foo#update, SQL.Generic, -SQL.Generic#deleteall
  1. The page containing the SQL statements has to have SQL capability
SQL.Foo: SQL

Possible future extensions:

  • stdin from DATABASE--Group.Page or Group.Page#select would automatically look for a #select
  • stdin from DATABASE--Group.Page#a would look for this:
    • Group.Page#a (presumably a select)
    • Group.Page#select_a (presumably a select)
    • perhaps using or or , whichever is first found
  • stdout to >DATABASE--Group.Page would automatically look for Group.Page#update
    • stdout to >DATABASE--Group.Page#a would use the #a and then #update_a as in the stdin example
    • perhaps using or or , whichever is first found
  • stdout to >>DATABASE--Group.Page (append) would automatically look for Group.Page#update
    • stdout to >>DATABASE--Group.Page#a (append) would use the #a and then #update_a as in the stdin example
  • rm DATABASE--Group.Page would look for Group.Page#delete
    • rm DATABASE--Group.Page#a would do the Group.Page#a and then Group.Page#delete_a
    • perhaps using or or , whichever is found first
  • Presumably any SQL statement would have insertable values/macros, probably like options:
    • SELECT {field1}, {field2} FROM {table} WHERE {field3} LIKE {pattern1}
      • the above would go in Group.Page#select_a
    • {(wikish cat field1=foo field2=bar table=mytable field3=abc pattern1=*foo* DATABASE--Group.Page#a)}
      • the above would be used in the wikish script
    • If the macro is not defined then obviously it is not usable.
    • If defined in the SQL but not in the call then it becomes blank (probably resulting in a syntax error)

SQL statement definition

  • Multiple statements are allowed, separated by a semi-colon
  • The following options are supported, either within a #defaults section on the page or in the specific section:
    • username=xyz
      • from config.php: wikishdb['username']='xyz';
      • default: none (only valid if no username is required)
    • password=abc
      • from config.php: wikishdb['password']='abc';)
      • default: none (only valid if no password is required)
    • database=foo (from config.php: wikishdb['database']='foo';)
      • this sets the filename for sqlite; filename is a synonym for database
      • default: none (always results in an error if not defined)
    • engine=mysql|sqlite|others? (from config.php: wikishdb['engine']='mysql';)
      • default: sqlite
    • fs=|| (from config.php: wikishdb['fs']='||';)
      • default: ::: (triple-colon)
    • rs=\n (from config.php: wikishdb['rs']='\n';)
      • default: \n===\n (return, triple-equals, return)
    • pre_rs=|| (from config.php: wikishdb['pre_rs']='||';)
      • default: empty
    • separators=table
      • shortcut to set fs=||, rs=\n, pre_rs=|| -- output a table

Thus a given section (in SQL.My_table? for this example) might look like this (defining all *necessary* options):

[[#select]]
username=foo
password=mysecret
database=xyz_db
engine=mysql
fs=||
pre_rs=||
SELECT * FROM my_table WHERE id={id};
[[#selectend]]

or, functionally equivalently (but making it easier to use the same defaults in other sections):

[[#defaults]]
username=foo
password=mysecret
database=xyz_db
engine=mysql
[[#defaultsend]]
[[#select]]
fs=||
pre_rs=||
SELECT * FROM my_table WHERE id={id};
[[#selectend]]

Calling this from a script like this:

|| border=1
{(cat DATABASE--SQL.My_table#select)}

Might result in source like this (which would obviously be converted to a table by later rules):

|| border=1
||Sam||Smith||
||Sally||White||
||Fred||Flintstone||
Edit - History - Print - Recent Changes - Search
Page last modified on January 30, 2011, at 10:47 AM