/
dbimport.ad
196 lines (171 loc) · 8.1 KB
/
dbimport.ad
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
[id="dbimport",reftext="DbImport"]
=== DBImport
The DbImport utility has the ability to import the structure and data of a tables(s) from a file to database tables.
ifdef::pro[]
The DbImport wizard is available from the explore tab of the pro console.
The popup window asks for the file format, catalog, schema, and tables to import to the database.
It also asks for the number of rows to commit, as well as multiple more specific options for the import.
image::appendix/dbimport/dbimport-tab.png[width=600]
Input files can be in SQL, XML, CSV, CSV_DQUOTE, or SYM_XML file formats.
The following is an example of the *SQL* format used for DbImport.
[source,sql]
----
CREATE TABLE ITEM(
ITEM_ID INTEGER NOT NULL,
NAME VARCHAR(100) NULL,
PRIMARY KEY (ITEM_ID)
);
insert into ITEM (ITEM_ID, NAME) values (1805,'lVlmyxlADKEMZFIfWiipfiBqoeMHsSjsbgsoqBuEAWKhmJfaNA');
insert into ITEM (ITEM_ID, NAME) values (2649,'qjyWrDHXnsfdSDBqUzHMJPkRFQmwmWPWxBPPwaQmgpoQrqLcQC');
insert into ITEM (ITEM_ID, NAME) values (3334,'sCTJrMPFLauMvNrmYVyuLwcrinAVDVNmuHLULCWGYYmHRSmbsc');
insert into ITEM (ITEM_ID, NAME) values (3613,'xCNfevpgVBfegbrXHQOsChxCYPcwbjkpHXZpLFkLxMACaFWJnF');
insert into ITEM (ITEM_ID, NAME) values (3622,'UPTWPHujLPjFvusxAqzKSZCSFXXyhqfkbmholwDvuNdhLfIBGY');
----
The following is an example of the *CSV* format used for DbImport.
[source, csv]
----
"ITEM_ID","NAME"
"12","zSYwAyLGsbvsLhYFLBqmeprkfISVTlRnfHwsHFZcmZUpKQMXkT"
"471","RFHEaWcFtPDOkqEtbomEomuEWDQoAuyvJVnjPEsPpaqLxEuWpj"
"113","SDFSDf\"SD\"ggdD"
----
The following is an example of the *CSV_DQUOTE* format used for DbImport.
*CSV_DQUOTE* escapes quote characters with double quotes if there are quotes in the column values so it can be correctly parsed by programs like Excel.
For example, "SPbSDf""SD""gNdD" parses to SPbSDf"SD"gNdD.
[source, csv]
----
"ITEM_ID","NAME"
"12","zSYwAyLGsbvsLhYFLBqmeprkfISVTlRnfHwsHFZcmZUpKQMXkT"
"471","RFHEaWcFtPDOkqEtbomEomuEWDQoAuyvJVnjPEsPpaqLxEuWpj"
"113","SPbSDf""SD""gNdD"
----
The following is an example of the *XML* format used for DbImport.
[source,xml]
----
<database xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="dbimport">
<table name="ITEM">
<column name="ITEM_ID" primaryKey="true" required="true" type="INTEGER" size="10">
<platform-column name="h2" type="INTEGER" size="10"/>
</column>
<column name="NAME" type="VARCHAR" size="100">
<platform-column name="h2" type="VARCHAR" size="100"/>
</column>
</table>
<table_data name="ITEM">
<row>
<field name="ITEM_ID">1805</field>
<field name="NAME">lVlmyxlADKEMZFIfWiipfiBqoeMHsSjsbgsoqBuEAWKhmJfaNA</field>
</row>
<row>
<field name="ITEM_ID">2649</field>
<field name="NAME">qjyWrDHXnsfdSDBqUzHMJPkRFQmwmWPWxBPPwaQmgpoQrqLcQC</field>
</row>
<row>
<field name="ITEM_ID">3334</field>
<field name="NAME">sCTJrMPFLauMvNrmYVyuLwcrinAVDVNmuHLULCWGYYmHRSmbsc</field>
</row>
<row>
<field name="ITEM_ID">3613</field>
<field name="NAME">xCNfevpgVBfegbrXHQOsChxCYPcwbjkpHXZpLFkLxMACaFWJnF</field>
</row>
<row>
<field name="ITEM_ID">3622</field>
<field name="NAME">UPTWPHujLPjFvusxAqzKSZCSFXXyhqfkbmholwDvuNdhLfIBGY</field>
</row>
<table_data>
----
endif::pro[]
ifndef::pro[]
The DbImport utility can be run directly from the command line.
To run DbImport enter the command *dbimport [filename...]* followed by any parameters from the command line in the bin directory of the Symmetric installation.
The *--help* parameter will display all the possible parameters for the import utility.
They are as follows:
- *--alter* : If the table already exists, attempt to alter it to match the import definition. This only applies for --format=XML.
- *--alter-case* : During the creation of tables alter the case to match the default case of the database. This only applies for --format=XML.
- *--catalog <arg>* : Look for tables in catalog.
- *--commit <arg>* : The number of rows to import before committing data. The default is 10000.
- *--debug* : Print debug information in logging.
- *--drop-if-exists* : If the table already exists, attempt to drop it before creating it. This only applies for --format=XML.
- *-e, --engine <arg>* : The name of a configured engine. The name should correspond to an engine.name setting in one of the properties files in the engines directory.
- *--filter-classes <arg>* : A comma separated list of Java classes that implement org.jumpmind.symmetric.io.data.writer.IDatabaseWriterFilter. These filters will be applied to the import.
- *--force* : Ignore ANY errors and attempt to continue on processing the import data.
- *--format <arg>* : Input format: SQL, CSV, CSV_DQUOTE, XML, or SYM_XML.
- *-h, --help* : Print help with each option and description.
- *--ignore* : Indicates that conflicts with existing rows should be ignored.
- *--interval <arg>* : The number of milliseconds to wait between committing transaction.
- *--no-log-console* : No output will be sent to the console.
- *--no-log-file* : No output will be sent to the log file.
- *-p, --properties <arg>* : The properties file with settings for the SymmetricDS engine. If not provided. defaults are used, then overridden with first symmetric.properties in classpath, then overridden with symmetric.properties values in user.home directory.
- *--replace* : Indicates that existing rows should be replaced. They will be replaced by an update statement. This only applies for --format=CSV, SYM_XML.
- *--schema <arg>* : Look for tables in schema.
- *--table <arg>* : Specify table to import.
- *--use-variable-dates* : Allow date subscription using format ${curdate+-millis}.
- *-v, --verbose* : Use verbose format for console output.
The following is an example of the *SQL* format used for DbImport.
[source,sql]
----
CREATE TABLE ITEM(
ITEM_ID INTEGER NOT NULL,
NAME VARCHAR(100) NULL,
PRIMARY KEY (ITEM_ID)
);
insert into ITEM (ITEM_ID, NAME) values (1805,'lVlmyxlADKEMZFIfWiipfiBqoeMHsSjsbgsoqBuEAWKhmJfaNA');
insert into ITEM (ITEM_ID, NAME) values (2649,'qjyWrDHXnsfdSDBqUzHMJPkRFQmwmWPWxBPPwaQmgpoQrqLcQC');
insert into ITEM (ITEM_ID, NAME) values (3334,'sCTJrMPFLauMvNrmYVyuLwcrinAVDVNmuHLULCWGYYmHRSmbsc');
insert into ITEM (ITEM_ID, NAME) values (3613,'xCNfevpgVBfegbrXHQOsChxCYPcwbjkpHXZpLFkLxMACaFWJnF');
insert into ITEM (ITEM_ID, NAME) values (3622,'UPTWPHujLPjFvusxAqzKSZCSFXXyhqfkbmholwDvuNdhLfIBGY');
----
The following is an example of the *CSV* format used for DbImport.
[source, csv]
----
"ITEM_ID","NAME"
"12","zSYwAyLGsbvsLhYFLBqmeprkfISVTlRnfHwsHFZcmZUpKQMXkT"
"471","RFHEaWcFtPDOkqEtbomEomuEWDQoAuyvJVnjPEsPpaqLxEuWpj"
"113","SDFSDf\"SD\"ggdD"
----
The following is an example of the *CSV_DQUOTE* format used for DbImport.
*CSV_DQUOTE* escapes quote characters with double quotes if there are quotes in the column values so it can be correctly parsed by programs like Excel.
For example, "SPbSDf""SD""gNdD" parses to SPbSDf"SD"gNdD.
[source, csv]
----
"ITEM_ID","NAME"
"12","zSYwAyLGsbvsLhYFLBqmeprkfISVTlRnfHwsHFZcmZUpKQMXkT"
"471","RFHEaWcFtPDOkqEtbomEomuEWDQoAuyvJVnjPEsPpaqLxEuWpj"
"113","SPbSDf""SD""gNdD"
----
The following is an example of the *XML* format used for DbImport.
[source,xml]
----
<database xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="dbimport">
<table name="ITEM">
<column name="ITEM_ID" primaryKey="true" required="true" type="INTEGER" size="10">
<platform-column name="h2" type="INTEGER" size="10"/>
</column>
<column name="NAME" type="VARCHAR" size="100">
<platform-column name="h2" type="VARCHAR" size="100"/>
</column>
</table>
<table_data name="ITEM">
<row>
<field name="ITEM_ID">1805</field>
<field name="NAME">lVlmyxlADKEMZFIfWiipfiBqoeMHsSjsbgsoqBuEAWKhmJfaNA</field>
</row>
<row>
<field name="ITEM_ID">2649</field>
<field name="NAME">qjyWrDHXnsfdSDBqUzHMJPkRFQmwmWPWxBPPwaQmgpoQrqLcQC</field>
</row>
<row>
<field name="ITEM_ID">3334</field>
<field name="NAME">sCTJrMPFLauMvNrmYVyuLwcrinAVDVNmuHLULCWGYYmHRSmbsc</field>
</row>
<row>
<field name="ITEM_ID">3613</field>
<field name="NAME">xCNfevpgVBfegbrXHQOsChxCYPcwbjkpHXZpLFkLxMACaFWJnF</field>
</row>
<row>
<field name="ITEM_ID">3622</field>
<field name="NAME">UPTWPHujLPjFvusxAqzKSZCSFXXyhqfkbmholwDvuNdhLfIBGY</field>
</row>
<table_data>
----
endif::pro[]