PersistentTable/Open - Maple Help

# Online Help

###### All Products    Maple    MapleSim

Home : Support : Online Help : PersistentTable/Open

PersistentTable

 Open
 create a PersistentTable connection object

 Calling Sequence connection := Open(path, opts)

Parameters

 path - string, path to the file (or empty string for a temp file that is cleaned up after disconnecting, or ":memory:" for in memory) opts - (optional) equations giving the readonly, create, prefix, and style options

Description

 • The Open command creates a new PersistentTable connection object.
 • The path argument is the only required argument for a pre-existing table. (For new tables, you also need to specify the style option.) It is used to specify the file used for the SQLite table backing the object. It is passed to Database[SQLite][Open], so it has the following special behaviors, inherited from that package:
 – If it is the path to a file, then that file is used for the SQLite table backing the object.
 – If it is the empty string, then Maple creates a temporary file to hold the table, which will be removed once the object is disposed. The table does not persist after this.
 – If it is the string ":memory:", then the table is created in memory. It will not persist after the object is disposed.
 • A PersistentTable object has a fixed number of columns with associated types, say k. The first column, or the first n columns for some positive integer n <= k, are taken to make up the primary key of the object. This means that when viewed as a key/value store, the object's keys are all expression sequences of n Maple expressions, and the values are expression sequences of n - k Maple expressions. In particular, every row of the table needs to have a distinct n-tuple of values for the first n columns.

Options

The following options can be used.

 • prefix = alphanumeric string (default: the empty string)
 The prefix option can be used to store several tables in the same file. There is no functionality for these tables to interact, e.g. with foreign key constraints.
 • create = true, false, or force (default: true)
 By default, if a file exists at the specified path, it is used; a new file is created if it does not exist. This behavior is specified explicitly by using the create = true option. If you specify create = false, then an error will be generated if the file does not exist. If you specify create = force, an error will be generated if the file does exist.
 • readonly = true or false (default: false)
 If you specify readonly = true, then the database is opened in read-only mode, meaning no changes can be made to the table. Otherwise, changes can be made.
 You cannot open the same file in read-only mode and regular, non-read-only mode in the same Maple session, even if you use different tables (specified with different prefixes).
 • style = list (default: same as when the table was created)
 The style option defines the layout of the table: the number and type of its columns, whether these columns are stored in compressed form, the number of columns that make up the primary key, and any indices to speed up frequent queries.
 If you are creating a new PersistentTable, specifying the style option is required. If you are accessing a pre-existing PersistentTable object, you may specify the style option if it is identical to the value specified when you created the object.
 Each entry of the list needs to have one of the following forms:
 – name :: type, where type is one of integer, float, boolean, or anything
 This specifies a column for values of the specified type. The column is not stored in compressed form. The types are detailed below.
 – compress(name :: type), where type is anything
 This specifies a column for values of type anything. The column is stored in compressed form. This is useful if most values in the column are very large expressions.
 – primarykey = n, where n is a positive integer
 This specifies that the first n columns form the primary key. The default is 1.
 – 'index'(nm1, nm2, ...), where nm1, nm2, ... is a sequence of one or more column names
 This instructs Maple to create an index on the columns nm1, nm2, ... . The columns must be defined in other list entries. Note that index is a Maple command, so it needs to be quoted to avoid evaluation.

Data types

 • Four different data types are supported:
 – integer
 This represents Maple integers of type integer[8], that is, integers between ${-2}^{63}$ and ${2}^{63}-1$ (inclusive). They are stored as INTEGER values in the SQLite table. If you need to store integers outside this range, you must use data type anything
 – float
 This represents double precision hardware floating-point values, the same type as Maple's HFloats. They are stored as REAL values in the SQLite table. Any arithmetic done for queries using the Get, GetAll, GetKeys, and Count commands involving such columns will be done in the database engine; it will not, for example, respect the Digits setting.
 – boolean
 This represents Maple's boolean constants, true and false. They are stored as 1 and 0, respectively; that is, values of type INTEGER in the SQLite table.
 – anything
 Almost all Maple values can be stored in a column of type anything. The values are converted to the so-called "dot m" format for storage, as generated by the %m specifier for the sprintf command and as described in the Format,m help page. These values are re-converted to regular Maple values when they are retrieved from the table.
 There are a few types of expressions that do not survive the conversion to and from "dot m" format in the exact same way. For example, a module local variable that undergoes this process will still be a module local with the same name, but it cannot be detected as belonging to the same module.
 Because arbitrary Maple values can be used in this format, the database engine cannot do any comparisons involving such values other than for equality. As a consequence, queries using the Get, GetAll, GetKeys, and Count commands involving such columns may only test the column values for equality.

Thread Safety

 • Persistent tables are in general thread safe as of Maple 2021; see the Thread Safety section of the PersistentTable overview page for more details and caveats.
 • For more information on thread safety, see index/threadsafe.

Examples

 > $\mathrm{with}\left(\mathrm{PersistentTable}\right)$
 $\left[{\mathrm{Close}}{,}{\mathrm{Count}}{,}{\mathrm{Get}}{,}{\mathrm{GetAll}}{,}{\mathrm{GetKeys}}{,}{\mathrm{Has}}{,}{\mathrm{MaybeGet}}{,}{\mathrm{Open}}{,}{\mathrm{RawCommand}}{,}{\mathrm{Set}}\right]$ (1)

Select temporary file locations.

 > $\mathrm{path1}≔\mathrm{FileTools}:-\mathrm{JoinPath}\left(\left[\mathrm{FileTools}\left[\mathrm{TemporaryDirectory}\right]\left(\right),"foo.mks"\right]\right)$
 ${\mathrm{path1}}{≔}{"/tmp/mpldoc12/foo.mks"}$ (2)
 > $\mathrm{path2}≔\mathrm{FileTools}:-\mathrm{JoinPath}\left(\left[\mathrm{FileTools}\left[\mathrm{TemporaryDirectory}\right]\left(\right),"bar.mks"\right]\right)$
 ${\mathrm{path2}}{≔}{"/tmp/mpldoc12/bar.mks"}$ (3)

Make sure the files do not exist.

 > $\mathrm{FileTools}\left[\mathrm{Remove}\right]\left(\mathrm{op}\left(\mathrm{select}\left(\mathrm{FileTools}\left[\mathrm{Exists}\right],\left[\mathrm{path1},\mathrm{path2}\right]\right)\right)\right)$

This generates a table with two integer columns (together forming the primary key) and one column for arbitrary values.

 > $\mathrm{store1}≔\mathrm{Open}\left(\mathrm{path1},\mathrm{style}=\left[\mathrm{k1}::'\mathrm{integer}',\mathrm{k2}::'\mathrm{integer}',v::\mathrm{anything},\mathrm{primarykey}=2\right]\right)$
 ${\mathrm{store1}}{≔}{\mathrm{<< 3-column persistent table at /tmp/mpldoc12/foo.mks >>}}$ (4)

To use this as a table, you can use indexing.

 > $\mathrm{store1}\left[3,5\right]≔x+1$
 ${{\mathrm{store1}}}_{{3}{,}{5}}{≔}{x}{+}{1}$ (5)
 > $\mathrm{store1}\left[3,5\right]$
 ${x}{+}{1}$ (6)

This creates a table similar to store1, but with two additional integer columns. In order to be able to search quickly based on the values of the first integer column, we add an index for it.

 > $\mathrm{store2}≔\mathrm{Open}\left(\mathrm{path2},\mathrm{style}=\left[\mathrm{k1}::'\mathrm{integer}',\mathrm{k2}::'\mathrm{integer}',v::\mathrm{anything},\mathrm{i1}::'\mathrm{integer}',\mathrm{i2}::'\mathrm{integer}',\mathrm{primarykey}=2,'\mathrm{index}'\left(\mathrm{i1}\right)\right]\right)$
 ${\mathrm{store2}}{≔}{\mathrm{<< 5-column persistent table at /tmp/mpldoc12/bar.mks >>}}$ (7)
 > $\mathrm{store2}\left[2,3\right]≔x+1,5,12$
 > $\mathrm{store2}\left[5,2\right]≔\mathrm{sin}\left(z\right),6,7$
 > $\mathrm{store2}\left[3,5\right]≔"string",7,3$
 > $\mathrm{GetAll}\left(\mathrm{store2},5<\mathrm{i1}\right)$
 $\left\{\left[{3}{,}{5}{,}{"string"}{,}{7}{,}{3}\right]{,}\left[{5}{,}{2}{,}{\mathrm{sin}}{}\left({z}\right){,}{6}{,}{7}\right]\right\}$ (8)

You can still search based on i2, but for large tables, it will be a bit slower.

 > $\mathrm{GetAll}\left(\mathrm{store2},\mathrm{i2}<5\right)$
 $\left\{\left[{3}{,}{5}{,}{"string"}{,}{7}{,}{3}\right]\right\}$ (9)

If you expect to store relatively large volumes of data in some cells of a particular column, it may make sense to compress that column. This may result in a smaller file size on disk, and may even result in faster operations (if disk access is the bottleneck). In the following example, compression saves about 20% of the file size on disk.

 > $\mathrm{store3}≔\mathrm{Open}\left(\mathrm{path2},\mathrm{prefix}="compressedexample",\mathrm{style}=\left[k::'\mathrm{integer}',\mathrm{compress}\left(v::\mathrm{anything}\right)\right]\right)$
 ${\mathrm{store3}}{≔}{\mathrm{<< 2-column persistent table at /tmp/mpldoc12/bar.mks with prefix compressedexample_ >>}}$ (10)
 > $\mathrm{store3}\left[1\right]≔\mathrm{expand}\left({\left(x+1\right)}^{10000}\right):$
 > $\mathrm{store3}\left[2\right]≔\mathrm{StringTools}:-\mathrm{Random}\left(10000,'\mathrm{alnum}'\right):$
 > $\mathrm{Close}\left(\mathrm{store1}\right)$
 > $\mathrm{Close}\left(\mathrm{store2}\right)$
 > $\mathrm{Close}\left(\mathrm{store3}\right)$
 > $\mathrm{FileTools}:-\mathrm{Remove}\left(\mathrm{path1},\mathrm{path2}\right)$

Compatibility

 • The PersistentTable[Open] command was introduced in Maple 2021.
 • For more information on Maple 2021 changes, see Updates in Maple 2021.

 See Also