3c2b924e9bc0813d28535c66c7e46363723c96ab mspeir Wed Jul 15 10:41:34 2026 -0700 changes to autoSql page based on CR, refs #37852 diff --git docs/file-formats/autoSql.md docs/file-formats/autoSql.md index b1769f42615..041fbd5b05d 100644 --- docs/file-formats/autoSql.md +++ docs/file-formats/autoSql.md @@ -1,29 +1,29 @@ --- title: "AutoSql Format Specification" --- AutoSql is a small specification language used throughout the UCSC Genome Browser to describe -the columns of a table or the fields of a annotation file. A single AutoSql file (conventionally +the columns of a table or the fields of an annotation file. A single AutoSql file (conventionally given the `.as` extension) defines the name, type, and a human-readable description of each -field. +field. In particular, they are used to add extra fields to a +[bigBed](/goldenPath/help/bigBed.html) or [bigGenePred](/goldenPath/help/bigGenePred.html) track: +`bedToBigBed` reads the `.as` file (via its `-as=` option) to learn the names and types of the +fields beyond the standard BED columns, and the Genome Browser uses those descriptions to label +values on item detail pages and in filter menus. This page is aimed at track creators and +hub developers make use of autoSql's more advanced features. -Most Genome Browser users encounter AutoSql when adding extra fields to a -[bigBed](/goldenPath/help/bigBed.html) or [bigGenePred](/goldenPath/help/bigGenePred.html) -track. The `bedToBigBed` utility reads an `.as` file (via its `-as=` option) to learn the names -and types of any fields beyond the standard BED columns, and the Genome Browser uses those field -descriptions to label values on item detail pages and in filter menus. ## A simple example Let's start with a basic [BED format](/FAQ/FAQformat.html#format1) example. A 6-column BED record stores a position (chromosome, start, end), a name, a score, and a strand. Its AutoSql specification looks like this: ``` table bed6 "Browser Extensible Data, 6-column variant" ( string chrom; "Reference sequence chromosome or scaffold" uint chromStart; "Start position in chromosome" uint chromEnd; "End position in chromosome" string name; "Name of item" @@ -58,74 +58,75 @@ struct bed6 /* Browser Extensible Data, 6-column variant */ { struct bed6 *next; /* Next in singly linked list. */ char *chrom; /* Reference sequence chromosome or scaffold */ unsigned chromStart; /* Start position in chromosome */ unsigned chromEnd; /* End position in chromosome */ char *name; /* Name of item */ unsigned score; /* Score from 0-1000 */ char strand[2]; /* + or - for strand */ }; ``` Along with the structure, `autoSql` generates functions to load a row from the database, save a structure in tab- or comma-separated form, and free dynamically allocated structures (for -example, `bed6Load`, `bed6TabOut`, and `bed6Free`). This saves writing the many lines of -repetitive field-by-field conversion code that reading and writing such records would otherwise -require. +example, `bed6Load`, `bed6TabOut`, and `bed6Free`). `bed6Load` reads one row from the database +into a `bed6` structure, `bed6TabOut` writes a structure back out as a tab-separated line, and +`bed6Free` releases the memory it used. This saves writing the many lines of repetitive +field-by-field conversion code that reading and writing such records would otherwise require. ## Anatomy of a declaration Every AutoSql object follows the same shape: ``` <declareType> <name> [index] [auto] "<description>" ( <fieldType> <fieldName>; "<field description>" ... ) ``` -- **declareType** — one of `table`, `object`, or `simple`. See [Object types](#object-types). -- **name** — a valid identifier: letters, digits, and underscores, starting with a letter. -- **description** — a double-quoted string describing the object, used as a comment in the +- **declareType**: one of `table`, `object`, or `simple`. See [Object types](#object-types). +- **name**: a valid identifier: letters, digits, and underscores, starting with a letter. +- **description**: a double-quoted string describing the object, used as a comment in the generated SQL and C. -- **fields** — one per line, each with a type, a name, a semicolon, and a double-quoted comment. +- **fields**: one per line, each with a type, a name, a semicolon, and a double-quoted comment. The quoted comments are not optional decoration. They are required, and the Genome Browser surfaces the per-field comments to users, so write them as if an end user will read them. ## Field types AutoSql supports the following basic field types: | Type | Description | | - | - | | `int` | 32-bit signed integer | | `uint` | 32-bit unsigned integer | | `short` | 16-bit signed integer | | `ushort` | 16-bit unsigned integer | | `byte` | 8-bit signed integer | | `ubyte` | 8-bit unsigned integer | | `bigint` | 64-bit integer | | `float` | single-precision IEEE floating point | | `double` | double-precision IEEE floating point | | `char` | 8-bit character (can only be used in an array) | | `string` | variable-length string, up to 255 bytes | -| `lstring` | "long string" — variable-length string up to 2 billion bytes | +| `lstring` | "long string", variable-length string up to 2 billion bytes | | `enum` | enumerated type holding a single symbolic value | | `set` | set type holding multiple symbolic values | In addition, the `simple`, `object`, and `table` types may be used as fields, allowing one object to contain another. See a more complex [example](#a-more-complicated-example) below. ## Arrays An array is declared by placing a size in square brackets between the field type and the field name. The size may be either a fixed number or the name of a previously declared field, which gives a variable-length array: ``` char[2] state; "Fixed-length array of 2 characters" int pointCount; "Number of points" @@ -142,86 +143,96 @@ The `enum` and `set` types map to SQL enum and set columns and provide compact symbolic values. Each symbolic value must be a valid C identifier. For an `enum`, a field holds exactly one of the listed values; for a `set`, a field may hold any combination, represented as a comma-separated string when loaded from text. ``` table symbolCols "example of enum and set symbolic columns" ( int id; "unique id" enum(male, female) sex; "enumerated column" set(cProg,javaProg,pythonProg,awkProg) skills; "set column" ) ``` +In this example, `sex` is an `enum`, so each row is either `male` or `female` and never both. +`skills` is a `set`, so one row can combine several of the listed values, such as +`cProg,pythonProg`. + This is a common pattern for real Genome Browser data. For example, the bigDbSnp format uses an enum to record each variant's class: ``` enum(snv, mnv, ins, del, delins, identity) class; "Variation class/type" ``` `autoSql` generates a C enum for the symbolic values and stores a `set` as an unsigned bit field. ## Indexes and autoincrement If you do not specify any index, `autoSql` assumes the first field is the primary key and indexes it. To control indexing, add one of the following keywords after a field name: -- `primary` — make the field the primary key. -- `unique` — index a field whose values are all unique. -- `index` — index a field that may contain duplicate values. +- `primary`: make the field the primary key. +- `unique`: index a field whose values are all unique. +- `index`: index a field that may contain duplicate values. You can index just the first part of a long field by following the keyword with a character count -in brackets, for example `index[12]`. An integer field can be made auto-incrementing — letting -the database assign a new numeric value to each row — by adding the `auto` keyword after the index -keyword, if any. +in brackets, for example `index[12]`. To make an integer field auto-incrementing, add the `auto` +keyword after the index keyword, if there is one. ``` table bedIndexed "BED-like record with explicit indexes" ( uint id primary auto; "Autoincrementing primary key for this record" string name unique; "Name of item - must be unique" string chrom index; "Reference sequence chromosome or scaffold" uint chromStart index; "Start position in chromosome" uint chromEnd; "End position in chromosome" lstring description index[12]; "Item description - indexing just the first 12 characters" ) ``` +In `bedIndexed` above, `id` is declared `auto`, so MySQL fills in its value on its own, counting +up by one for each new row. You never supply an `id` yourself. + ## Object types AutoSql has three kinds of objects, which differ in what code is generated and in how arrays of them are stored in memory: | Type | Generates SQL? | Array storage in memory | | - | - | - | | `simple` | No | C array | | `object` | No | Singly linked list | | `table` | Yes (SQL + C) | Singly linked list | -- **`simple`** — objects that do not appear in lists. An array of a `simple` object is stored in +- **`simple`**: objects that do not appear in lists. An array of a `simple` object is stored in memory as a C array. (Historically `simple` objects could not contain strings or variable-sized arrays; that restriction has been lifted.) -- **`object`** — like `simple`, but a `next` pointer is automatically inserted as the first field +- **`object`**: like `simple`, but a `next` pointer is automatically inserted as the first field of the generated C structure, and what looks like an array in the `.as` file becomes a singly linked list in memory. -- **`table`** — like `object`, but `autoSql` also generates a SQL table definition. This is the +- **`table`**: like `object`, but `autoSql` also generates a SQL table definition. This is the type used for anything stored in the database. +A C array stores its elements one after another in a single block of memory, so you can reach any +element directly by its position. A singly linked list stores each element separately and chains +them together, so reaching the third element means following the chain from the start. + For example, given `point[3]` as a field, declaring `point` as `simple` stores the three points as a C array, while declaring it as `object` stores them as a linked list. ## A more complicated example Objects can contain other objects, which makes it possible to describe nested structures such as a gene locus built from transcripts, each of which is in turn built from exons: ``` simple exon "A single exon" ( int start; "Start position of exon in chromosome" int end; "End position of exon in chromosome" )