Comment 1 Matt Heitz 2011-08-23 16:39:45 EDT

*** Bug 346425 has been marked as a duplicate of this bug. ***

Comment 2 Zhi Zhu 2011-08-24 04:25:01 EDT

Tim:
  When I downloaded the attached document, I cannot open it. When I checked its attribute, the size of file is 162 bytes, perhaps a wrong file was uploaded, please check, thanks

Comment 3 Tony Chen 2011-08-24 05:11:04 EDT

So do I. Tim, could you attach another version.

Comment 4 Tim Wilson 2011-08-24 08:59:09 EDT

Created attachment 202081 [details]
EGL SQL Access Specification version 0.1.0

Apparently the first attachment is corrupted

Comment 5 Tim Wilson 2011-08-24 14:12:51 EDT

Created attachment 202104 [details]
EGL SQL Access Specification version 0.1.1

Removal of the Confidential footer
Addition of the PREPARE statement
minor changes here and there

Comment 6 Zhi Zhu 2011-08-29 01:44:46 EDT

In the scenario:
   C.	ADD a Customer Record to the CUSTOMER table
     myCust Customer {
	id = 12345,
	name = “Fred Flinstone”
	…
     }

    // Assume the default mapping of the Customer record
    // to the CUSTOMER table
    add myCust to ds; 

   Can I assume the type of Customer Record is basic record? If so, how do we define the mapping between Customer Record and CUSTOMER table when defining Customer Record?

Comment 7 Tim Wilson 2011-08-29 15:08:38 EDT

(In reply to comment #6)
> In the scenario:
>    C.    ADD a Customer Record to the CUSTOMER table
>      myCust Customer {
>     id = 12345,
>     name = “Fred Flinstone”
>     …
>      }
> 
>     // Assume the default mapping of the Customer record
>     // to the CUSTOMER table
>     add myCust to ds; 
> 
>    Can I assume the type of Customer Record is basic record? If so, how do we
> define the mapping between Customer Record and CUSTOMER table when defining
> Customer Record?

There is a default mapping for any Entity (any instantiable type with fields).  See section on default mapping.  For the default to work in most statements the @Id annotation must be applied to some field to mark it as the primary key.  If the field names are not exactly the column names the @Column annotation is also necessary.

The updated spec has more information about this.

Comment 8 Tim Wilson 2011-08-29 15:20:15 EDT

Created attachment 202356 [details]
EGL SQL Access Specification version 0.2.0

Updates with more complete specification along with additional information about Entity Associations and their usage.

Note that Entity Associations will not be done as part of I13

Comment 9 Tim Wilson 2011-09-13 15:50:23 EDT

Created attachment 203294 [details]
EGL SQL Access Specification version 0.3.0

Updated doc with additions to validation and the EXECUTE statement

Comment 10 Zhi Zhu 2011-09-13 23:05:25 EDT

I have two questions for SQL part:
 1. In RBD, we use 'KeyItem' to identify a primary key column in record definition, in EDT, from my understanding,we use 'Id' annotation to do the same thing, correct?
 2. In Column annotation, we have the below definition:
    record Column type Annotation {
	targets=[FieldMbr]
    }
	name string;
	insertable boolean = true;
	updateable boolean = true;
	table string;
   end

  then how do we identify whether a column is nullable or not?

Comment 11 Zhi Zhu 2011-09-16 04:17:19 EDT

Inconsistent places in EGL SQL Access Specification version 0.3.0

Page 9 contains the below example
  // Declare an SQLResultSet variable
  // Note that one cannot directly create an SQLResultSet
  // using the NEW operator
  rs SQLResultSet;

  // OPEN a result set using the same SQLDataSource
  // This query happens to get all CUSTOMERs
  open rs from ds with #sql{
	SELECT * 
     FROM CUSTOMER
  };

and Page 28, definition of OPEN Statement, its syntax:
   Get_statement ::=
	open sql_statement_expr [ from  data_source_expr  ] [ for_clause]
        [using_clause] [with_clause | for_clause];


In Open Statement definition, open's operand should be 'sql_statement_expr', but in the example, the 'rs' is type of SQLResultSet, not compatible with each other.

Comment 12 Joseph Vincens 2011-09-22 14:26:27 EDT

The following actions, annotations, types, and stereoType are supported for 0.7.0
Open, Prepare, Get, ForEach, Add, Delete, Replace, Execute, SQLDataSource, SQLResultSet, SQLStatement
@Id, @Transient, @Table, @Column, @GeneratedValue
Entity

The following annotations are planned for a future release:
@ManyToMany, @ManyToOne, @OneToMany, @OneToOne, @SecondaryTables

Comment 13 Yu Hao 2011-09-27 03:21:06 EDT

I have a question for 'open', in p9 of the spec. there is a scenario:
rs SQLResultSet;

// OPEN a result set using the same SQLDataSource
// This query happens to get all CUSTOMERs
open rs from ds with #sql{
	SELECT * 
FROM CUSTOMER
};
but in p29, the Syntax for 'open' is :
open sql_statement_expr [ from  data_source_expr  ] [ for_clause]
 [using_clause] [with_clause | for_clause];

Which one is correct or both of them are supported?

Currently I tried it in EDT.

"open rs from ds with sql_statement" is supported
"open sql_statement from ds" is NOT supported

Comment 14 Joseph Vincens 2011-10-05 08:04:12 EDT

Created attachment 204586 [details]
Access Specification version 0.3.1

Updated SQLDataSource and SQLException. 
Added SQLWarning.

See 359774.

Comment 15 Will Smythe 2011-11-08 13:19:50 EST

Joe - if/when you have an updated version of the spec, can you post it?

Comment 16 Will Smythe 2011-11-13 02:16:53 EST

@GeneratedValue is a nice addition (avoids custom SQL for ADD on tables that have auto-generated fields), but developers need the ability to access the ID(s) generated when a new record is added to the SQL data source. The "add" statement correctly avoids trying to insert a value for @generatedvalue fields, but this statement should either return (somehow) any generated keys OR - even better - set them directly in the record that was the source record of the action.

Example record:

record Customer type Entity { @table { name = "customer" } }
  id string{@id, @generatedvalue};
  name string?
end

Simple add code (works correctly):

a Customer { name = "My Company Name" };
add a to ds;

Database table after add:

| ID   | Name                     |
| 331  | My Company Name          |

Note record "a" still has 0 set as its ID

a.name = "Updated company name";

replace a to ds; /// PROBLEM: no easy way to update this record since it doesn't have the correct ID set on it (probably still set to 0). Need for it to get set to

Comment 17 Will Smythe 2011-11-13 02:18:46 EST

I think it is logical for the ADD statement to update the values of any @generatedvalue fields in a record that was added. This way the record will have the correct (generated) value set for its ID after the ADD statement returns.

Btw, see JDBC reference: http://download.oracle.com/javase/1.4.2/docs/api/java/sql/Statement.html#getGeneratedKeys()

Comment 18 Will Smythe 2011-11-14 10:37:57 EST

I moved the request for 'access to generated IDs' to a new bug: Bug 363710

Comment 19 Will Smythe 2012-02-11 09:32:40 EST

The ability to populate a "primitive" array via the GET statement would be really nice. For example:

   tracks string [  ];       
   get tracks from ds with #sql{
	select distinct(track) from track_access };

This is currently not supported (the error message is "The action target must either be a data expression or a list of expressions that map to a column in a database").

Populating a simple primitive value is supported, however:

   track string;
   get track from ds with #sql{
	select track from track_access where id = 3 };

So, it would seem the array example should work as well. The current solution is not pretty:

        results SQLResultSet?;

        open results from ds with #sql{
			select
				distinct(track)
			from
				track_access;			

        tracks string [ ];
        track String;
        forEach (track from results)
            tracks.appendElement(track);
        end

Comment 20 Matt Heitz 2013-01-03 14:43:27 EST

Setting the target milestone to Future for bugs that won't be addressed in 0.8.2.