Defining a Schema
Scala classes are mapped to tables via instances of
org.squeryl.Table[T],
that are grouped in a org.squeryl.Schema singleton.
Columns attributes
Columns and group of columns can have attributes declared via the
on/declare syntax. Inside the example schema defined above, we could
influence the DDL generation with the following declarations :
Of course, not all combinations of column attributes make sense, the
valid combinations are :
| |
non numeric column |
numeric column |
column group |
| indexed |
Y |
Y |
Y |
| unique |
Y |
Y |
Y |
| autoIncremented |
N |
Y |
N |
| defaultsTo |
Y |
Y |
N |
- “Note*: the uniqueness of KeyedEntity[].id columns is not
overridable, it always gets declared as ‘primaryKey’
Schema generation (DDL)
Schema.create will connect to the database and create all tables,
constraints, indexes, etc. While this is usefull for development phases,
when a system has been in production long enough it is often more
convenient to generate the schema, and evolve it manually.
Use Schema.printDdl to print your schema :
Mapping fields to Columns
Squeryl applies the principle of Convention over Configuration :
The class to table and field to column correspondence is determined
by name equivalence. It is possible to override a field’s column name
with the org.squeryl.annotations.Column annotations and the class’s
table name table with the
org.squeryl.Schema.table[T](tableName:String)
method. as illustrated in the previous example.
The Column annotation can also be used to redefine default length
for String/varchar columns (and also for other types although it
should rarely be necessary).
Nullable columns are mapped with Option[] fields
The default (and strongly recommended) way of mapping nullable columns
to fields is with the Option[] type. If you use Squeryl to create
(or generate) your schema, all fields have a not null constraint,
and Option[] fields are nullable.
- Important : If a class has an Option[] field, it becomes
mandatory
to implement a zero argument constructor that initializes Option[]
fields
with Some() instances (like the Book class in the example above).
Failing to do so will cause an exception to be thrown
when the table will be instantiated. The reason for this is that
type erasures
imposed by the JVM prevents from reflecting on the Option[] type
parameter.
This constraint could be relaxed in a future version by a compiler
plugin
that would tell Squeryl the erased type information.
Correspondance of field types to database column types
| Java/JDBC |
Oracle |
PostgreSql |
DB2 |
MySql |
H2 |
MS SQL |
Derby |
| int |
number |
integer |
int |
int |
int |
int |
integer |
| long |
number |
bigint |
bigint |
bigint |
bigint |
bigint |
bigint |
| float |
float |
real |
real |
float |
real |
real |
real |
| double |
real |
double precision |
double |
double |
double |
float |
double |
| BigDecimal |
decimal |
numeric |
decimal |
decimal |
decimal |
decimal |
decimal |
| String |
varchar2(x) |
varchar(x) |
varchar(x) |
varchar(x) |
varchar(x) |
varchar(x) |
varchar(x) |
| Date |
date |
date |
date |
date |
date |
date |
date |
| Timestamp |
date |
timestamp |
timestamp |
datetime |
timestamp |
datetime |
timestamp |
| byte[] |
blob |
bytea |
blob |
blob |
binary |
varbinary |
blob(1M) |
| boolean |
number(1) |
boolean |
char(1) |
boolean |
boolean |
bit |
char(1) |
| UUID |
char(36) |
uuid |
char(36) |
char(36) |
uuid |
char(36) |
char(36) |
Enumerations
Enumerations are persisted by ‘int’ columns
- Explicitely specifying the index values in Enumerations is
strongly recomended
- Classes with an Enumeration field require a zero arg constructor
that gives a default value to all enumeration fields
Choosing between primitive or custom types
You will have to decide if your table objects will be mapped with
primitive (Int. Long, Date, String etc.) or custom types. It’s a
question of tradeoffs :
Primitive types
The main motivations for using primitive types are for performance and
simplicity.
If a query returns N rows of objects with M fields primitive types
will cause the the garbage collector to handle of N objects, while
same
query using custom types will cause the creation of N * M objects.
To use primitive types, simply import org.squeryl.PrimitiveTypeMode._
in the scope where database objects and queries are defined :
that’s all there is to it.
- important : in PrimitiveTypes mode there can be ambiguities
between numeric operators
When using org.squeryl.PrimitiveTypeMode, the compiler will treat an
expression like the
one in the next example as a Boolean. The .~ function is needed to tell
the compiler that the
left side is a node of TypedExpressionNode[Int] which will cause the
whole expression to be a
LogicalBoolean which is what the where clause takes :
It is also needed in the following case :
This is required when using PrimitiveType mode. With custom types
there is no ambiguity, since custom types are not (AnyVal) numerics.
- If you are using primitive types, you should use the following operators
- div, times, plus, minus instead of /. *, +, -
Custom types
One motivation for using custom wrapper types is to allow fields
to carry meta data along with validation, as in the next example.
Custom field types must inherit one of the subtypes of CustomType in the
package
org.squeryl.customtypes, and import the
org.squeryl.customtypes.CustomTypesMode._
into the scope where statements are defined.