Database

Index Unicon

Unicon databases

Unicon supports a couple of different database types.

• DBM
• ODBC

These are optional dependencies during compiler build. DBM requires a DBM engine, GDBM for instance, and ODBC requires a Open Data Base Connection layer. A third option is using the native table datatype (or other aggregate data structure) to handle memory based database management

Tables

table data can be an easy way to handle ad-hoc database problems.

The IPL entries for xencode and xdecode can then be used to add persistence.

#
# table-db, a small database in a table
#
link xcodes

procedure main()
    db := table()
    db["key"] := "valuable data"
    db["alternate"] := ["more Unicon treasure", "in a list"]

    dbf := open("table-db.dat", "w")
    xencode(db, dbf)
    close(dbf)

    dbf := open("table-db.dat")
    newdb := xdecode(dbf)
    close(dbf)

    write("newdb[\"key\"] is ", newdb["key"])
end

newdb["key"] is valuable data

Note

The xencode and xdecode procedures come in a couple of different flavours; as link xcode or link xcodes. xcodes makes handling record definitions a little easier, and provides for file and procedure structures that may not be present in the decoding program. Try and use xcodes for most developments.

DBM

When supported, open mode “d” (and mode “dr” for read-only) will open DBM database resources. Once opened, the resource is treated as a persistent table datatype. datum := dbm[s] will retrieve data for key s, and dbm[s] := "some data" will attempt to insert or update the DBM information on disk. insert, delete and fetch built-in functions can also be used. Update and insert are blocked for mode “dr” read-only data stores.

#
# dbm, database sample
#
procedure main()
    db := open("dbm.dat", "d")
    db["key"] := "valuable data"
    db["alternate"] := "more Unicon treasure"
    close(db)

    dbf := open("dbm.dat", "dr")
    write(dbf["key"])
    write(dbf["alternate"])
    close(dbf)
end

valuable data
more Unicon treasure

The example above will create dbm.dat, the user visible data file, and also some internal files; dbm.dat.dir and dbm.dat.pag.

DBM information is converted to string form when written to disk. Unlike memory tables, 1 and "1" are the same key in DBM mode. Use xencode (and xdecode) if you need to differentiate between string and other datatypes for DBM keys and values.

ODBC

Unicon includes SQL features, when built with ODBC support.

Documented in Unicon Technical Report, UTR1, http://unicon.org/utr/utr1/utr1.htm by Federico Balbi and Clint.

Requirements

• ODBC, unixodbc package (for instance)
• SQLiteODBC, libsqliteodbc package (or other ODBC driver)
• datasource definintion, ~/.odbc.ini

#
# odbc.icn, ODBC trial
#
link ximage

procedure main()
    # mode 'o' open, ODBC SQL
    db := open("unicon", "o", "", "") | stop("no odbc for \"unicon\"")

    # Display some ODBC driver information
    write("dbproduct:")     
    write(ximage(dbproduct(db)))

    write("\ndbdriver:")     
    write(ximage(dbdriver(db)))

    write("\ndblimits:")     
    write(ximage(dblimits(db)))

    # create a sample table
    sql(db, "drop table if exists contacts")
    sql(db, "create table contacts (id integer primary key, name, phone)")

    # insert some records, with and without transaction control
    sql(db, "insert into contacts (name, phone) _
             values ('brian', '613-555-1212')")

    sql(db, "BEGIN; insert into contacts (name, phone) _
             values ('jafar', '615-555-1213'); _
             COMMIT")

    sql(db, "insert into contacts (name, phone) _
             values ('brian', '615-555-1214')")

    sql(db, "BEGIN; insert into contacts (name, phone) _
             values ('clint', '615-555-1215'); _
             COMMIT")

    sql(db, "insert into contacts (name, phone) _
             values ('nico', '615-555-1216')")

    # display ODBC view of table schema
    write("\ndbtables:")     
    every write(ximage(dbtables(db)))

    tables := dbtables(db)
    write("\ndbcolumns.", tables[1].name, ":")     
    every write(ximage(dbcolumns(db, tables[1].name)))

    # query a few phone numbers
    write("\nPhone numbers for brian:")
    sql(db, "select id, phone from contacts where name='brian'")
    while rec := fetch(db) do write(rec.id, ": ", rec.phone)

    write("\nPhone numbers for jafar:")
    sql(db, "select id, phone from contacts where name='jafar'")
    while rec := fetch(db) do write(rec.id, ": ", rec.phone)

    # query with an intrinsic function
    write("\nRecord count:")
    sql(db, "select count(*) from contacts")
    rec := fetch(db)
    write(ximage(rec))
    write(rec["count(*)"])

    # query with an intrinsic function given alias
    writes("\nRecord count (as counter): ")
    sql(db, "select count(*) as counter from contacts")
    rec := fetch(db)
    write(rec.counter)

    # close off the resource
    close(db)
end

With a unicon DSN (data source name) configuration of:

[unicon]
Description=Unicon ODBC sample
Driver=SQLite3
Database=/home/btiffin/lang/unicon/databases/unicon.db
Timeout=2000

Giving:

dbproduct:
R__1 := ()
   R__1.name := "SQLite"
   R__1.ver := "3.9.2"

dbdriver:
R__1 := ()
   R__1.name := "sqlite3odbc.so"
   R__1.ver := "0.9992"
   R__1.odbcver := "03.00"
   R__1.connections := 0
   R__1.statements := ""
   R__1.dsn := "unicon"

dblimits:
R__1 := ()
   R__1.maxbinlitlen := 0
   R__1.maxcharlitlen := 0
   R__1.maxcolnamelen := 255
   R__1.maxgroupbycols := 0
   R__1.maxorderbycols := 0
   R__1.maxindexcols := 0
   R__1.maxselectcols := 0
   R__1.maxtblcols := 0
   R__1.maxcursnamelen := 255
   R__1.maxindexsize := 0
   R__1.maxownnamelen := 255
   R__1.maxprocnamelen := 0
   R__1.maxqualnamelen := 255
   R__1.maxrowsize := 0
   R__1.maxrowsizelong := "N"
   R__1.maxstmtlen := 16384
   R__1.maxtblnamelen := 255
   R__1.maxselecttbls := 0
   R__1.maxusernamelen := 16

dbtables:
L1 := list(1)
   L1[1] := R__1 := ()
      R__1.qualifier := ""
      R__1.owner := ""
      R__1.name := "contacts"
      R__1.type := ""
      R__1.remarks := ""

dbcolumns.contacts:
L6 := list(3)
   L6[1] := R__1 := ()
      R__1.catalog := ""
      R__1.schema := ""
      R__1.tablename := "contacts"
      R__1.colname := "id"
      R__1.datatype := 4
      R__1.typename := "integer"
      R__1.colsize := 9
      R__1.buflen := 10
      R__1.decdigits := 10
      R__1.numprecradix := 0
      R__1.nullable := 1
      R__1.remarks := ""
   L6[2] := R__2 := ()
      R__2.catalog := ""
      R__2.schema := ""
      R__2.tablename := "contacts"
      R__2.colname := "name"
      R__2.datatype := 12
      R__2.typename := ""
      R__2.colsize := 0
      R__2.buflen := 255
      R__2.decdigits := 10
      R__2.numprecradix := 0
      R__2.nullable := 1
      R__2.remarks := ""
   L6[3] := R__3 := ()
      R__3.catalog := ""
      R__3.schema := ""
      R__3.tablename := "contacts"
      R__3.colname := "phone"
      R__3.datatype := 12
      R__3.typename := ""
      R__3.colsize := 0
      R__3.buflen := 255
      R__3.decdigits := 10
      R__3.numprecradix := 0
      R__3.nullable := 1
      R__3.remarks := ""

Phone numbers for brian:
1: 613-555-1212
3: 615-555-1214

Phone numbers for jafar:
2: 615-555-1213

Record count:
R__1 := ()
   R__1.count(*) := 5
5

Record count (as counter): 5

That same code will work with MariaDB, PostgreSQL, Oracle, or any of the many other ODBC drivers that are available for most operating systems and database engines.

---

Index | Next: Networking | Previous: Graphics