Difference between revisions of "EDT:Default SQL Generation"

Revision as of 03:59, 22 December 2011

         Quick Assist Support for Default SQL Generation

  EDT 0.7.0 release provides quick assist support for the following EGL action statements to 
generate default SQL statements:
  1 ADD Statement
  2 DELETE Statement
  3 GET Statement
  4 OPEN Statement
  5 REPLACE Statement

Use examples to show how to use the feature in EDT plugin.

EGL Record Definition:
    record Product{@Table {name="Product"}}
       id bigint{@Id};
      name string;
      price float;
   end


The above is a basic record definition, one must use annotation @Id to specific which record 
field is used as primary key, also can use annotation @Table to specify to which table the record 
is mapped, just as 'Product' definition shows, but this is optional.

record OrderItem type Entity{@table{name = "OrderItem"}}
   ITEM_ID int{@Id};
   NAME string{ @Column { insertable=true } };
   IMAGE string?{ @Column { updateable=true } };
   PRICE decimal(7, 2)?;
   DESCRIPTION string?;
end

The above is an entity record definition that is generated from a table schema.



EGL Variable Definition
  basicRec Product;    
  itemEntity OrderItem;
  rs SQLResultSet?;
  ds SQLDataSource? = new SQLDataSource("jdbc:derby:C:/databases/EGLDerbyR7;create=true");    


ADD Statement

Click anywhere in the EGL statement and press CTRL+1, and press “Add SQL Statement” proposal, 
default SQL statement will be added to the above statement: 
(1) add basicRec to ds; //for basic record 

Change To: 
add basicRec to ds with 
    #sql{ insert into Product (id, name, price) values (?, ?, ?)}; 

(2) add itemEntity to ds; //for entity record

Change To:
 add itemEntity to ds with
			#sql{
				insert into OrderItem
					(ITEM_ID, Name, IMAGE, price, description)
				values
					(?, ?, ?, ?, ?)
			};

DELETE Statement
(3) delete basicRec from ds; // for basic record

Change To:
 delete basicRec from ds with
			#sql{
				delete from Product
				where id = ?
			};

(4) delete itemEntity from ds; //for entity record

Change To:
   delete itemEntity from ds with
			#sql{
				delete from OrderItem
				where ITEM_ID = ?
			};

GET Statement
(5) GET basicRec from ds; //for basic record

Change To:
  GET basicRec from ds using basicRec.id with
		#sql{
		   select id, rtrim(name), price
		   from  Product
		   where id = ?
		};


(6) GET itemEntity from ds; //for entity record

Change To:
  GET itemEntity from ds using itemEntity.ITEM_ID  with
		#sql{
		   select ITEM_ID, rtrim(Name), rtrim(IMAGE), price, rtrim(description)
		   from OrderItem
		   where ITEM_ID = ?
		};


OPEN Statement 
(7) rows OrderItem; open rs from ds for rows; 

Change To:
 open rs from ds using rows.ITEM_ID  with
			#sql{
				select ITEM_ID, rtrim(Name), rtrim(IMAGE), price, rtrim(description)
				from OrderItem
				where ITEM_ID =  ?
			};

REPLACE Statement
(8) replace itemEntity to ds ;

Change To:
 replace itemEntity to ds with
			#sql{
				update OrderItem
				set Name = ?,IMAGE = ?,
				    price = ?,description = ?
				where ITEM_ID = ?
			} ;


Now the feature can only generate default SQL statements for entity record that does not contain 
any entity relationship field.