[weld-dev] SQL framework proof of concept

[Arbi Sookazian]

Ok, my attempt at rev 6.  introduced orders and customers tables.  compiles
fine.  throws exception here in createTable():

Create<orders> create4 = new Create<orders>(orders.table);
create4.add(orders.id, "varchar(40) not null primary key");
create4.add(orders.customersid, "varchar(40) not null foreign
key(customersid) references customers(id)");
session.prepare(create4).execute();

console output:

create table users (name varchar(40) not null, username varchar(10) not null
primary key, password varchar(10) not null, age smallint not null)
create table permissions (username varchar(10) not null, id varchar(20) not
null primary key)
create table customers (id varchar(40) not null primary key)
create table orders (id varchar(40) not null primary key, customersid
varchar(40) not null foreign key(customersid) references customers(id))
Exception in thread "main" java.lang.RuntimeException:
java.sql.SQLException: Unexpected token: FOREIGN in statement [create table
orders (id varchar(40) not null primary key, customersid varchar(40) not
null foreign]
    at
org.seamframework.sql.ExecutableStatement.execute(ExecutableStatement.java:53)
    at eg.Main.createTable(Main.java:87)
    at eg.Main.main(Main.java:52)
Caused by: java.sql.SQLException: Unexpected token: FOREIGN in statement
[create table orders (id varchar(40) not null primary key, customersid
varchar(40) not null foreign]
    at org.hsqldb.jdbc.Util.throwError(Unknown Source)
    at org.hsqldb.jdbc.jdbcPreparedStatement.execute(Unknown Source)
    at
org.seamframework.sql.ExecutableStatement.execute(ExecutableStatement.java:50)
    ... 2 more

We'll need to be able to handle this in a typesafe manner (I'm talking about
DDL stuff like defining not null and PK/FK, etc.) as well and also specify
rdbms vendor in a props file, etc. b/c I noticed that the syntax for sql
server and oracle are different for defining FK's:

http://www.1keydata.com/sql/sql-foreign-key.html

Also need to be able to handle identity columns for surrogate keys as well
as sequences.

thx.


On Fri, Nov 27, 2009 at 10:25 PM, Arbi Sookazian <asookazian at gmail.com>wrote:

> Looks like there are two tables (users and permissions) but testQuery2()
> and testQuery3() both select only from one table - users.  So now we need a
> small db schema (i.e. data model).  The typical customers and orders would
> suffice to demo FK and potential outer joins.  How can I define customerId
> as a FK on orders table?  Ideally, it would be nice to be able to do all
> this via tooling (i.e. exec a wizard that would generate the code for us).
> But we need to be able to do all this via the API for now...
>
> On Fri, Nov 27, 2009 at 3:16 PM, Gavin King <gavin.king at gmail.com> wrote:
>
>> New rev, with ability to pass SQL as strings.
>>
>> On Fri, Nov 27, 2009 at 4:47 PM, Gavin King <gavin.king at gmail.com> wrote:
>> > Latest, with a new approach to specifying types.
>> >
>>
>>
>>
>> --
>> Gavin King
>> gavin.king at gmail.com
>> http://in.relation.to/Bloggers/Gavin
>> http://hibernate.org
>> http://seamframework.org
>>
>> _______________________________________________
>> weld-dev mailing list
>> weld-dev at lists.jboss.org
>> https://lists.jboss.org/mailman/listinfo/weld-dev
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/weld-dev/attachments/20091127/1256931d/attachment-0001.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: seam-sql6.7z
Type: application/octet-stream
Size: 2500273 bytes
Desc: not available
Url : http://lists.jboss.org/pipermail/weld-dev/attachments/20091127/1256931d/attachment-0001.obj