1 package ejava.jpa.examples.tuning;
2
3 import javax.persistence.EntityManager;
4
5 import javax.persistence.EntityTransaction;
6
7 import org.slf4j.Logger;
8 import org.slf4j.LoggerFactory;
9 import org.junit.Assert;
10
11 public class MovieFactory {
12 private static final Logger log = LoggerFactory.getLogger(MovieFactory.class);
13 private EntityManager em;
14
15 public MovieFactory setEntityManager(EntityManager em) {
16 this.em = em;
17 return this;
18 }
19
20 public static class SQLStatement {
21 final SQLConstruct sql;
22 final boolean required;
23 public SQLStatement(SQLConstruct sql, boolean required) {
24 this.sql = sql;
25 this.required = required;
26 }
27 }
28
29 public abstract class SQLConstruct {
30 protected String name;
31 protected String create;
32 protected String drop;
33 protected String populate;
34 protected boolean required;
35 public SQLConstruct(String name) { this.name = name; }
36 public SQLConstruct setCreate(String create) { this.create = create; return this; }
37 public SQLConstruct setDrop(String drop) { this.drop = drop; return this; }
38 public SQLConstruct setPopulate(String populate) { this.populate = populate; return this; }
39 public SQLConstruct setRequired(boolean required) { this.required = required; return this; }
40 public String getName() { return name; }
41 public String getCreate() { return create; }
42 public String getDrop() { return drop; }
43 public String getPopulate() { return populate; }
44 public boolean isRequired() { return required; }
45 public abstract boolean exists();
46 }
47 public class SQLIndex extends SQLConstruct {
48 public SQLIndex(String name, String create) {
49 super(name);
50 setCreate(create);
51 setDrop("drop index " + name);
52 }
53 public boolean exists() {
54 return ((Number)em.createNativeQuery(
55 "select count(*) from user_indexes where index_name=?1")
56 .setParameter(1, name.toUpperCase())
57 .getSingleResult()).intValue()==1;
58 }
59 }
60 public class SQLColumn extends SQLConstruct {
61 private String table;
62 private String column;
63 public SQLColumn(String table, String column, String create, String populate) {
64 super(table+"."+column);
65 this.table=table;
66 this.column=column;
67 setCreate(create);
68 setDrop(String.format("alter table %s drop column %s", table, column));
69 setPopulate(populate);
70 }
71 public boolean exists() {
72 return ((Number)em.createNativeQuery(
73 "select count(*) from user_tab_cols where table_name=?1 and column_name=?2")
74 .setParameter(1, table.toUpperCase())
75 .setParameter(2, column.toUpperCase())
76 .getSingleResult()).intValue()==1;
77 }
78 }
79 public class SQLConstraintNonNull extends SQLConstruct {
80 private String table;
81 private String column;
82 public SQLConstraintNonNull(String table, String column) {
83 super(table+"."+column+" not null");
84 this.table=table;
85 this.column=column;
86 setCreate(String.format("alter table %s modify %s not null", table, column));
87 setDrop( String.format("alter table %s modify %s null", table, column));
88 }
89 @Override
90 public boolean exists() {
91 return ((Number)em.createNativeQuery(
92 "select count(*) from user_tab_cols where table_name=?1 and column_name=?2 and nullable='N'")
93 .setParameter(1, table.toUpperCase())
94 .setParameter(2, column.toUpperCase())
95 .getSingleResult()).intValue()==1;
96 }
97 }
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115 public SQLConstruct MOVIE_DIRECTOR_FKX = new SQLIndex("movie_director_fkx", "create index movie_director_fkx on jpatune_movie(director_id)");
116 public SQLConstruct MOVIE_RATING_IDX = new SQLIndex("movie_rating_idx", "create index movie_rating_idx on jpatune_movie(rating)");
117 public SQLConstruct MOVIE_RATING_RIDX = new SQLIndex("movie_rating_ridx", "create index movie_rating_ridx on jpatune_movie(rating desc)");
118 public SQLConstruct MOVIE_RATING_LOWER_IDX = new SQLIndex("movie_rating_lower_idx", "create index movie_rating_lower_idx on jpatune_movie(lower(rating))");
119 public SQLConstruct MOVIE_RATING_LOWER_RIDX = new SQLIndex("movie_rating_lower_ridx", "create index movie_rating_lower_ridx on jpatune_movie(lower(rating) desc)");
120 public SQLConstruct MOVIE_TITLE_IDX = new SQLIndex("movie_title_idx", "create index movie_title_idx on jpatune_movie(title)");
121 public SQLConstruct MOVIE_TITLE_RIDX = new SQLIndex("movie_title_ridx", "create index movie_title_ridx on jpatune_movie(title desc)");
122 public SQLConstruct MOVIE_RATING_TITLE_IDX = new SQLIndex("movie_rating_title_idx", "create index movie_rating_title_idx on jpatune_movie(rating, title)");
123 public SQLConstruct MOVIE_TITLE_RATING_IDX = new SQLIndex("movie_title_rating_idx", "create index movie_title_rating_idx on jpatune_movie(title, rating)");
124 public SQLConstruct MOVIE_TITLE_RDATE_IDX = new SQLIndex("movie_title_rdate_idx", "create index movie_title_rdate_idx on jpatune_movie(title, release_date)");
125 public SQLConstruct MOVIE_TITLE_RDATE_ID_IDX = new SQLIndex("movie_title_rdate_id_idx", "create index movie_title_rdate_id_idx on jpatune_movie(title, release_date, id)");
126 public SQLConstruct MOVIE_RDATE_IDX = new SQLIndex("movie_rdate_idx", "create index movie_rdate_idx on jpatune_movie(release_date)");
127 public SQLConstruct GENRE_MOVIE_FKX = new SQLIndex("genre_movie_fkx", "create index genre_movie_fkx on jpatune_moviegenre(movie_id)");
128 public SQLConstruct MOVIEROLE_ACTOR_FKX = new SQLIndex("movierole_actor_fkx", "create index movierole_actor_fkx on jpatune_movierole(actor_id)");
129 public SQLConstruct MOVIEROLE_MOVIE_FKX = new SQLIndex("movierole_movie_fkx", "create index movierole_movie_fkx on jpatune_movierole(movie_id)");
130
131 public SQLConstruct MOVIE_UTITLE = new SQLColumn("jpatune_movie", "utitle", "alter table jpatune_movie add utitle varchar2(256)",
132 "update jpatune_movie set utitle=concat(concat(concat(title,'('),id),')')");
133 public SQLConstruct MOVIE_UTITLE_NONNULL = new SQLConstraintNonNull("jpatune_movie", "utitle");
134 public SQLConstruct MOVIE_UTITLE_IDX = new SQLIndex("movie_utitle_idx", "create index movie_utitle_idx on jpatune_movie(utitle)");
135 public SQLConstruct MOVIE_UTITLE_UDX = new SQLIndex("movie_utitle_udx", "create unique index movie_utitle_udx on jpatune_movie(utitle)");
136
137
138 public SQLConstruct MOVIE_ROLE_IDX = new SQLIndex("movie_role_idx", "create index movie_role_idx on jpatune_movierole(movie_role)");
139 public SQLConstruct MOVIE_ROLE_MOVIE_FDX = new SQLIndex("movie_role_movie_fdx", "create index movie_role_movie_fdx on jpatune_movierole(movie_id)");
140 public SQLConstruct MOVIE_ROLE_MOVIE_CDX = new SQLIndex("movie_role_movie_cdx", "create index movie_role_movie_cdx on jpatune_movierole(movie_role, movie_id)");
141 public SQLConstruct MOVIE_ROLE_ACTOR_MOVIE_CDX = new SQLIndex("movierole_actor_movie_cdx", "create index movierole_actor_movie_cdx on jpatune_movierole(actor_id, movie_id)");
142 public SQLConstruct MOVIE_ROLE_MOVIE_ACTOR_CDX = new SQLIndex("movierole_movie_actor_cdx", "create index movierole_movie_actor_cdx on jpatune_movierole(movie_id, actor_id)");
143
144 public void populate() {
145 }
146
147 public void cleanup() {
148 dropConstructs();
149 }
150
151 public MovieFactory executeSQL(SQLConstruct[] constructs) {
152 log.info("------------------------------------------------------------");
153 SQLStatement[] statements = new SQLStatement[constructs.length];
154 for (int i=0; i< constructs.length; i++) {
155 statements[i] = new SQLStatement(constructs[i], true);
156 }
157 executeSQL(statements, false);
158 log.info("------------------------------------------------------------");
159 return this;
160 }
161
162 public MovieFactory executeSQL(SQLStatement sql[], boolean drop) {
163 for (SQLStatement s: sql) {
164 EntityTransaction tx = em.getTransaction();
165 tx.begin();
166 StringBuilder text = new StringBuilder(drop ? s.sql.getDrop() : s.sql.getCreate());
167 try {
168 boolean exists = s.sql.exists();
169 if (!drop && !exists) {
170 log.info("{}",text);
171 em.createNativeQuery(s.sql.getCreate()).executeUpdate();
172 if (s.sql.getPopulate()!=null) {
173 text=new StringBuilder(s.sql.getPopulate());
174 log.info("{}",text);
175 em.createNativeQuery(s.sql.getPopulate()).executeUpdate();
176 }
177 } else if (drop && exists) {
178 log.info("{}", text);
179 em.createNativeQuery(s.sql.getDrop()).executeUpdate();
180 } else {
181 text.append(" (noop)");
182 log.debug("{}",text);
183 }
184 } catch (Exception ex) {
185 if (s.required) {
186 log.error("{}",text);
187 log.error("failed:" + s.sql, ex);
188 throw new RuntimeException("failed:" + s.sql, ex);
189 }
190 }
191 if (!tx.getRollbackOnly()) { tx.commit(); }
192 else { tx.rollback(); }
193 }
194 return this;
195 }
196
197 public MovieFactory executeSQL(String[] sql, boolean failOnError) {
198 for (String s: sql) {
199 EntityTransaction tx = em.getTransaction();
200 tx.begin();
201 try {
202 em.createNativeQuery(s).executeUpdate();
203 } catch (Exception ex) {
204 if (failOnError) {
205 log.error("failed:" + s, ex);
206 throw new RuntimeException("failed:" + s, ex);
207 }
208 }
209 if (!tx.getRollbackOnly()) { tx.commit(); }
210 else { tx.rollback(); }
211 }
212 return this;
213 }
214
215 public MovieFactory assertConstructs(SQLConstruct[] sql) {
216 for (SQLConstruct s: sql) {
217 Assert.assertTrue(s.getName() + " does not exist", s.exists());
218 }
219 return this;
220 }
221
222
223 public MovieFactory dropConstructs() {
224 SQLStatement sql[] = new SQLStatement[]{
225 new SQLStatement(MOVIE_DIRECTOR_FKX, false),
226 new SQLStatement(GENRE_MOVIE_FKX, false),
227 new SQLStatement(MOVIEROLE_ACTOR_FKX, false),
228 new SQLStatement(MOVIEROLE_MOVIE_FKX, false),
229 new SQLStatement(MOVIE_RATING_IDX, false),
230 new SQLStatement(MOVIE_RATING_RIDX, false),
231 new SQLStatement(MOVIE_RATING_LOWER_IDX, false),
232 new SQLStatement(MOVIE_RATING_LOWER_RIDX, false),
233 new SQLStatement(MOVIE_TITLE_IDX, false),
234 new SQLStatement(MOVIE_TITLE_RIDX, false),
235 new SQLStatement(MOVIE_RATING_TITLE_IDX, false),
236 new SQLStatement(MOVIE_TITLE_RATING_IDX, false),
237 new SQLStatement(MOVIE_UTITLE_IDX, false),
238 new SQLStatement(MOVIE_UTITLE_NONNULL, false),
239 new SQLStatement(MOVIE_UTITLE_IDX, false),
240 new SQLStatement(MOVIE_UTITLE_UDX, false),
241 new SQLStatement(MOVIE_TITLE_RDATE_IDX, false),
242 new SQLStatement(MOVIE_TITLE_RDATE_ID_IDX, false),
243 new SQLStatement(MOVIE_RDATE_IDX, false),
244 new SQLStatement(MOVIE_ROLE_IDX, false),
245 new SQLStatement(MOVIE_ROLE_MOVIE_FDX, false),
246 new SQLStatement(MOVIE_ROLE_MOVIE_CDX, false),
247 new SQLStatement(MOVIE_ROLE_ACTOR_MOVIE_CDX, false),
248 new SQLStatement(MOVIE_ROLE_MOVIE_ACTOR_CDX, false)
249 };
250 executeSQL(sql, true);
251 return this;
252 }
253
254 public MovieFactory createFKIndexes() {
255 SQLConstruct sql[] = new SQLConstruct[]{
256 MOVIE_DIRECTOR_FKX,
257 GENRE_MOVIE_FKX,
258 MOVIEROLE_ACTOR_FKX,
259 MOVIEROLE_MOVIE_FKX
260 };
261 executeSQL(sql);
262 return this;
263 }
264
265 public MovieFactory flush() {
266 executeSQL(new String[]{
267 "alter system flush shared_pool",
268 "alter system flush buffer_cache"
269 }, true);
270 return this;
271 }
272
273 }