Category talk:Wren-table
Database support
Wren CLI has no built in database support and, apart from Wren-sql which can currently only be run using a special executable, you need to program the necessary infrastructure from scratch each time one is needed.
I have therefore designed and coded a simple single table database system to address those situations where the power of a multi-table relational database management system is not needed. As creating a generic system in Wren inevitably leads to some inefficiencies, it is best used for smaller data sets.
A Table object represents a sequence of data records which conform to the table's fields. Fields are stored as a list of FieldInfo objects. There must be a fixed number of them whose individual types must be String, Num or Bool. The first field is always the 'key' used to identify a record and must be unique.
A record is simply an ordered list of field values whose size is therefore constant and which are checked against their types each time a record is added or amended. Each of the record's fields must contain a value of the specified type. For empty fields use a default value such as "", 0 or false. Null is not allowed.
Tables must be such that they can be held entirely in memory and can optionally be saved to or loaded from a .csv file. You can automatically resave the table to disk after each alteration by setting 'autosave' to true (it's false by default).
To maintain the integrity of a table, records are always copied when exposed to outside code to ensure any mutations will not affect the records in the table itself. Mutations to the latter can only be made by the table's own methods and in place sorting is not supported because of the difficulty of ensuring that the function used is 'safe' in this respect.
Although Table objects can use methods inherited from the Sequence class, it should be borne in mind that copies are made of each record every time the sequence is iterated. It may therefore be preferable to use the Records property to create a snapshot of the records and then sort, query or otherwise manipulate that using normal Wren code or static methods provided by the Records class to avoid any subsequent copying.
Source code
import "io" for File
import "os" for Platform
/*
A FieldInfo object contains the information needed (name and kind)
to create a field used by a Table object. FieldInfo objects are immutable.
*/
class FieldInfo {
// Private method to check that a field's kind (i.e. its type) is acceptable.
static checkKind_(kind) {
if (![Num, Bool, String].contains(kind)) {
Fiber.abort("Field kind must be Num, Bool or String.")
}
}
// Creates a new FieldInfo object from its name and kind.
construct new(name, kind) {
if (!(name is String)) Fiber.abort("Field name must be a string.")
_name = name
FieldInfo.checkKind_(kind)
_kind = kind
}
// Self explanatory properties.
name { _name }
kind { _kind }
// Returns a list representation of this instance.
toList { [_name, _kind] }
// Returns a string representation of this instance.
toString { "{%(_name), %(_kind)}" }
}
/*
A Table object represents a sequence of data records which conform to the table's fields.
Fields are stored as a list of FieldInfo objects. There must be a fixed number of them
whose individual types must be String, Num or Bool. The first field is always the 'key' used
to identify a record and must be unique.
A record is simply an ordered list of field values whose size is therefore constant and which
are checked against their types each time a record is added or amended. Each of the record's
fields must contain a value of the specified type. For empty fields use a default value such
as "", 0 or false. Null is not allowed.
Tables must be such that they can be held entirely in memory and can optionally be saved to
or loaded from a .csv file. You can automatically resave the table to disk after each
alteration by setting 'autosave' to true (it's false by default).
*/
class Table is Sequence {
// Private helper method which lists the field values of each record in 'records'
// to the terminal. Each field is left (right for Nums) justified within the width
// for its column with fields then being separated by two spaces.
// A title followed by the field names precedes the records list.
//
// If 'null' is passed for 'colWidths', the method will work out the widths needed
// to fully accomodate each field. Otherwise 'colWidths' should specify the width
// to be used for each column in the form w[.p] where 'w' is the overall width and 'p'
// (for Num fields only) is the number of decimal places to be shown.
// If a column width of 0 is passed the corresponding field will be suppressed.
// The maximum number of fields displayed can be limited to the first 'maxFields'.
static list_(fields, records, title, colWidths, maxFields) {
if (!((maxFields is Num) && maxFields.isInteger && maxFields > 0)) {
Fiber.abort("Number of fields to list must be a positive integer.")
}
var fc = maxFields.min(fields.count)
if (colWidths == null) {
colWidths = List.filled(fc, 0)
for (r in records) {
for (i in 0...fc) {
var width = (r[i] is String) ? r[i].count : r[i].toString.count
if (colWidths[i] < width) colWidths[i] = width
}
}
for (i in 0...fc) {
var fnc = fields[i].name.count
if (colWidths[i] < fnc) colWidths[i] = fnc
}
} else if (!((colWidths is List) && colWidths.count == fields.count)) {
Fiber.abort("colWidths must be a list of the same size as fields or must be null.")
} else {
for (w in colWidths) {
if (w < 0) Fiber.abort("Column width cannot be negative.")
}
}
System.print(title)
for (i in 0...fc) {
System.write("%(padRight_(fields[i].name, colWidths[i])) ")
}
System.print()
for (i in 0...fc) {
System.write(("-" * colWidths[i].floor) + " ")
}
System.print()
for (r in records) {
for (i in 0...fc) {
if (fields[i].kind == Num) {
System.write("%(padNum_(r[i], colWidths[i])) ")
} else {
System.write("%(padRight_(r[i], colWidths[i])) ")
}
}
System.print()
}
}
// Private helper method which pads a data item to a given column width
// by adding spaces to its right.
static padRight_(data, colWidth) {
if (!(data is String)) data = data.toString
colWidth = colWidth.floor
var dc = data.count
if (dc < colWidth) {
return data + (" " * (colWidth - dc))
} else if (dc > colWidth) {
return data[0...colWidth]
}
return data
}
// Private helper method which pads a number to a given column width
// and number of decimal places by adding zeros to its right
// and spaces to its left.
static padNum_(data, colWidth) {
data = data.toString
var dps = 0
if (!colWidth.isInteger) {
var ws = colWidth.toString
dps = Num.fromString(ws.split(".")[1])
colWidth = colWidth.floor
if (dps + 2 > colWidth) Fiber.abort("Column width is inconsistent.")
}
var point = data.indexOf(".")
var places = (point == -1) ? 0 : data.count - point - 1
if (places < dps) {
if (places == 0) data = data + "."
data = data + ("0" * (dps - places))
} else if (places > dps) {
data = data[0..-(1 + places - dp)]
}
var dc = data.count
if (dc < colWidth) {
return (" " * (colWidth - dc)) + data
} else if (dc > colWidth) {
return data[0...colWidth]
}
return data
}
// Private helper method which splits a CSV 'line' into a list of one or more quoted
// fields separated by commas, dealing properly with any embedded commas therein.
// Removes leading and trailing quotes from the quoted fields before returning them.
static splitCsv_(line) {
var fields = line.split(",")
var count = 0
var quoted = false
var chars = line.toList
for (i in 0...fields.count) {
var f = fields[i]
var fc = f.count
if (fc > 0) {
count = count + fc
if (!quoted && f[0] == "\"") {
if (f[-1] != "\"") {
quoted = true
chars[count] = "\v"
}
} else if (quoted && f[-1] == "\"") {
quoted = false
} else if (quoted) {
chars[count] = "\v"
}
} else if (quoted) {
chars[count] = "\v"
}
count = count + 1
}
fields = chars.join("").split(",")
for (i in 0...fields.count) fields[i] = fields[i].replace("\v", ",")
for (i in 0...fields.count) {
var f = fields[i]
var fc = f.count
if (fc < 2) continue
if (f[0] == "\"" && f[-1] == "\"") fields[i] = f[1...-1]
}
return fields
}
// Private helper method to check fieldValues are of the right size and type.
checkFieldValues_(fieldValues) {
if (!((fieldValues is List) && fieldValues.count == _fields.count)) {
Fiber.abort("fieldValues must be a list of the same size as fields.")
}
for (i in 0..._fields.count) {
var f = fields[i]
if (!(fieldValues[i] is f.kind)) {
Fiber.abort("Field value for %(f.name) should be a %(f.kind).")
}
}
if (recordIndex(fieldValues[0]) >= 0) {
Fiber.abort("A record with key '%(fieldValues[0])' already exists.")
}
}
// Returns whether or not the file 'wren-table-<name>.csv' already exists on disk.
static fileExists(name) { File.exists("wren-table-" + name + ".csv") }
// Constructs a new Table object from the data in the disk file 'wren-table-<name>.csv'
// located in the current directory.
construct load(name) {
_name = name
_autosave = false
var fileName = "wren-table-" + name + ".csv"
if (!File.exists(fileName)) Fiber.abort("Table file does not exist.")
var lineBreak = Platform.isWindows ? "\r\n" : "\n"
var lines = File.read(fileName).trimEnd().split(lineBreak)
if (lines.count == 0) Fiber.abort("Table file is empty.")
var fi = Table.splitCsv_(lines[0])
var fic = fi.count / 2
_fields = List.filled(fic, null)
for (i in 0...fic) {
var j = 2 * i
var kind = fi[j + 1]
if (kind == "String") {
kind = String
} else if (kind == "Num") {
kind = Num
} else if (kind == "Bool") {
kind = Bool
} else {
Fiber.abort("Table file is using an unsupported type.")
}
_fields[i] = FieldInfo.new(fi[j], kind)
}
_records = []
for (line in lines.skip(1)) {
var record = List.filled(_fields.count, null)
var fieldValues = Table.splitCsv_(line)
if (fieldValues.count != _fields.count) {
Fiber.abort("Table file contains a record of the wrong length.")
}
for (i in 0..._fields.count) {
var kind = _fields[i].kind
var value = fieldValues[i]
if (kind == Num) {
value = Num.fromString(value)
} else if (kind == Bool) {
value = (value == "true")
}
record[i] = value
}
_records.add(record)
}
}
// Constructs a new Table object with a given name from a
// list of either FieldInfo objects or a list of 2 element lists
// consisting of a field name and its kind.
construct new(name, fieldInfos) {
if (!(name is String)) Fiber.abort("Table name must be a string.")
_name = name
if (!((fieldInfos is List) && fieldInfos.count > 0)) {
Fiber.abort("fieldInfos must be a non-empty list.")
}
if (fieldInfos[0].type == FieldInfo) {
_fields = fieldInfos.toList
} else {
_fields = List.filled(fieldInfos.count, null)
for (i in 0..._fields.count) {
_fields[i] = FieldInfo.new(fieldInfos[i][0], fieldInfos[i][1])
}
}
_records = []
_autosave = false
}
// Constructs a new Table object with a given name from a
// list of field names and a corresponding list of field kinds.
construct new(name, fieldNames, fieldKinds) {
if (!(name is String)) Fiber.abort("Table name must be a string.")
_name = name
if (!((fieldNames is List) && fieldNames.count > 0)) {
Fiber.abort("fieldNames must be non-empty list.")
}
var fc = fieldNames.count
if (!((fieldKinds is List) && fieldKinds.count == fc)) {
Fiber.abort("fieldKinds must be a list of the same size as fieldNames.")
}
_fields = List.filled(fc, null)
for (i in 0...fc) {
_fields[i] = FieldInfo.new(fieldNames[i], fieldKinds[i])
}
_records = []
_autosave = false
}
// Self explanatory properties.
name { _name }
autosave { _autosave }
autosave=(a) { _autosave = a }
// Returns a copy of the fields list.
fields { _fields.toList }
// Returns a list of just the field names.
fieldNames { _fields.map { |f| f.name }.toList }
// Returns a list of just the field kinds.
fieldKinds { _fields.map { |f| f.kind }.toList }
// Returns the index of the field called 'fieldName' or -1 if not found.
fieldIndex(fieldName) {
for (i in 0..._fields.count) {
if (_fields[i].name == fieldName) return i
}
return -1
}
// Alias for fieldIndex
[fieldName] { fieldIndex(fieldName) }
// Returns the FieldInfo for the field at index 'i'.
fieldAt(i) { _fields[i] }
// Returns the number of fields.
numFields { _fields.count }
// Returns the number of records.
numRecords { _records.count }
// Returns whether or not there are no records.
isEmpty { _records.count == 0 }
// Creates a deep copy of the records list (i.e. a snapshot thereof) for sorting,
// querying or otherwise manipulating by external code or by static methods in
// the Records class to avoid any subsequent copying.
records {
var rc = _records.count
if (rc == 0) return []
var copy = List.filled(rc, null)
for (i in 0...rc) copy[i] = _records[i].toList
return copy
}
// As 'records' except that the deep copy is first sorted in accordance with the
// function 'fn' before being returned.
sortedRecords(fn) {
if (!((fn is Fn) && fn.arity == 2)) {
Fiber.abort("'fn' must be a function which takes 2 arguments.")
}
return records.sort(fn)
}
// Returns the index of the record with key field 'key' or -1 if not found.
recordIndex(key) {
for (i in 0..._records.count) {
if (_records[i][0] == key) return i
}
return -1
}
// Returns a copy of the record at index 'i'.
recordAt(i) { _records[i].toList }
// Returns the key of the record at index 'i'.
keyAt(i) { _records[i][0] }
// Creates and adds a new record based on the given fieldValues.
add(fieldValues) {
checkFieldValues_(fieldValues)
_records.add(fieldValues.toList)
if (_autosave) save()
}
// Creates and adds multiple new records based on the given fieldValuesList.
addAll(fieldValuesList) {
if (!((fieldValuesList is List) && fieldValuesList.count > 0)) {
Fiber.abort("fieldValuesList must be a non-empty list.")
}
var a = _autosave
if (a) _autosave = false
for (fieldValues in fieldValuesList) add(fieldValues)
if (a) {
_autosave = true
save()
}
}
// Creates and inserts a new record at records[index] based on the given fieldValues.
insert(index, fieldValues) {
if (!((index is Num) && index.isInteger)) {
Fiber.abort("Index must be an integer.")
}
if (index < 0) index = _records.count + index
if (index < 0 || index > _records.count) {
Fiber.abort("Index is out of range.")
}
checkFieldValues_(fieldValues)
_records.insert(index, fieldValues.toList)
if (_autosave) save()
}
// Creates and inserts multiple new records at records[index]
// based on the given fieldValuesList.
insertAll(index, fieldValuesList) {
if (!((fieldValuesList is List) && fieldValuesList.count > 0)) {
Fiber.abort("fieldValuesList must be a non-empty list.")
}
var a = _autosave
if (a) _autosave = false
var ix = (index >= 0) ? index : _records.count + index
for (fieldValues in fieldValuesList) {
insert(ix, fieldValues)
ix = ix + 1
}
if (a) {
_autosave = true
save()
}
}
// Replaces the record with key field 'key' with a new one based on fieldValues
// and returns 'true'. Returns 'false' if 'key' cannot be found.
replace(key, fieldValues) {
var ix = recordIndex(key)
if (ix == -1) return false
checkFieldValues_(fieldValues)
_records[ix] = fieldValues.toList
if (_autosave) save()
return true
}
// Changes the value of 'fieldName' for the record with key field 'key' to 'newValue'
// and returns 'true'. Returns 'false' if 'key' or 'fieldName' cannot be found.
// 'fieldName' can either be the name of a field or its index.
change(key, fieldName, newValue) {
var kix = recordIndex(key)
if (kix == -1) return false
var fix = -1
if (fieldName is Num) {
if (fieldName.isInteger && fieldName >= 0 && fieldName < _fields.count) {
fix = fieldName
}
} else if (fieldName is String) {
fix = fieldIndex(fieldName)
} else {
Fiber.abort("fieldName must be a valid index or field name.")
}
if (fix == -1) return false
var f = _fields[fix]
if (!(newValue is f.kind)) {
Fiber.abort("Field value for %(f.name) should be a %(f.kind).")
}
if (_records[kix][fix] == newValue) return true
if (fix == 0 && recordIndex(newValue) >= 0) {
Fiber.abort("A record with key '%(newValue)' already exists.")
}
_records[kix][fix] = newValue
if (_autosave) save()
return true
}
// Removes the record at records[index] and returns its key.
removeAt(index) {
var removal = _records.removeAt(index)
if (_autosave) save()
return removal[0]
}
// Removes the record with key field 'key' and returns 'true'.
// Returns 'false' if 'key' cannot be found.
remove(key) {
var ix = recordIndex(key)
if (ix == -1) return false
_records.removeAt(ix)
if (_autosave) save()
return true
}
// Removes all records with key fields in the 'keys' list.
// Returns 'true' if all such records were removed or 'false' otherwise.
removeAll(keys) {
if (!((keys is List) && keys.count > 0)) {
Fiber.abort("keys must be a non-empty list.")
}
var a = _autosave
if (a) _autosave = false
var res = true
for (key in keys) {
if (!remove(key)) res = false
}
if (a) {
_autosave = true
save()
}
return res
}
// Swaps the record at index 'i' with the record at index 'j'.
swap(i, j) {
_records.swap(i, j)
if (_autosave) save()
}
// Returns a copy of the record with key field 'key' or 'null' if 'key' cannot be found.
find(key) {
var ix = recordIndex(key)
return (ix >= 0) ? _records[ix].toList : null
}
// Returns the value of field 'fieldName' in the record with key field 'key' or
// 'null' if either 'key' or 'fieldName' cannot be found.
// 'fieldName' can either be the name of a field or its index.
value(key, fieldName) {
var kix = recordIndex(key)
if (kix == -1) return null
var fix = -1
if (fieldName is Num) {
if (fieldName.isInteger && fieldName >= 0 && fieldName < _fields.count) {
fix = fieldName
}
} else if (fieldName is String) {
fix = fieldIndex(fieldName)
} else {
Fiber.abort("fieldName must be a valid index or field name.")
}
if (fix == -1) return null
return _records[kix][fix]
}
// Saves the Table data to a disk file called 'wren-table-<name>.csv'
// located in the current directory in CSV format. If the file already
// exists it is overwritten.
// All data items are enclosed in double quotes and separated by commas.
// Each field, followed by its kind, is stored in the first line.
// The Table's records are then stored in order with each record
// being stored on a separate line.
save() {
var fileName = "wren-table-" + _name + ".csv"
File.create(fileName) { |file|
var line = ""
for (f in _fields) {
line = line + "\"%(f.name)\"" + "," + "\"%(f.kind)\"" + ","
}
var lineBreak = Platform.isWindows ? "\r\n" : "\n"
file.writeBytes(line[0...-1])
file.writeBytes(lineBreak)
for (r in records) {
var s = r.map { |f| "\"%(f)\"" }.join(",")
file.writeBytes(s)
file.writeBytes(lineBreak)
}
}
}
// Deletes the associated disk file located in the current directory.
// Does nothing if it doesn't exist.
delete() {
var fileName = "wren-table-" + _name + ".csv"
if (!File.exists(fileName)) return
File.delete(fileName)
}
// Returns an indepedent copy of this Table named 'newName'.
copy(newName) {
var t = Table.new(newName, fields)
t.addAll(records)
return t
}
// Lists the records of this Table to the terminal with the number of fields
// displayed limited to 'maxFields'.
// A title followed by the field names precedes the records list.
list(title, colWidths, maxFields) { Table.list_(_fields, _records, title, colWidths, maxFields) }
// As above but lists all fields.
list(title, colWidths) { Table.list_(_fields, _records, title, colWidths, _fields.count) }
// As above but uses a default title.
list(colWidths) { list("Records in '%(_name)' table:\n", colWidths) }
// As above but uses default column widths.
list() { list(null) }
// Lists only the fields of this instance.
listFields() {
var fieldInfos = [FieldInfo.new("name", String), FieldInfo.new("kind", String)]
var records = _fields.map { |f| f.toList }.toList
var title = "Fields for %(_name) table:\n"
Table.list_(fieldInfos, records, title, null, 2)
}
// Iterator protocol methods.
iterate(iterator) { _records.iterate(iterator) }
iteratorValue(iterator) { _records.iteratorValue(iterator).toList }
// Returns a string representation of the Table's fields.
toString { "%(_fields)" }
// Returns a string representation of the Table's records.
recordsString { "%(_records)" }
// Returns a string representation of the Table's records, one per line.
recordsByLine { "%(_records.join("\n"))" }
}
/*
Records contains static methods which operate on a list of data records
usually derived from a Table object but can be used more generally.
As records may not be in sorted order, all searchs are performed sequentially.
*/
class Records {
// Returns the first record with value 'value' at index 'valueIndex'
// in the record or 'null' if 'value' cannot be found.
static first(records, value, valueIndex) {
var ix = indexOf(records, value, valueIndex)
return (ix >= 0) ? records[ix] : null
}
// Returns the last record with value 'value' at index 'valueIndex'
// in the record or 'null' if 'value' cannot be found.
static last(records, value, valueIndex) {
var ix = lastIndexOf(records, value, valueIndex)
return (ix >= 0) ? records[ix] : null
}
// Returns the index of the first record with value 'value' at index 'valueIndex'
// in the record or -1 if 'value' cannot be found.
static indexOf(records, value, valueIndex) {
for (i in 0...records.count) {
if (records[i][valueIndex] == value) return i
}
return -1
}
// Returns the index of the last record with value 'value' at index 'valueIndex'
// in the record or -1 if 'value' cannot be found.
static lastIndexOf(records, value, valueIndex) {
for (i in records.count-1..0) {
if (records[i][valueIndex] == value) return i
}
return -1
}
// Convenience versions of the above methods where 'valueIndex' is always zero.
static first(records, value) { first(records, value, 0) }
static last(records, value) { last (records, value, 0) }
static indexOf(records, value) { indexOf(records, value, 0) }
static lastIndexOf(records, value) { lastIndexOf(records, value, 0) }
// Lists 'records' to the terminal with the number of fields
// displayed limited to 'maxFields'.
// A title followed by the field names precedes the records list.
static list(fields, records, title, colWidths, maxFields) {
Table.list_(fields, records, title, colWidths, numFields)
}
// As above but lists all fields.
static list(fields, records, title, colWidths) {
Table.list_(fields, records, title, colWidths, fields.count)
}
// As above but uses default column widths.
static list(fields, records, title) {
Table.list_(fields, records, title, null, fields.count)
}
// Saves the records to a disk file at 'filePath' in CSV format.
// If the file already exists it is overwritten.
// All data items are enclosed in double quotes and separated by commas.
// The records are then stored in order with each record
// being stored on a separate line.
static save(records, filePath) {
File.create(filePath) { |file|
var lineBreak = Platform.isWindows ? "\r\n" : "\n"
for (r in records) {
var s = r.map { |f| "\"%(f)\"" }.join(",")
file.writeBytes(s)
file.writeBytes(lineBreak)
}
}
}
}