DDL (Data Definition Language) is a subset of SQL used to define structure (metadata) of database tables.

ODB API supports DDL syntax CREATE TABLE

  • to define columns of files that application will write data to.
  • to associate a SQL table with a dataset

Defining metadata of tables

The statement CREATE TABLE can be used to define names and columns of new SQL tables and associate them with a "physical" ODB API file or another dataset, e.g. a RETRIEVE request.

The example below is a Python code but the syntax of CREATE TABLE is the same when passed to appropriate C or Fortran API.

In this example we define metadata of a new SQL table: its columns and their types. Using the ON clause we also associate the table with a file. In order to define bitfield columns we have to first define details of their types with CREATE TYPE statement.

Define columns of a new table and associate it with file name
conn = odb.connect('''
    CREATE TYPE bf AS (f1 bit1, f2 bit2); 
 
    CREATE TABLE foo AS 
    ( x INTEGER, y DOUBLE, v STRING, status bf) 
    ON 'new_api_example.odb';
''')

 

After executing such CREATE TABLE statement we can write data to a table defined with it using INSERT INTO statement.

Insert data
c = conn.cursor()
c.executemany('INSERT INTO foo (x,y,v,status) VALUES (?,?,?,?);', 
                [[1,0.1, '  one   ', 1], 
                 [2,0.2, '  two   ', 2], 
                 [3,0.3, '  three ', 3], 
                 [4,0.4, '  four  ', 4]])

 

Associating SQL tables with datasets

In the previous example of CREATE TABLE we used ON clause to associate a new table with a new file. When reading an existing dataset CREATE TABLE can be used to associate descriptor of the dataset, which currently can be a file name or a RETRIEVE request, with a table name. This table name can be referred to in subsequent SELECT statement. In this case we can skip definition of columns as ODB API data have its metadata embedded in it.

Associate SQL table with a data descriptor
conn = odb.connect(ddl = '''
    CREATE TABLE rt16001
    ON "mars://RETRIEVE,
                DATABASE  = marsod,
                CLASS     = OD,
                TYPE      = MFB,
                STREAM    = OPER,
                EXPVER    = 0001,
                DATE      = 20160830,
                TIME      = 1200,
                REPORTYPE = 16001";
''')
c = conn.cursor()
c.execute('select * from rt16001;')