-
Notifications
You must be signed in to change notification settings - Fork 0
/
extension-es-sql.html
246 lines (212 loc) · 17.8 KB
/
extension-es-sql.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>SQL EntityStore</title><link rel="stylesheet" href="css/style.css" type="text/css" /><meta name="generator" content="DocBook XSL Stylesheets V1.75.2" /><link rel="home" href="index.html" title="" /><link rel="up" href="extensions.html" title="Extensions" /><link rel="prev" href="extension-es-riak.html" title="Riak EntityStore" /><link rel="next" href="extension-es-voldemort.html" title="Voldemort EntityStore" />
<!-- favicon -->
<link rel="shortcut icon" href="http://qi4j.org/favicon.ico" type="image/vnd.microsoft.icon" />
<link rel="icon" href="http://qi4j.org/favicon.ico" type="image/x-icon" />
<!-- style -->
<link href="css/shCore.css" rel="stylesheet" type="text/css" />
<link href="css/shCoreEclipse.css" rel="stylesheet" type="text/css" />
<link href="css/shThemeEclipse.css" rel="stylesheet" type="text/css" />
<link href="css/qi4j.css" rel="stylesheet" type="text/css" />
<!-- Syntax Highlighter -->
<script type="text/javascript" src="js/shCore.js"></script>
<script type="text/javascript" src="js/shBrushJava.js"></script>
<script type="text/javascript" src="js/shBrushScala.js"></script>
<script type="text/javascript" src="js/shBrushJScript.js"></script>
<script type="text/javascript" src="js/shBrushBash.js"></script>
<script type="text/javascript" src="js/shBrushPlain.js"></script>
<script type="text/javascript" src="js/shBrushXml.js"></script>
<script type="text/javascript" src="js/shBrushGroovy.js"></script>
<script type="text/javascript" src="js/shBrushPython.js"></script>
<script type="text/javascript" src="js/shBrushRuby.js"></script>
<script type="text/javascript" src="js/shBrushCSharp.js"></script>
<script type="text/javascript">
SyntaxHighlighter.defaults['tab-size'] = 4;
SyntaxHighlighter.defaults['gutter'] = false;
SyntaxHighlighter.defaults['toolbar'] = false;
SyntaxHighlighter.all()
</script>
<!-- JQuery -->
<script type="text/javascript" src="js/jquery-1.6.4.min.js"></script>
<!-- Image Scaler -->
<script type="text/javascript" src="js/imagescaler.js"></script>
<!-- Table Styler -->
<script type="text/javascript" src="js/tablestyler.js"></script>
<!-- Qi4j WebSite Progressive Enhancement -->
<link href="css/progressive-enhancement.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="js/jquery.scrollTo-1.4.2.js"></script>
<script type="text/javascript" src="js/progressive-enhancement.js"></script>
<!-- Analytics -->
<script type="text/javascript">
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-3118496-1']);
_gaq.push(['_trackPageview']);
(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
</script>
</head><body><div xmlns="" xmlns:exsl="http://exslt.org/common" class="logo"><a href="index.html"><img src="images/logo-standard.png" /></a></div><div xmlns="" xmlns:exsl="http://exslt.org/common" class="top-nav"><div xmlns="http://www.w3.org/1999/xhtml" class="toc"><dl><dt><span class="section"><a href="index.html#home">Qi4j</a></span></dt><dt><span class="section"><a href="intro.html">Introduction</a></span></dt><dt><span class="section"><a href="tutorials.html">Tutorials</a></span></dt><dt><span class="section"><a href="javadocs.html">Javadoc</a></span></dt><dt><span class="section"><a href="samples.html">Samples</a></span></dt><dt><span class="section"><a href="core.html">Core</a></span></dt><dt><span class="section"><a href="libraries.html">Libraries</a></span></dt><dt><span class="section"><span xmlns="" href="extensions.html">Extensions</span></span></dt><dt><span class="section"><a href="tools.html">Tools</a></span></dt><dt><span class="section"><a href="glossary.html">Glossary </a></span></dt></dl></div></div><div xmlns="" xmlns:exsl="http://exslt.org/common" class="sub-nav"><div xmlns="http://www.w3.org/1999/xhtml" class="toc"><dl><dt><span class="section"><a href="extensions.html#_overview_7">Overview</a></span></dt><dt><span class="section"><a href="extension-vs-orgjson.html">org.json ValueSerialization</a></span></dt><dt><span class="section"><a href="extension-vs-jackson.html">Jackson ValueSerialization</a></span></dt><dt><span class="section"><a href="extension-vs-stax.html">StAX ValueSerialization</a></span></dt><dt><span class="section"><a href="extension-cache-ehcache.html">Ehcache Cache</a></span></dt><dt><span class="section"><a href="extension-es-memory.html">Memory EntityStore</a></span></dt><dt><span class="section"><a href="extension-es-file.html">File EntityStore</a></span></dt><dt><span class="section"><a href="extension-es-gae.html">Google AppEngine EntityStore</a></span></dt><dt><span class="section"><a href="extension-es-hazelcast.html">Hazelcast EntityStore</a></span></dt><dt><span class="section"><a href="extension-es-jclouds.html">JClouds EntityStore</a></span></dt><dt><span class="section"><a href="extension-es-jdbm.html">JDBM EntityStore</a></span></dt><dt><span class="section"><a href="extension-es-leveldb.html">LevelDB EntityStore</a></span></dt><dt><span class="section"><a href="extension-es-mongodb.html">MongoDB EntityStore</a></span></dt><dt><span class="section"><a href="extension-es-neo4j.html">Neo4j EntityStore</a></span></dt><dt><span class="section"><a href="extension-es-preferences.html">Preferences EntityStore</a></span></dt><dt><span class="section"><a href="extension-es-redis.html">Redis EntityStore</a></span></dt><dt><span class="section"><a href="extension-es-riak.html">Riak EntityStore</a></span></dt><dt><span class="section"><span xmlns="" href="extension-es-sql.html">SQL EntityStore</span></span></dt><dt><span class="section"><a href="extension-es-voldemort.html">Voldemort EntityStore</a></span></dt><dt><span class="section"><a href="extension-index-elasticsearch.html">ElasticSearch Index/Query</a></span></dt><dt><span class="section"><a href="extension-index-rdf.html">OpenRDF Index/Query</a></span></dt><dt><span class="section"><a href="extension-index-solr.html">Apache Solr Index/Query</a></span></dt><dt><span class="section"><a href="extension-indexing-sql.html">SQL Index/Query</a></span></dt><dt><span class="section"><a href="extension-metrics-yammer.html">Yammer Metrics</a></span></dt><dt><span class="section"><a href="extension-migration.html">Migration</a></span></dt><dt><span class="section"><a href="extension-reindexer.html">Reindexer</a></span></dt></dl></div></div><div class="section" title="SQL EntityStore"><div class="titlepage"><div><div><h3 class="title"><a id="extension-es-sql"></a>SQL EntityStore</h3></div></div></div><p class="remark"><i><span class="comment"></span></i></p><p class="devstatus-code-stable">code</p><p class="devstatus-docs-good">docs</p><p class="devstatus-tests-good">tests</p><p>EntityStore service backed by a SQL database.</p><p>This extension fully leverage the <a class="xref" href="library-sql.html" title="SQL">SQL Library</a> meaning that you must use it to assemble your DataSource and that you
get <a class="link" href="library-circuitbreaker.html" title="Circuit Breaker">Circuit Breaker</a> and <a class="link" href="library-jmx.html" title="JMX">JMX</a> integration for free.</p><div class="tip" title="Tip" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Tip</h3><p>See the <a class="xref" href="sample-sql-support.html" title="SQL Support Sample">SQL Support Sample</a> that demonstrate combined use of <a class="xref" href="library-sql.html" title="SQL">SQL Library</a>, <a class="xref" href="extension-es-sql.html" title="SQL EntityStore"> SQL EntityStore</a> and
<a class="xref" href="extension-indexing-sql.html" title="SQL Index/Query">SQL Index/Query</a>.</p></div><p>The following SQL databases are supported:</p><div class="itemizedlist"><ul class="itemizedlist"><li class="listitem">
<a class="ulink" href="http://www.postgresql.org/" target="_top">PostgreSQL</a>
</li><li class="listitem">
<a class="ulink" href="http://www.mysql.com/" target="_top">MySQL</a> and <a class="ulink" href="http://mariadb.org/" target="_top">MariaDB</a>
</li><li class="listitem">
<a class="ulink" href="http://www.sqlite.org/" target="_top">SQLite</a>
</li><li class="listitem">
<a class="ulink" href="http://www.h2database.com/" target="_top">H2 Database Engine</a>
</li><li class="listitem">
<a class="ulink" href="http://db.apache.org/derby/" target="_top">Apache Derby</a> and <a class="ulink" href="http://www.oracle.com/technetwork/java/javadb/overview/index.htm" target="_top">Oracle JavaDB</a>
</li></ul></div><p>Each entity state is stored as a single row so maximum number of entities is the maximum number of rows per table
supported by the underlying SQL database.</p><p>Implementations per database Vendor share a generic codebase but can override about everything SQL. As a consequence
they can have strong differences in terms of performance if they use vendor specific extensions.</p><div class="table"><a id="idp20835264"></a><p class="title"><b>Table 78. Artifact</b></p><div class="table-contents"><table summary="Artifact" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th align="left" valign="top">Group ID</th><th align="left" valign="top">Artifact ID</th><th align="left" valign="top">Version</th></tr></thead><tbody><tr><td align="left" valign="top"><p>org.qi4j.extension</p></td><td align="left" valign="top"><p>org.qi4j.extension.entitystore-sql</p></td><td align="left" valign="top"><p>2.0</p></td></tr></tbody></table></div></div><br class="table-break" /><div class="section" title="Configuration"><div class="titlepage"><div><div><h4 class="title"><a id="_configuration_13"></a>Configuration</h4></div></div></div><p>SQL EntityStore Configuration is optional and provides only one configuration property: <code class="literal">schemaName</code> defaulted to
<span class="emphasis"><em>qi4j_es</em></span>. On SQL databases that don’t support schemas this configuration property is simply ignored.</p><p>The assembly snippets below show the DataSource assembly alongside the SQL EntityStore assembly. Remember to configure
the DataSource properly, see <a class="xref" href="library-sql.html" title="SQL">SQL Library</a> and <a class="xref" href="howto-configure-service.html" title="Configure a Service">Configure a Service</a>.</p></div><div class="section" title="PostgreSQL"><div class="titlepage"><div><div><h4 class="title"><a id="_postgresql"></a>PostgreSQL</h4></div></div></div><p>Maximum number of entities is unlimited.</p><p>Assembly is done using the provided Assembler:</p><pre class="programlisting brush: java">public void assemble( ModuleAssembly module )
throws AssemblyException
{
[...snip...]
// DataSourceService
new DBCPDataSourceServiceAssembler().
identifiedBy( "postgresql-datasource-service" ).
visibleIn( Visibility.module ).
withConfig( config ).
withConfigVisibility( Visibility.layer ).
assemble( module );
// DataSource
new DataSourceAssembler().
withDataSourceServiceIdentity( "postgresql-datasource-service" ).
identifiedBy( "postgresql-datasource" ).
visibleIn( Visibility.module ).
withCircuitBreaker();
// SQL EntityStore
new PostgreSQLEntityStoreAssembler().
visibleIn( Visibility.application ).
withConfig( config ).
withConfigVisibility( Visibility.layer ).
assemble( module );
}
</pre><p>Sample DataSource configuration defaults:</p><pre class="programlisting brush: java">enabled=true
url=jdbc:postgresql://localhost:5432/jdbc_test_db
driver=org.postgresql.Driver
username=jdbc_test_login
password=password</pre></div><div class="section" title="MySQL and MariaDB"><div class="titlepage"><div><div><h4 class="title"><a id="_mysql_and_mariadb"></a>MySQL and MariaDB</h4></div></div></div><p>Maximum number of entities depends on the choosen storage engine.</p><p>Assembly is done using the provided Assembler:</p><pre class="programlisting brush: java">public void assemble( ModuleAssembly module )
throws AssemblyException
{
[...snip...]
// DataSourceService
new DBCPDataSourceServiceAssembler().
identifiedBy( "mysql-datasource-service" ).
visibleIn( Visibility.module ).
withConfig( config ).
withConfigVisibility( Visibility.layer ).
assemble( module );
// DataSource
new DataSourceAssembler().
withDataSourceServiceIdentity( "mysql-datasource-service" ).
identifiedBy( "mysql-datasource" ).
visibleIn( Visibility.module ).
withCircuitBreaker().
assemble( module );
// SQL EntityStore
new MySQLEntityStoreAssembler().
visibleIn( Visibility.application ).
withConfig( config ).
withConfigVisibility( Visibility.layer ).
assemble( module );
}
</pre><p>Sample DataSource configuration defaults:</p><pre class="programlisting brush: java">enabled=true
url=jdbc:mysql://localhost:3306/jdbc_test_db?profileSQL=true
driver=com.mysql.jdbc.Driver
username=root
password=</pre></div><div class="section" title="SQLite"><div class="titlepage"><div><div><h4 class="title"><a id="_sqlite"></a>SQLite</h4></div></div></div><p>Maximum number of entities is unlimited.</p><p>The <a class="ulink" href="http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC" target="_top">Xerial SQLite JDBC</a> driver is recommended.
It provides native support on Linux, Windows and MaxOSX, pure Java on other OSes.</p><p>Assembly is done using the provided Assembler:</p><pre class="programlisting brush: java">public void assemble( ModuleAssembly module )
throws AssemblyException
{
[...snip...]
// DataSourceService
new DBCPDataSourceServiceAssembler().
identifiedBy( "sqlite-datasource-service" ).
visibleIn( Visibility.module ).
withConfig( config ).
withConfigVisibility( Visibility.layer ).
assemble( module );
// DataSource
new DataSourceAssembler().
withDataSourceServiceIdentity( "sqlite-datasource-service" ).
identifiedBy( "sqlite-datasource" ).
visibleIn( Visibility.module ).
withCircuitBreaker().
assemble( module );
// SQL EntityStore
new SQLiteEntityStoreAssembler().
visibleIn( Visibility.application ).
withConfig( config ).
withConfigVisibility( Visibility.layer ).
assemble( module );
}
</pre><p>Sample DataSource configuration defaults:</p><pre class="programlisting brush: java">enabled=true
url=jdbc:sqlite::memory:
driver=org.sqlite.JDBC
username=
password=</pre></div><div class="section" title="H2 Database Engine"><div class="titlepage"><div><div><h4 class="title"><a id="_h2_database_engine"></a>H2 Database Engine</h4></div></div></div><p>Maximum number of entities is 2^64.</p><p>Assembly is done using the provided Assembler:</p><pre class="programlisting brush: java">public void assemble( ModuleAssembly module )
throws AssemblyException
{
[...snip...]
// DataSourceService
new DBCPDataSourceServiceAssembler().
identifiedBy( "h2-datasource-service" ).
visibleIn( Visibility.module ).
withConfig( config ).
withConfigVisibility( Visibility.layer ).
assemble( module );
// DataSource
new DataSourceAssembler().
withDataSourceServiceIdentity( "h2-datasource-service" ).
identifiedBy( "h2-datasource" ).
visibleIn( Visibility.module ).
withCircuitBreaker().
assemble( module );
// SQL EntityStore
new H2SQLEntityStoreAssembler().
visibleIn( Visibility.application ).
withConfig( config ).
withConfigVisibility( Visibility.layer ).
assemble( module );
}
</pre><p>Sample DataSource configuration defaults:</p><pre class="programlisting brush: java">enabled=true
url=jdbc:h2:mem:test
driver=org.h2.Driver
username=
password=</pre></div><div class="section" title="Apache Derby and Oracle JavaDB"><div class="titlepage"><div><div><h4 class="title"><a id="_apache_derby_and_oracle_javadb"></a>Apache Derby and Oracle JavaDB</h4></div></div></div><p>Maximum number of entities is unlimited.</p><p>Assembly is done using the provided Assembler:</p><pre class="programlisting brush: java">public void assemble( ModuleAssembly module )
throws AssemblyException
{
[...snip...]
// DataSourceService
new DBCPDataSourceServiceAssembler().
identifiedBy( "derby-datasource-service" ).
visibleIn( Visibility.module ).
withConfig( config ).
withConfigVisibility( Visibility.layer ).
assemble( module );
// DataSource
new DataSourceAssembler().
withDataSourceServiceIdentity( "derby-datasource-service" ).
identifiedBy( "derby-datasource" ).
visibleIn( Visibility.module ).
withCircuitBreaker().
assemble( module );
// SQL EntityStore
new DerbySQLEntityStoreAssembler().
visibleIn( Visibility.application ).
withConfig( config ).
withConfigVisibility( Visibility.layer ).
assemble( module );
}
</pre><p>Sample DataSource configuration defaults:</p><pre class="programlisting brush: java">enabled=true
url=jdbc:derby:memory:testdb;create=true
driver=org.apache.derby.jdbc.EmbeddedDriver
username=
password=</pre></div></div><div xmlns="" xmlns:exsl="http://exslt.org/common" class="footer">(c) 2012 The Qi4j Community</div></body></html>